Funkce OFFSET aplikace Excel zpomalí výpočet vašeho sešitu. Existuje lepší alternativa: neobvyklá syntaxe INDEXU.
Toto je speciální tip. Existuje neuvěřitelně flexibilní funkce zvaná OFFSET. Je flexibilní, protože může ukazovat na rozsah jiné velikosti, který se počítá za běhu. Na obrázku níže, pokud někdo změní rozbalovací nabídku # Qtrs v H1 ze 3 na 4, čtvrtý argument OFFSET zajistí, že se rozsah rozšíří tak, aby zahrnoval čtyři sloupce.
Guru tabulky nenávidí OFFSET, protože se jedná o volatilní funkci. Pokud přejdete do zcela nesouvisející buňky a zadáte číslo, vypočítají se všechny funkce OFFSET. I když tato buňka nemá nic společného s H1 nebo B2. Excel je většinou velmi opatrný, aby ztrácel čas pouze výpočtem buněk, které je třeba vypočítat. Ale jakmile zavedete OFFSET, všechny buňky OFFSET plus vše podřízené od OFFSETu se začnou počítat po každé změně v listu.
Posuzoval jsem finále ModelOff 2013 v New Yorku, když několik mých přátel z Austrálie poukázalo na bizarní řešení. Ve vzorci níže je před funkcí INDEX dvojtečka. Normálně by funkce INDEX zobrazená níže vrátila 1403 z buňky D2. Ale když vložíte dvojtečku na obě strany funkce INDEX, začne vracet adresu buňky D2 namísto obsahu D2. Je divoké, že to funguje.
Proč na tom záleží? INDEX není volatilní. Získáte veškerou flexibilní dobrotu OFFSET bez časově sajícího přepočtu znovu a znovu.
Tento tip jsem se poprvé naučil od Dana Mayoha ve Fintega. Děkujeme společnosti Access Analytic za navržení této funkce.
Sledovat video
Přepis videa
Naučte se Excel z podcastu, epizoda 2048 -: INDEX nahradí těkavý offset!
Ahoj, podcastuji všechny své tipy z této knihy, kliknutím na „i“ v pravém horním rohu se dostanete do seznamu skladeb!
Dobře, OFFSET je úžasná funkce! OFFSET nám umožňuje určit buňku v levém horním rohu a poté pomocí proměnných definovat, o kolik řádků dolů, o kolik řádků více, a poté definovat tvar, dobře. Takže tady, pokud chci sčítat, nebo udělat průměr, řekněme 3/4, tento vzorec se zde podívá na vzorec. OFFSET říká, že začínáme od B2, od Q1, jdeme dolů 0, přes 0, tvar bude vysoký 1 řádek a bude H1, jinými slovy 3 buňky široké, v pořádku. Takže v tomto případě vše, co opravdu děláme, je změna počtu buněk, které sčítáme, vždy začínáme zpět v B2 nebo B3 nebo B4, když kopíruji, a pak rozhodne, kolik buněk je široké. Dobře, OFFSET je to skvělá věc, dělá všechny druhy úžasných funkcí, ale tady je problém, je to volatilní!Což znamená, že i když něco není ve výpočtovém řetězci, Excel si to bude muset přepočítat, což všechno zpomalí, dobře.
Tato úžasná verze INDEXu, správně, normálně, kdybych požádal o INDEX těchto 4 buněk, 3, vrátí číslo 1403. Když však vložím dvojtečku před nebo po INDEXU, stane se něco velmi odlišného, zde se podíváme na tento vzorec. Takže index 4 buněk, který z nich chci, chci třetí, ale vidíte, že tam je: právě tam. Takže vždy přejdeme z B2: E2 a já vám ukážu, jestli půjdeme do Formulas, Evaluate Formula, fajn, takže právě tady to spočítá číslo 3. A tady, INDEX s: next místo toho, abychom nám řekli 1403, což by normálně vypočítal INDEX, vrátí to $ D2 a pak PRŮMĚR udělá průměr z těch 3. Absolutně úžasný způsob, jakým to funguje, a další výhoda, není to nestálé, dobře,a to lze dokonce použít k nahrazení neuvěřitelně složitého OFSETU.
Takže tady s tímto OFSETEM vycházíme z těchto hodnot. Pokud zvolím Q2 a Central, v pořádku, tyto vzorce používají MATCH a COUNTIF k tomu, aby zjistili, kolik řádků dolů, kolik sloupců přes, jak vysoký, jak široký. A pak zde OFSET používá všechny tyto hodnoty k určení mediánu. Uděláme jen test, abychom se ujistili, že funguje, takže = MEDIAN toho modrého rozsahu tam, lépe 71, dobře, a my si zde vybereme něco jiného, Q3 a West, takže. Stiskněte klávesu F2, přetáhnu to a změním jeho velikost, abych přepsal tento vzorec, stiskněte klávesu Enter a pracuje s OFSETEM.
Tady, pomocí INDEXU, mám ve skutečnosti dvojtečku uprostřed s INDEXem na levé straně a INDEXem na pravé straně, sledujte, jak se tato věc vypočítá v Hodnotit vzorec. Začíná to, vyhodnotí se, vyhodnotí se a právě tady se to INDEX chystá změnit na adresu buňky. Takže H16 je 1st, West, Q3, a na pravé straně bude vyhodnocovat, párovat více, a pak se jí to změní na I20. Takže cool, cool non-volatile nahradit OFFSET pomocí funkce INDEX. Dobře, tento tip a mnoho dalších v této knize, kliknutím na „i“ v pravém horním rohu knihu zakoupíte.
Dobře rekapitulace: OFFSET, úžasná, flexibilní funkce, jakmile zvládnete, můžete dělat všechno možné. Ukažte na proměnnou buňku vlevo nahoře, přejděte na oblast, která má proměnný tvar, ale je volatilní, a tak se počítají při každém výpočtu. Excel obvykle provede inteligentní výpočet nebo přepočítá buňky, které je třeba vypočítat, ale s OFFSET se vždy vypočítá. Místo OFSET můžete použít INDEX: nebo: INDEX nebo dokonce INDEX: INDEX, kdykoli má INDEX vedle sebe dvojtečku, vrátí adresu buňky namísto hodnoty této buňky. Výhodou je, že INDEX není volatilní!
Dobře, chci vám poděkovat, že jste se zastavili, uvidíme se příště pro další netcast od!
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2048.xlsm
