![]() | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
КАТАЛОГ ТОВАРОВ ![]() Срок доставки товара в течении 1-3 дней !!!
|
Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ. Впр excelФункция ВПР для Excel — Служба поддержки OfficeКогда вам требуется найти данные по строкам в таблице или диапазоне, используйте функцию ВПР — одну из функций ссылки и поиска. Например, можно найти цену автомобильной детали по ее номеру. Самая простая функция ВПР означает следующее: =ВПР(искомое значение; диапазон для поиска значения; номер столбца в диапазоне с возвращаемым значением; точное или приблизительное совпадение — указывается как 0/ЛОЖЬ или 1/ИСТИНА). ![]() Это видео — часть учебного курса Использование функции ВПР. Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (номер детали) отображалось слева от возвращаемого значения, которое нужно найти (цена детали). Используйте функцию ВПР для поиска значения в таблице. Синтаксис ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр]) Например:
Начало работыДля построения синтаксиса функции ВПР вам потребуется следующая информация:
Теперь объедините все перечисленное выше аргументы следующим образом: =ВПР(искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением; при желании укажите ИСТИНА для поиска приблизительного или ЛОЖЬ для поиска точного совпадения). На рисунке ниже показано, как настроить функцию ВПР, чтобы получить цену тормозных дисков, равную 5487. ![]()
ПримерыЗдесь приведены еще несколько примеров использования функции ВПР. Пример 1![]() Пример 2![]() Пример 3![]() Пример 4![]() Пример 5![]()
У вас есть вопрос об определенной функции?Задать вопрос на форуме сообщества, посвященном Excel Помогите нам улучшить ExcelУ вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel. См. также:Краткий справочник: функция ВПР Краткий справочник: советы по устранению неполадок с функцией ВПР Все, что необходимо знать о функции ВПР Исправление ошибки #ЗНАЧ! в функции ВПР Исправление ошибки #Н/Д в функции ВПР Общие сведения о формулах в Excel Рекомендации, позволяющие избежать появления неработающих формул Обнаружение ошибок в формулах Функции Excel (по алфавиту) Функции Excel (по категориям) support.office.com Видео: функция ВПР - Excel![]() Проверьте, как это работает!Находите данные по строкам в таблице или диапазоне с помощью функции ВПР.
Формула в этом видео выглядит так: =ВПР(A7;A2:B5;2;ЛОЖЬ). Самая простая функция ВПР означает следующее: =ВПР(искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением; при желании укажите ИСТИНА для поиска приблизительного или ЛОЖЬ для поиска точного совпадения).
Функция ВПР support.office.com Краткий справочник: функция ВПР - Служба поддержки OfficeПримечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) . ВПР — это одна из наиболее распространенных и полезных функций Excel, однако при редком использовании ее формулу трудно вспомнить. Если вам нужен только синтаксис функции ВПР, вот он: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])Чтобы скачать справочник, объясняющий, что имеете аргументы и их использование, нажмите ссылку ниже. Справочник ВПР открывается в виде PDF-файла в Программе Adobe Reader. Вы можете распечатать копию карточку или сохраните его там, где на компьютере, чтобы использовать впоследствии.
Дополнительные сведенияВы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice. См. такжеФункция ВПР support.office.com Как пользоваться функцией ВПР в Excel: пример с двумя таблицамиВПР в Excel очень удобный и часто используемый инструмент для работы с таблицами как с базой данных и не только. Данная функция проста в освоении и очень функциональна при выполнении. Благодаря гармоничному сочетанию простоты и функциональности ВПР пользователи активно ее используют в процессе работы с электронными таблицами. Но стоит отметить, что у данной функции достаточно много недостатков, которые ограничивают возможности. Поэтому ее иногда нужно использовать с другими функциями или вообще заменять более сложными. Для начала на готовом примере применения функции рассмотрим ее преимущества, а потом определим недостатки. Как работает функция ВПР в Excel: примерФункция ВПР предназначена для выборки данных из таблицы Excel по определенным критериям поиска. Например, если таблица состоит из двух колонок: «Наименование товара» и «Цена». Рядом находится другая таблица, которая будет искать в первой таблице по наименованию товара и получать значение соответствующей цены.
В поле «Исходное значение» вводим ссылку на ячейку под наименованием товара второй таблицы D3. В поле «Таблица» вводим диапазон всех значений первой таблицы A2:B7. В поле «Номер столбца» вводим значение 2, так как во втором столбце у нас находиться цена, которую мы хотим получить при поиске товара. И нажимаем ОК. Теперь под заголовком столбца второй таблицы «Товар» введите наименования того товара по котором нам нужно узнать его цену. И нажмите Enter. Функция позволяет нам быстро находить данные и получать по ним все необходимые значения из больших таблиц. Это похоже на работу с базами данных. Когда к базе создается запрос, а в ответ выводятся результаты, которые являются ответом на критерии запроса. Функция ВПР в Excel и две таблицыНемного усложним задание, изменив структуру и увеличив объем данных в таблице. Расширьте объем данных первой таблицы, добавив столбцы: «январь», «февраль», «март». Там запишем суммы продаж в первом квартале как показано на рисунке: Как видите вторую таблицу так же нужно немного изменить, чтобы не потерять суть задачи. Теперь нам нужно сделать выборку данных с помощью функции ВПР отдельно по товару и просуммировать продажи за первый квартал. Для этого переходим в ячейку h4 и после вызова функции заполняем ее аргументы следующим образом:
Теперь вводите в ячейку G3 наименование товара, в ячейке h4 получаем сумму продаж в первом квартале по данному товару. Происходит сравнение двух таблиц в Excel функцией ВПР и как только определяется совпадение запрашиваемых данных, сразу подставляется их значения для суммирования функцией СУММ. Весь процесс выполняется циклически благодаря массиву функций о чем свидетельствуют фигурные скобки в строке формул. Примечание. Если ввести вручную крайние фигурные скобки в строку формул то это не приведет ни ка какому результату. Выполнить функцию циклическим массивом можно только через комбинацию горячих клавиш: CTRL+SHIFT+ENTER. Стоит отметить, что главным недостатком функции ВПР является отсутствие возможности выбрать несколько одинаковых исходных значений в запросе. Скачать пример функции ВПР с двумя таблицами Другими словами если в нашей таблице повторяются значения «груши», «яблока» мы не сможем просуммировать всех груш и яблок. Для этого нужно использовать функцию ПРОСМОТР(). Она очень похожа на ВПР но умеет хорошо работать с массивами в исходных значениях. exceltable.com Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗПримечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) . Предположим, что у вас есть списка номеров офисов расположение и вам нужно знать, какие сотрудники являются в каждой программы office. Электронную таблицу огромный, поэтому вы думаете, что он является довольно сложной задачи. Это задача несложная делать с помощью функции поиска. Функции ВПР и ГПР вместе с функций индекс и ПОИСКПОЗ,описаны некоторые из наиболее полезных функций в Microsoft Excel. Примечание: Функция мастер подстановок больше не доступен в Microsoft Excel. Вот пример того, как использовать функцию ВПР. =ВПР(B2;C2:E7,3,ИСТИНА) В этом примере B2 — это первый аргумент— элемент данных, функция должна работать. Функции ВПР это первый аргумент — значение, которое требуется найти. Этот аргумент может быть ссылка на ячейку или фиксированным значением, например «строфа» или 21,000. Второй аргумент — это диапазон ячеек, C2-:E7, в котором выполняется поиск значения, которые нужно найти. Третий аргумент — это столбец в диапазон ячеек, содержащий значение, которое вы поиска. Четвертый аргумент не является обязательным. Введите TRUE или FALSE. Если ввести значение ИСТИНА или аргумент оставлен пустым, функция возвращает приблизительное значение, указать в качестве первого аргумента. Если ввести значение FALSE, функция будут соответствовать значение в первом аргументе предоставить. Другими словами, оставив четвертый аргумент пустым, или ввести значение ИСТИНА — обеспечивает гибкость. В этом примере показано, как работает функция. При вводе значения в ячейке B2 (первый аргумент) функция ВПР ищет ячейки в диапазоне C2:E7 (2-й аргумент) и возвращает ближайший Приблизительное совпадение с третьего столбца в диапазоне, столбец E (3-й аргумент).
Четвертый аргумент пуст, поэтому функция возвращает Приблизительное совпадение. Если это не так, вам придется введите одно из значений в столбцах C и D, чтобы получить результат вообще. Когда вы будете довольны ВПР, ГПР одинаково удобно использовать. Введите те же аргументы, но он осуществляет поиск в строках вместо столбцов. " Попробуйте попрактиковатьсяЕсли вы хотите поэкспериментировать с функциями подстановки, прежде чем применять их к собственным данным, то некоторые образцы данных. Некоторые пользователи Excel, такие как с помощью функции ВПР и ГПР; другие пользователи предпочитают с помощью функций индекс и ПОИСКПОЗ вместе. Попробуйте каждый из методов и посмотрите, какие из них подходящий вариант. Пример функции ВПР в действииСкопируйте следующие данные в пустой лист. Совет: Прежде чем вставлять данные в Excel, установите для столбцов A – С ширину в 250 пикселей и нажмите кнопку Перенос текста (вкладка "Главная ", группа " Выравнивание ").
Пример функции ГПРСкопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel. Совет: Прежде чем вставлять данные в Excel, установите для столбцов A – С ширину в 250 пикселей и нажмите кнопку Перенос текста (вкладка "Главная ", группа " Выравнивание ").
ИНДЕКС и ПОИСКПОЗ примерыВ этом примере последней использует функций индекс и ПОИСКПОЗ вместе для возвращения раннюю номер счета-фактуры и его соответствующих даты для каждого из пяти городов. Так как дата возвращаются в виде числа, мы используем функцию текст отформатировать его как дату. Результат функции ПОИСКПОЗ фактически используется функция индекс аргументом. Сочетание функций индекс и ПОИСКПОЗ используются два раза в каждой формуле — сначала получить номер счета-фактуры, а затем для возврата даты. Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel. Совет: Прежде чем вставлять данные в Excel, установите для столбцов A – D ширину в 250 пикселей и нажмите кнопку Перенос текста (вкладка "Главная ", группа " Выравнивание ").
Дополнительные сведения о функциях поискаsupport.office.com Исправление ошибки #ЗНАЧ! в функции ВПРПримечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) . Если вы работаете с функцией ВПР, весьма вероятно, что вы не раз сталкивались с ошибкой #ЗНАЧ!. В этом разделе перечислены наиболее распространенные проблемы с функцией ВПР и их возможные решения. Проблема: длина аргумента искомое_значение превышает 255 символов.Решение: Сократите значение или используйте сочетание функций ИНДЕКС и ПОИСКПОЗ в качестве обходного пути.
Примечание: Это формула массива, которую нужно вводить с помощью клавиш CTRL+SHIFT+ВВОД. Excel автоматически заключит формулу в фигурные скобки {}. Если вы попытаетесь ввести их вручную, Excel отобразит формулу как текст. Проблема: аргумент номер_столбца содержит текст или значение меньше 0.
Номер столбца — это индекс столбца, который нужно вернуть, отсчитываемый вправо от столбца поиска. Причиной может быть то, что вы допустили ошибку при вводе аргумента номер_столбца или случайно указали число менее 1 в качестве значения индекса (такое часто происходит, если другая функция Excel, вложенная в функцию ВПР, возвращает число, например 0, в качестве значения аргумента номер_столбца). Минимальное значение аргумента номер_столбца равно 1. При этом значение 1 соответствует столбцу поиска, значение 2 — первому столбцу справа от столбца поиска и т. д. Поэтому при поиске в столбце A значение 1 указывает на него, значение 2 — на столбец B, значение 3 — на столбец C и т. д. У вас есть вопрос об определенной функции?Задать вопрос на форуме сообщества, посвященном Excel Помогите нам улучшить ExcelУ вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel. См. такжеИсправление #VALUE! ошибки Все, что вам нужно знать о функции ВПР Обзор формул в Excel Как избежать появления неработающих формул Использовать проверку ошибок, чтобы обнаружить ошибки в формулах Функции Excel все (по алфавиту) Функции Excel все (по категориям) support.office.com Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗДля поиска значения в большом списке можно использовать функцию просмотра. Функция ВПР часто используется, но можно задействовать и функции ГПР, ИНДЕКС и ПОИСКПОЗ. Общий вид функции ВПР и ее аргументов: =ВПР(<искомое значение>;<диапазон поиска>;<столбец>;<приблизительное соответствие>) Например, =ВПР(21500;C2:E7;3;ЛОЖЬ).
Хотя четвертый аргумент не является обязательным, большинство пользователей вводят аргумент ЛОЖЬ (или 0). Почему? Потому что в этом случае функция будет искать точное совпадение. Можно ввести аргумент ИСТИНА или вообще не вводить аргумент, но если точное совпадение не будет найдено, функция вернет наиболее близкое приблизительное совпадение, а большинство людей приблизительное совпадение не устраивает. Чтобы убедиться в том, что использование приблизительного совпадения может иметь серьезные последствия, предположим, что ищется цена детали с идентификатором 2345768, но вы перепутали две цифры и ввели их в формулу следующим образом: =ВПР(2345678;A1:E7;5). Формула возвращает цену на другую деталь, потому что функция ВПР нашла ближайшее число, меньшее или равное указанному (2345678). Эта ошибка может привести к неправильному выставлению счета клиенту. Если для аргумента "приблизительное соответствие" указано значение ЛОЖЬ или 0, а точного совпадения нет, вместо неправильного значения формула возвращает в ячейку строку "#Н/Д". Это наилучшее решение. В данном случае "#Н/Д" не означает, что формула введена неправильно (за исключением неправильно введенного номера). Это означает, что номер 2345678 не был найден, потому что вы искали значение 2345768. В этом примере показано, как работает функция. Если ввести значение в ячейку B2 (первый аргумент), функция ВПР выполняет поиск в ячейках C2:E7 (второй аргумент) и возвращает наиболее близкое приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).
В данном примере четвертый аргумент оставлен пустым, поэтому функция возвращает приблизительное совпадение. Использование функции ГПРРазобравшись с функцией ВПР, несложно будет освоить и функцию ГПР. Функция ГПР использует те же аргументы, но выполняет поиск в строках вместо столбцов. Одновременное использование функций ИНДЕКС и ПОИСКПОЗЕсли вы не хотите ограничиваться поиском в крайнем левом столбце, можно использовать сочетание функций ИНДЕКС и ПОИСКПОЗ. Формула, использующая эти функции вместе, немного сложнее формулы с функцией ВПР, но она открывает больше возможностей. Поэтому некоторые пользователи предпочитают применять сочетание функций ИНДЕКС и ПОИСКПОЗ, а не функцию ВПР. В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения "Воронеж" в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Еще о функциях поискаК началу страницы support.office.com ![]() |
![]() |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
© All rights reserved | Карта сайта
|