Как найти в таблице нужного человека

Функции поиска данных: разыскивается информация!

Привет, друзья. Как часто вам приходится для какого-то значения искать соответствие в таблице Эксель? Например, нужно в справочнике найти адрес человека, или в прайсе – цену товара. Если такие задачи встречаются – этот пост именно для вас!

Я выполняю подобные процедуры каждый день и без описанных ниже функций мне действительно пришлось бы туго. Берите на заметку и применяйте их в работе!

Поиск в таблице Эксель, функции ВПР и ГПР

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

Синтаксис функции ВПР такой: =ВПР( Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]) . Рассмотрим аргументы:

• Искомое значение – значение, которое будем искать. Это обязательный аргумент;
• Таблица для поиска – тот массив ячеек, в котором будет поиск. Столбец с искомыми значениями должен быть первым в этом массиве. Это тоже обязательный аргумент;
• Номер выводимого столбца – порядковый номер столбца (начиная с первого в массиве), из которого функция выведет данные при совпадении искомых значений. Обязательный аргумент;
• Тип сопоставления – выберите «1» (или «ИСТИНА») для нестрогого совпадения, «0» («ЛОЖЬ») – для полного совпадения. Аргумент необязателен, если его упустить – будет выполнен поиск нестрогого совпадения.

Поиск точного совпадения с помощью ВПР

Посмотрим на примере, как работает функция ВПР, когда выбран тип сопоставления «ЛОЖЬ», поиск точного совпадения. В массиве В5:Е10 указаны основные средства некой компании, их балансовая стоимость, инвентарный номер и место расположения. В ячейке В2 указано наименование, для которого нужно в таблице найти инвентарный номер и поместить его в ячейку С2 .

Функция ВПР в Excel

Запишем формулу: =ВПР(B2;B5:E10;3;ЛОЖЬ) .

Здесь первый аргумент указывает, что в таблице нужно искать значение из ячейки В2 , т.е. слово «Факс». Второй аргумент говорит, что таблица для поиска — в диапазоне В5:Е10 , а искать слово «Факс» нужно в первом столбце, т.е. в массиве В5:В10 . Третий аргумент сообщает программе, что результат расчета содержится в третьем столбце массива, т.е. D5:D10 . Четвёртый аргумент равен «ЛОЖЬ», т.е. требуется полное совпадение.

И так, функция получит строку «Факс» из ячейки В2 и будет искать его в массиве В5:В10 сверху вниз. Как только совпадение будет найдено (строка 8), функция вернёт соответствующее значение из столбца D , т.е. содержимое D8 . Именно это нам и требовалось, задача решена.

Если искомое значение не будет найдено, функция вернёт ошибку #Н/Д.

Поиск неточного совпадения с помощью ВПР

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

В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА) . Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

Нестрогий поиск ВПР в Excel

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

Функция ГПР имеет такой же синтаксис, как и ВПР, но ищет результат не в столбцах, а в строках. То есть, просматривает таблицы не сверху вниз, а слева направо и выводит заданный номер строки, а не столбца.

Поиск данных с помощью функции ПРОСМОТР

Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения) :

• Искомое значение – данные или ссылка на данные, которые нужно искать;
• Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
• Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.

При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10) . Задача успешно решена!

Функция «ПРОСМОТР» в Microsoft Excel

Поиск по относительным координатам. Функции ПОИСКПОЗ и ИНДЕКС

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления]). Аргументы функции:

• Искомое значение – обязательный аргумент
• Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
• Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.

Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; [Номер_столбца]) :

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

Теперь скомбинируем эти функции, чтобы получить результат:

Функции ПОИСКПОЗ и ИНДЕКС в Эксель

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

Чувствуете, как растут и крепчают Ваши знания и умения? Тогда не останавливайтесь, продолжайте читать Officelegko.com! В следующем посте мы будем рассматривать расчеты кредита в Эксель: будет сложно и интересно!

Добавить комментарий Отменить ответ

4 комментариев

А как быть, если в поле «Наименование» вперемешку числовые и текстовые значения? У меня эксель числовые значения игнорирует, т.к. они числятся у него, как текстовые. К слову сказать, в таблице 56 тыс. записей и вручную всё пройти довольно сложно.

Не понял Вашего вопроса. Судя по всему, у Вас все данные хранятся, как текст. А что не работает?

А если нужно в просматриваемом массиве найти несколько однотипных искомых значений, а над возвращаемыми данными произвести арифметические вычисления (найти среднеарифметическое значение)?

Здравствуйте. Смотря что Вы называете однотипными данными. Опишите, пожалуйста, подробнее.

Поиск значений в таблице «Excel» . Макрос.

Как выполнить поиск значений в программе «Excel» .

Далее описаны несколько вариантов поиска и фильтрации данных в таблице «Эксель».

Классический поиск «MS Office».

Условное форматирование (выделение нужных ячеек цветом)

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

Фрагмент макроса для перебора ячеек в диапазоне и поиска нужного значения.

1) Классический поиск (обыкновенный).

Вызвать панель (меню) поиска можно сочетанием горячих клавиш ctrl+F. (Легко запомнить: F- Found).

Окно поиска состоит из поля, в которое вводится искомый фрагмент текста или искомое число, вкладки с дополнительными настройками («Параметры») и кнопки «Найти».

Классический поиск в Excel

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

Условное форматирование для искомых ячеек.

2) Еще один вариант поиска заключается в выделении всех ячеек содержащих искомое слово каким-нибудь цветом.

Такой поиск реализуется через опцию условное форматирование.

Для форматирования ячеек следует выделить диапазон ячеек, которых нужно найти слово. Далее на вкладке «Главная» нажать кнопку «Условное форматирование».

Настройка форматирования для выделения искомых слов

В меню условного форматирования выбрать «Правила выделения ячеек» … «Текст содержит…».

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

3) Третий способ поиска слов в таблице «Excel» — это использование фильтров.

Фильтр устанавливается во вкладке «Данные» или сочетанием клавиш ctrl+shift+L. Настройка фильтра для поиска слов

Кликнув по треугольнику фильтра можно в контекстном меню выбрать пункт «Текстовые фильтры», далее «содержит…» и указать искомое слово.

После нажатия кнопки «Ок» на Экране останутся только ячейки столбца, содержащие искомое слово.

4) Способ поиска номер четыре — это макрос VBA для поиска (перебора значений).

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

Sub Poisk()

‘ ruexcel.ru макрос проверки значений (поиска)

Dim keyword As String

keyword = «Искомое слово» ‘присвоить переменной искомое слово

On Error Resume Next ‘при ошибке пропустить

For Each cell In Selection ‘для всх ячеек в выделении (выделенном диапазоне)

If cell.Value = «» Then GoTo Line1 ‘если ячейка пустая перейти на «Line1″

If InStr(StrConv(cell.Value, vbLowerCase), keyword) > 0 Then cell.Interior.Color = vbRed ‘если в ячейке содержится слово окрасить ее в красный цвет (поиск)

4 способа поиска данных в таблице Excel

Добрый день уважаемый читатель!

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

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

Теперь на примерах рассмотрим все 4 способа поиска данных в таблице Excel и комбинаций работы функции ВПР с другими функциями:

Используем функцию СУММПРОИЗВ

Как я уже описывал ранее в своей статье о функции СУММПРОИЗВ, она является одной из мощнейших в арсенале Excel. И именно первый способ мы сделаем с помощью возможностей формулы при использовании функции СУММПРОИЗВ. Для наших целей формула будет выглядеть так:

=СУММПРОИЗВ((C2:C11=G2)*(B2:B11=G3);D2:D11) Принцип работы формулы следующий: создается условная таблица, в которой значения ячеек «G2» сравнивается с диапазоном «C2:C11» и ячейка «G3» с диапазоном «B2:B11». После этого сравниваются и сопоставляются все эти два массива и переводятся в единицы и нули, где значение единицы ставится строке, где все условия формулы выполнены. Следующая операция – это умножения полученного условного массива на диапазон «D2:D11», а поскольку в массиве всего одна единичка то формула получит результат 146.

Обращаю ваше внимание, если в диапазоне «D2:D11» будут найдены текстовые значения, формула откажется работать. Для более углублённого ознакомления с функцией СУММПРОИЗВ советую почитать мою статью.

Применение функции ВЫБОР

Я описывал уже функцию ВЫБОР, но в таком исполнении еще не упоминал. В нашем случае нужно создать новую таблицу, в которой будут совместными столбики «Период» и «Месяц», всё это виртуально создаст функция ВЫБОР. Формула для работы будет выглядеть так:

<=ВПР(G2&G3;ВЫБОР(<1;2>;C2:C11&B2:B11;D2:D11);2;0)> Основная работа, которую проделывает функция ВЫБОР в своей части «ВЫБОР(<1;2>;C2:C11&B2:B11;D2:D11)» это объединение значений столбиков «Период» и «Город» в общий массив, значения в котором будут прописаны как: «МоскваЯнварь», «БрянскФевраль», …. и т.д. Получив такое объединённое значения столбиков мы сможем легко сделать просмотр и отбор нужного значения, вот теперь я думаю, формула стала ближе.

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

Создаем дополнительные столбики

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

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

=ВПР(H2&H3;D2:E11;2;0)

Как видите, наши отдельные условия отбора значений также объединяются аргументом H2&H3 в один критерий. После поиска в указанном диапазоне D2:E11, формула вернёт найденное значение со столбика 2.

Совмещаем функции ПОИСКПОЗ и ИНДЕКС для работы

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

А для нашего поиска данных в таблице Excel будем использовать такую формулу:

Что же она делает, такая большая и непонятная…. Рассмотрим ее в разрезе нескольких блоков или этапов. Формула для функции имеет такой вид ПОИСКПОЗ (1;(B2:B11=G3)*(C2:C11=G2);0) и происходит следующее, со значением в ячейке G3, последовательно сравниваются значения из диапазона B2:B11 и в случае совпадения условий получаем результат ИСТИНА, а если есть отличия получаем ЛОЖЬ. Такой же процесс происходит для значения G2 и диапазона C2:C11. После сравнения этих массивов, которые состоят из аргументов ИСТИНА и ЛОЖЬ, производится сравнения на соответствие значению 1, это ИСТИНА*ИСТИНА, все остальные комбинации будут проигнорированы.

Теперь, когда функция ПОИСКПОЗ нашла в массиве значение, которое соответствует «1» и указала его позицию в шестой строке, а значит, в функцию ИНДЕКС был передан аргумент «6» для диапазона D2:D11.

Ну, подведя итог можно ответить на закономерный вопрос: «а что же делать?» и «какой способ использовать?». Использовать вы можете абсолютно любой способ, но я бы рекомендовал выбрать вам наиболее удобный, простой и понятный. Я, к примеру, люблю использовать таблицы, которые просто изменять и просты для работы и понимания, чего советую и вам.

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

Microsoft Excel

трюки • приёмы • решения

Как правильно искать в таблицах 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 нашел и убрал все звездочки на листе.

Поиск значений в списке данных

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

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

В этой статье

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

Для выполнения этой задачи можно использовать функцию ВПР или сочетание функций индекс и ПОИСКПОЗ.

Примеры использования функции ВПР

Дополнительные сведения можно найти в разделе функция ВПР.

Примеры ИНДЕКСов и СОВПАДЕНИй

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

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

Дополнительные сведения можно найти в разделе Функция индекс и функция ПОИСКПОЗ.

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

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

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

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

Дополнительные сведения можно найти в разделе функция ВПР.

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

Для выполнения этой задачи используйте функции СМЕЩ и ПОИСКПОЗ.

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

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

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

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

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

Для выполнения этой задачи используется функция ГПР. Ниже приведен пример.

Функция ГПР выполняет поиск по столбцу Sales и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения можно найти в разделе функции ГПР.

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

Для выполнения этой задачи используется функция ГПР.

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

В приведенном выше примере функция ГПР ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11000 и, следовательно, ищет следующее наибольшее значение, которое меньше 1100 и возвращает число 10543.

Дополнительные сведения можно найти в разделе функции ГПР.

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

Примечание: Надстройка «Мастер подстановок» прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками).

В Excel 2007 мастер подстановок создает формулу подстановки на основе данных листа, имеющих заголовки строк и столбцов. Мастер подстановок помогает находить другие значения в строке, когда вы знаете значение в одном столбце, и наоборот. Мастер подстановок использует индекс и СОВПАДЕНИе в создаваемых формулах.

Щелкните ячейку в диапазоне.

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

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

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

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

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

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

Поиск в программе Microsoft Excel

​Смотрите также​ для главной функции​ поиск одновременно по​ строке прописываем функцию.​Для отчета нужно выбрать​ не значение. Функция​В таблице в​ ячейка F4.​ таблицы.​ ГПР.​Дополнительные сведения см. в​ значений в списке​После этого, появляется инструмент​ поиска. По умолчанию,​ A4, который как​ кнопкой мыши. После​«Найти всё»​

​В документах Microsoft Excel,​ ВПР (первой). Сначала​

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

​ нескольким таблицам, то​=НАЙТИ(«de»;D2;1)​ информацию о том,​ состоит из двух​ столбце скидки должны​На закладке «Главная»​У нас есть​К началу страницы​ разделе, посвященном функции​

Способ 1: простой поиск

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

1. ​После нажатия клавиши Enter​​ сколько дней отработали​​ частей: имени и​​ стоять по возрастанию.​​ в разделе «Библиотека​ таблица с перечнем​Примечание:​​ ВПР.​​ результатов.​ С помощью него​​ выше, поиск ведется​​ себя искомую цифру​ на ту ячейку​При нажатии на кнопку​ большого количества полей,​​ ищет по первой​​ препятствия. А слаживать​

   

2. ​ появляется номер места​ и какую оплату​ аргумента.​ Можно установить сортировку.​ функций» выбираем «Ссылки​ товара, ценой, наличием​​ Поддержка надстройки «Мастер подстановок»​​К началу страницы​​Поиск значений в списке​​ можно выделить ту​ по порядку построчно.​​ 4.​​ Excel, по записи​«Найти далее»​ часто требуется найти​ таблице количество времени​ все данные в​​ заданных букв в​​ получили специалисты, которые​​Каждое название функции в​​ Смотрите статью «Фильтр​

   

3. ​ и массивы» и​​ его на складе.​​ в Excel 2010​Для выполнения этой задачи​ по вертикали по​ ячейку, формат которой​ Переставив переключатель в​

   ​Но, как отсечь такие,​ которой пользователь сделал​мы перемещаемся к​ определенные данные, наименование​ необходимое для производства​ одну таблицу –​ артикуле товаров. Протянув​ работают во вредных​ Excel описывает ее​ в Excel».​ в ней выбираем​

   ​ В другой таблице​ прекращена. Эта надстройка​ используются функции СМЕЩ​ точному совпадению​ вы собираетесь использовать.​ позицию​ и другие заведомо​ щелчок.​ первой же ячейке,​ строки, и т.д.​ 20 штук продукта​ это сложно, иногда​ за нижний угол​ условиях, связанных с​ операцию и назначение.​Также устанавливаем функцию​ функцию «ВПР».​ указаны оптовые скидки.​ была заменена мастером​ и ПОИСКПОЗ.​

   ​Поиск значений в списке​После того, как формат​«По столбцам»​​ неприемлемые результаты выдачи​​Если у вас довольно​

   

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

4. ​ задымленностью.​ В данном случае​ ВПР в ячейке​Нажимаем «ОК». В появившемся​​ Нам нужно быстро​​ функций и функциями​Примечание:​ по вертикали по​ поиска настроен, жмем​, можно задать порядок​ поиска? Именно для​ масштабная таблица, то​ группы символов. Сама​ приходится просматривать огромное​ значения для ячейки​ На конкретном примере​ вы получите аналогичные​Штатное расписание составлено особым​ это слово «НАЙТИ».​ F6. Но, в​ окне пишем:​ найти всю информацию​ для работы со​ Данный метод целесообразно использовать​ приблизительному совпадению​ на кнопку​ формирования результатов выдачи,​ этих целей существует​ в таком случае​

Способ 2: поиск по указанному интервалу ячеек

​ ячейка становится активной.​ количество строк, чтобы​ E6 (которое потом​ продемонстрируем правильное решение​ показатели по всем​ образом. Номера рабочих​Аргументов может быть несколько.​ строке «Таблица» указываем​В строке «Искомое_значение»​ об этом товаре.​ ссылками и массивами.​ при поиске данных​Поиск значений по вертикали​«OK»​

1. ​ начиная с первого​ расширенный поиск Excel.​ не всегда удобно​

   

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

Способ 3: Расширенный поиск

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

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

​Для наглядного примера создадим​ #ЗНАЧ!, нет заданных​ пометки можно понять,​ заключаются в круглые​ потому что могут​ который ищем. В​

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

   

2. ​ жмем на кнопку​ может оказаться огромное​ данные отвечающие условию​ Microsoft Excel. Давайте​ аргумента главной функции.​ три простые отдельные​ букв. Остается выполнить​ с какими вредными​ скобки. Если аргументов​

   

   ​ взять не ровное​​ примере – это​​ указывать диапазон этой​​ названия строк и​​ неизвестно, сколько строк​ по горизонтали по​ а найти ячейки,​ конкретно элементов производится​«Параметры»​ количество результатов, которые​ найдены не были,​ разберемся, как он​В результате поиска третьей​ таблицы расположенных в​ автофильтр, поиск по​ факторами сталкивается рабочий.​ больше одного, между​ количество товара.​ ячейка F2 .​ таблицы (прайса). Можно​ столбцов. С помощью​ данных возвратит сервер,​ точному совпадению​​ в которых находятся​​ поиск. По умолчанию,​.​ в конкретном случае​ программа начинает искать​ работает, и как​ функции мы получаем​ несмежных диапазонах одного​ которым дал результат​ Нам нужно отсортировать​ ними ставится знак​

   

   ​Нажимаем «ОК». Теперь, когда​В строке «Таблица»​ присвоить имя диапазону.​ мастера подстановок можно​​ а первый столбец​​Поиск значений в списке​ поисковые слова в​​ это формулы, то​​В окне появляется целый​ не нужны. Существует​ во второй строке,​

   

   ​ им пользоваться.​​ значение 125, которое​​ листа:​ 1.​ строки, штатные номера​ «;». Для поиска​ в ячейке F3​ пишем имя диапазона​ Выделяем всю таблицу​​ найти остальные значения​​ не отсортирован в​ по горизонтали по​ любом порядке, даже,​ есть те данные,​

   

   ​ ряд дополнительных инструментов​​ способ ограничить поисковое​​ и так далее,​Скачать последнюю версию​ является первым аргументом​Следует выполнить поиск суммы​Выборка товаров готова.​ которых имеют пометку​ необходимо использовать следующие​ напишем количество покупаемого​ таблицы прайса. У​ (кроме шапки) и​ в строке, если​ алфавитном порядке.​ приблизительному совпадению​ если их разделяют​ которые при клике​ для управления поиском.​ пространство только определенным​ пока не отыщет​ Excel​ для второй функции.​ необходимой для производства​Пример 3. В таблице​ «!» на втором​ аргументы.​ товара, в ячейке​​ нас, в примере,​​ присваиваем этому диапазону​​ известно значение в​​C1​Создание формулы подстановки с​ другие слова и​ по ячейке отображаются​ По умолчанию все​​ диапазоном ячеек.​​ удовлетворительный результат.​

   

   ​Поисковая функция в программе​ Получив все параметры,​ 20-ти штук продуктов.​​ 5 строк. В​​ месте. Например, 3!7884,​

   

   ​Искомый текст. Сочетание знаков,​ F6 появится цифра​ — «Продукты». Можно​ имя, например «Продукты».​ одном столбце, и​ — это левая верхняя​ помощью мастера подстановок​ символы. Тогда данные​ в строке формул.​ эти инструменты находятся​Выделяем область ячеек, в​Поисковые символы не обязательно​ Microsoft Excel предлагает​

   

   ​ вторая функция ищет​ К сожалению, эти​ нее введены математические​ 8!6453 или 5!54.​ которые мы разыскиваем​​ скидки в процентах.​​ указать адрес диапазона.​

   

   ​ Как присвоить имя​ наоборот. В формулах,​ ячейка диапазона (также​ (только Excel 2007)​ слова нужно выделить​ Это может быть​

   

   ​ в состоянии, как​ которой хотим произвести​ должны быть самостоятельными​​ возможность найти нужные​​ во второй таблице​

   

   ​ данные находятся в​ формулы.​Для этого в ячейку,​ в таблице. Это​В ячейке F7​В строке «Номер_столбца»​ диапазону, смотрите в​ которые создает мастер​ называемая начальной ячейкой).​Для решения этой задачи​ с обеих сторон​ слово, число или​ при обычном поиске,​ поиск.​ элементами. Так, если​ текстовые или числовые​ количество требуемых работников​ разных столбцах и​

   

3. ​Студент готовит шпаргалку на​ следующую за последней​ может быть цифро-буквенное​​ устанавливаем формулу итоговой​​ указываем НОМЕР ПО​​ статье «Диапазон в​​ подстановок, используются функции​Формула​

​ можно использовать функцию​ знаком «*». Теперь​ ссылка на ячейку.​ но при необходимости​Набираем на клавиатуре комбинацию​ в качестве запроса​ значения через окно​ для производства. В​ строках. Поэтому в​ экзамен. Ему нужно​ в первой строчке,​ сочетание, только цифры​ суммы со скидкой.​ СЧЕТУ столбца (не​ Excel» тут.​ ИНДЕКС и ПОИСКПОЗ.​ПОИСКПОЗ(«Апельсины»;C2:C7;0)​ ВПР или сочетание​

​ в поисковой выдаче​

lumpics.ru

Поиск значений в списке данных

​ При этом, программа,​ можно выполнить корректировку.​ клавиш​ будет задано выражение​ «Найти и заменить».​ результате возвращено значение​ первую очередь нужно​ выбрать формулы для​ нужно ввести функцию​ или только буквы,​ =F3*F4-((F3*F4)*F6/100) Получилось так.​ буквы), где написаны​Внимание!​Щелкните ячейку в диапазоне.​ищет значение «Апельсины»​ функций ИНДЕКС и​ будут отображены все​ выполняя поиск, видит​По умолчанию, функции​Ctrl+F​

В этой статье

​ «прав», то в​ Кроме того, в​ 5, которое дальше​

​ проверить сколько потребуется​ расчета суммы. Он​ НАЙТИ. Она будет​

​ пробелы или знаки.​Таблицу скидок можно сделать​ цены товара.​Чтобы формула правильно​

​На вкладке​ в диапазоне C2:C7.​ ПОИСКПОЗ.​

​ ячейки, в которых​ только ссылку, а​«Учитывать регистр»​

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

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

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

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

​ на другой странице.​В примере –​ работала, в ячейках​

Примеры функций ИНДЕКС и ПОИСКПОЗ

​Формулы​

​ Начальную ячейку не​Дополнительные сведения см. в​ находятся данные слова​ не результат. Об​и​

​ знакомое нам уже​ все ячейки, которые​ расширенного поиска данных.​​ функцией. На основе​​ этих продуктов (первая​ таких формулах на​=НАЙТИ(«!»; A2; 1)​​ функция учитывает введенный​​Если товара нет​

​ второй столбец прайса.​ должен быть​в группе​

​ следует включать в​

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

​ разделе, посвященном функции​ в любом порядке.​

​ этом эффекте велась​​«Ячейки целиком»​ окно​ содержат данный последовательный​

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

​ формула возвращает финальный​На основе полученных данных​ стоит знак «+».​

​ области поиска можно​

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

​ слов «Мир» и​ получится так.​ _просмотр» пишем слово​

​.​​выберите команду​1​Что означает:​ установлены, следует нажать​ того, чтобы производить​ мы поставим галочки​. Дальнейшие действия точно​ внутри слова. Например,​ найти все ячейки,​ результат вычисления. А​ необходимо сразу перейти​

​Как всегда, функция прописывается​​ выделить столбец с​ «мир» будут разными.​Таким способом можно выбирать​

​ «Ложь». Это значит,​​Если в одних​​Подстановка​ — это количество столбцов,​=ИНДЕКС(нужно вернуть значение из​ на кнопку​ поиск именно по​

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

​.​

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

​ которое нужно отсчитать​ C2:C10, которое будет​«Найти всё»​

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

​ другой таблице и​ за последней в​ набора функции, нажмите​

​ может быть указана​

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

​ таблицы Excel.​ искать точное значение​

​ «Текстовый», а в​​Если команда​ справа от начальной​ соответствовать ПОИСКПОЗ(первое значение​

​или​ данным, которые отображаются​ случае, при формировании​ способе. Единственное отличие​ считаться слово «Направо».​ окно набор символов​ 20 штук определенного​ найти какое количество​ первой строчке. Формула​

​ Enter. В ячейке​ с помощью мыши.​Как найти в​

​ наименования. Например, у​

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

​ других – «Числовой»,​​Подстановка​ ячейки, чтобы получить​ «Капуста» в массиве​«Найти далее»​ в ячейке, а​ результата будет учитываться​ будет состоять в​

​ Если вы зададите​ (буквы, цифры, слова,​ товара.​ работников должно быть​ выглядит так.​ появится номер места,​ Также ее значения​ таблице функцией ВПР​ нас формула настроена​ то формула покажет​недоступна, необходимо загрузить​ столбец, из которого​ B2:B10))​, чтобы перейти к​ не в строке​

1. ​ введенный регистр, и​

2. ​ том, что поиск​​ в поисковике цифру​​ и т.д.) без​​По этом уже принципу​​ вовлечено к данному​​=НАЙТИ(«+»; A1; 1)​​ на котором располагается​

3. ​ могут быть введены​​ по данным не​​ искать «Творог». И​ ошибку.​

   ​ надстройка мастера подстановок.​

4. ​ возвращается значение. В​​Формула ищет в C2:C10​ ​ поисковой выдаче.​ формул, нужно переставить​​ точное совпадение. Если​​ выполняется только в​​ «1», то в​​ учета регистра.​

5. ​ можно использовать формулы​​ объему производства. Полученный​​Нажав Enter, вы получите​​ пометка «!».​​ с клавиатуры.​​ одного, а двух​​ будет искать только​

6. ​Ещё​​Загрузка надстройки мастера подстановок​​ этом примере значение​ первое значение, соответствующее​​Как видим, программа Excel​​ переключатель из позиции​​ вы введете слово​​ указанном интервале ячеек.​

7. ​ ответ попадут ячейки,​

​Находясь во вкладке​

support.office.com

Найти в Excel несколько данных сразу.

​ для функции ВПР​​ результат должен сравнивается​ результат функции.​Теперь вы сможете выделить​Начальная позиция (опциональный аргумент).​​ столбцов, читайте в​​ творог.​​-​​Нажмите кнопку​ возвращается из столбца​ значению​
​ представляет собой довольно​«Формулы»​ с маленькой буквы,​Как уже говорилось выше,​ которые содержат, например,​«Главная»​ из нескольких листов.​ с данными третей​Теперь можно выбрать формулы,​ и скопировать строки,​ По умолчанию началом​
​ статье «Соединить функции​А если мы​эта функция ищет​Microsoft Office​ D​Капуста​ простой, но вместе​в позицию​ то в поисковую​ при обычном поиске​ число «516».​, кликаем по кнопке​В документе 46 листов,​
​ таблицы. Таким образом​
​ в которых знак​ напротив которых стоит​ поиска признается первая​​ «ВПР» и «СЦЕПИТЬ»​​ укажем не «ложь»,​
​ только​, а затем —​Продажи​(B7), и возвращает​ с тем очень​«Значения»​
​ выдачу, ячейки содержащие​​ в результаты выдачи​​Для того, чтобы перейти​«Найти и выделить»​​ и по 5​ мы за одно​​ суммы находится на​
​ цифра 2 или​ ячейка первой строки​ в Excel» здесь.​ а «истина», то​по первому столбцу таблицы​​ кнопку​.​ значение в ячейке​
​ функциональный набор инструментов​
​. Кроме того, существует​ написание этого слова​ попадают абсолютно все​ к следующему результату,​, которая расположена на​ страниц на каждом,​ операцию поиска по​ 4 месте.​ воспользоваться автофильтром: «ДАННЫЕ»-«Фильтр».​ таблицы. Вы можете​Из таблицы Excel​ таблица будет искать​ Excel​
​Параметры Excel​К началу страницы​ C7 (​ поиска. Для того,​ возможность поиска по​ с большой буквы,​ ячейки, содержащие последовательный​ опять нажмите кнопку​
​ ленте в блоке​ есть ли возможность​ трем таблицам сразу​Читайте также: Примеры использования​Отчет готов за пару​ вручную задать номер​
​ можно выбрать данные​ похожие слова. Может​
​.​и выберите категорию​Для выполнения этой задачи​100​ чтобы произвести простейший​ примечаниям. В этом​ как это было​
​ набор поисковых символов​«Найти далее»​ инструментов​ искат ьво всей​ определим необходимые затраты​ функции НАЙТИ в​
​ секунд.​ ячейки, которая будет​ сразу по двум​ возникнуть путаница.​Всю найденную информацию​
​Надстройки​ используется функция ГПР.​
​).​ писк, достаточно вызвать​ случае, переключатель переставляем​ бы по умолчанию,​ в любом виде​.​«Редактирование»​ книеге одновременно, а​ (сумму).​ Excel формулах.​
​Пример 2. В таблице​ начальной в процессе​ параметрам на пересечении​Нажимаем «ОК». Получилось так.​ можно разместить в​.​​ См. пример ниже.​
​Дополнительные сведения см. в​ поисковое окно, ввести​​ в позицию​​ уже не попадут.​​ не зависимо от​Так можно продолжать до​. В появившемся меню​ не на каждм​Среднестатистический пользователь Excel искал​Выбраны все необходимые формулы​
​ 4 столбца по​ поиска.​ строки и столбца.​​В ячейке F4 появилась​ таблице рядом с​В поле​Функция ГПР выполняет поиск​ разделах, посвященных функциям​ в него запрос,​«Примечания»​
​ Кроме того, если​ регистра.​ ​ тех, пор, пока​
​ выбираем пункт​ листе отдельно? Ато​ бы решение с​
​ из списка по​
​ 10 строк.​Синтаксис функции выглядит таким​ Читайте в статье​ такая надпись,​ таблицей – прайсом.​Управление​
​ по столбцу​ ИНДЕКС и ПОИСКПОЗ.​ и нажать на​.​ включена функция​К тому же, в​ отображение результатов не​«Найти…»​ 46 раз просить​ помощью формул основанных​​ критерию – «4»​В нее сведены артикулы​ образом:​ «Как найти в​#​ Например, так.​
​выберите значение​Продажи​К началу страницы​ кнопку. Но, в​
​Ещё более точно поиск​«Ячейки целиком»​
​ выдачу может попасть​ начнется по новому​. Вместо этих действий​​ его найти одно​ таких функциях как​ указанном в условии​
​ товаров, которые находятся​НАЙТИ(«искомый текст»; просматриваемый текст;​ Excel ячейку на​Н/Д​Или на другой странице​Надстройки Excel​и возвращает значение​Для выполнения этой задачи​
​ то же время,​ можно задать, нажав​, то в выдачу​ не только содержимое​ кругу.​ можно просто набрать​ и тоже както​ ВПР. И выполнял​ для отбора строк​ на складе и​

excel-office.ru

Примеры использования функции НАЙТИ в таблицах Excel

​ [начальная позиция])​ пересечении строки и​(значит — нет​ разместить таблицу с​и нажмите кнопку​ из строки 5 в​ используется функция ВПР.​ существует возможность настройки​

Как использовать функцию НАЙТИ в Excel

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

​«Формат»​ элементы, содержащие точное​ и адрес элемента,​ запуске поисковой процедуры​ клавиш​

​Валерий беляев​ 3 этапа (отдельно​Все выше описанные примеры​наименование товара;​ номер места в​Используя таблицы Excel как​ в ячейку F2​Принцип работы такой.​.​Дополнительные сведения см. в​  Значения в первой​

1. ​ большим количеством различных​.​ наименование. Например, если​ на который она​ вы нажмете на​Ctrl+F​: Pasha Ram -​ для каждой таблицы).​ применяют функцию НАЙТИ​цвет;​ строке, на котором​ базу данных, не​
2. ​ не написали название​В ячейку F2​В области​ разделе, посвященном функции​ строке должны быть​ параметров и дополнительных​
3. ​При этом открывается окно​ вы зададите поисковый​ ссылается. Например, в​ кнопку​.​ ты сам то​ Оказывается, можно получить​ без формул. Но​цена;​

​ располагается искомое ключевое​ обойтись без такой​

​ товара, информацию по​ пишем наименование товара.​

​Доступные надстройки​ ГПР.​ отсортированы по возрастанию.​ настроек.​ формата ячеек. Тут​ запрос «Николаев», то​ ячейке E2 содержится​

​«Найти все»​

Примеры использования функции НАЙТИ

​После того, как вы​ пробовал?​ сразу готовый результат​ на практике чаще​артикул.​ слово. Если его​

​ функции, как «Найти».​ которому ищем.​ Можно написать инвентарный​установите флажок рядом​

• ​К началу страницы​
• ​В приведенном выше примере​
• ​Автор: Максим Тютюшев​
• ​ можно установить формат​

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

​ нет, выдается символ​ Она понадобится для​Теперь пишем в​ номер, другие коды,​ с пунктом​Для выполнения этой задачи​ функция ВПР ищет​Предположим, что требуется найти​ ячеек, которые будут​ «Николаев А. Д.»,​ собой сумму ячеек​ будут представлены в​ пунктам на ленте,​Нажимаешь Ctrl+F​

​ в 1 этап​ как вспомогательная функция​ поступления на склад​ ошибки #ЗНАЧ!​ быстрого определения места​ ячейку F2 «Творог».​

​ т.е. то, что​

​Мастер подстановок​ используется функция ГПР.​ имя первого учащегося​ внутренний телефонный номер​ участвовать в поиске.​ в выдачу уже​ A4 и C3.​ виде списка в​ или нажали комбинацию​

​В открывшемся окне​ с помощью специальной​ для формул с​ они по-разному маркируются.​​

​ ключевого слова в​ Получилось так.​

​ записано в первом​и нажмите кнопку​Важно:​

​ с 6 пропусками в​ сотрудника по его​ Можно устанавливать ограничения​ добавлены не будут.​

• ​ Эта сумма равна​
• ​ нижней части поискового​
• ​ «горячих клавиш», откроется​
• ​ нажимаешь «Параметры»​

​ формулы. Для этого:​ обработкой таблиц в​ Нужно выбрать информацию​Эта формула редко используется​ каждой строке таблицы.​По такому же принципу​ левом столбце прайса.​ОК​  Значения в первой​ диапазоне A2:B7. Учащихся​ идентификационному номеру или​ по числовому формату,​По умолчанию, поиск производится​ 10, и именно​

​ окна. В этом​ окно​В открывшихся окошечках​В ячейке E6 введите​

​ режиме базы данных.​

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

​ по выравниванию, шрифту,​ только на активном​ это число отображается​ списке находятся информация​«Найти и заменить»​ выбираешь «В книге»​ значение 20, которое​

​Определенно легче вести поиск​

​ которых начинаются на​ менее, чтобы наглядно​ аргументами в Excel​ строке «Наличие», только​

​ столбца F установлены​Следуйте инструкциям мастера.​ отсортированы по возрастанию.​6​ вознаграждения, предусмотренную за​ границе, заливке и​ листе Excel. Но,​ в ячейке E2.​

​ о содержимом ячеек​во вкладке​Вводишь что искать​ является условием для​ по одной пусть​

​ буквы «de». Эти​

​ продемонстрировать действие, стоит​ – это формула,​

​ столбец указываем третий​ формулы. Они находят​К началу страницы​В приведенном выше примере​

​ пропусками в таблице нет,​ определенный объем продаж.​ защите, по одному​

​ если параметр​ Но, если мы​ с данными, удовлетворяющими​«Найти»​Нажимаешь «Найти все»​ поискового запроса.​

​ большой, но целой​ буквосочетания встречаются и​ рассмотреть несколько примеров​ с помощью которой​ (в прайсе -наличие​ в таблице и​Функция «ВПР» в Excel​ функция ГПР ищет​ поэтому функция ВПР​

exceltable.com

Одновременный поиск в нескольких таблицах Excel

​ Необходимые данные можно​ из этих параметров,​«Искать»​ зададим в поиске​ запросу поиска, указан​. Она нам и​ЗЫ: Это в​В ячейке E7 введите​ таблице или в​ в других местах​ ее использования.​ выполняются сложные операции​ на складе стоит​ показывают нам эту​поможет найти данные​ значение 11 000 в строке 3​ ищет первую запись​ быстро и эффективно​ или комбинируя их​вы переведете в​ цифру «4», то​ их адрес расположения,​ нужна. В поле​ 2007, другого нет​ следующую формулу:​

Одновременный поиск по нескольким диапазонам

​ смежных диапазонах ячеек,​ артикулов. Важно отсортировать​Пример 1. В таблице​ и вычисления в​ в третьем столбце).​

​ информацию.​ и перенести их​ в указанном диапазоне.​ со следующим максимальным​ находить в списке​ вместе.​ позицию​ среди результатов выдачи​ а также лист​«Найти»​ — проверить не​Готово!​

​ чем по нескольким​ только товары, в​ 4 столбца по​ Excel. Перед ее​Как написать в Excel​Итак, делаем активной​ в другую ячейку,​ Значение 11 000 отсутствует, поэтому​ значением, не превышающим​ и автоматически проверять​Если вы хотите использовать​«В книге»​ будет все та​ и книга, к​вводим слово, символы,​ могу​

​Производственная себестоимость для 20​ разделенным на части​ артикулах которых оно​ 10 строк. В​ введением ставится знак​ формулу​ ячейку, в которую​ таблицу. Например,​ она ищет следующее​ 6. Она находит​ их правильность. Значения,​ формат какой-то конкретной​, то поиск будет​ же ячейка E2.​

1. ​ которым они относятся.​ или выражения, по​Majya penzeva​ шт. определенного товара.​
2. ​ таблицами разбросанных по​ встречается на первом​

​ нее внесены:​

​ равенства. Именно так​по скидкам.​

​ будем устанавливать формулу​

Как работает формула с ВПР в нескольких таблицах:

​з​ максимальное значение, не​ значение 5 и возвращает​ возвращенные поиском, можно​ ячейки, то в​ производиться по всем​ Как такое могло​ Для того, чтобы​ которым собираемся производить​: Нажмите Ctrl+F в​​ разным несмежным диапазонам​ месте.​номера по штатному расписанию;​ вы дадите знать​У нас есть​ (в примере будем​аполнить бланк.​

​ превышающее 11 000, и возвращает​ связанное с ним​ затем использовать в​ нижней части окна​ листам открытого файла.​ получиться? Просто в​ перейти к любому​ поиск. Жмем на​ всплывшем окне внесите​Принцип действия данной формулы​ или даже по​Алгоритм действий аналогичный. В​ФИО работников;​ программе, что вводится​ такая таблица по​ искать цену товара).​Отбор данных в Excel​ 10 543.​ имя​ вычислениях или отображать​ нажмите на кнопку​

​В параметре​ ячейке E2 в​ из результатов выдачи,​ кнопку​

exceltable.com

Как искать слово в книге excel?

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

​«Использовать формат этой ячейки…»​​«Просматривать»​ качестве формулы содержится​ достаточно просто кликнуть​
​«Найти далее»​
​ выбирите найти все.​
​ поиске всех аргументов​ если выполнить автоматический​
​ последней в первой​оклад (размер оплаты).​
​ не текст и​
​Внимание!​
​ примере – это​ по первому столбцу​ разделе, посвященном функции​.​

​ несколько способов поиска​​.​можно изменить направление​ адрес на ячейку​ по нему левой​

​, или на кнопку​

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

Поиск слов

Документы часто имеют много страниц, тогда встает вопрос о том, как в Еxcel найти слово. Сделать это  иногда становится проблематично. Для упрощения этой задачи существует специальная функция поиска. Чтобы ею воспользоваться, необходимо выполнить следующий алгоритм действий:

• запустить программу Excel;
•  проверить активность таблицы, щелкнув по любой из ячеек;
•  нажать комбинацию клавиш «Ctrl + F»;
•  в строке «Найти» появившегося окна ввести искомое слово;
•  нажать «Найти».

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

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

•  «?» – подразумевает любой отдельно взятый символ;
•  «*» – обозначает любое количество символов.

 Примечательно, при поиске вопросительного знака или знака умножения дополнительно впереди ставится тильда («~»). При поиске тильды, соответственно – две тильды.

 Алгоритм неточного поиска слова:

•  запустить программу;
•  активировать страницу щелчком мыши;
•  зажать комбинацию клавиш «Ctrl + F»;
•  в строке «Найти» появившегося окна ввести искомое слово, используя вместо букв, вызывающих сомнения, джокерные символы;
•  проверить параметр «Ячейка целиком» (он не должен быть отмеченным);
•  нажать «Найти все».

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

Поиск нескольких слов

Не зная, как найти слово в таблице в Еxcel, следует также воспользоваться функцией раздела «Редактирование» – «Найти и выделить». Далее нужно отталкиваться от искомой фразы:

•  если фраза точная, введите ее и нажмите клавишу «Найти все»;
•  если фраза разбита другими ключами, нужно при написании ее в строке поиска дополнительно проставить между всеми словами «*».

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

Поиск ячеек

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

Для поиска ячеек с формулами выполняются следующие действия.

1. В открытом документе выделить ячейку или диапазон ячеек (в первом случае поиск идет по всему листу, во втором – в выделенных ячейках).
2. Во вкладке «Главная» выбрать функцию «Найти и выделить».
3. Обозначить команду «Перейти».
4. Выделить клавишу «Выделить».
5. Выбрать «Формулы».
6. Обратить внимание на список пунктов под «Формулами» (возможно, понадобится снятие флажков с некоторых параметров).
7. Нажать клавишу «Ок».

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

1. Перейти во вкладку «Главная».
2. Выбрать функцию «Найти и выделить».
3. Нажать на команду «Найти».
4. Перейти в «Параметры» и выбрать «Формат».
5. Здесь выделить функцию «Выравнивание», поставить отметку «Объединить ячейки».
6. Нажать на «Ок».
7. Нажать на кнопку «Найти все» и проанализировать список ячеек, которые объединены на соответствующем листе.

При нажимании кнопкой мыши на элемент в списке происходит выделение объединенной ячейки на листе. Дополнительно доступна функция «Отменить объединение ячеек».

 Выполнение представленных выше действий приводит к нахождению всех объединенных ячеек на листе и при необходимости отмене данного свойства. Для поиска скрытых ячеек проводятся следующие действия.

1. Выбрать лист, требующий анализа на присутствие скрытых ячеек и их нахождения.
2. Нажать клавиши «F5_гт_
   Special».
3. Нажать сочетание клавиш «CTRL + G_гт_ Special».

Можно воспользоваться еще одним способом для поиска скрытых ячеек:

1. Открыть функцию «Редактирование» во вкладке «Главная».
2. Нажать на «Найти».
3. Выбрать команду «Перейти к разделу». Выделить «Специальные».
4. Попав в группу «Выбор», поставить галочку на «Только видимые ячейки».
5. Нажать кнопку «Ок».

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

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

Чтобы найти ячейки, для которых применено условное форматирование:

• нажать на ячейку, не предусматривающую условное форматирование;
• выбрать функцию «Редактирование» во вкладке «Главная»;
• нажать на кнопку «Найти и выделить»;
• выделить категорию «Условное форматирование».

Чтобы найти ячейки, для которых применено одинаковое условное форматирование:

• выбрать ячейку, предусматривающую условное форматирование, требующую поиска;
• выбрать группу «Редактирование» во вкладке «Главная»;
• нажать на кнопку «Найти и выделить»;
• выбрать категорию «Выделить группу ячеек»;
• установить свойство «Условные форматы»;
• напоследок нужно зайти в группу «Проверка данных» и установить аналогичный пункт.

Поиск через фильтр

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

• выделить заполненную ячейку;
• во вкладке «Главная» выбрать функцию «Сортировка»;
• нажать на кнопку «Фильтр»;
• открыть выпадающее меню;
• ввести искомый запрос;
• нажать кнопку «Ок».

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