Nalezení nesrovnalostí v Excelu – 5 vysvětlených jednoduchých technik

Při práci s velkými datovými sadami v aplikaci Microsoft Excel je nevyhnutelné narazit na nesrovnalosti v datech. Identifikace a náprava těchto nesrovnalostí je zásadní pro zajištění přesného vykazování a analýzy.

Dobré zprávy? přichází s řadou nástrojů a technik, které vám pomohou tyto problémy najít a vyřešit. V tomto článku se dozvíte o některých nejúčinnějších metodách. To zahrnuje filtrování, podmíněné formátování a používání pokročilých vzorců.

Začleněním těchto technik do vašeho každodenního pracovního postupu budete efektivnější při správě a analýze dat. V konečném důsledku vede k lepšímu rozhodování a lepším výsledkům vašich projektů.

Obsah

• Pochopení nesrovnalostí v Excelu
• 5 způsobů, jak najít nesrovnalosti v Excelu
• #1 – Nalezení nesrovnalostí pomocí ruční kontroly v Excelu
• #2 – Použití filtru v Excelu k detekci a opravě nekonzistentních dat
• 3# – Zvýraznění nesrovnalostí s podmíněným formátováním
• #4 – Hledání nesrovnalostí pomocí pokročilých funkcí Excelu
  • Funkce IF a IS
  • VLOOKUP, HLOOKUP & XLOOKUP
  • ZÁPAS
• #5 – Použití doplňků aplikace Excel pro zjišťování nesrovnalostí
• Jak můžete vytvořit pravidla ověřování dat, abyste předešli nesrovnalostem?
• Naše závěrečné slovo

Pochopení nesrovnalostí v Excelu

Nesrovnalosti v řádcích a sloupcích mohou být důsledkem chyb lidského vstupu, duplicitních záznamů nebo nesrovnalostí ve formátování dat.

Podívejte se na následující údaje. Všimnete si, že Pohlaví Waltera Whitea a Wayna Cena je špatně napsáno – Mael místo Muž. Ve skutečnosti je snadné si všimnout a opravit chyby v tomto scénáři. To však není případ tabulek obsahujících tisíce řádků a sloupců.

K nalezení a správě těchto nesrovnalostí můžete použít následující funkce:

• Filtry: Použijte filtry na konkrétní sloupce, abyste zobrazili jedinečné hodnoty a rychle našli nekonzistentní data.
• Podmíněné formátování: Pomocí pravidel formátování zvýrazněte buňky, které splňují určitá kritéria, nebo neshodují data mezi sloupci.
• Pokročilé funkce Excelu: Využijte funkce jako MATCH a porovnejte datové sady a identifikujte mezi nimi nesrovnalosti.

Když pochopíte, jak používat tyto funkce v Excelu, můžete efektivně najít a opravit nesrovnalosti v sadách dat.

5 způsobů, jak najít nesrovnalosti v Excelu

Existuje 5 hlavních způsobů, jak rádi řešíme nesrovnalosti v Excelu:

1. Ruční kontrola
2. Filtrování
3. Podmíněné formátování
4. Pokročilé funkce Excelu
5. Doplňky aplikace Excel

Pojďme si projít každou z nich, abyste se mohli dopracovat k tomu, jak vám nejlépe vyhovuje.

#1 – Nalezení nesrovnalostí pomocí ruční kontroly v Excelu

Při práci s , ruční kontrola může být užitečnou metodou pro identifikaci nesrovnalostí ve vašich datech. Tento proces vyžaduje pečlivé prozkoumání souborů dat, abychom odhalili případné nesrovnalosti nebo chyby.

Zde je několik kroků, kterými můžete provést ruční kontrolu:

• Začněte tím, že jasně pochopíte datové sady, se kterými pracujete. Seznamte se s řádky, sloupci a formátováním.
• Procházejte data a věnujte zvýšenou pozornost informacím uvedeným v každé buňce. Pro snazší čtení dat můžete použít nástroje jako zvýraznění buněk barvou výplně, formátování textu nebo podmíněné formátování.
• Porovnejte jeden řádek s druhým nebo jeden sloupec s druhým. Hledejte neodpovídající informace, duplicitní záznamy atd., které mohou způsobit nepřesnosti ve vašich excelových listech.
• Jakmile zjistíte nesrovnalosti, vytvořte seznam nebo je zdokumentujte pomocí komentářů. Později mohou sloužit jako důležité reference pro nápravu problémů.

Při ruční kontrole dat na nesrovnalosti v excelové tabulce je klíčem trpělivost. Tento proces je časově náročný. Ale je to dobrý výchozí bod pro identifikaci potenciálních chyb.

#2 – Použití filtru v Excelu k detekci a opravě nekonzistentních dat

Jedním z nejjednodušších způsobů, jak zjistit nekonzistentní data, je použití filtru aplikace Excel .

Pomocí filtru izolujeme nesprávná data ve sloupci Pohlaví v následující sadě dat:

1. Vyberte položku Seřadit a filtrovat na kartě Domů .

2. Zvolte Filtr – ujistěte se, že je kurzor v rozsahu dat.

3. Klepněte na šipku rozevíracího seznamu ve sloupci s nekonzistentními daty.

4. Nyní zrušte výběr všech správných odpovídajících dat. V našem případě je to Muž . Poté kliknutím na OK uložte změny.

5. Váš excelový list nyní zobrazí pouze řádky s nesprávnými hodnotami. Chcete-li je opravit, klikněte na tlačítko Najít a vybrat na kartě Domů .

6. Po zobrazení rozevíracího seznamu klepněte na tlačítko Nahradit .

7. Zadejte nesprávnou hodnotu do pole Najít a správnou hodnotu do pole Nahradit . Poté klikněte na Nahradit vše .

8. Zobrazí se dialogové okno potvrzující změny. Pokračujte kliknutím na OK .

3# – Zvýraznění nesrovnalostí s podmíněným formátováním

Dalším způsobem, jak identifikovat rozdíly mezi sloupci a řádky, je použití podmíněného formátování.

Máte přístup k pravidlům, jako jsou zvýrazněné buňky, Nahoře/dole, datové pruhy, škály barev a sady železa. Navíc můžete vytvořit nové pravidlo a spravovat nebo vymazat předchozí pravidla.

Použijeme vzorovou tabulku, která vám ukáže sílu podmíněného formátování:

1. Vyberte tabulku nebo preferované řádky a sloupce. Vybíráme sloupec A (společnost) a sloupec B (země). Poté vyberte Podmíněné formátování na kartě Domů .

2. Vyberte typ pravidla a poté pravidlo. Vybíráme pravidla pro zvýraznění buněk a duplicitní hodnoty…

3. Nyní musíme vybrat, zda zvýraznit duplicitní nebo jedinečné hodnoty a barvu výplně a písmo pro formátování buněk. Po dokončení klikněte na OK .

4. Všechny duplicitní hodnoty jsou nyní zvýrazněny světle červenou výplní s tmavě červeným textem.

#4 – Hledání nesrovnalostí pomocí pokročilých funkcí Excelu

Funkce IF a IS

K porovnání buněk a identifikaci rozdílů můžete využít funkce IF a IS.

Vzorec jsme například použili =IF(A2=B2,"MATCH","NOT MATCHED")k porovnání buněk A2 a B2. A vzorec jsme přetáhli, abychom jej použili na další buňky. Takže identické buňky vracejí MATCH , jinak NEPŘIHODNÉ .

Můžete kombinovat IF s funkcemi IS, jako je ISNUMBER nebo ISTEXT, abyste zkontrolovali konkrétní datové typy. Vzorec například =IF(ISNUMBER(B2), "Number", "NaN")vrátí Číslo ve sloupci c, pokud je B2 číselný a NaN jinak.

VLOOKUP, HLOOKUP & XLOOKUP

Funkce SVYHLEDAT (vertikálně) a HLOOKUP (horizontálně) jsou užitečné při porovnávání hodnot v různých sloupcích nebo řádcích dvou tabulek. Chcete-li to provést, použijte následující vzorec: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) nebo =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

Nahraďte lookup_value hodnotou, kterou chcete najít v druhé tabulce, table_array rozsahem buněk ve druhé tabulce, col_index_num nebo row_index_num indexovým číslem sloupce nebo řádku, ze kterého chcete vrátit hodnotu, a [ range_lookup] FALSE pro přesné shody nebo TRUE pro přibližnou shodu.

V následujícím tutoriálu používáme funkci SVYHLEDAT ve sloupci E k porovnání jmen zaměstnanců ve sloupci A a D, abychom odebrali jejich platy ze sloupce B, pokud je nalezena shoda.

XLOOKUP je aktualizovaná verze SVYHLEDAT a HLOOKUP dostupná v Excel 2021 a Excel 365. Vzorec XLOOKUP je: =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]).

Nahraďte lookup_value hodnotou, kterou chcete najít v druhé tabulce, lookup_array rozsahem buněk k prohledání ve druhé tabulce, return_array polem, ze kterého chcete vrátit hodnotu, a [if_nout_found] textovou hodnotou, pokud žádná odpovídající hodnoty jsou nalezeny.

Zde je rychlá ukázka XLOOKUP se stejnými tabulkami:

ZÁPAS

Funkci MATCH lze také použít k porovnání dvou seznamů kvůli nesrovnalostem.

Funkce MATCH, =MATCH(lookup_value, lookup_array, [match_type])hledá zadanou položku v rozsahu a vrací relativní pozici položky v tomto rozsahu.

Zde zjišťujeme pozici vodních melounů v poli A2:A8 pomocí MATCH. Používáme match_type jako 0 k nalezení první hodnoty, která se přesně rovná lookup_value .

#5 – Použití doplňků aplikace Excel pro zjišťování nesrovnalostí

Excel nabízí různé doplňky a vestavěné nástroje, které vám pomohou zjišťovat a analyzovat nesrovnalosti efektivněji. V této části probereme některé z těchto užitečných doplňků.

Nejprve zvažte použití doplňku Spreadsheet Inquire . To vám může pomoci porovnat dva sešity a zvýraznit rozdíly buňku po buňce. Chcete-li povolit doplněk Spreadsheet Inquire:

1. Klepněte na kartu Soubor .

2. Vyberte Možnosti a poté klikněte na kategorii Doplňky .

3. V poli Spravovat vyberte Doplňky modelu COM a klikněte na Přejít .

4. Zaškrtněte políčko Spreadsheet Inquire a klepněte na OK .

5. Po povolení přejděte na kartu Dotaz a použijte příkaz Porovnat soubory .

Dalším užitečným nástrojem pro detekci nesrovnalostí je Analysis ToolPak . Tento doplněk poskytuje pokročilé statistické funkce a nástroje pro analýzu dat, které mohou být užitečné při analýze nesrovnalostí.

Chcete-li povolit nástroj Analysis ToolPak, postupujte podle stejných kroků jako při povolení doplňku Spreadsheet Inquire. Vyberte však doplňky Excelu v poli Spravovat a zaškrtněte políčko Analytické nástroje .

Stručně řečeno, Excel nabízí různé doplňky, které vám pomohou odhalit a zvládnout nesrovnalosti ve vašich datech. Přesnost dat můžete zlepšit, když se s těmito nástroji a vzorci seznámíte.

Jak můžete vytvořit pravidla ověřování dat, abyste předešli nesrovnalostem?

Chcete-li vytvořit pravidla ověřování dat v aplikaci Excel, která mohou pomoci předejít nesrovnalostem, postupujte takto:

1. Vyberte buňky, které chcete omezit. Může to být jedna buňka, rozsah buněk nebo celý sloupec.

2. Přejděte na kartu Data na panelu nástrojů a klikněte na tlačítko Ověření dat (označené dvěma vodorovnými rámečky, zeleným zaškrtnutím a červeným přeškrtnutým kroužkem).

3. V okně Ověření dat se ujistěte, že jste na kartě Nastavení . Zde budete mít příležitost definovat svá ověřovací kritéria na základě vašich potřeb.

V Excelu jsou k dispozici různé typy ověřovacích kritérií, z nichž některá zahrnují:

• Celé číslo – umožňuje pouze celá čísla v zadaném rozsahu.
• Decimal – povoluje pouze desetinná čísla v určeném rozsahu.
• Seznam – omezuje vstup na předdefinovaný seznam přijatelných hodnot.
• Datum – vyžaduje data v určitém časovém období.
• Čas – přijímá pouze časy v určitém časovém rozsahu.
• Délka textu – vynucuje omezení délky zadávaného textu.
• Vlastní – umožňuje vytvářet vlastní ověřovací pravidla pomocí vzorců aplikace Excel.

Po výběru vhodných ověřovacích kritérií zadejte podle potřeby parametry. Pokud jste například vybrali celé číslo, museli byste nastavit Minimum a Maximum pomocí validátoru rozsahu dat , jako je mezi, rovno, menší než atd.

Kromě ověřovacích kritérií můžete také nastavit vlastní chybové zprávy a vstupní rady, které uživatelům pomohou pochopit a dodržovat vaše ověřovací pravidla. Chcete-li to provést, přepněte na karty Vstupní zpráva a Upozornění na chybu a zadejte požadované zprávy.

Zavedením pravidel ověřování dat výrazně snížíte pravděpodobnost nekonzistence dat a zlepšíte celkovou přesnost svých excelových listů.

Naše závěrečné slovo

V tomto článku jste se naučili různé metody, jak najít a vyřešit nesrovnalosti v datech aplikace Excel. Pojďme si tyto klíčové strategie zrekapitulovat:

• Použití rozevíracích seznamů Filtr ve sloupcích k identifikaci jedinečných hodnot a nekonzistentních dat.
• Použití podmíněného formátování ve vybraných buňkách ke zvýraznění nesrovnalostí pomocí řady šablon pravidel.
• Použití pokročilých funkcí aplikace Excel, jako jsou funkce MATCH , VLOOKUP , XLOOKUP atd., pro křížovou kontrolu dat ve dvou seznamech nebo tabulkách.
• Použití doplňků aplikace Excel ke zjišťování nesrovnalostí.
• Nastavení pravidel ověřování dat , aby se zabránilo chybám v datech

S těmito technikami ve vaší sadě dovedností Excelu budou vaše data přesnější a důvěryhodnější.

Nezapomeňte tyto metody pravidelně procvičovat, abyste byli efektivnější při odhalování nesrovnalostí ve vašich tabulkách.

Pokračujte v zdokonalování svých dovedností tím, že se zaregistrujete do našeho , a pokračujte ve zkoumání nových funkcí a funkcí, abyste byli v Excelu ještě zběhlejší.