Bill stuurde de Excel-vraag van deze week.
Ik heb een database met gebeurtenissen in Excel en mijn baas wil dat ik frequentiegrafieken per maand uitzet. Ik heb je truc gelezen om dagelijkse datums te veranderen in maandelijkse datums en over Excel CSE-formules. Ik heb alle criteria die ik kan bedenken geprobeerd in de Excel CountIf-formule hieronder om het naar 2 criteria te laten kijken.
Uw situatie kan waarschijnlijk eenvoudig worden opgelost met een draaitabel (XL95-XL2000) of een draaitabel (alleen XL2000). Laten we voorlopig de vraag bespreken die u heeft gesteld. Aan de linkerkant is je werkblad. Het lijkt erop dat u formules in de cellen B4406: D4415 wilt invoeren om het aantal bepaalde gebeurtenissen per maand te berekenen.
De CountIf-functie is een gespecialiseerde vorm van een matrixformule die geweldig is als u één criterium hebt. Het werkt niet goed als u meerdere criteria heeft. De volgende voorbeeldformules tellen het aantal rijen met Rain en het aantal gebeurtenissen in januari 97:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Er is geen manier om CountIf te gebruiken om de kruising van twee voorwaarden te krijgen.
Voor elke lezer die niet bekend is met het invoeren van matrixformules, raad ik ten zeerste aan Gebruik CSE-formules om Excel een boost te geven.
Bill heeft het niet in zijn vraag vermeld, maar ik wil een formule bouwen die hij maar één keer in cel B4406 kan invoeren en die gemakkelijk naar de andere cellen in zijn bereik kan worden gekopieerd. Door absolute en gemengde verwijzingen in de formule te gebruiken, bespaart u het gedoe van het invoeren van een nieuwe formule voor elk kruispunt.
Hier is een kort overzicht van absolute, relatieve en gemengde formules. Normaal gesproken, als u een formule invoert zoals =SUM(A2:A4403)in D1 en vervolgens de formule naar E2 kopieert, verandert uw formule in E2 in =SUM(B3:C4403). Dit is een coole functie van werkbladen die "relatieve adressering" wordt genoemd, maar soms willen we niet dat dat gebeurt. In dit geval willen we dat elke formule verwijst naar het bereik A2: B4403. Terwijl we de formule van cel naar cel kopiëren, moet deze altijd verwijzen naar A2: B4403. Terwijl u de formule invoert, drukt u eenmaal op F4 nadat u het bereik hebt ingevoerd, en uw formule verandert in=SUM($A$2:$A$4403). Het dollarteken geeft aan dat dat gedeelte van de referentie niet verandert als u de formule kopieert. Dit heet absolute adressering. Het is mogelijk om alleen de kolom met $ te vergrendelen en de rij relatief te laten zijn. Dit wordt een gemengde referentie genoemd en zou worden ingevoerd als =$A4406. Gebruik om de rij te vergrendelen maar de kolom relatief te laten zijn =B$4405. Gebruik tijdens het invoeren van een formule F4 om te wisselen tussen de vier smaken relatieve, absolute en gemengde verwijzingen.
Hier is de formule voor cel B4406:
=SUM(IF($C$2:$C$4403=$A4406,IF($B$2:$B$4403=B$4405,1,0),0))
Typ de formule. Wanneer u klaar bent met de formule, houdt u Ctrl, Shift ingedrukt en voert u vervolgens in. U kunt de formule nu naar C4406: D4406 kopiëren en vervolgens die drie cellen naar elke rij in uw resultaattabel kopiëren.
De formule gebruikt alle drie vormen van gemengde en absolute verwijzingen. Het nestelt 2 if-instructies, aangezien de functie AND () niet leek te werken in een matrixformule. Voor een betere uitleg van wat er gebeurt met de arrayfunctionaliteit, leest u CSE-formules gebruiken om Excel te stimuleren, zoals hierboven vermeld, opnieuw te lezen.
