Функция VLOOKUP в Power Pivot. Функция vlookup

Функция VLOOKUP (ВПР) - Технослон

Как использовать формулу Vlookup? Почему не работает Vlookup? Ошибки #REF, #Value, #N/A (#ССЫЛКА, #ЗНАЧ, №Н/Д). Для того, чтобы правильно использовать Vlookup нужно идти самым лёгким путём: пошагово выбрать 4 пункта формулы Vlookup и нажать ОК. Эта формула сложная, и с первого раза освоить её никому не удаётся. В каждой задаче свои условия и формула может не сработать по той или иной причине. Как пользоваться формулой Vlookup и как справляться с ошибками и написано в этой статье.

Формула Vlookup — это формула для поиска нужного числа (или значения/текста) из одной таблицы для другой таблицы.

Чтобы использовать формулу Vlookup нужно обязательно иметь следующее:

1) 2 таблицы (минимум) с 1ой одинаковой колонкой (т.е. с колонкой с одними и теми же данными). Если в 1ой таблице (Прайс-лист) цены и названия продуктов, то в 2ой таблице (Ассортимент) идентификаторы продуктов должны иметь колонку с теми же названиями, что и в 1ой таблице.

2) Таблицы должны постоянно быть расположены в одном и том же месте (желательно в общей Excel книге, т.е. в едином файле). Если у вас 1 таблица на рабочем столе, а 2ая на флешке, то, изъяв флешку из компьютера, формула перестанет работать.

3) Знание некоторых нюансов (описаны в случаях с ошибками ниже, например, неверный формат)

Как Использовать Формулу Vlookup

Для начала необходимо для примера найти 2 таблицы. Вот они:

В Таблице № 1 мы видим что колонка «Название Товара» пустая. Самый лёгкий способ, чтобы её заполнить, это скопировать-вставить имена товаров с Таблицы № 2 в эту колонку. Но что если бы эта таблица была длиннее, чем 4 строки? Да и к тому же номера и названия товаров в правой таблице в другом порядке. Для этого и нужна формула VLookup.

ВАЖНО: Если бы вы захотели добавить колонку «Название Товара» в Таблицу № 1 Слева от колонки «# товара», то формула VLookup бы не сработала. Почему? См. п. 1 «Ошибок VLookup и их Причин» (ниже).

Добавляем формулу Vlookup в Таблицу

Стаём в ячейку B3, вводим =Vlookup( и нажимаем значок функции fx (см. картинку ниже). После нажатия видим появившееся окно с четырьмя полями (для ввода аргументов). В каждое поле вводим соответствующее ему значение.

Lookup_value (Искомое значение) = ячейка данных, которые есть как в Таблице № 1 так и в Таблице № 2.
Table_array (Таблица) = Таблица 2, из которой нам нужно взять информацию. А именно, колонку «Название Товара».
Col_index_num (Номер столбца) = № столбца из Таблицы № 2, считается по очередности слева направо, в данном случае 2.
Range_lookup (Интервальный просмотр) = Если здесь ставите 0 (или FALSE или ЛОЖЬ), то получите точные данные. Если здесь ставите 1 (или TRUE или ИСТИНА), получите приблизительные данные.

Итак, мы добавляем следующие данные в следующие поля:

Lookup_value (Искомое значение) = A3 (т.е. число 110 из 1ой колонки «# товара»)
Table_array (Таблица) = $F$2:$G$6 (просто выбрали Таблицу 2 и зафиксировали её значками доллара)
Col_index_num (Номер столбца) = 2 (№ столбца «Название товара» из Таблицы № 2)
Range_lookup (Интервальный просмотр) = 0 (чтобы получить точные данные).

Ошибки Vlookup и их Причины

Виды ошибок формулы VLookup

Колонка искомого значения (1го аргумента Lookup_value в формуле) находится справа от вводимой формулы.

Формула не находит значения, т.к. съехала рамка выбранной таблицы (выбранный диапазон не зафиксирован долларовыми знаками). (ошибка #N/A или #Н/Д)
Формула выбирает данные не из той колонки, хотя выбрано всё верно.
Сравнивая числа [01], [ 1] (пробел слева и единица) и [1] формула считает их разными и не может найти сопоставимые им значения. (ошибка #VALUE или #ЗНАЧ)
После перемещения файла с Таблицой № 2, формула больше не видит Таблицу № 2 и не может ничего найти. (ошибка #REF или #ССЫЛКА)
Для чисел 110 и 111 формула находят одни и те же данные, т.к. считает их похожими.
Только в Excel 2003: Всё сделано правильно, но формула не работает.

Советы и Способы решения проблем с формулой VLookup (соответственно номерам ошибок)

Выбор таблицы (table_array) должен начинаться с колонки, в которой данные из обоих Таблиц (в данном случае это # товара).
Всегда ставьте долларовые значки на диапазон Таблицы, делая ссылку абсолютной.
Номер колонки должен соблюдаться даже тогда, когда какие-то колонки спрятаны (hidden).
Формат значений должен быть одинаковым в обоих таблицах:
- формат должен быть везде числовой (если видите зелёный треугольник в левом верхнем углу, значит формат числа выставлен текстовым — нажмите на него — и формат станет числовым).
- соблюдение нулей (например в случае с числами 01, 02 это не то же самое что 1, 2).
- количество пробелов должно быть одинаковым.
Таблицы должны всегда оставаться в одном и том же месте.
В последнем аргументе всегда надо ставить 0 (или FALSE), чтобы искать точные данные.
Только в Excel 2003: Если Ваши таблицы находятся в разных файлах, необходимо убедиться, что вы сохранили эти файлы хотя бы 1 раз и хотя бы 1 раз закрывали эти файлы. В противном случае формула не будет работать.

[embeddoc url=»http://www.tehnoslon.com.ua/Excel/vlookup-1.xlsx» viewer=»microsoft»] Скачать пример:Скачать Если вас интересует ещё одна интересная формула Excel, то вас может заинтересовать следующая статья: Функция СЧEТЕСЛИ в Excel

www.tehnoslon.com.ua

Функция ВПР - ONLYOFFICE

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

Синтаксис функции ВПР:

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

где искомое_значение - это значение, которое необходимо найти, таблица - это два или более столбца с данными, отсортированными в порядке возрастания, номер_столбца - это номер столбца в таблице, интервальный_просмотр - это логическое значение ИСТИНА или ЛОЖЬ,

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

Примечание: если значение аргумента номер столбца меньше 1, функция возвращает ошибку #ЗНАЧ!. Если значение аргумента номер столбца больше, чем количество столбцов в таблице, функция возвращает ошибку #ССЫЛКА!. Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, но точное соответствие не найдено, функция возвращает ошибку #Н/Д.

Чтобы применить функцию ВПР,

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

Результат будет отображен в выбранной ячейке.

Функция ВПР

Вернуться на предыдущую страницу

helpcenter.onlyoffice.com

Левый ВПР

Необходимое предисловие

Если вы раньше не работали с функцией ВПР (VLOOKUP), то много потеряли очень рекомендую сначала почитать вот эту статью и посмотреть в ней видеоурок.

Проблема

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

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

Стоимость по коду заказа найти легко - обычный ВПР тут поможет на раз-два. А вот как найти название товара по коду? На тренингах этот вопрос я чаще всего слышу в формулировке "а как сделать левый ВПР"?

Давайте разберем несколько способов.

Способ 1. Лобовая атака

Если следовать принципу Оккама и не усложнять без надобности, то можно просто скопировать нужный столбец правее (или сделать его ссылками) и использовать обычный ВПР:

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

Способ 2. Виртуальная перестановка столбцов функцией ВЫБОР

Если переставить местами столбцы на листе нельзя, то это можно сделать виртуально, т.е. "на лету" прямо в самой формуле. Для этого нам потребуется функция ВЫБОР (CHOOSE). Основное ее предназначение – выбирать нужный элемент из списка по заданному номеру. Ее, например, можно использовать для замены номера дня недели на его текстовый аналог:

Ничего сверхъестественного, на первый взгляд, но тут есть пара хитрых моментов.

Во-первых, вместо текстовых названий выбираемых элементов списка ("пн", "вт" и т.д.) можно использовать адреса диапазонов. И тогда функция вернет ссылку на выбранный диапазон. Так, например, формула:

=ВЫБОР(2; A1:A10; D1:D10; B1:B10)

… выдаст на выходе ссылку на второй указанный диапазон (D1:D10).

Во-вторых, вместо простого одиночного номера извлекаемого элемента в первом аргументе функции ВЫБОР можно задать массив констант в фигурных скобках, например, так:

=ВЫБОР({1;2}; A1:A10; D1:D10; B1:B10)

Тогда на выходе мы получим два первых диапазона (A1:A10 и D1:D10), склеенных в единое целое.

И вот теперь все это можно вложить внутрь нашей ВПР, чтобы реализовать «левый поиск»:

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

Минусы такого способа - это скорость (примерно в 5-7 раз медленнее обычного ВПР) и некоторая непривычность для коллег (а может это даже плюс!)

Способ 3. Связка функций ИНДЕКС и ПОИСКПОЗ

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

Функция ПОИСКПОЗ ищет заданное значение (С2, т.е. код нужного нам заказа) в одномерном диапазоне (столбце кодов в таблице C10:C25) и выдает в качестве результата порядковый номер ячейки, где нашла искомое - в нашем случае это будет число 4, т.к. код нужного нам заказа четвертый в таблице.

А затем в дело вступает функция ИНДЕКС, которая умеет извлекать данные из вертикального массива-столбца (названия товаров в B10:B25) по порядковому номеру (который предварительно нашла ПОИСКПОЗ). Таким образом, ИНДЕКС выдаст нам содержимое четвертой ячейки из столбца Товар, что и требовалось.

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

Похожий пример (с видео) я разбирал ранее вот в этой статье. А уж про функцию ИНДЕКС можно говорить совсем долго :)

Способ 4. Функция СУММЕСЛИ(МН)

Если нужно извлечь из таблицы именно число (допустим, объем в литрах), то иногда проще использовать для реализации "левого ВПР" функцию выборочного суммирования СУММЕСЛИ (SUMIF) или ее старшую сестру - функцию СУММЕСЛИМН (SUMIFS):

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

Большая статья про функции выборочного подсчета по одному или нескольким условиям есть тут.

Способ 5. Готовая макрофункция из PLEX

Если не пугает использование макросов, то можно использовать готовую пользовательскую функцию VLOOKUPS на Visual Basic, которая входит в состав последней версии моей надстройки PLEX для Microsoft Excel. По сравнению с обычной ВПР она умеет:

искать по нескольким столбцам сразу (до 3)
выдавать результаты из любого столбца (левее или правее - не важно)

выдавать не только первое встретившееся значение, а нужное по порядку
можно задать, что вывести, если ничего не найдено вместо ошибки #Н/Д

У такого способа два минуса: нужно сохранять файл с поддержкой макросов (XLSM) и скорость у любой макрофункции не очень высокая - на больших таблицах может ощутимо подтормаживать.

Но как один из вариантов - пойдет :)

Ссылки по теме

www.planetaexcel.ru

Пишем свой VLOOKUP для того, чтобы не зависеть от стандартного (Excel functions) / Хабр

Преамбула

Как известно, знание функции VLOOKUP в MS EXCEL, достаточно чтобы в Москве стать средним аналитиком. Если человек знаком еще и с PIVOT или например знает как убрать дубликаты из списка — все двери в счастливый офисный мир перед ним настежь раскыты.

У сообщества Хабрахабр, конечно, такие знания могут вызвать лишь улыбку умиления. Работать с данными (если вообще до этого снисходить) допустимо только на олдскульном ANSI T-SQL — 92.

Но иногда суровая реальность заставляет сравнивать массивы данных. При этом, как правило, нет времени на перенос данных в СУБД, либо это просто нецелесообразно. Поэтому предлагаю уважаемому сообществу поделиться друг с другом своими «фишками» для удобной и быстрой обработки данных в EXCEL. Не пропадать же добру…

Амбула, собственно

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

Но вот незадача, иногда нужно в ходе сравнения уточнить какие-то дополнительные параметры. Например соответствий может быть очень много, и все они в общем-то верные, но некоторые все же «верней». То есть нужно в ходе сравнения провести дополнительную валидацию.

В общем случае, такие дополнительные задачи могут быть какие угодно — получить контроль над столь ценной функцией, как VLOOKUP, может оказаться крайне полезным. Предлагаю вашему вниманию код, который выполняет такую двойную проверку. По сути это DOUBLE LOOKUP. Если выполняется соответствие для «базовой» колонки, то функция ищет параметр «уточняющей» колонки и в случае успеха выдает результат, по аналогии со стандартным VLOOKUP.

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

Его величество код

Формат вызова функции: VLOOKUP2my ([Table] таблица, внутри которой ищем соответствия; SearchColumnNum колонка в [Table], в которой ищем "базовое" соответствие; SearchValue значение для поиска "базового" соответствия; ResultColumnNum колонка в [Table], из которой заберем результат в случае успеха поисков; N2 значение для поиска "уточняющего" соответствия; N2col колонка в [Table], в которой ищем "уточняющее" соответствие)Function VLOOKUP2my(Table As Range, SearchColumnNum As Integer, SearchValue As Variant, ResultColumnNum As Integer, N2 As Variant, N2col As Integer) Dim i As Long For i = 1 To Table.Rows.Count If UCase(Table.Cells(i, SearchColumnNum)) = UCase(SearchValue) Then If (UCase(Table.Cells(i, N2col)) = UCase(N2)) Then VLOOKUP2my = Table.Cells(i, ResultColumnNum) Exit For End If VLOOKUP2my = "Second option not exists" End If Next i End Function

habr.com

Функция VLOOKUP в Power Pivot

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

Один из самых популярных функций в формулах Excel — ВПР. Но нельзя использовать функцию ВПР в Power Pivot. Это связано с тем в основном в Power Pivot функции Выражений анализа данных (DAX) не принимают ячейку или диапазон ячеек как ссылка — как ВПР в Microsoft Excel. Только принимать столбец или таблицу как ссылка.

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

Например, допустим, имеется сводной таблицы на листе Excel для анализа данные о продажах в модель данных, и вы хотите разделить на основе региональных данные из области столбцов в таблицу Geography. Область можно просто добавить как поля в сводную таблицу. Необходима без подстановки или формулу. Это простое решение у некоторых требования: столбец подстановки региона должен находиться в связанной таблицеи таблицы Geography не может быть скрытые из списка полей сводной таблицы.

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

Выберите нужное действие

Для поиска значений в связанной таблице используйте функцию RELATED.

Для поиска таблицы со всеми строками, связанными с текущей строкой, используйте функцию RELATEDTABLE.

Для получения значений по условиям фильтра используйте функцию LOOKUPVALUE.

support.office.com