Перейти к основному содержанию

Суммировать, если ячейки содержат или равны x или y в Excel

Чтобы суммировать ячейки, если они содержат одно или другое значение или равны ему, в этом руководстве подробно представлены две формулы, которые помогут решить проблемы.

Как суммировать, если ячейки содержат x или y в Excel?
Как суммировать, если ячейки равны x или y в Excel?

Как суммировать, если ячейки содержат x или y в Excel?

Как показано на снимке экрана ниже, для суммирования ячеек в столбце D, если ячейки в столбце C содержат «яблоко» или «личи», вы можете применить формулу, основанную на функциях СУММПРОИЗВ, ЕЧИСЛО и ПОИСК.

Общие формулы

=SUMPRODUCT(--((ISNUMBER(SEARCH("criteria1",rng1)) + ISNUMBER(SEARCH("criteria2",rng2)))>0),sum_rng)

аргументы

Критерий_диапазон1: Диапазон ячеек, которые вы хотите оценить по критерию1;
Критерий_ранг2: Диапазон ячеек, которые вы хотите оценить по критерию2;
Критерии1: Критерии, используемые для определения, какие ячейки в Criteria_rang1 будут добавлены;
Критерии2: Критерии, используемые для определения, какие ячейки в Criteria_rang2 будут добавлены.
Суммарный_диапазон: Диапазон ячеек для суммирования;

Как пользоваться этой формулой?

Выберите пустую ячейку, скопируйте или введите формулу ниже и нажмите Enter ключ для получения результата.

=SUMPRODUCT(--((ISNUMBER(SEARCH(G4,C5:C12))+ISNUMBER(SEARCH(G5,C5:C12)))>0),D5:D12)

Заметки: В этой формуле G4 и G5 - это ячейки, содержащие два условия. Кроме того, вы можете напрямую ввести фактические текстовые значения, заключенные в двойные кавычки.

=SUMPRODUCT(--((ISNUMBER(SEARCH("apple",C5:C12))+ISNUMBER(SEARCH("lychee",C5:C12)))>0),D5:D12)

Пояснение формулы

=SUMPRODUCT(--((ISNUMBER(SEARCH("apple",C5:C12))+ISNUMBER(SEARCH("lychee",C5:C12)))>0),D5:D12)

ЕЧИСЛО (ПОИСК («яблоко»; C5: C12))
> ПОИСК («яблоко»; C5: C12): Функция ПОИСК определяет положение первого символа слова «яблоко» в каждой ячейке диапазона C5: C12 и возвращает такой массив {#VALUE!; # VALUE!; 1; 9; 1; #VALUE !; # ЗНАЧЕНИЕ!; 18}. Знак #VALUE! Означает, что «яблоко» не найдено в соответствующей ячейке;
> ЕЧИСЛО ({# ЗНАЧЕНИЕ!; # ЗНАЧЕНИЕ!; 1; 9; 1; # ЗНАЧЕНИЕ!; # ЗНАЧЕНИЕ!; 18}): Здесь функция ЕЧИСЛО проверяет, является ли указанное значение в массиве числовым или нечисловым. Если параметр значения является числовым, он возвращает «ИСТИНА», если нет, он возвращает «ЛОЖЬ». Тогда вы получите новый массив {ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА}.
ЕЧИСЛО (ПОИСК ("личи"; C5: C12)): Объяснение этой части такое же, как и выше. Здесь функция ЕЧИСЛО наконец возвращает массив, подобный этому {ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА};
{ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА} + {ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА}: Затем мы складываем эти массивы вместе, и математическая операция преобразует значения ИСТИНА и ЛОЖЬ в единицы и нули. В итоге мы получаем массив чисел {1; 0; 0; 0; 1; 2; 1; 0};
({0;0;1;2;1;0;1;2}) > 0: Каждое число в массиве сравнивается с числом 0 и возвращает массивы ИСТИНА и ЛОЖЬ, например {ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА};
- ({ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА}): Эти два знака минус преобразуют «ИСТИНА» в 1 и «ЛОЖЬ» в 0. Здесь вы получите новый массив как {0; 0; 1; 1; 1; 0; 1; 1};
SUMPRODUCT({0;0;1;1;1;0;1;1},D5:D12) = SUMPRODUCT({0;0;1;1;1;0;1;1},{2000;1600;980;1200;600;900;150;300}): Функция СУММПРОИЗВ умножает соответствующие компоненты двух массивов и возвращает массив как {0; 0; 980; 1200; 600; 0; 150; 300}, затем суммирует все результаты в массиве и, наконец, возвращает 3230.

Как суммировать, если ячейки равны x или y в Excel?

В предыдущем разделе мы ввели формулу для суммирования ячеек, если они содержат одно или другое значение. В этом разделе будет показано, как сложить две функции СУММЕСЛИ, чтобы суммировать ячейки, если они равны x или y.

В приведенном ниже примере суммируются продажи, если продукт равен «яблоку» или «личи».

Общая формула

=SUMIF(criteria_range1,"criteria1",sum_range)+SUMIF(criteria_range2,"criteria2",sum_range)

аргументы

Критерий_диапазон1: Диапазон ячеек, которые вы хотите оценить по критерию1;
Критерий_ранг2: Диапазон ячеек, которые вы хотите оценить по критерию2;
Критерии1: Критерий, определяющий, какие ячейки в Criteria_rang1 будут добавлены.
Критерии2: Критерий, определяющий, какие ячейки в Criteria_rang2 будут добавлены.
Суммарный_диапазон: Диапазон ячеек для суммирования;

Как пользоваться этой формулой?

Выберите пустую ячейку, скопируйте или введите в нее формулу ниже и нажмите Enter ключ для получения результата.

=SUMIF(C5:C12,G4,D5:D12)+SUMIF(C5:C12,G5,D5:D12)

Внимание: Эта формула ссылается на две ячейки G4 и G5, содержащие два условия. Вы можете напрямую вводить текстовые значения, заключенные в двойные кавычки, в качестве критериев в формуле.

=SUMIF(C5:C12,"apple",D5:D12)+SUMIF(C5:C12,"lychee",D5:D12)

Объяснение этой формулы

=SUMIF(C5:C12,"apple",D5:D12)+SUMIF(C5:C12,"lychee",D5:D12)

Первая функция СУММЕСЛИ суммирует продажи «яблока», а вторая функция СУММЕСЛИ суммирует продажи «личи». Затем эти два результата складываются, чтобы получить общий объем продаж.

Связанные функции

Функция СУММПРОИЗВ в Excel
Функцию СУММПРОИЗВ в Excel можно использовать для умножения двух или более столбцов или массивов, а затем получения суммы произведений.

ЕЧИСЛО в Excel
Функция Excel ЕЧИСЛО возвращает ИСТИНА, если ячейка содержит число, и ЛОЖЬ, если нет.

Функция ПОИСК в Excel
Функция ПОИСК в Excel может помочь вам найти позицию определенного символа или подстроки в заданной текстовой строке.

Функция СУММЕСЛИМН в Excel
Функция СУММЕСЛИ в Excel может помочь суммировать значения ячеек на основе нескольких критериев.

Родственные формулы

Сумма, если в ячейках есть звездочка
Чтобы суммировать ячейки, содержащие звездочку, которая рассматривается как буквальный символ, а не подстановочный знак, в этом руководстве объясняется формула, основанная на функции СУММЕСЛИ и тильде (~).

Суммируйте, если ячейки содержат как x, так и y
В этом пошаговом руководстве показано, как суммировать диапазон ячеек, если соответствующие ячейки содержат как x, так и y, с помощью функции СУММЕСЛИМН.

Сумма, если дата находится между двумя датами
В этом руководстве показано, как суммировать значения в определенном диапазоне дат в Excel с помощью формулы, основанной на функции СУММЕСЛИМН.

Суммировать, если ячейки равны или не равны определенному значению
В этом пошаговом руководстве объясняются две формулы для суммирования диапазона ячеек, которые равны или не равны определенному значению на основе функции СУММЕСЛИ.

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

🤖 Kutools AI Помощник: Революционный анализ данных на основе: Интеллектуальное исполнение   |  Генерировать код  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов функций Kutools...
Популярные опции: Найдите, выделите или определите дубликаты  |  Удалить пустые строки  |  Объедините столбцы или ячейки без потери данных  |  Раунд без формулы ...
Супер ВПросмотр: Несколько критериев  |  Множественное значение  |  На нескольких листах  |  Нечеткий поиск...
Адв. Выпадающий список: Простой раскрывающийся список  |  Зависимый раскрывающийся список  |  Выпадающий список с множественным выбором...
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить столбцы с Выберите одинаковые и разные ячейки ...
Рекомендуемые функции: Сетка Фокус  |  Просмотр дизайна  |  Большой Формулный Бар  |  Менеджер книг и листов | Библиотека ресурсов (Авто текст)  |  Выбор даты  |  Комбинировать листы  |  Шифровать/дешифровать ячейки  |  Отправлять электронные письма по списку  |  Суперфильтр  |  Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов:  12 Текст Инструменты (Добавить текст, Удалить символы ...)  |  50+ График Тип (Диаграмма Ганта ...)  |  40+ Практических Формулы (Рассчитать возраст по дню рождения ...)  |  19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути ...)  |  12 Конверсия Инструменты (Числа в слова, Конверсия валюты ...)  |  7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить ячейки Excel ...)  |  ... и более

Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...

Описание

Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations