Kā aprēķināt kolonnas summu programmā Excel. Kā aprēķināt kolonnas summu programmā Excel Laboratory starpsummas programmā Excel

Strādājot programmā Excel, diezgan bieži rodas nepieciešamība apkopot starpsummas tabulā. Apskatīsim, kā to var izdarīt, izmantojot konkrētu piemēru.

Tabulas prasības starpsummu izmantošanai

Ne visās tabulās ir iespēja izmantot starpsummu aprēķināšanas funkciju. Zemāk ir saraksts ar galvenajām obligātajām prasībām tabulām:

1. Nav tukšu šūnu, t.i. visas rindas un kolonnas ir jāaizpilda ar datiem.
2. Tabulas galvenē nevar izmantot vairākas rindas. Tam jābūt attēlotam tikai vienā rindā. Un arī tās atrašanās vietai ir nozīme. Tam vajadzētu būt tikai pašā augšējā līnijā un nekur citur.
3. Tabulas formāts ir jāuzrāda regulāra šūnu laukuma veidā.

Tātad, tagad, kad esam izlēmuši par galvenajiem kritērijiem tabulu “piemērotībai”, sāksim aprēķināt starpsummas.

Pieņemsim, ka mums ir tabula ar produktu pārdošanas rezultātiem, kas sadalīti pa rindiņām pa dienām. Jums ir jāaprēķina visu preču kopējais pārdošanas apjoms katrai atsevišķai dienai un pēc tam jāaprēķina kopējais pārdošanas apjoms visām dienām.

1. Atzīmējiet jebkuru tabulas šūnu, pārejiet uz cilni "Dati", atrodiet sadaļu "Struktūra", noklikšķiniet uz tās un atvērtajā sarakstā noklikšķiniet uz opcijas "Starpsumma".
2. Rezultātā parādīsies logs, kurā mēs veiksim turpmākus iestatījumus atbilstoši mūsu uzdevumam.
   
3. Tātad mums ir jāaprēķina visu produktu ikdienas pārdošanas apjoms. Informācija par pārdošanas datumu tiek ievietota tāda paša nosaukuma ailē. Pamatojoties uz to, mēs aizpildām nepieciešamos iestatījumu laukus.
   
4. Veikto darbību rezultātā tabulā tiks parādītas starpsummas pa grupām (pēc datuma). Pretī katrai grupai ir redzama mīnusa ikona, uz kuras noklikšķinot, tajā esošās rindas tiks sakļautas.
   
5. Ja vēlaties, no redzamības lauka varat noņemt nevajadzīgos datus, atstājot tikai kopējo summu un starpsummas. Noklikšķinot uz pogas “plus”, varat izvērst rindas grupu iekšpusē.
   

Piezīme: Pēc izmaiņu veikšanas un jaunu datu pievienošanas starpsummas tiks automātiski pārrēķinātas.

1. Vispirms atzīmējiet šūnu, kurā jāparāda skaitīšanas kopsumma. Pēc tam noklikšķiniet uz ikonas "Ievietot funkciju" ( fx) blakus formulas joslai tās kreisajā pusē.
   
2. Tiks atvērts funkciju vednis. Atlasiet kategoriju “Pilns alfabētiskais saraksts”, piedāvātajā sarakstā atrodiet funkciju “INTERMEDIATE TOTAL”, novietojiet kursoru uz tās un noklikšķiniet uz Labi.
   
3. Tagad jums ir jāiestata funkciju iestatījumi. Laukā “Funkcijas_numurs” norādām numuru, kas atbilst vēlamajai informācijas apstrādes opcijai. Kopumā ir vienpadsmit iespējas:
   
4. Laukā “Reference 1” norādiet tā diapazona koordinātas, kuram vēlaties aprēķināt kopsummas. Kopumā varat norādīt līdz 255 diapazoniem. Pēc pirmās saites koordinātu ievadīšanas parādīsies rinda, lai pievienotu nākamo. Manuāli ievadīt koordinātas nav īpaši ērti, turklāt pastāv liela kļūda. Tāpēc mēs vienkārši novietojam kursoru informācijas ievadīšanas laukā un pēc tam ar peles kreiso taustiņu atzīmējam vēlamo datu apgabalu. Tāpat, ja nepieciešams, varat pievienot tālāk norādītās saites. Pēc pabeigšanas apstipriniet iestatījumus, nospiežot pogu Labi.
   
5. Rezultātā šūnā ar formulu tiks parādīts starpsummu aprēķināšanas rezultāts.
   

Piezīme: Tāpat kā citas Excel funkcijas, varat izmantot “SUBTOTAL”, neizmantojot funkciju vedni. Lai to izdarītu, manuāli ievadiet formulu vajadzīgajā šūnā, kas izskatās šādi.

Aprēķināsim starpsummas MS EXCEL tabulā. Piemēram, tabulā, kurā ir informācija par vairāku dažādu preču kategoriju pārdošanu, mēs aprēķinām katras kategorijas izmaksas.

Mums ir preču pārdošanas tabula (produkti tiek atkārtoti). Skatīt parauga failu.

Aprēķināsim katra produkta pašizmaksu, izmantojot MS EXCEL Starpsummas (Dati/Struktūra/Starpsummas).

Lai to izdarītu, jums ir nepieciešams:

• pārliecinieties, ka sleju nosaukumos ir virsraksti;
• kolonnas dati Preces, piemēram, izmantojot ;
• atlasot jebkuru tabulas šūnu, zvaniet Starpsummas(izvēlnē Dati/struktūra);

• laukā" Par katru izmaiņu:"izvēlies Produkts;
• laukā" Darbība"izvēlies Summa;
• laukā" Pievienot kopsummas pēc» atzīmējiet izvēles rūtiņu blakus vērtībai Cena;
• Noklikšķiniet uz Labi.

Kā redzams augšējā attēlā, pēc rīka izmantošanas Starpposma rezultāti, MS EXCEL izveidoja trīs datu organizēšanas līmeņus: tabulas kreisajā pusē parādījās struktūras vadīklas. 1. līmenis: kopējā summa (visu preču izmaksas tabulā); 2. līmenis: preču izmaksas katrā kategorijā; 3. līmenis: visas tabulas rindas. Noklikšķinot uz atbilstošām pogām, jūs varat parādīt tabulu vēlamajā detalizācijas līmenī. Zemāk esošie attēli parāda 1. un 2. līmeni.

Kopēt tikai rindas ar starpsummas

Kopēt tikai rindas ar starpsummām citā diapazonā nav tik vienkārši: pat ja tabula ir sagrupēta 2. līmenī (skat. attēlu augstāk), tad atlasot šūnas ar summām (faktiski tiks atlasīts diapazons A4:D92) un kopējot to citā diapazonā, mēs iegūstam visu tabulu. Lai kopētu tikai mūsu izmantotās summas (veidojot struktūru, mēs izmantosim faktu, ka MS EXCEL Starpsummas ievieto kopējo rindas ar pievienoto vārdu Apakšējā līnija vai angļu valodā - Kopā).

• izveidot diapazonā D5:D6 kritēriju padome: in D5 ievieto kolonnas virsrakstu, kurā ir vārdi Total, t.i. vārdu Produkts; V D6 ievadiet *Kopā (tiks atlasītas visas rindas, kuru ailē Produkts satur vērtības, kas beidzas ar vārdu Apakšējā līnija) Zvaigznīte nozīmē *;

• atlasiet jebkuru tabulas šūnu;
• zvanīt ( Dati/ Kārtot un filtrēt/ Papildu);
• laukā Nosacījumu diapazons ievadiet D5:D6 ;
• iestatīt opciju Kopējiet rezultātu uz citu vietu;
• laukā Novietojiet rezultātu diapazonā norādiet, piemēram, tukšu šūnu A102 ;

Excel lapas, kurās ir liels informācijas apjoms, dažkārt var izskatīties pārblīvētas un pat grūti salasāmas. Programmā Excel varat grupēt datus, lai jūs varētu viegli paslēpt un parādīt dažādas darblapas sadaļas. Turklāt, izmantojot komandu, varat vispārināt dažādas grupas Starpsumma un piešķiriet Excel darblapai struktūru. Šajā nodarbībā mēs aplūkosim abus šos rīkus pēc iespējas detalizētāk un ar skaidriem piemēriem.

Rindu un kolonnu grupēšana programmā Excel

Lai atgrupētu datus programmā Excel, atlasiet grupētās rindas vai kolonnas un pēc tam noklikšķiniet uz komandas Atgrupēt.

Kā paslēpt un parādīt grupas

Apkopojot programmā Excel

Komanda Starpsummaļauj automātiski izveidot grupas un izmantot tādas pamatfunkcijas kā SUM, COUNT un AVERAGE, lai atvieglotu apkopošanu. Piemēram, komanda Starpsumma prot aprēķināt kancelejas preču izmaksas pa grupām lielā pasūtījumā. Komanda izveidos grupu hierarhiju, ko sauc arī par kontūru, lai sakārtotu informāciju darblapā.

Pirms komandas izmantošanas jūsu dati ir pareizi jāsakārto Starpsumma Lai iegūtu plašāku informāciju, varat izpētīt nodarbību sēriju Datu kārtošana programmā Excel.

Starpsummas izveidošana

Nākamajā piemērā mēs izmantosim komandu Starpsumma lai noteiktu, cik katra izmēra T-krekli (S, M, L un XL) tiek pasūtīti. Rezultātā Excel darblapa tiks strukturēta grupās katram T-krekla izmēram, un pēc tam tiks aprēķināts kopējais T-kreklu skaits katrā grupā.

Skatīt grupas pēc līmeņa

Apkopojot starpsummas programmā Excel, darblapa tiek sadalīta dažādos līmeņos. Varat pārslēgties starp šiem līmeņiem, lai varētu pielāgot attēlotās informācijas apjomu, izmantojot kontūras ikonas 1, 2, 3 lapas kreisajā pusē. Nākamajā piemērā mēs pārslēgsimies starp visiem trīs struktūras līmeņiem.

Lai gan šajā piemērā ir parādīti tikai trīs līmeņi, programma Excel ļauj izveidot līdz pat 8 ligzdošanas līmeņiem.

Varat arī izmantot ikonas Rādīt vai Slēpt detaļas lai paslēptu vai parādītu grupas.

Starpsummu noņemšana programmā Excel

Laika gaitā izzūd nepieciešamība pēc starpsummas, it īpaši, ja Excel lapas dati ir jāpārkārto citādi. Ja vairs nevēlaties redzēt starpsummas, varat tās izdzēst.

Lai dzēstu tikai grupas, atstājot starpsummas, izmantojiet vienumu Dzēst struktūru no komandu nolaižamās izvēlnes Atgrupēt.

Programmas Excel starpsummas funkcija ir vienkāršs un ērts veids, kā analizēt datus noteiktā kolonnā vai tabulā.

Starpaprēķini samazina aprēķina laiku un ļauj ātri apskatīt datus par preču apjomu, daudzumu, kopējiem ienākumiem utt.

Starp citu, lai efektīvāk strādātu ar tabulām, varat iepazīties ar mūsu materiālu Excel karstie taustiņi — vissvarīgākās iespējas.

Lai atvieglotu lietotāju dzīvi, programmas izstrādātāji izveidoja funkcijas datu grupēšanai.

Ar to palīdzību jūs varat ērti meklēt sev nepieciešamos laukus un kolonnas, kā arī paslēpt informāciju, kas šobrīd nav nepieciešama.

Izmantojot funkciju, varat apkopot vairākas lapu grupas "Starprezultāti".

Sīkāk apskatīsim šīs funkcijas darbības principu un tās realizāciju. visās tabulas procesora versijās Excel.

Funkciju sintakse

Programmā Excel opcija tiek parādīta kāSTARPRESULTĀTI (Nr.; saite 1; saite 2; saite 3;…; saite N ) , kur skaitlis ir funkcijas apzīmējums, saite ir kolonna, ar kuru tiek apkopots rezultāts.

Saites laukos var iekļaut ne vairāk kā 29 saites un atstarpes, ko izmanto starpsummu izpildē.

Skaitlis var būt jebkurš skaitlis no 1 līdz 11.Šis skaitlis parāda programmai, ko funkciju jāizmanto, lai uzskaitītu kopsummas atlasītajā sarakstā.

Kopsummas opcijas sintakse tabulā norādīts:

Starpsummas ieviešanas piemērs

Darbojas starpsummas ietver šādas darbības:

• Datu grupēšana;
• Rezultātu radīšana;
• Līmeņu izveide grupām.

Grupēšanas tabulas

Izpildiet norādījumus, lai grupētu rindas. Mūsu gadījumā mēs saistām kolonnas A, B, C:

1 Izmantojot peli, atlasiet kolonnas A, B, C, kā parādīts zemāk esošajā attēlā.

2 Tagad, saglabājot atlasi, Programmas rīkjoslā atveriet lauku “Dati”. Pēc tam opciju loga labajā pusē atrodiet ikonu "Struktūra" un noklikšķiniet uz tā;

3 Nolaižamajā sarakstā noklikšķiniet uz "Grupa". Ja vajadzīgās tabulas kolonnu atlases posmā pieļāvāt kļūdu, noklikšķiniet uz "Atgrupēt" un atkārtojiet darbību vēlreiz.

4 Jaunā logā izvēlieties "Strings" un noklikšķiniet uz "Labi";

5 Tagad atkal atlasiet kolonnas A, B, C un vadīt grupu, bet jau pa kolonnām:

Starpsummu izveidošana

Starpsummas opcija, pateicoties grupu un standarta vienpadsmit funkciju izmantošanai (1. tabula), ļauj ātri apkopot aizpildītās tabulas datus.

Apskatīsim šīs funkcijas praktisko izmantošanu.

Darbības rezultāts būs grupu parādīšanās, kas ir sakārtotas pēc katra vienuma lieluma. Izpildiet norādījumus:

PAR noteikt, kuriem datiem jums jāveic galīgie aprēķini. Mūsu gadījumā šī ir kolonna "Izmērs". Tās saturs ir jāsakārto. Šķirošana tiek veikta no lielākā elementa līdz mazākajam. Atlasiet kolonnu “Izmērs”.;

Atrodiet lauku "Šķirošana" un noklikšķiniet uz tā;

Parādītajā logā atlasiet “Kārtot pēc norādīto vērtību diapazona” un nospiediet taustiņu "Labi".

• Rezultātā visi izmēri tiks parādīti dilstošā secībā. Tikai pēc tam mēs varam veikt starprezultātus, kas ietekmē pasūtīto preču izmēru diagrammas datus.

Tagad ejam tālāk lai ieviestu funkciju “Starpsumma”:

• Programmas rīkjoslā atveriet lauku “Dati”;
• Atlasiet flīzi "Struktūra";
• Noklikšķiniet uz "Starprezultāti";

Pēc tam tiks atvērts logs starpsummu iestatīšanai un parādīšanai. Pirmajā laukā atlasiet tās kolonnas nosaukumu, par kuru tiks veikts kopsavilkums. Mūsu gadījumā tas ir "Izmērs". Ar katru izmaiņu šajā laukā datu kopsummas mainīsies;

Laukā "Darbība". izvēlieties funkcijas veidu, kas attiecas uz visiem pirmajā laukā atlasītās kolonnas elementiem. Lai aprēķinātu izmērus, jāizmanto daudzuma skaitīšana;

Čīkstēšanā "Pievienot kopsummas pēc" jūs atlasāt kolonnu, kurā tiks parādītas starpsummas. Izvēlieties vienumu "Izmērs";

Pēc visu parametru atkārtotas iestatīšanas vēlreiz pārbaudiet ievadītās vērtības un noklikšķiniet uz Labi.

Rezultātā tabula tiks parādīta lapā, sagrupēta pēc izmēra. Tagad ir ļoti viegli redzēt visas pasūtītās preces izmērā Mazs, īpaši liels un tā tālāk.

Kā redzams attēlā, visas kopsummas tiek parādītas starp parādītajām grupām jaunā rindā.

Apakšējā līnija drēbēm ar izmēru Mazs – 5 gab., apģērbam ar izmēruĪpaši liels – 2 gab. Kopējais elementu skaits ir parādīts arī zem tabulas.

Grupu līmeņi

Varat arī skatīt grupas pēc līmeņa.

Šī attēlošanas metode ļauj samazināt vizuālās informācijas daudzumu uz ekrāna - redzēsiet tikai nepieciešamos kopējos datus - katra izmēra apģērbu skaitu un kopējo daudzumu.

Grupu vadības panelī noklikšķiniet uz pirmā otrā vai trešā līmeņa. Izvēlieties piemērotāko informācijas prezentācijas opciju:

Kopējo summu dzēšana

Pēc kāda laika tabulā tiks ievadīti citi dati, tāpēc informācija būs jāpārgrupē.

Vecās starpsummas un to aprēķina veids vairs nebūs aktuālas, tāpēc tās ir jāsvītro.

Lai izdzēstu kopējo summu, atlasiet cilni "Dati" - "Struktūra" - "Starprezultāti".

Atvērtajā logā noklikšķiniet uz pogas “Noņemt visu” un apstipriniet darbību ar pogu “OK”.

13. att. - datu dzēšana

Strādājot programmā Excel, lietotājs var saskarties ar nepieciešamību apkopot starprezultātu papildus parastajam vispārīgajam.

Rakstā tiks aplūkota mēneša preču pārdošanas tabula, jo visbiežāk šī funkcija tiek izmantota uzrādītajai darbībai. Tas izskatīsies kā trīs kolonnas: produkta nosaukums, datums un ieņēmumu summa. Izmantojot starpsummas programmā Excel, ir iespējams aprēķināt konkrēta produkta ikdienas ienākumus. Turklāt beigās varat summēt preču pārdošanas apjomu. Tagad mēs sīkāk aplūkosim, kā programmā Excel tiek veidotas starpsummas.

Funkcijas izmantošanas nosacījumi

Tūlīt ir vērts precizēt, ka, lai izmantotu parādīto darbību, tabulai jāatbilst prasībām. Ir jāievēro trīs punkti:

• tabulas galvenei jāatrodas lapas pirmajā rindā;
• Šūnas apgabalam jābūt formatētam pēc noklusējuma;
• tabula ir jāaizpilda ar datiem.

Ja atbilstat visām šīm prasībām, varat viegli izdarīt starprezultātu. Tagad sīkāk aplūkosim pašu procesu.

Starpsummas izveidošana. Izmantojot īpašu instrumentu

Kā programmā Excel izveidot starpsummu? Pirmā metode tiks demonstrēta, izmantojot standarta rīku ar tādu pašu nosaukumu programmā. Tātad sāksim.

1. darbība. Atveriet rīku

Vispirms jums ir jāatver pats rīks "Starpsumma". Tas atrodas rīkjoslā cilnē "Dati" (rīku grupā "Struktūra"). Pirms tā atvēršanas jums būs jāizvēlas šūna, kurā atrodas viens no produkta nosaukumiem šajā piemērā, atlasiet “Kartupeļi”.

2. darbība. Displeja iestatījumi

Pēc noklikšķināšanas uz rīka tiks atvērts logs, kurā jāiestata parametri informācijas parādīšanai programmā. Šajā gadījumā jums ir jānoskaidro ieņēmumu summa konkrētai dienai, tāpēc sadaļā “Katras izmaiņas” ir jāizvēlas “Datums”.

Tā kā mēs aprēķināsim summu, nolaižamajā sarakstā “Darbība” ir jāizvēlas vērtība “Summa”. Ir arī vērts atzīmēt, ka šajā posmā jūs varat izvēlēties citus mainīgos lielumus atkarībā no jūsu vajadzībām. Starp ierosinātajiem ir:

• minimums;
• daudzums;
• maksimums;
• strādāt.

Atliek tikai noteikt, kur rezultāts tiks izvadīts. Lai to izdarītu, laukā "Pievienot kopsummu pēc" ir jāatzīmē izvēles rūtiņa blakus kolonnas nosaukumam, kurā tiks parādīts rezultāts. Tā kā mēs aprēķinām summu, mēs atlasām “Ieņēmumu summa, berzēt”.

Šajā logā ir arī vairāki citi mainīgie: "Aizstāt esošās kopsummas", "Lapas beigas starp grupām" un "Kopā zem datiem". No svarīgākajiem varat atzīmēt tikai pirmo punktu, bet pārējo iestatīt pēc vēlēšanās.

3. darbība: nobeigums

Pēc visu iestatījumu veikšanas viss, kas jums jādara, ir jānoklikšķina uz pogas "OK". Tagad programmā varat novērot starpsummas pēc datuma. Ir vērts atzīmēt, ka grupas var sakļaut un paplašināt, izmantojot mīnusa zīmi loga kreisajā pusē. Kopējais rezultāts tiks apkopots pašā apakšā.

Starpsummas izveidošana. Izmantojot īpašu formulu

Papildus iepriekš aprakstītajai metodei, kā vēl jūs varat aprēķināt starpsummas programmā Excel? Formula ir tā, kas mums palīdzēs.

Tie, kas bieži strādā ar programmu, zina, ka formulu ievadīšanai ir vieglāk izmantot īpašo funkciju “Funkciju vednis”. Tieši pie viņa mēs vēršamies.

1. darbība. Atveriet funkciju vedni

Pirms nepieciešamo kopsummu ievadīšanas jums jāizvēlas šūna, kurā tie tiks parādīti.

Vienkārši atlasiet jebkuru šūnu, kurā nav ievadīti nekādi dati. Tālāk jums jānoklikšķina uz ikonas “Ievietot funkciju”, kas atrodas blakus ievades līnijai. Precīzu atrašanās vietu varat redzēt zemāk esošajā attēlā.

Tiks atvērts funkciju vedņa logs, kurā laukā “Select a function” ir jāizvēlas “SUBTOTAL” un jānoklikšķina uz “OK”. Lai atvieglotu meklēšanu, varat kārtot alfabētiskā secībā, nolaižamajā sarakstā “Kategorija” atlasot “Pilns alfabētiskais saraksts”.

2. darbība. Funkcijas argumentu ievadīšana

Visu iepriekš minēto darbību rezultātā tiks atvērts logs, kurā jānorāda visi nepieciešamie argumenti. Apskatīsim visu kārtībā. Rindā "Funkcijas numurs" jānorāda numurs no saraksta, kur:

Mūsu gadījumā mums ir jāaprēķina summa, tāpēc mums jāievada skaitlis “9”.

Ievades laukā "Saite 1" ir jāizvēlas šūnu apgabals ar datiem, kas tiks apstrādāti, izmantojot funkciju. Lai to izdarītu, varat izmantot divus veidus: ievadiet visu pats vai norādiet, izmantojot kursoru. Mēs izmantosim otro iespēju, jo tā ir vienkāršāka. Lai to izdarītu, noklikšķiniet uz pogas labajā pusē un atlasiet vajadzīgo apgabalu un visbeidzot vēlreiz noklikšķiniet uz šīs pogas.

Atkal parādīsies pazīstamais logs, kurā varat norādīt otro saiti (kopā ir četras). Ja jums tas nav jādara, noklikšķiniet uz pogas “OK”, un rezultāti tiks parādīti tabulā.