V rámci mého zaměstnání na plný úvazek před několika lety byla jednou z věcí, které jsem musel řešit, kombinovat data z různých pracovních sešitů sdílených jinými lidmi.
A jedním z běžných úkolů bylo kombinovat data tak, aby neexistovaly žádné duplicitní záznamy.
Níže je například datová sada, která více záznamů pro stejnou oblast.
A konečným výsledkem musí být konsolidovaný soubor dat, kde je každá země vykazována pouze jednou.
V tomto tutoriálu vám ukážu, jak zkombinovat duplicitní řádky a sečíst hodnoty a vytvořit tak jedinou konsolidovanou datovou sadu.
Kombinujte a sumujte data pomocí možnosti Konsolidovat
Pokud vše, co musíte udělat, je konsolidovat data a přidat všechny hodnoty pro opakující se záznamy, je nejlepší použít funkci konsolidace v aplikaci Excel.
Druhou metodou je použít kontingenční tabulku a shrnout data (popsaná dále v tomto kurzu).
Předpokládejme, že máte sadu dat uvedenou níže, kde se název země opakuje několikrát.
Přestože se jedná o jedinečné záznamy, protože prodejní hodnota se liší, pro účely vykazování můžete chtít odebrat více instancí stejné země a zobrazit prodejní hodnotu jako jeden konsolidovaný součet.
Níže jsou uvedeny kroky, jak to provést:
- Zkopírujte záhlaví původních dat a vložte je tam, kam chcete konsolidovaná data
- Vyberte buňku pod záhlavím úplně vlevo
- Klikněte na kartu Data
- Ve skupině Datové nástroje klikněte na ikonu Konsolidovat
- V dialogovém okně Konsolidovat vyberte z rozevíracího seznamu funkcí Součet (pokud již není ve výchozím nastavení vybráno)
- Klikněte na ikonu výběru rozsahu v poli Reference.
- Vyberte rozsah A2: B9 (data bez záhlaví)
- Zaškrtněte políčko Levý sloupec
- Klikněte na Ok
Výše uvedené kroky by konsolidovaly data odstraněním duplicitních záznamů a přidáním hodnot pro každou zemi.
Nakonec získáte jedinečný seznam zemí spolu s prodejní hodnotou z původní sady dat.
Rozhodl jsem se získat SUM hodnot z každého záznamu. Můžete také zvolit další možnosti, jako je počet nebo průměr nebo max./Min.
V tomto příkladu jsem vám ukázal, jak konsolidovat data do jedné sady dat v listu. Tuto funkci můžete také použít ke konsolidaci dat z více listů ve stejném sešitu a dokonce i z několika různých sešitů.
Kombinujte a sumujte data pomocí kontingenčních tabulek
Pivot Table je švýcarský armádní nůž na krájení a krájení dat v Excelu.
Může vám snadno poskytnout souhrn, který je kombinovanou sadou dat bez duplikátů a hodnot, které jsou součtem všech podobných záznamů, a dokáže mnohem více.
Nevýhodou této metody ve srovnání s předchozí je, že tato metoda vyžaduje více kliknutí a několik dalších sekund ve srovnání s předchozím.
Předpokládejme, že máte sadu dat uvedenou níže, kde se název země opakuje několikrát, a chcete tato data konsolidovat.
Níže jsou uvedeny kroky k vytvoření kontingenční tabulky:
- Vyberte libovolnou buňku v datové sadě
- Klikněte na kartu Vložit
- Ve skupině Tabulky klikněte na možnost Kontingenční tabulka
- V dialogovém okně Vytvořit kontingenční tabulku zkontrolujte, zda je tabulka/rozsah správný
- Klikněte na existující list
- Vyberte umístění, kam chcete vložit výslednou kontingenční tabulku.
- Klikněte na OK
Výše uvedené kroky by vložily kontingenční tabulku do vybrané cílové buňky.
Nyní můžeme s kontingenční tabulkou dělat nejrůznější věci - včetně konsolidace naší sady dat a odstranění duplikátů.
Níže jsou uvedeny kroky, jak to provést:
- Klikněte kamkoli v oblasti kontingenční tabulky a otevře se podokno kontingenční tabulky vpravo
- Přetáhněte pole Země do oblasti Řádek
- Přetáhněte pole Prodej do oblasti Hodnoty
Výše uvedené kroky shrnují data a poskytují součet tržeb za všechny země.
Pokud je to vše, co chcete, a nepotřebujete kontingenční tabulku, můžete data zkopírovat a vložit jako hodnoty někam jinam a kontingenční tabulku odstranit.
To vám také pomůže zmenšit velikost sešitu aplikace Excel.
Jedná se tedy o dvě rychlé a snadné metody, které můžete použít ke konsolidaci dat, kde by kombinovaly duplicitní řádky a součet všech hodnot v těchto záznamech.
Doufám, že jste našli tento návod užitečný!
