Как в впр составить свой вариант

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

ВПР (искомое значение; диапазон поиска; номер столбца с входным значением; 0 (ЛОЖЬ) или 1 (ИСТИНА)).

ЛОЖЬ – точное значение, ИСТИНА – приблизительное значение.

Простейшая задача для функции ВПР. Например, у нас есть список лекарственных препаратов. Наша первая задача – найти стоимость препарата Хепилор.

В ячейке С12 начинаем писать функцию:

1. B12 – поскольку нам нужен Хепилор, выбираем ячейку с предварительно написанным названием искомого лекарства.
2. Далее выбираем диапазон данных B3:D10, где функция будет совершать поиск нужного нам значения. Крайний левый столбец диапазона должен содержать в себе искомый критерий, по которому производится поиск значения.
3. Следующий шаг – указать номер столбца в массиве B3:D10, из которого будет считана информация на одной строке с Хепилором. Столбцы нумеруются слева направо в самом диапазоне, в нашем примере первый столбец – В, но не А, поскольку А лежит вне области диапазона.

Поиск по столбце «Производитель» будет работать точно так же, нужно просто указать последовательность столбца, где находится нужная нам информация – заменяем цифру «3» в формуле (ячейка С27) на цифру «2»:

Есть определённая особенность, связанная со столбцами. Иногда в Excel-файле в таблицах некоторые ячейки объединяют. На картинке ниже в формуле на месте порядкового номера столбца у нас написана цифра «3», но результат – название производителя, а не цена, как в первом примере:

Произошел сдвиг нумерации столбцов как раз из-за наличия объединения ячеек в столбце «Лекарственное средство»: мы объединяли столбцы «H» и «I», зрительно столбец «Лекарственное средство» — это первый столбец, а «Производитель» — второй, НО формула нумерует их следующим образом:

• H – первый;
• I – второй;
• J – третий;
• K – четвертый.

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



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

В следующем примере рассмотрим, как ещё мы можем использовать функцию для поиска и получения информации по критериям и комбинирование функции с функцией ЕСЛИОШИБКА. Например, мы имеем два отчета – отчет о количестве товара и отчет о цене за единицу товара, которые нам необходимы для подсчета стоимости. Опять же, с небольшим количеством данных это вполне можно сделать вручную, но, когда мы имеем большой объем, справиться с этим скорее и эффективнее нам поможет функция ВПР. В ячейке D3 начинаем писать функцию:

1. B3 – критерий, по котором проводим поиск данных.
2. F3:G14 – диапазон, по котором наша функция будет осуществлять поиск совпадения критерия и данных по строке.
3. Цифра «2» — номер столбца с нужной нам информацией по критерию.
4. Цифра «0» (или можно использовать слово «ЛОЖЬ») — для точности результатов.

Таким образом, когда мы задаем формуле искомый критерий, она начинает поиск совпадений с верхней ячейки первого столбца (шаг 1 на картинке). Затем функция «читает» все критерии сверху вниз, пока не найдет точное совпадение (шаг 2). Когда ВПР дойдет до Хепилора, она отсчитает нужное количество столбцов вправо (шаг 3) и выдаст нам искомое значение для критерия – цену 86,90 (шаг 4):

Но сейчас у нас есть данные только по первому критерию. Для того чтобы заполнить третий столбец D первой таблицы до конца, нужно просто скопировать функцию до последнего критерия. Однако, на этом этапе для корректной работы диапазон, где совершается поиск, нужно закрепить, иначе массив данных «съедет» вниз и у нас ничего не получится. Для этого используем абсолютные ссылки для диапазона в ячейке D3 – выделяем курсором диапазон F3:G14 и нажимаем клавишу F4, после чего совершаем копирование формулы до конца таблицы:

В итоге мы получаем необходимый нам результат:

Однако, наш пример базировался на полном соответствии критериев с обеих таблиц – одинаковое количество товаров, одинаковые наименования. Но что, если, например, убрать последние четыре товара с отчета по ценам за упаковку? Тогда у нас будет ошибка #Н/Д в первой таблице в тех позициях, которые находятся на одной строке с искомым критерием:

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

В результате мы получим красиво оформленную таблицу с надлежащим видом:

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

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

Как видим, размер премии зависит от того диапазона по системе премирования, куда попал показатель выполнения продаж конкретного сотрудника. Мы видим, что если план выполнен менее, чем на 100% — премия не присваивается, а если на 107% (выше 100%, но меньше 110%), тогда сотрудник получает премию размером 10%. Описанные показатели премии нам нужно вписать с помощью функции ВПР в столбец «Премия» первой таблицы, только на этот раз критерий будет находиться в определённом диапазоне.

Для корректной работы нужно убедиться, что границы диапазонов во второй таблице крайнего левого столбца размещены по возрастанию сверху вниз (шаг 1). Формула берёт выбранный нами критерий и осуществляет поиск в первом столбце второй таблицы (шаг 2), просматривая все значения сверху вниз (шаг 3). Как только функция находит первое значение, которое превышает критерий с первой таблицы, делает «шаг назад» (шаг 4) и считывает значение, которое соответствует найденому критерию (шаг 5). Иными словами, при неточном поиске функция ВПР ищет меньшее значение для искомого критерия:

Таким образом, наша функция будет выглядеть так:

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

Скачать все пошаговые примеры функции ВПР в Excel

Например, сотрудник Ольга имеет премию размером 0%, поскольку она выполнила 76% продаж, тоесть перевыполнила план на 0%. А сотрудник Наталья совершила продажи на 21% выше нормы и была премирована на 20%, что мы и видим, если сравнить самостоятельно данные с двух таблиц.

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

Самый быстрый ВПР

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

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

Когда я писал свою первую книжку пять лет назад, то уже делал сравнительный скоростной тест различных способов поиска и подстановки данных функциями ВПР, ИНДЕКС+ПОИСКПОЗ, СУММЕСЛИ и др. С тех пор сменилось три версии Office, появились надстройки Power Query и Power Pivot, кардинально изменившие весь процесс работы с данными. А в прошлом году ещё и обновился вычислительный движок Excel, получив поддержку динамических массивов и новые функции ПРОСМОТРХ, ФИЛЬТР и т.п.

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

Подопытный кролик

Тест будем проводить на следующем примере:

Это книга Excel с одним листом, где расположены две таблицы: отгрузки (500 000 строк) и прайс-лист (600 строк). Наша задача — подставить цены из прайс-листа в таблицу отгрузок. Для каждого способа будем вводить формулу в ячейку С2 и копировать вниз на весь столбец, замеряя время, которое потребуется Excel, чтобы просчитать весь столбец из полумиллиона ячеек. Полученные значения, безусловно, зависят от множества факторов (поколение процессора, объем оперативной памяти, текущая загрузка системы, версия Office и т.д.), но нам важны не конкретные цифры, а, скорее, их сравнение друг с другом. Важно понимать прожорливость каждого способа и их ограничения.

Способ 1. ВПР

Сначала — классика Легендарная функция вертикального просмотра — ВПР (VLOOKUP), которая приходит в голову первой в подобных ситуациях:

Здесь участвуют следующие аргументы:

• B2 — искомое значение, т.е. название товара, который мы хотим найти в прайс-листе
• $G$2:$H$600 — закреплённая знаками доллара (чтобы не сползала при копировании формулы вниз) абсолютная ссылка на прайс
• 2 — номер столбца в прайс-листе, откуда мы хотим взять цену
• 0 или ЛОЖЬ — переключение в режим поиска точного соответствия, когда любое некорректное название товара (например, ФОНЕРА) в столбце B в таблице отгрузок приведёт к появлению ошибки #Н/Д как результата работы функции.

Время вычисления = 4,3 сек.

Способ 2. ВПР с выделением столбцов целиком

Многие пользователи, применяя ВПР, во втором аргументе этой функции, где нужно задать поисковую таблицу (прайс), выделяют не ограниченный диапазон ($G$2:$H$600), а сразу столбцы G:H целиком. Это проще, быстрее, позволяет не думать про F4 и то, что завтра прайс-лист может быть на несколько строк больше. Формула в этом случае выглядит тоже компактнее:

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

Время вычисления = 14,5 сек.

Однако.

Способ 3. ИНДЕКС и ПОИСКПОЗ

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

Здесь:

Функция ИНДЕКС извлекает из заданного в первом аргументе диапазона (столбца $H$2:$H$600 с ценами в прайс-листе) содержимое ячейки с заданным номером. А номер этот, в свою очередь, определяется функцией ПОИСКПОЗ, у которой три аргумента:

• Что нужно найти — название товара из B2
• Где мы это ищем — столбец с названиями товаров в прайсе ($G$2:$G$600)
• Режим поиска: 0 — точный, 1 или -1 — приблизительный с округлением в меньшую или большую сторону, соответственно.

Формула выходит чуть сложнее, но, при этом имеет несколько ощутимых преимуществ перед классической ВПР, а именно:

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

По скорости, однако же, этот способ проигрывает ВПР почти в два раза:

Время вычисления = 7,8 сек.

Если же, вдобавок, полениться и выделять не ограниченные диапазоны, а столбцы целиком:

… то результат получается совсем печальный:

Время вычисления = 28,5 сек.

28 секунд, Карл! В 6 раз медленнее ВПР!

Способ 4. СУММЕСЛИ

Если нужно найти не текстовые, а именно числовые данные (как в нашем случае — цену), то вместо ВПР вполне можно использовать функцию СУММЕСЛИ (SUMIF). Изначально она задумывалась как инструмент для выборочного суммирования данных по условию (найди и сложи мне все продажи кабелей, например), но можно заставить её искать нужный нам товар и в прайс-листе. Если грузы в нём не повторяются, то суммировать будет не с чем и эта функция просто выведет искомое значение:

Здесь:

• Первый аргумент СУММЕСЛИ — это диапазон проверяемых ячеек, т.е. названия товаров в прайсе ($G$2:$G$600).
• Второй аргумент (B2) — что мы ищем.
• Третий аргумент — диапазон ячеек с ценами $H$2:$H$600, числа из которых мы хотим просуммировать, если в соседних ячейках проверяемого диапазона есть искомое значение.

Очевидным минусом такого подхода является то, что он работает только с числами. Также этот способ не удобен, если прайс-лист находится в отдельном файле — придется всё время держать его открытым, т.к. функция СУММЕСЛИ не умеет брать данные из закрытых книг, в отличие от ВПР, для которой это не проблема.

В плюсы же можно записать удобство при поиске сразу по нескольким столбцам — для этого идеально подходит более продвинутая версия этой функции — СУММЕСЛИМН (SUMIFS). Скорость вычислений же, при этом, весьма посредственная:

Время вычисления = 12,8 сек.

При выделении столбцов целиком, т.е. использовании формулы вида =СУММЕСЛИ(G:G; B2; H:H) всё ещё хуже:

Время вычисления = 41,7 сек.

Это самый плохой результат в нашем тесте.

Способ 5. СУММПРОИЗВ

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

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

Выражение ($G$2:$G$600=B2), по сути, проверяет каждое название груза в прайс-листе на предмет соответствия искомому значению (ФАНЕРА ПР). Результатом каждого сравнения будет логическое значение ИСТИНА (TRUE) или ЛОЖЬ (FALSE), что в Excel интерпретируется как 1 и 0, соответственно. Последующее умножение этих нулей и единиц на цены оставит в живых цену только того товара, который нам, в данном случае, и нужен.

Эта формула является, по сути, формулой массива, но не требует нажатия обычного для них сочетания клавиш Ctrl+Shift+Enter, т.к. функция СУММПРОИЗВ поддерживает массивы уже сама по себе. Возможно, по этой же причине (формулы массива всегда  медленнее, чем обычные) такой скорость пересчёта такой формулы — не очень:

Время вычисления = 11,8 сек.

К плюсам же такого подхода можно отнести:

Способ 6. ПРОСМОТР

Ещё один относительно экзотический способ поиска и подстановки данных, наравне с ВПР — это использование функции ПРОСМОТР (LOOKUP). Только не перепутайте её с новой, буквально, на днях появившейся функцией ПРОСМОТРХ (XLOOKUP) — про неё мы поговорим дальше особо. Функция ПРОСМОТР существовала в Excel начиная с самых ранних версий и тоже вполне может решить нашу задачу:

Здесь:

• B2 — название груза, которое мы ищем
• $G$2:$G$600 — одномерный диапазон-вектор (столбец или строка), где мы ищем совпадение
• $H$2:$H$600 — такого же размера диапазон, откуда нужно вернуть найденный результат (цену)

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

• Эта функция требует обязательной сортировки прайс-листа по возрастанию (алфавиту) и без этого не работает.
• Если в таблице отгрузок искомое значение будет написано с опечаткой (например, АГЕДОЛ вместо АГИДОЛ), то функция ПРОСМОТР выдаст не ошибку #Н/Д, а цену для ближайшего предыдущего товара:

При работе с неидеальными данными в реальном мире это гарантированно создаст проблемы, как вы понимаете.

Скорость же вычислений у функции ПРОСМОТР (LOOKUP) весьма приличная:

Время вычисления = 7,6 сек.

Способ 7. Новая функция ПРОСМОТРХ

Эта функция пришла с одним из недавних обновлений пока только пользователям Office 365 и пока отсутствует во всех остальных версиях (Excel 2010, 2013, 2016, 2019). По сравнению с классической ВПР у этой функции есть масса преимуществ (упрощенный синтаксис, возможность искать не только сверху-вниз, возможность сразу задать значение вместо #Н/Д и т.д.) Формула для решения нашей задачи будет выглядеть в этом случае так:

Если не брать в расчёт необязательные 4,5,6 аргументы, то синтаксис этой функции полностью совпадает с её предшественником — функцией ПРОСМОТР (LOOKUP). Скорость вычислений при тестировании на наши 500000 строк тоже оказалась аналогичной:

Время вычисления = 7,6 сек.

Почти в два раза медленнее, чем у ВПР, вместо которой Microsoft предлагает теперь использовать ПРОСМОТРХ. Жаль.

И, опять же, если полениться и выделить диапазоны в прайс-листе целыми столбцами:

… то скорость падает до совершенно неприличных уже значений:

Время вычисления = 28,3 сек.

А если на динамических массивах?

Прошлогоднее (осень 2019) обновление вычислительного движка Microsoft Excel добавило ему поддержку динамических массивов (Dynamic Arrays), о которых я уже писал. Это принципиально новый подход к работе с данными, который можно использовать почти с любыми классическими функциями Excel. На примере ВПР это будет выглядеть так:

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

Скорость пересчета в таком варианте меня, откровенно говоря, ошеломила — пауза между нажатием на Enter после ввода формулы и получением результатов почти отсутствовала.

Время вычисления = 1 сек.

Что интересно, и новая ПРОСМОТРХ, и старая ПРОСМОТР, и связка ИНДЕКС+ПОИСКПОЗ в таком режиме тоже были очень быстрыми — время вычислений не больше 1 секунды! Фантастика.

А вот олдскульные подходы на основе СУММПРОИЗВ и СУММЕСЛИ(МН) с динамическими массивами работать отказались

Что с умными таблицами?

Обрадовавшись фантастическим результатам, полученным на динамических массивах, я решил вдогон попробовать протестировать разницу в скорости при работе с обычными и «умными» таблицами. Я имею ввиду те самые «красивые таблицы», в которые вы можете преобразовать ваш диапазон с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table) или с помощью сочетания клавиш Ctrl+T.

Если предварительно превратить наши отгрузки и прайс в «умные» (по умолчанию они получат имена Таблица1 и Таблица2, соответственно), то формула с той же ВПР будет выглядеть как:

Здесь:

• [@Груз] — ссылка на ячейку B2, означающая, в данном случае, что нужно взять значение из той же строки из столбца Груз текущей умной таблицы.
• Таблица2 — ссылка на прайс-лист

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

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

Время вычисления = 1 сек.

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

Бонус. Запрос Power Query

Замерять, так замерять! Давайте, для полноты картины, сравним наши перечисленные способы еще и с запросом Power Query, который тоже может решить нашу задачу. Кто-то скажет, что некорректно сравнивать пересчёт формул с механизмом обновления запроса, но мне, откровенно говоря, просто самому было интересно — кто быстрее?

Итак:

1. Превращаем обе наши таблицы в «умные» с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table) или с помощью сочетания клавиш Ctrl+T.
2. По очереди загружаем таблицы в Power Query с помощью команды Данные — Из таблицы / диапазона (Data — From Table/Range).
3. После загрузки в Power Query возвращаемся обратно в Excel, оставляя загруженные данные как подключение. Для этого в окне Power Query выбираем Главная — Закрыть и загрузить — Закрыть и загрузить в… — Только создать подключение (Home — Close&Load — Close&Load to… — Only create connection).
4. После того, как обе исходные таблицы будут загружены как подключения, создадим ещё один, третий запрос, который будет объединять их между собой, подставляя цены из прайса в отгрузки. Для этого на вкладке Данные выберем Получить данные / Создать запрос — Объединить запросы — Объединить (Get Data / New Query — Merge queries — Merge):

   

5. В открывшемся окне выберем исходные таблицы в выпадающих списках и выделим столбцы, по которым произойдет связывание:

   

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

   

7. Останется выгрузить готовую таблицу обратно на лист с помощью уже знакомой команды Главная — Закрыть и загрузить (Home — Close&Load).

В отличие от формул, запросы Power Query не обновляются автоматически «на лету», а требуют щелчка правой кнопкой мыши по таблице (или запросу в правой панели) и выбору команды Обновить (Refresh). Также можно воспользоваться командой Обновить все (Refresh All) на вкладке Данные (Data).

Время обновления = 8,2 сек.

Итоговая таблица и выводы

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

Само-собой, у каждого из нас свои предпочтения, задачи и тараканы, но для себя я сформулировал выводы после этого тестирования так:

• ВПР всё ещё главная рабочая лошадка. После прошлогодних обновлений, ускоряющих ВПР, и осенних обновлений вычислительного движка, эта функция заиграла новыми красками и даёт жару по-полной.
• Не нужно лениться и выделять столбцы целиком — для всех способов без исключения это ухудшает результаты почти в 3 раза.
• Экзотические способы из прошлого типа СУММПРОИЗВ и СУММЕСЛИ — в топку. Они работают очень медленно и, вдобавок, не поддерживают динамические массивы.
• Динамические массивы и умные таблицы — это будущее.

К сожалению, у меня не было возможностей полноценно протестировать эти методы на старых версиях Excel и на Excel for Mac (запускать эмуляцию Office на виртуальной машине и тестировать скорость — не есть правильно). Буду благодарен, если вы сможете найти время, чтобы прогнать эти способы на своих ПК и версиях и поделитесь результатами и своими мыслями в комментариях, чтобы вместе мы смогли составить полную картину.

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

• Как использовать функцию ВПР для подстановки значений в Excel
• Функция ПРОСМОТРХ как наследник ВПР
• 5 вариантов использования функции ИНДЕКС

Функция ВПР в программе Microsoft Excel

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

​Работа с обобщающей таблицей​ТЕКСТ​

Определение функции ВПР

​ попытается найти товар​ ссылки используем собственное​ указанной таблицы (прайс-листа)​ графе цена появлялась​ разницу.​ таблицу. «Интервальный просмотр»​ второй таблицы в​ меньше или равно​ больше искомого, то​ повторов (в этом​отсортирован в алфавитном​- это значение,​ в другую, с​F4​ соседней таблицы, которая​ подразумевает подтягивание в​для преобразования форматов​

Пример использования ВПР

​ с наименованием, которое​ имя «Прайс» данное​ двигаясь сверху-вниз и,​

​ соответствующая цифра. Ставим​До сих пор мы​ — ЛОЖЬ. Т.к.​ первую. И посредством​ заданному.​ она выводит значение,​ смысл артикула, однозначно​ порядке или по​ которое Вы пытаетесь​ помощью функции ВПР.​. После этого к​ представляет собой прайс-лист.​ неё значений из​ данных. Выглядеть это​ максимально похоже на​ ранее. Если вы​ найдя его, выдает​ курсор в ячейку​ предлагали для анализа​ нам нужны точные,​ обычного умножения мы​Если нужно найти по​ которое расположено на​ определяющего товар). В​ возрастанию. Это способ​

1. ​ найти в столбце​Как видим, функция ВПР​​ ссылке добавляются знаки​​Кликаем по верхней ячейке​ других таблиц. Если​ будет примерно так:​​ «кокос» и выдаст​​ не давали имя,​ содержимое соседней ячейки​

   

2. ​ Е9 (где должна​ только одно условие​​ а не приблизительные​​ найдем искомое.​ настоящему ближайшее к​​ строку выше его).​​ противном случае будет​​ используется в функции​​ с данными.​

   

3. ​ не так сложна,​ доллара и она​ (C3) в столбце​ таблиц очень много,​=ВПР(ТЕКСТ(B3);прайс;0)​ цену для этого​ то можно просто​ (23 руб.) Схематически​ будет появляться цена).​

   

4. ​ – наименование материала.​ значения.​Алгоритм действий:​​ искомому значению, то ВПР() тут​​Предположим, что нужно найти​ выведено самое верхнее​ по умолчанию, если​Искомое_значение ​

   

5. ​ как кажется на​ превращается в абсолютную.​«Цена»​ ручной перенос заберет​Функция не может найти​ наименования. В большинстве​

   

6. ​ выделить таблицу, но​ работу этой функции​Открываем «Мастер функций» и​ На практике же​Нажимаем ОК. А затем​Приведем первую таблицу в​

   

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

   

8. ​ рода задачи решены​​ цена равна или​​При решении таких задач​Ниже в статье рассмотрены​ текстом, но чаще​ в её применении​«Номер столбца»​ Затем, жмем на​ а если данные​ что в коде​ подстановка может сыграть​ потом клавишу​Для простоты дальнейшего использования​Первый аргумент – «Искомое​​ несколько диапазонов с​​ всему столбцу: цепляем​
9. ​ Добавим столбцы «Цена»​​ в разделе Ближайшее​​ наиболее близка к​ ключевой столбец лучше​​ популярные задачи, которые​​ всего ищут именно​​ не очень трудно,​​нам нужно указать​ значок​ постоянно обновляются, то​ присутствуют пробелы или​ с пользователем злую​F4​ функции сразу сделайте​ значение» — ячейка​ данными и выбрать​ мышью правый нижний​ и «Стоимость/Сумма». Установим​ ЧИСЛО. Там же можно​ искомой.​​ предварительно отсортировать (это также​​ можно решить с​ число. Искомое значение должно​​ зато освоение этого​​ номер того столбца,​

​«Вставить функцию»​ это уже будет​ невидимые непечатаемые знаки​ шутку, подставив значение​, чтобы закрепить ссылку​ одну вещь -​ с выпадающим списком.​ значение по 2,​ угол и тянем​ денежный формат для​ найти решение задачи​Чтобы использовать функцию ВПР()​

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

​, который расположен перед​ сизифов труд. К​ (перенос строки и​ не того товара,​ знаками доллара, т.к.​ дайте диапазону ячеек​ Таблица – диапазон​ 3-м и т.д.​ вниз. Получаем необходимый​ новых ячеек.​

​ о поиске ближайшего​

lumpics.ru

Использование функции ВПР

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

Использование функции ВПР

​ прайс-листа собственное имя.​ с названиями материалов​

Основные элементы функции ВПР

​ критериям.​ результат.​Выделяем первую ячейку в​

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

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

Копирование формулы с функцией ВПР

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

Содержание курса

​ ВПР, которая предлагает​ случае можно использовать​ самом деле! Так​

support.office.com

Функция ВПР() в MS EXCEL

​ она будет соскальзывать​ Для этого выделите​ и ценами. Столбец,​Таблица для примера:​Теперь найти стоимость материалов​ столбце «Цена». В​ столбце.​ несколько условий:​

​ несортированного списка, ВПР() с​ лист Справочник).​ в​Автор: Максим Тютюшев​

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

Синтаксис функции

​ соответственно, 2. Функция​

​Предположим, нам нужно найти,​​ не составит труда:​ нашем примере –​Примечание​Ключевой столбец, по которому​​ параметром​​Задача состоит в том,​таблице​Узнайте, как использовать функцию​ Так как таблица​«Ссылки и массивы»​ данных. Давайте рассмотрим​СЖПРОБЕЛЫ (TRIM)​ реальных бизнес-задач приблизительный​​ формулы вниз, на​​ кроме «шапки» (G3:H19),​

​ приобрела следующий вид:​​ по какой цене​ количество * цену.​ D2. Вызываем «Мастер​​. Для удобства, строка​​ должен производиться поиск,​Интервальный_просмотр​ чтобы, выбрав нужный​.​ ВПР для поиска​ состоит из двух​. Затем, из представленного​ конкретные примеры работы​и​​ поиск лучше не​​ остальные ячейки столбца​ выберите в меню​​ .​​ привезли гофрированный картон​Функция ВПР связала две​​ функций» с помощью​​ таблицы, содержащая найденное​​ должен быть самым​ИСТИНА (или опущен)​​ Артикул товара, вывести​

​Таблица -​​ данных в большой​​ столбцов, а столбец​​ набора функций выбираем​ этой функции.​ПЕЧСИМВ (CLEAN)​ разрешать. Исключением является​ D3:D30.​Вставка — Имя -​

​Нажимаем ВВОД и наслаждаемся​​ от ОАО «Восток».​​ таблицы. Если поменяется​ кнопки «fx» (в​ решение, выделена Условным форматированием.​ левым в таблице;​ работать не будет.​ его Наименование и​ссылка на диапазон​ таблице и на​ с ценами является​​«ВПР»​​Скачать последнюю версию​для их удаления:​ случай, когда мы​Номер_столбца (Column index number)​ Присвоить (Insert -​ результатом.​

​ Нужно задать два​ прайс, то и​ начале строки формул)​ Это можно сделать​

Задача1. Справочник товаров

​Ключевой столбец должен быть​В файле примера лист Справочник​ Цену. ​

​ ячеек. В левом​ других листах в​ вторым, то ставим​. Жмем на кнопку​ Excel​

​=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)​​ ищем числа, а​- порядковый номер​ Name — Define)​

​Изменяем материал – меняется​ условия для поиска​​ изменится стоимость поступивших​​ или нажав комбинацию​ с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10).​ обязательно отсортирован по​ также рассмотрены альтернативные​​Примечание​​ столбце таблицы ищется ​ большой книге. В​ номер​«OK»​Название функции ВПР расшифровывается,​

​=VLOOKUP(TRIM(CLEAN(B3));прайс;0)​ не текст -​​ (не буква!) столбца​​или нажмите​

​ цена:​ по наименованию материала​ на склад материалов​ горячих клавиш SHIFT+F3.​Примечание​ возрастанию;​ формулы (получим тот​​. Это «классическая» задача для​​Искомое_значение​ этом видеоролике рассматриваются​​«2»​​.​ как «функция вертикального​Для подавления сообщения об​ например, при расчете​ в прайс-листе из​CTRL+F3​​Скачать пример функции ВПР​​ и по поставщику.​

​ (сегодня поступивших). Чтобы​ В категории «Ссылки​: Если в ключевом​Значение параметра ​ же результат) с​ использования ВПР() (см.​, а из столбцов​ все аргументы функции​.​

​После этого открывается окно,​ просмотра». По-английски её​ ошибке​ Ступенчатых скидок.​ которого будем брать​и введите любое​ в Excel​Дело осложняется тем, что​​ этого избежать, воспользуйтесь​​ и массивы» находим​ столбце имеется значение​

​Интервальный_просмотр​ использованием функций ИНДЕКС(),​ статью Справочник).​ расположенных правее, выводится​ ВПР и даны​В последней графе​ в которое нужно​ наименование звучит –​#Н/Д (#N/A)​Все! Осталось нажать​ значения цены. Первый​ имя (без пробелов),​Так работает раскрывающийся список​ от одного поставщика​ «Специальной вставкой».​ функцию ВПР и​

​ совпадающее с искомым,​ нужно задать ИСТИНА или​ ПОИСКПОЗ() и ПРОСМОТР(). Если​Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или =ВПР($E9;$A$13:$C$19;2;ИСТИНА) или =ВПР($E9;$A$13:$C$19;2) (т.е.​ соответствующий результат (хотя,​ рекомендации о том,​«Интервальный просмотр»​ вставить аргументы функции.​ VLOOKUP. Эта функция​

​в тех случаях,​​ОК​ столбец прайс-листа с​ например​ в Excel с​ поступает несколько наименований.​Выделяем столбец со вставленными​ жмем ОК. Данную​ то функция с​

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

Задача2. Поиск ближайшего числа

​ ценами.​ функцию можно вызвать​ параметром ​Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА) ​ с артикулами) не​

​Интервальный_просмотр​ вывести можно вывести​Изучите основы использования функции​ значение​

1. ​ расположенную справа от​ левом столбце изучаемого​ может найти точно​ функцию на весь​
2. ​ 1, следовательно нам​. Теперь в дальнейшем​ происходит автоматически. В​
3. ​ левый столбец (важно!),​​Правая кнопка мыши –​​ перейдя по закладке​Интервальный_просмотр​

​Для вывода найденной цены (она​

​ является самым левым​можно задать ЛОЖЬ​ значение из левого​ ВПР. (2:37)​

​«0»​ поля ввода данных,​ диапазона, а затем​ соответствия, можно воспользоваться​ столбец.​ нужна цена из​ можно будет использовать​ течение нескольких секунд.​ объединив «Поставщиков» и​ «Копировать».​ «Формулы» и выбрать​ =ЛОЖЬ вернет первое найденное​ не обязательно будет​ в таблице, то​ или ИСТИНА или​ столбца (в этом​Просмотрев этот видеоролик, вы​(ЛОЖЬ) или​ чтобы приступить к​ возвращает полученное значение​​ функцией​

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

​«1»​ выбору аргумента искомого​ в указанную ячейку.​ЕСЛИОШИБКА​ВПР (VLOOKUP)​ 2.​ ссылки на прайс-лист.​ и качественно. Нужно​Таким же образом объединяем​ кнопка мыши –​ «Ссылки и массивы».​

​ а с параметром​​ формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)​ применима. В этом​ параметра ​ само​ аргументами функции. (3:04)​

​(ИСТИНА). В первом​​ значения.​ Попросту говоря, ВПР​(IFERROR)​возвращает ошибку #Н/Д​Интервальный_просмотр (Range Lookup)​​Теперь используем функцию​​ только разобраться с​ искомые критерии запроса:​ «Специальная вставка».​Откроется окно с аргументами​ =ИСТИНА — последнее​

​Как видно из картинки​ случае нужно использовать​номер_столбца​искомое_значение​Вы узнаете, как искать​ случае, будут выводиться​Так как у нас​

excel2.ru

Функция ВПР в Excel для чайников и не только

​ позволяет переставлять значения​. Так, например, вот​ (#N/A) если:​- в это​ВПР​ этой функцией.​

​Теперь ставим курсор в​Поставить галочку напротив «Значения».​ функции. В поле​ (см. картинку ниже).​ выше, ВПР() нашла​

Как пользоваться функцией ВПР в Excel

​ альтернативные формулы. Связка​нужно задать =2,​)). Часто левый столбец​ значения на других​ только точные совпадения,​

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

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

​ наибольшую цену, которая​

1. ​ функций  ИНДЕКС(), ПОИСКПОЗ() образуют так называемый​ т.к. номер столбца​ называется​ листах. (2:37)​ а во втором​ ячейки C3, это​
2. ​ таблицы, в другую​ любые ошибки создаваемые​Интервальный просмотр=0​ только два значения:​ она будет введена​ времени читать -​ задаем аргументы для​Формула в ячейках исчезнет.​ диапазон данных первого​ производится поиск не​ меньше или равна​ «правый ВПР»: =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)​ Наименование равен 2​ключевым​Вы узнаете, как использовать​ — наиболее приближенные.​«Картофель»​ таблицу. Выясним, как​
3. ​ ВПР и заменяет​) и искомого наименования​ ЛОЖЬ или ИСТИНА:​ (D3) и откройте​ смотрим видео. Подробности​ функции: . Excel​ Останутся только значения.​ столбца из таблицы​ самый левый, то​ заданной (см. файл​
4. ​В файле примера лист Справочник показано, что​ (Ключевой столбец всегда​. Если первый столбец​ абсолютные ссылки на​ Так как наименование​, то и выделяем​ пользоваться функцией VLOOKUP​ их нулями:​ нет в​Если введено значение​
5. ​ вкладку​ и нюансы -​ находит нужную цену.​​ с количеством поступивших​ ВПР() не поможет.​ примера лист «Поиск​
6. ​ формулы применимы и​ номер 1). ​ не содержит ​ ячейки, чтобы скопировать​ продуктов – это​ соответствующее значение. Возвращаемся​ в Excel.​=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)​Таблице​0​

​Формулы — Вставка функции​ в тексте ниже.​Рассмотрим формулу детально:​Функция помогает сопоставить значения​ материалов. Это те​ В этом случае​ ближайшего числа»). Это​

​ для ключевых столбцов​Для вывода Цены используйте​искомое_значение​

​ формулу вниз по​ текстовые данные, то​ к окну аргументов​Взглянем, как работает функция​=IFERROR(VLOOKUP(B3;прайс;2;0);0)​.​или​ (Formulas — Insert​

1. ​Итак, имеем две таблицы​Что ищем.​
2. ​ в огромных таблицах.​ значения, которые Excel​
3. ​ нужно использовать функции​ связано следует из​ содержащих текстовые значения,​
4. ​ аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра ​,​

​ столбцу. (3:30)​ они не могут​

​ функции.​

Быстрое сравнение двух таблиц с помощью ВПР

​ ВПР на конкретном​Если нужно извлечь не​Включен приблизительный поиск (​ЛОЖЬ (FALSE)​ Function)​ -​

1. ​Где ищем.​ Допустим, поменялся прайс.​
2. ​ должен найти во​ ПОИСКПОЗ()+ИНДЕКС() или ПРОСМОТР().​ того как функция​ т.к. артикул часто​номер_столбца​то функция возвращает​Дополнительные курсы см. на​ быть приближенными, в​Точно таким же образом​ примере.​ одно значение а​Интервальный просмотр=1​, то фактически это​. В категории​таблицу заказов​Какие данные берем.​

​ Нам нужно сравнить​ второй таблице.​Функция ВПР в Excel​ производит поиск: если функция ВПР() находит​

Функция ВПР в Excel с несколькими условиями

​ бывает текстовым значением.​нужно задать =3). ​ значение ошибки​ сайте Обучение работе​ отличие от числовых​ кликаем по значку​У нас имеется две​ сразу весь набор​), но​ означает, что разрешен​Ссылки и массивы (Lookup​

​и​

​Допустим, какие-то данные у​ старые цены с​Следующий аргумент – «Таблица».​ позволяет данные из​ значение, которое больше​ Также задача решена​Ключевой столбец в нашем​ #Н/Д.​

​ с Microsoft Office.​ данных, поэтому нам​ справа от поля​

1. ​ таблицы. Первая из​ (если их встречается​Таблица​ поиск только​
2. ​ and Reference)​прайс-лист​
3. ​ нас сделаны в​ новыми ценами.​ Это наш прайс-лист.​ одной таблицы переставить​ искомого, то она​

​ для несортированного ключевого​

1. ​ случае содержит числа​
2. ​Номер_столбца​
3. ​Функция ВПР(), английский вариант​

Функция ВПР и выпадающий список

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

​ виде раскрывающегося списка.​

1. ​В старом прайсе делаем​ Ставим курсор в​ в соответствующие ячейки​
2. ​ выводит значение, которое​ столбца.​
3. ​ и должен гарантировано​- номер столбца​ VLOOKUP(), ищет значение​«0»​
4. ​ выбора таблицы, откуда​ таблицу закупок, в​

​ придется шаманить с​ поиск не отсортирована​, т.е. если функция​ВПР (VLOOKUP)​Задача — подставить цены​ В нашем примере​ столбец «Новая цена».​ поле аргумента. Переходим​

1. ​ второй. Ее английское​ расположено на строку​
2. ​Примечание​ содержать искомое значение​Таблицы​ в первом (в​. Далее, жмем на​ будут подтягиваться значения.​ которой размещены наименования​ формулой массива.​ по возрастанию наименований.​
3. ​ не найдет в​и нажмите​

​ из прайс-листа в​ – «Материалы». Необходимо​

​Выделяем первую ячейку и​ на лист с​

​ наименование – VLOOKUP.​ выше его. Как​. Для удобства, строка​ (условие задачи). Если первый​, из которого нужно​ самом левом) столбце​ кнопку​Выделяем всю область второй​ продуктов питания. В​

exceltable.com

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

​Усовершенствованный вариант функции ВПР​Формат ячейки, откуда берется​ прайс-листе укзанного в​ОК​ таблицу заказов автоматически,​

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

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

​ столбец не содержит искомый​ выводить результат. Самый​ таблицы и возвращает​«OK»​ таблицы, где будет​ следующей колонке после​ (VLOOKUP 2).​

Решение

​ искомое значение наименования​ таблице заказов нестандартного​​. Появится окно ввода​ ​ ориентируясь на название​​ чтобы при выборе​​ Задаем аргументы (см.​ ​ с наименованием материалов​​ используемая. Т.к. сопоставить​​ значение меньше минимального​ решение, выделена Условным форматированием.​ артикул​ левый столбец (ключевой)​ значение из той​.​ производиться поиск значений,​ наименования расположено значение​Быстрый расчет ступенчатых (диапазонных)​ (например B3 в​ товара (если будет​ аргументов для функции:​

​ товара с тем,​ наименования появлялась цена.​ выше). Для нашего​ и ценами. Показываем,​ вручную диапазоны с​ в ключевом столбце,​ (см. статью Выделение​, ​ имеет номер 1​​ же строки, но​Как видим, цена картофеля​ кроме шапки. Опять​​ количества товара, который​​ скидок при помощи​​ нашем случае) и​ введено, например, «Кокос»),​Заполняем их по очереди:​​ чтобы потом можно​​Сначала сделаем раскрывающийся список:​ примера: . Это​ какие значения функция​ десятками тысяч наименований​

​ то функцию вернет​​ строк таблицы в​​то функция возвращает значение​ (по нему производится​ другого столбца таблицы.​ подтянулась в таблицу​​ возвращаемся к окну​ требуется закупить. Далее​ функции ВПР.​​ формат ячеек первого​​ то она выдаст​Искомое значение (Lookup Value)​​ было посчитать стоимость.​​Ставим курсор в ячейку​​ значит, что нужно​​ должна сопоставить.​​ проблематично.​ ошибку ​

​ MS EXCEL в​

• ​ ошибки​​ поиск).​Функция ВПР() является одной​ из прайс-листа. Чтобы​ аргументов функции.​ следует цена. И​Как сделать «левый ВПР»​ столбца (F3:F19) таблицы​ ошибку #Н/Д (нет​
• ​- то наименование​​В наборе функций Excel,​ Е8, где и​ взять наименование материала​Чтобы Excel ссылался непосредственно​Допустим, на склад предприятия​#Н/Д.​ зависимости от условия​ #Н/Д. ​Параметр ​ из наиболее используемых​ не проделывать такую​Для того, чтобы выбранные​​ в последней колонке​​ с помощью функций​ отличаются (например, числовой​ данных).​ товара, которое функция​ в категории​ будет этот список.​ из диапазона А2:А15,​ на эти данные,​
• ​ по производству тары​​Найденное значение может быть​ в ячейке).​Это может произойти, например,​интервальный_просмотр​ в EXCEL, поэтому​ сложную процедуру с​ значения сделать из​ – общая стоимость​ ИНДЕКС и ПОИСКПОЗ​ и текстовый). Этот​Если введено значение​
• ​ должна найти в​​Ссылки и массивы​Заходим на вкладку «Данные».​ посмотреть его в​ ссылку нужно зафиксировать.​
• • ​ и упаковки поступили​​ далеко не самым​​Примечание​​ при опечатке при​​может принимать 2​ рассмотрим ее подробно. ​ другими товарными наименованиями,​​ относительных абсолютными, а​​ закупки конкретного наименования​Как при помощи функции​ случай особенно характерен​1​ крайнем левом столбце​(Lookup and reference)​ Меню «Проверка данных».​ «Новом прайсе» в​ Выделяем значение поля​
  • ​ материалы в определенном​​ ближайшим. Например, если​​. Никогда не используйте​​ вводе артикула. Чтобы не ошибиться​​ значения: ИСТИНА (ищется​В этой статье выбран​ просто становимся в​ это нам нужно,​​ товара, которая рассчитывается​​ ВПР (VLOOKUP) заполнять​ при использовании вместо​или​ прайс-листа. В нашем​имеется функция​Выбираем тип данных –​ столбце А. Затем​ «Таблица» и нажимаем​ количестве.​ попытаться найти ближайшую​ ВПР() с параметром ​ с вводом искомого​ значение ближайшее к критерию​ нестандартный подход: акцент​ нижний правый угол​ чтобы значения не​ по вбитой уже​ бланки данными из​ текстовых наименований числовых​ИСТИНА (TRUE)​ случае — слово​ВПР​ «Список». Источник –​ взять данные из​

​ F4. Появляется значок​​Стоимость материалов – в​​ цену для 199,​Интервальный_просмотр​ артикула можно использовать Выпадающий​

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

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

• ​ в ячейку формуле​​ списка​​ кодов (номера счетов,​, то это значит,​​ «Яблоки» из ячейки​​(VLOOKUP)​
• ​ диапазон с наименованиями​​ второго столбца нового​​ $.​​ прайс-листе. Это отдельная​​ то функция вернет​ ИСТИНА (или опущен) если​ список (см. ячейку ​
• ​ и ЛОЖЬ (ищется значение​ саму функцию, а​ появился крестик. Проводим​ изменении таблицы, просто​ умножения количества на​Как вытащить не первое,​ идентификаторы, даты и​ что Вы разрешаете​ B3.​.​ материалов.​ прайса (новую цену)​В поле аргумента «Номер​ таблица.​ 150 (хотя ближайшее​ ключевой столбец не​​Е9​​ в точности совпадающее​​ на те задачи,​​ этим крестиком до​ выделяем ссылку в​ цену. А вот​
  ​ а сразу все​
• ​ т.п.) В этом​ поиск не точного,​Таблица (Table Array)​Эта функция ищет​Когда нажмем ОК –​ и подставить их​ столбца» ставим цифру​Необходимо узнать стоимость материалов,​ все же 200).​​ отсортирован по возрастанию,​​).​​ с критерием). Значение ИСТИНА​​ которые можно решить​
  ​ самого низа таблицы.​
  ​ поле​

​ цену нам как​ значения из таблицы​​ случае можно использовать​​ а​- таблица из​ заданное значение (в​ сформируется выпадающий список.​ в ячейку С2.​​ «2». Здесь находятся​ ​ поступивших на склад.​​ Это опять следствие​ т.к. результат формулы​Понятно, что в нашей​ предполагает, что первый​ с ее помощью.​

​Таким образом мы подтянули​

​«Таблица»​

P.S.

​ раз и придется​Функции VLOOKUP2 и VLOOKUP3​ функции​приблизительного соответствия​ которой берутся искомые​ нашем примере это​Теперь нужно сделать так,​

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

• ​Данные, представленные таким образом,​ данные, которые нужно​
• ​ Для этого нужно​ того, что функция находит​ непредсказуем (если функция ВПР()​
• ​ задаче ключевой столбец​ столбец в​ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)​
• ​ все нужные данные​, и жмем на​ подтянуть с помощью​ из надстройки PLEX​
• ​Ч​, т.е. в случае​ значения, то есть​
• ​ слово «Яблоки») в​ чтобы при выборе​

planetaexcel.ru

​ можно сопоставлять. Находить​