Jednou z mnoha možností aplikace Microsoft Excel je možnost porovnat dva seznamy dat, identifikovat shody mezi seznamy a identifikovat položky, které se nacházejí pouze v jednom seznamu. To je užitečné při porovnávání finančních záznamů nebo při kontrole, zda je konkrétní jméno v databázi. Funkci MATCH můžete použít k identifikaci a označení shodných nebo neodpovídajících záznamů, nebo můžete použít formátování s úpravou pomocí funkce COUNTIF. Následující kroky vám řeknou, jak použít každý k přiřazení vašich dat.
Kroky
Metoda 1 ze 2: Identifikace záznamů pomocí funkce MATCH
Krok 1. Zkopírujte seznamy dat do jednoho listu
Excel může pracovat s více listy v jednom sešitu nebo s více sešity, ale porovnání seznamů najdete snáze, když zkopírujete jejich informace na jeden list.
Krok 2. Udělte každé položce seznamu jedinečný identifikátor
Pokud vaše dva seznamy nesdílejí běžný způsob jejich identifikace, bude možná nutné do každého seznamu dat přidat další sloupec, který danou položku identifikuje do Excelu, aby mohl zjistit, zda položka v daném seznamu souvisí s položkou v druhém seznamu. Povaha tohoto identifikátoru bude záviset na druhu dat, která se pokoušíte porovnat. Pro každý seznam sloupců budete potřebovat identifikátor.
- U finančních údajů spojených s daným obdobím, například u daňové evidence, to může být popis aktiva, datum pořízení majetku nebo obojí. V některých případech může být záznam označen číslem kódu; pokud však pro oba seznamy není použit stejný systém, může tento identifikátor vytvářet shody tam, kde žádné nejsou, nebo ignorovat shody, které by měly být provedeny.
- V některých případech můžete vzít položky z jednoho seznamu a zkombinovat je s položkami z jiného seznamu a vytvořit tak identifikátor, například popis fyzického majetku a rok, kdy byl zakoupen. Chcete -li vytvořit takový identifikátor, zřetězíte (přidáte, zkombinujete) data ze dvou nebo více buněk pomocí znaku ampersand (&). Chcete -li zkombinovat popis položky v buňce F3 s datem v buňce G3 odděleným mezerou, zadáte do jiné buňky v tomto řádku, například E3, vzorec '= F3 & "" & G3'. Pokud byste chtěli do identifikátoru zahrnout pouze rok (protože jeden seznam používá úplná data a druhý používá pouze roky), zahrnuli byste funkci YEAR tak, že místo toho do buňky E3 zadáte '= F3 & "" & YEAR (G3)'. (Nezahrnujte jednoduché uvozovky; slouží pouze k označení příkladu.)
- Jakmile vytvoříte vzorec, můžete jej zkopírovat do všech ostatních buněk sloupce identifikátorů tak, že vyberete buňku se vzorcem a přetáhnete úchyt výplně přes ostatní buňky sloupce, kam chcete vzorec zkopírovat. Když uvolníte tlačítko myši, každá buňka, kterou přetáhnete, bude naplněna vzorcem, přičemž odkazy na buňky budou upraveny na příslušné buňky ve stejném řádku.
Krok 3. Standardizujte data, kde je to možné
Zatímco mysl uznává, že „Inc.“a „Incorporated“znamenají totéž, Excel nikoli, pokud jej necháte přeformátovat jedno nebo druhé slovo. Podobně můžete považovat hodnoty jako 11, 950 a 11 999,95 dolarů za dostatečně blízké, aby se shodovaly, ale Excel ne, pokud to neřeknete.
- S některými zkratkami, například „Co“pro „Společnost“a „Inc“pro „Incorporated, můžete pracovat pomocí funkce VLEVO pro zkrácení dalších znaků. Jiné zkratky, například„ Assn “pro„ Association “, mohou být nejlepší vyřešeno vytvořením průvodce stylem zadávání dat a poté napsáním programu, který vyhledá a opraví nevhodné formáty.
- U řetězců čísel, jako jsou PSČ, kde některé položky obsahují příponu ZIP+4 a jiné nikoli, můžete znovu použít funkci VLEVO řetězec k rozpoznání a přiřazení pouze primárních PSČ. Chcete -li, aby Excel rozpoznal číselné hodnoty, které jsou blízké, ale ne stejné, můžete použít funkci ROUND zaokrouhlit zavřené hodnoty na stejné číslo a přiřadit je.
- Nadbytečné mezery, například psaní dvou mezer mezi slova místo jedné, lze odebrat pomocí funkce TRIM.
Krok 4. Vytvořte sloupce pro srovnávací vzorec
Stejně jako jste museli vytvořit sloupce pro identifikátory seznamu, budete muset vytvořit sloupce pro vzorec, který za vás porovnává. Pro každý seznam budete potřebovat jeden sloupec.
Tyto sloupce budete chtít označit něčím jako „Chybí?“
Krok 5. Do každé buňky zadejte srovnávací vzorec
Pro srovnávací vzorec použijete funkci MATCH vnořenou do jiné funkce Excelu, ISNA.
- Vzorec má tvar „= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))“, kde je buňka sloupce identifikátoru prvního seznamu porovnána s každým z identifikátorů ve druhém seznamu s zjistěte, zda odpovídá jednomu z nich. Pokud se neshoduje, záznam chybí a v této buňce se zobrazí slovo „PRAVDA“. Pokud se shoduje, záznam je přítomen a zobrazí se slovo „FALSE“. (Při zadávání vzorce nezahrnujte uvozovky.)
- Vzorec můžete zkopírovat do zbývajících buněk sloupce stejným způsobem, jako jste zkopírovali vzorec identifikátoru buňky. V tomto případě se změní pouze odkaz na buňku pro buňku identifikátoru, protože umístění znaků dolaru před odkazy na řádky a sloupce pro první a poslední buňky v seznamu identifikátorů druhé buňky z nich činí absolutní odkazy.
- Srovnávací vzorec pro první seznam můžete zkopírovat do první buňky sloupce pro druhý seznam. Poté budete muset upravit odkazy na buňky tak, aby „G3“bylo nahrazeno odkazem na první identifikační buňku druhého seznamu a „$ L $ 3: $ L $ 14“nahrazeno první a poslední identifikační buňkou druhý seznam. (Nechte znaky dolaru a dvojtečku na pokoji.) Tento upravený vzorec pak můžete zkopírovat do zbývajících buněk ve srovnávacím řádku druhého seznamu.
Krok 6. Seřaďte seznamy, abyste v případě potřeby snáze viděli neshodné hodnoty
Pokud jsou vaše seznamy velké, možná je budete muset seřadit, abyste dali dohromady všechny neodpovídající hodnoty. Pokyny v dílčích krocích níže převedou vzorce na hodnoty, aby se předešlo chybám v přepočtu, a pokud jsou vaše seznamy velké, zabrání se dlouhému času přepočítávání.
- Vyberte ji přetažením myši přes všechny buňky v seznamu.
- Vyberte Kopírovat z nabídky Upravit v aplikaci Excel 2003 nebo ze skupiny Schránka na pásu karet Domů v aplikaci Excel 2007 nebo 2010.
- Vyberte Vložit jinak z nabídky Upravit v aplikaci Excel 2003 nebo z rozevíracího tlačítka Vložit ve skupině Schránka na pásu karet aplikace Excel 2007 nebo 2010 na domovské stránce.
- Vyberte „Hodnoty“ze seznamu Vložit jako v dialogovém okně Vložit jinak. Dialog zavřete kliknutím na OK.
- V nabídce Data v aplikaci Excel 2003 nebo ve skupině Seřadit a filtrovat na pásu dat v aplikaci Excel 2007 nebo 2010 vyberte možnost Seřadit.
- V dialogu Seřadit podle vyberte "Řádek záhlaví" ze seznamu "Můj rozsah dat má", vyberte "Chybí?" (nebo jméno, které jste ve skutečnosti dali záhlaví sloupce srovnání) a klikněte na OK.
- Opakujte tyto kroky pro další seznam.
Krok 7. Porovnejte neshodné položky vizuálně, abyste zjistili, proč se neshodují
Jak již bylo uvedeno dříve, Excel je navržen tak, aby vyhledával přesné shody dat, pokud jej nenastavíte tak, aby vyhledával přibližné. Vaše neshoda může být stejně jednoduchá jako náhodné transponování písmen nebo číslic. Mohlo by to být také něco, co vyžaduje nezávislé ověření, jako je kontrola, zda je třeba v první řadě vykazovat uvedená aktiva.
Metoda 2 ze 2: Podmíněné formátování pomocí COUNTIF
Krok 1. Zkopírujte seznamy dat do jednoho listu
Krok 2. Rozhodněte, ve kterém seznamu chcete zvýraznit shodné nebo neodpovídající záznamy
Pokud chcete zvýraznit záznamy pouze v jednom seznamu, pravděpodobně budete chtít zvýraznit záznamy jedinečné pro tento seznam; tj. záznamy, které neodpovídají záznamům v jiném seznamu. Pokud chcete zvýraznit záznamy v obou seznamech, budete chtít zvýraznit záznamy, které se navzájem shodují. Pro účely tohoto příkladu budeme předpokládat, že první seznam zabírá buňky G3 až G14 a druhý seznam buňky L3 až L14.
Krok 3. Vyberte položky v seznamu, ve kterých chcete zvýraznit jedinečné nebo odpovídající položky
Pokud chcete zvýraznit shodné položky v obou seznamech, budete muset seznamy vybrat jeden po druhém a na každý seznam použít srovnávací vzorec (popsaný v dalším kroku).
Krok 4. Použijte příslušný srovnávací vzorec
Chcete -li to provést, budete muset ve své verzi aplikace Excel otevřít dialogové okno Podmíněné formátování. V aplikaci Excel 2003 to provedete výběrem podmíněného formátování z nabídky Formát, zatímco v aplikaci Excel 2007 a 2010 kliknete na tlačítko Podmíněné formátování ve skupině Styly na pásu karet Domů. Vyberte typ pravidla jako „Vzorec“a zadejte vzorec do pole Upravit popis pravidla.
- Pokud chcete zvýraznit záznamy jedinečné pro první seznam, vzorec by byl „= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)“, přičemž rozsah buněk druhého seznamu je vykreslen jako absolutní hodnoty a odkaz do první buňky prvního seznamu jako relativní hodnota. (Nezadávejte úzké uvozovky.)
- Pokud chcete zvýraznit záznamy jedinečné pro druhý seznam, vzorec by byl „= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)“, přičemž rozsah buněk prvního seznamu je vykreslen jako absolutní hodnoty a odkaz do první buňky druhého seznamu jako relativní hodnota. (Nezadávejte úzké uvozovky.)
- Pokud chcete zvýraznit záznamy v každém seznamu, které se nacházejí v druhém seznamu, budete potřebovat dva vzorce, jeden pro první seznam a jeden pro druhý. Vzorec pro první seznam je „= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)“, zatímco vzorec pro druhý seznam je COUNTIF ($ G $ 3: $ G $ 14, L3> 0) “. poznamenal dříve, vyberete první seznam použít jeho vzorec a pak vyberte druhý seznam použít jeho vzorec.
- Použijte jakékoli formátování, které chcete, aby se zvýraznily označované záznamy. Dialog zavřete kliknutím na OK.
Video - Používáním této služby mohou být některé informace sdíleny s YouTube
Tipy
- Místo použití odkazu na buňku s metodou podmíněného formátování COUNTIF můžete zadat hledanou hodnotu a označit jeden nebo více seznamů pro instance této hodnoty.
- Pro zjednodušení srovnávacích formulářů můžete pro svůj seznam vytvořit názvy, například „List1“a „List2“. Při psaní vzorců pak tyto názvy seznamů mohou nahradit absolutní rozsahy buněk použité v příkladech výše.
