Letysite.ru

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

Excel впр ссылка

Использование функции ВПР в программе Excel

Во время работы в Эксель нередко требуется перенести или скопировать определенную информацию из одной таблицы в другую. Выполнить подобную процедуру, конечно же, можно вручную, когда речь идет о небольших объемах данных. Но что делать, если нужно обработать большие массивы данных? В программе Microsoft Excel на этот случай предусмотрена специальная функция ВПР, которая автоматически все сделает в считанные секунды. Давайте посмотрим, как это работает.

Описание функции ВПР

ВПР – это аббревиатура, которая расшифровывается как “функция вертикального просмотра”. Английское название функции – VLOOKUP.

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

Применение функции ВПР на практике

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

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

Порядок действий в данном случае следующий:

  1. Щелкаем по самой верхней ячейке столбца, значения которого мы хотим заполнить (в нашем случае – это C2). После этого нажимаем на кнопку “Вставить функцию” (fx) слева от строки формул.
  2. В окне вставки функции нам нужна категория “Ссылки и массивы”, в которой выбираем оператор “ВПР” и щелкаем OK.
  3. Теперь предстоит правильно заполнить аргументы функции:
    • в поле “Искомое_значение” указываем адрес ячейки в основной таблице, по значению которой будет производиться поиск соответствия во второй таблице с ценами. Координаты можно прописать вручную, либо, находясь курсивом в поле для ввода информации просто кликнуть в самой таблице по нужной ячейке.
    • переходим к аргументу “Таблица”. Здесь мы указываем координаты таблицы (или ее отдельной части), в которой будет выполняться поиск искомого значения. При этом важно, чтобы первый столбец указанного диапазона содержал именно те данные, по которым будет осуществляться поиск и сопоставление значений (в нашем случае – это наименования позиций). И, конечно же, в указанные координаты должны попадать ячейки с информацией, которая будет “подтягиваться” в основную таблицу (в нашем случае – это цены).
      Примечание: Таблица может располагаться как на том же листе, что и основная, так и на других листах книги.
    • Чтобы координаты, указанные в аргументе “Таблица” не сместились при возможных дальнейших корректировках данных, делаем их абсолютными, так как по умолчанию они являются относительными. Для этого выполняем выделение всей ссылки в поле и нажимаем кнопку F4. В результате перед всеми обозначениями строк и столбцов будут добавлены символы “$”.
    • в поле аргумента “Номер_столбца” указываем порядковый номер столбца, значения которого нужно вставить в основную таблицу при совпадении искомого значения. В нашем случае это столбец с ценами, который занимает вторую позицию в указанной выше области (аргумент “Таблица”).
    • в значении аргумента “Интервальный_просмотр” можно указать два значения:
      • ЛОЖЬ (0) – результат будет выводиться только в случае точного совпадения;
      • ИСТИНА (1) – будут выводиться результаты по приближенным совпадениям.
      • мы выбираем первый вариант, так как нам важна предельная точность.
    • Когда все готово, нажимаем OK.
  4. В выбранной ячейку, куда мы вставили функцию, автоматически вставилась требуемая цена.Причем, если мы изменим значение во второй таблице с ценами, так как данные взаимосвязаны посредством функции, то и в основной таблице произойдут соответствующие изменения.
  5. Чтобы автоматически заполнить аналогичными данными другие ячейки столбца, воспользуемся Маркером заполнения. Для этого наводим курсор мыши на нижний правый угол ячейки с результатом, когда появится черный плюсик, зажав левую кнопку мыши тянем его вниз до конца таблицы или до той ячейки, которую нужно заполнить.
  6. В итоге нам удалось получить в основной таблице все данные по ценам, а также посчитать итоговые суммы по продажам, что и требовалось сделать.

Заключение

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

Как работать с функцией ВПР в Эксель: инструкция для новичков

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

Использование ВПР в программе Excel

Для того, чтобы наглядно разобраться как работает функция ВПР в Excel: поможет пошаговая инструкция на конкретном примере.

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

Первая – это список предметов, единицы их измерения и количество.

Вторая – содержит тот же список, но в ней ещё есть цена за 1 штуку.

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

Этапы работы (инструкция):

  1. Для начала в первую Excel таблицу добавляются два столбца: «Цена за 1 шт.» и «Общая сумма».
  2. Отметить верхнее поле в новом.
  3. Выбрать раздел формулы, и нажать «Вставить функцию».
  4. Из предложенных категорий Excel отметить «Ссылки и массивы».
  5. Найти ВПР, и нажать «ОК».
  6. Заполнить открывшееся окно «Аргументы».

– это товары из первой таблицы, которые необходимо будет определить во второй. Их значение выставляется таким образом: X: Y, где Х – это адрес первой ячейки столбика с товарами, а Y – последней. В рассматриваемой это А2 и А5.

– в этом поле будет стоимость из второго листа с данными. Чтобы её проставить следует кликнуть по строке, затем перейти на страницу с суммой, и выделить нужное (А2 – В5).

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

Фиксирование информации производится путём нажатия горячей клавиши F4, на выделенной строке. Если всё сделано правильно там же появится значок $.

Читать еще:  Создание диаграммы ганта в excel

Номер — это строка в которой должна быть информация о том, что будет переноситься из другой таблицы. В рассматриваемом случае – это второй столбец (2).

Интервальный просмотр – логическое значение Excel, где точно это ЛОЖЬ, а приближённо – ИСТИНА. Если пользователю нужны точные, он должен написать «ЛОЖЬ».

В конечном итоге, окно «Аргументы» выглядит так:

Нужное значение появится в ячейке. Чтобы опция сработала на все товары, достаточно растянуть её.

Теперь, чтобы сосчитать общую стоимость предмета, достаточно вставить соответствующую формулу в ячейку Е2, и также растянуть её на все продукты. Конец инструкции.

Как сравнить две таблицы: пошаговая инструкция для «чайников»

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

Допустим, что к тому же администратору торгового центра снова привезли товар, но предупредили, что стоимость у некоторых предметов изменились. Как сравнить две таблицы функцией ВПР в Эксель?

Делается это в несколько шагов:

  1. Открыть первую со старой информацией.
  2. Добавить дополнительный столбик для новых данных «Новая стоимость».
  3. Выделить первое пустое поле в созданном столбце (С2).
  4. Выбрать раздел «ВПР Формулы» и «Вставить функцию».
  5. Найти категорию Excel «Ссылки и массивы».
  6. Выбрать ВПР.
  7. Задать «Аргументы».

– то, что важно будет найти во второй таблице. Чтобы значение появилось в строке, нужно выделить первый столбик с наименованиями товаров (А2 – А5).

– с чем программа будет сравнивать. Для заполнения нужно перейти на вторую страницу и отметить два наименования – предметы и цена (А2 – В5). И зафиксировать результат кнопкой F4.

Номер столбца – второй, так как именно стоимость переносится в новую.

Интервальный просмотр – ЛОЖЬ.

Заполненное окно выглядит так:

После нажатия кнопки «ОК» новые значения появятся в таблице. Чтобы ценовая информация появилась у всех предметов нужно растянуть ячейку.

Теперь администратор может работать с данными стандартными функциями Excel, благодаря инструкции.

Поиск с помощью ВПР по нескольким условиям

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

Итак, имеется документ, в котором обозначены: компании, товары и цены.

Нужно найти цену на конкретный товар – гелевая ручка. Но так как каталог может быть огромным, а гелевые ручки быть не у одной компании, поиск стоимости в Эксель лучше проводить через ВПР с несколькими условиями: название компании и предмета.

Чтобы осуществить поиск следует:

  1. Создать слева новый столбец с объединёнными данными (название компании и товара).

Делается это просто:

  • выделить крайнюю левую ячейку (А1);
  • щёлкнуть ПКМ и выбрать «Вставить»;
  • отметить добавление столбца и нажать «ОК».

  1. Внести данные в новый столбец. Для этого нужно нажать на пустое поле А2, ввести формулу объединения (=B2&C2) и нажать кнопку Enter. Чтобы продлить список достаточно растянуть ячейку.

  2. Нажать на любое свободное место и самостоятельно ввести, что нужно найти (ЛасточкаГелевая ручка).
  3. Выбрать ячейку где будет отображен результат и заполнить Аргументы функции.

– что нужно найти (щёлкнуть по введенной — ЛасточкаГелевая ручка – А8).

– где искать нужное значение (выделить ячейки от первой до последней — А2 – D5).

Номер столбца – из какого столбца вывести результат (4).

Интервальный просмотр – ЛОЖЬ.

После нажатия команды «ОК», программа отобразит результат.

Как сделать выпадающий список через функцию ВПР

Чтобы сделать выпадающий список из существующего нужно следовать инструкции:

  1. Выбрать поле, в котором будет сформированы показатели. Например, Е2.
  2. Зайти в раздел «Данные», и выбрать «Проверка данных».
  3. Установить тип данных, как список.

  1. В появившуюся строку «Источник» ввести информацию (выделить с первой до последней ячейки – А2:А5).

Выпадающий список готов.

Теперь с помощью функции ВПР нужно добавить возможность просмотра цены, при выборе товара. Как это работает в Эксель? (Инструкция).

  1. Создать новое поле с названием «Цена».
  2. Вставить аргументы.

– ячейка Excel, в которой находится выпадающий список (Е2).

– выделенный фрагмент с предметами и ценами (А2-В5).

Номер столбца – 2 (в нём находятся цены).

Интервальный просмотр – ЛОЖЬ.

После подтверждения команды можно пользоваться списком и просмотром цены.

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

Исправление ошибки #ССЫЛКА!

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

Ошибка #REF! показывает, что формула ссылается на недопустимую ячейку. Это чаще всего происходит, если ячейки, на которые ссылаются формулы, удаляются или вставляются.

Пример ошибки #ССЫЛКА! из-за удаления столбца

В следующем примере в столбце E используется формула =СУММ(B2;C2;D2).

Если вы удалили столбец B, C или D, это приведет к #REF! ошибку. В этом случае мы будем удалять столбец C (2007 Sales), и формула теперь читает = Sum (B2, #REF!, C2). При использовании явных ссылок на ячейки, разделенных запятыми (в которых вы указываете на каждую ячейку по отдельности) и удалении строки или столбца, на которые указывает ссылка, приложение Excel не сможет решить эту проблему, поэтому возвращает #REF! ошибку. Это основная причина, по которой использование явных ссылок на ячейки в функциях не рекомендуется.

Если вы случайно удалили строки или столбцы, вы можете немедленно нажать кнопку «Отменить» на панели быстрого доступа (или нажать клавиши CTRL+Z), чтобы восстановить их.

Измените формулу таким образом, чтобы она использовала ссылку на диапазон, а не отдельные ячейки, например = Sum (B2: D2). Теперь вы можете удалить любой столбец в диапазоне сумм, и Excel автоматически настроит формулу. Вы также можете использовать = Sum (B2: B5) для суммирования строк.

Пример функции ВПР с неправильными ссылками на диапазоны

В следующем примере функция =ВПР(A8;A2:D5;5;ЛОЖЬ) вернет ошибку #ССЫЛКА!, так как она выполняет поиск возвращаемого значения в столбце 5, но указан диапазон A:D, который содержит только 4 столбца.

Измените диапазон, чтобы он был больше, или уменьшите значение подстановки столбца в соответствии с диапазоном ссылок. = ВПР (A8; a2: «5; ложь») является допустимым диапазоном ссылок, как если бы был = ВПР (A8, a2: Д5, 4, ложь).

Читать еще:  Как вставить столбец справа в excel

Пример функции ИНДЕКС с неправильной ссылкой на строку или столбец

В этом примере формула =ИНДЕКС(B2:E5;5;5) возвращает ошибку #ССЫЛКА!, так как диапазон в ней содержит 4 строки и 4 столбца, а запрашивается значение ячейки в пятой строке и пятом столбце.

Настройте ссылки на строки или столбцы так, чтобы они находящегося в диапазоне подстановки ИНДЕКСов. = Индекс (B2: ячейка, 4, 4) возвращает допустимый результат.

Пример ссылки на закрытую книгу с использованием функции ДВССЫЛ

В следующем примере функция INDIRECT пытается сослаться на закрытую книгу, что приводит к ошибке #ССЫЛКА!.

Откройте книгу, на которую указывает ссылка. Эта же ошибка возникает, если вы указываете на закрытую книгу с динамическим массивом.

Проблемы OLE

Если вы использовали ссылку OLE и она возвращает ошибку #ССЫЛКА!, запустите программу, которая вызывает ссылку.

Примечание. OLE — это технология, которая используется для обмена информацией между приложениями.

Проблемы DDE

Если вы использовали раздел динамического обмена данными (DDE), возвращающий #REF! ошибка, сначала убедитесь, что вы ссылаетесь на правильную тему. Если вы по-прежнему получаете #REF! ошибка Проверьте Параметры центра управления безопасностью для внешнего содержимого, как описано в блоке блокирование или разблокирование внешнего содержимого документов Office.

Примечание. Динамический обмен данными (DDE)— это установленный протокол для обмена данными между программами Microsoft Windows.

Проблемы с макросами

Если макрос вводит в лист функцию, которая ссылается на ячейку над функцией, а ячейка, содержащая эту функцию, находится в строке 1, функция возвращает #REF! из-за отсутствия ячеек над строкой 1. Проверьте функцию, указывающую на то, что аргумент ссылается на недействительную ячейку или диапазон ячеек. Для этого может потребоваться изменение макроса в редакторе Visual Basic (VBE), чтобы учесть эту ситуацию.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Функция ВПР в программе Microsoft Excel

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

Определение функции ВПР

Название функции ВПР расшифровывается, как «функция вертикального просмотра». По-английски её наименование звучит – VLOOKUP. Эта функция ищет данные в левом столбце изучаемого диапазона, а затем возвращает полученное значение в указанную ячейку. Попросту говоря, ВПР позволяет переставлять значения из ячейки одной таблицы, в другую таблицу. Выясним, как пользоваться функцией VLOOKUP в Excel.

Пример использования ВПР

Взглянем, как работает функция ВПР на конкретном примере.

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

    Кликаем по верхней ячейке (C3) в столбце «Цена» в первой таблице. Затем, жмем на значок «Вставить функцию», который расположен перед строкой формул.

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

Так как у нас искомое значение для ячейки C3, это «Картофель», то и выделяем соответствующее значение. Возвращаемся к окну аргументов функции.

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

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

  • В следующей графе «Номер столбца» нам нужно указать номер того столбца, откуда будем выводить значения. Этот столбец располагается в выделенной выше области таблицы. Так как таблица состоит из двух столбцов, а столбец с ценами является вторым, то ставим номер «2».
  • В последней графе «Интервальный просмотр» нам нужно указать значение «0» (ЛОЖЬ) или «1» (ИСТИНА). В первом случае, будут выводиться только точные совпадения, а во втором — наиболее приближенные. Так как наименование продуктов – это текстовые данные, то они не могут быть приближенными, в отличие от числовых данных, поэтому нам нужно поставить значение «0». Далее, жмем на кнопку «OK».
  • Как видим, цена картофеля подтянулась в таблицу из прайс-листа. Чтобы не проделывать такую сложную процедуру с другими товарными наименованиями, просто становимся в нижний правый угол заполненной ячейки, чтобы появился крестик. Проводим этим крестиком до самого низа таблицы.

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

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Функция ВПР в Excel

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

    Формула ВПР

    Функция ВПР предназначена для поиска и подстановки значений из одной таблицы в другую на основании какого-либо признака, объединяющего обе эти таблицы. Находится функция в категории «Ссылки и массивы».

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

    Читать еще:  Как убрать условное форматирование в excel

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

    Функция ВПР имеет четыре аргумента:

    1. Искомое значение, в рассматриваемом примере, это табельный номер, ячейка с табельным номером выделена рамкой синего цвета;
    2. Таблица, в приведенном примере это таблица с табельными номерами и именами, выделена рамкой зеленого цвета;
    3. Номер столбца, в используемой для примера таблице, столбец с именами имеет порядковый номер два;
    4. Интервальный просмотр. Это необязательный аргумент, о нем чуть позже.

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

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

    Ошибки #Н/Д, #ССЫЛКА! и #ЗНАЧ!

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

    Неправильный ввод формулы

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

    Результатом вычисления функции будет ошибка вида #Н/Д.

    В нашем простом примере таблица состоит всего из двух столбцов, на практике же столбцов может быть больше. Важно, чтобы, во-первых, столбец, в котором будет производиться поиск искомых значений был крайним левым, а во-вторых правильно определить номер столбца. Если в таблице два столбца, а при вводе формулы в третьем аргументе указано число, которое больше двух, то результатом вычисления функции будет ошибка типа #ССЫЛКА!, а если меньше единицы, то #ЗНАЧ!

    Число отформатировано как текст

    В первой таблице, в ячейке с адресом «A2» число 2551 отформатировано как текст, а во второй таблице, в ячейке «D3» записано числовое значение 2551. Поскольку число 2551 не равно тексту 2551, функция выдает ошибку #Н/Д.

    При поиске числовых значений, необходимо позаботиться о том, чтобы и в таблице эти значения были также числовыми.

    Различие в написании текстовых значений

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

    В ячейке с адресом «A2» написана буква кириллицы, а в «D3» — буква латиницы, в результате ошибка.

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

    ЛОЖЬ и ИСТИНА

    Аргумент «Интервальный_просмотр» не является обязательным для функции ВПР. Этот аргумент может принимать два значения ЛОЖЬ (если необходимо найти точное совпадение) и ИСТИНА (если необходимо найти приблизительное совпадение).

    Этот момент не всегда понятен пользователям, поэтому требует уточнения, которое проще всего показать на примере.

    В последнем аргументе формулы установлено значение «ИСТИНА», что соответствует приблизительному поиску. Искомым значением является число 2552, но в таблице (той, которая расположена слева) искомое значение отсутствует, точного совпадения нет, поэтому функция ищет ближайшее меньшее число, то есть 2551 и возвращает значение «Иван».

    Если искомое значение – это текст, то при интервальном просмотре, соответствующем значению «ИСТИНА» и при отсутствии точного совпадения, функция будет искать ближайшее меньшее значение по алфавиту.

    Искомое значение – Петр, но в таблице нет значения Петр, поэтому функция ищет ближайшее меньшее значение по алфавиту. Буква «Ф» в алфавите находится после буквы «П», а вот «И» — до буквы «П», поэтому она и является меньшим значением. Соответственно результатом функции будет значение «Иванов».

    В случае, когда необходимо найти неточное совпадение с текстовым значением, необходимо использовать интервальный просмотр «ЛОЖЬ», а к искомому значению подставлять специальные символы совпадения (?-одиночный символ и *-произвольная последовательность символов). Тильда (

    ) ставится в том случае, если необходимо найти сами знаки (?) и (*).

    Несколько условий в ВПР

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

    Ограничения в функции ВПР

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

    VBA-аналог функции ВПР

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

    Аналогично функции ВПР, в диалоговом окне надстройки необходимо задать несколько параметров:

    1. Номер столбца с искомыми значениями;
    2. Таблица;
    3. Номер столбца в таблице;
    4. Условие (тип) поиска;
    5. Номер столбца для вставки результатов.

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

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

    Ссылка на основную публикацию
    Adblock
    detector