Stiahnite si ukážkový zošit
V iných článkoch sme diskutovali o tom, ako existujú funkcie ako OFFSET a INDIRECT, ktoré sú volatilné. Ak začnete používať veľa z nich v tabuľke alebo máte veľa buniek závislých od volatilnej funkcie, môžete spôsobiť, že váš počítač strávi znateľný čas prepočítavaním pri každom pokuse o zmenu bunky. Tento článok namiesto frustrácie z toho, že váš počítač nie je dostatočne rýchly, sa zameria na alternatívne spôsoby riešenia bežných situácií, ktoré ľudia používajú OFFSET a INDIRECT.
Výmena OFFSET za vytvorenie dynamického zoznamu
Po oboznámení sa s funkciou OFFSET je bežnou mylnou predstavou, že je to jediný spôsob, ako vrátiť výsledok s dynamickou veľkosťou pomocou posledných dvoch argumentov. Pozrime sa na zoznam v stĺpci A, kde sa náš používateľ môže neskôr rozhodnúť pridať ďalšie položky.
Ak chcete vytvoriť rozbaľovací zoznam v bunke C2, môžete definovať pomenovaný rozsah pomocou prchavého vzorca
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
Pri súčasnom nastavení by to určite vrátilo odkaz na rozsah A2: A5. Existuje však ďalší spôsob, ako využiť energeticky nezávislý INDEX. Za týmto účelom premýšľajte o tom, že napíšeme odkaz na rozsah od A2 do A5. Keď píšete „A2: A5“, nemyslite na to ako na jeden údaj, ale skôr na „počiatočný bod“ a „koncový bod“ oddelené dvojbodkou (napr. StartingPoint: EndingPoint). Vo vzorci môžu byť StartingPoint aj EndingPoint výsledkami iných funkcií.
Tu je vzorec, ktorý použijeme na vytvorenie dynamického rozsahu pomocou funkcie INDEX:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))
Upozorňujeme, že sme uviedli, že počiatočný bod pre tento rozsah bude vždy A2. Na druhej strane dvojbodky používame INDEX na určenie, kde bude EndingPoint. COUNTA určí, že v stĺpci A je 5 buniek s údajmi, a preto náš INDEX vytvorí odkaz na A5. Vzorec sa teda vyhodnotí takto:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
Pomocou tejto techniky môžete pomocou funkcie INDEX dynamicky vytvoriť odkaz na ľubovoľný zoznam alebo dokonca na dvojrozmernú tabuľku. V tabuľke s množstvom funkcií OFFSET vám nahradenie OFFSETOV za INDEX umožní, aby váš počítač začal pracovať oveľa rýchlejšie.
Nahrádza sa INDIRECT pre názvy hárkov
Funkcia NEPRIAMY sa často volá, keď sú zošity navrhnuté s údajmi rozptýlenými do viacerých pracovných hárkov. Ak nemôžete získať všetky údaje na jeden list, ale nechcete používať volatilnú funkciu, možno budete môcť použiť ZVOLIŤ.
Zvážte nasledujúce rozloženie, kde máme údaje o predaji z 3 rôznych pracovných hárkov. V našom súhrnnom hárku sme vybrali, z ktorého štvrťroka chceme zobrazovať údaje.
Náš vzorec v B3 je:
= VYBERTE (ZÁPAS (B2, D2: D4, 0), jeseň! A2, zima! A2, jar! A2)
V tomto vzorci funkcia MATCH určí, ktorú oblasť chceme vrátiť. Potom to povie funkcii CHOOSE, ktorý z nasledujúcich rozsahov sa má ako výsledok vrátiť.
Na vrátenie väčšieho rozsahu môžete použiť aj funkciu CHOOSE. V tomto prípade máme tabuľku údajov o predajoch pre každý z našich troch pracovných hárkov.
Namiesto písania INDIRECT funkcie na zostavenie názvu hárka môžete nechať CHOOSE určiť, v ktorej tabuľke sa má vykonať vyhľadávanie. V mojom prípade som už pomenoval tri tabuľky tbFall, tbWinter a tbSpring. Vzorec v B4 je:
= VLOOKUP (B3, VYBERTE (ZÁPAS (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
V tomto vzorci MATCH určí, že chceme 2nd položka z nášho zoznamu. CHOOSE potom vezme 2 a vráti odkaz na tbWinter. Nakoniec náš VLOOKUP bude schopný dokončiť vyhľadávanie v danej tabuľke a zistí, že celkové tržby za banán v zime boli 6 000 dolárov.
= 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
Táto technika je obmedzená skutočnosťou, že funkciu CHOOSE musíte vyplniť všetkými oblasťami, z ktorých by ste chceli načítať hodnotu, ale prináša vám výhodu, že sa vyhnete prchavému vzorcu. V závislosti od toho, koľko výpočtov musíte dokončiť, sa táto schopnosť môže ukázať ako veľmi cenná.
