Note: The other languages of the website are Google-translated. Back to English
English English

Суммировать, если ячейки содержат или равны 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)

аргументы

Criteria_range1: Диапазон ячеек, которые вы хотите оценить по критерию1;
Criteria_rang2: Диапазон ячеек, которые вы хотите оценить по критерию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)

аргументы

Criteria_range1: Диапазон ячеек, которые вы хотите оценить по критерию1;
Criteria_rang2: Диапазон ячеек, которые вы хотите оценить по критерию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 for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (0)
Оценок пока нет. Оцените первым!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места