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. Laten we deze manieren onderzoeken.
Laten we, in tegenstelling tot andere artikelen, eerst het scenario bekijken.
Hier heb ik een tabel met verkopen die door verschillende verkopers in verschillende maanden zijn gedaan.
Nu is het de taak om de som van de verkopen van een bepaalde maand in cel C10 te krijgen. Als we de maand in B10 veranderen, zou de som moeten veranderen en de som van die maand retourneren, zonder iets in de formule te veranderen.
Methode 1: Som hele kolom in tabel op met de functie SOMPRODUCT.
De syntaxis van de SOMPRODUCT-methode om de overeenkomende kolom op te tellen is:
| =SOMPRODUCT((kolommen)*(kopteksten=kop)) |
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.
Kopteksten:Het is het kopbereik van kolommen die u wilt optellen. In de bovenstaande gegevens is dit C2:N2.
rubriek: Het is de kop die u wilt matchen. In het bovenstaande voorbeeld is dit in B10.
Laten we zonder verder uitstel de formule gebruiken.
| =SOMPRODUCT((C3:N7)*(C2:N2=B10)) |
en dit komt terug:
Hoe werkt het?
Het is simpel. In de formule, de verklaringC2:N2=B10 geeft een array terug die alle FALSE-waarden bevat, behalve één die overeenkomt met B10. Nu is de formule
| =SOMPRODUCT((C3:N7)*{FALSE,FALSE,FALSE,FALSE,WAAR,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}) |
Nu wordt C3:N7 vermenigvuldigd met elke waarde van deze array. Elke kolom wordt nul, behalve de kolom die wordt vermenigvuldigd met WAAR. Nu wordt de formule:
| =SOMPRODUCT({0,0,0,0,6,0,0,0,0,0,0,0;0,0,0,0,12,0,0,0,0,0,0, 0;0,0,0,0,15,0,0,0,0,0,0,0;0,0,0,0,15,0,0,0,0,0,0,0; 0,0,0,0,8,0,0,0,0,0,0,0}) |
Nu wordt deze array samengevat en krijgen we de som van de kolom die overeenkomt met de kolom in cel B10.
Methode 2: Som hele kolom in tabel op met behulp van de INDEX-MATCH-functie.
De syntaxis van de methode om de overeenkomende kolomkop in Excel op te tellen is:
| =SOM(INDEX(kolommen,,VERGELIJKEN(kop;koppen,0))) |
Alle variabelen in deze methode zijn hetzelfde als de SOMPRODUCT-methode. Laten we het gewoon implementeren om het probleem op te lossen. Schrijf deze formule in C10.
| =SOM(INDEX(C3:N7,,VERGELIJKEN(B10,C2:N2,0))) |
Dit geeft terug:
Hoe werkt het?
De formule is binnenstebuiten opgelost. Ten eerste retourneert de MATCH-functie de index van de overeenkomende maand uit het bereik C2:N2. Omdat we mei hebben in B1o, krijgen we 5. Nu wordt de formule
| =SOM(INDEX(C3:N7,,5)) |
Vervolgens retourneert de functie INDEX waarden uit de 5e kolom van C3:N7. Nu wordt de formule:
| =SOM({6;12;15;15;8}) |
En tot slot krijgen we de som van deze waarden.
Methode 3: Som hele kolom in tabel op met behulp van benoemd bereik en INDIRECT-functie
Alles wordt eenvoudig als u uw bereiken een naam geeft als kolomkoppen. Bij deze methode moeten we eerst de kolommen een naam geven als kop.
Selecteer de tabel inclusief de kopjes en druk op CTRL+SHIFT+F3. Er wordt een dialoogvenster geopend om een naam uit de bereiken te maken. Controleer de bovenste rij en druk op de OK-knop.
Het zal alle gegevenskolommen een naam geven als hun koppen.
Nu is de generieke formule om de overeenkomende kolom op te tellen:
| =SOM(INDIRECT(kop)) |
rubriek: Het is de naam van de kolom die u wilt optellen. In dit voorbeeld is het B10 dat vanaf nu may bevat.
Om deze generieke formule te implementeren, schrijft u deze formule in cel C10.
| =SOM(INDIRECT(B10)) |
Dit geeft de som van de maand mei:
Een andere methode lijkt hierop. Bij deze methode gebruiken we Excel-tabellen en de gestructureerde naamgeving. Stel dat u de bovenstaande tabel tabel1 hebt genoemd. Dan werkt deze formule hetzelfde als de bovenstaande formule.
| =SOM(INDIRECT("Tabel1["&B10&"]")) |
Hoe werkt het?
In deze formule neemt de INDIRECT-functie de referentie van de naam en converteert deze naar de werkelijke naamreferentie. De procedure verder is eenvoudig. De SOM-functie somt het genoemde bereik op.
Dus ja jongens, dit is hoe je de overeenkomende kolom in Excel kunt optellen. Ik hoop dat het nuttig en verhelderend voor je is. Als je twijfels hebt over dit artikel of een ander Excel/VBA-gerelateerd onderwerp, vraag het dan in de comments hieronder.
Hoe te optellen door rij en kolom te matchen in Excel|De SOMPRODUCT 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 …
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.