Už jsme se tedy dozvěděli, co je 3D reference v Excelu. Zábavným faktem je, že normální odkazy na 3D Excel nefungují s podmíněnými funkcemi, jako je funkce SUMIF. V tomto článku se naučíme, jak získat 3D referenční práci s funkcí SUMIF.
Obecný vzorec pro SUMIF s 3D referencí v aplikaci Excel
Vypadá to složitě, ale není (tolik).
| = SUMPRODUCT (SUMIF (NEPŘÍMÝ ("" "& název_ranže_of_sheet_names &" '! "&" Rozsah_kritérií "), kritéria, NEPŘÍMÝ (" "" & název_rozsahu_název_listu & "'!" & "Součet_rozsahu"))) |
"'" název_rozsahu_název_listu "" ":Jedná se o pojmenovaný rozsah, který obsahuje názvy listů. Tohle je velmi důležité.
"rozsah_kritérií":Jedná se o textový odkaz na kritéria obsahující rozsah. (Mělo by být stejné ve všech listech pro 3D referenční práci.)
kritéria:Je to prostě podmínka, kterou chcete dát pro sčítání. Může to být odkaz na text nebo buňku.
"sum_range":Jedná se o textový odkaz rozsahu součtů. (Mělo by být stejné ve všech listech pro 3D referenční práci.)
Dost teorie, pojďme pracovat 3D odkazy s funkcí SUMIF.
Příklad: Součet podle oblasti z více listů pomocí 3D odkazu na Excel:
Bereme stejná data, která jsme použili v jednoduchém příkladu 3D odkazování. V tomto příkladu mám pět různých listů, které obsahují podobná data. Každý list obsahuje údaje za měsíc. V hlavním listu chci součet jednotek a sběr podle regionů ze všech listů. Udělejme to nejprve pro jednotky. Jednotky jsou ve všech listech v rozsahu D2: D14.
Pokud nyní používáte normální 3D odkazování s funkcí SUMIF,
= SUMIF (leden: duben! A2: A14, mistr! B4, leden: duben! D2: D14)
Vrátí #HODNOTU! chyba. Nemůžeme to tedy použít. Použijeme obecný vzorec uvedený výše.
Pomocí výše uvedeného obecného 3D referenčního vzorce SUMIF v Excelu napište tento vzorec do buňky C3:
| = SÚČET |
Tady měsíce je pojmenovaný rozsah, který obsahuje názvy listů. To je zásadní.
Když stisknete Enter, získáte přesný výstup.
Jak to funguje?
Jádrem vzorce je NEPŘÍMÁ funkce a pojmenovaný rozsah. Tady řetězec"'" & Měsíce & "'!" & "A2: A14"překládá na řadu referencí rozsahu každého listu v souboru pojmenovaný rozsah.
| {"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"} |
Toto pole obsahuje textový odkazrozsahů, nikoli skutečných rozsahů. Vzhledem k tomu, že se jedná o textový odkaz, může jej funkce INDIRECT použít k jejich převodu na skutečné rozsahy. K tomu dochází u obou NEPŘÍMÝCH funkcí. Po vyřešení textů uvnitř NEPŘÍMÝCH funkcí (držte pevně) vypadá vzorec takto:
| = SÚČET , Pane! B3, NEPŘÍMÝ |
Nyní se aktivuje funkce SUMIF (nikoli NEPŘÍMÝ, jak jste asi uhodli). Podmínka je uzavřena do prvního rozsahu"'Jan'! A2: A14". Zde funkce INDIRECT funguje dynamicky a převádí tento text na skutečný rozsah (proto když se pokusíte vyřešit NEPŘÍMO nejprve pomocí klávesy F9, nedostanete výsledek). Další sečte odpovídající hodnoty v rozsahu"'Jan'! D2: D14".K tomu dochází pro každý rozsah v poli. Nakonec budeme mít pole vrácené funkcí SUMIF.
| = SUMPRODUCT ({97; 82; 63; 73}) |
Nyní SUMPRODUCT dělá to, co umí nejlépe. Tyto hodnoty jsou shrnuty a naše funkce 3D SUMIF funguje.
Takže jo, lidi, tímto můžete dosáhnout funkce 3D SUMIF. To je trochu složité, v tom souhlasím. V tomto 3D vzorci je velký prostor pro chyby. Navrhoval bych použít funkci SUMIF na každém listu v určité buňce a poté použít normální 3D odkazování pro shrnutí těchto hodnot.
Doufám, že jsem to vysvětlil dostatečně. Máte -li jakékoli pochybnosti týkající se odkazu na jakýkoli jiný dotaz související s Excelem/VBA, zeptejte se níže v sekci komentáře.
Relativní a absolutní reference v aplikaci Excel | Odkazování v Excelu je důležitým tématem pro každého začátečníka. Dokonce i zkušení uživatelé aplikace Excel dělají chyby při odkazování.
Reference dynamického listu | Poskytujte referenční listy dynamicky pomocí NEPŘÍMÉ funkce Excel. To je jednoduché…
Rozbalení odkazů v aplikaci Excel | Rozbalená reference se rozbalí při kopírování dolů nebo doprava. K tomu použijeme znak $ před číslem sloupce a řádku. Zde je jeden příklad…
Vše o absolutní referenci | Výchozí typ odkazu v aplikaci Excel je relativní, ale pokud chcete, aby odkaz na buňky a rozsahy byl absolutní, použijte znak $. Zde jsou všechny aspekty absolutního odkazování v aplikaci Excel.
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity | Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Funkce VLOOKUP v Excelu | Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která slouží k vyhledávání hodnot z různých rozsahů a listů.
COUNTIF v aplikaci Excel 2016 | Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétní hodnoty nepotřebujete filtrovat data. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel | Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.