Un tabel pivot în Excel este un instrument util pentru analiza și prezentarea datelor. Dar dacă datele sunt în surse diferite? Să ne dăm seama cum să facem un tabel pivot cu mai multe foi în Excel.
Chiar dacă există sisteme ERP și BI, un CFO nu se poate descurca fără Excel. Tot felul de calcule tabele pivot, grafice convenabile - în Excel puteți face aproape orice. Dar trebuie să știi cum să o faci.
Tabel pivot în Excel folosind Power Query
Pentru început, trebuie remarcat faptul că Excel poate lucra cu tabele sursă de diferite dimensiuni. Dar titlurile și titlurile acestor tabele trebuie să fie aceleași. Acest lucru este necesar pentru ca programul să interpreteze corect datele utilizate. În caz contrar, poate apărea o eroare.
Să presupunem că este necesar, datele pentru care trebuie luate din foi diferite. Această situație poate apărea dacă societatea are mai multe puncte de vânzare distribuite, depozite, clienți diferiți ai acelorași grupe de mărfuri. În acest caz, rapoartele vor fi furnizate de diferite filiale. Și pentru a le analiza adecvat, are sens să se creeze un tabel de date comun întregii companii, pe baza căruia se va construi un tabel pivot în viitor.
În acest caz, trebuie să creați o nouă foaie de lucru goală în Excel.
În această foaie, accesați fila „Date”.
Apoi, faceți clic pe „Creați cerere”, în lista derulantă, selectați „Din fișier” și apoi - „Din carte”. Să creăm un tabel pivot cu mai multe foi folosind un exemplu - două magazine au trimis rapoarte despre prezența cutiilor de diferite culori pe rafturile lor. Datele fiecărui magazin sunt stocate pe o singură foaie. Din aceste foi se formează „Cartea 1”, cu care lucrăm.
În fereastra care apare, trebuie să specificați cartea de unde programul ar trebui să preia datele și să faceți clic pe butonul „Import”.
Va apărea o fereastră numită „Navigator”. În ea, trebuie să selectați foaia din care vor fi preluate datele. Puteți specifica orice foaie.
Programul va afișa datele în fereastra de previzualizare, care urmează să fie preluată din foaia specificată.
În exemplul nostru, se poate observa că foaia specificată conține multe celule cu date „nule”. Acest lucru nu este adevărat, deoarece programul va procesa și aceste celule. Pentru a reduce zona valorilor procesate și pentru a elimina astfel de celule zero, trebuie să corectați dosarul original. Pentru a face acest lucru, mergeți la tabelul original și apăsați „Ctrl + End”. Ultima celulă activă din tabel va fi evidențiată. Este necesar să ștergeți toate celulele din dreapta și de sub tabel, asigurându-vă că atunci când apăsați „Ctrl + End”, celula din dreapta jos a tabelului devine activă.
După aceea, sursa de date nu va conține informații inutile.
Puteți șterge liniile „Navigație” și „Tip schimbat”. Și începeți editarea datelor în secțiunea „Sursă”. Fereastra editorului principal va afișa o listă cu toate foile cărții specificate. În cazul nostru, „Sheet1” și „Sheet2”.
Apoi, în linia „Date”, faceți clic pe pictograma cu două săgeți, așa cum se arată în figură.
În fereastra care apare, debifați caseta „Utilizați numele coloanei originale ca prefix”. Și faceți clic pe „OK”. Va apărea un tabel care conține toate datele.
Masa va fi reconstruită. Linia duplicată cu „antetul” poate fi eliminată. Pentru a face acest lucru, în filtrul coloanei „Depozit”, debifați caseta „Depozit” și faceți clic pe „OK”. Apoi, în același filtru, faceți clic pe „Ștergeți gol”. Liniile corespunzătoare vor fi eliminate.
În fereastra „Download to” care apare, puneți comutatorul în poziția „Only create connection” și faceți clic pe butonul „Download”. Va apărea o interogare, pe baza căreia va fi construit tabelul pivot.
În fereastra care apare, setați comutatorul în poziția „Utilizați sursa de date externă” și faceți clic pe butonul „Selectați conexiunea”.
În fereastra care apare, selectați numele cererii generate, în cazul nostru - „rezumat” și faceți clic pe butonul „Deschidere”.
Apare PivotTable Designer. Datele pot fi adăugate și mutate la fel ca într-un tabel pivot obișnuit.
PivotTable în versiunile de Excel anterioare lui 2016
LA programe Excel, publicat până în 2003 inclusiv, tabele pivot din diferite surse au fost create utilizând opțiunea „construiți un pivot pentru mai multe intervale de consolidare”. Cu toate acestea, o astfel de construcție a tabelului nu face posibilă analiza completă a întregii cantități de informații primite. Un tabel construit în acest fel poate fi folosit doar pentru analiza de date de bază.
În versiunile din 2007, a fost necesar să se adauge un „Asistent tabel pivotant” special, în care noi intervale de date erau specificate unul câte unul prin casetele de dialog. Tabelele pivot rezultate au avut și o funcționalitate destul de limitată.
Ce sunt tabelele pivot Excel? Probabil, o persoană neinițiată va presupune cel mai evident - acestea sunt tabele în care sunt colectate (rezumate) date din mai multe alte surse disparate care îndeplinesc o anumită interogare. De fapt, această concluzie nu este în întregime corectă.
Excel PivotTables este un instrument care colectează, conform unui algoritm dat, informații de la care cel mai adesea un tabel de bază de date! (Posibilitatea de a utiliza mai multe intervale de consolidare ca sursă există, dar nu va fi discutată în acest articol.)
Cu ajutorul tabelelor pivot dintr-un set haotic de rânduri ale bazei de date, puteți în secunde extrageți-le pe cele necesare, tăiați informațiile inutile din ele, adăugați informațiile lipsă și prezentați-le utilizatorului într-o formă frumoasă și ușor de citit.
Dacă lucrezi constant în MS Excel cu cantități mari de informații și nu știi nimic despre tabelele pivot, atunci ia în considerare că „bați cuie” cu un calculator fără a-i cunoaște adevăratul scop!
Când ar trebui folosite tabelele pivot?
În primul rând, atunci când lucrați cu o cantitate mare de date statistice, ceea ce este foarte laborios de analizat cu ajutorul sortării și filtrelor.
În al doilea rând, când este necesară repetarea (recalcularea) rapoartelor la anumite intervale din cauza modificărilor sursei - tabelul bazei de date.
În al treilea rând, atunci când se analizează modificările datelor din bazele de date în contextul diferitelor perioade de timp sau alte criterii.
Acestea sunt principalele situații în care este dezvăluită puterea magică reală a instrumentului Excel PivotTable.
Crearea tabelelor pivot și lucrul cu acestea nu afectează conținutul sursei - baza de date! Tabele pivot excelavă permit să priviți aceste date din unghiuri diferite fără a schimba nimic din baza de date!!!
Acesta este ultimul și cel mai important articol al seriei de recenzii pe stocarea informațiilor și gestionarea volumelor mari de date tabulare.
În articolele anterioare ale ciclului, am creat, am învățat, am aplicat și, am intrat.
Creați un șablon de tabel pivot.
Vom crea un tabel pivot în același registru de lucruexcela, unde se află sursa de informații - un tabel de bază de date, dar pe o altă foaie.
Continuăm să lucrăm cu minibaza educațională DB2 „Producția structurilor metalice pe secția Nr. 2”, cu care am lucrat în toate articolele acestui ciclu.
1. Deschideți fișierul în MS Excel.
2. Activăm („click cu mouse-ul”) orice celulă din interiorul tabelului de bază.
3. Executăm comanda meniului principal al programului „Date” - „Tabel pivot...”. Această comandă lansează mini-serviciul PivotTable Wizard.
4. Fără să ne gândim mult timp la opțiunile de alegere a pozițiilor comutatoarelor din ferestrele drop-down ale „Wizard...”, le configuram (mai precis, nu le atingem) așa cum se arată în capturile de ecran de mai jos, deplasarea între ferestre folosind butoanele „Următorul”.
La al doilea pas, „Wizard...” va alege intervalul în sine dacă ați pregătit corect baza de date și ați finalizat pasul 2 din această secțiune a articolului.
5.
La al treilea pas al „Expert...”, faceți clic pe butonul „Terminare”. Șablonul tabel pivot este generat și plasat pe o nouă foaie a aceluiași fișier în care se află baza de date!
Cu excepția momentului șablon gol pe foaia nou creată fisier Excel vedeți panoul „Tabelele pivot” cu instrumentele sale și fereastra „Lista câmpurilor tabelului pivot” care apare atunci când orice celulă din interiorul tabelului pivot este activată. Apar automat, doar le-am așezat lângă șablon și am ajustat dimensiunile. Dacă nu vedeți panoul „Pivot Tables”, atunci apelați-l prin meniul principal cu comanda „View” - „Toolbars” - „Pivot Table”
În MS Excel 2007 și versiunile mai noi, „Wizard...” este desființat deoarece 99% dintre utilizatori nu modifică niciodată setările propuse ale comutatorului și parcurg acești trei pași, pur și simplu fiind de acord cu opțiunile propuse (am făcut și asta).
În MS Excel 2007, folosind comanda „Pivot Table”, apare caseta de dialog „Create Pivot Table”, în care este suficient să specificați sursa de date pentru analiză și locația tabelului pivot care urmează să fie creat.
Crearea de tabele pivot Excel funcționale.
În șablonul creat există patru zone, în care pentru a crea un tabel pivot ar trebui să plasați numele câmpurilor-coloane ale tabelului bazei de date trăgându-le cu butonul stâng al mouse-ului din fereastra „Lista câmpurilor tabelului pivot”. Vă reamintesc că în baza de date un câmp este o coloană cu un nume.
Atenţie!!!
Elementele ferestrei „List of PivotTable Fields” sunt titlurile câmpurilor bazei de date!
1. Dacă plasați unul dintre elementele „Pivot Table Field List” în cel mai mult zona superioarașablon unde va deveni „Câmpul paginii”, atunci obținem un convenabil filtrați înregistrările acestui câmp.
2. Dacă glisați un element (sau două, mai rar trei sau mai multe) din Lista de câmpuri PivotTable în zona din stânga a șablonului, unde va deveni „Câmp de șiruri” antete de rând masă rotativă.
3. Dacă trageți un element (sau două, mai rar - trei sau mai multe) din „Lista...” în zona superioară a șablonului, unde va deveni „Câmp de coloană”, apoi vom obține din înregistrările acestui câmp al tabelului bazei de date titlurile coloanelor masă rotativă.
4. Dacă trageți un element (sau două, mai rar - trei sau mai multe) din „Lista ...” în zona centrală a șablonului, unde va deveni "articol de date", apoi vom obține din înregistrările acestui câmp de bază de date valorile masă rotativă.
Valorile - elementele de date - vor fi localizate în strictă conformitate cu regulile tabelelor bidimensionale, adică la intersecția antetelor de rând corespunzătoare și antetelor de coloană!
Nu foarte clar? Să trecem la exemple practice - totul va deveni clar!
Sarcina numărul 9:
Câte tone de structuri metalice au fost produse pentru fiecare comandă?
Să facem două lucruri pentru a răspunde la această întrebare! Schema acestor acțiuni este prezentată în figura anterioară cu săgeți albastre.
1. Trageți elementul „Comandă” din fereastra „List of Pivot Table Fields” în zona „Row Fields” a șablonului gol.
2.
Trageți elementul „Masa totală, t” din fereastra „Lista de câmpuri din tabelul pivot” în zona „Elemente de date” a șablonului. Rezultatul este în captura de ecran din stânga. Cred că nu este nevoie de explicații.
Se află antetul „Elementelor de date” „Suma peste câmpul Masa totală, t”. de mai sus rubrica „Câmpuri de rând” și în stânga locului în care poate fi amplasată titlul „Câmpuri de coloană”. Fii atent la asta!
Sarcina numărul 10:
Câte tone de structuri din oțel au fost produse pentru fiecare comandă după date?
Continuăm să muncim.
3. Pentru a răspunde la întrebarea sarcinii nr. 10, este suficient să adăugați elementul „Dată” la tabelul nostru pivot din fereastra „Lista câmpurilor tabelului pivot” în zona „Câmpuri de coloană” a șablonului.
4.
Înregistrările pot fi grupate, de exemplu, pe lună. Pentru a face acest lucru, în panoul „Pivot Tables”, faceți clic pe fila „Pivot Table” și selectați „Group and Structure” - „Group...”. În fereastra Grupare, faceți setările în conformitate cu captura de ecran din stânga.
Deoarece toate înregistrările din baza noastră de date au fost făcute în aprilie, după grupare, vedem doar două coloane - „Apr” și „Total mare”. Când înregistrările din mai și iunie apar în baza de date sursă, cele două coloane corespunzătoare sunt adăugate la tabelul pivot. 
Sarcina numărul 11:
Când și câte tone și bucăți de timbre au fost produse în total prin comanda nr. 3?
5. Pentru a începe, trageți câmpul „Comandă” din fereastra „List of Pivot Table Fields” sau direct din Pivot Table în partea de sus a foii în zona „Page Fields” a șablonului.
7. Adăugați câmpul „Cantitate, buc” în zona „Elemente de date”.
S-a format configurația tabelului pivot Excel necesar pentru a răspunde la întrebarea sarcinii.
8. Faceți clic pe butonul săgeată în jos din celula B1 și în lista derulantă, în loc de intrarea „(Toate)”, selectați „3”, corespunzător comenzii nr. 3 care ne interesează și închideți lista făcând clic pe „ butonul OK”.
Răspunsul la întrebarea despre sarcină este în captura de ecran de sub acest text.
Formatarea tabelului pivot.
Dacă tabelul pivot trebuie să fie tipărit și arătat cuiva, sau doar pentru a vă mulțumi puțin, atunci trebuie formatat - dat un aspect decent și reformulat niște titluri „inadecvate”.
Titlurile „Suma pentru câmpul Greutatea totală, t” și „Suma pentru câmpul Cantitate, buc” sună, într-un fel, și de înțeles, dar cumva nu în rusă. Să le redenumim în „Masa totală de produse, t” și „Număr de produse, bucăți”.
1. Să continuăm cu tabelul creat. Activați celula B5 cu mouse-ul.
2. În bara de instrumente PivotTable, faceți clic pe butonul Opțiuni câmp (evidențiat în partea dreaptă în imaginea de mai jos).
3. În fereastra derulantă „Calculul câmpului tabelului pivot” schimbați numele și faceți clic pe butonul „OK”.
4.
Conform unui algoritm similar, redenumim al doilea antet de câmp, după ce mai întâi „inserăm” mouse-ul pe celula B6.
5. În panoul „Tabel Pivot”, faceți clic pe butonul „Format raport” (evidențiat în partea stângă în imaginea de mai sus - deasupra punctului 3).
6. În fereastra „AutoFormat” care apare, selectați dintre opțiunile de formatare propuse, de exemplu, „Raport 6” și faceți clic pe „OK”.
Aspect tabelul nostru pivot s-a schimbat semnificativ.
Concluzie.
Vă atrag atenția asupra mai multor puncte foarte importante!
La schimbarea sursei (de exemplu, adăugarea unei alte înregistrări la baza de date), modificările nu vor avea loc automat în tabelul pivot în sine !!!
Pentru ca modificările din baza de date să se reflecte în tabelul pivot, este necesar să apăsați „manual” de fiecare dată pe butonul „Actualizați datele” (pictograma butonului este „ ! ) în panoul PivotTables!!!
Plasați în zona „Margini de pagină” câmpurile care conțin informații text, care poate deveni rubrica pentru tabelul pivot generat.
În zona Câmpuri de rând și Câmpuri de coloană, plasați câmpurile care conțin informații text, care poate deveni titluri pentru rânduri și coloane a creat un tabel pivot.
În zona „Elemente de date”, plasați în principal informatii numerice.
Continuați prin plasarea câmpurilor așa cum ați face-o pe o bucată de hârtie! Nu vă fie frică să faceți greșeli. Totul se remediază ușor.
Tabelele pivot create cu două sau trei mișcări ale mouse-ului într-una dintre secțiunile anterioare ale acestui articol au răspuns foarte repede la întrebări foarte dificile! Dacă comenzile sunt făcute în decurs de câteva luni, iar numărul de nume de mărci depășește câteva mii, atunci cât timp vă va dura să rezolvați problemele discutate mai sus? Ora? Zi? Excel PivotTables o fac instantaneu, repetat și fără erori!!!
S-au scris multe cărți groase și subțiri despre Excel PivotTables. În primul rând, acestea sunt cărți de Bill Jelen („Mr. Excel”) și Michael Alexander. Pentru cei care sunt interesați de acest subiect, recomand să le citească.
Desigur, este foarte greu, aproape imposibil, să vorbim în detaliu despre acest instrument minunat într-un scurt articol. Pe de o parte, am vrut să ating aspecte mai importante din punct de vedere practic, dar, pe de altă parte, în articolul introductiv nu am vrut să „intru în junglă”, sperierea utilizatorilor cu volumul și complexitatea informațiilor. Dacă acest „instrument magic” MS Excel stârnește interesul cititorilor blogului, atunci în viitor acest subiect va fi continuat cu lansarea unei mici broșuri cu recomandări practice importante și valoroase.
Asigurați-vă că utilizați Excel PivotTables! Valoarea ta ca specialist va creste de multe ori!
Pe această serie de șase articole, care este o prezentare generală a subiectului procesarea unor cantități mari de informații în Excelefectuat.
implor respectând opera autorului Abonati-va pentru anunţuri de articole în fereastra situată la sfârșitul fiecărui articol sau în fereastra din partea de sus a paginii!
Dragi cititori, lăsați întrebări, feedback și comentarii în comentariile din partea de jos a paginii.
Sursa de date pentru un PivotTable este o listă de date, unde, de regulă, fiecare coloană acționează ca un câmp în PivotTable. Dar dacă ți-ar veni un tabel care arată doar ca un tabel pivot (este formatat și arată ca acesta, dar este imposibil să folosești instrumentele pentru a lucra cu tabelele pivot). Și trebuie să îl transformați într-o listă de date, de exemplu. a executa operare inversă. În această postare, veți învăța cum să convertiți un tabel pivot cu două variabile într-o listă de date.
Figura arată principiul pe care l-am descris. Acestea. în intervalul A2:E5 este tabelul pivot original, care este convertit într-o listă de date (interval H2:J14). Al doilea tabel reprezintă același set de date, doar dintr-un unghi diferit. Fiecare valoare a tabelului pivot original arată ca un șir, constând dintr-un element de câmp rând, un câmp de coloană și valoarea lor corespunzătoare. Această afișare a datelor este utilă atunci când trebuie să sortați și să manipulați datele în alte moduri.
Pentru a realiza posibilitatea creării unei astfel de liste, vom folosi instrumentele tabelului pivot. Să adăugăm un buton Expert Tabel Pivot la panoul de acces rapid, care nu ne este disponibil pe panglică, dar a rămas ca un vestigiu din versiunile anterioare de Excel.
Navigați la fila Fișier -> Opțiuni. În caseta de dialog care apare Opțiuniexcela, fila Panou acces rapid în câmpul din stânga găsiți articolul PivotTable și PivotChart Wizard si adauga-l la cel potrivit. Faceți clic pe OK.
Acum aveți o pictogramă nouă în Bara de instrumente Acces rapid.
Faceți clic pe această filă pentru a lansa Expert Tabel Pivot.
Primul pas al expertului este selectarea tipului de sursă de date a tabelului pivot. Instalarea comutatorului În mai multe intervale de consolidareși faceți clic Mai departe.
La pasul 2a, specificați cum trebuie create câmpurile paginii. Puneți comutatorul Creați câmpuri de pagină -> Următorul.
La pasul 2b, pe teren Gamă selectați intervalul care conține datele și faceți clic Adăuga.În cazul nostru, aceasta va fi locația tabelului pivot original A1:E4.
În al treilea pas, trebuie să decideți unde doriți să plasați tabelul pivot și să faceți clic pe butonul Gata.
Excel va crea un tabel pivot cu datele. În partea stângă a ecranului veți vedea o zonă Lista câmpurilor din tabelul pivot. Eliminați toate elementele din câmpurile de rânduri și coloane. Am scris mai multe despre asta într-un articol anterior.
Veți ajunge la un mic tabel pivot format dintr-o celulă care conține suma tuturor valorilor din tabelul original.
Faceți dublu clic pe această celulă. Excel va crea o nouă foaie care va conține un tabel cu o listă de valori.
Titlurile acestui tabel reprezintă informatii generale probabil că doriți să le faceți mai informative.
Un tabel pivot este utilizat pentru a analiza rapid o cantitate mare de date. Vă permite să combinați informații din diferite tabele și foi, să calculați rezultatul general. Acest instrument analitic universal extinde semnificativ capacitățile programului Excel.
Puteți genera totaluri noi pe baza parametrilor inițiali schimbând rândurile și coloanele. Puteți filtra datele afișând diferite elemente. Și, de asemenea, detaliază vizual zona.
Tabel Pivot în Excel
De exemplu, folosim tabelul vânzărilor de mărfuri în diferite ramuri comerciale.
Din farfurie se vede in ce departament, ce, cand si pentru ce suma a fost vandut. Pentru a afla valoarea vânzărilor pentru fiecare departament, va trebui să calculați manual pe calculator. Sau faceți o altă foaie de calcul Excel, unde folosiți formule pentru a afișa totalurile. Analizarea informațiilor în acest fel este neproductivă. Nu suficient de mult pentru a greși.
Cea mai rațională soluție este crearea unui tabel pivot în Excel:
Simplu, rapid și de înaltă calitate.
Nuanțe importante:
- Trebuie completat primul rând din intervalul specificat pentru acumularea de date.
- În placa de bază, fiecare coloană ar trebui să aibă propriul său titlu - este mai ușor să configurați un raport rezumat.
- În Excel, puteți utiliza ca sursă de informații Tabele de acces, SQL Server si etc.
Cum să faci un tabel pivot din mai multe tabele
Adesea doriți să creați rapoarte rezumative din mai multe tabele. Există câteva panouri informative. Trebuie să le combinăm într-un singur comun. Pentru știință, să venim cu resturi în depozitele din două magazine.
Procedura de creare a unui tabel pivot din mai multe foi este aceeași.
Să creăm un raport utilizând expertul PivotTable:
După cum puteți vedea, în doar câteva clicuri, puteți crea rapoarte complexe din mai multe foi sau tabele cu cantități diferite de informații.
Cum să lucrați cu tabele pivot în Excel
Să începem cu cel mai simplu: adăugarea și eliminarea coloanelor. De exemplu, luați în considerare un tabel rezumat al vânzărilor pentru diferite departamente (vezi mai sus).
În dreapta tabelului pivot, aveam o zonă de activități în care selectam coloane din lista de câmpuri. Dacă a dispărut, faceți clic pe tabletă.
Să adăugăm un alt câmp la tabelul pivot pentru raport. Pentru a face acest lucru, bifați caseta de lângă „Dată” (sau vizavi de „Produs”). Raportul se schimbă imediat - apare dinamica vânzărilor pe zi în fiecare departament.
Gruparea datelor într-un raport pe luni. Pentru a face acest lucru, faceți clic Click dreapta mouse-ul peste câmpul „Dată”. Faceți clic pe „Grup”. Selectați „pe lună”. Rezultă un tabel pivot ca acesta:
La modificați opțiunile din tabelul pivot, este suficient să debifați casetele de lângă câmpurile de rând existente și să le setați pentru alte câmpuri. Să facem un raport după numele produsului, nu după departament.
Și iată ce se întâmplă dacă eliminăm „data” și adăugăm „departamentul”:
Dar un astfel de raport se poate face dacă trageți câmpurile între diferite zone:
Pentru a schimba titlul unui rând în titlul unei coloane, selectați acest titlu, faceți clic pe meniul pop-up. Faceți clic pe „Mutați la titlurile coloanei”. În acest fel, am mutat data în coloane.
Punem câmpul „Departament” în fața denumirilor mărfurilor. Folosind secțiunea de meniu „mutare la început”.
Afișați detaliile unui anumit produs. Pe exemplul celui de-al doilea tabel pivot, care afișează soldurile din depozite. Selectați o celulă. Faceți clic pe butonul din dreapta al mouse-ului - „extinde”.
În meniul care se deschide, selectați câmpul cu datele pe care doriți să le afișați.
Când facem clic pe un tabel pivot, devine disponibilă o filă cu opțiuni de raport. Cu ajutorul acestuia, puteți modifica titlurile, sursele de date, informațiile de grup.
Verificarea corectitudinii facturilor de utilitati emise
Cu ajutorul tabelelor pivot Excel, este ușor să verificați cât de corect percepe chiria organizațiilor de servicii. Un alt punct pozitiv este economiile. Dacă controlăm lunar câtă energie electrică și gaz se consumă, putem găsi o rezervă pentru a economisi bani la plata unui apartament.
Pentru început, propunem să alcătuim un tabel rezumativ al tarifelor pentru toate facturile de utilități. Pentru diferite orașe, datele vor fi diferite.
De exemplu, am făcut un tabel rezumativ al tarifelor pentru Moscova:
În scop educațional, să luăm o familie de 4 persoane care locuiește într-un apartament de 60 mp. m. Pentru a controla facturile de utilități, trebuie să creați tabele pentru calculul pentru fiecare lună.
Prima coloană = prima coloană din tabelul pivot. A doua este formula pentru calcularea vederii:
Tarif * numarul de persoane / citirea contorului / suprafata
Formulele noastre se referă la foaia în care se află tabelul rezumativ cu tarife.
Dacă se aplică beneficii la calcularea facturilor de utilități, acestea pot fi și ele introduse în formule. Solicitați informații despre angajamente în departamentul de contabilitate al organizației dvs. de servicii. Când tarifele se schimbă, schimbați doar datele din celule.
Un tabel pivot este un instrument care vă ajută să analizați datele companiei. Puteți crea un astfel de tabel în diferite editoare de text.
Pentru a crea, trebuie să selectați titluri atât pentru rânduri, cât și pentru coloane din raportul generat. Cel mai convenabil mod de a face rapoarte este în Excel. Puteți aplica filtrul întregului raport sau unei anumite părți a acestuia.
cum să faci un tabel pivot în excel
Să aruncăm o privire mai întâi la versiunea din 2007. Unii utilizatori au încă versiunea veche a editorului. Pentru a începe aveți nevoie creați un tabel pentru a construi un raport. Nu trebuie să conțină celule și linii goale. Să analizăm procesul de creare a unui raport folosind cel mai simplu exemplu:
- Începând cu Excel 2007. Selectați fila „Inserare” din bara de sus.
- Selectați „Creați tabele pivot recomandate”.
- Va apărea o fereastră care arată opțiunile de cadru. Puteți previzualiza cum va arăta raportul dvs. făcând clic pe aspect. După aceea, îl puteți alege pe cel principal. Faceți clic pe OK și raportul va apărea pe foaie. În plus, Excel însuși va determina ordinea rândurilor.
- Puteți filtra și sorta valorile în program. Există o pictogramă specială pe panou pentru aceasta.
Acesta este cel mai simplu exemplu despre cum puteți crea un tabel pivot versiune veche excela.
Tabelele pivot în Excel 2010 și 2013 - un exemplu de lucru în Excel
Crearea unui raport în Excel 2010 și 2013 este aproape aceeași. Procesul în aceste versiuni este ușor diferit de crearea în versiunea 2007. Dar, unele momente similar: înainte de a începe, trebuie să creați un tabel simplu în care specificați toate datele de care aveți nevoie. Linia de sus ar trebui să conțină numele coloanelor.
Acum puteți crea un raport în Excel:
Ce se poate face cu foaia:
- Veți avea acces la o bară de instrumente pentru a gestiona valorile. Toate câmpurile și titlurile acestora vor fi listate în fila „Selectați câmpurile de adăugat la raport”. Pentru a adăuga elementele necesare, trebuie să selectați câmpurile necesare și lista va fi creată.
- Este posibil să controlați locația câmpurilor. Puteți să glisați și să plasați coloanele în ferestrele dorite, precum și să sortați în ordinea care vi se potrivește. De asemenea, puteți filtra articole. Pentru a face acest lucru, trageți elementul în fereastra „Filtru”. Dacă trebuie să calculați pentru un câmp separat, trageți-l în fereastra „Valoare”.
- Poți să folosești opțiuni suplimentare. Le puteți găsi după următorul principiu: „Parametri” „Lucrul cu tabele pivot”. Există o mulțime de instrumente care au un număr mare de setări și funcții suplimentare.
Cum se creează un tabel pivot folosind expertul PivotTable
Instrumentul PivotTable Wizard nu apare în panoul principal. De aceea trebuie mai întâi activat. Pentru a face acest lucru, accesați „Fișier”, „Opțiuni” „Bara de instrumente Acces rapid”. în fața dumneavoastră se va deschide o listă„selectați comenzi din”, bifați „Comenzi de pe panglică”. Acum selectați expertul PivotTable și adăugați-l. După toate acțiunile efectuate, pictograma funcției selectate va apărea pe panoul principal.
Acum puteți continua chiar cu procesul de creare a unui raport rezumat utilizând expertul:
Dacă a fost selectat ca sursă un raport simplu, care are toate datele necesare pentru raport, trebuie doar să îl faci. Setează intervalul, adică alege datele masă simplă. Alegeți un loc de plasat și faceți clic pe butonul „Finish”.
Dacă selectați o sursă ale cărei date trebuie consolidate din alte surse, atunci trebuie bifat vizavi de acest articol. Acum trebuie să specificați cum veți crea câmpurile paginii. De asemenea, va trebui să specificați un interval. Pentru a face acest lucru, mai întâi selectați un interval și faceți clic pe butonul „adăugați”. Se adaugă și valorile ulterioare.
Pentru a nu se confunda în intervale, trebuie să li se dea nume. Pentru a face acest lucru, selectați unul dintre intervale lista și specificați numărul de câmpuri pentru aceasta, după care dați fiecărui câmp un nume. Și așa cu toate gamele.
Excel 2010 tabele pivot cu mai multe foi
Dacă informațiile din rapoarte sunt de același tip, atunci este mai ușor să utilizați vrăjitorul tabel pivot. Dacă raportul are o structură complexă, atunci trebuie să acționați diferit. De exemplu, sunt doua mese, unul conține primirea mărfurilor, iar celălalt - cantitatea de mărfuri vândută în diferite magazine. Principalul lucru este să aveți aceleași titluri, mai puțin timp le va transfera într-un singur raport. În acest caz, raportul se face după cum urmează:
Acum trebuie să creăm un raport rezumat:
- Introduce.
- Raport consolidat.
- Introduceți intervalul și locația.
Dar ce se întâmplă dacă trebuie să creați un raport din mai multe tabele. Este greu să lucrezi manual. Pentru asta poți să folosești supliment special EXCEL. Este descărcat de pe site-ul dezvoltatorului.
Așa că ne-am dat seama cum creați tabele pivotîn excel. Trebuie să arătați puțină perseverență și atenție, iar apoi rezultatul nu vă va face să așteptați.
Se încarcă...