Kaundan
Ang mga lamesa sa pivot maayo alang sa tanan - kini dali nga nagkalkula, ug gi-configure nga flexible, ug ang disenyo mahimong masamdan niini nga elegante, kung gikinahanglan. Apan adunay usab pipila ka mga langaw sa pahumot, ilabi na, ang kawalay katakus sa paghimo sa usa ka summary, diin ang bili nga dapit dili kinahanglan nga adunay mga numero, apan teksto.
Atong paningkamutan nga likayan kini nga limitasyon ug maghimo usa ka "parehas nga crutches" sa parehas nga sitwasyon.
Ibutang ta nga ang among kompanya nagdala sa mga produkto niini sa mga sudlanan sa daghang mga lungsod sa Atong Nasud ug Kazakhstan. Ang mga sudlanan ipadala dili molapas sa kausa sa usa ka bulan. Ang matag sudlanan adunay alphanumeric nga numero. Isip inisyal nga datos, adunay usa ka standard nga listahan sa mga paghatud sa lamesa, diin kinahanglan nimo nga maghimo usa ka matang sa katingbanan aron klaro nga makita ang gidaghanon sa mga sudlanan nga gipadala sa matag lungsod ug matag bulan:
Alang sa kasayon, atong himoon ang lamesa nga adunay inisyal nga datos nga "smart" daan gamit ang command Panimalay - Pag-format ingon usa ka lamesa (Balay — Format isip Talaan) ug hatagi siya ug ngalan Mga Pagluwas tab Magtutukod (Laraw). Sa umaabot, kini makapasayon sa kinabuhi, tungod kay. mahimong posible nga gamiton ang ngalan sa lamesa ug ang mga kolum niini direkta sa mga pormula.
Pamaagi 1. Ang pinakasayon – gamita ang Power Query
Ang Power Query usa ka labi ka kusgan nga himan alang sa pagkarga ug pagbag-o sa datos sa Excel. Kini nga add-in gitukod sa Excel nga default sukad sa 2016. Kung ikaw adunay Excel 2010 o 2013, mahimo nimo kining i-download ug i-install nga gilain (bug-os nga libre).
Ang tibuuk nga proseso, alang sa katin-awan, gisusi nako ang lakang sa lakang sa mosunud nga video:
Kung dili posible ang paggamit sa Power Query, nan mahimo kang moadto sa ubang mga paagi - pinaagi sa pivot table o mga pormula.
Pamaagi 2. Auxiliary summary
Atong idugang ang usa pa ka kolum sa atong orihinal nga lamesa, diin gamit ang usa ka yano nga pormula atong kuwentahon ang gidaghanon sa matag laray sa lamesa:
Dayag, -1 ang gikinahanglan, tungod kay kami adunay usa ka linya nga header sa among lamesa. Kung ang imong lamesa wala sa sinugdanan sa sheet, nan mahimo nimong gamiton ang usa ka gamay nga labi ka komplikado, apan unibersal nga pormula nga nagkalkula sa kalainan sa mga numero sa karon nga laray ug ang ulohan sa lamesa:
Karon, sa usa ka standard nga paagi, maghimo kami usa ka pivot table sa gusto nga tipo base sa among data, apan sa field nga kantidad among ihulog ang field. Numero sa linya imbes sa atong gusto sudlanan:
Tungod kay wala kami daghang mga sudlanan sa parehas nga lungsod sa parehas nga bulan, ang among summary, sa tinuud, maghatag dili kantidad, apan ang mga numero sa linya sa mga sudlanan nga among gikinahanglan.
Dugang pa, mahimo nimong i-off ang grand ug subtotal sa tab Konstruktor - Kinatibuk-ang kinatibuk-an и Mga Subtotal (Disenyo — Dakong Total, Subtotal) ug sa samang dapit ibalhin ang summary ngadto sa mas kombenyente nga layout sa lamesa gamit ang buton Ireport ang mockup (Layout sa Report).
Busa, tunga na kita sa resulta: aduna kitay lamesa diin, sa intersection sa siyudad ug bulan, adunay row number sa source table, diin nahimutang ang container code nga atong gikinahanglan.
Karon atong kopyahon ang summary (sa parehas nga sheet o lain) ug idikit kini ingon mga kantidad, ug dayon isulod ang among pormula sa lugar nga kantidad, nga makuha ang code sa sudlanan pinaagi sa numero sa linya nga makita sa summary:
function IF (KUNG), sa niini nga kaso, susiha nga ang sunod nga cell sa summary walay sulod. Kung walay sulod, dayon i-output ang usa ka walay sulod nga text string "", ie ibilin nga blangko ang cell. Kung dili walay sulod, unya kuhaa gikan sa kolum Kontainer tinubdan nga lamesa Mga Pagluwas cell content pinaagi sa row number gamit ang function INDEX (INDEX).
Tingali ang dili kaayo klaro nga punto dinhi mao ang doble nga pulong Kontainer sa pormula. Katingad-an nga porma sa pagsulat:
Mga suplay [[Container]:[Container]]
… gikinahanglan lamang sa paghisgot sa kolum Kontainer hingpit (sama sa usa ka reperensiya nga adunay $ mga karatula alang sa ordinaryo nga "dili-smart" nga mga lamesa) ug wala ma-slip sa silingang mga kolum kung gikopya ang among pormula sa tuo.
Sa umaabot, kung gibag-o ang datos sa gigikanan nga lamesa Mga Pagluwas, kinahanglan natong hinumdoman nga i-update ang atong auxiliary summary sa mga numero sa linya pinaagi sa pag-right-click niini ug pagpili sa command I-update ug I-save (I-refresh).
Pamaagi 3. Mga pormula
Kini nga pamaagi wala magkinahanglan sa paghimo sa usa ka intermediate pivot table ug manual nga pag-update, apan naggamit sa Excel nga "bug-at nga hinagiban" - ang function SUMMESLIMN (SUMIFS). Imbis nga tan-awon ang mga numero sa linya sa usa ka summary, mahimo nimong kalkulahon kini gamit kini nga pormula:
Uban sa pipila ka eksternal nga bulkiness, sa pagkatinuod, kini mao ang usa ka standard nga kaso sa paggamit alang sa pinili nga summation function SUMMESLIMNA nga nagsumada sa mga numero sa linya alang sa gihatag nga siyudad ug bulan. Usab, tungod kay wala kami daghang mga sudlanan sa parehas nga lungsod sa parehas nga bulan, ang among function, sa tinuud, maghatag dili kantidad, apan ang numero sa linya mismo. Ug unya ang function pamilyar na gikan sa miaging pamaagi INDEX Mahimo usab nimo makuha ang mga code sa sudlanan:
Siyempre, sa kini nga kaso, dili na nimo kinahanglan maghunahuna bahin sa pag-update sa summary, apan sa dagkong mga lamesa, ang function SUMMESLI mahimong mamatikdan nga hinay. Unya kinahanglan nimo nga i-off ang awtomatikong pag-update sa mga pormula, o gamiton ang una nga pamaagi - usa ka pivot table.
Kung ang dagway sa summary dili kaayo angay alang sa imong report, nan mahimo nimong makuha ang mga numero sa linya gikan niini ngadto sa katapusang lamesa nga dili direkta, sama sa among gibuhat, apan gamit ang function. GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Kon unsaon pagbuhat niini makita dinhi.
- Giunsa paghimo ang usa ka report gamit ang pivot table
- Giunsa ang pag-set up sa mga kalkulasyon sa mga pivot table
- Pinili nga pag-ihap nga adunay SUMIFS, COUNTIFS, ug uban pa.