Porovnanie údajov programu Excel. Ako porovnávať dva stĺpce v programe Excel pre zhody

Čítanie tohto článku vám bude trvať asi 10 minút. V nasledujúcich 5 minútach môžete ľahko porovnať dva stĺpce v programe Excel a zistiť, či sú v nich duplikáty, vymazať alebo farebne ich zvýrazniť. Nastal teda čas!

Excel je veľmi výkonná a skutočne skvelá aplikácia na vytváranie a manipuláciu s veľkým množstvom údajov. Ak máte niekoľko zošitov s údajmi (alebo iba jednu obrovskú tabuľku), pravdepodobne budete chcieť porovnať dva stĺpce, nájsť duplicitné hodnoty a potom s nimi podniknúť určité kroky, napríklad odstrániť, zvýrazniť alebo vyčistiť obsah ... Stĺpce môžu byť v tej istej tabuľke, môžu byť súvislé alebo nesúvislé, môžu byť umiestnené na 2 rôznych listoch alebo dokonca v rôznych knihách.

Predstavte si, že máme 2 stĺpce mien ľudí - 5 mien v stĺpci A a 3 mená v stĺpci B... Je potrebné porovnať názvy v týchto dvoch stĺpcoch a nájsť duplikáty. Ako ste pochopili, ide o fiktívne údaje, ktoré slúžia iba ako príklad. V reálnych tabuľkách máme do činenia s tisíckami, ak nie s desiatkami tisíc záznamov.

Možnosť A: oba stĺpce sú na rovnakom hárku. Napríklad stĺpec A a stĺpec B.

Možnosť B: Stĺpce sú umiestnené na rôznych listoch. Napríklad stĺpec A na hárku Hárok2 a stĺpec A na hárku List3.

Program Excel 2013, 2010 a 2007 má zabudovaný nástroj Odstrániť duplikát(Odstrániť duplikáty), ale je bezmocný v takej situácii, že nemôže porovnávať údaje v 2 stĺpcoch. Okrem toho môže odstrániť iba duplikáty. Nie sú k dispozícii žiadne ďalšie možnosti, ako napríklad zvýraznenie alebo zmena farieb. A pointa!

Porovnajte 2 stĺpce v programe Excel a nájdite duplicitné záznamy pomocou vzorcov

Možnosť A: oba stĺpce sú na rovnakom hárku

Výzva: Vo veľkých tabuľkách bude kopírovanie vzorca rýchlejšie, ak použijete klávesové skratky. Vyberte bunku C1 a stlačte Ctrl + C(skopírujte vzorec do schránky), potom stlačte Ctrl + Shift + Koniec(vyberte všetky nevyplnené bunky v stĺpci C) a nakoniec stlačte Ctrl + V(pre vloženie vzorca do všetkých vybraných buniek).

Možnosť B: dva stĺpce sú na rôznych listoch (v rôznych knihách)

Spracovanie nájdených duplikátov

Skvelé, v prvom stĺpci sme našli záznamy, ktoré sa nachádzajú aj v druhom stĺpci. Teraz s nimi musíme niečo urobiť. Ručné prezeranie všetkých duplicitných záznamov v tabuľke je dosť neefektívne a časovo náročné. Existujú aj lepšie spôsoby.

Zobraziť iba duplicitné riadky v stĺpci A

Ak vaše stĺpce nemajú hlavičky, musíte ich pridať. Umiestnite kurzor na číslo, ktoré predstavuje prvý riadok, a zmení sa na čiernu šípku, ako je to znázornené na obrázku nižšie:

Kliknite pravým tlačidlom myši a vyberte možnosť Vložte(Vložiť):

Dajte názvy stĺpcov, napríklad „ názov"A" Duplikát?”Potom otvorte záložku Údaje(Údaje) a stlačte Filtrovať(Filter):

Potom kliknite na malú šedú šípku vedľa položky „ Duplikát?„Rozbaliť ponuku filtra; zrušte začiarknutie všetkých položiek v tomto zozname okrem Duplikát a stlačte Ok.

To je všetko, teraz vidíte iba tie prvky stĺpca ALE ktoré sú duplikované v stĺpci IN... V našej študijnej tabuľke sú iba dve také bunky, ale ako viete, v praxi je ich oveľa viac.

Ak chcete znova zobraziť všetky riadky stĺpca ALE, kliknite na symbol filtra v stĺpci IN ktorý teraz vyzerá ako lievik s malou šípkou a vyberte Vybrať všetko(Vybrať všetko). Prípadne môžete urobiť to isté prostredníctvom informačného kanála kliknutím Údaje(Údaje)> Vyberte & filtrovať(Triediť a filtrovať)> jasný(Vymazať), ako je znázornené na snímke obrazovky nižšie:

Zmeňte farbu alebo zvýraznite nájdené duplikáty

Ak sú značky Duplikát„Nestačí to na vaše účely a chcete označiť opakujúce sa bunky inou farbou písma, farbou výplne alebo iným spôsobom ...

V takom prípade odfiltrujte duplikáty, ako je uvedené vyššie, vyberte všetky filtrované bunky a kliknite na tlačidlo Ctrl + 1 otvorte dialógové okno Naformátujte bunky(Formát bunky). Ako príklad zmeňme farbu výplne buniek v duplicitných riadkoch na žiarivo žltú. Samozrejme môžete pomocou nástroja zmeniť farbu výplne Naplňte(Farba výplne) na karte Domov(Domov), ale výhodou dialógového okna Naformátujte bunky(Formát bunky) spočíva v tom, že môžete nakonfigurovať všetky možnosti formátovania súčasne.

Teraz vám určite nebude chýbať ani jedna bunka s duplikátmi:

Odstraňujú sa duplicitné hodnoty z prvého stĺpca

Filtrujte tabuľku tak, aby sa zobrazili iba bunky s duplicitnými hodnotami, a vyberte tieto bunky.

Ak sú dva porovnávané stĺpce na rôznych listoch, to znamená v rôznych tabuľkách kliknite pravým tlačidlom myši na vybraný rozsah a vyberte Odstrániť riadok(Odstrániť riadok):

Kliknite na Ok keď vás Excel požiada o potvrdenie, že chcete skutočne vymazať celý riadok tabuľky, a potom vyčistiť filter. Ako vidíte, zostávajú iba riadky s jedinečnými hodnotami:

Ak sú 2 stĺpce na rovnakom hárku, blízko seba (susedia) alebo nie sú blízko seba (nesusedia), potom bude proces odstraňovania duplikátov o niečo náročnejší. Nemôžeme vymazať celý riadok s duplicitnými hodnotami, pretože to vymaže aj bunky z druhého stĺpca. Takže v stĺpci ponechať iba jedinečné záznamy ALE, to urobiť:

Ako vidíte, odstránenie duplikátov z dvoch stĺpcov v programe Excel pomocou vzorcov nie je také ťažké.

Používatelia Excelu pomerne často čelia úlohe porovnávať dve tabuľky alebo zoznamy, aby identifikovali rozdiely alebo chýbajúce položky. Každý užívateľ sa s touto úlohou vyrovná po svojom, ale riešeniu tejto otázky sa najčastejšie venuje pomerne veľa času, pretože nie všetky prístupy k tomuto problému sú racionálne. Zároveň existuje niekoľko osvedčených algoritmov pre akcie, ktoré vám umožnia porovnávať zoznamy alebo tabuľky v pomerne krátkom čase s minimálnym úsilím. Poďme sa na tieto možnosti pozrieť bližšie.

Existuje niekoľko spôsobov, ako porovnať oblasti tabuliek v programe Excel, ale všetky je možné rozdeliť do troch veľkých skupín:

porovnanie zoznamov na rovnakom liste;

porovnanie tabuliek umiestnených na rôznych listoch;

porovnanie rozsahov tabuliek v rôznych súboroch.

Na základe tejto klasifikácie sa najskôr vyberú porovnávacie metódy a určia sa konkrétne akcie a algoritmy na vykonanie úlohy. Napríklad pri porovnávaní v rôznych zošitoch musíte otvoriť dva súbory programu Excel súčasne.

Ďalej je potrebné povedať, že má zmysel porovnávať tabuľkové priestory iba vtedy, ak majú podobnú štruktúru.

Metóda 1: jednoduchý vzorec

Najjednoduchší spôsob, ako porovnať údaje v dvoch tabuľkách, je použiť jednoduchý vzorec rovnosti. Ak sa údaje zhodujú, potom vráti ukazovateľ TRUE, a ak nie, potom - FALSE. Môžete porovnať číselné údaje aj text. Nevýhodou tejto metódy je, že ju možno použiť, iba ak sú údaje v tabuľke zoradené alebo zoradené rovnako, synchronizované a majú rovnaký počet riadkov. Pozrime sa, ako túto metódu v praxi využiť na príklade dvoch tabuliek umiestnených na jednom liste.

Máme teda dve jednoduché tabuľky so zoznamami zamestnancov podniku a ich platmi. Je potrebné porovnať zoznamy zamestnancov a zistiť nezrovnalosti medzi stĺpcami, v ktorých sú mená umiestnené.

Na to potrebujeme ďalší stĺpec na hárku. Vchádzame tam na značku «=» ... Potom klikneme na prvú položku, ktorá sa má porovnať v prvom zozname. Vložte symbol znova «=» z klávesnice. Ďalej kliknite na prvú bunku stĺpca, ktorý porovnávame, v druhej tabuľke. Výsledkom je výraz nasledujúceho typu:

Aj keď samozrejme budú v každom prípade súradnice odlišné, ale podstata zostane rovnaká.
Kliknite na kľúč Zadajte získať výsledky porovnania. Ako vidíte, pri porovnaní prvých buniek oboch zoznamov program označil indikátor „PRAVDA“, čo znamená zhodu údajov.

Teraz musíme vykonať podobnú operáciu so zvyškom buniek oboch tabuliek v stĺpcoch, ktoré porovnávame. Ale vzorec môžete jednoducho skopírovať, čo výrazne ušetrí čas. Tento faktor je obzvlášť dôležitý pri porovnávaní zoznamov s veľkým počtom reťazcov.
Postup kopírovania je najjednoduchšie dosiahnuť pomocou rukoväte na vyplnenie. Umiestnite kurzor na pravý dolný roh bunky, kde sme dostali indikátor „PRAVDA“... V takom prípade by sa mal premeniť na čierny kríž. Toto je značka plnenia. Stlačte ľavé tlačidlo myši a potiahnite kurzor nadol na počet riadkov v porovnaných poliach tabuľky.

Ako vidíte, ďalší stĺpec teraz zobrazuje všetky výsledky porovnávania údajov v dvoch stĺpcoch polí tabuľky. V našom prípade sa údaje nezhodovali iba v jednom riadku. Pri ich porovnaní vzorec dal výsledok „FALSE“... Ako vidíte, pre všetky ostatné riadky ukazovateľ vydal porovnávací vzorec „PRAVDA“.

Okrem toho je možné vypočítať počet nezhôd pomocou špeciálneho vzorca. Ak to chcete urobiť, vyberte prvok tabuľky, kde sa bude zobrazovať. Potom kliknite na ikonu "Vložiť funkciu".

V okne Sprievodcovia funkciami v skupine operátorov „Matematický“ zvýraznite meno SUMPRODUKT... Kliknite na tlačidlo „OK“.

Okno argumentov funkcie je aktivované SUMPRODUKT, ktorej hlavnou úlohou je vypočítať súčet výrobkov vybraného rozsahu. Túto funkciu však môžeme použiť aj na naše účely. Jeho syntax je dosť jednoduchá:
SUMPRODUCT (pole1; pole2; ...)

Celkovo možno ako argumenty použiť adresy do 255 polí. Ale v našom prípade použijeme iba dve polia, navyše ako jeden argument.

Dáme kurzor do poľa „Pole1“ a vyberte na hárku porovnávaný rozsah údajov v prvej oblasti. Potom sme do poľa vložili značku "nerovná sa" (<> ) a vyberte porovnaný rozsah druhého regiónu. Ďalej výsledný výraz zabalíme do zátvoriek, pred ktoré dáme dva znaky «-» ... V našom prípade sme dostali nasledujúci výraz:

- (A2: A7<>D2: D7)

Kliknite na tlačidlo „OK“.

Operátor vypočíta a zobrazí výsledok. Ako vidíte, v našom prípade sa výsledok rovná číslu "jeden" to znamená, že znamená, že v porovnávaných zoznamoch sa našiel jeden nesúlad. Keby boli zoznamy úplne identické, potom by sa výsledok rovnal číslu «0» .

Rovnakým spôsobom môžete porovnávať údaje v tabuľkách, ktoré sú umiestnené na rôznych listoch. Ale v tomto prípade je žiaduce, aby riadky v nich boli očíslované. Inak je postup porovnania takmer úplne rovnaký, ako je popísané vyššie, s výnimkou skutočnosti, že pri zadávaní vzorca budete musieť prepínať medzi hárkami. V našom prípade bude výraz vyzerať takto:

B2 = List2! B2

To znamená, ako vidíme, že pred súradnicami údajov, ktoré sa nachádzajú na iných listoch, ktoré sa líšia od toho, kde sa zobrazuje výsledok porovnania, je uvedené číslo listu a výkričník.

Metóda 2: selekcia skupín buniek

Porovnanie je možné vykonať pomocou nástroja na výber bunkovej skupiny. Umožňuje tiež porovnávať iba synchronizované a zoradené zoznamy. V takom prípade by navyše mali byť zoznamy umiestnené vedľa seba na rovnakom hárku.

Metóda 3: podmienené formátovanie

Porovnania je možné vykonať pomocou metódy podmieneného formátovania. Rovnako ako v predchádzajúcej metóde musia byť porovnávané oblasti na rovnakom pracovnom hárku programu Excel a navzájom synchronizované.

Existuje ďalší spôsob, ako použiť podmienené formátovanie na splnenie úlohy. Rovnako ako predchádzajúce možnosti vyžaduje umiestnenie oboch porovnávaných oblastí na rovnakom hárku, ale na rozdiel od predtým opísaných metód nebude vyžadovaná podmienka synchronizácie alebo triedenia údajov, čo túto možnosť priaznivo odlišuje od tých predtým opísaných.

Ak je to žiaduce, môžete naopak nezhodné prvky vyfarbiť a tie indikátory, ktoré sa zhodujú s výplňou, môžete nechať v rovnakej farbe. V tomto prípade je algoritmus akcií prakticky rovnaký, ale v okne nastavení na zvýraznenie duplicitných hodnôt v prvom poli namiesto parametra „Opakujúce sa“ parameter by mal byť vybraný „Jedinečný“... Potom kliknite na tlačidlo „OK“.

Zvýraznia sa teda ukazovatele, ktoré sa nezhodujú.

Metóda 4: zložitý vzorec

Môžete tiež porovnať údaje pomocou zložitého vzorca založeného na funkcii COUNTIF... Pomocou tohto nástroja môžete vypočítať, koľko sa každý prvok z vybraného stĺpca druhej tabuľky opakuje v prvom.

Prevádzkovateľ COUNTIF patrí do štatistickej skupiny funkcií. Jeho úlohou je spočítať počet buniek, v ktorých hodnoty vyhovujú danej podmienke. Syntax tohto operátora je nasledovná:

COUNTIF (rozsah, kritérium)

Argument „Rozsah“ je adresa poľa, kde sa počítajú zodpovedajúce hodnoty.

Argument „Kritérium“ určuje podmienku zhody. V našom prípade to bude predstavovať súradnice konkrétnych buniek v prvej oblasti tabuľky.

Na porovnanie tabuľkových ukazovateľov je možné tento výraz samozrejme použiť v existujúcej podobe, ale je možné ho vylepšiť.

Urobme to tak, aby sa hodnoty, ktoré sú v druhej tabuľke, ale nie v prvej, zobrazili v samostatnom zozname.

V prvom rade si trochu upravme náš vzorec. COUNTIF, menovite to urobíme jedným z argumentov operátora AK... Ak to chcete urobiť, vyberte prvú bunku, v ktorej sa nachádza operátor COUNTIF... Na riadok vzorcov pred neho pridajte výraz "AK" bez úvodzoviek a otvorte zátvorku. Ďalej, aby sme nám uľahčili prácu, vyberte hodnotu v riadku vzorcov "AK" a kliknite na ikonu "Vložiť funkciu".

Otvorí okno argumentov funkcie AK... Ako vidíte, prvé pole okna je už vyplnené hodnotou operátora COUNTIF... Musíme však do tohto poľa pridať niečo iné. Nastavíme tam kurzor a pridáme k už existujúcemu výrazu «=0» bez úvodzoviek.
Potom choďte do poľa „Hodnota, ak je pravda“... Tu použijeme ešte jednu vnorenú funkciu - LINKA... Zadávame slovo „LINE“ bez úvodzoviek, potom otvorte zátvorky a v druhej tabuľke označte súradnice prvej bunky s priezviskom a potom zátvorky zatvorte. Konkrétne v našom prípade v teréne „Hodnota, ak je pravda“ dostal nasledujúci výraz:

RIADOK (D2)

Teraz operátor LINKA bude komunikovať funkcie AKčíslo riadku, v ktorom sa nachádza konkrétne priezvisko, a v prípade splnenia podmienky uvedenej v prvom poli funkcia AK odošle toto číslo do bunky. Kliknite na tlačidlo „OK“.

Ako vidíte, prvý výsledok sa zobrazí ako „FALSE“... To znamená, že hodnota nespĺňa podmienky prevádzkovateľa AK... To znamená, že v obidvoch zoznamoch je uvedené prvé priezvisko.

Pomocou značky výplne skopírujte výraz operátora obvyklým spôsobom AK celý stĺpec. Ako vidíte, pre dve polohy, ktoré sú prítomné v druhej tabuľke, ale nie v prvej, dáva vzorec čísla riadkov.

Ustúpime z oblasti tabuľky napravo a vyplníme stĺpec číslami v poradí, počnúc od 1 ... Počet čísel sa musí zhodovať s počtom riadkov v druhej porovnávacej tabuľke. Číslovanie môžete urýchliť aj pomocou značky plnenia.

Potom vyberte prvú bunku napravo od stĺpca s číslami a kliknite na ikonu "Vložiť funkciu".

Otvára sa Sprievodca funkciami... Prejdite do kategórie „Štatistický“ a vyberte meno „NAJMENEJ“... Kliknite na tlačidlo „OK“.

Funkcia NAJMENEJ, ktorého okno s argumentmi bolo otvorené, je určené na zobrazenie najnižšej hodnoty určenej počtom.
V teréne „Pole“ mali by ste určiť súradnice rozsahu dodatočného stĺpca „Počet náhod“ ktoré sme predtým transformovali pomocou funkcie AK... Všetky odkazy robíme absolútne.

V teréne „K“ označuje najmenšiu hodnotu, ktorá sa má zobraziť. Tu označíme súradnice prvej bunky stĺpca s číslovaním, ktoré sme nedávno pridali. Nechajte relatívnu adresu. Kliknite na tlačidlo „OK“.

Operátor vydá výsledok - číslo 3 ... Toto je najmenšie číslovanie nezodpovedajúcich riadkov v poliach tabuľky. Pomocou rukoväte na vyplnenie skopírujte vzorec úplne na koniec.

Teraz, keď poznáme čísla riadkov nezhodných prvkov, môžeme pomocou funkcie vložiť do bunky a ich hodnoty INDEX... Vyberte prvý prvok listu obsahujúci vzorec NAJMENEJ... Potom prejdite na riadok vzorcov a pred meno „NAJMENEJ“ pridajte meno „INDEX“ bez úvodzoviek, okamžite otvorte zátvorku a vložte bodkočiarku ( ; ). Potom na paneli vzorcov vyberte meno „INDEX“ a kliknite na ikonu "Vložiť funkciu".

Potom sa otvorí malé okno, v ktorom musíte určiť, či má mať funkcia referenčný formulár INDEX alebo navrhnuté na prácu s poľami. Potrebujeme druhú možnosť. Je nainštalovaný predvolene, takže v tomto okne stačí kliknúť na tlačidlo „OK“.

Spustí sa okno funkčných argumentov INDEX... Tento operátor je určený na zobrazenie hodnoty, ktorá sa nachádza v konkrétnom poli v zadanom riadku.
Ako vidíte, pole "Poradové číslo" už sú vyplnené funkčnými hodnotami NAJMENEJ... Rozdiel medzi číslovaním hárka programu Excel a vnútorným číslovaním oblasti tabuľky by sa mal odpočítať od hodnoty, ktorá tam už existuje. Ako vidíte, nad hodnotami tabuľky máme iba hlavičku. To znamená, že rozdiel je jeden riadok. Preto pridáme do poľa "Poradové číslo" hodnotu „-jeden“ bez úvodzoviek.

V teréne „Pole“ uveďte adresu rozsahu hodnôt druhej tabuľky. V tomto prípade urobíme všetky súradnice absolútnymi, to znamená, že pred ne vložíme znak dolára spôsobom, ktorý sme už opísali.

Kliknite na tlačidlo „OK“.

Po zobrazení výsledku na obrazovke roztiahnite funkciu pomocou značky výplne na koniec stĺpca nadol. Ako vidíte, obe priezviská, ktoré sú v druhej tabuľke, ale v prvej chýbajú, sa zobrazujú v samostatnom rozsahu.

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

Pri porovnávaní rozsahov v rôznych zošitoch môžete použiť metódy uvedené vyššie, okrem tých možností, do ktorých chcete umiestniť obe oblasti tabuľky na jeden hárok. Hlavnou podmienkou vykonania porovnávacieho postupu je v tomto prípade súčasné otvorenie okien oboch súborov. Pre verzie programu Excel 2013 a novších verzií, ako aj pre verzie staršie ako Excel 2007 nie je s touto podmienkou žiadny problém. V programoch Excel 2007 a Excel 2010 je však potrebná ďalšia manipulácia, aby bolo možné otvoriť obe okná súčasne. Ako na to je popísané v samostatnej lekcii.

Ako vidíte, existuje veľa možností vzájomného porovnania tabuliek. Ktorá možnosť sa použije, závisí od toho, kde sa presne nachádzajú tabuľkové údaje navzájom (na rovnakom hárku, v rôznych knihách, na rôznych hárkoch), ako aj od toho, ako si používateľ želá, aby sa toto porovnanie zobrazilo na obrazovke.

Povedzme, že chcete porovnať verzie zošita, analyzovať zošit z hľadiska problémov alebo nezrovnalostí alebo zobraziť odkazy medzi zošitmi alebo pracovnými hárkami. Ak je vo vašom počítači nainštalovaný Microsoft Office 365 alebo Office Professional Plus 2013, doplnok Spreadsheet Inquire je k dispozícii v programe Excel.

Všetky tieto úlohy a ešte viac môžete vykonávať pomocou príkazov na karte Dopyt. Na karte Dopyt na páse s nástrojmi programu Excel sú tlačidlá pre príkazy popísané nižšie.

Ak nevidíte Opýtajte sa na páse s nástrojmi Excel nájdete v časti Zapnutie doplnku Spreadsheet Enquire.

Porovnajte dva zošity

The Porovnať súbory Príkaz umožňuje zobraziť rozdiely medzi bunkami medzi jednotlivými zošitmi. Ak chcete spustiť tento príkaz, v programe Excel musíte mať otvorené dva zošity.

Výsledky sú farebne rozlíšené podľa druhu obsahu, ako sú napríklad zadané hodnoty, vzorce, pomenované rozsahy a formáty. Existuje dokonca aj okno, ktoré dokáže zobraziť zmeny kódu VBA po riadkoch. Rozdiely medzi bunkami sú zobrazené v ľahko čitateľnom rozložení mriežky, napríklad takto:

The Porovnať súbory príkaz používa na porovnanie týchto dvoch súborov porovnanie tabuľky Microsoft. V systéme Windows 8 môžete kliknutím na ikonu Spustiť porovnávanie tabuliek mimo programu Excel Porovnanie tabuľky na Aplikácie obrazovka. Vo Windows 7 kliknite na Windows Štart tlačidlo a potom> Všetky programy > Microsoft Office 2013 > Nástroje balíka Office 2013 > Porovnanie tabuľky 2013.

Ak sa chcete dozvedieť viac informácií o porovnaní tabuliek a porovnaní súborov, prečítajte si článok Porovnanie dvoch verzií zošita.

Analyzujte zošit

The Analýza zošita príkaz vytvorí interaktívnu správu s podrobnými informáciami o zošite a jeho štruktúre, vzorcoch, bunkách, rozsahoch a varovaniach. Na tomto obrázku je znázornený veľmi jednoduchý zošit obsahujúci dva vzorce a údajové pripojenia k databáze Accessu a textový súbor.

Zobraziť odkazy na zošity

Zošity spojené s inými zošitmi prostredníctvom odkazov na bunky môžu byť mätúce. Použite na vytvorenie interaktívnej grafickej mapy závislostí zošita vytvorenej spojeniami (odkazmi) medzi súbormi. Medzi typy odkazov v diagrame môžu patriť ďalšie zošity, databázy Accessu, textové súbory, stránky HTML, databázy servera SQL Server a ďalšie zdroje údajov. V relačnom diagrame môžete vybrať prvky, vyhľadať o nich ďalšie informácie a presunutím spojovacích čiar zmeniť tvar diagramu.

Tento diagram zobrazuje aktuálny zošit vľavo a spojenia medzi ním a ostatnými zošitmi a zdrojmi údajov. Ukazuje tiež ďalšie úrovne pripojení zošita, čo vám poskytne obraz o pôvode údajov v zošite.

Zobraziť odkazy na hárky

Máte veľa pracovných listov, ktoré na sebe závisia? Použite na vytvorenie interaktívnej grafickej mapy spojení (odkazov) medzi pracovnými hárkami v rovnakom zošite aj v iných zošitoch. To vám pomôže získať jasnejší obraz o tom, ako môžu vaše údaje závisieť od buniek na iných miestach.

Tento diagram ukazuje vzťahy medzi pracovnými hárkami v štyroch rôznych zošitoch, so závislosťami medzi pracovnými hárkami v rovnakom zošite, ako aj prepojenia medzi pracovnými hárkami v rôznych zošitoch. Keď umiestnite ukazovateľ nad uzol v diagrame, napríklad na pracovný hárok s názvom „Západ“, objaví sa bublina obsahujúca informácie.

Zobraziť vzťahy buniek

Ak chcete získať podrobný interaktívny diagram všetkých odkazov z vybratej bunky na bunky v iných pracovných hárkoch alebo dokonca v iných zošitoch, použite Vzťah buniek nástroj. Tieto vzťahy s inými bunkami môžu existovať vo vzorcoch alebo v odkazoch na pomenované rozsahy. Diagram môže prechádzať cez pracovné listy a zošity.

Tento diagram zobrazuje dve úrovne bunkových vzťahov pre bunku A10 na hárku 5 v knihe Book1.xlsx. Táto bunka je závislá od bunky C6 na hárku 1 v inom zošite, Book2.xlsx. Táto bunka je precedensom pre niekoľko buniek v iných pracovných hárkoch v rovnakom súbore.

Ak sa chcete dozvedieť viac informácií o prezeraní vzťahov buniek, prečítajte si tému Prepojenia medzi bunkami

Vyčistite nadbytočné formátovanie buniek

Už ste niekedy otvorili zošit a zistili, že sa načítava pomaly, alebo sa stal obrovským? Je možné, že sa formátovanie použilo na riadky alebo stĺpce, o ktorých neviete. Použite Vyčistite nadbytočné formátovanie buniek príkaz na odstránenie nadmerného formátovania a výrazné zmenšenie veľkosti súboru. To vám pomôže vyhnúť sa „nafukovaniu tabuliek“, ktoré zvyšuje rýchlosť programu Excel.

Spravujte heslá

Ak používate funkcie Inquire na analýzu alebo porovnanie zošitov chránených heslom, budete musieť do zoznamu hesiel pridať heslo zošita, aby mohla služba Inquire otvoriť uloženú kópiu vášho zošita. Použi Heslá zošita príkaz na Opýtajte sa Na karte môžete pridať heslá, ktoré sa uložia vo vašom počítači. Tieto heslá sú šifrované a prístupné iba vy.

Tento článok poskytuje odpovede na nasledujúce otázky:

Ako porovnať dve tabuľky v programe Excel?
Ako porovnávať zložité tabuľky v programe Excel?
Ako porovnávať tabuľky v programe Excel pomocou funkcie VLOOKUP ()?
Ako vygenerovať jedinečné identifikátory riadkov, ak je ich jedinečnosť pôvodne určená súborom hodnôt v niekoľkých stĺpcoch?
Ako zmraziť hodnoty buniek vo vzorcoch pri kopírovaní vzorcov?

Pri práci s veľkým množstvom informácií môže používateľa čakať taká úloha, ako je porovnanie dvoch tabuľkových zdrojov údajov. Pri ukladaní údajov do jedného účtovného systému (napríklad systémy založené na 1C Enterprise, systémy využívajúce databázy SQL) možno na porovnanie údajov využiť schopnosti zabudované do systému alebo DBMS. Spravidla k tomu stačí zapojiť programátora, ktorý napíše dopyt do databázy alebo mechanizmus softvérového reportu. Skúsený používateľ, ktorý má skúsenosti s písaním dotazov 1C alebo SQL, tiež dokáže spracovať dopyt.

Problémy začínajú, keď potrebujete urgentne vykonať úlohu porovnania údajov. Účasť programátora a jeho včasné napísanie žiadosti alebo správy o programe môže prekročiť termíny stanovené na vyriešenie úlohy. Ďalším rovnako častým problémom je potreba porovnávať informácie z rôznych zdrojov. V takom prípade bude vyhlásenie problému pre programátora znieť ako integrácia dvoch systémov. Riešenie takejto úlohy bude vyžadovať vyššiu kvalifikáciu programátora a bude tiež vyžadovať viac času ako vývoj v jednom systéme.

Na vyriešenie uvedených problémov je ideálnou technikou porovnanie údajov pomocou tabuľkového editora Microsoft Excel. Väčšina bežných riadiacich a regulačných účtovných systémov podporuje export do formátu Excel. Táto úloha bude vyžadovať iba určitú kvalifikáciu používateľa na prácu s týmto kancelárskym balíkom a nebude vyžadovať programátorské schopnosti.

Uvažujme o riešení problému porovnávania tabuliek v programe Excel pomocou príkladu. Máme dve tabuľky obsahujúce zoznamy apartmánov. Zdroje vykládky - 1C Enterprise (stavebné účtovníctvo) a tabuľka v programe Excel (účtovníctvo predaja). Tabuľky sa nachádzajú v zošite programu Excel na prvom a druhom hárku.

Našou úlohou je porovnať tieto zoznamy podľa adries. Prvá tabuľka obsahuje všetky byty v dome. Druhá tabuľka obsahuje iba predané byty a meno kupujúceho. Konečným cieľom je zobraziť meno kupujúceho v prvej tabuľke pre každý apartmán (pre tie byty, ktoré boli predané). Úlohu komplikuje skutočnosť, že adresa bytu v každej tabuľke je konštrukčná a pozostáva z niekoľkých polí: 1) adresa budovy (domu), 2) časť (vchod), 3) poschodie, 4) číslo na poschodí (napríklad od 1 do 4) ...

Na porovnanie dvoch tabuliek programu Excel musíme zabezpečiť, aby bol každý riadok v oboch tabuľkách identifikovaný jedným poľom, nie štyrmi. Takéto pole môžete získať zreťazením hodnôt štyroch polí adresy pomocou funkcie Concatenate (). Účelom funkcie Concatenate () je spojiť niekoľko textových hodnôt do jedného riadku. Hodnoty vo funkcii sú uvedené prostredníctvom symbolu „;“. Hodnotami môžu byť buď adresy buniek, alebo ľubovoľný text uvedený v úvodzovkách.

Krok 1. Vložme prázdny stĺpec „A“ na začiatok prvej tabuľky a do bunky tohto stĺpca napíšeme vzorec oproti prvému riadku s údajmi:
= KONCATENÁT (B3; "-"; C3; "-"; D3; "-"; E3)
Pre uľahčenie vizuálneho vnímania sme medzi hodnoty buniek, ktoré sa majú zlúčiť, nastavili symboly „-“.

Krok 2. Skopírujte vzorec do nasledujúcich buniek v stĺpci A.

Krok 4. Ak chcete porovnať tabuľky programu Excel podľa hodnôt, použite funkciu VLOOKUP (). Účelom funkcie VLOOKUP () je nájsť hodnotu v stĺpci úplne vľavo tabuľky a vrátiť hodnotu bunky umiestnenej v zadanom stĺpci toho istého riadku. Prvým parametrom je požadovaná hodnota. Druhým parametrom je tabuľka, v ktorej sa bude hodnota hľadať. Tretím parametrom je číslo stĺpca, z ktorého sa vráti hodnota v nájdenom riadku. Štvrtým parametrom je typ vyhľadávania: false je presná zhoda, true je približná zhoda. Pretože výstupné informácie by mali byť umiestnené v prvej tabuľke (do tejto tabuľky bolo treba pridať mená kupujúcich), potom sa do nej zapíše vzorec. Vytvorme vzorec vo voľnom stĺpci napravo od tabuľky oproti prvému riadku údajov:
= VLOOKUP (A3; list2! $ A $ 3: $ F $ 10; 6; FALSE)
Pri kopírovaní vzorcov inteligentný Excel automaticky zmení adresovanie buniek. V našom prípade sa zmení požadovaná hodnota pre každý riadok: A3, A4 atď. A adresa tabuľky, v ktorej sa vykonáva vyhľadávanie, musí zostať nezmenená. Za týmto účelom opravte bunky v parametri adresy tabuľky symbolmi „$“. Namiesto „Sheet2! A3: F10“ vyrábame „Sheet2! $ A $ 3: $ F $ 10“.

Po nainštalovaní doplnku sa vám zobrazí nová karta s príkazom na vyvolanie funkcie. Keď kliknete na príkaz Porovnávanie rozsahov objaví sa dialógové okno pre zadanie parametrov.

Toto makro umožňuje porovnávať tabuľky ľubovoľnej veľkosti a s ľubovoľným počtom stĺpcov. Porovnávanie tabuliek je možné vykonať v jednom, dvoch alebo troch stĺpcoch súčasne.

Dialógové okno je rozdelené na dve časti: ľavá pre prvú tabuľku a pravá pre druhú.

Ak chcete porovnať tabuľky, postupujte takto:

Zadajte rozsahy tabuliek.
Zaškrtnite políčko (začiarkavacie políčko / políčko) pod vybratým rozsahom tabuliek, ak tabuľka obsahuje hlavičku (riadok hlavičky).
Vyberte stĺpce ľavej a pravej tabuľky, pomocou ktorých sa porovnanie uskutoční (ak rozsahy tabuliek neobsahujú hlavičky, budú stĺpce očíslované).
Zadajte typ porovnania.
Vyberte možnosť zobrazenia výsledkov.

Porovnávací typ tabuliek

Program umožňuje výber niekoľkých typov porovnávania tabuliek:

Nájdite riadky z jednej tabuľky, ktoré sa nenachádzajú v inej tabuľke

Keď je vybratý tento typ porovnania, program vyhľadá riadky z jednej tabuľky, ktoré v inej chýbajú. Ak porovnáte tabuľky podľa viacerých stĺpcov, výsledkom práce budú riadky, v ktorých je rozdiel minimálne v jednom zo stĺpcov.

Nájdite zodpovedajúce riadky

Pri výbere tohto typu porovnania program vyhľadá riadky, ktoré sa zhodujú v prvej a druhej tabuľke. Riadky sa považujú za zhodné, ak sa hodnoty vo vybratých porovnávacích stĺpcoch (1, 2, 3) jednej tabuľky úplne zhodujú s hodnotami stĺpcov druhej tabuľky.

Príklad práce programu v tomto režime je zobrazený vpravo na obrázku.

Zhodné tabuľky založené na vybratých

V tomto režime porovnania sa oproti každému riadku prvej tabuľky (vybranej ako hlavná) skopírujú údaje o zhodnom riadku druhej tabuľky. Ak neexistujú žiadne zodpovedajúce riadky, riadok oproti hlavnej tabuľke zostáva prázdny.