Ce veți face dacă aveți nevoie să creați o masă consolidată și datele dvs. pe diferite foi? Cu excelul instalat 2013 aveți o modalitate simplă pentru acest lucru. Există o tehnologie numită model de date și utilizează relații de date, deoarece face o bază de date.
În această lecție, vă voi arăta totul pentru a crea o masă de pivot în Excel 2013 de la date pe mai multe coli utilizând modelul de date.
Video
Dacă doriți să urmați lecția utilizând propriul fișier Excel, îl puteți face. Sau descărcați fișierul ZIP la această lecție, care conține o carte de eșantionare. Pivot consolidate.xlsx.
Învățarea datelor
Această carte are trei foi de lucru: informații despre client, informații despre informații și informații de plată.
Faceți clic pe foaia. Informații despre clienți. Și asigurați-vă că conține numere de ordine, precum și numele și starea clienților.
Lista de informații despre clienți.
Faceți clic pe foaia. Comandați informații. Și uita-te la ea conține ordine, precum și câmpuri pentru luna, au comandat bunuri și dacă aceste produse sunt organice.
Comandă Info Leaf.Faceți clic pe fila Informații de plată și asigurați-vă că conține comenzi, suma în dolari din fiecare vânzare, metoda de plată și ordinea de plasare a comenzii cu un client nou sau existent.
Informații de plată FrunzePrin conectarea tuturor acestor foi în zona sarcinii sarcinii, putem selecta date din fiecare foaie. Deoarece numerele comenzilor există pe toate cele trei foi, ele vor deveni puncte de conectare. Aceasta este ceea ce cauzează baza de date cHEIA PRINCIPALA. Rețineți: Nu este necesar să aveți cheie primară, dar reduce probabilitatea de eroare.
Creați tabele numite
Înainte de a crea o masă de pivot, să creați o masă din fiecare foaie.
Faceți clic înapoi B. Tabelul de client, apoi faceți clic pe oriunde în zona de date. Faceți clic pe fila. Introduce. Pe panoul de bandă și faceți clic pe pictograma Masa.
Conversia datelor pe foaie selectând Inserați\u003e TabelulCaseta de dialog Creare tabel determină corect zona de masă. Caseta de selectare de mai jos ar trebui, de asemenea, să identifice că primul șir al tabelului este destinat anteturilor. (Dacă nu, selectați această opțiune.)
Crearea dialogului de tabel trebuie să ghicească în mod corespunzător zona de date.Clic O.K.Și acum aveți o masă cu curse alternante și butoane de filtrare. Puteți face clic în interior pentru a elimina selecția dacă doriți să o luați în considerare mai bine (doar nu apăsați în afara tabelului). Panoul de bandă afișează și fila Proiecta. Pentru o masă. În partea stângă a panglicii din câmp Numele tabelului. Se afișează numele temporar table1. Ștergeți-l și spuneți-l. Client_info. (Utilizați simbolul de închidere în loc de un spațiu). Clic INTRODUCE.
Aplicați un nume pentru fiecare tabelRepetați acești pași cu informații despre informații și informații de plată. Numele tabelelor Ordine_info. și Informatii plată..
Acum suntem gata să inserați pivottable.
Cutie pivottable.
Asigurați-vă că, în fișa de informații de plată, cursorul este undeva în tabel. Reveniți la fila Introduce. Panglici și pictograma Faceți clic pe pictograma Masă rotativă (Aceasta este prima pictogramă).
Folosind cursorul în interiorul uneia dintre mese, selectați Inserați\u003e pivottable.În caseta de dialog care apare, trebuie să definiți corect tabelul și să alegeți că pivottable se deplasează într-o nouă foaie de lucru. Mai jos, bifați caseta de selectare Adăugați aceste date la modelul de date. Clic O.K..
Adăugarea datelor la modelul de date este ceea ce permite conexiunile la locul de muncăAcum veți avea pivottable pe o foaie nouă, bara de activități va fi în partea dreaptă a ecranului, iar fila Analyze va apărea pe bandă.
Bara de activități afișează numai o masă și un câmp de foaie activă, astfel încât să apăsați Toate.Pentru a vedea toată tabelul pe care l-ați creat. Dar, înainte de a le putea folosi, trebuie să le legăm unul cu celălalt, ceea ce înseamnă a crea relații. apasa butonul Relații. Pe panoul de bandă.
Stabilirea relațiilor de tabel
Apăsând acest buton afișează caseta de dialog Gestionare relații. apasa butonul Nou Și apare caseta de dialog Creare relație. Vom crea două relații utilizând câmpul Comandă # ca conector.
În listele derulante, selectați Informatii plată. Pentru masă și lângă ea selectează Ordin # În lista derulantă a coloanei. În a doua linie, selectați Client_info. Din tabelul legat de lista derulante și lângă acesta selectați Ordin # Din coloana legate de lista derulante.
Există trei mese, creați două relații.Aceasta înseamnă că tabelele de plată_info și client_info sunt legate de coincidența numărului ordinelor.
apasa butonul O.K. Și vom vedea aceste relații enumerate în fereastra Relații Gestionate.
Repetați acest proces pentru a crea o legătură între plata_info și comanda_info, folosind și câmpul Comandă #. Acum fereastra de gestionare a relațiilor arată astfel:
În caseta de dialog Gestionare relații, relațiile pe care le creați sunt afișate.Rețineți că nu este nevoie să creați relații între mesele de comandă_info și client_info, deoarece acestea sunt conectate automat prin intermediul tabelului de plată_info.
apasa butonul Închide. În partea de jos a ferestrei. Acum putem trage în sfârșit câmpurile în pivottable.
Introducerea câmpurilor în pivottable
În secțiunea Toate de pe bara de activități, faceți clic pe săgețile mici pentru a implementa trei mese, consultați câmpurile lor. Trageți câmpurile din zona pivotată după cum urmează:
- Stat și LUNĂ. în corzi
- Produs în coloana
- $ Vânzare. În valoare
- Stare. în filtre
Acum îl puteți folosi și schimba-l, ca orice alt pivot.
Concluzie
Folosind noua model de model de date din Excel 2013, puteți alege câmpuri roz de mai multe coli pentru a crea un singur pivottable. Rețineți că liniile fiecărui tabel trebuie să fie conectate cumva unul cu celălalt. Aveți mai multe șanse de succes, când tabelele au un câmp comun cu valori unice.
Dacă sunteți în căutarea unor modalități bune de a vă trimite datele, piața de ender are o alegere bună, precum și pentru a converti datele Excel în formate web și invers.
Din acest articol, veți afla cum să combinați rapid datele din cele două mese Excel atunci când nu există coincidențe exacte în coloanele cheie. De exemplu, atunci când un identificator unic de la primul tabel este primele cinci caractere de identificare din a doua masă. Toate soluțiile oferite în acest articol sunt testate de mine în Excel 2013, 2010 și 2007.
Deci, există două foi Excel care trebuie combinate pentru analiza ulterioară a datelor. Să presupunem că, în același tabel, conține prețuri (coloană, preț) și descrieri ale produsului (coloana de bere) pe care o vindeți și în al doilea reflectă datele privind disponibilitatea mărfurilor în stoc (coloană în stoc). Dacă dvs. sau colegii dvs. ați reprezentat ambele tabele în conformitate cu directorul, atunci cel puțin o coloană cheie cu identificatori unici ar trebui să fie prezentă în ambele moduri. Descrierea sau prețul produsului poate varia, dar identificatorul unic rămâne întotdeauna neschimbat.
Dificultăți încep când primești câteva mese de la producător sau de la alte departamente ale companiei. Cazul poate deveni chiar mai complicat dacă este introdus un nou format de identificatori unici sau nomenclaturi de depozitare (SKU) se vor schimba. Și înainte de tine există o sarcină de a îmbina în Excel o masă nouă și veche cu date. Într-un fel sau altul, există o situație în care doar un meci parțial de înregistrări are loc în coloane cheie, de exemplu " 12345 "Și" 12345-New_Suffx.". Sunteți clar că acesta este același SKU, dar computerul nu este atât de treaz! Această coincidență nu este imposibilă utilizarea formulelor uzuale Excel pentru a combina datele din două tabele.
Și că este foarte rău - respectarea nu poate fi deloc și " O companie"Într-o masă se poate transforma în" CJSC "Unele companii""În altă masă și" Noua companie (fosta companie)"Și" Companie veche"De asemenea, va fi o înregistrare a aceleiași companii. Este cunoscut de tine, dar cum să explice Excel?
Cometariu: Soluțiile descrise în acest articol sunt universale. Le puteți adapta pentru utilizarea în continuare cu toate formulele standard, cum ar fi Relatii cu publicul (Vlookup), Boarda de căutare (Meci), GPr. (Hlookup) și așa mai departe.
Selectați exemplul corespunzător pentru a accesa imediat soluția dorită:
Coloana cheie din unul dintre tabele conține caractere suplimentare.
Luați în considerare două mese. Primele coloane de masă conțin un număr de nomenclatură (SKU), numele berii (bere) și prețul acesteia (prețul). A doua masă a înregistrat SKU și numărul de sticle din depozit (în stoc). În loc de bere, poate fi un produs, iar numărul de coloane în viața reală poate fi mult mai mult.
Într-o masă cu simboluri suplimentare, creați o coloană auxiliară. Puteți să-l adăugați la capătul mesei, dar este cel mai bine să îl introduceți lângă dreapta după coloana cheie pentru a fi în vedere.
Cheia din tabelul din exemplul nostru este coloana A. Cu datele SKU și trebuie să eliminați primele 5 caractere din acesta. Adăugați coloană auxiliară și sunați-l SKU Helper:
Gata! Acum avem coloane cheie cu coincidența exactă a valorilor - coloană SKU Helper În tabelul și coloana principală Sku. În tabelul în care va fi efectuată căutarea.
Acum cu funcția. Relatii cu publicul (VLOOKUP) Vom obține rezultatul dorit:
Alte formule
- Extras în primul rând H. Simboluri din dreapta: de exemplu, 6 caractere în partea dreaptă a intrării "DSFH-164900". Formula va arăta astfel:
PRACTERV (A2; 6)
\u003d Dreapta (A2,6) - Săriți mai întâi în primul rând H. Caracterele extrag următoarele Y. Simboluri: De exemplu, trebuie să eliminați "0123" din înregistrarea "Prefix_0123_Suff". Aici trebuie să trecem peste primele 8 caractere și să extraim următoarele 4 caractere. Formula va arăta astfel:
PSTR (A2; 8; 4)
\u003d Mijloc (A2.8,4) - Extrageți toate caracterele Înainte de separator, lungimea secvenței rezultate poate fi diferită. De exemplu, trebuie să extrageți "123456" și "0123" din înregistrările "123456-sufix" și, respectiv, "0123-sufix". Formula va arăta astfel:
Levsimv (a2; găsi ("-" "a2) -1)
\u003d Stânga (A2, găsiți ("-", A2) -1)
Pe scurt, puteți utiliza caracteristici Excel cum ar fi Levsimv. (Stânga), Pratseim. (Dreapta) PST (MID), A GĂSI (Găsiți) pentru a extrage orice părți ale indicelui compozit. Dacă au apărut dificultăți - vom face tot posibilul pentru a vă ajuta.
Datele din coloana cheie din primul tabel sunt împărțite în două sau mai multe coloane din tabelul al doilea.
Să presupunem că masa în care se efectuează căutarea conține o coloană cu identificatori. În celulele din această coloană conțin Xxxx-aaa.Unde XXXX. - acesta este un cod de desemnare a unui grup de bunuri (telefoane mobile, televizoare, camere video, camere foto); Aia. - Acesta este codul mărfurilor din interiorul grupului. Tabelul principal constă din două coloane: într-unul conține coduri de grup de mărfuri (grup), secundele sunt înregistrate în al doilea (ID). Nu putem renunța pur și simplu la codurile grupurilor de produse, deoarece același cod de produs poate fi repetat în diferite grupuri.
În celulă. C2. Noi scriem o astfel de formulă:
Captura (A2; - "; B2)
\u003d Concatenate (A2, "-", B2)
Aici A2. - aceasta este adresa celulei care conține codul grupului; simbol " – "Este un separator; B2. - Aceasta este adresa celulară care conține codul produsului. Copiați formula în liniile rămase.
Acum combină datele din cele două mese noastre nu vor fi dificile. Vom compara coloana ID-ul complet. Prima masă cu coloana ID. al doilea tabel. Când este detectată coincidența, înregistrarea din coloane Descriere. și Preț Al doilea tabel va fi adăugat la primul tabel.
Datele din coloanele cheie nu coincid
Iată un exemplu: Sunteți proprietarul unui magazin mic, obțineți bunurile de la unul sau mai mulți furnizori. Fiecare dintre ei are propria lor nomenclatură care diferă de a ta. Ca rezultat, există situații în care înregistrarea dvs. "Case-IP4S-01" se potrivește cu intrarea "SPK-A1403" în fișierul Excel primit de la furnizor. Astfel de discrepanțe apar la întâmplare și nu există o regulă comună care să convertească automat "SPK-A1403" în "Case-IP4S-01".
Vesti proaste: Datele conținute în aceste două mese Excel vor trebui să se ocupe manual, astfel încât în \u200b\u200bviitor să fie posibilă combinarea acestora.
Vești bune: Acesta va trebui să facă o singură dată, iar tabelul auxiliar rezultat poate fi salvat pentru utilizare ulterioară. În continuare puteți combina automat aceste tabele și puteți salva astfel timpul
1. Creați un tabel subsidiar pentru căutare.
Creați o nouă frunză de Excel și spuneți-o Convertor de SKU.. Copiați întreaga coloană Nosku. De la foaie Magazin. Pentru o nouă foaie, eliminăm duplicatele și lăsăm doar valori unice în ea.
Coloana de adăugare în apropiere Sup.sku. și căutați manual conformitatea între valorile coloanei Nosku. și Sup.sku. (Acest lucru ne va ajuta descrierile americane din coloană Descriere.). Acesta este un muncitor plictisitor, lăsați ideea că trebuie să o împliniți o singură dată :-).
Ca rezultat, avem o astfel de tabelă:
2. Actualizăm tabelul principal folosind datele din tabel pentru a căuta.
La tabelul principal (magazinul de tablă) Introduceți o coloană nouă Sup.sku..
Apoi, cu o funcție Relatii cu publicul (Vlookup) Comparați foile Magazin. și Convertor de SKU. Nosku., și pentru date actualizate - coloană Sup.sku..
Coloană Sup.sku. Umplut cu coduri originale ale producătorului.
Cometariu: Dacă în coloană Sup.sku. Au fost celule goale, atunci trebuie să luați toate codurile Sku.Corespunzător acestor celule goale le adaugă la masă Convertor de SKU. și găsiți codul potrivit de la tabelul furnizorului. După aceea, repetați pasul 2.
3. Transferați datele din tabelul de căutare la tabelul principal
În tabelul nostru principal există o coloană cheie cu o coincidență exactă cu elementele tabelului de căutare, deci acum această sarcină nu va provoca dificultăți
Folosind o funcție Relatii cu publicul (VLOOKUP) Combinați datele de coli Magazin. Cu date de foaie Furnizorul en-gros 1.Utilizarea coloanei pentru a căuta meciul Sup.sku..
Iată un exemplu de date actualizate în coloană Prețul cu ridicata.:
Totul este simplu, nu? Puneți întrebările dvs. în comentariile la articol, voi încerca să răspund cât mai curând posibil.
Tabelele consolidate sunt convenabile de a utiliza datele și creează rapoarte pentru a analiza datele. Și dacă aceste date relaționale (adică, cele stocate în tabele individuale, dar în același timp pot fi combinate din cauza valorilor generale), puteți crea o astfel de masă consolidată în doar câteva minute:
Ce este diferit de această masă de pivot? Rețineți că în lista câmpurilor din dreapta nu este afișată un tabel, ci un set complet de tabele. Fiecare dintre aceste tabele conține câmpuri care pot fi combinate într-o masă consolidată pentru a obține diferite secțiuni de date. Nu sunt necesare formatarea manuală și pregătirea datelor. Imediat după importul de date, puteți crea un tabel rezumat bazat pe tabele conexe.
Pentru a combina mai multe tabele din lista câmpurilor de tabel Pivot:
Mai jos este procedura de importare a mai multor tabele din baza de date SQL Server.
Notă: Lista câmpurilor conține mai multe tabele. Acestea sunt toate tabelele alese de dvs. în timpul importului. Fiecare tabel poate fi implementat și colaps pentru a vizualiza câmpurile sale. Deoarece sunt conectate tabele, puteți crea o masă consolidată, care a aruncat câmpurile de la orice tabel în zonă Valori, Siruri de caractere sau Coloane.
Tabele de import din alte surse
În plus față de SQL Server, puteți importa tabele dintr-o serie de alte baze de date relaționale.
Bazele de date relaționale nu sunt singura sursă de date care acceptă funcționarea cu mai multe tabele din lista câmpurilor de tabel Pivot. Puteți utiliza tabele în cartea dvs. sau importați canalele de date și apoi integrați-le cu alte tabele de date din carte. Pentru ca toate aceste date care nu au legătură să colaboreze, fiecare tabel este adăugat la modelul de date și apoi creează legături între ele folosind valorile corespunzătoare câmpului.
Utilizarea unui model de date pentru a crea o nouă masă de pivot
Este posibil să fi creat legături între tabelele din modelul de date și sunt acum gata să utilizeze aceste date pentru analiză. Următoarele sunt descrise cum se creează un nou tabel rezumat sau o diagramă sumară utilizând modelul de date din carte.
Foarte des, atunci când generează rapoarte (analiști) de cantități mari de date, trebuie să conduceți informații din mai multe coli din cartea "Excel" într-o singură masă.
În mod ideal, pentru colectarea și analizele informațiilor din diferite foi "Excel" este recomandabil să se facă un tabel rezumat, care va lua date din mai multe surse (foile de cărți).
O astfel de masă poate fi efectuată în două moduri diferite.
Luați în considerare primul mod.
Crearea unei mese de pivot din diferite foi utilizând caracteristici standard și unelte.
Primul pas.
Trebuie să adăugați butonul pe instrumentul de scule la instrumentul de scule și diagrame la bandă.
Pentru a face acest lucru, faceți clic pe clic dreapta pe bandă (bare de instrumente) și selectați "Banda Setup" din meniul derulant
introduceți fila
"Fișier" \u003d\u003e "parametri" \u003d\u003e "Banda de configurare".
Apoi, în setările benzii de pe lista derulantă din inaplicația "Selectați comenzile", selectați "Toate comenzile". Fereastra de mai jos va afișa toate comenzile posibile care pot fi plasate pe panou în ordine alfabetică.
Din listă, selectați "Master în tabele și diagrame rezumat"
În fereastra dreaptă, folosind butonul "Creare grup", creați un nou grup de instrumente. Pentru un grup, puteți alege un nume convenabil pentru dvs. De exemplu, "grupul propriu". Puteți selecta în ce grup va crea grupul. În exemplul meu, am ales fila "Acasă".
Când grupul este creat, evidențiați-l cu cursorul, selectați cursorul "Rezumatul și diagramele" din fereastra din stânga și faceți clic pe butonul Adăugare \u003e\u003e.
După ce faceți clic pe "OK".
Acum, pe fila principală a barei de instrumente este instrumentul "Rezumat și diagrame".
Pas secundă. Construiți o masă de pivot din mai multe surse de date.
Al doilea mod.
Crearea unei mese de date țintă din mai multe coli utilizând o interogare formată în add-on-ul de interogare a energiei.
Această metodă este de a utiliza solicitarea de adăugare a interogării de alimentare.
Crearea unei interogări de interogare a energiei pentru mai multe pagini dintr-o carte într-o singură masă.
Primul pas.
Trebuie să creați două cereri, fiecare dintre acestea va lua informații dintr-un tabel separat.
Pasul doi.
Pentru a face acest lucru, în fila Interogare Power, faceți clic pe butonul "Din tabel" și specificați intervalul sursei de date din fereastra care apare. După aceea, faceți clic pe "OK".
Pasul trei.
Când a fost creată oa doua solicitare, trebuie să faceți clic pe butonul "Merge interogare" din fila de interogare a alimentării și configurează vizualizarea tabelului comun rezultat din fereastra care apare.
Sursa de date pentru tabelul consolidat servește ca o listă de date, unde, de regulă, fiecare coloană acționează ca un câmp al unui tabel consolidat. Dar dacă ați venit la dvs., numai cu o privire la masa consolidată (este formatată și o reamintește extern, dar este imposibil să se utilizeze unelte de lucru cu mese consolidate). Și trebuie să o transformi în lista de date, adică Efectuați o operație inversă. În acest post, veți învăța cum să convertiți un tabel consolidat cu două variabile în lista de date.
Figura arată principiul pe care l-am descris. Acestea. În intervalul A2: E5, există un tabel de rezumat original care este convertit în lista de date (gama H2: J14). Al doilea tabel reprezintă același set de date, numai într-un alt unghi. Fiecare valoare a mesei consolidate sursă arată ca un șir constând dintr-un punct de câmp, un câmp de coloană și valoarea corespunzătoare. Acest afișaj de date este util atunci când este necesar să sortați și să manipulați aceste alte metode.
Pentru a realiza capacitatea de a crea o astfel de listă, vom folosi instrumentele tabelului de pivot. Adăugați un buton Maestru de mese de pivot În hubbar, care nu este disponibilă pentru noi pe bandă, dar a rămas ca un rudiment din versiunile anterioare ale Excel.
Urmați fila. Fișier -\u003e Parametri. În caseta de dialog care apare dialog ParametriExcela,În tabelul Panou de acces rapidÎn câmpul din stânga, găsiți elementul Master în tabele sumare și diagrameȘi adăugați-l în dreapta. Faceți clic pe OK.
Acum, pe panoul de comandă rapidă aveți o nouă pictogramă.
Faceți clic pe această filă pentru a rula Maestru de mese de pivot.
În prima etapă a expertului, trebuie să selectați tipul de sursă de date sursă de date. Instalați comutatorul. În mai multe intervale de consolidare și Zhmem. Mai departe.
În pasul 2a, specificați modul de creare a câmpurilor de pagini. Așezați comutatorul. Creați câmpuri de pagină -\u003e Următorul.
La pasul 2b, în \u200b\u200bcâmp Gamă Selectați o gamă care conține date și faceți clic pe Adăuga.În cazul nostru, aceasta va fi localizarea tabelului de rezumat original A1: E4.
În al treilea pas, este necesar să se determine unde este necesar să plasați o masă de jetoane și să faceți clic pe Gata.
Excel va crea o masă consolidată cu date. În partea stângă a ecranului veți vedea zona Lista câmpurilor de masă pivot.Eliminați toate elementele din câmpurile rândurilor și coloanelor. În detaliu despre am scris în articolul precedent.
Veți avea un tabel rezumat mic constând dintr-o celulă, care conține suma tuturor valorilor tabelului sursă.
Faceți dublu clic pe această celulă. Excel va crea o nouă foaie care va conține o masă cu o listă de valori.
Titlurile acestui tabel sunt informații generale, probabil că doriți să le faceți mai multe informative.
