Ошибка перенос в excel при впр

Эта ошибка возникает, если диапазон переноса для формулы массива с переносом не является пустым.

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

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

Excel не удалось определить размер массива с переносом, так как он является переменным и меняет размер между этапами вычислений. Например, следующая формула вызывает эту ошибку #ПЕРЕНОС! :

=ПОСЛЕДОВ(СЛУЧМЕЖДУ(1;1000))

Динамическое изменение размера массива может вызвать дополнительные этапы вычислений, чтобы обеспечить полное вычисление таблицы. Если размер массива продолжает изменяться во время этих дополнительных этапов и не стабилизируется, Excel разрешит динамический массив ошибкой #ПЕРЕНОС!.

Это значение ошибки обычно связано с использованием функций СЛЧИС, СЛМАССИВи СЛУЧМЕЖДУ. Другие переменные функции, например СМЕЩ, ДВССЫЛ и СЕГОДНЯ, не возвращают разные значения на каждом этапе вычисления. 

Например, при размещении в ячейке E2 формулы =ВПР(A:A;A:C;2;ЛОЖЬ) (как в примере ниже) ранее выполнялся поиск ИД только в ячейке A2. Однако в динамическом массиве Excel формула вызывает ошибку #ПЕРЕНОС!, так как Excel выполняет поиск во всем столбце, возвращает 1 048 576 результатов и достигает конца сетки Excel.

Эту проблему можно устранить 3 простыми способами:

#	Способ	Формула
1	Ссылайтесь только на нужные значения поиска. Этот стиль формулы возвращает динамический массив, но не работает с таблицами Excel.	=ВПР(A2:A7;A:C;2;ЛОЖЬ)
2	Сошлитесь на значение в той же строке, а затем скопируйте формулу. Этот традиционный стиль формулы работает в таблицах, но не возвращает динамический массив.	=ВПР(A2;A:C;2;ЛОЖЬ)
3	Создайте в Excel запрос неявного пересечения с помощью оператора @, а затем скопируйте формулу. Этот стиль формулы работает в таблицах, но не возвращает динамический массив.	=ВПР(@A:A;A:C;2;ЛОЖЬ)

Формулы массивов с переносом не поддерживаются в таблицах Excel. Попробуйте переместить формулу из таблицы или преобразовать таблицу в диапазон (нажмите Конструктор таблиц > Инструменты > Преобразовать в диапазон).

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

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

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

Функция ВПР в Экселе – быстрый перенос данных

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

Например, у вас есть большой прайс на 500 позиций и запрос от покупателя, скажем на 50 позиций (в реальности и прайс и запрос могут быть гораздо больше, но принцип от этого не меняется).

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

Итак, у нас в прайсе 500 позиций. Позиции обозначаются следующим образом, буквами обозначается вид позиции, а цифрами модификация.

Например, «Стул_1» и «Стул_21» это два совершенно разных стула.

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

Обозначим задачу.

В ООО «ЫкэА» пришел запрос от «Петровича».

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

Однако это нас не страшит, во-первых, у нас есть ВПР, во-вторых мы и не такое видали.

Вот собственно и сам запрос:

Петрович требует, чтобы мы очень быстро проставили цены в его запросе. Ждать он намерен максимум 5 минут. Ведь другие поставщики уже завалили его предложениями.

Нам не хочется терять такого клиента и мы практически мгновенно открываем прайс:

Получается у нас должно быть открыто два файла (две книги в Эксель). Запрос от Петровича и Прайс.

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

Для этого перейдем в таблицу запроса и в первой ячейке столбца «Цены» (D4) введем «=впр» и два раза кликнем на значок функции:

Сразу же после этого, в строке формулы нужно поставить курсор внутри надписи ВПР и нажать Fx, перед вами появится окно с аргументами функции ВПР:

В аргументах функции вы говорите Экселю что и где нужно искать:

Искомое значение — это значение (в данном случае наименование), цену которого вы хотите найти в прайсе. Соответственно кликайте на первую ячейку столбца «Наименование».

Далее, сразу переходите в «Прайс»:

Теперь в аргументах функции заполните следующие поля:

Таблица — выделяете столбцы, которые содержат искомые наименования и цены, таким образом, чтобы наименования были крайним левым столбцом.

Так работает функция ВПР — ищет искомые значения в крайнем левом столбце (для ВПР это столбец №1). Когда ВПР находит искомое значение он начинает смотреть правее, в тот столбец, который вы указали в «Номере столбца».

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

Интервальный просмотр — ставьте 0. Ноль обозначает точное соответствие.

После заполнения аргументов функции нажимайте «Ок» и если всё сделано верно, то в столбце «Цена» (файл «Запрос от Петровича»), появится цена.

Вам нужно протянуть цены на оставшиеся ячейки:

Коллеги, вот и всё, вы овладели функцией ВПР.

Очень важное замечание!

Обратите внимание на то, что сейчас мы работали в двух разных файлах (книгах).

Когда работа идёт в двух разных книгах, Эксель автоматически закрепляет таблицу в функции ВПР:

Делает это он при помощи значка $, который проставляет перед столбцами и строками таблицы.

Это позволяет не съезжать формуле когда вы протягиваете её вниз. Это очень актуально когда вы работаете в рамках одного листа или одной книги (в этом случае Эксель автоматически Не закрепляет ячейки).

Давайте посмотрим что получиться если протянуть формулу «без закрепления»:

Обратите внимание, что для первой ячейки всё в порядке и диапазон B3:C502 точно соответствует той таблице, которую мы выделили для поиска данных, однако (без закрепления) так будет не всегда, по мере «протягивания» формулы ВПР вниз, будет смещаться и таблица, пока однажды, мы не увидим вот такую странную надпись #Н/Д:

#Н/Д значит что функция ВПР не смогла найти цену Стул_13 в прайсе и это не мудрено, ведь диапазон таблицы в формуле ВПР уехал ниже этого значения:

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

Очень важное замечание №2

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

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

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

Для этого нужно выделить столбец с формулами, нажать Ctrl+C и в левом верхнем углу выбрать «Вставить» — «Вставить значения».

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

Критерий поиска

Адрес ячейки листа Excel, в которой указываются данные для осуществления поиска в таблице.

Диапазон данных

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

Номер столбца для итогового значения

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

Условие для поиска

Логическое значение (истина/1 или ложь/0), которое указывает приблизительное совпадение искать (1) или точное (0).

Функция ВПР в Экселе: пошаговая инструкция

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

Во второй – цены:

Если перечень товаров в обеих таблицах совпадает, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится.

Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.

Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.

Вот так.

Функция ВПР в Эксель легко справится с задачей.

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

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

Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.

Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».

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

Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов. Это не важно, Excel все сосчитает.

Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.

Нажимаем ОК. Если все правильно и значение критерия есть в обеих таблицах, то на месте только что введенной формулы появится некоторое значение. Остается только протянуть (или просто скопировать) формулу вниз до последней строки таблицы.

Теперь легко рассчитать стоимость простым умножением количества на цену.

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

ВПР пример 1 

1. После создания отсортированного учебного файла щёлкнуть любую свободную ячейку (например, F9): в ней будет возвращён результат. 

1. На строке формул щёлкнуть fx. 

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

4. Выделить функцию ВПР и кликнуть OK. 

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

В окне аргументов необходимо корректно заполнить три обязательных параметра, выделенных жирным шрифтом (последний необязательный параметр будет рассмотрен в примере 3). 

6. Как видно, заданное или ключевое значение в окне аргументов именуется «Искомым». Такое название не кажется мне удачным: оно задаётся изначально, а не ищется, и используется как ключ к поиску. Предположим, что нас интересует зарплата, получаемая сотрудницей Ириной Соколовой. Самый простой способ задать верхний параметр – щёлкнуть на ячейке A8, после чего – кликнуть на строке параметра «Таблица». 

7. В качестве параметра «Таблица» выделяется весь заполненный участок Excel, исключая заголовки столбцов. А на строке «Номер столбца» следует ввести номер столбца, содержащего список зарплат, т. е. 2. Как видно, правильное значение зарплаты сотрудницы Ирины Соколовой появляется прямо в окне аргументов. 

8. После клика на OK возвращённое функцией значение появится и в ячейке, зарезервированной для результата. 

ВПР пример 2 

Естественно, что аналогичным образом могут быть извлечены данные из других информационных столбцов. Предположим, что нам нужно узнать дату рождения всё той же Ирины Соколовой. Аргументы функции ВПР претерпят единственное изменение на строке «Номер столбца». Как видно, функция возвращает, на первый взгляд, непонятное число 29025.  

После щелчка на ОК оно появится и в ячейке результата. 

  Опытные пользователи Excel знают, что в этой программе даты (и время) хранятся в числовом формате, начиная с условно принятой даты 0 января 1900 года. Для отображения полученного значения в привычном человеку виде достаточно придать ячейке результата формат даты. Для этого сначала открыть её контекстное меню, и щёлкнуть пункт «Формат ячеек». 

В появившемся одноимённом окне: 

• щёлкнуть пункт «Дата»; 

• в правом списке при необходимости поменять вариант отображения; 
• щёлкнуть OK.  

После этого отображаемая в ячейке результата дата примет «человеческий» вид. 

ВПР пример 3 

Этот пример отличается от первого двумя аргументами – первым и заполненным последним. Как видно, в первом аргументе вместо имени использован подстановочный знак звёздочки *. Если не заполнять последний аргумент, то возвращаемое функцией значение будет ошибочным. Ввод логического значения ЛОЖЬ сделает результат правильным.  

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

1. При использовании функцией ВПР подстановочных знаков её последний аргумент обязательно должен использоваться, и иметь значение ЛОЖЬ. 

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

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

1. Первую особенность можно считать общей для функций, которые используются для многих ячеек путем прописывания формулы в одной из них и дальнейшим копированием в остальные. Здесь нужно обращать внимание на относительность и абсолютность ссылок. Конкретно в ВПР критерий (первое поле) должно иметь относительную ссылку (без знаков $), так как у каждой ячейки свой собственный критерий. А вот поле «Таблица» должно иметь абсолютную ссылку (адрес диапазона прописывается через $). Если этого не сделать, то при копировании формулы диапазон «поедет» вниз и многие значения просто не найдутся, так как искать будет негде.

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

3. Функция ВПР из диапазона с искомыми данными выдает первое сверху значение. Это значит, что, если во второй таблице, откуда мы пытаемся «подтянуть» некоторые данные, присутствуют несколько ячеек с одинаковым критерием, то в рамках выделенного диапазона ВПР захватит первое сверху значение. Об этом следует помнить. К примеру, если мы хотим к цене товара подтянуть количество из другой таблицы, а там этот товар встречается несколько раз (в нескольких строках), то к цене подтянется первое сверху количество. 

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

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

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

Это позволит удалить сразу все внешние ссылки. 

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

Для следующих примеров использования функции ВПР возьмем немного другие данные.

Требуется цены из второй таблицы подтянуть в первую. В качестве критерия здесь используется код. Ниже показаны этапы вычисления ВПР.

Вторая таблица меньше первой, т.е. некоторые коды в ней отсутствуют. Для отсутствующих позиций ВПР выдает ошибку #Н/Д.

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

Конструкция с функцией ЕСЛИОШИБКА

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

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

Разные форматы критерия в таблицах

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

Первый случай, когда критерии в первой таблице сохранены как числа, а критерии во второй таблице – как текст.

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

Такое решение используется довольно часто. Но оно не всегда подходит. Например, когда данные из второй таблицы регулярно выгружаются из какой-нибудь базы данных типа 1С. В подобных файлах вообще все сохранено как текст. И если мы планируем постоянно использовать такие данные, вставляя их в заранее подготовленный диапазон, то лучше, чтобы формулы работали без дополнительного вмешательства. 

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

Две ошибки по-прежнему связаны с тем, что эти товары отсутствую во второй таблице. Чтобы их заглушить, можно вновь воспользоваться функцией ЕСЛИОШИБКА.

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

Как и в прошлый раз, будем вносить коррективы в функцию ВПР. Преобразовать «текст» в «число» еще проще. Достаточно к ссылке на «текстовый» критерий добавить 0 или умножить на 1.

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

Отсутствующие критерии по-прежнему вызывают ошибку #Н/Д. В таком случае всю формулу можно еще раз «обернуть» в ЕСЛИОШИБКА.

Функция СЖПРОБЕЛЫ для чистки текстового критерия

В качестве критерия рекомендуется брать уникальный код, в котором опечатки, характерные для текста, маловероятны. Но иногда все-таки кода нет и критерием выступает текст (названия организаций, фамилии людей и т.д.). В этом случае возможны случайные ошибки в написании. Одна из распространенных ошибок – лишние пробелы. Проблема решается просто с помощью функции СЖПРОБЕЛЫ для всех критериев. Сделать это можно внутри формулы ВПР, а можно и предварительно пройтись по всем критериям в обеих таблицах. Кому как удобней.

Подсчет номера столбца в большой таблице

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

Здорово экономит время.

Видео-урок: Как использовать функцию ВПР в Excel

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

На примере ниже, в таблице справа, мы хотим подставить данные результатов экзамена по математике для студента Сидорова. Нам известны данные по всем студентам из таблицы слева. Используя формулу, мы можем подставить необходимые для нас данные.

С одним условием

Рассмотрим функцию на простом примере поиска сотрудника по присвоенному ему коду. Таблицу нужно отсортировать в порядке возрастания.

1. Кликнуть по ячейке справа от таблицы или под ней и вписать искомый номер.
2. В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.
3. Задать параметры поиска, заполнив соответствующие поля. Интервальный просмотр в данном случае пропустим.
4. Нажать «Enter» – и появится результат поиска.
   Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».
   Чтобы найти другой элемент, следует просто изменить первый аргумент, и результат автоматически преобразится.

С несколькими условиями

Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.

Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:

1. В первую выделенную ячейку вписать дату, во вторую – фамилию менеджера. В третьей ячейке будет происходить поиск.
2. Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).
3. Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «{}», в которые взята вся формула).

Синтаксис

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) – английская версия

=ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр]) – русская версия

Аргументы функции

• lookup_value (искомое_значение) – это величина для поиска, из крайнего левого столбца таблицы. Это может быть значение, ссылка на ячейку или текстовая строка. В примере со студентами это их фамилии;
• table_array (таблица) – это диапазон данных, в котором будет осуществлен поиск. Это может быть ссылка на диапазон ячеек или именованный диапазон. В примере с таблицей со студентами, это будет вся таблица, которая содержит оценку и фамилии студентов;
• col_index (номер_столбца) – это порядковый номер столбца в диапазоне с данными, из которого будет получена искомая величина;
• [range lookup] ([интервальный_просмотр]) – этот аргумент указывает на точность совпадения данных при поиске. Укажите “0” – если точное, “1” – если приблизительное совпадение.

Дополнительная информация

• совпадение искомых данных может быть точным и приблизительным;
• при сопоставлении по приблизительной точности данных убедитесь, что данные в таблицах отсортированы в убывающем порядке (от большого к малому). Иначе, результат сопоставления будет некорректным;
• при сопоставлении данных по приблизительной точности:
  – если функция не найдет искомое значение, она выдаст наибольшую величину, которая будет меньше чем значения поиска;
  – если функция при сопоставлении выдает ошибку #N/A, то искомое значение меньше чем самая маленькая величина в искомом диапазоне;
  – вы можете использовать подстановочные знаки для искомых значений.

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

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

Как работает ВПР. Для чайников

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

Синтаксис функции ВПР

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

Когда я думаю о функции, я вспоминаю знакомую всем телепередачу «Что? Где? Когда?».

Первые два аргумента — как раз «Что?» и «Где?», и идут именно в этом порядке. Первый аргумент — искомое значение, иначе говоря, что мы ищем. Второй аргумент — диапазон, таблица, простыми словами — где мы ищем. Третий аргумент числовой, и обозначает, когда мы должны остановиться, считая столбцы в таблице слева направо от найденного в первом столбце таблицы значения.

А четвертый аргумент («интервальный просмотр») вроде бы даже не обязательный, но на поверку очень важен. Ведь от его значения зависит, как будет работать функция. Жаль, вопроса «Как?» в телепередаче не было.

Интервальный просмотр — 0 или 1, ЛОЖЬ или ИСТИНА

Скажу заранее. Если нужно просто найти искомое значение и вернуть то, что напротив — используйте в этом параметре 0 (или ЛОЖЬ, но 0 напечатать проще) и не читайте дальше.

---

Если же хотите узнать главный секрет функции ВПР, то дальше информация для вас.

Тот, кто перевел последний параметр range lookup с английского как «интервальный просмотр», испортил жизнь многим новичкам в Excel.

Потому что там нет ничего интервального, все вполне себе сплошное. А корректно перевести параметр следовало бы «способ просмотра диапазона«.

Также справочный текст говорит следующее:

• ЛОЖЬ или 0 обозначает точное совпадение;
• ИСТИНА, 1 или эквивалентный им пропуск параметра (т.к. это значение по умолчанию) обозначает приблизительное совпадение

И вот с этим приблизительным совпадением сразу возникает ряд вопросов. Что значит приблизительное? Насколько? Каким алгоритмом считается «приблизительность»? Зачем сортировать? И почему по возрастанию?

Тайна четвертого параметра ВПР

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

• Когда четвертый параметр ЛОЖЬ или 0 — используется линейный поиск;
• Когда он ИСТИНА, 1 или не указан явно, но используется по умолчанию — используется бинарный поиск.

В чем разница?

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

Бинарный же поиск в Excel позволяет находить данные практически мгновенно, так как выполняет четыре основных шага:

1. Длина диапазона данных делится пополам и позиция чтения перемещается в середину.
2. Найденное значение (пусть n) сравнивается с тем, которое мы ищем (пусть m).
3. Если m > n, то берется вторая часть массива, если m < n — первая часть.
4. Далее шаги 1-3 повторяются на выбранной части диапазона.

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

В сравнении с линейным поиском, на целиком заполненном столбце Excel (1048576 строк, или 2 в 20 степени) бинарный будет в ~52.000 раз быстрее! (1048576 поделить на 20).

Но есть несколько моментов, которые нужно знать для работы с ВПР в этом режиме:

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

=ЕСЛИ(ВПР(искомое;диапазон;1;1)<>искомое;"";ВПР(искомое;диапазон;n;1))

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

Важный момент — в Excel 2019 и выше даже при использовании параметра 0 используется оптимальный быстрый алгоритм, поэтому большого прироста скорости в последних версиях такая формула не даст (зато даст в Excel 2016 и всех версий что раньше). Вероятнее всего, в Microsoft решили, что пришло время упростить жизнь пользователям, которые не хотят во всём этом разбираться

Как сделать ВПР — понятная пошаговая инструкция

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

Ниже пошаговая инструкция по использованию функции ВПР с полезными лайфхаками и картинками. Итак:

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

ВПР по вхождению подстроки

Помимо поиска по точному совпадению, ВПР умеет искать данные и с неточным совпадением, т.к. поддерживает подстановочные символы (? и *). Важно, что их учёт возможен только в режиме линейного поиска (четвертый параметр 0 или ЛОЖЬ). Ниже пример с использованием знака вопроса, обозначающего один любой символ:

Не работает ВПР — ошибки и причины

Функция ВПР иногда ведет себя непредсказуемо, например, не подтягивает значения, возвращает ошибки там, где не должна, или возвращает неожиданные значения. Рассмотрим, почему ВПР не срабатывает так, как должен.

ВПР выдает ошибку #Н/Д

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

Хотите избавиться от ошибки, когда искомое не найдено? Почитайте, как сделать это с помощью функции ЕСЛИОШИБКА в соответствующей статье. А я лишь приведу формулы оттуда:

=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"ошибка")
=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"")

ВПР не подтягивает значение, хотя искомое в таблице есть

Иногда такое бывает — вы видите, что в таблице есть данные, которые вы ищете, но ВПР будто не срабатывает. Не находит данные. Абсолютно всегда проблема не в ВПР, а в самих данных, и они действительно не совпадают. Самые распространенные проблемы, когда визуально в ячейках одинаковы, но на поверку — нет:

• В таблице искомых значений или в диапазоне поиска присутствуют лишние пробелы, которые нужно удалить.
• Вместо обычных пробелов в ячейках используются неразрывные пробелы, визуально между ними нет никакой разницы, но программа её видит.
• В данных присутствуют переносы строк, символы табуляции, возврат каретки и другие виды «невидимых» символов
• Вместо кириллицы используется латиница или наоборот, для букв с идентичным написанием (для компьютера русская А и английская A — разные символы!). Тогда нужно заменить кириллицу на похожую латиницу (или наоборот).

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

Понравилась статья? Поддержите ее автора!
Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!

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

Синтаксис и описание функции ВПР в Excel

Функция ВПР в Excel примеры

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

Почему не работает функция ВПР

Синтаксис

=ВПР(элемент_который_нужно_найти;адрес_таблицы_где_находится_искомое_значение;порядковый_номер_столбца;интервальный_просмотр)

Элемент – может быть числовым (адрес ячейки) или текстовым («текст»).

Адрес таблицы – диапазон ячеек, где примерно находится значение.

Номер столбца – принимает целое число из диапазона от 1 до n, из него будет извлечен результат.

Интервальный просмотр – приблизительное (ближайшее) соответствие критерию обозначается как 1 (истина), а точное соответствие – 0 (ложь). Данный логический аргумент указывать необязательно, если таблица отсортирована от минимального к максимальному значению. Если таблица не отсортирована и аргумент опущен, это равносильно истине.

Важно! Искомое значение должно находиться слева (в первом столбце) от возвращаемого элемента.

В русифицированной версии Excel аргументы вводятся через знак «;», в англоязычной – через запятую.

Видео — «Быстрый перенос данных с помощью функции ВПР в Экселе»

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

Это очень актуально для тех кто работает в закупках и отправляет заказы поставщику.

Обычно происходит следующая ситуация. Вы отправляете заказ поставщику, через некоторое время получаете ответ в виде счёта и сверяете заказ с счётом.

Всё ли есть в счёте, в нужном ли количестве, по правильным ли ценам и т.д.

Ошибки

Когда пользователь ошибается при вводе данных или выборе диапазона, вместо результата отображаются различные ошибки: #Н/Д, #ЗНАЧ, #ССЫЛКА.

Ошибка #Н/Д появляется, если:

1. Указанный диапазон не содержит искомый элемент.
2. Искомый элемент меньше, чем минимальный в массиве.
3. Задан точный поиск (аргумент «ЛОЖЬ» или 0), а искомого нет в диапазоне.
4. Задан приблизительный поиск (аргумент «ИСТИНА» или 1), но данные не отсортированы по возрастанию.
5. Разный формат (числовой и текстовый) у ячейки, откуда берется искомое, и ячейки с данными первого столбца.
6. В коде есть пробелы или невидимые непечатаемые знаки.
7. Используются значения времени или большие десятичные числа.

Во избежание ошибки #Н/Д, когда ВПР не находит значение, рекомендуется использовать следующую формулу: =ЕСЛИОШИБКА(ВПР(C2;A1:B12;2;ЛОЖЬ);0) – вместо 0 можно написать «не найдено».

Ошибка #ЗНАЧ появляется, если:

1. В качестве номера столбца указано число 0.
2. Длина первого аргумента превышает 255 знаков.

Ошибка #ССЫЛКА появляется, если третий аргумент больше количества столбцов в таблице.

Функция ВПР в Экселе – быстрый перенос данных

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

Например, у вас есть большой прайс на 500 позиций и запрос от покупателя, скажем на 50 позиций (в реальности и прайс и запрос могут быть гораздо больше, но принцип от этого не меняется).

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

Итак, у нас в прайсе 500 позиций. Позиции обозначаются следующим образом, буквами обозначается вид позиции, а цифрами модификация.

Например, «Стул_1» и «Стул_21» это два совершенно разных стула.

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

Обозначим задачу.

В ООО «ЫкэА» пришел запрос от «Петровича».

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

Однако это нас не страшит, во-первых, у нас есть ВПР, во-вторых мы и не такое видали.

Вот собственно и сам запрос:

Петрович требует, чтобы мы очень быстро проставили цены в его запросе. Ждать он намерен максимум 5 минут. Ведь другие поставщики уже завалили его предложениями.

Нам не хочется терять такого клиента и мы практически мгновенно открываем прайс:

Получается у нас должно быть открыто два файла (две книги в Эксель). Запрос от Петровича и Прайс.

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

Для этого перейдем в таблицу запроса и в первой ячейке столбца «Цены» (D4) введем «=впр» и два раза кликнем на значок функции:

Сразу же после этого, в строке формулы нужно поставить курсор внутри надписи ВПР и нажать Fx, перед вами появится окно с аргументами функции ВПР:

В аргументах функции вы говорите Экселю что и где нужно искать:

Искомое значение — это значение (в данном случае наименование), цену которого вы хотите найти в прайсе. Соответственно кликайте на первую ячейку столбца «Наименование».

Далее, сразу переходите в «Прайс»:

Теперь в аргументах функции заполните следующие поля:

Таблица — выделяете столбцы, которые содержат искомые наименования и цены, таким образом, чтобы наименования были крайним левым столбцом.

Так работает функция ВПР — ищет искомые значения в крайнем левом столбце (для ВПР это столбец №1). Когда ВПР находит искомое значение он начинает смотреть правее, в тот столбец, который вы указали в «Номере столбца».

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

Интервальный просмотр — ставьте 0. Ноль обозначает точное соответствие.

После заполнения аргументов функции нажимайте «Ок» и если всё сделано верно, то в столбце «Цена» (файл «Запрос от Петровича»), появится цена.

Вам нужно протянуть цены на оставшиеся ячейки:

Коллеги, вот и всё, вы овладели функцией ВПР.

Очень важное замечание!

Обратите внимание на то, что сейчас мы работали в двух разных файлах (книгах).

Когда работа идёт в двух разных книгах, Эксель автоматически закрепляет таблицу в функции ВПР:

Делает это он при помощи значка $, который проставляет перед столбцами и строками таблицы.

Это позволяет не съезжать формуле когда вы протягиваете её вниз. Это очень актуально когда вы работаете в рамках одного листа или одной книги (в этом случае Эксель автоматически Не закрепляет ячейки).

Давайте посмотрим что получиться если протянуть формулу «без закрепления»:

Обратите внимание, что для первой ячейки всё в порядке и диапазон B3:C502 точно соответствует той таблице, которую мы выделили для поиска данных, однако (без закрепления) так будет не всегда, по мере «протягивания» формулы ВПР вниз, будет смещаться и таблица, пока однажды, мы не увидим вот такую странную надпись #Н/Д:

#Н/Д значит что функция ВПР не смогла найти цену Стул_13 в прайсе и это не мудрено, ведь диапазон таблицы в формуле ВПР уехал ниже этого значения:

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

Очень важное замечание №2

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

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

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

Для этого нужно выделить столбец с формулами, нажать Ctrl+C и в левом верхнем углу выбрать «Вставить» — «Вставить значения».

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

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

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

.

Вот небольшая таблица для наглядности.

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

1. Материал.
2. Производитель.

Но это еще не все, потому что каждый производитель завозит сразу несколько товаров. Как можно выкрутиться в этой ситуации? А вот, как:

1. К таблице присоединяется крайний левый столбец, чтобы поставщики и материалы были в одной группе.

   

2. Критерии также нужно объединить. 13
3. Курсор устанавливается в требуемом месте, и в скобках указываются аргументы функции (или же через соответствующее диалоговое окно). =ВПР(I6;$A$2:$D$15;4;ЛОЖЬ)
   . После этого Excel определит необходимую стоимость. 14

Функция ВПР в Экселе – сравнение двух таблиц

Итак, у вас есть «Заказ поставщику» (1) и ответ поставщика в виде «Счёта на оплату» (2).

Для удобства восприятия я разместил их на одном листе:

Ваша задача сверить количество позиций и их цены.

Для начала проверим все ли позиции и по правильной ли цене указал в счёте поставщик.

Для этого нужно из Счёта перетянуть данные в Заказ при помощи функции ВПР.

Перед «перетяжкой», в таблицу «Заказ поставщику» нужно добавить два «сравнительных» столбца:

«Кол/во в счёте» и «Цена в счёте»

После добавления столбцов, нужно перетянуть соответствующие данные при помощи ВПР:

Обратите внимание, я закрепил диапазоны ячеек.

Теперь когда данные перенесены, нужно их сравнить, для это необходимо добавить еще два столбца (Разница 1 и Разница 2):

В столбце «Разница 1» нужно вычесть от исходного количества (D4) количество в счёте (E4).

В столбце «Разница 2» нужно вычесть от исходной цены (G4) цену в счёте (H4).

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

Если значение «0», то значит всё хорошо и данные одинаковые.

Если значение плюсовое (например «+3»), то это значит что в счёте не хватает 3 штук.

Если значение отрицательное, это значит, что нам пытаются «впихнуть» лишнее.

Если значение #Н/Д — это значит, что в счёте вообще нет такой позиции.

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

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

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

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

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

Для этого в «Счёт на оплату» нужно добавить столбец «Кол/во в заказе» и «отвепээрить» туда значения из столбца «Количество» Заказа поставщику.

И если в столбце «Количество в заказе» мы вдруг увидим значения #Н/Д это значит, что позиции с таким наименование не было в нашем заказе поставщику.

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

Теперь всё тоже самое продемонстрирую в небольшом видео.