Lejupielādējiet darbgrāmatas piemēru
Mēs esam apsprieduši citos rakstos par to, kā pastāv tādas funkcijas kā OFFSET un INDIRECT, kuras ir nepastāvīgas. Ja sākat izmantot daudzas no tām izklājlapā vai ja daudzas šūnas ir atkarīgas no gaistošām funkcijām, jūs varat likt datoram ievērojami pavadīt laiku, veicot pārrēķinus katru reizi, kad mēģināt mainīt šūnu. Tā vietā, lai kļūtu neapmierināti ar to, ka jūsu dators nav pietiekami ātrs, šajā rakstā tiks aplūkoti alternatīvi veidi, kā atrisināt bieži sastopamās situācijas, ko cilvēki izmanto OFFSET un INDIRECT.
Nomainot OFFSET, lai izveidotu dinamisku sarakstu
Uzzinot par funkciju OFFSET, ir izplatīts nepareizs uzskats, ka tas ir vienīgais veids, kā atgriezt dinamiskā lieluma rezultātu, izmantojot pēdējos pāris argumentus. Apskatīsim sarakstu A slejā, kur mūsu lietotājs vēlāk varētu izlemt pievienot papildu vienumus.
Lai veiktu nolaižamo darbību šūnā C2, varat definēt nosaukto diapazonu ar nepastāvīgu formulu, piemēram
= NOBĪDE ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
Izmantojot pašreizējo iestatījumu, tas noteikti atgriezīs atsauci uz diapazonu A2: A5. Tomēr ir vēl viens veids, kā izmantot nepastāvīgo INDEX. Lai to izdarītu, padomājiet par to, ka mēs uzrakstām atsauci uz diapazonu no A2 līdz A5. Rakstot “A2: A5”, nedomājiet par to kā par vienu datu daļu, bet gan par “sākuma punktu” un “beigu punktu”, kas atdalīti ar kolu (piemēram, sākuma punkts: beigu punkts). Formulā gan sākuma punkts, gan beigu punkts var būt citu funkciju rezultāti.
Šeit ir formula, kuru mēs izmantosim, lai izveidotu dinamisko diapazonu, izmantojot funkciju INDEX:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))
Ņemiet vērā, ka esam norādījuši, ka šī diapazona sākuma punkts vienmēr būs A2. Resnās zarnas otrā pusē mēs izmantojam INDEX, lai noteiktu, kur atrodas beigu punkts. COUNTA noteiks, ka A slejā ir 5 šūnas ar datiem, un tāpēc mūsu INDEX izveidos atsauci uz A5. Tādējādi formula tiek novērtēta šādi:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
Izmantojot šo paņēmienu, jūs varat dinamiski izveidot atsauci uz jebkuru sarakstu vai pat divdimensiju tabulu, izmantojot funkciju INDEX. Izklājlapā, kurā ir daudz OFFSET funkciju, OFFSET aizstāšana ar INDEX ļaus datoram sākt darboties daudz ātrāk.
Lapu nosaukumu INDIRECT aizstāšana
Funkcija INDIRECT bieži tiek izsaukta, ja darbgrāmatas ir izstrādātas, izmantojot datus, kas izkliedēti vairākās darblapās. Ja nevarat iegūt visus datus vienā lapā, bet nevēlaties izmantot nepastāvīgu funkciju, iespējams, varēsit izmantot opciju CHOOSE.
Apsveriet šādu izkārtojumu, kur mums ir pārdošanas dati par 3 dažādām darblapām. Savā kopsavilkuma lapā mēs esam izvēlējušies, kurā ceturksnī mēs vēlētos skatīt datus.
Mūsu B3 formula ir šāda:
= IZVĒLIES (MATCH (B2, D2: D4, 0), Fall! A2, Winter! A2, Spring! A2)
Šajā formulā funkcija MATCH noteiks, kuru apgabalu mēs vēlamies atgriezt. Pēc tam funkcija CHOOSE norāda, kurš no šiem diapazoniem ir jāatgriež kā rezultāts.
Varat arī izmantot funkciju IZVĒLĒT, lai atgrieztu lielāku diapazonu. Šajā piemērā mums ir pārdošanas tabula par katru no mūsu trim darblapām.
Tā vietā, lai rakstītu NETIEŠU funkciju lapas nosaukuma veidošanai, varat ļaut CHOOSE noteikt, kurā tabulā veikt meklēšanu. Manā piemērā trīs tabulas jau esmu nosaucis par tbFall, tbWinter un tbSpring. B4 formula ir šāda:
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
Šajā formulā MATCH noteiks, ka mēs vēlamies 2nd vienumu no mūsu saraksta. CHOOSE ņems šo 2 un atgriezīs atsauci uz tbWinter. Visbeidzot, mūsu VLOOKUP varēs pabeigt meklēšanu dotajā tabulā, un tas atklās, ka kopējais banānu pārdošanas apjoms ziemā bija 6000 USD.
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, CHOOSE (2, tbFall, tbWinter, tbSpring), 2, 0) = MEKLĒŠANA (B3, tbZiema, 2, 0) = 6000
Šo paņēmienu ierobežo fakts, ka jums ir jāaizpilda funkcija IZVĒLĒTIES ar visām jomām, no kurām jūs, iespējams, vēlēsities iegūt vērtību, taču tas dod jums priekšrocības, izvairoties no nepastāvīgas formulas. Atkarībā no tā, cik aprēķinu jums jāveic, šī spēja var izrādīties diezgan vērtīga.
