Vi vet att COUNTIFS -funktionen i Excel kan räkna med flera kriterier. Det tar argument som ett par kriterier och kriterier. Vi kan ändra kriterier dynamiskt genom att ge cellens referens men vi kan inte ändra kriteriekolumnen dynamiskt. Tja, inte direkt men vi kan. Det är vad vi lär oss i förväg excelformler. Att göra saker i excel som inte kan göras normalt. Låt oss se hur.
Låt oss lära oss genom exempel.
Sammanhang
Här har jag utarbetat en försäljningsdata som gjorts under olika månader av året av våra försäljningskonsulter. De säljer olika modeller av vår produkt, generellt namngiven modell1, modell2 och så vidare. Vad vi behöver göra är att räkna försäljningen av olika modeller under olika månader dynamiskt.
I cell I2 väljer vi månad. I cell I2 väljer vi modellen. Dessa värden kan förändras. Och antalet bör också förändras. RÄTTNINGSFUNKTIONEN ska leta efter månadskolumnen som kommer att vara kriterier. Sedan kommer den att leta efter modell i den månadskolumnen.
Så här är kriterierna och kriterierna_intervall variabla. Så hur gör vi kolumnvariabel i COUNTIFS? Så här gör du?
Använda namngivet intervall för variabel kolumn eller kriterier
Generisk formel
= RÄKNINGAR (INDIRECT (named_range), kriterier)
Namnge först varje kolumn enligt deras rubriker. För att göra det, välj tabellen och tryck på CTRL+SKIFT+F3 och namnge kolumnerna enligt översta raden. Läs om det här.
Så, intervall B3: B11, C3: C11, D3: D11 och E3: E11 heter Jan, Feb, Mar respektive Apr.
Skriv denna formel i I4.
= RÄKNINGAR (INDIRECT (I2), I3)
Om du nu ändrar månaden i I4 kommer respektive månads räkning av Model4 att visas i I4.
Hur det fungerar?
Formeln är enkel. Låt oss börja inifrån.
INDIRECT (I2): Som vi vet INDIRECT -funktionen konverterar text ref till faktisk referens. Vi har tillhandahållit det I2. I2 innehåller apr. Eftersom vi har intervallet E3: E11 -namnet Apr, översätter INDIRECT (I2) till E3: E11.
Formeln förenklad till = RÄTTA (E3: E11, I3). COUNTIFS räknar vad som helst i I3 i intervall E3: E11.
När du ändrar månad ändras kolumnen dynamiskt. Detta kallas COUNTIFS med variabla kolumner. I gif har jag använt en del villkorlig formatering baserad på en annan cell.
Formeln kan också fungera med countif -funktion. Men om du vill ha flera villkor använder du COUNTIFS -funktionen.
Kreativt kolumndiagram som innehåller totalt
Skapa överlagringsdiagram i Excel 2016
Utför Pareto -diagram och analys i Excel
Utför vattenfallsschema i Excel
Excel Sparklines: The Tiny Charts in Cell
Hastighetsmätare (mätare) diagram i Excel 2016