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