Cum se creează un raport în Excel. Cum se face un raport cu aspect profesional în Excel

Nu este prima dată când Renat este scriitor invitat pe Lifehacker. Am publicat anterior materiale excelente de la el despre cum să creați un plan de antrenament: și resurse online, precum și crearea unui plan de antrenament.

Acest articol conține tehnici simple care vă permit. Acestea sunt utile în special pentru cei care sunt angajați în raportarea managementului, pregătesc o varietate de rapoarte analitice pe baza descărcărilor din 1C și din alte rapoarte, formează prezentări și diagrame din acestea pentru management. Nu mă prefac a fi o noutate absolută - într-o formă sau alta, aceste tehnici au fost probabil discutate pe forumuri sau.

Alternative simple la VLOOKUP și HLOOKUP, dacă valorile dorite nu se află în prima coloană a tabelului: LOOKUP, INDEX + SEARCH

Rezultat:

V acest caz, pe lângă funcția CHAR (pentru a afișa ghilimele), este utilizată funcția IF, care vă permite să modificați textul în funcție de tendința de vânzare pozitivă și funcția TEXT, care vă permite să afișați numărul în orice format. Sintaxa sa este descrisă mai jos:

TEXT ( sens; format)

Formatul este specificat între ghilimele, la fel ca și cum ați fi introdus un format personalizat în fereastra Formatare celule.

Textele mai complexe pot fi, de asemenea, automatizate. În practica mea, a existat automatizarea comentariilor lungi, dar de rutină către raportarea conducerii în formatul „INDICATOR a scăzut / a crescut cu XX față de plan, în principal datorită creșterii / declinului FACTOR1 cu XX, creșterea / declinului FACTOR2 cu YY ... ”cu o listă în schimbare de factori. Dacă scrieți adesea astfel de comentarii și procesul de scriere a acestora poate fi algoritmizat, merită, odată nedumerit, să creați o formulă sau o macro care să vă salveze cel puțin o parte din lucrare.

Cum se stochează date în fiecare celulă după concatenare

Când îmbinați celulele, se păstrează o singură valoare. Excel avertizează despre acest lucru când încearcă să îmbine celulele:

În consecință, dacă ați avea o formulă care depindea de fiecare celulă, aceasta ar înceta să funcționeze după combinarea acestora (eroare # N / A în rândurile 3-4 ale exemplului):

Pentru a îmbina celulele și a păstra în continuare datele din fiecare dintre ele (poate aveți o formulă ca în acest exemplu abstract; poate doriți să îmbinați celulele, dar păstrați toate datele pentru viitor sau să le ascundeți în mod intenționat), îmbinați orice celule de pe foaie , selectați-le, apoi utilizați comanda Format Painter pentru a transfera formatarea în celulele pe care trebuie să le combinați:

Cum să construiți un pivot din mai multe surse de date

Dacă trebuie să construiți un pivot din mai multe surse de date simultan, va trebui să adăugați la panglică sau panou acces rapid„PivotTable and Chart Wizard”, care are o astfel de opțiune.

Puteți face acest lucru după cum urmează: „Fișier” → „Opțiuni” → „Bara de instrumente de acces rapid” → „Toate comenzile” → „Tabel pivot și expert grafic” → „Adăugare”:

După aceea, o pictogramă corespunzătoare va apărea pe panglică, făcând clic pe care apelează același expert:

Când faceți clic pe ea, apare o casetă de dialog:

În acesta, trebuie să selectați elementul „În mai multe intervale de consolidare” și să faceți clic pe „Următorul”. În paragraful următor, puteți selecta „Creați un câmp de pagină” sau „Creați câmpuri de pagină”. Dacă doriți să veniți independent cu un nume pentru fiecare dintre sursele de date, selectați al doilea element:

În fereastra următoare, adăugați toate intervalele pe baza cărora va fi construit pivotul și dați-le nume:

După aceea, în ultima casetă de dialog, specificați unde va fi plasat raportul tabelului pivot - pe o foaie existentă sau nouă:

Raportul tabelului pivot este gata. În filtrul „Pagina 1”, puteți selecta doar una dintre sursele de date, dacă este necesar:

Cum se calculează numărul aparițiilor textului A din textul B („Tariful MTS SuperMTS” - două apariții ale abrevierii MTS)

În acest exemplu, coloana A conține mai multe linii de text, iar sarcina noastră este de a afla de câte ori fiecare dintre ele conține textul de căutare situat în celula E1:

Pentru a rezolva această problemă, puteți utiliza o formulă complexă formată din următoarele funcții:

  1. DLSTR (LEN) - calculează lungimea textului, singurul argument este textul. Exemplu: DLSTR („mașină”) = 6.
  2. SUBSTITUTE - înlocuiește un text specific dintr-un șir de text cu altul. Sintaxă: SUBSTITUTE ( text; text_vechi; text_ nou). Exemplu: SUBSTITUTE („mașină”; „auto”; „”) = „mobil”.
  3. UPPER - înlocuiește toate caracterele dintr-un șir cu majuscule. Singurul argument este textul. Exemplu: SUPERIOR („mașină”) = „AUTO”. Avem nevoie de această funcție pentru a efectua căutări care nu fac sensibilitate la majuscule. La urma urmei, UPPER ("car") = UPPER ("Machine")

Pentru a găsi apariția unui anumit șir de text într-un altul, trebuie să ștergeți toate aparițiile sale în cel original și să comparați lungimea șirului rezultat cu cel original:

DLSTR („Tariful MTS Super MTS”) - DLSTR („ Super Tarif ”) = 6

Și apoi împărțiți această diferență la lungimea șirului pe care îl căutam:

6 / DLSTR („MTS”) = 2

Exact de două ori linia „MTS” este inclusă în cea originală.

Rămâne să scriem acest algoritm în limbajul formulelor (să denotăm textul în care căutăm apariții prin „text” și cel al cărui număr de apariții ne interesează prin „căutat”):

= (DLSTR ( text) -LSTR (SUBSTITUT (SUPERIOR ( text); SUPERIOR ( căutat); „”))) / DLSTR ( căutat)

În exemplul nostru, formula arată astfel:

= (DLSTR (A2) -LSTR (SUBSTITUT (UPPER (A2), UPPER ($ E $ 1), „”))) / DLSTR ($ E $ 1)

Tabelele pivot în Excel sunt un instrument puternic de raportare. Este util mai ales în cazurile în care utilizatorul nu funcționează bine cu formule și îi este dificil să analizeze în mod independent datele. În acest articol, vom analiza cum să creați corect astfel de tabele și ce posibilități există pentru aceasta în editorul Excel. Nu trebuie să descărcați niciun fișier pentru aceasta. Instruirea este disponibilă online.

Primul pas este crearea unui fel de masă. Este de dorit să existe mai multe coloane. În acest caz, unele informații ar trebui repetate, deoarece numai în acest caz va fi posibil să se facă un fel de analiză a informațiilor introduse.

De exemplu, luați în considerare aceleași costuri financiare în luni diferite.

Crearea tabelelor pivot

Pentru a construi un astfel de tabel, trebuie să faceți următoarele.

  1. Pentru început, trebuie să îl selectați complet.

  1. Apoi accesați fila „Inserați”. Faceți clic pe pictograma „Tabel”. În meniul care apare, selectați elementul „Pivot Table”.

  1. Ca urmare, va apărea o fereastră în care trebuie să specificați mai mulți parametri de bază pentru construirea unui tabel pivot. Primul pas este selectarea zonei de date pe baza căreia va fi efectuată analiza. Dacă ați selectat anterior un tabel, atunci un link către acesta va fi înlocuit automat. În caz contrar, va trebui evidențiat.

  1. Vi se va cere apoi să indicați exact unde va avea loc construcția. Este mai bine să selectați elementul „Pe foaia existentă”, deoarece va fi incomod să analizați informațiile atunci când totul este împrăștiat pe mai multe foi. Apoi, trebuie să specificați intervalul. Pentru a face acest lucru, faceți clic pe pictograma de lângă câmpul de introducere.

  1. Imediat după aceasta, Expertul pentru tabelul pivot se va prăbuși la o dimensiune mică. In plus aspect cursor. Va trebui să faceți un clic stâng al mouse-ului în orice loc convenabil pentru dvs.

  1. Ca urmare, un link către celula specificată va fi înlocuit automat. Apoi, trebuie să faceți clic pe pictograma din partea dreaptă a ferestrei pentru a o restabili la dimensiunea inițială.

  1. Pentru a finaliza setările, faceți clic pe butonul „OK”.

  1. Ca rezultat, veți vedea un șablon gol pentru lucrul cu tabele pivot.

  1. În această etapă, trebuie să specificați câmpul care va fi:
    1. coloană;
    2. şir;
    3. valoare pentru analiză.

Puteți alege orice doriți. Totul depinde de ce fel de informații doriți să primiți.

  1. Pentru a adăuga orice câmp, trebuie să faceți clic stânga pe el și, fără a elibera degetul, trageți-l în zona dorită. În acest caz, cursorul își va schimba aspectul.

  1. Puteți elibera degetul numai atunci când dispare cercul tăiat. La fel, trebuie să glisați și să fixați toate câmpurile din tabelul dvs.

  1. Pentru a vedea întregul rezultat, puteți închide panou lateral setări. Pentru a face acest lucru, trebuie doar să faceți clic pe cruce.

  1. Ca urmare, veți vedea următoarele. Cu acest instrument, veți putea reuni suma cheltuielilor în fiecare lună pentru fiecare poziție. În plus, sunt disponibile informații despre totalul total.

  1. Dacă nu vă place masa, puteți încerca să o construiți puțin diferit. Pentru a face acest lucru, trebuie să schimbați câmpurile din zonele de construcție.

  1. Închideți din nou asistentul pentru construcții.

  1. De data aceasta, vedem că tabelul pivot a devenit mult mai mare, deoarece acum coloanele nu sunt luni, ci categorii de cheltuieli.

Dacă nu puteți construi singuri masa, puteți conta întotdeauna pe ajutorul editorului. În Excel, este posibil să creați astfel de obiecte în mod automat.

Pentru a face acest lucru, trebuie să faceți următoarele, dar mai întâi selectați toate informațiile în întregime.

  1. Faceți clic pe fila „Inserați”. Apoi faceți clic pe pictograma „Tabel”. În meniul care apare, selectați al doilea element.

  1. Imediat după aceea, va apărea o fereastră în care vor exista diverse exemple de construcție. Opțiuni similare sunt oferite pe baza mai multor coloane. Numărul de șabloane depinde direct de numărul acestora.

  1. Când plasați cursorul peste fiecare element, va fi disponibilă o previzualizare a rezultatului. Este mult mai convenabil să lucrezi în acest fel.

  1. Puteți alege ceea ce vă place cel mai mult.

  1. Pentru a insera opțiunea selectată, trebuie doar să faceți clic pe butonul „OK”.

  1. Drept urmare, veți obține următorul rezultat.

Vă rugăm să rețineți: tabelul a fost creat pe o foaie nouă. Acest lucru se va întâmpla de fiecare dată când este utilizat constructorul.

Odată ce adăugați (indiferent cum) masă rotativă, veți vedea în bara de instrumente filă nouă"Analiză". Conține un număr mare de instrumente și funcții diferite.

Să luăm în considerare fiecare dintre ele mai detaliat.

Dând clic pe butonul marcat în captura de ecran, puteți face următoarele:

  • schimbați numele;

  • apelați fereastra de setări.

Veți vedea o mulțime de lucruri interesante în fereastra de opțiuni.

Câmp activ

Cu acest instrument, puteți face următoarele:

  1. Mai întâi trebuie să selectați o celulă. Apoi faceți clic pe butonul „Câmp activ”. În meniul care apare, faceți clic pe elementul „Parametri câmp”.

  1. Imediat după aceea, veți vedea următoarea fereastră. Aici puteți specifica tipul de operație care ar trebui utilizată pentru a aplatiza datele în câmpul selectat.

  1. În plus, puteți personaliza formatul numerelor. Pentru a face acest lucru, trebuie să faceți clic pe butonul corespunzător.

  1. Ca urmare, va apărea fereastra „Formatare celule”.

Aici puteți specifica în ce formă trebuie afișat rezultatul analizei informațiilor.

Datorită acestui instrument, puteți personaliza gruparea după valorile selectate.

Introduceți felia

Editor Microsoft Excel vă permite să creați tabele pivot interactive. În acest caz, nu trebuie să faceți nimic complicat.

  1. Selectați o coloană. Apoi faceți clic pe butonul „Insert Slicer”.
  2. În fereastra care apare, ca exemplu, selectați unul dintre câmpurile propuse (în viitor, le puteți selecta într-un număr nelimitat). După ce este selectat ceva, butonul „OK” este imediat activat. Apasa pe el.

  1. Ca urmare, va apărea o fereastră mică care poate fi mutată oriunde. Va oferi tot posibilul valori unice care sunt în acest domeniu. Datorită acestui instrument, veți putea retrage suma doar pentru anumite luni (în acest caz). În mod implicit, sunt afișate informații pentru toată durata.

  1. Puteți face clic pe oricare dintre elemente. Imediat după aceea, toate valorile din câmpul Suma se vor schimba.

  1. Astfel, va fi posibil să alegeți orice perioadă de timp.

  1. În orice moment, totul poate fi readus la forma sa originală. Pentru a face acest lucru, faceți clic pe pictograma din dreapta colțul superior această fereastră.

În acest caz, am reușit să sortăm raportul în funcție de lună, deoarece aveam un câmp corespunzător. Dar există un instrument mai puternic pentru lucrul cu datele.

Dacă faceți clic pe butonul corespunzător de pe bara de instrumente, atunci, cel mai probabil, veți vedea această eroare. Faptul este că nu există celule în tabelul nostru care să aibă un format de date „Data” explicit.

De exemplu, să creăm un mic tabel cu date diferite.

Apoi, va trebui să construiți un tabel pivot.

Accesați din nou fila „Inserați”. Faceți clic pe pictograma „Tabel”. În submeniul care apare, selectați opțiunea de care avem nevoie.

  1. Ni se va cere apoi să selectăm o gamă de valori.

  1. Pentru a face acest lucru, este suficient să selectați întregul tabel ca întreg.

  1. Imediat după aceea, adresa va fi înlocuită automat. Totul este foarte simplu aici, deoarece este conceput pentru manechine. Pentru a finaliza construcția, faceți clic pe butonul „OK”.

  1. Editorul Excel ne va oferi o singură opțiune, deoarece tabelul este foarte simplu (de exemplu, nu aveți nevoie de mai multe).

  1. Încercați din nou făcând clic pe pictograma „Inserați o cronologie” (aflată în fila „Analiză”).

  1. De această dată nu vor exista erori. Vi se va solicita să selectați un câmp de sortat. Bifați caseta și faceți clic pe butonul „OK”.

  1. Datorită acestui fapt, va apărea o fereastră în care puteți selecta data dorită folosind glisorul.

  1. Alegem încă o lună și nu există date, deoarece toate costurile din tabel sunt indicate numai pentru luna martie.

Dacă ați făcut modificări la datele originale și, din anumite motive, acestea nu au apărut în tabelul pivot, îl puteți actualiza oricând manual. Pentru a face acest lucru, trebuie doar să faceți clic pe butonul corespunzător de pe bara de instrumente.

Dacă decideți să modificați câmpurile pe baza cărora ar trebui să aibă loc construcția, atunci este mult mai ușor să faceți acest lucru în setări și să nu ștergeți tabelul și să îl recreați ținând cont de noile preferințe.

Pentru a face acest lucru, faceți clic pe pictograma „Sursă de date”. Apoi selectați elementul de meniu cu același nume.

Ca urmare, va apărea o fereastră în care puteți selecta din nou cantitatea necesară de informații.

Acțiuni

Cu acest instrument puteți:

  • curăță masa;
  • pune in evidenta;
  • mișcă-l.

Calcule

Dacă calculele din tabel nu sunt suficiente sau nu corespund nevoilor dvs., puteți face oricând modificările. Dând clic pe pictograma acestui instrument, veți vedea următoarele opțiuni.

Acestea includ:

  • câmp calculat;

  • obiect calculat;

  • ordinea calculelor (formulele adăugate sunt afișate în listă);

  • afișează formule (nu există informații disponibile, deoarece nu există formule adăugate).

Aici puteți crea o diagramă pivot sau puteți schimba tipul de tabel recomandat.

Cu acest instrument, puteți personaliza aspectul spațiului de lucru al editorului.

Datorită acestui fapt, puteți:

  • personalizați afișarea barei laterale cu o listă de câmpuri;

  • activați sau dezactivați butoanele plus / min cu;

  • personalizați afișarea anteturilor de câmp.

Când lucrați cu tabele pivot, pe lângă fila „Analiză”, va apărea și alta - „Proiectare”. Aici puteți schimba aspectul obiectului dvs., dincolo de recunoaștere, din valoarea implicită.

Puteți personaliza:

  • subtotale:
    • nu arata;
    • afișează toate totalurile în partea de jos;
    • afișează toate totalurile în antet.

  • rezultate generale:
    • dezactivați pentru rânduri și coloane;
    • activați pentru rânduri și coloane;
    • activați numai pentru șiruri;
    • activați numai pentru coloane.

  • aspectul raportului:
    • arată într-o formă concisă;
    • spectacol sub forma unei structuri;
    • arată în formă tabelară;
    • repetați toate etichetele articolelor;
    • nu repetați etichetele articolelor.

  • linii goale:
    • introduceți o linie goală după fiecare element;
    • eliminați rândul gol după fiecare element.

  • parametrii stilului tabelului pivot (aici puteți activa / dezactiva fiecare element):
    • anteturi de rând;
    • anteturi de coloană;
    • linii alternative;
    • alternând coloane.

  • personalizați stilul elementelor.

Pentru a vedea mai multe opțiuni diferite, trebuie să faceți clic pe triunghiul din colțul din dreapta jos al acestui instrument.

Imediat după aceea, va apărea o listă uriașă. Puteți alege orice doriți. Când plasați cursorul peste fiecare dintre șabloane, tabelul dvs. se va schimba (acest lucru se face pentru previzualizare). Modificările nu vor intra în vigoare decât după ce faceți clic pe oricare dintre opțiunile furnizate.

În plus, dacă doriți, vă puteți crea propriul stil de design.

Tot aici puteți modifica ordinea de afișare a liniilor. Uneori, acest lucru este necesar pentru confortul analizei costurilor. Mai ales dacă lista este foarte lungă, deoarece este mai ușor să găsești poziția cerută alfabetic decât să parcurgi lista de mai multe ori.

Pentru a face acest lucru, trebuie să faceți următoarele.

  1. Faceți clic pe triunghiul de lângă câmpul necesar.
  2. Ca urmare, veți vedea următorul meniu. Aici puteți selecta opțiunea de sortare dorită („de la A la Z” sau „de la Z la A”).

Dacă opțiunea standard nu este suficientă, puteți face clic pe articolul „ Opțiuni suplimentare sortare ".

Ca urmare, veți vedea următoarea fereastră. Pentru setări mai detaliate, trebuie să faceți clic pe butonul „Avansat”.

Totul aici este configurat automat. Dacă debifați această casetă, veți putea specifica cheia de care aveți nevoie.

Tabelele pivot în Excel 2003

Pașii de mai sus sunt potriviți editorilor moderni (2007, 2010, 2013 și 2016). V versiune veche totul arată diferit. Există, desigur, mult mai puține oportunități acolo.

Pentru a crea un tabel pivot în Excel 2003, trebuie să faceți următoarele.

  1. Accesați secțiunea „Date” din meniu și selectați elementul corespunzător.

  1. Ca urmare, va apărea un vrăjitor pentru crearea unor astfel de obiecte.

  1. După ce faceți clic pe butonul „Următorul”, se va deschide o fereastră în care trebuie să specificați gama de celule. Apoi faceți din nou clic pe „Următorul”.

  1. Pentru a finaliza setările, faceți clic pe „Finalizare”.

  1. Ca urmare, veți vedea următoarele. Aici trebuie să trageți câmpurile în zonele corespunzătoare.

  1. De exemplu, puteți obține următorul rezultat.

Devine evident că este mult mai bine să creați astfel de rapoarte în editorii moderni.

Concluzie

Acest articol a acoperit toate complexitățile lucrului cu tabele pivot în Excel. Dacă ceva nu funcționează pentru dvs., poate selectați câmpuri greșite sau sunt foarte puține - pentru a crea un astfel de obiect, aveți nevoie de mai multe coloane cu valori duplicat.

Dacă acest tutorial nu vă este suficient, Informații suplimentare pot fi găsite în ajutorul online al Microsoft.

Instrucțiuni video

Pentru cei care au încă întrebări fără răspuns, mai jos este atașat un videoclip cu comentarii la instrucțiunile descrise mai sus.

Urmăriți videoclipul articolului:


Rapoarte tipice incluse în module de soluții gata făcute Business Intelligence (BI) vă permit să răspundeți la 99% din cele mai populare interogări ale utilizatorilor.

Dar, dacă se dorește, analiștii sau orice alți utilizatori își pot crea rapoartele în Excel pe cuburi OLAP.

Mulțumită fiecare dintre modulele noastre de Business Intelligence (BI) se bazează pe un cub OLAP, prin conectarea la care utilizatorii pot construi cu ușurință orice raport în Excel folosind tabele pivot.

De asemenea, rapoartele încorporate în Excel pe cuburi OLAP pot fi salvate și, la redeschidere, vor fi actualizate automat cu datele curente de la sistem corporativ Business Intelligence (BI) .

Exemple de rapoarte construite pe cuburi OLAP:

Descrierea rapoartelor:

(Pentru procesul de generare a rapoartelor, consultați videoclipul acestui articol)

1. Raport privind vânzările în ruble și unități:
  • perioadă: pentru anul curent
  • indicatori: fapt al vânzărilor
  • detaliind: coloane / trimestru, lună
  • detaliind: linii / canale de vânzare, clienți, mărci, produse

2. Raport asupra structurii vânzărilor în ruble și unități (filtru):
  • perioadă: pentru anul curent
  • indicatori: fapt al vânzărilor, fapt al vânzărilor în procente
  • detaliind: coloane / trimestru, lună
  • detaliind: linii / canale de vânzare, mărci

3. Raport privind implementarea planului de vânzări în ruble și unități (filtru):
  • perioadă: pentru anul curent
  • indicatori: plan de vânzări, fapt de vânzări, îndeplinirea planului
  • detaliind: coloane / trimestru, lună
  • detaliind: linii / canale de vânzare, clienți, categorii, produse

4. Raport privind creșterea vânzărilor în ruble și unități (filtru):
  • perioadă
  • indicatori
  • detaliind: linii / districte federale, regiuni, categorii, produse

5. Raport privind creșterea vânzărilor în timp în ruble și unități (filtru):
  • perioadă: pentru anul curent în comparație cu anul precedent
  • indicatori: vânzările anului precedent, planul de vânzări, faptul vânzărilor, îndeplinirea planului, creșterea vânzărilor în termeni absoluți și relativi față de anul precedent
  • detaliind: linii / trimestru, lună, categorii, produse

6. Raport de creanțe:
  • perioadă: pentru data curentă
  • indicatori: suma documentului, creanțe totale, creanțe curente, creanțe restante pe perioade - până la 15 zile, până la 30 de zile, până la 60 de zile, până la 90 de zile, până la 180 de zile, până la 365 de zile sau mai mult, structura creanțelor
  • detaliind: linii / canale de vânzare, contrapartidă, data plății documentului, data documentului, document

7. Raportul dinamicii creanțelor:
  • perioadă: pentru anul curent
  • indicatori: total creanțe, creanțe curente, creanțe restante, structura creanțelor
  • detaliind: linii / an, săptămână, contrapartidă

8. Raportați chitanța de plată după data documentului:
  • perioadă: pentru anul curent
  • indicatori: valoarea documentului, valoarea plății documentului, abaterile de plată în termeni absoluți și relativi
  • detaliind: linii / an, lună, contrapartidă, tip de document, data documentului, numărul documentului

9. Raportați chitanța de plată după data plății:
  • perioadă: pentru anul curent
  • indicatori: valoarea plății, suma distribuției plății, variațiile de distribuție în termeni absoluți și relativi
  • detaliind: linii / an, lună, contrapartidă, tipul plății, data plății, numărul plății

10. Raport de inventar:
  • perioadă: pentru data curentă
  • indicatori: restul mărfurilor, termenul de valabilitate rămas în procente (conform DSO), restul mărfurilor până la termenul de valabilitate - Durată 0%, Durată 10-30%, Durată 40-60%, Durată 70-90% , Durata 99%
  • detaliind: linii / categorie, articol, stare, depozit, data primirii documentului

Bună tuturor! Materialul de astăzi este destinat celor care continuă să stăpânească munca cu programe de aplicații și nu știu cum să facă un tabel pivot în excel.

Prin crearea unui tabel comun, în oricare dintre documente text, îl puteți analiza realizând tabele pivot în Excel.

Crearea unui tabel Excel pivot necesită îndeplinirea anumitor condiții:

  1. Datele se încadrează într-un tabel cu coloane și liste cu nume.
  2. Lipsa formularelor goale.
  3. Lipsa obiectelor ascunse.

Cum se face un tabel pivot în Excel: instrucțiuni pas cu pas

Pentru a crea un tabel pivot, trebuie:

A fost creată o foaie goală, unde puteți vedea listele de zone și câmpuri. Titlurile au devenit câmpuri în noul nostru tabel. Tabelul pivot va fi format prin glisarea și plasarea câmpurilor.

Acestea vor fi marcate cu o bifă și, pentru comoditatea analizei, le veți schimba în zonele tabelului.


Am decis că voi analiza datele printr-un filtru de către vânzători, astfel încât să fie vizibil de către cine și pentru cât s-a vândut în fiecare lună și ce fel de produs.

Selectăm un anumit vânzător. Faceți clic pe mouse și trageți câmpul „Vânzător” în „Filtru raport”. Noul câmp este bifat și vizualizarea în tabel se modifică ușor.


Vom pune categoria „Produse” sub formă de șiruri. Transferăm câmpul obligatoriu în „Nume de linie”.


Pentru a afișa lista derulantă, contează în ce ordine specificăm numele. Dacă inițial facem o alegere în favoarea produsului în linii și apoi indicăm prețul, atunci produsele vor fi listele derulante și invers.

Coloana „Unități”, aflată în tabelul principal, afișează cantitatea de bunuri vândute de un anumit vânzător la un anumit preț.


Pentru a afișa vânzările, de exemplu, pentru fiecare lună, trebuie să înlocuiți câmpul „Data” cu „Numele coloanelor”. Selectați comanda „Grup” făcând clic pe dată.


Vă indicăm perioadele datei și ale pasului. Confirmăm alegerea.

Vedem o astfel de masă.


Să transferăm câmpul „Sumă” în zona „Valori”.


Afișarea numerelor a devenit vizibilă, dar avem nevoie exact de formatul numerelor


Pentru a o remedia, selectați celulele, apelați fereastra cu mouse-ul, selectați „Format număr”.

Alegem formatul numeric pentru următoarea fereastră și marcăm „Separatorul de grup”. Confirmați cu butonul „OK”.

Proiectare tabel pivot

Dacă bifăm caseta care confirmă selectarea mai multor obiecte simultan, atunci vom putea procesa date pentru mai mulți vânzători simultan.


Filtrul poate fi aplicat coloanelor și rândurilor. Bifând caseta de pe unul dintre soiurile de produs, puteți afla cât din acesta a fost vândut de unul sau mai mulți vânzători.


Parametrii de câmp sunt, de asemenea, configurați separat. În exemplu, vedem că un anumit vânzător de romi a vândut cămăși pentru o anumită sumă într-o anumită lună. Făcând clic cu mouse-ul în linia „Suma după câmp ...” apelăm meniul și selectăm „Parametrii câmpurilor de valoare”.


Apoi, pentru informații despre date în câmp, selectați „Cantitate”. Confirmăm alegerea.

Uită-te la masă. Arată clar că într-una dintre luni vânzătorul a vândut 2 cămăși.


Acum schimbăm tabelul și facem filtrul să funcționeze pe lună. Mutăm câmpul „Data” la „Filtru raport” și unde „Numele coloanelor” vor fi „Vânzător”. Tabelul afișează întreaga perioadă de vânzare sau pentru o anumită lună.


Selectarea celulelor dintr-un tabel pivot va afișa o filă, cum ar fi Instrumente pentru tabelul pivot, și vor exista încă două file, Opțiuni și Design.


De fapt, putem vorbi în continuare despre setările tabelelor pivot pentru o perioadă foarte lungă de timp. Modificați gustul dvs., obținând o utilizare convenabilă pentru dvs. Nu vă fie frică să împingeți și să experimentați. Puteți schimba oricând orice acțiune apăsând comanda rapidă de la tastatură Ctrl + Z.

Sper că ați stăpânit tot materialul și acum știți cum să creați un tabel pivot în excel.

Pentru analiza tabelelor mari și complexe, este de obicei utilizat. Formulele pot fi, de asemenea, utilizate pentru a grupa și analiza datele existente. Să creăm rapoarte simple folosind formule.

Noi vom folosi ( Insert / Tables / Table) care conține informații despre vânzările de loturi de produse. Rândurile din tabel conțin date despre livrarea unui lot de produse și vânzările acestuia. Un tabel similar a fost folosit în articol .

Tabelul are coloane:

  • Produs- numele lotului, de exemplu, „ Portocale»;
  • grup- grup de produse, de exemplu, " Portocale„Sunt incluse în grup” Fructe»;
  • data livrării- Data livrării Bunurilor de către Furnizor;
  • Regiunea de vânzări- Regiunea în care a fost vândut lotul de Bunuri;
  • Vânzări- Costul la care a fost posibilă vânzarea lotului de bunuri;
  • Vânzări- termenul de vânzare efectivă a Bunurilor în regiune (în zile);
  • Profit- un semn despre dacă s-a obținut un profit din lotul vândut al Bunurilor.

Peste tot Manager nume corectează tabelele la „ Sursa_tabel"(A se vedea fișierul de exemplu).

Folosind formulele, vom crea 5 rapoarte simple, pe care le vom plasa pe foi separate.

Raport nr. 1 Vânzări totale de bunuri

Să găsim vânzările totale ale fiecărui produs.
Sarcina poate fi rezolvată simplu folosind funcția SUMIF (), totuși, construirea raportului în sine necesită anumite abilități în lucrul cu unele instrumente EXCEL.

Deci sa începem. În primul rând, trebuie să creăm o listă cu numele mărfurilor. pentru că în coloana Produs din tabelul sursă, numele se repetă, apoi trebuie să selectăm doar valorile din acesta. Acest lucru se poate face în mai multe moduri: prin formule (vezi articolul), prin meniu Date / Lucrul cu datele / Eliminați duplicatele sau folosind. Dacă utilizați prima metodă, atunci când adăugați produse noi în tabelul original, noile nume vor fi incluse automat în listă. Dar, aici, din motive de simplitate, vom folosi a doua metodă. Pentru aceasta:

  • Mergeți la foaia cu tabelul original;
  • Apel ( Date / Sortare & Filtrare / Avansat);
  • Completați câmpurile așa cum se arată în figura de mai jos: setați comutatorul în poziție Copiați rezultatul într-o altă locație; în caseta Range Source, introduceți $ A $ 4: $ A $ 530; Bifeaza casuta Numai înregistrări unice.

  • Copiați lista rezultată în foaia în care va fi plasat raportul;
  • Sortează lista produselor ( Date / Sortare & Filtrare / Sortare de la A la Z).

Ar trebui să obțineți următoarea listă.

Într-o celulă B6 introduceți următoarea formulă, apoi copiați-o în josul listei:

SUMIF (Source_Table [Product]; A6; Source_Table [Sales])

COUNTIF (Source_Table [Product]; A6)

Raport nr. 2 Vânzări de bunuri pe regiuni

Să găsim vânzările totale ale fiecărui produs în regiuni.
Să folosim lista Produselor create pentru Raportul nr. 1. În mod similar, vom obține o listă de nume de regiuni (în câmpul Range Source, introduceți $ D $ 4: $ D $ 530).
Copiați intervalul vertical rezultat în Clipboardși orizontală. Plasați intervalul rezultat care conține numele regiunilor în antetul raportului.

Într-o celulă B 8 introducem următoarea formulă:

SUMIFS (Source_Table [Sales]);
Source_Table [Product]; $ A8;
Source_Table [Regiunea de vânzări]; 7 USD B)

Formula va returna vânzările totale ale Produsului, al cărui nume este plasat în celulă A8 , în regiunea din celulă LA 7 ... Acordați atenție utilizării (referințele $ A8 și B $ 7), va fi necesar atunci când copiați formula pentru restul celulelor de tabel goale.

Nu va funcționa să copiați formula de mai sus în celulele din dreapta cu ajutorul (acest lucru a fost făcut pentru Raportul nr. 1), deoarece în acest caz, în celulă C8 formula va arăta astfel:

SUMIFS (Source_Table [Vânzări, zile];
Sursa_Tabel [Grup]; $ A8;
Source_Table [Vânzări]; C $ 7)

Raport nr. 3 Filtrarea produselor după rentabilitate

Să ne întoarcem la tabelul original. Fiecare lot de Bunuri a obținut fie un profit, fie nu l-a adus (consultați coloana Profit din tabelul sursă). Să calculăm vânzările pe grupuri de produse în funcție de profitabilitate. Pentru a face acest lucru, vom filtra înregistrările tabelului sursă după câmpul Profit folosind formule.

FREQUENCY (Source_Table [Sales, days]; A7: A12)

Selectați un interval pentru a introduce o formulă C6: C12 apoi în Bara de formule introduceți formula de mai sus și apăsați CTRL+ SCHIMB+ INTRODUCE.

Același rezultat poate fi obținut folosind funcția obișnuită SUMPRODUCT ():
= SUMPRODUCT ((Source_Table [Vânzări, zile]> A6) *
(Source_Table [Vânzări, zile]<=A7))

Raport nr. 5 Statistica livrărilor de bunuri

Acum, să pregătim un raport privind aprovizionarea cu bunuri pentru luna respectivă.
În primul rând, să creăm o listă de luni cu an. În tabelul original, prima dată de livrare este 11/07/2009. Îl puteți calcula folosind formula:
= MIN (Original_Table [Data livrării])

Să creăm o listă de date - începând cu prima dată de livrare. Pentru a face acest lucru, vom folosi formula:
= EON LUNI ($ C $ 5; -1) +1

Drept urmare, obținem o listă de date - primele zile ale lunilor:

Aplicând formatul de celulă adecvat, vom schimba afișarea datelor:

Formula pentru calcularea numărului de loturi de bunuri livrate pe lună:

SUMPRODUCT ((Original_Table [Data livrării]> = B9) *
(Source_Table [Data livrării]

Acum să adăugăm linii pentru a calcula numărul total de loturi pentru fiecare an. Pentru a face acest lucru, vom schimba ușor tabelul, evidențiind anul în care livrarea a fost efectuată într-o coloană separată folosind funcția YEAR ().

Acum, pentru rezultatul pe ani, vom crea o structură prin elementul de meniu:

  • Selectați orice celulă din tabelul modificat;
  • Apelați fereastra prin elementul de meniu Date / Structură / Subtotale;
  • Completați câmpurile așa cum se arată în imagine:

După ce faceți clic pe OK, tabelul va fi modificat după cum urmează:

Se vor genera subtotaluri pe an.
Apăsând butoanele mici din colțul din stânga sus al foii, puteți controla afișarea datelor în tabel.

Rezumat :

Rapoarte similare cu cele create pot fi făcute în mod natural cu sau folosind Filtru la tabelul original sau cu ajutorul altor funcții BDSUMM (), BIZVLECH (), BCOUNT () etc. Alegerea abordării depinde de situația specifică.

ARTICOLE SIMILARE