Metodu 1. Formuli
Nibdew, għat-tisħin, bl-aktar għażla sempliċi - formuli. Jekk ikollna tabella żgħira magħżula skond id-data bħala input, allura biex nikkalkulaw it-total kurrenti f'kolonna separata, għandna bżonn formula elementari:
Il-karatteristika ewlenija hawnhekk hija l-iffissar delikat tal-firxa ġewwa l-funzjoni SUM - ir-referenza għall-bidu tal-firxa ssir assoluta (b'sinjali tad-dollaru), u sa l-aħħar - relattiva (mingħajr dollari). Għaldaqstant, meta nikkopja l-formula sal-kolonna kollha, aħna nġibu firxa tespandi, li s-somma tagħha nikkalkulaw.
L-iżvantaġġi ta 'dan l-approċċ huma ovvji:
- It-tabella trid tiġi magħżula skond id-data.
- Meta żżid ringieli ġodda bid-dejta, il-formula se jkollha tiġi estiża manwalment.
Metodu 2. Pivot table
Dan il-metodu huwa ftit aktar ikkumplikat, iżda ħafna aktar pjaċevoli. U biex tiggrava, ejja nikkunsidraw problema aktar serja – tabella ta’ 2000 ringiela ta’ dejta, fejn m’hemm l-ebda għażla skont il-kolonna tad-data, iżda hemm ripetizzjonijiet (jiġifieri nistgħu nbigħu diversi drabi fl-istess jum):
Aħna nikkonverti t-tabella oriġinali tagħna fi shortcut tat-tastiera "intelliġenti" (dinamika). Ctrl+T jew tim Home – Format bħala tabella (Id-dar — Format bħala Tabella), u mbagħad nibnu tabella tal-pern fuqha bil-kmand Daħħal – PivotTable (Daħħal — Pivot Table). Aħna npoġġu d-data fiż-żona ta 'ringieli fis-sommarju, u n-numru ta' oġġetti mibjugħa fiż-żona tal-valuri:
Jekk jogħġbok innota li jekk għandek verżjoni mhux antika ta 'Excel, allura d-dati huma awtomatikament raggruppati skond is-snin, kwarti u xhur. Jekk għandek bżonn grupp differenti (jew m'għandek bżonnha xejn), allura tista' tirranġah billi tikklikkja bil-lemin fuq kwalunkwe data u tagħżel kmandi Grupp / Ungroup (Grupp/Agħlaq).
Jekk trid tara kemm it-totali li jirriżultaw mill-perjodi kif ukoll it-total kurrenti f'kolonna separata, allura jagħmel sens li tarmi l-qasam fiż-żona tal-valur Mibjugħa għal darb'oħra biex tikseb duplikat tal-qasam - fiha se nixgħel il-wiri tat-totali kurrenti. Biex tagħmel dan, ikklikkja l-lemin fuq il-qasam u agħżel il-kmand Kalkoli Addizzjonali – Total Kumulattiv (Uri l-Valuri bħala — Totali kurrenti):
Hemmhekk tista 'wkoll tagħżel l-għażla li tkabbar it-totali bħala persentaġġ, u fit-tieqa li jmiss għandek bżonn tagħżel il-qasam li għalih se tmur l-akkumulazzjoni - fil-każ tagħna, dan huwa l-qasam tad-data:
Il-vantaġġi ta 'dan l-approċċ:
- Ammont kbir ta 'dejta jinqara malajr.
- L-ebda formuli ma jeħtieġ li jiddaħħlu manwalment.
- Meta tinbidel id-dejta tas-sors, huwa biżżejjed li taġġorna s-sommarju bil-buttuna tal-lemin tal-maws jew bil-kmand Data - Aġġorna Kollha.
L-iżvantaġġi jsegwu mill-fatt li dan huwa sommarju, li jfisser li ma tistax tagħmel dak kollu li trid fiha (daħħal linji, ikteb formuli, tibni xi dijagrammi, eċċ.) Mhux se taħdem aktar.
Metodu 3: Mistoqsija dwar l-Enerġija
Ejja tagħbija t-tabella "intelliġenti" tagħna bid-dejta tas-sors fl-editur tal-mistoqsijiet tal-Power Query billi tuża l-kmand Dejta – Minn Tabella/Firxa (Data — Minn Tabella/Medda). Fl-aħħar verżjonijiet ta 'Excel, bil-mod, ingħata isem ġdid - issa jissejjaħ Bil-weraq (Mill Folja):
Imbagħad se nwettqu l-passi li ġejjin:
1. Issortja t-tabella f'ordni axxendenti bil-kolonna tad-data bil-kmand Sort axxendenti fil-lista drop-down tal-filtru fl-intestatura tat-tabella.
2. Ftit aktar tard, biex nikkalkulaw it-total kurrenti, għandna bżonn kolonna awżiljarja bin-numru ta 'ringiela ordinali. Ejja nżiduha mal-kmand Żid Kolonna - Kolonna tal-Indiċi - Minn 1 (Żid il-kolonna — Kolonna tal-indiċi — Minn 1).
3. Ukoll, biex nikkalkulaw it-total kurrenti, għandna bżonn referenza għall-kolonna Mibjugħa, fejn tinsab id-dejta fil-qosor tagħna. Fil-Power Query, il-kolonni jissejħu wkoll listi (lista) u biex tikseb link għaliha, ikklikkja bil-lemin fuq l-intestatura tal-kolonna u agħżel il-kmand Dettaljar (Uri dettall). L-espressjoni li għandna bżonn tidher fil-bar tal-formula, li tikkonsisti fl-isem tal-pass preċedenti #"Indiċi miżjud", minn fejn nieħdu t-tabella u l-isem tal-kolonna [Bejgħ] minn din it-tabella fil-parentesi kwadri:
Ikkopja din l-espressjoni fil-clipboard għal aktar użu.
4. Ħassar l-aħħar pass mhux meħtieġ Mibjugħa u żid minflok kolonna kkalkulata għall-kalkolu tat-total kurrenti bil-kmand Żieda ta 'Kolonna - Kolonna Custom (Żid kolonna — Kolonna personalizzata). Il-formula li għandna bżonn se tidher bħal din:
Hawnhekk il-funzjoni Lista.Medda jieħu l-lista oriġinali (kolonna [Bejgħ]) u estratti elementi minnha, li jibdew mill-ewwel (fil-formula, dan huwa 0, peress li n-numerazzjoni f'Power Query tibda minn żero). In-numru ta 'elementi li għandhom jiġu rkuprati huwa n-numru ta' ringiela li nieħdu mill-kolonna [Indiċi]. Allura din il-funzjoni għall-ewwel ringiela tirritorna biss l-ewwel ċellula waħda tal-kolonna Mibjugħa. Għat-tieni linja - diġà l-ewwel żewġ ċelloli, għat-tielet - l-ewwel tlieta, eċċ.
Ukoll, allura l-funzjoni Lista.Sum jiġbor il-valuri estratti u nġibu f'kull ringiela s-somma tal-elementi kollha preċedenti, jiġifieri total kumulattiv:
Jibqa' li tħassar il-kolonna tal-Indiċi li m'għadniex neħtieġu u ttella' r-riżultati lura f'Excel bid-Dar - Agħlaq & Tagħbija għall-kmand.
Il-problema hija solvuta.
Fast u Furious
Fil-prinċipju, dan seta 'twaqqaf, iżda hemm dubbiena żgħira fl-ingwent - it-talba li ħloqna taħdem bil-veloċità ta' fekruna. Pereżempju, fuq il-PC mhux l-iktar dgħajjef tiegħi, tabella ta 'ringieli 2000 biss tiġi pproċessata fi 17-il sekonda. X'jiġri jekk ikun hemm aktar data?
Biex tħaffef, tista 'tuża buffering billi tuża l-funzjoni speċjali List.Buffer, li tgħabbi l-lista (lista) mogħtija lilha bħala argument fir-RAM, li tħaffef ħafna l-aċċess għaliha fil-futur. Fil-każ tagħna, jagħmel sens li nibferixxu l-lista #"Indiċi Miżjud"[Mibjugħ], li Power Query għandha taċċessa meta tikkalkula t-total kurrenti f'kull ringiela tat-tabella tagħna ta '2000 ringiela.
Biex tagħmel dan, fl-editur tal-Power Query fit-tab Prinċipali, ikklikkja l-buttuna Editur Avvanzat (Home – Editur Avvanzat) biex tiftaħ il-kodiċi tas-sors tal-mistoqsija tagħna fil-lingwa M mibnija fil-Power Query:
U mbagħad żid linja b'varjabbli hemmhekk MyList, li l-valur tiegħu jiġi rritornat mill-funzjoni tal-buffering, u fil-pass li jmiss nibdlu s-sejħa għal-lista b'din il-varjabbli:
Wara li nagħmlu dawn il-bidliet, il-mistoqsija tagħna ssir b'mod sinifikanti aktar mgħaġġla u se tlaħħaq ma 'tabella ta' 2000 ringiela f'0.3 sekondi biss!
Ħaġa oħra, hux? 🙂
- Ċart Pareto (80/20) u kif tibniha f'Excel
- Tfittxija bil-kliem kjavi fit-test u l-buffering tal-mistoqsijiet fil-Power Query