Kako lahko v Excelu zagotovim, da se podvojene številke računov ne vnesejo v določen stolpec Excel?
V Excelu 97 lahko za to uporabite novo funkcijo preverjanja podatkov. V našem primeru se številke računov vnašajo v stolpec A. Evo, kako ga nastavite za eno celico:
- Naslednja celica, ki jo je treba vnesti, je A9. Kliknite celico A9 in v meniju izberite Podatki> Preverjanje.
- V spustnem polju »Dovoli:« izberite »Po meri«
- Vnesite to formulo natančno tako, kot je videti:
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE)) - V pogovornem oknu Data Validation preverite zavihek Error Alert.
- Prepričajte se, da je potrjeno polje »Prikaži opozorilo«.
- Za slog: izberite Ustavi
- Vnesite naslov "Non Unique Value"
- Vnesite sporočilo "Vnesti morate enolično številko računa."
- Kliknite "V redu"
Lahko ga preizkusite. V celico A9 vnesite novo vrednost, recimo 10001. Ni problema. Toda poskusite ponoviti vrednost, recimo 10088 in pojavilo se bo naslednje:
Nazadnje je treba to validacijo kopirati iz celice A9 v druge celice v stolpcu A.
- Kliknite stolpec A in izberite Uredi> kopiraj, da kopirate celico.
- V stolpcu A. izberite velik obseg celic. Morda A10: A500.
- Izberite Uredi, Prilepi posebej. V pogovornem oknu Prilepi posebno izberite "Preverjanje" in kliknite V redu. Pravilo preverjanja veljavnosti, ki ste ga vnesli iz celice A9, bo kopirano v vse celice do A500.
Če kliknete celico A12 in izberete Preverjanje podatkov, boste videli, da je Excel spremenil formulo za preverjanje v » =ISNA(VLOOKUP(A12,A$1:A11,1,FALSE))To je vse, kar morate vedeti, da bo delovalo. Za tiste, ki želite vedeti več, bom v angleščini razložil, kako deluje formula.
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))
Sedimo v celici A9. Funkciji Vlookup sporočamo, naj zavzame vrednost celice, ki smo jo pravkar vnesli (A9), in naj poskuša najti ujemanje v celicah, ki se gibljejo od A $ 1 do A8. Naslednji argument, 1, Vlookupu sporoča, da ko najdemo ujemanje, ki nam sporoča podatke iz prvega stolpca. Na koncu False v vlookupu pravi, da iščemo samo natančna ujemanja. Tu je trik št. 1: Če VLOOKUP najde ujemanje, bo vrnil vrednost. Če pa ne najde ujemanja, bo vrnil posebno vrednost "# N / A". Običajno so te vrednosti # N / A slabe stvari, vendar v tem primeru ŽELIMO # N / A. Če dobimo # N / A, potem veste, da je ta novi vnos edinstven in se ne ujema z ničemer nad njim. Preprost način preverjanja, ali je vrednost # N / A, je uporaba funkcije ISNA (). Če je nekaj znotraj ISNA () ocenjeno na # N / A, dobite TRUE. Torej,ko vnesejo novo številko računa in je ne najdete na seznamu nad celico, bo vlookup vrnil # N / A, zaradi česar bo ISNA () resnična.
Drugi del trika je v drugem argumentu za funkcijo Vlookup. Previdno sem določil A $ 1: A8. Znak za dolar pred 1 pove Excelu, da mora, ko kopiramo to potrditev v druge celice, vedno začeti iskati celico trenutnega stolpca. To se imenuje absolutni naslov. Prav tako sem bil previden, da pred osmico v A8 nisem dal znaka dolarja. To se imenuje relativni naslov in Excelu sporoča, da mora, ko kopiramo ta naslov, prenehati iskati celico tik nad trenutno celico. Nato, ko kopiramo preverjanje veljavnosti in si ogledamo preverjanje veljavnosti celice A12, drugi argument v vlookupu pravilno prikaže A $ 1: A11.
Pri tej rešitvi obstajata dve težavi. Prvič, v Excelu 95 ne bo delovalo. Drugič, preverjanja veljajo samo na celicah, ki se spremenijo. Če v celico A9 vnesete edinstveno vrednost in se nato vrnete navzgor in uredite celico A6, da bo enaka vrednosti, ki ste jo vnesli v A9, logika preverjanja veljavnosti v A9 ne bo aktivirana in na delovnem listu boste imeli podvojene vrednosti.
Staromodna metoda, uporabljena v Excelu 95, bo obravnavala obe težavi. V stari metodi bi imeli logiko preverjanja v začasnem stolpcu B. Če želite to nastaviti, vnesite naslednjo formulo v celico B9: =ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))Kopirajte to formulo iz B9. Prilepite ga v celice B2: B500. Ko vnesete številke računov v stolpec A, bo stolpec B prikazal TRUE, če je račun enoličen, in FALSE, če ni enoličen.
