Preskoči na glavno vsebino

 Kako uporabiti preverjanje veljavnosti podatkov v eni celici na Excelovem delovnem listu?

V Excelovem delovnem listu je uporaba ene validacije podatkov v celici za nas običajna, vendar, ali ste že kdaj poskusili uporabiti več validacij podatkov v eno celico? V tem članku bom govoril o nekaterih različnih primerih za reševanje te naloge v Excelu.

Uporabi več preverjanja veljavnosti podatkov v eni celici (primer 1)

Uporabi več preverjanja veljavnosti podatkov v eni celici (primer 2)

Uporabi več preverjanja veljavnosti podatkov v eni celici (primer 3)


Uporabi več preverjanja veljavnosti podatkov v eni celici (primer 1)

Na primer, želim nastaviti validacijo podatkov v celice s temi merili: če je vnesena številka, mora biti manjša od 100, če je vneseno besedilo, mora biti na seznamu od D2 do D7, kot je prikazano na sliki spodaj:

1. Izberite celice, za katere želite uporabiti preverjanje veljavnosti več podatkov, in kliknite datum > Preverjanje podatkov > Preverjanje podatkov, glej posnetek zaslona:

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

(1.) Izberite po meri Iz Dovoli spustni seznam;

(2.) Vnesite to formulo: =OR(A2<$C$2,COUNTIF($D$2:$D$7,A2)=1) v Formula Polje z besedilom.

Opombe: V zgornji formuli, A2 je celica, za katero želite uporabiti preverjanje veljavnosti podatkov, C2 in D2: D7 so merila, na katerih morate uporabiti preverjanje veljavnosti podatkov.

3. Nato kliknite OK, od zdaj naprej je mogoče v celice vnašati samo vrednosti, ki ustrezajo merilom, v nasprotnem primeru se bo pojavilo opozorilno okno, ki vas bo opozorilo, glejte posnetek zaslona:


Uporabi več preverjanja veljavnosti podatkov v eni celici (primer 2)

V tem primeru bom dovolil samo besedilo »Kutools for Excel« ali pa bo v določene celice mogoče vnesti datum med 12. 1. 2017 in 12. 31. 2017, kot je prikazano spodaj. Če želite rešiti to nalogo, naredite naslednje:

1. Pojdi Preverjanje podatkov v pogovornem oknu in v pogovornem oknu naredite naslednje:

(1.) Kliknite Nastavitve zavihek;

(2.) Izberite po meri možnost iz Dovoli spustni seznam;

(3.) Vnesite to formulo: =OR(A2=$C$2,AND(A2>=DATE(2017,12,1), A2<=DATE(2017,12,31))) v Formula Polje z besedilom.

Opombe: V zgornji formuli, A2 je celica, za katero želite uporabiti preverjanje veljavnosti podatkov, C2, 2017,12,1 in 2017,12,31 so merila, na katerih morate uporabiti preverjanje veljavnosti podatkov.

2. Nato kliknite OK gumb, zdaj lahko vnesete samo vrednosti, ki ustrezajo merilom, druge pa bodo omejene, kot je prikazano na sliki spodaj:


Uporabi več preverjanja veljavnosti podatkov v eni celici (primer 3)

Tretji primer, želim, da se besedilni niz začne z “KTE” ali “www”, in če se začne s “KTE”, je dovoljenih le 6 znakov; če se začne z “www”, je dovoljenih le 10 znakov, glejte posnetek zaslona:

Naslednja formula v validaciji podatkov vam lahko pomaga, da se z njo spoprimete.

1. Pojdi na Preverjanje podatkov v pogovornem oknu v pogovornem oknu izvedite naslednje postopke:

(1.) Kliknite Nastavitve zavihek;

(2.) Izberite po meri možnost iz Dovoli spustni seznam;

(3.) Vnesite to formulo: =OR(AND(LEFT(A2,3)="KTE",LEN(A2)=6),AND(LEFT(A2,3)="www",LEN(A2)=10)) v Formula Polje z besedilom.

Opombe: V zgornji formuli, A2 je celica, za katero želite uporabiti preverjanje veljavnosti podatkov, merila v formuli pa lahko spremenite v svoja.

2. Nato kliknite OK gumb in zdaj je dovoljeno vnašati samo besedilne vrednosti, ki ustrezajo navedenim merilom, glejte sliko zaslona:

Opombe: Morda obstajajo drugačna merila za preverjanje veljavnosti podatkov, ki jih lahko uporabite, lahko ustvarite lastne formule za druga merila, kot želite.

Najboljša pisarniška orodja za produktivnost

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

zavihek kte 201905


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • 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!
Comments (15)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi,
I am capturing department (10 departments), rating (5,4,3,2 & 1)and reward type criteria (X, Y and Z) of which i calculate the overall callibration based on certain %. Eg. i have 10 departments having each department 15-20 employees. Now i need a formula wherein in the table against the particular department, lets say department1 to populate the % of employees (only with rating 5 and criteria X) against the total employee of (criteria X and Z).
This comment was minimized by the moderator on the site
I have data in two different columns and i have to apply this with two different criteria. For example, first criteria shows result if a particular cell contains a text "Highlighter" and second criteria show when the cell contain text "Pointer".
Highlighter test results are different than the pointer results and we make separate validation of both data. Is this possible to extract the validation of each criteria in a single cell with no overlapping?
This comment was minimized by the moderator on the site
Hey I want a parent child relationship sort of multiple drop down lists.
For example, if in column one some selects 'a' among the list of a, b & c
Then in the second column the child category of 'a' which is x, y & z should appear
How can that be done?
This comment was minimized by the moderator on the site
This comment was minimized by the moderator on the site
Hi Team, I failed to build example no.1. I copied and pasted the formula:  =OR(A2<$C$2,COUNTIF($D$2:$D$7,A2)=1) and failed. Where is the bug ? Can you please assist ? THX a lot.
This comment was minimized by the moderator on the site
Hello Frank,
Glad to help. The reason why the data validation failed is that you probably didn't choose the data validation cell range. Please select the cell range first (say A2:A10), then click Data > Data Validation > Data Validation to input the formula =OR(A2<$C$2,COUNTIF($D$2:$D$7,A2)=1). Please see the screenshots I uploaded here. Hope it can solve your problem. Have a nice day.
Sincerely,
Mandy
This comment was minimized by the moderator on the site
How can I apply a data validation to multiple columns?
This comment was minimized by the moderator on the site
Hello Fabian,To apple a data validation to multiple columns, the trick is almost identical to the ways we mentioned above. The most important thing is that you select the columns first, then you can set up the rules in the Data Validation dialog box. Plesase have a try. Any question, please feel free to contact us. Have a nice day.Sincerely,Mandy
This comment was minimized by the moderator on the site
i am confuse
This comment was minimized by the moderator on the site
Hi,
I need to validate multiple columns before entering a value into cell. please find my requirement below,
I have 3 columns for example,

A1-Total Credit limit (Mandatory field)
Should have a minimum amount of 100 and multiples of 100:
=AND(ISNUMBER(A1),A1>=100, MOD(A1,100)=0) - Solved!
B1- Weekly Credit limit (Optional filed)
User should be able to input a value only when A1 is filled, Should not be greater than A1, Minimum amount 100 and multiples of 100
=AND(A1<>"",ISNUMBER(B1),B1<=A1, MOD(B1,100=0) - Solved!
C1-Daily Credit limit (Optional field)
User should be able to input a value only when A1 is filled, Should not be greater than A1&B1, Minimum amount 100 and multiples of 100

I need to add following validation, Please Help
I have to check if A1 is filled or not, then C1 shouldn't be greater than A1 and C1 min value should be 100 and multiples of 100 - I have answer to this
How can add a validation on B1 because B1 is optional it can have value and cannot. so if there's a value I have to make sure C1 is not greater than B1 else ignore B1?

How can i have this in data validation?

Thanks once again for the help
This comment was minimized by the moderator on the site
how can I add another Data validation function with this list function. for example I added a list of "yes" and "no". but I also want to add an OR function to this so that if "yes" is selected in one column then others only have to be "no".
This comment was minimized by the moderator on the site
Is it possible to apply data validation on the calcuated column attribute?
This comment was minimized by the moderator on the site
i'm looking for the ability to have a data validation which allows the user to select multiple choices. I.e. the list pops up and user can select Apple, grape, and/or banana. If they select all three, it shows in cell as: "apple, grade, banana". if only 1: "grape" etc... any ideas?
This comment was minimized by the moderator on the site
Hello, jeffe,
Maybe the below article can help you to deal with your problem, please check it:
https://www.extendoffice.com/documents/excel/2448-excel-drop-down-list-multiple-selection.html
This comment was minimized by the moderator on the site
Any idea how to have multiple data validation and a drop down. Drop down uses "List" and this example uses "Custom". Basically if the correct data is present in another column, I want the drop down to be able to be selected to input data. Otherwise if the dropdown is selected the error message appears.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations