Kaundan
Pagporma sa problema
Ingon nga data sa pag-input, kami adunay usa ka Excel file, diin ang usa sa mga sheet adunay daghang mga lamesa nga adunay data sa pagbaligya sa mosunod nga porma:
Timan-i nga:
- Mga lamesa sa lain-laing gidak-on ug uban sa lain-laing mga set sa mga produkto ug mga rehiyon sa mga laray ug mga kolum nga walay bisan unsa nga paghan-ay.
- Ang mga blangko nga linya mahimong isulod taliwala sa mga lamesa.
- Ang gidaghanon sa mga lamesa mahimong bisan unsa.
Duha ka importante nga mga pangagpas. Gituohan nga:
- Ibabaw sa matag lamesa, sa unang kolum, adunay ngalan sa manedyer kansang mga pagbaligya gihulagway sa lamesa (Ivanov, Petrov, Sidorov, ug uban pa)
- Ang mga ngalan sa mga butang ug mga rehiyon sa tanan nga mga lamesa gisulat sa parehas nga paagi - nga adunay katukma sa kaso.
Ang katapusan nga katuyoan mao ang pagkolekta sa datos gikan sa tanan nga mga lamesa ngadto sa usa ka patag nga normal nga lamesa, nga sayon alang sa sunod nga pagtuki ug paghimo og usa ka summary, ie sa usa niini:
Lakang 1. Sumpaysumpaya ang file
Maghimo kita og bag-ong walay sulod nga Excel file ug pilia kini sa tab Data Command Pagkuha Data - Gikan sa File - Gikan sa Libro (Data — Gikan sa file — Gikan sa workbook). Ipiho ang lokasyon sa gigikanan nga file nga adunay mga datos sa pagbaligya ug dayon sa window sa navigator pilia ang sheet nga kinahanglan namon ug i-klik ang buton Kinabig Data (Pagbag-o sa Data):
Ingon usa ka sangputanan, ang tanan nga datos gikan niini kinahanglan i-load sa editor sa Power Query:
Lakang 2. Limpyohi ang basura
Pagtangtang sa awtomatiko nga nahimo nga mga lakang giusab nga tipo (Gibag-o nga Uri) и Gipataas nga mga ulohan (Gi-promote nga mga ulohan) ug kuhaa ang mga walay sulod nga linya ug linya nga adunay mga total gamit ang filter bili и TOTAL pinaagi sa unang kolum. Ingon usa ka sangputanan, makuha namon ang mosunod nga litrato:
Lakang 3. Pagdugang managers
Aron masabtan sa ulahi kung diin ang mga baligya, kinahanglan nga magdugang usa ka kolum sa among lamesa, diin sa matag laray adunay katugbang nga apelyido. Alang niini:
1. Magdugang ta og auxiliary column nga adunay mga numero sa linya gamit ang command Idugang ang Column – Index Column – Gikan sa 0 (Idugang ang kolum — Kolum sa indeks — Gikan sa 0).
2. Pagdugang usa ka kolum nga adunay pormula nga adunay mando Pagdugang ug Column – Custom Column (Idugang ang kolum — Custom nga kolum) ug ipaila ang mosunod nga konstruksyon didto:
Ang lohika niini nga pormula yano - kung ang bili sa sunod nga selula sa unang kolum mao ang "Produkto", nan kini nagpasabot nga kita napandol sa sinugdanan sa usa ka bag-ong lamesa, mao nga atong gipakita ang bili sa miaging selula uban sa ngalan sa manager. Kung dili, wala kami magpakita bisan unsa, ie null.
Aron makuha ang parent cell nga adunay apelyido, una namon nga gipunting ang lamesa gikan sa miaging lakang #"Gidugang ang indeks", ug dayon ipiho ang ngalan sa kolum nga atong gikinahanglan [Kolum 1] sa square bracket ug ang cell number sa maong column sa curly bracket. Ang numero sa cell mahimong usa ka gamay kaysa sa karon, nga among gikuha gikan sa kolum index, Matag usa.
3. Nagpabilin kini nga pun-on ang mga walay sulod nga mga selyula bili mga ngalan gikan sa mas taas nga mga cell nga adunay command Pagbag-o - Pun-an - Pababa (Pagbag-o - Pun-an - Pababa) ug tangtanga ang dili na kinahanglan nga kolum nga adunay mga indeks ug mga laray nga adunay mga apelyido sa unang kolum. Ingon usa ka sangputanan, makuha namon:
Lakang 4. Paggrupo ngadto sa lain nga mga lamesa sa mga manedyer
Ang sunod nga lakang mao ang paggrupo sa mga laray sa matag manedyer ngadto sa lain nga mga lamesa. Aron mahimo kini, sa tab nga Pagbag-o, gamita ang Grupo pinaagi sa mando (Pagbag-o - Grupo Ni) ug sa bintana nga magbukas, pilia ang kolum sa Manager ug ang operasyon Tanan nga mga laray (Tanan nga mga laray) aron yano nga mangolekta og datos nga wala mag-apply sa bisan unsang aggregating function sa kanila (sum, average, ug uban pa). P.):
Ingon usa ka sangputanan, nakakuha kami lahi nga mga lamesa alang sa matag manager:
Lakang 5: Pag-usab sa Nested Tables
Karon among gihatag ang mga lamesa nga nahimutang sa matag cell sa resulta nga kolum Tanan nga datos sa desente nga porma.
Una, kuhaa ang usa ka kolum nga dili na kinahanglan sa matag lamesa manager. Gigamit namo pag-usab Pasadya nga kolum tab kausaban (Pagbag-o - Pasadya nga kolum) ug ang mosunod nga pormula:
Dayon, uban sa laing kalkulado nga kolum, atong ipataas ang unang laray sa matag lamesa ngadto sa mga ulohan:
Ug sa katapusan, gihimo namon ang panguna nga pagbag-o - pagbuklad sa matag lamesa gamit ang M-function Table.UnpivotOtherColumns:
Ang mga ngalan sa mga rehiyon gikan sa ulohan moadto sa usa ka bag-ong kolum ug makakuha kami usa ka pig-ot, apan sa parehas nga oras, usa ka mas taas nga normal nga lamesa. Walay sulod nga mga selula nga adunay bili gibalewala
Pagtangtang sa wala kinahanglana nga tunga nga mga kolum, kami adunay:
Lakang 6 Pagpalapad sa Nested Tables
Nagpabilin nga palapdan ang tanan nga na-normalize nga nested nga mga lamesa sa usa ka lista gamit ang buton nga adunay doble nga mga pana sa ulohan sa kolum:
… ug sa katapusan makuha namo ang among gusto:
Mahimo nimong i-export ang resulta nga lamesa balik sa Excel gamit ang command Panimalay — Pagsira ug Pag-load — Pagsira ug Pag-load sa… (Balay — Close&Load — Close&Load to…).
- Paghimo mga lamesa nga adunay lainlaing mga ulohan gikan sa daghang mga libro
- Pagkolekta sa datos gikan sa tanan nga mga file sa usa ka gihatag nga folder
- Pagkolekta sa datos gikan sa tanang panid sa libro ngadto sa usa ka lamesa