Svinkovod.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Поиск значения в столбце и строке таблицы Excel

Поиск значения в столбце и строке таблицы Excel

Имеем таблицу, в которой записаны объемы продаж определенных товаров в разных месяцах. Необходимо в таблице найти данные, а критерием поиска будут заголовки строк и столбцов. Но поиск должен быть выполнен отдельно по диапазону строки или столбца. То есть будет использоваться только один из критериев. Поэтому здесь нельзя применить функцию ИНДЕКС, а нужна специальная формула.

Поиск значений в таблице Excel

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

Лист с таблицей для поиска значений по вертикали и горизонтали:

Отчет объем продаж товаров.

Над самой таблицей расположена строка с результатами. В ячейку B1 водим критерий для поискового запроса, то есть заголовок столбца или название строки. А в ячейке D1 формула поиска должна возвращать результат вычисления соответствующего значения. После чего в ячейке F1 сработает вторая формула, которая уже будет использовать значения ячеек B1 и D1 в качестве критериев для поиска соответствующего месяца.

Поиск значения в строке Excel

Теперь узнаем, в каком максимальном объеме и в каком месяце была максимальная продажа Товара 4.

Чтобы выполнить поиск по столбцам следует:

  1. В ячейку B1 введите значение Товара 4 – название строки, которое выступит в качестве критерия.
  2. В ячейку D1 введите следующую формулу:
  3. Для подтверждения после ввода формулы нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно, в строке формул появятся фигурные скобки. Результат поиска по строкам.
  4. В ячейку F1 введите вторую формулу:
  5. Снова Для подтверждения нажмите комбинацию клавиш CTRL+SHIFT+Enter.

Найдено в каком месяце и какая была наибольшая продажа Товара 4 на протяжении двух кварталов.

Принцип действия формулы поиска значения в строке Excel:

В первом аргументе функции ВПР (Вертикальный ПРосмотр) указывается ссылка на ячейку где находится критерий поиска. Во втором аргументе указывается диапазон ячеек для просмотра в процессе поиска. В третьем аргументе функции ВПР должен указываться номер столбца, из которого следует взять значение на против строки с именем Товар 4. Но так как нам заранее не известен этот номер мы с помощью функции СТОЛБЕЦ создаем массив номеров столбцов для диапазона B4:G15.

Это позволяет функции ВПР собрать целый массив значений. В результате в памяти хранится все соответствующие значения каждому столбцу по строке Товар 4 (а именно: 360; 958; 201; 605; 462; 832). После чего функции МАКС остается только взять из этого массива максимальное число и возвратить в качестве значения для ячейки D1, как результат вычисления формулы.

Как видно конструкция формулы проста и лаконична. На ее основе можно в похожий способ находить для определенного товара и другие показатели. Например, минимальное или среднее значение объема продаж используя для этого функции МИН или СРЗНАЧ. Вам ни что не препятствует, чтобы приведенный этот скелет формулы применить с использованием более сложных функций для реализации максимально комфортного анализа отчета по продажам.

Как получить заголовки столбцов по зачиню одной ячейки?

Например, как эффектно мы отобразили месяц, в котором была максимальная продажа, с помощью второй формулы. Не сложно заметить что во второй формуле мы использовали скелет первой формулы без функции МАКС. Главная структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0). Мы заменили функцию МАКС на ПОИСКПОЗ, которая в первом аргументе использует значение, полученное предыдущей формулой. Оно теперь выступает в качестве критерия для поиска месяца. И в результате функция ПОИСКПОЗ нам возвращает номер столбца 2 где находится максимальное значение объема продаж для товара 4. После чего в работу включается функция ИНДЕКС, которая возвращает значение по номеру сроки и столбца из определенного в ее аргументах диапазона. Так как у нас есть номер столбца 2, а номер строки в диапазоне где хранятся названия месяцев в любые случаи будет 1. Тогда нам осталось функцией ИНДЕКС получить соответственное значение из диапазона B4:G4 – Февраль (второй месяц).

Поиск значения в столбце Excel

Вторым вариантом задачи будет поиск по таблице с использованием названия месяца в качестве критерия. В такие случаи мы должны изменить скелет нашей формулы: функцию ВПР заменить ГПР, а функция СТОЛБЕЦ заменяется на СТРОКА.

Это позволит нам узнать какой объем и какого товара была максимальная продажа в определенный месяц.

Чтобы найти какой товар обладал максимальным объемом продаж в определенном месяце следует:

  1. В ячейку B2 введите название месяца Июнь – это значение будет использовано в качестве поискового критерия.
  2. В ячейку D2 введите формулу:
  3. Для подтверждения после ввода формулы нажмите комбинацию клавиш CTRL+SHIFT+Enter, так как формула будет выполнена в массиве. А в строке формул появятся фигурные скобки. Результат поиска по столбцам.
  4. В ячейку F1 введите вторую формулу:
  5. Снова Для подтверждения нажмите CTRL+SHIFT+Enter.

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

В первом аргументе функции ГПР (Горизонтальный ПРосмотр) указываем ссылку на ячейку с критерием для поиска. Во втором аргументе указана ссылка на просматриваемый диапазон таблицы. Третий аргумент генерирует функция СТРОКА, которая создает в памяти массив номеров строк из 10 элементов. Так как в табличной части у нас находится 10 строк.

Далее функция ГПР поочередно используя каждый номер строки создает массив соответственных значений продаж из таблицы по определенному месяцу (Июню). Далее функции МАКС осталось только выбрать максимальное значение из этого массива.

Читайте так же:
Где в ворде арабские цифры

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

ВНИМАНИЕ! При использовании скелета формулы для других задач всегда обращайте внимание на второй и третий аргумент поисковой функции ГПР. Количество охваченных строк в диапазоне указанного в аргументе, должно совпадать с количеством строк в таблице. А также нумерация должна начинаться со второй строки!

По сути содержимое диапазона нас вообще не интересует, нам нужен просто счетчик строк. То есть изменить аргументы на: СТРОКА(B2:B11) или СТРОКА(С2:С11) – это никак не повлияет на качество формулы. Главное, что в этих диапазонах по 10 строк, как и в таблице. И нумерация начинается со второй строки!

Найти несколько значений в Excel

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

Поиск в Excel по нескольким критериям

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

Пример 1: Поиск по 2-м разным критериям

Предположим, у нас есть список заказов и мы хотим найти Количество товара (Qty.), основываясь на двух критериях – Имя клиента (Customer) и Название продукта (Product). Дело усложняется тем, что каждый из покупателей заказывал несколько видов товаров, как это видно из таблицы ниже:

Руководство по функции ВПР в Excel

Обычная функция ВПР не будет работать по такому сценарию, поскольку она возвратит первое найденное значение, соответствующее заданному искомому значению. Например, если Вы хотите узнать количество товара Sweets, заказанное покупателем Jeremy Hill, запишите вот такую формулу:

Есть простой обходной путь – создать дополнительный столбец, в котором объединить все нужные критерии. В нашем примере это столбцы Имя клиента (Customer) и Название продукта (Product). Не забывайте, что объединенный столбец должен быть всегда крайним левым в диапазоне поиска, поскольку именно левый столбец функция ВПР просматривает при поиске значения.

Итак, Вы добавляете вспомогательный столбец в таблицу и копируете по всем его ячейкам формулу вида: =B2&C2. Если хочется, чтобы строка была более читаемой, можно разделить объединенные значения пробелом: =B2&» «&C2. После этого можно использовать следующую формулу:

=VLOOKUP(«Jeremy Hill Sweets»,$A$7:$D$18,4,FALSE) =ВПР(«Jeremy Hill Sweets»;$A$7:$D$18;4;ЛОЖЬ)

Руководство по функции ВПР в Excel

Пример 2: ВПР по двум критериям с просматриваемой таблицей на другом листе

Если Вам необходимо обновить основную таблицу (Main table), добавив данные из второй таблицы (Lookup table), которая находится на другом листе или в другой рабочей книге Excel, то Вы можете собрать искомое значение непосредственно в формуле, которую вставляете в основную таблицу.

Как и в предыдущем примере, Вам понадобится в таблице поиска (Lookup table) вспомогательный столбец с объединенными значениями. Этот столбец должен быть крайним левым в заданном для поиска диапазоне.

Итак, формула с ВПР может быть такой:

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

Руководство по функции ВПР в Excel

Чтобы формула работала, значения в крайнем левом столбце просматриваемой таблицы должны быть объединены точно так же, как и в критерии поиска. На рисунке выше мы объединили значения и поставили между ними пробел, точно так же необходимо сделать в первом аргументе функции (B2&» «&C2).

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

Извлекаем 2-е, 3-е и т.д. значения, используя ВПР

Вы уже знаете, что ВПР может возвратить только одно совпадающее значение, точнее – первое найденное. Но как быть, если в просматриваемом массиве это значение повторяется несколько раз, и Вы хотите извлечь 2-е или 3-е из них? А что если все значения? Задачка кажется замысловатой, но решение существует!

Предположим, в одном столбце таблицы записаны имена клиентов (Customer Name), а в другом – товары (Product), которые они купили. Попробуем найти 2-й, 3-й и 4-й товары, купленные заданным клиентом.

Простейший способ – добавить вспомогательный столбец перед столбцом Customer Name и заполнить его именами клиентов с номером повторения каждого имени, например, John Doe1, John Doe2 и т.д. Фокус с нумерацией сделаем при помощи функции COUNTIF (СЧЁТЕСЛИ), учитывая, что имена клиентов находятся в столбце B:

После этого Вы можете использовать обычную функцию ВПР, чтобы найти нужный заказ. Например:

    Находим 2-й товар, заказанный покупателем Dan Brown:

Читайте так же:
Где множитель в биосе

=VLOOKUP(«Dan Brown2»,$A$2:$C$16,3,FALSE) =ВПР(«Dan Brown2»;$A$2:$C$16;3;ЛОЖЬ)

=VLOOKUP(«Dan Brown3»,$A$2:$C$16,3,FALSE) =ВПР(«Dan Brown3»;$A$2:$C$16;3;ЛОЖЬ)

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

Руководство по функции ВПР в Excel

Если Вы ищите только 2-е повторение, то можете сделать это без вспомогательного столбца, создав более сложную формулу:

=IFERROR(VLOOKUP($F$2,INDIRECT(«$B$»&(MATCH($F$2,Table4[Customer Name],0)+2)&»:$C16″),2,FALSE),»») =ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ(«$B$»&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&»:$C16″);2;ИСТИНА);»»)

  • $F$2 – ячейка, содержащая имя покупателя (она неизменна, обратите внимание – ссылка абсолютная);
  • $B$ – столбец Customer Name;
  • Table4 – Ваша таблица (на этом месте также может быть обычный диапазон);
  • $C16 – конечная ячейка Вашей таблицы или диапазона.

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

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

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

Как упоминалось выше, ВПР не может извлечь все повторяющиеся значения из просматриваемого диапазона. Чтобы сделать это, Вам потребуется чуть более сложная формула, составленная из нескольких функций Excel, таких как INDEX (ИНДЕКС), SMALL (НАИМЕНЬШИЙ) и ROW (СТРОКА)

Например, формула, представленная ниже, находит все повторения значения из ячейки F2 в диапазоне B2:B16 и возвращает результат из тех же строк в столбце C.

Руководство по функции ВПР в Excel

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

Часть 1:

Результатом функции IF (ЕСЛИ) окажется вот такой горизонтальный массив:

Поиск на листе Excel

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

Поиск перебором значений

Довольно простой в реализации способ. Например, найти в колонке «A» ячейку, содержащую «123» можно примерно так:

Минусами этого так сказать «классического» способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т.п.

Поиск функцией Find

Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую «123» достаточно такого кода:

Вкратце опишу что делают строчки данного кода:
1-я строка: Выбираем в книге лист «Данные»;
2-я строка: Осуществляем поиск значения «123» в колонке «A», результат поиска будет в fcell;
3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае — будет пустой, т.е. Nothing.

Полностью синтаксис оператора поиска выглядит так:

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What — Строка с текстом, который ищем или любой другой тип данных Excel

After — Ячейка, после которой начать поиск. Обратите внимание, что это должна быть именно единичная ячейка, а не диапазон. Поиск начинается после этой ячейки, а не с нее. Поиск в этой ячейке произойдет только когда весь диапазон будет просмотрен и поиск начнется с начала диапазона и до этой ячейки включительно.

LookIn — Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).

LookAt — Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).

SearchOrder — Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)

SearchDirection — Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)

MatchCase — Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)

MatchByte — Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)

SearchFormat — Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.

Чтобы продолжить поиск, можно использовать FindNext (искать «далее») или FindPrevious (искать «назад»).

Примеры поиска функцией Find

Пример 1: Найти в диапазоне «A1:A50» все ячейки с текстом «asd» и поменять их все на «qwe»

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

Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.

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

Пример 3: Продолжение поиска с использованием Find с параметром After.

Читайте так же:
Как вордовский документ перевести в jpg

Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.

Пример 4: Найти все ячейки с шрифтом «курсив» и поменять их формат на обычный (не «курсив»)

Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка — цикл пока результат поиска не будет пустым. 7-я строка — меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

Хочу обратить внимание на то, что в этом примере я не стал использовать «защиту от зацикливания», как в Примерах 2 и 3, т.к. шрифт меняется и после «прохождения» по всем ячейкам, больше не останется ни одной ячейки с курсивом.

Свойство FindFormat можно задавать разными способами, например, так:

Поиск последней заполненной ячейки с помощью Find

Следующий пример — применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.

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

Для поиска функцией Find по маске (шаблону) можно применять символы:
* — для обозначения любого количества любых символов;
? — для обозначения одного любого символа;

— для обозначения символов *, ? и

. (т.е. чтобы искать в тексте вопросительный знак, нужно написать

?, чтобы искать именно звездочку (*), нужно написать

* и наконец, чтобы найти в тексте тильду, необходимо написать

Поиск в скрытых строках и столбцах

Для поиска в скрытых ячейках нужно учитывать лишь один нюанс: поиск нужно осуществлять в формулах, а не в значениях, т.е. нужно использовать LookIn:=xlFormulas

Поиск даты с помощью Find

Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:

  • Тип данных Date в VBA представляется в виде #[месяц]/[день]/[год]#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не «01.03.2018»
  • В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas

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

Пример 7: Найти текущую дату на листе независимо от формата отображения даты.

Пример 8: Найти 1 марта 2018 г.

Искать часть даты — сложнее. Например, чтобы найти все ячейки, где месяц «март», недостаточно искать «03» или «3». Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел — это выбрать формат в котором месяц прописью для ячеек с датами и искать слово «март» в xlValues.

Тем не менее, можно найти, например, 1 марта независимо от года.

Пример 9: Найти 1 марта любого года.

Книги по теме:

Посмотреть все книги по программированию

Комментарии к статье:

10.09.17 ДмитрийОчень толковая и полезная статья. Помогла мне существенно ускорить мой код. Спасибо!
23.11.17 ГостьСпасибо, хорошая статья.
03.12.17 ВладимирСпасибо! Использую в своих проектах.
07.12.17 ЭдСпасибо, очень пригодилась Ваша статья!
19.01.18 Николай.find не ищет значение в ячейке, если ячейка в скрытой строке.
.match позволяет не беспокоится о том, что на листах с источниками данных строка с искомым значением будет скрыта из-за установленного фильтра.
05.02.18 ВладимирБольшое спасибо! Очень толково и понятно.
11.03.18 ГостьЗдравствуйте,
А если мне требуется найти ячейку в определенном столбце с определенным значением, например «строка 1» и если я нахожу такую, то через несколько строк(неизвестно сколько) мне нужно раскрасить следующую ячейку в другом столбце(самую ближайшую) и остановить цикл, и опять по новой искать дальше «строка 1». Можете посоветовать?
26.03.18 ГостьСпасибо! Все бы так описывали! Все доступно и понятно))
23.05.18 АркадийВ VBA я новичок. Активно использую интернет для своих вопросов, однако таких информативных, лаконичных и простых в понимании сайтов не много. Огромное спасибо автору! Адрес уже в закладках.
21.07.18 ГостьСпасибо! Уже несколько недель искала подобное!
25.07.18 JoannМетод .find (в случае далнейших множественных обращений к этому механизму) разумно вынести в отдельную функцию, как из управляющей процедуры передать в эту функцию ее параметры (обязательный текстовый параметр ‘what:=’ — передается без проблем, а вот значения (‘xlValues’, ‘xlWhole’, . ) для ‘LookIn:=’, ‘LookAt:=’, . — приводят к ошибке)?
15.08.18 МаратСпасибо за статью, пополнил свои знания в части метода Find. Простые переборы хороши на небольших диапазонах. А когда нужно обработать сотни тысяч ячеек, Find — хороший инструмент!
28.08.18 ГостьПознавательно и подробно. Спасибо за статью.
16.10.18 ГостьХорошая статья. Спасибо
29.10.18 SegaПолезная статейка. Спасибо.
14.11.18 ГостьСтатья для начинающих, а тонкости поиска по дате нет ни одного примера
02.02.19 ИбрагимЧушь полная, плагиат!
01.03.19 inexsu.wordpress.comLoop While Not c Is Nothing And c.Address <> firstResult
Когда c станет Nothing, c.Address даст ошибку
Решение https://inexsu.wordpress.com/2018/03/05/range-findnext-method/
01.03.19 АдминистраторВы правы, такое действительно может произойти, например, если менять значения найденных ячеек. Т.е., например, ищем значения «asd» и меняем их на «qwe». При замене последнего значения, FindNext ничего уже не найдет, вернет Nothing и произойдет ошибка в условии.
Внес изменения в статье. Спасибо вам за подсказку.
07.04.19 ГостьДобрый день. есть 2 таблица на разных листах. макрос нашел нужные ячейки с ИНН по 1 условию, затем мне надо чтобы макрос нашел в таблице 2 на листе 2 все названия клиентов с инн по условию 1 из таблицы 1 с листа 1. как тогда использовать find.
07.04.19 ГостьЛучше использовать функцию ВПР
21.05.19 ГостьОчень подробная статья. Отдельное спасибо за множество примеров.
17.10.19 МихаилСпасибо большое все понятно доступно и очень полезно
31.10.19 ГостьСпасибо.
01.11.19 Гостьку
01.11.19 Гость2 раза
19.11.19 KamolОтлично.
Поиск с двумя значениями Пример в колонке А="123" и В="456"
25.12.19 ГостьСпасибо!
05.03.20 ГостьДобрый день!
Спасибо за код автору
Вопрос

Как сделать поиск слова по, но с любым регистром и любым набором символов
Пример: Я ввожу слово через Textbox
В таблице есть слово компьютер, колодец, ком
Нужно, чтобы программа находила по запросу "омпьюте", "пьюте" слово компьютер а по запросу "ком" слова компьютер и ком"

Выполняем поиск в Excel с помощью функции ВПР

Элен Брэдли объясняет тонкости работы с функцией ВПР в Microsoft Excel при поиске данных в таблице.

Когда необходимо найти информацию в таблице, функции поиска Excel помогут Вам справиться с этой задачей. В более ранних версиях Excel существовал Мастер подстановок, с помощью которого настроить поиск было достаточно просто, но в Excel 2010 его уже нет. Теперь, если Вам требуется формула поиска, придется создавать её вручную. В этой статье я покажу Вам, как обуздать мощь функций поиска в таблицах Excel.

Основы

В Microsoft Excel есть несколько функций поиска, среди них VLOOKUP (ВПР), очень похожая на нее функция HLOOKUP (ГПР) и LOOKUP (ПРОСМОТР). Функция ВПР используется для того, чтобы искать данные в таблице. Она выполняет поиск искомого значения по первому столбцу таблицы и возвращает соответствующее значение из другого столбца.

Когда данные расположены по-другому, используйте ГПР, чтобы найти нужное значение в верхней строке таблицы и возвратить соответствующее значение из заданной строки, расположенной ниже. Функция ПРОСМОТР имеет две формы – векторную и массива, и может возвращать значение из одного столбца, одной строки или из массива (аналог ВПР и ГПР). Из этих трёх функций, вероятнее всего, Вы будете использовать ВПР гораздо чаще, чем остальные. Именно на ней я сфокусируюсь в этой статье. В целом, если Вы поймете и сможете применить функцию ВПР, то сможете справиться и с ГПР.

Синтаксис функции ВПР

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

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

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

Итак, для примера, в этой таблице представлены значения веса в фунтах (Lbs weight), а также стоимость обработки и перевозки. Мы можем использовать функцию ВПР, чтобы найти значение веса и определить стоимость обработки (Handling) и перевозки (Shipping) партии товара такого веса. Конечно же, вес большинства партий товара не будет иметь такие же ровные значения, поэтому мы используем в качестве последнего аргумента TRUE (ИСТИНА), либо вовсе не указываем его. В таком случае наша формула найдёт результат, даже без точного совпадения. Не забываем сделать сортировку таблицы, чтобы данные в первом столбце располагались в порядке возрастания.

ВПР в действии

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

Чтобы для значения из ячейки B11, которое является весом партии товара в фунтах (в данном случае 1.5 фунта), возвратить стоимость обработки из столбца Handling, воспользуемся вот такой формулой:

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

Поиск в Excel и функция ВПР

Если Вы хотите скопировать формулу вниз по столбцу, не забудьте указать в ней абсолютные ссылки вот таким образом:

Кроме этого, Вы можете присвоить своей таблице имя, для этого надо выделить ячейки от D1 до F7 и нажать Formulas (Формулы) > Define Name (Присвоить имя), затем ввести имя диапазона и нажать ОК. В нашем примере это имя shipping_and_handling.

Поиск в Excel и функция ВПР

Теперь при создании формулы Вы можете использовать имя диапазона. Вот здесь вместо адреса диапазона указано его имя:

=VLOOKUP(B12,shipping_and_handling,2)
=ВПР(B12; shipping_and_handling;2)

Поиск в Excel и функция ВПР

Мы можем адаптировать формулу из столбца Handling для расчёта значений в столбце Shipping. В данном случае поменяется только номер столбца. Для Shipping – это значение 3:

Функция ГПР работает точно таким же образом. Точнее говоря, она также использует искомое значение и диапазон данных, но вместо номера столбца Вы задаете ей номер строки. Строки нумеруются 1, 2, 3 и так далее, где 1 – самая первая строка таблицы.

Используя предыдущий пример, мы сможем найти соответствия для весов в 11, 12 или 25 фунтов, хоть они и отсутствуют в таблице. Возможность находить ближайшее значение, которое меньше искомого, выглядит очень привлекательной. Однако, существуют некоторые оговорки при использовании этой формулы. Одна из них – стартовое значение в таблице должно быть равно , что собственно и сделано у нас. Это позволяет исключить ошибки, когда используется вес, к примеру, меньше 1 фунта.

Поиск в Excel и функция ВПР

Работа с точными совпадениями

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

Поиск в Excel и функция ВПР

В данном примере мы ищем название в столбце A и возвращаем цену из столбца 2 или 3, в зависимости от указанного размера ковра – средний (M) или большой (L). В этой ситуации нам необходимо использовать функцию IF (ЕСЛИ), чтобы определить, какой номер столбца нужно использовать. Формула поиска будет выглядеть следующим образом:

В данном случае мы ищем название ковра в столбце A и возвращаем цену из столбца B или C, в зависимости от выбранного размера ковра. Если точное совпадение не найдено, т.е. название ковра в заказе не соответствует ни одному из названий в столбце A, тогда будет возвращено сообщение об ошибке #N/A (#Н/Д). Функция IF (ЕСЛИ) составлена так, что если заданный размер ковра не будет соответствовать одному из двух имеющихся вариантов, то по умолчанию будет взят большой размер (L).

Поиск в Excel и функция ВПР

Используем проверку данных

На практике желательно сделать так, чтобы пользователь гарантированно вводил правильное название ковра и его размер. Вы можете реализовать это, используя выпадающий список. Для этого выделите ячейки, в которые пользователь будет вводить свои заказы, например, столбец A или B. Перейдите Data > Data Validation > Data Validation (Данные > Проверка данных > Проверка данных). В появившемся диалоговом окне на вкладке Settings (Параметры) в поле Allow (Тип данных) выберите значение List (Список). Кликните в поле Source (Источник) и выделите ячейки от A2 до A4, в которых содержится список с названиями ковров. Нажмите ОК.

Поиск в Excel и функция ВПР

Таким же способом Вы можете создать выпадающий список для ввода размеров L или M, используя в качестве источника данных диапазон B1:C1.

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

Сортируем данные

Если Вы работаете с приблизительными совпадениями, то должны выполнить сортировку в таблице. Для этого выделите весь диапазон с данными, включая заголовки строк в первом столбце. Заголовки столбцов (шапку) можно не выделять. На вкладке Data (Данные) нажмите команду Sort (Сортировка), откроется одноименное диалоговое окно.

В строке Sort By (Сортировать по) укажите параметры сортировки. В первом выпадающем списке выберите столбец, по которому необходимо выполнить сортировку, в нашем случае это первый столбец таблицы. Во втором выберите Values (Значения), а в третьем укажите порядок сортировки по возрастанию. Если вместе с данными, Вы выделили шапку таблицы, не забудьте поставить галочку My data has headers (Мои данные содержат заголовки). Нажмите ОК.

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector