Metòd 1. Fòmil
Ann kòmanse, pou chofe, ak opsyon ki pi senp la - fòmil. Si nou gen yon ti tablo ki klase pa dat kòm opinyon, Lè sa a, pou kalkile total aktyèl la nan yon kolòn separe, nou bezwen yon fòmil elemantè:
Karakteristik prensipal la isit la se fikse difisil nan seri a andedan fonksyon SUM - referans a nan kòmansman an nan seri a se fè absoli (ak siy dola), ak nan fen a - relatif (san dola). An konsekans, lè kopye fòmil la desann nan kolòn nan tout antye, nou jwenn yon seri agrandi, sòm nan ki nou kalkile.
Dezavantaj yo nan apwòch sa a se evidan:
- Tablo a dwe klase pa dat.
- Lè w ajoute nouvo ranje ak done, fòmil la pral gen pou pwolonje manyèlman.
Metòd 2. Pivote tab
Metòd sa a se yon ti kras pi konplike, men pi plis bèl. Epi pou vin pi grav, ann konsidere yon pwoblèm ki pi grav - yon tablo 2000 ranje done, kote pa gen okenn klasman pa kolòn nan dat, men gen repetisyon (sa vle di nou ka vann plizyè fwa nan menm jou a):
Nou konvèti tablo orijinal nou an nan yon chemen kout klavye "entelijan" (dinamik). Ctrl+T oswa ekip Kay - Fòma kòm yon tab (Kay — Fòma kòm Tablo), ak Lè sa a, nou bati yon tab pivot sou li ak lòd la Mete - PivotTable (Antre — Tablo Pivote). Nou mete dat la nan zòn nan ranje nan rezime a, ak kantite machandiz vann nan zòn nan valè:
Tanpri sonje ke si ou gen yon vèsyon ki pa fin vye granmoun nan Excel, Lè sa a, dat yo otomatikman gwoupe pa ane, trimès ak mwa. Si ou bezwen yon gwoupman diferan (oswa ou pa bezwen li ditou), Lè sa a, ou ka ranje li pa klike dwa sou nenpòt dat epi chwazi kòmandman. Gwoup / Degroupe (Gwoup / Degroupe).
Si ou vle wè tou de total ki kapab lakòz yo pa peryòd ak total la kouri nan yon kolòn separe, Lè sa a, li fè sans jete jaden an nan zòn nan valè. Vann ankò jwenn yon kopi nan jaden an - nan li nou pral vire sou ekspozisyon an nan total kouri. Pou fè sa, klike sou jaden an dwa-dwa epi chwazi lòd la Lòt Kalkil – Total kimilatif (Montre valè yo kòm — Total kouri):
Gen ou ka chwazi tou opsyon pou ogmante total kòm yon pousantaj, ak nan pwochen fenèt la ou bezwen chwazi jaden an pou ki akimilasyon pral ale - nan ka nou an, sa a se jaden an dat:
Avantaj ki genyen nan apwòch sa a:
- Yon gwo kantite done byen vit li.
- Pa gen fòmil yo bezwen antre manyèlman.
- Lè w ap chanje done sous yo, li ase pou mete rezime a ajou ak bouton sourit dwat la oswa ak lòd Done - Refresh All.
Dezavantaj yo soti nan lefèt ke sa a se yon rezime, ki vle di ke ou pa ka fè sa ou vle nan li (insert liy, ekri fòmil, bati nenpòt dyagram, elatriye) p ap travay ankò.
Metòd 3: Power Query
Ann chaje tab "entelijan" nou an ak done sous nan editè rechèch Power Query lè l sèvi avèk kòmandman an Done - Soti nan Tablo / Ranje (Done - Soti nan Tablo / Ranje). Nan dènye vèsyon yo nan Excel, nan chemen an, li te chanje non - kounye a yo rele li Ak fèy (Soti nan Fèy):
Lè sa a, nou pral fè etap sa yo:
1. Ranje tablo a nan lòd monte pa kolòn dat ak kòmandman an Triye monte nan lis dewoulman filtre nan header tab la.
2. Yon ti kras pita, pou kalkile total kouri a, nou bezwen yon kolòn oksilyè ak nimewo ranje ordinal la. Ann ajoute li ak kòmandman an Ajoute Kolòn – Kolòn Endèks – Soti nan 1 (Ajoute kolòn — kolòn endèks — Soti nan 1).
3. Epitou, pou kalkile total kouri a, nou bezwen yon referans a kolòn nan Vann, kote done rezime nou yo bay manti. Nan Power Query, kolòn yo rele tou lis (lis) epi pou jwenn yon lyen pou li, klike sou tèt kolòn nan epi chwazi kòmandman an. Detaye (Montre detay). Ekspresyon nou bezwen an ap parèt nan ba fòmil la, ki gen non etap anvan an #"Endèks te ajoute", ki soti nan kote nou pran tab la ak non kolòn nan [Komèsyal] soti nan tablo sa a nan parantèz kare:
Kopi ekspresyon sa a nan clipboard la pou plis itilize.
4. Efase pa nesesè plis dènye etap Vann epi ajoute olye de sa yon kolòn kalkile pou kalkile total kouri ak lòd la Ajoute yon kolòn - Custom kolòn (Ajoute kolòn - kolòn Custom). Fòmil nou bezwen an pral sanble ak sa a:
Isit la fonksyon an List.Range pran lis orijinal la (kolòn [Lavant]) ak ekstrè eleman nan li, kòmanse nan premye a (nan fòmil la, sa a se 0, depi nimero nan Power Query kòmanse soti nan zewo). Nimewo a nan eleman yo rekipere se nimewo a ranje nou pran nan kolòn nan [Endèks]. Se konsa, fonksyon sa a pou premye ranje a sèlman retounen yon sèl premye selil kolòn nan Vann. Pou dezyèm liy lan - deja de premye selil yo, pou twazyèm lan - twa premye yo, elatriye.
Oke, Lè sa a, fonksyon an Lis.Sòm sòm valè ekstrè yo epi nou jwenn nan chak ranje sòm tout eleman anvan yo, sa vle di total kimilatif:
Li rete pou efase kolòn Index ke nou pa bezwen ankò epi telechaje rezilta yo tounen nan Excel ak Kay - Fèmen & Chaje bay lòd.
Pwoblèm nan rezoud.
Vit ak firye
Nan prensip, sa a te kapab sispann, men gen yon ti mouch nan odè a - demann nou te kreye travay nan vitès la nan yon tòti. Pou egzanp, sou mwen an pa pi fèb PC a, se yon tab nan sèlman 2000 ranje trete nan 17 segonn. E si gen plis done?
Pou pi vit, ou ka itilize tanpon lè l sèvi avèk fonksyon espesyal List.Buffer la, ki chaje lis la (lis) yo ba li kòm yon agiman nan RAM, ki akselere anpil aksè a li nan lavni. Nan ka nou an, li fè sans pou tampon #"Ajoute endèks" [Vann] lis la, ki Power Query gen aksè a lè w ap kalkile total aktyèl la nan chak ranje tab 2000-ranje nou an.
Pou fè sa, nan editè Power Query sou tab prensipal la, klike sou bouton Editè Avanse (Lakay – Editè Avanse) pou ouvri kòd sous rechèch nou an nan lang M ki te bati nan Power Query:
Apre sa, ajoute yon liy ak yon varyab la MyList, valè ki retounen pa fonksyon an tanpon, epi nan pwochen etap la nou ranplase apèl la nan lis la ak varyab sa a:
Apre yo fin fè chanjman sa yo, demann nou an ap vin siyifikativman pi vit epi li pral fè fas ak yon tab 2000-ranje nan jis 0.3 segonn!
Yon lòt bagay, pa vre? 🙂
- Tablo Pareto (80/20) ak kouman yo bati li nan Excel
- Rechèch mo kle nan tèks ak tanpon rechèch nan Power Query