Загрузите образец книги
В этом руководстве будет показано, как использовать функцию СУММЕСЛИМН для суммирования данных, соответствующих пустым или пустым ячейкам в Excel и Google Таблицах.
Сумма, если пусто
Сначала мы продемонстрируем, как суммировать строки с пустыми ячейками.
Функция СУММЕСЛИМН суммирует данные, соответствующие определенным критериям.
Мы можем использовать функцию СУММЕСЛИМН, чтобы суммировать все Очки для Игроки без имен в примере ниже.
| 1 | = СУММЕСЛИМН (C3: C8; B3: B8; "") |
Мы используем двойные кавычки («») для обозначения пустой ячейки в Excel. В нашем примере игнорируется Игроки A, B, C и D и суммирует баллы для неизвестных Игроки.
Обработка пробелов как пустых ячеек - со вспомогательным столбцом
Будьте осторожны при работе с пустыми ячейками в Excel. Ячейки могут казаться вам пустыми, но Excel не считает их пустыми. Это может произойти, если ячейка содержит пробелы, разрывы строк или другие невидимые символы. Это распространенная проблема при импорте данных в Excel из других источников.
Если нам нужно обрабатывать любые ячейки, содержащие только пробелы, так же, как если бы они были пустыми, то формула в предыдущем примере не будет работать. Обратите внимание, что формула СУММЕСЛИМН не считает ячейку B9 ниже ("") пустой:
Чтобы рассматривать ячейку, содержащую только пробелы, как если бы это была пустая ячейка, мы можем добавить вспомогательный столбец с функцией TRIM, чтобы удалить лишние пробелы из значения каждой ячейки:
| 1 | = ОБРЕЗАТЬ (B3) |
Мы применяем функцию СУММЕСЛИМН к вспомогательному столбцу, и теперь она точно вычисляет сумму.
| 1 | = СУММЕСЛИМН (E3: E9; D3: D9; "") |
Вспомогательный столбец легко создать и легко прочитать, но вы, возможно, захотите иметь одну формулу для выполнения этой задачи. Это рассматривается в следующем разделе.
Обработка пробелов как пустых ячеек - без вспомогательного столбца
Если вспомогательный столбец не подходит для ваших нужд, вы можете использовать функцию SUMPRODUCT в сочетании с функциями LEN и TRIM для суммирования пустых строк.
| 1 | = СУММПРОИЗВ (- (LEN (ОБРЕЗАТЬ (B3: B9)) = 0), D3: D9) |
В этом примере мы используем функцию СУММПРОИЗВ для выполнения сложного вычисления «суммы если». Давайте пройдемся по формуле.
Это наша окончательная формула:
| 1 | = СУММПРОИЗВ (- (LEN (ОБРЕЗАТЬ (B3: B9)) = 0), D3: D9) |
Во-первых, функция СУММПРОИЗВ перечисляет массив значений из двух диапазонов ячеек:
| 1 | = СУММПРОИЗВ (- (LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""})) = 0), {25; 10; 15; 5 ; 8; 17; 50) |
Затем функция TRIM удаляет начальные и конечные пробелы из Игрок имена:
| 1 | = СУММПРОИЗВ (- (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 символами изменяются на ИСТИНА:
| 1 | = СУММПРОИЗВ (- ({ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА}), {25; 10; 15; 5; 8; 17; 50) |
Затем двойные дефисы (-) преобразуют значения ИСТИНА и ЛОЖЬ в единицы и нули:
| 1 | = СУММПРОИЗВ ({0; 0; 1; 0; 1; 0; 1}, {25; 10; 15; 5; 8; 17; 50) |
Затем функция СУММПРОИЗВ умножает каждую пару записей в массивах, чтобы получить массив Очки только для Игрок имена, пустые или состоящие только из пробелов:
| 1 | = СУММПРОИЗВ ({0; 0; 15; 0; 8; 0; 50) |
Наконец, числа в массиве суммируются:
| 1 | =73 |
Более подробную информацию об использовании логических операторов и команды «-» в функции SUMPRODUCT можно найти здесь.
Сумма, если пусто в Google Таблицах
Эти формулы работают в Google Таблицах точно так же, как и в Excel.
