Как найти фамилию в большом списке

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

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

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

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

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

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

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

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

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

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

Примеры ВРОТ

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

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

Что означает:

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

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

Дополнительные сведения см. в функциях ИНДЕКС иФУНКЦИЯ MATCH.

К началу страницы

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

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

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

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

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

К началу страницы

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

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

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

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

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

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

К началу страницы

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

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

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

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

К началу страницы

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

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

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

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

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

К началу страницы

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

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

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

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

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

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

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

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

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

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

7. Следуйте инструкциям мастера.

К началу страницы

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

Юрий М Модератор Сообщений: 60750 Регистрация: 14.09.2012 Контакты см. в профиле	{quote}{login=МОЗГ}{date=06.03.2011 09:48}{thema=по первым буквам найти нужную фамилию из списка}{post}Нужно из списка выбрать нужную фамилию при наборе первых букв. {/post}{/quote}   Где выбрать?

	В прикрепленном файле есть графа ФИО, вот в ней и нужно выбирать. Допустим, можно через дополнительную строку. Просто в этой таблице еще много всяких граф, я их удалила. И если мне нужно найти, например, фио Шалаева из численности 2000, то это очень долго. А так, набрала 2 буковки в какой-нибудь дополнительной ячейке, и выделяется в этой графе нужная фамилия!

Юрий М Модератор Сообщений: 60750 Регистрация: 14.09.2012 Контакты см. в профиле

{quote}{login=МОЗГ}{date=06.03.2011 10:05}{thema=}{post}В прикрепленном файле есть графа ФИО, вот в ней и нужно выбирать. {/post}{/quote}   МОЗГ, я ведь не просто так спросил. Если следовать Вашему объяснению, то мне и не нужно вводить несколько первых символов, чтобы найти, например, Захарову. Я просто кликну по ячейке В11. Другое дело, если ЭТОТ столбец изначально пустой, и нужно в него вводить данные, набирая первые символы. А список где-то в другом месте… Понятно я о чём?

	The Prist , спсибо большое за ссылочку. Вот ашла там то, что мне нужно. Преобразовала, но почему-то работает не совсем полностью. Исправьте, пожалуйста, если что не так. Почему-то фамилии вверху табицы выделяются, а внизу — никак. Т.е., если нужно найти Байкову, то получается,  а вот если какую-нибудь Петрову — никак! Почему?

Hugo Пользователь Сообщений: 23371 Регистрация: 22.12.2012	У Вас условное форматирование кончается на Гавриловой, поэтому ниже не выделяет.   Есть ещё такие варианты макросов, на выбор:

Юрий, не совсем точно выразилась, прошу прощения. Чтоб кликнуть по ячейке В 11, ее нужно сначала найти, а если у меня 5000 человек и срочно нужно найти фио, то это очень долго роликом двигать туда-сюда и перебирать алфавит в голове!!! Мне нужно алгоритм примерно как в программе 1-С. Есть определенный список, я навожу на любую строку курсор, нажимаю первые буквы, и ВОАЛЯ! — вот она и нужная строка!!! Вот. В предыдущем ответе есть образец. Полазила еще в поиске и вот нашла, изменила под себя, но не совсем работает.

Hugo, спасибо огромное, очень интересно и все работает, но я честное слово с макросами не могу работать и даже на примере вашей таблички изменить не смогу на свои данные. Мне учиться этому не у кого — на работе просила отправить на курсы, но — фиг!!! Простите, пожалуйста, за тупой вопрос, а как мне продлить это условное ворматирование до конца таблички моей? Как сделать, чтоб моя табличка заработала полностью?

Hugo Пользователь Сообщений: 23371 Регистрация: 22.12.2012	В 2007 просто — заходите в правила и меняете D56 на D341:   =$D$8:$D$341   Но макросом удобнее — сразу переходит на нужную строку, или копирует данные нужной строки, или как прикажете

Юрий М Модератор Сообщений: 60750 Регистрация: 14.09.2012 Контакты см. в профиле	А что Вам даст условное форматирование? Ну покрасит ячейку, которую Вы даже не видите… Может быть есть смысл АКТИВИРОВАТЬ нужную ячейку? Тогда она сразу в поле Вашего зрения…

	{quote}{login=Hugo}{date=06.03.2011 11:33}{thema=}{post}В 2007 просто — заходите в правила и меняете D56 на D341:   =$D$8:$D$341   :){/post}{/quote}   Спасибо, сейчас попробую.

{quote}{login=Юрий М}{date=06.03.2011 11:34}{thema=}{post}А что Вам даст условное форматирование? Ну покрасит ячейку, которую Вы даже не видите… Может быть есть смысл АКТИВИРОВАТЬ нужную ячейку? Тогда она сразу в поле Вашего зрения…{/post}{/quote}   Форматирование — да, ячейку красит и не нужно напрягаться —  читать каждую фамилию. повторюсь — у меня много примерных таблиц и большие списки. Активировать, это как?

Юрий М Модератор Сообщений: 60750 Регистрация: 14.09.2012 Контакты см. в профиле	{quote}{login=МОЗГ}{date=06.03.2011 11:42}{thema=Re: }{post}{quote}{login=Юрий М}{date=06.03.2011 11:34}{thema=}{post}{/post}{/quote}Форматирование — да, ячейку красит и не нужно напрягаться…  Активировать, это как?{/post}{/quote}   Ну покрасилась строка № 120, а она за пределами экрана. Крутить будете список (не напрягаясь), пока не покажется залитая ячейка?   Активировать — делать активной.

{quote}{login=Юрий М}   Ну покрасилась строка № 120, а она за пределами экрана. Крутить будете список (не напрягаясь), пока не покажется залитая ячейка?   Активировать — делать активной.{/post}{/quote}   Спасибо большое, что цацкаетесь со мной. Ваш вариант с активацией мне тоже нравится! Вообще будет красиво. Научите, пожалуйста! Подскажите, куда войти, что нажимать! Да, ощущаю себя блондинкой, общаясь с вами!!!!

Hugo Пользователь Сообщений: 23371 Регистрация: 22.12.2012	Мой пример смотрели — вот там как раз ячейки активируются. Кроме версии The_Prist — там копируются.

	{quote}{login=Hugo}{date=06.03.2011 11:56}{thema=}{post}Мой пример смотрели — вот там как раз ячейки активируются. Кроме версии The_Prist — там копируются.{/post}{/quote}   Я смотрела, а не смогу так сделать! Но красиво!!!!!!!!!!!!!!!!!!!

	Посижу еще в поиске, поищу что-то подобное, поковыряюсь в имеющемся. Спасибо всем большое, очень благодарна. Напишу, если можно, вечерочком о проделанных подвигах, может и получится что-то! Спасибо

Юрий М Модератор Сообщений: 60750 Регистрация: 14.09.2012 Контакты см. в профиле	Вот ещё вариант. Вводим начальные символы и кликаем по нужной фамилии.

	{quote}{login=МОЗГ}{date=06.03.2011 10:05}{thema=}{post}…А так, набрала 2 буковки в какой-нибудь дополнительной ячейке, и выделяется в этой графе нужная фамилия!{/post}{/quote}   Если у вас XL-07/10, то вполне может подойти поиск по нескольким буквам в окне фильтра. Подобная тема на днях обсуждалась… См. скрин.   -36400-

{quote}{login=Hugo}{date=06.03.2011 11:33}{thema=}{post}В 2007 просто — заходите в правила и меняете D56 на D341:   =$D$8:$D$341{/post}{/quote}   спасибо огромное, все получилось. Сделала через условное форматирование.   Юрий М, благодарю, ваш вариант очень удобный, сейчас попробую переделать для своих таблиц. Это и есть вариант — активировать ячейки? Спасибо большое, удобно.     Z, ваш вариант сейчас посмотрю.

Юрий М Модератор Сообщений: 60750 Регистрация: 14.09.2012 Контакты см. в профиле	{quote}{login=МОЗГ}{date=07.03.2011 12:04}{thema=Re: }{post}{quote}{login=Hugo}{date=06.03.2011 11:33}{thema=}{post}{/post}{/quote} Это и есть вариант — активировать ячейки?{/post}{/quote}   Ну да! Наберите, например, одну букву «к». В списке появятся две фамилии. По какой кликните, ячейка с ЭТОЙ фамилией и активируется. И всегда будет в пределах видимости.

	Всем спасибо большое, очень удобную информацию получила, попробую поразбираться со всеми полученными предложениями!     Юрий, остановилась пока на вашем варианте. удобный. А можно еще вопросик? Сейчас я все свои таблицы — штук 10 -все переделываю по вашему варианту. Это делаю дома. А вот если я перенесу эти переделанные таблички на рабочий комп, они будут работать?

Юрий М Модератор Сообщений: 60750 Регистрация: 14.09.2012 Контакты см. в профиле	Перенесёте файл? — конечно будут. Если макросы будут разрешены Ещё поищите — была тема «Удобный автофильтр». Может быть тоже пригодится

	{quote}{login=Юрий М}{date=07.03.2011 12:47}{thema=}{post}Перенесёте файл? — конечно будут. Если макросы будут разрешены Ещё поищите — была тема «Удобный автофильтр». Может быть тоже пригодится :-){/post}{/quote}   Я каждый раз открываю таблички и пишется строка: запуск активного содержимого отключен. Я нажимаю параметры, нажимаю включить и все работает!

	А если в коде Юрий М , после   If UCase(Cells(i, 2)) Like UCase(Me.TextBox1) & «*» Then   вставить к примеру      Range(«B4:B80»).Interior.ColorIndex = xlNone       Cells(i, 2).Interior.ColorIndex = 4   Найденное в столбце ещё и закрасится.

Юрий М Модератор Сообщений: 60750 Регистрация: 14.09.2012 Контакты см. в профиле	Не совсем верно: тогда уж эти строчки нужно добавить в процедуру ListBox1_Click()   Private Sub ListBox1_Click()      Cells(ListBox1.List(ListBox1.ListIndex, 0), 2).Select      Range(«B4:B80»).Interior.ColorIndex = xlNone      Selection.Interior.ColorIndex = 4   End Sub

Guest Гость	#27 08.03.2011 21:31:37 Что-поделать , Вы — же знаток !!!

В программе Microsoft Excel много разных трюков и приемов, упрощающих работу. Например, есть функция, позволяющая в документе Эксель найти нужно слово. Сделать это очень просто. Нужно в пустой ячейке Microsoft Excel щелкнуть правой кнопкой мыши, чтобы появился курсор. Затем нужно нажать сочетание клавиш Ctrl + F Должно выйти окно «Найти/заменить». Выбираем «Найти», пишем в строку слово или фразу. автор вопроса выбрал этот ответ лучшим Azama­tik [55.3K] 7 лет назад  Если вы хотите найти какое — либо слово в Microsoft Excel (Эксель), то это можно сделать следующим способом: Но есть и вариант поиска определенного слова в Эксель быстрее. Для этого нужно поставить курсор в пустую ячейку Экселя и нажать следующую комбинацию на своей клавиатуре: Ctrl+F После этого у вас на экране появится такое диалоговое окно «Найти и заменить»: Мы хотим найти слово, поэтому выбираем «Найти» > записываем нужное слово и нажимаем найти все или найти далее. ЛИСА-НАСА [226K] 8 лет назад  Вы будете удивлены, но комбинация двух кнопок «Ctrl + F» является универсальной во всех программах, в том числе и в программе Microsoft Excel. Нажимая эту комбинацию кнопок вы можете найти нужно слово. Иногда можно выделить слово нажать эту комбинацию и ввод. Как видим всё просто. дольф­аника [379K] 8 лет назад  Комбинация двух кнопок Ctrl + F выполняет функцию поиска нужного слова в выделенной предварительно ячейке. Далее вписываем искомое слово и нажимаем клавишу энтер. Поиск совершается мгновенно, что очень облегчает работу. В общем. очень простое действие совершаете. cherr­y bloss­om [25.9K] 9 лет назад  Что в Ворде, что в программе Microsoft Excel найти слово очень легко. Рассмотрим пример в последнем. Выделяем ячейку. Удерживаем кнопку ctrl и нажимаем F. Что означает команду Найти. В окошке вводим слово для поиска и нажимаем Enter. Ksyus­ha26 [26.8K] 8 лет назад  Найти нужное слово в Excel очень просто. Для этого ставим в пустую ячейку курсор и нажимаем сочетание из двух клавиш: CTRL (контрл) и F (ф). Теперь появляется окошечко, в него вбиваем слово, которое нам нужно найти. Вот и все, эта комбинация работает ещё и в других программах Zolot­ynka [551K] 8 лет назад  У нас в компании практически все таблицы сделаны в Exel, чтобы быстро найти нужную цифру или слово я использую стандартную комбинацию Ctrl+F, она же работает и в вордовских файлах. Пользуюсь этой комбинацией очень часто — она облегчает и ускоряет работу в разы. В программе Эксэль можно найти слово, используя поиск — его можно вызвать клавишами Contrl + F. В открывшемся окне в графе Найти вписываем необходимое слово и нажимаем кнопку Найти далее. Принцип такой же, как в Ворд. Галоч­ка196­8 [28.8K] 8 лет назад  Для того, чтобы найти в программе Exel нужное слово, надо в пустую ячейку поставить курсор и нажать стандартную комбинацию Ctrl+F. В открывшемся окне вставляем слово, которое надо найти. Нажимаем кнопку Найти далее. rosen­bom [27.2K] 9 лет назад  Точно так же как и в Ворде. Нужно запустить функцию поиска, ввести нужное слово и нажать Enter. Честно говоря, не знаю как правильно делать «по науке», я нажимаю левый Contrl и F, появляется окно функции. Знаете ответ?

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

Пусть Исходный список значений (например, перечень инструментов) находится в диапазоне

A10:A19

(см.

Файл примера

).

Задача

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

А. Найти значения, которые содержат критерий

Для удобства создадим

именованный диапазон

Список

.

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

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

содержится

текст-критерий (например, слово

дрель

). Критерий вводится в ячейку

С6

.

Для создания списка, содержащего найденные значения, воспользуемся

формулой массива

:

=ИНДЕКС(Список; НАИМЕНЬШИЙ( ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($C$6;Список);0)>0;СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))

Алгоритм работы формулы следующий (для просмотра промежуточных шагов работы формулы воспользуйтесь

клавишей

F9

):

• Функция
  
  ПОИСК()
  
  , перебирая элементы исходного списка, определяет, содержится ли в нем значение-критерий. Если значение не содержится, то возвращается ошибка #ЗНАЧ! В противном случае возвращается числовое значение, соответствующее номеру начальной позиции вхождения критерия в значение из списка (здесь нам не важен номер позиции, важно, что это число);
• Функция
  
  ЕСЛИОШИБКА()
  
  используется для подавления ошибки #ЗНАЧ! заменяя ее на число 0;
• Функция
  
  ЕСЛИ()
  
  заменяет числовые значения, возвращенные функцией
  
  ПОИСК()
  
  , на номер позиции значения в списке. Если значение =0 (соответствует ошибке #ЗНАЧ!), то возвращается число 30. В принципе, вместо 30 можно указать любое число, которое больше номера последней заполненной позиции Исходного списка (это нужно для правильной сортировки функцией
  
  НАИМЕНЬШИЙ()
  
  );
• Функция
  
  НАИМЕНЬШИЙ()
  
  сортирует массив номеров строк по возрастанию;
• Функция
  
  ДВССЫЛ()
  
  возвращает

  массив последовательных чисел

  ;

• Функция
  
  ИНДЕКС()
  
  возвращает текстовые значения из Исходного списка, из строк, номера которых были получены на предыдущем шаге.

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

формулу массива

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

формула массива будет возвращать несколько значений

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

С10:С19

, ввести формулу в

Строке формул

и нажать

CRTL+SHIFT+ENTER

.

Для скрытия ошибок #ССЫЛКА!, возвращаемой формулой массива, к диапазону

С10:С19

применено правило

Условного форматирования

.

Б. Найти значения, которые совпадают с критерием (точное совпадение)

В этом случае будут выведены все значения, которые совпадают с критерием (без

учета РЕгиСТра

). Критерий вводится в ячейку

E

6

.

Для создания списка, содержащего найденные значения, воспользуемся

формулой массива

:

=ИНДЕКС(Список; НАИМЕНЬШИЙ( ЕСЛИ($E$6=Список;СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))

B. Найти значения, которые начинаются с критерия

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

G

6

.

Для создания списка, содержащего найденные значения, воспользуемся

формулой массива

: =

ИНДЕКС(Список;НАИМЕНЬШИЙ( ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($G$6;Список);0)=1;СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))

Г. Найти значения, которые заканчиваются на критерий

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

I

6

.

Для создания списка, содержащего найденные значения, воспользуемся

формулой массива

:

=ИНДЕКС(Список;НАИМЕНЬШИЙ( ЕСЛИ($I$6=ПРАВСИМВ(Список;ДЛСТР($I$6));СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))

СОВЕТ:

О поиске текстовых значений с использованием

подстановочных знаков

читайте в статье

Поиск текстовых значений в списках. Часть2. Подстановочные знаки

. В статье

Выделение ячеек c ТЕКСТом с применением Условного форматирования

приведено решение аналогичной задачи с использованием

Условного форматирования

.

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

​Смотрите также​ 2 на котором​ т.к. для них​ появиться ее стоимость​ этой функции -​ на практике чаще​ они по-разному маркируются.​ менее, чтобы наглядно​ выполняются сложные операции​С10:С19​ исходного списка, определяет,​ таблице функцией ВПР​ творог.​по первому столбцу таблицы​Надстройки​Продажи​  Значения в первой​Предположим, что требуется найти​ имеется шапка, как​ округление невозможно.​

В этой статье

​ из таблицы. Важный​ искомое значение (​ всего она используется​

​ Нужно выбрать информацию​ продемонстрировать действие, стоит​ и вычисления в​

​применено правило Условного​ содержится ли в​ по данным не​А если мы​

​ Excel​.​и возвращает значение​

​ строке должны быть​ внутренний телефонный номер​ на Листе 1.​

​Важно отметить, что при​ нюанс в том,​Яблоко​

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

​ как вспомогательная функция​ по товарам артикулы​ рассмотреть несколько примеров​ Excel. Перед ее​ форматирования.​

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

​ нем значение-критерий. Если​ одного, а двух​ укажем не «ложь»,​

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

​.​

​В поле​ из строки 5 в​ отсортированы по возрастанию.​ сотрудника по его​ Как сделать, чтобы​

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

​ столбцов, читайте в​ а «истина», то​Всю найденную информацию​

​Управление​

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

​ указанном диапазоне.​В приведенном выше примере​

​ идентификационному номеру или​​ при вводе названия​ с округлением диапазон​ вводит нестандартные значения​

​ J2), второй -​ обработкой таблиц в​ буквы «de». Эти​Пример 1. В таблице​ равенства. Именно так​ выведены все значения,​​ то возвращается ошибка​​ статье «Соединить функции​ таблица будет искать​ можно разместить в​выберите значение​Дополнительные сведения см. в​ функция ВПР ищет​ узнать ставку комиссионного​ фрукта, цены и​ поиска — а​​ размеров, то они​​ диапазон ячеек, где​

​ режиме базы данных.​ буквосочетания встречаются и​ 4 столбца по​

​ вы дадите знать​

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

​ которые совпадают с​ #ЗНАЧ! В противном​ «ВПР» и «СЦЕПИТЬ»​

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

​ 10 строк. В​​ программе, что вводится​ критерием (без учета​ случае возвращается числовое​

​ в Excel» здесь.​​ возникнуть путаница.​​ таблицей – прайсом.​и нажмите кнопку​ ГПР.​ с 6 пропусками в​ определенный объем продаж.​

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

​ Например, так.​

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

​Перейти​К началу страницы​ диапазоне A2:B7. Учащихся​

​ Необходимые данные можно​ 1. (максимально близкая​​ быть отсортирована по​​ в таблице и​ в таблице -​ВПР (VLOOKUP)​

​ только товары, в​номера по штатному расписанию;​ не текст и​

​ в ячейку​

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

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

​В ячейке F4 появилась​​Или на другой странице​.​Для выполнения этой задачи​

​ с​ быстро и эффективно​ по цене).​ возрастанию (для Типа​ в серой ячейке​ A2:A10), третий аргумент​или ее горизонтальным​ артикулах которых оно​ФИО работников;​

​ не значение. Функция​E6​ критерия в значение​

​ сразу по двум​

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

​ такая надпись,​​ разместить таблицу с​В области​ используется функция ГПР.​6​ находить в списке​Pelena​ сопоставления = 1)​

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

1. ​: Здравствуйте.​

2. ​ или по убыванию​​ изготовления двери для​​ (0 — точное​​ГПР (HLOOKUP)​​ месте.​​оклад (размер оплаты).​​ частей: имени и​

3. ​Для создания списка, содержащего​​ нам не важен​​ строки и столбца.​Н/Д​

   ​Принцип работы такой.​

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

5. ​, то должны помнить,​​Алгоритм действий аналогичный. В​​Для отчета нужно выбрать​​ аргумента.​​ найденные значения, воспользуемся​​ номер позиции, важно,​​ Читайте в статье​

6. ​(значит — нет​​В ячейку F2​​ с пунктом​ строке должны быть​​ ищет первую запись​​ возвращенные поиском, можно​​ название фрукта и​​ = -1) по​

7. ​ размеров.​

​ поиск запрещен).​

support.office.com

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

​ что эта замечательные​​ ячейке, следующей за​ информацию о том,​Каждое название функции в​ формулой массива:​​ что это число);​​ «Как найти в​​ данных), п.ч. мы​​ пишем наименование товара.​Мастер подстановок​ отсортированы по возрастанию.​
​ со следующим максимальным​ затем использовать в​ название сада однозначно​ строчкам и по​Решение для серой ячейки​Во-вторых, совершенно аналогичным способом​ функции ищут информацию​ последней в первой​ сколько дней отработали​ Excel описывает ее​=ИНДЕКС(Список; НАИМЕНЬШИЙ(​
​Функция ЕСЛИОШИБКА() используется для​ Excel ячейку на​ в ячейку F2​ Можно написать инвентарный​и нажмите кнопку​В приведенном выше примере​ значением, не превышающим​ вычислениях или отображать​ определяет строку в​ столбцам.​ будет практически полностью​ мы должны определить​ только по одному​
​ строке прописываем функцию.​
​ и какую оплату​ операцию и назначение.​ЕСЛИ($E$6=Список;СТРОКА(Список)-СТРОКА($A$9);30);​​ подавления ошибки #ЗНАЧ!​​ пересечении строки и​
​ не написали название​ номер, другие коды,​ОК​ функция ГПР ищет​ 6. Она находит​ как результаты. Существует​
​ первой таблице, зачем​​Иначе приблизительный поиск корректно​​ аналогично предыдущему примеру:​ порядковый номер столбца​​ параметру, т.е. в​=НАЙТИ(«de»;D2;1)​​ получили специалисты, которые​
​ В данном случае​СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))​ заменяя ее на​ столбца».​ товара, информацию по​​ т.е. то, что​.​ значение 11 000 в строке 3​
​ значение 5 и возвращает​
​ несколько способов поиска​ тогда вводить цену​ работать не будет!​=ИНДЕКС(C7:K16; ПОИСКПОЗ(D3;B7:B16;1); ПОИСКПОЗ(G3;C6:K6;1))​ в таблице с​ одномерном массиве -​После нажатия клавиши Enter​ работают во вредных​ это слово «НАЙТИ».​В этом случае будут​ число 0;​Найдем текстовые значения, удовлетворяющие​ которому ищем.​
​ записано в первом​Следуйте инструкциям мастера.​ в указанном диапазоне.​ связанное с ним​ значений в списке​ и искать ближайшую?​Для точного поиска (Тип​=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))​
​ нужным нам городом.​ по строке или​ появляется номер места​ условиях, связанных с​Аргументов может быть несколько.​ выведены все значения,​
​Функция ЕСЛИ() заменяет числовые​ заданному пользователем критерию.​
​Теперь пишем в​ левом столбце прайса.​К началу страницы​ Значение 11 000 отсутствует, поэтому​ имя​ данных и отображения​Grekpinto​
​ сопоставления = 0)​Разница только в последнем​ Функция​ по столбцу. А​ заданных букв в​ задымленностью.​
​ Они могут быть​ которые начинаются или​ значения, возвращенные функцией​ Поиск будем осуществлять​ ячейку F2 «Творог».​
​ В остальных ячейках​Функция «ВПР» в Excel​
​ она ищет следующее​Алексей​ результатов.​: Действительно, я не​ сортировка не нужна​ аргументе обеих функций​ПОИСКПОЗ(J3; B1:F1; 0)​ если нам необходимо​ артикуле товаров. Протянув​Штатное расписание составлено особым​
​ числовыми, символьными или​ совпадают с критерием.​ ПОИСК(), на номер​ в диапазоне с​ Получилось так.​ столбца F установлены​​поможет найти данные​
​ максимальное значение, не​.​​Поиск значений в списке​​ учел этот момент.​​ и никакой роли​ПОИСКПОЗ (MATCH)​сделает это и​ выбирать данные из​ за нижний угол​ образом. Номера рабочих​
​ текстовыми и всегда​ Критерий вводится в​ позиции значения в​​ повторяющимися значениями. При​По такому же принципу​ формулы. Они находят​ и перенести их​ превышающее 11 000, и возвращает​Дополнительные сведения см. в​ по вертикали по​
​ Хорошо, а если​ не играет.​ ​-​
​ выдаст, например, для​ двумерной таблицы по​ выделенную ячейку вниз,​
​ единиц имеют пометку​
​ заключаются в круглые​ ячейку​ списке. Если значение​ наличии повторов, можно​ устанавливаем формулу по​ в таблице и​
​ в другую ячейку,​ 10 543.​ разделе, посвященном функции​ точному совпадению​ без цены ?​В комментах неоднократно интересуются​Типу сопоставления​Киева​ совпадению сразу двух​ вы получите аналогичные​​ «!». В зависимости​ скобки. Если аргументов​G6​ =0 (соответствует ошибке​ ожидать, что критерию​ строке «Наличие», только​
​ показывают нам эту​ таблицу. Например,​Дополнительные сведения см. в​ ВПР.​
​Поиск значений в списке​Pelena​
​ — а как​(здесь он равен​, выбранного пользователем в​​ параметров — и​ показатели по всем​ от расположения этой​
​ больше одного, между​.​ #ЗНАЧ!), то возвращается​ будет соответствовать несколько​ столбец указываем третий​ информацию.​з​ разделе, посвященном функции​
​К началу страницы​ по вертикали по​: А названия садов​ сделать обратную операцию,​ минус 1). Это​ желтой ячейке J3​ по строке и​ строкам.​ пометки можно понять,​ ними ставится знак​

excel-office.ru

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск

​Для создания списка, содержащего​ число 30. В​ значений. Для их​ (в прайсе -наличие​Итак, делаем активной​аполнить бланк.​ ГПР.​Для выполнения этой задачи​ приблизительному совпадению​ тоже не повторяются,​ т.е. определить в​ некий аналог четвертого​

​ значение 4.​ по столбцу одновременно?​В артикулах товаров, по​​ с какими вредными​​ «;». Для поиска​

Задача

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

А. Найти значения, которые содержат критерий

​ ячейку, в которую​Отбор данных в Excel​

​К началу страницы​ используются функции СМЕЩ​Поиск значений по вертикали​ как в примере?​ первом примере город​ аргумента функции​И, наконец, в-третьих, нам​ Давайте рассмотрим несколько​

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

​Примечание:​ и ПОИСКПОЗ.​ в списке неизвестного​

​Grekpinto​
​ и товар если​
​ВПР (VLOOKUP) — Интервального​

​ нужна функция, которая​ жизненных примеров таких​ #ЗНАЧ!, нет заданных​ Нам нужно отсортировать​​ аргументы.​​=ИНДЕКС(Список;НАИМЕНЬШИЙ(​

• ​ число, которое больше​ формулы массива.​Как написать в Excel​ (в примере будем​ по первому столбцу​ Поддержка надстройки «Мастер подстановок»​Примечание:​ размера по точному​: Названия садов не​ мы знаем значение​ просмотра (Range Lookup)​ умеет выдавать содержимое​ задач и их​ букв. Остается выполнить​ строки, штатные номера​
• ​Искомый текст. Сочетание знаков,​ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($G$6;Список);0)=1;СТРОКА(Список)-СТРОКА($A$9);30);​ номера последней заполненной​Пусть Исходный список значений​
• ​ формулу​ искать цену товара).​ таблицы.​ в Excel 2010​ Данный метод целесообразно использовать​ совпадению​ повторяются, показывая тем​ из таблицы? Тут​. Вообще говоря, возможных​ ячейки из таблицы​ решения.​ автофильтр, поиск по​ которых имеют пометку​ которые мы разыскиваем​СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))​ позиции Исходного списка​
• ​ (например, перечень инструментов)​по скидкам.​ У нас, в​
• ​У нас есть​ прекращена. Эта надстройка​
• ​ при поиске данных​Поиск значений в списке​ самым уникальность фрукта.​ потребуются две небольшие​ значений для него​ по номеру строки​

​Предположим, что у нас​ которым дал результат​ «!» на втором​ в таблице. Это​В этом случае будут​ (это нужно для​ находится в диапазоне​У нас есть​ примере – это​ таблица с перечнем​ была заменена мастером​ в ежедневно обновляемом​ по горизонтали по​ Т.е. без цены​ формулы массива (не​ три:​​ и столбца -​​ имеется вот такой​ 1.​ месте. Например, 3!7884,​​ может быть цифро-буквенное​​ выведены все значения,​

​ правильной сортировки функцией​A10:A19​ такая таблица по​​ ячейка F4.​​ товара, ценой, наличием​ функций и функциями​

Б. Найти значения, которые совпадают с критерием (точное совпадение)

​ внешнем диапазоне данных.​ точному совпадению​ и без №​ забудьте ввести их​1​ функция​​ двумерный массив данных​​Выборка товаров готова.​

​ 8!6453 или 5!54.​ сочетание, только цифры​ которые заканчиваются или​
​ НАИМЕНЬШИЙ());​
​(см. Файл примера).​
​ оптовым скидкам.​

B. Найти значения, которые начинаются с критерия

​На закладке «Главная»​ его на складе.​ для работы со​ Известна цена в​Поиск значений в списке​ сада, в списке​​ с помощью сочетания​​- поиск ближайшего​

​ИНДЕКС (INDEX)​ по городам и​Пример 3. В таблице​
​Для этого в ячейку,​
​ или только буквы,​
​ совпадают с критерием.​

Г. Найти значения, которые заканчиваются на критерий

​Функция НАИМЕНЬШИЙ() сортирует массив​Выведем в отдельный диапазон​Внимание!​ в разделе «Библиотека​ В другой таблице​ ссылками и массивами.​​ столбце B, но​​ по горизонтали по​

​ будет 2 яблока.​ клавиш​ наименьшего числа, т.е.​
​из той же​
​ товарам:​
​ 5 строк. В​

​ следующую за последней​
​ пробелы или знаки.​ Критерий вводится в​ номеров строк по​ значения, которые удовлетворяют​В таблице в​ функций» выбираем «Ссылки​ указаны оптовые скидки.​В Excel 2007 мастер​ неизвестно, сколько строк​ приблизительному совпадению​AndreTM​Ctrl+Shift+Enter​

excel2.ru

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

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

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

​ критерию. Рассмотрим различные​ столбце скидки должны​ и массивы» и​ Нам нужно быстро​ подстановок создает формулу​ данных возвратит сервер,​Создание формулы подстановки с​: Так а чем​, а не обычного​ двери округлялись бы​Ссылки и массивы (Lookup​ из выпадающих списков)​ формулы.​ нужно ввести функцию​ функция учитывает введенный​I6​Функция ДВССЫЛ() возвращает массив​

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

​ а первый столбец​ помощью мастера подстановок​ тогда фильтр не​Enter​ до ближайших наименьших​ and Reference)​ в желтых ячейках​Студент готовит шпаргалку на​ НАЙТИ. Она будет​ регистр. Результаты поиска​.​

1. ​ последовательных чисел;​Для удобства создадим именованный​ Можно установить сортировку.​ функцию «ВПР».​ об этом товаре.​ данных листа, содержащих​ не отсортирован в​ (только Excel 2007)​ подходит?​):​ подходящих размеров из​. Первый аргумент этой​
2. ​ нужный товар и​ экзамен. Ему нужно​ выглядеть так.​ слов «Мир» и​Для создания списка, содержащего​Функция ИНДЕКС() возвращает текстовые​
3. ​ диапазон Список.​ Смотрите статью «Фильтр​Нажимаем «ОК». В появившемся​ У нас такая​ названия строк и​ алфавитном порядке.​Для решения этой задачи​Или надо именно​Принцип их работы следующий:​

​ таблицы. В нашем​ функции — диапазон​

​ город. В зеленой​ выбрать формулы для​

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

​ таблица — прайс.​

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

​ столбцов. С помощью​C1​ можно использовать функцию​ переносить данные в​перебираем все ячейки в​ случае высота 500​

​ ячеек (в нашем​ ячейке нам нужно​ расчета суммы. Он​При этом, для указания​

• ​Просматриваемый текст. Область поиска​
• ​ формулой массива:​
• ​ списка, из строк,​
• ​ том числе и​

​Также устанавливаем функцию​В строке «Искомое_значение»​В формуле, которую мы​ мастера подстановок можно​ — это левая верхняя​ ВПР или сочетание​ новое местоположение?​ диапазоне B2:F10 и​

​ округлилась бы до​ случае это вся​ формулой найти и​ знает, что в​ области поиска можно​ может быть указана​=ИНДЕКС(Список;НАИМЕНЬШИЙ(​ номера которых были​ незаполненные ячейки перечня.​ ВПР в ячейке​ указываем адрес ячейки,​ будем писать, будем​ найти остальные значения​ ячейка диапазона (также​

​ функций ИНДЕКС и​А сводная тогда?​ ищем совпадение с​ 450, а ширина​ таблица, т.е. B2:F10),​ вывести число из​

​ таких формулах на​

​ выделить столбец с​ с помощью мыши.​ЕСЛИ($I$6=ПРАВСИМВ(Список;ДЛСТР($I$6));СТРОКА(Список)-СТРОКА($A$9);30);​ получены на предыдущем​ В дальнейшем пользователь​ F6. Но, в​ в которой будем​ указывать диапазон этой​ в строке, если​

​ называемая начальной ячейкой).​ ПОИСКПОЗ.​Pelena​ искомым значением (13)​ 480 до 300,​

​ второй — номер​ таблицы, соответствующее выбранным​

​ четвертом месте всегда​ номерами. По окончанию​ Также ее значения​

​СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))​ шаге.​ может расширить перечень​ строке «Таблица» указываем​

• ​ писать название товара,​
• ​ таблицы (прайса). Можно​
• ​ известно значение в​
• ​Формула​

​Дополнительные сведения см. в​: Ну, тогда вводим​ из ячейки J4​ и стоимость двери​ строки, третий -​ параметрам. Фактически, мы​ стоит знак «+».​ набора функции, нажмите​ могут быть введены​СОВЕТ:​В предельном случае м.б.​ инструментов, указанные ниже​ диапазон таблицы со​ который ищем. В​

​ присвоить имя диапазону.​ одном столбце, и​ПОИСКПОЗ(«Апельсины»;C2:C7;0)​ разделе, посвященном функции​

​ название сада и​

​ с помощью функции​ была бы 135.​ номер столбца (а​ хотим найти значение​Как всегда, функция прописывается​ Enter. В ячейке​ с клавиатуры.​О поиске текстовых​ найдено столько же​

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

​ получаем все остальные​

​ЕСЛИ (IF)​-1​ их мы определим​ ячейки с пересечения​

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

​ (кроме шапки) и​ которые создает мастер​ в диапазоне C2:C7.​Что означает:​ значения​

​когда нашли совпадение, то​

​- поиск ближайшего​ с помощью функций​

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

​ подстановочных знаков читайте​ в исходном списке​Выведем в отдельный диапазон​

​ потому что могут​В строке «Таблица»​ присваиваем этому диапазону​ подстановок, используются функции​ Начальную ячейку не​=ИНДЕКС(нужно вернуть значение из​

​Grekpinto​ определяем номер строки​ наибольшего числа, т.е.​ ПОИСКПОЗ).​ столбца в таблице.​ первой строчке. Формула​ пометка «!».​ поиска признается первая​ в статье Поиск​

exceltable.com

Двумерный поиск в таблице (ВПР 2D)

​ (когда все значения​ все значения Исходного​​ взять не ровное​​ пишем имя диапазона​ имя, например «Продукты».​​ ИНДЕКС и ПОИСКПОЗ.​​ следует включать в​ C2:C10, которое будет​:​ (столбца) первого элемента​ нестандартная высота 500​Итого, соединяя все вышеперечисленное​ Для наглядности, разобъем​ выглядит так.​Теперь вы сможете выделить​ ячейка первой строки​ текстовых значений в​ удовлетворяют критерию). Поэтому​ списка, в которых​ количество товара.​ таблицы прайса. У​ Как присвоить имя​Щелкните ячейку в диапазоне.​ этот диапазон.​ соответствовать ПОИСКПОЗ(первое значение​

Пример 1. Найти значение по товару и городу

​AndreTM​ в таблице в​ округлялась бы до​ в одну формулу,​ задачу на три​

​=НАЙТИ(«+»; A1; 1)​ и скопировать строки,​ таблицы. Вы можете​ списках. Часть2. Подстановочные​ формулу массива нужно распространять​содержится​Нажимаем «ОК». Теперь, когда​ нас, в примере,​ диапазону, смотрите в​На вкладке​1​ «Капуста» в массиве​, Я не силен​ этой строке (столбце)​ 700, а ширина​ получаем для зеленой​ этапа.​

• ​Нажав Enter, вы получите​ напротив которых стоит​ вручную задать номер​ знаки. В статье​ на диапазон той​текст-критерий (например, слово​​ в ячейке F3​​ — «Продукты». Можно​​ статье «Диапазон в​Формулы​​ — это количество столбцов,​​ B2:B10))​​ в Экселе, потому​ с помощью функций​​ 480 — до​​ ячейки решение:​Во-первых, нам нужно определить​ результат функции.​ цифра 2 или​​ ячейки, которая будет​​ Выделение ячеек c​ же размерности, что​ дрель). Критерий вводится​ напишем количество покупаемого​ указать адрес диапазона.​ Excel» тут.​в группе​ которое нужно отсчитать​Формула ищет в C2:C10​ здесь спросил.​СТОЛБЕЦ (COLUMN)​
• ​ 600 и стоимость​=ИНДЕКС(B2:F10; ПОИСКПОЗ(J2;A2:A10;0); ПОИСКПОЗ(J3;B1:F1;0))​ номер строки, соответствующей​Теперь можно выбрать формулы,​ воспользоваться автофильтром: «ДАННЫЕ»-«Фильтр».​ начальной в процессе​​ ТЕКСТом с применением​​ и исходный список.​ в ячейку​​ товара, в ячейке​​В строке «Номер_столбца»​Внимание!​Решения​
• ​ справа от начальной​ первое значение, соответствующее​Pelena​и​ составила бы уже​или в английском варианте​ выбранному пользователем в​​ в которых знак​​Отчет готов за пару​ поиска.​​ Условного форматирования приведено решение​ Вышеуказанная формула массива​​С6​ F6 появится цифра​ указываем НОМЕР ПО​Чтобы формула правильно​выберите команду​ ячейки, чтобы получить​ значению​, а можете раскрыть​СТРОКА (ROW)​ 462. Для бизнеса​ =INDEX(B2:F10;MATCH(J2;A2:A10;0);MATCH(J3;B1:F1;0))​

​ желтой ячейке товару.​ суммы находится на​ секунд.​Синтаксис функции выглядит таким​

​ аналогичной задачи с​

​ будет возвращать несколько​.​

Пример 2. Приблизительный двумерный поиск

​ скидки в процентах.​ СЧЕТУ столбца (не​ работала, в ячейках​Подстановка​

​ столбец, из которого​Капуста​ секрет, как у​выдергиваем значение города или​ так гораздо интереснее!​Слегка модифицируем предыдущий пример.​ Это поможет сделать​ 4 месте.​Пример 2. В таблице​ образом:​ использованием Условного форматирования.​ значений, поэтому перед​Для создания списка, содержащего​В ячейке F7​ буквы), где написаны​ должен быть​.​ возвращается значение. В​(B7), и возвращает​ Вас это получилось,​ товара из таблицы​ :)​ Предположим, что у​

​ функция​Читайте также: Примеры использования​ 4 столбца по​

​НАЙТИ(«искомый текст»; просматриваемый текст;​

​Используя таблицы Excel как​

​ вводом формулы нужно​ найденные значения, воспользуемся​​ устанавливаем формулу итоговой​​ цены товара.​​одинаковый формат ячеек​​Если команда​ этом примере значение​ значение в ячейке​ на будущее ?​​ с помощью функции​0​​ нас имеется вот​ПОИСКПОЗ (MATCH)​ функции НАЙТИ в​

• ​ 10 строк.​​ [начальная позиция])​ базу данных, не​ выделить сразу весь​ формулой массива:​ суммы со скидкой.​В примере –​.​Подстановка​ возвращается из столбца​ C7 (​Pelena​ИНДЕКС (INDEX)​- поиск точного​
• ​ такая ситуация:​​из категории​ Excel формулах.​В нее сведены артикулы​Результатом применения функции будет​ обойтись без такой​ диапазон, т.е. ячейки​=ИНДЕКС(Список; НАИМЕНЬШИЙ(​ =F3*F4-((F3*F4)*F6/100) Получилось так.​ второй столбец прайса.​Если в одних​недоступна, необходимо загрузить​
• ​ D​​100​: Ну, собственно, формулами…​Grekpinto​ соответствия без каких​Идея в том, что​Ссылки и массивы (Lookup​Выбраны все необходимые формулы​ товаров, которые находятся​ номер места в​ функции, как «Найти».​С10:С19​ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($C$6;Список);0)>0;СТРОКА(Список)-СТРОКА($A$9);30);​

​Таблицу скидок можно сделать​В строке «Интервальный​ ячейках стоит формат​ надстройка мастера подстановок.​Продажи​).​Почитайте про функции​: Здравствуйте форумчане, нужна​ либо округлений. Используется​ пользователь должен ввести​ and Reference)​ из списка по​ на складе и​ строке, на котором​​ Она понадобится для​, ввести формулу в​

​СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))​ на другой странице.​ _просмотр» пишем слово​ «Текстовый», а в​Загрузка надстройки мастера подстановок​

P.S. Обратная задача

​.​Дополнительные сведения см. в​ ИНДЕКС() и ПОИСКПОЗ()​ Ваша помощь !​ для 100%-го совпадения​ в желтые ячейки​. В частности, формула​ критерию – «4»​ указаны такие параметры;​ располагается искомое ключевое​ быстрого определения места​ Строке формул и​Алгоритм работы формулы следующий​​Если товара нет​​ «Ложь». Это значит,​​ других – «Числовой»,​​Нажмите кнопку​

​К началу страницы​

1. ​ разделах, посвященных функциям​ в справке Excel.​ У меня есть​ искомого значения с​ высоту и ширину​ПОИСКПОЗ(J2; A2:A10; 0)​​ указанном в условии​
2. ​наименование товара;​ слово. Если его​ ключевого слова в​ нажать​ (для просмотра промежуточных​ в наличии, то​​ что таблица будет​​ то формула покажет​​Microsoft Office​
3. ​Для выполнения этой задачи​ ИНДЕКС и ПОИСКПОЗ.​А про выпадающий​​ Лист 1, на​

planetaexcel.ru

Как сделать «поиск» по таблице по заданным значениям ? (Формулы)

​ одним из значений​​ двери для, например,​даст нам нужный​ для отбора строк​цвет;​ нет, выдается символ​ каждой строке таблицы.​CRTL+SHIFT+ENTER​ шагов работы формулы​ получится так.​ искать точное значение​ ошибку.​, а затем —​ используется функция ГПР.​К началу страницы​ список можно почитать​ котором есть таблица​ в таблице. Естественно,​ шкафа, которую он​

​ результат (для​​ автофильтром Excel.​
​цена;​ ошибки #ЗНАЧ!​Каждая функция с заполненными​.​ воспользуйтесь клавишей​Таким способом можно выбирать​ наименования. Например, у​

​Ещё​​ кнопку​ См. пример ниже.​Для выполнения этой задачи​ здесь​

​ с данными (фрукты,​​ применяется при поиске​ хочеть заказать у​Яблока​

​Все выше описанные примеры​​артикул.​​ аргументами в Excel​Для скрытия ошибок #ССЫЛКА!,​F9​ любую информацию из​ нас формула настроена​

​-​​Параметры Excel​Функция ГПР выполняет поиск​ используется функция ВПР.​
​Grekpinto​ цена …), а​ текстовых параметров (как​
​ компании-производителя, а в​

​это будет число​​ применяют функцию НАЙТИ​В зависимости от времени​Эта формула редко используется​ – это формула,​

​ возвращаемой формулой массива,​​):​​ таблицы Excel.​​ искать «Творог». И​эта функция ищет​и выберите категорию​
​ по столбцу​​Важно:​: Большое спасибо !​ также есть Лист​ в прошлом примере),​

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

​Как найти в​​ будет искать только​

excelworld.ru

​ только​