Функция поискпоз в excel примеры с несколькими условиями

Поиск и подстановка по нескольким условиям

Постановка задачи

Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно — без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?

Предположим, что у нас есть база данных по ценам товаров за разные месяцы:

Нужно найти и вытащить цену заданного товара (Нектарин) в определенном месяце (Январь), т.е. получить на выходе152, но автоматически, т.е. с помощью формулы. ВПР в чистом виде тут не поможет, но есть несколько других способов решить эту задачу.

Способ 1. Дополнительный столбец с ключом поиска

Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы : Простой способ, знакомая функция, работает с любыми данными.

Минусы : Надо делать дополнительный столбец и потом, возможно, еще и прятать его от пользователя. При изменении числа строк в таблице — допротягивать формулу сцепки на новые строки (хотя это можно упростить применением умной таблицы).

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS) , появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

Плюсы : Не нужен дополнительный столбец, решение легко масштабируется на большее количество условий (до 127), быстро считает.

Минусы : Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

О том, как спользовать связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) в качестве более мощной альтернативы ВПР я уже подробно описывал (с видео). В нашем же случае, можно применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:

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

Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы : Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

Как узнать, что ячейка Эксель содержит латиницу

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

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

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

Использование специальной функции

Основная проблема поиска латинских букв, что их в два с половиной раза больше, чем цифр. Следовательно, нужно задавать программе цикл, состоящий из 26 итераций, что может быть довольно напряжно. Но если использовать формулу массива, состоящую из приведенных выше функций СЧЕТ и ПОИСК, то эта затея уже и не кажется настолько сложной: =СЧЁТ(ПОИСК({«a»:»b»:»c»:»d»:»e»:»f»:»g»:»h»:»i»:»j»:»k»:»l»:»m»:»n»:»o»:»p»:»q»:»r»:»s»:»t»:»u»:»v»:»w»:»x»:»y»:»z»};A1))>0. Эта формула вполне подходит для большинства ситуаций. Например, если нет возможности установить соответствующие макросы, которые бы смогли делать это проще и быстрее.

В описанной выше формуле A1 – это та ячейка, в которой осуществляется проверка. Соответственно, вам нужно поставить ту, которая подходит в вашей ситуации. В результате проверки с помощью этой функции возвращается логическое значение. Если совпадения обнаруживаются, то оператор возвращает ИСТИНА, если их нет – ЛОЖЬ.

Функция ПОИСК не дает возможности учитывать регистр при поиске символов. Чтобы это сделать, надо воспользоваться оператором НАЙТИ, который выполняет те же операции, имеет такие же самые аргументы, только уже учитывает регистр. Еще один способ – сделать описанную выше формулу формулой массива. В таком случае она будет иметь следующий вид:{=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1))>0}.

Поскольку это формула массива, ее нужно указывать без скобок. При этом после ввода надо нажать комбинацию клавиш Ctrl + Shift + Enter (вместо простого нажатия клавиши ввода, как в случае с обычной функцией), после чего фигурные скобки появятся сами.

Если же нужно найти кириллицу, последовательность действий аналогичная, только в качестве диапазона поиска нужно задавать всю последовательность кириллических символов. =СЧЁТ(ПОИСК({«а»:»б»:»в»:»г»:»д»:»е»:»ё»:»ж»:»з»:»и»:»й»:»к»:»л»:»м»:»н»:»о»:»п»:»р»:»с»:»т»:»у»:»ф»:»х»:»ц»:»ч»:»ш»:»щ»:»ъ»:»ы»:»ь»:»э»:»ю»:»я»};A1))>0. Также можно юзать функцию СИМВОЛ, чтобы сделать это. {=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(192:223));A1))>0}

Эта формула должна записываться так, как формула массива. Следовательно, нужно нажать комбинацию клавиш Ctrl + Shift + Enter вместо простого нажатия клавиши ввода. Но есть некоторые исключения, при которых эта функция не будет работать. Нужно предварительно убедиться в том, что язык по умолчанию для программ не в кодировке Unicode стоит русский. В этом случае проблем возникнуть не должно. Эти формулы имеют некоторые отличия между собой. Вместо 33 букв последняя формула применяет только 32. То есть, она не учитывает букву ё, как кириллическую.

В этом случае, аналогично, как и в предыдущем, для поиска нужных символов с учетом регистра, необходимо использовать функцию НАЙТИ. Поэтому можно искать, к примеру, половину алфавита, записанную маленькими буквами и половину, записанную большими. Аргументы те же самые.

Особенности использования функции ПОИСКПОЗ в Excel

Функция имеет следующую синтаксическую запись:

=ПОИСКПОЗ( искомое_значение;просматриваемый_массив; )

искомое_значение – обязательный аргумент, принимающий текстовые, числовые значения, а также данные логического и ссылочного типов, который используется в качестве критерия поиска (для сопоставления величин или нахождения точного совпадения);
просматриваемый_массив – обязательный аргумент, принимающий данные ссылочного типа (ссылки на диапазон ячеек) или константу массива, в которых выполняется поиск позиции элемента согласно критерию, заданному первым аргументом функции;
– необязательный для заполнения аргумент в виде числового значения, определяющего способ поиска в диапазоне ячеек или массиве. Может принимать следующие значения:

-1 – поиск наименьшего ближайшего значения заданному аргументом искомое_значение в упорядоченном по убыванию массиве или диапазоне ячеек.
0 – (по умолчанию) поиск первого значения в массиве или диапазоне ячеек (не обязательно упорядоченном), которое полностью совпадает со значением, переданным в качестве первого аргумента.
1 – Поиск наибольшего ближайшего значения заданному первым аргументом в упорядоченном по возрастанию массиве или диапазоне ячеек.

Если в качестве аргумента искомое_значение была передана текстовая строка, функция ПОИСКПОЗ вернет позицию элемента в массиве (если такой существует) без учета регистра символов. Например, строки «МоСкВа» и «москва» являются равнозначными. Для различения регистров можно дополнительно использовать функцию СОВПАД.
Если поиск с использованием рассматриваемой функции не дал результатов, будет возвращен код ошибки #Н/Д.
Если аргумент явно не указан или принимает число 0, для поиска частичного совпадения текстовых значений могут быть использованы подстановочные знаки («?» — замена одного любого символа, «*» — замена любого количества символов).
Если в объекте данных, переданном в качестве аргумента просматриваемый_массив, содержится два и больше элементов, соответствующих искомому значению, будет возвращена позиция первого вхождения такого элемента.

Как правильно искать в таблицах Excel

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

Выполните команду Главная ► Редактирование ► Найти и выделить ► Найти (или нажмите Ctrl+F), чтобы открыть диалоговое окно Найти и заменить. Если вам нужно заменить данные, то выберите команду Главная ► Редактирование ► Найти и выделить ► Заменить (или нажмите Ctrl+H). От того, какую именно команду вы выполните, зависит, на какой из двух вкладок откроется диалоговое окно.

Если в открывшемся диалоговом окне Найти и заменить нажать кнопку Параметры, то отобразятся дополнительные параметры поиска информации (рис. 21.1).

Рис. 21.1. Вкладка Найти диалогового окна Найти и заменить

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

Введите ив*смир* в поле Найти, а затем нажмите кнопку Найти все. Использование подстановочных знаков не только позволяет уменьшить количество вводимых слов, но и гарантирует, что вы найдете данные по клиенту, если они имеются на этом рабочем листе. Конечно, в результатах поиска могут содержаться не отвечающие цели вашего поиска записи, но это лучше, чем ничего.

При поиске с помощью диалогового окна Найти и заменить можно использовать два подстановочных знака:

? — соответствует любому символу;
* — соответствует любому количеству символов.

Кроме того, данные подстановочные символы можно также применять при поиске числовых значений. Например, если в строке поиска задать 3*, то в результате отобразятся все ячейки, которые содержат значение, начинающееся с 3, а если вы введете 1?9, то получите все трехзначные записи, которые начинаются с 1 и заканчиваются 9.

Для поиска вопросительного знака или звездочки поставьте перед ними символ тильды (

). Например, следующая строка поиска находит текст *NONE*: -*N0NE

* Чтобы найти символ тильды, поставьте в строке поиска две тильды.

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

Флажок Учитывать регистр — установите его, чтобы регистр искомого текста совпадал с регистром заданного текста. Например, если вы зададите в поиске слово иван и установите указанный флажок, то слово Иван в результатах поиска не отобразится.
Флажок Ячейка целиком — установите его, чтобы найти ячейку, которая содержит в точности тот текст, который указан в строке поиска. Например, набрав в строке поиска слово Excel и установив указанный флажок, вы не найдете ячейку, содержащую словосочетание Microsoft Excel.
Раскрывающийся список Область поиска — список содержит три пункта: значения, формулы и примечания. Например, если в строке поиска вы зададите число 900 и в раскрывающемся списке Область поиска выберете пункт значения, то в результатах поиска вы не увидите ячейку, содержащую значение 900, если оно получено при использовании формулы.

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

Кроме того, учтите, что с помощью окна Найти и заменить нельзя найти отформатированные числовые значения. Например, если в строку поиска вы введете $5*, то значение, к которому применено денежное форматирование и которое выглядит как $54.00, не будет найдено.

Работа с датами может оказаться непростой, поскольку Excel поддерживает очень много форматов дат. Если вы ищете дату, к которой применено форматирование по умолчанию, Excel находит даты, даже если они отформатированы различными способами. Например, если ваша система использует формат даты m/d/y, строка поиска 10/*/2010 находит все даты в октябре 2010 года, независимо от того, как они отформатированы.

Используйте пустое поле Заменить на, чтобы быстро удалить какую-нибудь информацию на рабочем листе. Например, введите — * в поле Найти и оставьте поле Заменить на пустым. Затем нажмите кнопку Заменить все, чтобы Excel нашел и убрал все звездочки на листе.

5 thoughts on “ «ВПР» по частичному совпадению ”

На форуме SQL.ru мне подсказали еще одно очень изящное решение этой задачи, посмотреть его можно здесь: http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&t > Спасибо большое, Казанский (автор совета)!

Игорь, спасибо Вам огромное за эту «бронебойную» формулу. Весь интернет «перелопатила» в поиске решения своей задачи и только Вы мне помогли на 100%. Всё работает как часики. Удачи Вам, успешной работы и ещё больше таких гениальных решений.

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

Игорь, добрый день! Формула прекрасная, но есть ли какая-нибудь ее вариация, которая может находить и подставлять несколько значений сразу? Например, в строке указаны два производителя холодильников, LG и Samsung Можно ли вывести их в ячейку через запятую?

Добрый день, Артём! Спасибо за ваш комментарий и прошу прощения за медленный ответ. Вопрос интересный, но с ходу у меня на него ответа, увы, нет, а по времени довольно сильно ограничен. Если будет свободное время, попробую поломать голову на эту тему

Рассмотрим использование функции ЕСЛИ в Excel в том случае, если в ячейке находится текст.

Будьте особо внимательны в том случае, если для вас важен регистр, в котором записаны ваши текстовые значения. Функция ЕСЛИ не проверяет регистр – это делают функции, которые вы в ней используете. Поясним на примере.

Как сравнить два столбца в Excel по строкам

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

Пример 1. Как сравнить два столбца на совпадения и различия в одной строке

Для того, чтобы сравнить данные в каждой строке двух столбцов в Excel напишем простую формулу ЕСЛИ . Вставлять формулу следует в каждую строку в соседнем столбце, рядом с таблицей, в которой размещены основные данные. Создав формулу для первой строки таблицы, мы сможем ее протянуть/скопировать на остальные строки.

Для того чтобы проверить, содержат ли два столбца одной строки одинаковые данные нам потребуется формула:

Формула, определяющая различия между данными двух столбцов в одной строке будет выглядеть так:

Мы можем уместить проверку на совпадения и различия между двумя столбцами в одной строке в одной формуле:

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

Для того чтобы сравнить данные в двух столбцах одной строки с учетом регистра следует использовать формулу:

Как сделать выборку в Excel по условию

Чтобы определить соответствующие значение первому наименьшему числу нужна выборка из таблицы по условию. Допустим мы хотим узнать первый самый дешевый товар на рынке из данного прайса:

Автоматическую выборку реализует нам формула, которая будет обладать следующей структурой:

В месте «диапазон_данных_для_выборки» следует указать область значений A6:A18 для выборки из таблицы (например, текстовых), из которых функция ИНДЕКС выберет одно результирующие значение. Аргумент «диапазон» означает область ячеек с числовыми значениями, из которых следует выбрать первое наименьшее число. В аргументе «заголовок_столбца» для второй функции СТРОКА, следует указать ссылку на ячейку с заголовком столбца, который содержит диапазон числовых значений.

Естественно эту формулу следует выполнять в массиве. Поэтому для подтверждения ее ввода следует нажимать не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.

Обратите внимание ниже на рисунок, где в ячейку B3 была введена данная формула в массиве:

Выборка соответственного значения с первым наименьшим числом:

С такой формулой нам удалось выбрать минимальное значение относительно чисел. Далее разберем принцип действия формулы и пошагово проанализируем весь порядок всех вычислений.

Как создать собственное окно поиска в Excel?

За исключением использования функции «Найти» в Excel, на самом деле вы можете создать собственное поле поиска для простого поиска необходимых значений. Эта статья подробно расскажет вам о двух методах создания собственного поля поиска в Excel.

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

1. Выберите диапазон с данными, которые вам нужны для поиска в поле поиска, затем нажмите Условное форматирование > Новое правило под Главная таб. Смотрите скриншот:

2. в Новое правило форматирования диалоговое окно, вам необходимо:

2.1) Выбрать Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант в Выберите тип правила коробка;

2.2) Введите формулу = ISNUMBER (ПОИСК ($ B $ 2; A5)) в Формат значений, где эта формула истинна коробка;

2.3) Нажмите Формат кнопка, чтобы указать выделенный цвет для искомого значения;

2.4) Нажмите OK кнопку.

Заметки:

1. В формуле $ B $ 2 — это пустая ячейка, которую необходимо использовать в качестве поля поиска, а A5 — это первая ячейка выбранного диапазона, в котором необходимо искать значения. Пожалуйста, измените их по своему усмотрению.

2. В формуле не учитывается регистр.

Теперь поле поиска создано: при вводе критериев поиска в поле поиска B2 и нажатии клавиши Enter выполняется поиск всех совпадающих значений в указанном диапазоне, которые немедленно выделяются, как показано ниже.

Создайте собственное окно поиска с формулами для вывода всех результатов поиска

Предположим, у вас есть список данных, расположенный в диапазоне E4: E23, который вам нужно найти, если вы хотите перечислить все совпадающие значения в другом столбце после поиска с помощью собственного поля поиска, вы можете попробовать следующий метод.

1. Выберите пустую ячейку, которая находится рядом с ячейкой E4, здесь я выбираю ячейку D4, затем вводю формулу = ЕСЛИОШИБКА (ПОИСК ($ B $ 2; E4) + СТРОКА () / 100000; «») в строку формул, а затем нажмите Enter ключ. Смотрите скриншот:

Внимание: В формуле $ B $ 2 — это ячейка, которую необходимо использовать в качестве поля поиска, E4 — это первая ячейка списка данных, в котором необходимо выполнить поиск. Вы можете менять их по своему усмотрению

2. Продолжайте выбирать ячейку E4, затем перетащите маркер заполнения вниз в ячейку D23. Смотрите скриншот:

3. Теперь выберите ячейку C4 и введите формулу. = ЕСЛИОШИБКА (РАНГ (D4; $ D $ 4: $ D $ 23,1); «») в панель формул и нажмите Enter ключ. Выберите ячейку C4, затем перетащите маркер заполнения до C23. Смотрите скриншот:

4. Теперь вам нужно заполнить диапазон A4: A23 порядковым номером, который увеличивается на 1 от 1 до 20, как показано на скриншоте ниже:

5. Выберите пустую ячейку, в которой будет отображаться результат поиска, введите формулу. = ЕСЛИОШИБКА (ВПР (A4; $ C $ 4: $ E $ 23,3; FALSE); «») в панель формул и нажмите Enter ключ. Продолжая выбирать ячейку B4, перетащите маркер заливки вниз до B23, как показано ниже.

С этого момента при вводе данных в поле поиска B2 все совпавшие значения будут перечислены в диапазоне B4: B23, как показано на скриншоте ниже.

Внимание: этот метод не чувствителен к регистру

Последнее значение в столбце

Вы можете использовать функцию ПРОСМОТР, чтобы найти последнюю непустую ячейку в столбце.

1	= ПРОСМОТР (2,1 / (B: B «»); B: B)

Давайте пройдемся по этой формуле.

Часть формулы B: B ”” возвращает массив, содержащий значения True и False: {FALSE, TRUE, TRUE,…}, при тестировании каждая ячейка в столбце B пуста (FALSE).

1	= ПРОСМОТР (2,1 / ({ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ;…); B: B)

Эти логические значения преобразуются в 0 или 1 и используются для деления 1.

1	= ПРОСМОТР (2; {# DIV / 0!; 1; 1; 1; 1; 1; # DIV / 0!;; B: B)

Это lookup_vector для функции LOOKUP. В нашем случае lookup_value равно 2, но наибольшее значение в lookup_vector равно 1, поэтому функция LOOKUP будет соответствовать последней 1 в массиве и вернет соответствующее значение в result_vector.

Если вы уверены, что в столбце есть только числовые значения, данные начинаются с строки 1, а диапазон данных непрерывен, вы можете использовать несколько более простую формулу с функциями ИНДЕКС и СЧЁТ.

1	= ИНДЕКС (B: B; СЧЁТ (B: B))

Функция COUNT возвращает количество ячеек, заполненных данными в непрерывном диапазоне (4), а функция INDEX, таким образом, дает значение ячейки в этой соответствующей строке (4-я).

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

1	= ПРОСМОТР (2,1 / (НЕ (ПУСТОЙ (B: B))); B: B)

Функция ISBLANK возвращает массив, содержащий значения True и False, соответствующие единицам и нулям. Функция NOT изменяет True (т.е. 1) на False и False (т.е. 0) на True. Если мы инвертируем этот результирующий массив (при делении 1 на этот массив), мы получим результирующий массив, содержащий снова # DIV / 0! ошибок и единиц, которые можно использовать как поисковый массив (lookup_vector) в нашей функции LOOKUP. Функциональные возможности функции LOOKUP будут такими же, как и в нашем первом примере: она возвращает значение вектора результатов в позиции последней единицы в поисковом массиве.

Когда вам нужно вернуть номер строки с последней записью, вы можете изменить формулу, используемую в нашем первом примере, вместе с функцией ROW в вашем result_vector.

1	= ПРОСМОТР (2,1 / (B: B «»); СТРОКА (B: B))

Макрос поиска ячейки с выпадающим списком

Допустим у нас имеется таблица Excel сформированная в результате экспорта журнала фактур из истории взаиморасчетов с клиентами фирмы, как показано ниже на рисунке:

Нам необходимо найти все выпадающие списки или определить каким ячейкам присвоена проверка вводимых данных, создана инструментом: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

В программе Excel по умолчанию есть встроенный инструмент для поиска ячеек с проверкой правил вводимых значений. Чтобы его использовать следует выбрать: ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек». В появившемся диалоговом окне следует отметить опцию «проверка данных» и нажать на кнопку ОК. Но как всегда более гибким решением является написание своего специального макроса. Ведь в такие случаи всегда можно усовершенствовать инструмент и дописать много других полезных функций. А этот код макроса послужит прекрасным началом программы.

Откройте редактор макросов Visual Basic (ALT+F11) и создайте новый модуль выбрав в редакторе инструмент: «Insert»-«Module». В созданный модуль введите VBA код макроса:

Если нужно выделить все ячейки в таблице, которые содержат проверку вводимых значений включенной инструментом «Проверка данных», тогда выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«ProvDan»-«Выполнить».

В результате выделились 14 ячеек в столбце G, для которых включена проверка данных в стиле выпадающего списка:

В данном коде мы сначала выделяем все ячейки на текущем листе с помощью инструкции:

Cells.Select

После, определяем диапазон ячеек на листе, который использует исходная таблица и с которыми будет работать наш макрос. Чтобы определить диапазон таблицы на рабочем листе Excel, мы могли бы использовать свойство UsedRange при создании экземпляра объекта Range в переменной diapaz1. Данное свойство охватывает только непустые ячейки, а это может быть даже несмежный диапазон. Но таблица может содержать пустые ячейки для, которых присвоена проверка ввода значений. Чтобы наш макрос не игнорировал пустые ячейки внутри таблицы мы определяем смежный (неразрывный) диапазон, который начинается с ячейки A1 и заканчивается последней используемой ячейкой на рабочем листе Excel.

Set diapaz1 = Application.Range(ActiveSheet.Range(“A1”), ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))

Последняя ячейка находиться наиболее отдаленно от ячейки A1 (в данном примере – это G15) и была использована на листе (это обязательное условие). При чем использована в прямом смысле, она может даже не содержать значения, но иметь измененный числовой формат, другой цвет фона, другие границы, объединение и т.п. Чтобы найти последнюю используемую ячейку на листе стандартными средствами Excel, выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек».

В появившемся окне следует выбрать опцию «последнюю ячейку». А после просто нажать ОК. Курсор клавиатуры сразу переместиться на последнюю используемую ячейку на рабочем листе Excel.

Можно даже при создании экземпляра объекта Range в переменной diapaz1 использовать диапазон целого листа. Для этого просто замените выше описанную инструкцию на:

Set diapaz1 = Selection

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

Далее в коде макроса перед циклом прописана инструкция для выключения обработки ошибок, выполняемых в коде.

On Error Resume Next

Но после конца цикла обработка ошибок снова включается.

On Error GoTo 0

Внутри цикла проверяться по отдельности все ячейки на наличие включенной проверки вводимых значений инструментом «Проверка данных». Если ячейка содержит проверку вводимых значений?

If IsError(diapaz1(i).Validation.Type) Then

Тогда она присоединяется к диапазону ячеек, находящихся в переменной diapaz2.

Set diapaz2 = Application.Union(diapaz2, diapaz1(i))

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

MsgBox “Найдено: ” & diapaz2.Count & ” ячеек!”

Поиск и подстановка по нескольким условиям

Постановка задачи

Предположим, что у нас есть база данных по ценам товаров за разные месяцы:

Способ 1. Дополнительный столбец с ключом поиска

Плюсы : Простой способ, знакомая функция, работает с любыми данными.

Способ 2. Функция СУММЕСЛИМН

Способ 3. Формула массива

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

Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.

Как это на самом деле работает:

Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.

Поиск и подстановка по нескольким условиям

Постановка задачи

Способ 1. Дополнительный столбец с ключом поиска

Способ 2. Функция СУММЕСЛИМН

Способ 3. Формула массива

Как узнать, что ячейка Эксель содержит латиницу

Особенности использования функции ПОИСКПОЗ в Excel

Как правильно искать в таблицах Excel

5 thoughts on “ «ВПР» по частичному совпадению ”

Как сравнить два столбца в Excel по строкам

Пример 1. Как сравнить два столбца на совпадения и различия в одной строке

Как сделать выборку в Excel по условию

Как создать собственное окно поиска в Excel?

Создайте собственное окно поиска с формулами для вывода всех результатов поиска

Последнее значение в столбце

Макрос поиска ячейки с выпадающим списком

Поиск и подстановка по нескольким условиям

Постановка задачи

Способ 1. Дополнительный столбец с ключом поиска

Способ 2. Функция СУММЕСЛИМН

Способ 3. Формула массива

Похожие записи:

Похожие записи: