Excelova funkcija OFFSET bo upočasnila izračun vašega delovnega zvezka. Obstaja boljša alternativa: nenavadna skladnja INDEX.
To je namig za nišo. Obstaja neverjetno prilagodljiva funkcija, imenovana OFFSET. Prilagodljiv je, ker lahko kaže na različno velik obseg, ki se izračuna sproti. Če na spodnji sliki nekdo spremeni spustni seznam # Qtrs v H1 s 3 na 4, bo četrti argument OFFSET poskrbel, da se bo obseg razširil na štiri stolpce.
Guru preglednic sovražijo OFFSET, ker je to spremenljiva funkcija. Če greste v popolnoma nepovezano celico in vnesete številko, bodo izračunane vse funkcije OFFSET. Tudi če ta celica nima nič skupnega s H1 ali B2. Excel je največkrat zelo previden, da samo zapravlja čas za izračun celic, ki jih je treba izračunati. Ko pa enkrat uvedete OFFSET, se vse celice OFFSET in vse spodnje črte iz OFFSET začnejo izračunavati po vsaki spremembi na delovnem listu.
Ocenjeval sem finale modela ModelOff leta 2013 v New Yorku, ko je nekaj mojih prijateljev iz Avstralije opozorilo na bizarno rešitev. V spodnji formuli je pred funkcijo INDEX dvopičje. Spodaj prikazana funkcija INDEX običajno vrne 1403 iz celice D2. Ko pa na obe strani funkcije INDEX postavite dvopičje, začne vrniti naslov celice D2 namesto vsebine D2. To je divje, da deluje.
Zakaj je to pomembno? INDEX ni spremenljiv. Dobite vso prilagodljivo dobroto OFFSET-a brez ponovnih preračunov za sesanje časa.
Tega nasveta sem se prvič naučil od Dana Mayoha pri Fintegi. Zahvaljujemo se programu Access Analytic, ki je predlagal to funkcijo.
Oglejte si video
Video zapis
Naučite se Excel iz podcasta, epizoda 2048 -: INDEX bo nadomestil hlapljiv POMAK!
Hej, podkastim vse svoje nasvete iz te knjige, kliknite na i v zgornjem desnem kotu, da pridete do seznama predvajanja!
V redu, OFFSET je neverjetna funkcija! OFFSET nam omogoča, da določimo zgornjo levo vogalno celico in nato s spremenljivkami določimo, koliko vrstic navzdol, koliko vrstic čez in nato določimo obliko, v redu. Torej, če želim sešteti ali narediti povprečje, recimo 3/4, bo ta formula tukaj pogledala formulo. OFFSET pravi, da začenjamo od B2, začenjamo od Q1, gremo navzdol za 0, nad 0, oblika bo 1 vrstico visoka in bo H1, z drugimi besedami široka 3 celice, v redu. V tem primeru torej vse, kar v resnici počnemo, spreminjamo, koliko celic seštevamo, vedno začnemo nazaj v B2 ali B3 ali B4, ko kopiram, nato pa se odloči, koliko celic je širokih. V redu, OFFSET je ta kul stvar, ki opravlja vse vrste neverjetnih funkcij, toda tukaj je težava, je nestanovitna!Kar pomeni, da si bo Excel, četudi ni v računski verigi, vzel čas za ponovni izračun, kar bo upočasnilo, v redu.
Ta neverjetna različica INDEX, kajne, običajno, če bi prosil za INDEX teh 4 celic, 3, bo vrnil številko 1403. Ko pa postavim dvopičje pred ali za INDEX, se zgodi nekaj povsem drugega tukaj bomo pogledali to formulo. Torej indeks 4 celic, katero hočem, hočem tretjo, ampak vidite, tam je:. Torej, vedno gremo od B2: E2, in pokazal vam bom, če gremo na Formule, ovrednoti formulo, v redu, torej tukaj bomo izračunali število 3. In tukaj, INDEX z: zanj, namesto da bi nam povedal 1403, kar bi običajno izračunal INDEX, bo vrnil D2 $, nato pa bo AVERAGE naredil povprečje teh 3. Popolnoma neverjetno, kako to deluje in dodana korist ni hlapno, v redu,in s tem lahko celo nadomestimo neverjetno zapleten OFFSET.
Torej, tukaj s tem OFFSET-om temeljimo na teh vrednostih. Če izberem Q2 in Central, te formule s pomočjo MATCH in COUNTIF ugotovijo, koliko vrstic navzdol, koliko stolpcev čez, kako visoko in kako široko. In potem OFFSET tukaj uporablja vse te vrednosti za ugotavljanje mediane. Naredili bomo samo test, da se prepričamo, da deluje, tako da = MEDIAN tistega modrega območja tam, raje 71, v redu, in tu bomo izbrali nekaj drugega, Q3 in West, torej. Pritisnite F2, samo povlečem to in jo spremenim, da napišem to formulo, pritisnem Enter in deluje z OFFSET.
No, tu z uporabo INDEX-a imam na sredini dejansko dvopičje z INDEX-om na levi in INDEX-om na desni strani, glejte, kako se ta stvar izračuna v formuli za ocenjevanje. Torej se začne, bo Evaluate, Evaluate in ravno tukaj ga bo INDEX spremenil v naslov celice. Torej, H16 je prvi, zahodni, Q3, na desni strani pa bo ocenil, še nekaj, nato pa se bo desno spremenil v I20. Torej hladen, hladen, hlapljiv, da nadomestite OFFSET s funkcijo INDEX. V redu, ta namig in še veliko več v tej knjigi kliknite na i v zgornjem desnem kotu, da kupite knjigo.
V redu povzetek: OFFSET, super, prilagodljiva funkcija, ko enkrat obvladate, lahko počnete vse mogoče stvari. Kažite na spremenljivo zgornjo levo celico, pokažite na obseg s spremenljivo obliko, ki pa je spremenljiv, zato izračunajo pri vsakem izračunu. Excel običajno opravi pameten izračun ali ponovno izračuna celice, ki jih je treba izračunati, vendar bo z OFFSET vedno izračunan. Namesto OFFSET lahko uporabite INDEX: ali: INDEX ali celo INDEX: INDEX, kadar koli ima INDEX dvopičje, namesto vrednosti te celice bo vrnil naslov celice. In korist je, da INDEKS ni nestanoviten!
V redu, zahvaljujem se vam, da ste se ustavili, se vidimo naslednjič za še eno oddajo!
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2048.xlsm
