Siiani oleme õppinud Exceli tabeli kogu vastava veeru summeerimist. Kuidas aga summeerida väärtusi, kui peame veeru ja rea sobitama. Selles artiklis õpime, kuidas teha ridade ja veergude sobitamine.
Selleks on kaks valemit, kuid kõigepealt vaatame stsenaariumi.
Siin on mul tabel, mis kajastab töötajate müüki erinevatel kuudel. Töötajate nimi võib korduda. Vaadake allolevat joonist:
Peame saama maikuu summa, kus müügimees on Donald.
Meetod: sobivate veerupäiste ja ridade päiste kokkuvõtmine Funktsiooni SUMPRODUCT kasutamine.
The Funktsioon SUMPRODUCT on kõige mitmekülgsem funktsioon keerukate kriteeriumidega väärtuste summeerimisel ja loendamisel. Üldine funktsioon veeru ja rea sobitamise järgi on järgmine:
| = SUMPRODUCT ((veerud)*(column_headers = column_heading)*(row_headers = row_heading) |
Veerud:See on veergude kahemõõtmeline vahemik, mille soovite liita. See ei tohiks sisaldada päiseid. Ülaltoodud tabelis on see C3: N7.
veerupealkirjad:See on päise vahemikveerud mida soovite kokku võtta. Ülaltoodud andmetes on see C2: N2.
veeru_pealkiri: See on pealkiri, millele soovite sobitada. Ülaltoodud näites on see B13 -s.
Kasutame ilma edasise viivituseta valemit.
rea_pealkirjad:See on päise vahemikridu mida soovite kokku võtta. Ülaltoodud andmetes on see B3: B10.
rea_pealkiri: See on pealkiri, mille soovite ridadesse sobitada. Ülaltoodud näites on see F13 -s.
Kasutame ilma edasise viivituseta valemit.
Kirjutage see valem lahtrisse D13 ja laske Excelil oma võlu teha (maagiat pole olemas) …
| = SUMPRODUCT ((C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)) |
See tagastab väärtuse:
Nüüd, kui muudate kuud või müügimeest, muutub summa vastavalt rea ja veeru pealkirjale.
Kuidas see töötab?
See lihtne loogiline loogika.
(C2: N2 = B13): See avaldus tagastab massiivi tõde ja vale. Kõik vastavad väärtused veerus on tõesed ja teised väär. Sel juhul on meil ainult üks tõene, kuna vahemik C2: N2 sisaldab ainult ühte eksemplari mai kell 5th Asukoht.
(B3: B10 = E13): See toimib samamoodi nagu eespool ja tagastab massiivi TRUE ja FALSE. Kõigil sobivatel väärtustel on tõene ja teistel vale. Sel juhul saame vahemikus B3 2 TÕELIST: B10 -l on kaks "Donaldi" eksemplari.
(C2: N2 = B13)*(B3: B10 = E13): Nüüd korrutame lausetega tagastatud massiivid. See rakendab ja loogika ning saame massiivi 1 ja 0. Nüüd on meil 2D massiiv, mis sisaldab 2 1 ja ülejäänud 0.
(C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)= Lõpuks korrutame 2D massiivi 2D tabeliga. See tagastab uuesti massiivi 0 ja kriteeriumidele vastavad numbrid.
Lõpuks, SUMPRODUCT funktsioon võtab massiivi kokku, mille tulemuseks on soovitud väljund.
2. meetod: sobiva veerupäise ja rea päise kokkuvõtmine Funktsiooni SUM ja IF kasutamine
Üldine valem sobiva rea ja veeru summeerimiseks, kasutades funktsiooni SUM ja IF Excel, on järgmine:
| = SUM (IF (veerupealkirjad = veerupealkiri, IF (rea_pealkirjad = rea_pealkiri, veerud))) |
Kõik muutujad on samad, mis ülalkirjeldatud meetodil. Siin tuleb neid lihtsalt kasutada teises järjekorras.
Kirjutage see valem lahtrisse D13:
| = SUM (IF (C2: N2 = B13, IF (B3: B10 = E13, C3: N10))) |
See tagastab õige vastuse. Vaadake allolevat ekraanipilti:
Kuidas see töötab?
Loogika on sama, mis esimesel SUMPRODCUT meetodil, ainult mehhanism on erinev. Kui ma seda lühidalt selgitan, tagastab sisemine IF -funktsioon tabeliga sama mõõtmega 2D -massiivi. See massiiv sisaldab kahe sobitatud rea arvu. Seejärel vastab sisemine IF-funktsioon selle massiivi kaheveerulistele pealkirjadele ja tagastab 2D-massiivi, mis sisaldab ainult numbreid, mis vastavad nii veerule kui ka pealkirjale. Kõik muud massiivi elemendid on VÄÄRAD.
Lõpuks võtab funktsioon SUM selle massiivi kokku ja saame oma summa.
Nii et jah, poisid, saate Excelis tabelist sobivad read ja veerud kokku võtta. Loodan, et see oli teile selgitav ja kasulik. Kui teil on selle teema kohta kahtlusi või teil on muid exceli/VBA -ga seotud kahtlusi, küsige allpool kommentaaride jaotises.
Kuidas Excelis veergu kokku liita, sobitades pealkirja | Kui soovite saada veeru summa pelgalt veeru nime kasutades, saate seda Excelis teha kolmel lihtsal viisil. SUMPRODUCT -meetodi süntaks summeerimise veergu on järgmine:
SUMIF koos 3D -viitega Excelis |Naljakas fakt on see, et tavaline Excel 3D viitamine ei tööta tingimuslike funktsioonidega, nagu SUMIF -funktsioon. Selles artiklis õpime, kuidas 3D -viiteid SUMIF -funktsiooniga tööle panna.
Suhteline ja absoluutne viide Excelis | Viitamine Excelis on iga algaja jaoks oluline teema. Isegi kogenud exceli kasutajad teevad viitamisel vigu.
Dünaamilise töölehe viide | Andke võrdluslehed dünaamiliselt, kasutades Exceli funktsiooni INDIRECT. See on lihtne…
Viidete laiendamine Excelis | Laienev viide laieneb alla või paremale kopeerimisel. Selleks kasutame veeru ja rea numbri ees olevat märki $. Siin on üks näide…
Kõik absoluutse viite kohta | Vaikeviide Excelis on suhteline, kuid kui soovite, et lahtrite ja vahemike viide oleks absoluutne, kasutage märki $. Siin on kõik Exceli absoluutse viitamise aspektid.
Populaarsed artiklid:
50 Exceli otseteed tootlikkuse suurendamiseks | Saa oma ülesandega kiiremini hakkama. Need 50 otseteed muudavad teie töö Excelis veelgi kiiremaks.
Funktsioon VLOOKUP Excelis | See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
COUNTIF Excelis 2016 | Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea konkreetse väärtuse loendamiseks oma andmeid filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.
Funktsiooni SUMIF kasutamine Excelis | See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.