0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

ВПР сопоставить первые n символов и вернуться к другому столбцу в Excel

ВПР сопоставить первые n символов и вернуться к другому столбцу в Excel

Совет: Попробуйте новую функцию PROSWERX, улучшенную версию функции VLOP, которая работает в обоих направлениях и по умолчанию возвращает точные совпадения, что делает ее проще и удобнее в использовании, чем ее предшественница.

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

Простейшая функция UPP — это

=B.SURVEY (что вы нашли, где это искать; номер столбца в поле, содержащем возвращаемое значение, возвращает приблизительное или точное совпадение, задается как 1/ISTIN или 0/false).

Браузер не поддерживает видео.

Совет: Секрет функции БПФ заключается в том, чтобы расположить данные так, чтобы желаемое значение (Fruit) оказалось слева от найденного возвращаемого значения (Quantity).

Вычислите значение с помощью функции БПФ.

(просмотр_значения, таблица, номер_столбца, интервал_просмотра)

=ВLOOKUP(A2;’Сведения о клиенте’! A:F;3;ЛОЖЬ)

Наименование аргумента

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

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

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

The_value может быть как значением, так и ссылкой на ячейку.

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

При использовании функции БПФ искомое_значение и возвращаемое значение будут искаться в диапазоне ячеек. Вместо ссылок на ячейки используйте имя диапазона или таблицы.

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

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

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

Интервальный_просмотр (факультативный)

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

Примерное соответствие: 1/ISTIN предполагает, что первый столбец таблицы отсортирован по алфавиту или числам, а затем ищет ближайшее значение. Это метод по умолчанию, если не указан другой метод. Например, =VCP(90;A1:B100;2;TRUE).

Точное совпадение: 0/False найдет точное значение в первом столбце. Например, =VCP(«Кузнецов»;A1:B100;2;False).

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

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

Значение, которое необходимо найти, или значение, которое вы ищете.

Определение диапазона, в котором находится искомое значение. Важно помнить, что искомое значение всегда должно находиться в первом столбце диапазона, чтобы функция БПФ работала. В данном случае, если искомое значение находится в ячейке C2, то диапазон должен начинаться с C.

Столбец, содержащий возвращаемое значение в пределах диапазона. Например, в случае B2:D11 следует считать B первым столбцом, C — вторым и т.д.

Читайте так же:
Как быстро добавить $ в формулы Excel?

Если вас устраивает приблизительное соответствие, вы можете указать TRUE, но если требуется точное соответствие, вы можете указать FALSE. Значение по умолчанию True всегда возвращается, когда вы ничего не указываете, что является приблизительным совпадением.

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

Используя метод BLOOKUP, вы должны указать значения для поиска, диапазон, содержащий эти значения, количество столбцов, содержащих возвращаемое значение, а также приближение (TRUE) или точное совпадение (FALSE).

Примеры

Ниже приведены примеры использования функции VPR.

Пример 1

=ВЛОП (B3;B2:E7;2;ЛОЖЬ)

ВРОТ.В.В.ищет Шрифтана в первом столбце (столбце B) в table_array B2:E7 и возвращает Olivier из второго столбца (столбца C) table_array. False возвращает точное совпадение.

Пример 2

=ВКП (102;A2:C7;2;ЛОЖЬ)

ВРОТ.В.В.ищет точное совпадение (ЛОЖЬ) фамилии 102 (lookup_value) во втором столбце (столбец B) в диапазоне A2:C7 и возвращает fontana.

Пример 3

=ЕСЛИ(ВЛП(103;A1:E7;2;ЛОЖЬ)="Souse";"Located";"Не найдено")

ЕСЛИ проверяет, возвращает ли Суза фамилию сотрудника в качестве фамилии сотрудника с именем 103 (lookup_value) в A1:E7 (table_array). Поскольку фамилия, соответствующая 103, является Лилем, условие ЕСЛИ ложно и не найдено.

Пример 4

=INT(YEARFRAC(ДАТА(2014;6;30);ВLOOKUP(105;A2:E7;5;ФЛАЖКИ);1))

ВЗ ПРОСМОТР ищет дату рождения сотрудника, соответствующую 109 (lookup_value) в диапазоне A2:E7 (table_array), и возвращает 04.03.1955. После этого преобразуется даты рождения с 06.02.2014 и возвращается значение, которое затем преобразуется с помощью iny в значение 59.

Пример 5

ЕСЛИ(ЕНА(ВЛП(105;A2:E7;2; FLASE))=ИСТИНА;"Сотрудник не найден";ВЗ ПРОСМОТР(105;A2:E7;2;ЛОЖЬ))

ЕСЛИ проверяет, возвращает ли значение фамилии в столбце B для 105 (lookup_value). Если ВЛОК находит фамилию, то если отображается фамилия, в противном случае если возвращает не найден сотрудник. IsNA позволяет убедиться, что если #N/A, ошибка заменяется не найденной ошибкой, а не #N/A.



В этом примере возвращается значение "Бурк", которое является фамилией, соответствующей 105.

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

В этом примере столбцы A-F и H содержат значения или формулы, которые используют только значения на данном сайте; остальные столбцы получены из других таблиц с помощью функции INSERT и значений в столбцах A (код клиента) и B (прокси).

Скопируйте таблицу с общими полями в новую таблицу и присвойте ей имя.

Чтобы открыть диалоговое окно Управление отношениями, > в > управления отношениями нажмите кнопку Data > Data Tools (Управление отношениями).

Для каждой из приведенных выше ссылок обратите внимание на следующее

Поле, которое связывает таблицы (в скобки в диалоговом окне). Это первый lookup_value для формулы ВЛКП.

Определите связанную таблицу поиска по ее имени. Первой таблицей_массива в формуле VLIO является.

Поле (столбец) подтаблицы, связанное с данными, которые будут включены в новый столбец. Эта информация не отображается в диалоговом окне Управление подключениями. Чтобы узнать, какое поле должно быть извлечено, необходимо посмотреть на связанную таблицу извлечения. Обратите внимание, что номер столбца (A=1) — это col_index_num формулы.

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

В нашем примере в столбце G для получения данных «Ставка выставления счета» из четвертого столбца(col_index_num = 4) из таблицы «Доверенности» используется столбец «Доверенность» lookup_value(table_array) с формулой =ВЛП([@Attorney];tbl_Attorneys;4;ЛОЖЬ).

В формуле можно также использовать ссылку на ячейку и ссылку на диапазон. В нашем примере это будет =VLVP(A2; ‘Lawyers’ ! A:D,4;False).

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

Читайте так же:
Как быстро суммировать значения без скрытых столбцов в Excel?

Возможны мотивы

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

Пока аргумент interval_view равен TRUE или не указан, первый столбец должен быть отсортирован по алфавиту или числам. Если первый столбец не отсортирован, возвращаемое значение может быть неожиданным. Чтобы найти точное совпадение, отсортируйте первый столбец или используйте FALSE.

Если аргумент interval_view равен TRUE и значение аргумента seek_value меньше наименьшего значения в первом столбце таблицы, будет возвращено значение ошибки #N/D.

В этом случае код ошибки #N/D означает, что точное число не удалось найти, если interval_view — FALSE.

Информацию об устранении ошибок #H/D в функции БПФ можно найти на странице Исправление ошибок #H/D БПФ.

#Отправляйте! Я Боксен

В случае, если number_column больше, чем количество столбцов в таблице, сообщение об ошибке будет выглядеть как #RELATED!

Как исправить ошибки #SUBCURRENT! в функции FTP, смотрите в статье Исправление ошибок #SUBCURRENT!

Если аргумент таблицы меньше 1, появится сообщение об ошибке #HOW. появится.

Свяжитесь с нами для получения дополнительной информации об исправлении ошибок #NUT! в функции ВПР, см. статью Исправление ошибок #NUT! в функции ВПР

Значение ошибки #NAME? чаще всего появляется, если в форме отсутствуют инвертированные запятые. При поиске имени сотрудника убедитесь, что имя в формуле заключено в инвертированные запятые. Например, в =VPR(«Иванов»;B2:E7;2;False) имя должно быть в формате «Иванов» и никак иначе.

Дополнительную информацию см. в статье Исправление ошибки IMF.

# Ошибки PASS! в мобильных телефонах.

Этот конкретный #SPILL! обычно означает, что формула использует неявное пересечение для искомого значения и использует весь столбец в качестве ссылки. Например, =VCP(A:A;A:C;2;FALSE). Эту проблему можно решить, привязав ссылку на подстроку к оператору @: =ВКП(@A:A,A:C;2;FALSE). Однако можно использовать традиционный метод VLKP и ссылаться на одну ячейку, а не на весь столбец: =BACKAGE( A2;A:C;2;FALSE).

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

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

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

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

Отсортировать первый столбец

Аргумент interval_view имеет значение TRUE, если вы хотите отсортировать первый столбец.

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

Если значение аргумента interval_view равно FALSE и искомое значение является текстом, в аргументе искомого значения можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому отдельному символу. Звездочка обозначает любую последовательность символов. Если требуется вопросительный знак или звездочка, используйте тильду (

Читайте так же:
Как автоматически фильтровать строки на основе значения ячейки в Excel?

) перед необходимым символом.

Например, =VLP(«Font? «;B2:E7;2;False) будет искать все вхождения Fontana с последней буквой, которая может быть различной.

Убедитесь, что все данные являются точными.

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

Для получения точных результатов попробуйте воспользоваться функциями PECSIMV или SJPROBLE.

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

Кроме того, вы всегда можете задать вопрос в сообществе Excel Tech Community или разместить вопрос в сообществе «Ответы».

ВПР сопоставить первые n символов и вернуться к другому столбцу в Excel

В следующей статье рассматриваются наиболее распространенные причины неправильных результатов ФУНКЦИИ VLP, а также как использовать функции INDEX и OPOS.

Обратитесь к краткому справочному руководству: советы по устранению неисправностей для #NA в распечатываемом PDF-файле. Вы можете поделиться PDF-файлом с другими или распечатать его для справки.

Проблема: искомое значение не находится в первом столбце аргумента таблица

Одним из ограничений функции VLP является то, что она может искать значения только в крайних левых столбцах таблицы. Если искомое значение отсутствует в первом столбце таблицы, отображается сообщение #N/A.

В следующей таблице должно быть указано, сколько капусты было продано.

Ошибка #Н/Д в функции ВПР: искомое значение не находится в первом столбце массива таблицы

Ошибка #N/A; запрашиваемый аргумент «Ест» появляется во втором столбце (Продукты) аргумента table_array A2:C10. Excel ищет его в столбце A, а не B.

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

Вместо нее можно использовать индекс или СОВПАДЕНИЕ

Функции index и SELECT являются хорошими вариантами для многих случаев, когда функции VLIO8 не удовлетворяют вашим потребностям. Основное преимущество индекса/MATCH заключается в том, что вы можете искать значения в столбце в любом месте сводной таблицы. Индекс возвращает значение из указанной таблицы или диапазона на основе его позиции. Он возвращает относительное положение значения в таблице или диапазоне. Чтобы найти значение в таблице или матрице, указав относительное положение значения в таблице или матрице, используйте функции ИНДЕКС и НАЙТИ В ФОРМУЛЕ.

Читайте так же:
Как быстро отсортировать IP-адрес от меньшего к большему в Excel?

Ниже перечислены несколько преимуществ использования индекса и CONNECT вместо ПРОСЕКУТОРА VL:

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

С помощью функций INDEX и MATCH можно указать либо строку, либо столбец матрицы, либо и то, и другое. Это означает, что поиск значений можно вести как по вертикали, так и по горизонтали.

Функции ИНДЕКС и ПОИСК можно использовать для поиска значений в любом столбце. В отличие от VROT.V.V., где вы можете искать значения только в первом столбце таблицы, INDEX и SEARCH работают, если искомые значения находятся в первом столбце, последнем столбце или где-то между ними.

Функции Index и MATCH позволяют динамически ссылаться на столбец, содержащий возвращаемое значение. Это означает, что вы можете добавлять столбцы в таблицу, не нарушая индекс и MATCH. С другой стороны, когда вы добавляете столбец в таблицу, VLP ломается, поскольку создается статическая ссылка на таблицу.

M ATCH и Index обеспечивают большую гибкость при подборе. Они могут найти точное совпадение или значение, которое больше или меньше запрашиваемого. ВПР находит только ближайшее (по умолчанию) или точное значение. Кроме того, функция VPR предполагает, что первый столбец таблицы отсортирован по алфавиту, и возвращает первое ближайшее совпадение, поэтому вы можете получить не те данные, которые ожидали.

Синтаксис

Вы должны использовать аргумент массива или ссылки функции INDEX и вложенный в нее синтаксис функции HINT FUNCTION для создания синтаксиса функции INDEX или HINT. Это форма:

=INDEX(массив или ссылка ; SEARCH(значение_поиска;массив;[тип_соответствия])

В качестве примера замените IN PROSWER в приведенном выше примере на индекс или MATCH. В этом случае синтаксис будет выглядеть следующим образом:

=ИНДЕКС(C2:C10;ПОИСКПОЗ(B13;B2:B10;0))

В данном случае INDEX(возвращает первое соответствующее значение для C2:C10, которое является результатом функции Find(Olga, которая находится в массиве B2:B10)) получает значение, соответствующее значению Olga.

Читайте так же:
Как автоматически увеличивать значение ячейки после каждой печати?

Функции ИНДЕКС и ПОИСКПОЗ можно использовать вместо функции ВПР

C2:C10 ищут в формуле первое значение, соответствующее значению Капуста (B7) (100), и возвращаются.

Проблема: не найдено точное совпадение

Если range_lookup равен false и в данных не найдено точного совпадения, возвращается #N/A.

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

В качестве альтернативы можно использовать функцию CLEAN или TRIM для очистки данных в строках и колонках.

Проблема: искомое значение меньше, чем наименьшее значение в массиве

Если range_lookup установлен в TRUE и искомое значение меньше, чем наименьшее значение в массиве, вы увидите #N/A. Функция ищет приблизительное совпадение в массиве и возвращает ближайшее значение, которое меньше искомого.

Этот пример показывает, что вы ищете значение 100, но в диапазоне B2:C10 нет значений меньше 100, поэтому происходит ошибка.

Ошибка #Н/Д в функции ВПР, если искомое значение меньше, чем наименьшее значение в массиве

Скорректируйте значение, которое вы ищете.

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

Проблема: столбец подстановки не отсортирован в порядке возрастания

Range_lookup должен быть true, если столбец в промежуточном итоге не отсортирован в порядке возрастания (A-Z). Это приведет к ошибке #N/A.

Используйте функцию FFT для поиска точных совпадений. Для этого установите аргумент range_search в FALSE. При этом сортировка не требуется.

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

Проблема: значение является большим числом с плавающей запятой

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

Решение. Округлите числа до 5 десятичных знаков с помощью функции ОКРУГЛ.

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

Вы всегда можете задать вопрос в техническом сообществе Excel или попросить помощи в сообществе «Ответы».

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector