1. metode. Formulas
Sāksim, iesildīšanai, ar vienkāršāko variantu – formulām. Ja mums ir maza tabula, kas ir sakārtota pēc datuma kā ievade, tad, lai aprēķinātu kopējo summu atsevišķā kolonnā, mums ir nepieciešama elementāra formula:
Galvenā iezīme šeit ir viltīgā diapazona fiksēšana funkcijā SUM – atsauce uz diapazona sākumu tiek padarīta absolūta (ar dolāra zīmēm), bet uz beigām – relatīva (bez dolāriem). Attiecīgi, kopējot formulu uz leju līdz visai kolonnai, mēs iegūstam paplašināmu diapazonu, kura summu mēs aprēķinām.
Šīs pieejas trūkumi ir acīmredzami:
- Tabula jāsakārto pēc datuma.
- Pievienojot jaunas rindas ar datiem, formula būs jāpaplašina manuāli.
2. metode. Rakurstabula
Šī metode ir nedaudz sarežģītāka, bet daudz patīkamāka. Un, lai to saasinātu, aplūkosim nopietnāku problēmu – 2000 datu rindu tabulu, kurā nav šķirošanas pēc datuma kolonnas, bet ir atkārtojumi (proti, vienā dienā varam pārdot vairākas reizes):
Mēs pārvēršam savu sākotnējo tabulu par “viedo” (dinamisko) īsinājumtaustiņu Ctrl+T vai komanda Sākums — formatējiet kā tabulu (Sākums — formatēt kā tabulu), un pēc tam mēs uz tā izveidojam rakurstabulu ar komandu Ievietošana — PivotTable (Ievietot — rakurstabula). Kopsavilkuma rindu apgabalā ievietojam datumu, bet vērtību apgabalā - pārdoto preču skaitu:
Lūdzu, ņemiet vērā: ja jums ir ne visai veca Excel versija, datumi tiek automātiski grupēti pēc gadiem, ceturkšņiem un mēnešiem. Ja jums ir nepieciešams cits grupējums (vai tas vispār nav vajadzīgs), varat to labot, ar peles labo pogu noklikšķinot uz jebkura datuma un atlasot komandas Grupēt / atgrupēt (Grupēt / atgrupēt).
Ja vēlaties atsevišķā kolonnā skatīt gan iegūtās kopsummas pa periodiem, gan tekošo kopsummu, ir lietderīgi iemest lauku vērtību apgabalā. Pārdots vēlreiz, lai iegūtu lauka dublikātu – tajā ieslēgsim tekošo kopsummu rādīšanu. Lai to izdarītu, ar peles labo pogu noklikšķiniet uz lauka un atlasiet komandu Papildu aprēķini – kumulatīvā summa (Rādīt vērtības kā — tekošās kopsummas):
Tur varat arī izvēlēties iespēju palielināt kopsummas procentos, un nākamajā logā jāizvēlas lauks, kuram tiks uzkrājums – mūsu gadījumā šis ir datuma lauks:
Šīs pieejas priekšrocības:
- Liels datu apjoms tiek ātri nolasīts.
- Formulas nav jāievada manuāli.
- Mainot avota datus, pietiek atjaunināt kopsavilkumu ar peles labo pogu vai ar komandu Data – Refresh All.
Trūkumi izriet no tā, ka šis ir kopsavilkums, kas nozīmē, ka tajā nevar darīt visu, ko gribi (ielikt rindiņas, rakstīt formulas, veidot jebkādas diagrammas utt.) vairs nedarbosies.
3. metode: Power Query
Ielādēsim mūsu viedo tabulu ar avota datiem Power Query vaicājumu redaktorā, izmantojot komandu Dati — no tabulas/diapazona (Dati — no tabulas/diapazona). Starp citu, jaunākajās Excel versijās tas tika pārdēvēts – tagad to sauc Ar lapām (No lapas):
Pēc tam mēs veiksim šādas darbības:
1. Sakārtojiet tabulu augošā secībā pēc datuma kolonnas ar komandu Kārtot augošā secībā tabulas galvenes filtru nolaižamajā sarakstā.
2. Nedaudz vēlāk, lai aprēķinātu kopējo summu, mums ir nepieciešama papildu kolonna ar kārtas rindas numuru. Pievienosim to ar komandu Pievienot kolonnu — rādītāja kolonna — no 1 (Pievienot kolonnu — rādītāja kolonnu — no 1).
3. Turklāt, lai aprēķinātu kopējo kopējo summu, mums ir nepieciešama atsauce uz kolonnu Pārdots, kur atrodas mūsu apkopotie dati. Programmā Power Query kolonnas sauc arī par sarakstiem (sarakstu), un, lai iegūtu saiti uz to, ar peles labo pogu noklikšķiniet uz kolonnas galvenes un atlasiet komandu Detaļas (Rādīt detalizētu informāciju). Nepieciešamā izteiksme parādīsies formulas joslā, kas sastāv no iepriekšējās darbības nosaukuma #"Indekss pievienots", no kurienes ņemam tabulu un kolonnas nosaukumu [Pārdošana] no šīs tabulas kvadrātiekavās:
Kopējiet šo izteiksmi starpliktuvē turpmākai lietošanai.
4. Izdzēsiet vairāk nevajadzīgo pēdējo darbību Pārdots un tā vietā pievienojiet aprēķināto kolonnu, lai aprēķinātu kopējo darbību ar komandu Kolonnas pievienošana — pielāgota kolonna (Pievienot kolonnu — pielāgota kolonna). Mums nepieciešamā formula izskatīsies šādi:
Šeit ir funkcija Saraksts.Diapazons ņem sākotnējo sarakstu (kolonna [Pārdošana]) un izvelk no tā elementus, sākot no pirmā (formulā tas ir 0, jo Power Query numerācija sākas no nulles). Izgūstamo elementu skaits ir rindas numurs, ko mēs paņemam no kolonnas [Rādītājs]. Tātad šī funkcija pirmajai rindai atgriež tikai vienu kolonnas pirmo šūnu Pārdots. Otrajai rindai – jau pirmās divas šūnas, trešajai – pirmās trīs utt.
Nu, tad funkcija Saraksts.Summa summē iegūtās vērtības un katrā rindā iegūstam visu iepriekšējo elementu summu, ti, kopējo summu:
Atliek dzēst mums vairs nevajadzīgo rādītāja kolonnu un augšupielādēt rezultātus atpakaļ programmā Excel, izmantojot komandu Sākums — Close & Load to.
Problēma ir atrisināta.
Ātrs un bez žēlastības
Principā to varēja apturēt, bet ir maza mušiņa – mūsu izveidotais pieprasījums darbojas bruņurupuča ātrumā. Piemēram, manā, ne vājākajā datorā, tikai 2000 rindu tabula tiek apstrādāta 17 sekundēs. Ko darīt, ja ir vairāk datu?
Lai paātrinātu, var izmantot buferizāciju, izmantojot speciālo funkciju List.Buffer, kas tam kā argumentam doto sarakstu (sarakstu) ielādē operatīvajā atmiņā, kas ievērojami paātrina piekļuvi tam nākotnē. Mūsu gadījumā ir lietderīgi buferizēt sarakstu #”Pievienots indekss”[Pārdots], kuram Power Query ir jāpiekļūst, aprēķinot kopējo rindu katrā mūsu 2000 rindu tabulas rindā.
Lai to izdarītu, Power Query redaktorā cilnē Galvenā noklikšķiniet uz pogas Papildu redaktors (Sākums — Papildu redaktors), lai atvērtu mūsu vaicājuma pirmkodu Power Query iebūvētajā M valodā:
Un tad pievienojiet tur rindiņu ar mainīgo Mans saraksts, kuras vērtību atgriež buferizācijas funkcija, un nākamajā darbībā mēs aizstājam saraksta izsaukumu ar šo mainīgo:
Pēc šo izmaiņu veikšanas mūsu vaicājums kļūs ievērojami ātrāks un tiks galā ar 2000 rindu tabulu tikai 0.3 sekundēs!
Cita lieta, vai ne? 🙂
- Pareto diagramma (80/20) un kā to izveidot programmā Excel
- Atslēgvārdu meklēšana tekstā un vaicājumu buferizācija programmā Power Query