Structuurwijzigingen: kolommen en rijen

In dit blog bespreken we de structuurwijzigingen die plaatsvinden als je kolommen of rijen toevoegt of verwijdert in Excel en welke invloed dergelijke wijzigingen hebben op je spreadsheet.

Iedereen met enig basisbegrip van Excel weet dat een spreadsheet uit kolommen en rijen bestaat. Kolommen en rijen maken het makkelijk om een bepaalde cel te vinden, omdat de locatie in feite door een X en een Y coördinaat bepaald wordt.

Wat gebeurt er als je rijen of kolommen verwijdert?

Wanneer je een rij in Excel verwijdert, schuiven alle cellen één rij naar boven. Op dezelfde manier schuiven alle cellen van een kolom één kolom naar links als je een kolom verwijdert. Deze simpele acties kunnen ertoe leiden dat honderden of zelfs duizenden cellen van locatie veranderen. De cellen van de verwijderde rij of kolom bestaan nu niet meer, en dat heeft invloed op eventuele verwijzingen naar die rij of kolom (de beruchte Excel foutmelding #REF). De meeste formules worden “goed” bijgewerkt wbij dergelijke structuurwijzigingen, omdat ze de verschoven cel naar de nieuwe locatie “volgen”, maar sommige formules (zoals VERT.ZOEKEN, of VLOOKUP) worden niet automatisch bijgewerkt!

Zo kan een eenvoudige verwijdering van een enkele rij of kolom effect hebben op duizenden, zelfs honderdduizenden cellen.

Een simpel voorbeeld

Hieronder zie je een eenvoudige tabel met formules en data. Let vooral op kolom F en G (de cellen in kolom G bevatten een VLOOKUP functie):

Tabel 1.1: VERT.ZOEKEN (VLOOKUP) functie in kolom G

Als we vervolgens kolom B verwijderen, dan gebeurt er dit:

Tabel 1.2: VERT.ZOEKEN (VLOOKUP) functie in kolom F na verwijdering kolom B

Zoals je ziet staan de gegevens nu niet alleen op een andere plek, maar ook de verwijzingen naar en vanuit de formules zijn veranderd. Zo is de kolomindex (4) in de VERT.ZOEKEN functie niet aangepast! De functie zoekt nu in de vernieuwde tabel en de nieuwe kolomverwijzing (voor hetzelfde resultaat) zou 3 moeten zijn. Kijk eens naar de onderstaande afbeeldingen met de resultaten van de formules. Je ziet dat kolom F in tabel 1.3 (E in tabel 1.4) dezelfde waarde teruggeeft met of zonder de kolomverwijdering, terwijl de VERT.ZOEKEN (kolom G in tabel 1.3, dan kolom F in tabel 1.4) een ander resultaat heeft:

Tabel 1.3: VERT.ZOEKEN (VLOOKUP) functie in kolom G

Tabel 1.4: VERT.ZOEKEN (VLOOKUP) functie in kolom F na verwijdering kolom B

Hoe ga je om met structuurwijzigingen?

We hebben laten zien wat er mis kan gaan in Excel zelf, maar veel professionals gebruiken ook externe tools (zoals vergelijkingstools) voor zaken als logging of versiemanagement. Hier loop je weer tegen een ander probleem aan, want hoeveel (structuur)wijzigingen hebben er nou werkelijk plaatsgevonden? In het bovenstaande voorbeeld (tabel 1.1), was kolom B (met 8 gevulde cellen) verwijderd (tabel 1.2). Dit resulteerde in een verschuiving, waarbij 40 individuele cellen naar links zijn verschoven. En daar bleef het niet bij. Ook de verwijzingen in 3 SOM-formules, 7 directe verwijzingen en 7 VERT.ZOEKEN functies zijn gewijzigd. Dit betekent dat een gemiddelde vergelijkingstool in totaal 57 individuele veranderingen zal rapporteren voor dit kleine tabelletje. Als je dit losjes doortrekt kom je al snel tot de conclusie dat één simpele kolominvoeging of -verwijdering in een “normaal’ Excel model gemakkelijk kan resulteren in duizenden individuele wijzigingen, terwijl er in werkelijkheid maar één plaatsvond: er werd een kolom verwijderd…

PerfectXL Compare

PerfectXL Compare vergelijkt twee verschillende spreadsheets met elkaar en rapporteert de verschillen. Alternatieve tools rapporteren ook kolom- en rijwijzigingen, maar PerfectXL Compare houdt daarnaast werkbladaanpassingen bij en is vele malen gebruiksvriendelijker als het gaat om complexe modellen. Onze tool zal, heel eenvoudig, het invoegen of verwijderen van 1 kolom volgen wanneer 1 kolom wordt ingevoegd of verwijderd, zodat je daadwerkelijk iets kunt met de resultaten van de vergelijking. Niemand kan iets beginnen met 40.000 structuurwijzigingen, en daarom beperken we ons tot de kern: er is 1 ding gewijzigd. Lees meer over PerfectXL Compare of over ons onderzoek naar de andere vergelijkingstools op de markt.