Tento wikiHow vás naučí, jak pomocí podmíněného formátování porovnat všechny buňky ve dvou samostatných seznamech v tabulce aplikace Excel a označit buňky, které se zobrazují v obou seznamech. Tato funkce je k dispozici pouze v desktopové verzi Excelu a mobilní aplikace ji nepodporuje.
Kroky
Metoda 1 ze 2: Použití vzorce CountIf
Krok 1. Otevřete tabulku aplikace Excel, kterou chcete upravit
Najděte soubor tabulky se seznamy, které chcete porovnat, a poklepáním na soubor jej otevřete v aplikaci Microsoft Excel.
Krok 2. Vyberte svůj první seznam
Klikněte na první buňku v prvním seznamu a přetažením myši až na poslední buňku seznamu vyberte celý rozsah dat.
Krok 3. Klikněte na kartu Vzorce na pásu nástrojů
Tuto kartu najdete nad panelem nástrojů v horní části tabulky. Otevře vaše nástroje pro vzorce na pásu nástrojů.
Krok 4. Na panelu nástrojů klikněte na Definovat jméno
Tuto možnost najdete uprostřed pásu karet „Vzorce“. Otevře se nové vyskakovací okno a umožní vám pojmenovat seznam.
Krok 5. Do pole Název zadejte List1
Klikněte na textové pole v horní části vyskakovacího okna a zde zadejte název seznamu.
- Tento název můžete později použít k vložení seznamu do srovnávacího vzorce.
- Případně zde můžete svůj seznam pojmenovat jinak. Pokud se jedná například o seznam míst, můžete jej pojmenovat „locations1“nebo „locationList“.
Krok 6. Ve vyskakovacím okně klikněte na OK
Tím potvrdíte svou akci a pojmenujete svůj seznam.
Krok 7. Pojmenujte svůj druhý seznam jako List2
Postupujte podle stejných kroků jako v prvním seznamu a pojmenujte svůj druhý seznam. To vám umožní rychle použít tento druhý seznam ve vašem srovnávacím vzorci později.
Seznamu můžete dát libovolné jméno. Zde si nezapomeňte zapamatovat nebo poznamenat název, který uvedete pro každý ze svých seznamů
Krok 8. Vyberte svůj první seznam
Klikněte na první buňku v prvním seznamu a přetažením dolů vyberte celý rozsah dat.
Před nastavením podmíněného formátování se ujistěte, že je vybrán váš první seznam
Krok 9. Klikněte na kartu Domů na panelu nástrojů
Toto je první karta v levém horním rohu pásu nástrojů. Otevře vaše základní tabulkové nástroje na panelu nástrojů.
Krok 10. Na panelu nástrojů klikněte na Podmíněné formátování
Tato možnost vypadá jako malá ikona tabulky s některými buňkami zvýrazněnými červeně a modře. Otevře se rozevírací nabídka všech vašich možností formátování.
Krok 11. V rozevírací nabídce klikněte na Nové pravidlo
Otevře se nové vyskakovací okno a umožní vám ručně nastavit nové pravidlo formátování pro vybraný rozsah.
Krok 12. Vyberte možnost „Pomocí vzorce určit, které buňky chcete formátovat“
Tato možnost vám umožní ručně zadat formátovací vzorec pro porovnání vašich dvou seznamů.
- Na Okna, najdete jej v dolní části seznamu pravidel v poli „Vyberte typ pravidla“.
- Na Mac, vyberte Klasický v rozevíracím seznamu „Styl“v horní části vyskakovacího okna. Tuto možnost pak najděte ve druhém rozevíracím seznamu pod nabídkou Styl.
Krok 13. Klikněte na pole vzorce ve vyskakovacím okně
Zde můžete zadat libovolný platný vzorec aplikace Excel a nastavit pravidlo podmíněného formátování.
Krok 14. Do řádku vzorců zadejte = countif (List2, A1) = 1
Tento vzorec naskenuje vaše dva seznamy a označí všechny buňky v prvním seznamu, které se objeví také ve druhém seznamu.
- Nahraďte A1 ve vzorci číslem první buňky vašeho prvního seznamu.
- Pokud je například první buňkou vašeho prvního seznamu buňka D5, pak bude váš vzorec vypadat = countif (List2, D5) = 1.
- Pokud jste svému druhému seznamu dali jiný název, nezapomeňte ve vzorci nahradit List2 skutečným názvem vašeho vlastního seznamu.
- Alternativně můžete změnit vzorec na = countif (List2, A1) = 0, pokud chcete označit buňky, které ne se objeví na druhém seznamu.
Krok 15. Do řádku vzorců (nepovinné) zadejte = countif (List1, B1) = 1
Pokud chcete najít a označit buňky na svém druhý seznam, který se také objevuje v prvním seznamu, použijte tento vzorec místo prvního.
Nahraďte List1 názvem vašeho prvního seznamu a B1 první buňkou vašeho druhého seznamu
Krok 16. Vyberte vlastní formát pro označení buněk (volitelně)
Buňky, které váš vzorec najde, můžete označit vlastní barvou výplně pozadí a různými styly písem.
- Na Okna, klikněte na Formát tlačítko v pravém dolním rohu vyskakovacího okna. Barvu pozadí můžete vybrat na kartě „Výplň“a styly písem na kartě „Písmo“.
- Na Mac, v dolní části rozevíracího seznamu „Formát s“vyberte přednastavený formát. Můžete také vybrat vlastní formát zde ručně vyberte výplň pozadí a styly písem.
Krok 17. Ve vyskakovacím okně klikněte na OK
Tím se potvrdí a použije váš srovnávací vzorec. Všechny buňky ve vašem prvním seznamu, které se objeví také ve druhém seznamu, budou označeny zvolenou barvou a písmem.
- Pokud například vyberete světle červenou výplň s tmavě červeným textem, všechny opakující se buňky se ve vašem prvním seznamu změní na tuto barvu.
- Pokud použijete druhý vzorec výše, podmíněné formátování označí opakující se buňky ve vašem druhém seznamu místo v prvním.
Metoda 2 ze 2: Použití vzorce VLookup
Krok 1. Otevřete tabulku aplikace Excel
Najděte soubor aplikace Excel se seznamy, které chcete porovnat, a poklepáním na název souboru nebo ikonu otevřete tabulku v aplikaci Microsoft Excel.
Krok 2. Klikněte na prázdnou buňku vedle první položky ve vašem druhém seznamu
Najděte v tabulce svůj druhý seznam a klikněte na prázdnou buňku vedle první položky seznamu v horní části.
- Zde můžete vložit vzorec VLookup.
- Alternativně můžete vybrat libovolnou prázdnou buňku v tabulce. Tato buňka vám pouze usnadní sledování srovnání vedle vašeho druhého seznamu.
Krok 3. Zadejte = vlookup (do prázdné buňky
Vzorec VLookup vám umožní porovnat všechny položky na dvou samostatných seznamech a zjistit, zda je hodnota opakováním nebo novou hodnotou.
Nezavírejte závorky vzorců, dokud není vzorec dokončen
Krok 4. Vyberte první položku ve svém druhém seznamu
Bez zavření závorky vzorce klikněte na první položku ve svém druhém seznamu. Tím se do vzorce vloží první buňka vašeho druhého seznamu.
Krok 5. Do vzorce napište a, čárku
Po výběru první buňky druhého seznamu zadejte do vzorce čárku. Dále budete moci vybrat rozsah porovnání.
Krok 6. Podržte a vyberte celý svůj první seznam
Tím se oblast buněk vašeho prvního seznamu vloží do druhé části vzorce VLookup.
To vám umožní vyhledat v prvním seznamu vybranou položku z vašeho druhého seznamu (první položka v horní části druhého seznamu) a vrátit se, pokud se jedná o opakovanou nebo novou hodnotu
Krok 7. Do vzorce napište a, čárku
Tím se ve vašem vzorci uzamkne rozsah porovnání.
Krok 8. Do vzorce za čárkou napište 1
Toto číslo představuje indexové číslo vašeho sloupce. Vyzve vzorec VLookup k prohledání skutečného sloupce seznamu namísto jiného sloupce vedle něj.
Pokud chcete, aby váš vzorec vrátil hodnotu ze sloupce hned vedle vašeho prvního seznamu, zadejte sem 2
Krok 9. Do vzorce napište a, čárku
Tím se uzamkne indexové číslo vašeho sloupce (1) ve vzorci VLookup.
Krok 10. Do vzorce zadejte FALSE
Tím se v seznamu místo přibližných shod vyhledá přesná shoda vybrané položky vyhledávání (první položka v horní části druhého seznamu).
- Místo FALSE můžete použít 0, je to úplně stejné.
- Pokud chcete vyhledat přibližnou shodu, můžete také zadat hodnotu TRUE nebo 1.
Krok 11. Napište) na konci pro uzavření vzorce
Nyní můžete spustit vzorec a zjistit, zda je vybraná položka hledání ve vašem druhém seznamu opakovanou nebo novou hodnotou.
Pokud například váš druhý seznam začíná na B1 a váš první seznam přechází z buněk A1 do A5, bude váš vzorec vypadat takto = vlookup (B1, $ A $ 1: $ A $ 5, 1, false).
Krok 12. Stiskněte klávesu ↵ Enter nebo ⏎ Vraťte se na klávesnici.
Tím se spustí vzorec a prohledá váš první seznam první položku z vašeho druhého seznamu.
- Pokud se jedná o hodnotu opakování, uvidíte stejnou hodnotu znovu vytištěnou v buňce vzorce.
- Pokud je to nová hodnota, uvidíte „ #N/A “vytištěno zde.
- Pokud například v prvním seznamu hledáte „John“a nyní v buňce vzorců uvidíte „John“, jedná se o hodnotu opakování, která se objeví v obou seznamech. Pokud vidíte „#N/A“, je to nová hodnota ve druhém seznamu.
Krok 13. Vyberte buňku vzorce
Po spuštění vzorce a zobrazení výsledků první položky seznamu jej vyberte kliknutím na buňku vzorce.
Krok 14. Klikněte a přetáhněte dolů zelenou tečku v pravém dolním rohu buňky
Tím se buňka vzorce rozbalí v seznamu a vzorec se použije na každou položku seznamu ve vašem druhém seznamu.
- Tímto způsobem můžete porovnat každou položku ve vašem druhém seznamu s celým prvním seznamem.
- To prohledá váš první seznam pro každou položku ve vašem druhém seznamu samostatně a zobrazí výsledek vedle každé buňky samostatně.
- Pokud chcete místo „#N/A“vidět jinou značku pro nové hodnoty, použijte tento vzorec: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, false), „Nová hodnota“). Tím se vytiskne „Nová hodnota“pro nové hodnoty místo „#N/A“.
