Hoe zorg ik er in Excel voor dat dubbele factuurnummers niet in een bepaalde Excel-kolom worden ingevoerd?
In Excel 97 kunt u hiervoor de nieuwe functie Gegevensvalidatie gebruiken. In ons voorbeeld worden de factuurnummers ingevoerd in kolom A. Hier ziet u hoe u dit voor een enkele cel instelt:
- De volgende cel die moet worden ingevoerd, is A9. Klik in cel A9 en selecteer Gegevens> Validatie in het menu.
- Kies in het dropdown-menu 'Toestaan' voor 'Aangepast'
- Voer deze formule precies in zoals deze wordt weergegeven:
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE)) - Klik op het tabblad Foutmelding in het dialoogvenster Gegevensvalidatie.
- Zorg ervoor dat het vakje "Toon waarschuwing" is aangevinkt.
- Voor stijl: kies Stop
- Voer een titel in met 'Niet-unieke waarde'
- Voer een bericht in van "U moet een uniek factuurnummer invoeren."
- Klik OK"
U kunt het uittesten. Voer een nieuwe waarde in, bijvoorbeeld 10001 in cel A9. Geen probleem. Maar probeer een waarde te herhalen, zeg 10088 en het volgende zal verschijnen:
Het laatste dat u moet doen, is deze validatie van cel A9 naar de andere cellen in kolom A kopiëren.
- Klik in kolom A en selecteer Bewerken> kopiëren om de cel te kopiëren.
- Selecteer een groot aantal cellen in kolom A. Misschien A10: A500.
- Selecteer Bewerken, Plakken speciaal. Selecteer "Validatie" in het dialoogvenster Plakken speciaal en klik op OK. De validatieregel die u hebt ingevoerd vanuit cel A9, wordt gekopieerd naar alle cellen tot en met A500.
Als u in cel A12 klikt en Gegevensvalidatie kiest, ziet u dat Excel de validatieformule heeft gewijzigd in =ISNA(VLOOKUP(A12,A$1:A11,1,FALSE))dat is alles wat u moet weten om het te laten werken. Voor degenen onder jullie die meer willen weten, zal ik in het Engels uitleggen hoe de formule werkt.
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))
We zitten in cel A9. We vertellen de functie Vlookup om de waarde van de cel die we zojuist hebben ingevoerd (A9) te nemen en te proberen een overeenkomst te vinden in de cellen die variëren van A $ 1 tot A8. Het volgende argument, de 1, vertelt Vlookup dat wanneer een match wordt gevonden, ons de gegevens uit de eerste kolom wordt verteld. Ten slotte zegt de False in de vlookup dat we alleen op zoek zijn naar exacte overeenkomsten. Hier is truc 1: als VERT.ZOEKEN een overeenkomst vindt, wordt een waarde geretourneerd. Maar als er geen overeenkomst wordt gevonden, wordt de speciale waarde "# N / A" geretourneerd. Normaal gesproken zijn deze # N / A-waarden slechte dingen, maar in dit geval WILLEN we een # N / A. Als we een # N / A krijgen, weet je dat dit nieuwe item uniek is en niet overeenkomt met iets erboven. Een eenvoudige manier om te testen of een waarde # N / A is, is door de functie ISNA () te gebruiken. Als iets in de ISNA () resulteert in # N / A, krijgt u een TRUE. Zo,wanneer ze een nieuw factuurnummer invoeren en het wordt niet gevonden in de lijst boven de cel, zal de vlookup een # N / A retourneren, waardoor de ISNA () waar is.
Het tweede stukje bedrog zit in het tweede argument voor de Vlookup-functie. Ik was voorzichtig om A $ 1: A8 te specificeren. Het dollarteken voor de 1 vertelt Excel dat wanneer we deze validatie naar andere cellen kopiëren, deze altijd in de cel van de huidige kolom moet gaan zoeken. Dit heet een absoluut adres. Ik was net zo voorzichtig dat ik geen dollarteken voor de 8 in A8 plaatste. Dit wordt een relatief adres genoemd en vertelt Excel dat wanneer we dit adres kopiëren, het niet meer in de cel net boven de huidige cel moet zoeken. Wanneer we vervolgens de validatie kopiëren en naar de validatie voor cel A12 kijken, toont het tweede argument in de vlookup correct A $ 1: A11.
Er zijn twee problemen met deze oplossing. Ten eerste zal het niet werken in Excel 95. Ten tweede worden de validaties alleen uitgevoerd op cellen die veranderen. Als u een unieke waarde invoert in cel A9, en vervolgens weer omhoog gaat en cel A6 bewerkt zodat deze dezelfde waarde heeft als u in A9 hebt ingevoerd, wordt de validatielogica in A9 niet aangeroepen en krijgt u dubbele waarden in uw werkblad.
De ouderwetse methode die in Excel 95 wordt gebruikt, lost beide problemen op. In de oude methode zou u de validatielogica in een tijdelijke kolom B hebben staan. Om dit in te stellen, voert u de volgende formule in cel B9 in: =ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))Kopieer deze formule van B9. Plak het in de cellen B2: B500. Nu, terwijl u factuurnummers invoert in kolom A, wordt in kolom B WAAR weergegeven als de factuur uniek is en ONWAAR als deze niet uniek is.
