Использование функции ВПР (VLOOKUP) для подстановки значений

Кому лень или нет времени читать - смотрим видео. Подробности и нюансы - в тексте ниже.

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

Итак, имеем две таблицы - таблицу заказов и прайс-лист:

vlookup1.gif

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

Решение

В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP). Эта функция ищет заданное значение (в нашем примере это слово "Яблоки") в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:

vlookup2.gif

Для простоты дальнейшего использования функции сразу сделайте одну вещь - дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме "шапки" (G3:H19), выберите в меню Вставка - Имя - Присвоить (Insert - Name - Define) или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы - Вставка функции (Formulas - Insert Function). В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:

vlookup3.png

Заполняем их по очереди:

  • Искомое значение (Lookup Value) - то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае - слово "Яблоки" из ячейки B3.
  • Таблица (Table Array) - таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя "Прайс" данное ранее. Если вы не давали имя, то можно просто выделить таблицу, но не забудьте нажать потом клавишу F4, чтобы закрепить ссылку знаками доллара, т.к. в противном случае она будет соскальзывать при копировании нашей формулы вниз, на остальные ячейки столбца D3:D30.
  • Номер_столбца (Column index number) - порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.
  • Интервальный_просмотр (Range Lookup) - в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:
      • Если введено значение 0 или ЛОЖЬ (FALSE), то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, "Кокос"), то она выдаст ошибку #Н/Д (нет данных).
      • Если введено значение 1 или ИСТИНА (TRUE), то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия, т.е. в случае с "кокосом" функция попытается найти товар с наименованием, которое максимально похоже на "кокос" и выдаст цену для этого наименования. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле! Так что для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать. Исключением является случай, когда мы ищем числа, а не текст - например, при расчете Ступенчатых скидок.

Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.

Ошибки #Н/Д и их подавление

Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если:

  • Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.
  • Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.
  • Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
    =ВПР(ТЕКСТ(B3);прайс;0)
    Подробнее об этом можно почитать тут.
  • Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:
    =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)
    =VLOOKUP(TRIM(CLEAN(B3));прайс;0)

Для подавления сообщения об ошибке #Н/Д (#N/A) в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR). Так, например, вот такая конструкция перехватывает любые ошибки создаваемые ВПР и заменяет их нулями:

=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)

=IFERROR(VLOOKUP(B3;прайс;2;0);0)

P.S.

Если нужно извлечь не одно значение а сразу весь набор (если их встречается несколько разных), то придется шаманить с формулой массива. или использовать новую функцию ПРОСМОТРX (XLOOKUP) из Office 365.

Ссылки по теме 

 


Страницы: 1  2  3  
02.04.2013 16:33:29
Отличное руководство. Спасибо.
21.04.2017 11:39:09
Поддерживаю!
У Николая лучшее объяснение как работает функция ВПР из всех, которые я встречал!…
28.04.2013 16:07:50
Спасибо огромное за Ваш труд!!! Отличные уроки!!!! Граммотная речь, темп в норме, понятное объяснение!!! Многое открыла для себя из Ваших уроков. Подскажите где найти уроки подряд. Спасибо!!!!!
01.05.2013 00:59:51
Не за что - стараемся по мере сил и способностей :) Все мои видеоуроки можно найти (и подписаться на них) на канале planetaexcel на YouTube.
05.05.2013 11:41:21
хорошая статья! спасибо!!!
03.06.2013 15:31:07
Подскажите пожалуйста, если введено значение ИСТИНА (TRUE), то это значит, что идет поиск не точного, а приблизительного, МЕНЬШЕГО  ПО ЗНАЧЕНИЮ ИЛИ РАВНОГО, соответствия.
А как сделать, чтобы искалось так же и бОльшее по значению соответствие, просто максимально приближенное число?  
04.06.2013 23:40:14
Анастасия, посмотрите как работают функции ИНДЕКС и ПОИСКПОЗ, заменяя ВПР. В этом случае можно выбирать - в какую сторону работает округление при приблизительном поиске.
04.06.2013 17:24:46
А если мне нужно найти значение, просматривая таблицу СНИЗУ?
04.06.2013 23:38:47
А почему бы не отсортировать ее наоборот предварительно тогда?
11.06.2013 15:19:32
Подскажите пожалуйста, всё сделал как написано у Вас, во вкладке Аргументы функции все срабатывает, а в строке таблицы формула а не искомое значение. Как быть?
13.06.2013 09:25:02
Проверьте формат ячейки - он, наверняка, текстовый. Исправьте на Общий (General) и введите формулу повторно.
02.07.2013 15:16:37
Добрый день! Подскажите, пожта, каким способом можно использовать функцию ВПР если происходит поиск значения, которое является частью текста в ячейке? Т.е. если ячейка состоит из одного значения - работает, а если значение "зашито" в ячейку - ВПР ее не распознает.

Заранее спасибо.
10.08.2013 01:24:56
Евгений, боюсь, что ВПР тут не поможет. Попробуйте применить технику поиска ближайшего похожего слова.
03.07.2013 11:43:20
Добрый день. Подскажите, как установить настройки функции так, чтобы при поиске из списка (столбец с названиями линий метро) у меня отображался цвет заливки ячейки соответствующий цвету ветки метрополитена?Спасибо.
10.08.2013 01:23:36
Марина, при помощи самой функции ВПР (и любой другой функции Excel, кстати) цвет ячеек менять нельзя в принципе. Но можно определять с ее помощью название линии метро и на ячейку с названием назначить правило условного форматирования (вкладка Главная - Условное форматирование), которое и будет перекрашивать ячейку в нужный цвет.
08.08.2013 19:25:44
Здравствуйте.
Подскажите,пожалуйста, функция будет работать только когда наименование в таблице заказов ТОЧНО совпадает с наименованием в прайс-листе? А можно как-то сделать что-бы при внесении наименования вручную в строке, например "ябло..." выпадал список с всеми существующими позициями в прайс листе, например "яблоки сладкие, яблоки красные и тд"  чтобы выбрать нужную позицию.
Спасибо
11.08.2013 01:35:33
Спасибо Вам, Николай, как раз то что надо! :)
15.08.2013 13:15:13
Николай, вопрос. Если в "таблице заказов" несколько строк "яблоки", "банан" - то почему то не все строки заполняются.  В некоторых выпадает НД. Как этого избежать?
08.10.2013 11:30:24
Проверьте закрепление адресов ($) в ссылке на таблицу в функции ВПР.
Также НД может возникать по куче других причин - см. статью выше.
Здравствуйте!
Скажите, пожалуйста, возможно ли с помощью ВПР подтянуть значения из двух разных таблиц в третью, сводную?
То есть, если показывать на уже изложенном Вами примере, возможно ли в таблицу заказов подставить цены из двух таких прайс-листов?
08.10.2013 11:32:31
Я бы сделал два столбца с двумя функциями ВПР, подтягивающими данные из двух прайс-листов. Причем НД заменял бы на нули. Тогда сумма этих столбцов дала бы результирующий итог.
15.10.2013 15:05:37
спасибо Вам огромное, господин Павлов за помощь!
02.11.2013 23:43:38
Николай,здравствуйте! Спасибо, с Вашей помощью научилась пользоваться функцией ВПР.  Не могли бы помочь ещё в одном вопросе :  возможно ли соединить функции ВПР и ЗАМЕНИТЬ (либо ПОДСТАВИТЬ)? Например, если взять Ваш пример с  функцией ВПР : в столбце Н цены с точкой ,  при вставке данных  в столбец D с помощью функции заменять точку на запятую.
25.11.2013 18:31:12
Николай огромное спасибо вам за ваш сайт и статью!  
27.11.2013 14:17:15
Спасибо, Николай, за доходчивое и наглядное обучение функции ВПР.
Возникает вопрос: для того, чтобы подтянуть значения из однотипных таблиц, расположенных на разных листах, приходится в ручную (либо с помощью приема "найти и заменить") в аргументе "Таблица" заменять название  нужного листа (при этом сам диапазон остается прежним, меняется лишь название листа). Дело в том, что когда листов множество (30 и более) процесс замены названия листов затягивается. Как решить данную проблему?
Технически можно сделать так, чтоб название листа бралось из ячейки, значение которой бы равнялось названию листа, но настроить данную формулу не получается...
11.12.2013 19:40:24
Копайте в строну функции ДВССЫЛ - она умеет преобразовывать текст в ссылку. Если с помощью функции СЦЕПИТЬ склеить из кусочков нужный адрес таблицы с нужного листа, то ДВССЫЛ потом превратит его в настоящую ссылку, которую можно подставить во второй аргумент ВПР.
14.12.2013 04:11:07
Не совсем пойму как это реализовать. Например, вот второй аргумент функции ВПР: (Омск!$A$3:$B$4). Предположим, в ячейке A1 находится название нужно листа (Москва!), а в ячейке В2 диапазон таблицы ($A$3:$B$4). Тогда второй аргумент должен выглядеть так: ДВССЫЛ(СЦЕПИТЬ(A1;B1)) ?
Но в таком виде функция работать не будет. Прошу помочь в данном вопросе, я думаю он может быть актуален многим, кто ВПРит с разных листов.
15.12.2013 09:51:39
Почему вы решили, что "в таком виде функция работать не будет"?
Вот такая формула, например:
=ВПР(D1;ДВССЫЛ(СЦЕПИТЬ(A1;B1)));2;0)
отлично будет искать содержимое ячейки D1 с текущего листа на в диапазоне на другом листе, который склеивается из ячеек А1 и B1.
15.12.2013 11:28:09
Да, действительно, все отлично работает! Огромное СПАСИБО.
09.12.2013 13:19:24
Добрый день!
Подскажите как подставить данные в третий столбик по первым двум? В первом столбике данные меняются, всего 3 значения, во втором 6 значений.
11.12.2013 19:46:08
Олег, если склеить данные из первых двух столбцов с помощью функции СЦЕПИТЬ, то мы получим уникальный код, по которому ВПР и может подставлять данные в третью ячейку из заранее подготовленной таблицы, где перечислены все возможные сочетания первых двух ячеек.
26.12.2013 08:00:44
Здравствуйте, Николай!
В статье про связанные выпадающие списки (в "способе 2") приводится конструкция динамического диапазона через функции СМЕЩ, ПОИСКПОЗ, СЧЁТЕСЛИ. Попробовал использовать подобную конструкцию для задания второго аргумента функции ВПР - выдаёт ошибку "#Н/Д". Причём использовал два способа - прописывал в самой формуле и задавал именованный диапазон - результат одинаковый. Можно ли для ВПРа использовать данную конструкцию и какие тут есть подводные камни?
Формула получилась следующего вида:
=ВПР(J3;СМЕЩ(Лист2!$C$1;ПОИСКПОЗ(Лист1!$H$3;Лист2!$B:$B;0)-1;1;СЧЁТЕСЛИ(Лист2!$B:$B;Лист1!$H$3);2);2;0)
26.12.2013 16:16:11
Степан, однозначно можно - я так сто раз делал. Ищите ошибку в формуле. Советую сначала сделать именованный диапазон с формулой СМЕЩ с помощью Диспетчера Имен на вкладке Формулы, а потом просто подставить имя созданного динамического диапазона в работающий ВПР.
30.12.2013 07:33:06
Спасибо, Николай. Проблема действительно в формуле была - промахнулся немножко с диапазоном.
11.02.2014 09:39:24
Добрый день, а не подскажите, с помощью функции ВПР или чего-нибудь другого можно подтянуть примечание к ячейке?
Вообще задача в следующем: у меня есть таблица с порядковым номером и в соседней ячейке в примечании рисунок. Необходимо подтянуть в другую таблицу по номеру, чтобы это примечание осталось. Мне главное не сколько примечание, сколько этот рисунок, другого способа как закрепить рисунок за ячейкой, кроме как через примечание я не нашёл. Ссылка на картинку в ячейке не подойдёт. Заранее спасибо!
17.02.2014 08:29:18
Добрый день, Николай! Большое Вам спасибо за информацию по ВПР.
Столкнулась с проблемой применения вложенной функции ТЕКСТ в указанном примере =ВПР(ТЕКСТ(B3);прайс;ЛОЖЬ), функция ТЕКСТ имеет 2 обязательных аргумента и в приведенном виде не работает (у меня, по крайней мере). Моя цель проставить цены из Таблицы Прайс-лист в Таблицу Заказов нужном формате (его можно взять из Таблицы Прайс-лист или прописать в аргументе функции ТЕКСТ), возможно ли это - то есть одновременно - при заборе данных ?
02.04.2014 10:52:08
Ирина, попробуйте использовать функцию преобразования в текст в виде:
=ТЕКСТ(B3;"#")
02.04.2014 10:42:34
Добрый день! А как сделать так чтоб при появлении в ячейке определенного слова в другой таблице заполнись данные из строки той самой ячейки? коротко говоря у меня есть строка заказа и когда в столбце "состояни заказа" будет значение "готов" данные этого заказа "размер , цена, категория и т.д." будут автоматически добавляться строкой в таблице склада. заранее благодарен
02.04.2014 10:48:54
Такое добавление можно только макросом организовать.
ann
10.04.2014 14:01:15
добрый день,  почему при вычислении формулы в искомом значении отображаются нужные данные а в ячейке прописывается сама формула?
 
19.04.2014 09:42:44
У вас, скорее всего, текстовый формат у ячейки с формулой. Поменяйте его на Общий и введите формулу еще раз.
23.04.2014 15:20:47
Да функция ограниченная, вот например я не могу выполнить поиск по двум критериям и уже из массива присвоить значение в ячейку, базируясь на 2-х критериях, как это сделать?Спасибо
07.05.2014 12:26:14
Сцепить два ваших критерия в отдельном столбце в один и делать ВПР по нему.
Либо написать свой вариант ВПР на Visual Basic :)
12.05.2014 17:37:46
Здравствуйте Николай! У меня есть рабочая задача. Создана база населенных пунктов страны порядка 14 тыс. значений, есть много повторяющихся названий.
База содержит информацию по расстоянию от ближайшего, определенного мной, населенного пункта, где есть сотрудник компании.  
От заказчика приходит запрос со списком населенных пунктов и из общей базы обычно при помощи ВПР, в два клика расстояния найдены, только часто возникают ошибки из-за повторяющихся в базе названий населенных пунктов. Как вы считаете, каким наиболее удобным способом можно было бы решить эту проблему. если необходимо предоставлю файлы.  Спасибо!
14.05.2014 14:53:57
Диана, я бы склеил в один столбец (с помощью функции СЦЕПИТЬ) название области и населенного пункта, чтобы получить столбец с уникальными неповторяющимися названиями и уже по нему искал ВПРом.
14.05.2014 18:06:16
Спасибо Николай, да, это вариант, только иногда, населенные пункты в списке бывают без области, т.е для начала нужно найти какой области этот населенный пункт соответствует, и эта область присваивается неправильно так как ВПР находит первый попавшийся в списке, соответствующий населенный пункт. А есть ли способ, если такие повторы существуют, чтобы выдавался не один результат, а сразу несколько вариантов или например их количество (повторов) тогда уже и легко будет проверить вручную какой населенный пункт нужен?
01.08.2014 16:37:28
Подскажите пожалуйста, если в прайсе есть яблоки и еще одна позиция яблоки. Одинаковое название, но разные цены, и изменить наименование нет возможности. А функция "впр" берет только первое значение. Есть ли возможность, чтобы значения  складывались?
02.08.2014 16:26:32
Да, вам нужна функция СУММЕСЛИ.
26.09.2014 12:27:49
Подскажите, почему формула не работает? Составил Таблицу из 2-х столбцов, в первом Наименование, во втором параметр. Рядом 2 ячейки - выбор Наименования (через выпадающий список), во второй должно ставиться значение через формулу ВПР. Но появляются ошибки: При выборе Строчки "Ручка Опера" значение подставляется неверно, При выборе строчки "Ручка Бридж" значения не находит.
Ручка Н54
Ручка С36
Ручка Сэко16
Ручка Опера37
Ручка Тауэр62
Ручка Бридж2
P.S.: и как сюда пример готовый добавить?
27.09.2014 09:21:54
Никак не добавить - это комментарии. Лучше на форум или мне в почту.
06.10.2014 06:55:20
Добрый день !
Подскажите пожалуйста ,возможно ли найти не первое значение соответствующее искомой ячейке , а сумму этих значений ?
Задача такая ,есть рабочие и количество дней которое отработал каждый. Иногда встречается что один рабочий дважды выходил . Сумму в таблице данных неуднобно вставлять каждый раз , можно ли ,чтобы ВПР суммировал значения ,если совпадает имя рабочего ?
14.10.2014 10:45:53
Доброе время суток) Подскажите, пожалуйста, как разделить любое значение в ячейке на искомое по формуле "ВПР"? Заранее спасибо!
24.10.2014 18:00:43
Спасибо, экономил очень много времении )))));)
05.11.2014 16:27:41
Добрый день!
Подскажите пожалуйста, как построить функцию VLOOKUP, если таблицы находятся на разных Worksheets?
05.11.2014 18:17:21
Галина, да так же, как и в случае обычного использования. Только у второго аргумента добавится перед адресом таблицы имя листа и восклицательный знак как разделитель, т.е. будет примерно так (исходный пример, но прайс на другом листе):
=VLOOKUP(B3;Лист2!$G$3:$H$19;2;0)
06.11.2014 10:23:45
Огромное спасибо за быстрый ответ!!!. Надеюсь у меня получится!
08.12.2014 19:12:09
Помогите, ВПР формулу прописал. Перепроверил. И мне в нужном месте вместо числового значения выдало ошибку #ССЫЛКА! Что делать и как ее обойти. И вообще почему она появилась? Для понимания проблемы... Всё сделал как в указанном примере. Но результат я уже написал.

УРРРЯЯЯЯЯЯ, сам разобрался. Проблема решилась когда я повторно пересмотрел урок и понял, что столбец указал не правильный для цены. Нужно указывать номер стоблца именно в ДИАПАЗОНЕ В КОТОРОМ БУДЕТ ВЫБИРАТЬСЯ ЦЕНА, а не столбец по порядку начиная с первого. :)
15.12.2014 21:21:15
Функцией ВПР пользуюсь успешно, но вот проблема, если в ячейках есть знак * то функция подставляет только то что было до этого знака и находит то что было до этого знака. Как быть?
Б 826Д*60*1001 067,00Нет этого товара в Ч.Р.10 937Б 826Д*60*100
Б 826Д*60*70914,00914,0010 939Б 826Д*60*70
Б 826Д*60*80965,00965,0010 940Б 826Д*60*80
Б 826Д*60*901 016,001 016,0010 941Б 826Д*60*90
Б 826Д*61*90,51 042,001 042,0010 942Б 826Д*61*90,5
Б 826Д*62*961 067,001 067,0010 943Б 826Д*62*96
Б 826Д*63*73965,00965,0010 944Б 826Д*63*73
914,00Б 826Д**
MX
17.12.2014 02:11:42
Николай, спасибо вам огромное! Отличный урок, всё понятно, очень понадобится.
Слету составила себе подобные таблички, задействовала ВПР, всё получилось, так приятно.
Возник вопрос. На вашем примере: допустим, в прайс-листе цена товара стоит третьим столбцом, а вторым номер магазина. Товары одинаковые, магазины разные, цены в них тоже отличаются. В таблице заказов тогда тоже вторым столбцом - номер магазина, и необходимо поместить в ячейку правильную цену, в зависимости от этого номера. Есть ли возможность это сделать "легким движением руки"?
20.12.2014 10:15:01
Николай, как быть в случае если цены в течении периода меняются. Предполагаем что в ТАБЛИЦЕ ЗАКАЗОВ есть столбец ДАТА. И в ПРАЙС-ЛИСТЕ есть столбцы ЦЕНА НА ЯНВАРЬ, ЦЕНА НА ФЕВРАЛ и т.д.

Спасибо.
18.02.2015 09:38:43
Очень полезная штука!
Николай, у меня вопрос.
Есть три таблицы:
1. Список товаров с артикулами и другими данными
2. Таблица соответствия артикула уникальному номеру (2 столбца).
3. Таблица соответствия уникального номера пути к фотографиям. В этой таблице два столбца, в одном уникальный номер, в другом путь к фото товара. Фотографий может быть как одна так и несколько. То есть айди может быть в пяти йчейках напротив которых ячейка с указанием пути.

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

Заранее огромное спасибо!
18.02.2015 15:30:34
Николай, здравствуйте!
У меня вопрос. Может ли ВПР найти артикул с текстом?
Например, "525 393-090" ищем в "Кроссовки 525 393-090"

Заранее огромное спасибо!
23.03.2015 15:34:02
ВПР не может, но можно по-другому:
22.07.2016 03:16:39
Добрый день, Николай!
Пожалуйста, помогите понять работу предложенной вами здесь формулы с функциями ПРОСМОТР и ПОИСК.
Она создана как-то не в соответствии с описанием этих функций, но... как ни странно, работает!

Непонятки вот в чём.

По описанию функции ПРОСМОТР, аргумент №1 -- строка с искомым значением, аргумент №2 -- область-вектор (т.е. одномерная), в которой происходит поиск ячейки со значением, заданным аргументом №1,  аргумент №3  -- область-вектор, из которой выбирается значение-результат. То есть, если в области “аргумент №2” находится ячейка, значение которой равно “аргумент №1” — функция вычисляет порядковый номер этой ячейки в этой области и возвращает значение ячейки уже из области “аргумент №3” с тем же порядковым номером.

Вопрос 1. В этом свете, непонятно в вашем примере, какой смысл имеет на месте аргумента №1 значение 2^15 ?
Вопрос 2. В качестве аргумента №2 в вашей формуле стоит функция ПОИСК, результатом работы которой является вроде как «позиция первого вхождения знака или текстовой строки». А по описанию функции ПРОСМОТР, аргументом №2 должен быть дипазон ячеек (область). Как так?
Вопрос 3. По описанию фукции ПОИСК её аргумент №1 -- строковый (текстовой), а в вашей формуле на этом месте -- дипазон ячеек (область). Как так?
Вопрос 4. Ещё по функции ПОИСК. Когда я скопировал часть вашей формулы (аргумент №2 из ПРОСМОТР) в отдельную ячейку, чтобы посмотреть промежуточный результат, то есть, =ПОИСК(F$2:F$4;A2) — к моему недоумению результатом оказалось #ЗНАЧ! ! То есть, фукция отдельно -- вроде неправильная, но внутри другой функции как-то работает?

Повторю, вся эта конструкция (формула) тем не менее, работает! Шаманство какое-то...  ;-)
22.07.2016 18:25:51
После долгих размышлений и экспериментов я догадался (в экселовской СПРАВКЕ об этом ни пол-слова! :?:), что конструкция ПОИСК(F$2:F$4;A2) возвращает виртуальный массив (!) из 4-х элементов (для удобства далее я его буду условно называть “Просматриваемый_Вектор”), с значениями, являющимися результатом поиска в значении ячейки A2 значений из соответствующих ячеек массива F$2:F$4 (далее буду условно называть “Массив_Примет”),
то есть,
в результирующий массив в элемент Просматриваемый_Вектор(1) помещается результат обычной функции ПОИСК(F$2;A2) (как бы, ПОИСК(Массив_Примет(1);A2)), в элемент Просматриваемый_Вектор(2) — результат ПОИСК(F$3;A2) (как бы, Массив_Примет(2);A2), и т. д.
То есть, в элементы Просматриваемый_Вектор(i) помещается: либо число (найденная в A2 позиция искомой подстроки Массив_Примет(i)), либо значение #ЗНАЧ! (если значение-подстрока Массив_Примет(i) в A2не найдена).

Что-ж, с моими вопросами 2, 3, 4 из предыдущего сообщения вроде разобрались. (За исключением того, что такое использование функции ПОИСК в экселовской Справке — отсутствует, т.е. — это недокументированная возможность?)

Остался вопрос 1, по функции ПРОСМОТР:
Почему для выявления среди элементов массива Просматриваемый_Вектор элемента, содержащего число, их надо сравнивать именно c значением 2^15 ?

И ещё: В экселовской справке по функции ПРОСМОТР отмечено: «Значения в аргументе `просматриваемый_вектор` должны быть расположены в порядке возрастания... в противном случае функция ПРОСМОТР может вернуть неверный результат.».
В то же время, в вашем примере, в возвращаемом функцией ПОИСК виртуальном массиве Просматриваемый_Вектор положение элемента, содержащего число (т.е. соответствующего искомой подстроке) — относительно других элементов произвольное. Как это может сказаться на правильности работы ПРОСМОТР?
14.04.2017 14:51:55
Николай, чувствую, что это решение очень крутое!
Можете пояснить, как оно работает?
Спасибо!
13.03.2015 07:42:32
Здравствуйте Николай.

Я правильно понимаю, что Вы исключили в функции VLOOKUP 2 такую вещь как "приблизительный поиск (Интервальный просмотр=1)" ?
Т.е. она ищет только точно совпадение. Верно ?
У меня потребность именно в приблизительном поиске и лучше (крайне желательно) без сортировки таблицы.
23.03.2015 15:31:54
Сергей, вы о чем? Об этом примере или о надстройке PLEX?
23.03.2015 16:40:54
Я про вашу функцию VLOOKUP 2 (она же есть в составе Plex).
23.03.2015 12:13:34
Здравствуйте, в принципе все работает, только при умножении(на вашем примере) =C3*D3, у меня выскакивает ошибка #ЗНАЧ!, в проверке формулы цифра в ковычках "134"*200, как быть?
23.03.2015 15:31:01
Скорее всего у вас в таблице цена или количество в текстовом формате, т.е. числа как текст.
16.04.2015 16:36:46
Николай, добрый день, а что это может быть за ошибка если ВПР функция совсем не работает, то есть после введения всех 4 аргументов и нажатия ОК в нужной ячейке отражается
=ВПР(A2;$J$2:$L$97864;3;0)
а собственно значения (хотя бы #Н/Д) не выдается. Много раз следовала Вашему уроку, но не получается..
23.04.2015 19:47:35
https://yadi.sk/i/WegiTDoZgCSvZ  - ссылка на файл

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

Проблема решена )
MEP
24.04.2015 15:22:08
Спасибо за макрос. а как изменить код что бы в случае если значение не найдено выводился не 0 а нет данных - НД() ?
Страницы: 1  2  3  
Наверх