Bill sendte denne uges Excel-spørgsmål ind.
Jeg har en database med begivenheder i Excel, og min chef vil have mig til at plotte frekvensdiagrammer efter måned. Jeg læste dit trick for at ændre daglige datoer til månedlige datoer og om Excel CSE-formler. Jeg har prøvet alle kriterier, jeg kan tænke på i Excel CountIf-formlen nedenfor for at få det til at se på 2 kriterier.
Din situation kunne sandsynligvis let løses med et drejetabel (XL95-XL2000) eller et drejetabel (kun XL2000). Lad os lige nu tage fat på det spørgsmål, du har stillet. Til venstre er dit regneark. Det ser ud til, at du vil indtaste formler i celler B4406: D4415 for at beregne antallet af bestemte begivenheder hver måned.
CountIf-funktionen er en specialiseret form for en matrixformel, som er fantastisk, når du har et enkelt kriterium. Det fungerer ikke godt, når du har flere kriterier. Følgende eksempelformler tæller antallet af rækker med Rain og antallet af begivenheder i januar 97:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Der er ingen måde at bruge CountIf til at få krydset mellem to betingelser.
For enhver læser, der ikke er fortrolig med, hvordan man indtaster matrixformler, anbefaler jeg stærkt at gennemgå Brug CSE-formler for at overbelaste Excel.
Bill sagde ikke det i sit spørgsmål, men jeg vil opbygge en formel, som han kan indtaste kun en gang i celle B4406, der let kan kopieres til de andre celler i hans rækkevidde. Ved at bruge absolutte og blandede referencer i formlen kan du gemme besværet med at indtaste en ny formel for hvert kryds.
Her er en hurtig gennemgang af absolutte, relative og blandede formler. Normalt hvis du indtaster en formel som =SUM(A2:A4403)i D1 og derefter kopierer formlen til E2, ændres din formel i E2 til =SUM(B3:C4403). Dette er en sej funktion i regneark kaldet "relativ adressering", men nogle gange ønsker vi ikke, at det skal ske. I dette tilfælde ønsker vi, at enhver formel henviser til området A2: B4403. Når vi kopierer formlen fra celle til celle, skal den altid pege på A2: B4403. Mens du går ind i formlen, skal du trykke på F4 en gang efter at have indtastet området, og din formel ændres til=SUM($A$2:$A$4403). Dollartegnet indikerer, at den del af referencen ikke ændres, når du kopierer formlen. Dette kaldes absolut adressering. Det er muligt kun at låse søjlen med $ og lade rækken være relativ. Dette kaldes en blandet reference og vil blive indtastet som =$A4406. Brug for at låse rækken, men tillade, at kolonnen er relativ =B$4405. Når du indtaster en formel, skal du bruge F4 til at skifte mellem de fire varianter af relative, absolutte og blandede referencer.
Her er formlen for celle B4406:
=SUM(IF($C$2:$C$4403=$A4406,IF($B$2:$B$4403=B$4405,1,0),0))
Skriv formlen. Når du er færdig med formlen, skal du holde Ctrl, Shift nede og derefter indtaste. Du kan nu kopiere formlen til C4406: D4406 og derefter kopiere de tre celler ned til hver række i din resultattabel.
Formlen bruger alle tre former for blandede og absolutte referencer. Det nestes 2, hvis udsagn, da AND () -funktionen ikke syntes at fungere i en matrixformel. For en bedre forklaring af, hvad der sker med array-funktionaliteten, skal du genlæse Brug CSE-formler til at overbelaste Excel nævnt ovenfor.
