Функция ВПР для Excel — Служба поддержки Office
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Когда вам требуется найти данные по строкам в таблице или диапазоне, используйте функцию ВПР — одну из функций ссылки и поиска. Например, можно найти цену автомобильной детали по ее номеру.
Самая простая функция ВПР означает следующее:
Совет: Секрет ВПР — для упорядочения данных, чтобы найти (фруктов) значение слева от возвращаемое значение (сумма), чтобы найти.
Используйте функцию ВПР для поиска значения в таблице.
Синтаксис
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
Например:
-
=ВПР(105,A2:C7,2,ИСТИНА)
-
=ВПР(“Иванов”;B2:E7;2;ЛОЖЬ)
|
Имя аргумента |
Описание |
|
искомое_значение (обязательный) |
Значение для поиска. Искомое значение должно находиться в первом столбце диапазона ячеек, указанного в таблице. Например, если таблица охватывает диапазон ячеек B2:D7, то искомое_значение должно находиться в столбце B. См. рисунок ниже. Искомое_значение может являться значением или ссылкой на ячейку. |
|
таблица (обязательный) |
Диапазон ячеек, в котором будет выполнен поиск искомого_значения и возвращаемого значения с помощью функции ВПР. Первый столбец в диапазоне ячеек должен содержать искомое_значение (например, фамилию, как показано на рисунке ниже). Диапазон ячеек также должен содержать возвращаемое значение (например, имя, как показано на рисунке ниже), которое нужно найти. Узнайте, как выбирать диапазоны на листе . |
|
номер_столбца (обязательный) |
Номер столбца (начиная с 1 для крайнего левого столбца таблицы), содержащий возвращаемое значение. |
|
интервальный_просмотр (необязательный) |
Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.
|
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
-
Значение, которое вам нужно найти, то есть искомое значение.
-
Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
-
Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона вы указываете B2:D11, следует считать B первым столбцом, C — вторым и т. д.
-
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
=ВПР(искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением; при желании укажите ИСТИНА для поиска приблизительного или ЛОЖЬ для поиска точного совпадения).
Примеры
Вот несколько примеров ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
|
Проблема |
Возможная причина |
|
Неправильное возвращаемое значение |
Если аргумент интервальный_просмотр имеет значение ИСТИНА или не указан, первый столбец должны быть отсортирован по алфавиту или по номерам. Если первый столбец не отсортирован, возвращаемое значение может быть непредвиденным. Отсортируйте первый столбец или используйте значение ЛОЖЬ для точного соответствия. |
|
#Н/Д в ячейке |
Дополнительные сведения об устранении ошибок #Н/Д в функции ВПР см. в статье Исправление ошибки #Н/Д в функции ВПР. |
|
#ССЫЛКА! в ячейке |
Если значение аргумента Дополнительные сведения об устранении ошибок #ССЫЛКА! в функции ВПР см. в статье Исправление ошибки #ССЫЛКА!. |
|
#ЗНАЧ! в ячейке |
Если значение аргумента таблица меньше 1, отобразится значение ошибки #ЗНАЧ!. Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР. |
|
#ИМЯ? в ячейке |
Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР(“Иванов”;B2:E7;2;ЛОЖЬ) имя необходимо указать в формате “Иванов” и никак иначе. Дополнительные сведения см. в разделе Исправление ошибки #ИМЯ?. |
|
Действие |
Результат |
|
Используйте абсолютные ссылки в аргументе интервальный_просмотр |
Использование абсолютных ссылок позволяет заполнить формулу так, чтобы она всегда отображала один и тот же диапазон точных подстановок. Узнайте, как использовать абсолютные ссылки на ячейки. |
|
Не сохраняйте числовые значения или значения дат как текст. |
При поиске числовых значений или значений дат убедитесь, что данные в первом столбце аргумента таблица не являются текстовыми значениями. Иначе функция ВПР может вернуть неправильное или непредвиденное значение. |
|
Сортируйте первый столбец |
Если для аргумента интервальный_просмотр указано значение ИСТИНА, прежде чем использовать функцию ВПР, отсортируйте первый столбец таблицы. |
|
Используйте подстановочные знаки |
Если значение аргумента интервальный_просмотр — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому одиночному символу, а звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, поставьте перед ними знак тильды (~). Например, с помощью функции =VLOOKUP(“Fontan?”,B2:E7,2,FALSE) можно выполнить поиск всех случаев употребления фамилии Иванов в различных падежных формах. |
|
Убедитесь, что данные не содержат ошибочных символов. |
При поиске текстовых значений в первом столбце убедитесь, что данные в нем не содержат начальных или конечных пробелов, недопустимых прямых (‘ или “) и изогнутых (‘ или “) кавычек либо непечатаемых символов. В этих случаях функция ВПР может возвращать непредвиденное значение. Для получения точных результатов попробуйте воспользоваться функциями ПЕЧСИМВ или СЖПРОБЕЛЫ. |
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
См. также
Краткий справочник: ФУНКЦИЯ ВПР
Краткий справочник: советы по устранению неполадок функции ВПР
YouTube: видео ВПР экспертов сообщества Excel
,которые вам нужно знать о функции ВПР
Как исправить #VALUE! ошибки в функции ВПР
как исправление ошибки # н/д в функции ВПР
Обзор формул в Excel
как избежать появления неработающих формул
Определять ошибок в формулах
Функции Excel (по алфавиту)
функции Excel (по категориям)
ВПР (бесплатно ознакомительная версия)
support.office.com
Варианты ВПР 4 класс с ответами 2017 год
Варианты ВПР 4 класс с ответами 2017 год
Всероссийские проверочные работы (ВПР) – итоговые контрольные работы, проводимые по отдельным учебным предметам с целью мониторинга качества образования. ВПР проводятся на региональном или школьном уровне по заданиям, разработанным федеральными экспертами, что обеспечивает единство подходов к составлению вариантов заданий, проведению и оцениванию работ. Результаты ВПР заносятся в федеральную информационную систему для дальнейшего анализа.
Смотрите новые образцы ВПР 2018 года
Демонстрационные варианты ВПР 4 класс 2017 год:
Математика
Официальная демоверсия 2017 год скачать (бланк работы, оценивание, ответы)
Бланки для работы с детьми в классе (без ответов и описания работы)
Описание проверочной работы – скачать
Русский язык
Официальная демоверсия 2017 год скачать (бланк работы, оценивание, ответы)
Бланки для работы с детьми в классе (без ответов и описания работы)
Описание проверочной работы по русскому языку 2017 г.
Окружающий мир
Официальная демоверсия 2017 год скачать (бланк работы, оценивание, ответы)
Бланки для работы с детьми в классе (без ответов и описания работы)
Описание проверочной работы по предмету окружающий мир – скачать
2017 год математика
2017 год – русский язык
Варианты ВПР за 2016 год 4 класс
График проведения ВПР в 2017 году
4 класс:
Русский язык. Часть 1 (диктант) – 18 апреля
Русский язык. Часть 2 – 20 апреля
Математика – 25 апреля
Окружающий мир – 27 апреля
5 класс:
Русский язык – 18 апреля
Математика – 20 апреля
История – 25 апреля
Биология – 27 апреля
10 класс
География – 19 апреля*
11 класс:
География – 19 апреля*
Физика – 25 апреля
Химия – 27 апреля
Биология – 11 мая
История – 18 мая
*Были внесены изменения Распоряжение Рособрнадзора от 06.12.2016 №3167-05 О внесении изменений в распоряжение Федеральной службы по надзору в сфере образования и науки от 30.08.2016 № 2322-05
*Каждая общеобразовательная организация может выбрать проведение всероссийской проверочной работа в 10 классе либо в 11 классе в зависимости от учебного плана общеобразовательной организации.
При выполнении работы учащимся не разрешено пользоваться учебником, рабочими тетрадями, справочниками по грамматике, орфографическими словарями и другими справочными материалами, калькулятором. При необходимости школьник может воспользоваться черновиком. Записи в черновике проверяться и оцениваться не будут.
Всероссийские проверочные работы – это итоговые контрольные работы, проводимые по отдельным учебным предметам для оценки уровня подготовки школьников с учетом требований Федерального государственного образовательного стандарта.
Всероссийские проверочные работы – работы, проводимые в соответствии с Правилами осуществления мониторинга системы образования в соответствии с Приказом Минобрнауки России от 26.11.2015 № 1381.
График проведения ВПР в 2017 году утверждён распоряжением Федеральной службы по надзору в сфере образования и науки от 30.08.2016 № 23222 – 05.
Официальный сайт Рособрнадзор о ВПР.
Результаты ВПР могут использоваться для формирования программ развития образования в муниципалитетах, регионах и в целом по стране, для совершенствования методики преподавания предметов в конкретных школах, а также для индивидуальной работы с учащимися по устранению имеющихся пробелов в знаниях. Работы выполняются по заданиям, разработанным на федеральном уровне, и проверяются по единым критериям.
Проверочные работы по всем предметам необходимы для старшеклассников, потому что позволят проверить их знание предметов, а также качество работы педагогов. Но вводить проверочные работы нужно постепенно, с учетом нагрузки на школьников и не доводить процесс до истерии. Такое мнение высказали большинство опрошенных ТАСС экспертов, комментируя рекомендацию Рособрнадзора провести в 2016 – 2017 учебном году проверочные работы в 11 классах сразу по всем предметам в дополнение к ЕГЭ.
Не будем скрывать, что есть тенденция, когда в старших классах начинают заниматься только двумя-тремя предметами, а все остальные перестают быть актуальными. Проведение проверочной работы поможет выявить эти проблемы к концу 11 класса для того, чтобы совершенствовать качество образования.
Всероссийские проверочные работы – видео
ucthat-v-skole.ru
Варианты ВПР 4 класс с ответами 2017 год
Варианты ВПР 4 класс с ответами 2017 год
Всероссийские проверочные работы (ВПР) – итоговые контрольные работы, проводимые по отдельным учебным предметам с целью мониторинга качества образования. ВПР проводятся на региональном или школьном уровне по заданиям, разработанным федеральными экспертами, что обеспечивает единство подходов к составлению вариантов заданий, проведению и оцениванию работ. Результаты ВПР заносятся в федеральную информационную систему для дальнейшего анализа.
Смотрите новые образцы ВПР 2018 года
Демонстрационные варианты ВПР 4 класс 2017 год:
Математика
Официальная демоверсия 2017 год скачать (бланк работы, оценивание, ответы)
Бланки для работы с детьми в классе (без ответов и описания работы)
Описание проверочной работы – скачать
Русский язык
Официальная демоверсия 2017 год скачать (бланк работы, оценивание, ответы)
Бланки для работы с детьми в классе (без ответов и описания работы)
Описание проверочной работы по русскому языку 2017 г.
Окружающий мир
Официальная демоверсия 2017 год скачать (бланк работы, оценивание, ответы)
Бланки для работы с детьми в классе (без ответов и описания работы)
Описание проверочной работы по предмету окружающий мир – скачать
2017 год математика
2017 год – русский язык
Варианты ВПР за 2016 год 4 класс
График проведения ВПР в 2017 году
4 класс:
Русский язык. Часть 1 (диктант) – 18 апреля
Русский язык. Часть 2 – 20 апреля
Математика – 25 апреля
Окружающий мир – 27 апреля
5 класс:
Русский язык – 18 апреля
Математика – 20 апреля
История – 25 апреля
Биология – 27 апреля
10 класс
География – 19 апреля*
11 класс:
География – 19 апреля*
Физика – 25 апреля
Химия – 27 апреля
Биология – 11 мая
История – 18 мая
*Были внесены изменения Распоряжение Рособрнадзора от 06.12.2016 №3167-05 О внесении изменений в распоряжение Федеральной службы по надзору в сфере образования и науки от 30.08.2016 № 2322-05
*Каждая общеобразовательная организация может выбрать проведение всероссийской проверочной работа в 10 классе либо в 11 классе в зависимости от учебного плана общеобразовательной организации.
При выполнении работы учащимся не разрешено пользоваться учебником, рабочими тетрадями, справочниками по грамматике, орфографическими словарями и другими справочными материалами, калькулятором. При необходимости школьник может воспользоваться черновиком. Записи в черновике проверяться и оцениваться не будут.
Всероссийские проверочные работы – это итоговые контрольные работы, проводимые по отдельным учебным предметам для оценки уровня подготовки школьников с учетом требований Федерального государственного образовательного стандарта.
Всероссийские проверочные работы – работы, проводимые в соответствии с Правилами осуществления мониторинга системы образования в соответствии с Приказом Минобрнауки России от 26.11.2015 № 1381.
График проведения ВПР в 2017 году утверждён распоряжением Федеральной службы по надзору в сфере образования и науки от 30.08.2016 № 23222 – 05.
Официальный сайт Рособрнадзор о ВПР.
Результаты ВПР могут использоваться для формирования программ развития образования в муниципалитетах, регионах и в целом по стране, для совершенствования методики преподавания предметов в конкретных школах, а также для индивидуальной работы с учащимися по устранению имеющихся пробелов в знаниях. Работы выполняются по заданиям, разработанным на федеральном уровне, и проверяются по единым критериям.
Проверочные работы по всем предметам необходимы для старшеклассников, потому что позволят проверить их знание предметов, а также качество работы педагогов. Но вводить проверочные работы нужно постепенно, с учетом нагрузки на школьников и не доводить процесс до истерии. Такое мнение высказали большинство опрошенных ТАСС экспертов, комментируя рекомендацию Рособрнадзора провести в 2016 – 2017 учебном году проверочные работы в 11 классах сразу по всем предметам в дополнение к ЕГЭ.
Не будем скрывать, что есть тенденция, когда в старших классах начинают заниматься только двумя-тремя предметами, а все остальные перестают быть актуальными. Проведение проверочной работы поможет выявить эти проблемы к концу 11 класса для того, чтобы совершенствовать качество образования.
Всероссийские проверочные работы – видео
ucthat-v-skole.ru
Варианты ВПР 4 класс с ответами 2016 год
Варианты ВПР 4 класс с ответами 2016 год
Всероссийские проверочные работы – это итоговые контрольные работы, проводимые по отдельным учебным предметам для оценки уровня подготовки школьников с учетом требований Федерального государственного образовательного стандарта.
Всероссийские проверочные работы – работы, проводимые в соответствии с Правилами осуществления мониторинга системы образования в соответствии с Приказом Минобрнауки России от 26.11.2015 № 1381.
Официальный сайт Рособрнадзор о ВПР.
Варианты ВПР 4 класс 2016 год:
Математика 4 класс 2016 год
Официальный образец проверочной работы по математике 2016 год скачать (бланк работы, оценивание, ответы)
Бланк для работы с детьми в классе (без ответов и описания работы)
Описание проверочной работы по математике 2016 г. – скачать
Русский язык, 4 класс 2016 год
Образец проверочной работы по русскому языку 4 класс 2016 г. Скачать
Бланк для работы с детьми в классе (без ответов и описания работы)
Описание проверочной работы по русскому языку. Скачать
Окружающий мир, 4 класс 2016 год
Официальный образец проверочной работы по окружающему миру 4 класс 2016 г. Скачать
Бланк для работы с детьми в классе (без ответов и описания работы)
Описание проверочной работы по окружающему миру. Скачать
Варианты ВПР 2015 год
ВПР для 4 класса по русскому языку 2015 год – задания и ответы
Всероссийские проверочные работы (ВПР) – итоговые контрольные работы, проводимые по отдельным учебным предметам с целью мониторинга качества образования. ВПР проводятся на региональном или школьном уровне по заданиям, разработанным федеральными экспертами, что обеспечивает единство подходов к составлению вариантов заданий, проведению и оцениванию работ. Результаты ВПР заносятся в федеральную информационную систему для дальнейшего анализа.
Тексты заданий в вариантах ВПР в целом соответствуют формулировкам, принятым в учебниках, включенных в Федеральный перечень учебников, рекомендуемых Министерством образования и науки РФ к использованию при реализации имеющих государственную аккредитацию образовательных программ начального общего образования.
В соответствии с Планом действий по модернизации общего образования на 2011–2015 гг., утвержденным распоряжением Правительства РФ от 7 сентября 2010 г. № 1507-р, в Российской Федерации реализуется поэтапное введение Федерального государственного образовательного стандарта (ФГОС) начального общего образования во всех общеобразовательных организациях Российской Федерации.
В рамках этого процесса начиная с 2011 г. первоклассники во всех школах России обучаются в соответствии с новым образовательным стандартом. Таким образом, в 2016 г. начальную школу закончат выпускники, которые обучались в соответствии с ФГОС с 1 класса. Всероссийские проверочные работы (ВПР) проводятся с учетом национально-культурной и языковой специфики многонационального российского общества в целях осуществления мониторинга результатов перехода на ФГОС и направлены на выявление уровня подготовки школьников.
Назначение ВПР по математике, русскому языку, окружающему миру – оценить уровень общеобразовательной подготовки обучающихся 4 класса в соответствии с требованиями ФГОС. ВПР позволяют осуществить диагностику достижения предметных и метапредметных результатов, в том числе уровня сформированности универсальных учебных действий (УУД) и овладения межпредметными понятиями. Результаты ВПР в совокупности с имеющейся в образовательной организации информацией, отражающей индивидуальные образовательные траектории обучающихся, могут быть использованы для оценки личностных результатов обучения.
Результаты ВПР могут быть использованы образовательными организациями для совершенствования методики преподавания предмета «Окружающий мир» в начальной школе, муниципальными и региональными органами исполнительной власти, осуществляющими государственное управление в сфере образования, для анализа текущего состояния муниципальных и региональных систем образования и формирования программ их развития. Не предусмотрено использование результатов ВПР для оценки деятельности образовательных организаций, учителей, муниципальных и региональных органов исполнительной власти, осуществляющих государственное управление в сфере образования.
ucthat-v-skole.ru
Функция ВПР в Excel с примерами
В Microsoft Excel очень много различных функций, которые значительно облегчают работу пользователю, и в данной статье мы поговорим про одну из таких. Называется она ВПР, а если на английском, то VLOOKUP.
Функция ВПР переносит значения из одной таблицы в определенные ячейки другой. Давайте объясню подробнее – в первой таблице Вы выбираете значение, которое нужно найти в левом столбце второй. Если есть совпадения, то ВПР переносит значение из указанного Вами столбца этой строки в первую таблицу. Хоть определение немного запутанное, пользоваться функцией не так уж и сложно. Рассмотрим несколько примеров.
Поскольку используют функцию чаще всего для заполнения столбца с ценой, которая указана в отдельных прайсах, то возьмем следующий пример. Например, есть таблица с фруктами (оранжевая), где для каждого указано, сколько килограмм мы хотим заказать. Соответственно, каждому фрукту необходимо выписать цену из прайса (зеленый), который дал поставщик, и потом рассчитать, сколько обойдется закупка. Просматривать каждое наименование и переносить данные сложно, тем более, если строчек с товарами тысячи, поэтому применим ВПР.
Нажмите по верхней ячейке в первой таблице в столбце Цена, а потом кнопочку «fx» в строке формул, чтобы открыть окно мастера функций.
Там, где написано категория выбираем «Ссылки и массивы». В списке выделите ее и нажимайте «ОК».
Следующее, что мы делаем – прописываем аргументы в предложенные поля.
Ставьте курсив в поле «Искомое_значение» и выделяйте в первой таблице то значение, которое будем искать. У меня это яблоко.
В строке «Таблица» необходимо выделить ту, из которой будут браться данные – шапку не выделяйте. Учтите, что крайний левый столбец должен состоять из значений, которые мы ищем. То есть, мне нужно яблоко и другие фрукты, значит, их перечень должен быть в левом столбце выделяемой области.
Чтобы после того, как мы напишем формулу и растянем ее по всему столбцу, выбранный диапазон не смещался вниз, нужно сделать ссылки абсолютными: выделите данные в поле и нажмите «F4». Теперь адрес на ячейки стал абсолютным, к ним добавился знак «$», и диапазон смещаться не будет.
Там, где номер столбца, поставьте цифру, соответствующую во второй таблице тому столбцу, данные откуда нужно переносить. У меня прайс состоит из фруктов и цены, мне нужно второе, поэтому ставлю цифру «2».
В «Интервальный_просмотр» пишем «ЛОЖЬ» – если искать нужно точные совпадения, или «Истина» – если значения могут быть приближенные. Для нашего примера выбираем первое. Если ничего не указать в данном поле, то по умолчанию выберется второе. Потом нажимайте «ОК».
Здесь обратите внимание на следующее, если работаете с числами и указываете «Истина», то вторая таблица (это наш прайс) обязательно должна быть отсортирована по возрастанию. Например, при поиске 5,25 найдется 5,27 и возьмутся данные с этой строки, хотя ниже может еще быть и число 5,2599 – но формула дальше смотреть не будет, поскольку она думает, что ниже числа только больше.
Как же работает ВПР? Она берет искомое значение (яблоко) и ищет его в крайнем левом столбце указанного диапазона (перечень фруктов). При совпадении берется значение из этой же строки, только того столбца, который указан в аргументах (2), и переносится в нужную нам ячейку (С2). Формула выглядит так:
=ВПР(А2;$G$2:$H$12;2;ЛОЖЬ)
Теперь можете растянуть ее на необходимое количество строчек вниз, потянув за правый нижний угол.
Все цены перенесены из прайса в таблицу закупок в соответствии с названиями фруктов.
Теперь можем рассчитать столбец Стоимость. Растянем формулу на необходимое количество строчек.
Если у Вас в первой таблице есть названия продуктов, которых нет в прайсе, у меня это овощи, то напротив данных пунктов формула ВПР выдаст ошибку #Н/Д.
При добавлении столбцов на лист, данные для аргумента «Таблица» функции автоматически изменятся. В примере прайс сдвинут на 2 столбца вправо. Выделим любую ячейку с формулой и видим, что вместо $G$2:$H$12 теперь $I$2:$J$14.
Теперь давайте разберемся, как работать с функцией ВПР в Эксель и с выпадающими списками. Для начала нужно сделать выпадающий список. Выделяем ячейку, вверху открываем «Данные» и жмем по кнопочке «Проверка данных».
В открывшемся окне «Тип данных» будет «Список», ниже указываем область источника – это названия фруктов, то есть тот столбец, который есть и в первой и во второй таблице. Нажимайте «ОК».
Дальше делаем так, чтобы при выборе одного из фруктов в нижней ячейке отображалась его цена.
Выделяю F2 и вставляю функцию ВПР. Аргумент первый – это сделанный список (F1).
Второй – таблица поставщика с указанными ценами. Помните, что здесь левый столбец должен совпадать с теми данными, из которых состоит выпадающий список.
Дальше указываем столбец (2), данные из которого нужно вытянуть, пишем ЛОЖЬ, для поиска точных совпадений, и нажимаем «ОК».
Получилось что-то вроде поиска: выбираем фрукт и ВПР находит в прайсе его цену.
Если у Вас были одни цены, а потом поставщики дали новый список, то нужно как-то просмотреть, что поменялось. Делать это вручную долго, а вот с помощью рассматриваемой функции все можно сделать очень быстро. Потребуется добавить один столбец и перенести в него новые значения, а потом просто сравнить данные.
Жмем по любой ячейке в столбце D и вставляем один новый.
Я назвала его Новый прайс – здесь будут новые цены, а в столбце слева, указаны старые. Новая таблица у меня находится на другом листе, чтобы Вам стало понятно, как использовать ВПР, если данные расположены на разных листах. В добавленном столбце выделяем первую пустую ячейку.
Вставляем функцию и указываем аргументы. Сначала то, что будем искать, в примере яблоко (А2). Для выбора диапазона из нового прайса, поставьте курсор в поле «Таблица» и перейдите на нужный лист, у меня «Лист1».
Выделяем мышкой необходимые столбцы и строки, без заголовков.
Дальше делаем абсолютные ссылки на ячейки: «Лист1!$A$2:$B$12». Выделите строчку и нажмите «F4», чтобы к адресам ячеек добавился знак доллара. Указываем столбец (2) и пишем «ЛОЖЬ».
В конце нажмите кнопку «ОК».
Теперь два столбца с новой и старой ценой расположены рядом и можно сделать или визуальное сравнение, или применив определенные формулы, или условное форматирование.
Надеюсь, у меня получилась пошаговая инструкция по использованию и применению функции ВПР в Excel, и Вам теперь все понятно.
Оценить статью: Загрузка… Поделиться новостью в соцсетях
« Предыдущая запись Следующая запись »
comp-profi.com
Примеры функции ВПР в Эксель
Работа с обобщающей таблицей подразумевает подтягивание в неё значений из других таблиц. Если таблиц очень много, ручной перенос заберет огромное количество времени, а если данные постоянно обновляются, то это уже будет сизифов труд. К счастью, существует функция ВПР, которая предлагает возможность автоматической выборки данных. Давайте рассмотрим конкретные примеры работы этой функции.
Определение функции ВПР
Название функции ВПР расшифровывается, как «функция вертикального просмотра». По-английски её наименование звучит – VLOOKUP. Эта функция ищет данные в левом столбце изучаемого диапазона, а затем возвращает полученное значение в указанную ячейку. Попросту говоря, ВПР позволяет переставлять значения из ячейки одной таблицы, в другую таблицу. Выясним, как пользоваться функцией VLOOKUP в Excel.
Пример использования ВПР
Взглянем, как работает функция ВПР на конкретном примере.
У нас имеется две таблицы. Первая из них представляет собой таблицу закупок, в которой размещены наименования продуктов питания. В следующей колонке после наименования расположено значение количества товара, который требуется закупить. Далее следует цена. И в последней колонке – общая стоимость закупки конкретного наименования товара, которая рассчитывается по вбитой уже в ячейку формуле умножения количества на цену. А вот цену нам как раз и придется подтянуть с помощью функции ВПР из соседней таблицы, которая представляет собой прайс-лист.
- Кликаем по верхней ячейке (C3) в столбце «Цена» в первой таблице. Затем, жмем на значок «Вставить функцию», который расположен перед строкой формул.
- В открывшемся окне мастера функций выбираем категорию «Ссылки и массивы». Затем, из представленного набора функций выбираем «ВПР». Жмем на кнопку «OK».
- После этого открывается окно, в которое нужно вставить аргументы функции. Жмем на кнопку, расположенную справа от поля ввода данных, чтобы приступить к выбору аргумента искомого значения.
- Так как у нас искомое значение для ячейки C3, это «Картофель», то и выделяем соответствующее значение. Возвращаемся к окну аргументов функции.
- Точно таким же образом кликаем по значку справа от поля ввода данных, для выбора таблицы, откуда будут подтягиваться значения.
- Выделяем всю область второй таблицы, где будет производиться поиск значений, кроме шапки. Опять возвращаемся к окну аргументов функции.
- Для того, чтобы выбранные значения сделать из относительных абсолютными, а это нам нужно, чтобы значения не сдвинулись при последующем изменении таблицы, просто выделяем ссылку в поле «Таблица», и жмем на функциональную клавишу F4. После этого к ссылке добавляются знаки доллара и она превращается в абсолютную.
- В следующей графе «Номер столбца» нам нужно указать номер того столбца, откуда будем выводить значения. Этот столбец располагается в выделенной выше области таблицы. Так как таблица состоит из двух столбцов, а столбец с ценами является вторым, то ставим номер «2».
- В последней графе «Интервальный просмотр» нам нужно указать значение «0» (ЛОЖЬ) или «1» (ИСТИНА). В первом случае, будут выводиться только точные совпадения, а во втором — наиболее приближенные. Так как наименование продуктов – это текстовые данные, то они не могут быть приближенными, в отличие от числовых данных, поэтому нам нужно поставить значение «0». Далее, жмем на кнопку «OK».
Как видим, цена картофеля подтянулась в таблицу из прайс-листа. Чтобы не проделывать такую сложную процедуру с другими товарными наименованиями, просто становимся в нижний правый угол заполненной ячейки, чтобы появился крестик. Проводим этим крестиком до самого низа таблицы.
Таким образом мы подтянули все нужные данные из одной таблицы в другую, с помощью функции ВПР.
Как видим, функция ВПР не так сложна, как кажется на первый взгляд. Разобраться в её применении не очень трудно, зато освоение этого инструмента сэкономит вам массу времени при работе с таблицами.
Мы рады, что смогли помочь Вам в решении проблемы.Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Да Нетlumpics.ru
Функция ВПР в Excel примеры использования + советы (2018)
Найти значение в таблице поможет функция ВПР в Excel примеры которой описаны ниже в статье.
Во время работы с программой у пользователей часто появляется необходимость быстрого поиска информации в одной таблице и переносе её в другой объект листа.
Понимание принципа работы ВПР существенно упростит вашу работу в Excel и поможет быстрее выполнять задачи.
VLOOKUP (Vertical Lookup) – это еще одно название функции, которое можно встретить в англоязычной версии табличного процессора.
Сама аббревиатура ВПР означает «вертикальный просмотр».
Анализ данных и их поиск в таблице осуществляется с помощью постепенного перебора элементов от строки к строке в каждой колонке.
Также, в Excel есть противоположная функция под названием HLOOKUP или ГПР – горизонтальный просмотр.
Единственное отличие работы опций состоит в том, что ГПР производит поиск в таблице по перебору столбцов, а не строчек.
Чаще пользователи отдают предпочтение именно функции ВПР, ведь большинство таблиц имеют больше строк, нежели столбцов.
Как выглядит синтаксис ВПР?
Синтаксис функции в Excel – это набор параметров, с помощью которых её можно вызвать и задать. Запись аналогична с методом записи математических функций.
Посмотреть правильный вид опции можно, открыв табличный процессор:
- Используйте уже созданный документ, либо откройте новый пустой лист;
- Кликните на клавишу «Формулы», как показано на рисунке ниже;
- В строке поиска напечатайте «ВПР» или «VLOOKUP» в зависимости от языка программы;
- Настройте категорию «Полный перечень»;
- Кликните на «Найти».
Рис.2 – поиск формул в Эксель
В результате поиска формулы вы увидите её нахождение в перечне. Нажав на элемент, внизу экрана отобразится его формула.
За скобками указывается название функции, а внутри скобок – её параметры. Внутри формулы каждый отдельный параметр прописывается в угловых <> скобах.
Общий вид описания для ВПР выглядит так:
Рис.3 – перечень параметров
Рассмотрим детальнее каждое из значений, которое описывается в скобках:
- <ЧТО> — первый элемент. Вместо него вам нужно прописать именно то значение, которое вы хотите найти в таблице. Также, можно вписывать адрес ячейки в таблице;
- <НОМЕР_СТОЛБЦА> — тут нужно напечатать номер столбика, в рамках которого будет осуществляться перебор данных.
- <ГДЕ> — здесь юзер определяет количество ячеек, задавая их размерность в виде двумерного массива данных. Первый столбик – это элемент «ЧТО»;
- <ОТСОРТИРОВАНО> — этот элемент функции ВПР отвечает за сортировку первого столбца по возрастанию (первый столбик для «ГДЕ»). В результате успешной сортировки, значение становится истинным (единица). Если возникают какие-либо неточности или ошибки во время ввода параметров – появляется ложное значение сортировки (ноль). Стоит заметить, что во время задания ВПР <ОТСОРТИРОВАНО> можно пропустить, и тогда его значение по умолчанию принимается как истина.
Читайте также:
вернуться к меню ↑Как работает ВПР. Полезный пример
Чтобы лучше понять принцип работы VLOOKUP, перейдём к рассмотрению конкретных примеров. Возьмём простейшую таблицу с двумя колонками. Пусть она обозначает код и наименование товара.
После заполнения таблицы кликнем на пустую ячейку и выпишем в неё формулу и результат ВПР. Кликните на вкладку «Формулы» и выберите VLOOKUP.
Затем введите все необходимые параметры в окно, которое изображено на рисунке 3. Подтвердите действие. В ячейке отобразится результат выполнения команды.
Рис.4 – пример поиска в простой таблице
На рисунке выше в цветных ячейках указывается значение для товара. Если вы не ввели значение для сортировки, то функция автоматически воспринимает это как единицу.
Далее программа «думает», что элементы первого столбика вашей таблицы идут по возрастанию сверху-вниз.
Благодаря этому, процедура поиска будет остановлена только когда будет достигнута строка со значением, номер которого уже превышает искомый объект.
Рассмотрим еще один пример использования функции, который часто встречается во время реальной работы с прайсами и листами наименований товара.
В случае, когда пользователь пропечатывает, что последний элемент в скобках равен нулю, Эксель работает следующим образом: опция проверяет самый первый столбец в заданном диапазоне массива.
Поиск будет остановлен автоматически, как только найдется совпадение параметра «ЧТО» и имени товара.
Если в таблице нет введенного вами идентификатора для имени продукции, в результате выполнения поиска VLOOKUP будет получено значение «Н/Д», что означает отсутствие элемента для заданного номера.
Рис.5 – второй пример для ВПР
вернуться к меню ↑Когда использовать ВПР?
Выше описаны два варианта применения VLOOKUP.
Первая вариация VLOOKUP подойдет для следующих случаев:
- Когда необходимо разделить значения объекта табличного процессора по его диапазонам;
- Для тех таблиц, в которых параметр ГДЕ может содержать несколько идентичных значений. В таком случае, формула вернет только то, которое находится в последней строчке относительно массива;
- Когда нужно искать значения, которые больше того, которое может содержаться в первом столбике. Так вы найдете последнюю строку таблицы практическим мгновенно.
Первый вариант правописания VLOOKUP не может найти элемент, если не было найдено значения меньше искомого или равное ему. В ячейке для результата вернется только «Н/Д».
Второй вариант для ВПР (с указанием «0» для сортировки) применяется для больших таблиц, в которых встречаются одинаковые названия для нескольких ячеек.
VLOOKUP позволит легко оперировать данными, ведь возвращает первую найденную строку.
В реальной жизни опция используется, когда нужно осуществить поиск по заданному диапазону – он не обязательно должен соответствовать всей величине таблицы.
В объектах листа, в которых встречаются разные виды значений, ВПР помогает найти текстовые строки.
Рис.6 – пример поиска текстового значения
ВПР бывает полезна, когда нужно удалить много лишних пробелов. Функция быстро находит все наименования с пробелами, и вы сможете быстро удалить их. Пример:
Рис.7 – ВПР при удалении пробелов
вернуться к меню ↑Быстродействие VLOOKUP
Большинство пользователей предпочитать не вписывать параметр <СОРТИРОВКА> во время работы с функцией. Конечно же, ноль вписать проще, но игнорирование оператора замедляет поиск.
При работе с большими массивами данных Эксель может работать слишком медленно. На старых устройствах табличный процессор иногда даже зависает из-за слишком медленного поиска с ВПР.
Если на одном листе вашего документа представлено сразу несколько тысяч формул, лучше позаботиться о сортировке первого столбца.
Это позволяет увеличить общую производительность поиска на целых 400%-500%.
Такая разница в скорости выполнении разных видов VLOOKUP заключается в том, что любой компьютерной программе намного проще работать с уже отсортированными данными, осуществляя бинарный поиск.
В первом виде функции применяется бинарный поиск, а во втором – нет, ведь оптимизация этого способа задания функции все еще отсутствует.
Тематические видеоролики:
Функция ВПР в Excel – примеры использования и советы
Проголосоватьgeek-nose.com