Tento wikiHow vás naučí používať podmienené formátovanie na porovnanie všetkých buniek v dvoch oddelených zoznamoch v tabuľke programu Excel a označiť bunky, ktoré sa zobrazujú v oboch zoznamoch. Táto funkcia je k dispozícii iba v počítačovej verzii Excelu a mobilná aplikácia ju nepodporuje.
Kroky
Metóda 1 z 2: Použitie vzorca CountIf
Krok 1. Otvorte tabuľku programu Excel, ktorú chcete upraviť
Nájdite tabuľkový súbor so zoznamami, ktoré chcete porovnať, a dvojitým kliknutím na súbor ho otvorte v programe Microsoft Excel.
Krok 2. Vyberte svoj prvý zoznam
Kliknite na prvú bunku v prvom zozname a ťahaním myši úplne nadol do poslednej bunky zoznamu vyberte celý rozsah údajov.
Krok 3. Kliknite na kartu Vzorce na páse s nástrojmi
Túto kartu nájdete nad panelom s nástrojmi v hornej časti tabuľky. Otvorí vaše nástroje vzorcov na páse s nástrojmi.
Krok 4. Na paneli s nástrojmi kliknite na položku Definovať názov
Túto možnosť nájdete v strede pása s nástrojmi „Vzorce“. Otvorí sa nové vyskakovacie okno a umožní vám pomenovať zoznam.
Krok 5. Do poľa Názov napíšte List1
Kliknite na textové pole v hornej časti kontextového okna a zadajte tu názov zoznamu.
- Tento názov môžete neskôr použiť na vloženie zoznamu do porovnávacieho vzorca.
- Zoznam tu môžete alternatívne pomenovať. Ak je to napríklad zoznam miest, môžete ho pomenovať „locations1“alebo „locationList“.
Krok 6. V kontextovom okne kliknite na tlačidlo OK
Toto potvrdí vašu akciu a pomenuje váš zoznam.
Krok 7. Druhý zoznam pomenujte ako List2
Vykonajte rovnaké kroky ako v prvom zozname a pomenujte svoj druhý zoznam. To vám umožní neskôr tento druhý zoznam vo vzore na porovnanie rýchlo použiť.
Zoznamu môžete dať ľubovoľné meno. Tu si nezabudnite zapamätať alebo uviesť názov, ktorý uvediete pre každý zo svojich zoznamov
Krok 8. Vyberte svoj prvý zoznam
Kliknite na prvú bunku v prvom zozname a potiahnutím nadol vyberte celý rozsah údajov.
Pred nastavením podmieneného formátovania sa uistite, že je vybratý váš prvý zoznam
Krok 9. Kliknite na kartu Domov na paneli s nástrojmi
Toto je prvá karta v ľavom hornom rohu na páse s nástrojmi. Otvorí vaše základné tabuľkové nástroje na paneli s nástrojmi.
Krok 10. Na paneli s nástrojmi kliknite na Podmienené formátovanie
Táto možnosť vyzerá ako malá ikona tabuľky s niektorými bunkami zvýraznenými červenou a modrou farbou. Otvorí sa rozbaľovacia ponuka všetkých vašich možností formátovania.
Krok 11. V rozbaľovacej ponuke kliknite na položku Nové pravidlo
Otvorí sa nové vyskakovacie okno a umožní vám manuálne nastaviť nové pravidlo formátovania pre vybratý rozsah.
Krok 12. Vyberte možnosť „Na základe vzorca určiť, ktoré bunky sa majú formátovať“
Táto možnosť vám umožní manuálne zadať formátovací vzorec na porovnanie vašich dvoch zoznamov.
- Zapnuté Windows, nájdete ho v spodnej časti zoznamu pravidiel v poli „Vyberte typ pravidla“.
- Zapnuté Mac, vyberte Klasika v rozbaľovacej ponuke „Štýl“v hornej časti vyskakovacieho okna. Potom nájdite túto možnosť v druhej rozbaľovacej ponuke pod ponukou Štýl.
Krok 13. V rozbaľovacom okne kliknite na pole vzorca
Tu môžete zadať ľubovoľný platný vzorec programu Excel a nastaviť tak pravidlo podmieneného formátovania.
Krok 14. Do panela vzorcov zadajte = countif (List2, A1) = 1
Tento vzorec naskenuje vaše dva zoznamy a označí všetky bunky v prvom zozname, ktoré sa zobrazia aj v druhom zozname.
- Nahraďte A1 vo vzorci číslom prvej bunky vášho prvého zoznamu.
- Ak je napríklad prvá bunka vášho prvého zoznamu bunka D5, váš vzorec bude vyzerať = countif (List2, D5) = 1.
- Ak ste druhému zoznamu dali iný názov, uistite sa, že ste vo vzorci nahradili zoznam 2 skutočným názvom svojho vlastného zoznamu.
- Prípadne zmeňte vzorec na = countif (List2, A1) = 0, ak chcete označiť bunky, ktoré nie figurujú v druhom zozname.
Krok 15. Do panela vzorcov (voliteľné) zadajte = countif (List1, B1) = 1
Ak chcete nájsť a označiť bunky na svojom druhý Ak sa nachádzate v prvom zozname, použite tento vzorec namiesto prvého.
Nahraďte List1 názvom vášho prvého zoznamu a B1 prvou bunkou vášho druhého zoznamu
Krok 16. Vyberte vlastný formát na označenie buniek (voliteľné)
Bunky, ktoré váš vzorec nájde, môžete označiť vlastnou farbou výplne pozadia a rôznymi štýlmi písma.
- Zapnuté Windows, kliknite na Formát tlačidlo v pravom dolnom rohu vyskakovacieho okna. Farbu pozadia môžete vybrať na karte „Výplň“a štýly písma na karte „Písmo“.
- Zapnuté Mac, v spodnej časti rozbaľovacej ponuky „Formátovať s“vyberte prednastavený formát. Môžete tiež vybrať vlastný formát tu manuálne vyberte výplň pozadia a štýly písma.
Krok 17. V kontextovom okne kliknite na tlačidlo OK
Tým sa potvrdí a použije váš porovnávací vzorec. Všetky bunky v prvom zozname, ktoré sa zobrazia aj v druhom zozname, budú označené zvolenou farbou a písmom.
- Ak napríklad vyberiete svetlo červenú výplň s tmavočerveným textom, všetky opakujúce sa bunky sa vo vašom prvom zozname zmenia na túto farbu.
- Ak použijete druhý vzorec vyššie, podmienené formátovanie označí opakujúce sa bunky vo vašom druhom zozname namiesto prvého.
Metóda 2 z 2: Použitie vzorca VLookup
Krok 1. Otvorte tabuľku programu Excel
Vyhľadajte súbor programu Excel so zoznamami, ktoré chcete porovnať, a dvojitým kliknutím na názov alebo ikonu súboru otvorte tabuľku v programe Microsoft Excel.
Krok 2. Kliknite na prázdnu bunku vedľa prvej položky vo vašom druhom zozname
Nájdite v tabuľke svoj druhý zoznam a kliknite na prázdnu bunku vedľa prvej položky zoznamu v hornej časti.
- Tu môžete vložiť vzorec VLookup.
- Prípadne môžete vybrať ľubovoľnú prázdnu bunku v tabuľke. Táto bunka vám iba umožní pohodlnejšie vidieť porovnanie vedľa vášho druhého zoznamu.
Krok 3. Zadajte = vlookup (do prázdnej bunky
Vzorec VLookup vám umožní porovnať všetky položky v dvoch oddelených zoznamoch a zistiť, či je hodnota opakovaním alebo novou hodnotou.
Nezatvárajte zátvorku vzorca, kým nie je váš vzorec kompletný
Krok 4. Vyberte prvú položku v druhom zozname
Bez zatvorenia zátvoriek vzorca kliknite na prvú položku v druhom zozname. Tým sa do vzorca vloží prvá bunka vášho druhého zoznamu.
Krok 5. Do vzorca zadajte a, čiarku
Po výbere prvej bunky v druhom zozname zadajte do vzorca čiarku. Ďalej budete môcť vybrať rozsah porovnania.
Krok 6. Podržte a vyberte celý svoj prvý zoznam
Tým sa rozsah buniek vášho prvého zoznamu vloží do druhej časti vzorca VLookup.
To vám umožní vyhľadať v prvom zozname vybratú položku z vášho druhého zoznamu (prvá položka v hornej časti druhého zoznamu) a vrátiť sa, ak ide o opakovanú alebo novú hodnotu
Krok 7. Do vzorca zadajte a, čiarku
Zamkne sa tým rozsah porovnania vo vašom vzorci.
Krok 8. Do vzorca za čiarkou zadajte 1
Toto číslo predstavuje indexové číslo stĺpca. Požiada vzorec VLookup, aby vyhľadal skutočný stĺpec zoznamu namiesto iného stĺpca vedľa neho.
Ak chcete, aby váš vzorec vrátil hodnotu zo stĺpca hneď vedľa prvého zoznamu, zadajte sem 2
Krok 9. Do vzorca zadajte a, čiarku
Vo vzorci VLookup sa uzamkne indexové číslo stĺpca (1).
Krok 10. Do vzorca zadajte FALSE
V zozname sa namiesto približných zhôd vyhľadá presná zhoda vybratej položky vyhľadávania (prvá položka v druhom zozname).
- Namiesto FALSE môžete použiť 0, je to úplne rovnaké.
- Prípadne môžete zadať TRUE alebo 1, ak chcete vyhľadať približnú zhodu.
Krok 11. Na záver zadajte vzorec)
Teraz môžete spustiť svoj vzorec a zistiť, či je vybratá položka vyhľadávania vo vašom druhom zozname opakovanou alebo novou hodnotou.
Ak napríklad váš druhý zoznam začína na B1 a váš prvý zoznam prechádza z buniek A1 do A5, váš vzorec bude vyzerať takto = vlookup (B1, $ A $ 1: $ A $ 5, 1, nepravda).
Krok 12. Stlačte kláves ↵ Enter alebo ⏎ Vráťte sa na klávesnicu.
Spustí sa vzorec a v prvom zozname sa vyhľadá prvá položka z druhého zoznamu.
- Ak sa jedná o hodnotu opakovania, rovnaká hodnota sa znova vytlačí v bunke vzorca.
- Ak je to nová hodnota, uvidíte „ #N/A “vytlačené tu.
- Ak napríklad hľadáte v prvom zozname výraz „Ján“a teraz sa v bunke vzorca zobrazí „Ján“, je to hodnota opakovania, ktorá sa objaví v oboch zoznamoch. Ak vidíte „#N/A“, je to nová hodnota v druhom zozname.
Krok 13. Vyberte bunku vzorca
Po spustení vzorca a zobrazení výsledkov pre prvú položku zoznamu ho vyberte kliknutím na bunku vzorca.
Krok 14. Kliknite a potiahnite nadol zelenú bodku v pravom dolnom rohu bunky
Tým sa bunka vzorca rozbalí v zozname a vzorec sa použije na každú položku zoznamu vo vašom druhom zozname.
- Týmto spôsobom môžete porovnať každú položku vo vašom druhom zozname s celým prvým zoznamom.
- Toto vyhľadá váš prvý zoznam pre každú položku v druhom zozname jednotlivo a výsledok sa zobrazí vedľa každej bunky zvlášť.
- Ak chcete namiesto „#N/A“vidieť inú značku pre nové hodnoty, použite tento vzorec: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, false), „Nová hodnota“). Toto vytlačí „Nová hodnota“pre nové hodnoty namiesto „#N/A“.
