Hvordan i Excel kan jeg sørge for at duplikatfakturanummer ikke blir lagt inn i en bestemt Excel-kolonne?
I Excel 97 kan du bruke den nye datavalideringsfunksjonen til å gjøre dette. I vårt eksempel blir fakturanummerene lagt inn i kolonne A. Slik konfigurerer du det for en enkelt celle:
- Neste celle som skal angis er A9. Klikk i celle A9, og velg Data> Validering fra menyen.
- Velg rullegardinlisten "Tillat:"
- Skriv inn denne formelen nøyaktig hvordan den ser ut:
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE)) - Klikk kategorien Feilvarsel i dialogboksen Datavalidering.
- Forsikre deg om at "Vis varsel" -boksen er merket av.
- For stil :, velg Stopp
- Skriv inn en tittel på "Ikke unik verdi"
- Skriv inn meldingen "Du må oppgi et unikt fakturanummer."
- Klikk "OK"
Du kan teste det ut. Skriv inn en ny verdi, si 10001 i celle A9. Ikke noe problem. Men prøv å gjenta en verdi, si 10088, og følgende vises:
Den siste tingen å gjøre er å kopiere denne valideringen fra celle A9 til de andre cellene i kolonne A.
- Klikk i kolonne A og velg Rediger> kopier for å kopiere cellen.
- Velg et stort utvalg av celler i kolonne A. Kanskje A10: A500.
- Velg Rediger, Lim inn spesial. Fra dialogboksen Lim inn spesial, velg "Validering" og klikk OK. Valideringsregelen du skrev inn fra celle A9, blir kopiert til alle cellene ned til A500.
Hvis du klikker i celle A12 og velger Datavalidering, vil du se at Excel endret valideringsformelen til å være =ISNA(VLOOKUP(A12,A$1:A11,1,FALSE))Det er alt du trenger å vite for å få det til å fungere. For de av dere som vil vite mer, vil jeg forklare på engelsk hvordan formelen fungerer.
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))
Vi sitter i celle A9. Vi forteller Vlookup-funksjonen å ta verdien av cellen vi nettopp skrev inn (A9) og prøve å finne en match i cellene som spenner fra A $ 1 til A8. Det neste argumentet, 1, forteller Vlookup at når det blir funnet en kamp som forteller oss dataene fra den første kolonnen. Endelig sier False in vlookup at vi bare leter etter eksakte treff. Her er triks nr. 1: Hvis VLOOKUP finner en kamp, vil den returnere en verdi. Men hvis den ikke finner samsvar, vil den returnere spesialverdien til "# N / A". Normalt er disse # N / A-verdiene dårlige ting, men i dette tilfellet vil vi ha en # N / A. Hvis vi får en # N / A, så vet du at denne nye oppføringen er unik og ikke samsvarer med noe over den. En enkel måte å teste om verdien er # N / A, er å bruke ISNA () -funksjonen. Hvis noe inni ISNA () evalueres til en # N / A, får du en SANT. Så,når de skriver inn et nytt fakturanummer og det ikke finnes i listen over cellen, vil vlookup returnere et # N / A, som vil føre til at ISNA () er sant.
Den andre delen av lureri er i det andre argumentet for Vlookup-funksjonen. Jeg var nøye med å spesifisere A $ 1: A8. Dollartegnet før 1 forteller Excel at når vi kopierer denne valideringen til andre celler, bør den alltid begynne å se i cellen i den aktuelle kolonnen. Dette kalles en absolutt adresse. Jeg var like forsiktig så jeg ikke satte et dollartegn før 8 i A8. Dette kalles en relativ adresse og forteller Excel at når vi kopierer denne adressen, bør den slutte å se i cellen rett over den nåværende cellen. Så når vi kopierer valideringen og ser på valideringen for celle A12, viser det andre argumentet i vlookup riktig A $ 1: A11.
Det er to problemer med denne løsningen. For det første vil det ikke fungere i Excel 95. For det andre utføres valideringene bare på celler som endres. Hvis du skriver inn en unik verdi i celle A9, og deretter går opp og redigerer celle A6 for å være den samme verdien du skrev inn i A9, blir ikke valideringslogikken i A9 påkalt, og du vil ende opp med dupliserte verdier i regnearket.
Den gammeldagse metoden som brukes i Excel 95, vil løse begge disse problemene. I den gamle metoden vil du ha valideringslogikken i en midlertidig kolonne B. For å sette opp dette, skriv inn følgende formel i celle B9: =ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))Kopier denne formelen fra B9. Lim den inn i celler B2: B500. Nå som du skriver inn fakturanumre i kolonne A, viser kolonne B SANT hvis fakturaen er unik, og FALSK hvis den ikke er unik.
