Как протянуть впр в excel на весь столбец

Как протянуть впр в excel на весь столбец

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Перенос данных таблицы через функцию ВПР

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

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

Синтаксис ВПР

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

Аргументы функции следующие:

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

Как перемещать данные с помощью ВПР?

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

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

Искомым значением будет гречка из ячейки B3. Важно проставить именно номер ячейки, а не слово «гречка», чтобы потом можно было протянуть формулу вниз и автоматически получить остальные значения.

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

Номер столбца – в нашем случае это цифра 2, потому что необходимые нам данные (цена) стоят во втором столбце выделенной таблицы (прайса).

Интервальный просмотр – ставим 0, т.к. нам нужны точные значения, а не приблизительные.

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

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

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Функция ВПР

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

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

Самая простая функция ВПР означает следующее:

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

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

Технические подробности

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

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

• =ВПР(A2;A10:C20;2;ИСТИНА)
• =ВПР(«Иванов»;B2:E7;2;ЛОЖЬ)
• =ВПР(A2;’Сведения о клиенте’!A:F;3;ЛОЖЬ)

Имя аргумента

искомое_значение (обязательный)

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

Например, если таблица охватывает диапазон ячеек B2:D7, искомое_значение должно находиться в столбце B.

Искомое_значение может являться значением или ссылкой на ячейку.

таблица (обязательный)

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

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

номер_столбца (обязательный)

Номер столбца (начиная с 1 для крайнего левого столбца таблицы), содержащий возвращаемое значение.

интервальный_просмотр (необязательный)

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

• Вариант Приблизительное совпадение — 1/ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по номерам, а затем выполняет поиск ближайшего значения. Это способ по умолчанию, если не указан другой. Например, =ВПР(90;A1:B100;2;ЛОЖЬ).
• Вариант Точное совпадение — 0/ЛОЖЬ осуществляет поиск точного значения в первом столбце. Например, =ВПР(«Иванов»;A1:B100;2;ЛОЖЬ).

Начало работы

Для построения синтаксиса функции ВПР вам потребуется следующая информация:

1. Значение, которое вам нужно найти, то есть искомое значение.
2. Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
3. Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона вы указываете B2:D11, следует считать B первым столбцом, C — вторым и т. д.
4. При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

Теперь объедините все перечисленное выше аргументы следующим образом:

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

Примеры

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

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

Объединение данных из нескольких таблиц на одном листе с помощью функции ВПР

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

Здесь столбцы A–F и H содержат значения или формулы, которые используют значения только на этом листе, а в остальных столбцах используется функция ВПР и значения столбца А (код клиента) и столбца B (адвокат) для получения данных из других таблиц.

Распространенные неполадки

Возможная причина

Неправильное возвращаемое значение

Если аргумент интервальный_просмотр имеет значение ИСТИНА или не указан, первый столбец должны быть отсортирован по алфавиту или по номерам. Если первый столбец не отсортирован, возвращаемое значение может быть непредвиденным. Отсортируйте первый столбец или используйте значение ЛОЖЬ для точного соответствия.

• Если аргумент интервальный_просмотр имеет значение ИСТИНА, а значение аргумента искомое_значение меньше, чем наименьшее значение в первом столбце таблицы, будет возвращено значение ошибки #Н/Д.
• Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, значение ошибки #Н/Д означает, что найти точное число не удалось.

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

#ССЫЛКА! в ячейке

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

Дополнительные сведения об устранении ошибок #ССЫЛКА! в функции ВПР см. в статье Исправление ошибки #ССЫЛКА!.

Если значение аргумента таблица меньше 1, появится значение ошибки #ЗНАЧ!.

Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР.

Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР(«Иванов»;B2:E7;2;ЛОЖЬ) имя необходимо указать в формате «Иванов» и никак иначе.

Дополнительные сведения см. в статье Исправление ошибки #ИМЯ?.

Ошибки #ПЕРЕНОС! в ячейке

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

Рекомендации

Используйте абсолютные ссылки в аргументе интервальный_просмотр

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

Не сохраняйте числовые значения или значения дат как текст.

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

Сортируйте первый столбец

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

Используйте подстановочные знаки

Если значение аргумента интервальный_просмотр — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом.

Например, с помощью функции =ВПР(«Ивано?»;B2:E7;2;ЛОЖЬ) будет выполнен поиск всех случаев употребления Иванов с последней буквой, которая может меняться.

Убедитесь, что данные не содержат ошибочных символов.

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

Для получения точных результатов попробуйте воспользоваться функциями ПЕЧСИМВ или СЖПРОБЕЛЫ.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Многоразовое копирование формулы ВПР

Опубликовано 12.04.2013 Автор Ренат Лотфуллин

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

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

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

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

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

Чтобы сослаться на разные части сводной таблицы, необходимо каждый раз менять номер столбца в формуле ВПР. К примеру, в поле Описание номер столбца должен быть 3-й, а в поле Бизнес сегмент — 4-й.

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

Решение 1: Использование дополнительных ячеек

Простым решением данного вопроса будет использование дополнительных ячеек. Как вы в видите, над каждой формулой ВПР я поместил значение номера столбца. Теперь, вместо ручного прописывания этого значения в каждой формуле =ВПР($A3;$H$3:$L$13;3;ЛОЖЬ), мы ссылаемся на дополнительную ячейку. Т.е. наша формула примет вид =ВПР($A3;$H$3:$L$13;C3;ЛОЖЬ).

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

Решение 2: Использование функции СТОЛБЕЦ()

Если вам не по вкусу первое решение, и вам требуется более элегантный метод, вы можете воспользоваться функцией СТОЛБЕЦ. Этот метод не требует использования дополнительных ячеек.

Для тех, кто не знает, функция СТОЛБЕЦ принимает в качестве аргумента адрес ячейки и возвращает номер столбца этой ячейки. К примеру, СТОЛБЕЦ(D1) вернет значение 4, так как колонка D имеет четвертый порядковый номер.

В нашем случае, мне необходимо указать 3-й номер столбца в сводной таблице. Поэтому вместо ручного коддинга, я использую СТОЛБЕЦ(C1).

При копировании формулы ВПР поперек столбцов, функция СТОЛБЕЦ автоматически сдвигается вместе с другими ссылками. Это позволяет копировать ВПР без того, чтобы корректировать наши ссылки вручную.

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

Вам также могут быть интересны следующие статьи

• Четыре способа использования ВПР с несколькими условиями
• Функция СЖПРОБЕЛЫ в Excel с примерами использования
• Несколько условий ЕСЛИ в Excel
• Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel
• Как расчитать дисперсию в Excel с помощью функции ДИСП.В
• Функция СУММПРОИЗВ — как использовать формулу СУММПРОИЗВ в Excel
• Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ Excel
• Функция СУММЕСЛИМН в Excel
• Изучаем функцию СМЕЩ
• Изучаем формулу ЕСЛИ — 5 приемов Excel, о которых вы не знали

Рубрика: Формулы | Метки: ВПР, Формулы | 9 комментариев | Permalink

9 комментариев

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