Ako priradiť údaje v programe Excel?
Ak chcete zvýrazniť záznamy v oboch zoznamoch, budete chcieť zvýrazniť záznamy, ktoré sa navzájom zhodujú.
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. Funkciu MATCH môžete použiť na identifikáciu a označenie zhodných alebo nezhodných záznamov alebo môžete použiť podmienené formátovanie s funkciou COUNTIF. Nasledujúce kroky vám povedia, ako ich použiť na priradenie údajov.
Metóda 1 z 2: identifikácia záznamov pomocou funkcie MATCH
- 1Skopí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.
- 2Každej položke zoznamu dajte jedinečný identifikátor. Ak vaše dva zoznamy nezdieľajú bež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 však pre oba zoznamy nie je použitý rovnaký systém, tento identifikátor môže vytvárať zhody tam, kde nie sú, alebo ignorovať zhody, ktoré by sa mali vykonať.
- 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 YEAR tak, že namiesto toho do bunky E3 zadáte '= F3 & "" & YEAR (G3)'. (Neuvádzajte jednoduché úvodzovky; slúžia len na ukážku.)
- 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 presunuli, sa vyplní vzorcom a odkazy na bunky sa upravia na príslušné bunky v rovnakom riadku.
- 3Pokiaľ je to možné, štandardizujte údaje. Zatiaľ čo myseľ uznáva, že „Inc.“ a "Incorporated" znamenajú rovnakú vec, Excel nie je, ak ste ju sformátovať jedno slovo alebo iný. Podobne môžete považovať hodnoty, ako sú 8920€ a 8960€, 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 VĽAVO 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 VLEVO 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.
- Extra medzery, ako napríklad napísanie dvoch medzier medzi slová namiesto jednej, je možné odstrániť pomocou funkcie TRIM.
Nasledujúce kroky vám povedia, ako ich použiť na priradenie údajov. - 4Vytvorte 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?“
- 5Do 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, $ L2,20€: $ L10€, FALSE))“, kde je bunka stĺpca identifikátora prvého zoznamu porovnaná s každým z identifikátorov v druhom zoznam, aby ste zistili, či sa zhoduje s jedným z nich. Ak sa nezhoduje, záznam chýba a v tejto bunke sa zobrazí slovo „TRUE“. Ak k tomu dôjde zápas, záznam je prítomný, a na displeji sa zobrazí 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 odkazy.
- 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 „$ L2,20€: $ L10€“ 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.
- 6Ak je to potrebné, zoraďte zoznamy a zobrazte nezhodujúce sa hodnoty jednoduchšie. Ak sú vaše zoznamy veľké, možno bude potrebné ich 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.
- Zvoliť "riadok hlavičky" z "Môj rozsah dát je" zoznam v zoradiť dialógu vyberte "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.
- 7Porovnajte položky, ktoré sa nezhodujú, vizuálne a zistite, 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 neúmyselné transponovanie 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.
Povaha tohto identifikátora bude závisieť od druhu údajov, ktoré sa pokúšate priradiť.
Metóda 2 z 2: Podmienené formátovanie pomocou countif
- 1Skopírujte zoznamy údajov do jedného pracovného hárka.
- 2Rozhodnite sa, 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.
- 3Vyberte položky v zozname, v ktorých chcete zvýrazniť jedinečné alebo zodpovedajúce položky. Ak chcete zvýrazniť zodpovedajúce položky v oboch zoznamoch, budete musieť zoznamy vybrať po jednom a použiť porovnávací vzorec (popísané v nasledujúcom kroku) do každého zoznamu.
- 4Použite 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 výberom podmieneného formátovania z ponuky Formát, zatiaľ čo v programe Excel 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 ($ L2,20€: $ L10€, G3 = 0)“, pričom rozsah buniek druhého zoznamu je vykreslený ako absolútne hodnoty a odkaz na prvú bunku prvého zoznamu ako relatívnu hodnotu. (Nezadávajte úvodzovky.)
- Ak chcete zvýrazniť záznamy jedinečné pre druhý zoznam, vzorec bude „= COUNTIF ($ G2,20€: $ G10€, L3 = 0)“, pričom rozsah buniek prvého zoznamu bude vykreslený ako absolútne hodnoty a odkaz na prvú bunku druhého zoznamu ako relatívnu hodnotu. (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 ($ L2,20€: $ L10€, G3> 0)“, zatiaľ čo vzorec pre druhý zoznam je COUNTIF ($ G2,20€: $ G10€, L3> 0) ". Ako bolo uvedené vyššie, vyberiete prvý zoznam, v ktorom použijete vzorec, a potom vyberiete druhý zoznam, aby ste použili vzorec.
- Na zvýraznenie označených záznamov použite akékoľvek formátovanie, ktoré chcete. Dialógové okno zatvoríte kliknutím na tlačidlo OK.
Na vytvorenie takého identifikátora zreťazíte (pridáte, skombinujete) údaje z dvoch alebo viacerých buniek pomocou znaku ampersand (&).
- 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 tieto názvy zoznamov môžu nahradiť absolútne rozsahy buniek použité v príkladoch vyššie.
Prečítajte si tiež: Ako vytvoriť vyzváňacie tóny pre iPhone na počítači Mac pomocou iTunes?
