Töltse le a példa munkafüzetet
Más cikkekben tárgyaltunk arról, hogy vannak olyan funkciók, mint az OFFSET és az INDIRECT, amelyek ingadoznak. Ha ezek közül sokat kezd el használni egy táblázatban, vagy sok cella függ az illékony függvényektől, akkor a számítógép észrevehető időt tölthet újraszámításokkal minden egyes cellacsere során. Ahelyett, hogy elkeseredne amiatt, hogy számítógépe nem elég gyors, ez a cikk alternatív módszereket fog feltárni az emberek által az OFFSET és INDIRECT által használt gyakori helyzetek megoldására.
Az OFFSET lecserélése dinamikus lista létrehozásához
Miután megtanulta az OFFSET függvényt, gyakori tévhit, hogy ez az egyetlen módja annak, hogy az utolsó pár argumentum segítségével dinamikus méretű eredményt adjon vissza. Nézzünk egy listát az A oszlopban, ahol felhasználónk később úgy dönthet, hogy további elemeket ad hozzá.
A C2 cella legördülő listájának létrehozásához definiálhat egy Named Range -t egy illékony képlettel, például
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
A jelenlegi beállításoknál ez minden bizonnyal visszatér az A2: A5 tartományra. Van azonban egy másik módja is a nem felejtő INDEX-nek. Ehhez gondoljon arra, hogy írunk egy hivatkozást az A2 és A5 közötti tartományra. Amikor „A2: A5” -t ír, ne gondolja ezt egyetlen adatként, hanem inkább „StartingPoint” és „EndingPoint” pontként, kettősponttal elválasztva (pl. StartingPoint: EndingPoint). Egy képletben mind a StartingPoint, mind a EndingPoint más funkciók eredménye lehet.
Íme a képlet, amellyel dinamikus tartományt hozunk létre az INDEX funkció használatával:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))
Ne feledje, hogy kijelentettük, hogy ennek a tartománynak a kiindulópontja mindig A2 lesz. A vastagbél másik oldalán az INDEX segítségével határozzuk meg, hogy hol legyen a végpont. A COUNTA megállapítja, hogy 5 cella van adatokkal az A oszlopban, és így az INDEX hivatkozást hoz létre az A5 -re. A képletet így értékelik:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
Ezzel a technikával dinamikusan létrehozhat hivatkozást bármely listára, vagy akár egy kétdimenziós táblázatra az INDEX funkció használatával. Az OFFSET funkciók sokaságát tartalmazó táblázatban az OFFSET -ek INDEX -re való cseréje lehetővé teszi, hogy a számítógép sokkal gyorsabban induljon el.
A KÖZVETLEN lecserélése a lapnevekre
Az INDIRECT funkciót gyakran hívják meg, ha a munkafüzeteket több munkalapra szétszórt adatokkal tervezték. Ha nem tudja az összes adatot egyetlen lapra hozni, de nem szeretne illékony függvényt használni, akkor használhatja a VÁLASZTÁS lehetőséget.
Tekintsük az alábbi elrendezést, ahol értékesítési adatok állnak rendelkezésre 3 különböző munkalapon. Az Összefoglaló lapon kiválasztottuk, hogy melyik negyedévből szeretnénk megtekinteni az adatokat.
A B3 képletünk a következő:
= VÁLASZT (MATCH (B2, D2: D4, 0), ősz! A2, tél! A2, tavasz! A2)
Ebben a képletben a MATCH függvény határozza meg, hogy melyik területet szeretnénk visszaadni. Ez megmondja a VÁLASZT függvénynek, hogy az alábbi tartományok közül melyiket adja vissza eredményként.
A CHOOSE funkcióval nagyobb tartományt is visszaadhat. Ebben a példában mindhárom munkalapunkra vonatkozóan értékesítési adatokat tartalmazó táblázatot kaptunk.
Ahelyett, hogy egy INDIRECT függvényt írna a lap nevének felépítéséhez, hagyhatja, hogy a CHOOSE határozza meg, melyik táblán végezze a keresést. Példámban a három táblát már elneveztem tbFall, tbWinter és tbSpring. A B4 képlet a következő:
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
Ebben a képletben a MATCH meghatározza, hogy a 2 -et akarjuk -end elem a listánkból. A CHOOSE ezt a 2 -t veszi fel, és visszaadja a hivatkozást a tbWinter -nek. Végül a VLOOKUP képes lesz befejezni a keresést az adott táblázatban, és megállapítja, hogy a banán teljes értékesítése télen 6000 dollár volt.
= VLOOKUP (B3, CHOOSE (Match (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, CHOOSE (2, tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, tbTél, 2, 0) = 6000
Ezt a technikát korlátozza az a tény, hogy ki kell töltenie a VÁLASZTÁS funkciót minden olyan területtel, ahonnan értéket szeretne lekérni, de előnye, hogy elkerülheti az illékony képletet. Attól függően, hogy hány számítást kell elvégeznie, ez a képesség igen értékesnek bizonyulhat.
