Funkcia OFFSET v programe Excel spomalí výpočet vášho zošita. Existuje lepšia alternatíva: neobvyklá syntax indexu INDEX.
Toto je špecifický tip. Existuje neuveriteľne flexibilná funkcia s názvom OFFSET. Je flexibilný, pretože môže ukazovať na rozsah inej veľkosti, ktorý sa počíta za chodu. Ak niekto na dolnom obrázku zmení rozbaľovaciu ponuku # Qtrs v hodnotách H1 z 3 na 4, štvrtý argument funkcie OFFSET zabezpečí, že sa rozsah rozšíri tak, aby zahŕňal štyri stĺpce.
Guru tabuľky nenávidia OFFSET, pretože ide o volatilnú funkciu. Ak prejdete do úplne nesúvisiacej bunky a zadáte číslo, vypočítajú sa všetky funkcie OFFSET. Aj keď táto bunka nemá nič spoločné s H1 alebo B2. Aplikácia Excel je väčšinou veľmi opatrná, aby iba strácala čas výpočtom buniek, ktoré je potrebné vypočítať. Ale akonáhle zavediete OFFSET, všetky bunky OFFSET plus všetko podčiarknuté z OFFSETu sa začnú počítať po každej zmene v pracovnom hárku.
Súdil som finále 2013 ModelOff v New Yorku, keď niekoľko mojich priateľov z Austrálie poukázalo na bizarné riešenie. Vo vzorci nižšie je pred funkciou INDEX dvojbodka. Normálne funkcia INDEX zobrazená nižšie vráti 1403 z bunky D2. Ale keď vložíte dvojbodku na obidve strany funkcie INDEX, začne vracať bunkovú adresu D2 namiesto obsahu D2. Je divoké, že to funguje.
Prečo na tom záleží? INDEX nie je volatilný. Získate všetku flexibilnú dobrotu OFFSETU bez časovo náročných prepočtov znova a znova.
Prvýkrát som sa tento tip dozvedel od Dana Mayoha vo Fintege. Ďakujem spoločnosti Access Analytic za navrhnutie tejto funkcie.
Pozeraj video
Prepis videa
Naučte sa Excel z podcastu, epizóda 2048 -: INDEX nahradí prchavý OFFSET!
Ahoj, podcastujem všetky svoje tipy z tejto knihy. Kliknutím na písmeno „i“ v pravom hornom rohu sa dostanete do zoznamu skladieb!
Dobre, OFFSET je úžasná funkcia! OFFSET nám umožňuje určiť bunku v ľavom hornom rohu a potom pomocou premenných definovať, o koľko riadkov nadol, o koľko riadkov viac, a potom definovať tvar, v poriadku. Ak tu teda chcem spočítať alebo urobiť priemernú hodnotu povedzme 3/4, tento vzorec sa tu bude zaoberať týmto vzorcom. OFFSET hovorí, že začíname od B2, od Q1, klesáme 0, cez 0, tvar bude vysoký 1 rad a bude H1, inými slovami 3 bunky široké, v poriadku. Takže v tomto prípade všetko, čo skutočne robíme, je zmena počtu buniek, ktoré sčítame, vždy začíname späť v B2 alebo B3 alebo B4, keď kopírujem, a potom rozhodne, koľko buniek je široké. Dobre, OFFSET je táto skvelá vec, robí všetky druhy úžasných funkcií, ale tu je problém, je volatilný!Čo znamená, že aj keď niečo nie je vo výpočtovom reťazci, program Excel si bude musieť čas na prepočet vypočítať, čo v konečnom dôsledku spomalí priebeh.
Táto úžasná verzia INDEXu, správne, za normálnych okolností, keby som požiadal o INDEX týchto 4 buniek, 3, vráti číslo 1403. Keď však dám dvojbodku pred alebo za INDEX, stane sa niečo veľmi odlišné, tu sa pozrieme na tento vzorec. Takže index 4 buniek, ktorý z nich chcem, chcem tretí, ale vidíte, že tam je: práve tam. Takže vždy pôjdeme z B2: E2 a ukážem vám, či pôjdeme do Vzorcov, Vyhodnotiť vzorec, v poriadku, takže práve tu sa bude počítať číslo 3. A tu, INDEX s: ďalším namiesto toho, aby nám povedal 1403, čo by normálne vypočítal INDEX, vráti $ D2 a potom PRIEMER urobí priemer tých 3. Absolútne úžasný spôsob, akým to funguje, a ďalšia výhoda, nie je to nestále, v poriadku,a to sa dá dokonca použiť na nahradenie neuveriteľne zložitého OFSETU.
Takže tu s týmto OFFSETOM vychádzame z týchto hodnôt. Ak zvolím Q2 a Central, v poriadku, tieto vzorce používajú MATCH a COUNTIF na zistenie počtu riadkov nadol, počtu stĺpcov nad, výšky a šírky. A potom OFSET používa všetky tieto hodnoty na zistenie mediánu. Urobíme iba test, aby sme sa ubezpečili, že funguje, takže = MEDIAN z toho modrého rozsahu tam, lepšie 71, v poriadku, a my si tu vyberieme niečo iné, Q3 a West, takže. Stlačte kláves F2, iba to pretiahnem a zmeníme jeho veľkosť, aby som tento vzorec prepísal, stlačte kláves Enter a funguje to s OFSETOM.
No, pomocou INDEXU mám v skutočnosti dvojbodku uprostred s INDEXOM na ľavej strane a INDEXOM na pravej strane, sledujte, ako sa táto vec počíta v Hodnotiť vzorec. Takže to začne, bude vyhodnocovať, vyhodnocovať a práve tu to INDEX chystá zmeniť na adresu bunky. Takže H16 je 1st, West, Q3, a na pravej strane bude hodnotiť, párovať viac a potom napravo od nej sa to zmení na I20. Takže cool, cool non-volatile nahradiť OFFSET pomocou funkcie INDEX. Dobre, tento tip a mnoho ďalších v tejto knihe si kúpite tak, že kliknete na „i“ v pravom hornom rohu.
Dobre zrekapitulovať: OFFSET, úžasná, flexibilná funkcia, akonáhle zvládnete, môžete robiť všetky možné veci. Ukážte na premennú ľavú hornú bunku, ukážte na rozsah, ktorý má premenlivý tvar, ale je volatilný, a tak sa počíta pri každom výpočte. Excel zvyčajne vykoná inteligentný výpočet alebo prepočíta bunky, ktoré je potrebné vypočítať, ale pri OFSETE sa vždy vypočíta. Namiesto OFSET môžete použiť INDEX: alebo: INDEX alebo dokonca INDEX: INDEX, kedykoľvek má INDEX dvojbodku vedľa seba, vráti bunkovú adresu namiesto hodnoty tejto bunky. Výhodou je, že INDEX nie je nestály!
Dobre, chcem sa vám poďakovať za návštevu, uvidíme sa nabudúce pri ďalšom netcaste od!
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2048.xlsm
