3 nieuwe gereedschappen in Excel

Lees ook: Nieuwe mogelijkheden in Excel (inclusief 6 Power Pivot tips)

3 nieuwe gereedschappen in Excel

Maar wat verstaan we eigenlijk onder ‘nieuw’? 

Op dat punt is Excel niet meer zo helder als het vroeger was. Sinds Office in een abonnement jasje is gestopt, vliegen de updates je om de oren. Het is nog niet zoals bij Power BI, waar iedere twee weken een nieuwe versie verschijnt, maar zeker meer dan eens in de anderhalf jaar. Dat betekent dat als jouw versie van Excel niet is bijgewerkt, of wanneer het een bedrijfsversie betreft, je deze vernieuwingen mogelijk niet aantreft. O, ja, en nieuw hoeft niet direct nieuw te betekenen. Naast vernieuwingen worden ook veel bestaande Excel functionaliteiten verder verbeterd en uitgebreid. 

1. Nieuwe (Logische) functies

Met Power Pivot kwamen de DAX functies in Excel. Gek genoeg zijn er een fors aantal DAX functies, die niet in Excel als functie beschikbaar zijn. Gelukkig hebben een aantal DAX functies de oversteek gemaakt. Een aantal nieuwe DAX functies zijn omgebouwd naar Excel functies. Hierbij zitten een aantal logische functies. Dat is voor mij erg fijn, omdat logische functies de hoekstenen zijn van mijn Excel-modellen. Door hun logisch vermogen, maken zij de door mij voor-gedefinieerde keuzes. Daarmee zorgen zij ervoor dat Excel voor mij werkt en ik niet voor Excel. 

Laten we een van die nieuwe functies nader bekijken. De functie ALS.VOORWAARDEN vervangt de populaire, maar lastig te doorgronden geneste (lees: meerdere in elkaar verweven) ALS functies. In afbeelding 1 zie je de functie uitgewerkt. 

Voordat ik begin vertel ik, om je geheugen op te frissen, hoe iedere logische functie in Excel werkt. De functie heeft een logische_test argument. Nadat Excel het argument heeft berekend, geeft het een van de twee resultaten terug aan de functie. Wordt de test positief geëvalueerd, dan wordt WAAR teruggeven. In alle andere gevallen wordt ONWAAR teruggegeven. Afhankelijk van het resultaat wordt of argument Waarde_indien_Waar, of Waarde_indien_Onwaar uitgevoerd en getoond in Excel. 

De functie ALS.VOORWAARDEN kent alleen het argument Waarde indien Waar. Maar je hebt wel 127 mogelijkheden om verschillende Logische testen in te voeren. De eerste Logische test, die WAAR teruggeeft, zorgt ervoor dat de waarde in het bijbehorende argument Waarde indien Waar wordt getoond in Excel. 

Lees je de functie in afbeelding 1, dan zie je dat de Logische_test1 ONWAAR teruggeeft. Excel gaat daarom door met Logische_test2. Deze wordt wel als WAAR geëvalueerd, dus het resultaat van Waarde_indien_waar2 (Yes) wordt teruggegeven. Zijn geen van de ingevulde logische testen WAAR, dan geeft de functie de melding #N/B terug. De functie heeft daarmee veel weg van de nieuwe functie SCHAKELEN, maar daar zijn de argumenten net omgedraaid. Je krijgt maar een Logische test, maar 127 meerdere mogelijke Waarde indien Waar.

2. Power Query vernieuwingen

In de Excel versies 2010 en 2013 was Power Query nog een invoegtoepassing, maar nu is het een volwaardig lid van de Excel familie geworden! Je vindt in Excel 2016 Power Query terug in het tabblad Gegevens, Ophalen en transformeren. Uiteindelijk zal het de sectie Externe gegevens ophalen helemaal gaan vervangen. O ja, had ik je al verteld dat Power Query jouw werken in Excel geheel gaat veranderen? Waar ik in Excel eindeloos complexe berekeningen nodig heb om mijn data aan te passen, doe ik hetzelfde met een paar muis klikken in Power Query.

Een van de vernieuwingen In Power Query is de functionaliteit om parameters te gebruiken. In afbeelding 2 zie je een tabel, genaamd Jaren. Deze tabel heb ik naar het Query-editor venster gehaald door middel van de knop Uit tabel (afbeelding 3). Vervolgens heb ik de functionaliteit om een nieuwe parameter te maken gebruikt, door te klikken in het tabblad Start, sectie Parameters, de knop Parameters beheren op Nieuwe Parameter.  
 

Ik heb verder het venster Parameters ingevuld, zoals je ziet in afbeelding 4. Je krijgt nu een nieuwe query, die het ingevulde jaar bevat. In de query Jaren ga je nu de parameter Jaar gebruiken. In die query kies je in het tabblad Kolom toevoegen voor de knop Aangepaste kolom. In afbeelding 5 zie je hoe je het venster moet invullen. Daarna heb je nu een extra kolom met het jaar daarin, die je hebt ingevuld in de Query Parameterjaar. 

Om het model af te ronden voeg je nog een Voorwaardelijke kolom toe. Deze vindt je naast Aangepaste kolom. De optie Voorwaardelijke kolom werkt als een logische ALS functie. Vul het venster in zoals je ziet in afbeelding 6. Nadat je op de OK knop hebt geklikt en je ziet het resultaat in de query-editor.

3. Grafieken

Excel kent een aantal nieuwe grafieken type. Zo kan je nu bijvoorbeeld Waterval en Trechter grafieken maken. Let op, niet alle nieuwe grafieken zijn beschikbaar als je deze vanuit de functionaliteit Draaigrafiek (dus vanuit een draaitabel) gebruikt. Voor deze blog maak ik de Treemap grafiek. Dit grafiektype werd ineens erg populair toen Steve Jobs deze in een van zijn fameuze presentaties gebruikte. 

In afbeelding 8 zie je een tabel met het olieverbruik in zes meest verbruikende landen.  Dat is mooi hoor ik je zeggen, maar maak je er een Treemap grafiek van, dan komen de verhoudingen beter in zicht. Zorg dat de actieve cel in het bereik van de tabel staat en kies voor het tabblad Invoegen, sectie Grafieken, de knop Aanbevolen grafieken. Selecteer het tabblad Alle grafieken en zoek de Treemap op (afbeelding 9). Klik op OK en de grafiek wordt in Excel geplaatst. Ik heb vervolgens de grafiek titel aangepast en gebruik gemaakt van de smart knoppen om de legenda niet te laten zien (afbeelding 10). 

Wat deze grafiek erg nuttig maakt is het vermogen om snel verhoudingen te laten zien. Het is je misschien niet direct opgevallen toe je de tabel zag, maar uit de Treemap blijkt dat de United States meer olie verbruiken, dan de vijf andere grote consumenten China, Japan, Rusland, Duitsland en India samen. 

Henk Vlootman geeft sinds 1994 Excel en Power BI trainingen aan managers over de gehele wereld. Vanaf 2013 ontving Henk jaarlijks de Microsoft MVP (Most Valuable Professional) award. In 2016 werd de MVP award voor de gebieden Excel en Data platform toegekend. Die laatste erkenning kreeg hij vanwege zijn werk met de Power BI gereedschappen. Henk is de auteur van “Excelmodellen voor financiële economische informatie” en “ExcelLeren, de Vlootman methode in de praktijk” en medeauteur van het “Praktijkboek Powerpivot in Excel” en “Handboek Power pivot”. Tijdens Excel en Power BI congressen en conferenties is hij een gewaardeerd spreker. Henk is voorzitter van de stichting Power BI gebruikersgroep Nederland.