Vlastní vzorce pro ověření dat

Tento tutoriál vám ukáže, jak vytvářet vlastní vzorce v Ověření dat v Excelu a Tabulkách Google

Ověření dat - musí začít - Excel

Můžeme napsat vlastní vzorec, abychom zajistili, že data v buňce začínají určitým textem.

1. Zvýrazněte požadovaný rozsah, např.: B3: B8.
2. Na pásu karet vyberte Data> Datové nástroje> Ověření dat.

3. Vybrat Zvyk z rozevíracího seznamu Povolit a poté zadejte následující vzorec:

= PŘESNĚ (VLEVO (B3,4), „FRU-“)

Vzorec používá 2 funkce PŘESNÝ a VLEVO, ODJET určit, zda jsou první 4 znaky zadané do buňky shodné s „FRU-“

4. Pokud chcete pro uživatele přidat vstupní zprávu, klikněte na Vstupní zpráva.

5. Zaškrtněte políčko „Zobrazit vstupní zprávu, když je vybrána buňka“Je zaškrtnuto a poté zadejte požadovaný název a zprávu.
6. Pokud chcete přidat upozornění na chybu, klikněte na Upozornění na chybu.

7. Styl můžete změnit z Stop buď Varování nebo Informace pokud požadujete, a poté zadejte název a chybovou zprávu.
8. Klikněte OK.
9. Pokud jste použili Vstup Možnost zprávy, na obrazovce se objeví komentář informující uživatele o pravidle.

10. Do buňky B3 napište „FRI-124“. Pokud jste vybrali Stop, zobrazí se následující okno se zprávou.

11. Pokud jste vybrali Varování, pak se zobrazí toto okno se zprávou. To vám umožní pokračovat, pokud zjistíte, že jsou data správná.

12. Pokud jste vybrali Informace, pak se zobrazí toto okno se zprávou.

13. Pokud kliknete OK, budete moci pokračovat s nesprávnými údaji zadanými do buňky.
14. Klikněte zrušení pro ukončení zprávy nebo OK pro vložení textu do buňky.

Ověření dat Povolit velká písmena pouze v aplikaci Excel

Můžeme napsat vlastní vzorec, abychom zajistili, že data v buňce povolí velká písmena pouze v případě, že je do buňky zadán text.

1. Zvýrazněte požadovaný rozsah, např.: B3: B8.
2. Na pásu karet vyberte Data> Datové nástroje> Ověření dat.
3. Vybrat Zvyk z rozevíracího seznamu Povolit a poté zadejte následující vzorec:

= PŘESNĚ (B3, HORNÍ (B3))

Vzorec používá 2 funkce PŘESNÝ a HORNÍ určit, zda je text zadaný do buňky psán velkými písmeny. Buňky se směsí čísla a textu jsou považovány za text a čísla jsou v pravidle ignorována.

4. Klikněte OK.
5. Do buňky B3 zadejte „fru-124“.
6. Pokud jste použili možnost Upozornění na chyby, zobrazí se vaše vlastní varovná a chybová zpráva. Pokud jste tuto možnost nevyužili, zobrazí se standardní varování.

7. Kliknutím na Zrušit zprávu opustíte, nebo Opakovat, chcete-li do buňky znovu zadat správný text.
8. Do buňky B3 zadejte „123456“.
9. To bude povoleno, protože se jedná o číslo a ne o text.

V našem dalším příkladu zajistíme, aby do buňky bylo možné zadat pouze velký text.

Ověření dat Povolit psaní velkých písmen pouze v aplikaci Excel

Můžeme napsat vlastní vzorec v Ověření dat lze použít k zajištění, že data v buňce umožňují pouze psaní velkých písmen

POZNÁMKA: Pokud zadáte informace do buňky, která začíná textem, ale obsahuje čísla, aplikace Excel text zohlední.

1. Zvýrazněte požadovaný rozsah, např.: B3: B8.
2. Na pásu karet vyberte Data> Datové nástroje> Ověření dat.
3. Vybrat Zvyk z rozevíracího seznamu Povolit a poté zadejte následující vzorec:

= A (PŘESNĚ (B3, HORNÍ (B3)), ISTEXT (B3))

Vzorec používá 4 funkce A, PŘESNĚ, HORNÍ a TEXT určit, zda je text zadaný do buněk psán velkými písmeny A určit, zda zadané informace jsou ve skutečnosti textové, a nikoli čisté číslo.

4. Klikněte OK.
5. Do buňky B3 zadejte „fru-124“.
6. Pokud jste použili možnost Upozornění na chybu, zobrazí se vaše vlastní varovná a chybová zpráva. Pokud jste tuto možnost nevyužili, zobrazí se standardní varování.

7. Kliknutím na Zrušit zprávu opustíte, nebo Opakovat, chcete-li do buňky znovu zadat správný text.
8. Do buňky B3 zadejte „123456“.
9. Opět se zobrazí chybová zpráva.
10. Kliknutím na Zrušit zprávu opustíte, nebo Opakovat, chcete-li do buňky znovu zadat správný text.

Ověření dat brání mezerám v aplikaci Excel

Můžeme napsat vlastní vzorec, abychom zajistili, že do dat zadaných v rozsahu buněk nebudou zadány mezery.

1. Zvýrazněte požadovaný rozsah, např.: B3: B8.
2. Na pásu karet vyberte Data> Datové nástroje> Ověření dat.
3. Vybrat Zvyk z rozevíracího seznamu Povolit a poté zadejte následující vzorec:

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

Vzorec používá funkci SUBSTITUTE ke kontrole, že mezery neexistují.

4. Klikněte OK.
5. Do buňky B4 zadejte „FRU - 124“.
6. Pokud jste použili možnost Upozornění na chyby, zobrazí se vaše vlastní varovná a chybová zpráva. Pokud jste tuto možnost nevyužili, zobrazí se standardní varování.

7. Kliknutím na Zrušit zprávu opustíte, nebo Opakovat, chcete-li do buňky znovu zadat správný text.

Ověření dat zabraňuje duplikátům v aplikaci Excel

Můžeme napsat vlastní vzorec, který nám zabrání v zadávání duplicitních informací do řady buněk.

1. Zvýrazněte požadovaný rozsah, např.: B3: B8.
2. Na pásu karet vyberte Data> Datové nástroje> Ověření dat.
3. V rozevíracím seznamu Povolit vyberte zákazníka a poté zadejte následující vzorec:

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

Vzorec používá COUNTIF funkce a použití ABSOLUTY v rozsahu B3: B8, aby se zajistilo, že se jedná o seznam, který COUNTIF funkce zjišťuje, zda kontroluje, zda existují nějaké duplicitní hodnoty.

4. Klikněte OK.
5. Do buňky D4 zadejte „FRU-123“.
6. Pokud jste použili možnost Upozornění na chybu, zobrazí se vaše vlastní varovná a chybová zpráva. Pokud jste tuto možnost nevyužili, zobrazí se standardní varování.

7. Kliknutím na Zrušit zprávu opustíte, nebo Opakovat, chcete-li do buňky znovu zadat správný text.

Ověření dat existuje v seznamu v aplikaci Excel

Můžeme napsat vlastní vzorec a zajistit, aby byl do buňky zadán pouze konkrétní text.

1. Zvýrazněte požadovaný rozsah, např .: D3: D8.
2. Na pásu karet vyberte Data> Datové nástroje> Ověření dat.
3. V rozevíracím seznamu Povolit vyberte Vlastní a zadejte následující vzorec:

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

Vzorec používá COUNTIF funkce a použití ABSOLUTY v rozsahu F3: F8, aby se zajistilo, že se jedná o seznam, který COUNTIF funkce se dívá, když kontroluje, zda je zadáván správný text.

4. Klikněte OK.
5. Do buňky D4 zadejte „Single“.
6. Pokud jste použili možnost Upozornění na chyby, zobrazí se vaše vlastní varovná a chybová zpráva. Pokud jste tuto možnost nevyužili, zobrazí se standardní varování.

7. Kliknutím na Zrušit zprávu opustíte, nebo Opakovat, chcete-li do buňky znovu zadat správný text.

Ověření dat v seznamu v aplikaci Excel neexistuje

Můžeme použít vlastní vzorec, který zajistí, že konkrétní text nebude zadán do buňky.

1. Zvýrazněte požadovaný rozsah, např.: C3: C8.
2. Na pásu karet vyberte Data> Datové nástroje> Ověření dat.
3. V rozevíracím seznamu Povolit vyberte zákazníka a poté zadejte následující vzorec:

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

Vzorec používá COUNTIF funkce a použití ABSOLUTY v rozsahu F3: F8, aby se zajistilo, že se jedná o seznam, který COUNTIF funkce se dívá, když kontroluje, zda je zadáván správný text.

4. Klikněte OK.
5. Do buňky C4 napište „Hovězí maso“.
6. Pokud jste použili možnost Upozornění na chybu, zobrazí se vaše vlastní varovná a chybová zpráva. Pokud jste tuto možnost nevyužili, zobrazí se standardní varování.

7. Kliknutím na Zrušit zprávu opustíte, nebo Opakovat, chcete-li do buňky znovu zadat správný text.

V Excelu se zadávají pouze čísla pro ověření dat

Můžeme použít vlastní vzorec, abychom zajistili, že do buňky bude zadáno pouze číslo.

1. Zvýrazněte požadovaný rozsah, např .: E3: E8.
2. Na pásu karet vyberte Data> Datové nástroje> Ověření dat.
3. V rozevíracím seznamu Povolit vyberte zákazníka a poté zadejte následující vzorec:

= ISNUMBER (F3: F8)

Vzorec používá funkci ISNUMBER k zajištění zadání čísla do buněk v rozsahu.

4. Klikněte OK.
5. Do buňky F4 napište „devět“.
6. Pokud jste použili možnost Upozornění na chybu, zobrazí se vaše vlastní varovná a chybová zpráva. Pokud jste tuto možnost nevyužili, zobrazí se standardní varování.

7. Kliknutím na Zrušit zprávu opustíte, nebo Opakovat, chcete-li do buňky znovu zadat správný text.

Ověření dat nepřekračuje hodnotu v aplikaci Excel

Můžeme použít vlastní vzorec, abychom zajistili, že hodnoty zadané do buňky nepřekročí zadanou hodnotu.

1. Zvýrazněte požadovaný rozsah, např.: E3: E8.
2. Na pásu karet vyberte Data> Datové nástroje> Ověření dat.
3. V rozevíracím seznamu Povolit vyberte zákazníka a poté zadejte následující vzorec:

= E3 <= $ G $ 6

Vzorec an ABSOLUTNÍ v rozsahu G6, aby bylo zajištěno, že toto je hodnota, kterou pravidlo kontroluje při zadávání dat do E3.

4. Klikněte OK.
5. Do buňky E4 zadejte „9“.
6. Pokud jste použili možnost Upozornění na chybu, zobrazí se vaše vlastní varovná a chybová zpráva. Pokud jste tuto možnost nevyužili, zobrazí se standardní varování.

7. Kliknutím na Zrušit zprávu opustíte, nebo Opakovat, chcete-li do buňky znovu zadat správný text.

Ověření dat nepřekračuje celkem v aplikaci Excel

Můžeme použít vlastní vzorec, který zajistí, že hodnoty zadané do rozsahu buněk nepřekročí zadanou celkovou hodnotu pro rozsah

1. Zvýrazněte požadovaný rozsah, např.: F3: F8.
2. Na pásu karet vyberte Data> Datové nástroje> Ověření dat.
3. V rozevíracím seznamu Povolit vyberte zákazníka a poté zadejte následující vzorec:

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

Vzorec používá SOUČET funkce a použití ABSOLUTY v rozsahu F3: F8, aby bylo zajištěno, že se jedná o seznam, který SOUČET funkce při kontrole zjišťuje, že součet rozsahu není větší než hodnota zadaná v H6.

4. Klikněte OK.
5. Do buňky F4 zadejte „40“.
6. Pokud jste použili možnost Upozornění na chyby, zobrazí se vaše vlastní varovná a chybová zpráva. Pokud jste tuto možnost nevyužili, zobrazí se standardní varování.

7. Kliknutím na Zrušit zprávu opustíte, nebo Opakovat, chcete-li do buňky znovu zadat správný text.

Ověření dat ve všední den pouze v aplikaci Excel.

Můžeme použít vlastní vzorec, abychom zajistili, že se při použití dat v Excelu zadávají pouze všední dny.

1. Zvýrazněte požadovaný rozsah, např.: G3: G8.
2. Na pásu karet vyberte Data> Datové nástroje> Ověření dat.
3. V rozevíracím seznamu Povolit vyberte zákazníka a poté zadejte následující vzorec:

= TÝDEN (F3,2) <6

Funkce všední den kontroluje, zda den obsažený v datu není sobota nebo neděle.

4. Upravte datum v G5 tak, aby zobrazovalo sobotu (např. 9^th Května 2022).
5. Pokud jste použili možnost Upozornění na chybu, zobrazí se vaše vlastní varovná a chybová zpráva. Pokud jste tuto možnost nevyužili, zobrazí se standardní varování.

7. Kliknutím na Zrušit nebo Opakovat zadejte alternativní datum.

Budoucí datum ověření dat pouze v aplikaci Excel

Můžeme vytvořit vlastní vzorec, který uživateli umožní pouze zadat datum, které je v budoucnosti.

1. Zvýrazněte požadovaný rozsah, např.: G3: G8.
2. Na pásu karet vyberte Data> Datové nástroje> Ověření dat.
3. V rozevíracím seznamu Povolit vyberte zákazníka a poté zadejte následující vzorec:

= G3> DNES ()

Vzorec používá DNES funkce pro kontrolu, zda je datum zadané do buňky větší než dnešní datum.

4. Upravit datum v G5 na včerejšek.
5. Pokud jste použili možnost Upozornění na chybu, zobrazí se vaše vlastní varovná a chybová zpráva. Pokud jste tuto možnost nevyužili, zobrazí se standardní varování.

6. Kliknutím na Zrušit nebo Opakovat zadejte alternativní datum.

S ověřováním dat musí začít v Tabulkách Google

1. Zvýrazněte požadovaný rozsah, např.: B3: B8.
2. V nabídce vyberte Data> Ověření dat.

3. Rozsah buněk již bude vyplněn.

4. Vybrat Vlastní vzorec je z rozevíracího seznamu Kritéria.

5. Zadejte vzorec.

= PŘESNĚ (VLEVO (B3,4), „FRU-“)

6. Vyberte buď Zobrazit varování nebo Odmítnout vstup pokud jsou údaje neplatné.

7. Pokud potřebujete, můžete napsat nějaký text nápovědy pro ověření.

8. Klikněte na Uložit.

9. Kliknutím na B3 zobrazíte text nápovědy k ověření

10. Zadejte FRI-123
11. Pokud jste vybrali Zobrazit varování, zobrazí se následující zpráva.

12. Případně, pokud jste vybrali Odmítnout zadání u neplatných dat, bude vám znemožněno zadání dat a na obrazovce se objeví následující zpráva.

Zbytek příkladů vlastního vzorce v Tabulkách Google funguje přesně stejným způsobem.