„Excel“ duomenų patvirtinimo vadovas

Įvadas

Duomenų patvirtinimas yra „Excel“ funkcija, naudojama valdyti, ką vartotojas gali įvesti į langelį. Pvz., Galite naudoti duomenų patvirtinimą, kad įsitikintumėte, jog reikšmė yra skaičius nuo 1 iki 6, įsitikinkite, kad data įvyksta per kitas 30 dienų, arba įsitikinkite, kad teksto įrašas yra trumpesnis nei 25 simboliai.

Duomenų patvirtinimas gali tiesiog parodyti vartotojui pranešimą, kuriame nurodoma, kas leidžiama, kaip parodyta toliau:

Duomenų patvirtinimas taip pat gali sustabdyti netinkamą vartotojo įvestį. Pvz., Jei nepavyksta patvirtinti produkto kodo, galite rodyti tokį pranešimą:

Be to, duomenų patvirtinimas gali būti naudojamas, kad išskleidžiamajame meniu vartotojui būtų pateiktas iš anksto nustatytas pasirinkimas:

Tai gali būti patogus būdas suteikti vartotojui tiksliai tas vertes, kurios atitinka reikalavimus.

Duomenų patvirtinimo valdikliai

Duomenų tikrinimas įgyvendinamas pagal taisykles, apibrėžtas „Excel“ vartotojo sąsajoje, juostos skirtuke „Duomenys“.

kelis jei tada teiginius „Excel 2010“

Svarbus apribojimas

Svarbu suprasti, kad duomenų patvirtinimą galima lengvai įveikti. Jei vartotojas nukopijuoja duomenis iš langelio be patvirtinimo į langelį su duomenų patvirtinimu, patvirtinimas sunaikinamas (arba pakeičiamas). Duomenų patvirtinimas yra geras būdas informuoti vartotojus, kas leidžiama ar ko tikimasi, tačiau tai nėra patikimas būdas užtikrinti įvestį.

Duomenų patvirtinimo taisyklių apibrėžimas

Duomenų patvirtinimas yra apibrėžtas lange, kuriame yra 3 skirtukai: Nustatymai, Įvesties pranešimas ir Klaidos įspėjimas:

Nustatymų skirtuke įvedate patvirtinimo kriterijus. Yra keletas integruotų patvirtinimo taisyklių su įvairiomis parinktimis, arba galite pasirinkti Pasirinktinis ir naudoti savo formulę, kad patvirtintumėte įvestį, kaip parodyta toliau:

Skirtuke Įvesties pranešimas apibrėžiamas pranešimas, kuris turi būti rodomas pasirinkus langelį su patvirtinimo taisyklėmis. Šis įvesties pranešimas yra visiškai neprivalomas. Jei nenustatytas įvesties pranešimas, vartotojui pasirinkus langelį, kuriame taikomas duomenų patvirtinimas, joks pranešimas nerodomas. Įvesties pranešimas neturi įtakos tam, ką vartotojas gali įvesti - jis tiesiog parodo pranešimą, kad vartotojas žinotų, kas leidžiama ar ko tikimasi.

Skirtukas Klaidų įspėjimas kontroliuoja, kaip vykdomas patvirtinimas. Pavyzdžiui, kai stilius nustatytas kaip „Stop“, netinkami duomenys suaktyvina langą su pranešimu ir įvestis neleidžiama.

Vartotojas mato tokį pranešimą:

Kai stilius nustatytas kaip Informacija arba Įspėjimas, su pasirinktiniu pranešimu rodoma kita piktograma, tačiau vartotojas gali ignoruoti pranešimą ir įvesti vertes, kurios nepraleidžia patvirtinimo. Žemiau esančioje lentelėje apibendrinama kiekvienos įspėjimo apie klaidą parinkties elgsena.

Duomenų patvirtinimo parinktys

Kai sukuriama duomenų tikrinimo taisyklė, yra aštuonios vartotojo įvesties patvirtinimo parinktys:

Bet kokia vertė - patvirtinimas neatliekamas. Pastaba: jei duomenų tikrinimas anksčiau buvo taikomas naudojant nustatytą įvesties pranešimą, pranešimas vis tiek bus rodomas pasirinkus langelį, net jei pasirinkta bet kuri vertė.

Visas skaičius - leidžiami tik sveikieji skaičiai. Pasirinkus viso skaičiaus parinktį, tampa prieinamos kitos parinktys, skirtos dar labiau apriboti įvestį. Pavyzdžiui, galite reikalauti sveiko skaičiaus nuo 1 iki 10.

Dešimtainis - veikia kaip viso skaičiaus parinktis, tačiau leidžia dešimtainę reikšmę. Pavyzdžiui, jei parinktis Dešimtainis yra sukonfigūruota taip, kad leistų reikšmes nuo 0 iki 3, leidžiamos tokios vertės kaip .5, 2.5 ir 3.1.

Sąrašas - leidžiamos tik vertės iš anksto nustatyto sąrašo. Vertės pateikiamos vartotojui kaip išskleidžiamojo meniu valdiklis. Leidžiamos reikšmės gali būti koduotos tiesiai į skirtuką Nustatymai arba nurodytos kaip diapazonas darbalapyje.

Data - leidžiamos tik datos. Pavyzdžiui, galite reikalauti datos nuo 2018 m. Sausio 1 d. Iki 2021 m. Gruodžio 31 d. Arba datos po 2018 m. Birželio 1 d.

Laikas - leidžiami tik laikai. Pavyzdžiui, galite reikalauti laiko nuo 9:00 iki 17:00 arba leisti tik po 12:00.

Teksto ilgis - patvirtina įvestį pagal simbolių ar skaitmenų skaičių. Pavyzdžiui, jums gali prireikti kodo, kurį sudarytų 5 skaitmenys.

Pasirinktinis - patvirtina vartotojo įvestį pagal pasirinktinę formulę. Kitaip tariant, galite parašyti savo formulę, kad patvirtintumėte įvestį. Tinkintos formulės labai išplečia duomenų patvirtinimo galimybes. Pvz., Galite naudoti formulę, kad įsitikintumėte, jog reikšmė yra didžioji, vertėje yra „xyz“ arba data yra savaitės diena per kitas 45 dienas.

Nustatymų skirtuke taip pat yra du žymės langeliai:

Ignoruoti tuščią - nurodo „Excel“ nepatvirtinti langelių, kuriuose nėra jokios vertės. Praktiškai atrodo, kad šis nustatymas turi įtakos tik komandai „apskristi netinkamus duomenis“. Kai įjungta, tušti langeliai nėra apskritimai, net jei jie nepavyksta patvirtinti.

Taikykite šiuos pakeitimus kitoms ląstelėms su tais pačiais nustatymais - šis nustatymas atnaujins patvirtinimą, taikomą kitoms ląstelėms, kai jis atitinka (originalų) redaguojamo langelio (-ių) patvirtinimą.

„Excel“ palikite langelį tuščią, jei nėra duomenų

Pastaba: taip pat galite rankiniu būdu pasirinkti visas ląsteles, kuriose duomenų patvirtinimas taikomas naudojant „Go To + Special“, kaip paaiškinta toliau.

Paprastas išskleidžiamasis meniu

Galite pateikti išskleidžiamąjį parinkčių meniu, sukietinę reikšmes nustatymų laukelyje arba pasirinkdami diapazoną darbalapyje. Pavyzdžiui, norėdami apriboti įrašus iki veiksmų „PIRKTI“, „PALAIKYTI“ arba „PARDUOTI“, galite įvesti šias vertes atskirtas kableliais, kaip parodyta žemiau:

Pritaikius darbalapio langelį, išskleidžiamasis meniu veikia taip:

Kitas būdas pateikti reikšmes išskleidžiamajame meniu yra naudoti darbalapio nuorodą. Pavyzdžiui, kai dydžiai (t. Y. Maži, vidutiniai ir tt) yra diapazone F3: F6, šį diapazoną galite pateikti tiesiogiai duomenų patvirtinimo nustatymų lange:

Atkreipkite dėmesį, kad diapazonas įvedamas kaip absoliutus adresas kad jis nesikeistų, kai duomenų patvirtinimas taikomas kitoms ląstelėms.

Patarimas: Spustelėkite mažos rodyklės piktogramą, esančią šaltinio lauko dešinėje, kad pasirinktumėte tiesiai darbalapyje, kad nereikėtų rankiniu būdu įvesti diapazono.

Taip pat galite naudoti pavadinti diapazonai norėdami nurodyti vertes. Pavyzdžiui, naudodami pavadintą diapazoną, pavadintą „dydžiai“ F3: F7, pavadinimą galite įvesti tiesiai lange, pradedant lygybės ženklu:

Pavadinti diapazonai yra automatiškai absoliutūs, todėl jie nesikeis, kai duomenų patvirtinimas bus pritaikytas skirtingoms ląstelėms. Jei įvardyti diapazonai jums nauji, šiame puslapyje yra gera apžvalga ir daugybė susijusių patarimų .

Patarimas - jei naudojate lentelę išskleidžiamosioms vertėms, „Excel“ automatiškai išplės arba sutrauks lentelę, kai pridedamos arba pašalinamos išskleidžiamosios meniu vertės. Kitaip tariant, „Excel“ automatiškai išskleidžiamąjį meniu sinchronizuoja su lentelės vertėmis, kai vertės keičiamos, pridedamos ar pašalinamos. Jei dar nesate susipažinę su „Excel“ lentelėmis, galite pamatyti a demo šiame vaizdo įraše ant stalo nuorodų.

Duomenų patvirtinimas naudojant pasirinktinę formulę

Duomenų patvirtinimo formulės turi būti loginės formulės, kurios grąžina TRUE, kai įvestis yra teisinga, ir FALSE, kai įvestis neteisinga. Pavyzdžiui, jei norite įvesti bet kurį skaičių į langelį A1, galite naudoti funkciją ISNUMBER pagal tokią formulę:

 
= ISNUMBER (A1)

Jei naudotojas A1 įveda tokią vertę kaip 10, ISNUMBER pateikia TRUE ir duomenys patvirtinami sėkmingai. Jei A1 įveda tokią vertę kaip „obuolys“, ISNUMBER pateikia FALSE ir duomenų patvirtinimas nepavyksta.

Norėdami įgalinti duomenų patvirtinimą naudodami formulę, nustatymų skirtuke pasirinkite „Pasirinktinis“, tada įveskite formulę formulės juostoje, prasidedančią lygybės ženklu (=), kaip įprasta.

Trikčių šalinimo formulės

„Excel“ nepaiso duomenų patvirtinimo formulių, kurios pateikia klaidas. Jei formulė neveikia ir negalite suprasti, kodėl, nustatykite netikras formules, kad įsitikintumėte, jog formulė veikia taip, kaip tikitės. Fiktyvios formulės yra tiesiog duomenų patvirtinimo formulės, įvestos tiesiai į darbalapį, kad galėtumėte lengvai pamatyti, ką jos grąžina. Žemiau esančiame ekrane rodomas pavyzdys:

Kai tik dirbtinė formulė veiks taip, kaip norite, tiesiog nukopijuokite ir įklijuokite ją į duomenų patvirtinimo formulės sritį.

Jei ši netikros formulės idėja jus glumina, žiūrėkite šį vaizdo įrašą , kuriame parodyta, kaip naudoti netikras formules tobulinant sąlyginio formato formules. Sąvoka yra visiškai ta pati.

Duomenų patvirtinimo formulės pavyzdžiai

Tinkintų duomenų patvirtinimo formulių galimybės yra beveik neribotos. Štai keletas pavyzdžių įkvėpimui:

Jei norite leisti tik 5 simbolių reikšmes, prasidedančias „z“, galite naudoti:

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

Ši formulė grąžina TRUE tik tada, kai kodas yra 5 skaitmenų ir prasideda „z“. Pagal šią formulę abi apskritimo reikšmės grąžina FALSE.

Jei norite leisti tik datą per 30 dienų nuo šiandien:

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

Jei norite leisti tik unikalias vertes:

 
= COUNTIF (range,A1)<2

Norėdami leisti tik el. Pašto adresą

 
=ISUMBER( FIND ('@',A1)

Duomenų tikrinimas, kad apskritimas negalioja

Pritaikius duomenų tikrinimą, galite paprašyti „Excel“ apvesti anksčiau įvestas netinkamas vertes. Juostelės skirtuke Duomenys spustelėkite Duomenų patvirtinimas ir pasirinkite „Apskritimas netinkamus duomenis“:

Pavyzdžiui, toliau pateiktame ekrane rodomos apskritimo reikšmės, kurių patvirtinti naudojant šią pasirinktinę formulę nepavyksta:

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

Raskite langelius naudodami duomenų patvirtinimą

Norėdami rasti langelius su pritaikytu duomenų patvirtinimu, naudokite dialogo langą Eiti į> Specialusis. Įveskite spartųjį klavišą „Control + G“, tada spustelėkite mygtuką Special. Kai pasirodys dialogo langas, pasirinkite „Duomenų patvirtinimas“:

kaip parodyti lygtį Excel grafike

Kopijuoti duomenų patvirtinimą iš vienos ląstelės į kitą

Norėdami kopijuoti patvirtinimą iš vieno langelio į kitą. Paprastai nukopijuokite langelį (-ius), kuriame yra norimas duomenų patvirtinimas, tada naudokite Įklijuoti specialųjį + Patvirtinimas. Kai pasirodys dialogo langas, įveskite „n“, kad pasirinktumėte patvirtinimą, arba spustelėkite patvirtinimą pele.

Pastaba: galite naudoti sparčiuosius klavišus „Control“ + „Alt“ + V, kad iškviestumėte „Paste Special“ be pelės.

Išvalyti visų duomenų patvirtinimą

Norėdami išvalyti visus duomenų patvirtinimus iš langelių diapazono, pasirinkite ir spustelėkite juostelės skirtuko Duomenys mygtuką Duomenų patvirtinimas. Tada spustelėkite mygtuką „Išvalyti viską“:

Norėdami išvalyti visus duomenų patvirtinimo duomenis iš darbalapio, pasirinkite visą darbalapį, tada atlikite tuos pačius aukščiau nurodytus veiksmus.