Nilalaman
Ang mga pivot table ay mabuti para sa lahat – mabilis silang nagkalkula, at na-configure nang may kakayahang umangkop, at ang disenyo ay maaaring isama sa mga ito nang elegante, kung kinakailangan. Ngunit mayroon ding ilang mga lumipad sa pamahid, sa partikular, ang kawalan ng kakayahan na lumikha ng isang buod, kung saan ang lugar ng halaga ay hindi dapat maglaman ng mga numero, ngunit teksto.
Subukan nating lagpasan ang limitasyong ito at makabuo ng isang "pares na saklay" sa isang katulad na sitwasyon.
Ipagpalagay na ang aming kumpanya ay nagdadala ng mga produkto nito sa mga lalagyan sa ilang mga lungsod sa Ating Bansa at Kazakhstan. Ang mga lalagyan ay ipinapadala nang hindi hihigit sa isang beses sa isang buwan. Ang bawat lalagyan ay may alphanumeric na numero. Bilang paunang data, mayroong karaniwang listahan ng mga paghahatid ng talahanayan, kung saan kailangan mong gumawa ng ilang uri ng buod upang malinaw na makita ang mga bilang ng mga container na ipinadala sa bawat lungsod at bawat buwan:
Para sa kaginhawahan, gawin natin ang talahanayan na may paunang data na "matalino" nang maaga gamit ang utos Tahanan – I-format bilang isang talahanayan (Home — Format bilang Talahanayan) at bigyan siya ng pangalan Mga Paghahatid tab Tagagawa (Disenyo). Sa hinaharap, ito ay gawing simple ang buhay, dahil. magiging posible na gamitin ang pangalan ng talahanayan at ang mga column nito nang direkta sa mga formula.
Paraan 1. Ang pinakamadali – gamitin ang Power Query
Ang Power Query ay isang napakalakas na tool para sa paglo-load at pagbabago ng data sa Excel. Ang add-in na ito ay binuo sa Excel bilang default mula noong 2016. Kung mayroon kang Excel 2010 o 2013, maaari mo itong i-download at i-install nang hiwalay (ganap na libre).
Ang buong proseso, para sa kalinawan, sinuri ko ang hakbang-hakbang sa sumusunod na video:
Kung hindi posible na gumamit ng Power Query, maaari kang pumunta sa ibang mga paraan - sa pamamagitan ng pivot table o mga formula.
Paraan 2. Pantulong na buod
Magdagdag tayo ng isa pang column sa aming orihinal na talahanayan, kung saan gamit ang isang simpleng formula ay kinakalkula namin ang bilang ng bawat hilera sa talahanayan:
Malinaw, kailangan ang -1, dahil mayroon kaming one-line na header sa aming talahanayan. Kung ang iyong talahanayan ay wala sa simula ng sheet, maaari kang gumamit ng bahagyang mas kumplikado, ngunit unibersal na formula na kinakalkula ang pagkakaiba sa mga numero ng kasalukuyang hilera at header ng talahanayan:
Ngayon, sa karaniwang paraan, bubuo kami ng pivot table ng nais na uri batay sa aming data, ngunit sa field ng halaga ay ibababa namin ang field Numero ng linya sa halip na kung ano ang gusto natin lalagyan:
Dahil wala kaming ilang container sa parehong lungsod sa parehong buwan, ang aming buod ay, sa katunayan, ay hindi magbibigay ng halaga, ngunit ang mga numero ng linya ng mga container na kailangan namin.
Bukod pa rito, maaari mong i-off ang grand at subtotal sa tab Konstruktor – Pangkalahatang mga kabuuan и Mga subtotal (Disenyo — Mga Grand Total, Subtotal) at sa parehong lugar ay ilipat ang buod sa isang mas maginhawang layout ng talahanayan gamit ang pindutan Mag-ulat ng mockup (Layout ng Ulat).
Kaya, nasa kalahati na tayo sa resulta: mayroon tayong talahanayan kung saan, sa intersection ng lungsod at buwan, mayroong isang row number sa source table, kung saan matatagpuan ang container code na kailangan natin.
Ngayon, kopyahin natin ang buod (sa parehong sheet o iba pa) at i-paste ito bilang mga halaga, at pagkatapos ay ilagay ang aming formula sa lugar ng halaga, na kukuha ng code ng lalagyan sa pamamagitan ng numero ng linya na makikita sa buod:
tungkulin IF (KUNG), sa kasong ito, sinusuri na ang susunod na cell sa buod ay walang laman. Kung walang laman, pagkatapos ay mag-output ng walang laman na text string na "", ibig sabihin, iwanang blangko ang cell. Kung hindi walang laman, pagkatapos ay i-extract mula sa column Lalagyan talahanayan ng pinagmulan Mga Paghahatid nilalaman ng cell ayon sa numero ng hilera gamit ang function INDEX (INDEX).
Marahil ang tanging hindi masyadong halatang punto dito ay ang dobleng salita Lalagyan sa formula. Kakaibang anyo ng pagsulat:
Mga gamit[[Lalagyan]:[Lalagyan]]
… ay kailangan lamang upang i-reference ang column Lalagyan ay ganap (tulad ng isang sanggunian na may $ na mga palatandaan para sa ordinaryong "hindi matalino" na mga talahanayan) at hindi dumulas sa mga kalapit na column kapag kinopya ang aming formula sa kanan.
Sa hinaharap, kapag binabago ang data sa source table Mga Paghahatid, dapat nating tandaan na i-update ang ating pantulong na buod sa mga numero ng linya sa pamamagitan ng pag-right-click dito at pagpili ng command I-update at I-save (Refresh).
Paraan 3. Mga Formula
Ang pamamaraang ito ay hindi nangangailangan ng paglikha ng isang intermediate pivot table at manu-manong pag-update, ngunit gumagamit ng "mabigat na sandata" ng Excel - ang function SUMMESLIMN (SUMIFS). Sa halip na maghanap ng mga numero ng row sa isang buod, maaari mong kalkulahin ang mga ito gamit ang formula na ito:
Sa ilang panlabas na bulkiness, sa katunayan, ito ay isang karaniwang kaso ng paggamit para sa selective summation function SUMMESLIMNA na nagbubuod ng mga numero ng row para sa ibinigay na lungsod at buwan. Muli, dahil wala kaming ilang lalagyan sa parehong lungsod sa parehong buwan, ang aming function ay, sa katunayan, ibibigay hindi ang halaga, ngunit ang numero ng linya mismo. At pagkatapos ay ang function na pamilyar mula sa nakaraang pamamaraan INDEX Maaari mo ring i-extract ang mga code ng container:
Siyempre, sa kasong ito, hindi mo na kailangang isipin ang tungkol sa pag-update ng buod, ngunit sa malalaking talahanayan, ang function SUMMESLI maaaring kapansin-pansing mabagal. Pagkatapos ay kailangan mong i-off ang awtomatikong pag-update ng mga formula, o gamitin ang unang paraan - isang pivot table.
Kung ang hitsura ng buod ay hindi masyadong angkop para sa iyong ulat, maaari mong kunin ang mga numero ng hilera mula dito papunta sa huling talahanayan nang hindi direkta, tulad ng ginawa namin, ngunit gamit ang function. GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Kung paano gawin ito ay matatagpuan dito.
- Paano gumawa ng ulat gamit ang pivot table
- Paano mag-set up ng mga kalkulasyon sa mga pivot table
- Selective counting na may SUMIFS, COUNTIFS, atbp.