Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parādīs, kā izmantot funkciju SUMIFS, lai summētu datus, kas atbilst tukšām vai tukšām šūnām programmā Excel un Google izklājlapās.
Summa, ja ir tukša
Pirmkārt, mēs parādīsim, kā summēt rindas ar tukšām šūnām.
Funkcija SUMIFS apkopo datus, kas atbilst noteiktiem kritērijiem.
Visu summēšanai mēs varam izmantot funkciju SUMIFS Rezultāti priekš Spēlētāji bez nosaukumiem zemāk redzamajā piemērā.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Mēs izmantojam dubultās pēdiņas (“”), lai attēlotu tukšu šūnu programmā Excel. Mūsu piemērs tiek ignorēts Spēlētāji A, B, C un D un apkopo nezināmu punktu skaitu Spēlētāji.
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:
Lai apstrādātu šūnu, kurā ir tikai atstarpes, tā būtu tukša šūna, mēs varam pievienot palīgu kolonnu ar funkciju TRIM, lai noņemtu papildu atstarpes no katras šūnas vērtības:
| 1 | = TRIM (B3) |
Mēs izmantojam SUMIFS funkciju palīga kolonnā, un tagad tā precīzi aprēķina summu.
| 1 | = SUMIFS (E3: E9, D3: D9, "") |
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 palīga kolonna nav piemērota jūsu vajadzībām, tukšo rindu summēšanai varat izmantot funkciju SUMPRODUCT kopā ar funkcijām LEN un TRIM.
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9))) = 0), D3: D9) |
Šajā piemērā mēs izmantojam funkciju SUMPRODUCT, lai veiktu sarežģītu summas aprēķinu. 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 0 rakstzīmēm tiek mainīti uz TRUE:
| 1 | = SUMPRODUCT (-({FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}), {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 ({0; 0; 1; 0; 1; 0; 1}, {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 ir tukši vai veidoti tikai no atstarpēm:
| 1 | = SUMPRODUCT ({0; 0; 15; 0; 8; 0; 50) |
Visbeidzot, masīvā esošie skaitļi tiek summēti kopā:
| 1 | =73 |
Sīkāku informāciju par Būla paziņojumu izmantošanu un komandu “-” SUMPRODUCT funkcijā var atrast šeit.
Summa, ja Google izklājlapās ir tukša
Šīs formulas Google izklājlapās darbojas tieši tāpat kā programmā Excel.
