Prenesite primer delovnega zvezka
Ta vadnica bo pokazala, kako uporabiti funkcijo SUMIFS za seštevanje podatkov, povezanih z nepraznimi ali praznimi celicami v Excelu in Google Preglednicah.
Vsota, če ni prazna
Najprej bomo pokazali, kako sešteti podatke v zvezi s celicami, ki niso prazne.
Za seštevanje vseh lahko uporabimo funkcijo SUMIFS Rezultati za Igralci z imen, ki niso prazna.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Če povzamemo vrstice z nepraznjenimi celicami, izključimo Rezultati z manjkajočo Igralec imena. V funkciji SUMIFS uporabljamo merila »ni enako prazno« (»«).
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:
| 1 | = SUMIFS (D3: D9, B3: B9, "") |
Če želimo celico, ki vsebuje samo presledke, obravnavati kot prazno celico, lahko s pomočjo funkcij LEN in TRIM za identifikacijo dodamo pomožni stolpec Igralci z imeni.
Funkcija TRIM odstrani dodatne presledke na začetku in koncu vrednosti vsake celice, funkcija LEN pa šteje število preostalih znakov. Če je rezultat funkcije LEN 0, potem je Igralec ime mora biti prazno ali samo iz presledkov:
| 1 | = LEN (TRIM (B3)) |
Funkcijo SUMIFS uporabimo za pomožni stolpec (seštevanje, če je večje od 0) in zdaj natančno izračuna vsoto.
| 1 | = SUMIFS (E3: E9, D3: D9, "> 0") |
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 je treba vse celice, ki vsebujejo samo presledke, obravnavati na enak način, kot če bi bile prazne, vendar uporaba pomožnega stolpca ni primerna, lahko za vsoto podatkov, ki se nanašajo na celice, uporabimo funkcijo SUMPRODUCT v kombinaciji s funkcijami LEN in TRIM ki vsebuje prazno polje Igralec imena:
| 1 | = PODROČJE (-(LEN (TRIM (B3: B9))> 0), D3: D9) |
V tem primeru uporabljamo funkcijo SUMPRODUCT za izvajanje zapletenih izračunov "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 testom (> 0), vse obrezane Igralec imena z več kot 0 znaki se spremenijo v TRUE:
| 1 | = SUMPRODUCT (-({TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}), {25; 10; 15; 5; 8; 17; 50) |
Nato dvojne črtice (-) pretvorijo vrednosti TRUE in FALSE v 1s in 0s:
| 1 | = PODROČJE ({1; 1; 0; 1; 0; 1; 0}, {25; 10; 15; 5; 8; 17; 50) |
Funkcija SUMPRODUCT nato pomnoži vsak par vnosov v matrikah, da ustvari matriko Rezultati samo za Igralec imena, ki niso prazna ali niso narejena samo iz presledkov:
| 1 | = PODROČJE ({25; 10; 0; 5; 0; 17; 0) |
Končno se številke v matriki seštejejo skupaj
| 1 | =57 |
Več podrobnosti o uporabi logičnih stavkov in ukaza "-" v funkciji SUMPRODUCT najdete tukaj
Vsota, če ni prazna v Google Preglednicah
Te formule delujejo popolnoma enako v Google Preglednicah kot v Excelu.
