Porovnajte dva rôzne stĺpce programu Excel. Ako porovnať dva stĺpce v Exceli - techniky porovnávania údajov v Exceli
Často je úlohou porovnať dva zoznamy prvkov. Robiť to ručne je príliš únavné a okrem toho nemožno vylúčiť možnosť chýb. Excel uľahčuje túto operáciu. AT tejto Rady opisuje metódu využívajúcu podmienené formátovanie.
Na obr. 164.1 je príkladom dvoch viacstĺpcových zoznamov mien. Použitím podmieneného formátovania môžu byť rozdiely v zoznamoch zreteľnejšie. Tieto príklady zoznamov obsahujú text, ale táto metóda pracuje aj s číselnými údajmi.
Prvý zoznam je A2:B31, tento rozsah sa nazýva starý zoznam. Druhý zoznam je D2:E31 , rozsah sa volá nový zoznam. Rozsahy boli pomenované pomocou príkazu Vzorce Definované názvy Priraďte názov. Pomenovanie rozsahov je voliteľné, ale uľahčuje to prácu s nimi.
Začnime pridaním podmieneného formátovania do starého zoznamu.
- Vyberte bunky rozsahu starý zoznam.
- Vyberte .
- V okne Vytvorte pravidlo formátovania vyberte položku s názvom Použite vzorec
- Do poľa okna zadajte tento vzorec (obr. 164.2): =COUNTIF(NewList;A2)=0 .
- Kliknite na tlačidlo Formátovať a nastavte formátovanie, ktoré sa má použiť, keď je podmienka pravdivá. Najlepšie je zvoliť rôzne farby výplne.
- Kliknite na tlačidlo OK.
Bunky v rozsahu nový zoznam použite podobný vzorec podmieneného formátovania.
- Vyberte bunky rozsahu nový zoznam.
- Vyberte Domov pravidlo vytvorenia podmieneného formátovania otvoríte dialógové okno Vytvorte pravidlo formátovania.
- V okne Vytvorte pravidlo formátovanie vyberte položku Použite vzorec na definovanie buniek, ktoré sa majú formátovať.
- Do poľa okna zadajte tento vzorec: =COUNTIF(StarýZoznam;D2)=0 .
- Kliknite na tlačidlo Formátovať a nastavte formátovanie, ktoré sa použije, keď je podmienka pravdivá (iná farba výplne).
- Kliknite na tlačidlo OK.
V dôsledku toho budú zvýraznené mená, ktoré sú v starom zozname, ale nie sú v novom (obr. 164.3). Okrem toho sú zvýraznené aj mená v novom zozname, ktoré nie sú v starom zozname, ale inou farbou. Mená, ktoré sa objavia v oboch zoznamoch, nie sú zvýraznené.
Obidva vzorce podmieneného formátovania používajú túto funkciu COUNTIF. Počíta, koľkokrát sa určitá hodnota objaví v rozsahu. Ak vzorec vráti 0, znamená to, že prvok nie je v rozsahu. Touto cestou, podmienené formátovanie sa pustí do práce a farba pozadia bunky sa zmení.
Pri práci v Exceli sa človek často stretáva s úlohou, ktorá zahŕňa porovnávanie rôznych druhov zoznamov hodnôt. Na porovnanie rozsahov hodnôt vo všeobecnosti a najmä stĺpcov hodnôt je možné použiť nasledovné: štandardné prostriedky Excel ako podmienené formátovanie a funkcie. Okrem toho môžete takéto problémy vyriešiť pomocou makier VBA a doplnkov Excelu, ktoré sú na nich založené.
Potreba porovnávať rozsahy vo všeobecnosti a stĺpce zvlášť vzniká pri aktualizácii rôznych zostáv, zoznamov a databáz generovaných v Exceli a vyžadujúcich si zmeny v priebehu času. Na internete je opísaných pomerne veľa príkladov, ktoré slúžia na porovnávanie hodnôt v stĺpcoch rôzne funkcie(vzorce) a podmienené formátovanie. Pre tých, ktorí z nejakého dôvodu nie sú vhodní pre takéto metódy, môžete použiť iný nástroj, ktorý vám umožní porovnať oba stĺpce a ľubovoľný rozsah hodnôt pozostávajúci z ľubovoľného počtu riadkov a stĺpcov. Pomocou doplnku pre Excel môžete porovnať ľubovoľné dva rozsahy alebo skôr porovnať každý prvok jedného rozsahu s každým prvkom druhého a nájsť zhody aj rozdiely medzi nimi podľa vlastného výberu.
Doplnok na porovnávanie hodnôt v dvoch rozsahoch Excelu
Doplnok, o ktorom sa bude diskutovať nižšie, je určený 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 porovnávania každého prvku jedného rozsahu s každým prvkom iného rozsahu, určuje, ktoré prvky týchto rozsahov sa zhodujú a ktoré sa líšia, a vypĺňa bunky farbou, ktorú definuje používateľ.
Ako vidíte z dialógového okna, všetko je celkom jednoduché, používateľ vyberie dva porovnávané rozsahy, určí, čo má hľadať, rôzne hodnoty alebo zodpovedajúce, vyberie farbu a spustí program. konečný výsledok Použitie doplnkov sú bunky vyplnené určitou farbou, čo naznačuje rozdiely alebo zhody v hodnotách vybraných rozsahov.
Pri porovnávaní hodnôt v Exceli 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 Exceli môže byť číselnou hodnotou aj textovou hodnotou a tieto dve hodnoty sa navzájom nerovnajú. Tento jav je veľmi často príčinou rôznych druhov chýb. Na vylúčenie takýchto chýb sa používa možnosť „Porovnať čísla ako text“, ktorá je predvolene povolená. Použitie tejto možnosti vám umožňuje porovnávať nie číselné hodnoty, ale textové hodnoty z nich prevedené.
Doplnok umožňuje:
1. Jedným kliknutím myši vyvolajte dialógové okno makra priamo z panela nástrojov Excelu;
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 buniek s požadovanými hodnotami;
7. rýchly výber rozsahov pomocou možnosti "Obmedziť rozsahy", pričom môžete vybrať celé riadky a stĺpce, redukcia vybraného rozsahu na použitý sa vykoná automaticky;
8. namiesto porovnávania číselných hodnôt použite porovnávanie textové hodnoty pomocou možnosti "Porovnať čísla ako text";
9. porovnajte hodnoty v bunkách rozsahu bez ohľadu na ďalšie medzery;
10. porovnajte hodnoty v bunkách rozsahu bez ohľadu na veľkosť písmen.
Ako porovnať dva stĺpce pomocou makra (doplnku) pre Excel?
Stĺpcové porovnanie je špeciálny prípad porovnávania ľubovoľného rozsahu. V rozsahoch č. 1 a č. 2 vyberieme dva stĺpce a vy môžete vybrať presne tie stĺpce, a nie ťahať výberový rám cez rozsahy s bunkami pomocou myši (pre pohodlie je možnosť „Obmedziť rozsahy“ aktivovaná default, ktorý, ak sú stĺpce alebo riadky vybraté úplne, obmedzuje takýto výber použitým rozsahom), vyberte požadovaná akcia ak chcete hľadať rozdiely alebo zhody, vyberte farbu výplne bunky a spustite program. Nižšie je uvedený výsledok vyhľadávania zodpovedajúcich hodnôt v dvoch stĺpcoch.
Ak pracujete s veľkými tabuľkovými dokumentmi (veľa údajov/stĺpcov), je veľmi ťažké kontrolovať presnosť/relevanciu všetkých informácií. Preto je veľmi často potrebné analyzovať dva alebo viac stĺpcov v dokumente programu Excel, aby sa zistila duplicita. A ak používateľ nemá informácie o všetkých funkciách programu, môže mať logicky otázku: ako porovnať dva stĺpce v programe Excel?
Odpoveď už dlho vymýšľali vývojári tohto programu, ktorí doň spočiatku vložili príkazy, ktoré pomáhajú porovnávať informácie. Vo všeobecnosti, ak pôjdete do hĺbky tejto problematiky, môžete nájsť asi tucet rôznych spôsobov vrátane písania jednotlivých makier a vzorcov. Ale prax ukazuje: stačí poznať tri alebo štyri spoľahlivé spôsoby, ako sa vyrovnať s novými potrebami porovnávania.
Ako porovnať dva stĺpce v Exceli pre zápasy
Údaje v Exceli sa zvyčajne porovnávajú medzi riadkami, medzi stĺpcami, pričom hodnota je uvedená ako referenčná hodnota. Ak je potrebné porovnať stĺpce, môžete použiť vstavanú funkciu, konkrétne akcie Zhoda a Ak. A všetko, čo potrebujete, je Excel nie skôr ako v siedmom roku "vydania".
Začneme funkciou „Náhoda". Napríklad porovnávané údaje sú v stĺpcoch s adresami C3 a B3. Výsledok porovnania musí byť umiestnený v bunke, napríklad D3. Klikneme na túto bunku, vstúpime do adresára ponuky „vzorce“, nájdeme riadok „knižnica funkcií“, otvoríme funkcie umiestnené v rozbaľovacom zozname, nájdeme slovo „text“ a klikneme na „Náhoda“.
O chvíľu uvidíte na displeji nový formulár, kde budú len dve polia: „text jedna“, „text dva“. Musíte ich vyplniť iba adresy porovnávaných stĺpcov (C3, B3) a potom kliknúť na obvyklé tlačidlo „OK“. V dôsledku toho uvidíte výsledok so slovami „True“ / „False“. V zásade nie je nič zložité ani pre začínajúceho používateľa! Ale to zďaleka nie je jediná metóda. Poďme si rozobrať funkciu "If".
Schopnosť porovnať dva stĺpce v Exceli pre zhody pomocou "If" umožňuje zadať hodnoty, ktoré sa po operácii zobrazia na konci. Kurzor sa umiestni do bunky, v ktorej sa bude zadávať, otvorí sa adresár ponuky „knižnica funkcií“, v rozbaľovacom zozname sa vyberie riadok „logický“ a na prvej pozícii v ňom bude „ Ak" príkaz. Vyberáme to.
Ďalej sa formuje odôvodnená náplň. "Logic_expression" je formulácia samotnej funkcie. V našom prípade ide o porovnanie dvoch stĺpcov, takže zadáme „B3 \u003d C3“ (alebo adresy vašich stĺpcov). Ďalšie polia "value_if true", "value_if_false". Tu by ste mali zadať údaje (štítky/slová/čísla), ktoré by mali zodpovedať pozitívnemu/negatívnemu výsledku. Po vyplnení kliknite ako obvykle na „ok“. Zoznámime sa s výsledkom.
Ak chcete vykonať analýzu dvoch stĺpcov riadok po riadku, do tretieho stĺpca vložíme ľubovoľnú funkciu, o ktorej sme hovorili vyššie („ak“ alebo „náhoda“). Jeho pôsobenie je rozšírené na celú výšku vyplnených stĺpcov. Ďalej vyberte tretí stĺpec, kliknite na kartu „hlavná“ a v zobrazenej skupine vyhľadajte slovo „štýly“. Otvoria sa „Pravidlá výberu stĺpcov/buniek“.
V nich musíte vybrať príkaz „rovná sa“, potom kliknúť na prvý stĺpec a stlačiť „Enter“. Výsledkom je, že sa „zafarbia“ stĺpce, v ktorých sa nachádzajú zodpovedajúce výsledky. A okamžite uvidíte informácie, ktoré potrebujete. Ďalej, v analýze témy "ako porovnať hodnoty dvoch stĺpcov v Exceli", prejdime k metóde, ako je podmienené formátovanie v Exceli.
Excel: podmienené formátovanie
Formátovanie podmieneného typu vám umožní nielen porovnať dva rôzne stĺpce/bunky/riadky, ale aj zvýrazniť v nich rôzne údaje danou farbou (červenou). To znamená, že nehľadáme náhody, ale rozdiely. Aby sme to dosiahli, správame sa takto. Vyberieme potrebné stĺpce bez toho, aby sme sa dotkli ich názvov, prejdeme do adresára „hlavného“ menu, v ktorom hľadáme podsekciu „štýly“.
Bude obsahovať riadok „podmienené formátovanie“. Kliknutím naň dostaneme zoznam, kde potrebujeme funkciu „vytvoriť pravidlo“. Ďalší krok: v riadku „formát“ musíte jazdiť vo vzorci = $ A2<>B2 USD. Tento vzorec pomôže Excelu pochopiť, čo presne potrebujeme, konkrétne vyfarbiť červenou farbou všetky hodnoty stĺpca A, ktoré sa nerovnajú hodnotám stĺpca B. O niečo zložitejší spôsob použitia vzorcov súvisí k účasti štruktúr ako HLOOKUP / VLOOKUP. Tieto vzorce odkazujú na horizontálne/vertikálne vyhľadávanie hodnôt. Zvážme túto metódu podrobnejšie.
HLOOKUP a VLOOKUP
Tieto dva vzorce umožňujú vyhľadávať údaje horizontálne/vertikálne. To znamená, že H je horizontálne a V je vertikálne. Ak sa porovnávané údaje nachádzajú v ľavom stĺpci vzhľadom na údaj, v ktorom sa nachádzajú porovnávané hodnoty, použite konštrukt VLOOKUP. Ak sú však údaje na porovnanie vodorovne v hornej časti tabuľky zo stĺpca, kde sú uvedené referenčné hodnoty, použijeme konštrukciu HLOOKUP.
Ak chcete pochopiť, ako vertikálne porovnávať údaje v dvoch stĺpcoch programu Excel, mali by ste použiť toto úplný vzorec: hodnota_vyhľadania, pole_tabuľky, číslo indexu stĺpca, vyhľadávanie_rozsahu.
Hodnota, ktorá má byť nájdená, je označená ako "lookup_value". Vyhľadávacie stĺpce sú riadené ako "pole tabuľky". Číslo stĺpca by malo byť špecifikované ako "sol_index_num". Navyše je to stĺpec, ktorého hodnota sa zhodovala a ktorý je potrebné vrátiť/opraviť. Príkaz "range lookup" tu funguje ako dodatočný. Môže naznačovať, či je potrebné určiť presnú alebo približnú hodnotu.
Ak tento príkaz nie je zadaný, vrátia sa hodnoty pre oba typy. Vzorec HLOOKUP vyzerá takto: hodnota_vyhľadania,pole_tabuľky,číslo_indexu_riadku,vyhľadanie_rozsahu. Práca s ním je takmer totožná s tou opísanou vyššie. Je pravda, že tu existuje výnimka. Toto je index reťazca, ktorý určuje reťazec, ktorého hodnoty sa majú vrátiť. Ak sa naučíte jasne aplikovať všetky vyššie uvedené metódy, bude jasné: neexistuje pohodlnejší a univerzálnejší program na prácu s veľkým množstvom údajov. odlišné typy než Excel. Porovnanie dvoch stĺpcov v exceli je však len polovica práce. So získanými hodnotami je predsa potrebné urobiť niečo iné. To znamená, že nájdené zhody treba ešte nejako spracovať.
Ako zaobchádzať s duplicitnými hodnotami
Takže v prvom, predpokladajme, stĺpci sa nachádzajú čísla, ktoré sa úplne opakujú v druhom stĺpci. Je jasné, že manuálne opravovanie opakovaní je nereálna práca, ktorá zaberá veľa drahocenného času. Preto by ste mali použiť hotovú techniku automatickej korekcie.
Aby to fungovalo, musíte najprv pomenovať stĺpce, ak neexistujú. Upravíme kurzor na prvý riadok, klikneme pravým tlačidlom myši, v zobrazenej ponuke vyberieme „vložiť“. Povedzme, že hlavičky sú „meno“ a „duplikát“. Ďalej potrebujete adresár "dátum" v ňom - "filter". Kliknite na malú šípku vedľa „duplikátu“ a odstráňte všetky „vtáky“ zo zoznamu. Teraz kliknite na OK a zviditeľnia sa duplicitné hodnoty stĺpca A.
Ďalej tieto bunky vyberieme, pretože sme potrebovali nielen porovnať text v dvoch stĺpcoch v exceli, ale aj odstrániť duplikáty. Vybraté, kliknúť pravým tlačidlom myši, vybrať „vymazať riadok“, kliknúť na „ok“ a získať tabuľku bez zodpovedajúcich hodnôt. Metóda funguje, ak sú stĺpce na tej istej stránke, to znamená, že susedia.
Preto sme analyzovali niekoľko spôsobov, ako porovnať dva stĺpce v Exceli. Zámerne som vám neukázal screenshoty, pretože by ste sa v nich zmiatli.
ALE pripravil som skvelé video jedného z najpopulárnejších a jednoduchými spôsobmi porovnať dva stĺpce v dokumente a teraz vám navrhujem, aby ste sa s nimi oboznámili, aby ste si upevnili preberaný materiál.
Ak bol pre vás článok stále užitočný, zdieľajte ho na sociálnych sieťach. siete alebo ohodnoťte kliknutím na počet hviezdičiek, ktoré uznáte za vhodné. Ďakujem, na dnes je to všetko, do skorého videnia.
© Alexander Ivanov.
Možnosť porovnať dve sady údajov v Exceli je často užitočná pre ľudí, ktorí spracúvajú veľké množstvo údajov a pracujú s obrovskými tabuľkami. Porovnanie sa dá využiť napríklad pri správnosti zadávania údajov alebo zadávaní údajov do tabuľky na čas. Nasledujúci článok popisuje niekoľko trikov na porovnanie dvoch stĺpcov s údajmi v Exceli.
Použitie podmieneného príkazu 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ť potrebná na porovnanie a nie celé pole. Kroky na implementáciu tejto metódy sú popísané nižšie:
Umiestnite oba stĺpce na porovnanie do stĺpcov A a B pracovného hárka.
Do bunky C2 zadajte nasledujúci vzorec =IF(ISERROR(MATCH(A2,$B$2:$B$11,0)),"";A2) a presuňte ho do bunky C11. Tento vzorec postupne hľadá prítomnosť každého prvku zo stĺpca A v stĺpci B a vráti hodnotu prvku, ak sa našiel v stĺpci B.
Použitie substitučného vzorca VLOOKUP
Princíp fungovania vzorca je podobný predchádzajúcej metóde, rozdiel spočíva v, namiesto MATCH. Výrazná vlastnosť tejto metódy je aj možnosť porovnať dve horizontálne polia pomocou vzorca HLOOKUP.
Ak chcete porovnať dva stĺpce s údajmi v stĺpcoch A a B (podobne predchádzajúci spôsob), zadajte nasledujúci vzorec =VLOOKUP(A2,$B$2:$B$11,1,0) do bunky C2 a presuňte ho do bunky C11.
Tento vzorec sa pozerá na každý prvok z hlavné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 Excel vráti chybu #N/A.
Použitie makra VBA
Použitie makier na porovnanie dvoch stĺpcov umožňuje zjednotiť proces a skrátiť čas prípravy údajov. Rozhodnutie, ktorý výsledok porovnania sa má zobraziť, je výlučne na vašej fantázii a makro zručnostiach. Nižšie je uvedená technika zverejnená na oficiálnej webovej stránke spoločnosti Microsoft.
1 | Sub Find_Matches() |
V tomto kóde je premennej CompareRange priradený rozsah s porovnávaným poľom. Potom spustí cyklus, ktorý iteruje cez každý prvok vo vybranom rozsahu a porovnáva ho s každým prvkom 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 do pracovného hárka, vyberte hlavný rozsah (v našom prípade sú to bunky A1:A11), stlačte Alt + F8. V zobrazenom dialógovom okne vyberte makro Nájsť_zápasy a kliknite na tlačidlo spustiť.
Po vykonaní makra by mal výsledok vyzerať takto:
Pomocou doplnku Inquire
Výsledok
Pozreli sme sa teda na niekoľko spôsobov porovnávania údajov v Exceli, ktoré vám pomôžu vyriešiť niektoré analytické problémy a uľahčia prácu pri hľadaní duplicitných (alebo jedinečných) hodnôt.
