Prenesite primer delovnega zvezka
Ta vadnica bo pokazala, kako uporabiti funkcijo SUMIFS za seštevanje podatkov, ki ustrezajo praznim ali praznim celicam v Excelu in Google Preglednicah.
Vsota, če je prazna
Najprej bomo pokazali, kako sešteti vrstice s praznimi celicami.
Funkcija SUMIFS sešteje podatke, ki izpolnjujejo določena merila.
Za seštevanje vseh lahko uporabimo funkcijo SUMIFS Rezultati za Igralci brez imen v spodnjem primeru.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Za predstavitev prazne celice v Excelu uporabljamo dvojne narekovaje (“”). Naš primer ne upošteva Igralci A, B, C in D ter sešteje ocene za neznano Igralci.
Obdelava prostorov kot praznih celic - s pomožnim stolpcem
Pri interakciji s praznimi celicami v Excelu morate biti previdni. Celice se vam lahko zdijo prazne, vendar jih Excel ne obravnava kot prazne. To se lahko zgodi, če celica vsebuje presledke, prelome vrstic ali druge nevidne znake. To je pogosta težava pri uvozu podatkov v Excel iz drugih virov.
Če moramo vse celice, ki vsebujejo samo presledke, obravnavati enako, kot če bi bile prazne, potem formula v prejšnjem primeru ne bo delovala. Upoštevajte, da formula SUMIFS ne šteje celice B9 spodaj (”“) za prazno:
Če želimo celico, ki vsebuje samo presledke, obravnavati kot prazno celico, lahko dodamo pomožni stolpec s funkcijo TRIM, da odstranimo dodatne presledke iz vrednosti vsake celice:
| 1 | = TRIM (B3) |
Za stolpec pomočnikov uporabimo funkcijo SUMIFS, ki zdaj natančno izračuna vsoto.
| 1 | = SUMIFS (E3: E9, D3: D9, "") |
Pomožni stolpec je enostaven za ustvarjanje in branje, vendar boste morda želeli imeti eno samo formulo za izvedbo naloge. To je obravnavano v naslednjem razdelku.
Obdelava prostorov kot praznih celic - brez stolpca pomočnikov
Če pomožni stolpec ne ustreza vašim potrebam, lahko uporabite funkcijo SUMPRODUCT v kombinaciji s funkcijama LEN in TRIM za seštevanje praznih vrstic.
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9)) = 0), D3: D9) |
V tem primeru uporabljamo funkcijo SUMPRODUCT za izvedbo zapletenega izračuna "vsote, če". Pojdimo skozi formulo.
To je naša zadnja formula:
| 1 | = PODROČJE (-(LEN (TRIM (B3: B9)) = 0), D3: D9) |
Prvič, funkcija SUMPRODUCT navaja niz vrednosti iz dveh obsegov celic:
| 1 | = SUMPRODUCT (-(LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""})) = 0), {25; 10; 15; 5 ; 8; 17; 50) |
Nato funkcija TRIM odstrani začetni in zadnji razmik Igralec imena:
| 1 | = SUMPRODUCT (-(LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""}) = 0), {25; 10; 15; 5; 8; 17; 50) |
Funkcija LEN izračuna dolžino obrezanih delov Igralec imena:
| 1 | = PODROČJE (-({1; 1; 0; 1; 0; 2; 0} = 0), {25; 10; 15; 5; 8; 17; 50) |
Z logičnim preskusom (= 0) je vse obrezano Igralec imena z 0 znaki se spremenijo v TRUE:
| 1 | = SUMPRODUCT (-({FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}), {25; 10; 15; 5; 8; 17; 50) |
Nato dvojne črtice (-) pretvorijo vrednosti TRUE in FALSE v 1s in 0s:
| 1 | = PODROČJE ({0; 0; 1; 0; 1; 0; 1}, {25; 10; 15; 5; 8; 17; 50) |
Funkcija SUMPRODUCT nato pomnoži vsak par vnosov v matrikah, da ustvari matriko Rezultati samo za Igralec prazna imena ali samo iz presledkov:
| 1 | = PODROČJE ({0; 0; 15; 0; 8; 0; 50) |
Končno se številke v matriki seštejejo skupaj:
| 1 | =73 |
Več podrobnosti o uporabi logičnih stavkov in ukaza "-" v funkciji SUMPRODUCT najdete tukaj.
Vsota, če je prazna v Google Preglednicah
Te formule delujejo popolnoma enako v Google Preglednicah kot v Excelu.
