Līdz šim mēs esam iemācījušies apkopot visu tabulas atbilstošo kolonnu programmā Excel. Bet kā mēs summējam vērtības, kad jāsaskaņo kolonna un rinda. Šajā rakstā mēs uzzināsim, kā izveidot atbilstošu rindu un kolonnu summu.
Lai to izdarītu, ir divas formulas, bet vispirms apskatīsim scenāriju.
Šeit man ir tabula, kurā reģistrēti darbinieku veiktie pārdošanas apjomi dažādos mēnešos. Darbinieku vārdus var atkārtot. Skatiet zemāk redzamo attēlu:
Mums jāiegūst maija mēneša summa, kurā pārdevējs ir Donalds.
1. metode: atbilstošās slejas galvenes un rindas galvenes apkopošana, izmantojot funkciju SUMPRODUCT.
The Funkcija 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ē pēc atbilstošas kolonnas un rindas, ir šāda:
| = SUMPRODUCT ((kolonnas)*(column_headers = column_heading)*(row_headers = row_heading) |
Kolonnas:Tas ir kolonnu divdimensiju diapazons, kuru vēlaties summēt. Tajā nedrīkst būt galvenes. Augšējā tabulā tas ir C3: N7.
column_headers:Tas ir galvenes diapazonskolonnas ko vēlaties summēt. Iepriekš minētajos datos tas ir C2: N2.
column_heading: Tas ir virsraksts, kuru vēlaties saskaņot. Iepriekš minētajā piemērā tas ir B13.
Bez turpmākas kavēšanās izmantosim formulu.
row_headers:Tas ir galvenes diapazonsrindas ko vēlaties summēt. Iepriekš minētajos datos tas ir B3: B10.
row_heading: Tas ir virsraksts, kuru vēlaties saskaņot rindās. Iepriekš minētajā piemērā tas ir F13.
Bez turpmākas kavēšanās izmantosim formulu.
Ierakstiet šo formulu šūnā D13 un ļaujiet Excelam paveikt savu maģiju (nav tādas lietas kā maģija)…
| = SUMPRODUCT ((C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)) |
Tas atgriež vērtību:
Tagad, mainot mēnesi vai pārdevēju, summa mainīsies atbilstoši rindas virsrakstam un kolonnas virsrakstam.
Kā tas darbojas?
Šī vienkāršā Būla loģika.
(C2: N2 = B13): Šis paziņojums atgriezīs masīvu TRUE un FALSE. Visām slejā esošajām atbilstošajām vērtībām ir patiesa vērtība, bet citām - nepatiesas. Šajā gadījumā mums būs tikai viens True, jo diapazonā C2: N2 ir tikai viens gadījums maijā 5tūkst Atrašanās vieta.
(B3: B10 = E13): Tas darbosies tāpat kā iepriekš un atgriezīs masīvu TRUE un FALSE. Visām atbilstošajām vērtībām būs TRUE, bet citām - FALSE. Šajā gadījumā mums būs 2 TRUE, jo diapazonā B3: B10 ir divi “Donald” gadījumi.
(C2: N2 = B13)*(B3: B10 = E13): Tagad mēs reizinām ar paziņojumiem atdotos masīvus. Tas ieviesīs un loģiku, un mēs iegūsim 1 un 0 masīvu. Tagad mums būs 2D masīvs, kurā būs 2 1 un pārējie 0.
(C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)= Visbeidzot, mēs reizinām 2D masīvu ar 2D tabulu. Tas atkal atgriezīs masīvu 0 un skaitļus, kas atbilst kritērijiem.
Visbeidzot, SUMPRODUCT funkcija apkopos masīvu, kā rezultātā tiks iegūta vēlamā izvade.
2. metode: atbilstošo sleju galvenes un rindas galvenes apkopošana, izmantojot funkciju SUM un IF
Vispārīgā formula atbilstošās rindas un kolonnas summēšanai, izmantojot funkciju SUM un IF Excel, ir šāda:
| = SUM (IF (column_headers = column_heading, IF (row_headers = row_heading, slejas))) |
Visi mainīgie ir tādi paši kā iepriekš aprakstītajā metodē. Šeit tie vienkārši jāizmanto citā secībā.
Ierakstiet šo formulu šūnā D13:
| = SUM (IF (C2: N2 = B13, IF (B3: B10 = E13, C3: N10))) |
Tas atgriež pareizo atbildi. Skatiet zemāk redzamo ekrānuzņēmumu:
Kā tas darbojas?
Loģika ir tāda pati kā pirmajai SUMPRODCUT metodei, tikai mehānisms ir atšķirīgs. Ja es to izskaidrošu īsi, iekšējā IF funkcija atgriež 2D masīvu ar tādu pašu izmēru kā tabula. Šajā masīvā ir divu saskaņotu rindu skaits. Tad iekšējā IF funkcija atbilst divu kolonnu virsrakstiem šajā masīvā un atgriež 2D masīvu, kurā ir tikai skaitļi, kas atbilst gan kolonnai, gan virsrakstam. Visi pārējie masīva elementi būs FALSE.
Visbeidzot, funkcija SUM apkopo šo masīvu, un mēs iegūstam savu summu.
Tātad, jā, puiši, šādā veidā varat apkopot atbilstošās rindas un kolonnas no tabulas programmā Excel. Es ceru, ka tas jums bija izskaidrojošs un noderīgs. Ja jums ir šaubas par šo tēmu vai jums ir citas ar Excel/VBA saistītas šaubas, jautājiet komentāru sadaļā zemāk.
Kā apkopot kolonnu Excel, saskaņojot virsrakstu | 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. Metodes SUMPRODUCT sintakse summas atbilstības kolonnai ir šāda:
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.