Cd-m.ru

ПК Журнал СД-М
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция поиска в Excel

Функция поиска в Excel

Функция поиска в Excel

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

Как сделать поиск в Экселе, найти слово, ячейку, текст

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

Метод 1 — Простой поиск

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

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

Функция поиска в Excel

После этого у вас откроется новое окно. В нем в строке «Найти» введем то сочетание слов, которое нам необходимо найти. После введения нажмем «Найти все» либо «Найти далее».

Функция поиска в Excel

Если нажать «Найти далее», то поиск перейдет на первую строку где есть совпадение. Строка в этот момент станет активной.

Данная поисковая система работает таким образом, что при запросе, она начинает проверять первую строку на наличие совпадений. И если в первой строке ничего нет, система начинает проверять следующую строчку, до момента пока не найдет указанное сочетание.

На самом деле не обязательно вводить конкретное слово. Вы можете ввести слово «лев» и система будет искать все слова, которые содержит данное сочетание букв, например «наЛЕВо».

Или ввести цифру 7, то система начнет выдавать все сочетания цифр, которые содержат указанную цифру, например «217».

Если вам нужен не первый выданный результат, нажмите «Найти далее».

Функция поиска в Excel

Вы можете искать, таким образом, пока система не начнет повторяться по второму кругу.

Также вы можете найти все совпадения в виде небольшого списка. Для этого в строке поиска необходимо нажать «Найти все». Система выдаст вам все данные в виде списка, в котором будут указаны адреса расположения, лист и книга, в которых они находятся.

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

Функция поиска в Excel

Метод 2 — Расширенный поиск

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

То есть под поиск попадает не только содержимое ячейки, но и адрес элемента, к которому она непосредственно относится. Вот к примеру, у нас имеется формула которая и находится в ячейке Е2, данная формула является суммой ячеек А4 и С3, сумма равна 10.

И если мы в поиске зададим цифру 4, то поиск выдаст нам ячейку Е2, все почему? Потому что в этой ячейке находится адрес на ячейку А4. А данная ячейка содержит цифру 4.

Функция поиска в Excel

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

Функция поиска в Excel

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

Функция поиска в Excel

В простом поиске функции «Учитывать регистр» и «Ячейки целиком» отключены, и если мы их включим, то при поиске вам будут выдаваться точные совпадения, и при поиске будет учитываться регистр.

И если в поиске вы будите вводить данные с маленькой буквы, то поиск уже не выдаст те ячейки, данные которых написаны с большой буквы, как это было в простом поиске.

А если вы включите функцию «Ячейки полностью», то при поиске вы сможете получить только те данные наименование которых абсолютно одинаково. К примеру, если мы введем в поиске «Василий», то ячейка, которая названа «Василий Л.И» в результат поиска уже не попадет.

Функция поиска в Excel

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

Функция поиска в Excel

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

Функция поиска в Excel

Графа под названием «Область поиска», определяет по каким данным, будет происходить поиск.

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

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

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

Функция поиска в Excel

Более точно настроить строку поиска можно нажав на «Формат».

Функция поиска в Excel

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

Читать еще:  Как обновить Телеграмм на Андроиде

Функция поиска в Excel

Если вам необходимо использовать формат какой – либо определенной ячейки, то внизу окна нажмите на функцию «Использовать формат этой ячейки».

Функция поиска в Excel

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

Функция поиска в Excel

Когда вы произвели необходимую вам настройку, нажмите «ОK».

Функция поиска в Excel

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

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

Функция поиска в Excel

После всех произведенных настроек, вы можете вводить данные, которые вы хотите найти и жать «Найти все», или «Найти далее».

Функция поиска в Excel

Метод 3 — Поиск по указанному интервалу ячеек

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

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

Функция поиска в Excel

При помощи горячих клавиш Ctrl+F откроем окно поиска. И дальше мы также вводим поисковые слова, и жмем «Найти все» либо «Найти далее». И поиск будет происходить среди выделенных ячеек.

Функция поиска в Excel

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

Как сделать поиск в Excel

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

Что необходимо сделать

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

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

Примеры ВРОТ

Пример 1 функции ВПР

Пример 2 функции ВПР

Дополнительные сведения см. в этой информации.

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

Функции ИНДЕКС и ПОИСКПОЗ можно использовать вместо функции ВПР

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

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

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

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

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

Пример формулы ВЛП, которая ищет приблизительное совпадение

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

Дополнительные сведения см. в этой информации.

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

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

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

Пример функций OFFSET и MATCH

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 и щелкните категорию Надстройки.

Читать еще:  Как получить права Администратора в Виндовс 10

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

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

Найти несколько значений в 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 (ИНДЕКС), которая с легкостью справится с этой задачей. Как будет выглядеть такая формула, Вы узнаете в следующем примере.

Читать еще:  FotoMix — обзор бесплатного приложения для создания коллажей

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

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

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

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

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

Часть 1:

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

Динамический поиск и фильтрация в Excel

В настоящее время я экспериментирую с динамическими панелями поиска и фильтрацией в Excel. Я нашел хороший учебник, который использует таблицу и текстовое поле Active-X. Я воссоздал его, и, конечно, он работает. Однако моя проблема заключается в том, что он учитывает только один столбец. Теперь я создал еще одну таблицу и еще одну строку поиска, думая, что она будет работать так же хорошо, однако по какой-то причине этого не происходит. Вот в чем проблема: Набор данных выглядит следующим образом; enter image description here Поиск ячеек, содержащих «1» в «addr1» (работает нормально): enter image description here Поиск ячеек, содержащих «1» в «addr2» (не работает): enter image description here

Я попытался установить формат на текст, общий, номер и т. д., Но, похоже, ничего не дало. Во втором поиске также должны быть перечислены ячейки, содержащие «123», но это не так.

VBA код в листе:

У кого-нибудь есть какое-нибудь решение?

Спасибо Вам за любую обратную связь!

3 ответа

  • Excel сравнение и фильтрация

На этот раз довольно сложная задача VBA. У меня есть рабочая тетрадь excel с двумя листами; данные и анализ. Лист данных содержит столбец (T) с именами и столбец с результатами (Y). Лист анализа содержит ячейку с именем (B1). Я хочу, чтобы поиск в листе столбец данных имя столбца по анализу лист.

Я создаю динамический объект. Я назначаю значения через IDictionary. Добавьте коллекции IDictionary к объекту. Затем я добавляю динамический объект в эластичный поиск, используя код NEST. Это выбрасывает мне исключение stackoverflow.Необработанное исключение типа ‘ System.StackOverflowException ‘.

Вы должны изменить Field:=1 на Field:=2 , в противном случае вы будете искать содержимое в столбце TextBox2_Change в столбце addr1 .

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

enter image description here

enter image description here

Проблема, с которой вы столкнулись сейчас, заключается в том, что фильтр «*1*» будет соответствовать только строкам, а не числам. Даже если вы отформатируете свои ячейки как Text , уже введенные числа все равно будут проанализированы как числа; они не будут преобразованы в строки. У вас есть два способа ввести число в виде строки:

введите его в ячейку после того, как вы отформатировали его как Text

введите его перед некоторым ‘ (одинарная кавычка). т. е. ‘123 . Это заставит Excel принять его как строку, даже если формат ячейки равен General .

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

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

Похожие вопросы:

У меня есть список продуктов excel лист и список категорий excel лист. В категории excel есть cat_id и cat_name, один соответствует другому. Мне нужно сделать поиск и замену на каждом cat_id и.

Если значение ячейки, например, равно 6, то как вывести динамический вывод, как показано в excel: 6 A1

я должен написать модуль поиска и экспорта в excel с php. Но если данные большие, то они очень медленные. Я думаю, что создам кнопку Скачать excel после того, как пользователь выполнит поиск,я не.

На этот раз довольно сложная задача VBA. У меня есть рабочая тетрадь excel с двумя листами; данные и анализ. Лист данных содержит столбец (T) с именами и столбец с результатами (Y). Лист анализа.

Я создаю динамический объект. Я назначаю значения через IDictionary. Добавьте коллекции IDictionary к объекту. Затем я добавляю динамический объект в эластичный поиск, используя код NEST. Это.

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

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

Кто-нибудь может помочь мне со статьей Томаса Petricek: http://tomasp.net/blog/fsharp-dynamic-lookup.aspx/#dynfslinks ? Проблема в том, что он сильно устарел. Я понимаю, что пространства имен open.

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

Я пытаюсь найти динамический процентиль в списке (или вы можете думать о нем как о функции LARGE в Excel). Ниже приводится представление о том, на что я смотрю. (Пожалуйста, простите мне.

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