Priročnik za preverjanje podatkov Excel

Uvod

Preverjanje podatkov je funkcija v Excelu, ki se uporablja za nadzor nad tem, kaj lahko uporabnik vnese v celico. Na primer, lahko uporabite potrditev podatkov, da se prepričate, da je vrednost številka med 1 in 6, da se datum pojavi v naslednjih 30 dneh ali da je vnos besedila manjši od 25 znakov.

Validacija podatkov lahko uporabniku preprosto prikaže sporočilo, ki mu pove, kaj je dovoljeno, kot je prikazano spodaj:

Validacija podatkov lahko ustavi tudi neveljaven vnos uporabnika. Če na primer koda izdelka ne uspe preveriti veljavnosti, lahko prikažete tako sporočilo:

Poleg tega lahko potrditev podatkov uporabniku predstavimo vnaprej določeno izbiro v spustnem meniju:

To je lahko priročen način, da uporabniku zagotovite točno tiste vrednosti, ki ustrezajo zahtevam.

Kontrole preverjanja veljavnosti podatkov

Validacija podatkov se izvaja s pravili, določenimi v uporabniškem vmesniku programa Excel na zavihku Podatki na traku.

kako alt f4 na mac

Pomembna omejitev

Pomembno je razumeti, da je preverjanje podatkov enostavno premagati. Če uporabnik kopira podatke iz celice brez validacije v celico z validacijo podatkov, se validacija uniči (ali zamenja). Validacija podatkov je dober način, da uporabnikom sporočite, kaj je dovoljeno ali pričakovano, vendar ni varen način za zagotovitev vnosa.

Določitev pravil za preverjanje veljavnosti podatkov

Preverjanje podatkov je definirano v oknu s tremi zavihki: Nastavitve, Vnosno sporočilo in Opozorilo o napaki:

Na zavihku z nastavitvami vnesete merila za preverjanje. Obstajajo številna vgrajena pravila za preverjanje veljavnosti z različnimi možnostmi ali pa izberete Po meri in uporabite svojo formulo za preverjanje vnosa, kot je prikazano spodaj:

Zavihek Vhodno sporočilo definira sporočilo, ki se prikaže, ko je izbrana celica s pravili preverjanja. To vhodno sporočilo je popolnoma neobvezno. Če vnosno sporočilo ni nastavljeno, se sporočilo ne prikaže, ko uporabnik izbere celico z uveljavljenim preverjanjem podatkov. Vhodno sporočilo nima vpliva na to, kaj lahko uporabnik vnese - preprosto prikaže sporočilo, ki uporabniku sporoči, kaj je dovoljeno ali pričakovano.

Zavihek Opozorilo o napaki nadzoruje, kako se izvaja preverjanje veljavnosti. Ko je na primer slog nastavljen na 'Ustavi', neveljavni podatki sprožijo okno s sporočilom in vnos ni dovoljen.

Uporabnik vidi takšno sporočilo:

Ko je slog nastavljen na Podatki ali Opozorilo, se prikaže druga ikona s sporočilom po meri, vendar lahko uporabnik sporočilo prezre in vnese vrednosti, ki ne prestanejo preverjanja. Spodnja tabela povzema vedenje za vsako možnost opozorila o napaki.

Možnosti preverjanja podatkov

Ko je ustvarjeno pravilo preverjanja podatkov, je na voljo osem možnosti za preverjanje vnosa uporabnika:

Kakršna koli vrednost - validacija ni izvedena. Opomba: če je bila veljavnost podatkov že uporabljena z nastavljenim vhodnim sporočilom, bo sporočilo še vedno prikazano, ko je celica izbrana, tudi če je izbrana katera koli vrednost.

Celo število - dovoljene so samo cele številke. Ko je izbrana možnost celotnega števila, so na voljo druge možnosti za dodatno omejitev vnosa. Na primer, lahko zahtevate celo število med 1 in 10.

Decimalno - deluje kot možnost celotnega števila, vendar dovoljuje decimalne vrednosti. Na primer, če je možnost Decimal nastavljena tako, da dovoljuje vrednosti med 0 in 3, so dovoljene vse vrednosti, kot so .5, 2.5 in 3.1.

Seznam - dovoljene so samo vrednosti iz vnaprej določenega seznama. Vrednosti so uporabniku predstavljene kot kontrolnik v spustnem meniju. Dovoljene vrednosti je mogoče trdo kodirati neposredno na zavihku Nastavitve ali določiti kot obseg na delovnem listu.

Datum - dovoljeni so le datumi. Na primer, lahko zahtevate datum med 1. januarjem 2018 in 31. decembrom 2021 ali datumom po 1. juniju 2018.

Čas - dovoljeni so le časi. Na primer, lahko zahtevate čas med 9.00 in 17.00 ali dovolite čas šele po 12.00.

Dolžina besedila - potrdi vnos glede na število znakov ali številk. Na primer, lahko zahtevate kodo, ki vsebuje 5 številk.

Po meri - preverja uporabniški vnos s formulo po meri. Z drugimi besedami, lahko za potrditev vnosa napišete svojo formulo. Formule po meri močno razširjajo možnosti preverjanja podatkov. Na primer, lahko uporabite formulo, da zagotovite, da je vrednost velika, vrednost vsebuje 'xyz' ali pa je datum v tednu v naslednjih 45 dneh.

Zavihek z nastavitvami vsebuje tudi dva potrditvena polja:

Prezri prazno - pove Excelu, da ne potrdi celic, ki ne vsebujejo vrednosti. Zdi se, da v praksi ta nastavitev vpliva le na ukaz 'obkroži neveljavne podatke'. Če je omogočeno, prazne celice niso obkrožene, tudi če ne uspejo preveriti veljavnosti.

Te spremembe uporabite za druge celice z enakimi nastavitvami - ta nastavitev bo posodobila preverjanje veljavnosti, uporabljeno za druge celice, če se bo ujemalo s (izvirno) potrditvijo celic (-ov), ki jih urejate.

oblikujte celice na podlagi vrednosti druge celice

Opomba: Ročno lahko izberete tudi vse celice s preverjanjem podatkov z uporabo možnosti Pojdi na + Posebno, kot je razloženo spodaj.

Preprost spustni meni

Spustni meni z možnostmi lahko vnesete s trdim kodiranjem vrednosti v polje z nastavitvami ali izberete obseg na delovnem listu. Na primer, če želite omejiti vnose na dejanja 'KUPI', 'HOLD' ali 'SELL', lahko vnesete te vrednosti, ločene z vejicami, kot je prikazano spodaj:

Če se spustni meni uporabi za celico na delovnem listu, deluje tako:

Drug način za podajanje vrednosti v spustni meni je uporaba sklica na delovni list. Na primer, pri velikostih (tj. Majhnih, srednjih itd.) V razponu F3: F6 lahko vnesete ta razpon neposredno v oknu nastavitev preverjanja podatkov:

Upoštevajte, da je obseg vnesen kot absolutni naslov da se prepreči spreminjanje, ko se preverjanje podatkov uporabi za druge celice.

Nasvet: Kliknite ikono majhne puščice na skrajni desni strani polja vira, da izberete neposredno na delovnem listu, da vam ni treba ročno vnesti obsega.

Uporabite lahko tudi imenovanih obsegov določiti vrednosti. Na primer, z imenovanim obsegom, imenovanim 'velikosti' za F3: F7, lahko vnesete ime neposredno v okno, začenši z znakom enakosti:

Imenovani obsegi so samodejno absolutni, zato se ne bodo spremenili, ker se preverjanje podatkov uporabi za različne celice. Če so vam imenovani obsegi novi, ta stran ima dober pregled in številne povezane nasvete .

Namig - če za spustne vrednosti uporabite tabelo, bo Excel samodejno razširil ali skrčil tabelo, ko dodate ali odstranite spustne vrednosti. Z drugimi besedami, Excel bo samodejno sinhroniziral spustni meni z vrednostmi v tabeli, ko se vrednosti spreminjajo, dodajajo ali odstranjujejo. Če ste novi v Excelovih tabelah, si lahko ogledate demo v tem videu bližnjice na mizi.

Preverjanje podatkov s formulo po meri

Formule za preverjanje podatkov morajo biti logične formule, ki vrnejo TRUE, če je vnos veljaven, in FALSE, če vnos ni veljaven. Če želite na primer vnesti poljubno število v celico A1, lahko uporabite funkcijo ISNUMBER v formuli, kot je ta:

 
= ISNUMBER (A1)

Če uporabnik vnese vrednost, kot je 10 v A1, ISNUMBER vrne TRUE in preverjanje podatkov uspe. Če vnesejo vrednost, kot je 'jabolko', v A1, ISNUMBER vrne FALSE in preverjanje podatkov ne uspe.

Če želite omogočiti preverjanje podatkov s formulo, izberite »Po meri« na zavihku z nastavitvami in nato vnesite formulo v vrstico s formulo, ki se začne z znakom enakega (=) kot običajno.

Formule za odpravljanje težav

Excel prezre formule za preverjanje podatkov, ki vračajo napake. Če formula ne deluje in ne morete ugotoviti, zakaj, nastavite lažne formule, da zagotovite, da formula deluje, kot ste pričakovali. Lažne formule so preprosto formule za preverjanje podatkov, vnesene neposredno na delovni list, tako da lahko preprosto vidite, kaj vrnejo. Spodnji zaslon prikazuje primer:

Ko lažna formula deluje, kot želite, jo preprosto kopirajte in prilepite v območje formule za preverjanje veljavnosti podatkov.

Če vas ta zamisel o lažni formuli zmede, poglejte ta video , ki prikazuje, kako z lažnimi formulami izpopolniti formule za pogojno oblikovanje. Koncept je popolnoma enak.

Primeri formule za preverjanje veljavnosti podatkov

Možnosti za formule za preverjanje veljavnosti podatkov so praktično neomejene. Tukaj je nekaj primerov, ki vam bodo dali navdih:

Če želite dovoliti le 5 vrednosti znakov, ki se začnejo z 'z', lahko uporabite:

 
= AND ( LEFT (A1)='z', LEN (A1)=5)

Ta formula vrne TRUE le, če je koda dolga 5 mest in se začne z 'z'. Dve obkroženi vrednosti s to formulo vrneta vrednost FALSE.

Če želite dovoliti samo datum v 30 dneh od danes:

 
= AND (A1> TODAY (),A1<=( TODAY ()+30))

Če želite dovoliti samo edinstvene vrednosti:

 
= COUNTIF (range,A1)<2

Dovoliti samo e -poštni naslov

 
=ISUMBER( FIND ('@',A1)

Validacija podatkov za kroženje neveljavnih vnosov

Ko se uporabi preverjanje podatkov, lahko od Excela zahtevate, da obkroži prej vnesene neveljavne vrednosti. Na zavihku Podatki na traku kliknite Preverjanje podatkov in izberite »Obkroži neveljavne podatke«:

Na spodnjem zaslonu so na primer prikazane obkrožene vrednosti, ki ne uspejo potrditi s to formulo po meri:

 
= AND ( LEFT (A1)='z', LEN (A1)=5)

Poiščite celice z validacijo podatkov

Če želite poiskati celice z uveljavljenim preverjanjem podatkov, uporabite pogovorno okno Pojdi na> Posebno. Vnesite bližnjico na tipkovnici Control + G in kliknite gumb Poseben. Ko se prikaže pogovorno okno, izberite »Preverjanje podatkov«:

uvoz besedilne datoteke v excel 2016

Kopirajte preverjanje podatkov iz ene celice v drugo

Za kopiranje preverjanja iz ene celice v drugo celico. Običajno kopirajte celice, ki vsebujejo želeno potrditev podatkov, nato uporabite Posebno lepljenje + Preverjanje. Ko se prikaže pogovorno okno, vnesite 'n', da izberete potrditev, ali kliknite potrditev z miško.

Opomba: z bližnjico na tipkovnici Control + Alt + V lahko prikličete Posebno prilepitev brez miške.

Počisti vse preverjanje podatkov

Če želite počistiti vso potrditev podatkov iz vrste celic, izberite, nato kliknite gumb Preverjanje podatkov na zavihku Podatki na traku. Nato kliknite gumb »Počisti vse«:

Če želite počistiti vse preverjanje podatkov z delovnega lista, izberite celoten delovni list in sledite istim korakom zgoraj.