Stáhněte si ukázkový sešit
V jiných článcích jsme diskutovali o tom, jak existují funkce jako OFFSET a INDIRECT, které jsou volatilní. Pokud začnete používat mnoho z nich v tabulce nebo máte mnoho buněk závislých na nestálé funkci, můžete způsobit, že váš počítač stráví znatelný čas přepočty pokaždé, když se pokusíte změnit buňku. Tento článek namísto toho, aby byl frustrovaný z toho, že váš počítač není dostatečně rychlý, prozkoumá alternativní způsoby řešení běžných situací, kdy lidé používají OFFSET a INDIRECT.
Nahrazením OFFSETu vytvoříte dynamický seznam
Poté, co jsme se dozvěděli o funkci OFFSET, je běžnou mylnou představou, že je to jediný způsob, jak vrátit výsledek s dynamickou velikostí pomocí posledních pár argumentů. Podívejme se na seznam ve sloupci A, kde by se náš uživatel mohl později rozhodnout přidat další položky.
Chcete -li vytvořit rozevírací seznam v buňce C2, můžete definovat pojmenovaný rozsah pomocí nestálého vzorce
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
Při současném nastavení by to určitě vrátilo odkaz na rozsah A2: A5. Existuje však ještě jiný způsob, jak použít energeticky nezávislý INDEX. Chcete -li to provést, přemýšlejte o tom, že napíšeme odkaz na rozsah od A2 do A5. Když píšete „A2: A5“, nepokládejte to za jeden kus dat, ale spíše za „StartingPoint“ a „EndingPoint“ oddělené dvojtečkou (např. StartingPoint: EndingPoint). Ve vzorci mohou být StartingPoint i EndingPoint výsledky jiných funkcí.
Zde je vzorec, který použijeme k vytvoření dynamického rozsahu pomocí funkce INDEX:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))
Všimli jsme si, že jsme uvedli, že výchozí bod pro tento rozsah bude vždy A2. Na druhé straně dvojtečky používáme INDEX k určení, kde bude EndingPoint. COUNTA určí, že ve sloupci A je 5 buněk s údaji, a proto náš INDEX vytvoří odkaz na A5. Vzorec se tedy vyhodnotí takto:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
Pomocí této techniky můžete pomocí funkce INDEX dynamicky vytvořit odkaz na libovolný seznam nebo dokonce na dvourozměrnou tabulku. V tabulce s množstvím funkcí OFFSET vám nahrazení OFFSETů INDEX umožní začít pracovat mnohem rychleji.
Nahrazování NEPŘÍMÝCH názvů listů
Funkce NEPŘÍMÉ se často zavolá, když byly sešity navrženy s daty rozptýlenými do více listů. Pokud nemůžete dostat všechna data na jeden list, ale nechcete používat volatilní funkci, můžete použít CHOOSE.
Zvažte následující rozložení, kde máme údaje o prodeji ze 3 různých listů. V našem souhrnném listu jsme vybrali, ze kterého čtvrtletí chceme data zobrazit.
Náš vzorec v B3 je:
= VYBERTE (ZÁPAS (B2, D2: D4, 0), podzim! A2, zima! A2, jaro! A2)
V tomto vzorci funkce MATCH určí, kterou oblast chceme vrátit. To pak sděluje funkci CHOOSE, který z následujících rozsahů se má jako výsledek vrátit.
Můžete také použít funkci CHOOSE k vrácení většího rozsahu. V tomto příkladu máme tabulku údajů o prodeji na každém ze našich tří pracovních listů.
Spíše než psát NEPŘÍMOU funkci pro sestavení názvu listu, můžete nechat CHOOSE určit, ve které tabulce se má hledat. V mém příkladu jsem již pojmenoval tři tabulky tbFall, tbWinter a tbSpring. Vzorec v B4 je:
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
V tomto vzorci MATCH určí, že chceme 2nd položka z našeho seznamu. CHOOSE pak vezme 2 a vrátí odkaz na tbWinter. Nakonec naše VLOOKUP bude moci dokončit vyhledávání v dané tabulce a zjistí, že celkové tržby za banán v zimě činily 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
Tato technika je omezena skutečností, že musíte vyplnit funkci CHOOSE se všemi oblastmi, ze kterých byste mohli chtít načíst hodnotu, ale přináší vám výhodu v tom, že se vyhnete těkavému vzorci. V závislosti na tom, kolik výpočtů musíte dokončit, se tato schopnost může ukázat jako velmi cenná.
