Sloučení dvou různých tabulek podle klíče

Aktualizováno: 20. 11. 2019, datum vydání: 17. 3. 2012

Problém – sjednocení různorodých tabulek

Narazila jsem na jeden problém z praxe. Myslím, že může jít o dosti obvyklou věc a její řešení není až tak triviální.

Máme dvě tabulky a potřebujeme je dát dohromady. Bohužel každou z tabulek dělal někdo jiný, a to samozřejmě zcela po svém. Obě tabulky však obsahují identifikátor záznamu (jedinečný klíč). Programátoři tomuto klíči obvykle říkají Id. Id může mít podobu unikátního čísla či jiného identifikátoru (např. rodného čísla, ISBN knížky apod.). Charakteristikou identifikátoru je, že jednoznačně identifikuje danou věc. Budeme zde předpokládat, že v jednotlivých tabulkách se daný klíč nachází právě jednou (neopakuje se).

Tabulky mohou vypadat například takto:

Tabulka Praha

Tabulka Brno

Tyto tabulky jsou již upravené tak, aby měly shodné sloupce. Pokud by shodné sloupce neměly, tak by se na shodné sloupce nejprve upravily.

Cíl – dát tabulky dohromady

Cílem je dát tyto různorodé tabulky dohromady (právě pomocí společného klíče) a vytvořit z nich jednu velkou tabulku. Chci, aby byly zahrnuty veškeré údaje z obou tabulek a veškeré identifikátory (klíče). Tam, kde se údaje překrývají (jsou uvedeny v obou tabulkách), tak se použijí údaje z jedné tabulky, která se předem zvolí. Tam, kde bude mít smysl údaje sečíst z obou tabulek, tak se sečtou. Výsledkem bude jedna tabulka zahrnující veškeré dostupné údaje.

Prakticky jde o sjednocení tabulky A s tabulkou B s tím, že některé „prvky“ mohou být společné, jiné nikoli. Matematicky vyjádřeno tabulka A a tabulka B mají společný průnik. Existují však data, která jsou uvedena pouze v tabulce A, a jiná data jsou pouze v tabulce B. Tabulky mohou mít dokonce i různou strukturu (sloupce). Některé sloupce mohou mít společné.

Takto by měl například vypadat výsledek sjednocení:

Sloučení tabulek

Obecně – v čem je průšvih

Ani jedna tabulka není kompletní

Průšvih je, že ani jedna tabulka není kompletní, tj. neobsahuje veškeré identifikátory. Každá tabulka obsahuje nějaké identifikátory a údaje k nim. Dokonce i údaje se mohou lišit či někde chybět. Tabulky mohou mít různou strukturu záznamů a některé záznamy společné, z hlediska obou tabulek duplikátní. Obě tabulky spojuje pouze to, že používají shodný společný klíč.

Protože ani jedna tabulka neobsahuje veškeré údaje spojené s identifikátorem, nelze použít jednoduše funkci SVYHLEDAT a z jedné tabulky se odkazovat do druhé s jistotou, že tam patřičný záznam najdu. Prakticky bychom potřebovali nějak propojit tabulku A na tabulku B a zase naopak tabulku B na tabulku A. V každé tabulce máme totiž pouze něco a nikde není všechno… A toto platí nejenom o údajích, ale i o klíčích!

Správný návrh databáze zde chybí

Chtěla bych zde poznamenat, že toto není záležitost, která by se řešila běžně v databázích. Není to vůbec situace, se kterou by Excel příliš počítal. Proč taky – takto by práce s tabulkami prostě vypadat neměla. Špatný návrh = špatná práce se všemi důsledky! Užíte si to :). Věci se mají navrhovat a dělat správně. S chybným postupem Excel, potažmo databáze, prostě nepočítají. A to je správně. I program samotný (Excel) by Vás měl automaticky motivovat dělat věci správně a nikoli Vám dávat k dispozici nástroje, abyste to od už začátku mohli dělat chybně. Bohužel Excel samotný databáze není. Excel je jen souhrn trochu chytřejších tabulek.

Databáze bývají, pokud jde o strukturu tabulek, správně a jednotně navrženy databázovým analytikem. Uživatelé pak pracují s jedinou databází. Následné propojení tabulek pomocí SQL dotazů obvykle není žádný problém. Je to většinou záležitost jednoho napsaného řádku.

V našem případě je problém v tom, že naopak správný návrh databáze zcela chybí. Tabulky udělali lidé, kteří databázím jako takovým nerozumí, a každý si je vytvořil tak jak zrovna on potřeboval. Prostě praxe. A teď to dej člověče – informatiku – dohromady.

Pokud by šlo o správný návrh databáze, tak by údaje vztahující se k danému klíči byly extra v nějaké tabulce. Tato tabulka by obsahovala veškeré existující identifikátory a údaje k nim. Ostatní tabulky, pokud by chtěly sdílet některé údaje z tabulky identifikátorů, tak by se do ní následně odkazovaly pomocí cizích klíčů. Simulaci propojení tabulek lze udělat v Excelu pomocí funkce SVYHLEDAT, nebo ještě lépe v opravdové databázi (třeba Access) formou SQL dotazů.

Bohužel běžný uživatel mnohdy o správném návrhu databáze nemá ani ponětí, takže není schopen si správně tabulky navrhnout. Další problém může být, že nejde o opravdickou databázi, ale pouze o soubor tabulek, které spravují různí uživatelé, jak se jim v Excelu zlíbí. Následná práce s takovými tabulkami je pak obtížná.

Řešení příkladu

Úprava tabulek na shodnou strukturu sloupců

Pokud by tabulky obsahovaly různé sloupečky, tak sloupce, o které nemáme zájem smažeme a další sloupce přidáme tak, aby tabulky vykazovaly shodnou strukturu, ačkoli některé sloupce zůstanou vlivem toho naprosto prázdné. Příklad počítá se shodnou strukturou dat a některé operace (například sloučení dat) by bez shodné struktury sloupců nešly vůbec provést. Věřím, že tuto úpravu byste zvládli, takže já rovnou začínám s již upravenými tabulkami.

Tabulka A (list Praha) obsahuje sloupečky: ISBN (klíč), Kniha, Autor, Cena, Počet kusů.

Tabulka B (list Brno) obsahuje sloupečky: ISBN (klíč), Kniha, Autor, Cena, Počet kusů.

Klíčem v obou tabulkách je ISBN. Barevně zvýrazněné položky se vyskytují v obou tabulkách. Hlavním cílem je získat sečtený počet kusů z obou tabulek. Ideálem však je sjednotit data tak, abychom měli zároveň k dispozici všechny údaje k danému ISBN a dále všechny kusy.

Název oblasti

Já jsem si oblast, se kterou budu pracovat pojmenovala, abych se na ni mohla odkazovat ve vzorečcích názvem. Vyberete oblast buněk a do pole adresy vepíšete název. Knihy z Prahy jsem si pojmenovala KPraha a knihy z Brna jsem si pojmenovala KBrno. Jde o absolutní adresaci a lze pak používat místo $A$2:$E$6 název KPraha.

Napadlo mne 5 různých řešení:

  1. Sloučení tabulek pomocí funkce SVYHLEDAT – nejtěžší, ale asi nejhezčí
  2. Souhrn
  3. Sloučení dat
  4. Kontingenční tabulka – rychlé a jednoduché pro toho, kdo zná KT
  5. Makro ve Visual Basic – pro programátory, univerzální

1. Sloučení tabulek pomocí funkce SVYHLEDAT

Kopie unikátních klíčů z obou tabulek

Vytvoříme nový list a zkopírujeme do něj veškerá ISBN (klíče) z první i druhé tabulky. Kopírované údaje dáme pod sebe. Některá ISBN se budou opakovat, ale to nevadí, my je pak vyloučíme příkazem Data/Datové nástroje/Odebrat duplicity. Tím máme soupis všech ISBN a neopakují se nám. Sloupečky nazveme stejně jako u původních tabulek, aby byla zachována stejná struktura tabulek.

Tvorba vzorce pro přenesení textové informace

A nyní vytvoříme vrorečky pro zjištění hodnot z obou tabulek.

Vzorečky pro vyplnění textových hodnot

SVYHLEDAT

Vzorečky vypadají šíleně, ale zas tak šílené to není. Funkce SVYHLEDAT se snaží podle klíče najít údaj (např. knihu) v tabulce Praha. Pokud ji nenajde, tak se koukneme do tabulky Brno a vrátíme knihu z tabulky Brno.

Funkce SVYHLEDAT je dosti klíčovou funkcí v Excelu a simuluje propojení tabulek. Obsahuje tři parametry. První parametr je identifikátor (klíč) – u nás ISBN. Jde vlastně o údaj, který budu hledat v jiné tabulce. Pak následuje druhý parametr, a to je zadání oblasti kde se bude vyhledávat – u nás např. tabulka Praha, konkrétně oblast KPraha. A třetí parametr je sloupec, který nám funkce vrátí. Vlastně funkce vrátí hodnotu nalezenou na řádku klíče v zadaném sloupci. Pokud budeme chtít vrátit název knihy, půjde o sloupec číslo 2. Čtvrtý parametr je nepovinný, ale je zapotřebí ho vyplnit na NEPRAVDA, já používám 0. Pokud by se nechal nevyplněný, tak se bude hledat i přibližná hodnota a funkce požaduje předem setříděné údaje. Prostě by se Vám mohly vracet nesmysly. Pokud chcete přesnou hodnotu, tj. aby se našlo právě to vaše ISBN a nic jiného, tak je zapotřebí tam dát do čtvrtého parametru 0!

SLOUPEC

Při vyplnění parametrů funkce SVYHLEDAT jsem využila funkci SLOUPEC, která vrátí číslo aktuálního sloupce. Strukturu tabulek máme shodnou, proto při použití funkce SLOUPEC mohu lépe kopírovat vzoreček i do dalších sloupečků, aniž by se vzorec narušil.

Relativní a absolutní adresace $A2

Ze stejného důvodu je použita adresace na ISBN ve formě $A2. $ je u A u 2 nikoli, což má za následek, že při posunu po řádcích se posouvá i odkaz, při posunu vzorce po sloupcích se sloupec nemění. Proto i při kopii vzorce směrem dolů či do stran zůstává odkaz na ISBN v pořádku.

Klíč se nenajde – #NENÍ_K_DISPOZICI, IFERROR

Samotná funkce SVYHLEDAT by byla celkem v pohodě. Průšvih však nastává v momentě, kdy se klíč nenajde. SVYHLEDAT pak vrátí chybovou hodnotu #NENÍ_K_DISPOZICI. Proto je výsledek hledání otestován funkcí IFERROR, která v sobě automaticky zahrnuje testování typu =KDYŽ(JE.CHYBHODN(… Takže když tabulka z Prahy vrátí chybnou hodnotu (např. ISBN nebylo nalezeno), tak se bude hledat v tabulce Brno. To by ještě šlo…

Najde se ISBN (klíč), ale údaj samotný není vyplněn

Další průšvih však byl, že já chci počítat s tím, že tabulka Praha ani Brno nemusí být kompletní, ačkoli se tam dané ISBN vyskytuje. Takže např. v případě, že se ISBN sice v tabulce Praha najde, ale název knihy nebude vyplněn, tak chci, aby i v tomto případě se funkce SVYHLEDAT koukla do tabulky Brno a vyčetla údaj o knize z ní. Když ISBN bylo nalezeno, ale údaj nikoli (byl prázdný), tak funkce SVYHLEDAT vracela 0. Obecně vzorce někdy převádějí prázdné hodnoty na 0. Proto je vše ještě obalené jedním velkým KDYŽ. Pro případ, že výsledné hledání v tabulce Praha bude 0, tak se zajistí stejným složitým způsobem hledání v tabulce Brno.

Logické funkce s chybovými hláškami nefungují správně

Vytvářet vzorečky a počítat přitom s případnými chybovými hláškami je dost strašné. Základní logické funkce typu NEBO a A nefungují pak správně, protože jakmile se v testování podmínek chybová hláška objeví, tak je chybně vyhodnocen celý vzorec a chybová hláška je přenesena do celého výsledku logické funkce. Ani zkrácené vyhodnocování výrazů známé z programovacích jazyků, které by toto mohlo zachránit, v Excelu nefunguje. Alespoň se mi nepodařilo dát rozumně dohromady do logických podmínek otestování na chyby či prázdné výrazy. A že by se to tady zrovna hodilo. Něco v tom stylu, jako „když to nenajdeš, nebo je údaj nevyplněn v tabulce Praha, tak pak hledej v tabulce Brno“. Nemůžete se prostě jednoduše zeptat, zda to vrátilo chybu nebo prázdný string. Neříkám, že to nejde, možná by stačilo si více pohrát s chybovými hodnotami, nicméně mne toto chování Excelu dosti odradilo. Proto jsem místo logické funkce NEBO použila funkci KDYŽ.

Údaj nenalezen v žádné tabulce – zobrazí se 0

Výsledný vzorec je již docela komplikovaný. I tak v momentě, kdy to nenajde ani jeden údaj, tak funkce vrátí 0. Toto by šlo také ošetřit, např. další obalenou funkcí KDYŽ. Výsledek by se otestoval a v případně 0 by to vypsalo prázdný string. Bylo by to však dosti nepřehledné (vzorec by byl pak skoro 2× delší), tak jsem toto ošetření do vzorce již nepřidávala.

Visual Basic by byl asi vhodnější

Myslím, že zde by bylo na místě šáhnout po Visual Basicu a tvorbu tabulky raději přímo naprogramovat, než se doslova patlat se vzorečky a jejich chybovými hláškami. Ve Visual Basicu si můžete do proměnné uložit výsledek, přehledně sestavit podmínky apod. Nejste nuceni opakovat zbytečně kód.

Tvorba vzorce pro součet počtu kusů

Vzoreček pro součet kusů je již jednodušší. Pouze sečteme nalezené kusy v listu Praha s kusy listu Brno. Pokud s nenajdou, do výpočtu jde 0.

Vzoreček pro součet kusů Prahy a Brna

2. Souhrn

Další možnost, která připadá v úvahu je udělat tzv. souhrn. Vykopírujeme si do samostatného listu veškeré údaje z listu Praha i listu Brna. Aby souhrn správně fungoval, musíme data nejprve seřadit podle kritéria souhrnu. Zde bude kritérium tvorby souhrnu změna ISBN. Proto data setřídíme podle ISBN. Následně dáme volbu Data/Osnova/Souhrn/ a vyplníme dialog Souhrny:

Souhrn

Zde je výsledek souhrnu:

Souhrn

3. Sloučení dat

Excel obsahuje funkci pro sloučení dat. Vytvoříte nový list. Kurzorem se umístíte nejlépe do buňky A1. Vyberete příkaz: Data/Datové nástroje/Sloučit. Vyberete volbou Procházet oblasti dat včetně záhlaví z listů Prahy a Brna a tlačítkem Přidat je přidáte do seznamu. Zaškrtnete Použít popisky v horním řádku a levém sloupci. Můžete nechat propojit vytvořené sloučení na zdrojová data. Pak se při změně zdrojových dat přepočítá i vzniklé sloučení. Úplně nahoře se vybírá funkce, která je použita při sloučení buněk. Zde vybereme Součet, protože budeme chtít sečíst kusy pro dané ISBN.

Sloučit dialog

Zde je výsledek sloučení dat:

Sloučení dat

Bohužel při sloučení dat nelze přenést textová pole (kromě záhlaví). Alespoň se mi to nijak nepodařilo. Daná funkce funguje víceméně jako trojrozměrná suma či jiný vybraný vzorec. Automaticky Vám vygeneruje unikátní seznam ISBN a poradí si tímto způsobem sama s různorodými záznamy a jejich duplikáty. Sloučená data musí mít shodnou strukturu sloupců! Bohužel lze nastavit pouze jeden vzorec pro všechna data. Takže zde se například sečetly i ceny, což je chyba.

4. Kontingenční tabulka

Na samostatný list si nakopírujete pod sebe data z Prahy i Brna včetně společného záhlaví. Následně k takto vzniklým datům vložíte kontingenční tabulku příkazem: Vložení/Tabulky/Kontingenční tabulka. Umístíte ji buď na samostatný list, nebo na stávající list.

Kontingenční tabulka

Kontingenční tabulku si upravíte, jak potřebujete. Do oblastí Popisky sloupců, Popisky řádků, Hodnoty si přetaháte ze seznamu polí taková pole, která tam chcete mít. V oblasti Hodnoty si doupravíte nastavení tak, aby například Excel sčítal dané buňky, a upravíte jejich formát - volba Nastavení polí hodnot

5. Makro ve Visual Basic

Pokud Vám však kontingenční tabulka, souhrny, sloučení dat či tabulka vytvořená pomocí vzorců nestačí, je zde možnost naprogramovat si vygenerování tabulky dle svých představ. Programování je však spíše pro odborníky. Nicméně pro případ sloučení dvou různých tabulek a pro ošetření různých chyb mi tato volba nakonec připadá snazší, než složité tvoření a vnořování různých vzorců. Až bude čas, tak tvorba makra bude předmětem dalšího samostatného článku pro Visual Basic.

Zdrojový soubor

xlsx knihy.xlsx

Případné spojení více tabulek (A, B, C…)

Kdybyste potřebovali spojit více tabulek, tak v případě tabulky se vzorci by šlo postup donekonečna opakovat. Např. spojíte tabulku A s tabulkou B. Výsledné spojení následně spojíte s tabulkou C atd. V ostatních případech byste pracovali s více listy zároveň.

Další články