Šūnas

Datu validācijas lietošana šūnām

Datu validācijas lietošana šūnām

Datu validāciju var izmantot, lai ierobežotu datu vai vērtību tipu, ko lietotāji var ievadīt šūnā. Viens no biežākajiem datu validācijas lietojumiem ir nolaižamā saraksta izveide.

Lejupielādējiet mūsu piemērus

Varat lejupielādēt parauga darbgrāmatu ar visiem datu validācijas piemēriem, kas izmantoti šajā rakstā. Varat sekot līdzi vai izveidot savu datu validācijas scenāriju.

Lejupielādējiet Excel datu validācijas piemērus

Datu validācijas pievienošana šūnai vai diapazonam

Piezīme.: Lai pievienotu jebkāda veida datu validāciju, izpildiet pirmās trīs darbības, kas minētas šajā sadaļā. 4.–8. darbība attiecas uz nolaižamā saraksta izveidi.

  1. Atlasiet vismaz vienu validējamu šūnu.

  2. Cilnes Dati grupā Datu rīkinoklikšķiniet uz Datu validācija.

    Opcija Datu validācija atrodas cilnes Dati grupa Datu rīki
  3. Cilnes Iestatījumi lodziņā Atļaut atlasiet Saraksts.

    Dialoglodziņa Datu validācija cilne Iestatījumi
  4. Lodziņā Avots ievadiet saraksta vērtības, atdalot tās ar komatiem. Piemērs.

    1. Lai atbildi uz kādu jautājumu, piemēram, “Vai jums ir bērni?”, ierobežotu ar divām izvēlēm, ierakstiet Jā,Nē.

    2. Lai piegādātāja kvalitātes reputāciju ierobežotu līdz trim vērtējumiem, ierakstiet Zema,Vidēja,Augsta.

      Piezīme.: Šādi ieteicams rīkoties tikai ar tādiem saraksta vienumiem, kurus nav paredzēts mainīt. Ja saraksts var mainīties vai ja laika gaitā nepieciešams pievienot vai noņemt vienumus, ieteicams izpildīt tālāk aprakstīto ieteicamās prakses darbību.

      Ieteicamā prakse. Saraksta ierakstus var arī izveidot, atsaucoties uz šūnu diapazonu citviet darbgrāmatā. Visefektīvākais veids ir izveidot sarakstu un pēc tam formatēt to kā Excel tabulu (Cilnē Sākums atlasiet Stili > Formatēt kā tabulu > izvēlieties vajadzīgo tabulas stilu). Pēc tam atlasiet tabulas pamatdatu diapazonu (tā ir tabulas daļa, kura satur tikai vajadzīgo sarakstu bez tabulas galvenes, šajā gadījumā — Nodaļa) un piešķiriet tam jēgpilnu nosaukumu, ierakstot to nosaukuma lodziņā virs kolonnas A.

      Nosaukuma lodziņā ierakstiet jēgpilnu saraksta nosaukumu

    Datu validācijas lodziņā Avots tagad nav jāievada saraksta vērtības, bet gan jānorāda tikko definētais nosaukums, pirms kura jāievada vienādības zīme (=).

    Pirms tabulas nosaukuma pievienojiet vienādības zīmi (=)

    Lielākā priekšrocība, izmantojot tabulu, ir tā, ka varat pievienot vienumus sarakstam vai tos noņemt, un datu validācijas saraksts tiek atjaunināts automātiski.

    Piezīme.: Ieteicams sarakstus ievietot atsevišķā darblapā (ja nepieciešams, to var paslēpt), lai neviens tos nevarētu rediģēt.

  5. Pārliecinieties, vai ir atzīmēta izvēles rūtiņa Nolaižamais saraksts šūnā, jo citādi nevar redzēt nolaižamo bultiņu blakus šūnai.

    Nolaižamais saraksts šūnā blakus šūnai
  6. Lai norādītu, kā apstrādāt tukšas (Null) vērtības, atzīmējiet vai notīriet izvēles rūtiņu Ignorēt tukšus.

    Piezīme.: Ja atļautās vērtības ir norādītas šūnu diapazonā, kam ir definēts nosaukums, un šajā diapazonā ir tukša šūna, tad, atzīmējot izvēles rūtiņu Ignorēt tukšus, validētajā šūnā var ievadīt jebkuru vērtību. Tas attiecas arī uz visām šūnām, uz kurām atsaucas validācijas formulas: ja kāda no šūnām, uz kuru ir atsauce, ir tukša, tad, atzīmējot izvēles rūtiņu Ignorēt tukšus, validētajā šūnā var ievadīt jebkuru vērtību.

  7. Mēģiniet šūnās ievadīt gan derīgus, gan nederīgus datus, lai pārliecinātos, ka iestatījumi darbojas, kā paredzēts, un ziņojumi tiek parādīti, kad tas ir nepieciešams.

Piezīmes.: 

  • Kad nolaižamais saraksts ir izveidots, pārliecinieties, vai tas darbojas tā, kā vēlaties. Piemēram, varat pārbaudīt, vai šūna ir pietiekami plata, lai rādītu visus ierakstus.

  • Ja nolaižamā saraksta ierakstu saraksts ir citā darblapā un jūs nevēlaties atļaut lietotājiem to skatīt vai veikt izmaiņas, apsveriet iespēju atbilstošo darblapu paslēpt un aizsargāt. Papildinformāciju par darblapas aizsargāšanu skatiet rakstā Šūnu bloķēšana aizsardzības nolūkos.

  • Datu validācijas noņemšana — atlasiet šūnu vai šūnas, kurās ir validācija, ko vēlaties dzēst, pēc tam dodieties uz Dati > Datu validācija un datu validācijas dialoglodziņā nospiediet pogu Notīrīt visu un pēc tam nospiediet Labi.

Nākamajā tabulā ir parādīti citi datu validācijas tipi un veidi, kā tos pievienot darblapām.

Lai to izdarītu

Izpildiet šādas darbības:

Ierobežojiet datu ievadi līdz veseliem skaitļiem noteiktā diapazonā

  1. Izpildiet 1.–3. darbību, kas iepriekš aprakstītas sadaļā Datu validācijas pievienošana šūnai vai diapazonam.

  2. Sarakstā Atļaut atlasiet Vesels skaitlis.

  3. Lodziņā Dati atlasiet vajadzīgo ierobežojuma tipu. Piemēram, lai iestatītu augšējo un apakšējo robežu, atlasiet starp.

  4. Ievadiet minimālo, maksimālo vai arī konkrētu atļauto vērtību.

    Validācijas kritēriju dialoglodziņš

    Varat ievadīt arī formulu, kura atgriež skaitlisku vērtību.

    Piemēram, jūs validējat datus šūnā F1. Lai šajā šūnā iestatītu minimālo atvilkumu robežu, reizinot ar bērnu skaitu ar divi, atlasiet ir lielāka vai vienāda ar lodziņā Dati un ievadiet formulu =2*F1 lodziņā Minimums.

Datu ievades ierobežošana: decimālskaitlis noteiktā diapazonā.

  1. Izpildiet 1.–3. darbību, kas iepriekš aprakstītas sadaļā Datu validācijas pievienošana šūnai vai diapazonam.

  2. Lodziņā Atļaut atlasiet Decimāldaļa.

  3. Lodziņā Dati atlasiet vajadzīgo ierobežojuma tipu. Piemēram, lai iestatītu augšējo un apakšējo robežu, atlasiet starp.

  4. Ievadiet minimālo, maksimālo vai arī konkrētu atļauto vērtību.

    Varat ievadīt arī formulu, kura atgriež skaitlisku vērtību. Piemēram, lai iestatītu maksimālo robežu komisijas maksām un prēmijām kā 6% no pārdevēja atalgojuma šūnā E1, lodziņā Dati atlasiet ir mazāka vai vienāda ar un lodziņā Maksimums ievadiet formulu =E1*6%.

    Piezīme.: Lai lietotājam atļautu ievadīt procentuālo vērtību, piemēram, 20%, lodziņā Atļaut atlasiet Decimāldaļskaitlis, lodziņā Dati atlasiet vajadzīgā tipa ierobežojumu; minimālo, maksimālo vai noteikto vērtību ievadiet kā decimāldaļu, piemēram, .2, un pēc tam norādiet datu validācijas šūnas attēlojumu kā procentuālu vērtību, atlasot šūnu un cilnes Sākums grupā Skaitlis noklikšķinot uz Procentu stils Pogas attēls .

Datu ievades ierobežošana: datums noteiktā datumu diapazonā.

  1. Izpildiet 1.–3. darbību, kas iepriekš aprakstītas sadaļā Datu validācijas pievienošana šūnai vai diapazonam.

  2. Lodziņā Atļaut atlasiet Datums.

  3. Lodziņā Dati atlasiet vajadzīgo ierobežojuma tipu. Piemēram, lai atļautu datumus pēc noteikta datuma, atlasiet ir lielāka nekā.

  4. Ievadiet sākuma, beigu vai konkrētu atļauto datumu.

    Varat ievadīt arī formulu, kas atgriež datumu. Piemēram, lai iestatītu laika periodu starp šodienas datumu un 3 dienām kopš šodienas datuma, lodziņā Dati atlasiet ir starp, lodziņā Sākums ievadiet =TODAY() un lodziņā Beigu datums ievadiet =TODAY()+3.

    Validācijas kritēriju iestatījumi datuma ierobežošanai noteiktā laika diapazonā

Datu ievades ierobežošana: laiks noteiktā diapazonā.

  1. Izpildiet 1.–3. darbību, kas iepriekš aprakstītas sadaļā Datu validācijas pievienošana šūnai vai diapazonam.

  2. Lodziņā Atļaut atlasiet Laiks.

  3. Lodziņā Dati atlasiet vajadzīgo ierobežojuma tipu. Piemēram, lai atļautu laiku pirms noteikta dienas laika, atlasiet ir mazāk par.

  4. Ievadiet sākuma, beigu vai noteiktu atļaujamo laiku. Ja vēlaties ievadīt konkrētus laikus, izmantojiet laika formātu hh:mm.

    Pieņemsim, ka šūnā E2 ir iestatīts sākuma laiks (8:00), bet šūnā F2 — beigu laiks (17:00), un jūs vēlaties ierobežot sapulču laikus līdz šim laika diapazonam; lodziņā Dati atlasiet ir starp, lodziņā Sākuma laiks ievadiet =E2, bet lodziņā Beigu laiks ievadiet =F2.

    Validācijas iestatījumi laika ievades ierobežošanai laika diapazonā

Datu ievades ierobežošana: noteikta garuma teksts.

  1. Izpildiet 1.–3. darbību, kas iepriekš aprakstītas sadaļā Datu validācijas pievienošana šūnai vai diapazonam.

  2. Lodziņā Atļaut atlasiet Teksta garums.

  3. Lodziņā Dati atlasiet vajadzīgo ierobežojuma tipu. Piemēram, lai neatļautu pārsniegt noteiktu rakstzīmju skaitu, atlasiet ir mazāks vai vienāds ar.

  4. Šādā gadījumā mēs vēlamies ierobežot ievadi līdz 25 rakstzīmēm, tāpēc lodziņā Dati izvēlieties ir mazāka vai vienāda ar un lodziņā Maksimālais ievadiet 25.

    Datu validācijas piemērs ar ierobežotu teksta garumu

Atļauto vērtību aprēķins, izmantojot citas šūnas saturu.

  1. Izpildiet 1.–3. darbību no iepriekš minētās sadaļas Datu validācijas pievienošana šūnai vai diapazonam. Lodziņā Atļaut atlasiet vajadzīgo datu tipu.

  2. Lodziņā Dati atlasiet vajadzīgo ierobežojuma tipu.

  3. Lodziņā vai lodziņos, kas atrodas zem lodziņa Dati, noklikšķiniet uz šūnas, kas jāizmanto, lai norādītu atļautās darbības.

    Piemēram, lai atļautu konta ierakstus tikai tad, ja rezultāts nepārsniedz budžetu šūnā E1, atlasiet Atļaut > Vesels skaitlis > Dati, ir mazāka vai vienāda ar un Maksimums >= =E1.

    Validācijas iestatījumi aprēķiniem atbilstoši citas šūnas saturam

Piezīme.: Nākamajos piemēros tiek izmantota pielāgošanas opcija, kurā rakstāt formulas, lai iestatītu savus nosacījumus. Jums nav jāuztraucas par lodziņā Dati parādīto, jo tas ir atspējots ar pielāgošanas opciju.

Lai pārliecinātos, vai

Ievadiet šo formulu

Šūna, kurā ir produkta ID (C2), vienmēr sākas ar standarta prefiksu ID- un ir vismaz 10 (vairāk nekā 9) rakstzīmes gara.

=AND(LEFT(C2, 3) ="ID-",LEN(C2) > 9)

6. piemērs Formulas datu validācijā

Šūnā, kurā ir produkta nosaukums (D2), ir tikai teksts.

=ISTEXT(D2)

2. piemērs Formulas datu validācijā

Šūnas, kurā ir kādas personas dzimšanas diena (B6), vērtībai ir jābūt lielākai par gadu skaitu, kas iestatīts šūnā B4.

=IF(B6<=(TODAY()-(365*B4)),TRUE,FALSE)

Datu validācijas piemērs ar ievades ierobežošanu pēc minimālā vecuma

Visi dati šūnu diapazonā A2:A10 satur unikālas vērtības.

=COUNTIF($A$2:$A$10,A2)=1

4. piemērs Formulas datu validācijā

Piezīme.: Vispirms ir jāievada datu validācijas formula šūnai A2, pēc tam kopējiet A2 uz A3:A10, lai otrais COUNTIF arguments atbilstu pašreizējai šūnai. T.i., daļa A2)=1 mainīsies uz A3)=1, A4)=1 utt.

Papildinformācija

Nodrošiniet, lai e-pasta adresē šūnā B4 ietilptu simbols @.

=ISUMBER(FIND("@",B4)

Datu validācijas piemērs, kurā nodrošināta e-pasta adrese ar simbolu @

  • Kāpēc datu validācijas komanda nav iespējota lentē? Komanda var nebūt pieejama tālāk norādīto iemeslu dēļ.

    • Microsoft Excel tabula, iespējams, ir saistīta ar SharePoint vietni. Excel tabulai, kas ir saistīta ar SharePoint vietni, datu validāciju nevar pievienot. Lai pievienotu datu validāciju, Excel tabulai ir jānoņem saite vai arī tabula ir jāpārvērš par diapazonu.

    • Pašlaik, iespējams, tiek ievadīti dati.  Cilnes Dati komanda Datu validācija nav iespējota, kamēr šūnā ievadāt datus. Lai pabeigtu datu ievadi, nospiediet taustiņu ENTER vai ESC.

    • Darblapa, iespējams, ir aizsargāta vai koplietota. Datu validācijas iestatījumus nevar mainīt, ja darbgrāmata ir koplietota vai aizsargāta. Lai uzzinātu, kā pārtraukt darbgrāmatas koplietošanu vai aizsargāšanu, skatiet sadaļu Darbgrāmatas aizsardzība.

  • Vai var mainīt fonta lielumu? Nē, fonta lielums ir fiksēts. Vienīgais veids, kā mainīt parādāmo lielumu, ir Excel loga labajā apakšējā stūrī pielāgot ekrāna tuvinājumu. Tomēr varat izmantot ActiveX kombinēto lodziņu. Skatiet rakstu Sarakstlodziņa vai kombinētā lodziņa pievienošana darblapai.

  • Vai pastāv veids, kā datu validācija var veikt automātisko aizpildīšanu vai automātisko atlasi rakstīšanas laikā? Nē, bet, ja izmantojat ActiveX kombinēto lodziņu, jums ir pieejama šī funkcija.

  • Vai datu validācijas sarakstā var veikt vairākatlasi? Nē, ja vien neizmantojat ActiveX kombinēto lodziņu vai sarakstlodziņu.

  • Vai var atlasīt vienumu datu validācijas sarakstā un ar to aizpildīt citu sarakstu? Jā. To sauc par pakārtoto datu validāciju. Papildinformāciju skatiet rakstā Pakārtoto nolaižamo sarakstu izveide.

  • Kā var noņemt visas datu validācijas darblapā? Varat izmantot dialoglodziņu Došanās uz > Īpaši vienumi. Cilne Sākums > Rediģēšana > Meklēt un atlasīt (vai nospiediet tastatūras taustiņu F5 vai Ctrl+G), pēc tam Īpaši vienumi > Datu validācija un atlasiet Visas (lai atrastu visas šūnas ar datu validāciju) vai Tāda pati (lai atrastu šūnas ar noteiktiem datu validācijas iestatījumiem).

    Dialoglodziņš Došanās uz īpašiem vienumiem

    Pēc tam atveriet datu validācijas dialoglodziņu (cilnē Dati > Datu validācija), nospiediet pogu Notīrīt visu un pēc tam nospiediet Labi.

  • Vai lietotājam var likt veikt ierakstu šūnā(s) ar datu validāciju? Nē, bet varat izmantot VBA (Visual Basic for Applications), lai pie noteiktiem nosacījumiem (piemēram, pirms lietotājs saglabā vai aizver darbgrāmatu) pārbaudītu, vai lietotājs ir veicis ierakstu. Ja lietotājs nav veicis atlasi, varat atcelt šo notikumu un liegt turpināt darbību, kamēr nav veikta atlase.

  • Kā var iekrāsot šūnas atbilstoši atlasei datu validācija sarakstā? Varat izmantot nosacījumformatēšanu. Šajā gadījumā jāizmanto opcija Formatēt tikai šūnas, kas satur.

    Formatēt tikai šūnas, kuras satur opciju
  • Kā validēt e-pasta adresi? Var izmantot metodi Pielāgots > Formula un pārbaudīt, vai ieraksts satur simbolu @. Šajā gadījumā jāizmanto formula =ISNUMBER(FIND(“@”,D2)). Funkcija FIND meklē simbolu @; ja tas tiek atrasts, tā atgriež tā skaitlisko pozīciju teksta virknē un ļauj veikt ierakstu. Ja simbols netiek atrasts, funkcija FIND atgriež kļūdu un ierakstu nevar veikt.

Vai jums ir jautājums par kādu konkrētu funkciju?

Jautājuma publicēšana Excel kopienas forumā

Palīdzība Excel uzlabošanā

Vai jums ir ieteikumi, kā mēs varam uzlabot nākamo programmas Excel versiju? Ja tā ir, lūdzu, skatiet tēmas sadaļā Excel User Voice.

Skatiet arī

Papildinformācija par datu validāciju

Video: nolaižamo sarakstu izveide un pārvaldība

Elementu pievienošana nolaižamajam sarakstam vai noņemšana no tā

Nolaižamā saraksta noņemšana

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta aģentiem.

×