Symptomen
- In de cellen van een spreadsheet kunnen (bedoeld of onbedoeld) dubbele waarden voorkomen, de gebruiker wil die kunnen opsporen.
- De gebruiker wil voorkomen dat bij invoer in een Excelmodel dubbele waarden worden ingevuld.
Remedie
Er zijn diverse manieren om dubbele waarden op te sporen (zie hieronder), maar soms zijn die erg bewerkelijk. Daarom is het vaak beter om juist te voorkomen dat er dubbele waarden in de cellen terecht kunnen komen.
Verdieping
Er zijn enkele methoden om dubbele waarden op te sporen in een model die in alle versies van Excel werken. Maak eerst een modelletje zoals in onderstaande figuur zodat u de methoden verderop kunt uitproberen.
De dubbele waarden zitten in dit modelletje in de kolom productomschrijving, maar in feite zijn de records (rijen) niet dubbel, omdat elk product een eigen, uniek productnummer heeft. Om verschillende redenen zou het toch nodig kunnen zijn deze gegevenstabel nader te onderzoeken op het voorkomen van dubbele waarden. Denk bijvoorbeeld aan mogelijke typefouten in de omschrijving in de lijst.
Lijst van unieke waarden
- Kopieer de kolomkop in cel B1 naar cel D1.
- Selecteer de gegevenstabel (één van de cellen erin mag ook, maar dan loopt u de kans dat Excel niet precies ziet waar de tabel begint en eindigt).
- Kies het geavanceerde filter via menu Data of tab Gegevens.
- Activeer de optie Kopiëren naar andere locatie.
- Geef bij Kopiëren naar als doellocatie op cel D1.
- Vink de optie Alleen unieke records aan
- Klik OK en in kolom D verschijnt een lijst van drie productomschrijvingen: Bureau, Tafel en Stoel.
De lijst wordt uiteindelijk gemaakt aan de hand van uw keuze voor de kolom, of liever de kolomkop die u heeft gekopieerd. Als één van de productomschrijvingen een typefout zou bevatten, zal die in het gekopieerde lijstje als unieke waarde opduiken.
Een nadeel van deze opsporingsmethode is wel dat u het steeds opnieuw moet doen, bijvoorbeeld als er producten aan de lijst worden toegevoegd. Maar het is wel een adequaat controlemiddel achteraf voor een groot model.
Filter
De methode hierboven kent ook een optie om niet de lijst van unieke waarde te kopiëren naar een nieuw bereik, maar om de lijst te filteren in de tabel zelf. Maar dat blijkt meer problemen op te leveren dan dat het antwoorden geeft, omdat Excel nu eenmaal ziet dat de dubbele waarden geen unieke records in de tabel zijn. Maar u kunt wel het reguliere filter gebruiken:
- Selecteer de gegevenstabel.
- Activeer via menu/tab het filter: zo verschijnen naast elke kolomkop keuzepijltjes.
- Klik op de keuzepijl achter de productomschrijving, en kies een van de producten (klik de optie Alles selecteren uit).
Dit resulteert direct in een lijst van alleen de records die voldoen aan uw keuzecriterium. Al in het keuzemenu van het filter zult u het trouwens zien als u een ‘afwijkend’ product in de lijst heeft staan. Als uw model moet bestaan uit alleen maar unieke omschrijvingen, is dit niet zo’n handige methode omdat de keuzelijst dan net zo lang moet zijn als de gegevenstabel zelf.
Formule
Met een telfunctie in een formule kunt u in een gegevenstabel bijhouden of een nieuwe toegevoegde rij een unieke waarde bevat of niet. De functie AANTALLEN.ALS in de onderstaande figuur kijkt of de waarde die in de cel staat al eerder in de kolom voorkomt. Is de uitkomst van de formule WAAR dan is het de eerste keer dat de waarde voorkomt, en voor de eerste gegevensrij is dat natuurlijk het geval. Door de formule naar beneden door te voeren, wordt dit voor elke rij opnieuw bekeken. Verderop komt de telling voor een zelfde celwaarde boven de 0 uit en is het resultaat dus ONWAAR. Oftewel: de waarde in dat record is niet uniek.
Andere versies
In de lintversies van Excel zijn er twee andere methoden beschikbaar met betrekking tot de opsporing van dubbele waarden.
Voorwaardelijke opmaak
Nieuw is de optie Dubbele waarden in de regels voor Voorwaardelijke opmaak. Ze highlighten ofwel de celwaarden die dubbel (of vaker) voorkomen, ofwel juist alleen de unieke waarden.
- Selecteer de gegevenstabel (zonder de kolomlabels) en kies op tab Start, groep Stijlen de knop Voorwaardelijke opmaak.
- Kies in het submenu van de knop Markeringsregels voor cellen, Dubbele waarden.
- In het dialoogvenster kunt u met behulp van het linker keuzevak aangeven of de dubbele of juist de unieke waarden moeten worden gemarkeerd.
In bovenstaande figuur wordt zo in de eerste kolom gesignaleerd dat een productnummer dubbel is gebruikt, en in de tweede kolom dat er één record is met een unieke waarde (de tekst ‘Kast’ komt maar één keer voor). Dat hoeft – zoals in dit geval – natuurlijk niet te betekenen dat het een ‘foutje’ is.
Verwijder duplicaten
Een optie die nog een stapje verder gaat is Verwijder duplicaten, en dat gaat Excel ook daadwerkelijk doen. Zorg dus eerst voor een back-up (kopie) van uw gegevens alvorens deze actie uit te voeren.
- Kopieer de gegevenstabel naar een leeg cellenbereik in het werkblad.
- Selecteer een van de gekopieerde cellen.
- Kies tab Gegevens, groep Hulpmiddelen voor gegevens, knop Duplicaten verwijderen.
- Zorg dat alleen kolom Productnummer op duplicaten wordt gecontroleerd (vinkje uitschakelen voor de Productomschrijving dus).
- Klik OK.
Op deze manier wordt de tweede rij met het (dubbele) Productnummer P003 verwijderd. Deze actie kunt u overigens ook op de gebruikelijke manier ongedaan maken.
Tips
- Ook met behulp van een draaitabel (pivot table) komen dubbele waarden aan het licht omdat bij het toevoegen van een veld aan zo’n draaitabel standaard de unieke waarden worden gebruikt, vergelijkbaar met de filterkeuzelijst. Maar het is wel een relatief complex hulpmiddel om dubbele records op te sporen.
- Standaard laat Excel bij het invoeren van een tekstwaarde in een cel een voorstel zien van een eerder in de kolom voorkomende waarde zolang de letters die u typt nog overeenkomen. Men noemt dat ook wel AutoAanvullen.
Dit invoerhandigheidje wijst dus ook op unieke of juist dubbele waarden. Als er (meteen of na enkele letters getypt te hebben) geen waarde verschijnt, is de nieuwe invoer uniek. Mocht u juist op die manier dubbele waarden wilt aanmaken, kunt u direct op [Enter] te drukken zodra de gewenste waarde in de cel staat (in het voorbeeld in de figuur dus meteen na de letter B, soms zijn meer letters nodig om de gewenste waarde te bereiken). Om deze methode succesvol te gebruiken kunt u beter geen lege rijen in de gegevenstabel laten vallen, want dat kan de functie AutoAanvullen blokkeren: in de regel worden alleen direct aan de huidige cel grenzende bovenliggende celwaarden aangeboden.