Porovnanie tabuliek v programe Excel

Pomerne často používatelia programu Excel majú úlohu porovnávania dvoch tabuliek alebo zoznamov na identifikáciu rozdielov v nich alebo chýbajúce položky. Každý užívateľ sa presvedčí s touto úlohou vlastným spôsobom, ale najčastejšie je pomerne veľké množstvo času vynaložené na riešenie špecifikovanej otázky, pretože nie všetky prístupy k tomuto problému sú racionálne. Zároveň existuje niekoľko osvedčených algoritmov akcií, ktoré umožnia porovnanie zoznamov alebo tabuľkových polí v pomerne krátkom čase s minimálnym značným úsilím. Uvažujme podrobnosti o týchto možnostiach.

Rovnakým spôsobom môžete údaje porovnať v tabuľkách, ktoré sa nachádzajú na rôznych listoch. Ale v tomto prípade je žiaduce, aby čiary v nich boli očíslované. Zvyšok porovnávacieho postupu je takmer presne rovnaký, ako je opísané vyššie, okrem toho, že pri výrobe vzorca je potrebné prepínať medzi listy. V našom prípade bude mať výraz nasledujúci formulár:

= B2 = zoznam2! B2

To znamená, že v prednej časti súradníc údajov, ktoré sa nachádzajú na iných listoch, ako tam, kde sa zobrazí výsledok porovnávania, je uvedené číslo listu a výkričník.

Metóda 2: Výber skupín buniek

Porovnanie sa môže uskutočniť pomocou nástroja na separáciu skupín buniek. S ním môžete porovnať aj synchronizované a objednané zoznamy. Okrem toho, v tomto prípade musia byť zoznamy umiestnené vedľa seba na jednom liste.

1. Vyberte porovné polia. Prejdite na kartu "Domov". Ďalej kliknite na ikonu "Nájsť a zvoľte", ktorá sa nachádza na páske v paneli s nástrojmi na úpravu. K dispozícii je zoznam, v ktorom by ste mali vybrať pozíciu "Výber skupiny buniek ...".

   

   Okrem toho, výber skupiny buniek môže byť dosiahnutý iným spôsobom. Táto možnosť bude užitočná pre tých používateľov, ktorí majú verziu programu skôr ako Excel 2007, pretože metóda cez tlačidlo "Nájsť a zvoľte" nepodporuje tieto aplikácie. Vyberte položku Polia, ktoré chcú porovnať a kliknite na tlačidlo F5.

2. Aktivuje sa malé okno prechodu. Kliknite na tlačidlo "Zvýraznite ..." v ľavom dolnom rohu.



3. Potom sa spustí čokoľvek dve z vyššie uvedených možností, ktoré ste zvolení, sa spustí okno výberu bunkových skupín. Nainštalujte prepínač na polohu "Highlight On Line". Kliknite na tlačidlo "OK".



4. Ako môžeme vidieť, potom, nekonzistentné hodnoty riadkov budú zvýraznené iným odtieňom. Okrem toho, ako možno posudzovať z obsahu vzorca line, program urobí aktívny jeden z buniek v uvedených neexistovaných tratiach.

Metóda 3: Podmienené formátovanie

Porovnanie môžete vykonať použitím metódy podmieneného formátovania. Rovnako ako v predchádzajúcom spôsobe, porovnávacie oblasti by mali byť na jednom pracovnom liste programu Excel a sú navzájom synchronizované.

1. V prvom rade si vyberieme, čo tabuľková oblasť bude považovaná za hlavnú, a v tom, čo hľadať rozdiel. Posledný poďme urobiť v druhej tabuľke. Preto prideľujeme zoznam pracovníkov v ňom. Presunutie na kartu "HOME", kliknite na tlačidlo "Podmienené formátovanie", ktoré má umiestnenie na páske v bloku "štýly". Z rozbaľovacieho zoznamu prejdite na položku "Riadenie pravidiel".



2. Aktivuje sa okno Dispatcher Pravidlá Manager. Kliknutím na tlačidlo "Vytvoriť pravidlo".



3. V spustenom okne vyberieme polohu "Použiť vzorec". V poli "Formátové" pole napíšte vzorec obsahujúci adresy prvých buniek rozsahov porovnávacích stĺpcov, oddelené znakom "nie je rovnaké". Tesne pred týmto výrazom bude tento čas postaviť znak "=". Okrem toho, na všetky súradnice stĺpcov v tomto vzorci, musíte použiť absolútne adresovanie. Aby sme to urobili, prideľujeme vzorca s kurzorom a trikrát kliknite na tlačidlo F4. Ako vidíme, znak dolára sa objavil v blízkosti všetkých adries stĺpca, čo znamená otáčanie odkazov na absolútne. V prípade nášho konkrétneho prípadu vzorec bude mať nasledujúci formulár:

   = $ A2 $ D2

   Tento výraz zaznamenávame vo vyššie uvedenom poli. Potom klikneme na tlačidlo "Formát ...".



4. Okno "Cell Format" je aktivovaný. Prejdeme na kartu "Fill". Tu v zozname farieb zastaviť voľbu na farbe, ktorú chceme maľovať tie prvky, kde sa údaje nezhodujú. Kliknite na tlačidlo "OK".



5. Vrátenie sa do okna Formátovania, kliknite na tlačidlo "OK".



6. Po automatickom prechode na okno "Pravidlá Manager" kliknite na tlačidlo "OK" av ňom.



7. Teraz v druhej tabuľke, prvky, ktoré majú údaje, ktoré sú nekonzistentné s zodpovedajúcimi hodnotami prvej tabuľky, zvýrazní vo vybranej farbe.

Tam je ďalší spôsob, ako použiť podmienené formátovanie na vykonanie úlohy. Rovnako ako predchádzajúce možnosti, že vyžaduje umiestnenie oboch porovnávaných oblastí na jeden list, ale na rozdiel od predtým popísaných spôsobov, bude stav synchronizácie alebo triedenie dát nie je povinné, ktorý odlišuje túto možnosť z predtým popísaného.

1. Vyrábame výber oblastí, ktoré je potrebné porovnávať.



2. Vykonávame prechod na záložku s názvom "Home". Klikneme na tlačidlo "Podmienené formátovanie". V aktívnom zozname vyberte pozíciu "Pravidlá pre prideľovanie buniek". V ďalšej ponuke, aby sa pozície "Opakujúce sa hodnoty".



3. Duplicitné hodnoty sú začali. Ak ste všetko urobili správne, potom v tomto okne zostáva len kliknúť na tlačidlo "OK". Aj keď, ak si prajete, do príslušného poľa v tomto okne si môžete vybrať inú farbu výberu.



4. Potom, čo vyrábame určenú akciu, budú všetky opakujúce sa prvky sa zvýrazní vo zvolenej farbe. Tieto prvky, ktoré nesplývajú zostanú lakované v ich pôvodnú farbu (štandardne biela). Takto si môžete okamžite vidieť vizuálne vidieť, aký je rozdiel medzi poľami.

Ak chcete, môžete naopak, maľovať nesúrodé prvky a indikátory, ktoré sa zhodujú, odísť s výplňou z predchádzajúcej farby. V rovnakej dobe, algoritmus akcie je takmer rovnaký, ale v nastavení duplicitných hodnôt v prvom poli, namiesto parametra "opakoval" zvoľte parameter "jedinečný". Potom kliknite na tlačidlo "OK".

Tak, to je práve tieto ukazovatele, ktoré sa neprekrývajú.

Poučenie: Podmienené formátovanie v Exceli

Metóda 4: Komplexné Vzorec

Tiež porovnaní dát pomocou zložitého vzorca, ktorého základom je funkcia prístroja. Pomocou tohto nástroja si môžete vypočítať, koľko každý prvok z vybraného stĺpci druhej tabuľky sa opakuje do tej prvej.

Tieto rady operátor odkazuje na štatistickú skupinu funkcií. Jej úlohou je spočítať počet buniek, hodnoty vo ktoré spĺňajú zadané podmienky. Syntax tohto operátora má tento druh:

= Poriadky (rozsah; kritérium)

Argument "Rozsah" je adresa pole, ktorý vypočítava zjednotenej hodnoty.

Argument "Kritériá" udáva stav náhoda. V našom prípade to bude súradnicami špecifických buniek prvého tabuľkové oblasti.

1. My prideliť prvý prvok dodatočného stĺpce, v ktorom bude vypočítaná počet zápasov. Potom kliknite na ikonu "funkcie Insert".



2. Funkcia Spustí sa sprievodca. Prejsť do kategórie "štatistického". Nájdite v zozname meno "countecles". Po jej výbere, kliknite na tlačidlo "OK".



3. Okno argumenty prevádzkovateľ rozbehol. Ako vidíme, názvy polí v tomto okne zodpovedajú názvy argumentov.

   Nainštalovať kurzor do poľa "Rozsah". Po tom, držaním ľavého tlačidla myši, prideliť všetky hodnoty stĺpce s názvami druhej tabuľky. Ako môžete vidieť, že poloha okamžite spadnú do príslušného poľa. Ale pre naše účely, táto adresa by mala byť vykonaná absolútna. K tomu je upozorniť na súradnice v poli a kliknite na kľúč F4.

   Ako môžete vidieť, je referenčná vzal absolútna formu, ktorá sa vyznačuje prítomnosťou dolára.

   Potom choď do poľa "Kritérium" inštaláciou kurzor. Kliknite na prvý prvok s poslednými menami v prvom tabuľkovej radu. V tomto prípade nechajte odkaz relatívna. Potom, čo sa zobrazí v poli, môžete kliknúť na tlačidlo "OK".



4. Výsledkom je odovzdávaný do plechového prvku. Je rovný počtu "1". To znamená, že v zozname mien druhej tabuľky, názov "Grinev V. P.", ktorý je prvý v zozname prvej tabuľky polia, dochádza raz.



5. Teraz musíme vytvoriť podobný výsledok a pre všetky ostatné prvky v prvej tabuľke. Ak to chcete, budem vykonávať kopírovanie pomocou plniacej značky, ako sme už predtým. Dali sme kurzor na spodnej pravej časti listu prvku, ktorý obsahuje funkciu merače, a potom prevedením na plniacu markeru, upnutie ľavé tlačidlo myši a ťahať kurzorom dole.



6. Ako môžeme vidieť, program urobil výpočet náhod, porovnávajúcej každú bunku prvej tabuľky s údajmi, ktoré sú umiestnené v druhom rozsahu tabuľky. V štyroch prípadoch bol výsledok "1", a v dvoch prípadoch - "0". To znamená, že program nemohol nájsť dve hodnoty v druhej tabuľke, ktoré sú k dispozícii v prvej tabuľke poľa.

Samozrejme, že tento výraz na účely porovnania ukazovateľov v tabuľke, je možné použiť v existujúcej podobe, ale je možné ju zlepšiť.

Budeme robiť, že hodnoty, ktoré existujú v druhej tabuľke, ale nie sú k dispozícii v prvom boli zobrazené ako samostatný zoznam.

1. Po prvé, budeme recyklovať náš vzorec, ale budeme robiť to jeden z argumentov prevádzkovateľa If. K tomu, upozorňujeme na prvú bunku, v ktorom operátor je operátor. Vo vzorci reťazci pred ním, pridáme výraz "ak" bez úvodzoviek a otvorte držiak. Ďalej, aby to bolo pre nás ľahšie sa do práce, prideľovať vzorcov vo vzorci riadku a kliknite na ikonu "funkcie Insert".



2. Ak sa otvorí okno argumenty funkcie. Ako vidíte, prvé okno okná už bola naplnená ocenenie rady prevádzkovateľa. Ale musíme pridať ešte niečo v tejto oblasti. Vytvorili sme tam kurzor a už existujúce výraz doplnok "= 0" bez úvodzoviek.

   Po tom, choďte na "Význam Popravde" pole. Tu budeme používať iný vnorené funkciu - line. Zadajte slovo "reťazec" bez úvodzoviek, potom otvorené zátvorky a označujú súradnice prvú bunku s priezviskom v druhej tabuľke, po ktorom sme sa zavrieť zátvoriek. Konkrétne v našom prípade v "Znamená Ak skutočné" poľa nasledujúci výraz stalo:

   Riadok (D2)

   Teraz reťazec operátor bude hlásiť funkcie, ak táto funkcia, ak sa zobrazí číslo riadku, v ktorom je špecifický priezvisko nachádza, a v prípade, kedy sa bude vykonávať podmienka uvedená v prvom poli toto číslo. Kliknite na tlačidlo "OK".



3. Ako vidíte, prvý výsledok je zobrazený ako "lži". To znamená, že hodnota nespĺňa podmienky pre prevádzkovateľa, ak. To znamená, že prvé priezvisko je prítomná v oboch zoznamoch.



4. Pomocou plniacej marker, ktorá je už známa kopírovať expresiu operátora, ak sa na celý stĺpec. Ako vidíme, v dvoch polohách, ktoré sú prítomné v druhej tabuľke, ale nie v prvej vzorec vydáva riadky čísel.



5. My ustúpiť od plochy stola na pravej strane a vyplňte stĺpce čísel v poradí, počnúc 1. Počet čísiel musí zodpovedať počtu riadkov v druhom compaable tabuľky. Aby urýchlili postup číslovanie, môžete tiež použiť plniaci značky.



6. Za to, že sa zameriame na prvú bunku na pravej strane reproduktora s číslami a kliknite na ikonu "funkcie Insert".



7. Otvorí sa sprievodca. Prejdite do kategórie "štatistickú" a produkujú názov "najmenší" meno. Kliknite na tlačidlo "OK".



8. Funkcia je najmenší, argumenty argumentov, z ktorých bola popísaná, má stiahnuť zadanú najmenšiu hodnotu.

   V poli "Array" by ste mali špecifikovať súradnice sortimentu voliteľného stĺpca "Počet zhody", ktoré sme predtým transformovali pomocou funkcie, ak. Robíme všetky odkazy absolútne.

   V poli "K" sa uvedie, ktoré v účte by sa mala zobraziť najmenšia hodnota. Tu uvádzame súradnice prvej bunky stĺpca s číslovaním, ktoré sme nedávno pridali. Relatívne. Kliknite na tlačidlo "OK".



9. Prevádzkovateľ zobrazuje výsledok - číslo 3. Je presne najmenší z číslovania nekonzistentných línií polí. Pomocou výplňového markera skopírujte vzorec na samotný nos.



10. Teraz, keď poznáte čísla riadkov nekomprízových prvkov, môžeme vložiť do bunky a ich hodnoty pomocou indexovej funkcie. Vyberte prvý prvok listu obsahujúceho vzorca je najmenší. Potom prejdite na riadkové vzorce a pred názvom "najmenší" pridajte názov "index" bez úvodzoviek, okamžite otvorte konzolu a dajte bod s čiarkou (;). Potom prideľujeme v riadku a kliknite na ikonu "Funkcia paste".



11. Potom sa otvorí malé okno, v ktorom je potrebné určiť, či by mal mať referenčný pohľad indexovú funkciu alebo navrhnuté tak, aby pracovali s poliami. Potrebujeme druhú možnosť. Je nainštalovaný štandardne, takže v tomto okne jednoducho kliknite na tlačidlo "OK".



12. Okno argumentu je spustenie funkcie indexu. Tento operátor je určený na výstup hodnoty, ktorá sa nachádza v špecifickom poli v určenom riadku.

    Ako môžete vidieť, pole "Číslo riadku" je už naplnená hodnotami zmysle najmenšieho funkcie. Z existujúcej hodnoty tam rozdiel medzi číslovaním listu Excel a interným číslovaním tabuľkovej oblasti. Ako vidíme, máme len klobúk na hodnotách tabuľky. To znamená, že rozdiel je jeden riadok. Preto pridajte "-1" bez úvodzoviek v poli "Number Number".

    V poli "Array" zadajte adresu hodnoty druhej tabuľkovej hodnoty. Zároveň, všetky súradnice robia absolútne, to znamená, že dolár podpísať pred metódou opísanou podľa nás.

    Kliknite na tlačidlo "OK".



13. Po vytiahnutí, je výsledok na obrazovke sa preťahuje funkciu pomocou výplne značku na konci stĺpca nadol. Ako vidíme, oba priezviská, ktoré sú prítomné v druhej tabuľke, ale nie sú k dispozícii v prvom, sú odstránené v samostatnom rozsahu.

Metóda 5: Porovnanie polí v rôznych knihách

Ak porovnáme rozsah v rôznych knihách, môžete použiť vyššie uvedené metódy, s výnimkou tých možností, kde je požadovaný umiestnenie oboch tabuľkových oblastí na jeden list. Hlavnou podmienkou pre postup porovnanie v tomto prípade je otváranie okien oboch súborov naraz. Pre verzie programu Excel 2013 a novšie, rovnako ako u verzie programu Excel 2007, nie sú tam žiadne problémy s implementáciou tohto stavu. Ale v programe Excel 2007 a Excel 2010 s cieľom otvoriť obe okná v rovnakom čase, je nutné vykonať ďalšie manipulácie. Ako to urobiť, hovorí v samostatnej lekcii.

Poučenie: ako otvoriť Excel v rôznych oknách

Ako vidíte, existuje celý rad možností, ako porovnať tabuľky so sebou. Aký druh možnosť využitia závisí na tom, kde sa nachádza tabuľka údaje navzájom (na jeden list papiera, v rôznych knihách, na rôznych listoch), rovnako ako z chce užívateľ toto porovnanie, ktorý sa zobrazí na obrazovke.