Defalcarea în valori elementare - UNPIVOT, marele truc

unpivot

Adesea întâmpin problema că o gamă într-un aspect asemănător cu PIVOT ar trebui rearanjată așa cum se arată în figura următoare. Această operațiune este numită UNPIVOT de către lume. În limba maghiară se numește defalcare în valori elementare.

Cred că putem fi de acord că a face acest lucru cu munca manuală este o întreprindere destul de lipsită de speranță. Mai ales dacă există mai multe rânduri și/sau multe coloane în tabelul original.

Puteți scrie o macro pentru această problemă sau puteți utiliza unul dintre meniurile PowerQuery, dar puteți găsi, de asemenea, un supliment UNPIVOT plătit pentru a vă ajuta să rezolvați problema. A existat, de asemenea, o întrebare a Forumului în 2016, unde a luat naștere și soluția PowerQuery.

Zilele trecute am studiat cum să creez tabele PIVOT prin consolidarea mai multor surse de date cu aceeași structură. Această caracteristică a fost integrată în vrăjitorul Excel PIVOT de ceva timp. L-am privit din nou și l-am găsit din nou inutil. Opinia mea a fost consolidată conform căreia mai multe surse de date trebuie plasate mai întâi într-o sursă de date comună prin orice mijloace, iar apoi raportul trebuie pregătit.

Iluminarea

În timp ce studiam cele de mai sus, mi s-a prezentat o oportunitate pe care nu o știam până acum, deși am aprofundat destul de mult subiectul tabelelor PIVOT. Până acum, am folosit și am predat Power Query pentru sarcini de tip UNPIVOT. Pentru aceasta, se dovedește că această problemă poate fi rezolvată și cu o soluție simplă de placă PIVOT.

Pașii UNPIVOT

Primul trebuie pornit vrăjitorul tabelului PIVOT. Aici ne confruntăm cu problema că vrăjitorul PIVOT nu se află în partea de sus a vreunei benzi în mod implicit. Dar îl putem atrage în Excel maghiar Alt-> D-> I prin apăsarea tastelor una după alta (nu în același timp.). În limba engleză Excel, același lucru este Alt-> D-> P.

Aici stabilim că dorim să agregăm mai multe domenii. Acest lucru nu este adevărat, dar aceasta este alegerea acum.

Al doilea La pas, setăm câmpurile de pagină pentru a fi create de utilizator

THE al treilea La pasul 1, specificați intervalul pe care doriți să îl împărțiți în elemente. Selectați întreaga gamă și validați cu butonul Adăugați. Mai jos confirmăm că nu aveți nevoie de un câmp de pagină.

THE Al patrulea La pasul 1, vă rugăm să trimiteți declarația pe o nouă pagină. Până acum nu s-a întâmplat nimic, „doar” am pus gama originală într-un tabel PIVOT.

Aici vine surpriza!

Dacă facem dublu clic pe suma finală, miracolul, rezultatul final UNPIVOT, se desfășoară în fața noastră pe o nouă foaie de lucru. Tot ce trebuie să facem este să redenumiți anteturile.

Atenţie!
Această soluție poate fi utilizată numai dacă vrem să împărțim doar o coloană din tabelul original, adică prima coloană, cu datele celorlalte coloane.

Dacă doriți să împărțiți mai multe coloane în valori elementare, Power Query rămâne.