Tagarchief: Wim de Groot

Subtotaal in Excel berekenen

Je kunt cellen filteren, en van die gefilterde selectie een subtotaal in Excel berekenen. Hoe dat in z’n werk gaat leest u hieronder. Afkomstig uit mijn boek Gegevens verwerken in Excel.

Als je een kolom in een tabel optelt met de functie SOM en je gaat die tabel filteren, dan zal de uitkomst van deze optelling altijd gelijk blijven: je ziet altijd het totaal van alle rijen, ook de niet-gefilterde getallen worden meegeteld. Wil je alleen de getallen optellen die gefilterd zijn, gebruik dan de functie SUBTOTAAL. Deze functie berekent een subtotaal in een lijst of database.


Syntaxis van SUBTOTAAL

=SUBTOTAAL(functiegetal; gebied)

Als functiegetal geef je een getal van 1 tot en met 11 op, of van 101 tot en met 111, dat aangeeft welke functie moet worden gebruikt, zie de volgende tabel; na de puntkomma verwijs je naar een gebied van cellen.


Deze functie is vooral praktisch als je een lijst gaat filteren. Deze laat het totaal zien van alleen de gefilterde rijen (of een andere berekening).

De tabel in dit voorbeeld heeft 300 rijen. Typ twee rijen lager, dus in C302, de formule:

=SUBTOTAAL(9;C1:C300)

Het functiegetal is 9, dus de functie SOM wordt gebruikt. Deze formule telt het aantal verkochte artikelen op.

Schakel vervolgens het filter in. Klik op de pijlknop bij Land; er gaat een menu open met alle landen. Schakel (Alles selecteren) uit, schakel bijvoorbeeld België in en klik op OK; je ziet hierna alleen de gegevens van België. De formule onderaan telt nu de aantallen van België op. Excel aan het werk Gegevens verwerken in Excel

Klik je bovendien op de pijlknop bij Product en kies je van de fietstypen alleen VTT (vélo tout terrain, mountain bike), dan zie je alleen de VTT’s die verkocht zijn in België. De formule onderaan telt dan het aantal VTT’s in België op.

Schakel je in het menu Land bijvoorbeeld Frankrijk en Spanje in, dan geeft de formule de totalen van deze beide landen.

De 9 vooraan in de formule geeft aan dat je de gefilterde gegevens wilt optellen. Kies je daar een 1, dan krijg je het gemiddelde van de gefilterde records, met een 4 zie je de grootste waarde en met 5 de kleinste waarde daarvan.

De functie SUBTOTAAL is bedoeld voor kolommen met gegevens en voor verticale gebieden, want het wegfilteren of verbergen van rijen is van invloed op het subtotaal.

Met de functie SUBTOTAAL tel je alleen de getallen op van het gefilterde land.

Meer tips en trucs van Wim de Groot? Op dit blog zijn er veel te vinden. Al zijn Excel-tips, plus interviews met Wim vind je HIER.

Gegevens verwerken in Excel

Leuke tip? Je vindt er nog veel meer in het boek Gegevens verwerken in Excel van Wim De Groot, want gegevens importeren in Excel vanuit Word is slechts een van de vele mogelijkheden. Met Excel kun je behalve rekenen ook gegevens bijhouden. Die kunnen in een eenvoudige tabel staan of in een professionele database. Vervolgens wil je die gegevens op een bepaalde manier op een rij zetten, verbanden zien, ze analyseren en conclusies trekken. Dat leer je in dit boek. Je hoeft hiervoor niet veel ervaring met Excel te hebben. De voorbeelden in dit boek worden helder uiteengezet, zodat je ze kunt aanpassen voor je eigen werk. Kortom: prima leesvoer voor een ieder die ook eens wat andere mogelijkheden van ‘s werelds meest bekende spreadsheet wil leren kennen.

Random getallen in Excel

Zo af en toe heb je eens wat random getallen in Excel nodig. Is geen enkel probleem, hieronder lees je hoe dat in z’n werk gaat. Afkomstig uit mijn boek Excel aan het werk, functies voor wiskunde en statistiek.

Soms heb je willekeurige cijfers nodig. Voor een proefberekening, voor zomaar wat data of voor kansberekening. Dan kun je steeds zelf getallen bedenken, maar je kunt ze ook door Excel laten invoeren. Hiervoor gebruik je de functie ASELECT, ASELECTTUSSEN of ASELECT.MATRIX.

Lees verder Random getallen in Excel

Grootste waarden optellen in Excel

Je kunt eenvoudig de tien grootste waarden optellen in Excel, een klusje dat best eens van pas komt zo af en toe. Onderstaande tip is afkomstig uit mijn boek Excel aan het werk, functies voor wiskunde en statistiek.

De tien grootste waarden optellen in Excel is snel te realiseren en komt best van pas. Stel, je hebt verkoopcijfers (of scores) en je wilt daar de tien grootste waarden uit halen en die optellen. Dat gaat niet met SOM.ALS, maar wel met SOMPRODUCT.

In het volgende voorbeeld staan de waarden onder elkaar in B2 tot en met B24. Je formule is:

=SOMPRODUCT(GROOTSTE(B2:B24;RIJ(1:10)))

Deze zoekt automatisch de tien grootste waarden uit het gebied en telt die op.

Hier verwijst RIJ niet naar de gewone rijnummers in het werkblad, maar in het geheugen wordt een eigen tabel aangemaakt en daarvan worden de rijnummers gebruikt. Lees verder Grootste waarden optellen in Excel

Zelf een klok maken in Excel

Een klok maken in Excel is in een handomdraai te realiseren. Zomaar een van de aardige dingen die je met dit spreadsheetprogramma kunt doen. Onderstaand fragment is afkomstig uit Het Complete Boek Office 2019. Het boek is  ook geschikt voor Office 365.

Wilt u weten hoe laat het is, dan gebruikt u de functie NU. Eerder in dit hoofdstuk hebt u gelezen dat u de datum van vandaag krijgt met:

=VANDAAG()

De functie NU toont u hoe laat het op dit moment is.


Opbouw van de functie NU

=NU()

U typt wel haakjes achter deze functie, maar NU werkt zonder argumenten, dus tussen de haakjes staat niets.
De uitkomst is het moment van invoeren; u krijgt de datum met uren en minuten. Druk op de functietoets F9 om de tijd bij te werken.


Lees verder Zelf een klok maken in Excel

Afronden naar een veelvoud in Excel

Afronden van getallen kan op allerlei manieren, en dus is afronden naar een veelvoud in Excel slechts een van de opties. Onderstaand fragment is afkomstig uit mijn boek Excel aan het werk, functies voor wiskunde en statistiek.

Bij de functies voor afronden tot nu toe laat je Excel naar boven of beneden afronden, waarbij je zelf bepaalt hoe groot de stap is. Met AFRONDEN. BOVEN.WISK neemt Excel het volgende hogere veelvoud en met AFRONDEN.BENEDEN.WISK ga je naar het lagere veelvoud. Maar je kunt ook door Excel laten opzoeken of het omhoog of omlaag moet; de volgende functie neemt automatisch het veelvoud dat het dichtstbij ligt.

De functie AFRONDEN.N.VEELVOUD

Met de functie AFRONDEN.N.VEELVOUD gaat Excel naar het dichtstbij liggende veelvoud.


Syntaxis van AFRONDEN.N.VEELVOUD

=AFRONDEN.N.VEELVOUD(getal; factor)

Tussen de haakjes geef je de waarde op als getal of verwijzing; daarna geef je de factor op waarop je wilt afronden.


Resultaat: het getal wordt afgerond naar het dichtstbij gelegen veelvoud van het getal dat je opgeeft.

De uitkomst is bijvoorbeeld 15 met:

=AFRONDEN.N.VEELVOUD(16,4;3)

Want 15 is het veelvoud van 3 dat het dichtst bij 16,4 ligt. Je krijgt 18 met de formule:

=AFRONDEN.N.VEELVOUD(16,6;3)

Want 18 ligt dichter bij 16,6 dan 15. Bereken je over een bedrag in B2 een korting van vijf procent en wil je dat afronden op een veelvoud van vijf euro, dan neem je de formule:

=AFRONDEN.N.VEELVOUD(B2*(1-5%);5)

Staat er 130 in B2, dan is 5% daarvan 6,50. Dat afgetrokken van 130 is 123,50 en afgerond naar het dichtstbijzijnde veelvoud van 5 maakt 125.

Wil je bedragen afronden op vijf cent, dan is je formule:

=AFRONDEN.N.VEELVOUD(B2;0,05)

Met bijvoorbeeld 13,67 in B2 krijg je 13,65. Dit heeft hetzelfde effect als de formule uit de paragraaf Afronden op vijf cent (pagina 46), namelijk:

=AFRONDEN(B2/5;2)*5

Werk je met negatieve getallen, dan moet de factor ook negatief zijn.

Help! Ik zie #GETAL!

Bij de functie AFRONDEN.N.VEELVOUD moeten het getal dat je bewerkt en de factor waarop je afrondt, beide positief of beide negatief zijn. Doe je dat niet, dan krijg je de melding #GETAL!, zoals met de volgende formule:

=AFRONDEN.N.VEELVOUD(16,4;-3)

Afronden naar veelvoud van een uur

Je kunt in Excel ook een tijdsduur afronden. In feite is dit ook afronden naar een veelvoud, namelijk van een uur, een kwartier enzovoort.

Goed om te weten: een dag heeft in Excel de waarde 1, dus een uur is 1/24.

Je typt bijvoorbeeld in C3 het tijdstip waarop je begint, in D3 het tijdstip waarop je stopt en je trekt die in E3 van elkaar af; je ziet je gewerkte uren. Wil je deze gewerkte uren afronden op hele uren, typ dan in F3 de formule:

=AFRONDEN.N.VEELVOUD(E3;1/24)

Deze rondt de tijdsduur in E3 af op een heel uur (dat is 1/24).

Je mag een uur ook als “1:00” opgeven, dus het volgende kan ook:

=AFRONDEN.N.VEELVOUD(E3;”1:00″)

Voor afronden op het dichtstbijzijnde kwartier heb je dezelfde opties, namelijk 1/24 delen door 4 of een kwartier als “0:15” opgeven. Dus als de tijdsduur in E3 staat, kun je kiezen uit:

=AFRONDEN.N.VEELVOUD(E3;1/24/4)

=AFRONDEN.N.VEELVOUD(E3;”0:15″)

Meer tips en trucs van Wim de Groot? Op dit blog zijn er veel te vinden. Al zijn Excel-tips, plus interviews met Wim vind je HIER.

Wiskunde en statistiek in Excel

Nog veel meer weten over het gemiddelde berekenen in Excel? Lees dan mijn boek Excel aan het werk, functies voor wiskunde en statistiek. Daarin worden honderd rekenfuncties van Excel besproken op het gebied van wiskunde en statistiek. Die worden uitgelegd aan de hand van voorbeelden. Je leert hoe je een functie gebruikt, zodat je die kunt toepassen in je eigen werk, studie of hobby.

Nadat je hebt gezien hoe je een formule samenstelt, ga je de diepte in. Tot de behandelde onderwerpen behoren:

  • optellen, tellen en gemiddelde berekenen
  • totaal, aantal en gemiddelde voor een beperkte groep berekenen en op meer criteria
  • afronden in allerlei stappen
  • werken met breuken en logaritme
  • willekeurige getallen trekken
  • kansen en standaardafwijking berekenen
  • getallen in groepen verdelen met statistische functies
  • correlatie, trend en regressie tussen getallenreeksen vinden

Dit boek is te gebruiken met Excel 2010 tot en met 2019 (en Microsoft 365).

Je kunt negentig gratis oefenbestanden bij dit boek downloaden. In de ene helft staan alleen de gegevens zodat je zelf aan de slag kunt, in de andere helft van de bestanden zijn de voorbeelden helemaal uitgewerkt.

 

Breuken in Excel

Je kunt ook rekenen met breuken in Excel, wat vooral voor scholieren zo af en toe best praktisch kan zijn natuurlijk. Onderstaand artikel is een destillaat uit mijn boek mijn boek Excel aan het werk, functies voor wiskunde en statistiek.

Werken met breuken in Excel is geen probleem, het programma beschikt over diverse functies speciaal voor die tak van sport bedoeld. Zo is er bijvoorbeeld de functie GGD. Het is gebruikelijk om een breuk zo klein mogelijk te schrijven. Dit heet vereenvoudigen. Om een breuk te kunnen vereenvoudigen zoek je naar de grootste gemene deler van de teller en de noemer, ofwel de grootste gemeenschappelijke deler. De afkorting hiervoor is ggd. De deler is het getal waardoor je deelt.

Lees verder Breuken in Excel

De functie GROOTSTE in Excel

De functie GROOTSTE in Excel is slechts een van de vele functies die dit ultieme spreadsheetprogramma van Microsoft aan boord heeft. In mijn boek Excel aan het werk, functies voor wiskunde en statistiekvind je er vele beschreven. Hieronder gaan we op zoek naar de grootste!


Syntaxis van GROOTSTE

=GROOTSTE(gebied; rangnummer)

Je mag slechts één gebied opgeven; het rangnummer is de positie in de ranglijst van de getallen, van groot naar klein gezien.

Resultaat: een getal dat op de opgegeven plaats in de ranglijst staat.


Je wilt bijvoorbeeld een lijst zien met de toptien van de bestverkopende producten, waarvan de aantallen in kolom B staan. Voor het product met het grootste aantal is je formule:

=GROOTSTE(B2:B1000;1)

De uitkomst hiervan is gelijk aan:

=MAX(B2:B1000)

Lees verder De functie GROOTSTE in Excel

Telefoonnummers invoeren in Excel

Bij het invoeren van telefoonnummers in Excel kan de eerste nul wegvallen. In deze blogpost kun je lezen hoe je dit oplost. Deze blogpost ‘Telefoonnummers invoeren in Excel’ is afkomstig uit mijn boek Gegevens verwerken in Excel.

Als je telefoonnummers invoert in Excel, kan de eerste nul wegvallen. Je typt bijvoorbeeld 0345473392 en er blijft 345473392 over. Als je de telefoonnummers invoert met een streepje (of spatie) tussen netnummer en abonneenummer, doet zich dit niet voor.

Wil je de telefoonnummers toch invoeren zonder een streepje of spatie, dan voorkom je het wegvallen van de nul als je de kolom opmaakt voordat je gegevens invoert. Dat gaat als volgt. Selecteer de kolom voor de telefoonnummers en klik met de rechtermuisknop op zijn kolomletter; dit opent een menu. Kies Celeigenschappen; het venster Celeigenschappen gaat open. Kies de optie Speciaal en kies onder Type de optie Telefoonnummer. Voer hierna de telefoonnummers in.

Voor lezers in België: als je op Speciaal klikt en het vak onder Type is leeg, dan is onder in dit venster onder Locatie de optie Nederlands (België) ingesteld. Kies je met de keuzelijst onder Locatie voor Nederlands (standaard), dan krijg je wel de genoemde keuzes.

De ‘truc’ is ook beschikbaar in Excel voor iOS en iPadOS, alleen tik je daar op de opmaakknop en dan Speciaal om de gewenste optie terug te vinden.

Lees verder Telefoonnummers invoeren in Excel

De functie ‘Zoeken’ in Excel

Zoeken in Excel is erg makkelijk én veelzijdig dankzij de speciaal daarvoor bedoelde functie. In het boek Gegevens verwerken in Excel van Wim de Groot lees je er – naast vele andere zaken – alles over. Hieronder een voorbeeld van wat je met de functie kunt doen.

Zoeken naar waarden is een van de vele voorbeelden die mogelijk is dankzij de functie Zoeken in Excel. Het programma heeft een aantal functies waarmee je in lijsten en tabellen kunt zoeken naar een getal, een datum of tekst.

De functie ZOEKEN

De functie ZOEKEN geeft een waarde uit maximaal één kolom of één rij.


Syntaxis van ZOEKEN

=ZOEKEN(zoekwaarde; verwijzing naar zoekgebied; eventueel verwijzing naar resultaatgebied)

Geef de zoekwaarde op als een getal, een woord of een celverwijzing; dan geef je het zoekgebied op waaruit de waarde wordt opgehaald; geef je een tweede gebied op, dan zoekt de functie naar de waarde in het eerste gebied en haalt deze de bijbehorende waarde uit het tweede gebied (dit tweede gebied moet even lang zijn als het eerste gebied).

Resultaat: de overeenkomstige waarde.


Lees verder De functie ‘Zoeken’ in Excel

Formule via het dialoogvenster in Excel

Stel een formule via het dialoogvenster in Excel op, zo maak je in een handomdraai de meest complexe formules zonder steeds weer op zoek te moeten naar de correcte syntaxis. Onderstaande tip is afkomstig uit het boek Gegevens verwerken in Excel van Wim de Groot.

Klik je op het pijltje naast AutoSom, dan heb je andere vaak gebruikte functies onder handbereik. Je kunt daar het Gemiddelde kiezen, het Aantal getallen, Max (het grootste getal) en Min (het kleinste getal).

Excel heeft echter veel meer rekenfuncties. Klik op de tab Formules; je ziet daar een aantal knoppen in de vorm van boeken; deze vormen de zogeheten Functiebibliotheek. Achter deze knoppen zijn de functies in groepen ondergebracht. Klik je op een van deze knoppen, dan verschijnt er een menu met de rekenfuncties die in die groep zijn ondergebracht. Kies daaruit een functie en het dialoogvenster Functieargumenten verschijnt. Daarmee stel je de formule samen door enkele invoervakjes in te vullen; zo bouw je de formule via het dialoogvenster in Excel stap voor stap op. Lees verder Formule via het dialoogvenster in Excel