Срок доставки товара в течении 1-3 дней !!!
|
|
Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ. Впр excel
Функция ВПР для Excel — Служба поддержки Office
Когда вам требуется найти данные по строкам в таблице или диапазоне, используйте функцию ВПР — одну из функций ссылки и поиска. Например, можно найти цену автомобильной детали по ее номеру.
Самая простая функция ВПР означает следующее:
=ВПР(искомое значение; диапазон для поиска значения; номер столбца в диапазоне с возвращаемым значением; точное или приблизительное совпадение — указывается как 0/ЛОЖЬ или 1/ИСТИНА).
Это видео — часть учебного курса Использование функции ВПР.
Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (номер детали) отображалось слева от возвращаемого значения, которое нужно найти (цена детали).
Используйте функцию ВПР для поиска значения в таблице.
Синтаксис
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
Например:
|
Имя аргумента |
Описание |
|
искомое_значение (обязательный) |
Значение для поиска. Искомое значение должно находиться в первом столбце диапазона ячеек, указанного в таблице. Например, если таблица охватывает диапазон ячеек B2:D7, то искомое_значение должно находиться в столбце B. См. рисунок ниже. Искомое_значение может являться значением или ссылкой на ячейку. |
|
таблица (обязательный) |
Диапазон ячеек, в котором будет выполнен поиск искомого_значения и возвращаемого значения с помощью функции ВПР. Первый столбец в диапазоне ячеек должен содержать искомое_значение (например, фамилию, как показано на рисунке ниже). Диапазон ячеек также должен содержать возвращаемое значение (например, имя, как показано на рисунке ниже), которое нужно найти. Узнайте, как выбирать диапазоны на листе . |
|
номер_столбца (обязательный) |
Номер столбца (начиная с 1 для крайнего левого столбца таблицы), содержащий возвращаемое значение. |
|
интервальный_просмотр (необязательный) |
Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.
|
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
-
Значение, которое вам нужно найти, то есть искомое значение.
-
Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
-
Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона вы указываете B2:D11, следует считать B первым столбцом, C — вторым и т. д.
-
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
=ВПР(искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением; при желании укажите ИСТИНА для поиска приблизительного или ЛОЖЬ для поиска точного совпадения).
На рисунке ниже показано, как настроить функцию ВПР, чтобы получить цену тормозных дисков, равную 5487.
-
В ячейке D13 указано искомое_значение, т. е. значение, которое вам нужно найти.
Ячейки B2:E11 (выделены в таблице желтым цветом) представляют собой таблицу или диапазон, в котором находится искомое значение.
-
3 — это номер_столбца в таблице, которая содержит возвращаемое значение. В приведенном примере третьим столбцом в является столбец Цена детали, поэтому результатом формулы будет значение из этого столбца.
-
Для аргумента интервальный_просмотр указано значение ЛОЖЬ , поэтому возвращаемым значением будет точное совпадение.
-
Результатом формулы ВПР является цена тормозных дисков, равная 5487.
Примеры
Здесь приведены еще несколько примеров использования функции ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
|
Проблема |
Возможная причина |
|
Неправильное возвращаемое значение |
Если аргумент интервальный_просмотр имеет значение ИСТИНА или не указан, первый столбец должны быть отсортирован по алфавиту или по номерам. Если первый столбец не отсортирован, возвращаемое значение может быть непредвиденным. Отсортируйте первый столбец или используйте значение ЛОЖЬ для точного соответствия. |
|
|
Дополнительные сведения об устранении ошибок #Н/Д в функции ВПР см. в статье Исправление ошибки #Н/Д в функции ВПР. |
|
#ССЫЛКА! в ячейке |
Если значение аргумента номер_столбца превышает число столбцов в таблице, отобразится значение ошибки #ССЫЛКА!. Дополнительные сведения об устранении ошибок #ССЫЛКА! в функции ВПР см. в статье Исправление ошибки #ССЫЛКА!. |
|
#ЗНАЧ! в ячейке |
Если значение аргумента таблица меньше 1, отобразится значение ошибки #ЗНАЧ!. Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР. |
|
#ИМЯ? в ячейке |
Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР("Иванов";B2:E7;2;ЛОЖЬ) имя необходимо указать в формате "Иванов" и никак иначе. Дополнительные сведения см. в разделе Исправление ошибки #ИМЯ?. |
|
Действие |
Результат |
|
Используйте абсолютные ссылки в аргументе интервальный_просмотр |
Использование абсолютных ссылок позволяет заполнить формулу так, чтобы она всегда отображала один и тот же диапазон точных подстановок. Узнайте, как использовать абсолютные ссылки на ячейки. |
|
Не сохраняйте числовые значения или значения дат как текст. |
При поиске числовых значений или значений дат убедитесь, что данные в первом столбце аргумента таблица не являются текстовыми значениями. Иначе функция ВПР может вернуть неправильное или непредвиденное значение. |
|
Сортируйте первый столбец |
Если для аргумента интервальный_просмотр указано значение ИСТИНА, прежде чем использовать функцию ВПР, отсортируйте первый столбец таблицы. |
|
Используйте подстановочные знаки |
Если значение аргумента интервальный_просмотр — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому одиночному символу, а звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, поставьте перед ними знак тильды (~). Например, с помощью функции =VLOOKUP("Fontan?",B2:E7,2,FALSE) можно выполнить поиск всех случаев употребления фамилии Иванов в различных падежных формах. |
|
Убедитесь, что данные не содержат ошибочных символов. |
При поиске текстовых значений в первом столбце убедитесь, что данные в нем не содержат начальных или конечных пробелов, недопустимых прямых (' или ") и изогнутых (‘ или “) кавычек либо непечатаемых символов. В этих случаях функция ВПР может возвращать непредвиденное значение. Для получения точных результатов попробуйте воспользоваться функциями ПЕЧСИМВ или СЖПРОБЕЛЫ. |
У вас есть вопрос об определенной функции?
Задать вопрос на форуме сообщества, посвященном 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 по определенным критериям поиска. Например, если таблица состоит из двух колонок: «Наименование товара» и «Цена». Рядом находится другая таблица, которая будет искать в первой таблице по наименованию товара и получать значение соответствующей цены.
- Переходим в ячейку второй таблицы под названием столбца «Цена».
- Выберите «Формулы»-«Ссылки и массивы»-«ВПР». Ввести функцию ВПР можно и с помощью «мастера функций». Для этого нажмите на кнопку «fx», которая находиться в начале строки формул. Или нажмите комбинацию горячих клавиш SHIFT+F3. В появившимся диалоговом окне на поле категория, выберите из выпадающего списка: «Ссылки и массивы», а потом ниже укажите на функцию.
- Заполняем аргументы функции.
В поле «Исходное значение» вводим ссылку на ячейку под наименованием товара второй таблицы D3. В поле «Таблица» вводим диапазон всех значений первой таблицы A2:B7. В поле «Номер столбца» вводим значение 2, так как во втором столбце у нас находиться цена, которую мы хотим получить при поиске товара. И нажимаем ОК.
Теперь под заголовком столбца второй таблицы «Товар» введите наименования того товара по котором нам нужно узнать его цену. И нажмите Enter.
Функция позволяет нам быстро находить данные и получать по ним все необходимые значения из больших таблиц. Это похоже на работу с базами данных. Когда к базе создается запрос, а в ответ выводятся результаты, которые являются ответом на критерии запроса.
Функция ВПР в Excel и две таблицы
Немного усложним задание, изменив структуру и увеличив объем данных в таблице. Расширьте объем данных первой таблицы, добавив столбцы: «январь», «февраль», «март». Там запишем суммы продаж в первом квартале как показано на рисунке:
Как видите вторую таблицу так же нужно немного изменить, чтобы не потерять суть задачи.
Теперь нам нужно сделать выборку данных с помощью функции ВПР отдельно по товару и просуммировать продажи за первый квартал. Для этого переходим в ячейку h4 и после вызова функции заполняем ее аргументы следующим образом:
- Исходное значение: G3.
- Таблица: A2:E7. Диапазон нашей таблицы расширен.
- Номер столбца: {3;4;5}. Нам нужно с помощью функции обращаться одновременно к нескольким столбцам, поэтому значение данного аргумента будет взято в массив фигурными скобками. А номера столбцов следует перечислять через точку с запятой.
- Интервальный просмотр: ЛОЖЬ.
- Чтобы значения в выбранных столбцах суммировались, тогда всю функцию нужно поместить внутрь функции СУММ(). Вся формула в целом выглядит следующим образом: =СУММ(ВПР(G3;A3:E6;{3;4;5};ЛОЖЬ)).
- После ввода данной формулы следует нажать комбинацию клавиш: CTRL+SHIFT+ENTER. Внимание! Если не нажать комбинацию этих клавиш формула будет работать ошибочно. В Excel иногда приходиться выполнять функции в массиве для этого нужно обязательно использовать клавиши: CTRL+SHIFT+ENTER при вводе функций. Тогда в строке формул все содержимое будет взято в фигурные скобки «{}», что свидетельствует о выполнении формулы в массиве.
Теперь вводите в ячейку 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 пикселей и нажмите кнопку Перенос текста (вкладка "Главная ", группа " Выравнивание ").
|
Плотность |
Вязкость |
Температура |
|
0,457 |
3,55 |
500 |
|
0,525 |
3,25 |
400 |
|
0,606 |
2,93 |
300 |
|
0,675 |
2,75 |
250 |
|
0,746 |
2,57 |
200 |
|
0,835 |
2,38 |
150 |
|
0,946 |
2,17 |
100 |
|
1,09 |
1,95 |
50 |
|
1,29 |
1,71 |
0 |
|
Формула |
Описание |
Результат |
|
=ВПР(1,A2:C10,2) |
Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равняется 1 и составляет 0,946, а затем возвращает значение из столбца B в той же строке. |
2,17 |
|
=ВПР(1,A2:C10,3,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равняется 1 и составляет 0,946, а затем возвращает значение из столбца C в той же строке. |
100 |
|
=ВПР(0,7,A2:C10,3,ЛОЖЬ) |
Используя точное соответствие, функция ищет в столбце A значение 0,7. Поскольку точного соответствия нет, возвращается сообщение об ошибке. |
#Н/Д |
|
=ВПР(0,1,A2:C10,2,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 0,1. Поскольку 0,1 меньше наименьшего значения в столбце A, возвращается сообщение об ошибке. |
#Н/Д |
|
=ВПР(2,A2:C10,2,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 2, находит наибольшее значение, которое меньше или равняется 2 и составляет 1,29, а затем возвращает значение из столбца B в той же строке. |
1,71 |
Пример функции ГПР
Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel.
Совет: Прежде чем вставлять данные в Excel, установите для столбцов A – С ширину в 250 пикселей и нажмите кнопку Перенос текста (вкладка "Главная ", группа " Выравнивание ").
|
Оси |
Подшипники |
Болты |
|
4 |
4 |
9 |
|
5 |
7 |
10 |
|
6 |
8 |
11 |
|
Формула |
Описание |
Результат |
|
=ГПР("Оси";A1:C4;2;ИСТИНА) |
Поиск слова "Оси" в строке 1 и возврат значения из строки 2, находящейся в том же столбце (столбец A). |
4 |
|
=ГПР("Подшипники";A1:C4;3;ЛОЖЬ) |
Поиск слова "Подшипники" в строке 1 и возврат значения из строки 3, находящейся в том же столбце (столбец B). |
7 |
|
=ГПР("П";A1:C4;3;ИСТИНА) |
Поиск буквы "П" в строке 1 и возврат значения из строки 3, находящейся в том же столбце. Так как "П" найти не удалось, возвращается ближайшее из меньших значений: "Оси" (в столбце A). |
5 |
|
=ГПР("Болты";A1:C4;4) |
Поиск слова "Болты" в строке 1 и возврат значения из строки 4, находящейся в том же столбце (столбец C). |
11 |
|
=ГПР(3;{1;2;3:"a";"b";"c";"d";"e";"f"};2;ИСТИНА) |
Поиск числа 3 в трех строках константы массива и возврат значения из строки 2 того же (в данном случае — третьего) столбца. Константа массива содержит три строки значений, разделенных точкой с запятой (;). Так как "c" было найдено в строке 2 того же столбца, что и 3, возвращается "c". |
c |
ИНДЕКС и ПОИСКПОЗ примеры
В этом примере последней использует функций индекс и ПОИСКПОЗ вместе для возвращения раннюю номер счета-фактуры и его соответствующих даты для каждого из пяти городов. Так как дата возвращаются в виде числа, мы используем функцию текст отформатировать его как дату. Результат функции ПОИСКПОЗ фактически используется функция индекс аргументом. Сочетание функций индекс и ПОИСКПОЗ используются два раза в каждой формуле — сначала получить номер счета-фактуры, а затем для возврата даты.
Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel.
Совет: Прежде чем вставлять данные в Excel, установите для столбцов A – D ширину в 250 пикселей и нажмите кнопку Перенос текста (вкладка "Главная ", группа " Выравнивание ").
|
Счет |
Город |
Дата выставления счета |
Самая ранняя счет по городу, с датой |
|
3115 |
Казань |
07.04.12 |
="Казань = "&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Казань",$B$2:$B$33,0),1)& ", Дата выставления счета: " & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Казань",$B$2:$B$33,0),3),"m/d/yy") |
|
3137 |
Казань |
09.04.12 |
="Орел = "&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Орел",$B$2:$B$33,0),1)& ", Дата выставления счета: " & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Орел",$B$2:$B$33,0),3),"m/d/yy") |
|
3154 |
Казань |
11.04.12 |
="Челябинск = "&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Челябинск",$B$2:$B$33,0),1)& ", Дата выставления счета: " & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Челябинск",$B$2:$B$33,0),3),"m/d/yy") |
|
3191 |
Казань |
21.04.12 |
="Нижний Новгород = "&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Нижний Новгород",$B$2:$B$33,0),1)& ", Дата выставления счета: " & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Нижний Новгород",$B$2:$B$33,0),3),"m/d/yy") |
|
3293 |
Казань |
25.04.12 |
="Москва = "&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Москва",$B$2:$B$33,0),1)& ", Дата выставления счета: " & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Москва",$B$2:$B$33,0),3),"m/d/yy") |
|
3331 |
Казань |
27.04.12 |
|
|
3350 |
Казань |
28.04.12 |
|
|
3390 |
Казань |
01.05.12 |
|
|
3441 |
Казань |
02.05.12 |
|
|
3517 |
Казань |
08.05.12 |
|
|
3124 |
Орел |
09.04.12 |
|
|
3155 |
Орел |
11.04.12 |
|
|
3177 |
Орел |
19.04.12 |
|
|
3357 |
Орел |
28.04.12 |
|
|
3492 |
Орел |
06.05.12 |
|
|
3316 |
Челябинск |
25.04.12 |
|
|
3346 |
Челябинск |
28.04.12 |
|
|
3372 |
Челябинск |
01.05.12 |
|
|
3414 |
Челябинск |
01.05.12 |
|
|
3451 |
Челябинск |
02.05.12 |
|
|
3467 |
Челябинск |
02.05.12 |
|
|
3474 |
Челябинск |
04.05.12 |
|
|
3490 |
Челябинск |
05.05.12 |
|
|
3503 |
Челябинск |
08.05.12 |
|
|
3151 |
Нижний Новгород |
09.04.12 |
|
|
3438 |
Нижний Новгород |
02.05.12 |
|
|
3471 |
Нижний Новгород |
04.05.12 |
|
|
3160 |
Москва |
18.04.12 |
|
|
3328 |
Москва |
26.04.12 |
|
|
3368 |
Москва |
29.04.12 |
|
|
3420 |
Москва |
01.05.12 |
|
|
3501 |
Москва |
06.05.12 |
Дополнительные сведения о функциях поиска
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;ЛОЖЬ).
-
Первый аргумент (часть, необходимая для работы функции) — это искомое значение. Это может быть ссылка на ячейку, например B2, или значение, например "кузьмина" или 21500.
-
Второй аргумент — это диапазон ячеек, который, как вы предполагаете, содержит искомое значение.
Важно: В функции ВПР столбец, содержащий искомое значение или ссылку на ячейку, должен быть крайним левым столбцом в диапазоне.
-
Третий аргумент — это столбец в диапазоне поиска ячеек, содержащий значение, которое нужно найти.
Хотя четвертый аргумент не является обязательным, большинство пользователей вводят аргумент ЛОЖЬ (или 0). Почему? Потому что в этом случае функция будет искать точное совпадение. Можно ввести аргумент ИСТИНА или вообще не вводить аргумент, но если точное совпадение не будет найдено, функция вернет наиболее близкое приблизительное совпадение, а большинство людей приблизительное совпадение не устраивает.
Чтобы убедиться в том, что использование приблизительного совпадения может иметь серьезные последствия, предположим, что ищется цена детали с идентификатором 2345768, но вы перепутали две цифры и ввели их в формулу следующим образом: =ВПР(2345678;A1:E7;5). Формула возвращает цену на другую деталь, потому что функция ВПР нашла ближайшее число, меньшее или равное указанному (2345678). Эта ошибка может привести к неправильному выставлению счета клиенту.
Если для аргумента "приблизительное соответствие" указано значение ЛОЖЬ или 0, а точного совпадения нет, вместо неправильного значения формула возвращает в ячейку строку "#Н/Д". Это наилучшее решение. В данном случае "#Н/Д" не означает, что формула введена неправильно (за исключением неправильно введенного номера). Это означает, что номер 2345678 не был найден, потому что вы искали значение 2345768.
В этом примере показано, как работает функция. Если ввести значение в ячейку B2 (первый аргумент), функция ВПР выполняет поиск в ячейках C2:E7 (второй аргумент) и возвращает наиболее близкое приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).
В данном примере четвертый аргумент оставлен пустым, поэтому функция возвращает приблизительное совпадение.
Использование функции ГПР
Разобравшись с функцией ВПР, несложно будет освоить и функцию ГПР. Функция ГПР использует те же аргументы, но выполняет поиск в строках вместо столбцов.
Одновременное использование функций ИНДЕКС и ПОИСКПОЗ
Если вы не хотите ограничиваться поиском в крайнем левом столбце, можно использовать сочетание функций ИНДЕКС и ПОИСКПОЗ. Формула, использующая эти функции вместе, немного сложнее формулы с функцией ВПР, но она открывает больше возможностей. Поэтому некоторые пользователи предпочитают применять сочетание функций ИНДЕКС и ПОИСКПОЗ, а не функцию ВПР.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения "Воронеж" в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Еще о функциях поиска
К началу страницы
support.office.com
