Как для поиска точного соответствия с помощью функции SUMPRODUCT в Excel
В этой статье мы узнаем, как найти точные совпадения с помощью функции СУММПРОИЗВ в Excel.
Сценарий:
Проще говоря, при работе с таблицами данных иногда нам нужно искать значения с чувствительными к регистру буквами в Excel. Функции поиска, такие как link: / formulas-and-functions-Introduction-of-vlookup-function [VLOOKUP]
или link: / lookup-formulas-excel-match-function [MATCH]
функции не находят точного соответствия, поскольку они не чувствительны к регистру. Предположим, вы работаете с данными, где 2 имени различаются на основе чувствительности к регистру, например, Джерри и ДЖЕРРИ — это два разных имени. Вы можете выполнять такие задачи, как операции с несколькими диапазонами, используя формулу, описанную ниже.
Как решить проблему?
Для этой задачи нам потребуется использовать link: / summing-excel-sumproduct-function [функция СУММПРОИЗВ]
& link: / logic-formulas-excel-точная-функция-2 [ТОЧНАЯ функция]
. Теперь составим формулу из функции. Здесь нам дана таблица и нам нужно найти значения, соответствующие точному совпадению в таблице в Excel. Функция СУММПРОИЗВ возвращает СУММУ соответствующих ИСТИННЫХ значений (как 1) и игнорирует значения, соответствующие значениям ЛОЖЬ (как 0) в возвращаемом массиве
Общая формула:
= ( — ( ( lookup_value , lookup_array ) ) , (return_array ) ) |
lookup_value: значение для поиска.
lookup_array: массив поиска для значения поиска.
return_array: массив, возвращаемое значение соответствует массиву поиска.
-: отрицание (-) char изменяет значения, ИСТИНА или 1 на ЛОЖЬ или 0 и ЛОЖЬ или 0 на ИСТИНА или 1.
Пример:
Все это может сбивать с толку. Итак, давайте протестируем эту формулу, запустив ее на примере, показанном ниже. Здесь у нас есть данные о количестве и цене продуктов, полученных на даты. Если какой-либо товар покупается дважды, он имеет 2 названия. Здесь нам нужно найти количество предметов для всех предметов первой необходимости. Для этого мы назначили 2 списка в качестве полученного списка и необходимые элементы в столбце формулы. Теперь мы воспользуемся приведенной ниже формулой, чтобы получить количество «молока» из таблицы.
Используйте формулу:
= ( — ( ( F5 , B3:B11 ) ) , ( C3:C11 ) ) |
F6: найти значение в ячейке F6 B3: B11: найти массив — это элементы C3: C11: вернуть массив — количество -: отрицание (-) char изменяет значения, TRUE или 1 на FALSE или 0 и FALSE или 0 на TRUE или 1 с.
Здесь диапазон указан как ссылка на ячейку. Нажмите Enter, чтобы получить количество.
Как видите, 58 — это количество, соответствующее значению «молоко» в ячейке B5, а не «Молоко» в ячейке B9. Придется искать значение «Молоко»
если вам нужно количество «54» в ячейке C9.
Вы можете найти цену, соответствующую значению «молоко», просто используя формулу, показанную ниже.
Используйте формулу:
= ( — ( ( F5 , B3:B11 ) ) , ( D3:D11 ) ) |
F6: искать значение в ячейке F6 B3: B11: искать в массиве Items D3: D11: возвращать массив — Price
Здесь 58 — это цена, соответствующая значению «молоко» в ячейке B5, а не «молоко» в ячейке B9. Вам нужно будет искать значение «Молоко», если вам нужна цена «15.58» в ячейке D9.
Уникальные значения в поисковом массиве
Точно так же вы можете найти уникальные значения с помощью формулы. Здесь в качестве примера использовано значение поиска «EGGS».
На изображении, показанном выше, мы получили значения, изменяющие ту же формулу.
Но проблема возникает, если он имеет уникальное значение, и вы не ищете правильное значение поиска. Как здесь, используя значение «Хлеб» в формуле для поиска в таблице.
Формула возвращает 0 как количество и цену, но это не означает, что количество и цена хлеба равны 0. Просто формула возвращает 0 как значение, когда значение, которое вы ищете, не найдено. Поэтому используйте эту формулу только для поиска точного совпадения.
Вы также можете выполнять поиск точных совпадений, используя link: / lookup-formulas-excel-index-function [INDEX]
и link: / lookup-formulas-excel-match-function [MATCH]
функция в Excel. Дополнительные сведения о link: / lookup-formulas-how-to-do-case-sensitive-lookup-in-excel [Как выполнять поиск с учетом регистра с помощью функции ИНДЕКС и ПОИСКПОЗ в Excel]
. Вы также можете найти `link: / excel-text-formulas-partial-matches-in-an-array [частичные совпадения с использованием подстановочных знаков в Excel].
Вот некоторые наблюдения, показанные ниже.
Примечания:
-
Формула работает только для поиска точного совпадения.
-
Функция СУММПРОИЗВ рассматривает нечисловые значения как нули.
-
Функция СУММПРОИЗВ рассматривает логическое значение ИСТИНА как 1 и Ложь как 0.
-
Массив аргументов должен быть такого же размера, иначе функция вернет ошибку.
-
Функция СУММПРОИЗВ возвращает значение, соответствующее ИСТИННЫМ значениям в возвращаемом массиве после взятия отдельных продуктов в соответствующий массив.
-
Такие операторы, как равно (=), меньше, чем равно (⇐), больше (>) или не равно (<> *), могут выполняться в применяемой формуле только с числами.
Надеюсь, эта статья о том, как найти точное совпадение с помощью функции СУММПРОИЗВ в Excel, является пояснительной. Дополнительные статьи о формулах счета можно найти здесь. Если вам понравились наши блоги, поделитесь ими со своими друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected]
Похожие статьи
link: / lookup-formulas-use-index-and-match-to-lookup-value [Использовать ИНДЕКС и ПОИСКПОЗ для поиска значения]
: Формула ИНДЕКС-ПОИСКПОЗ используется для динамического и точного поиска значения в данной таблице. Это альтернатива функции VLOOKUP, которая устраняет недостатки функции VLOOKUP.
link: / tips-sum-by-offset-groups-in-rows-and-columns [Сумма по группам OFFSET в строках и столбцах]
: Функция OFFSET может использоваться для динамического суммирования группы ячеек. Эти группы могут находиться в любом месте листа.
link: / lookup-formulas-retrieving-every-nth-value-in-a-range [Как получить каждое N-е значение в диапазоне в Excel]
: Используя функцию СМЕЩЕНИЕ в Excel, мы можем извлекать значения из чередующихся строк или столбцы. Эта формула использует функцию СТРОКА для чередования строк и функцию COLUMN для чередования столбцов.
link: / counting-the-offset-function-in-excel [Как использовать функцию OFFSET в Excel]
: Функция OFFSET — это мощная функция Excel, которая недооценивается многими пользователями. Но эксперты знают мощь функции СМЕЩЕНИЕ и как мы можем использовать эту функцию для выполнения некоторых волшебных задач в формуле Excel. Вот основы функции СМЕЩЕНИЕ.
link: / tips-excel-wildcards [Как использовать подстановочные знаки в Excel]
: Подсчет ячеек, соответствующих фразам, с использованием подстановочных знаков в excel
Популярные статьи
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-Повышение-продуктивность [50 ярлыков Excel для повышения производительности]
: быстрее справляйтесь с задачей. Эти 50 ярлыков заставят вас работать в Excel еще быстрее.
link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию VLOOKUP в Excel]
: это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы в Excel. link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]
: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений в Excel. Функция СЧЁТЕСЛИ необходима для подготовки панели управления.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]
: Это еще одна важная функция информационной панели. Это поможет вам суммировать значения с конкретными условиями.