Víme, že funkce COUNTIFS v aplikaci Excel může počítat s více kritérii. Bere argumenty jako několik rozsahů kritérií a kritérií. Kritéria můžeme dynamicky měnit zadáním odkazu na buňku, ale sloupec kritérií nemůžeme dynamicky měnit. Tedy ne přímo, ale můžeme. To je to, co se předem učíme, vzorce Excel. Dělat věci v excelu, které nelze dělat normálně. Uvidíme jak.
Učme se příkladem.
Kontext
Zde jsem připravil údaje o tržbách provedených v různých měsících roku našimi prodejními poradci. Prodávají různé modely našeho produktu, obecně pojmenované model1, model2 atd. Co musíme udělat, je dynamicky počítat prodeje různých modelů v různých měsících.
V buňce I2 vybereme měsíc. V buňce I2 vybereme model. Tyto hodnoty se mohou změnit. A počet by se měl také změnit. Funkce COUNTIFS by měla hledat sloupec měsíce, který bude rozsahem kritérií. Pak bude ve sloupci měsíců hledat model.
Zde jsou tedy kritéria a rozsah_kritérií proměnné. Jak tedy vytvoříme proměnnou sloupců v COUNTIFS? Tady je jak?
Použití pojmenovaného rozsahu pro proměnný sloupec nebo rozsah kritérií
Obecný vzorec
= COUNTIFS (NEPŘÍMÝ (pojmenovaný_rozsah), kritéria)
Nejprve pojmenujte každý sloupec podle jeho nadpisu. Vyberte tabulku a stiskněte CTRL+SHIFT+F3 a pojmenujte sloupce podle horního řádku. Přečtěte si o tom zde.
Takže rozsah B3: B11, C3: C11, D3: D11 a E3: E11 jsou pojmenovány Jan, Feb, Mar a Apr.
Napište tento vzorec do I4.
= COUNTIFS (NEPŘÍMÝ (I2), I3)
Nyní, když změníte měsíc v I4, počet příslušných měsíců Model4 se zobrazí v I4.
Jak to funguje?
Vzorec je jednoduchý. Začněme zevnitř.
INDIRECT (I2): Jak víme, funkce INDIRECT převádí textový odkaz na skutečný odkaz. Poskytli jsme to I2. I2 obsahuje duben. Protože máme rozsah E3: E11 název Apr, INDIRECT (I2) překládá na E3: E11.
Vzorec zjednodušen na = COUNTIFS (E3: E11, I3). COUNTIFS počítá vše, co je v I3 v rozsahu E3: E11.
Když změníte měsíc, sloupec se bude dynamicky měnit. Toto se nazývá COUNTIFS s proměnnými sloupci. V gifu jsem použil nějaké podmíněné formátování založené na jiné buňce.
Vzorec může také fungovat s funkcí countif. Pokud však chcete mít více podmínek, použijte funkci COUNTIFS.
Sloupcový graf kreativ, který zahrnuje součty
Vytvořte překryvný graf v aplikaci Excel 2016
Proveďte Paretův graf a analýzu v aplikaci Excel
Proveďte vodopádový graf v aplikaci Excel
Sparklines aplikace Excel: Drobné grafy v buňce
Graf rychloměru (měřidla) v aplikaci Excel 2016