Как для поиска точного соответствия с помощью функции SUMPRODUCT в 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: массив, возвращаемое значение соответствует массиву поиска.

-: отрицание (-) char изменяет значения, ИСТИНА или 1 на ЛОЖЬ или 0 и ЛОЖЬ или 0 на ИСТИНА или 1.

Все это может сбивать с толку. Итак, давайте протестируем эту формулу, запустив ее на примере, показанном ниже. Здесь у нас есть данные о количестве и цене продуктов, полученных на даты. Если какой-либо товар покупается дважды, он имеет 2 названия. Здесь нам нужно найти количество предметов для всех предметов первой необходимости. Для этого мы назначили 2 списка в качестве полученного списка и необходимые элементы в столбце формулы. Теперь мы воспользуемся приведенной ниже формулой, чтобы получить количество «молока» из таблицы.

F6: найти значение в ячейке F6 B3: B11: найти массив — это элементы C3: C11: вернуть массив — количество -: отрицание (-) char изменяет значения, TRUE или 1 на FALSE или 0 и FALSE или 0 на TRUE или 1 с.

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

Как видите, 58 — это количество, соответствующее значению «молоко» в ячейке B5, а не «Молоко» в ячейке B9. Придется искать значение «Молоко»

если вам нужно количество «54» в ячейке C9.

Вы можете найти цену, соответствующую значению «молоко», просто используя формулу, показанную ниже.

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].

Вот некоторые наблюдения, показанные ниже.

Примечания:

Надеюсь, эта статья о том, как найти точное совпадение с помощью функции СУММПРОИЗВ в 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]: Это еще одна важная функция информационной панели. Это поможет вам суммировать значения с конкретными условиями.