Kui soovite saada veeru summa pelgalt veeru nime kasutades, saate seda Excelis teha kolmel lihtsal viisil. Uurime neid viise.
Erinevalt teistest artiklitest vaatame kõigepealt stsenaariumi.
Siin on mul müügitabel, mille on teinud erinevad müügimehed erinevatel kuudel.
Nüüd on ülesandeks Cell C10 -s saada antud kuu müügi summa. Kui muudame B10 kuud, peaks summa muutuma ja tagastab selle kuu summa, muutmata valemis midagi.
1. meetod: kogu veeru summa tabelis, kasutades funktsiooni SUMPRODUCT.
SUMPRODUCT -meetodi süntaks summeerimise veergu on järgmine:
| = SUMPRODUCT ((veerud)*(päised = pealkiri)) |
Veerud:See on veergude kahemõõtmeline vahemik, mille soovite liita. See ei tohiks sisaldada päiseid. Ülaltoodud tabelis on see C3: N7.
Päised:See on päise vahemik veerud mida soovite kokku võtta. Ülaltoodud andmetes on see C2: N2.
Rubriik: See on pealkiri, millele soovite sobitada. Ülaltoodud näites on see B10 -s.
Kasutame ilma edasise viivituseta valemit.
| = SUMPRODUCT ((C3: N7)*(C2: N2 = B10)) |
ja see tuleb tagasi:
Kuidas see töötab?
See on lihtne. Valemis avaldusC2: N2 = B10 tagastab massiivi, mis sisaldab kõiki FALSE väärtusi, välja arvatud see, mis vastab B10 -le. Nüüd on valem selline
| =SUMPRODUCT ((C3: N7)*{FALSE, FALSE, FALSE, FALSE,TÕSI, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}) |
Nüüd korrutatakse C3: N7 selle massiivi iga väärtusega. Iga veerg muutub nulliks, välja arvatud veerg, mis korrutatakse tõega. Nüüd saab valemiks:
| = 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}) |
Nüüd on see massiiv kokku võetud ja saame veeru summa, mis vastab lahtri B10 veerule.
2. meetod: kogu veeru summa tabelis, kasutades funktsiooni INDEX-MATCH.
Excelis sobiva veeru pealkirja summeerimise meetodi süntaks on järgmine:
| = SUM (INDEX (veerud ,, MATCH (rubriik, päised, 0))) |
Kõik selle meetodi muutujad on samad, mis SUMPRODUCT. Rakendame selle lihtsalt probleemi lahendamiseks. Kirjutage see valem lahtrisse C10.
| = SUMMA (INDEKS (C3: N7,, MATCH (B10, C2: N2,0))) |
See tagastab:
Kuidas see töötab?
Valem on lahendatud seestpoolt. Esiteks tagastab funktsioon MATCH sobiva kuu indeksi vahemikust C2: N2. Kuna meil on B1o mai, saame 5. Nüüd saab valem
| = SUMMA (INDEKS (C3: N7,, 5)) |
Seejärel tagastab funktsioon INDEX väärtused C3: N7 viiendast veerust. Nüüd saab valemiks:
| = SUM ({6; 12; 15; 15; 8}) |
Ja lõpuks saame nende väärtuste summa.
3. meetod: kogu veeru summa tabelis, kasutades nimega vahemikku ja funktsiooni INDIRECT
Kõik on lihtne, kui nimetate oma vahemikud veerupäisteks. Selle meetodi puhul peame esmalt nimetama veerud pealkirjade nimedena.
Valige tabel koos pealkirjadega ja vajutage CTRL+SHIFT+F3. See avab dialoogi, et luua vahemikest nimi. Kontrollige ülemist rida ja klõpsake nuppu OK.
See nimetab kõik andmeveerud pealkirjadeks.
Nüüd on üldine valem sobiva veeru summeerimiseks järgmine:
| = SUM (KAUDNE (pealkiri)) |
Rubriik: See on selle veeru nimi, mille soovite summeerida. Selles näites sisaldab B10 maikuud.
Selle üldvalemi rakendamiseks kirjutage see valem lahtrisse C10.
| = SUMMA (KAUDNE (B10)) |
See tagastab mai kuu summa:
Teine meetod on sarnane sellele. Selle meetodi puhul kasutame Exceli tabeleid ja selle struktureeritud nimetamist. Oletame, et olete ülaltoodud tabeli nimetanud tabeliks1. Siis töötab see valem samamoodi nagu ülaltoodud valem.
| = SUM (KAUDNE ("Tabel1 [" & B10 & "]")) |
Kuidas see töötab?
Selles valemis võtab funktsioon INDIRECT nime viite ja teisendab selle tegelikuks nimeviiteks. Edasine protseduur on lihtne. Funktsioon SUM võtab kokku nimetatud vahemiku.
Nii et jah, poisid, saate Excelis sobiva veeru kokku võtta. Loodan, et see on teile kasulik ja selgitav. Kui teil on selle artikli või mõne muu Exceli/VBA -ga seotud teema osas kahtlusi, küsige allpool kommentaaride osas.
Kuidas summeerida Exceli rea ja veeru sobitamise teel.SUMPRODUCT on kõige mitmekülgsem funktsioon keeruliste kriteeriumidega väärtuste summeerimisel ja loendamisel. Veeru ja rea sobitamise üldine funktsioon on…
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.