Bill sendte inn ukens Excel-spørsmål.
Jeg har en database med hendelser i Excel, og sjefen min vil at jeg skal plotte frekvensdiagrammer etter måned. Jeg leste trikset ditt for å endre daglige datoer til månedlige datoer og om Excel CSE-formler. Jeg har prøvd alle kriteriene jeg kan tenke meg i Excel CountIf-formelen nedenfor for å få det til å se på to kriterier.
Situasjonen din kan sannsynligvis løses enkelt med et pivottabell (XL95-XL2000) eller et pivot-diagram (kun XL2000). For nå, la oss ta opp spørsmålet du har stilt. Til venstre er regnearket ditt. Det ser ut til at du vil legge inn formler i celler B4406: D4415 for å beregne antall bestemte hendelser hver måned.
CountIf-funksjonen er en spesialisert form for en matriseformel som er flott når du har ett enkelt kriterium. Det fungerer ikke bra når du har flere kriterier. Følgende eksempelformler vil telle antall rader med Rain og antall hendelser i januar 97:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Det er ingen måte å bruke CountIf for å få skjæringspunktet mellom to forhold.
For alle lesere som ikke er kjent med hvordan du skriver inn matriseformler, anbefaler jeg sterkt å se gjennom Bruk CSE-formler for å overbelaste Excel.
Bill uttalte det ikke i spørsmålet sitt, men jeg vil bygge en formel som han kan skrive inn bare en gang i celle B4406 som lett kan kopieres til de andre cellene i hans område. Ved å bruke absolutte og blandede referanser i formelen, kan du spare bryet med å legge inn en ny formel for hvert kryss.
Her er en rask gjennomgang av absolutte, relative og blandede formler. Normalt hvis du skriver inn en formel som =SUM(A2:A4403)i D1 og deretter kopierer formelen til E2, vil formelen din i E2 endres til =SUM(B3:C4403). Dette er en kul funksjon på regneark kalt "relativ adressering", men noen ganger vil vi ikke at det skal skje. I dette tilfellet vil vi at hver formel skal referere til området A2: B4403. Når vi kopierer formelen fra celle til celle, bør den alltid peke på A2: B4403. Mens du går inn i formelen, trykker du på F4 en gang etter at du har angitt området, og formelen din vil endres til=SUM($A$2:$A$4403). Dollartegnet indikerer at den delen av referansen ikke endres når du kopierer formelen. Dette kalles absolutt adressering. Det er mulig å låse bare kolonnen med $ og la raden være relativ. Dette kalles en blandet referanse og vil bli angitt som =$A4406. Bruk for å låse raden, men la kolonnen være relativ =B$4405. Når du skriver inn en formel, kan du bruke F4 til å bytte mellom de fire smaker av relative, absolutte og blandede referanser.
Her er formelen for celle B4406:
=SUM(IF($C$2:$C$4403=$A4406,IF($B$2:$B$4403=B$4405,1,0),0))
Skriv inn formelen. Når du er ferdig med formelen, holder du nede Ctrl, Shift og deretter inn. Du kan nå kopiere formelen til C4406: D4406 og deretter kopiere de tre cellene ned til hver rad i resultattabellen.
Formelen bruker alle tre former for blandede og absolutte referanser. Den hekker 2 hvis setninger siden AND () -funksjonen ikke så ut til å fungere i en matriseformel. For å få en bedre forklaring på hva som skjer med matrisefunksjonaliteten, les på nytt Bruk CSE-formler for å overbelaste Excel som er nevnt ovenfor.
