U kunt een kolom optellen met een enkele klik, de AutoSom-functie, SOM-functie, filterfunctie en door een dataset om te zetten in een tabel.
Het optellen van kolommen of rijen met getallen is iets dat de meesten van ons vrij vaak moeten doen. Als u bijvoorbeeld cruciale gegevens zoals verkooprecords of prijslijsten opslaat in de cellen van een enkele kolom, wilt u misschien snel het totaal van die kolom weten. Het is dus noodzakelijk om te weten hoe u een kolom in Excel optelt.
Er zijn verschillende manieren waarop u een kolom/rij in Excel kunt optellen of optellen, waaronder, met een enkele klik, de AutoSom-functie, SOM-functie, filterfunctie, SUMIF-functie en door een gegevensset om te zetten in een tabel. In dit artikel zullen we de verschillende methoden zien voor het optellen van een kolom of rij in Excel.
SUM een kolom met één klik met behulp van de statusbalk
De eenvoudigste en snelste manier om de totale waarde van een kolom te berekenen, is door op de letter van de kolom met de cijfers te klikken en de 'Status'-balk onderaan aan te vinken. Excel heeft een statusbalk onder aan het Excel-venster, die verschillende informatie over een Excel-werkblad weergeeft, inclusief het gemiddelde, het aantal en de somwaarde van de geselecteerde cellen.
Laten we aannemen dat u een tabel met gegevens heeft zoals hieronder weergegeven en dat u het totaal van de prijzen in kolom B wilt vinden.
Het enige wat je hoeft te doen is de hele kolom te selecteren met de getallen die je wilt optellen (Kolom B) door op de letter B bovenaan de kolom te klikken en naar de Excel Statusbalk te kijken (naast de zoomknop).
Daar ziet u het totaal van de geselecteerde cellen samen met de gemiddelde en telwaarden.
U kunt ook gegevensbereik B2 tot B11 selecteren in plaats van de hele kolom en de statusbalk zien om het totaal te weten. U kunt het totaal aantal getallen in een rij ook vinden door de rij met waarden te selecteren in plaats van een kolom.
Het voordeel van het gebruik van deze methode is dat het automatisch de cellen met tekstwaarden negeert en alleen de getallen optelt. Zoals je hierboven kunt zien, somde het alleen de getallen in die kolom op toen we de hele kolom B selecteerden, inclusief cel B1 met een teksttitel (Prijs).
SUM een kolom met AutoSum-functie
Een andere snelste manier om een kolom in Excel samen te vatten, is door de functie AutoSom te gebruiken. AutoSom is een Microsoft Excel-functie waarmee u snel een celbereik (kolom of rij) met getallen/gehele getallen/decimalen kunt optellen met behulp van de SOM-functie.
Er is een 'AutoSom'-opdrachtknop op zowel het tabblad 'Home' als 'Formule' van het Excel-lint dat de 'SOM-functie' in de geselecteerde cel zal invoegen wanneer erop wordt gedrukt.
Stel dat u de onderstaande gegevenstabel hebt en u wilt de getallen in kolom B optellen. Selecteer een lege cel direct onder de kolom of het rechteruiteinde van een rij gegevens (om een rij op te tellen) die u moet optellen.
Selecteer vervolgens het tabblad 'Formule' en klik op de knop 'AutoSom' in de groep Functiebibliotheek.
Of ga naar het tabblad 'Home' en klik op de knop 'AutoSom' in de groep Bewerken.
Hoe dan ook, zodra u op de knop klikt, voegt Excel automatisch de '= SUM ()' in de geselecteerde cel in en markeert het bereik met uw nummers (marsende mieren rond het bereik). Controleer of het geselecteerde bereik correct is en als het niet het juiste bereik is, kunt u dit wijzigen door een ander bereik te selecteren. En de parameters van de functie passen zich automatisch aan.
Druk vervolgens op Enter op uw toetsenbord om de som van de hele kolom in de geselecteerde cel te zien.
U kunt de AutoSom-functie ook oproepen met een sneltoets.
Om dat te doen, selecteert u de cel, die zich net onder de laatste cel in de kolom bevindt waarvan u het totaal wilt, en gebruikt u de onderstaande snelkoppeling:
Alt+= (Houd de Alt-toets ingedrukt en druk op het gelijkteken = toetsEn het zal automatisch de SOM-functie invoegen en het bereik ervoor selecteren. Druk vervolgens op Enter om de kolom te totaliseren.
Met AutoSom kunt u snel een kolom of rij optellen met een enkele klik of druk op een sneltoets.
Er is echter een bepaalde beperking aan de AutoSom-functie, deze zou het juiste bereik niet detecteren en selecteren als er lege cellen in het bereik zijn of een cel met een tekstwaarde.
Zoals u in het bovenstaande voorbeeld kunt zien, is cel B6 leeg. En toen we de AutoSom-functie in cel B12 invoerden, selecteert deze slechts 5 cellen hierboven. Het is omdat de functie waarneemt dat cel B7 het einde van de gegevens is en slechts 5 cellen retourneert voor het totaal.
Om dit op te lossen, moet u het bereik wijzigen door met de muis te klikken en te slepen of de juiste celverwijzingen handmatig in te typen om de hele kolom te markeren en op Enter te drukken. En u krijgt het juiste resultaat.
Om dit te voorkomen, kunt u de SOM-functie ook handmatig invoeren om de som te berekenen.
SUM een kolom door de SUM-functie handmatig in te voeren
Hoewel de opdracht AutoSom snel en gemakkelijk te gebruiken is, moet u soms de functie SOM handmatig invoeren om de som van een kolom of rij in Excel te berekenen. Vooral als u slechts enkele cellen in uw kolom wilt optellen of als uw kolom lege cellen of cellen met een tekstwaarde bevat.
Als u uw somwaarde wilt weergeven in een van de cellen in het werkblad, behalve de cel direct onder de kolom of de cel na de rij getallen, kunt u de functie SOM gebruiken. Met de SOM-functie kunt u de som of het totaal van cellen overal in het werkblad berekenen.
De syntaxis van de SOM-functie:
=SOM(getal1, [getal2],...).nummer 1(vereist) is de eerste numerieke waarde die moet worden toegevoegd.nummer 2(optioneel) is de tweede extra numerieke waarde die moet worden toegevoegd.
Hoewel nummer 1 het vereiste argument is, kunt u maximaal 255 extra argumenten optellen. De argumenten kunnen de getallen zijn die je wilt optellen of celverwijzingen naar de getallen.
Een ander voordeel van het handmatig gebruiken van de SOM-functie is dat u getallen optelt in niet-aangrenzende cellen van een kolom of rij, evenals in meerdere kolommen of rijen. Zo gebruikt u de SOM-functie handmatig:
Selecteer eerst de cel waar u het totaal van een kolom of rij ergens op het werkblad wilt zien. Start vervolgens uw formule door te typen =SOM( in de cel.
Selecteer vervolgens het celbereik met de getallen die u wilt optellen of typ de celverwijzingen voor het bereik dat u wilt optellen in de formule.
U kunt met de muis klikken en slepen of de Shift-toets ingedrukt houden en vervolgens de pijltoetsen gebruiken om een celbereik te selecteren. Als u de celverwijzing handmatig wilt invoeren, typt u de celverwijzing van de eerste cel van het bereik, gevolgd door een dubbele punt, gevolgd door de celverwijzing van de laatste cel van het bereik.
Nadat u de argumenten hebt ingevoerd, sluit u het haakje en drukt u op Enter om het resultaat te krijgen.
Zoals u kunt zien, geeft de functie u de som van alle geselecteerde cellen, ook al heeft de kolom een lege cel en een tekstwaarde.
Optellen van niet-continue cellen in een kolom
In plaats van een reeks doorlopende cellen op te tellen, kunt u ook niet-doorlopende cellen in een kolom optellen. Als u niet-aangrenzende cellen wilt selecteren, houdt u de Ctrl-toets ingedrukt en klikt u op de cellen die u wilt optellen, of typt u handmatig celverwijzingen en scheidt u ze met comms (,) in de formule.
Hierdoor wordt de som van alleen de geselecteerde cellen in de kolom weergegeven.
Meerdere kolommen optellen
Als u de som van meerdere kolommen wilt, selecteert u meerdere kolommen met de muis of voert u de celverwijzing van de eerste in het bereik in, gevolgd door een dubbele punt, gevolgd door de laatste celverwijzing van het bereik voor de argumenten van de functie.
Nadat u de argumenten hebt ingevoerd, sluit u het haakje en drukt u op Enter om het resultaat te zien.
Niet-aangrenzende kolommen optellen
U kunt ook niet-aangrenzende kolommen optellen met behulp van de SOM-functie. Hier is hoe:
Selecteer een cel in het werkblad waar u het totaal van de niet-aangrenzende kolommen wilt weergeven. Start vervolgens de formule door de functie te typen =SOM( in die cel. Selecteer vervolgens het eerste kolombereik met de muis of typ de bereikverwijzing handmatig.
Voeg vervolgens een komma toe en selecteer het volgende bereik of typ de tweede bereikverwijzing. U kunt op deze manier zoveel bereiken toevoegen als u wilt en ze allemaal scheiden met een komma (,).
Sluit na de argumenten het haakje en druk op Enter om het resultaat te krijgen.
Kolom optellen met benoemd bereik
Als u een groot werkblad met gegevens hebt en u snel het totaal van getallen in een kolom wilt berekenen, kunt u benoemde bereiken in de SOM-functie gebruiken om het totaal te vinden. Wanneer u benoemde bereiken maakt, kunt u deze namen gebruiken in plaats van de celverwijzingen, waardoor u gemakkelijk naar gegevenssets in Excel kunt verwijzen. Het is gemakkelijk om genoemd bereik in de functie te gebruiken in plaats van honderden rijen naar beneden te scrollen om het bereik te selecteren.
Een ander voordeel van het gebruik van Benoemd bereik is dat je kunt verwijzen naar een dataset (bereik) in een ander werkblad in het SUM-argument en de somwaarde in het huidige werkblad kunt krijgen.
Als u een benoemd bereik in een formule wilt gebruiken, moet u er eerst een maken. Hier ziet u hoe u een benoemd bereik in de SOM-functie maakt en gebruikt.
Selecteer eerst het cellenbereik (zonder kopteksten) waarvoor u een benoemd bereik wilt maken. Ga vervolgens naar het tabblad 'Formules' en klik op de knop 'Naam definiëren' in de groep Gedefinieerde namen.
Geef in het dialoogvenster Nieuwe naam de naam op die u aan het geselecteerde bereik wilt geven in het veld 'Naam:'. In het veld 'Bereik:' kunt u het bereik van het benoemde bereik wijzigen als de hele werkmap of een specifiek werkblad. Het bereik geeft aan of het genoemde bereik beschikbaar zou zijn voor de hele werkmap of alleen voor een specifiek blad. Klik vervolgens op de knop 'OK'.
U kunt ook de referentie van het bereik wijzigen in het veld 'Verwijst naar'.
Als alternatief kunt u een bereik ook een naam geven door het vak 'Naam' te gebruiken. Selecteer hiervoor het bereik, ga naar het vak 'Naam' aan de linkerkant van de formulebalk (net boven de letter A) en voer de naam in die u aan het geselecteerde datumbereik wilt toewijzen. Druk vervolgens op Enter.
Maar wanneer u een benoemd bereik maakt met behulp van het vak Naam, wordt het bereik van het benoemde bereik automatisch ingesteld op de hele werkmap.
Nu kunt u het benoemde bereik gebruiken dat u hebt gemaakt om snel de somwaarde te vinden.
Om dit te doen, selecteert u een willekeurige lege cel ergens in de werkmap waar u het Som-resultaat wilt weergeven. En typ de SOM-formule met het benoemde bereik zoals de argumenten en druk op Enter:
=SOM(Prijzen)
In het bovenstaande voorbeeld verwijst de formule in Blad 4 naar de kolom met de naam 'Prijzen' in Blad 2 om de som van een kolom te krijgen.
Som alleen de zichtbare cellen in een kolom op met de functie SUBTOTAAL
Als u cellen of verborgen cellen in een gegevensset of kolom hebt gefilterd, is het niet ideaal om de SOM-functie te gebruiken om een kolom op te tellen. Omdat de SOM-functie gefilterde of verborgen cellen in de berekening opneemt.
Het onderstaande voorbeeld laat zien wat er gebeurt als u een kolom optelt met verborgen of gefilterde rijen:
In de bovenstaande tabel hebben we kolom B gefilterd op prijzen die lager zijn dan 100. Als resultaat hebben we enkele gefilterde rijen. U kunt zien dat er gefilterde/verborgen rijen in de tabel zijn door de ontbrekende rijnummers.
Als u nu de zichtbare cellen in kolom B optelt met behulp van de SOM-functie, zou u '207' als de somwaarde moeten krijgen, maar in plaats daarvan wordt '964' weergegeven. Dit komt omdat de SOM-functie ook rekening houdt met de gefilterde cellen bij het berekenen van de som.
Dit is de reden waarom u de SOM-functie niet kunt gebruiken als het om gefilterde of verborgen cellen gaat.
Als u niet wilt dat de gefilterde/verborgen cellen worden meegenomen in de berekening bij het optellen van een kolom en u wilt alleen de zichtbare cellen optellen, dan moet u de functie SUBTOTAAL gebruiken.
SUBTOTAAL Functie
Het SUBTOTAAL is een krachtige ingebouwde functie in Excel waarmee u verschillende berekeningen (SUM, AVERAGE, COUNT, MIN, VARIANCE en andere) op een reeks gegevens kunt uitvoeren en een totaal of een totaal resultaat van de kolom retourneert. Deze functie vat alleen gegevens in de zichtbare cellen samen en negeert gefilterde of verborgen rijen. SUBTOTAAL is een veelzijdige functie die 11 verschillende functies kan uitvoeren in zichtbare cellen van een kolom.
De functie Syntaxis van SUBTOTAAL:
=SUBTOTAAL (function_num, ref1, [ref2], ...)Argumenten:
function_num(vereist)– Het is een functienummer dat aangeeft welke functie moet worden gebruikt voor het berekenen van het totaal. Dit argument kan elke waarde aannemen van 1 tot 11 of van 101 tot 111. Hier moeten we de zichtbare cellen optellen en weggefilterde cellen negeren. Daarvoor moeten we '9' gebruiken.ref1(vereist)– Het eerstgenoemde bereik of de eerstgenoemde referentie waarvan u een subtotaal wilt maken.ref2(optioneel) - Het tweede benoemde bereik of de referentie waarvan u een subtotaal wilt maken. Na de eerste referentie kunt u maximaal 254 extra referenties toevoegen.
Een kolom optellen met de functie SUBTOTAAL
Als u zichtbare cellen wilt optellen en gefilterde of verborgen cellen wilt uitsluiten, volgt u deze stappen om de functie SUBTOTAAL te gebruiken om een kolom op te tellen:
Eerst moet je je tabel filteren. Om dat te doen, klikt u op een cel in uw dataset. Navigeer vervolgens naar het tabblad 'Gegevens' en klik op het pictogram 'Filter' (trechterpictogram).
De pijlen verschijnen naast kolomkoppen. Klik op het pijltje naast de kolomkop waarmee je de tabel wilt filteren. Kies vervolgens de filteroptie die u op uw gegevens wilt toepassen. In het onderstaande voorbeeld willen we kolom B filteren met getallen kleiner dan 100.
In het dialoogvenster Custom AutoFilter voeren we '100' in en klikken we op 'OK'.
De getallen in de kolom worden gefilterd op waarden kleiner dan 100.
Selecteer nu de cel waar u de somwaarde wilt weergeven en begin met het typen van de functie SUBTOTAAL. Zodra u de functie SUBTOTAAL opent en het haakje typt, ziet u een lijst met functies die u in de formule kunt gebruiken. Klik op ‘9 – SOM’ in de lijst of typ handmatig ‘9’ als eerste argument.
Selecteer vervolgens het celbereik dat u wilt optellen of typ de bereikverwijzing handmatig en sluit het haakje. Druk vervolgens op Enter.
Nu zou je de som (subtotaal) van alleen de zichtbare cellen krijgen - '207'
Als alternatief kunt u ook het bereik (B2:B11) selecteren met de getallen die u wilt optellen en op 'AutoSom' klikken onder het tabblad 'Home' of 'Formules'.
Het voegt automatisch de SUBTOTAAL-functie toe aan het einde van de tabel en somt het resultaat op.
Converteer uw gegevens naar een Excel-tabel om de som van de kolom te krijgen
Een andere gemakkelijke manier om uw kolom op te tellen, is door uw spreadsheetgegevens om te zetten in een Excel-tabel. Door uw gegevens om te zetten in een tabel, kunt u niet alleen uw kolom optellen, maar kunt u ook vele andere functies of bewerkingen met uw lijst uitvoeren.
Als uw gegevens nog niet in tabelindeling zijn, moet u deze converteren naar een Excel-tabel. Zo converteert u uw gegevens naar een Excel-tabel:
Selecteer eerst een cel in de gegevensset die u naar een Excel-tabel wilt converteren. Ga vervolgens naar het tabblad 'Invoegen' en klik op het pictogram 'Tabel'
Of u kunt op de sneltoets Ctrl+T drukken om het celbereik om te zetten in een Excel-tabel.
Bevestig in het dialoogvenster Tabel maken het bereik en klik op 'OK'. Als uw tafel kopteksten heeft, laat u de optie 'Mijn tafel heeft kopteksten' aangevinkt.
Hiermee converteert u uw gegevensset naar een Excel-tabel.
Als de tabel klaar is, selecteert u een willekeurige cel in de tabel. Navigeer vervolgens naar het tabblad 'Ontwerp' dat alleen verschijnt als u een cel in de tabel selecteert en het selectievakje 'Totale rij' onder de groep 'Opties voor tabelstijl' aanvinkt.
Nadat u de optie 'Totale rij' heeft aangevinkt, verschijnt er onmiddellijk een nieuwe rij aan het einde van uw tabel met waarden aan het einde van elke kolom (zoals hieronder weergegeven).
En wanneer u op een cel in die nieuwe rij klikt, ziet u een vervolgkeuzelijst naast die cel van waaruit u een functie kunt toepassen om het totaal te krijgen. Selecteer de cel in de laatste rij (nieuwe rij) van de kolom die u wilt optellen, klik op de vervolgkeuzelijst ernaast en zorg ervoor dat de functie 'SOM' is geselecteerd in de lijst.
U kunt de functie ook wijzigen in Gemiddelde, Aantal, Min, Max en andere om hun respectieve waarden in de nieuwe rij te zien.
Som een kolom op op basis van een criterium
Alle voorgaande methoden hebben u laten zien hoe u het totaal van de hele kolom kunt berekenen. Maar wat als u alleen specifieke cellen wilt optellen die aan criteria voldoen in plaats van alle cellen. Dan moet je de SUMIF-functie gebruiken in plaats van de SOM-functie.
De functie SUMIF zoekt naar een specifieke voorwaarde in een reeks cellen (kolom) en somt vervolgens de waarden op die aan de gegeven voorwaarde voldoen (of waarden die overeenkomen met de cellen die aan de voorwaarde voldoen). U kunt waarden optellen op basis van getalvoorwaarde, tekstvoorwaarde, datumvoorwaarde, jokertekens en op basis van lege en niet-lege cellen.
Syntaxis van SUMIF-functie:
=SUMIF(bereik, criteria, [som_bereik])Argumenten/parameters:
bereik– Het cellenbereik waarin we zoeken naar de cellen die aan de criteria voldoen.criteria– De criteria die bepalen welke cellen moeten worden opgeteld. Het criterium kan een getal, tekenreeks, datum, celverwijzing, uitdrukking, logische operator, jokerteken en andere functies zijn.sum_range(optioneel) – Het is het gegevensbereik met waarden die moeten worden opgeteld als de corresponderende bereikinvoer overeenkomt met de voorwaarde. Als dit argument niet is opgegeven, wordt in plaats daarvan het 'bereik' opgeteld.
Stel dat u de onderstaande dataset heeft met verkoopgegevens van elke vertegenwoordiger uit verschillende regio's en dat u alleen het verkoopbedrag uit de regio 'Zuid' wilt optellen.
Dat doe je eenvoudig met de volgende formule:
=SOM.ALS(B2:B19,"Zuid",C2:C19)Selecteer de cel waar u het resultaat wilt weergeven en typ deze formule. De bovenstaande SUMIF-formule zoekt naar de waarde 'Zuid' in kolom B2:B19 en telt het overeenkomstige verkoopbedrag op in kolom C2:C19. Geeft vervolgens het resultaat weer in cel E7.
U kunt ook verwijzen naar de cel die de tekstvoorwaarde bevat in plaats van rechtstreeks de tekst in het criteriaargument te gebruiken:
=SOM.ALS(B2:B19,E6,C2:C19)
Dat is het.