Vlastné vzorce na overenie údajov

Tento tutoriál vám ukáže, ako vytvoriť vlastné vzorce pri overovaní údajov v Exceli a Tabuľkách Google

Overenie údajov - musí začať - Excel

Môžeme napísať vlastný vzorec, aby sme zaistili, že údaje v bunke začínajú určitým textom.

1. Zvýraznite požadovaný rozsah, napr .: B3: B8.
2. Na páse s nástrojmi vyberte Dáta> Nástroje údajov> Overenie údajov.

3. Vyberte Vlastné z rozbaľovacieho poľa Povoliť a potom zadajte nasledujúci vzorec:

= PRESNE (VĽAVO (B3,4), „FRU-“)

Vzorec používa 2 funkcie PRESNE a VĽAVO na určenie, či sú prvé 4 znaky zadané do bunky identické s „FRU-“

4. Ak chcete pre svojho používateľa pridať vstupnú správu, kliknite na Vstupná správa.

5. Zaistite začiarkavacie políčko „Zobraziť vstupnú správu, keď je vybratá bunka“Je začiarknuté a potom zadajte požadovaný názov a správu.
6. Ak chcete pridať upozornenie na chybu, kliknite na Upozornenie na chybu.

7. Štýl môžete zmeniť z Prestaň buď Pozor alebo Informácie ak potrebujete, zadajte názov a chybové hlásenie.
8. Kliknite OK.
9. Ak ste použili Vstup Možnosť správy, na obrazovke sa zobrazí komentár informujúci užívateľa o pravidle.

10. Do bunky B3 napíšte „FRI-124“. Ak ste vybrali Prestaň, zobrazí sa nasledujúce okno so správou.

11. Ak ste vybrali Pozor, potom sa zobrazí toto okno so správou. To vám umožní pokračovať, ak zistíte, že údaje sú správne.

12. Ak ste vybrali Informácie, potom sa zobrazí toto okno so správou.

13. Ak kliknete OK, budete môcť pokračovať s nesprávnymi údajmi zadanými do bunky.
14. Kliknite Zrušiť na ukončenie správy alebo OK na zadanie textu do bunky.

Overenie údajov povoliť veľké písmená iba v programe Excel

Môžeme napísať vlastný vzorec, aby sme zaistili, že údaje v bunke povolia veľké písmená iba vtedy, ak je do bunky zadaný text.

1. Zvýraznite požadovaný rozsah, napr .: B3: B8.
2. Na páse s nástrojmi vyberte Dáta> Nástroje údajov> Overenie údajov.
3. Vyberte Vlastné z rozbaľovacieho poľa Povoliť a potom zadajte nasledujúci vzorec:

= PRESNE (B3, HORNE (B3))

Vzorec používa 2 funkcie PRESNE a HORNÉ na určenie, či je text zadaný do bunky napísaný veľkými písmenami. Bunky so zmesou čísla a textu sa považujú za text a čísla sa v pravidle ignorujú.

4. Kliknite OK.
5. Do bunky B3 napíšte „fru-124“.
6. Ak ste použili možnosť Upozornenie na chybu, zobrazí sa vaše vlastné varovné a chybové hlásenie. Ak ste túto možnosť nevyužili, zobrazí sa štandardné upozornenie.

7. Kliknutím na tlačidlo Zrušiť opustíte správu alebo kliknutím na položku Znova zadať správny text do bunky.
8. Do bunky B3 zadajte „123456“.
9. Toto bude povolené, pretože ide o číslo a nie o text.

V našom nasledujúcom príklade zaistíme, aby bolo možné do bunky zadať iba veľký text.

Overenie údajov Povoliť veľký text iba v programe Excel

Môžeme napísať vlastný vzorec. Validáciu údajov je možné použiť na zabezpečenie toho, aby údaje v bunke umožňovali iba písanie veľkých písmen

POZNÁMKA: Ak zadáte informácie do bunky, ktorá začína textom, ale obsahuje čísla, Excel bude brať do úvahy informačný text.

1. Zvýraznite požadovaný rozsah, napr .: B3: B8.
2. Na páse s nástrojmi vyberte Dáta> Nástroje údajov> Overenie údajov.
3. Vyberte Vlastné z rozbaľovacieho poľa Povoliť a potom zadajte nasledujúci vzorec:

= A (PRESNE (B3, HORNE (B3)), ISTEXT (B3))

Vzorec používa 4 funkcie A, PRESNE, HORNE a TEXT na určenie, či je text zadaný do buniek napísaný veľkými písmenami A zistiť, či sú zadané informácie skutočne textové, a nie čisté číslo.

4. Kliknite OK.
5. Do bunky B3 napíšte „fru-124“.
6. Ak ste použili možnosť Upozornenie na chybu, zobrazí sa vaše vlastné varovné a chybové hlásenie. Ak ste túto možnosť nevyužili, zobrazí sa štandardné upozornenie.

7. Kliknutím na tlačidlo Zrušiť opustíte správu alebo kliknutím na položku Znova zadať správny text do bunky.
8. Do bunky B3 zadajte „123456“.
9. Opäť sa zobrazí chybové hlásenie.
10. Kliknutím na tlačidlo Zrušiť opustíte správu alebo kliknutím na položku Znova zadať správny text do bunky.

Validácia údajov zabraňuje priestorom v programe Excel

Môžeme napísať vlastný vzorec, aby sme zaistili, že do údajov zadaných v rozsahu buniek nebudú zadané žiadne medzery.

1. Zvýraznite požadovaný rozsah, napr .: B3: B8.
2. Na páse s nástrojmi vyberte Dáta> Nástroje údajov> Overenie údajov.
3. Vyberte Vlastné z rozbaľovacieho poľa Povoliť a potom zadajte nasledujúci vzorec:

= B3 = NÁHRADA (B3, ””, „”)

Vzorec používa funkciu SUBSTITUTE na kontrolu, či medzery neexistujú.

4. Kliknite OK.
5. Do bunky B4 napíšte „FRU - 124“.
6. Ak ste použili možnosť Upozornenie na chybu, zobrazí sa vaše vlastné varovné a chybové hlásenie. Ak ste túto možnosť nevyužili, zobrazí sa štandardné upozornenie.

7. Kliknutím na tlačidlo Zrušiť opustíte správu alebo kliknutím na položku Znova zadať správny text do bunky.

Validácia údajov zabraňuje duplikátom v programe Excel

Môžeme napísať vlastný vzorec, ktorý nám zabráni zadávať duplicitné informácie do radu buniek.

1. Zvýraznite požadovaný rozsah, napr .: B3: B8.
2. Na páse s nástrojmi vyberte Dáta> Nástroje údajov> Overenie údajov.
3. V rozbaľovacom zozname Povoliť vyberte zákazníka a potom zadajte nasledujúci vzorec:

= COUNTIF ($ B $ 3: $ B $ 8, B3) <2

Vzorec používa príponu COUNTIF funkcie a použitia ABSOLÚTY v rozsahu B3: B8, aby sa zabezpečilo, že sa jedná o zoznam, ktorý COUNTIF funkcia skontroluje, či kontroluje, či existujú duplicitné hodnoty.

4. Kliknite OK.
5. Do bunky D4 napíšte „FRU-123“.
6. Ak ste použili možnosť Upozornenie na chybu, zobrazí sa vaše vlastné varovné a chybové hlásenie. Ak ste túto možnosť nevyužili, zobrazí sa štandardné upozornenie.

7. Kliknutím na tlačidlo Zrušiť opustíte správu alebo kliknutím na položku Znova zadať správny text do bunky.

Overenie údajov existuje v zozname v programe Excel

Môžeme napísať vlastný vzorec, aby sme zaistili, že do bunky bude zadaný iba konkrétny text.

1. Zvýraznite požadovaný rozsah, napr .: D3: D8.
2. Na páse s nástrojmi vyberte Dáta> Nástroje údajov> Overenie údajov.
3. V rozbaľovacom zozname Povoliť vyberte položku Vlastné a potom zadajte nasledujúci vzorec:

= COUNTIF ($ F $ 6: $ F $ 8, D3)> 0

Vzorec používa príponu COUNTIF funkcie a použitia ABSOLÚTY v rozsahu F3: F8, aby ste sa uistili, že sa jedná o zoznam, ktorý COUNTIF funkcia pri kontrole kontroluje, či sa zadáva správny text.

4. Kliknite OK.
5. Do bunky D4 napíšte „Single“.
6. Ak ste použili možnosť Upozornenie na chybu, zobrazí sa vaše vlastné varovné a chybové hlásenie. Ak ste túto možnosť nevyužili, zobrazí sa štandardné upozornenie.

7. Kliknutím na tlačidlo Zrušiť opustíte správu alebo kliknutím na položku Znova zadať správny text do bunky.

Overenie údajov v zozname v programe Excel neexistuje

Môžeme použiť vlastný vzorec, ktorý zaistí, že konkrétny text nebude vložený do bunky.

1. Zvýraznite požadovaný rozsah, napr .: C3: C8.
2. Na páse s nástrojmi vyberte Dáta> Nástroje údajov> Overenie údajov.
3. V rozbaľovacom zozname Povoliť vyberte zákazníka a potom zadajte nasledujúci vzorec:

= COUNTIF ($ F $ 6: $ F $ 8, C3) = 0

Vzorec používa príponu COUNTIF funkcie a použitia ABSOLÚTY v rozsahu F3: F8, aby ste sa uistili, že sa jedná o zoznam, ktorý COUNTIF funkcia pri kontrole kontroluje, či sa zadáva správny text.

4. Kliknite OK.
5. Do bunky C4 napíšte „Hovädzie mäso“.
6. Ak ste použili možnosť Upozornenie na chybu, zobrazí sa vaše vlastné varovné a chybové hlásenie. Ak ste túto možnosť nevyužili, zobrazí sa štandardné upozornenie.

7. Kliknutím na tlačidlo Zrušiť opustíte správu alebo kliknutím na položku Znova zadať správny text do bunky.

V programe Excel sa zadávajú iba čísla na overenie údajov

Môžeme použiť vlastný vzorec na zaistenie toho, aby bolo do bunky zadané iba číslo.

1. Zvýraznite požadovaný rozsah, napr .: E3: E8.
2. Na páse s nástrojmi vyberte Dáta> Nástroje údajov> Overenie údajov.
3. V rozbaľovacom zozname Povoliť vyberte zákazníka a potom zadajte nasledujúci vzorec:

= ISNUMBER (F3: F8)

Vzorec používa funkciu ISNUMBER na zaistenie zadania čísla do buniek v rozsahu.

4. Kliknite OK.
5. Do bunky F4 zadajte „deväť“.
6. Ak ste použili možnosť Upozornenie na chybu, zobrazí sa vaše vlastné varovné a chybové hlásenie. Ak ste túto možnosť nevyužili, zobrazí sa štandardné upozornenie.

7. Kliknutím na tlačidlo Zrušiť opustíte správu alebo kliknutím na položku Znova zadať správny text do bunky.

Validácia údajov neprekračuje hodnotu v programe Excel

Môžeme použiť vlastný vzorec, ktorý zaistí, že hodnoty zadané do bunky nepresiahnu zadanú hodnotu.

1. Zvýraznite požadovaný rozsah, napr .: E3: E8.
2. Na páse s nástrojmi vyberte Dáta> Nástroje údajov> Overenie údajov.
3. V rozbaľovacom zozname Povoliť vyberte zákazníka a potom zadajte nasledujúci vzorec:

= E3 <= $ G $ 6

Vzorec an ABSOLÚTNE v rozsahu G6, aby sa zaistilo, že ide o hodnotu, ktorú pravidlo kontroluje pri zadávaní údajov do E3.

4. Kliknite OK.
5. Do bunky E4 napíšte „9“.
6. Ak ste použili možnosť Upozornenie na chybu, zobrazí sa vaše vlastné varovné a chybové hlásenie. Ak ste túto možnosť nevyužili, zobrazí sa štandardné upozornenie.

7. Kliknutím na tlačidlo Zrušiť opustíte správu alebo kliknutím na položku Znova zadať správny text do bunky.

Validácia údajov neprekračuje v programe Excel celkom

Môžeme použiť vlastný vzorec, ktorý zaistí, že hodnoty zadané do rozsahu buniek nepresiahnu uvedenú celkovú hodnotu pre rozsah

1. Zvýraznite požadovaný rozsah, napr .: F3: F8.
2. Na páse s nástrojmi vyberte Dáta> Nástroje údajov> Overenie údajov.
3. V rozbaľovacom zozname Povoliť vyberte zákazníka a potom zadajte nasledujúci vzorec:

= SUM ($ F $ 3: $ F $ 8) <= $ H $ 6

Vzorec používa príponu SÚČET funkcie a použitia ABSOLÚTY v rozsahu F3: F8, aby ste sa uistili, že sa jedná o zoznam, ktorý SÚČET funkcia pri kontrole kontroluje, či súčet rozsahov nie je väčší ako hodnota zadaná v H6.

4. Kliknite OK.
5. Do bunky F4 napíšte „40“.
6. Ak ste použili možnosť Upozornenie na chybu, zobrazí sa vaše vlastné varovné a chybové hlásenie. Ak ste túto možnosť nevyužili, zobrazí sa štandardné upozornenie.

7. Kliknutím na tlačidlo Zrušiť opustíte správu alebo kliknutím na položku Znova zadať správny text do bunky.

Dátumy overenia údajov v dňoch v týždni iba v programe Excel.

Môžeme použiť vlastný vzorec, aby sme zaistili, že pri použití dátumov v programe Excel budú zadané iba pracovné dni.

1. Zvýraznite požadovaný rozsah, napr .: G3: G8.
2. Na páse s nástrojmi vyberte Dáta> Nástroje údajov> Overenie údajov.
3. V rozbaľovacom zozname Povoliť vyberte zákazníka a potom zadajte nasledujúci vzorec:

= TÝŽDEŇ (F3,2) <6

Funkcia všedný deň kontroluje, či deň zahrnutý v dátume nie je sobota alebo nedeľa.

4. Zmeňte dátum v G5 tak, aby ukazoval sobotu (napr. 9^th Máj 2022).
5. Ak ste použili možnosť Upozornenie na chybu, zobrazí sa vaše vlastné varovné a chybové hlásenie. Ak ste túto možnosť nevyužili, zobrazí sa štandardné upozornenie.

7. Kliknutím na položku Zrušiť alebo Skúsiť znova zadajte alternatívny dátum.

Budúci dátum validácie údajov iba v programe Excel

Môžeme vytvoriť vlastný vzorec, ktorý umožní používateľovi zadať iba dátum, ktorý je v budúcnosti.

1. Zvýraznite požadovaný rozsah, napr .: G3: G8.
2. Na páse s nástrojmi vyberte Dáta> Nástroje údajov> Overenie údajov.
3. V rozbaľovacom zozname Povoliť vyberte zákazníka a potom zadajte nasledujúci vzorec:

= G3> DNES ()

Vzorec používa príponu DNES funkcia na kontrolu, či je dátum zadaný do bunky väčší ako dnešný dátum.

4. Zmeňte dátum v G5 na včerajšok.
5. Ak ste použili možnosť Upozornenie na chybu, zobrazí sa vaše vlastné varovné a chybové hlásenie. Ak ste túto možnosť nevyužili, zobrazí sa štandardné upozornenie.

6. Kliknutím na položku Zrušiť alebo Skúsiť znova zadajte alternatívny dátum.

V Tabuľkách Google musí začať validácia údajov

1. Zvýraznite požadovaný rozsah, napr .: B3: B8.
2. V ponuke vyberte položku Údaje> Overenie údajov.

3. Rozsah buniek už bude vyplnený.

4. Vyberte Vlastný vzorec je z rozbaľovacieho zoznamu Kritériá.

5. Napíšte vzorec.

= PRESNE (VĽAVO (B3,4), „FRU-“)

6. Vyberte buď Zobraziť upozornenie alebo Odmietnuť vstup ak sú údaje neplatné.

7. V prípade potreby môžete napísať nejaký text pomocníka overenia.

8. Kliknite na položku Uložiť.

9. Kliknutím na B3 zobrazíte text pomocníka validácie

10. Zadajte FRI-123
11. Ak ste vybrali Zobraziť upozornenie, zobrazí sa nasledujúca správa.

12. Prípadne, ak ste vybrali Odmietnuť zadanie pri neplatných údajoch, zabránite zadaniu údajov a na obrazovke sa zobrazí nasledujúca správa.

Ostatné príklady vlastných vzorcov v Tabuľkách Google fungujú úplne rovnako.