Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodys, kaip naudoti funkciją SUMIFS, norint susumuoti duomenis, atitinkančius tuščius arba tuščius langelius „Excel“ ir „Google“ skaičiuoklėse.
Suma, jei tuščia
Pirmiausia parodysime, kaip sudėti eilutes su tuščiais langeliais.
Funkcija SUMIFS apibendrina duomenis, atitinkančius tam tikrus kriterijus.
Mes galime naudoti SUMIFS funkciją, kad susumuotume viską Balai dėl Žaidėjai be pavardžių žemiau esančiame pavyzdyje.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Mes naudojame dvigubas kabutes („“), kad pavaizduotume tuščią langelį „Excel“. Mūsų pavyzdys ignoruojamas Žaidėjai A, B, C ir D ir sumuoja nežinomo balus Žaidėjai.
Erdvių apdorojimas kaip tuščios ląstelės - su pagalbiniu stulpeliu
Bendraudami su tuščiomis „Excel“ ląstelėmis, turite būti atsargūs. Langeliai jums gali pasirodyti tušti, tačiau „Excel“ jų nelaikys tuščiais. Taip gali atsitikti, jei langelyje yra tarpų, eilučių ar kitų nematomų simbolių. Tai dažna problema importuojant duomenis į „Excel“ iš kitų šaltinių.
Jei visas ląsteles, kuriose yra tik tarpai, turime elgtis taip pat, lyg jos būtų tuščios, tada ankstesnio pavyzdžio formulė neveiks. Atkreipkite dėmesį, kaip SUMIFS formulė nemano, kad žemiau esantis langelis B9 („“) yra tuščias:
Jei norite, kad ląstelė, kurioje yra tik tarpai, būtų tuščia, galime pridėti pagalbinį stulpelį su TRIM funkcija, kad pašalintume papildomus tarpus iš kiekvienos langelio vertės:
| 1 | = TRIM (B3) |
Pagalbininkų stulpeliui pritaikome funkciją SUMIFS, o dabar ji tiksliai apskaičiuoja sumą.
| 1 | = SUMIFS (E3: E9, D3: D9, "") |
Pagalbinę skiltį lengva sukurti ir ją lengva perskaityti, tačiau galbūt norėsite turėti vieną formulę užduočiai atlikti. Tai aptariama kitame skyriuje.
Erdvių apdorojimas kaip tuščios ląstelės - be pagalbinio stulpelio
Jei pagalbinis stulpelis neatitinka jūsų poreikių, tuščioms eilutėms sudėti galite naudoti funkciją SUMPRODUCT kartu su LEN ir TRIM funkcijomis.
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9))) = 0), D3: D9) |
Šiame pavyzdyje mes naudojame funkciją SUMPRODUCT, kad atliktume sudėtingą „sum if“ skaičiavimą. Eikime per formulę.
Tai yra mūsų galutinė formulė:
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9))) = 0), D3: D9) |
Pirma, funkcijoje SUMPRODUCT pateikiamas verčių masyvas iš dviejų langelių diapazonų:
| 1 | = SUMPRODUCT (-(LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""}))) = 0), {25; 10; 15; 5 ; 8; 17; 50) |
Tada TRIM funkcija pašalina priekines ir galines erdves Žaidėjas vardai:
| 1 | = SUMPRODUKTAS (-(LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""}) = 0), {25; 10; 15; 5; 8; 17; 50) |
Funkcija LEN apskaičiuoja apipjaustymo ilgį Žaidėjas vardai:
| 1 | = SUMPRODUCT (-({1; 1; 0; 1; 0; 2; 0} = 0), {25; 10; 15; 5; 8; 17; 50) |
Atliekant loginį testą (= 0), visi apipjaustyti Žaidėjas vardai su 0 simbolių pakeisti į TRUE:
| 1 | = SUMPRODUCT (-({FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}), {25; 10; 15; 5; 8; 17; 50) |
Tada dvigubi brūkšneliai (-) konvertuoja TRUE ir FALSE reikšmes į 1 ir 0:
| 1 | = SUMPRODUCT ({0; 0; 1; 0; 1; 0; 1}, {25; 10; 15; 5; 8; 17; 50) |
Tada funkcija SUMPRODUCT padaugina kiekvieną masyvų įrašų porą, kad gautų masyvą Balai tik už Žaidėjas pavadinimai, kurie yra tušti arba sudaryti tik iš tarpų:
| 1 | = SUMPRODUCT ({0; 0; 15; 0; 8; 0; 50) |
Galiausiai masyvo skaičiai sumuojami kartu:
| 1 | =73 |
Daugiau informacijos apie „Boolean“ teiginių naudojimą ir komandą „-“ funkcijoje SUMPRODUCT rasite čia.
„Sum If If Blank“ „Google“ skaičiuoklėse
Šios formulės „Google“ skaičiuoklėse veikia lygiai taip pat, kaip „Excel“.
