Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodys, kaip naudoti funkciją SUMIFS, norint susumuoti duomenis, susijusius su tuščiomis ar tuščiomis ląstelėmis „Excel“ ir „Google“ skaičiuoklėse.
Suma, jei ne tuščia
Pirmiausia parodysime, kaip apibendrinti duomenis, susijusius su tuščiomis ląstelėmis.
Mes galime naudoti SUMIFS funkciją, kad susumuotume viską Balai dėl Žaidėjai su tuščiais vardais.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Norėdami sudėti eilutes su tuščiais langeliais, mes neįtraukiame Balai su dingusiu Žaidėjas vardus. SUMIFS funkcijos viduje naudojame kriterijus „nelygu tuščiam“ („“).
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:
| 1 | = SUMIFS (D3: D9, B3: B9, "") |
Jei norite, kad ląstelė, kurioje yra tik tarpai, būtų tuščia, galime pridėti pagalbinį stulpelį, naudodami LEN ir TRIM funkcijas, kad nustatytume Žaidėjai su vardais.
Funkcija TRIM pašalina papildomus tarpus iš kiekvienos langelio vertės pradžios ir pabaigos, o LEN funkcija suskaičiuoja likusių simbolių skaičių. Jei LEN funkcijos rezultatas yra 0, tada Žaidėjas vardas turi būti tuščias arba sudarytas tik iš tarpų:
| 1 | = LEN (TRIM (B3)) |
Mes taikome SUMIFS funkciją pagalbinio stulpelio (apibendrinimas, jei didesnis nei 0), ir dabar jis tiksliai apskaičiuoja sumą.
| 1 | = SUMIFS (E3: E9, D3: D9, "> 0") |
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 visas ląsteles, kuriose yra tik tarpai, reikia apdoroti taip, lyg jos būtų tuščios, tačiau pagalbinio stulpelio naudojimas nėra tinkamas, tada mes galime naudoti funkciją SUMPRODUCT kartu su LEN ir TRIM funkcijomis, kad susumuotume duomenis, susijusius su ląstelėmis kuriame nėra tuščio Žaidėjas vardai:
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9))> 0), D3: D9) |
Šiame pavyzdyje mes naudojame SUMPRODUCT funkciją sudėtingiems „sum if“ skaičiavimams atlikti. 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 daugiau nei 0 simbolių pavadinimai pakeisti į TRUE:
| 1 | = SUMPRODUCT (-({TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}), {25; 10; 15; 5; 8; 17; 50) |
Tada dvigubi brūkšneliai (-) konvertuoja TRUE ir FALSE reikšmes į 1 ir 0:
| 1 | = SUMPRODUCT ({1; 1; 0; 1; 0; 1; 0}, {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 nėra tušti arba nėra sudaryti tik iš tarpų:
| 1 | = SUMPRODUCT ({25; 10; 0; 5; 0; 17; 0) |
Galiausiai masyvo skaičiai sumuojami
| 1 | =57 |
Daugiau informacijos apie „Boolean“ teiginių naudojimą ir komandą „-“ funkcijoje SUMPRODUCT rasite čia
Suma, jei ne tuščia „Google“ skaičiuoklėse
Šios formulės „Google“ skaičiuoklėse veikia lygiai taip pat, kaip „Excel“.
