Veel mensen met een kwantitatieve achtergrond worden liever niet geassocieerd met Excel. Een gemiste kans! Want Excel heeft ook voor data-experts die het gros van hun werk in R of Python doen wel degelijk wat te bieden. In dit artikel helpen we data & analytics specialisten en beginnende analisten op weg in MS-Excel.

Als bonusmateriaal leggen we ook nog uit hoe je Python kan verbinden met Excel zodat je een spreadsheet kan verrijken met mooi scriptwerk. In dit artikel lees je het allemaal.

Waarom halen data & analytics specialisten vaak hun neus op voor Excel?

Wie bedenkt het in vredesnaam om bijvoorbeeld de input syntax per gebruikerstaal te vertalen? Er is een reden dat data & analytics specialisten vaak hun neus op halen voor Microsoft Excel. In Excel werk je te veel met de muis en het biedt geen mooie IDE die je input in “MATRIX”-achtige stijlen kleurt. Verder bevat Excel een aantal totaal idiote manifestaties van digibetisme. “vlookup()” is een prima korte functienaam, vanwaar de noodzaak om dit te vertalen tot “vert.zoeken()” ?

Excel een beunmedium?

Wat wellicht ook een rol speelt is een identiteitskwestie. Mensen met een business achtergrond kunnen vaak niet programmeren en hebben al doende geleerd te beunen in Excel. Mensen met een meer kwantitatieve achtergrond krijgen al tijdens hun studie aangeleerd om zoveel mogelijk te scripten en mijden liever omslachtige GUI’s die helpen onder het schrijven van een deugdelijk script uit te komen.

Ze worden dan ook liever niet geassocieerd met Excel als beunmedium en mensen die niet kunnen programmeren. Zo kan het dus zijn dat mensen die erg handig zijn met Python of Julia geen benul hebben van de meest basale Excel functionaliteiten. Dit is een gemiste kans want Excel heeft ook voor data-experts die het gros van hun werk in R of Python doen wel degelijk wat te bieden. Zet daarom even een hashtag voor de import statement van je snobmodule en lees in dit artikel wat er handig is aan Excel.

Waar Excel zich uitermate goed voor leent is het snel verwerken van gelimiteerde hoeveelheden tweedimensionale data en het snel naast elkaar zetten, patchen en filteren van data uit verschillende CSV en Excel bestanden. Met één druk op de knop zet je een tabel vervolgens om in een grafiek in de bedrijfsstijl die je zonder gêne kan tonen aan klanten.

Hoewel je dezelfde bewerkingen ook allemaal kan doen in Python, vraagt het toch altijd meer tijd om een scriptje op te tuigen en de data zo te kneden dat deze presentatie klaar is. Er is simpelweg geen betere editor voor 2-dimensionale data dan Excel. Dit is waarschijnlijk ook waarom Excel al zolang dezelfde lay-out heeft en waarom andere spreadsheet software of editing modules in statistische software dezelfde lay-out en vergelijkbare functies gebruiken. Datatabellen in Excel zijn gemakkelijk te kleuren, te sorteren en te transformeren. Goed, je kan maximaal iets van 17 miljoen datapunten kwijt in een Excel sheet, en dat is niet superveel, maar voor het analyseren van output of beperkte data-analyses is dit ruim voldoende. Een bijkomend voordeel is dat veel collega’s wel een Excel sheet kunnen openen maar moeite hebben met een zipfile vol met input CSV’s en een Python script.

In deze blogpost

In dit artikel helpen we data & analytics specialisten en beginnende analisten op weg in MS-Excel. Als bonusmateriaal leggen we ook nog uit hoe je Python kan verbinden met Excel zodat je een spreadsheet kan verrijken met mooi scriptwerk. Hieronder lees je het allemaal!

Creatief combineren van Excel functies

De basis van Excel is het creatief toepassen van functies en die vervolgens te extrapoleren naar de rest van je sheet. Voor de mensen die weinig met Excel hebben gewerkt geven we hier een overzicht van handige functies. Klik hier om te lezen hoe je deze functies vervolgens kunt extrapoleren naar de rest van je sheet.

1.    Gebruiken van combinaties van vlookup en match

Stel je wil afhankelijk van een landscode en een specifieke maand data uit een tabel halen. Dan kan je met combinaties van vlookup() en match() snel data opvragen uit een datatabel op een andere sheet. Zie in deze video hoe je vlookup() en match() kan combineren. Deze combinatie heb ik vaak gebruikt voor het bouwen van datatabellen voor paneldata regressieanalyses. Vergeet geen dollartekens te gebruiken om de cel referenties de juiste richting te locken wanneer je de formule wil extrapoleren naar de rest van je nieuwe datatabel.

2.    Gebruiken van averageifs() voor frequentie transformaties.

Stel je hebt een CSV-bestand met in de eerste kolom landnamen, in de tweede kolom dagelijkse datumlabels en in de derde kolom prijsdata. Dan kunnen we met de averageifs() functie in een nieuwe tabel met maandelijkse data de maand gemiddelden geven voor deze prijs data. Zie in deze video hoe je de averageifs functie gebruikt. Mocht je meer criteria willen kan dat ook! Let wel op, deze formule is een klein beetje brak, hij vraagt namelijk disproportioneel veel rekenkracht. Bij een kleine tabel merk je het niet, maar zeker bij iets grotere hoeveelheden data maakt deze formule je sheet erg traag.

3.    Interpoleren van missende data

Met functies als if() ifna() of iferror() kan je missende data interpoleren die je bijvoorbeeld met één van de bovenstaande formules geprobeerd hebt op te vragen. Dit kan met een lege cel waarde “” zijn, 0, of een andere formule die alsnog een waarde aan de cel geeft. Bijvoorbeeld het gemiddelde van de omliggende waarden doormiddel van “average()” of een lookup formule die data ophaalt uit een andere datatabel. De mogelijkheden zijn eindeloos!

4.    Cijfer uit tekstcel halen

Soms komen datumlabels in een naar format, bijv: 2022M1. Met combinaties van left() en right() kan je hier bijvoorbeeld het jaartal uithalen. Door dit te combineren met numbervalue() ziet excel 2022 gewoon als nummer en niet als tekst. In dit geval zou een dergelijke formule er zo uitzien: =numbervalue(left(celverwijzing,4)).

5.    Lifehack, combinatie van Excel en kladblok

Soms gebeurt het dat je data downloadt en dat waarden verhuld zijn als stringwaarden. Wanneer er alleen cijfers in een tekstcel staan geeft Excel de optie “convert to number”. Met grote hoeveelheden data moet je dit echter niet proberen want op de één of andere manier neemt Excel hiervoor een hele omslachtige route die veel rekenkracht en tijd kost. Je bent dan sneller door een kolom naar kladblok te kopiëren en de data terug te plakken in een nieuwe kolom. Via deze route zijn er ook nog een heleboel andere zaken mogelijk. Want zodra data in kladblok staat kan je met de “find en replace” optie een heleboel bewerkingen uitvoeren. Enkele voorbeelden hiervan zijn punten vervangen met komma’s of tekst en waarden scheiden door het vervangen van spaties met tabs.

Wanneer je eenmaal bekend bent met deze basale functies kan je beginnen met beunen. Er zijn nog een heleboel andere mooie functies waarmee je oneindig veel lol kan trappen. Bijvoorbeeld de indirect() functie om afhankelijk van andere cellen data uit verschillende Excel sheets of bronbestanden op te halen. Zeker in combinatie met de voorgaande formules kunnen dit onnavolgbare netwerken van getallen en Excel bestanden worden.

Naar het product van al dit beunwerk refereren we als de Excelhell. Het in elkaar beunen van veel verschillende formules tot een complexe brij is voor veel kantoorslaven met een beetje logisch denkvermogen een ware hobby. Er is zelfs iemand die het voor elkaar heeft gekregen om louter met Excel formules een 3d game engine in elkaar te bouwen. Wie een beetje rondkijkt in organisaties kan ook niet anders concluderen dan dat de wereld aan elkaar hangt met ondoorzichtige en half niet-functionerende Excel sheets. Waarschijnlijk wordt zelfs het Amerikaans kernwapenprogramma op een dergelijke manier aangestuurd. Geruststellende gedachte, niet?

Analysis-toolkit en solver

Analysis-toolkit en solver zijn twee add-ins die standaard worden meegeleverd met Excel om analyses te vergemakkelijken. Met de Analysis toolkit kan je gemakkelijk statistieken en regressievergelijkingen schatten. Met de Solver kan je met behulp van verschillende algoritmen een celwaarde minimaliseren of gelijkstellen aan een andere waarde doormiddel van het veranderen van een of meerdere inputcellen. Ik heb dit vaker gebruikt in analyses om een som van kwadraten te minimaliseren in vergelijkingen die niet overeenkomen met standaard regressievergelijkingen. Het mooie is dat de Solver add-in naast een simpele iteratieve benadering ook meer geavanceerde benaderingsalgoritmen ondersteund zoals gradiënt-decent met multistart mogelijkheden om voorbij lokale minima te komen. Op deze manier kan je behoorlijk geavanceerde minimalisaties oplossen. Hoewel je dergelijk geavanceerde analyses uiteraard efficiënter kan maken in Python, R of Matlab was voor mij een groot voordeel destijds dat mijn manager op deze manier beter in staat was mijn werk te begrijpen.

Python en Excel

Doormiddel van de win32com.client module voor Python kan je vanuit Python een live verbinding maken met Excel. Zie in de box hieronder hoe je de verbinding tot stand brengt:

import win32com.client
import pandas as pd

app = win32com.client.Dispatch(“Excel.Application”)
aes = app.ActiveSheet

De actieve Excel sheet met de naam Table1 bevat economische groei data voor eurozone landen voor verschillende perioden. Deze data kan ik vervolgens gemakkelijk inladen in een panda’s dataframe:

delta_gdp = aes.Range("Table1").Value

headers = aes.ListObjects("Table1").HeaderRowRange.Value[0]

df = pd.DataFrame(list(delta_gdp), columns=headers)

>>> df

       Country      Quarter      DeltaGDP

0      AUT          Q3           1.8    

1      ESP          Q3           3.8

2      NLD          Q3           3.2

3      BEL          Q3           1.2

4      IRL          Q3           6.7

…      …            …            … 

We kunnen nu bewerkingen op deze data uitvoeren zoals een gemiddelde voor elk kwartaal berekenen en de data weer terugschrijven naar Excel:

mean_growth = df.groupby('Quarter').DeltaGDP.mean()

aes.Range("D1:D4").Value = (('Eurozone_average_Q1',), ('Eurozone_average _Q2',), ('Eurozone_average _Q3',), ('Eurozone_average _Q4',))

aes.Range("E1:E4").Value = tuple((_,) for _ in mean_growth)

Omdat Python rechtstreek leest en schrijft in de data array storage van Excel kunnen dergelijke bewerkingen zo zonder problemen worden opgeschaald tot vele duizenden rijen. Op deze manier kan je bijvoorbeeld Excel gebruiken in combinatie met de scipy packages,

Excel: een tool om van te houden

Hoewel Excel soms het bloed onder onze nagels vandaan kan halen, is het ook een van de belangrijkste tools waarmee de kantoorwereld aan elkaar hangt. In deze blogpost hebben we enkel een heel klein deel van de functies uitgelicht voor de mensen die nooit naar het programma hebben omgekeken. Begrijp ons niet verkeerd, we proberen niemand over te halen om Python, R of Julia links te laten liggen voor Excel. Maar we geloven zeker dat een flinke dosis Excel kennis ook thuishoort in het repertoire van een data specialist. De negatieve associatie die veel hardcore data & analytics specialisten hebben met Excel is dan ook ongegrond.

Over DataJobs

DataJobs heeft als doel om mensen en organisaties die actief zijn in het veld van data & analytics de weg te wijzen in de arbeidsmarkt én direct met elkaar te verbinden. Om zo de nodige ruis, onduidelijkheid en irritatie uit de arbeidsmarkt te halen en recruitment organisaties grotendeels overbodig te maken.

Ben je ook actief in de data & analytics wereld en wil je onderdeel worden van de DataJobs community? Volg dan onze website www.datajobs.nl en onze snelgroeiende groep op LinkedIn.