Šiame straipsnyje išmoksime grąžinti SUM tik iš „Excel“ formulių.
Paprastais žodžiais tariant, dirbant su iš dalies užpildytais duomenimis. Kartais turime rasti vertės sumą, turinčią sąlygą. Sąlyga yra gauti sumą, kai vertės išgaunamos tik formulėmis.
Šiame straipsnyje mums reikės naudoti šias funkcijas:
- SUMPRODUCT funkcija
- Funkcija ISFORMULA
Funkcija SUMPRODUCT yra matematinė „Excel“ funkcija. Jis veikia keliuose diapazonuose. Jis padaugina atitinkamus masyvus ir tada juos prideda.
Iš viso = (A1 * B1 * C1 *… + A2 * B2 * C2 *… +…)
Sintaksė:
| = SUMPRODUCT (masyvas1, [masyvas2],…) |
masyvas: tai verčių diapazonas arba sąrašas.
Funkcija ISFORMULA grąžina TRUE, kai formulė yra ląstelės_referencijoje, ir grąžina FALSE.
Sintaksė:
| = ISFORMULA (nuoroda) |
nuoroda: nuoroda į langelį, kur patikrinti formulę
Dabar iš šių funkcijų sudarysime formulę. Čia pateiksime duomenis ir mums reikėjo sumos rezultatų, kur taikoma formulė.
Naudokite formulę:
| = SUMPRODUCT (masyvas * ISFORMULA (masyvas)) |
Paaiškinimas:
- Funkcija ISFORMULA grąžina TRUE & FALSE, atsižvelgdama į langeliuose tikrinamą būklę.
- Funkcija SUMPRODUCT atsižvelgia į 1 reikšmę kiekvienai TRUE vertei ir 0 į FALSE reikšmę.
- Produktas, paimtas tarp atitinkamų verčių, nepaisys FALSE reikšmių, nes vertės dauginamos iš 0. Tik TRUE reikšmės gauna SUM, nes vertės dauginamos iš 1.
Pavyzdys:
Išbandykime šią formulę, naudodamiesi pavyzdžiu
Čia mes turime duomenis, kuriuose grūdų kaina, išgauta pagal kiekio produktą, į jos vieneto kainą, o kai kurios kainos vertės įvedamos rankiniu būdu. Taigi, jei man reikia rasti kainos sumą, kai formulė išgavo bendrą kainą.
Dabar mes naudosime žemiau pateiktą formulę, kad gautume SUM
Formulė:
| = SUMPRODUCT (E2: E15 * ISFORMULA (E2: E15)) |
Paaiškinimas:
- Funkcija ISFORMULA grąžina TRUE & FALSE, remdamasi sąlyga, kuri tikrinama masyvo langeliuose nuo E2 iki E15.
- Funkcija SUMPRODUCT atsižvelgia į 1 reikšmę kiekvienai gautai TRUE vertei ir 0 į FALSE reikšmę, kaip parodyta žemiau.
= SUMPRODUCT (E2: E15*
{ TIESA ; NETIESA; NETIESA; NETIESA; TIESA ; TIESA ; NETIESA; TIESA ; NETIESA; TIESA ; TIESA ; TIESA ; TIESA ; TIESA } )
- Produktas, paimtas tarp atitinkamų verčių, nepaisys FALSE reikšmių, nes vertės dauginamos iš 0. Tik TRUE reikšmės gauna SUM, nes vertės dauginamos iš 1, kaip parodyta žemiau.
= SUMPRODUCT ({58.41; 0; 0; 0; 82.84; 95.58; 0; 90.27; 0; 37.8; 78.48; 57.97; 97.72; 77.88})
Čia funkcijos masyvas pateikiamas kaip ląstelės nuoroda. Norėdami gauti rezultatą, paspauskite „Enter“.
Kaip matote aukščiau esančioje momentinėje nuotraukoje, išgaunamų verčių suma, kur yra formulė.
Jei reikia rasti verčių, neturinčių formulės, sumą, naudokite funkciją NOT su funkcija ISFORMULA.
Naudokite formulę:
| = SUMPRODUCT (E2: E15 * NOT (ISFORMULA (E2: E15))) |
Naudokite formulę ir gaukite vertę, kaip parodyta paveikslėlyje žemiau.
Kaip matote iš aukščiau pateiktos formulės, galite gauti sąlygines reikšmes.
Pastabos:
- Funkcija SUMPRODUCT neskaitines reikšmes laiko 0s.
- Funkcija SUMPRODUCT loginę reikšmę TRUE laiko 1, o klaidingą - 0.
- Argumentų masyvas turi būti vienodo ilgio, kitaip funkcija grąžina klaidą.
Tikimės, kad šis straipsnis yra aiškinantis, kaip grąžinti SUM tik iš „Excel“ formulių. Daugiau straipsnių apie SUMPRODUCT funkcijas rasite čia. Prašome pasidalinti savo užklausa žemiau komentarų laukelyje. Mes jums padėsime.
Kaip naudoti „SUMPRODUCT“ funkciją programoje „Excel“
Kaip pašalinti tekstą „Excel“ pradedant nuo pozicijos
Teksto įrašų patvirtinimas
Sukurkite išskleidžiamąjį sąrašą „Excel“ su spalva
„Excel“ pašalinkite iš teksto priekines ir galines tarpus
Populiarūs straipsniai
50 „Excel“ spartusis klavišas produktyvumui padidinti
Redaguoti išskleidžiamąjį sąrašą
Absoliuti nuoroda „Excel“
Jei su sąlyginiu formatavimu
Jei su pakaitos simboliais
„Vlookup“ pagal datą