Funcția OFFSET a Excel va încetini calculul registrului de lucru. Există o alternativă mai bună: o sintaxă neobișnuită a INDEX.
Acesta este un sfat de nișă. Există o funcție uimitor de flexibilă numită OFFSET. Este flexibil, deoarece poate indica o gamă de dimensiuni diferite, care este calculată din mers. În imaginea de mai jos, dacă cineva modifică meniul derulant # Qtrs în H1 de la 3 la 4, al patrulea argument al OFFSET se va asigura că intervalul se extinde pentru a include patru coloane.
Guru-urile foii de calcul urăsc OFFSET-ul deoarece este o funcție volatilă. Dacă mergeți la o celulă complet legată și introduceți un număr, toate funcțiile OFFSET se vor calcula. Chiar dacă celula respectivă nu are nimic de-a face cu H1 sau B2. De cele mai multe ori, Excel este foarte atent să piardă doar timpul calculând celulele care trebuie calculate. Dar odată ce introduceți OFFSET, toate celulele OFFSET, plus orice linie descendentă din OFFSET, încep să se calculeze după fiecare modificare a foii de lucru.
Judecam finalele ModelOff din 2013 din New York, când câțiva dintre prietenii mei din Australia au subliniat o soluție bizară. În formula de mai jos, există două puncte înainte de funcția INDEX. În mod normal, funcția INDEX prezentată mai jos ar returna 1403 din celula D2. Dar când puneți două puncte pe ambele părți ale funcției INDEX, acesta începe să returneze adresa celulei D2 în loc de conținutul D2. Este sălbatic că funcționează.
De ce contează asta? INDEX nu este volatil. Obțineți toată bunătatea flexibilă a OFFSET-ului fără recalcularile care sugerează timpul din nou și din nou.
Am aflat mai întâi acest sfat de la Dan Mayoh la Fintega. Mulțumim Access Analytics pentru sugerarea acestei caracteristici.
Urmăriți videoclipul
Transcriere video
Aflați Excel din podcast, episodul 2048 -: INDEX va înlocui un OFFSET volatil!
Hei, podcastez toate sfaturile mele din această carte, dă clic pe „i” din colțul din dreapta sus pentru a ajunge la lista de redare!
Bine, OFFSET este o funcție uimitoare! OFFSET ne permite să specificăm o celulă din colțul din stânga sus, apoi să folosim variabile pentru a defini de acolo câte rânduri în jos, câte rânduri peste și apoi să definim o formă, bine. Deci, aici, dacă vreau să adun sau să fac o medie de, să spunem 3/4, această formulă chiar aici va arunca o privire la formulă. OFFSET spune că începem de la B2, începând de la Q1, coborâm 0, peste 0, forma va avea 1 rând înălțime și va fi H1, cu alte cuvinte lățime de 3 celule, bine. Deci, în acest caz, tot ceea ce facem cu adevărat este să schimbăm câte celule adăugăm, începem mereu înapoi în B2, sau B3 sau B4, pe măsură ce copiez, și apoi decide câte celule sunt lățime. Bine, OFFSET este un lucru grozav, face tot felul de funcții uimitoare, dar iată-l bătăi de cap, este volatil!Ceea ce înseamnă că, chiar dacă ceva nu se află în lanțul de calcul, Excel, va lua timp să-l recalculeze, ceea ce va încetini lucrurile, bine.
Această versiune uimitoare a INDEX, corect, în mod normal, dacă aș cere INDEX-ul acestor 4 celule, 3, va returna numărul 1403. Cu toate acestea, atunci când am pus două puncte înainte sau după INDEX, se întâmplă ceva foarte diferit, vom arunca o privire la această formulă aici. Deci, indexul celor 4 celule, pe care o vreau, o vreau pe a 3-a, dar vedeți că există un: chiar acolo. Deci, vom merge întotdeauna de la B2: E2 și vă voi arăta dacă mergem la Formule, Evaluați formula, bine, așa că chiar aici va calcula numărul 3. Și aici, INDEX cu următorul: la el, în loc să ne spună 1403, care este modul în care ar calcula INDEX în mod normal, va întoarce $ D2, iar apoi MEDIA va face media celor 3. Absolut uimitor, modul în care funcționează acest lucru și beneficiul adăugat, nu este volatil, bine,și acest lucru poate fi folosit chiar pentru a înlocui un OFFSET incredibil de complex.
Deci, aici, cu acest OFFSET, ne bazăm pe aceste valori. Dacă aleg Q2 și Central, bine, aceste formule folosesc MATCH și COUNTIF pentru a afla câte rânduri în jos, câte coloane peste, cât de înalt, cât de lat. Și apoi OFFSET-ul de aici folosește toate aceste valori pentru a afla mediana. Vom face doar un test pentru a ne asigura că funcționează, deci = MEDIAN din acea gamă albastră de acolo, mai bine să fie 71, bine, și vom alege altceva aici, Q3 și West, deci. Apăsați F2, voi trage acest lucru și îl voi redimensiona pentru a rescrie formula respectivă, apăsați Enter și funcționează cu OFFSET.
Ei bine, aici, folosind un INDEX, am de fapt un punct în mijloc cu un INDEX în partea stângă și un INDEX în partea dreaptă, urmăriți acest lucru calculat în Formula de evaluare. Deci începe, va evalua, va evalua și chiar aici că INDEX este pe cale să-l transforme într-o adresă de celulă. Deci H16 este primul, vestul, Q3, și peste partea dreaptă va evalua, va cupla mai multe, iar apoi dreapta ei se schimbă în I20. Deci, cool, cool non-volatil pentru a înlocui un OFFSET utilizând funcția INDEX. În regulă, acest sfat și multe altele din această carte, dați clic pe „i” din colțul din dreapta sus pentru a cumpăra cartea.
Recapitulează bine: OFFSET, funcție minunată, flexibilă, odată ce stăpânești, poți face tot felul de lucruri. Indicați spre o celulă variabilă din stânga sus, indicați spre un interval care are o formă variabilă, dar este volatil, și astfel se calculează la fiecare calcul. Excel face de obicei un calcul inteligent sau recalculează celulele care trebuie calculate, dar cu OFFSET se va calcula întotdeauna. În loc de OFFSET puteți utiliza INDEX: sau: INDEX sau chiar INDEX: INDEX, oricând INDEX are două puncte lângă el, acesta va returna o adresă de celulă în loc de valoarea acelei celule. Iar beneficiul este că INDEX nu este volatil!
OK, vreau să vă mulțumesc că ați trecut pe aici, ne vedem data viitoare pentru un alt netcast de la!
Descărcare fișier
Descărcați exemplarul de fișier aici: Podcast2048.xlsm
