Tabelele pivot sunt bune pentru toată lumea – calculează rapid și sunt configurate flexibil, iar designul poate fi înfășurat în ele elegant, dacă este necesar. Dar există, de asemenea, câteva zbură în unguent, în special, incapacitatea de a crea un rezumat, în care zona de valoare nu ar trebui să conțină numere, ci text.
Să încercăm să ocolim această limitare și să venim cu o „pereche de cârje” într-o situație similară.
Să presupunem că compania noastră își transportă produsele în containere în mai multe orașe din țara noastră și Kazahstan. Containerele sunt trimise nu mai mult de o dată pe lună. Fiecare container are un număr alfanumeric. Ca date inițiale, există un tabel standard care listează livrările, din care trebuie să faceți un fel de rezumat pentru a vedea clar numărul de containere trimise în fiecare oraș și în fiecare lună:
Pentru comoditate, să facem tabelul cu datele inițiale „inteligent” în avans folosind comanda Acasă – Formatați ca tabel (Acasă — Format ca tabel) si da-i un nume Livrări fila Constructor (Proiecta). În viitor, acest lucru va simplifica viața, deoarece. se va putea folosi numele tabelului și coloanele acestuia direct în formule.
Metoda 1. Cea mai ușoară – utilizați Power Query
Power Query este un instrument foarte puternic pentru încărcarea și transformarea datelor în Excel. Acest program de completare este încorporat implicit în Excel din 2016. Dacă aveți Excel 2010 sau 2013, îl puteți descărca și instala separat (complet gratuit).
Întregul proces, pentru claritate, l-am analizat pas cu pas în următorul videoclip:
Dacă nu este posibil să utilizați Power Query, atunci puteți merge pe alte căi - printr-un tabel pivot sau formule.
Metoda 2. Rezumat auxiliar
Să mai adăugăm o coloană la tabelul nostru original, unde folosind o formulă simplă calculăm numărul fiecărui rând din tabel:
Evident, este necesar -1, deoarece avem un antet de o linie în tabelul nostru. Dacă tabelul dvs. nu este la începutul foii, atunci puteți utiliza o formulă puțin mai complexă, dar universală, care calculează diferența dintre numerele rândului curent și antetul tabelului:
Acum, într-un mod standard, vom construi un tabel pivot de tipul dorit pe baza datelor noastre, dar în câmpul de valoare vom arunca câmpul Numărul liniei în loc de ceea ce ne dorim recipient:
Deoarece nu avem mai multe containere în același oraș în aceeași lună, rezumatul nostru va oferi, de fapt, nu suma, ci numerele de rând ale containerelor de care avem nevoie.
În plus, puteți dezactiva totalurile generale și subtotalurile din filă Constructor – Totaluri generale и subtotaluri (Design — Totaluri mari, subtotaluri) și, în același loc, comutați rezumatul la un aspect al tabelului mai convenabil cu butonul Raportați o machetă (Aspect raport).
Astfel, suntem deja la jumătatea rezultatului: avem un tabel în care, la intersecția orașului și a lunii, există un număr de rând în tabelul sursă, unde se află codul containerului de care avem nevoie.
Acum să copiem rezumatul (în aceeași foaie sau alta) și să-l lipim ca valori, apoi să introducem formula noastră în zona de valori, care va extrage codul containerului după numărul de linie găsit în rezumat:
Funcţie IF (DACĂ), în acest caz, verifică dacă următoarea celulă din rezumat nu este goală. Dacă este gol, atunci scoateți un șir de text gol „”, adică lăsați celula goală. Dacă nu este gol, atunci extrageți din coloană Recipient tabel sursă Livrări conținutul celulei după numărul rândului folosind funcția INDEX (INDEX).
Poate singurul punct nu foarte evident aici este cuvântul dublu Recipient în formulă. O formă atât de ciudată de scriere:
Provizii[[Container]:[Container]]
… este necesar doar pentru a face referire la coloană Recipient a fost absolută (ca o referință cu semnele $ pentru tabelele obișnuite „non-inteligente”) și nu a alunecat în coloanele învecinate când am copiat formula noastră în dreapta.
În viitor, la modificarea datelor din tabelul sursă Livrări, trebuie să ne amintim să actualizăm rezumatul nostru auxiliar cu numere de rând făcând clic dreapta pe el și alegând comanda Actualizați și salvați (Reîmprospăta).
Metoda 3. Formule
Această metodă nu necesită crearea unui tabel pivot intermediar și actualizarea manuală, dar folosește „arma grea” a Excel – funcția SUMMESLIMN (SUMIFS). În loc să căutați numerele de rând într-un rezumat, le puteți calcula folosind această formulă:
Cu un anumit volum extern, de fapt, acesta este un caz de utilizare standard pentru funcția de însumare selectivă SUMMESLIMNA care însumează numerele rândurilor pentru orașul și luna date. Din nou, deoarece nu avem mai multe containere în același oraș în aceeași lună, funcția noastră va oferi, de fapt, nu suma, ci numărul liniei în sine. Și apoi funcția deja familiară din metoda anterioară INDEX De asemenea, puteți extrage coduri de container:
Desigur, în acest caz, nu mai trebuie să vă gândiți la actualizarea rezumatului, ci pe tabele mari, funcția SUMMESLI poate fi vizibil lent. Apoi va trebui să dezactivați actualizarea automată a formulelor sau să utilizați prima metodă – un tabel pivot.
Dacă aspectul rezumatului nu este foarte potrivit pentru raportul dvs., atunci puteți extrage numerele de rând din acesta în tabelul final nu direct, așa cum am făcut noi, ci folosind funcția OBȚINE.DATE.TABEL.PIVOT (GET.PIVOT.DATA). Cum se face acest lucru poate fi găsit aici.
- Cum să creați un raport folosind un tabel pivot
- Cum să configurați calculele în tabele pivot
- Numărarea selectivă cu SUMIFS, COUNTIFS etc.