Selles artiklis õpime Excelis tagastama SUM -i hinna nädalapäevade kaupa.
Lihtsamalt öeldes, kui töötate hindadega täidetud andmetega. Mõnikord peame leidma tingimuse väärtuse summa. Tingimus on saada hinnaväärtuste summa eraldatud veergudesse.
Selle artikli jaoks vajame järgmiste funktsioonide kasutamist:
- Funktsioon SUMPRODUCT
- Funktsioon COLUMN
- MOD funktsioon
Nüüd teeme neist funktsioonidest valemi. Siin antakse meile väärtuste loend ja peame saama väärtuste summa antud n -nda veeru iga kordaja kohta.
Kasutage valemit:
= SUMPRODUCT ( - (MOD (COLUMN (loend)) - COLUMN (esimene) + 1, n) = 0), loend)
nimekirja : väärtuste loend veerus
esimene : loendi esimene lahter, mis tagastab veeru numbri
n : n -nda veeru väärtused liidetavad.
Näide:
Kõiki neid võib segadusse ajada. Niisiis, katsetame seda valemit, kasutades seda allpool näidatud näitel.
Siin oleme andnud numbreid sisaldavad andmed. Siin peame leidma numbrite SUMMA kolmanda veeru iga kordaja kohta. Seetõttu oleme selle valemi veerus loendid määranud.
Nüüd kasutame allolevat valemit, et saada numbrite summa 3., 6., 9., 12. ja nii edasi, kuni loend lõpeb
Valem:
= SUMPRODUCT ( - (MOD (COLUMN (list) - COLUMN (C5) + 1, P5) = 0), list)
loend: väärtuste loend veerus, mis on antud nimevahemike abil.
C5: loendi esimene lahter, mis tagastab lahtri viitena antud veeru numbri
P5: lahtri viitena antud n väärtus
Selgitus:
- MOD (VEERG (loend) -VEERG (C5)+1, P5) tagastab suhtelise veeru numbri iga n intervalli kohta. Nagu
{ 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 }
- Ülaltoodud massiiv sobitatakse väärtusega 0 ja tagastab massiivi Tõene ja vale.
{FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE}
- Funktsioon SUMPRODUCT võtab arvesse iga saadud tõelise väärtuse väärtust 1 ja väärtuse VÄÄR väärtust 0, nagu allpool näidatud.
= SUMPRODUCT ( - ({0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0}), {40, 36, 51, 33, 42, 30, 92, 67, 34, 54, 69, 56, 50})
- - teisendab TRUE väärtuseks 1 ja FALSE väärtuseks 0.
- 1 -le vastavad väärtused liidetakse ainult nii, nagu on näidatud ülaltoodud valemis.
- Nüüd liidab funktsioon SUMPRODUCT kõik allpool toodud väärtused kokku.
= SUMPRODUCT ({0, 0, 51, 0, 0, 30, 0, 0, 34, 0, 0, 56, 0})
Väärtuste {51 + 30 + 34 + 56} SUM peab tagastama 171.
Siin on funktsiooni massiiv antud nimega vahemik ja lahter lahtri_viide. Tulemuse nägemiseks vajutage sisestusklahvi.
Nagu ülaltoodud hetktõmmisel näha, tagastab iga kolmanda intervalliga eraldatud väärtuste summa 171. Väärtused on märgitud punaste kastide all, nagu on näidatud ülaltoodud hetktõmmisel.
Või võite kasutada loendimassiivi, kasutades järgmist valemit:
= SUMPRODUCT ( - (MOD (VEERG (C5: O5) - VEERG (C5) + 1, P5) = 0), C5: O5)
Ülaltoodud valem tagastab sama tulemuse. On ainult üks asendusloend nimega vahemik, mis asendatakse massiivivahemikuga (C5: O5).
Nagu näete, töötab valem hästi. Nüüd peame teiste loendite valemi kopeerima. Nii et kasutage CTRL + D või lohistage Exceli valik alla.
Nagu ülaltoodud valemist näete, saate tingimuslikke väärtusi.
Märkused:
- Funktsioon SUMPRODUCT loeb mittearvulised väärtused 0 -deks.
- Funktsioon SUMPRODUCT loeb loogilise väärtuse TRUE väärtuseks 1 ja vale väärtuseks 0.
- Argumendimassiiv peab olema sama pikk, vastasel juhul tagastab valem vea.
Loodetavasti on see artikkel selgitav, kuidas tagastada SUM ainult iga n -ö veerg Excelis valemitega. Siit leiate rohkem artikleid funktsioonide SUMPRODUCT kohta. Palun jagage oma päringut allpool kommentaaride kastis. Me aitame teid.
Funktsiooni SUMPRODUCT kasutamine Excelis: Tagastab SUM -i pärast väärtuste korrutamist mitmes Exceli massiivis.
SUM, kui kuupäev jääb vahele : Tagastab Exceli kuupäevade või perioodide vaheliste väärtuste SUMMA.
Summa, kui kuupäev on antud kuupäevast suurem: Tagastab väärtuste SUM pärast Excelis antud kuupäeva või perioodi.
2 võimalust Excelis iga kuu summeerimiseks: Tagastab Exceli konkreetse kuu väärtuste summa.
Kuidas liita mitu veergu olekuga: Tagastab väärtuste SUM väärtuse mitmes veerus, mille tingimus on Excelis
Populaarsed artiklid
50 Exceli otsetee tootlikkuse suurendamiseks
Muutke ripploendit
Absoluutne viide Excelis
Kui tingimusvorminguga
Kui metamärkidega
Vlookup kuupäeva järgi
Teisendage tollid Excelis 2016 jalgadeks ja tollideks
Liituge Excelis ees- ja perekonnanimega
Loendage lahtrid, mis vastavad kas A või B.