Descărcați exemplul de registru de lucru
Am discutat în alte articole despre modul în care există funcții precum OFFSET și INDIRECT care sunt volatile. Dacă începeți să utilizați multe dintre acestea într-o foaie de calcul sau aveți multe celule dependente de funcția volatilă, puteți determina computerul să petreacă un timp vizibil făcând recalculări de fiecare dată când încercați să schimbați o celulă. În loc să devină frustrat de modul în care computerul dvs. nu este suficient de rapid, acest articol va explora modalități alternative de a rezolva situațiile obișnuite pe care oamenii le folosesc OFFSET și INDIRECT.
Înlocuind OFFSET pentru a crea o listă dinamică
După ce ați aflat despre funcția OFFSET, este o concepție greșită obișnuită că este singura modalitate de a returna un rezultat cu dimensiune dinamică folosind ultimele două argumente. Să vedem o listă din coloana A în care utilizatorul nostru ar putea decide ulterior să adauge articole suplimentare.
Pentru a face o listă derulantă în celula C2, puteți defini un domeniu numit cu o formulă volatilă, cum ar fi
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
Odată cu configurarea actuală, acest lucru ar returna cu siguranță o referință la gama A2: A5. Cu toate acestea, există un alt mod de a folosi INDEX-ul volatil. Pentru a face acest lucru, gândiți-vă că scriem o referință la intervalul de la A2 la A5. Când scrieți „A2: A5”, nu vă gândiți la aceasta ca la o singură bucată de date, ci mai degrabă ca „Punct de pornire” și „Punct final” separat de două puncte (de exemplu, Punct de pornire: Punct final). Într-o formulă, atât StartPoint cât și EndingPoint pot fi rezultatele altor funcții.
Iată formula pe care o vom folosi pentru a crea un interval dinamic folosind funcția INDEX:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))
Rețineți că am declarat că Punctul de pornire pentru acest interval va fi întotdeauna A2. Pe cealaltă parte a colonului, folosim INDEX pentru a determina unde va fi punctul final. COUNTA va determina că există 5 celule cu date în coloana A, astfel încât INDEX-ul nostru va crea o referință la A5. Formula este astfel evaluată astfel:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
Folosind această tehnică, puteți construi dinamic o referință la orice listă sau chiar un tabel bidimensional utilizând funcția INDEX. Într-o foaie de calcul cu o mulțime de funcții OFFSET, înlocuirea OFFSET-urilor cu INDEX va permite computerului să înceapă să ruleze mult mai repede.
Înlocuirea INDIRECT pentru numele foilor
Funcția INDIRECT este adesea apelată atunci când registrele de lucru au fost proiectate cu date împrăștiate pe mai multe foi de lucru. Dacă nu puteți obține toate datele pe o singură foaie, dar nu doriți să utilizați o funcție volatilă, este posibil să puteți utiliza ALEGE.
Luați în considerare următorul aspect, în care avem date despre vânzări în 3 foi de lucru diferite. În foaia de rezumat, am selectat din ce trimestru dorim să vedem datele.
Formula noastră în B3 este:
= ALEGE (MATCH (B2, D2: D4, 0), toamnă! A2, iarnă! A2, primăvară! A2)
În această formulă, funcția MATCH va determina ce zonă dorim să returnăm. Aceasta spune apoi funcției CHOOSE care dintre următoarele intervale să revină ca rezultat.
De asemenea, puteți utiliza funcția CHOOSE pentru a reveni la o gamă mai mare. În acest exemplu, avem un tabel cu date privind vânzările pentru fiecare dintre cele trei foi de lucru.
În loc să scrieți o funcție INDIRECTĂ pentru a construi numele foii, puteți lăsa CHOOSE să stabilească pe ce tabel să efectuați căutarea. În exemplul meu, am numit deja cele trei tabele tbFall, tbWinter și tbSpring. Formula din B4 este:
= VLOOKUP (B3, ALEGE (MATCH (B2, D2: D4, 0), tbTon, tbWinter, tbSpring), 2, 0)
În această formulă, MATCH-ul va determina că dorim 2nd element din lista noastră. CHOOSE va lua apoi acel 2 și va returna referința la tbWinter. În cele din urmă, VLOOKUP-ul nostru va putea finaliza căutarea în tabelul dat și va constata că vânzările totale pentru Banana în timpul iernii au fost de 6000 $.
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, CHOOSE (2, tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, tbWinter, 2, 0) = 6000
Această tehnică este limitată de faptul că trebuie să completați funcția ALEGE cu toate zonele din care ați putea dori să obțineți o valoare, dar vă oferă avantajul de a evita o formulă volatilă. În funcție de câte calcule trebuie să efectuați, această abilitate s-ar putea dovedi a fi destul de valoroasă.
