Vi ved, at COUNTIFS -funktionen i Excel kan regne med flere kriterier. Det tager argumenter som et par kriterier og kriterier. Vi kan ændre kriterier dynamisk ved at give cellehenvisningen, men vi kan ikke ændre kriteriekolonnen dynamisk. Nå, ikke direkte, men vi kan. Det er, hvad vi på forhånd lærer excel -formler. Gør ting i excel, som ikke kan gøres normalt. Lad os se hvordan.
Lad os lære ved eksempel.
Sammenhæng
Her har jeg udarbejdet en salgsdata udført i forskellige måneder af året af vores salgskonsulenter. De sælger forskellige modeller af vores produkt, generisk navngivet model1, model2 og så videre. Det, vi skal gøre, er at tælle salget af forskellige modeller dynamisk i forskellige måneder.
I celle I2 vælger vi måneden. I celle I2 vælger vi modellen. Disse værdier kan ændres. Og optællingen bør også ændre sig. COUNTIFS -funktionen skal lede efter den månedskolonne, der vil være kriterieinterval. Derefter vil den lede efter modellen i den månedlige kolonne.
Så her er kriterierne og kriterierne_interval begge variable. Så hvordan laver vi kolonnevariabel i COUNTIFS? Her er hvordan?
Brug af navngivet område til variabel kolonne eller kriterieområde
Generisk formel
= COUNTIFS (INDIRECT (named_range), kriterier)
Navngiv først hver kolonne i henhold til deres overskrifter. For at gøre det skal du vælge tabellen og trykke på CTRL+SKIFT+F3 og navngive kolonnerne i øverste række. Læs om det her.
Så område B3: B11, C3: C11, D3: D11 og E3: E11 hedder henholdsvis Jan, Feb, Mar og Apr.
Skriv denne formel i I4.
= TÆLLER (INDIREKTE (I2), I3)
Hvis du nu ændrer måneden i I4, vises de respektive måneders tælling af Model4 i I4.
Hvordan det virker?
Formlen er enkel. Lad os starte indefra.
INDIRECT (I2): Som vi kender INDIRECT funktionen konverterer tekst ref til faktisk reference. Vi har givet det I2. I2 indeholder apr. Da vi har område E3: E11 navn Apr, oversættes INDIRECT (I2) til E3: E11.
Formlen forenklet til = COUNTIFS (E3: E11, I3). COUNTIFS tæller det, der er i I3 i område E3: E11.
Når du ændrer måned, ændres kolonnen dynamisk. Dette kaldes COUNTIFS med variable kolonner. I gif har jeg brugt en betinget formatering baseret på en anden celle.
Formlen kan også fungere med countif -funktion. Men hvis du vil have flere betingelser, skal du bruge funktionen TÆLLER.
Kreativt søjlediagram, der indeholder totaler
Opret overlejringsdiagram i Excel 2016
Udfør Pareto -diagram og analyse i Excel
Udfør vandfaldsdiagram i Excel
Excel Sparklines: The Tiny Charts in Cell
Speedometer (Gauge) -diagram i Excel 2016