Porovnání dat aplikace Excel. Jak porovnat dva sloupce v aplikaci Excel pro shody

Přečtení tohoto článku vám zabere asi 10 minut. Během příštích 5 minut můžete snadno porovnat dva sloupce v aplikaci Excel a zjistit, zda jsou v nich duplikáty, odstranit je nebo zvýraznit barvou. Nadešel čas!

Excel je velmi výkonná a opravdu skvělá aplikace pro vytváření a manipulaci s velkým množstvím dat. Pokud máte několik sešitů s daty (nebo jen jednu velkou tabulku), pravděpodobně budete chtít porovnat 2 sloupce, najít duplicitní hodnoty a poté s nimi provést nějakou akci, například odstranit, zvýraznit nebo vymazat obsah ... Sloupce mohou být ve stejné tabulce, mohou být souvislé nebo nesouvislé, mohou být umístěny na 2 různých listech nebo dokonce v různých knihách.

Představte si, že máme 2 sloupce jmen lidí - 5 jmen ve sloupci A a 3 jména ve sloupci B... Je nutné porovnat názvy v těchto dvou sloupcích a najít duplikáty. Jak jste pochopili, jedná se o fiktivní data, která jsou brána pouze jako příklad. V reálných tabulkách máme co do činění s tisíci, ne-li desítkami tisíc záznamů.

Možnost A: oba sloupce jsou na stejném listu. Například sloupec A a sloupec B.

Možnost B: Sloupce jsou umístěny na různých listech. Například sloupec A na listu List2 a sloupec A na listu List3.

Excel 2013, 2010 a 2007 mají vestavěný nástroj Odebrat duplikát(Odstranit duplikáty), ale v takové situaci je bezmocný, protože nemůže porovnávat data ve 2 sloupcích. Kromě toho může odstranit pouze duplikáty. Neexistují žádné další možnosti, jako je zvýraznění nebo změna barev. A pointa!

Porovnejte 2 sloupce v aplikaci Excel a pomocí vzorců vyhledejte duplicitní záznamy

Možnost A: oba sloupce jsou na stejném listu

Výzva: Ve velkých tabulkách bude kopírování vzorce rychlejší, pokud použijete klávesové zkratky. Vyberte buňku C1 a stiskněte Ctrl + C(pro zkopírování vzorce do schránky), poté stiskněte Ctrl + Shift + Konec(vyberte všechny neprázdné buňky ve sloupci C) a nakonec stiskněte Ctrl + V(pro vložení vzorce do všech vybraných buněk).

Možnost B: dva sloupce jsou na různých listech (v různých knihách)

Zpracování nalezených duplikátů

Skvělé, v prvním sloupci jsme našli položky, které jsou také ve druhém sloupci. Nyní s nimi musíme něco udělat. Ruční prohlížení všech duplicitních záznamů v tabulce je poměrně neefektivní a časově náročné. Existují lepší způsoby.

Zobrazit pouze duplicitní řádky ve sloupci A

Pokud vaše sloupce nemají záhlaví, musíte je přidat. Chcete-li to provést, umístěte kurzor na číslo, které představuje první řádek, a změní se na černou šipku, jak je znázorněno na obrázku níže:

Klepněte pravým tlačítkem a vyberte Vložit(Vložit):

Uveďte názvy sloupců, například „ název"A" Duplikát?“Poté otevřete kartu Data(Data) a stiskněte Filtr(Filtr):

Poté klikněte na malou šedou šipku vedle „ Duplikát?"Chcete-li rozšířit nabídku filtrů; zrušte zaškrtnutí všech položek v tomto seznamu kromě Duplikát a stiskněte OK.

To je vše, nyní vidíte pouze ty prvky sloupce ALE které jsou ve sloupci duplikovány V... V našem tréninkovém stole jsou pouze dvě takové buňky, ale jak víte, v praxi je jich mnohem více.

Chcete-li znovu zobrazit všechny řádky sloupce ALE, klikněte na symbol filtru ve sloupci V který nyní vypadá jako trychtýř s malou šipkou a vyberte Vybrat vše(Vybrat vše). Případně můžete udělat totéž prostřednictvím kanálu kliknutím Data(Data)> Vyberte & filtrovat(Třídit a filtrovat)> Průhledná(Vymazat), jak je znázorněno na následujícím obrázku:

Změňte barvu nebo zvýrazněte nalezené duplikáty

Pokud značky Duplikát„Nestačí to pro vaše účely a chcete označit opakující se buňky jinou barvou písma, barvou výplně nebo jiným způsobem ...

V takovém případě odfiltrujte duplikáty, jak je uvedeno výše, vyberte všechny filtrované buňky a klikněte Ctrl + 1 otevřete dialogové okno Formátovat buňky(Formát buňky). Jako příklad změňme barvu výplně buněk v duplicitních řádcích na jasně žlutou. Samozřejmě můžete nástrojem změnit barvu výplně Vyplnit(Barva výplně) na kartě Domov(Domů), ale výhodou dialogového okna Formátovat buňky(Formát buňky) je, že můžete konfigurovat všechny možnosti formátování současně.

Nyní vám určitě nebude chybět ani jedna buňka s duplikáty:

Odebrání duplicitních hodnot z prvního sloupce

Filtrujte tabulku tak, aby se zobrazovaly pouze buňky s duplicitními hodnotami, a vyberte tyto buňky.

Pokud jsou porovnávané 2 sloupce na různých listech, tj. v různých tabulkách klikněte pravým tlačítkem na vybraný rozsah a vyberte Smazat řádek(Smazat řádek):

Klikněte na OK když vás aplikace Excel požádá o potvrzení, že opravdu chcete odstranit celý řádek listu, a poté vyčistit filtr. Jak vidíte, zbývají pouze řádky s jedinečnými hodnotami:

Pokud jsou 2 sloupce na stejném listu, blízko sebe (sousedí) nebo nejsou blízko sebe (nesousedí), bude proces odstraňování duplikátů trochu obtížnější. Nemůžeme smazat celý řádek s duplicitními hodnotami, protože tím odstraníme také buňky z druhého sloupce. Takže ve sloupci ponechat pouze jedinečné položky ALE, Udělej tohle:

Jak vidíte, odstranění duplikátů ze dvou sloupců v aplikaci Excel pomocí vzorců není tak obtížné.

Uživatelé aplikace Excel se často potýkají s úkolem porovnávat dvě tabulky nebo seznamy, aby identifikovali rozdíly nebo chybějící položky. Každý uživatel se s tímto úkolem vyrovnává po svém, ale řešení tohoto problému se většinou věnuje poměrně velké množství času, protože ne všechny přístupy k tomuto problému jsou racionální. Současně existuje několik osvědčených algoritmů pro akce, které vám umožní porovnat seznamy nebo pole tabulek v poměrně krátké době s minimálním úsilím. Pojďme se na tyto možnosti podívat blíže.

Existuje několik způsobů, jak porovnat oblasti tabulky v aplikaci Excel, ale všechny je lze rozdělit do tří velkých skupin:

porovnání seznamů na stejném listu;

porovnání tabulek umístěných na různých listech;

srovnání rozsahů tabulek v různých souborech.

Na základě této klasifikace jsou nejprve vybrány metody porovnání a jsou určeny konkrétní akce a algoritmy pro provedení úkolu. Například při porovnávání v různých sešitech musíte otevřít současně dva soubory aplikace Excel.

Kromě toho je třeba říci, že má smysl srovnávat tabulkové prostory, pouze pokud mají podobnou strukturu.

Metoda 1: jednoduchý vzorec

Nejjednodušší způsob, jak porovnat data ve dvou tabulkách, je pomocí jednoduchého vzorce rovnosti. Pokud se data shodují, vrátí indikátor TRUE, a pokud ne, pak - FALSE. Můžete porovnat numerická data i text. Nevýhodou této metody je, že ji lze použít, pouze pokud jsou data v tabulce uspořádána nebo tříděna stejně, synchronizována a má stejný počet řádků. Podívejme se, jak tuto metodu použít v praxi na příkladu dvou tabulek umístěných na jednom listu.

Máme tedy dvě jednoduché tabulky se seznamy zaměstnanců podniku a jejich platy. Je nutné porovnat seznamy zaměstnanců a identifikovat nesrovnalosti mezi sloupci, ve kterých jsou jména umístěna.

K tomu potřebujeme další sloupec na listu. Zadáme tam značku «=» ... Poté klikneme na první položku, která má být porovnána v prvním seznamu. Vložte symbol znovu «=» z klávesnice. Dále klikněte na první buňku sloupce, který porovnáváme, v druhé tabulce. Výsledkem je výraz následujícího typu:

I když samozřejmě budou v každém případě souřadnice odlišné, ale podstata zůstane stejná.
Klikněte na klíč Enter získat výsledky srovnání. Jak vidíte, při porovnání prvních buněk obou seznamů program označil indikátor "SKUTEČNÝ", což znamená shodu dat.

Nyní musíme provést podobnou operaci se zbytkem buněk obou tabulek ve sloupcích, které porovnáváme. Ale můžete jednoduše zkopírovat vzorec, což výrazně ušetří čas. Tento faktor je obzvláště důležitý při porovnávání seznamů s velkým počtem řetězců.
Postup kopírování je nejjednodušší provést pomocí rukojeti výplně. Umístěte kurzor na pravý dolní roh buňky, kde jsme dostali indikátor "SKUTEČNÝ"... V tomto případě by měl být přeměněn na černý kříž. Toto je značka naplnění. Stiskněte levé tlačítko myši a táhněte kurzorem dolů na počet řádků v polích srovnávané tabulky.

Jak vidíte, další sloupec nyní zobrazuje všechny výsledky porovnání dat ve dvou sloupcích polí tabulky. V našem případě se data neshodovala pouze v jednom řádku. Při jejich porovnání vzorec poskytl výsledek "NEPRAVDIVÉ"... U všech ostatních řádků, jak vidíte, srovnávací vzorec vydal indikátor "SKUTEČNÝ".

Kromě toho je možné vypočítat počet neshod pomocí speciálního vzorce. Chcete-li to provést, vyberte prvek listu, kde bude zobrazen. Poté klikněte na ikonu "Vložit funkci".

V okně Průvodci funkcemi ve skupině operátorů "Matematický" zvýrazněte jméno SUMPRODUKT... Klikněte na tlačítko "OK".

Okno argumentů funkce je aktivováno SUMPRODUKT, jehož hlavním úkolem je vypočítat součet produktů vybraného rozsahu. Tuto funkci však můžeme použít i pro naše účely. Jeho syntax je poměrně jednoduchá:
SUMPRODUCT (pole1; pole2; ...)

Celkově lze jako argumenty použít adresy až 255 polí. Ale v našem případě použijeme pouze dvě pole, navíc jako jeden argument.

Umístili jsme kurzor do pole „Pole1“ a vyberte na listu srovnávaný rozsah dat v první oblasti. Poté jsme do pole umístili znamení „nerovná se“ (<> ) a vyberte srovnávaný rozsah druhé oblasti. Dále zabalíme výsledný výraz do závorek, před které dáme dva znaky «-» ... V našem případě jsme dostali následující výraz:

- (A2: A7<>D2: D7)

Klikněte na tlačítko "OK".

Operátor vypočítá a zobrazí výsledek. Jak vidíte, v našem případě se výsledek rovná počtu "jeden" to znamená, že znamená, že v porovnávaných seznamech byl nalezen jeden nesoulad. Pokud by seznamy byly zcela identické, pak by se výsledek rovnal číslu «0» .

Stejným způsobem můžete porovnávat data v tabulkách, které jsou umístěny na různých listech. Ale v tomto případě je žádoucí, aby řádky v nich byly očíslovány. Jinak je postup porovnání téměř přesně stejný, jak je popsáno výše, kromě skutečnosti, že při zadávání vzorce budete muset přepínat mezi listy. V našem případě bude výraz vypadat takto:

B2 = List2! B2

To znamená, jak vidíme, že před souřadnicemi dat, která jsou umístěna na jiných listech, odlišných od místa, kde je zobrazen výsledek srovnání, je uvedeno číslo listu a vykřičník.

Metoda 2: vyberte skupiny buněk

Porovnání lze provést pomocí nástroje pro výběr skupiny buněk. Umožňuje také porovnávat pouze synchronizované a seřazené seznamy. V tomto případě by navíc měly být seznamy umístěny vedle sebe na stejném listu.

Metoda 3: podmíněné formátování

Porovnání lze provést pomocí metody podmíněného formátování. Stejně jako v předchozí metodě musí být porovnávané oblasti na stejném listu aplikace Excel a musí být navzájem synchronizovány.

Existuje další způsob, jak použít podmíněné formátování k provedení úkolu. Stejně jako předchozí možnosti vyžaduje umístění obou srovnávaných oblastí na stejném listu, ale na rozdíl od dříve popsaných metod nebude vyžadována podmínka synchronizace nebo třídění dat, což příznivě odlišuje tuto možnost od dříve popsaných.

Je-li to žádoucí, můžete naopak nevyhovující prvky vybarvit a ty indikátory, které odpovídají výplni, nechat ve stejné barvě. V tomto případě je algoritmus akcí prakticky stejný, ale v okně nastavení pro zvýraznění duplicitních hodnot v prvním poli místo parametru "Opakující se" parametr by měl být vybrán "Unikátní"... Poté klikněte na tlačítko "OK".

Zvýrazní se tedy přesně ty ukazatele, které se neshodují.

Metoda 4: složitý vzorec

Můžete také porovnat data pomocí složitého vzorce založeného na funkci COUNTIF... S tímto nástrojem můžete spočítat, kolik se každý prvek z vybraného sloupce druhé tabulky opakuje v první.

Operátor COUNTIF patří do statistické skupiny funkcí. Jeho úkolem je spočítat počet buněk, ve kterých hodnoty splňují danou podmínku. Syntaxe tohoto operátoru je následující:

COUNTIF (rozsah, kritérium)

Argument "Rozsah" je adresa pole, kde se počítají odpovídající hodnoty.

Argument "Kritérium" určuje podmínku shody. V našem případě to bude představovat souřadnice konkrétních buněk v první oblasti tabulky.

Abychom mohli porovnat tabulkové ukazatele, lze tento výraz samozřejmě použít ve své současné podobě, ale existuje možnost jej vylepšit.

Udělejme to tak, aby se hodnoty, které jsou v druhé tabulce, ale ne v první, zobrazovaly v samostatném seznamu.

Nejprve trochu upravme náš vzorec. COUNTIF, jmenovitě z něj uděláme jeden z argumentů operátora POKUD... Chcete-li to provést, vyberte první buňku, ve které je operátor umístěn COUNTIF... Na řádku vzorců před něj přidejte výraz "POKUD" bez uvozovek a otevřete závorku. Abychom nám usnadnili práci, vyberte na řádku vzorců hodnotu "POKUD" a klikněte na ikonu "Vložit funkci".

Otevře okno argumentů funkce POKUD... Jak vidíte, první pole okna je již vyplněno hodnotou operátoru COUNTIF... Ale do tohoto pole musíme přidat něco jiného. Nastavíme tam kurzor a přidáme k již existujícímu výrazu «=0» bez uvozovek.
Poté jděte do pole "Hodnota, pokud je pravda"... Zde použijeme ještě jednu vnořenou funkci - ČÁRA... Zadáme slovo "ČÁRA" bez uvozovek, poté otevřete závorky a v druhé tabulce označte souřadnice první buňky příjmením a zavřete závorky. Konkrétně v našem případě v terénu "Hodnota, pokud je pravda" ukázal se následující výraz:

ŘADA (D2)

Nyní operátor ČÁRA bude komunikovat funkce POKUDčíslo řádku, ve kterém se konkrétní příjmení nachází, a v případě, že bude splněna podmínka uvedená v prvním poli, funkce POKUD odešle toto číslo do buňky. Klikněte na tlačítko "OK".

Jak vidíte, první výsledek se zobrazí jako "NEPRAVDIVÉ"... To znamená, že hodnota nesplňuje podmínky provozovatele POKUD... To znamená, že první příjmení je přítomno v obou seznamech.

Pomocí značky výplně zkopírujte výraz operátoru obvyklým způsobem POKUD celý sloupec. Jak vidíte, pro dvě pozice, které jsou přítomny v druhé tabulce, ale ne v první, dává vzorec čísla řádků.

Ustoupíme z oblasti tabulky doprava a vyplníme sloupec čísly v pořadí, počínaje od 1 ... Počet čísel se musí shodovat s počtem řádků ve druhé srovnávací tabulce. Můžete také použít značku plnění k urychlení procesu číslování.

Poté vyberte první buňku napravo od sloupce s čísly a klikněte na ikonu "Vložit funkci".

Otevře se Průvodce funkcí... Přejít na kategorii "Statistický" a vyberte jméno "NEJMÉNĚ"... Klikněte na tlačítko "OK".

Funkce NEJMÉNĚ, jehož okno argumentů bylo otevřeno, je určeno k zobrazení nejnižší hodnoty určené počtem.
V terénu "Pole" měli byste zadat souřadnice rozsahu dalšího sloupce "Počet náhod" které jsme předtím transformovali pomocí funkce POKUD... Děláme všechny odkazy absolutní.

V terénu "K" označuje nejmenší zobrazovanou hodnotu. Zde označíme souřadnice první buňky sloupce s číslováním, které jsme nedávno přidali. Nechte relativní adresu. Klikněte na tlačítko "OK".

Operátor odešle výsledek - číslo 3 ... Toto je nejmenší číslování neodpovídajících řádků v polích tabulky. Pomocí úchytu výplně zkopírujte vzorec úplně dole.

Nyní, když známe čísla řádků neshodných prvků, můžeme pomocí funkce vložit do buňky a jejich hodnoty INDEX... Vyberte první prvek listu obsahující vzorec NEJMÉNĚ... Poté přejděte do řádku vzorců a před jménem "NEJMÉNĚ" přidej jméno "INDEX" bez uvozovek, okamžitě otevřete závorku a vložte středník ( ; ). Poté vyberte název na řádku vzorců "INDEX" a klikněte na ikonu "Vložit funkci".

Poté se otevře malé okno, ve kterém musíte určit, zda by funkce měla mít referenční formulář INDEX nebo jsou navrženy pro práci s poli. Potřebujeme druhou možnost. Je nainstalován ve výchozím nastavení, takže v tomto okně stačí kliknout na tlačítko "OK".

Spustí se okno argumentů funkce INDEX... Tento operátor je určen k zobrazení hodnoty, která je umístěna v konkrétním poli na zadaném řádku.
Jak vidíte, pole "Číslo řádku" již je vyplněn hodnotami funkcí NEJMÉNĚ... Rozdíl mezi číslováním listu aplikace Excel a interním číslováním oblasti tabulky by měl být odečten od hodnoty, která tam již existuje. Jak vidíte, nad hodnotami tabulky máme pouze záhlaví. To znamená, že rozdíl je jeden řádek. Proto přidáme do pole "Číslo řádku" hodnota "-jeden" bez uvozovek.

V terénu "Pole" zadejte adresu rozsahu hodnot druhé tabulky. V tomto případě uděláme všechny souřadnice absolutní, to znamená, že před ně vložíme znak dolaru způsobem, který jsme již popsali.

Klikněte na tlačítko "OK".

Po zobrazení výsledku na obrazovce roztáhněte funkci pomocí značky výplně na konec sloupce dolů. Jak vidíte, obě příjmení, která jsou přítomna ve druhé tabulce, ale chybí v první, jsou zobrazena v samostatném rozsahu.

Metoda 5: Porovnání polí v různých knihách

Při porovnávání rozsahů v různých sešitech můžete použít metody uvedené výše, s výjimkou těch možností, které vyžadují, aby byly oba tabulkové prostory umístěny na stejném listu. Hlavní podmínkou pro provedení srovnávacího postupu je v tomto případě otevření oken obou souborů současně. U verzí aplikace Excel 2013 a novějších, stejně jako u verzí starších než Excel 2007, není s touto podmínkou problém. Ale v Excel 2007 a Excel 2010, aby bylo možné otevřít obě okna současně, je nutná další manipulace. Jak na to je popsáno v samostatné lekci.

Jak vidíte, existuje řada možností vzájemného porovnání tabulek. Která možnost použít závisí na tom, kde přesně jsou tabelární data relativní vůči sobě navzájem (na stejném listu, v různých knihách, na různých listech), a také na tom, jak si uživatel přeje, aby se toto srovnání zobrazilo na obrazovce.

Řekněme, že chcete porovnat verze sešitu, analyzovat sešit na problémy nebo nekonzistence nebo zobrazit odkazy mezi sešity nebo listy. Pokud je v počítači nainstalován Microsoft Office 365 nebo Office Professional Plus 2013, doplněk Spreadsheet Inquire je k dispozici v aplikaci Excel.

Všechny tyto úkoly a další můžete provádět pomocí příkazů na kartě Dotaz. Na kartě Poptávka na pásu karet aplikace Excel jsou tlačítka pro níže popsané příkazy.

Pokud nevidíte Poptat na pásu karet aplikace Excel, viz Zapnutí doplňku Spreadsheet Enquire.

Porovnejte dva sešity

The Porovnat soubory Příkaz umožňuje zobrazit rozdíly mezi buňkami mezi dvěma sešity. Chcete-li spustit tento příkaz, musíte mít v aplikaci Excel otevřené dva sešity.

Výsledky jsou barevně kódovány podle druhu obsahu, jako jsou zadané hodnoty, vzorce, pojmenované rozsahy a formáty. K dispozici je dokonce i okno, které zobrazuje změny kódu VBA řádek po řádku. Rozdíly mezi buňkami jsou zobrazeny ve snadno čitelném rozložení mřížky, například takto:

The Porovnat soubory příkaz používá k porovnání obou souborů Microsoft Spreadsheet Compare. Ve Windows 8 můžete spustit Porovnávání tabulek mimo Excel kliknutím Porovnání tabulek na Aplikace obrazovka. Ve Windows 7 klikněte na Windows Start tlačítko a poté> Všechny programy > Microsoft Office 2013 > Nástroje sady Office 2013 > Porovnání tabulek 2013.

Chcete-li se dozvědět více o porovnání tabulek a porovnání souborů, přečtěte si téma Porovnání dvou verzí sešitu.

Analyzujte sešit

The Analýza sešitu příkaz vytvoří interaktivní zprávu zobrazující podrobné informace o sešitu a jeho struktuře, vzorcích, buňkách, rozsahech a varováních. Obrázek zde ukazuje velmi jednoduchý sešit obsahující dva vzorce a datová připojení k databázi Accessu a textovému souboru.

Zobrazit odkazy na sešity

Sešity spojené s jinými sešity prostřednictvím odkazů na buňky mohou být matoucí. Použijte k vytvoření interaktivní grafické mapy závislostí sešitu vytvořených spojením (odkazy) mezi soubory. Typy odkazů v diagramu mohou zahrnovat další sešity, databáze Accessu, textové soubory, stránky HTML, databáze serveru SQL Server a další zdroje dat. V relačním diagramu můžete vybrat prvky a vyhledat o nich více informací a přetažením spojovacích čar změnit tvar diagramu.

Tento diagram ukazuje aktuální sešit vlevo a spojení mezi ním a dalšími sešity a zdroji dat. Zobrazuje také další úrovně připojení sešitu a poskytuje vám obrázek o původu dat sešitu.

Zobrazit odkazy na listy

Máte spoustu pracovních listů, které na sobě závisí? Použijte k vytvoření interaktivní grafické mapy spojů (odkazů) mezi listy ve stejném sešitu i v jiných sešitech. To vám pomůže získat jasnější představu o tom, jak mohou vaše data záviset na buňkách na jiných místech.

Tento diagram ukazuje vztahy mezi listy ve čtyřech různých sešitech, se závislostmi mezi listy ve stejném sešitu a odkazy mezi listy v různých sešitech. Když umístíte ukazatel nad uzel v diagramu, například list s názvem „Západ“ v diagramu, zobrazí se bublina obsahující informace.

Zobrazit vztahy buněk

Chcete-li získat podrobný interaktivní diagram všech odkazů z vybrané buňky na buňky v jiných pracovních listech nebo dokonce v jiných sešitech, použijte Vztah buněk nářadí. Tyto vztahy s jinými buňkami mohou existovat ve vzorcích nebo odkazech na pojmenované rozsahy. Schéma může procházet listy a sešity.

Tento diagram ukazuje dvě úrovně buněčných vztahů pro buňku A10 na listu 5 v Book1.xlsx. Tato buňka je závislá na buňce C6 na listu 1 v jiném sešitu Book2.xlsx. Tato buňka je precedensem pro několik buněk na jiných pracovních listech ve stejném souboru.

Další informace o prohlížení vztahů buněk najdete v článku Viz odkazy mezi buňkami.

Vyčistěte přebytečné formátování buněk

Otevřeli jste někdy sešit a zjistili, že se načítá pomalu, nebo se stal obrovským? Může být použito formátování na řádky nebo sloupce, které si nejste vědomi. Použijte Vyčistěte přebytečné formátování buněk příkaz k odstranění přebytečného formátování a výraznému zmenšení velikosti souboru. To vám pomůže vyhnout se „nafouknutí tabulky“, což zvyšuje rychlost aplikace Excel.

Spravujte hesla

Pokud používáte funkce Inquire k analýze nebo porovnání sešitů chráněných heslem, budete muset přidat heslo sešitu do svého seznamu hesel, aby mohla aplikace Inquire otevřít uloženou kopii sešitu. Použijte Hesla sešitu příkaz na Poptat záložka pro přidání hesel, která budou uložena ve vašem počítači. Tato hesla jsou šifrovaná a přístupná pouze vy.

Článek poskytuje odpovědi na následující otázky:

Jak porovnat dvě tabulky v aplikaci Excel?
Jak porovnávat složité tabulky v aplikaci Excel?
Jak porovnávat tabulky v aplikaci Excel pomocí funkce VLOOKUP ()?
Jak generovat jedinečné identifikátory řádků, pokud je jejich jedinečnost původně určena sadou hodnot v několika sloupcích?
Jak zmrazit hodnoty buněk ve vzorcích při kopírování vzorců?

Při práci s velkým množstvím informací může být uživatel konfrontován s úkolem, jako je porovnání dvou tabulkových zdrojů dat. Při ukládání dat v jediném účetním systému (například systémech založených na 1C Enterprise, systémech využívajících databáze SQL) lze k porovnání dat využít funkce zabudované do systému nebo DBMS. Zpravidla k tomu stačí zapojit programátora, který zapíše dotaz do databáze nebo mechanismus softwarového hlášení. Zkušený uživatel, který umí psát dotazy 1C nebo SQL, může také zpracovat dotaz.

Problémy začínají, když potřebujete urgentně provést úlohu porovnání dat, a najímání programátora a jeho včasné napsání požadavku nebo zprávy o programu může překročit termíny stanovené pro vyřešení úlohy. Dalším stejně častým problémem je potřeba porovnávat informace z různých zdrojů. V tomto případě bude prohlášení o problému pro programátora znít jako integrace dvou systémů. Řešení takového úkolu bude vyžadovat vyšší kvalifikaci programátora a bude také trvat déle než vývoj v jediném systému.

K vyřešení uvedených problémů je ideální použít k porovnání dat tabulkový editor Microsoft Excel. Většina běžných systémů pro správu a regulační účetnictví podporuje export do formátu Excel. Tento úkol bude pro práci s tímto kancelářským balíkem vyžadovat pouze určitou kvalifikaci uživatele a nebude vyžadovat programátorské dovednosti.

Zvažme řešení problému porovnání tabulek v aplikaci Excel pomocí příkladu. Máme dvě tabulky obsahující výpisy bytů. Zdroje vykládky - 1C Enterprise (stavební účetnictví) a tabulka v Excelu (prodejní účetnictví). Tabulky jsou umístěny v sešitu aplikace Excel na prvním a druhém listu.

Naším úkolem je porovnat tyto seznamy podle adresy. První tabulka obsahuje všechny byty v domě. Druhá tabulka obsahuje pouze prodané byty a jméno kupujícího. Konečným cílem je zobrazit jméno kupujícího v první tabulce pro každý byt (pro ty byty, které byly prodány). Úkol komplikuje skutečnost, že adresa bytu v každé tabulce je konstrukční a skládá se z několika polí: 1) adresa budovy (domu), 2) sekce (vchod), 3) patro, 4) číslo na podlaze (například od 1 do 4) ...

Chcete-li porovnat dvě tabulky aplikace Excel, musíme zajistit, aby byl každý řádek v obou tabulkách identifikován jedním polem, nikoli čtyřmi. Takové pole můžete získat zřetězením hodnot čtyř polí adresy pomocí funkce Concatenate (). Účelem funkce Concatenate () je spojit několik textových hodnot do jednoho řádku. Hodnoty ve funkci jsou vyčísleny prostřednictvím symbolu „;“. Hodnotami mohou být adresy buněk nebo libovolný text uvedený v uvozovkách.

Krok 1. Vložme prázdný sloupec „A“ na začátek první tabulky a zapíšeme vzorec do buňky tohoto sloupce naproti prvnímu řádku s údaji:
= KONCATENÁT (B3; "-"; C3; "-"; D3; "-"; E3)
Pro usnadnění vizuálního vnímání jsme mezi hodnotami buněk, které mají být sloučeny, nastavili symboly „-“.

Krok 2. Zkopírujte vzorec do následujících buněk ve sloupci A.

Krok 4. Chcete-li porovnat tabulky aplikace Excel podle hodnot, použijte funkci VLOOKUP (). Účelem funkce VLOOKUP () je najít hodnotu ve sloupci úplně vlevo tabulky a vrátit hodnotu buňky umístěné v zadaném sloupci stejného řádku. První parametr je požadovaná hodnota. Druhým parametrem je tabulka, ve které bude hodnota prohledána. Třetím parametrem je číslo sloupce, ze kterého bude vrácena hodnota v nalezeném řádku. Čtvrtý parametr je typ vyhledávání: false je přesná shoda, true je přibližná shoda. Vzhledem k tomu, že výstupní informace by měly být umístěny v první tabulce (do této tabulky bylo třeba přidat jména kupujících), zapíšeme do ní vzorec. Vytvořme vzorec ve volném sloupci napravo od tabulky naproti prvnímu datovému řádku:
= VLOOKUP (A3; List2! $ A $ 3: $ F $ 10; 6; FALSE)
Při kopírování vzorců inteligentní Excel automaticky změní adresování buněk. V našem případě se změní požadovaná hodnota pro každý řádek: A3, A4 atd. A adresa tabulky, ve které probíhá vyhledávání, musí zůstat nezměněna. Chcete-li to provést, opravte buňky v parametru adresy tabulky symboly „$“. Místo „Sheet2! A3: F10“ vytvoříme „Sheet2! $ A $ 3: $ F $ 10“.

Po instalaci doplňku budete mít novou kartu s příkazem pro volání funkce. Když kliknete na příkaz Porovnání rozsahů zobrazí se dialogové okno pro zadání parametrů.

Toto makro umožňuje porovnávat tabulky libovolné velikosti a s libovolným počtem sloupců. Porovnání tabulek lze provést po jednom, dvou nebo třech sloupcích.

Dialogové okno je rozděleno na dvě části: levá pro první tabulku a pravá pro druhou.

Chcete-li porovnat tabulky, postupujte takto:

Určete rozsahy tabulek.
Pokud tabulka obsahuje záhlaví (řádek záhlaví), zaškrtněte políčko (zaškrtávací políčko / zaškrtávací políčko) pod vybraným rozsahem tabulek.
Vyberte sloupce levé a pravé tabulky, podle kterých bude provedeno porovnání (pokud rozsahy tabulek neobsahují záhlaví, budou sloupce očíslovány).
Určete typ srovnání.
Vyberte možnost pro zobrazení výsledků.

Srovnávací typ tabulek

Program umožňuje vybrat několik typů srovnání tabulek:

Najděte řádky z jedné tabulky, které se nenacházejí v jiné tabulce

Pokud je vybrán tento typ porovnání, program vyhledá řádky z jedné tabulky, které chybí v jiné. Pokud srovnáváte tabulky podle několika sloupců, pak výsledkem práce budou řádky, ve kterých je rozdíl alespoň v jednom ze sloupců.

Najděte odpovídající řádky

Při výběru tohoto typu porovnání program najde řádky, které se shodují v první a druhé tabulce. Řádky se považují za shodné, pokud se hodnoty ve vybraných srovnávacích sloupcích (1, 2, 3) jedné tabulky zcela shodují s hodnotami sloupců druhé tabulky.

Příklad práce programu v tomto režimu je uveden na obrázku vpravo.

Porovnávací tabulky na základě vybraných

V tomto porovnávacím režimu se naproti každému řádku první tabulky (vybrané jako hlavní) zkopírují data shodného řádku druhé tabulky. Pokud neexistují žádné odpovídající řádky, zůstane řádek před hlavní tabulkou prázdný.