Preskoči na glavno vsebino
 

Preverjanje podatkov Excel: dodajanje, uporaba, kopiranje in odstranjevanje preverjanja podatkov v Excelu

V Excelu je preverjanje podatkov uporabna funkcija, s katero lahko omejite, kaj lahko uporabnik vnese v celico. Na primer, funkcija preverjanja podatkov vam lahko pomaga omejiti dolžino besedilnih nizov ali besedila, ki se začne / konča z določenimi znaki ali edinstvene vrednosti, ki jih je treba vnesti, itd.

V tej vadnici bomo govorili o tem, kako dodati, uporabiti in odstraniti preverjanje podatkov v Excelu, podrobno bodo prikazane tudi nekatere osnovne in napredne operacije te funkcije.

Kazalo:

1. Kaj je preverjanje podatkov v Excelu?

2. Kako dodati preverjanje podatkov v Excelu?

3. Osnovni primeri za validacijo podatkov

4. Napredna pravila po meri za preverjanje veljavnosti podatkov

5. Kako urediti preverjanje podatkov v Excelu?

6. Kako najti in izbrati celice z validacijo podatkov v Excelu?

7. Kako kopirati pravilo preverjanja veljavnosti podatkov v druge celice?

8. Kako z validacijo podatkov obkrožiti neveljavne vnose v Excelu?

9. Kako odstraniti preverjanje podatkov v Excelu?

1. Kaj je preverjanje podatkov v Excelu?

O Preverjanje podatkov funkcija vam lahko pomaga omejiti vnos vsebine na vašem delovnem listu. Običajno lahko ustvarite nekatera pravila preverjanja, da preprečite ali dovolite vnos samo nekaterih vrst podatkov na seznam izbranih celic.

Nekaj ​​osnovnih uporab funkcije preverjanja podatkov:

  • 1. Kakršna koli vrednost: validacija ni izvedena, v navedene celice lahko vnesete karkoli.
  • 2. Celotna vrednost: dovoljene so samo cele številke.
  • 3. Decimalno: omogoča vnos celih številk in decimalk.
  • 4. Seznam: dovoljeno je vnesti ali izbrati samo vrednosti s vnaprej določenega seznama. Vrednosti so prikazane na spustnem seznamu.
  • 5. Datum: dovoljeni so le datumi.
  • 6. Čas: dovoljeni so le časi.
  • 7. Dolžina besedila: dovoljuje vnos samo določene dolžine besedila.
  • 8. Po meri: ustvarite pravila formule po meri za potrditev vnosa uporabnikov.

2. Kako dodati preverjanje podatkov v Excelu?

V Excelovem delovnem listu lahko potrditev podatkov dodate z naslednjimi koraki:

1. Izberite seznam celic, v katere želite nastaviti preverjanje podatkov, in kliknite datum > Preverjanje podatkov > Preverjanje podatkov, glej posnetek zaslona:

2. v Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek, ustvarite lastna pravila preverjanja. v poljih meril lahko vnesete katero koli od naslednjih vrst:

  • Vrednote: Neposredno vnesite merila v polja meril;
  • Sklic na celico: Sklicevanje na celico na delovnem listu ali drugem delovnem listu;
  • Formule: Ustvarite bolj zapletene formule kot pogoje.

Kot primer bom ustvaril pravilo, ki dovoljuje vnos samo celih številk med 100 in 1000, tukaj nastavite merila, kot je prikazano na spodnjem posnetku zaslona:

3. Ko konfigurirate pogoje, pojdite na input sporočilo or Opozorilo o napaki za nastavitev vhodnega sporočila ali opozorila o napaki za celice za preverjanje, kot želite. (Če ne želite nastaviti opozorila, kliknite OK dokončati neposredno.)

3.1) Dodajte vhodno sporočilo (neobvezno):

Ustvarite lahko sporočilo, ki se prikaže, ko izberete celico, ki vsebuje potrditev podatkov. To sporočilo uporabnika opomni, kaj lahko vnese v celico.

Pojdi na input sporočilo zavihek in naredite naslednje:

  • Prijava Pokaži vhodno sporočilo, ko je izbrana celica možnost;
  • V ustrezna polja vnesite želeni naslov in sporočilo opomnika;
  • klik OK , da zaprete to pogovorno okno.

Zdaj, ko izberete preverjeno celico, se prikaže okno s sporočilom, kot sledi:

3.2) Ustvarite pomembna sporočila o napakah (neobvezno):

Poleg ustvarjanja vnosnega sporočila lahko prikažete tudi opozorila o napakah, ko v celico z validacijo podatkov vnesete neveljavne podatke.

Pojdi na Opozorilo o napaki jeziček Preverjanje podatkov pogovorno okno, naredite tako:

  • Prijava Pokaži opozorilo o napaki po vnosu neveljavnih podatkov možnost;
  • v Style na spustnem seznamu izberite želeno vrsto opozorila, ki jo potrebujete:
    • Ustavi (privzeto): Ta vrsta opozorila uporabnikom preprečuje vnos neveljavnih podatkov.
    • Opozorilo: Opozarja uporabnike, da so podatki neveljavni, vendar ne preprečuje vnosa.
    • Informacije: Obvešča uporabnike samo o neveljavnem vnosu podatkov.
  • V ustrezna polja vnesite želeni naslov in opozorilno sporočilo;
  • klik OK da zaprete pogovorno okno.

In zdaj, ko vnesete neveljavno vrednost, se prikaže okno z opozorilom za sporočilo, kot je prikazano spodaj:

stop možnost: Lahko kliknete Poskusite znova vnesti drugo vrednost oz Prekliči odstraniti vnos.

Opozorilo možnost: Kliknite Da za vnos neveljavnega vnosa, Ne spremeniti, ali Prekliči odstraniti vnos.

Informacije možnost: Kliknite OK za vnos neveljavnega vnosa oz Prekliči odstraniti vnos.

Opombe: Če svojega sporočila po meri ne nastavite v Opozorilo o napaki polje, privzeto stop bo prikazano polje z opozorilom, kot je prikazano spodaj:

3. Osnovni primeri za validacijo podatkov

Ko uporabljate to funkcijo preverjanja podatkov, je na voljo 8 vgrajenih možnosti za nastavitev preverjanja podatkov. Na primer: poljubno vrednost, cela števila in decimalke, datum in čas, seznam, dolžina besedila in formula po meri. V tem razdelku bomo razpravljali o tem, kako uporabiti nekatere vgrajene možnosti v Excelu?

3.1 Validacija podatkov za cela števila in decimalke

1. Izberite seznam celic, kjer želite dovoliti samo cela števila ali decimalke, nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov.

2. v Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite ustrezen element Celo število or Decimalno v Dovoli spustnem polju.
  • Nato izberite eno od meril, ki jih potrebujete v datum polje (v tem primeru izberem med možnost).
  • nasveti: Merila vsebujejo: med, ne med, enako, ne enako, večje od, manjše, večje ali enako, manjše ali enako.
  • Nato vnesite Minimalna in največja vrednosti, ki jih potrebujete (želim številke med 0 in 1 00).
  • Končno kliknite OK gumb.

3. Zdaj je v izbrane celice dovoljeno vnesti samo celotna števila od 0 do 100.

3.2 Validacija podatkov za datum in uro

S tem lahko preprosto potrdite določen datum ali čas, ki ga želite vnesti Preverjanje podatkov, naredite naslednje:

1. Izberite seznam celic, kjer želite dovoliti samo določene datume ali ure, nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov.

2. v Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite ustrezen element Datum or čas v Dovoli spustnem polju.
  • Nato izberite eno od meril, ki jih potrebujete v datum polje (tukaj se odločim več kot možnost).
  • nasveti: Merila vsebujejo: med, ne med, enako, ne enako, večje od, manjše, večje ali enako, manjše ali enako.
  • Nato vnesite Datum začetka potrebujete (želim, da so datumi večji od 8).
  • Končno kliknite OK gumb.

3. Zdaj je v izbrane celice dovoljeno vnesti samo datume, večje od 8.

3.3 Validacija podatkov za dolžino besedila

Če morate omejiti število znakov, ki jih lahko vnesete v celico. Če želite na primer omejiti vsebino na največ 10 znakov za določen obseg, to Preverjanje podatkov ti lahko tudi naredi uslugo.

1. Izberite seznam celic, kjer želite omejiti dolžino besedila, in kliknite datum > Preverjanje podatkov > Preverjanje podatkov.

2. v Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite Dolžina besedila Iz Dovoli spustnem polju.
  • Nato izberite eno od meril, ki jih potrebujete v datum polje (v tem primeru izberem manj kot možnost).
  • nasveti: Merila vsebujejo: med, ne med, enako, ne enako, večje od, manjše, večje ali enako, manjše ali enako.
  • Nato vnesite največja število, ki ga morate omejiti (želim, da besedilo ne presega 10 znakov).
  • Končno kliknite OK gumb.

3. Zdaj lahko izbrane celice vnesejo le besedilni niz, manjši od 10 znakov.

3.4 Seznam potrditev podatkov (spustni seznam)

S tem močnim Preverjanje podatkov funkcijo, lahko hitro in enostavno ustvarite spustni seznam v celicah. Prosim, naredite tako:

1. Izberite ciljne celice, kamor vstavite spustni seznam, in kliknite datum > Preverjanje podatkov > Preverjanje podatkov.

2. v Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite Seznam Iz Dovoli spustni seznam.
  • v vir besedilno polje, vnesite elemente seznama, ki so neposredno ločeni z vejicami. Če želite na primer omejiti uporabniški vnos na tri izbire, vnesite Not Started, In Progress, Completed ali pa izberite seznam celic, ki vsebuje vrednosti, na podlagi katerih vstavite spustni meni.
  • Končno kliknite OK gumb.

3. Zdaj je bil v celicah ustvarjen spustni seznam, kot je prikazano na spodnjem posnetku zaslona:

Kliknite, če želite izvedeti podrobnejše informacije o spustnem seznamu ...

4. Napredna pravila po meri za preverjanje veljavnosti podatkov

V tem razdelku bom predstavil, kako narediti nekaj naprednih pravil za preverjanje veljavnosti podatkov po meri za reševanje vaših vrst težav, na primer: ustvarite formule za preverjanje, ki dovoljujejo samo številke ali besedilne nize, samo edinstvene vrednosti, samo določene telefonske številke, e -poštne naslove itd. .

4.1 Validacija podatkov dovoljuje samo številke ali besedila

 S funkcijo preverjanja podatkov dovolite vnos samo številk

Če želite v nizu celic dovoliti samo številke, naredite naslednje:

1. Izberite obseg celic, v katerega želite vnesti samo številke.

2. Kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek, naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite to formulo: = ISNUMBER (A2) v Formula besedilno polje. (A2 je prva celica izbranega obsega, ki jo želite omejiti)
  • klik OK , da zaprete to pogovorno okno.

3. Od zdaj naprej je v izbrane celice mogoče vnesti samo številke.

Opombe: Ta ŠTEVILO funkcija dopušča vse številske vrednosti v preverjenih celicah, vključno s celimi števili, decimalkami, ulomki, datumi in časi.

 Dovoli vnos samo besedilnih nizov s funkcijo Preverjanje podatkov

Če želite omejiti vnose celic samo na besedilo, lahko uporabite Preverjanje podatkov funkcijo s formulo po meri, ki temelji na ISTEXT funkcijo, naredite tako:

1. Izberite obseg celic, v katerega želite vnesti samo besedilne nize.

2. Kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek, naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite to formulo: = ISTEXT (A2) v Formula besedilno polje. (A2 je prva celica izbranega obsega, ki jo želite omejiti)
  • klik OK , da zaprete to pogovorno okno.

3. Zdaj lahko pri vnosu podatkov v določene celice dovolite samo podatke v obliki besedila.

4.2 Validacija podatkov dopušča samo alfanumerične vrednosti

Za nekatere namene preprosto dovolite vnos abeced in številskih vrednosti, vendar omejite posebne znake, kot so ~,%, $, presledek itd., Ta razdelek vam bo predstavil nekaj trikov.

 Dovoli samo alfanumerične vrednosti s funkcijo Preverjanje podatkov

Če želite preprečiti posebne znake, vendar dovolite le alfanumerične vrednosti, morate v datoteko Preverjanje podatkov funkcijo, naredite naslednje:

1. Izberite obseg celic, v katerega želite vnesti samo alfanumerične vrednosti.

2. Kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek, naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite spodnjo formulo v Formula Polje z besedilom.
  • =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornjih formulah A2 je prva celica izbranega obsega, ki jo želite omejiti.

3. Zdaj je dovoljeno vnašanje samo abeced in številskih vrednosti, posebni znaki pa bodo pri tipkanju omejeni, kot je prikazano na spodnjem posnetku zaslona:

 Dovolite le alfanumerične vrednosti z neverjetno lastnostjo

Morda je zgornja formula za nas razumljiva in zapomnjena, tukaj bom predstavil priročno funkcijo - Prepreči tipkanje of Kutools za Excel, s to funkcijo lahko to delo hitro rešite z lahkoto.

Po namestitvi Kutools za Excel, naredite tako:

1. Izberite obseg celic, v katerega želite vnesti samo alfanumerične vrednosti.

2. Nato kliknite Kutools > Prepreči tipkanje > Prepreči tipkanje, glej posnetek zaslona:

3. V izskočil Prepreči tipkanje pogovorno okno, izberite Prepreči vnos posebnih znakov možnost, glej posnetek zaslona:

4. Nato kliknite Ok in v naslednjih poljih za poziv kliknite Da > OK za dokončanje operacije. Zdaj so v izbranih celicah dovoljene samo abecede in številske vrednosti, oglejte si posnetek zaslona:

4.3 Validacija podatkov omogoča, da se besedila začnejo ali končajo z določenimi znaki

Če se morajo vse vrednosti v določenem obsegu začeti ali končati z določenim znakom ali podnizom, lahko uporabite potrditev podatkov s formulo po meri, ki temelji na funkciji EXACT, LEFT, RIGHT ali COUNTIF.

 Dovoli, da se besedila začnejo ali končajo z določenimi znaki samo z enim pogojem

Na primer, želim, da se besedila pri vnosu besedilnih nizov v določene celice začnejo ali končajo s "CN". Naredite naslednje:

1. Izberite obseg celic, ki dovoljuje samo besedila, ki se začnejo ali končajo z določenimi znaki.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek, naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite spodnjo formulo v Formula Polje z besedilom.
  • Begin with: =EXACT(LEFT(A2,2),"CN")
    End with: =EXACT(RIGHT(A2,2),"CN")
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornjih formulah A2 je prva celica izbranega obsega, številka 2 je število znakov, ki ste jih določili, CN je besedilo, s katerim želite začeti ali končati.

3. Od zdaj naprej lahko v izbrane celice vnesete samo besedilni niz z določenimi znaki. V nasprotnem primeru se prikaže opozorilno opozorilo, ki vas opomni na spodnji posnetek zaslona:

Nasvet: Zgornje formule razlikujejo med velikimi in malimi črkami. Če ne potrebujete velikih in malih črk, uporabite spodnje formule CONTIF:

Begin with (non case sensitive): =COUNTIF(A2,"CN*")
End with (non case sensitive): =COUNTIF(A2,"*CN")

Opombe: Zvezdica * je nadomestni znak, ki se ujema z enim ali več znaki.

 Dovoli besedila, ki se začnejo ali končajo z določenimi znaki z več merili (ALI logika)

Na primer, če želite, da se besedila začnejo ali končajo z "CN" ali "UK", kot je prikazano na spodnjem posnetku zaslona, ​​morate dodati še en primerek EXACT z znakom plus (+). Naredite naslednje:

1. Izberite obseg celic, ki dovoljuje samo besedila, ki se začnejo ali končajo z več merili.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek, naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite spodnjo formulo v Formula Polje z besedilom.
  • Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
    End with: =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornjih formulah A2 je prva celica izbranega obsega, številka 2 je število znakov, ki ste jih določili, CN in UK so posebna besedila, s katerimi želite začeti ali končati.

3. Zdaj je v izbrane celice mogoče vnesti samo besedilni niz z določenimi znaki.

Nasvet: Če želite prezreti velike in male črke, uporabite spodnje formule CONTIF:

Begin with (non case sensitive): =COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")
End with (non case sensitive): =COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")

Opombe: Zvezdica * je nadomestni znak, ki se ujema z enim ali več znaki.

4.4 Vnosi za dovoljenje validacije podatkov morajo vsebovati / ne smejo vsebovati določenega besedila

V tem razdelku bom govoril o tem, kako uporabiti preverjanje podatkov, da dovolite, da vrednosti vsebujejo ali ne smejo vsebovati enega ali več podnizov ali enega od številnih podnizov v Excelu.

 Dovoljeni vnosi morajo vsebovati eno ali eno od številnih posebnih besedil

Dovoljeni vnosi morajo vsebovati eno posebno besedilo

Če želite na primer omogočiti vnose, ki vsebujejo določen besedilni niz, morajo vse vnesene vrednosti vsebovati besedilo »KTE«, kot je prikazano na spodnjem posnetku zaslona, ​​lahko potrdite podatke s formulo po meri, ki temelji na funkcijah FIND in ISNUMBER. Prosim, naredite tako:

1. Izberite obseg celic, ki dovoljuje samo besedila, ki vsebujejo določeno besedilo.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek, naredite naslednje:

  • Izberite po meri s spustnega seznama Dovoli.
  • Nato vnesite eno od spodnjih formul v Formula Polje z besedilom.
  • =ISNUMBER(FIND("KTE",A2))             (Case sensitive)
    =ISNUMBER(SEARCH("KTE",A2))         (Non case sensitive)
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornjih formulah A2 je prva celica izbranega obsega, besedilo KTE je besedilni niz, ki ga morajo vnosi vsebovati.

3. Zdaj, ko vnesena vrednost ne vsebuje oblikovanega besedila, se prikaže okno z opozorilom.

Dovoljeni vnosi morajo vsebovati eno od številnih posebnih besedil

Zgornja formula deluje samo za en besedilni niz, če želite, da je v celicah dovoljen kateri koli od številnih besedilnih nizov, kot je prikazano na spodnjem posnetku zaslona, ​​morate za ustvarjanje formule uporabiti funkcije SUMPRODUCT, FIND in ISNUMBER skupaj.

1. Izberite obseg celic, ki dovoljuje samo besedila, ki vsebujejo katero koli od številnih postavk.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek, naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite eno od spodnjih formul, kot jih potrebujete, v Formula Polje z besedilom.
  • =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0                        (Case sensitive)
    =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0                   (Non case sensitive)
  • In nato kliknite OK da zaprete pogovorno okno.

Opombe: V zgornjih formulah A2 je prva celica izbranega obsega, C2: C4 je seznam vrednosti, ki jim želite dovoliti vnose, ki vsebujejo katero koli od njih.

3. In zdaj je mogoče vnesti samo vnose, ki vsebujejo eno od vrednosti na določenem seznamu.

 Dovoljeni vnosi ne smejo vsebovati enega ali enega od številnih posebnih besedil

Dovoljeni vnosi ne smejo vsebovati enega posebnega besedila

Za potrditev vnosov ne smejo vsebovati določenega besedila, na primer za dovoljenje vrednosti, ki ne smejo vsebovati besedila »KTE« v celici, lahko s funkcijami ISERROR in FIND ustvarite pravilo preverjanja podatkov. Prosim, naredite tako:

1. Izberite obseg celic, ki dovoljuje samo besedila, ki ne vsebujejo določenega besedila.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek, naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite eno od spodnjih formul v Formula Polje z besedilom.
  • =ISERROR(FIND("KTE",A2))                  (Case sensitive)
    =ISERROR(SEARCH("KTE",A2))                  (Non case sensitive)
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornjih formulah A2 je prva celica izbranega obsega, besedilo KTE je besedilni niz, ki ga vnosi ne smejo vsebovati.

3. Zdaj ne bo mogoče vnesti vnosov, ki vsebujejo določeno besedilo.

Dovoljeni vnosi ne smejo vsebovati enega od številnih posebnih besedil

Če želite preprečiti vnos enega od številnih besedilnih nizov na seznam, kot je prikazano na spodnjem posnetku zaslona, ​​storite naslednje:

1. Izberite obseg celic, za katere želite, da se nekatera besedila preprečijo.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek, naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite spodnjo formulo v Formula Polje z besedilom.
  • =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0                     (Case sensitive)
    =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0                 (Non case sensitive)
  • In nato kliknite OK da zaprete pogovorno okno.

Opombe: V zgornjih formulah A2 je prva celica izbranega obsega, C2: C4 je seznam vrednosti, ki jih želite preprečiti, če vnosi vsebujejo katero koli od njih.

3. Od zdaj naprej bo onemogočen vnos vnosov, ki vsebujejo katero koli posebno besedilo.

4.5 Validacija podatkov dopušča samo edinstvene vrednosti

Če želite preprečiti vnos podvojenih podatkov v obseg celic, bo ta razdelek predstavil nekaj hitrih metod za reševanje te naloge v Excelu.

 Dovoli samo edinstvene vrednosti s funkcijo Preverjanje podatkov

Običajno vam lahko pomaga funkcija preverjanja podatkov s formulo po meri, ki temelji na funkciji COUNTIF, naredite naslednje:

1. Izberite celice ali stolpec, v katerega želite vnesti edinstvene vrednosti.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite spodnjo formulo v Formula Polje z besedilom.
  • =COUNTIF($A$2:$A$9,A2)=1
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornji formuli, A2: A9 je obseg celic, za katerega želite dovoliti samo edinstvene vrednosti, in A2 je prva celica izbranega obsega.

3. Zdaj je dovoljeno vnesti samo edinstvene vrednosti, ob vnosu podvojenih podatkov pa se prikaže opozorilno sporočilo, glejte posnetek zaslona:

 Dovolite samo edinstvene vrednosti s kodo VBA

Naslednja koda VBA vam lahko pomaga tudi pri preprečevanju vnosa podvojenih vrednosti, naredite naslednje:

1. Z desno miškino tipko kliknite zavihek lista, za katerega želite dovoliti samo edinstvene vrednosti, in izberite Ogled kode iz kontekstnega menija v izpuščenem Microsoft Visual Basic za aplikacije okno, kopirajte in prilepite naslednjo kodo v prazen modul:

Koda VBA: dovolite edinstvene vrednosti v vrsti celic:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
  Dim xRg As Range, iLong, fLong As Long
  If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
     Application.EnableEvents = False
     For Each xRg In Target
     With xRg
         If (.Value <> "") Then
          If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
            iLong = .Interior.ColorIndex
            fLong = .Font.ColorIndex
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 6
            MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
            .ClearContents
            .Interior.ColorIndex = iLong
            .Font.ColorIndex = fLong
          End If
       End If
     End With
     Next
     Application.EnableEvents = True
  End If
End Sub

Opombe: V zgornji kodi je A1: A100 in A: A so celice v stolpcu, ki jih želite preprečiti, jih spremenite glede na vaše potrebe.

2. Nato shranite in zaprite to kodo, zdaj, ko v celico A1: A100 vnesete podvojeno vrednost, se prikaže okno z opozorilom, kot je prikazano spodaj:

 Dovolite samo edinstvene vrednosti s priročno funkcijo

Če imate Kutools za Excel, Z njegovim Prepreči podvajanje funkcijo, lahko nastavite preverjanje podatkov, da preprečite podvojevanje za vrsto celic z le nekaj kliki.

Po namestitvi Kutools za Excel, naredite tako:

1. Izberite obseg celic, za katere želite preprečiti podvojene vrednosti, vendar dovolite samo edinstvene podatke.

2. Nato kliknite Kutools > Prepreči tipkanje > Prepreči podvajanje, glej posnetek zaslona:

3. Pojavi se opozorilno sporočilo, ki vas opomni, da bo preverjanje podatkov odstranjeno, če uporabite to funkcijo, kliknite Da in v naslednjem polju za poziv kliknite OK, glej posnetke zaslona:

4. Ko vnesete podvojene podatke v navedene celice, se prikaže polje z opozorilom, ki vas opozori, da podvojeni podatki niso veljavni, glejte posnetek zaslona:

4.6 Validacija podatkov dovoljuje samo velike / male / pravilne črke

Ta potrditev podatkov je močna funkcija, uporabniku pa lahko tudi pomaga, da v obseg celic vnese samo velike, male ali pravilne vnose. Naredite naslednje:

1. Izberite obseg celic, ki ga želite vnesti samo z velikimi, malimi ali pravilnimi črkami.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite eno od spodnjih formul, ki jih potrebujete Formula Polje z besedilom.
  • =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))                   (only allow uppercase text)
    =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))                 (only allow lowercase text)
    =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))               (only allow proper case text)
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornji formuli, A2 je prva celica stolpca, ki jo želite uporabiti.

3. Zdaj bodo sprejeti samo vnosi, ki so v skladu z ustvarjenim pravilom.

4.7 Validacija podatkov dovoljuje vrednosti, ki obstajajo / ne obstajajo na drugem seznamu

Dovoliti, da vrednosti obstajajo ali ne obstajajo na drugem seznamu in jih vnesti v vrsto celic, je lahko za večino od nas boleča težava. Pravzaprav lahko s to funkcijo uporabite funkcijo preverjanja podatkov s preprosto formulo, ki temelji na funkciji COUNTIF.

Na primer, želim, da se v obseg celic, kot je prikazano na spodnjem posnetku zaslona, ​​vnesejo samo vrednosti v območju C2: C4, da rešite to nalogo, naredite tako:

1. Izberite obseg celic, za katere želite uporabiti potrditev podatkov.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite eno od spodnjih formul, ki jih potrebujete Formula Polje z besedilom.
  • =COUNTIF($C$2:$C$4,A2)>0                (only allow values exist in another column)
    =COUNTIF($C$2:$C$4,A2)=0                (prevent values exist in another column)
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornji formuli, A2 je prva celica stolpca, ki jo želite uporabiti, C2: C4 je seznam vrednosti, ki jih želite preprečiti ali dovoliti, če so vnosi med njimi.

3. Zdaj lahko vnesete samo vnose, ki so v skladu z ustvarjenim pravilom, drugi pa bodo preprečeni.

4.8 Validacija podatkov vnese samo obliko telefonske številke

Ko vnesete podatke o zaposlenih v svojem podjetju, morate v en stolpec vnesti telefonsko številko, da zagotovite hiter in natančen vnos telefonskih številk. V tem primeru lahko nastavite preverjanje podatkov za telefonske številke. Na primer, samo želim, da se telefonski številki, kot je ta oblika (123) 456-7890, dovoli vnos na delovni list, ta razdelek bo predstavil dva hitra trika za reševanje te naloge.

 Forsirajte samo obliko telefonske številke s funkcijo Preverjanje podatkov

Če želite vnesti samo določeno obliko telefonske številke, naredite naslednje:

1. Izberite seznam celic, za katere želite, da se vnese določena oblika telefonske številke, nato pa z desno tipko miške kliknite in izberite Oblikuj celice v priročnem meniju si oglejte posnetek zaslona:

2. v Oblikuj celice v pogovornem oknu pod Število jeziček, izberite po meri na levi Kategorija na seznamu in nato vnesite želeno obliko telefonske številke v besedilno polje Vrsta, na primer bom uporabil to (###) ### - #### format, glej posnetek zaslona:

3. Nato kliknite OK da zaprete pogovorno okno.

4. Po oblikovanju celic nadaljujte z izbiro celic in odprite datoteko Preverjanje podatkov pogovorno okno s klikom datum > Preverjanje podatkov > Preverjanje podatkov, v pojavnem pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite to formulo = IN (ŠTEVILO (A2), LEN (A2) = 10) v besedilno polje Formula.
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornji formuli, A2 je prva celica stolpca, v katerem želite potrditi telefonsko številko.

5. Zdaj, ko vnesete 10-mestno številko, se bo samodejno pretvorila v določeno obliko telefonske številke, kot potrebujete, oglejte si posnetke zaslona:

Opombe: Če vnesena številka ni 10 števk, se prikaže okno z opozorilom, ki vas opomni, glejte posnetek zaslona:

 Forsirajte samo obliko telefonske številke s koristno funkcijo

Kutools za ExcelJe Potrdite telefonsko številko funkcija vam lahko pomaga tudi, da z nekaj kliki prisilite vnos samo oblike telefonske številke.

Po namestitvi Kutools za Excel, naredite tako:

1. Izberite seznam celic, ki dovoljujejo samo določeno telefonsko številko, nato kliknite Kutools > Prepreči tipkanje > Potrdite telefonsko številko, glej posnetek zaslona:

2. v Telefonska številka pogovorno okno, izberite želeno obliko telefonske številke ali pa ustvarite lastno oblikovanje s klikom na Dodaj gumb, glej posnetek zaslona:

3. Ko izberete ali nastavite oblikovanje telefonske številke, kliknite OK, zdaj je mogoče vnesti samo telefonsko številko z določenim oblikovanjem, sicer se prikaže opozorilno sporočilo, ki vas opomni, oglejte si posnetek zaslona:

4.9 Validacija podatkov velja samo za e -poštne naslove

Recimo, da morate v stolpec delovnega lista vnesti več e -poštnih naslovov, da preprečite vnos nepravilnega oblikovanja e -poštnih naslovov, običajno lahko nastavite pravilo preverjanja podatkov, ki dovoljuje oblikovanje samo e -poštnih naslovov.

 Prisilite samo oblikovanje e -poštnih naslovov s funkcijo preverjanja podatkov

Z uporabo funkcije preverjanja podatkov s formulo po meri lahko ustvarite pravilo, ki preprečuje hiter vnos neveljavnih e -poštnih naslovov, naredite tako:

1. Izberite celice, v katere želite vnesti samo e -poštne naslove, nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov.

2. V izskočil Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite to formulo = ŠTEVILO (TEKMO ("*@*.?*", A2,0)) v Formula Polje z besedilom.
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornji formuli, A2 je prva celica stolpca, ki jo želite uporabiti.

3. Če vneseno besedilo ni v obliki e -poštnega naslova, se prikaže okno z opozorilom, ki vas opomni. Oglejte si posnetek zaslona:

 Prisilite le obliko e -poštnih naslovov s priročno funkcijo

Kutools za Excel podpira neverjetno funkcijo - Potrdite e-poštni naslov, s tem pripomočkom lahko z enim klikom preprečite neveljavne e -poštne naslove.

Po namestitvi Kutools za Excel, naredite naslednje:

1. Izberite celice, ki jim dovolite vnos samo e -poštnih naslovov, nato kliknite Kutools > Prepreči tipkanje > Potrdite e-poštni naslov. Oglejte si posnetek zaslona:

2. Nato lahko vnesete samo oblikovanje e -poštnega naslova, sicer se prikaže okno z opozorilom, ki vas opomni, oglejte si posnetek zaslona:

4.10 Validacija podatkov vnese samo naslove IP

V tem razdelku bom predstavil nekaj hitrih trikov za nastavitev preverjanja podatkov, da sprejme samo naslove IP v vrsti celic.

 Forsirajte samo oblikovanje naslovov IP s funkcijo Preverjanje podatkov

Dovolite, da se v določen obseg celic vnesejo samo naslovi IP, naredite to:

1. Izberite celice, za katere želite, da se vnese samo naslov IP, nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov.

2. V izskočil Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite spodnjo formulo v Formula Polje z besedilom.
  • =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
  • klik OK , da zaprete to pogovorno okno.

Opomba: V zgornji formuli A2 je prva celica stolpca, ki jo želite uporabiti.

3. Če v celico vnesete neveljaven naslov IP, se prikaže okno z opozorilom, ki vas opomni na spodnji posnetek zaslona:

 Forsiraj samo oblikovanje naslovov IP s kodo VBA

Tukaj lahko tudi naslednja koda VBA omogoči vnos samo naslovov IP in omeji drugo vnos, naredite tako:

1. Z desno miškino tipko kliknite jeziček lista in kliknite Ogled kode iz kontekstnega menija, v uvodu Microsoft Visual Basic za aplikacije vanj kopirajte spodnjo kodo VBA.

Koda VBA: potrdite celice, da sprejmejo samo naslov IP

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
    Exit Sub
Else
    If Target = "" Then
        Exit Sub
    End If
    xArrIp = Split(Target.Text, ".")
    If UBound(xArrIp) <> 3 Then
        GoTo EIP
    Else
    xIntIP1 = CInt(xArrIp(0))
    xIntIP2 = CInt(xArrIp(1))
    xIntIP3 = CInt(xArrIp(2))
    xIntIP4 = CInt(xArrIp(3))
    If (xIntIP1 < 1) Or (xIntIP1 > 255) _
    Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
    Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
    Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
    GoTo EIP
     End If
    End If
End If
Exit Sub
EIP:
    MsgBox "Please enter correct IP address"
    Target = ""
End Sub

Opombe: V zgornji kodi, A2: A10 je obseg celic, v katerem želite sprejeti samo naslove IP.

2. Nato shranite in zaprite to kodo, zdaj lahko v določene celice vnesete samo pravilne naslove IP.

 Forsirajte samo oblikovanje naslovov IP z enostavno funkcijo

Če imate Kutools za Excel nameščen v delovnem zvezku Preverite naslov IP funkcija vam lahko pomaga rešiti tudi to nalogo.

Po namestitvi Kutools za Excel, naredite tako:

1. Izberite celice, ki dovoljujejo samo vnos naslovov IP, nato kliknite Kutools > Prepreči tipkanje > Preverite naslov IP. Oglejte si posnetek zaslona:

2. Po uporabi te funkcije lahko zdaj vnesete samo naslov IP, sicer se prikaže okno z opozorilom, ki vas opomni, oglejte si posnetek zaslona:

4.11 Validacija podatkov omejuje vrednosti, ki presegajo skupno vrednost

Recimo, da imate mesečno poročilo o stroških in skupni proračun znaša 18000 USD, zdaj pa morate, da skupni znesek na seznamu odhodkov ne presega vnaprej določenih skupaj 18000 USD, kot je prikazano na spodnjem posnetku zaslona. V tem primeru lahko s funkcijo SUM ustvarite pravilo preverjanja veljavnosti podatkov, da preprečite, da bi vsota vrednosti presegla vnaprej določeno vsoto.

1. Izberite seznam celic, kjer želite omejiti vrednosti.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite spodnjo formulo v Formula Polje z besedilom.
  • =SUM($B$2:$B$7)<=18000
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornji formuli, B2: B7 je obseg celic, ki jih želite omejiti.

3. Zdaj, ko vnesete vrednosti v obseg B2: B7, če je skupna vrednost manjša od 18000 USD, validacija mine. Če zaradi katere koli vrednosti skupni znesek preseže 18000 USD, se prikaže opozorilno polje z opozorilom.

4.12 Validacija podatkov omejuje vnos celic na podlagi druge celice

Če želite omejiti vnose podatkov na seznamu celic na podlagi vrednosti v drugi celici, lahko funkcija preverjanja podatkov pomaga rešiti tudi to opravilo. Na primer, če je celica C1 besedilo »Da«, lahko obseg A2: A9 vnese karkoli, če pa je celica C1 drugo besedilo, v obseg A2: A9 ni dovoljeno vnesti nič, kot je prikazano na spodnjih posnetkih zaslona :

Če želite rešiti to težavo, naredite naslednje:

1. Izberite seznam celic, kjer želite omejiti vrednosti.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite spodnjo formulo v Formula Polje z besedilom.
  • =$C$1="Yes"
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornji formuli, C1 je celica, ki vsebuje določeno besedilo, ki ga želite uporabiti, in besedilo »Da”Je besedilo, na podlagi katerega želite omejiti celice, jih spremenite glede na vaše potrebe.

3. Če ima celica C1 besedilo »Da«, lahko v obseg A2: A9 vnesete karkoli, če ima celica C1 drugo besedilo, ne boste mogli vnesti nobene vrednosti, glejte spodnji demo:

4.13 Validacija podatkov omogoča vnos samo med delavniki ali vikendi

Če morate na seznam celic vnesti samo delavnike (od ponedeljka do petka) ali vikende (sobota in nedelja), se Preverjanje podatkov vam lahko tudi pomaga, naredite naslednje:

1. Izberite seznam celic, kamor želite vnesti tedne ali tedne.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite eno od spodnjih formul v Formula besedilno polje, kot ga potrebujete.
  • =WEEKDAY(A2,2)<6                      (allow only weekdays)
    =WEEKDAY(A2,2)>5                      (allow only weekends)
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornji formuli, A2 je prva celica stolpca, ki jo želite uporabiti.

3. Zdaj lahko v posebne celice vnesete le datum delavnika ali vikenda glede na vaše potrebe.

4.14 Validacija podatkov omogoča vnos datuma glede na današnji datum

Včasih boste morda morali dovoliti, da se na seznam celic vnesejo le datumi, večji ali manjši od današnjega. The Preverjanje podatkov funkcijo z DANES funkcija vam lahko naredi uslugo. Prosim, naredite tako:

1. Izberite seznam celic, kamor želite vnesti samo prihodnji datum (datum večji od današnjega).

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite spodnjo formulo v Formula Polje z besedilom.
  • =A2>Today()
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornji formuli, A2 je prva celica stolpca, ki jo želite uporabiti.

3. Zdaj lahko v celice vnesete le datume, ki so večji od današnjega, sicer se prikaže okno z opozorilom, ki vas opomni, oglejte si posnetek zaslona:

Nasvet:

1. Če želite omogočiti vnos preteklega datuma (datum manjši od današnjega), v potrditev podatkov uporabite spodnjo formulo:

=A2<Today()

2. Dovolite vnos datuma v določenem obsegu, na primer datumov v naslednjih 30 dneh, v potrditev podatkov vnesite spodnjo formulo:

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

4.15 Validacija podatkov omogoča vneseni čas glede na trenutni čas

Če želite na primer potrditi podatke glede na trenutni čas, lahko v celice vnesete le krat pred ali po trenutnem času. Ustvarite lahko svojo formulo za preverjanje podatkov, naredite tako:

1. Izberite seznam celic, v katerega želite vnesti samo čase pred ali po trenutnem času.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite čas Iz Dovoli spustni seznam.
  • Potem izberite manj kot dovoliti le krat pred trenutnim časom, oz več kot omogočiti čas po trenutnem času, kot ga potrebujete od datum spustite.
  • In potem v Končni čas or Začetni čas polje, vnesite spodnjo formulo:
  • =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornji formuli, A2 je prva celica stolpca, ki jo želite uporabiti.

3. Zdaj je v določene celice mogoče vnesti le čase pred ali po trenutnem času.

4.16 Potrditev podatkov datum določenega ali tekočega leta

Če želite dovoliti vnos samo datumov v določenem letu ali tekočem letu, lahko uporabite potrditev podatkov s formulo po meri, ki temelji na funkciji YEAR.

1. Izberite seznam celic, kamor želite vnesti samo datume v določenem letu.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite po meri Iz Dovoli spustni seznam.
  • Nato vnesite spodnjo formulo v Formula Polje z besedilom.
  • =YEAR(A2)=2020
  • klik OK , da zaprete to pogovorno okno.

Opombe: V zgornji formuli, A2 je prva celica stolpca, ki jo želite uporabiti, 2020 je število leta, ki ga želite omejiti.

3. Nato lahko vnesete samo datume v letu 2020, če ne, se prikaže okno z opozorilom, kot je prikazano na spodnjem posnetku zaslona:

Nasvet:

Če želite dovoliti samo datume v tekočem letu, lahko v potrditev podatkov uporabite naslednjo formulo:

=YEAR(A2)=YEAR(TODAY())

4.17 Datum potrditve datuma v tekočem tednu ali mesecu

Če želite uporabniku omogočiti, da lahko v določene celice vnese datume trenutnega tedna ali meseca, bo v tem razdelku predstavljeno nekaj formul za obravnavo te naloge v Excelu.

 Dovoli vnos datuma trenutnega tedna

1. Izberite seznam celic, kamor želite vnesti samo datume v tekočem tednu.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite Datum Iz Dovoli spustni seznam.
  • In potem izberite med Iz datum spustite.
  • v Datum začetka vnesite to formulo: = TODAY ()-WEEKDAY (TODAY (), 3)
  • v Končni datum vnesite to formulo: = TODAY ()-WEEKDAY (TODAY (), 3) +6
  • Končno kliknite OK gumb.

3. Nato lahko vnesete samo datume v tekočem tednu, drugi datumi bodo preprečeni, kot je prikazano na spodnjem posnetku zaslona:

 Dovoli vnos datuma tekočega meseca

Če želite dovoliti samo vnos datumov tekočega meseca, naredite naslednje:

1. Izberite seznam celic, kamor želite vnesti samo datume v tekočem mesecu.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v izpuščenem Preverjanje podatkov v pogovornem oknu pod Nastavitve zavihek naredite naslednje:

  • Izberite Datum Iz Dovoli spustni seznam.
  • Nato izberite med datum spustite.
  • v Datum začetka vnesite to formulo: = DATUM (LETO (DANES ()), MESEC (DANES ()), 1)
  • v Končni datum vnesite to formulo: = DATUM (LETO (DANES ()), MESEC (DANES ()), DAN (DATUM (LETO (DANES ()), MESEC (DANES ())+1,1) -1))
  • Končno kliknite OK gumb.

3. Od zdaj naprej lahko v izbrane celice vnesete samo datume tekočega meseca.

5. Kako urediti preverjanje podatkov v Excelu?

Če želite urediti ali spremeniti obstoječe pravilo preverjanja veljavnosti podatkov, sledite spodnjim korakom:

1. Izberite katero koli celico s pravilom preverjanja veljavnosti podatkov.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov Pojdite na Preverjanje podatkov pogovorno okno, v polju uredite ali spremenite pravila po svojih potrebah in nato preverite Te spremembe uporabite za vse druge celice z enakimi nastavitvami možnost uporabe tega novega pravila za vse druge celice s prvotnimi merili preverjanja. Oglejte si posnetek zaslona:

3. Kliknite OK Da shranite spremembe.

6. Kako najti in izbrati celice z validacijo podatkov v Excelu?

Če ste na svojem delovnem listu ustvarili več pravil za preverjanje veljavnosti podatkov, morate zdaj poiskati in izbrati celice, v katerih so veljala pravila preverjanja podatkov, Pojdi na posebno Ukaz vam lahko pomaga izbrati vse vrste preverjanja podatkov ali določeno vrsto preverjanja podatkov.

1. Aktivirajte delovni list, ki ga želite poiskati, in izberite celice s preverjanjem podatkov.

2. Nato kliknite Domov > Poiščite in izberite > Pojdi na posebno, glej posnetek zaslona:

3. v Pojdi na posebno pogovorno okno, izberite Validacija podatkov > vsi, glej posnetek zaslona:

4. Vse celice z validacijo podatkov so bile na trenutnem delovnem listu izbrane hkrati.

nasveti: Če želite samo izbrati eno posebno vrsto preverjanja podatkov, najprej izberite eno celico, ki vsebuje določeno potrditev podatkov, ki jo želite izvedeti, nato pojdite na Pojdi na posebno pogovorno okno in izberite Validacija podatkov > Same.

7. Kako kopirati pravilo preverjanja veljavnosti podatkov v druge celice?

Recimo, da ste ustvarili pravilo za preverjanje podatkov za seznam celic, zdaj pa morate isto pravilo za preverjanje podatkov uporabiti za druge celice. Namesto da znova ustvarite pravilo, lahko hitro in enostavno kopirate in prilepite obstoječe pravilo v druge celice.

1. Kliknite, da izberete eno celico s pravilom preverjanja, ki ga želite uporabiti, nato pritisnite Ctrl + C da ga kopirate.

2. Nato izberite celice, ki jih želite preveriti, za izbiro več celic, ki niso sosednje, pritisnite in držite tipko Ctrl tipko pri izbiri celic.

3. Nato z desno tipko miške kliknite izbor, izberite Posebno lepljenje možnost, glej posnetek zaslona:

4. v Posebno lepljenje pogovorno okno, izberite Potrditev možnost, glej posnetek zaslona:

5. Kliknite OK , je pravilo za preverjanje kopirano v nove celice.

8. Kako z validacijo podatkov obkrožiti neveljavne vnose v Excelu?

Včasih boste morda morali ustvariti pravila za preverjanje veljavnosti podatkov za obstoječe podatke, v tem primeru se lahko v razponu celic pojavijo nekateri neveljavni podatki. Kako preveriti neveljavne podatke in jih spremeniti? V Excelu lahko uporabite Obkroži neveljavne podatke funkcijo za označbo neveljavnih podatkov z rdečim krogom.

Če želite obkrožiti neveljavne podatke, ki jih potrebujete, uporabite datoteko Preverjanje podatkov funkcijo za nastavitev pravila za obseg podatkov. Naredite naslednje:

1. Izberite obseg podatkov, v katerem želite obkrožiti neveljavne podatke.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkovV Preverjanje podatkov pogovorno okno, nastavite pravilo preverjanja glede na vaše potrebe, na primer tukaj bom preveril vrednosti, večje od 500, glejte posnetek zaslona:

3. Nato kliknite OK zapreti pogovorno okno. Ko nastavite pravilo preverjanja veljavnosti podatkov, kliknite datum > Preverjanje podatkov > Obkroži neveljavne podatke, potem so vse neveljavne vrednosti, ki so manjše od 500, obkrožene z rdečo ovalno. Oglejte si posnetke zaslona:

Opombe:

  • 1. Takoj, ko popravite neveljavne podatke, rdeči krog samodejno izgine.
  • 2. To Obkroži neveljavne podatke funkcija lahko obkroži največ 255 celic. Ko shranite trenutni delovni zvezek, bodo odstranjeni vsi rdeči krogi.
  • 3. Ti krogi niso za tiskanje.
  • 4. Rdeče kroge lahko odstranite tudi s klikom datum > Preverjanje podatkov > Počisti potrditvene kroge.

9. Kako odstraniti preverjanje podatkov v Excelu?

Če želite pravila za preverjanje veljavnosti podatkov odstraniti iz vrste celic, trenutnega delovnega lista ali celotnega delovnega zvezka, vam lahko naslednje metode naredijo uslugo.

 Odstranite preverjanje podatkov v izbranem obsegu s funkcijo preverjanja podatkov

1. Izberite celice s preverjanjem podatkov, ki jih želite odstraniti.

2. Nato kliknite datum > Preverjanje podatkov > Preverjanje podatkov, v pojavnem pogovornem oknu pod Nastavitve jeziček, kliknite Počisti vse gumb, glej posnetek zaslona:

3. Nato kliknite OK gumb, da zaprete to pogovorno okno. Pravilo za preverjanje veljavnosti podatkov, uporabljeno za izbrano območje, je bilo takoj odstranjeno.

nasveti: Če želite odstraniti preverjanje podatkov s trenutnega delovnega lista, najprej izberite celoten list in nato uporabite zgornje korake.

 Odstranite preverjanje podatkov v izbranem obsegu s priročno funkcijo

Če imate Kutools za Excel, njeno Počisti omejitve preverjanja veljavnosti podatkov funkcija lahko tudi pomaga odstraniti pravila za preverjanje veljavnosti podatkov iz izbranega območja ali celotnega delovnega lista.

Po namestitvi Kutools za Excel, naredite tako:

1. Izberite obseg celic ali celoten delovni list vsebuje potrditev podatkov, ki jo želite odstraniti.

2. Nato kliknite Kutools > Prepreči tipkanje > Počisti omejitve preverjanja veljavnosti podatkov, glej posnetek zaslona:

3. V pojavnem polju za poziv kliknite OK, pravilo za preverjanje veljavnosti podatkov pa je bilo po potrebi izbrisano.

 Odstranite preverjanje podatkov z vseh delovnih listov s kodo VBA

Če želite pravila za preverjanje veljavnosti podatkov odstraniti iz celotnega delovnega zvezka, bodo zgornje metode zamudne, če je veliko delovnih listov, vam lahko spodnja koda pomaga pri hitrem reševanju te naloge.

1. Držite tipko ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Nato kliknite Vstavi > Moduliin v mapo prilepite naslednji makro Moduli okno.

Koda VBA: Odstranite pravila za preverjanje veljavnosti podatkov na vseh delovnih listih:

Sub RemoveDataValidation()
'Updateby Extendoffice
  Dim xwsh As Worksheet
  For Each xwsh In ActiveWorkbook.Worksheets
    xwsh.Cells.Validation.Delete
  Next xwsh
End Sub

3. Nato pritisnite F5 za zagon te kode, vsa pravila za preverjanje podatkov pa so bila takoj izbrisana iz celotnega delovnega zvezka.

 

  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce in vodenje podatkov; Vsebina razdeljenih celic; Združite podvojene vrstice in vsoto / povprečje... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Priljubljene in hitro vstavite formule, Obsegi, grafikoni in slike; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Razvrščanje vrtilne tabele po številka tedna, dan v tednu in še več ... Prikaži odklenjene, zaklenjene celice po različnih barvah; Označite celice s formulo / imenom...
zavihek kte 201905
  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
dno pisarniške mize

 

Comments (1)
No ratings yet. Be the first to rate!
Ghomergher
Ghomergher
This comment was minimized by the moderator on the site
Thanks For Sharing this Great Information. I loved it.
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations