Pamaagi 1. Mga pormula
Magsugod kita, alang sa pagpainit, sa pinakasimple nga kapilian - mga pormula. Kung kami adunay gamay nga lamesa nga gisunud sa petsa ingon input, unya aron makalkulo ang total nga nagdagan sa usa ka lahi nga kolum, kinahanglan namon ang usa ka elementarya nga pormula:
Ang nag-unang bahin dinhi mao ang malisud nga pag-ayo sa sakup sa sulod sa SUM function - ang paghisgot sa sinugdanan sa range gihimo nga hingpit (nga adunay mga timaan sa dolyar), ug hangtod sa katapusan - paryente (walay dolyar). Tungod niini, kung gikopya ang pormula hangtod sa tibuuk nga kolum, nakakuha kami usa ka nagpalapad nga sakup, ang kantidad nga among gikalkula.
Ang mga disbentaha niini nga pamaagi klaro:
- Ang lamesa kinahanglan nga ihan-ay sa petsa.
- Kung magdugang ug bag-ong mga laray nga adunay datos, ang pormula kinahanglan nga i-extend nga mano-mano.
Pamaagi 2. Pivot table
Kini nga pamaagi usa ka gamay nga mas komplikado, apan mas makapahimuot. Ug aron makapasamot, atong tagdon ang usa ka mas seryoso nga problema – usa ka lamesa sa 2000 ka laray sa datos, diin walay paghan-ay sumala sa kolum sa petsa, apan adunay mga pagbalik-balik (ie makabaligya kita sa makadaghang higayon sa samang adlaw):
Among gi-convert ang among orihinal nga lamesa ngadto sa "smart" (dynamic) nga keyboard shortcut Ctrl+T o team Panimalay - Pag-format ingon usa ka lamesa (Balay — Format isip Talaan), ug unya maghimo kami usa ka pivot table sa ibabaw niini nga adunay mando Isulod – PivotTable (Sal-ot — Pivot Table). Gibutang namo ang petsa sa row area sa summary, ug ang gidaghanon sa mga butang nga gibaligya sa values area:
Palihug timan-i nga kung ikaw adunay dili kaayo karaan nga bersyon sa Excel, nan ang mga petsa awtomatik nga gigrupo sa mga tuig, quarters ug mga bulan. Kung kinahanglan nimo ang usa ka lahi nga grupo (o wala gyud kinahanglana), mahimo nimo kini ayohon pinaagi sa pag-right-click sa bisan unsang petsa ug pagpili sa mga sugo Grupo / Pag-ungroup (Grupo / Ungroup).
Kung gusto nimo nga makita ang mga resulta nga total sa mga yugto ug ang total nga nagdagan sa usa ka lahi nga kolum, nan makatarunganon nga ihulog ang uma sa lugar nga kantidad Gibaligya pag-usab sa pagkuha sa usa ka duplicate sa uma – sa niini kita turn on sa display sa running totals. Aron mahimo kini, pag-right-click sa field ug pilia ang command Dugang nga mga Kalkulasyon – Kinatibuk-ang Kinatibuk-ang (Ipakita ang mga Bili isip — Nagdagan nga Total):
Didto mahimo usab nimo pilion ang kapilian sa pagtubo sa mga kinatibuk-an ingon usa ka porsyento, ug sa sunod nga bintana kinahanglan nimo nga pilion ang uma kung diin moadto ang pagtipon - sa among kaso, kini ang natad sa petsa:
Ang mga bentaha niini nga pamaagi:
- Ang usa ka dako nga kantidad sa datos dali nga mabasa.
- Wala’y mga pormula nga kinahanglan isulod sa mano-mano.
- Kung nagbag-o sa gigikanan nga datos, igo na nga i-update ang summary gamit ang tuo nga buton sa mouse o gamit ang Data - I-refresh Tanan nga mando.
Ang mga disbentaha nagsunod gikan sa kamatuoran nga kini usa ka summary, nga nagpasabot nga dili nimo mahimo ang bisan unsa nga gusto nimo niini (pagsal-ot sa mga linya, pagsulat sa mga pormula, paghimo og bisan unsang mga diagram, ug uban pa) dili na molihok.
Pamaagi 3: Power Query
Atong ikarga ang atong “smart” nga lamesa nga adunay tinubdan nga datos ngadto sa Power Query query editor gamit ang command Data – Gikan sa Talaan/Sakop (Data — Gikan sa Table/Range). Sa pinakabag-o nga mga bersyon sa Excel, sa dalan, kini giusab - karon kini gitawag Uban sa mga dahon (Gikan sa Sheet):
Unya atong buhaton ang mosunod nga mga lakang:
1. Pagsunud sa lamesa sa pataas nga han-ay pinaagi sa kolum sa petsa nga adunay mando Pagsunud sa pagsaka sa filter nga drop-down list sa table header.
2. Usa ka gamay nga ulahi, aron makalkulo ang total nga nagdagan, kinahanglan namon ang usa ka kolum sa auxiliary nga adunay numero sa laray sa ordinal. Idugang nato kini sa sugo Idugang ang Column – Index Column – Gikan sa 1 (Idugang ang kolum — Kolum sa indeks — Gikan sa 1).
3. Usab, aron makalkulo ang total nga nagdagan, kinahanglan namon ang usa ka pakisayran sa kolum Gibaligya, diin nahimutang ang among summarize data. Sa Power Query, ang mga kolum gitawag usab nga mga lista (listahan) ug aron makakuha usa ka link niini, pag-klik sa tuo sa ulohan sa kolum ug pilia ang mando. Pagdetalye (Ipakita ang detalye). Ang ekspresyon nga atong gikinahanglan makita sa formula bar, nga naglangkob sa ngalan sa miaging lakang #"Gidugang ang indeks", gikan diin atong gikuha ang lamesa ug ang ngalan sa kolum [Sales] gikan niini nga lamesa sa square bracket:
Kopyaha kini nga ekspresyon sa clipboard alang sa dugang nga paggamit.
4. Pagtangtang sa wala kinahanglana nga katapusang lakang Gibaligya ug idugang sa baylo ang usa ka kalkulado nga kolum alang sa pagkalkulo sa nagdagan nga total gamit ang mando Pagdugang ug Column – Custom Column (Idugang ang kolum — Custom nga kolum). Ang pormula nga atong gikinahanglan tan-awon sama niini:
Dinhi ang function Lista. Range gikuha ang orihinal nga lista (kolum [Sales]) ug gikuha ang mga elemento gikan niini, sugod sa una (sa pormula, kini 0, tungod kay ang pag-numero sa Power Query nagsugod gikan sa zero). Ang gidaghanon sa mga elemento nga makuha mao ang numero sa laray nga atong gikuha gikan sa kolum [Indeks]. Busa kini nga function alang sa unang laray nagbalik lamang sa usa ka unang cell sa kolum Gibaligya. Alang sa ikaduhang linya - na ang unang duha ka mga selula, alang sa ikatulo - ang unang tulo, ug uban pa.
Aw, unya ang function Listahan.Sum gisumada ang nakuha nga mga kantidad ug makuha namon sa matag laray ang sumada sa tanan nga nangaging mga elemento, ie kumulative total:
Nagpabilin nga tangtangon ang kolum sa Index nga dili na namo kinahanglan ug i-upload ang mga resulta balik sa Excel gamit ang Home - Close & Load to command.
Nasulbad na ang problema.
Pagpuasa ug Galit
Sa prinsipyo, kini mahimo unta nga nahunong, apan adunay usa ka gamay nga langaw sa pahumot - ang hangyo nga among gibuhat molihok sa gikusgon sa usa ka pawikan. Pananglitan, sa akong dili ang labing huyang nga PC, usa ka lamesa nga 2000 ra ka laray ang giproseso sa 17 segundos. Unsa kaha kung adunay daghang datos?
Aron mapadali, mahimo nimong gamiton ang buffering gamit ang espesyal nga List.Buffer function, nga nag-load sa listahan (lista) nga gihatag niini isip argumento ngadto sa RAM, nga makapadali sa pag-access niini sa umaabot. Sa among kaso, makatarunganon nga i-buffer ang #"Gidugang nga indeks" [Gibaligya] nga lista, nga kinahanglan ma-access sa Power Query kung kalkulado ang total nga nagdagan sa matag laray sa among 2000-row nga lamesa.
Aron mahimo kini, sa Power Query editor sa Main tab, i-klik ang Advanced Editor button (Home – Advanced Editor) aron maablihan ang source code sa among pangutana sa M language nga gitukod sa Power Query:
Ug dayon pagdugang usa ka linya nga adunay usa ka variable didto Akong Listahan, ang bili niini gibalik sa buffering function, ug sa sunod nga lakang atong ilisan ang tawag sa lista sa kini nga variable:
Pagkahuman sa paghimo niini nga mga pagbag-o, ang among pangutana mahimong labi ka paspas ug makasagubang sa usa ka 2000-row nga lamesa sa 0.3 segundos lang!
Laing butang, di ba? 🙂
- Pareto nga tsart (80/20) ug kung giunsa kini paghimo sa Excel
- Pagpangita sa keyword sa teksto ug pag-buffer sa pangutana sa Power Query