Atsisiųskite darbaknygės pavyzdį
Kituose straipsniuose aptarėme, kaip yra tokių funkcijų kaip OFFSET ir INDIRECT, kurios yra nepastovios. Jei daugelį iš jų pradedate naudoti skaičiuoklėje arba turite daug langelių, priklausančių nuo nepastovios funkcijos, kiekvieną kartą, kai bandote pakeisti langelį, kompiuteris gali praleisti daug laiko perskaičiuodamas. Užuot nusivylęs tuo, kad jūsų kompiuteris nėra pakankamai greitas, šiame straipsnyje bus nagrinėjami alternatyvūs būdai, kaip išspręsti įprastas situacijas, kurias žmonės naudoja OFFSET ir INDIRECT.
Pakeitus OFFSET, sukuriamas dinaminis sąrašas
Sužinojus apie OFFSET funkciją, dažnai klaidinga nuomonė, kad tai yra vienintelis būdas grąžinti dinaminio dydžio rezultatą naudojant paskutinius porą argumentų. Pažvelkime į A stulpelio sąrašą, kuriame mūsų vartotojas vėliau gali nuspręsti pridėti papildomų elementų.
Norėdami atlikti išskleidžiamąjį langelį C2, galite apibrėžti pavadintą diapazoną naudodami nepastovią formulę, pvz
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
Esant dabartinei sąrankai, tai tikrai grąžintų nuorodą į diapazoną A2: A5. Tačiau yra dar vienas būdas naudoti nepastovųjį INDEX. Norėdami tai padaryti, pagalvokite, kad parašysime nuorodą į diapazoną nuo A2 iki A5. Rašydami „A2: A5“, negalvokite apie tai kaip apie vieną duomenų dalį, o apie „StartingPoint“ ir „EndingPoint“, atskirtus dvitaškiu (pvz., „StartingPoint: EndingPoint“). Formulėje tiek „StartingPoint“, tiek „EndingPoint“ gali būti kitų funkcijų rezultatai.
Štai formulė, kurią naudosime kurdami dinaminį diapazoną naudodami funkciją INDEX:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))
Atminkite, kad nurodėme, kad šio diapazono pradžios taškas visada bus A2. Kitoje storosios žarnos pusėje mes naudojame INDEX, kad nustatytume, kur turi būti pabaigos taškas. COUNTA nustatys, kad A stulpelyje yra 5 langeliai su duomenimis, todėl mūsų INDEX sukurs nuorodą į A5. Taigi formulė vertinama taip:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
Naudodami šią techniką, galite dinamiškai sukurti nuorodą į bet kurį sąrašą ar net dvimatę lentelę, naudodami funkciją INDEX. Skaičiuoklėje, kurioje gausu OFFSET funkcijų, pakeitus OFFSET į INDEX, kompiuteris pradės veikti daug greičiau.
Lapo pavadinimų INDIRECT pakeitimas
Funkcija INDIRECT dažnai iškviečiama, kai darbaknygės buvo sukurtos naudojant duomenis, išsklaidytus keliuose darbalapiuose. Jei negalite surinkti visų duomenų į vieną lapą, bet nenorite naudoti nepastovios funkcijos, galbūt galėsite naudoti CHOOSE.
Apsvarstykite šį išdėstymą, kuriame turime pardavimo duomenų iš 3 skirtingų darbalapių. Suvestinės lape pasirinkome, iš kurio ketvirčio norime peržiūrėti duomenis.
Mūsų formulė B3 yra:
= PASIRINKITE (MATCH (B2, D2: D4, 0), Fall! A2, Winter! A2, Spring! A2)
Šioje formulėje funkcija MATCH nustatys, kurią sritį norime grąžinti. Tada funkcijai PASIRINKTI nurodoma, kurį iš šių diapazonų grąžinti kaip rezultatą.
Taip pat galite naudoti funkciją CHOOSE, kad grąžintumėte didesnį diapazoną. Šiame pavyzdyje mes turime kiekvieno iš trijų darbalapių pardavimo duomenų lentelę.
Užuot rašę netiesioginę funkciją, kad sukurtumėte lapo pavadinimą, galite leisti CHOOSE nustatyti, kurioje lentelėje atlikti paiešką. Savo pavyzdyje aš jau pavadinau tris lenteles tbFall, tbWinter ir tbSpring. B4 formulė yra tokia:
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
Šioje formulėje MATCH nustatys, kad norime 2antra elementas iš mūsų sąrašo. Tada CHOOSE paims tą 2 ir grąžins nuorodą į tbWinter. Galiausiai, mūsų VLOOKUP galės užbaigti paiešką pateiktoje lentelėje ir pamatys, kad bendras bananų pardavimas žiemą sudarė 6000 USD.
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, CHOOSE (2, tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, tbŽiema, 2, 0) = 6000
Šią techniką riboja tai, kad turite užpildyti funkciją PASIRINKTI visas sritis, iš kurių galbūt norėsite gauti vertę, tačiau tai leidžia jums išvengti nestabilios formulės. Atsižvelgiant į tai, kiek skaičiavimų reikia atlikti, šis gebėjimas gali pasirodyti gana vertingas.
