Skriešanas kopsumma (tiek saukta arī kumulatīvā summa) ir diezgan bieži izmantota daudzās situācijās. Tā ir metrika, kas parāda, kāda ir līdzšinējo vērtību summa.
Piemēram, ja jums ir ikmēneša pārdošanas dati, tad tekošā kopsumma parādīs, cik daudz pārdošanas ir veikts līdz noteiktai dienai no mēneša pirmās dienas.
Ir arī dažas citas situācijas, kad bieži tiek izmantota kopējā summa, piemēram, naudas atlikuma aprēķināšana bankas izrakstos/virsgrāmatā, kaloriju skaitīšana ēdienkartē utt.
Programmā Microsoft Excel ir vairāki dažādi veidi, kā aprēķināt kopējos rādītājus.
Jūsu izvēlētā metode būs atkarīga arī no jūsu datu struktūras.
Piemēram, ja jums ir vienkārši tabulas dati, varat izmantot vienkāršu SUM formulu, bet, ja jums ir Excel tabula, vislabāk ir izmantot strukturētas atsauces. Lai to izdarītu, varat izmantot arī Power Query.
Šajā apmācībā es apskatīšu visas šīs dažādās metodes aprēķiniet darbības kopsummas programmā Excel.
Tātad, sāksim!
Kopējās darbības aprēķināšana ar tabulas datiem
Ja jums ir tabulas dati (t.i., tabula programmā Excel, kas nav pārvērsta Excel tabulā), varat izmantot dažas vienkāršas formulas, lai aprēķinātu kopējos rādītājus.
Papildu operatora izmantošana
Pieņemsim, ka jums ir datuma dati par pārdošanu un C slejā vēlaties aprēķināt tekošo kopsummu.
Tālāk ir norādītas darbības, kā to izdarīt.
1. darbība - Šūnā C2, kas ir pirmā šūna, kurā vēlaties rādīt kopējo summu, ievadiet
= B2
Tas vienkārši iegūs tādas pašas pārdošanas vērtības šūnā B2.
2. solis - Šūnā C3 ievadiet šādu formulu:
= C2+B3
3. solis - Izmantojiet formulu visai kolonnai. Jūs varat izmantot aizpildīšanas rokturi, lai to atlasītu un vilktu, vai vienkārši un kopējiet un ielīmējiet šūnu C3 uz visām atlikušajām šūnām (kas automātiski pielāgotu atsauci un sniegtu pareizo rezultātu).
Tas sniegs jums rezultātu, kā parādīts zemāk.
Tā ir patiešām vienkārša metode, un vairumā gadījumu tā darbojas labi.
Loģika ir vienkārša - katra šūna iegūst vērtību virs tās (kas ir kumulatīvā summa līdz iepriekšējam datumam) un pievieno vērtību blakus esošajā šūnā (kas ir šīs dienas pārdošanas vērtība).
Ir tikai viens trūkums - ja dzēšat kādu no esošajām rindām šajā datu kopā, visas tālāk esošās šūnas, kas atgriezīs atsauces kļūdu (#REF!)
Ja šāda iespēja ir jūsu datu kopā, izmantojiet nākamo metodi, kurā tiek izmantota SUM formula
SUM izmantošana ar daļēji bloķētu šūnu atsauci
Pieņemsim, ka jums ir datuma dati par pārdošanu un C slejā vēlaties aprēķināt tekošo kopsummu.
Zemāk ir SUM formula, kas sniegs jums kopējo rādītāju.
= SUMMA ($ B $ 2: B2)
Ļaujiet man paskaidrot, kā šī formula darbojas.
Iepriekš minētajā SUM formulā esmu izmantojis atsauci, lai pievienotu kā $ B $ 2: B2
- $ B $ 2 - šī ir absolūta atsauce, kas nozīmē, ka, kopējot to pašu formulu zemāk esošajās šūnās, šī atsauce nemainīsies. Tātad, kopējot formulu zemāk esošajā šūnā, formula mainīsies uz SUM ($ B $ 2: B3)
- B2 - šī ir atsauces otrā daļa, kas ir relatīva atsauce, kas nozīmē, ka tas mainīsies, kad kopēju formulu uz leju vai pa labi. Tātad, kopējot formulu zemāk esošajā šūnā, šī vērtība kļūs par B3
Lasiet arī: Absolūtās, relatīvās un jaukto šūnu atsauces programmā Excel
Šīs metodes lieliskais ir tas, ka gadījumā, ja izdzēsīsit kādu no datu kopas rindām, šī formula tiks pielāgota un joprojām sniegs pareizos rādītājus.
Kopējās darbības aprēķināšana Excel tabulā
Strādājot ar tabulas datiem programmā Excel, ieteicams tos pārvērst Excel tabulā. Tas ievērojami atvieglo datu pārvaldību, kā arī ļauj ērti izmantot tādus rīkus kā Power Query un Power Pivot.
Darbam ar Excel tabulām ir tādas priekšrocības kā strukturētas atsauces (kas ļauj patiešām viegli atsaukties uz tabulas datiem un izmantot tās formulās), kā arī automātiska atsauču pielāgošana, ja pievienojat vai izdzēšat datus no tabulas.
Lai gan jūs joprojām varat izmantot iepriekš minēto formulu, ko esmu jums parādījis Excel tabulā, ļaujiet man parādīt dažas labākas metodes, kā to izdarīt.
Pieņemsim, ka jums ir Excel tabula, kā parādīts zemāk, un vēlaties aprēķināt kopējo vērtību C slejā.
Zemāk ir formula, kas to darīs:
= SUM (pārdošanas dati [[#galvenes], [izpārdošana]]: [@izpārdošana])
Iepriekš minētā formula var šķist nedaudz gara, taču jums tas nav jāraksta pašam. to, ko redzat summas formulā, sauc par strukturētām atsaucēm, kas ir efektīvs Excel veids, kā atsaukties uz noteiktiem datu punktiem Excel tabulā.
Piemēram, SalesData [[#Headers], [Sale]] attiecas uz pārdošanas pārdošanas virsrakstu tabulā SalesData (SalesData ir tās Excel tabulas nosaukums, kuru es norādīju, izveidojot tabulu)
Un [@Pārdošana] attiecas uz vērtību šūnā tās pašas rindas slejā Pārdošana.
Es to šeit tikai paskaidroju, lai saprastu, bet pat ja jūs neko nezināt par strukturētām atsaucēm, jūs joprojām varat viegli izveidot šo formulu.
Tālāk ir norādītas darbības, kā to izdarīt:
- Šūnā C2 ievadiet = SUM (
- Atlasiet šūnu B1, kas ir tās slejas galvene, kurai ir pārdošanas vērtība. Jūs varat izmantot peli vai bulttaustiņus. Jūs pamanīsit, ka Excel automātiski ievada šīs šūnas strukturēto atsauci
- Pievienojiet: (kolu simbols)
- Atlasiet šūnu B2. Excel atkal automātiski ievietos šūnas strukturēto atsauci
- Aizveriet iekavu un nospiediet enter
Jūs arī pamanīsit, ka jums nav jākopē formula visā kolonnā, Excel tabula to automātiski dara jūsu vietā.
Vēl viena lieliska šīs metodes priekšrocība ir tāda, ka gadījumā, ja šai datu kopai pievienojat jaunu ierakstu, Excel tabula automātiski aprēķina visu jauno ierakstu darbības kopsummu.
Kamēr mēs esam iekļāvuši kolonnas galveni mūsu formulā, atcerieties, ka formula ignorētu galvenes tekstu un ņemtu vērā tikai slejas datus
Kopējās darbības aprēķināšana, izmantojot Power Query
Power Query ir pārsteidzošs rīks, lai izveidotu savienojumu ar datu bāzēm, iegūtu datus no vairākiem avotiem un pārveidotu tos pirms ievietošanas programmā Excel.
Ja jūs jau strādājat ar Power Query, efektīvāk būtu pievienot darbības kopsummas, kamēr pārveidojat datus pašā Power Query redaktorā (tā vietā, lai pirmo reizi iegūtu datus programmā Excel un pēc tam pievienotu darbības kopsummas, izmantojot kādu no iepriekš minētajām metodēm). iepriekš aprakstītās metodes).
Lai gan Power Query nav iebūvēta līdzekļa, lai pievienotu darbības kopsummas (kas, manuprāt, būtu), jūs joprojām varat to izdarīt, izmantojot vienkāršu formulu.
Pieņemsim, ka jums ir Excel tabula, kā parādīts zemāk, un vēlaties šiem datiem pievienot darbības kopsummas:
Tālāk ir norādītas darbības, kā to izdarīt:
- Excel tabulā atlasiet jebkuru šūnu
- Noklikšķiniet uz Dati
- Cilnē Iegūt un pārveidot noklikšķiniet uz ikonas no tabulas/diapazona. Tādējādi tiks atvērta tabula Power Query redaktorā
- [Neobligāti] Ja sleja Datums vēl nav sakārtota, noklikšķiniet uz filtra ikonas slejā Datums un pēc tam noklikšķiniet uz Kārtot augošā secībā
- Power Query redaktorā noklikšķiniet uz cilnes Pievienot kolonnu
- Grupā Vispārīgi noklikšķiniet uz nolaižamā saraksta Indeksa kolonna (neklikšķiniet uz indeksa kolonnas ikonas, bet uz mazās melnās slīpās bultiņas blakus tai, lai parādītu vairāk iespēju)
- Noklikšķiniet uz opcijas “No 1”. To darot, tiks pievienota jauna indeksa sleja, kas sāksies no vienas, un visā kolonnā ievadīs skaitļus, kas palielinās par 1
- Noklikšķiniet uz ikonas “Pielāgota kolonna” (kas atrodas arī cilnē Pievienot kolonnu).
- Atvērtajā pielāgotās slejas dialoglodziņā ievadiet jaunās kolonnas nosaukumu. šajā piemērā es izmantošu nosaukumu “Running Total”
- Laukā Pielāgotas slejas formula ievadiet zemāk esošo formulu: List.Sum (List.Range (#”Pievienotais indekss” [izpārdošana], 0, [rādītājs]))
- Pārliecinieties, vai dialoglodziņa apakšā ir izvēles rūtiņa ar tekstu “Nav atrastas sintakses kļūdas”.
- Noklikšķiniet uz Labi. Tas pievienotu jaunu kolonnu, kas darbojas kopā
- Noņemiet indeksa kolonnu
- Noklikšķiniet uz cilnes Fails un pēc tam noklikšķiniet uz “Aizvērt un ielādēt”
Iepriekš minētās darbības jūsu darbgrāmatā ievietotu jaunu lapu ar tabulu, kurā ir rādītāji.
Tagad, ja jūs domājat, ka tie ir pārāk daudz soļu, salīdzinot ar iepriekšējām vienkāršu formulu izmantošanas metodēm, jums ir taisnība.
Ja jums jau ir datu kopa un viss, kas jums jādara, ir jāpievieno kopējie rādītāji, labāk nelietot Power Query.
Power Query izmantošanai ir jēga, ja jums ir jāizgūst dati no datu bāzes vai jāapvieno dati no vairākām dažādām darbgrāmatām, kā arī jāpievieno tai darbības rādītāji.
Turklāt, veicot šo automatizāciju, izmantojot Power Query, nākamreiz, kad datu kopa mainīsies, jums tas vairs nebūs jādara vēlreiz, varat vienkārši atsvaidzināt vaicājumu, un tas sniegs rezultātu, pamatojoties uz jauno datu kopu.
Kā tas darbojas?
Tagad ļaujiet man ātri izskaidrot, kas notiek šajā metodē.
Pirmā lieta, ko mēs darām Power Query redaktorā, ir ievietot indeksa kolonnu, sākot no vienas un pieaugot par vienu, kad tā iet uz leju pa šūnām.
Mēs to darām, jo mums ir jāizmanto šī sleja, kamēr mēs aprēķinām kopējo darbību citā kolonnā, kuru ievietojam nākamajā darbībā.
Tad mēs ievietojam pielāgotu kolonnu un izmantojam zemāk esošo formulu
List.Sum (List.Range (#"Pievienotais indekss" [izpārdošana], 0, [indekss]))
Šī ir List.Sum formula, kas sniegtu tajā norādītā diapazona summu.
Un šis diapazons ir norādīts, izmantojot funkciju List.Range.
Funkcija List.Range kā izlaidi norāda norādīto diapazonu pārdošanas kolonnā, un šis diapazons mainās atkarībā no indeksa vērtības. Piemēram, pirmajam ierakstam diapazons būtu vienkārši pirmā pārdošanas vērtība. Un, ejot pa šūnām, šis diapazons paplašināsies.
Tātad, pirmajai šūnai. Saraksts. Summa sniegs jums tikai pirmās pārdošanas vērtības summu, bet otrajai šūnai - summu par pirmajām divām pārdošanas vērtībām un tā tālāk.
Lai gan šī metode darbojas labi, tā kļūst ļoti lēna, izmantojot lielas datu kopas - tūkstošiem rindu. Ja strādājat ar lielu datu kopu un vēlaties tai pievienot darbības rādītājus, ieskatieties šajā apmācībā, kas parāda citas ātrākas metodes.Kopējās darbības aprēķināšana, pamatojoties uz kritērijiem
Līdz šim mēs esam redzējuši piemērus, kad esam aprēķinājuši kopējo vērtību visām kolonnas vērtībām.
Bet var būt gadījumi, kad vēlaties aprēķināt konkrēto ierakstu kopējo gaitu.
Piemēram, zemāk man ir datu kopa, un es vēlos atsevišķi aprēķināt printeru un skeneru darbības kopsummu divās dažādās kolonnās.
To var izdarīt, izmantojot SUMIF formulu, kas aprēķina kopējo braukšanas laiku, vienlaikus pārliecinoties, ka ir izpildīts noteiktais nosacījums.
Zemāk ir formula, kas to darīs kolonnās Printeris:
= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)
Līdzīgi, lai aprēķinātu skeneru kopējo darbību, izmantojiet šādu formulu:
= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)
Iepriekšminētajās formulās esmu izmantojis SUMIF, kas dotu man summu diapazonā, kad ir izpildīti norādītie kritēriji.
Formulai ir trīs argumenti:
- diapazons: šis ir kritēriju diapazons, kas tiks pārbaudīts atbilstoši noteiktajiem kritērijiem
- kritērijiem: šie ir kritēriji, kas tiktu reģistrēti tikai tad, ja šis kritērijs būtu izpildīts, tad tiktu pievienotas trešā argumenta vērtības, kas ir summas diapazons
- [summas_diapazons]: tas ir summu diapazons, no kura vērtības tiktu pievienotas, ja kritēriji ir izpildīti
Tāpat, diapazons un summas_diapazons argumentu, es esmu bloķējis atsauces otro daļu, lai, ejot lejup pa šūnām, diapazons turpinātu paplašināties. Tas ļauj mums tikai apsvērt un pievienot vērtības līdz šim diapazonam (tātad rādīt kopsummas).
Šajā formulā kā kritēriju esmu izmantojis galvenes kolonnu (Printeris un skeneris). varat arī kodēt kritērijus, ja sleju galvenes nav tieši tādas pašas kā kritēriju teksts.
Ja jums ir jāpārbauda vairāki nosacījumi, varat izmantot SUMIFS formulu.
Kopējais rādītājs rakurstabulās
Ja rakurstabulas rezultātam vēlaties pievienot darbības kopsummas, varat to viegli izdarīt, izmantojot iebūvēto funkcionalitāti rakurstabulās.
Pieņemsim, ka jums ir rakurstabula, kā parādīts zemāk, kur man ir datums vienā slejā un pārdošanas vērtība otrā slejā.
Tālāk ir norādītas darbības, lai pievienotu papildu kolonnu, kurā tiks parādīta pārdošanas kopējā summa pēc datuma:
- Velciet lauku Pārdošana un ievietojiet to apgabalā Vērtība.
- Tādējādi tiks pievienota vēl viena sleja ar pārdošanas vērtībām
- Sadaļā Vērtība noklikšķiniet uz opcijas Pārdošanas summa2
- Noklikšķiniet uz opcijas “Vērtības lauka iestatījumi”
- Dialoglodziņā Vērtību lauku iestatījumi mainiet pielāgoto nosaukumu uz “Running Totals”
- Noklikšķiniet uz cilnes “Rādīt vērtību kā”
- Nolaižamajā izvēlnē Rādīt vērtību kā atlasiet opciju “Running Total in”
- Lauka bāzes opcijās pārliecinieties, vai ir atlasīts datums
- Noklikšķiniet uz Labi
Iepriekš minētās darbības mainītu otro pārdošanas sleju uz kolonnu Running Total.
Tātad šie ir daži veidi, kā varat aprēķināt kopējo darbību programmā Excel. Ja jums ir dati tabulas formātā, varat izmantot vienkāršas formulas, un, ja jums ir Excel tabula, tad varat izmantot formulas, kurās tiek izmantotas strukturētas atsauces.
Esmu arī apskatījis, kā aprēķināt kopējo braukšanu, izmantojot Power Query un rakurstabulās.
Es ceru, ka šī apmācība jums šķita noderīga.
