Excel funkcija OFFSET palēninās jūsu darbgrāmatas aprēķinu. Ir labāka alternatīva: neparasta INDEX sintakse.
Tas ir nišas padoms. Ir apbrīnojami elastīga funkcija, ko sauc par OFFSET. Tas ir elastīgs, jo var norādīt uz dažāda lieluma diapazonu, kas tiek aprēķināts lidojuma laikā. Zemāk redzamajā attēlā, ja kāds H1 nolaižamo izvēlni # Qtrs maina no 3 uz 4, OFFSET ceturtais arguments nodrošinās, ka diapazons tiek paplašināts, iekļaujot četras kolonnas.
Izklājlapu guru ienīst OFFSET, jo tā ir nepastāvīga funkcija. Ja dodaties uz pilnīgi nesaistītu šūnu un ievadāt skaitli, tiks aprēķinātas visas OFFSET funkcijas. Pat ja šai šūnai nav nekāda sakara ar H1 vai B2. Lielāko daļu laika Excel ir ļoti uzmanīgs, lai tērētu laiku tikai šūnu aprēķināšanai, kurām jāaprēķina. Bet, kad jūs ieviesīsit OFFSET, visas OFFSET šūnas, kā arī viss no OFFSET apakšējā līnija sāk aprēķināt pēc katrām izmaiņām darblapā.
Es tiesāju 2013. gada ModelOff finālus Ņujorkā, kad daži mani draugi no Austrālijas norādīja uz dīvainu risinājumu. Zemāk esošajā formulā pirms funkcijas INDEX ir kols. Parasti zemāk parādītā INDEX funkcija atgriezīs 1403 no šūnas D2. Bet, ievietojot kolu abās INDEX funkcijas pusēs, tā sāk atgriezt šūnas adresi D2, nevis D2 saturu. Tas ir savvaļas, ka tas darbojas.
Kāpēc tas ir svarīgi? INDEX nav nestabila. Jūs saņemat visu OFFSET elastīgo labumu bez laika pārspīlētiem pārrēķiniem.
Es pirmo reizi uzzināju šo padomu no Dan Mayoh vietnē Fintega. Paldies programmai Access Analytic par šīs funkcijas ieteikšanu.
Skatīties video
Video atšifrējums
Uzziniet programmu Excel no podkasta, 2048. sērija -: INDEX aizstās gaistošu kompensāciju!
Hei, es aplādēju visus savus padomus no šīs grāmatas, augšējā labajā stūrī noklikšķiniet uz “i”, lai nokļūtu atskaņošanas sarakstā!
Labi, OFFSET ir pārsteidzoša funkcija! OFFSET ļauj mums norādīt augšējā kreisā stūra šūnu un pēc tam izmantot mainīgos, lai no turienes noteiktu, cik rindu uz leju, cik rindu pāri, un pēc tam definējiet formu labi. Tātad šeit, ja es gribu summēt vai veikt vidējo, teiksim, 3/4, šī formula šeit apskatīs formulu. OFFSET saka, ka mēs sākam no B2, sākot no Q1, mēs ejam uz leju 0, vairāk nekā 0, forma būs 1 rindas gara, un tā būs H1, citiem vārdiem sakot, 3 šūnas plata, labi. Tātad šajā gadījumā viss, ko mēs patiešām darām, ir mainīt to, cik daudz šūnu mēs summējam, mēs vienmēr sākam atpakaļ B2, B3 vai B4, kad es nokopēju uz leju, un tad tas izlemj, cik daudz šūnu ir. Labi, OFFSET ir šī foršā lieta, tā veic visādas apbrīnojamas funkcijas, bet šeit ir grūtības, tas ir nepastāvīgs!Tas nozīmē, ka pat tad, ja kaut kas nav aprēķinu ķēdē, Excel prasīs laiku, lai to pārrēķinātu, kas palēninās lietas, labi.
Šī apbrīnojamā INDEX versija, labi, parasti, ja es lūgtu šo 4 šūnu INDEX, 3, tas atgriezīs skaitli 1403. Tomēr, kad es ievietoju kolu pirms vai pēc INDEX, notiek kaut kas ļoti atšķirīgs, mēs šeit apskatīsim šo formulu. Tātad indeksu no 4 šūnām, kuru es vēlos, es gribu trešo, bet redzat, ka tur ir: tieši tur. Tātad mēs vienmēr ejam no B2: E2, un es jums parādīšu, vai mēs ejam uz Formulas, Novērtēt formulu, labi, tāpēc tieši šeit tiks aprēķināts skaitlis 3. Un šeit, INDEKSS ar: nākamo pie tā, tā vietā, lai pastāstītu mums 1403, kā parasti aprēķina INDEX, tas atgriezīs $ D2, un pēc tam AVERAGE darīs šo 3 vidējo rādītāju. Pilnīgi pārsteidzoši, tas, kā tas darbojas, un papildu ieguvums, tas nav gaistošs, labi,un to pat var izmantot, lai aizstātu neticami sarežģītu OFFSET.
Tātad šeit ar šo OFFSET mēs balstāmies uz šīm vērtībām. Ja es izvēlos Q2 un Central, labi, šīs formulas izmanto MATCH un COUNTIF, lai noskaidrotu, cik rindu uz leju, cik kolonnu virs, cik garš, cik plats. Un tad OFFSET šeit izmanto visas šīs vērtības, lai noskaidrotu mediānu. Mēs vienkārši veiksim testu, lai pārliecinātos, ka tas darbojas, tātad = ZILAIS MEDIAN no šī zilā diapazona, labāk ir 71, labi, un mēs šeit izvēlēsimies kaut ko citu, Q3 un West, tātad. Nospiediet F2, es to vienkārši velšu un mainīšu tā lielumu, lai pārrakstītu šo formulu, nospiediet taustiņu Enter, un tas darbojas ar OFFSET.
Nu, šeit, izmantojot INDEX, man patiesībā ir kols pa vidu ar INDEX kreisajā pusē un INDEX labajā pusē. Skatieties, kā šī lieta tiek aprēķināta programmā Novērtēt formulu. Tātad tas sākas, novērtēs, novērtēs un tieši šeit INDEX gatavojas to pārveidot par šūnas adresi. Tātad H16 ir 1., Rietumi, Q3, un labajā pusē tiks novērtēts, vēl pāris, un pēc tam pa labi viņai tas mainīsies uz I20. Tātad foršs, foršs, gaistošs, lai aizstātu OFFSET, izmantojot INDEX funkciju. Labi, šis padoms un daudz kas cits šajā grāmatā, noklikšķiniet uz “i” augšējā labajā stūrī, lai iegādātos grāmatu.
Labi, apkopojums: OFFSET, lieliska, elastīga funkcija, kad jūs apgūstat, jūs varat darīt visu veidu lietas. Norādiet uz mainīgu šūnu augšējā kreisajā stūrī, norādiet uz diapazonu, kuram ir mainīga forma, bet tas ir nepastāvīgs, un tāpēc viņi aprēķina katrā aprēķinā. Excel parasti veic gudru aprēķinu vai arī pārrēķina šūnas, kuras jāaprēķina, bet ar OFFSET tas vienmēr tiks aprēķināts. OFFSET vietā jūs varat izmantot INDEX: vai: INDEX vai pat INDEX: INDEX. Jebkurā laikā INDEX blakus ir kols, tas atgriezīs šūnas adresi, nevis šīs šūnas vērtību. Un ieguvums ir INDEX nav nestabils!
Labi, es vēlos pateikties, ka apstājāties, tiksimies nākamreiz uz citu netcast no!
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Podcast2048.xlsm
