Vi vet at COUNTIFS -funksjonen i Excel kan stole på flere kriterier. Det tar argumenter som et par kriterier og kriterier. Vi kan endre kriterier dynamisk ved å gi cellenes referanse, men vi kan ikke endre kriteriekolonnen dynamisk. Vel, ikke direkte, men vi kan. Det er det vi lærer på forhånd excel formler. Å gjøre ting i excel som ikke kan gjøres normalt. La oss se hvordan.
La oss lære ved eksempel.
Kontekst
Her har jeg utarbeidet en salgsdata gjort i forskjellige måneder av året av våre salgskonsulenter. De selger forskjellige modeller av produktet vårt, generisk navngitt modell1, modell2 og så videre. Det vi trenger å gjøre er å telle salget av forskjellige modeller i forskjellige måneder dynamisk.
I celle I2 velger vi måneden. I celle I2 velger vi modellen. Disse verdiene kan endres. Og tellingen bør endres også. COUNTIFS -funksjonen bør se etter månedskolonnen som vil være kriterier. Så vil den se etter modell i den månedlige kolonnen.
Så her er kriteriene og kriteriene_område begge variable. Så hvordan lager vi kolonnevariabel i COUNTIFS? Her er hvordan?
Bruke navngitt område for variabel kolonne eller kriterieområde
Generisk formel
= COUNTIFS (INDIRECT (named_range), kriterier)
Navngi først hver kolonne i henhold til overskriftene. For å gjøre det, velg tabellen og trykk CTRL+SKIFT+F3 og navngi kolonnene i den øverste raden. Les om det her.
Så, område B3: B11, C3: C11, D3: D11 og E3: E11 heter henholdsvis Jan, Feb, Mar og Apr.
Skriv denne formelen i I4.
= TELLER (INDIREKTE (I2), I3)
Hvis du endrer måneden i I4, vises de respektive månedene i Model4 i I4.
Hvordan det fungerer?
Formelen er enkel. La oss starte fra innsiden.
INDIRECT (I2): Som vi vet INDIRECT -funksjonen konverterer tekst ref til faktisk referanse. Vi har gitt det I2. I2 inneholder apr. Siden vi har område E3: E11 navn Apr, oversetter INDIRECT (I2) til E3: E11.
Formelen forenklet til = COUNTIFS (E3: E11, I3). COUNTIFS teller det som er i I3 i område E3: E11.
Når du endrer måned, endres kolonnen dynamisk. Dette kalles COUNTIFS med variable kolonner. I gif har jeg brukt noen betinget formatering basert på en annen celle.
Formelen kan også fungere med countif -funksjon. Men hvis du vil ha flere betingelser, kan du bruke COUNTIFS -funksjonen.
Kreativt kolonnediagram som inneholder totaler
Lag overleggsdiagram i Excel 2016
Utfør Pareto -diagram og analyse i Excel
Utfør Fossdiagram i Excel
Excel Sparklines: The Tiny Charts in Cell
Hastighetsmåler (måler) diagram i Excel 2016