NL/Documentation/How Tos/Optellen en tellen met voorwaarden

Auteur van het originele document: Arthur King - Conditional Counting and Summation

Optellen en tellen met voorwaarden in Calc

Dit is een overzicht van de verschillende manieren om te tellen en het met voorwaarden optellen van celinhoud, afhankelijk van het resultaat van enkele testen.

AANTAL

De functie AANTAL telt het aantal cellen dat getallen bevat en negeert alle andere. Tekstcellen worden bijvoorbeeld genegeerd.

AANTAL.ALS

De functie AANTAL.ALS telt het aantal cellen dat iets bevat (tekst, getallen, fouten, logische waarden, formules). Het negeert lege (blanco) cellen.

AANTAL.LEGE.CELLEN

De functie AANTAL.LEGE.CELLEN telt het aantal lege (blanco) cellen.

SOM

De functie SOM somt alle getallen in de opgegeven cellen op. Zie hieronder om dit te gebruiken als een functie met voorwaarden.

SUBTOTAAL

De functie SUBTOTAAL geeft de resultaten voor AANTAL, AANTALARG of SOM terug voor gefilterde gegevens, dat zijn gegevens in cellen die zijn gekozen met een filter.

AANTAL.ALS

De functie AANTAL.ALS telt die items die voldoen aan één enkele voorwaarde. AANTAL.ALS(A1:A4; ">4") telt bijvoorbeeld de cellen in A1:A4 die groter zijn dan 4.

SOM.ALS

De functie SOM.ALS telt die items op die aan één enkele voorwaarde voldoen. SOM.ALS(A1:A4; "=rood"; B1:B4) telt bijvoorbeeld de waarden op in B1:B4 die overeenkomen met de items “rood” in A1:A4.

DBAANTAL, DBAANTALC, DBSOM

De functie DBAANTAL, functie DBAANTALC en functie DBSOM voeren hetzelfde uit als AANTAL, AANTALARG en SOM, met het verschil dat de cellen die moeten worden geteld of opgeteld worden gekozen overeenkomstig een tabel met voorwaarden. DBAANTAL(A1:C5; 0; E6:F7) telt bijvoorbeeld het aantal rijen van A1:C5 waarvoor de meerdere voorwaarden, gespecificeerd in E6:F7, allemaal waar zijn.

Voorwaarden in celbereiken

Een eenvoudige methode om met behulp van meerdere voorwaarden te tellen of op te tellen is om die voorwaarden in een nieuwe rij of kolom in te voeren. Als bijvoorbeeld A1:A6 een lijst met kleuren bevat en B1:B6 een lijst van grootten, dan kunnen we in cel D1 de formule =(A1="rood") invoeren, die WAAR of ONWAAR teruggeeft, afhankelijk van het feit of cel A1 rood is of niet. Als alternatief kunnen we in cel D1 de formule =EN(A1="rood"; B1="groot") invoeren, die WAAR teruggeeft als cel A1 is rood EN cel B1 is groot en anders ONWAAR. Kopieer en plak deze formule naar D2:D6 en we hebben een celbereik dat WAAR is als aan de voorwaarden wordt voldaan en anders ONWAAR is.

In numerieke berekeningen, wordt WAAR behandeld als 1 en ONWAAR wordt behandeld als 0. Dus invoeren van =SOM(D1:D6) zal eenvoudigweg deze 1's en 0's optellen en ons het aantal items geven dat zowel rood EN groot zijn.

Omdat in feite WAAR en ONWAAR evalueren als 1 en 0, hebben we de functie EN niet nodig - in D1 kunnen we eenvoudigweg schrijven =(A1="rood")*(B1="groot"), en dat kopiëren/plakken naar beneden, naar D2:D6.

Laten we nu aannemen dat C1:C6 een lijst bevat van gewichten van deze items en dat we het totale gewicht willen weten voor alle items groot en rood. In D1 schrijven we =(A1="rood")*(B1="groot")*C1 en dat kopiëren/plakken we naar beneden, naar D2:D6. D1 zal het gewicht bevatten in C1 als aan de voorwaarden wordt voldaan (en anders nul), enzovoort voor D2:D6. Daarom zal =SOM(D1:D6) ons nu het totale gewicht geven.

Als alternatief is het mogelijk om D1:D6 te vullen met een matrixformule. Schrijf in D1 =(A1:A6="rood")*(B1:B6="groot")*C1:C6, en voer dat in door op Ctrl_Shift_Enter te drukken. Alle cellen in D1:D6 geven nu de gewenste gewichten weer zoals eerder.

SOMPRODUCT

De functie SUMPRODUCT kan worden gebruikt om de berekeningen van tellen en optellen in de vorige sectie uit te voeren, zonder extra kolommen te gebruiken. Het is nodig om kennis te hebben van matrixformules om dit te begrijpen.

Met behulp van het optel-voorbeeld uit de vorige sectie, mogen A1:A6="rood", B1:B6="groot" en C1:C6 worden behandeld als 3 afzonderlijke matrices, niet weergegeven maar intern berekend.

=SOMPRODUCT(A1:A6="rood"; B1:B6="groot"; C1:C6) zal de overeenkomende elementen van de matrices met elkaar vermenigvuldigen en hun som teruggeven, dat is:

(A1="rood")*(B1="groot")*C1 + (A2="rood")*(B2="groot")*C2 + ...

Dit geeft ons opnieuw het totale gewicht, zonder dat een extra kolom vereist is.

Gebruik =SOMPRODUCT((A1:A6="rood") * (B1:B6="groot") * C1:C6) voor portabiliteit naar Excel, omdat Calc en Excel logische resultaten verschillend behandelen.

Merk op dat formules met SOMPRODUCT eenvoudigweg worden ingevoerd met het drukken op de Enter-toets - zij vereisen geen Ctrl_Shift_Enter hoewel er toch matrices bij zijn betrokken.

Wees er ook alert op dat berekeningen met hele grote matrices een grote hoeveelheid computertijd vereisen en het werkblad kunnen vertragen.

SOM met matrixformules

Een alternatief voor SOMPRODUCT is om de functie SOM te gebruiken. Het vorige voorbeeld zou dan worden geschreven:

=SOM( (A1:A6="rood")*(B1:B6="groot")*C1:C6) )

en worden ingevoerd als een matrixformule door te drukken op Ctrl_Shift_Enter. Net als met SOMPRODUCT, werkt dit door de overeenkomende elementen van de matrices met elkaar vermenigvuldigen en hun som terug te geven.

Zie Matrices gebruiken voor de details van matrixformules en meer voorbeelden.

Draaitabel

Een andere manier om voorwaardelijk tellen en optellen te benaderen is om de Draaitabel te gebruiken om een interactieve tabel te genereren, waarbij gegevens kunnen worden geschikt en opgeteld, vanuit verschillende invalshoeken.

Tips en trucs: instellingen controleren

Bij het vergelijken van tekst met sommige functies (zoals SOM.ALS), kunnen de resultate heel goed afhangen van de instellingen in de pagina van het menu Extra-> Opties-> OpenOffice.org Calc-> Berekenen. Als de gebruikers-instellingen niet juist zijn, kunnen daarom de resultaten ook verkeerd zijn.

Eén oplossing is om op een prominente plek in het werkblad een controle op te nemen dat de instellingen juist zijn. Bijvoorbeeld:

=ALS(ISFOUT2(VIND.SPEC(".";"a"));"FOUT: schakel alstublieft Reguliere uitdrukkingen in";"")

zal een foutbericht weergeven als de reguliere uitdrukkingen zijn uitgeschakeld.

Een ander voorbeeld - voer in cel A3 de tekst:

Controle:  in.

Voer in cel A4 in:

="Reguliere uitdrukkingen zijn "&ALS(AANTAL.ALS(A3;".*"); "ingeschakeld"; "uitgeschakeld")

Voer in cel A5 in:

="Zoekcriteria = en <> moeten op hele cellen toepasbaar zijn is "&ALS(AANTAL.ALS(A3;"<>e"); "ingeschakeld"; "uitgeschakeld")

of zelfs beter: gebruik toepasselijke foutberichten.

Tips en trucs: items tussen twee datums

Datums worden intern opgeslagen als getallen en kunnen dus eenvoudig worden vergeleken. U zou kunnen gebruiken, om bijvoorbeeld het aantal cellen in A1:A6 tussen twee datums te tellen:

=SOMPRODUCT(A1:A6>DATUMWAARDE("5 nov 08"); A1:A6<DATUMWAARDE("5 dec 08"))

Als u de datums uitdrukt met voorwaartse strepen (slashes) (bijv. “1/2/2008”) kunt u de functie DATUMWAARDE weglaten omdat Calc de datum zal converteren. Wees er echter alert op dat in het ene land dit kan worden geconverteerd naar 1feb08 en in een ander naar 2jan08.

Tips en trucs: de grootste/kleinste items optellen

Voor het optellen van de 3 grootste getallen in A1:A5, is de meest rechtstreekse methode om in te voeren: =GROOTSTE(A1:A5; 1) in cel B1 =GROOTSTE(A1:A5; 2) in cel B2 =GROOTSTE(A1:A5; 3) in cel B3 zodat de grootste 3 getallen in B1:B3 staan en dan te gebruiken de formule: =SOM(B1:B3) om het resultaat te geven.

Deze methode is zeer helder en wordt daarom in het algemeen daar voor aanbevolen.

Indien u echter hetzelfde resultaat wilt bereiken in één enkele cel, zou u kunnen gebruiken

=SOMPRODUCT(GROOTSTE(A1:A5; RIJ(A1:A3)))

Hier is RIJ(A1:A3) een 1-koloms/3-rijen-matrix die de getallen 1, 2 en 3 bevat.

GROOTSTE(A1:A5; RIJ(A1:A3)) is dan een 1-koloms/3-rijen-matrix die de 3 grootste getallen bevat en SOMPRODUCT telt die eenvoudigweg bij elkaar op. We zouden SOM in plaats van SOMPRODUCT kunnen gebruiken, maar in dat geval moet de formule als een matrixformule worden ingevoerd door op Ctrl_Shift_Enter te drukken.

Gebruik ...RIJ(A1:A4).. in plaats van ...RIJ(A1:A3).. om (bijvoorbeeld) de 4 grootste getallen op te tellen.

Gebruik KLEINSTE(...) in plaats van GROOTSTE(...) om de kleinste getallen op te tellen.

Tips en trucs: overeenkomende blanco, etc cellen optellen

=SOMPRODUCT(ISLEEG(A1:A5); B1:B5)

somt deze cellen op in B1:B5 overeenkomend met de blanco cellen in A1:A5.

Anderse soortgelijke functies (bijv. ISTEKST, ISGETAL) kunnen op dezelfde manier worden gebruikt.

Tips en trucs: meer dan één kolom optellen

=SOMPRODUCT(D1:D6="rood";E1:E6+F1:F6)

zal de cellen optellen in E1:E6 en in F1:F6 die overeenkomen met cellen in D1:D6 die rood bevatten. Het resultaat is bijvoorbeeld E2+F2+E4+F4 als D2 en D4 "rood" bevatten.

Tips en trucs: elke n^e rij optellen

=SOMPRODUCT(MOD(RIJ(A1:A8); 2)=0;  A1:A8)

zal elke tweede rij in A1:A8 optellen. Wijzig de =0 naar =1 om elke tweede rij op te tellen, maar beginnend met de eerste rij. Wijzig de 2 naar 3 om elke derde rij op te tellen, enzovoort.

Tips en trucs: items met bepaalde opmaak optellen

De functie CEL geeft informatie terug over cellen, bijvoorbeeld de opmaak waarin getallen of datums worden weergegeven en de kolombreedte. Het kan dus worden gebruikt met één van bovenstaande methodes. Er bestaat geen functie die de kleur of een lettertype in een cel teruggeeft. Indien er geen onafhankelijke formule, in relatie tot de kleur of lettertype, bestaat, dan kan het nodig zijn om een macro te gebruiken.

Tips en trucs: overeenkomende items in een afzonderlijke lijst optellen

Stel dat A1:A9 een lijst met datums bevat, B1:B9 bevat telefoonnummers en C1:C9 de kosten van gemaakte telefoongesprekken. F1:F5 is een lijst van bepaalde telefoonnummers, en u wilt de totale kosten weten voor gesprekken naar deze nummers.

Voer in cel D1 in:

=ISGETAL(VERGELIJKEN(B1; F$1:F$5; 0))*C1

en kopieer/plak dat naar beneden, naar D2:D9

=SOM(D1:D9) geeft nu de totale kosten van de gesprekken.

Voor het uitvoeren van deze berekening zonder een extra kolom, zou u kunnen gebruiken:

=SOMPRODUCT(NIET(ISFOUT(VERGELIJKEN(B1:B9; F$1:F$5; 0))); C1:C9)

We hebben hierboven besproken hoe belangrijk het kan zijn om de instellingen voor Calc te controleren; dit is een goed voorbeeld - als de gebruiker reguliere uitdrukkingen heeft ingeschakeld (de standaard) wordt een telefoonnummer dat is geschreven als (072) 528-1700 geïnterpreteerd als een reguliere uitdrukking en zou overeen kunnen komen met andere items dan (072) 528-1700.

Content on this page is licensed under the Public Documentation License (PDL).