Mida te teete, kui teil on vaja luua konsolideeritud tabeli ja teie andmed erinevate lehtede kohta? Paigaldatud Excel 2013-ga on teil selle jaoks lihtne viis. On tehnoloogia nimetatakse andmete mudeli ja see kasutab andmete suhteid nagu see muudab andmebaasi.

Selles õppetund näidata teile kõike luua Pivot tabel Excel 2013 andmete mitme lehtede andmemudel.

Videot

Kui soovite jälgida õppetund kasutades oma Exceli faili, saate seda teha. Või laadige alla zip-fail sellesse õppetund, mis sisaldab prooviraamatut. Pivot konsolideerige.xlsx.

Andmeõpe

Sellel raamatus on kolm töölehte: teave kliendi kohta, tellimuse teabe ja makseteabe kohta.

Klõpsake lehel Kliendi info. Ja veenduge, et see sisaldab tellimusi numbreid, samuti klientide nime ja seisundit.

Kliendi info nimekiri

Klõpsake lehel Tellimus info. Ja vaadake see sisaldab tellimusi, samuti väljad kuu, tellitud kaupade jaoks ja kas need tooted on mahepõllumajanduslikud.

Tellimuse info leht

Klõpsake vahekaarti makseinfo ja veenduge, et see sisaldab tellimusi, summa dollarit iga müügi, makseviisi ja tellimuse paigutamise järjekorda uue või olemasoleva kliendiga.

Makseteabe leht

Ühendades kõik need lehed ülesande ülesanne piirkonnas, saame valida andmeid iga lehe. Kuna tellimuste numbrid on olemas kõigil kolmel lehel, muutuvad nad ühenduspunktideks. See on see, mida andmebaasi põhjustab esmane võti. Pange tähele: esmane võti ei ole vaja, kuid see vähendab vigade tõenäosust.

Loo nimega tabelid

Enne pivot tabeli loomist loodame iga lehe tabeli.

Klõpsake Tagasi B. KliendilaudSeejärel klõpsake kõikjal andmevaldkonnas kõikjal. Klõpsake vahekaarti Sisestage. Lindipaneelil ja klõpsake ikooni ikoonil Tabel.

Teisenda andmed lehel valides Lisa\u003e Tabel

Dialoogiboksi loomise tabeli õigesti määrab tabeli ala õigesti. Allolev märkeruut peaks samuti tuvastama, et tabeli esimene string on mõeldud päiste jaoks. (Kui ei, valige see valik.)

Loo Tabeli dialoogi loomine peab andmeala õigesti arvama.

Klõpsutama OKEI.Ja nüüd on teil tabel koos vahelduvate lööki ja filtri nupud. Valiku eemaldamiseks saate klõpsata sees, kui soovite seda paremini kaaluda (lihtsalt ei vajuta tabelis väljaspool tabelit). Tape paneel kuvab ka vahekaardi Disain. Tabeli jaoks. Vasakul pool lindi valdkonnas Tabeli nimi. Kuvatakse tabelis 1 ajutine nimi. Kustuta see ja nimetage see. Customer_info. (Kasutage ruumi asemel sulgemissülitit). Klõpsutama SISENEMA.

Rakenda iga tabeli nimi

Korrake neid samme tellimuse info ja makseinfo lehed. Nimi tabelid Tellimus_info. ja Makse_info..

Nüüd oleme valmis sisestama Pivottable.

Kasti Pivottable.

Veenduge, et makseinfo lehel on kursor kusagil tabelis. Tagasi vahekaardile Sisestage. Lindid ja klõpsake ikooni Pivottable (See on esimene ikoon).

Kursori kasutamine ühe tabelite sees valige Lisa\u003e PivotTable

Dialoogiboksis, mis ilmub, peate tabeli õigesti määratlema ja valima, et PivotTable liigub uuele töölehele. Allpool valige märkeruut Lisage need andmed andmemudelile. Klõpsutama OKEI..

Andmete lisamine andmemudelile on see, mis võimaldab ühendused töötada

Nüüd on teil uue lehel pöörata, tegumiribal on ekraani paremal küljel ja vahekaart Analyse ilmub lindile.

Tegumiriba kuvab ainult aktiivse lehe tabeli ja valdkonnas, nii et vajutage Kõik.Kõigi loodud tabeli vaatamiseks. Aga enne kui me neid kasutame, peame neid üksteisega siduma ja see tähendab suhete loomist. vajuta nuppu Suhted. Lindipaneelil.

Lauasuhete seadistamine

Selle nupu vajutamine kuvab dialoogiboksi juhtimise suhted. vajuta nuppu Uus Ilmub dialoogiboksi loomise suhe. Loome kaks suhet, kasutades tellimuse # väli pistikuna.

Vali rippmenüüst Makse_info. Tabeli jaoks ja selle kõrval valige Tellimus # Veerus rippmenüüst. Teises reas valige Customer_info. Alates rippmenüüst seotud tabelist ja selle kõrval valige Tellimus # Rippmenüüst seotud veerus.

On kolm tabelit, seega luua kaks suhet.

See tähendab, et maksed_info ja customer_info tabelid on seotud tellimuste numbri kokkusattumusega.

vajuta nuppu OKEI. Ja me näeme neid suhteid, mis on loetletud juhtimise suhetes aknas.

Korrake seda protsessi, et luua seos makse_info ja tellimuse_info vahel, kasutades ka tellimuse esitamist. Nüüd näeb välja suhted Windowsi juhtimine:

Dialoogiboksis asuvate suhete haldamisel kuvatakse loodud suhted.

Pange tähele, et tellimuse_info ja customer_info tabelite vahel ei ole vaja luua suhteid, kuna need on automaatselt ühendatud makse_info tabeli kaudu.

vajuta nuppu Sulge Akna allosas. Nüüd saame lõpuks väljad pivottable.

Väljade sisestamine Pivottable

Kõigis tegumiriba sektsioonis klõpsake väikestest nooledest, asetage kolm tabelit, vaadake oma väljad. Lohistage Pivottable piirkonnas väljad järgmiselt:

• Riik ja Kuu. stringides
• Toode veerus
• $ Müük. Väärtus
• Staatus. Filtrid

Lohistage iga kolme tabeli väljad Pivottable'is

Nüüd saate seda kasutada ja muuta, nagu mis tahes muu Pivottable.

Järeldus

Kasutades uue objekti andmete mudeli funktsiooni Excel 2013, saate valida roosa väljad mitmete lehtede loomiseks ühe pööratava. Pidage meeles, et iga tabeli jooned peavad olema kuidagi üksteisega ühendatud. Sul on edu saavutamiseks rohkem võimalusi, kui tabelidel on ainulaadsete väärtustega ühine valdkond.

Kui otsite head võimalusi oma andmete esitamiseks, on Enaskato turul hea valik, samuti teisendada Exceli andmed veebivormingutele ja vastupidi.

Sellest artiklist saate teada, kuidas kiiresti ühendada andmeid kahest Exceli tabelitest, kui võtme veergudes puuduvad täpsed kokkupaigud. Näiteks kui unikaalne tunnus esimesest tabelist on teise tabeli esimese viie identifikaatori tähemärki. Kõik käesolevas artiklis pakutavad lahendused testivad mind Excel 2013, 2010 ja 2007.

Niisiis, seal on kaks Exceli lehed, mis tuleb kombineerida edasiseks andmete analüüsiks. Oletame, et samas tabelis on hinnad (veerg, hind) ja tootekirjeldused (õlle kolonn), mida müüte, ja teises kajastab andmeid kaupade kättesaadavuse kohta laos (veerus). Kui teie või teie kolleegid on kataloogi järgi nii tabelite puhul arvestanud, peaks mõlemas teedel olema vähemalt üks võtme kolonn ainulaadsete identifikaatoritega. Toote kirjeldus või hind võib erineda, kuid ainulaadne identifikaator jääb alati samaks.

Raskused algavad, kui saad mõne tabelite tootjalt või ettevõtte teistest osakondadest. Juhtum võib isegi keerulisemaks muutuda keerulisemaks, kui uus unikaalsete identifikaatorite vorming või ladu nomenklatuur (SKU) muutub. Ja enne kui olete ülesanne ühendada Excel uue ja vana tabeli andmetega. Ühel või teisel viisil on olemas olukord, kus peamiste veergude puhul esineb ainult osaline dokumentide osakaal " 12345 "Ja" 12345-New_Suffix.". Te olete selge, et see on sama komplekt, kuid arvuti ei ole nii ärkvel! See ei ole täpne juhus ei võimalda tavalisi Exceli valemeid kasutada kahe tabeli andmete ühendamiseks.

Ja see on väga halb - vastavus ei pruugi üldse olla ja " Mõned ettevõtted"Ühes tabelis võib muutuda" Cjsc "mõned ettevõtted""Teises tabelis ja" Uus ettevõte (endine mõni firma)"Ja" Vanafirma"Samuti on sama firma rekord. See on teile teada, kuid kuidas Excel selgitada?

Kommentaar: Käesolevas artiklis kirjeldatud lahendused on universaalsed. Saate kohandada neid edasiseks kasutamiseks standardsete valemitega, näiteks Pr (Vluoorup), Otsi juhatus (Match), Gpr (Hlookup) ja nii edasi.

Valige sobiv näide soovitud lahusele koheseks avamiseks:

Võtmesamba ühes tabelites sisaldab täiendavaid märke.

Kaaluge kahte tabelit. Esimesed tabeli veerud sisaldavad nomenklatuuri numbrit (SKU), õlle nimi (õlu) ja selle hind (hind). Teine tabel salvestas SKU ja lao pudelite arv (laos). Õlle asemel võib olla ükskõik milline toode ja reaalses elus veergude arv võib olla palju rohkem.

Täiendavate sümbolitega tabelis luua lisakolonn. Saate selle lisada selle tabeli lõpuni, kuid kõige parem on sisestada see kohe pärast võtmerola silmis.

Võti tabelis meie näites on kolonn A. SKU andmetega ja teil on vaja eemaldada sellest esimese 5 tähemärki. Lisage abiturg ja helistage SKU abimees:

Valmis! Nüüd on meil peamised veerud väärtuste täpne kokkusattumus - veerg SKU abimees Peamises tabelis ja veerus SKU. Tabelis, kus otsing toimub.

Nüüd funktsiooniga Pr (Vluoorup) Saame soovitud tulemuse:

Muud valemid

• Ekstrakti esimene H. Sümbolid paremal: näiteks 6 tähemärki paremal "DSFH-164900" kirje. Valem näeb välja selline:

  Practerv (A2; 6)
  \u003d Parem (A2,6)

• Vahele kõigepealt H. tähemärgid ekstraheerige järgmised Y. Sümbolid: näiteks peate eemaldada "0123" salvestus "Prefitex_0123_Suff". Siin peame vahele esimese 8 tähemärki ja ekstraheerige järgmised 4 tähemärki. Valem näeb välja selline:

  PSTR (A2; 8; 4)
  \u003d Keskel (A2.8,4)

• Eemaldage kõik tähemärgid enne eraldajat, saadud järjestuse pikkus võib olla erinev. Näiteks peate eraldama vastavalt "123456" ja "0123" vastavalt dokumentidest "123456-sufipsi" ja "0123-sufiks". Valem näeb välja selline:

  Levsimv (A2; leida ("-"; A2) -1)
  \u003d Vasakule (A2, Leia ("-", A2) -1)

Lühidalt öeldes saate kasutada Exceli funktsioone Levsimv (Vasakul), Praatseim (Paremal) Pst (Keskmine), LEIDMA (Leia) komposiitindeksi osade eraldamiseks. Kui raskusi on tekkinud -, me teeme oma parima, et teid aidata.

Esimese tabeli võtmeveerundi andmed on jagatud kahe või enama veeru teise tabeli.

Oletame, et tabel, kus otsing toimub, sisaldab veeru identifikaatoritega. Selle veeru rakkudes sisaldavad Xxxx-yyyy.kus Xxxx - See on kaubagrupi koodide nimetus (mobiiltelefonid, telerid, videokaamerad, kaamerad) ja Yyyy. - See on grupi sees kauba kood. Peamine tabel koosneb kahest veergust: ühes sisaldab kaupade rühmade (rühma) koode, sekundid salvestatakse teises (ID). Me ei saa lihtsalt visata tootegruppide koode, kuna sama tootekoodi saab korrata erinevates rühmades.

Raku C2. Kirjutame sellise valemi:

Saak (A2; "-"; B2)
\u003d Konkuptsioon (A2, "-", B2)

Siin A2. - See on grupi koodi sisaldava lahtri aadress; sümbol " – "On eraldaja; B2. - See on toote koodi sisaldav raku aadress. Kopeerige valem ülejäänud liinidesse.

Nüüd ühendage andmed meie kahe tabeli andmed ei ole raske. Me võrdleme veergu Täielik ID Esimene tabel veerguga Id Teine tabel. Kui juhus on tuvastatud, salvestamine veergudest Kirjeldus. ja Hind Teine tabel lisatakse esimesele tabelile.

Andmed võti veerud ei lange kokku

Siin on näide: Olete väikese poe omanik, saada kaupa ühest või mitmest tarnijalt. Igaühel neist on oma nomenklatuur, mis erineb sinu omast. Selle tulemusena on olukordi, kus teie "Case-IP4S-01" kirje vastab "SPK-A1403" kirje tarnija Exceli faili. Sellised lahknevused toimuvad juhuslikult ja puudub ühine reegel, et muuta "SPK-A1403" automaatselt "puhul-IP4S-01".

Halvad uudised: Nende kahe Exceli tabelis sisalduvad andmed peavad käsitsi käsitsi käsitsema nii, et tulevikus on võimalik neid kombineerida.

Head uudised: See peab tegema ainult üks kord ja sellest tulenev lisalaud saab edasiseks kasutamiseks salvestada. Järgmisena saate need tabelid ühendada automaatselt ja säästa aega

1. Loo otsingu jaoks tütarettevõte.

Loo uus leht Excel ja helistage SKU konverter.. Kopeerige kogu veerg Our.SKU. Lehelt Hoida. Uue lehe jaoks eemaldame duplikaadid ja jätame ainult ainulaadsete väärtuste.

Lähedal asuv veerg Supp.SKU. ja käsitsi otsides veeru väärtuste vastavust Our.SKU. ja Supp.SKU. (See aitab meil kirjeldada veerust Kirjeldus.). See on igav töömees, lase idee, et sa pead seda täitma ainult üks kord :-).

Selle tulemusena on meil selline tabel:

2. Uuendame peamist tabeli tabeli andmete abil otsingule.

Põhilaud (lehtkauplus) sisestage uus veerg Supp.SKU..

Järgmine funktsiooniga Pr (Vluoorup) Võrdle lehed Hoida. ja SKU konverter. Our.SKU.ja ajakohastatud andmete jaoks - veerg Supp.SKU..

Veerg Supp.SKU. Tootja algkoodide täitmine.

Kommentaar: Kui veerus Supp.SKU. Seal olid tühjad rakud, siis peate võtma kõik koodid SKU.Nende tühjade rakkude vastavad lisavad need tabelisse SKU konverter. ja leidke asjakohast koodi tarnija tabelist. Pärast seda korrake samm 2.

3. Andmete edastamine otsingu tabelist peamisele tabelile

Meie peamises tabelis on peamine kolonn, millel on täpselt kokkusattumus otsingu tabeli elementidega, nii et nüüd ei põhjusta see ülesanne raskusi

Funktsiooni kasutamine Pr (Vluoorup) Kombineerige leheandmed Hoida. Leheandmetega Hulgimüük tarnija 1.Kasutades veeru mängu otsimiseks Supp.SKU..

Siin on näide veerus ajakohastatud andmetest Hulgihind:

Kõik on lihtne, eks? Küsige oma küsimustele artiklites artiklites, püüan vastata võimalikult kiiresti.

Konsolideeritud tabelid on andmete kasutamiseks mugavad ja loovad aruandeid andmete analüüsimiseks. Ja kui need suhtelised andmed (s.o need salvestatakse üksikutes tabelites, kuid samal ajal saab neid kombineerida üldiste väärtuste tõttu), saate luua sellise konsolideeritud tabeli vaid mõne minuti jooksul:

Mis on see pivot tabelis erinev? Pange tähele, et paremal asuvate väljade loendis ei kuvata ühte tabelit, vaid terve tabelite kogumit. Kõik need tabelid sisaldavad väljad, mida saab kombineerida üheks konsolideeritud tabelisse, et saada erinevaid andmeid. Käsitsi vormindamise ja andmete ettevalmistamist ei ole vaja. Kohe pärast andmete importimist saate luua seotud tabelite põhjal kokkuvõtliku tabeli.

Mitme tabeli ühendamiseks pivot tabeli väljade loendis:

Allpool on protseduur mitme tabeli importimiseks SQL Server andmebaasist.

Märkus: väljade loend sisaldab mitu tabelit. Need on kõik teie poolt impordi ajal valitud tabelid. Iga tabelit saab oma väljade vaatamiseks kasutada ja kokkuvarisema. Kuna tabelid on ühendatud, saate luua konsolideeritud tabeli, olles viskasid väljad mis tahes tabelist piirkonda Väärtused, Stringid või Veerud.

Muudest allikatest pärit impordi tabelid

Lisaks SQL-serverile saate importida tabeleid mitmete teiste relatsiooniandmebaaside hulgast.

Relational andmebaasid ei ole ainus andmeallikas, mis toetab operatsiooni mitme tabeliga pivot tabeli väljade loendis. Raamatute või impordi andmekanalite tabeleid saate kasutada ning seejärel integreerida need teistes andmetabelites raamatusse. Selleks, et kõik need sõltumatud andmed töötamiseks koos töötamiseks, lisatakse iga tabel andmemudelile ja looge seejärel nende vahelised seosed vastavate väljade väärtuste abil.

Andmemudeli kasutamine uue pööningulaua loomiseks

Teil võib olla lingid andmemudeli tabelite vahel ja on nüüd valmis seda andmeid analüüsimiseks kasutama. Kirjeldatakse järgmist, kuidas luua uue kokkuvõtliku tabeli või kokkuvõtliku diagrammi raamatus andmemudeli abil.

Väga sageli, kui genereerides aruanded (analüütikud) suurte andmemahtude, siis tuleb juhtida teavet mitmete lehtede raamatu "Excel" ühte tabeli.
Ideaaljuhul on erinevate lehtede teabe kogumiseks ja analüüsiks "Excel" soovitatav kokkuvõtliku tabeli teha, mis võtab andmeid mitmetest allikatest (raamatulehed).

Sellist tabelit saab teostada kahel erineval viisil.

Kaaluge esimest teed.

Erinevate lehtede pivot tabeli loomine standardfunktsioonide ja tööriistade abil.

Esimene samm.

Peate lisama nupu tööriista tööriista tööriista tööriista ja graafikuid lindile.

Selleks klõpsake nuppu Paremklõpsake lindi (tööriistaribad) ja valige rippmenüüst "Tape Setup"

kas sisestage vahekaart

"Fail" \u003d\u003e "Parameetrid" \u003d\u003e "Tape Setup".

Järgmisena valige rippmenüüst lindi seaded "Vali käskude" all "Vali käsud", valige "Kõik käsud". Allolev aken kuvab kõik võimalikud käsud, mida saab asetada paneelile tähestikulises järjekorras.

Nimekirjast valige "Kokkuvõtete ja diagrammide magister"

Parempoolses aknas, kasutades nuppu "Loo grupp", luua uus tööriistade rühm. Rühma jaoks saate valida teile mugava nime. Näiteks "oma grupp". Saate valida, mida tab grupp loob. Minu näites valisin ma vahekaardi "Home".

Kui grupp on loodud, tõstke see kursorit valige kursor "Kokkuvõte ja kaardid" vasakul aknas ja klõpsake nuppu Lisa \u003e\u003e.

Pärast klõpsake "OK".

Nüüd on tööriistariba peamine vahekaart "Kokkuvõte ja kaardid" tööriista.

Samm teiseks. Ehita pivot tabel mitu andmeallikatest.

Teine võimalus.

Sihtandmete tabeli loomine mitmeast lehtedest, kasutades päringuga moodustatud päringu lisandmoodul.

See meetod on kasutada elektripäringu lisandmoodul.

Power Päringu päringu loomine raamatu mitme lehekülje kohta ühes tabelis.

Esimene samm.

Te peate looma kaks taotlust, millest igaüks teeb eraldi tabeli kohta teavet.

Teine etapp.

Selleks klõpsake vahekaardil Power Quordi "Tabelist" ja täpsustage andmeallika vahemikku ilmuvates aken. Pärast seda klõpsake "OK".

Kolmas etapp.

Kui teine \u200b\u200btaotlus loodi, peate klõpsake vahekaardil Power Päringu nupule "Merge Query" ja konfigureerib tulemusena saadud ühise tabeli vaate ilmuva aknas.

Konsolideeritud tabeli andmeallikas toimib andmete loendina, kus reeglina toimib iga veerus konsolideeritud tabeli valdkonnas. Aga mis siis, kui sa tulid sinu juurde, siis ainult konsolideeritud tabelis (see on vormindatud ja väliselt meenutab seda, kuid see on võimatu kasutada konsolideeritud tabelite töövahendeid). Ja teil on vaja muuta selle andmete loendisse, st Tehke pöördtoiming. Selles postituses saate teada, kuidas teisendada konsolideeritud tabelit kahe muutujaga andmete loendist.

Joonis näitab põhimõtet, mida ma kirjeldasin. Need. A2: E5 vahemikus on algne kokkuvõtlik tabel, mis muundatakse andmete loendisse (H2: J14 vahemik). Teine tabel kujutab endast sama andmekogumit ainult teise nurga all. Iga lähtekoondis konsolideeritud tabeli väärtus näeb välja nagu rountivälja punkti, veeru välja ja vastav väärtus. See andmete kuvamine on kasulik, kui need teised meetodid on vajalikud ja manipuleerida.

Sellise nimekirja loomise võime realiseerimiseks kasutame Pivot tabeli tööriistu. Lisa nupp Master of Pivot tabelid Hubbaris, mis pole meile lindile kättesaadav, kuid jäi excli varasemate versioonide rutimendina.

Järgige vahekaarti Fail -\u003e Parameetrid. Dialoogiboksis, mis ilmub dialoogi ParameetridExcel,vahekaardil Kiire juurdepääsupaneelvasakpoolses valdkonnas leidke toode Kokkuvõtete kapten ja kaardidja lisage see paremale. Klõpsake nuppu OK.

Nüüd on kiirklahvil uus ikoon.

Käivita sellel vahekaardil Master of Pivot tabelid.

Viisardi esimeses etapis peate valima andmeallika andmeallika tüübi. Paigaldage lüliti Mitmes konsolideerimisvahemikus ja Zhmem. Edasi.

Etapis 2a täpsustage lehekülje väljad loomine. Asetage lüliti Loo lehekülje väljad -\u003e Järgmine.

Sammu 2b valdkonnas Vahemik Valige andmed, mis sisaldavad andmeid ja klõpsake Lisama.Meie puhul on see algse kokkuvõtliku tabeli A1: E4 asukoht.

Kolmandas etapis on vaja kindlaks teha, kus on vaja paigutada tabelit ja klõpsake Valmis.

Excel loob andmetega konsolideeritud tabeli. Ekraani vasakus servas näete ala Pöördelaua väljade nimekiri.Eemaldage kõik esemed ridude ja veergude väljadest. Täpsemalt ma kirjutasin eelmises artiklis.

Teil on väike kokkuvõtlik tabel, mis koosneb ühest rakust, mis sisaldab kõigi allika tabeli väärtuste summat.

Topeltklõpsake sellel rakus. Excel loob uue lehe, mis sisaldab tabelit väärtuste loendiga.

Selle tabeli pealkirjad on üldised andmed, siis ilmselt tahad neid rohkem informatiivsemaks muuta.