Excel: Rozevírací seznam použít tabulku jako ověření dat

Ověření dat: Jak vytvořit buňku, aby vybírala hodnoty z rozevíracího seznamu

Vytváření tabulky v MS Excel může být velmi frustrující, pokud opakujete stejný vstup znovu a znovu.

Zároveň je velmi výhodné, když pouze vyberete vstupní obsah z určitého rozevíracího seznamu.

Podívejme se, jak použít „ověřovací pravidlo“, což je funkce, která to umožňuje.

Níže uvedená tabulka obsahuje seznam výdajů jednotlivce. Datum, typ výdajů a hodnota se zadávají pokaždé, když se utratí nějaké peníze.

Pomocí této tabulky nastavíme sloupec B tak, aby bylo možné vybrat rozevírací seznam typů výdajů, a to pouze odtud, abychom se vyhnuli jejich pokaždé nastavení a vyhnuli se jakékoli chybě při zadávání dat.

Řešení 1: Vytvořte seznam přímým zadáním

Začněme výběrem buňky, pro kterou chceme nastavit rozevírací seznam.

Na kartě „Data“ v aplikaci Excel vyberte „Ověření dat“, může se zobrazovat pouze jako malá ikona.

Na kartě nastavení zobrazte možnost rozevíracího seznamu povolit a vyberte možnost Seznam. To vám umožní vytvořit nebo vybrat seznam povolených hodnot pro vybrané datové buňky.

Do zdrojového pole jednoduše zadejte hodnoty, které chcete povolit, oddělené čárkou.

Jednoduše potvrďte kliknutím na OK a pro vybrané buňky bude vytvořen rozevírací seznam pro ověření.

Pokud vyberete jednu z těchto buněk, na konci buňky se zobrazí ikona a kliknutím na ni se zobrazí rozevírací seznam ověření, který vám umožní vybrat hodnotu, která se má automaticky zadat do buňky na základě vašeho seznam přímých vstupů.

Řešení 2: Vyberte rozsah existujících hodnot

Podobně místo ručního zadávání hodnot můžete určit rozsah povolených hodnot výběrem seznamu, který již v sešitu existuje.

Osvědčeným postupem při vytváření několika rozevíracích seznamů ověření v aplikaci Microsoft Excel je mít jeden nebo více konkrétních listů, které obsahují pouze různé seznamy ověření, aby bylo snazší je najít a aktualizovat.

Poté, co jste vytvořili seznam povolených hodnot, v našem příkladu na stejném listu, aby byl vizuálně přístupnější, vyberte buňky, u kterých by mělo být vynuceno ověřování dat.

Poté podobně otevřete nabídku ověření dat na kartě Data, v rozevírací nabídce vyberte typ seznamu a u zdroje namísto zadávání vlastních hodnot klikněte na tlačítko na pravé straně pole.

Budete přesměrováni na svůj list a vše, co musíte udělat, je kliknout na první buňku tabulky pro ověření dat, přesunout myš do poslední buňky obsahující jednu z platných hodnot, podržet kliknutí a uvolnit kliknutí teprve poté, co jste dosáhli poslední buňky hodnot ověření dat.

Na hodnoty, které jste vybrali tímto způsobem, se bude odkazovat ve vašem seznamu ověření dat a můžete jednoduše přidat hodnoty do svého ověření dat - nebo je odebrat - aktualizací tohoto seznamu.

Nastavte položky pro ověření dat na jiném listu

Staňte se Excel Pro: Připojte se k našemu kurzu!

Povýšejte své dovednosti z začínajícího na Hero pomocí našeho kurzu Excel 365 Basics, který je navržen tak, aby vás způsobilo, že vás způsobí, že je v několika sezeních.

Zaregistrujte se zde

V zájmu lepšího uspořádání sešitu může být snazší vytvořit seznamy ověření dat v konkrétním sešitu, abyste je mohli snadno najít a mít k nim přístup a nebudete se muset divit, kde jsou nastaveny.

Vložte tam svá data, vyberte tabulku a použijte tabulku možností: tabulka na kartě Vložit k transformaci seznamu hodnot na tabulku, která se automaticky upraví při přidávání nebo odebírání hodnot - a tak bude i vaše ověření dat.

Poté klikněte kamkoli do stylizované tabulky a na kartě Návrh tabulky zadejte název tabulky.

To vám umožní odkazovat na tuto tabulku kdekoli, včetně seznamu ověřování dat, jednoduchým zadáním názvu tabulky namísto rozsahu!

Jak povolit prázdné buňky v seznamu ověření dat

Pokud chcete povolit prázdné hodnoty v poli, které má povolenou validaci dat, musíte vybrat jednu z buněk, pro které je aktivována validace dat, a přejít na kartu data, abyste otevřeli nabídku ověření dat (viz výše, kde je tlačítko ).

Zde zkontrolujte, zda je zaškrtnuta možnost ignorovat prázdné pole, a před kliknutím na OK klikněte na „použít tyto změny na všechny ostatní buňky se stejným nastavením“.

Poté budete moci buňku vyprázdnit jejím výběrem a odebráním obsahu, aniž by se zobrazila chybová zpráva uvádějící, že obsah buněk není povolen.

Jak zakázat prázdné hodnoty v seznamu ověření dat

Pokud nechcete, aby byly v rozevíracím seznamu pro ověření dat povoleny prázdné hodnoty, a při každém výběru se zobrazí chybová zpráva „hodnota v této buňce je neplatná nebo chybí“, začněte výběrem rozsahu buněk, ve kterých jsou data použije se ověření nebo jakákoli buňka v tomto seznamu.

Poté otevřete nabídku ověření dat na kartě Data a zrušte zaškrtnutí políčka „Ignorovat prázdné“. Pokud jste vybrali pouze jednu buňku, před kliknutím na tlačítko OK zaškrtněte políčko „použít tyto změny na všechny ostatní buňky se stejným nastavením“.

Zpět na rozevíracím seznamu ověření dat se zobrazí chyba - a již nebude možné ponechat buňku prázdnou.

Jak odebrat rozevírací seznam pro ověření dat

Kdykoli skončíte s omezením rozsahu povolených hodnot v seznamu, vyberte libovolnou buňku v seznamu.

Poté otevřete nabídku ověření dat na kartě Data - viz výše v první části, jak to udělat - a ujistěte se, že je zaškrtnuta možnost „Použít tyto změny na všechny ostatní buňky se stejným nastavením“, jinak data pouze odstraníte ověření na aktuálně vybraných buňkách.

Poté klikněte na Vymazat vše a vámi vybrané ověření dat bude ze sešitu MS Excel odstraněno!

VBA: Přidat ověřovací seznam

Přidání ověřovacího seznamu pomocí VBA je stejně snadné jako použití níže uvedené funkce, kde A1 je buňka, ve které budou data ověřována proti seznamu ověřování údajů uloženému v buňkách B1 až B5.

Range ("a1"). Validation _ .Modify xlValidateList, xlValidAlertStop, "= $ B $ 1: $ B $ 5"

Pomocí pokročilých vlastností funkce seznamu ověřování dat VBA je možné vytvořit několik závislých rozevíracích seznamů v aplikaci Excel VBA, které každý ověřuje různá pole proti různým seznamům ověřování dat a má své vlastní názvy, názvy chyb, zprávy a chybové zprávy .