Vieme, že funkcia COUNTIFS v programe Excel môže počítať s viacerými kritériami. Argumenty vyžadujú niekoľko rozsahov kritérií a kritérií. Kritériá môžeme dynamicky meniť zadaním odkazu na bunku, ale stĺpček kritérií nemôžeme dynamicky zmeniť. Nie priamo, ale môžeme. To je to, čo sa vopred učíme, vzorce Excel. Robiť veci v Exceli, ktoré sa nedajú robiť normálne. Pozrime sa ako.
Učme sa príkladom.
Kontext
Tu som pripravil údaje o tržbách vykonaných našimi predajnými poradcami v rôznych mesiacoch roka. Predávajú rôzne modely nášho produktu, genericky pomenované model1, model2 atď. Čo musíme urobiť, je dynamicky počítať predaj rôznych modelov v rôznych mesiacoch.
V bunke I2 vyberieme mesiac. V bunke I2 vyberieme model. Tieto hodnoty sa môžu zmeniť. A počet by sa mal tiež zmeniť. Funkcia COUNTIFS by mala hľadať stĺpec mesiaca, v ktorom bude rozsah kritérií. Potom bude v danom stĺpci mesiacov hľadať model.
Takže tu sú kritériá aj rozsah_kritérií rôzne. Ako teda urobíme stĺpcovú premennú v COUNTIFS? Ako?
Použitie pomenovaného rozsahu pre rozsah premenných stĺpcov alebo kritérií
Generický vzorec
= COUNTIFS (NEPRIAME (pomenovaný_rozsah), kritériá)
Každý stĺpček najskôr pomenujte podľa nadpisov. Ak to chcete urobiť, vyberte tabuľku a stlačte kombináciu klávesov CTRL+SHIFT+F3 a pomenujte stĺpce podľa horného riadka. Prečítajte si o tom tu.
Rozsah B3: B11, C3: C11, D3: D11 a E3: E11 sú teda pomenované Jan, Feb, Mar a Apríl.
Napíšte tento vzorec do I4.
= COUNTIFS (NEPRIAMY (I2), I3)
Ak teraz zmeníte mesiac na I4, príslušný počet mesiacov Model4 sa zobrazí na I4.
Ako to funguje?
Vzorec je jednoduchý. Začnime zvnútra.
INDIRECT (I2): Ako vieme, funkcia INDIRECT prevádza textový odkaz na skutočnú referenciu. Poskytli sme to I2. I2 obsahuje apr. Keďže máme rozsah E3: názov E11 apríl, NEPRIAMY (I2) sa prekladá na E3: E11.
Vzorec sa zjednodušil na = COUNTIFS (E3: E11, I3). COUNTIFS počíta, čo je v I3 v rozsahu E3: E11.
Keď zmeníte mesiac, stĺpec sa bude dynamicky meniť. Hovorí sa tomu COUNTIFS s premennými stĺpcami. V gife som použil určité podmienené formátovanie na základe inej bunky.
Vzorec môže fungovať aj s funkciou countif. Ak však chcete mať viac podmienok, použite funkciu COUNTIFS.
Kreatívna stĺpcová tabuľka, ktorá obsahuje súčty
Vytvorte prekryvný graf v programe Excel 2016
Vykonajte Paretovu graf a analýzu v programe Excel
Vykonajte vodopádový graf v programe Excel
Iskry v Exceli: Drobné grafy v bunke
Graf rýchlomera (rozchod) v programe Excel 2016