Me teame, et Exceli funktsioon COUNTIFS võib loota mitmele kriteeriumile. See võtab argumente kui paari kriteeriumivahemikku ja kriteeriume. Saame kriteeriume dünaamiliselt muuta, andes lahtri viite, kuid me ei saa kriteeriumide veergu dünaamiliselt muuta. No mitte otse, aga saame. See on see, mida me õpime eelnevalt Exceli valemeid. Excelis asjade tegemine, mida normaalselt teha ei saa. Vaatame, kuidas.
Õpime näite varal.
Kontekst
Siin olen koostanud andmed meie müügikonsultantide aasta erinevatel kuudel tehtud müügi kohta. Nad müüvad meie toote erinevaid mudeleid, üldnimetusega mudel1, mudel2 ja nii edasi. Peame dünaamiliselt arvestama erinevate mudelite müüki erinevatel kuudel.
Lahtris I2 valime kuu. Lahtris I2 valime mudeli. Need väärtused võivad muutuda. Ja arv peaks ka muutuma. Funktsioon COUNTIFS peaks otsima kuu veergu, mis on kriteeriumivahemik. Seejärel otsib see selle kuu veerust mudelit.
Nii et siin on kriteeriumid ja kriteeriumivahemik mõlemad muutlikud. Niisiis, kuidas teha veerg muutujaks COUNTIFS? Siin on, kuidas?
Nimega vahemiku kasutamine muutuva veeru või kriteeriumivahemiku jaoks
Üldine valem
= COUNTIFS (INDIRECT (nime_vahemik), kriteeriumid)
Esiteks nimetage iga veerg nende pealkirjade järgi. Selleks valige tabel ja vajutage CTRL+SHIFT+F3 ja nimetage veerud ülemise rea järgi. Loe sellest siit.
Seega on vahemik B3: B11, C3: C11, D3: D11 ja E3: E11 vastavalt Jan, Feb, Mar ja Apr.
Kirjutage see valem lahtrisse I4.
= COUNTIFS (KAUDNE (I2), I3)
Nüüd, kui muudate kuud I4 -s, kuvatakse mudelis4 vastavate kuude arv I4 -s.
Kuidas see töötab?
Valem on lihtne. Alustame seestpoolt.
INDIRECT (I2): Nagu me teame, muudab funktsioon INDIRECT teksti viite tegelikuks viiteks. Oleme selle pakkunud I2. I2 sisaldab apr. Kuna meil on vahemik E3: E11 nimi Apr, tähendab kaudne (I2) E3: E11.
Valem on lihtsustatud = COUNTIFS (E3: E11, I3). COUNTIFS loeb kõik, mis on I3 vahemikus E3: E11.
Kuu muutmisel muutub veerg dünaamiliselt. Seda nimetatakse COUNTIFS koos muutuva veeruga. Gifis olen kasutanud mõnda tingimuslikku vormingut, mis põhineb teisel lahtril.
Valem võib töötada ka kontrafunktsiooniga. Aga kui soovite, et teil oleks mitu tingimust, kasutage funktsiooni COUNTIFS.
Loominguliste veergude diagramm, mis sisaldab kogusummasid
Loo ülekatte diagramm Excelis 2016
Tehke Excelis Pareto diagramm ja analüüs
Tehke Excelis jugade diagramm
Excel Sparklines: väikesed diagrammid lahtris
Spidomeetri (gabariidi) diagramm Excelis 2016