Bill skickade in veckans Excel-fråga.
Jag har en databas över händelser i Excel och min chef vill att jag ska rita frekvensdiagram per månad. Jag läste ditt trick för att ändra dagliga datum till månadsdatum och om Excel CSE-formler. Jag har provat alla kriterier jag kan tänka mig i Excel CountIf-formeln nedan för att få det att titta på två kriterier.
Din situation kan förmodligen enkelt lösas med ett pivottabell (XL95-XL2000) eller ett pivotdiagram (endast XL2000). Låt oss för närvarande ta upp frågan du har ställt. Till vänster finns ditt kalkylblad. Det ser ut som att du vill ange formler i cellerna B4406: D4415 för att beräkna antalet vissa händelser varje månad.
CountIf-funktionen är en specialiserad form av en matrisformel som är bra när du har ett enda kriterium. Det fungerar inte bra när du har flera kriterier. Följande exempelformler räknar antalet rader med Rain och antalet händelser i januari 97:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Det finns inget sätt att använda CountIf för att få skärningspunkten mellan två villkor.
För alla läsare som inte känner till hur man anger matrisformler rekommenderar jag starkt att man granskar Använd CSE-formler för att överbelasta Excel.
Bill uppgav inte det i sin fråga, men jag vill bygga en formel som han kan ange bara en gång i cell B4406 som lätt kan kopieras till de andra cellerna i hans sortiment. Genom att använda absoluta och blandade referenser i formeln kan du spara besväret med att ange en ny formel för varje korsning.
Här är en snabb genomgång av absoluta, relativa och blandade formler. Normalt om du anger en formel som =SUM(A2:A4403)i D1 och sedan kopierar formeln till E2, kommer din formel i E2 att ändras till =SUM(B3:C4403). Detta är en cool funktion i kalkylblad som kallas "relativ adressering", men ibland vill vi inte att det ska hända. I det här fallet vill vi att varje formel hänvisar till intervallet A2: B4403. När vi kopierar formeln från cell till cell bör den alltid peka på A2: B4403. När du går in i formeln trycker du på F4 en gång efter att du har angett intervallet och din formel ändras till=SUM($A$2:$A$4403). Dollartecknet indikerar att den delen av referensen inte ändras när du kopierar formeln. Detta kallas absolut adressering. Det är möjligt att endast låsa kolumnen med $ och låta raden vara relativ. Detta kallas en blandad referens och skulle anges som =$A4406. Använd för att låsa raden men låta kolumnen vara relativ =B$4405. När du anger en formel, använd F4 för att växla mellan de fyra smakerna av relativa, absoluta och blandade referenser.
Här är formeln för cell B4406:
=SUM(IF($C$2:$C$4403=$A4406,IF($B$2:$B$4403=B$4405,1,0),0))
Skriv formeln. När du är klar med formeln håller du ned Ctrl, Shift och anger sedan. Du kan nu kopiera formeln till C4406: D4406 och sedan kopiera de tre cellerna ner till varje rad i din resultattabell.
Formeln använder alla tre former av blandade och absoluta referenser. Det häckar 2 om uttalanden eftersom AND () -funktionen inte verkade fungera i en matrisformel. För en bättre förklaring av vad som händer med arrayfunktionaliteten, läs igenom Använd CSE-formler för att överbelasta Excel som nämns ovan.
