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