porovnání seznamů s VLOOKUP
běžným požadavkem v aplikaci Excel je potřeba porovnat dva seznamy. V tomto tutoriálu porovnáme seznamy s VLOOKUPEM.
možná budete muset porovnat dva seznamy, abyste zvýraznili chybějící záznamy, zvýraznili odpovídající záznamy nebo vrátili hodnotu.
funkce VLOOKUP lze použít k porovnání seznamů v aplikaci Excel hledáním hodnoty z jednoho seznamu v jiném. Poté můžete provést požadovanou akci.
tento tutoriál ukazuje, jak porovnat seznamy s VLOOKUP. Funkce VLOOKUP lze použít s jinými vzorci nebo funkcemi aplikace Excel k dosažení různých výsledků spojených s porovnáním dvou seznamů.
zkombinujte s funkcí IF a vraťte hodnotu
řekněme, že máme dva seznamy zákazníků, jako je obrázek níže. Oba seznamy jsou totožné s tou výjimkou, že seznam 1 má méně zákazníků než seznam 2.
chceme porovnat dva seznamy a identifikovat rozdíly. Pole ID bude použito ke kontrole obou seznamů, protože se jedná o jedinečné pole.
v tomto prvním příkladu budeme hledat zákazníky ze seznamu 1 v seznamu 2 a vrátit text „nenalezeno“, pokud chybí. Pokud se nacházejí v seznamu 2, ponecháme buňku prázdnou.
níže uvedený vzorec používá k dosažení tohoto cíle funkci VLOOKUP spolu s funkcemi IF a ISNA.
pojďme se podívat na roli, kterou každá funkce hraje v tomto vzorci.
VLOOKUP: Funkce VLOOKUP byla použita k vyhledání zákazníka ve druhém seznamu a vrácení ID zákazníka (ve vzorci uvedeno jako 1).
pokud funkce VLOOKUP nemůže najít zákazníka, vrátí chybovou zprávu #N/A. To je to, co nás zajímá, protože to znamená, že zákazník je na seznamu 1, ale ne na seznamu 2.
ISNA: funkce ISNA se používá k detekci zprávy # N/A přinesené zpět pomocí VLOOKUP a překladu do True. Pokud je ID vráceno, vrátí False.
pokud: Funkce IF zobrazí text „nenalezeno“, pokud ISNA vrátí True a ponechá buňku prázdnou, pokud ISNA vrátí False. Tato funkce se používá k provedení jakékoli akce, kterou chcete, na základě zjištění z VLOOKUP.
Porovnejte seznamy s VLOOKUP pro zvýraznění chybějících záznamů
tento vzorec lze také použít s podmíněným formátováním pro zvýraznění rozdílů mezi těmito dvěma seznamy namísto vrácení hodnoty.
tento příklad ukazuje, jak změnit barvu buňky celého řádku tabulky, když zákazník chybí v druhém seznamu. Formátování celého řádku, nikoli pouze jedné buňky, umožní jasnější identifikaci chybějících záznamů, zejména v tabulce s mnoha sloupci.
- Vyberte všechny buňky tabulky kromě řádku záhlaví.
- klikněte na podmíněné formátování na domovské kartě pásu karet a vyberte Nové pravidlo.
- vyberte pomocí vzorce určit, které buňky se mají formátovat z horní poloviny okna. Ve spodní polovině zadejte vzorec níže do příslušného pole.
- klikněte na tlačítko Formát a vyberte formátování, které chcete použít na řádek chybějících záznamů
Porovnejte dva seznamy a zvýrazněte odpovídající záznamy
Chcete-li porovnat dva seznamy a zvýraznit odpovídající záznamy, bude nutné přidat funkci NOT k předchozímu vzorci.
=NOT(ISNA(VLOOKUP($A2,'Customers 2'!$A:$F,1,FALSE)))
funkce NOT se používá ke zvrácení výsledku VLOOKUP. Takže místo zvýraznění chybějících záznamů zdůrazňuje zápasy.