Чем Можно Заменить Функцию Впр в Excel • Дополнительные сведения
Функции ВПР2 ГПР2
Является модифицированной версией стандартной функции ВПР Excel. Она разработана таким образом, чтобы позволять:
- Возвращать для ключа все его совпавшие значения в отличии от встроенной функции, которая возвращает только одно значение;
- Производить поиск по ключу вне зависимости от его расположения в таблице;
- Производить поиск по 2 и более условиям (составной ключ).
В надстройке также имеется функция ГПР2, ее описание аналогично данному, но последняя осуществляет горизонтальный поиск.
Синтаксис: =ВПР2(Ключ; Таблица; Столбец_поиска; [Столбец_Ключа]), где:
- Ключ – обязательный аргумент. Значение либо ссылка на ячейку, содержащую значение для поиска. Когда необходимо определить несколько ключей, они должны быть записаны через символ амперсанда – &;
- Таблица – обязательный аргумент. Диапазон, содержащий искомые значения и ключи;
- Столбец_поиска – обязательный аргумент. Целое число, указывающее на порядковый номер столбца в таблице, из которого необходимо возвратить значение;
- Столбец_Ключа – необязательный аргумент. Значение по умолчанию 1. Целое число, указывающее на порядковый номер столбца в таблице, в котором находится значение ключа. В случаях использования нескольких ключей, номера их столбцов записываются в виде массива (внутри фигурных скобок) через точку с запятой – . Имейте в виду, что порядок указания столбцов должен совпадать с порядком задания ключей.
На приведенном скриншоте функция ВПР2 возвращает для ключей 1 и 4 по два значения, разделенных точкой с запятой. При включенном переносе текста в ячейках, каждое последующее значение начинается с новой строки.
В следующей примере осуществляется поиск значений для составного ключа. Если значение не найдено, то возвращается ошибка #Н/Д.
Для того, чтобы использовать описанные в данной категории функции, скачайте и установите нашу надстройку.
Работа надстройки была успешно протестирована на версиях Excel: 2007, 2010 и 2013. В случае возникновения проблем с ее использованием, сообщайте Администрации сайта.
Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.
Комментарии
Здравствуйте! Скажите, пожалуйста, ВПР2 не работает с именными диапазонами?
=ВПР2($A3&E$1;Таблица1;5;)
Не хочет считать. При этом, когда тут диапазон формата А3:С16 все работает.
Софья, добрый день!
Я так понимаю, что номера столбцов в третьем и четвертом аргументах выходят за пределы таблицы, и функция возвращает ошибку.
В диапазоне А3:С16 3 стоблца, а Ваша функция ссылается на 5 и 4, которые отсутствуют.
Проверил работу функции, именные диапазоны распознаются (за их распознание отвечает Excel).
Подсказки можно получить, если открыть окно функции. При написании в строке функции подсказки не видны. Реализовать это посредством VBA у меня не получилось.
Если снова будут проблемы с функцией, то скиньте Ваш пример на почту, указанную в контактах и укажите версию офиса.
Андрей, спасибо!
Только что проверила с именным диапазоном, все работает! Вероятно, в силу позднего времени в прошлый раз чего-то намудрила.
Добрый день!
Спасибо за ответ.
Возможно мы не поняли друг друга.
Посм.влож.файл.
В D8 при вычислении ВПР2 возникает #Н/Д
Стандартная же ф-ция ВПР в аналогичном случае (яч.D7) возвращает 0
Возможно Вы так задумывали это реализовать.
Но на мой взгляд с точки зрения совместимости со стд.функцией – логично было бы возвращать 0 (ноль)
Спасибо за интересные и нужные дополнения функций Excel.)
Почему не работает впр в excel н д
- Поиск числа : =ВПР(40; A2:B15; 2) — формула будет искать число 40.
- Поиск текста : =ВПР(«яблоки»; A2:B15; 2) — формула будет искать текст «яблоки». Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
- Поиск значения из другой ячейки : =ВПР(C2; A2:B15; 2) — формула будет искать значение в ячейке C2.
- таблица — это два или более столбца данных.
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Лабораторная работа «Функции ВПР и ГПР, операции с диапазонами»
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
33 конкурса для учеников 1–11 классов и дошкольников от проекта «Инфоурок»
Функция ВПР (вертикальный просмотр) незаменима, когда данные из одной таблицы, содержащей какие-то признаки, нужно перенести в другую таблицу, где содержится тот же характерный признак.
Задание 1: имеются 2 таблицы: таблица количества проданных товаров и таблица цен на эти товары, а для решения задачи требуется рассчитать стоимость всех проданных товаров.
1. Создать файл MS Excel, сохранить именем lab_2. 2. Скопировать из Word таблицу 1 Продажа товаров и озаглавить лист, как Продажа.
3. Скопировать таблицу 2 Цены товаров на лист 2 с тем же размещением на листе и озаглавить лист, как Цены.
Порядок работы
Для решения задачи нужно перенести цены проданных товаров из таблицы 2 в пустой столбец «Цена, руб.», создать формулу расчёта стоимости товара, распространить её на все товары и рассчитать полную стоимость проданных товаров, как в рублях, так и в долларах.
1. Создать в ячейке C3 таблицы 1 формулу для перенесения из таблицы 2 цены товара «Ведро», для чего:
− щёлкнуть кнопку f(x) и выбрать функцию ВПР из раздела «Ссылки и массивы»; − в строке окна «Аргументы функции» создать формулу:
где A3 – «Искомое значение» (адрес наименования товара, для которого ищется цена);
Цена!$A$1:$C$20 – «Таблица» (таблица Цены товаров). Для фиксации ценового диапазона необходимо устанавливать знаки доллара;
3 – «Номер столбца» (номер столбца Цена, руб. в таблице Цены товаров);
0 – «Интервальный просмотр» (индекс, указывающий на то, что функция ВПР ищет только точное совпадение). Если точное совпадение не найдено, в ячейку записывается значение ошибки #Н/Д (нет данных).
2. Проанализировать соответствие полученного результата и щёлкнуть ОК. В ячейку C3 должна быть занесена цена товара «Ведро».
3. Распространить формулу, используя маркер заполнения, на все строки таблицы 1 и проверить корректность простановки цены товаров. В случае появления символов #Н/Д проверить соответствие названий товаров в обеих таблицах и наличие цен.
4. Создать в ячейке D3 столбца «Стоимость, руб.» формулу расчёта стоимости проданных вёдер и распространить её с помощью маркера заполнения на все товары.
5. Создать в ячейке D28 формулу расчёта итоговой стоимости проданных товаров. Она составляет 37509 руб.
6. Изменить в ячейке B27 слово «Карандаш» на слово «Карандаши». Отметить, что в столбцах этой строки появились символы ошибки #Н/Д, т. к. возникло несоответствие названий в таблицах 1 и 2.
7. Вернуть правильное название товара, при этом ошибка исчезнет.
8. Изменить в ячейке F20 слово «Карандаш» на слово «Карандаши».
9. Отметить, что ошибка в таблице Продажа товаров возникла во всех строках, где был записан этот товар. Вернуть правильное название товара для исключения ошибки.
10. Установить курсор в ячейку C3 и изменить формулу в строке формул следующим образом:
=ЕСЛИОШИБКА(ВПР(A3;$F$2:$H$20;3;0);ЛОЖЬ)
и распространить её на все ячейки столбца C. В этом случае при имитации ошибки вместо символов ошибки #Н/Д будет возникать слово ЛОЖЬ.
Операции с диапазонами
Для того чтобы умножить или разделить массив чисел на какую-нибудь константу, можно использовать команду Специальная вставка.
Задание 2: Изменить данные в столбце Стоимость таблицы Продажа согласно курсу доллара.
Порядок работы
1. Занести в свободную ячейку листа Продажа число, соответствующее курсу доллара, например: 70.
3. Выделить в таблице Продажа товаров ячейки со значениями стоимости товаров.
4. Нажать правую кнопку мыши и выбрать в окне Специальная вставка операцию «Разделить».
5. Оценить правильность перевода цены товаров из рублей в доллары и уменьшить количество дробных разрядов до двух знаков.
Аналогичным образом с помощью Специальной вставки в диапазонах осуществляются операции сложения и вычитания.
Работа с функцией ГПР и транспонирование
Задание 3: Транспонировать таблицу Продажа фруктов и рассчитать Стоимость на основе данных таблицы Цены фруктов.
Функция ВПР (VLOOKUP) или тайна четвёртого параметра — Формулы рабочего листа — Excel — Каталог статей — Perfect Excel
- Возвращать для ключа все его совпавшие значения в отличии от встроенной функции, которая возвращает только одно значение;
- Производить поиск по ключу вне зависимости от его расположения в таблице;
- Производить поиск по 2 и более условиям (составной ключ).
Минусы : Надо делать дополнительный столбец и потом, возможно, еще и прятать его от пользователя. При изменении числа строк в таблице — допротягивать формулу сцепки на новые строки (хотя это можно упростить применением умной таблицы).
Почему не работает впр в excel н д
Как работает ВПР в Excel читайте в посте “Функция ВПР – чудо экономии времени”. Здесь же вы узнаете, что делать, если ВПР не работает. Так бывает. Скорее всего вы допустили ошибку. Есть четыре наиболее распространенных варианта ошибок:
Вариант 1. ВПР вернула ошибку #Н/Д во всех ячейках
Все будет в порядке, как только вы исправите ошибку:
Например, в одном файле текст “Компьютер AF”, в другом файле “Компьютер—AF”. Если не заметили, разница всего лишь в наличии дефиса во втором случае. Тем не менее, ВПР не считает такое расхождение мелочью и, следовательно, не сработает.
Единственное исключение – допускаются разные регистры. Например, “Компьютер AF” и “компьютер af” не составит проблемы для ВПР.
Идентичность текста удобно проверять функцией =ЯЧЕЙКА=ЯЧЕЙКА. Смотрите пример на рисунке ниже и описание под ним:
- Введите в ячейку D2 знак “=”
- Выделите ячейку B2 с первым значением Планшет DC.
- Опять введите “=”.
- Выделите ячейку C2 со вторым значением.
- Кликните Enter и протяните формулу вниз.
В результате в ячейках отобразится либо “ИСТИНА” (значения совпадают), либо “ЛОЖЬ” (значения не совпадают).
Как видите, не совпадают значения в строке 4: “Компьютер А” и “Компьютер F” (в ячейке D4 видим слово “ЛОЖЬ”).
Использовать функцию можно как в одном, так и в разных файлах.
- Если вы регулярно работаете с этими файлами, поменяйте текст вручную. Единожды потратив на это время, вы значительно сэкономите его в будущем. Если смена текста невозможна, создайте дополнительный столбец, куда введете текст из второго файла.
- Иногда текст не совпадает из-за каких-то наборов знаков, которые повторяются в каждой ячейке. К примеру, в одном из файлов везде добавлен артикул: “010-01583 Компьютер AF”, а в другом файле этого артикула нет “Компьютер AF”. В этом случае текст можно заменить автоматически. Об этом читайте в следующих постах.
- В одном из файлов в тексте содержатся невидимые пробелы. Это хитрая разновидность несовпадения текста. Хитрая, потому что не всегда ее можно заметить. Вот полюбуйтесь:
Как быть? Воспользуйтесь формулой =СЖПРОБЕЛЫ(ЯЧЕЙКА). Эта формула убирает все лишние пробелы. Подробнее – в посте о работе с текстом.
- Выделите весь столбец с числами в виде текста (на рисунке выше диапазон B2:В5).
- Подведите курсор к значку “!” и кликните по появившейся стрелочке.
- Выберите из выпадающего списка “Преобразовать в число” – см. рисунок ниже.
Вариант 2. ВПР подтянула значения только в верхней части таблицы, в нижней — #Н/Д
- Смещение диапазона. Чаще всего это происходит, если данные подтягиваются из одного и того же файла. Например, из Листа 1 на Лист 2 или даже из одного и того же листа. Что происходит? Мы тянем формулу вниз, а вместе с ней тянется и диапазон. Вот так:
- Кликните дважды по ячейке с формулой (С5) – отобразится формула.
- В отобразившейся формуле поместите курсор на В12 и нажмите клавишу F4 на клавиатуре.
- Переместите курсор на С14 в этой же формуле и опять нажмите F4.
- Кликните Enter и формула преобразится, из а) в б)
Значок “$” указывает на то, что диапазон закреплен. Теперь при протягивании формулы вниз, он не будет смещаться.
Вариант 3. ВПР подтягивает только часть значений, остальные – #Н/Д
Если таких ячеек немного, то проще всего исправить это вручную, копируя из одного файл в другой.
Вариант 4. ВПР возвращает ошибку #ССЫЛКА
И еще несколько советов по функции ВПР в Excel:
- При вводе формулы используете точку с запятой, а не запятые.
- Если протягиваете формулу вправо, не забывайте закреплять ссылку в начале формулы, чтобы формула имела вид: =ВПР( $В5 ;$B$12:$С$14;2;ложь). Для этого наведите курсор на В5 (прямо в формуле) и трижды нажмите клавишу F4 на клавиатуре.
- Не используйте аргумент “ИСТИНА” в ВПР, если работаете с текстом. Это приблизительный поиск, в котором будет много ошибок.
9 комментариев к “ ВПР не работает ”
Спасибо за комментарий! Да, конечно, это все совершенно верно для ВПР с аргументом ИСТИНА (или без него). Но поскольку статья является фактически продолжением статьи, в которой рассматривалась исключительно ВПР с аргументом ЛОЖЬ, то данная ошибка не приведена.
Почему не работает ВПР в Excel?
Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте).
Давайте постараемся вместе ответим на вопрос: «Почему функция ВПР не работает?»
В первую очередь, сообщение об ошибке вы увидите, если данные, которое вы ищете, действительно отсутствуют в указанном диапазоне поиска. Здесь мы бессильны.
Все остальные случаи связаны с тем, что мы что-то не учли или сделали неверно.
Неточность при вводе данных.
В этом случае ничего найти не получится, и ВПР возвратит ошибку #Н/Д. Самое простое решение здесь очень часто оказывается самым верным. Быть может, вы просто ошиблись при вводе критерия поиска.
Это вполне может случиться, особенно если вы записываете его прямо в формулу.
Опечатка при вводе функции.
Если вы видите ошибку #ИМЯ?, то это означает, что при записи названия самой функции вы допустили неточность – перепутали или добавили лишнюю букву.
Неверные ссылки.
Проверьте, верно ли вы указали диапазон поиска, а также координаты значения, которое мы будем искать.
Случается, что в формуле вы указали обычные (относительные) ссылки, забыв заменить их на абсолютные ($), а затем произвели какие-то действия с таблицей. К примеру, добавили столбец. Ваши ссылки в формуле теперь будут указывать на неверные координаты.
Важно! При таком изменении ссылок вы можете и не знать, что найдено не то, что нужно. Ведь какое-то значение все же возвращено, хоть и неверное. Поэтому будьте внимательны в использовании относительных и абсолютных ссылок! Рекомендуем по этой причине стараться использовать «умные» таблицы или именованные диапазоны.
Неверно указан параметр «интервальный просмотр».
Включен поиск до первого приблизительного совпадения в отсортированном диапазоне (параметр = ИСТИНА или вовсе опущен), но на самом деле данные не отсортированы.
Столбец поиска не является первым слева столбцом диапазона поиска.
Часто забывают о том, что ВПР ищет совпадающие данные только в первом слева столбце диапазона, который вы указали.
Если ваши просматриваемые данные находятся не в первом, а во втором или другом столбце, то она не сможет найти ни одного совпадения, и вы вновь увидите сообщение #Н/Д.
Несовпадение форматов данных.
Преобразуем число в текст.
Как видите, с виду записи одинаковы, однако в D6 артикул сохранен как число, а в A10 – как текст (на таких ячейках обычно видна пометка — зелёный уголок). Текст не может быть равнозначен числу, поэтому получаем #Н/Д. ВПР не находит значение, хотя оно с виду есть.
Как решить эту проблему со случайным несоответствием в формате записи? Можно использовать функцию ТЕКСТ, которая преобразует любой тип данных в текстовый вид.
Как видно, обычную ссылку на D6 мы заменяем конструкцией
Первый аргумент – это адрес, а второй аргумент означает формат, в который мы будем преобразовывать значение (в нашем случае – текстовый).
Можно поступить и проще — «приклеить» к числу пустую строку при помощи оператора склейки &.
Не работает ВПР в Excel: с чем это связано
Причины и пути решения в Excel
Существует много причин, почему не работает ВПР в Excel и появляется надпись Н / Д. В каждой из ситуаций необходимо индивидуально подходить к решению вопроса с учетом возникшей неисправности.
Наиболее эффективный метод
В ситуации, когда не работает функция ВПР в Excel, проверьте наличие элемента на листе или задействуйте в формуле функцию обработки ошибок, к примеру, =ЕСЛИОШИБКА(ФОРМУЛА();0). В таком случае при появлении сбоев в расчете показывается ноль, а в ином случае — результат формулы. Можно дополнить запись “”, чтобы ничего не показывалось, или внести в скобки какую-либо запись.
Ошибка в типе параметров
Характерная причина, почему не работает ВПР в Excel — нахождение исходных / искомых данных к различным типам. К примеру, если вы используете ВПР в виде числа, а исходные данные сохраняются в качестве текста. Для решения вопроса убедитесь, что типы информации идентично. Для проверки формата сделайте следующее:
Для принудительного внесения изменений нужно изменить формат для всего столбца. Для начала примените требуемое форматирование, а после выберите «Данные», «Текст по столбцам» и «Готово». После этого проверьте, появляется Н Д или нет.
Лишние пробелы
Распространенная причина, почему не работает формула ВПР в Excel, состоит в наличии пробелов. Для их удаления используйте функцию СЖПРОБЕЛЫ.
Ошибки метода поиска совпадения
Следующее объяснение, почему не срабатывает ВПР в Excel и возвращается Н Д — ошибки в применении метода совпадения. По умолчанию у опции ВПР имеется аргумент «интервальный просмотр», который дает команду на поиск точного совпадения даже при отсутствии сортировки данных в таблице.
Для поиска точного совпадения введите для аргумента «интервальный_просмотр» показатель ЛОЖЬ.
При этом учтите, что ИСТИНА, которое дает возможность поиска приблизительного параметра, может вернуть ошибку Н / Д. При использовании опции ПОИСКПОЗ попробуйте поменять параметр аргумента «тип_сопоставления» для указания порядке сортировки таблицы.
Не соответствие числа строк / столбцов заданному диапазону
В ситуации, когда не работает ВПР в Excel и вылетает ошибка Н / Д, сделайте дополнительную проверку. Вам нужно убедиться, что диапазон, в отношении которого ссылается формула, правильный. Как вариант, можно ввести формулу массива в меньшее / большее количество ячеек с учетом ссылки на диапазон.
В ячейке введена надпись Н / Д или Н Д
В Эксель ВПР часто не работает, если пользователь вручную ввел в ячейку параметр #Н / Д или НД (). Для решения проблем его нужно поменять на фактические данные, как только они будут доступны. До этого момента формулы, в которых содержатся ссылки на эти ячейки, не смогут вычислить этот параметр. При этом будет возвращаться ошибка Н Д.
Другие ошибки
Дополнительно стоит выделить и ряд других ситуаций, когда ВПР в Excel по какой-то причине не работает:
- В используемой формуле нет одного или более аргументов. Для исправления проблемы введите все необходимые документы и проверьте, работает опция или нет. Для контроля можно использовать Visual Basic.
- Пользовательская опция недоступна. Для исправления проблемы убедитесь, что документ Excel с пользовательской функцией открыт, а опция работает корректно.
- Макрос имеет функцию, которая возвращает Н Д. Если ВПР не работает по этой причине, для исправления ошибки убедитесь в правдивости аргументов и их нахождении в нужных местах.
- Изменение защищенного файл с опцией ЯЧЕЙКА. Для исправления ситуации, когда ВПР в Excel не работает, жмите на комбинацию Ctrl+Alt+F9.
- Столбец не является первым слева дли поискового диапазона. Для решения проблемы нужно ввести соответствующий параметр и проверить, появляется ли Н Д. Как вариант, можно использовать функции ИНДЕКС и ПОИСКПОК в качестве гибкой альтернативы для ВПР.
- Неправильное форматирование числа. Бывают ситуации, когда цифры указаны в текстовом формате. Это часто происходит при импортировании сведений из внешней базы данных или при вводе апострофа перед числом для сохранения нуля в начале. Для решения проблемы жмите по ошибке и укажите Convert to Number. При появлении Н Д для многих чисел выделите их и жмите правой кнопкой мышки, а после выберите Format Cells и вкладку Число и Числовой.
Выше рассмотренные основные причины, почему не работает ВПР в Excel, а также описаны базовые шаги для устранения ошибки Н Д. Всегда начинайте с проверки правильности ввода параметра, а после переходите к другим вариантам.
Что за функция
В завершение кратко рассмотрим, что это за опция ВПР в Excel, и как она работает. Простыми словами, это опция, позволяющая переставлять данные из одной таблицы в соответствующие параметры другой. Английское название опции звучит как VLOOKUP. Это очень полезная опция, позволяющая сэкономить время и одновременно обработать большое количество параметров.
К примеру, в вас есть две таблицы. Первая — цены и названиями, вторая — заказ на покупку продукции. Осуществлять поиск в первом документе и пытаться вписать цену в заказ трудно. Необходимо, чтобы работа проходила автоматически. Для этого достаточно найти нужное значение в 1-м столбце и вернуть его содержимое из столбца той же строки, где находится название.
В комментариях расскажите, приходилось ли вам пользовался опцией ВПР, и случалась ли ситуация, когда она не работает в Excel, и появляется ошибка Н Д. Отдельно поделитесь, какие варианты решения вопроса можно использовать.
Функция ВПР в Excel: примеры использования для чайников
Принцип работы данной функции заключается в том, что Excel ищет фрагмент в указанной вами области и затем может вернуть любое значение с этой строки. От вас только требуется указать порядковый номер столбца в указанном диапазоне.
Публикуя свою персональную информацию в открытом доступе на нашем сайте вы, даете согласие на обработку персональных данных и самостоятельно несете ответственность за содержание высказываний, мнений и предоставляемых данных. Мы никак не используем, не продаем и не передаем ваши данные третьим лицам.