Tot nu toe hebben we geleerd hoe we de volledige overeenkomende kolom van een tabel in Excel kunnen optellen. Maar hoe tellen we waarden op als we kolom en rij moeten matchen. In dit artikel zullen we leren hoe u de som van overeenkomende rijen en kolommen kunt maken.
Er zijn twee formules om dit te doen, maar laten we eerst het scenario bekijken.
Hier heb ik een tabel die de verkopen van werknemers in verschillende maanden registreert. De naam van medewerkers kan herhalen. Zie onderstaande figuur:
We hebben de som nodig van de maand mei waarin de verkoper Donald is.
Methode 1: De overeenkomende kolomkop en rijkop optellen met behulp van de functie SOMPRODUCT.
De SOMPRODUCT Functie is de meest veelzijdige functie als het gaat om het optellen en tellen van waarden met lastige criteria. De generieke functie om op te tellen door kolom en rij te matchen is:
| =SOMPRODUCT((kolommen)*(column_headers=column_heading)*(row_headers=row_heading) |
kolommen:Het is het 2-dimensionale bereik van de kolommen die u wilt optellen. Het mag geen headers bevatten. In de bovenstaande tabel is dat C3:N7.
column_headers:Het is het kopbereik vankolommen die u wilt optellen. In de bovenstaande gegevens is dit C2:N2.
column_heading: Het is de kop die u wilt matchen. In het bovenstaande voorbeeld is het in B13.
Laten we zonder verder uitstel de formule gebruiken.
rij_headers:Het is het kopbereik vanrijen die u wilt optellen. In de bovenstaande gegevens is dit B3:B10.
row_heading: Het is de kop die u in rijen wilt matchen. In het bovenstaande voorbeeld is dit in F13 .
Laten we zonder verder uitstel de formule gebruiken.
Schrijf deze formule in cel D13 en laat Excel zijn magie doen (er bestaat niet zoiets als magie) …
| =SOMPRODUCT((C3:N10)*(C2:N2=B13)*(B3:B10=E13)) |
Dit retourneert de waarde:
Wanneer u nu de maand of verkoper wijzigt, verandert de som volgens de rijkop en kolomkop.
Hoe werkt het?
Deze eenvoudige booleaanse logica.
(C2:N2=B13): Deze instructie retourneert een array van TRUE en FALSE. Alle overeenkomende waarden in de kolom hebben waar en andere hebben onwaar. In dit geval hebben we slechts één True, omdat het bereik C2:N2 slechts één instantie bevat May op 5e Plaats.
(B3:B10=E13): Dit werkt hetzelfde als hierboven en retourneert een array TRUE en FALSE. Alle overeenkomende waarden hebben TRUE en andere hebben FALSE. In dit geval hebben we 2 TRUE's omdat het bereik B3:B10 twee instanties van "Donald" heeft.
(C2:N2=B13)*(B3:B10=E13): Nu vermenigvuldigen we de arrays die worden geretourneerd door instructies. Dit wordt geïmplementeerd en logisch en we krijgen een array van enen en nullen. Nu hebben we een 2D-array die 2 1s en rust 0s zal bevatten.
(C3:N10)*(C2:N2=B13)*(B3:B10=E13)= Ten slotte vermenigvuldigen we de 2D-array met de 2D-tabel. Het retourneert opnieuw een array van nullen en de getallen die overeenkomen met de criteria.
eindelijk, de SOMPRODUCT functie zal de array samenvatten die zal resulteren in de gewenste output.
Methode 2: De overeenkomende kolomkop en rijkop optellen Met behulp van de SOM- en ALS-functie
De generieke formule voor het optellen van overeenkomende rij en kolom met behulp van SOM en ALS Excel-functie is:
| =SUM(IF(column_headers=column_heading,IF(row_headers=row_heading,kolommen))) |
Alle variabelen zijn hetzelfde als in de hierboven uitgelegde methode. Hier moeten ze alleen in een andere volgorde worden gebruikt.
Schrijf deze formule in cel D13:
| =SOM(ALS(C2:N2=B13,ALS(B3:B10=E13,C3:N10))) |
Dit geeft het juiste antwoord. Zie de schermafbeelding hieronder:
Hoe werkt het?
De logica is hetzelfde als de eerste SUMPRODCUT-methode, alleen het mechanisme is anders. Als ik het in het kort uitleg, retourneert de innerlijke IF-functie een 2D-array met dezelfde dimensie als de tabel. Deze array bevat het aantal van twee overeenkomende rijen. Vervolgens komt de binnenste ALS-functie overeen met de koppen met twee kolommen in deze array en retourneert de 2D-array die alleen de getallen bevat die overeenkomen met zowel de kolom als de kop. Alle andere elementen van de array zijn FALSE.
Ten slotte somt de SOM-functie deze array op en krijgen we onze som.
Dus ja jongens, zo kun je de overeenkomende rijen en kolommen uit een tabel in Excel samenvatten. Ik hoop dat het verhelderend en nuttig voor je was. Als je twijfels hebt over dit onderwerp of een andere Excel/VBA-gerelateerde twijfel hebt, vraag het dan in de comments hieronder.
Kolom in een Excel optellen door koptekst te matchen | Als u de som van een kolom wilt krijgen door alleen de kolomnaam te gebruiken, kunt u dit op 3 eenvoudige manieren doen in Excel. De syntaxis van de SOMPRODUCT-methode om de overeenkomende kolom op te tellen is:
SUMIF met 3D-referentie in Excel |Het leuke feit is dat de normale Excel 3D-referentie niet werkt met voorwaardelijke functies, zoals de SUMIF-functie. In dit artikel zullen we leren hoe u 3D-referentie kunt laten werken met de SUMIF-functie.
Relatieve en absolute referentie in Excel | Verwijzen in Excel is een belangrijk onderwerp voor elke beginner. Zelfs ervaren Excel-gebruikers maken fouten bij het verwijzen.
Dynamische werkbladreferentie | Geef referentiebladen dynamisch met behulp van de INDIRECT-functie van Excel. Dit is eenvoudig…
Referenties uitbreiden in Excel | De uitbreidende referentie wordt groter wanneer deze naar beneden of naar rechts wordt gekopieerd. Hiervoor gebruiken we het $-teken voor de kolom en het rijnummer. Hier is een voorbeeld…
Alles over absolute referentie | Het standaardverwijzingstype in Excel is relatief, maar als u wilt dat de verwijzing van cellen en bereiken absoluut is, gebruikt u het $ -teken. Hier zijn alle aspecten van absolute verwijzingen in Excel.
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werkt u nog sneller in Excel.
De VERT.ZOEKEN-functie in Excel | 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.
AANTAL.ALS in Excel 2016 | Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om een specifieke waarde te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
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.