Ja vēlaties iegūt kolonnas summu, vienkārši izmantojot kolonnas nosaukumu, programmā Excel to varat izdarīt trīs vienkāršos veidos. Izpētīsim šos veidus.
Atšķirībā no citiem rakstiem, vispirms apskatīsim scenāriju.
Šeit man ir pārdošanas tabula, ko dažādi pārdevēji veikuši dažādos mēnešos.
Tagad uzdevums ir iegūt konkrētā mēneša pārdošanas summu šūnā C10. Ja mēs mainām mēnesi B10, summai vajadzētu mainīties un atgriezt šī mēneša summu, neko nemainot formulā.
1. metode: tabulas visu kolonnu summa, izmantojot funkciju SUMPRODUCT.
Metodes SUMPRODUCT sintakse summas atbilstības kolonnai ir šāda:
| = SUMPRODUCT ((kolonnas)*(galvenes = virsraksts)) |
Kolonnas:Tas ir kolonnu divdimensiju diapazons, kuru vēlaties summēt. Tajā nedrīkst būt galvenes. Augšējā tabulā tas ir C3: N7.
Galvenes:Tas ir galvenes diapazons kolonnas ko vēlaties summēt. Iepriekš minētajos datos tas ir C2: N2.
Virsraksts: Tas ir virsraksts, kuru vēlaties saskaņot. Iepriekš minētajā piemērā tas ir B10.
Bez turpmākas kavēšanās izmantosim formulu.
| = SUMPRODUCT ((C3: N7)*(C2: N2 = B10)) |
un tas atgriezīsies:
Kā tas darbojas?
Tas ir vienkārši. Formulā paziņojumsC2: N2 = B10 atgriež masīvu, kurā ir visas FALSE vērtības, izņemot vienu, kas atbilst B10. Tagad formula ir
| =SUMPRODUCT ((C3: N7)*{FALSE, FALSE, FALSE, FALSE,PATIESA, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}) |
Tagad C3: N7 tiek reizināts ar katru šī masīva vērtību. Katra kolonna kļūst par nulli, izņemot kolonnu, kas reizināta ar TRUE. Tagad formula kļūst šāda:
| = SUMPRODUCT ({0,0,0,0,6,0,0,0,0,0,0,0; 0,0,0,0,6,12,0,0,0,0,0,0,0, 0; 0,0,0,0,15,0,0,0,0,0,0,0; 0,0,0,0,15,0,0,0,0,0,0,0,0; 0,0,0,0,8,0,0,0,0,0,0,0,0}) |
Tagad šis masīvs ir apkopots, un mēs iegūstam kolonnas summu, kas atbilst kolonnai šūnā B10.
2. metode: Summējiet visu kolonnu tabulā, izmantojot funkciju INDEX-MATCH.
Metodes sintakse, lai summētu atbilstošās kolonnas virsrakstu programmā Excel, ir šāda:
| = SUM (INDEX (kolonnas ,, MATCH (virsraksts, galvenes, 0))) |
Visi šīs metodes mainīgie ir tādi paši kā SUMPRODUCT metode. Vienkārši īstenosim to, lai atrisinātu problēmu. Uzrakstiet šo formulu C10.
| = SUMMA (INDEKSS (C3: N7,, MATCH (B10, C2: N2,0))) |
Tas atgriež:
Kā tas darbojas?
Formula ir atrisināta no iekšpuses. Pirmkārt, funkcija MATCH atgriež atbilstošā mēneša indeksu no diapazona C2: N2. Tā kā B1o ir maijs, mēs iegūstam 5. Tagad formula kļūst
| = SUMMA (INDEKSS (C3: N7,, 5)) |
Tālāk funkcija INDEX atgriež vērtības no C3: N7 piektās kolonnas. Tagad formula kļūst šāda:
| = SUM ({6; 12; 15; 15; 8}) |
Un visbeidzot, mēs iegūstam šo vērtību summu.
3. metode. Summējiet visu kolonnu tabulā, izmantojot nosaukumu diapazonu un funkciju NETIEK
Viss kļūst vienkāršs, ja savus diapazonus nosaucat par kolonnu virsrakstiem. Šajā metodē kolonnas vispirms ir jānosauc par virsrakstu nosaukumiem.
Atlasiet tabulu ar virsrakstiem un nospiediet CTRL+SHIFT+F3. Tiks atvērts dialoglodziņš, lai no diapazoniem izveidotu nosaukumu. Pārbaudiet augšējo rindu un nospiediet pogu Labi.
Visas datu slejas tiks nosauktas par to virsrakstiem.
Tagad vispārīgā formula atbilstošās slejas summēšanai būs šāda:
| = SUM (NETIEŠA (virsraksts)) |
Virsraksts: Tas ir tās kolonnas nosaukums, kuru vēlaties summēt. Šajā piemērā šobrīd B10 satur maiju.
Lai ieviestu šo vispārīgo formulu, ierakstiet šo formulu šūnā C10.
| = SUMMA (NETIEŠA (B10)) |
Tas atgriež maija mēneša summu:
Cita metode ir līdzīga šai. Šajā metodē mēs izmantojam Excel tabulas un strukturētu nosaukumu. Pieņemsim, ka iepriekš minēto tabulu esat nosaucis par tabulu1. Tad šī formula darbosies tāpat kā iepriekš minētā formula.
| = SUMMA (NETIEŠA ("1. tabula [" & B10 & "]")) |
Kā tas darbojas?
Šajā formulā funkcija INDIRECT ņem atsauci uz nosaukumu un pārvērš to par faktisko nosaukuma atsauci. Turpmākā procedūra ir vienkārša. Funkcija SUM apkopo nosaukto diapazonu.
Tātad, jā, puiši, šādā veidā varat apkopot atbilstošo kolonnu programmā Excel. Es ceru, ka tas jums ir noderīgs un izskaidrojošs. Ja jums ir šaubas par šo rakstu vai kādu citu ar excel/VBA saistītu tēmu, jautājiet komentāru sadaļā zemāk.
Kā saskaitīt, saskaņojot rindu un kolonnu programmā Excel |SUMPRODUCT ir vispusīgākā funkcija, kad runa ir par summēšanu un skaitīšanu ar sarežģītiem kritērijiem. Vispārējā funkcija, kas jāsummē, saskaņojot kolonnu un rindu, ir…
SUMIF ar 3D atsauci programmā Excel |Jautri ir tas, ka parastā Excel 3D atsauce nedarbojas ar nosacījuma funkcijām, piemēram, SUMIF funkciju. Šajā rakstā mēs uzzināsim, kā panākt, lai 3D atsauces darbotos ar SUMIF funkciju.
Relatīvā un absolūtā atsauce programmā Excel | Atsauces programmā Excel ir svarīga tēma ikvienam iesācējam. Pat pieredzējuši Excel lietotāji atsaucoties pieļauj kļūdas.
Atsauce uz dinamisko darblapu | Dodiet atsauces lapas dinamiski, izmantojot Excel INDIRECT funkciju. Tas ir vienkārši…
Atsauču paplašināšana programmā Excel | Izvēršamā atsauce izvēršas, ja to nokopē uz leju vai pa labi. Mēs izmantojam $ zīmi pirms kolonnas un rindas numura. Šeit ir viens piemērs…
Viss par absolūtu atsauci | Noklusējuma atsauces veids programmā Excel ir relatīvs, bet, ja vēlaties, lai šūnu un diapazonu atsauce būtu absolūta, izmantojiet $ zīmi. Šeit ir visi absolūtās atsauces aspekti programmā Excel.
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti | Ātrāk izpildiet savu uzdevumu. Šie 50 saīsnes padarīs jūsu darbu vēl ātrāku programmā Excel.
Funkcija VLOOKUP programmā Excel | Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
COUNTIF programmā Excel 2016 | Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai uzskaitītu konkrētu vērtību. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot SUMIF funkciju programmā Excel | Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.