Поиск совпадений в 2-ух перечнях

Тема сопоставления 2-ух списков поднималась уже не один раз и с различных сторон, но остается одной из самых животрепещущих всюду и постоянно. Давайте разглядим один из ее качеств — подсчет количества и вывод совпадающих значений в 2-ух перечнях. Представим, что у нас есть два спектра данных, которые мы желаем сопоставить:

Для удобства, можно отдать им имена, чтоб позже употреблять их в формулах и ссылках. Для этого необходимо выделить ячейки с элементами перечня и на вкладке Формулы надавить клавишу Менеджер Имен — Сделать (Formulas — Name Manager — Create) . Также можно перевоплотить таблицы в «умные» при помощи сочетания кнопок Ctrl + T либо клавиши Форматировать как таблицу на вкладке Основная (Home — Format as Table) .

Подсчет количества совпадений

Для подсчета количества совпадений в 2-ух перечнях можно употреблять последующую элегантную формулу:

В британской версии это будет =SUMPRODUCT(COUNTIF(Список1;Список2))

Давайте разберем ее поподробнее, ибо в ней укрыто пару неочевидных фишек.

Во-1-х, функция СЧЁТЕСЛИ (COUNTIF) . Обычно она подсчитывает количество разыскиваемых значений в спектре ячеек и употребляется в последующей конфигурации:

=СЧЁТЕСЛИ( Где_искать ; Что_искать )

Обычно 1-ый аргумент — это спектр, а 2-ой — ячейка, значение либо условие (одно!), совпадения с которым мы отыскиваем в спектре. В нашей же формуле 2-ой аргумент — тоже спектр. На практике это значит, что мы заставляем Excel перебирать по очереди все ячейки из второго перечня и подсчитывать количество вхождений всякого из их в 1-ый перечень. На самом деле, это равносильно целому столбцу доп вычислений, свернутому в одну формулу:

Во-2-х, функция СУММПРОИЗВ (SUMPRODUCT) тут делает две функции — суммирует вычисленные СЧЁТЕСЛИ совпадения и заодно превращает нашу формулу в формулу массива без необходимости жать сочетание кнопок Ctrl + Shift + Enter . Формула массива нужна, чтоб функция СЧЁТЕСЛИ в режиме с 2-мя аргументами-диапазонами корректно отработала свою задачку.

Вывод перечня совпадений формулой массива

Если необходимо не попросту подсчитать количество совпадений, да и вывести совпадающие элементы отдельным перечнем, то будет нужно не самая обычная формула массива:

В британской версии это будет, соответственно:

Логика работы данной формулы последующая:

• фрагмент СЧЁТЕСЛИ(Список2;Список1), как и в примере ранее, отыскивает совпадения частей из первого перечня во 2-м
• фрагмент НЕ(СЧЁТЕСЛИ($E$1:E1;Список1)) инспектирует, не найдено ли уже текущее совпадение выше
• и, в конце концов, связка функций ИНДЕКС и ПОИСКПОЗ извлекает совпадающий элемент

Возникающие на лишних ячейках ошибки #Н/Д можно добавочно перехватить и поменять на пробелы либо пустые строчки «» при помощи функции ЕСЛИОШИБКА (IFERROR) .

Вывод перечня совпадений при помощи слияния запросов Power Query

На огромных таблицах формула массива из предшествующего метода может очень осязаемо тормозить, потому еще удобнее будет употреблять Power Query. Это бесплатная надстройка от Microsoft, способная загружать в Excel 2010-2013 и трансформировать фактически любые данные. Мощь и способности Power Query так значительны, что Microsoft включила все ее функции по дефлоту в Excel начиная с 2016 версии.

Для начала, нам нужно загрузить наши таблицы в Power Query. Для этого выделим 1-ый перечень и на вкладке Данные (в Excel 2016) либо на вкладке Power Query (если она была установлена как отдельная надстройка в Excel 2010-2013) нажимаем клавишу Из таблицы/спектра (From Table) :

Excel превратит нашу таблицу в «умную» и даст ей типовое имя Таблица1. Опосля что данные попадут в редактор запросов Power Query. Никаких преобразований с таблицей нам созодать не надо, потому можно смело нажимать в левом верхнем углу клавишу Закрыть и загрузить — Закрыть и загрузить в. (Close & Load To. ) и избрать в показавшемся окне Лишь сделать подключение (Create only connection) :

Потом повторяем то же самое со вторым спектром.

И, в конце концов, перебегаем с выявлению совпадений. Для этого на вкладке Данные либо на вкладке Power Query находим команду Получить данные — Соединить запросы — Соединить (Get Data — Merge Queries — Merge) :

В открывшемся окне делаем три вещи:

1. избираем наши таблицы из выпадающих списков
2. выделяем столбцы, по которым идет сопоставление
3. избираем Тип соединения = Внутреннее (Inner Join)

Опосля нажатия на ОК на дисплее останутся лишь совпадающие строчки:

Ненадобный столбец Таблица2 можно правой клавишей мыши удалить, а заголовок первого столбца переименовать во что-то наиболее понятное (к примеру Совпадения). А потом выгрузить полученную таблицу на лист, используя всё ту же команду Закрыть и загрузить (Close & Load) :

Если значения в начальных таблицах в будущем будут изменяться, то нужно не запамятовать обновить результирующий перечень совпадений правой клавишей мыши либо сочетанием кнопок Ctrl + Alt + F5 .

Макрос для вывода перечня совпадений

Само-собой, для решения задачки поиска совпадений можно пользоваться и макросом. Для этого нажмите клавишу Visual Basic на вкладке Разраб (Developer) . Если ее не видно, то показать ее можно через Файл — Характеристики — Настройка ленты (File — Options — Customize Ribbon) .

В окне редактора Visual Basic необходимо добавить новейший пустой модуль через меню Insert — Module и потом скопировать туда код нашего макроса:

Пользоваться добавленным макросом весьма просто. Выделите, удерживая кнопку Ctrl , оба спектра и запустите макрос клавишей Макросы на вкладке Разраб (Developer) либо сочетанием кнопок Alt + F8 . Макрос попросит указать ячейку, начиная с которой необходимо вывести перечень совпадений и опосля нажатия на ОК сделает всю работу:

Наиболее совершенный макрос подобного типа есть, к слову, в моей надстройке PLEX для Microsoft Excel.

Функция поиска совпадений в excel

Представим, что вы желаете отыскать расширение телефона сотрудника, используя его номер эмблемы либо правильную ставку комиссионных за размер продаж. Вы сможете находить данные для резвого и действенного поиска определенных данных в перечне, также для автоматической проверки корректности данных. Опосля поиска данных можно выполнить вычисления либо показать результаты с возвращаемой величиной. Существует несколько методов поиска значений в перечне данных и отображения результатов.

Что нужно создать

Четкое совпадение значений по вертикали в перечне

Для этого можно употреблять функцию ВLOOKUP либо сочетание функций ИНДЕКС и НАЙТИПОЗ.

Примеры ВРОТ

Доп сведения см. в данной инфы.

Примеры индексов и совпадений

=ИНДЕКС(необходимо возвратить значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(1-ое значение «Капуста» в массиве B2:B10))

Формула отыскивает в C2:C10 1-ое значение, соответственное значению «Ольга» (в B7), и возвращает значение в C7(100),которое является первым значением, которое соответствует значению «Ольга».

Подыыывка значений по вертикали в перечне при помощи ориентировочного совпадения

Для этого используйте функцию ВЛВП.

Принципиально: Удостоверьтесь, что значения в первой строке отсортировали в порядке возрастания.

В примере выше ВРОТ отыскивает имя учащегося, у которого 6 просмотров в спектре A2:B7. В таблице нет записи для 6 просмотров, потому ВРОТ отыскивает последующее самое высочайшее совпадение меньше 6 и находит значение 5, связанное с именованием Виктор,и таковым образом возвращает Его.

Доп сведения см. в данной инфы.

Подстановка значений по вертикали в перечне неведомого размера с внедрением четкого совпадения

Для этого используйте функции СМЕЩЕНИЕ и НАЙТИВМЕСЯК.

Примечание: Используйте этот подход, если данные в спектре наружных данных обновляются любой денек. Вы понимаете, что стоимость находится в столбце B, но вы не понимаете, сколько строк данных возвращает сервер, а 1-ый столбец не отсортировали по алфавиту.

C1 — это левые верхние ячейки спектра (также именуемые исходной).

MATCH(«Оранжевая»;C2:C7;0) отыскивает «Оранжевые» в спектре C2:C7. В спектр не следует включать запускаемую ячейку.

1 — количество столбцов справа от исходной ячейки, из которых обязано быть возвращено значение. В нашем примере ворачивается значение из столбца D, Sales.

Четкое совпадение значений по горизонтали в перечне

Для этого используйте функцию ГГПУ. См. пример ниже.

Г ПРОСМОТР отыскивает столбец «Реализации» и возвращает значение из строчки 5 в обозначенном спектре.

Доп сведения см. в сведениях о функции Г ПРОСМОТР.

Подыыывка значений по горизонтали в перечне с внедрением ориентировочного совпадения

Для этого используйте функцию ГГПУ.

Принципиально: Удостоверьтесь, что значения в первой строке отсортировали в порядке возрастания.

В примере выше ГЛЕБ отыскивает значение 11000 в строке 3 обозначенного спектра. Она не находит 11000, потому отыскивает последующее наибольшее значение меньше 1100 и возвращает значение 10543.

Доп сведения см. в сведениях о функции Г ПРОСМОТР.

Создание формулы подступа при помощи мастера подметок (толькоExcel 2007 )

Примечание: В Excel 2010 больше не будет надстройки #x0. Эта функция была заменена мастером функций и доступными функциями подменю и справки (справка).

В Excel 2007 создается формула подытов на базе данных на базе данных на базе строк и столбцов. Если вы понимаете значение в одном столбце и напротив, мастер под поисков помогает отыскивать остальные значения в строке. В формулах, которые он делает, употребляются индекс и MATCH.

Щелкните ячейку в спектре.

На вкладке Формулы в группе Решения нажмите клавишу Под поиск.

Если команда Подытов недосягаема, для вас нужно загрузить мастер под надстройка подытогов.

Загрузка надстройки «Мастер подстройок»

Нажмите клавишу Microsoft Office , изберите Характеристики Excel и щелкните категорию Надстройки.

В поле Управление изберите элемент Надстройки Excel и нажмите клавишу Перейти.

В диалоговом окне Доступные надстройки щелкните рядом с полем Мастер подстрок инажмите клавишу ОК.

Функция поиска совпадений в excel

Внедрение функции vlookup и sum поможет для вас стремительно отыскать обозначенные аспекты и сразу суммировать надлежащие значения. В данной статье мы собираемся показать для вас два способа vlookup и суммирования первого либо всех совпавших значений в строчках либо столбцах Excel.

Vlookup и суммирование совпадений в строке либо нескольких строчках с формулами

Формулы в этом разделе могут посодействовать суммировать 1-ое либо все совпавшие значения в строке либо нескольких строчках на базе определенных критериев в Excel. Пожалуйста, сделайте последующее.

Vlookup и суммируйте 1-ое совпадающее значение в строке

Представим, у вас есть таблица фруктов, как показано на снимке экрана ниже, и для вас необходимо отыскать 1-ое Apple в таблице, а потом просуммировать все надлежащие значения в той же строке. Для этого сделайте последующее.

1. Изберите пустую ячейку для вывода результата, тут я выбираю ячейку B10. Скопируйте в нее приведенную ниже формулу и нажмите Ctrl + Shift + Enter ключи, чтоб получить итог.

=SUM(VLOOKUP(A10, $A$2:$F$7, <2,3,4,5,6>, FALSE))

Нотки:

• A10 это ячейка, содержащая разыскиваемое значение;
• 2 австралийских бакса: 7 франкских баксов это спектр таблицы данных (без заголовков), который включает значение поиска и совпадающие значения;
• Номер 2,3,4,5,6 <> значит, что столбцы значений результата начинаются со второго столбца и завершаются шестым столбцом таблицы. Если количество столбцов результатов больше 6, измените <2,3,4,5,6>на <2,3,4,5,6,7,8,9….>.

Vlookup и суммирование всех совпавших значений в нескольких строчках

Приведенная выше формула может суммировать значения в строке лишь для первого совпадающего значения. Если вы желаете возвратить сумму всех совпадений в нескольких строчках, сделайте последующее.

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

=SUMPRODUCT((A2:A7=A10)*B2:F7)

С легкостью просматривайте и суммируйте совпадения в строчках либо столбцах Excel:

Высвободи Себя ПРОСМОТР и сумма полезности Kutools for Excel может посодействовать для вас стремительно отыскать и суммировать совпадения в строчках либо столбцах в Excel, как показано в демонстрации ниже.
Загрузите полнофункциональную 30-дневную бесплатную версию Kutools for Excel прямо на данный момент!

Vlookup и суммирование согласованного значения в столбце с формулами

В этом разделе представлена ​​формула для возврата суммы столбца в Excel на базе определенных критериев. Как показано на снимке экрана ниже, вы ищете заголовок столбца «Янв» в таблице фруктов, а потом суммируете все значения столбца. Пожалуйста, сделайте последующее.

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

=SUM(INDEX(B2:F7,0,MATCH(A10,B1:F1,0)))

Просто просматривайте и суммируйте совпадения в строчках либо столбцах при помощи необычного инструмента

Если вы не умеете использовать формулы, мы советуем для вас Vlookup и сумма изюминка Kutools for Excel. При помощи данной функции вы сможете просто просматривать и суммировать совпадения в строчках либо столбцах одним щелчком мыши.

Перед применением Kutools for Excel, Пожалуйста, поначалу скачайте и установите.

Vlookup и суммируйте 1-ое либо все совпавшие значения в строке либо нескольких строчках

1. Нажмите Kutools > Супер ПОСМОТРЕТЬ > ПРОСМОТР и сумма чтоб включить функцию. Смотрите снимок экрана:

2. в ПРОСМОТР и сумма диалоговое окно, настройте последующим образом.

• 2.1). Поиск и тип суммы изберите Отыскать и суммировать совпавшие значения в строке (ах) вариант;
• 2.2). Значения поиска поле изберите ячейку, содержащую разыскиваемое значение;
• 2.3). Выходной спектр поле изберите ячейку для вывода результата;
• 2.4). Спектр таблицы данных поле изберите спектр таблицы без заголовков столбцов;
• 2.5). Характеристики раздел, если вы желаете суммировать значения лишь для первого совпавшего, изберите Возвратить сумму первого совпадающего значения вариант. Если вы желаете суммировать значения для всех совпадений, изберите Возвратить сумму всех значений соответствия вариант;
• 2.6) Нажмите OK клавишу, чтоб сходу получить итог. Смотрите снимок экрана:

Внимание: Если вы желаете выполнить vlookup и суммировать 1-ое либо все совпавшие значения в столбце либо нескольких столбцах, проверьте Отыскать и суммировать совпавшие значения в столбце (ах) в диалоговом окне, а потом настройте, как показано на скриншоте ниже.

Для получения доп сведений о данной функции, Пожалуйста, нажмите тут.

Если вы желаете получить 30-дневную бесплатную пробную версию данной утилиты, пожалуйста, нажмите, чтоб загрузить это, а потом перейдите к применению операции в согласовании с обозначенными выше шагами.

Статьи по Теме

Значения Vlookup на нескольких листах
Вы сможете применить функцию vlookup, чтоб возвратить совпадающие значения в таблице рабочего листа. Но, если для вас необходимо употреблять значение vlookup на нескольких листах, как вы сможете это создать? Эта статья содержит подробные аннотации, которые посодействуют для вас просто решить делему.

Vlookup и возврат совпадающих значений в нескольких столбцах
Обычно применение функции Vlookup может возвратить совпадающее значение лишь из 1-го столбца. Время от времени для вас может потребоваться извлечь совпадающие значения из нескольких столбцов на базе критериев. Вот решение вам.

Vlookup возвращает несколько значений в одной ячейке
Обычно при применении функции ВПР, если есть несколько значений, соответственных аспектам, вы сможете получить итог лишь для первого из их. Если вы желаете возвратить все совпавшие результаты и показать их все в одной ячейке, как этого достигнуть?

Vlookup и возврат всей строчки совпадающего значения
Обычно внедрение функции vlookup может возвращать итог лишь из определенного столбца в той же строке. Эта статья покажет для вас, как возвратить всю строчку данных на базе определенных критериев.

Оборотный просмотр Vlookup либо в оборотном порядке
Обычно, функция ВПР отыскивает значения слева вправо в таблице массивов и просит, чтоб значение поиска оставалось в левой части мотивированного значения. Но время от времени вы сможете знать целевое значение и возжелать выяснить значение поиска в оборотном порядке. Как следует, для вас нужно выполнить поиск в оборотном порядке в Excel. В данной статье есть несколько методов просто совладать с данной неувязкой!