Laadige alla töövihiku näide
See õpetus näitab, kuidas kasutada funktsiooni SUMIFS, et liita Exceli ja Google'i arvutustabelite tühjade või tühjade lahtritega seotud andmed.
Summa, kui pole tühi
Esiteks demonstreerime, kuidas summeerida mittetühjade lahtritega seotud andmeid.
Kõigi summeerimiseks saame kasutada funktsiooni SUMIFS Skoorid eest Mängijad tühjade nimedega.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Mitte-tühjade lahtritega ridade summeerimiseks välistame Skoorid kadunutega Mängija nimed. Funktsiooni SUMIFS sees kasutame kriteeriume „mitte võrdne tühjaga” („”).
Ruumide käsitlemine tühjade lahtritena - abistajaveeruga
Excelis tühjade lahtritega suhtlemisel peate olema ettevaatlik. Lahtrid võivad teile tühjad tunduda, kuid Excel ei käsitle neid tühjana. See võib juhtuda, kui lahter sisaldab tühikuid, reavahesid või muid nähtamatuid märke. See on tavaline probleem, kui importite andmeid Excelisse teistest allikatest.
Kui peame tühikuid sisaldavaid lahtreid käsitlema samamoodi nagu tühjad, siis eelmise näite valem ei tööta. Pange tähele, kuidas SUMIFSi valem ei pea lahtrit B9 allpool ("") tühjaks:
| 1 | = SUMIFS (D3: D9, B3: B9, "") |
Ainult tühikuid sisaldava lahtri kohtlemiseks tühja lahtrina võime lisada abistajaveeru, kasutades tuvastamiseks funktsioone LEN ja TRIM Mängijad nimedega.
Funktsioon TRIM eemaldab iga lahtri väärtuse algusest ja lõpust täiendavad tühikud ning LEN -funktsioon loendab allesjäänud märkide arvu. Kui funktsiooni LEN tulemus on 0, siis Mängija nimi peab olema tühi või ainult tühikutest koosnev:
| 1 | = LEN (TRIM (B3)) |
Rakendame abistaja veerule funktsiooni SUMIFS (summeerimine, kui see on suurem kui 0) ja see arvutab nüüd summa täpselt.
| 1 | = SUMIFS (E3: E9, D3: D9, "> 0") |
Abistajaveergu on lihtne luua ja seda on lihtne lugeda, kuid võib -olla soovite ülesande täitmiseks kasutada ühte valemit. Seda käsitletakse järgmises osas.
Ruumide käsitlemine tühjade lahtritena - ilma abistaja veerguta
Kui mis tahes lahtreid, mis sisaldavad ainult tühikuid, tuleb kohelda samamoodi, nagu oleksid need tühjad, kuid abistajaveeru kasutamine ei ole asjakohane, siis saame rakkudega seotud andmete summeerimiseks kasutada funktsiooni SUMPRODUCT koos funktsioonidega LEN ja TRIM. sisaldab tühja Mängija nimed:
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9))> 0), D3: D9) |
Selles näites kasutame funktsiooni SUMPRODUCT keeruliste „summa, kui” arvutuste tegemiseks. Lähme läbi valemi.
See on meie viimane valem:
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9))> 0), D3: D9) |
Esiteks loetleb funktsioon SUMPRODUCT kahe lahtrivahemiku väärtuste massiivi:
| 1 | = SUMPRODUCT (-(LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""}))> 0), {25; 10; 15; 5 ; 8; 17; 50) |
Seejärel eemaldab funktsioon TRIM eesmised ja tagumised tühikud Mängija nimed:
| 1 | = SUMPRODUCT (-(LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""})> 0), {25; 10; 15; 5; 8; 17; 50) |
Funktsioon LEN arvutab kärbitud pikkused Mängija nimed:
| 1 | = SUMPRODUCT (-({1; 1; 0; 1; 0; 2; 0}> 0), {25; 10; 15; 5; 8; 17; 50) |
Loogilise testiga (> 0) kärbitakse kõik Mängija nimed, milles on rohkem kui 0 tähemärki, muudetakse väärtuseks TRUE:
| 1 | = SUMPRODUCT (-({TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}), {25; 10; 15; 5; 8; 17; 50) |
Seejärel teisendavad topeltkriipsud (-) väärtused TRUE ja FALSE väärtusteks 1 ja 0:
| 1 | = SUMPRODUCT ({1; 1; 0; 1; 0; 1; 0}, {25; 10; 15; 5; 8; 17; 50) |
Funktsioon SUMPRODUCT korrutab seejärel massiivide iga kirjepaari, et saada massiiv Skoorid ainult Mängija nimed, mis ei ole tühjad ega ole tehtud ainult tühikutest:
| 1 | = SUMPRODUCT ({25; 10; 0; 5; 0; 17; 0) |
Lõpuks summeeritakse massiivi numbrid kokku
| 1 | =57 |
Lisateavet Boole'i lausete ja käsu „-” kasutamise kohta funktsioonis SUMPRODUCT leiate siit
Summa, kui pole Google'i arvutustabelites tühi
Need valemid töötavad Google'i arvutustabelites täpselt samamoodi nagu Excelis.
