Jednou z mnohých možností programu Microsoft Excel je možnosť porovnať dva zoznamy údajov, identifikovať zhody medzi zoznamami a identifikovať položky, ktoré sa nachádzajú iba v jednom zozname. Je to užitočné pri porovnávaní finančných záznamov alebo kontrole, či je konkrétne meno v databáze. Na identifikáciu a označenie zhodných alebo nezhodných záznamov môžete použiť funkciu MATCH, alebo môžete použiť podmienené formátovanie s funkciou COUNTIF. Nasledujúce kroky vám povedia, ako ich použiť na priradenie údajov.
Kroky
Metóda 1 z 2: Identifikácia záznamov pomocou funkcie MATCH
Krok 1. Skopírujte zoznamy údajov do jedného pracovného hárka
Excel môže pracovať s viacerými pracovnými listami v jednom zošite alebo s viacerými zošitmi, ale porovnávanie zoznamov bude jednoduchšie, ak skopírujete ich informácie do jedného pracovného hárka.
Krok 2. Každej položke zoznamu dajte jedinečný identifikátor
Ak vaše dva zoznamy nezdieľajú spoločný spôsob ich identifikácie, možno budete musieť do každého zoznamu údajov pridať ďalší stĺpec, ktorý danú položku identifikuje do Excelu, aby mohol zistiť, či položka v danom zozname súvisí s položkou. v druhom zozname. Povaha tohto identifikátora bude závisieť od druhu údajov, ktoré sa pokúšate priradiť. Pre každý zoznam stĺpcov budete potrebovať identifikátor.
- V prípade finančných údajov spojených s daným obdobím, ako napríklad v daňovej evidencii, to môže byť opis majetku, dátum nadobudnutia majetku alebo oboje. V niektorých prípadoch môže byť záznam identifikovaný číslom kódu; ak sa však pre oba zoznamy nepoužije rovnaký systém, tento identifikátor môže vytvárať zhody tam, kde nie sú, alebo ignorovať zhody, ktoré by sa mali vytvoriť.
- V niektorých prípadoch môžete prevziať položky z jedného zoznamu a skombinovať ich s položkami z iného zoznamu, aby ste vytvorili identifikátor, napríklad popis fyzického majetku a rok jeho nákupu. Na vytvorenie takého identifikátora zreťazíte (pridáte, skombinujete) údaje z dvoch alebo viacerých buniek pomocou znaku ampersand (&). Ak chcete skombinovať popis položky v bunke F3 s dátumom v bunke G3 oddeleným medzerou, do inej bunky v tomto riadku, napríklad E3, zadáte vzorec „= F3 &““& G3 '. Ak by ste chceli do identifikátora zahrnúť iba rok (pretože jeden zoznam používa úplné dátumy a druhý používa iba roky), zadali by ste funkciu ROK zadaním '= F3 & "" & YEAR (G3)' do bunky E3. (Neuvádzajte jednoduché úvodzovky; slúžia len na uvedenie príkladu.)
- Po vytvorení vzorca ho môžete skopírovať do všetkých ostatných buniek stĺpca identifikátora tak, že vyberiete bunku so vzorcom a potiahnete rukoväť výplne nad ostatné bunky stĺpca, do ktorého chcete vzorec skopírovať. Keď uvoľníte tlačidlo myši, každá bunka, ktorú ste pretiahli, bude vyplnená vzorcom s odkazmi na bunky upravenými na príslušné bunky v rovnakom riadku.
Krok 3. Štandardizujte údaje, ak je to možné
Zatiaľ čo myseľ uznáva, že „Inc.“a „začlenené“znamenajú to isté, Excel to neurobí, pokiaľ ho nepreformátujete na jedno alebo druhé slovo. Podobne môžete považovať hodnoty, ako sú 11 950 a 11 999,95 dolárov, za dostatočne blízke na to, aby sa zhodovali, ale Excel to neurobí, pokiaľ vám to nepovie.
- Môžete sa vysporiadať s niektorými skratkami, ako napríklad „Co“pre „Spoločnosť“a „Inc“pre „Začlenené pomocou funkcie VLEVO na skrátenie ďalších znakov. Najlepšie môžu byť iné skratky, ako napríklad„ Assn “pre„ Asociácia “. bolo vyriešené vytvorením sprievodcu štýlom zadávania údajov a potom napísaním programu na vyhľadávanie a opravu nevhodných formátov.
- V prípade reťazcov čísel, ako sú PSČ, kde niektoré položky obsahujú príponu ZIP+4 a iné nie, môžete znova použiť funkciu ĽAVÉHO reťazca na rozpoznanie a priradenie iba primárnych PSČ. Ak chcete, aby Excel rozpoznal číselné hodnoty, ktoré sú blízke, ale nie sú rovnaké, môžete pomocou funkcie ROUND zaokrúhliť blízke hodnoty na rovnaké číslo a priradiť ich.
- Nadbytočné medzery, ako napríklad zadanie dvoch medzier medzi slová namiesto jednej, je možné odstrániť pomocou funkcie TRIM.
Krok 4. Vytvorte stĺpce pre porovnávací vzorec
Rovnako ako ste museli vytvoriť stĺpce pre identifikátory zoznamu, budete musieť vytvoriť stĺpce pre vzorec, ktorý za vás porovnáva. Pre každý zoznam budete potrebovať jeden stĺpec.
Tieto stĺpce budete chcieť označiť niečím ako „Chýba?“
Krok 5. Do každej bunky zadajte porovnávací vzorec
Na porovnávací vzorec použijete funkciu MATCH vnorenú do inej funkcie programu Excel, ISNA.
- Vzorec má tvar „= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))“, kde je bunka stĺpca identifikátora prvého zoznamu porovnaná s každým z identifikátorov v druhom zozname s zistite, či sa zhoduje s jedným z nich. Ak sa nezhoduje, záznam chýba a v tejto bunke sa zobrazí slovo „PRAVDA“. Ak sa zhoduje, záznam je k dispozícii a zobrazí sa slovo „FALSE“. (Pri zadávaní vzorca nepoužívajte úvodzovky.)
- Vzorec môžete skopírovať do zvyšných buniek stĺpca rovnakým spôsobom, ako ste skopírovali vzorec identifikátora bunky. V tomto prípade sa zmení iba odkaz na bunku identifikátora, pretože umiestnenie znakov dolára pred odkazy na riadky a stĺpce pre prvé a posledné bunky v zozname identifikátorov druhých buniek z nich robí absolútne referencie.
- Porovnávací vzorec pre prvý zoznam môžete skopírovať do prvej bunky stĺpca pre druhý zoznam. Potom budete musieť upraviť odkazy na bunky tak, aby sa „G3“nahradilo odkazom na prvú identifikačnú bunku druhého zoznamu a „$ L $ 3: $ L $ 14“sa nahradilo prvou a poslednou identifikačnou bunkou druhý zoznam. (Nechajte znaky dolára a dvojbodku na pokoji.) Tento upravený vzorec potom môžete skopírovať do zvyšných buniek v porovnávacom riadku druhého zoznamu.
Krok 6. Zoraďte zoznamy, aby sa v prípade potreby ľahšie zobrazili nezhodné hodnoty
Ak sú vaše zoznamy veľké, možno ich budete musieť zoradiť, aby ste dali dohromady všetky nezhodné hodnoty. Pokyny v nižšie uvedených krokoch prevedú vzorce na hodnoty, aby sa predišlo chybám pri prepočte, a ak sú vaše zoznamy veľké, zabráni dlhému času prepočtu.
- Vyberte ju potiahnutím myši nad všetkými bunkami v zozname.
- Vyberte položku Kopírovať z ponuky Upraviť v programe Excel 2003 alebo zo skupiny Schránka na páse s nástrojmi Domov v programe Excel 2007 alebo 2010.
- Vyberte položku Prilepiť špeciálne z ponuky Upraviť v programe Excel 2003 alebo z rozbaľovacieho tlačidla Prilepiť v skupine Schránka na domovskej páse s nástrojmi Excelu 2007 alebo 2010.
- V zozname Prilepiť ako v dialógovom okne Prilepiť špeciálne vyberte položku „Hodnoty“. Dialógové okno zatvoríte kliknutím na tlačidlo OK.
- Vyberte položku Zoradiť z ponuky Údaje v programe Excel 2003 alebo zo skupiny Zoradiť a filtrovať na páse s údajmi v programe Excel 2007 alebo 2010.
- V dialógovom okne Zoradiť podľa vyberte „Riadok hlavičky“zo zoznamu „Môj rozsah údajov obsahuje“, zvoľte „Chýba?“(alebo názov, ktorý ste skutočne dali hlavičke porovnávacieho stĺpca) a kliknite na tlačidlo OK.
- Zopakujte tieto kroky pre ďalší zoznam.
Krok 7. Porovnajte nesúladné položky vizuálne, aby ste zistili, prečo sa nezhodujú
Ako už bolo uvedené, Excel je navrhnutý tak, aby vyhľadával presné zhody údajov, pokiaľ ho nenastavíte tak, aby hľadal približné. Váš nesúlad môže byť taký jednoduchý, ako náhodná transpozícia písmen alebo číslic. Mohlo by to byť aj niečo, čo si vyžaduje nezávislé overenie, ako je kontrola, či je v prvom rade potrebné uviesť uvedené aktíva.
Metóda 2 z 2: Podmienené formátovanie pomocou COUNTIF
Krok 1. Skopírujte zoznamy údajov do jedného pracovného hárka
Krok 2. Rozhodnite, v ktorom zozname chcete zvýrazniť zodpovedajúce alebo nezhodné záznamy
Ak chcete zvýrazniť záznamy iba v jednom zozname, pravdepodobne budete chcieť zvýrazniť záznamy jedinečné pre daný zoznam; to znamená záznamy, ktoré sa nezhodujú so záznamami v inom zozname. Ak chcete zvýrazniť záznamy v oboch zoznamoch, budete chcieť zvýrazniť záznamy, ktoré sa navzájom zhodujú. Na účely tohto príkladu budeme predpokladať, že prvý zoznam zaberá bunky G3 až G14 a druhý zoznam bunky L3 až L14.
Krok 3. Vyberte položky v zozname, v ktorých chcete zvýrazniť jedinečné alebo zodpovedajúce položky
Ak chcete v oboch zoznamoch zvýrazniť zodpovedajúce položky, budete musieť zoznamy vybrať po jednom a na každý zoznam použiť porovnávací vzorec (popísaný v nasledujúcom kroku).
Krok 4. Aplikujte príslušný porovnávací vzorec
Ak to chcete urobiť, budete vo svojej verzii Excelu musieť otvoriť dialógové okno Podmienené formátovanie. V programe Excel 2003 to urobíte tak, že v ponuke Formát vyberiete Podmienené formátovanie, zatiaľ čo v Exceli 2007 a 2010 kliknete na tlačidlo Podmienené formátovanie v skupine Štýly na páse s nástrojmi Domov. Vyberte typ pravidla ako „Vzorec“a zadajte vzorec do poľa Upraviť popis pravidla.
- Ak chcete zvýrazniť záznamy jedinečné pre prvý zoznam, vzorec by bol „= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)“, pričom rozsah buniek druhého zoznamu sa vykreslí ako absolútne hodnoty a odkaz do prvej bunky prvého zoznamu ako relatívna hodnota. (Nezadávajte úzke úvodzovky.)
- Ak chcete zvýrazniť záznamy jedinečné pre druhý zoznam, vzorec by bol „= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)“, pričom rozsah buniek prvého zoznamu sa vykreslí ako absolútne hodnoty a odkaz do prvej bunky druhého zoznamu ako relatívna hodnota. (Nezadávajte úvodzovky.)
- Ak chcete zvýrazniť záznamy v každom zozname, ktoré sa nachádzajú v druhom zozname, budete potrebovať dva vzorce, jeden pre prvý zoznam a jeden pre druhý. Vzorec pre prvý zoznam je „= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)“, zatiaľ čo vzorec pre druhý zoznam je COUNTIF ($ G $ 3: $ G $ 14, L3> 0) “. Ako ako už bolo spomenuté, vyberiete prvý zoznam a použijete jeho vzorec a potom vyberiete druhý zoznam, aby ste použili jeho vzorec.
- Na zvýraznenie záznamov, ktoré sú označené, použite akékoľvek formátovanie, ktoré chcete. Dialógové okno zatvoríte kliknutím na tlačidlo OK.
Video - používaním tejto služby môžu byť niektoré informácie zdieľané so službou YouTube
Tipy
- Namiesto použitia odkazu na bunku pri metóde podmieneného formátovania COUNTIF môžete zadať hľadanú hodnotu a označiť jeden alebo viac zoznamov pre inštancie tejto hodnoty.
- Na zjednodušenie porovnávacích formulárov môžete pre svoj zoznam vytvoriť názvy, napríklad „List1“a „List2“. Potom pri písaní vzorcov môžu tieto názvy zoznamov nahradiť absolútne rozsahy buniek použité v príkladoch vyššie.
