Paraan 1. Mga Formula
Magsimula tayo, para sa pag-init, gamit ang pinakasimpleng opsyon – mga formula. Kung mayroon kaming maliit na talahanayan na pinagsunod-sunod ayon sa petsa bilang input, pagkatapos ay upang kalkulahin ang kabuuang tumatakbo sa isang hiwalay na hanay, kailangan namin ng elementarya na formula:
Ang pangunahing tampok dito ay ang nakakalito na pag-aayos ng hanay sa loob ng SUM function - ang reference sa simula ng hanay ay ginawang ganap (na may mga dollar sign), at hanggang sa dulo - kamag-anak (nang walang dolyar). Alinsunod dito, kapag kinokopya ang formula hanggang sa buong hanay, nakakakuha kami ng isang lumalawak na hanay, ang kabuuan ng kung saan namin kinakalkula.
Ang mga kawalan ng pamamaraang ito ay halata:
- Ang talahanayan ay dapat ayusin ayon sa petsa.
- Kapag nagdaragdag ng mga bagong row na may data, ang formula ay kailangang manual na palawigin.
Paraan 2. Pivot table
Ang pamamaraang ito ay medyo mas kumplikado, ngunit mas kaaya-aya. At para lumala, isaalang-alang natin ang isang mas seryosong problema – isang talahanayan ng 2000 hilera ng data, kung saan walang pag-uuri ayon sa column ng petsa, ngunit may mga pag-uulit (ibig sabihin, maaari tayong magbenta ng ilang beses sa parehong araw):
Kino-convert namin ang aming orihinal na talahanayan sa isang "matalinong" (dynamic) na keyboard shortcut Ctrl+T o pangkat Tahanan – I-format bilang isang talahanayan (Home — Format bilang Talahanayan), at pagkatapos ay bumuo kami ng pivot table dito gamit ang command Ipasok – PivotTable (Ipasok — Pivot Table). Inilalagay namin ang petsa sa lugar ng mga hilera sa buod, at ang bilang ng mga kalakal na ibinebenta sa lugar ng mga halaga:
Pakitandaan na kung mayroon kang hindi masyadong lumang bersyon ng Excel, ang mga petsa ay awtomatikong ipapangkat ayon sa mga taon, quarter at buwan. Kung kailangan mo ng ibang pagpapangkat (o hindi na kailangan), maaari mo itong ayusin sa pamamagitan ng pag-right click sa anumang petsa at pagpili ng mga command Pangkatin / Alisin sa pangkat (Pangkatin / Alisin sa pangkat).
Kung gusto mong makita ang mga resultang kabuuan ayon sa mga tuldok at ang kabuuang tumatakbo sa isang hiwalay na column, makatuwirang itapon ang field sa lugar ng halaga Nabenta muli upang makakuha ng duplicate ng field – sa loob nito ay i-on namin ang pagpapakita ng mga tumatakbong kabuuan. Upang gawin ito, mag-right-click sa field at piliin ang command Mga Karagdagang Pagkalkula – Pinagsama-samang Kabuuan (Ipakita ang Mga Halaga bilang — Mga Tumatakbong Kabuuan):
Doon ay maaari mo ring piliin ang opsyon ng paglaki ng mga kabuuan bilang isang porsyento, at sa susunod na window kailangan mong piliin ang field kung saan pupunta ang akumulasyon - sa aming kaso, ito ang field ng petsa:
Ang mga bentahe ng diskarteng ito:
- Ang isang malaking halaga ng data ay mabilis na nabasa.
- Walang mga formula na kailangang manu-manong ipasok.
- Kapag nagbabago sa source data, sapat na upang i-update ang buod gamit ang kanang pindutan ng mouse o gamit ang Data – Refresh All command.
Ang mga disadvantages ay sumusunod mula sa katotohanan na ito ay isang buod, na nangangahulugan na hindi mo magagawa ang anumang gusto mo dito (magpasok ng mga linya, magsulat ng mga formula, bumuo ng anumang mga diagram, atbp.) ay hindi na gagana.
Paraan 3: Power Query
I-load natin ang aming "matalinong" table na may source data sa editor ng query ng Power Query gamit ang command Data – Mula sa Talahanayan/Saklaw (Data — Mula sa Talahanayan/Saklaw). Sa mga pinakabagong bersyon ng Excel, siya nga pala, pinalitan ng pangalan - ngayon ay tinatawag na May mga dahon (Mula sa Sheet):
Pagkatapos ay gagawin namin ang mga sumusunod na hakbang:
1. Pagbukud-bukurin ang talahanayan sa pataas na pagkakasunud-sunod ayon sa column ng petsa na may command Pataas sa drop-down na listahan ng filter sa header ng talahanayan.
2. Makalipas ang ilang sandali, upang kalkulahin ang kabuuang tumatakbo, kailangan namin ng isang pantulong na haligi na may numero ng ordinal na hilera. Idagdag natin ito sa utos Magdagdag ng Column – Index Column – Mula sa 1 (Magdagdag ng column — Index column — Mula sa 1).
3. Gayundin, upang kalkulahin ang kabuuang tumatakbo, kailangan namin ng sanggunian sa column Nabenta, kung saan matatagpuan ang aming summarized data. Sa Power Query, ang mga column ay tinatawag ding mga listahan (listahan) at para makakuha ng link dito, i-right-click ang column header at piliin ang command Detalye (Ipakita ang mga detalye). Ang expression na kailangan namin ay lalabas sa formula bar, na binubuo ng pangalan ng nakaraang hakbang #”Idinagdag ang index”, mula sa kung saan namin kinukuha ang talahanayan at ang pangalan ng column [Benta] mula sa talahanayang ito sa mga square bracket:
Kopyahin ang expression na ito sa clipboard para sa karagdagang paggamit.
4. Tanggalin ang hindi kailangan pang huling hakbang Nabenta at sa halip ay magdagdag ng isang kinakalkula na column para sa pagkalkula ng tumatakbong kabuuang gamit ang command Pagdaragdag ng Column – Custom Column (Magdagdag ng column — Custom na column). Ang formula na kailangan natin ay magiging ganito:
Narito ang function Listahan. Saklaw kinukuha ang orihinal na listahan (column [Sales]) at kinukuha ang mga elemento mula dito, simula sa una (sa formula, ito ay 0, dahil ang pagnunumero sa Power Query ay nagsisimula sa zero). Ang bilang ng mga elementong kukunin ay ang row number na kinukuha namin mula sa column [Index]. Kaya ang function na ito para sa unang hilera ay nagbabalik lamang ng isang unang cell ng column Nabenta. Para sa pangalawang linya - na ang unang dalawang cell, para sa pangatlo - ang unang tatlo, atbp.
Well, pagkatapos ay ang function Listahan.Sum nagsusuma ng mga nakuhang halaga at nakukuha namin sa bawat hilera ang kabuuan ng lahat ng nakaraang elemento, ibig sabihin, pinagsama-samang kabuuan:
Ito ay nananatiling tanggalin ang Index column na hindi na namin kailangan at i-upload ang mga resulta pabalik sa Excel gamit ang Home – Close & Load to command.
Ang problema ay nalutas.
Mabilis at galit na galit
Sa prinsipyo, ito ay maaaring tumigil, ngunit mayroong isang maliit na langaw sa pamahid - ang kahilingan na aming nilikha ay gumagana sa bilis ng isang pagong. Halimbawa, sa aking hindi ang pinakamahina na PC, isang talahanayan ng 2000 row lamang ang naproseso sa loob ng 17 segundo. Paano kung marami pang data?
Upang mapabilis, maaari mong gamitin ang buffering gamit ang espesyal na List.Buffer function, na naglo-load ng listahan (listahan) na ibinigay dito bilang argumento sa RAM, na lubos na nagpapabilis sa pag-access dito sa hinaharap. Sa aming kaso, makatuwirang i-buffer ang #"Idinagdag na index" [Nabenta] na listahan, na kailangang i-access ng Power Query kapag kinakalkula ang kabuuang tumatakbo sa bawat row ng aming 2000-row na talahanayan.
Upang gawin ito, sa editor ng Power Query sa Main tab, i-click ang pindutan ng Advanced na Editor (Home – Advanced Editor) upang buksan ang source code ng aming query sa wikang M na binuo sa Power Query:
At pagkatapos ay magdagdag ng isang linya na may isang variable doon Aking listahan, ang halaga nito ay ibinalik ng buffering function, at sa susunod na hakbang ay papalitan namin ang tawag sa listahan ng variable na ito:
Pagkatapos gawin ang mga pagbabagong ito, ang aming query ay magiging mas mabilis at makakayanan ang isang 2000-row na talahanayan sa loob lamang ng 0.3 segundo!
Isa pang bagay, tama ba? 🙂
- Pareto chart (80/20) at kung paano ito buuin sa Excel
- Paghahanap ng keyword sa text at query buffering sa Power Query