In dit artikel leren we Meerdere bereiken tellen met één criterium in Microsoft Excel.
Scenario:
In eenvoudige bewoordingen, tijdens het werken met gegevenstabellen, moeten we soms de cellen tellen waarin meer dan twee bereiken aan criteria voldoen. In Excel kunt u taken zoals bewerkingen over meerdere bereiken uitvoeren met behulp van de onderstaande formule. Criteria kunnen worden toegepast op tekst, nummer of een gedeeltelijke overeenkomst in Excel. Criteria binnen de formule die worden uitgevoerd met behulp van de operatoren. Operators zoals is gelijk aan ( = ), kleiner dan gelijk aan ( <= ), groter dan ( > ) of niet gelijk aan ( ).
Hoe het probleem op te lossen?
Voor dit probleem moeten we de SOMPRODUCT-functie gebruiken. Nu gaan we een formule maken van de functie. Hier krijgen we twee gegevensbereiken en we moeten de rijen tellen die aan 3 criteria voldoen. De functie SOMPRODUCT retourneert de SOM van de bijbehorende TRUE-waarden (als 1) en negeert waarden die overeenkomen met FALSE-waarden (als 0) in de geretourneerde array
Generieke formule:
| = SOMPRODUCT ( ( rng_1 op_1 crit_1 ) + 0 , ( rng_2 op_2 crit_1 ) + 0 , rng_2 op_2 crit_1 ) + 0 ) |
rng: bereik om naar te zoeken
crit: criteria om toe te passen
op : criteria-operator, voorwaarde gegeven als operator tussen bereik en criteria
+0 : converteert booleaanse waarden naar binair (0 & 1).
Voorbeeld:
Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we deze formule dus testen door deze in het onderstaande voorbeeld uit te voeren. Hier moeten we het aantal rijen vinden dat wordt vermeld in het bereik met 3 voorwaarden. Hier hebben we een lijst van diplomatenbijeenkomsten tussen India en de VS vanaf 2014. De tabel toont de president / premier met het landlabel en het jaar. De tabel is ook verdeeld in delen die het thuisland en de lijst met bezoekende landen vertegenwoordigen.
Onderstaande voorwaarden:
De Amerikaanse president "Barack Obama bezocht India met minder dan 2 problemen.
Gebruik de formule:
| = SOMPRODUCT (( C4:C10 = "Barack Obama") + 0, ( F4:F10 = "India") + 0, (G4:G10 < 2) + 0)) |
C4:C10="Barack Obama" : President die overeenkomt met "Barack Obama" in de bezoekerslijst.
F4:F10="India" : gastland dat overeenkomt met "India".
G4:G10<2: problemen minder dan twee.
+0 : converteert booleaanse waarden naar binair (0 & 1).
Hier wordt het bereik gegeven als celverwijzing. Druk op Enter om de telling te krijgen.
Zoals u kunt zien, heeft de Amerikaanse president Barack Obama slechts één keer een bezoek gebracht aan India, wat in 2015 is gebeurd. Dit laat zien dat de formule het aantal keren dat overeenkomt met de overeenkomstige matrix extraheert. Zoals er ook een en dezelfde tijd is dat de Amerikaanse president "Barack Obama" India bezocht, waar de problemen ook gelijk zijn aan 1 wat kleiner is dan 2.
Met gelijk aan criteria:
Bovenstaand voorbeeld was eenvoudig. Dus om het interessant te maken, zullen we tellen hoe vaak de VS India hebben gehost vanaf 2014 als door gegevens.
Onderstaande voorwaarden:
Door de VS gehost India met problemen is gelijk aan 2.
Gebruik de formule:
| =SOMPRODUCT ( ( F4:F10 = "VS") + 0, ( D4:D10 = "India") + 0, (G4:G10 = 2) + 0) |
F4:F10="VS" : gastland dat overeenkomt met "VS".
D4:D10="India" : bezoekend land dat overeenkomt met "India".
G4:G10=2 : uitgifte is gelijk aan twee.
+0 : converteert booleaanse waarden naar binair (0 & 1).
Hier wordt het bereik gegeven als celverwijzing. Druk op Enter om de telling te krijgen.
Zoals je kunt zien, zijn er 2 keer dat de VS India hebben gehost en problemen gelijk zijn aan twee. Dit laat zien dat de formule het aantal keren dat overeenkomt in de overeenkomstige matrix extraheert. Er zijn 5 keer dat de VS India hosten, maar de problemen waren 1 of 3, maar hier moeten we problemen met 2 matchen.
Met meer dan criteria:
Om het interessant te maken, zullen we tellen hoe vaak de Amerikaanse president "Donald Trump" de Indiase premier vanaf 2014 heeft gehost als door gegevens.
Onderstaande voorwaarden:
De Amerikaanse president "Donald Trump" gehost India met problemen is groter dan 1.
Gebruik de formule:
| =SOMPRODUCT ( ( C4:C10 = "Donald Trump") + 0 , ( F4 : F10 = "India") + 0 , ( G4:G10 >1 ) + 0 ) |
F4:F10="VS" : gastheer-president die overeenkomt met "Donald Trump".
D4:D10="India" : bezoekend land dat overeenkomt met "India".
G4:G10=2 : uitgifte is gelijk aan twee.
+0 : converteert booleaanse waarden naar binair (0 & 1).
Hier wordt het bereik gegeven als celverwijzing. Druk op Enter om de telling te krijgen.
Zoals je kunt zien, een keer waar de Amerikaanse president "Donald Trump" India ontving en meer dan twee uitgeeft. Dit laat zien dat de formule het aantal keren dat overeenkomt in de overeenkomstige matrix extraheert. Omdat er 2 keer was dat de Amerikaanse president "Donald Trump" India gastheer was, maar de problemen waren 1 of 3, maar hier moeten we problemen groter dan 1 hebben, wat 3 leugens is in het jaar 2019.
Met problemen die niet in criteria worden behandeld:
Om het gemakkelijk en gemakkelijk te begrijpen te maken, zullen we tellen hoe vaak de Amerikaanse president in totaal India heeft bezocht vanaf 2014, op basis van gegevens.
Onderstaande voorwaarden:
De Amerikaanse president bezocht India in totaal vanaf 2014.
Gebruik de formule:
| =SOMPRODUCT((F4:F10="India")+0,(D4:D10="VS")+0) |
F4:F10="VS" : gastland dat overeenkomt met "VS".
D4:D10="India" : bezoekend land dat overeenkomt met "India".
G4:G10=2 : uitgifte is gelijk aan twee.
+0 : converteert booleaanse waarden naar binair (0 & 1).
Hier wordt het bereik gegeven als celverwijzing. Druk op Enter om de telling te krijgen.
Zoals je kunt zien, 2 keer waar de VS India hebben bezocht en meer dan twee keer. Dit laat zien dat de formule het aantal keren dat overeenkomt in de overeenkomstige matrix extraheert. Zoals er een keer was dat de Amerikaanse president "Barack Obama" India bezocht in 2015 en een keer dat de Amerikaanse president "Donald Trump" India bezocht in het jaar 2020.
U kunt ook bereiken als criteria uitvoeren. Tel de cellen waar 2 bereiken voldoen aan de criteria. Lees hier meer over Countif met SUMPRODUCT in Excel.
Hier zijn enkele observatienotities die hieronder worden weergegeven.
Opmerkingen:
- De formule werkt alleen met getallen.
- De matrices in de formule moeten even lang zijn, omdat de formule een fout retourneert als dat niet het geval is.
- De functie SOMPRODUCT beschouwt niet-numerieke waarden als nullen.
- De functie SOMPRODUCT beschouwt de logische waarde TRUE als 1 en False als 0.
- De argumentarray moet dezelfde grootte hebben, anders retourneert de functie een fout.
- De functie SOMPRODUCT retourneert de som na het nemen van individuele producten in de bijbehorende array.
Ik hoop dat dit artikel over Meerdere bereiken tellen met één criterium in Microsoft Excel verhelderend is. Vind hier meer artikelen over het berekenen van waarden en gerelateerde Excel-formules. Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SOMPRODUCT-functie in Excel te gebruiken?: Retourneert de SUM na vermenigvuldiging van waarden in meerdere arrays in Excel.
AANTALLEN.ALS met dynamisch criteriumbereik : Tel cellen die de criteria selecteren uit de lijst met opties in de criteriacel in Excel met behulp van de gegevensvalidatietool.
AANTAL.ALS Twee criteria-overeenkomst : meerdere criteria komen overeen in verschillende lijsten in de tabel met behulp van de AANTAL.ALS-functie in Excel
AANTALLEN MET OF Voor meerdere criteria : match twee of meer namen in dezelfde lijst met behulp van de OR-criteria die op de lijst in Excel zijn toegepast.
Hoe Countif te gebruiken in VBA in Microsoft Excel : Tel cellen met criteria met behulp van Visual Basic for Applications-code in Excel-macro's.
Jokertekens gebruiken in Excel : Tel cellen die overeenkomen met zinnen in tekstlijsten met behulp van de jokertekens ( * , ? , ~ )in Excel
Populaire artikels :
Hoe de IF-functie in Excel te gebruiken? : De IF-instructie in Excel controleert de voorwaarde en retourneert een specifieke waarde als de voorwaarde WAAR is of retourneert een andere specifieke waarde als ONWAAR.
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken? : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.