Kā apkopot kolonnu programmā Excel (5 patiešām vienkārši veidi)

Lai iegūtu visu kolonnu, lielākā daļa no mums ir jādara diezgan bieži. Piemēram, ja jums ir pārdošanas dati līdz šim, iespējams, vēlēsities ātri uzzināt kopējo summu kolonnā līdz pārdošanas vērtībai līdz mūsdienām.

Iespējams, vēlēsities ātri redzēt, kāda ir kopējā summa, vai arī kā formulu atsevišķā šūnā.

Šī Excel apmācība aptvers pāris ātras un ātras metodes kolonnas summēšanai programmā Excel.

Statusa joslā atlasiet un iegūstiet kolonnas SUM

Programmai Excel ir statusa josla (Excel ekrāna apakšējā labajā stūrī), kas parāda noderīgu statistiku par atlasītajiem datiem, piemēram, vidējo, skaitu un SUM.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties ātri uzzināt konkrēto nedēļu pārdošanas summu.

Lai to izdarītu, atlasiet visu kolonnu B (to varat izdarīt, kolonnas augšdaļā noklikšķinot uz B alfabēta).

Tiklīdz atlasīsit visu kolonnu, pamanīsit, ka statusa joslā tiek parādīts kolonnas SUM.

Tas ir patiešām ātrs un vienkāršs veids, kā iegūt visas kolonnas summu.

Labā lieta, izmantojot statusa joslu, lai iegūtu kolonnas summu, ir tā, ka tā ignorē šūnas, kurās ir teksts, un ņem vērā tikai skaitļus. Mūsu piemērā šūnai B1 ir teksta nosaukums, kas tiek ignorēts, un kolonnas summa tiek parādīta statusa joslā.

Ja vēlaties iegūt vairāku kolonnu summu, varat veikt atlasi, un tā parādīs visu atlasīto kolonnu kopējo summu.

Ja nevēlaties atlasīt visu kolonnu, varat atlasīt diapazonu, un statusa joslā tiks parādīta tikai atlasīto šūnu summa.

Viens no statusa joslas izmantošanas trūkumiem summas iegūšanai ir tas, ka šo vērtību nevar kopēt.

Ja vēlaties, varat pielāgot statusa joslu un iegūt vairāk datu, piemēram, maksimālo vai minimālo vērtību no atlases. Lai to izdarītu, ar peles labo pogu noklikšķiniet uz statusa joslas un veiciet pielāgojumus.

Iegūstiet kolonnas kopsummu, izmantojot automātisko summu (ar vienu klikšķi/saīsni)

Autosum ir patiešām lielisks rīks, kas ļauj ar vienu klikšķi ātri iegūt visas kolonnas summu.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties iegūt B kolonnas vērtību summu.

Tālāk ir norādītas darbības, lai iegūtu kolonnas summu:

1. Atlasiet šūnu tieši zem pēdējās šūnas kolonnā, kurai vēlaties summu
2. Noklikšķiniet uz cilnes Formula
3. Grupā Funkciju bibliotēka noklikšķiniet uz opcijas Automātiskā summa

Iepriekš minētās darbības uzreiz parādīs visas atlasītās šūnas kolonnas summu.

Varat arī izmantot automātisko summu, atlasot kolonnu ar vērtību un cilnē Formula noklikšķinot uz automātiskās summas opcijas. Tiklīdz jūs to izdarīsit, tas parādīs automātisko summu šūnā zem atlases.

Piezīme: Autosum automātiski nosaka diapazonu un iekļauj visas šūnas SUM formulā. Ja izvēlaties šūnu, kurā ir summa, un aplūkojat tajā esošo formulu, pamanīsit, ka tā attiecas uz visām kolonnā esošajām šūnām.

Viens neliels kairinātājs, lietojot Autosum, ir tas, ka tas nenosaka pareizo diapazonu, ja diapazonā ir tukšas šūnas vai jebkurai šūnai ir teksta vērtība. Tukšas šūnas (vai teksta vērtības) gadījumā automātiskās summas diapazons sākas zem šīs šūnas.

Pro padoms: Varat izmantot arī funkciju Automātiskā summa, lai iegūtu kolonnu un rindu summu. Ja jūsu dati ir rindā, vienkārši atlasiet šūnu pēc datiem (tajā pašā rindā) un noklikšķiniet uz taustiņa Autosum.

AutoSum tastatūras saīsne

Lai gan opcija Automātiskā summa cilnē Formula ir pietiekami ātra, jūs varat padarīt SUM iegūšanu vēl ātrāku, izmantojot īsinājumtaustiņu.

Lai izmantotu saīsni, atlasiet šūnu, kurā vēlaties ievietot kolonnas summu, un izmantojiet zemāk esošo saīsni:

ALT = (turiet taustiņu ALT un nospiediet vienādu ar taustiņu)

Funkcijas SUM izmantošana, lai manuāli aprēķinātu summu

Lai gan automātiskās summēšanas opcija ir ātra un efektīva, dažos gadījumos, iespējams, vēlēsities manuāli aprēķināt kolonnu (vai rindu) summu.

Viens no iemesliem to darīt varētu būt tad, ja nevēlaties visas kolonnas, bet tikai dažu kolonnu šūnu summu.

Šādā gadījumā varat izmantot funkciju SUM un manuāli norādīt diapazonu, kuram vēlaties summu.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties B kolonnas vērtību summu:

Tālāk ir norādītas darbības, lai manuāli izmantotu funkciju SUM:

1. Atlasiet šūnu, kurā vēlaties iegūt šūnu/diapazona summu
2. Ievadiet šādu informāciju: = SUM (
3. Atlasiet šūnas, kuras vēlaties summēt. Varat izmantot peli vai bulttaustiņu (ar bulttaustiņiem turiet nospiestu taustiņu Shift un pēc tam izmantojiet bulttaustiņus, lai atlasītu šūnu diapazonu).
4. Nospiediet taustiņu Enter.

Iepriekš minētās darbības parādīs slejā atlasīto šūnu summu.

Funkcijā SUM varat arī izveidot un izmantot nosauktos diapazonus, lai ātri iegūtu summas vērtību. Tas varētu būt noderīgi, ja dati ir izplatīti lielā izklājlapā un vēlaties ātri iegūt kolonnas vai diapazona summu. Vispirms jums ir jāizveido nosaukts diapazons, un pēc tam varat izmantot šo diapazona nosaukumu, lai iegūtu summu.

Zemāk ir piemērs, kur esmu nosaucis diapazonu - Pārdošana. Tālāk sniegtā formula man arī parāda pārdošanas slejas summu:

= SUM (pārdošana)

Ņemiet vērā, ka, izmantojot funkciju SUM, lai iegūtu kolonnas summu, tajā tiks iekļautas arī filtrētās vai slēptās šūnas.

Ja vēlaties, lai, summējot kolonnu, netiktu iekļautas slēptās šūnas, jums jāizmanto funkcija SUBTOTAL vai AGGREGATE (aplūkota vēlāk šajā apmācībā).

Summējiet kolonnā tikai redzamās šūnas

Ja jums ir datu kopa, kurā esat filtrējis šūnas vai slēptas šūnas, jūs nevarat izmantot funkciju SUM.

Tālāk ir sniegts piemērs tam, kas var notikt nepareizi:

Iepriekš minētajā piemērā, saskaitot redzamās šūnas, tiek iegūts rezultāts 2549, savukārt redzamo šūnu summas faktiskais rezultāts būtu 2190.

Iemesls, kāpēc mēs iegūstam nepareizu rezultātu, ir tas, ka funkcija SUM ņem arī filtrētās/slēptās šūnas, aprēķinot kolonnas summu.

Ja vēlaties iegūt tikai redzamo šūnu summu, funkciju SUM nevar izmantot. Šajā gadījumā jums jāizmanto funkcija AGGREGATE vai SUBTOTAL.

Ja izmantojat Excel 2010 vai jaunākas versijas, varat izmantot funkciju AGGREGATE. Tas dara visu, ko dara SUBTOTAL funkcija, un nedaudz vairāk. Ja izmantojat vecākas versijas, varat izmantot funkciju SUBTOTAL, lai iegūtu tikai redzamo šūnu SUM (t.i., tā ignorē filtrētās/slēptās šūnas).

Zemāk ir formula, kuru varat izmantot, lai iegūtu tikai kolonnā redzamo šūnu summu:

= APLIKT (9,3, B2: B6)

Funkcija Apkopot izmanto šādus argumentus:

• funkcija_numurs: Šis ir skaitlis, kas norāda funkciju AGGREGATE aprēķinu, kas jāveic. Šajā piemērā es esmu izmantojis 9, kā es vēlos summu.
• iespējas: Šajā argumentā varat norādīt, ko vēlaties ignorēt, veicot aprēķinu. Šajā piemērā esmu izmantojis 3, kas “ignorē slēptās rindas, kļūdu vērtības, ligzdotās SUBTOTAL un AGGREGATE funkcijas”. Īsāk sakot, aprēķinam tā izmanto tikai redzamās šūnas.
• masīvs: Šis ir šūnu diapazons, kuram vēlaties iegūt vērtību. Mūsu piemērā tas ir B2: B6 (kurā ir arī dažas slēptas/filtrētas rindas)

Ja izmantojat Excel 2007 vai iepriekšējās versijas, varat izmantot šādu Excel formulu:

= KOPSUMMA (9, B2: B6)

Zemāk ir formula, kurā es parādīšu, kā summēt kolonnu, kad ir filtrētas šūnas (izmantojot funkciju SUBTOTAL).

Konvertējiet tabulas datus uz Excel tabulu, lai iegūtu kolonnas summu

Pārvēršot tabulas datus par Excel tabulu, ir ļoti viegli iegūt kolonnu summu.

Es vienmēr iesaku datus pārvērst Excel tabulā, jo tas sniedz daudz priekšrocību. Tā kā jaunie rīki, piemēram, Power Query, Power Pivot un Power BI tik labi darbojas ar tabulām, ir vēl viens iemesls to izmantot.

Lai datus pārvērstu Excel tabulā, veiciet tālāk norādītās darbības.

1. Atlasiet datus, kurus vēlaties pārvērst Excel tabulā
2. Noklikšķiniet uz cilnes Ievietot
3. Noklikšķiniet uz tabulas ikonas
4. Dialoglodziņā Izveidot tabulu pārliecinieties, vai diapazons ir pareizs. Pārbaudiet arī opciju “Manai tabulai ir galvenes”, ja jūsu datos ir galvenes.
5. Noklikšķiniet uz Labi.

Iepriekš minētās darbības jūsu tabulas datus pārvērstu Excel tabulā.

Īsinājumtaustiņš, ko pārvērst tabulā, ir Control + T (turiet nospiestu vadības taustiņu un nospiediet taustiņu T)

Kad esat izveidojis tabulu, varat viegli iegūt visu kolonnu summu.

Tālāk ir norādītas darbības, lai iegūtu Excel tabulas kolonnu summu:

1. Excel tabulā atlasiet jebkuru šūnu
2. Noklikšķiniet uz cilnes Dizains. Šī ir konteksta cilne, kas tiek parādīta tikai tad, kad Excel tabulā atlasāt šūnu.
3. Grupā “Galda stila opcijas” atzīmējiet opciju “Kopējā rinda”

Iepriekš minētās darbības tabulas apakšdaļā uzreiz pievienos kopsummu rindu un sniegs visu kolonnu summu.

Vēl viena lieta, kas jāzina par Excel tabulas izmantošanu, ir tā, ka jūs varat viegli mainīt vērtību no kolonnas SUM uz Vidējais, Skaits, Min/Maks. Utt.

Lai to izdarītu, kopsummu rindās atlasiet šūnu un izmantojiet nolaižamo izvēlni, lai atlasītu vēlamo vērtību.

Iegūstiet kolonnas summu, pamatojoties uz kritērijiem

Visas iepriekš minētās metodes parādīs visas kolonnas summu.

Ja vēlaties iegūt tikai to vērtību summu, kas atbilst kritērijam, to var viegli izdarīt, izmantojot SUMIF vai SUMIFS formulu.

Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties iegūt tikai to vērtību summu, kas ir vairāk nekā 500.

To var viegli izdarīt, izmantojot zemāk esošo formulu:

= SUMIF (B2: B11, "> 500", B2: B11)

Izmantojot SUMIF formulu, varat izmantot skaitlisku nosacījumu, kā arī teksta nosacījumu.

Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties iegūt visu Marka veikto pārdošanas darījumu kopsummu.

Šādā gadījumā jūs varat izmantot kolonnu A kā kritēriju diapazonu un “Atzīmēt” kā kritēriju, un formula sniegs jums visu atzīmes vērtību summu.

Zemāk redzamā formula sniegs rezultātu:

= SUMIF (A2: A11, "Atzīmēt", B2: B10)

Piezīme. Vēl viens veids, kā iegūt kritērijiem atbilstošas ​​slejas summu, varētu būt filtrēt kolonnu, pamatojoties uz kritērijiem, un pēc tam izmantot formulu APGABĀT vai SABTOTAL, lai iegūtu tikai redzamo šūnu summu.