Завантажте зразок робочої книги
У цьому посібнику буде показано, як використовувати функцію SUMIFS для підсумовування даних, пов’язаних із непустими чи непорожніми клітинками в Excel та Google Таблицях.
Сума, якщо не пуста
По-перше, ми покажемо, як підсумовувати дані, що стосуються непустих клітинок.
Ми можемо використовувати функцію SUMIFS, щоб підсумувати все Оцінки за Гравці з непорожніми іменами.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Щоб підсумувати рядки з непустими осередками, ми виключаємо Оцінки з відсутнім Гравець імена. Ми використовуємо критерій "не дорівнює порожньому" ("") всередині функції SUMIFS.
Обробка просторів як порожніх клітин - за допомогою стовпця -помічника
Потрібно бути обережним при взаємодії з порожніми осередками в Excel. Для вас клітинки можуть виглядати порожніми, але Excel не вважатиме їх порожніми. Це може статися, якщо клітинка містить пробіли, розриви рядків або інші невидимі символи. Це поширена проблема при імпорті даних в Excel з інших джерел.
Якщо нам потрібно розглядати будь -які клітинки, які містять лише пробіли, так само, як якщо б вони були порожніми, то формула в попередньому прикладі не працюватиме. Зверніть увагу, що формула SUMIFS не вважає клітинку В9 нижче (”“) порожньою:
| 1 | = SUMIFS (D3: D9, B3: B9, "") |
Щоб обробляти клітинку, що містить лише пробіли, як пусту клітинку, ми можемо додати допоміжний стовпець за допомогою функцій LEN та TRIM для ідентифікації Гравці з іменами.
Функція TRIM видаляє зайві пробіли від початку та кінця значення кожної комірки, а потім функція LEN підраховує кількість символів, що залишилися. Якщо результат функції LEN дорівнює 0, то Гравець ім’я повинно бути порожнім або містити лише пробіли:
| 1 | = ДОГОЛОГА (ОБРІЗКА (B3)) |
Ми застосовуємо функцію SUMIFS до допоміжного стовпця (Підсумовуючи, якщо більше 0), і тепер вона точно обчислює суму.
| 1 | = SUMIFS (E3: E9, D3: D9, "> 0") |
Стовпець -помічник легко створити та легко читати, але вам може знадобитися єдина формула для виконання завдання. Про це йдеться у наступному розділі.
Обробка просторів як порожніх клітин - без стовпця -помічника
Якщо потрібно обробляти будь -які клітинки, що містять лише пробіли, так само, як якщо б вони були порожніми, але використання допоміжного стовпця недоцільно, то ми можемо використовувати функцію SUMPRODUCT у поєднанні з функціями LEN та TRIM для підсумовування даних, що стосуються комірок містить непорожнє Гравець імена:
| 1 | = ПІДПРИЄМСТВО (-(Об’єктив (ОБЛИЧЧЕННЯ (В3: В9))> 0), D3: D9) |
У цьому прикладі ми використовуємо функцію SUMPRODUCT для виконання складних обчислень "сума якщо". Пройдемо формулу.
Ось наша остаточна формула:
| 1 | = ПІДПРИЄМСТВО (-(Об’єктив (ОБЛИЧЧЕННЯ (В3: В9))> 0), D3: D9) |
По -перше, функція SUMPRODUCT перелічує масив значень з двох діапазонів комірок:
| 1 | = SUMPRODUCT (-(LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""}))> 0), {25; 10; 15; 5 ; 8; 17; 50) |
Потім функція TRIM видаляє початковий і кінцевий пробіли з Гравець імена:
| 1 | = SUMPRODUCT (-(LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""})> 0), {25; 10; 15; 5; 8; 17; 50) |
Функція LEN обчислює довжину обрізки Гравець імена:
| 1 | = ПІДПРИЄМСТВО (-({1; 1; 0; 1; 0; 2; 0}> 0), {25; 10; 15; 5; 8; 17; 50) |
За допомогою логічного тесту (> 0) будь -яке обрізане Гравець імена з більш ніж 0 символами змінюються на TRUE:
| 1 | = SUMPRODUCT (-({TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}), {25; 10; 15; 5; 8; 17; 50) |
Далі подвійні тире (-) перетворюють значення TRUE і FALSE в 1s і 0s:
| 1 | = ПОВНІННЯ ({1; 1; 0; 1; 0; 1; 0}, {25; 10; 15; 5; 8; 17; 50) |
Потім функція SUMPRODUCT перемножує кожну пару записів у масивах для створення масиву Оцінки лише для Гравець імена, які не є порожніми або зроблені не лише з пробілів:
| 1 | = ПОВНІННЯ ({25; 10; 0; 5; 0; 17; 0) |
Нарешті, числа в масиві підсумовуються разом
| 1 | =57 |
Докладніше про використання булевих операторів та команди “-” у функції SUMPRODUCT можна знайти тут
Сума, якщо не є порожньою у Таблицях Google
Ці формули працюють точно так само в Google Таблицях, як і в Excel.
