In dit artikel leren we hoe u berekende kolommen in Excel kunt maken.
Scenario
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 met verschillende scenario's kijken.
Een berekende kolom in een tabel maken:
- Selecteer een cel in een van de kolommen van een tabel of een lege cel tot aan de laatste kolom, in het onderstaande voorbeeld is de geselecteerde cel G2.
- Voeg de formule in die de verkoop per eenheid berekent, typ het gelijkteken (=), selecteer cel F2, typ het deelteken (/) en selecteer vervolgens cel E2 en druk op Enter.
De formule wordt automatisch gekopieerd naar alle cellen in de kolom.
Voorbeeld :
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 moeten 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 verklaring C2: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(INHOUDSOPGAVE(kolommen, ,BIJ ELKAAR PASSEN(kop, koptekst,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(INHOUDSOPGAVE(C3:N7,,BIJ ELKAAR PASSEN(B10,C2:N2,0))) |
Dit geeft terug:
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(INDIRECTE(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(INDIRECTE(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&"]")) |
Ik hoop dat dit artikel over het maken van berekende kolommen in Excel verklarend 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 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 :
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.