Формула впр в excel

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

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

Давайте разберем следующий пример. У нас есть основная таблица (Main table) со столбцом SKU (new), куда необходимо добавить столбец с соответствующими ценами из другой таблицы. Кроме этого, у нас есть 2 таблицы поиска. Первая (Lookup table 1) содержит обновленные номера SKU (new) и названия товаров, а вторая (Lookup table 2) – названия товаров и старые номера SKU (old).

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

1. Запишите функцию ВПР, которая находит имя товара в таблице Lookup table 1, используя SKU, как искомое значение:

   Здесь New_SKU – именованный диапазон $A:$B в таблице Lookup table 1, а 2 – это столбец B, который содержит названия товаров (смотрите на рисунке выше)

2. Запишите формулу для вставки цен из таблицы Lookup table 2 на основе известных названий товаров. Для этого вставьте созданную ранее формулу в качестве искомого значения для новой функции ВПР:

   Здесь Price – именованный диапазон $A:$C в таблице Lookup table 2, а 3 – это столбец C, содержащий цены.

На рисунке ниже виден результат, возвращаемый созданной нами формулой:

Вставка столбца может «сломать» ваши вычисления.

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

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

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

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

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

Функция ВПР на разных листах

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

Чтобы использовать функцию ВПР с другого листа Excel, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(40;Лист2!A2:B15;2). Формула указывает, что диапазон поиска A2:B15 находится в Лист2.

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

Формула, которую вы видите на изображении ниже, ищет текст в ячейке А2 (« Продукт 3 ») в столбце A (1-й столбец диапазона поиска A2:B9) на листе « Цены »:

Функция ВПР в Excel – Функция ВПР на разных листах

Обратите внимание, что рекомендуется использовать абсолютные ссылки на ячейки (со знаком $) в аргументе таблица. Это предотвратит изменение диапазона поиска при копировании формулы ВПР в другие ячейки

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

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

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

Функция ВПР в Excel – Присвоение имени диапазону

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

Функция ВПР в Excel – Пример функции ВПР с именем диапазона

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

Если вы преобразовали диапазон ячеек в полнофункциональную таблицу Excel (вкладка «Вставка» —> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, а Microsoft Excel автоматически добавит имена колонок или имя таблицы в формулу:

Функция ВПР в Excel – Пример функции ВПР с именем таблицы

Полная формула может выглядеть примерно так:

или даже =ВПР(«Продукт 1»;Таблица6;2).

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

Извлекаем 2-е, 3-е и т.д. значения, используя ВПР

Вы уже знаете, что ВПР может возвратить только одно совпадающее значение, точнее – первое найденное. Но как быть, если в просматриваемом массиве это значение повторяется несколько раз, и Вы хотите извлечь 2-е или 3-е из них? А что если все значения? Задачка кажется замысловатой, но решение существует!

Предположим, в одном столбце таблицы записаны имена клиентов (Customer Name), а в другом – товары (Product), которые они купили. Попробуем найти 2-й, 3-й и 4-й товары, купленные заданным клиентом.

Простейший способ – добавить вспомогательный столбец перед столбцом Customer Name и заполнить его именами клиентов с номером повторения каждого имени, например, John Doe1, John Doe2 и т.д. Фокус с нумерацией сделаем при помощи функции COUNTIF (СЧЁТЕСЛИ), учитывая, что имена клиентов находятся в столбце B:

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

• Находим 2-й товар, заказанный покупателем Dan Brown:

• Находим 3-й товар, заказанный покупателем Dan Brown:

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

Если Вы ищите только 2-е повторение, то можете сделать это без вспомогательного столбца, создав более сложную формулу:

В этой формуле:

$F$2 – ячейка, содержащая имя покупателя (она неизменна, обратите внимание – ссылка абсолютная);

$B$ – столбец Customer Name;

Table4 – Ваша таблица (на этом месте также может быть обычный диапазон);

$C16 – конечная ячейка Вашей таблицы или диапазона.

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

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

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

Взглянем, как работает функция ВПР на конкретном примере.

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

1. Кликаем по верхней ячейке (C3) в столбце «Цена» в первой таблице. Затем, жмем на значок «Вставить функцию», который расположен перед строкой формул.

В открывшемся окне мастера функций выбираем категорию «Ссылки и массивы». Затем, из представленного набора функций выбираем «ВПР». Жмем на кнопку «OK».

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

Так как у нас искомое значение для ячейки C3, это «Картофель», то и выделяем соответствующее значение. Возвращаемся к окну аргументов функции.

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

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

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

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

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

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

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

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Исправляем ошибку #Н/Д функции ВПР в Excel

В формулах с ВПР сообщение об ошибке #N/A (#Н/Д) – означает not available (нет данных) – появляется, когда Excel не может найти искомое значение. Это может произойти по нескольким причинам.

1. Искомое значение написано с опечаткой

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

2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР

Если Вы используете формулу с условием поиска приближённого совпадения, т.е. аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, Ваша формула может сообщить об ошибке #Н/Д в двух случаях:

• Искомое значение меньше наименьшего значения в просматриваемом массиве.
• Столбец поиска не упорядочен по возрастанию.

3. Ошибка #Н/Д при поиске точного совпадения с ВПР

Если Вы ищете точное совпадение, т.е. аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ) и точное значение не найдено, формула также сообщит об ошибке #Н/Д. Более подробно о том, как искать точное и приближенное совпадение с функцией ВПР.

4. Столбец поиска не является крайним левым

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

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

5. Числа форматированы как текст

Другой источник ошибки #Н/Д в формулах с ВПР – это числа в текстовом формате в основной таблице или в таблице поиска.

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

Наиболее очевидные признаки числа в текстовом формате показаны на рисунке ниже:

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

Решение: Если это одиночное значение, просто кликните по иконке ошибки и выберите Convert to Number (Конвертировать в число) из контекстного меню.

Если такая ситуация со многими числами, выделите их и щелкните по выделенной области правой кнопкой мыши. В появившемся контекстном меню выберите Format Cells (Формат ячеек) > вкладка Number (Число) > формат Number (Числовой) и нажмите ОК.

6. В начале или в конце стоит пробел

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

Решение 1: Лишние пробелы в основной таблице (там, где функция ВПР)

Если лишние пробелы оказались в основной таблице, Вы можете обеспечить правильную работу формул, заключив аргумент lookup_value (искомое_значение) в функцию TRIM (СЖПРОБЕЛЫ):

Решение 2: Лишние пробелы в таблице поиска (в столбце поиска)

Если лишние пробелы оказались в столбце поиска – простыми путями ошибку #Н/Д в формуле с ВПР не избежать. Вместо ВПР Вы можете использовать формулу массива с комбинацией функций ИНДЕКС (INDEX), ПОИСКПОЗ (MATCH) и СЖПРОБЕЛЫ (TRIM):

Так как это формула массива, не забудьте нажать Ctrl+Shift+Enter вместо привычного Enter, чтобы правильно ввести формулу.

Ошибки #Н/Д

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

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

Как расшифровываются параметры ВПР?

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

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

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

Затем в открывшемся окне заполняем все параметры. Их вы видите на рисунке выше.

• Искомое — то, что ищем.
• Таблица — диапазон ячеек, в котором ищем и из которого затем извлекаем данные.
• Номер_столбца — номер столбца диапазона, из которого возвращаются данные, если значение в первом столбце данной строки совпадает с аргументом искомое_ значение.
• Интервальный_просмотр — необязательный, но очень важный логический аргумент. О нем мы поговорим ниже.

Ошибка #ЗНАЧ! в формулах с ВПР

В большинстве случаев, Microsoft Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Что касается ВПР, то обычно выделяют две причины ошибки #ЗНАЧ!.

1. Искомое значение длиннее 255 символов

Будьте внимательны: функция ВПР не может искать значения, содержащие более 255 символов. Если искомое значение превышает этот предел, то Вы получите сообщение об ошибке #ЗНАЧ!.

Решение: Используйте связку функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH). Ниже представлена формула, которая отлично справится с этой задачей:

2. Не указан полный путь к рабочей книге для поиска

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

Вот полная структура функции ВПР для поиска в другой книге:

Настоящая формула может выглядеть так:

Эта формула будет искать значение ячейки A2 в столбце B на листе Sheet1 в рабочей книге New Prices и извлекать соответствующее значение из столбца D.

Если любая часть пути к таблице пропущена, Ваша функция ВПР не будет работать и сообщит об ошибке #ЗНАЧ! (даже если рабочая книга с таблицей поиска в данный момент открыта).

Для получения дополнительной информации о функции ВПР, ссылающейся на другой файл Excel, обратитесь к уроку: .

3. Аргумент Номер_столбца меньше 1

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

Итак, если случилось, что аргумент col_index_num (номер_столбца) меньше 1, функция ВПР также сообщит об ошибке #ЗНАЧ!.

Если же аргумент col_index_num (номер_столбца) больше количества столбцов в заданном массиве, ВПР сообщит об ошибке #REF! (#ССЫЛ!).

Данные в таблице дублируются

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

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

Решение 1

Нужны ли Вам повторяющиеся данные в списке? Если нет – удалите их. Это можно сделать быстро при помощи кнопки Removes Duplicates (Удалить дубликаты) на вкладке Data (Данные).

Решение 2

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

Таблица ниже – это список заказов. Допустим, Вы хотите найти все заказы определённого фрукта.

Сводная таблица позволяет выбрать значение из столбца ID в фильтре, которое соответствует определенному фрукту, и получить список всех связанных заказов. В нашем примере выбрано значение ID равное 23 (Бананы).

Что такое ВПР и как ею пользоваться?

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

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

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

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

Теперь активируйте первую ячейку в блоке «Цена» и вызовите «Мастер функций». Сделать это можно, нажав на кнопку «fx», расположенную перед строкой формул, или зажав комбинацию клавиш «Shift+F3». В открывшемся диалоговом окне отыщите категорию «Ссылки и массивы». Здесь нас не интересует ничего кроме функции ВПР. Выберите её и нажмите «ОК». Кстати, следует сказать, что функция VLOOKUP может быть вызвана через вкладку «Формулы», в выпадающем списке которой также находится категория «Ссылки и массивы».

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

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

Укажите Excel, какие именно значения необходимо сопоставить функции VLOOKUP.
Для того чтобы Excel не путался и ссылался на нужные вам данные, важно зафиксировать заданную ему ссылку. Чтобы сделать это, выделите в поле «Таблица» требуемые значения и нажмите клавишу F4

Если всё выполнено верно, на экране должен появиться знак $.

Теперь мы переходим к полю аргумента «Номер страницы» и задаём ему значения «2». В этом блоке находятся все данные, которые требуется отправить в нашу рабочую таблицу, а потому важно присвоить «Интервальному просмотру» ложное значение (устанавливаем позицию «ЛОЖЬ»). Это необходимо для того, чтобы функция ВПР работала только с точными значениями и не округляла их.

Теперь, когда все необходимые действия выполнены, нам остаётся лишь подтвердить их нажатием кнопки «ОК». Как только в первой ячейке изменятся данные, нам нужно будет применить функцию ВПР ко всему Excel документу. Для этого достаточно размножить VLOOKUP по всему столбцу «Цена». Сделать это можно при помощи перетягивания правого нижнего уголка ячейки с изменённым значением до самого низа столбца. Если все получилось, и данные изменились так, как нам было необходимо, мы можем приступить к расчёту общей стоимости наших товаров. Для выполнения этого действия нам необходимо найти произведение двух столбцов — «Количества» и «Цены». Поскольку в Excel заложены все математические формулы, расчёт можно предоставить «Строке формул», воспользовавшись уже знакомым нам значком «fx».

Усложняем задачу

Давайте немного усложним задачу. Установим ещё одно пороговое значение: если продавец зарабатывает более $40000, тогда ставка комиссионных возрастает до 40%:

Вроде бы всё просто и понятно, но наша формула в ячейке B2 становится заметно сложнее. Если Вы внимательно посмотрите на формулу, то увидите, что третий аргумент функции IF (ЕСЛИ), превратился в ещё одну полноценную функцию IF (ЕСЛИ). Такая конструкция называется вложением функций друг в друга. Excel с радостью допускает такие конструкции, и они даже работают, но их гораздо сложнее читать и понимать.

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

Как бы там ни было, формула усложняется! А что, если мы введем еще один вариант ставки комиссионных, равный 50%, для тех продавцов, кто сделал объём продаж более $50000. А если кто-то продал на сумму более $60000 – тому заплатить 60% комиссионных?

Теперь формула в ячейке B2, даже если она записана без ошибок, стала совершенно не читаемой. Думаю, что найдется мало желающих использовать формулы с 4-мя уровнями вложенности в своих проектах. Должен же существовать более простой способ?!

И такой способ есть! Нам поможет функция ВПР.

Как пользоваться функцией ВПР, если данные на разных листах

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

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

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

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

Ошибки

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

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

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

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

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

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

Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а Вы решите – остаться с ВПР или переключиться на ИНДЕКС/ПОИСКПОЗ.

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

1. Поиск справа налево. Как известно любому грамотному пользователю Excel, ВПР не может смотреть влево, а это значит, что искомое значение должно обязательно находиться в крайнем левом столбце исследуемого диапазона. В случае с ПОИСКПОЗ/ИНДЕКС, столбец поиска может быть, как в левой, так и в правой части диапазона поиска. Пример: покажет эту возможность в действии.

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

Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:

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

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

3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.

Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:

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

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

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