Co uděláte, pokud potřebujete vytvořit konsolidovanou tabulku a data na různých listech? S instalovaným Excelem 2013 máte pro to jednoduchý způsob. Existuje technologie nazvaná datový model a používá datové vztahy, jak to dělá databázi.

V této lekci vám ukážu vše pro vytvoření pivotové tabulky v aplikaci Excel 2013 z dat na více listech pomocí datového modelu.

Video

Pokud chcete sledovat lekci pomocí vlastního souboru aplikace Excel, můžete to udělat. Nebo stáhnout soubor zip do této lekce, který obsahuje ukázkovou knihu. Pivot consolidide.xlsx.

Data učení

Tato kniha má tři pracovní listy: informace o klientovi, informace o objednávce a informace o platbě.

Klikněte na list Informace o zákaznících. A ujistěte se, že obsahuje čísla objednávek, stejně jako jméno a stav zákazníků.

Seznam informací zákazníka

Klikněte na list Informace o objednávce. A podívejte se na to Obsahuje objednávky, stejně jako pole pro měsíc, objednané zboží a zda jsou tyto produkty organické.

Objednat informace

Klikněte na kartu Informace o platbě a ujistěte se, že obsahuje objednávky, částku v dolarech každého prodeje, způsob platby a pořadí umístění objednávky s novým nebo stávajícím klientem.

Platba Info Leaf.

Připojením všech těchto listů v oblasti úlohy úlohy můžeme vybrat data z každého listu. Vzhledem k tomu, že čísla objednávek existují na všech třech listech, stanou se spojeními. To je to, co databáze způsobuje pRIMÁRNÍ KLÍČ. Upozornění: Není nutné mít primární klíč, ale snižuje pravděpodobnost chyby.

Vytvořit pojmenované tabulky

Před vytvořením otočného stolu, pojďme vytvořit tabulku z každého listu.

Klepněte na tlačítko Zpět B. Zákaznický stůl, pak klikněte kdekoli v oblasti dat. Klepněte na kartu Vložit. Na páskovém panelu a klepněte na ikonu Stůl.

Převést data na list výběrem Vložit\u003e Tabulka

Dialogové okno Vytvořit tabulka správně určuje oblast tabulky. Zaškrtávací políčko by měl také určit, že první řetězec tabulky je určen pro záhlaví. (Pokud ne, vyberte tuto možnost.)

Dialog Vytvořit tabulka musí správně odhadnout datovou oblast.

Kliknutí OK.A teď máte stůl se střídavými tahy a filtračními tlačítky. Chcete-li odstranit výběr, můžete klepnout na tlačítko Uvnitř, chcete-li jej lépe zvážit (prostě nestiskněte mimo tabulku). Pásková páska také zobrazuje kartu Design. Pro stůl. Na levé straně stuhy v poli Název tabulky. Zobrazí se dočasné jméno tabulky1. Smazat a pojmenujte jej. Customer_info. (Použijte symbol vypnutí místo prostoru). Kliknutí Enter..

Aplikujte jméno ke každé tabulce

Tyto kroky opakujte s informacemi o objednávce a platebních informačních listů. Jméno tabulek Order_info. a Platební info..

Nyní jsme připraveni vložit kontingenční tabulku.

Box kontingenční tabulka.

Ujistěte se, že na adrese Info Plateb, kurzor je někde v tabulce. Návrat na kartu Vložit. Stuhy a klepněte na ikonu Pivotable (Toto je první ikona).

Pomocí kurzoru uvnitř jednoho z tabulek vyberte Vložit\u003e kontingenční tabulka

V zobrazeném dialogovém okně musíte správně definovat tabulku a zvolit, že kontingenční tabulka se přesune do nového pracovního listu. Níže, zaškrtněte políčko Přidejte tato data do datového modelu. Kliknutí OK..

Přidání dat do datového modelu je to, co umožňuje připojení k práci

Nyní budete mít kontingenční tabulku na nový list, hlavní panel bude na pravé straně obrazovky a na pásku se zobrazí karta Analyzovat.

Hlavní panel zobrazuje tabulku a pole aktivního listu, takže stiskněte Všechno.Chcete-li zobrazit veškerou tabulku, kterou jste vytvořili. Ale než je můžeme použít, musíme je navzájem spojit, a to znamená vytváření vztahů. zmáčknout tlačítko Vztahy. Na páskovém panelu.

Nastavení vztahů tabulky

Stisknutím tohoto tlačítka zobrazí dialogové okno Správa vztahů. zmáčknout tlačítko Nový Zobrazí se dialogové okno Vytvořit vztah. Jako konektor vytvoříme dva vztahy.

V rozevíracích seznamech vyberte Platební info. pro tabulku a vedle ní vyberte Objednat # V rozevíracím seznamu sloupců. Ve druhém řádku vyberte Customer_info. z rozevíracího seznamu souvisejícího tabulky a vedle ní vyberte Objednat # Z rozevíracího seznamu Související sloupec.

Existují tři stoly, takže vytvářet dva vztahy.

To znamená, že tabulky plateb_info a customer_info souvisí s náhodou čísla objednávek.

zmáčknout tlačítko OK. A uvidíme tento vztah uvedený v okně Spravovat vztahy.

Tento proces opakujte, abyste vytvořili propojení mezi platebně_info a order_info, také pomocí pole Objednat #. Okno Správa vztahů vypadá takto:

V dialogovém okně Správa vztahů se zobrazí vztahy, které vytvoříte se.

Vezměte prosím na vědomí, že není třeba vytvářet vztahy mezi tabulkami Order_info a Customer_info, protože jsou automaticky připojeny přes tabulku plateb_info.

zmáčknout tlačítko Zavřít. V dolní části okna. Nyní můžeme konečně přetáhnout pole v kontingenční tabulce.

Vložení polí v kontingenční tabulce

V části Všechny na hlavním panelu klepněte na malé šipky, nasazujte tři tabulky, viz jejich pole. Přetáhněte pole v oblasti kontingenční tabulky následujícím způsobem:

• Stát a MĚSÍC. v řetězcích
• Produkt ve sloupci
• Prodej $. V hodnotě
• Postavení. v filtrech

Přetáhněte pole každého ze tří tabulek v kontingenční tabulce

Nyní můžete použít a změnit, stejně jako jakýkoli jiný kontingenční tabulku.

Závěr

Pomocí nového modelu datového modelu objektů v aplikaci Excel 2013 můžete vybrat růžová pole několika listů pro vytvoření jediné kontingenční tabulky. Mějte na paměti, že linie každé tabulky musí být nějakým způsobem spojeny. Máte větší šance na úspěch, když tabulky mají společné pole s jedinečnými hodnotami.

Pokud hledáte dobré způsoby, jak předložit své údaje, má trh Endato má dobrou volbu, stejně jako převést data Excel do webových formátů a naopak.

Z tohoto článku se dozvíte, jak rychle kombinovat data ze dvou tabulek aplikace Excel, pokud nejsou žádné přesné shody v klíčových sloupcích. Například, když je jedinečný identifikátor z první tabulky prvním pěti znaky identifikátoru z druhé tabulky. Všechna řešení nabízená v tomto článku jsou testována mnou v aplikaci Excel 2013, 2010 a 2007.

Existují dva listy aplikace Excel, které je třeba kombinovat pro další analýzu dat. Předpokládejme, že ve stejné tabulce obsahuje ceny (sloupec, cena) a popisy produktu (pivní sloupec), které prodáváte, a ve druhé odrážejí údaje o dostupnosti zboží skladem (sloupec skladem). Pokud jste vy nebo vaši kolegové účtovali oba tabulky podle adresáře, pak alespoň jeden klíčový sloupec s jedinečnými identifikátory by měl být přítomen jak cestou. Popis výrobku nebo ceny se může lišit, ale jedinečný identifikátor zůstává nezměněn.

Potíže začínají, když získáte nějaké tabulky od výrobce nebo z jiných oddělení společnosti. Případ může dokonce komplikovat, pokud je zaveden nový formát jedinečných identifikátorů nebo se změní skladové nomenklatury (SKU). A předtím existuje úkol sloučit v aplikaci Excel nový a starý stůl s daty. Jeden nebo jiný, existuje situace, kdy dojde pouze částečný zápas záznamů v klíčových sloupcích, například " 12345 "A" 12345-New_Suffix.". Jste jasné, že je to stejné SKU, ale počítač není tak vzhůru! To není přesná shodaidence znemožňuje používat obvyklé vzorce aplikace Excel kombinovat data ze dvou tabulek.

A to je velmi špatné - dodržování nesmí být vůbec a "\\ t Nějaká společnost"V jedné tabulce se může proměnit" CJSC "některá společnost""V jiné tabulce a" Nová společnost (bývalá společnost)"A" Stará společnost"Také bude záznam stejné společnosti. Je vám známo, ale jak vysvětlit Excel?

Komentář: Řešení popsaná v tomto článku jsou univerzální. Můžete je přizpůsobit pro další použití s \u200b\u200bjakýmkoliv standardním vzorcem, například Prsa (Vlookup), Vyhledávací deska (Zápas), Gpr (Hlookup) a tak dále.

Vyberte příslušný příklad, který chcete okamžitě přejdete na požadované řešení:

Klíčový sloupec v jednom z tabulek obsahuje další znaky.

Zvažte dva tabulky. První sloupce tabulky obsahují číslo nomenklatury (SKU), název piva (pivo) a její cena (cena). Druhá tabulka zaznamenala SKU a počet lahví ve skladu (skladem). Místo piva může být jakýkoliv produkt, a počet sloupců v reálném životě může být mnohem více.

V tabulce s dalšími symboly vytvoří pomocný sloupec. Můžete ji přidat na konec tabulky, ale je nejlepší vložit ji vedle vpravo po sloupci klíče, který má být v dohledu.

Klíč v tabulce v našem příkladu je sloupec A. S daty SKU a musíte od něj odstranit prvních 5 znaků. Přidat pomocný sloupec a zavolejte SKU HELPER.:

Připraven! Nyní máme klíčové sloupce s přesnou náhodou hodnot - sloupec SKU HELPER. V hlavní tabulce a sloupci SKU. V tabulce, kde bude vyhledávání provedeno.

Nyní s funkcí Prsa (Vlookup) Dostaneme požadovaný výsledek:

Jiné vzorce

• Extrahovat jako první H. Symboly vpravo: například 6 znaků vpravo od položky "DSFH-164900". Vzorec bude vypadat takto:

  Práce (A2; 6)
  \u003d Vpravo (A2,6)

• Přeskočit první H. Znaky extrahují následující Y. Symboly: Například musíte odebrat "0123" ze záznamu "prefix_0123_suff". Zde musíme přeskočit prvních 8 znaků a extrahovat následující 4 znaky. Vzorec bude vypadat takto:

  Pstr (A2; 8; 4)
  \u003d Střední (A2,8,4)

• Před oddělovačem extrahuje všechny znaky, délka výsledné sekvence může být odlišná. Například musíte extrahovat "123456" a "0123" ze záznamů "123456-příponu" a "0123-příponu", resp. Vzorec bude vypadat takto:

  Levsimv (A2; Najít ("-"; A2) -1)
  \u003d Vlevo (A2, najít ("-", A2) -1)

Stručně řečeno, můžete použít Excel funkce jako Levsimv. (Vlevo, odjet), Prdeseim (Že jo) Pst. (STŘEDNÍ), NAJÍT (Najít) extrahovat všechny části kompozitního indexu. Pokud by byly potíže vzniknout - budeme dělat to nejlepší, abychom vám pomohli.

Data ze sloupce klíčů v první tabulce jsou rozděleny do dvou nebo více sloupců ve druhé tabulce.

Předpokládejme, že tabulka, ve které se vyhledávání provádí, obsahuje sloupec s identifikátory. V buňkách tohoto sloupce obsahují Xxxx-yyyy.kde Xxxx. - Jedná se o označení kódu skupiny zboží (mobilní telefony, televize, videokamery, kamery) a Yyyy. - Toto je kód zboží uvnitř skupiny. Hlavní tabulka se skládá ze dvou sloupců: V jednom obsahuje commoditní skupiny (skupiny), sekundy jsou zaznamenány ve druhém (ID). Nemůžeme jednoduše zlikvidovat kódy skupin produktů, protože stejný kód produktu lze opakovat v různých skupinách.

V buňce. C2. Píšeme takový vzorec:

Úlovek (A2; "-"; B2)
\u003d Konatenatěr (A2, "-", B2)

Tady A2. - Jedná se o adresu buňky obsahující kód skupiny; symbol " – "Je separátor; B2. - Jedná se o buněčnou adresu obsahující kód produktu. Zkopírujte vzorec do zbývajících čar.

Nyní kombinovat data z našich dvou stolů nebudou obtížné. Porovnáme sloupec Plné ID. První tabulka s sloupcem ID Druhý stůl. Když je zjištěna náhoda, nahrávání ze sloupců Popis. a Cena Druhá tabulka bude přidána do první tabulky.

Data v klíčových sloupcích se neshodují

Zde je příklad: Jste vlastníkem malého obchodu, získejte zboží z jednoho nebo více dodavatelů. Každý z nich má vlastní nomenklaturu, která se liší od vás. V důsledku toho existují situace, kdy záznam "Case-IP4S-01" odpovídá vstupu "SPK-A1403" v souboru Excel přijaté od dodavatele. Takové nesrovnalosti se vyskytují náhodně a neexistuje žádné společné pravidlo automaticky převést "SPK-A1403" v "Case-IP4S-01".

Špatné zprávy: Data obsažená v těchto dvou tabulkách aplikace Excel budou muset ručně zpracovávat, takže v budoucnu je je možné kombinovat.

Dobré zprávy: Bude muset udělat pouze jednou a výsledný pomocný stůl lze uložit pro další použití. Dále můžete tyto tabulky automaticky kombinovat a ušetřit čas

1. Vytvořte dceřiné tabulky pro vyhledávání.

Vytvořte nový list aplikace Excel a zavolejte SKU konvertor.. Zkopírujte celý sloupec Naše.sky. Z listu Obchod. Pro nový list odstraníme duplikáty a necháme v něm pouze unikátní hodnoty.

Nedaleko Přidat sloupec Supp.sky. a ručně hledají shodu mezi hodnotami sloupců Naše.sky. a Supp.sky. (To nám pomůže popisy z sloupce Popis.). Toto je nudný dělník, nechte myšlenku, kterou musíte splnit pouze jednou :-).

V důsledku toho máme takový stůl:

2. Aktualizujeme hlavní tabulku pomocí dat z tabulky pro vyhledávání.

Do hlavní tabulky (Skládání listů) vložte nový sloupec Supp.sky..

Dále s funkcí Prsa (Vlookup) Porovnejte listy Obchod. a SKU konvertor. Naše.sky.a pro aktualizované data - sloupec Supp.sky..

Sloupec Supp.sky. Naplněné původními kódy výrobce.

Komentář: Pokud ve sloupci Supp.sky. Byly tam prázdné buňky, pak musíte vzít všechny kódy SKU.Odpovídající těmto prázdným buňkám je přidejte do tabulky SKU konvertor. a najít příslušný kód ze stolu dodavatele. Poté opakujte krok 2.

3. Přeneste data z vyhledávací tabulky do hlavní tabulky

V našem hlavním stole je klíčový sloupec s přesnou shodou okolností s prvky vyhledávací tabulky, takže tento úkol nebude způsobit potíže

Pomocí funkce Prsa (Vlookup) kombinovat data listů Obchod. S datovými daty Velkoobchodní dodavatel 1.Pomocí sloupce pro vyhledávání shody Supp.sky..

Zde je příklad aktualizovaných dat ve sloupci Velkoobchodní cena:

Všechno je jednoduché, že? Zeptejte se na své otázky v připomínkách k článku, pokusím se odpovědět co nejdříve.

Konsolidované tabulky jsou vhodné použít data a vytvářet sestavy k analýze dat. A pokud tato relační data (tj. Ty, které jsou uloženy v jednotlivých tabulkách, ale zároveň mohou být kombinovány v důsledku obecných hodnot), můžete vytvořit takovou konsolidovanou tabulku během několika minut:

Co je odlišný tento pivotový stůl? Upozorňujeme, že v seznamu polí vpravo není zobrazena jedna tabulka, ale celá sada tabulek. Každý z těchto tabulek obsahuje pole, která lze kombinovat do jedné konsolidované tabulky pro získání různých datových sekcí. Není vyžadováno žádné ruční formátování a příprava dat. Ihned po importu dat můžete vytvořit souhrnnou tabulku založenou na příbuzných tabulkách.

Kombinovat více tabulek v seznamu polí pivotových tabulek:

Níže je postup pro importu více tabulek z databáze SQL Server.

Poznámka: Seznam polí obsahuje několik tabulek. Jedná se o všechny tabulky vybrané vás během importu. Každá tabulka lze nasadit a zhroutit pro zobrazení jeho polí. Vzhledem k tomu, že jsou připojeny tabulky, můžete vytvořit konsolidovanou tabulku, když hodil pole z jakékoli tabulky do oblasti Hodnoty, Řetězce nebo Sloupce.

Importovat tabulky z jiných zdrojů

Kromě SQL Serveru můžete importovat tabulky z řady dalších relačních databází.

Relační databáze nejsou jediným zdrojem dat, který podporuje operaci s více tabulkami v seznamu polí pivotových tabulek. Můžete použít tabulky v knihách nebo importních datových kanálech a poté je integrovat s jinými datovými tabulkami v knize. Aby se všechna tato nespříznutá data spolupracovala společně, je každá tabulka přidána do modelu dat a potom vytvořte odkazy mezi nimi pomocí odpovídajících hodnot pole.

Pomocí datového modelu pro vytvoření nového otočného stolu

Můžete vytvořit odkazy mezi tabulkami v datovém modelu a jsou nyní připraveny k použití těchto dat pro analýzu. Následuje následující, jak vytvořit novou souhrnnou tabulku nebo souhrnný diagram pomocí modelu dat v knize.

Velmi často, při generování přehledů (analytiků) velkých částek dat, musíte řídit informace z několika listů knihy "Excel" do jedné tabulky.
V ideálním případě pro sběr a analytics informací z různých listů "Excel" je vhodné provést souhrnnou tabulku, která bude mít data z několika zdrojů (knižní listy).

Taková tabulka může být prováděna dvěma různými způsoby.

Zvážit první cestu.

Vytvoření pivotové tabulky z různých listů pomocí standardních funkcí a nástrojů.

Krok první.

Musíte přidat tlačítko na nástroj nástroje na nástroj nástroje a grafy na pásku.

Chcete-li to provést, klepněte pravým tlačítkem myši na kazetu (panely nástrojů) a z rozevírací nabídky vyberte "Nastavení pásky"

zadejte záložku

"Soubor" \u003d\u003e "Parametry" \u003d\u003e "Nastavení pásky".

Dále v nastavení pásky z rozevíracího seznamu pod "Vybrat příkazy" vyberte možnost "Všechny příkazy". Níže uvedený okno zobrazí všechny možné příkazy, které lze umístit na panelu v abecedním pořadí.

Ze seznamu vyberte "Master of souhrnných tabulek a grafů"

V pravém okně pomocí tlačítka "Vytvořit skupinu" vytvořte novou skupinu nástrojů. Pro skupinu si můžete vybrat pohodlný název pro vás. Například "jeho vlastní skupina". Můžete vybrat na které skupině kartu vytvoří. V mém příkladu jsem si vybral kartu "Domovská stránka".

Když je skupina vytvořena, zvýrazněte ji s kurzorem, vyberte v levém okně kurzor "Souhrn a grafy" a klepněte na tlačítko Přidat \u003e\u003e.

Po kliknutí "OK".

Nyní na hlavní kartě panelu nástrojů je nástroj Shrnutí a grafy ".

Krok za druhé. Vybudujte otočnou tabulku z více zdrojů dat.

Druhým způsobem.

Vytvoření cílové tabulky dat z více listů pomocí dotazu vytvořeného v doplňku napájení.

Tato metoda slouží k použití doplňku napájení dotazu.

Vytvoření dotazu napájení pro více stránek knihy v jedné tabulce.

Krok první.

Musíte vytvořit dvě požadavky, z nichž každý bude mít informace ze samostatné tabulky.

Krok dva.

Chcete-li to provést v kartě Power Query, klepněte na tlačítko "Ze tabulky" a zadejte rozsah zdroje dat v zobrazeném okně. Poté klikněte na tlačítko OK.

Krok tři.

Když byl vytvořen druhý požadavek, musíte kliknout na tlačítko "Sloučit dotaz" v záložce Query Query a nakonfiguruje zobrazení výsledné běžné tabulky v okně, které se zobrazí.

Zdroj dat pro konsolidovanou tabulku slouží jako seznam dat, kde zpravidla každý sloupec funguje jako pole konsolidované tabulky. Ale co když jste přišli k vám, jen se podíváním na konsolidovanou tabulku (to je formátováno a externě připomíná, ale není možné používat pracovní nástroje s konsolidovanými tabulkami). A musíte ji zapnout do seznamu dat, tj. Proveďte zpětný provoz. V tomto příspěvku se naučíte převést konsolidovaný stůl se dvěma proměnnými do seznamu dat.

Obrázek ukazuje princip, který jsem popsal. Ty. V rozsahu A2: E5 je zde původní souhrnná tabulka, která je převedena na seznam dat (H2: J14 rozsah). Druhá tabulka představuje stejný soubor dat, pouze v jiném úhlu. Každá hodnota zdroje konsolidované tabulky vypadá jako řetězec sestávající z řádku pole pole, pole sloupce a odpovídající hodnotu. Tento displej dat je užitečný, pokud je nutné seřadit a manipulovat s těmito dalšími metodami.

Aby bylo možné realizovat schopnost vytvořit takový seznam, použijeme nástroje pivotové tabulky. Přidat tlačítko Mistr pivotových stolů V Hubbaru, který nám není k dispozici na pásku, ale zůstal jako rudiment z dřívějších verzí aplikace Excel.

Postupujte podle záložky Soubor -\u003e Parametry. V dialogovém okně ParametryVynikat,na kartě Rychlý přístupový panelv levém poli najděte položku Master of souhrnné tabulky a grafya přidejte ji doprava. Klepněte na tlačítko OK.

Nyní na panelu zástupce máte novou ikonu.

Kliknutím na tuto kartu spustit Mistr pivotových stolů.

V prvním kroku průvodce musíte vybrat typ zdroje datového zdroje dat. Nainstalujte přepínač V několika rozsahech konsolidace a Zhmem. Dále.

V kroku 2A určete, jak vytvořit pole stránek. Umístěte přepínač Vytvořit pole stránek -\u003e Další.

V kroku 2b v poli Rozsah Vyberte rozsah obsahující data a klepněte na tlačítko Přidat.V našem případě to bude umístění původní souhrnné tabulky A1: E4.

Ve třetím kroku je nutné určit, kde je nutné umístit tabulku drancování a klikněte Připraven.

Excel vytvoří konsolidovanou tabulku s daty. Na levé straně obrazovky uvidíte oblast Seznam polí pivotových tabulek.Odstraňte všechny položky z polí řádů a sloupců. Více podrobněji jsem napsal v předchozím článku.

Budete mít malou souhrnnou tabulku sestávající z jedné buňky, která obsahuje součet všech hodnot zdrojové tabulky.

Poklepejte na tuto buňku. Excel vytvoří nový list, který bude obsahovat tabulku se seznamem hodnot.

Hlavní linie této tabulky jsou obecné informace, pravděpodobně chcete učinit více informativističtěji.