Ako porovnávať dva stĺpce v programe Excel - metódy porovnávania údajov v programe Excel
Toto je kapitola z knihy: Michael Girwin. Ctrl + Shift + Enter. Zvládnutie maticových vzorcov v programe Excel.
Vzorky založené na jednej alebo viacerých podmienkach.Riadok funkcie programu Excel používať operátory porovnania. Napríklad SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF a AVERAGEIFS. Tieto funkcie uskutočňujú výber na základe jednej alebo viacerých podmienok (kritérií). Problém je v tom, že tieto funkcie môžu iba pridávať, počítať a priemerovať. Čo ak chcete na svoje vyhľadávanie klásť podmienky, napríklad maximálnu hodnotu alebo štandardnú odchýlku? V týchto prípadoch, pretože neexistuje vstavaná funkcia, musíte vytvoriť vzorec poľa. Je to často spôsobené používaním operátora porovnania polí. Prvý príklad v tejto kapitole ukazuje, ako vypočítať minimálnu hodnotu za jednej podmienky.
Použime funkciu IF na výber prvkov poľa, ktoré vyhovujú podmienke. Na obr. 4.1 v ľavej tabuľke je stĺpec s názvami miest a stĺpec s časom. Je potrebné nájsť minimálny čas pre každé mesto a umiestniť túto hodnotu do zodpovedajúcej bunky v pravej tabuľke. Podmienkou výberu je názov mesta. Ak používate funkciu MIN, v stĺpci B nájdete minimálnu hodnotu. Ako však vyberiete iba tie čísla, ktoré sú špecifické pre Oakland? A ako skopírujete vzorce dolu v stĺpci? Pretože Excel nemá zabudovanú funkciu MINESLI, musíte napísať originálny vzorec kombinujúci funkcie IF a MIN.
Obrázok: 4.1. Účel vzorca: vyberte minimálny čas pre každé mesto
Stiahnite si poznámku vo formáte alebo formáte
Ako je znázornené na obr. 4.2, mali by ste začať zadávať vzorec do bunky E3 s funkciou MIN. Ale nemôžete sa hádať číslo 1 všetky hodnoty v stĺpci B!? Chcete vybrať iba tie hodnoty, ktoré sú špecifické pre Auckland.
Ako je znázornené na obr. 4.3, v ďalšom kroku zadajte funkciu IF ako argument číslo 1 na MIN. Vložíš IF dovnútra MIN.
Umiestnením kurzora na miesto, kde je zadaný argument log_expression funkcia IF (obr. 4.4), vyberiete rozsah s názvami miest A3: A8 a potom stlačte F4, aby boli odkazy na bunky absolútne (pozri napríklad ďalšie podrobnosti). Potom napíšete operátor porovnania, znamienko rovnosti. Nakoniec vyberiete bunku naľavo od vzorca - D3 a zachováte relatívny odkaz na ňu. Formulovaná podmienka vám umožní zvoliť iba Aucklands pri prezeraní rozsahu A3: A8.
Obrázok: 4.4. Vytvorte operátor poľa na argumente log_expression funkcie IF
Takže ste vytvorili operátor poľa pomocou operátora porovnania. Kedykoľvek je pole spracované, operátor poľa je operátorom porovnania, takže výsledkom jeho operácie bude pole hodnôt TRUE a FALSE. Ak to chcete overiť, vyberte pole (urobte to tak, že kliknete na popis argumentu log_expression) a stlačte kláves F9 (obr. 4.5). Spravidla použijete jeden argument log_expression, vrátenie PRAVDA alebo NEPRAVDA; tu výsledné pole vráti viaceré hodnoty PRAVDA a NEPRAVDA, takže funkcia MIN vyberie iba minimálny počet miest, ktoré zodpovedajú hodnote PRAVDA.
Obrázok: 4.5. Ak chcete zobraziť pole s hodnotami TRUE a FALSE, kliknite na argument v popise nástroja log_expression a stlačte kláves F9
Máme dve tabuľky objednávok skopírované do jedného pracovného hárka. Je potrebné porovnať údaje dvoch tabuliek v programe Excel a skontrolovať, ktoré pozície sú v prvej tabuľke, ale nie v druhej. Nemá zmysel ručne porovnávať hodnotu každej bunky.
Porovnajte dva stĺpce pre zhody v programe Excel
Ako vykonať porovnanie hodnôt v programe Excel z dvoch stĺpcov? Na vyriešenie tohto problému odporúčame použiť podmienené formátovaniena rýchle zvýraznenie položiek, ktoré sú iba v jednom stĺpci. Pracovný list s tabuľkami:
Prvým krokom je priradenie mien k obidvom tabuľkám. Toto uľahčuje pochopenie, ktoré bunkové rozsahy sa porovnávajú:
- Vyberte nástroj FORMULAS - Definované mená - Priradiť meno.
- V okne, ktoré sa zobrazí, zadajte do poľa „Názov:“ hodnotu - Table_1.
- Kliknite ľavým tlačidlom myši na vstupné pole „Rozsah:“ a vyberte rozsah: A2: A15. A kliknite na OK.
Pre druhý zoznam urobte to isté, ale priraďte názov - Table_2. A uveďte rozsah C2: C15.
Užitočná rada! Názvy rozsahov je možné rýchlejšie priradiť pomocou poľa pre názov. Nachádza sa vľavo od riadku vzorcov. Stačí vybrať rozsahy buniek a do poľa pre názov zadajte vhodný názov rozsahu a stlačte kláves Enter.
Teraz použijeme podmienené formátovanie na porovnanie dvoch zoznamov v programe Excel. Musíme dosiahnuť nasledujúci výsledok:
Pozície, ktoré sú v tabuľke_1, ale nie v tabuľke_2, sa zobrazia zelenou farbou. Polohy umiestnené v tabuľke_2, ktoré sa v tabuľke_1 nenachádzajú, budú zároveň zvýraznené modrou farbou.
Princíp porovnania údajov dvoch stĺpcov v programe Excel
Na definovanie podmienok formátovania buniek stĺpca sme použili funkciu COUNTIF. V tomto príklade táto funkcia kontroluje, koľkokrát sa hodnota druhého argumentu (napríklad A2) vyskytne v zozname prvého argumentu (napríklad Table_2). Ak je počet opakovaní \u003d 0, potom vzorec vráti hodnotu TRUE. V takom prípade je bunke priradený vlastný formát zadaný v možnostiach podmieneného formátovania. Odkaz v druhom argumente je relatívny, čo znamená, že sa postupne skontrolujú všetky bunky vybratého rozsahu (napríklad A2: A15). Druhý vzorec funguje podobným spôsobom. Rovnaký princíp je možné uplatniť na rôzne podobné úlohy. Napríklad na porovnanie dvoch rovnakých cien v programe Excel
Pri práci v programe Excel je pomerne častou úlohou porovnávať rôzne druhy zoznamov hodnôt. Na porovnanie rozsahov hodnôt všeobecne a osobitne ich stĺpcov je možné použiť štandardné nástroje programu Excel, ako sú podmienené formátovanie a funkcie. Takéto úlohy môžete navyše vyriešiť pomocou makier a doplnkov VBA pre Excel na ich základe.
Potreba všeobecne porovnávať rozsahy a najmä stĺpce vzniká pri aktualizácii rôznych správ, zoznamov a databáz generovaných v programe Excel, ktoré si vyžadujú zmeny v priebehu času.IN internetové siete bolo popísaných pomerne veľa príkladov, ktoré používajú rôzne funkcie (vzorce) a podmienené formátovanie na porovnanie hodnôt v stĺpcoch. Pre tých, pre ktorých tieto metódy nie sú z nejakého dôvodu vhodné, môžete použiť iný nástroj, ktorý vám umožní porovnať obidva stĺpce a akékoľvek rozsahy hodnôt pozostávajúce z ľubovoľného počtu riadkov a stĺpcov. Pomocou doplnku pre program Excel môžete porovnať ľubovoľné dva rozsahy, alebo lepšie povedané, porovnať každý prvok jedného rozsahu s každým prvkom druhého a vyhľadať zhody aj rozdiely medzi nimi podľa vášho výberu.
Doplnok porovnania programu Excel pre hodnoty v dvoch rozsahoch
Doplnok, o ktorom sa bude diskutovať nižšie, je navrhnutý na porovnanie hodnôt v dvoch rozsahoch pozostávajúcich z ľubovoľného počtu riadkov a stĺpcov. Tento doplnok používa postup na porovnanie každej položky v jednom rozsahu s každou položkou v inom rozsahu, na určenie, ktoré položky v týchto rozsahoch sú rovnaké a ktoré sa líšia, a vyplní bunky farbou určenou používateľom.
Ako vidíte z dialógového okna, všetko je celkom jednoduché, používateľ vyberie dva rozsahy, ktoré sa majú porovnať, určí, čo má hľadať, rôzne hodnoty alebo to isté, vyberie farbu a spustí program. Konečným výsledkom použitia doplnku sú vyplnené bunky špecifickou farbou, čo naznačuje rozdiely alebo prekrytia v hodnotách vybratých rozsahov.
Pri porovnávaní hodnôt v programe Excel existuje jedna výhrada. Čísla môžu byť formátované ako text, ktorý nie je vždy vizuálne identifikovateľný (). To znamená, že číslo v programe Excel môže byť buď číselná hodnota, alebo textová hodnota, a tieto dve hodnoty sa navzájom nerovnajú. Tento jav veľmi často spôsobuje rôzne druhy chýb. Na vylúčenie takýchto chýb sa používa možnosť „Porovnať čísla ako text“, ktorá je predvolene povolená. Používanie tejto možnosti vám umožňuje porovnávať nie číselné hodnoty, ale text prevedený z nich.
Doplnok umožňuje:
1. Jedným kliknutím myši otvorte dialógové okno makra priamo z panela nástrojov programu Excel;
2. nájdite prvky rozsahu # 1, ktoré nie sú v rozsahu # 2;
3. nájdite prvky rozsahu # 2, ktoré nie sú v rozsahu # 1;
4. nájdite prvky rozsahu # 1, ktoré sú v rozsahu # 2;
5. nájdite prvky rozsahu # 2, ktoré sú v rozsahu # 1;
6. vyberte jednu z deviatich farieb výplne pre bunky s požadovanými hodnotami;
7. rýchlo vyberte rozsahy pomocou možnosti „Obmedziť rozsahy“, pričom môžete vybrať celé riadky a stĺpce, redukcia vybratého rozsahu na použitý sa vykoná automaticky;
8. namiesto porovnávania číselných hodnôt použite porovnanie textových hodnôt pomocou možnosti „Porovnať čísla ako text“;
9. porovnávať hodnoty v bunkách rozsahu bez zohľadnenia medzier;
10. Porovnávajte hodnoty v bunkách rozsahu bez rozlišovania malých a veľkých písmen.
Ako porovnať dva stĺpce pomocou makra (doplnku) pre program Excel?
Porovnanie stĺpcov je zvláštny prípad porovnania ľubovoľných rozsahov. V rozsahu č. 1 a č. 2 vyberte dva stĺpce, môžete vybrať presne tie stĺpce a výberový rámček nepresúvať cez rozsahy s bunkami myšou (pre pohodlie je v predvolenom nastavení povolená možnosť „Obmedziť rozsahy“, ktorá , v prípade výberu celých stĺpcov alebo riadkov obmedzuje takýto výber pomocou rozsahu), vyberte požadovanú akciu na vyhľadanie rozdielov alebo zhôd, vyberte farbu výplne buniek a spustite program. Nižšie vidíte výsledok hľadania zhodných hodnôt v dvoch stĺpcoch.
Schopnosť porovnávať dva súbory údajov v programe Excel je často užitočná pre ľudí, ktorí spracúvajú veľké množstvo údajov a pracujú s obrovskými tabuľkami. Napríklad je možné použiť porovnanie pri správnosti zadávania údajov alebo zadávaní údajov do tabuľky včas. V článku nižšie je popísaných niekoľko techník na porovnanie dvoch stĺpcov s údajmi v programe Excel.
Použitie podmieneného operátora IF
Spôsob použitia podmieneného operátora IF sa líši v tom, že na porovnanie dvoch stĺpcov sa používa iba časť nevyhnutná na porovnanie, a nie celé pole. Ďalej sú uvedené kroky, ktoré je potrebné implementovať touto metódou:
Umiestnite oba stĺpce na porovnanie do stĺpcov A a B tabuľky.
Do bunky C2 zadajte nasledujúci vzorec \u003d IF (ISERROR (SEARCH (A2; $ B $ 2: $ B $ 11; 0)); ""; A2) a rozšírte ho na bunku C11. Tento vzorec postupne vyhľadáva každú položku zo stĺpca A v stĺpci B a vráti hodnotu položky, ak sa nachádza v stĺpci B.
Pomocou substitučného vzorca VLOOKUP
Princíp vzorca je podobný predchádzajúcej metóde, rozdiel spočíva v tom, namiesto HĽADAŤ. Charakteristickým rysom tejto metódy je tiež schopnosť porovnávať dve horizontálne polia pomocou vzorca HLP.
Ak chcete porovnať dva stĺpce s údajmi v stĺpcoch A a B (podobne ako v predchádzajúcej metóde), zadajte do bunky C2 nasledujúci vzorec \u003d VLOOKUP (A2; $ B $ 2: $ B $ 11; 1; 0) a presuňte ho do bunka C11.
Tento vzorec sleduje každý prvok zo základného poľa v porovnávanom poli a vráti jeho hodnotu, ak sa našiel v stĺpci B. V opačnom prípade program Excel vráti chybu # N / A.
Používanie makra VBA
Použitie makier na porovnanie dvoch stĺpcov môže zjednotiť proces a skrátiť čas potrebný na prípravu údajov. Rozhodnutie, ktorý výsledok porovnania by sa mal zobraziť, závisí výlučne od vašej fantázie a zručností pri používaní makier. Ďalej je uvedená technika zverejnená na oficiálnych webových stránkach spoločnosti Microsoft.
1 | Sub Find_Matches () |
V tomto kóde je premennej CompareRange priradený rozsah s porovnávaným poľom. Potom spustí slučku, ktorá prechádza každou položkou vo vybranom rozsahu a porovnáva ju s každou položkou v porovnávanom rozsahu. Ak sa našli prvky s rovnakými hodnotami, makro zadá hodnotu prvku do stĺpca C.
Ak chcete použiť makro, vráťte sa na pracovný hárok, vyberte hlavný rozsah (v našom prípade sú to bunky A1: A11) a stlačte kombináciu klávesov Alt + F8. V zobrazenom dialógovom okne vyberte makro Nájsť_zápasya kliknite na tlačidlo spustenia.
Po vykonaní makra by mal byť výsledok nasledovný:
Pomocou doplnku Inquire
Výsledok
Pozreli sme sa teda na niekoľko spôsobov porovnania údajov v programe Excel, ktoré vám pomôžu vyriešiť niektoré analytické problémy a uľahčia hľadanie duplicitných (alebo jedinečných) hodnôt.
Pri porovnaní niekoľkých porovnateľných položiek v Tabuľky programu Excel, údaje sú často usporiadané do stĺpcov, takže je vhodné porovnávať vlastnosti týchto objektov po riadkoch. Napríklad modely automobilov, telefóny, experimentálne a kontrolné skupiny, množstvo obchodov maloobchodného reťazca atď. Pri veľkom počte liniek nemôže byť vizuálna analýza spoľahlivá. Funkcie VLOOKUP, INDEX, SEARCH (VLOOKUP, INDEX, MATCH) sú vhodné na porovnanie údajov o bunkách a neposkytujú celkový obraz. Ako zistíte, ako sú si stĺpce celkovo podobné? Sú stĺpce identické?
Doplnok Mapové stĺpce vám umožňuje mapovať stĺpce a vidieť celkový obraz:
- Porovnajte navzájom dva alebo viac stĺpcov
- Porovnajte stĺpce s referenčnými hodnotami
- Vypočítajte presné percento zhody
- Výsledok prezentujte vo vizuálnej kontingenčnej tabuľke
Jazyk videa: angličtina. Titulky: ruské, anglické. (Pozor: video sa nemusí odrážať najnovšie aktualizácie... Postupujte podľa pokynov uvedených nižšie.)
Pridajte do Excelu 2016, 2013, 2010, 2007 možnosť „Zhodiť stĺpce“
Vhodné pre: Microsoft Excel 2016 - 2007, desktop Office 365 (32-bitový a 64-bitový).
Ako pracovať s doplnkom:
Ako porovnávať dva alebo viac stĺpcov navzájom a vypočítať percento zhody
Zvážte príklad vývoja produktu. Predpokladajme, že musíte navzájom porovnať niekoľko hotových prototypov a zistiť, aké sú podobné, odlišné a možno dokonca totožné.
- Kliknite na tlačidlo Mapovať stĺpce na paneli nástrojov XLTools\u003e Vybrať navzájom mapovať stĺpce.
- Kliknite na tlačidlo OK\u003e
Rada:
Prosím vyber súhrnná tabuľka výsledok\u003e Kliknite na ikonu Expresná analýza\u003e Použiť „Farebná škála“.
Čítanie výsledku: prototypy typu 1 a typu 3 sú takmer totožné, miera zhody 99% znamená, že 99% ich parametrov v riadkoch je rovnakých. Typ 2 a typ 4 sú si najmenej podobné - ich parametre sa zhodujú iba o 30%.
Ako porovnávať stĺpce s referenčnými hodnotami a vypočítať mieru zhody
Zvážte príklad vývoja produktu. Predpokladajme, že musíte porovnať niekoľko hotových prototypov s určitým cieľovým štandardom a tiež vypočítať mieru, do akej prototypy vyhovujú týmto štandardom.
- Vyberte stĺpce na porovnanie.
Napríklad stĺpce s údajmi o prototype. - Kliknite na tlačidlo Mapovať stĺpce na paneli nástrojov XLTools.
- Vyberte Mapovať na rozsah referenčných stĺpcov\u003e Vyberte referenčné stĺpce.
Napríklad stĺpce so štandardmi. - Ak je to tak, začiarknite políčko „Stĺpce obsahujú názvy“.
- Začiarknutím možnosti „Zobraziť percento zhody“ zobrazíte percento zhody ako percento.
V opačnom prípade sa výsledok zobrazí ako 1 (úplná zhoda) alebo 0 (žiadna zhoda). - Uveďte, kam sa má výsledok umiestniť: na nový hárok alebo na existujúci hárok.
- Kliknite na OK\u003e Dokončiť, výsledok sa zobrazí v kontingenčnej tabuľke.
Rada: Aby ste uľahčili interpretáciu výsledku, použite na ňu podmienené formátovanie:
Vyberte kontingenčnú tabuľku výsledku\u003e Kliknite na ikonu Rýchla analýza\u003e Použiť „Farebnú škálu“.
Čítanie výsledku: Prototyp typu 2 je na 99% kompatibilný so štandardom 2, t.j. 99% ich parametrov v reťazcoch je rovnakých. Produkt 5 je najbližšie k štandardu 3 - 96% ich parametrov je identických. Produkt 4 zároveň ani zďaleka nespĺňa žiadny z troch štandardov. Teraz môžeme konštatovať, ako veľmi sa každý z prototypov líši od cieľových referenčných hodnôt.
S akými úlohami pomôže doplnok Match Columns?
Doplnok prehľadáva bunky riadok po riadku a počíta percento rovnakých hodnôt v stĺpcoch. XLTools Match Columns nie je vhodný na bežné porovnanie hodnôt v bunkách - nie je určený na hľadanie duplikátov alebo jedinečných hodnôt.
Doplnok Mapové stĺpce má iný účel. Ju hlavná úloha - zistite, ako sú súbory údajov (stĺpce) vo všeobecnosti podobné alebo odlišné. Doplnok pomáha pri analýze veľkého množstva údajov, keď sa napríklad potrebujete pozrieť na širšiu úroveň makra. odpovedaj na nasledujúce otázky:
- Aká podobná je výkonnosť experimentálnych skupín
- Aké podobné sú výsledky experimentálnej a kontrolnej skupiny
- Nakoľko podobné / odlišné je niekoľko výrobkov rovnakej kategórie
- Ako blízko sú KPI zamestnancov k plánovaným ukazovateľom?
- Aké podobné sú ukazovatele viacerých maloobchodných predajní atď.