Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parādīs, kā izmantot funkciju SUMIFS, lai apkopotu datus, kas saistīti ar tukšām vai tukšām šūnām programmā Excel un Google izklājlapās.
Summa, ja nav tukša
Pirmkārt, mēs parādīsim, kā apkopot datus, kas saistīti ar tukšām šūnām.
Visu summēšanai mēs varam izmantot funkciju SUMIFS Rezultāti priekš Spēlētāji ar tukšiem vārdiem.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Lai apkopotu rindas ar tukšām šūnām, mēs izslēdzam Rezultāti ar pazudušo Spēlētājs vārdi. Funkcijas SUMIFS ietvaros mēs izmantojam kritērijus “nav vienāds ar tukšu” (“”).
Telpu apstrāde kā tukšas šūnas - ar palīgu kolonnu
Mijiedarbojoties ar tukšām šūnām programmā Excel, jums jābūt uzmanīgam. Šūnas jums var šķist tukšas, taču Excel tās neuzskata par tukšām. Tas var notikt, ja šūnā ir atstarpes, līniju pārtraukumi vai citas neredzamas rakstzīmes. Šī ir izplatīta problēma, importējot datus programmā Excel no citiem avotiem.
Ja visas šūnas, kurās ir tikai atstarpes, jāapstrādā tāpat kā tukšas, tad iepriekšējā piemērā esošā formula nedarbosies. Ievērojiet, kā SUMIFS formula neuzskata, ka zemāk esošā šūna B9 ("") ir tukša:
| 1 | = SUMIFS (D3: D9, B3: B9, "") |
Lai apstrādātu šūnu, kurā ir tikai atstarpes, tā būtu tukša šūna, mēs varam pievienot palīgu kolonnu, izmantojot LEN un TRIM funkcijas, lai identificētu Spēlētāji ar nosaukumiem.
Funkcija TRIM noņem papildu atstarpes no katras šūnas vērtības sākuma un beigām, un LEN funkcija pēc tam saskaita atlikušo rakstzīmju skaitu. Ja LEN funkcijas rezultāts ir 0, tad Spēlētājs nosaukumam jābūt tukšam vai tikai no atstarpēm:
| 1 | = LEN (TRIM (B3)) |
Mēs izmantojam SUMIFS funkciju palīga kolonnai (Summēšana, ja tā ir lielāka par 0), un tagad tā precīzi aprēķina summu.
| 1 | = SUMIFS (E3: E9, D3: D9, "> 0") |
Palīga kolonnu ir viegli izveidot un viegli lasīt, taču, iespējams, vēlēsities, lai uzdevuma veikšanai būtu viena formula. Tas ir aplūkots nākamajā sadaļā.
Telpu apstrāde kā tukšas šūnas - kolonna bez palīga
Ja jebkuras šūnas, kurās ir tikai atstarpes, ir jāapstrādā tāpat kā tukšas, bet palīga kolonnas izmantošana nav piemērota, tad mēs varam izmantot funkciju SUMPRODUCT kopā ar funkcijām LEN un TRIM, lai apkopotu datus par šūnām kas nesatur tukšu Spēlētājs vārdi:
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9))> 0), D3: D9) |
Šajā piemērā mēs izmantojam funkciju SUMPRODUCT, lai veiktu sarežģītus summas aprēķinus. Ejam cauri formulai.
Šī ir mūsu galīgā formula:
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9))> 0), D3: D9) |
Pirmkārt, funkcija SUMPRODUCT uzskaita vērtību masīvu no diviem šūnu diapazoniem:
| 1 | = SUMPRODUCT (-(LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""})))> 0), {25; 10; 15; 5 ; 8; 17; 50) |
Pēc tam funkcija TRIM noņem priekšējās un aizmugurējās atstarpes Spēlētājs vārdi:
| 1 | = SUMPRODUCT (-(LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""})> 0), {25; 10; 15; 5; 8; 17; 50) |
Funkcija LEN aprēķina apgriezto garumu Spēlētājs vārdi:
| 1 | = SUMPRODUCT (-({1; 1; 0; 1; 0; 2; 0}> 0), {25; 10; 15; 5; 8; 17; 50) |
Izmantojot loģisko pārbaudi (> 0), jebkura apgriezta Spēlētājs vārdi ar vairāk nekā 0 rakstzīmēm tiek mainīti uz TRUE:
| 1 | = SUMPRODUCT (-({TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}), {25; 10; 15; 5; 8; 17; 50) |
Tālāk dubultās svītras (-) pārvērš TRUE un FALSE vērtības par 1s un 0s:
| 1 | = SUMPRODUCT ({1; 1; 0; 1; 0; 1; 0}, {25; 10; 15; 5; 8; 17; 50) |
Funkcija SUMPRODUCT reizina katru masīvu ierakstu pāri, lai izveidotu masīvu Rezultāti tikai priekš Spēlētājs nosaukumi, kas nav tukši vai nav veidoti tikai no atstarpēm:
| 1 | = SUMPRODUCT ({25; 10; 0; 5; 0; 17; 0) |
Visbeidzot, masīvā esošie skaitļi tiek summēti kopā
| 1 | =57 |
Sīkāku informāciju par Būla paziņojumu izmantošanu un komandu “-” SUMPRODUCT funkcijā var atrast šeit
Summa, ja tā nav tukša Google izklājlapās
Šīs formulas Google izklājlapās darbojas tieši tāpat kā programmā Excel.
