Макрос вместо впр в excel
Обновлено: 07.07.2024
Кто-нибудь подскажет как заменить формулу ВПР на тоже самое только в VBA? И повлияет ли это при работе в сети..
Приведите хоть какой-нибудь маленький примерчик в exel..
дальше постараюсь домучать и примень это самостоятельно
Задаю этот вопрос потому как постоянно имею проблему с этой формулой, может все таки на скриптах это будет на много надежнее..
Скорость вычисления.
10000 ячеек с формулами:
ВПР - меньше 1с
ЗаменаВПР - 5 с
Разницу между функциями вижу только в том, что ВПР вместо пустой ячейки Y выводит 0 - что несколько неверно.
Для того, кто действительно имеет представление о стандартной функции рабочего листа =ВПР(), разница видна даже невооружённым взглядом, а для тех кто не видит, приготовлен небольшой скриншот.Похоже тема так или иначе должна свестись к обсуждению и выявлению достоинств и недостатков функции ВПР,
которые приносят arturhome проблемы, и о нюансах работы которой так хорошо осведомлен pashulka.
arturhome
Код ЗаменаВПР не претендует на абсолютную замену ВПР (хотя бы количеству аргументов) и является заготовкой для вас, если выберете путь VBA.
Может быть всё-таки стоит перестать винить в бедах, которые люди творят собственными руками, весь мир, а обратить свой взор на свои собственные недостатки, например, раз и навсегда разобраться с форматами ячеек, содержащими числовые значения. pashulka, спокойнее! Был задан вопрос - был дан ответ! ВПР - клёвая штука, никто не спорит! Но. Я тоже делал замену для ВПР по просьбе пользователей! Это вынужденные меры! О них речь и идет. Если интересно, могу описать свою ситуацию, а Вы можете предложить решение на ВПР.
Avsha
спасибо попробую применить на практике, возможно это болие действенный вариант
Sokl, Попытка полностью повторить стандартную функцию рабочего листа =ВПР() говорит только о кривизне рук пользователей и не более. И не надо призывать меня к спокойствию, а вопросы бывают разные и не всегда надо решать их подобным способом.pashulka я очень рад за вас что вы достигли своим умом и сообразительностью высот, которые обычный юзер при его дилетантском подходе.. суть проблемы ищет в кривизне программы а не его рук.. Но есть люди которым действительно нужна помощь таких как Вы что бы облегчить существование в этом безумном и постоянно обновляющемся мире ПК.
Если уж речь пошла об этом.. предложите свой вариант решения проблемы.. она у меня действительно есть..
Если у Вас получится заменить стандартную функцию ВПР средствами VBA причем что бы эту функцию не пришлось выставлять а пользоваться только скриптами.. с постоянно увеличивающим диапазоном и обновлением с рабочей книгой по сети..
если есть такой способ плиз помогите .. потому как при работе с более 25000 записями у меня почему то это занимает очень большое время что бы обнавится с рабочей книгой.. второе это исправлять ошибки за чайниками которые каким то образом умудряются испортить все ..поэтому и требуется использование формулы ВПР средствами VBA..
может это и не решит все проблемы но может какую то часть
не большой примерчик.. но думаю что есть другой способ.. потому как есть не большие нюансы..вствлять нужно не всю строку а определенные части и в нужном месте. нужно что бы рабочая книга была все время открыта вобщем я понимаю что ВПР это делает как нельзя лучше . но она тоже не совершенна
Программа предназначена для сравнения и подстановки значений в таблицах Excel.
Если вам надо сравнить 2 таблицы (по одному столбцу, или по нескольким),
и для совпадающих строк скопировать значения выбранных столбцов из одной таблицы в другую,
надстройка «Lookup» поможет сделать это нажатием одной кнопки.
То же самое можно сделать при помощи формулы =ВПР(), но:
- формулы могут тормозить работу с файлом при пересчёте, если объём данных большой (много строк или столбцов)
- если источник данных или файл, в который подставляются данные, каждый раз новый, — требуется время на прописывание или редактирование формул
- если с файлами работают люди, «далёкие» от Excel, - их проще обучить нажимать одну кнопку, чем объяснять им, как прописывать эти формулы
- иногда нужны дополнительные возможности (не учитывать заданные слова и символы при сравнении, выделять цветом изменения, копировать недостающие строки, и т.д.)
В настройках программы можно задать:
- где искать сравниваемые файлы (использовать уже открытый файл, загружать файл по заданному пути, или же выводить диалоговое окно выбора файла)
- с каких листов брать данные (варианты: активный лист, лист с заданным номером или названием)
- какие столбцы сравнивать (можно задать несколько столбцов)
- значения каких столбцов надо копировать в найденные строки (также можно указать несколько столбцов)
- каким цветом подсвечивать совпавшие и ненайденные строки (для каждого из 2 файлов)
- исключаемые при сравнении символы и фразы
Как скачать и протестировать программу
Если не удаётся скачать надстройку, читайте инструкцию про антивирус
Если скачали файл, но он не запускается, читайте почему не появляется панель инструментов
Это полнофункциональная пробная (TRIAL) версия, у вас есть 15 дней бесплатного использования ,
в течение которых вы можете протестировать работу программы.
Этого вполне достаточно, чтобы всё настроить и проверить, используя раздел Справка по программе
Если вам понравится, как работает программа, вы можете Купить лицензию
Лицензия (для постоянного использования) стоит 1400 рублей .
В эту стоимость входит активация на 2 компьютера (вы сможете пользоваться программой и на работе, и дома).
Если нужны будут дополнительные активации, их можно будет в любой момент приобрести по 500 рублей за каждый дополнительный компьютер.
Комментарии
Андрей, напишите мне в Скайп или на почту, попробуем решить проблему.
Ну как бы там ни было, а происходит именно так, после подстановки данных, даже если файл из которого брались данные на момент подстановки был открыт, он просто самопроизвольно закрывается. Я думал это баг системы, переустановил даже винду, но даже после переустановки это продолжается. удалил офис 19 установил офис 16 все равно ничего не меняется, сейчас снес офис 16 установил офис 21 все равно тоже самое.
Здравствуйте, Андрей.
Если файл-источник был закрыт на момент нажатия кнопки «Подставить данные», то он автоматически закрывается после подстановки.
Если же он был открыт, то остаётся открытым.
От версии Office тут ничего не зависит.
Здравствуйте Игорь.
Установил Офис 19, запустил данную надстройку, происходит следующее после подстановки данных, тот файл с которого брались данные просто закрывается, почему так происходит понять не могу. Раньше стоял Офис 16 такого не было.
Здравствуйте, Анна.
Увы, такая возможность в программе не предусмотрена.
Добрый день ! Очень нужна данная программа для работы, подставлять значения из другого файла в один, столбцы одинаковые в обоих файлах, но подставлять значения необходимо только в пустые ячейки. Есть ли готовое решение ?
Скачал для пробы файл, не пойму как сравнить два файла? Нужно сравнить все столбцы и строки одного файла, со всеми столбцами и строками другого файла. Сверка с поставщиками.
Спасибо! Все получилось.
Просто активируйте заново, - всё получится.
Здравствуйте! Компьютер остался старый, просто была переустановлена винда.
Здравствуйте, Евгений.
Если компьютер новый, - то только докупать активации (по 500 руб за каждый дополнительный комп). Перенос активаций с компа на комп не предусмотрен.
Здравствуйте! Переустановил компьютер, как активировать Ваши приложения на новом компьютере. Пишет что две лицензии израсходованы.
Здравствуйте, Константин
Поищите решения для нечеткого поиска в эксель.
Например, есть бесплатная надстройка Fuzzy Lookup от Майкрософт.
Я же в своей программе подобного делать не буду, - с нечетким поиском куча проблем всяких, я устану объяснять покупателям, почему здесь нашлось не то, а здесь не нашлось.
Здравствуйте, Александр.
С чего бы это программе перестать работать?
Если подстановка не выполняется, — значит, что-то не так настроено.
Жаль, но похоже перестала работать.
Стандартная процедура подтягивания данных из столбца одной таблицы по значению другого столбца в другую таблицу не работает. Диалоговое окно проскакивает, но подстановка не происходит.
Очень жаль, прекрасная была программа
Напишите в скайп, обсудим нюансы и стоимость доработки
Здравствуйте.
Напишите мне на почту более подробно, с примером файлов и пояснением, что куда подставляется, и что как должно работать.
Автор, доработайте свое чудо пожалуйста. Купил, пользуюсь, все ок. Но есть критичные требования
1. При подстановке в другую таблицу есть строки, которые не задействованы были. Так вот, в них нужен фильтр, что бы распределить какие значения меняем, какие нет.
По умолчанию у Вас можно изменить только все сразу (которые не совпали). Но если мы работаем с ценами или остатками одного бренда в большой таблице с разными брендами, то программа поменяет у всех.. а это как раз и не надо
Здравствуйте.
Напишите на почту, скиньте файлы и скриншоты настроек программы, - проверю у себя.
Добрый день.
Запускаю программу, но через 3 секунды, доходя до 18% , программа не отвечает.
Кол-во строк в файле, из которого берем значения, максимальное (1.048.576).
В чём может быть проблема и как ее устранить?
к меня вопрос: есть например значение 5*n где n =1. бесконечности. мне нужно например ячейке (5n,1) присвоить какое то значение? как это сделать? заранее благоадарю. Михаил
Жанна, перенос активаций с одного компьютера на другой не предусмотрен.
Вы можете докупить активации для новых компьютеров, по 600 руб за каждый дополнительный компьютер.
Добрый день, как можно сбросить привязку к компьютеру, если я не пользуюсь на нем этой надстройкой, чтобы привязать к другому
Лимита нет, объём данных не критичен.
Скорее всего, программа не видит совпадения (либо настроили что-то не так, либо данные отличаются)
Подскажите, в прайс-листе большой объем данных с формулами, данными, при сравнении к примеру по артикулу и дальнейшей подстановке данных при совпадении, ничего не подставляется. Существует какой-то лимит?
Вроде не делал никаких ограничений.
До 256 столбца точно должно работать.
Игорь, имеется ввиду НЕ НОМЕРА столбцов, а их КОЛИЧЕСТВО (A-AX - 50 столбцов). Я правильно понял, что именно количество не ограничено?
Александр, там столбцы указаны ДЛЯ ПРИМЕРА. Вы можете задать любые номера столбцов.
Научитесь использовать все прикладные инструменты из функционала MS Excel.
Функция ВПР (VLOOKUP) скоро отпразднует юбилей 10 лет, что для временного цикла программного обеспечения сопоставимо с выходом на пенсию.
Перед разработчиками из Microsoft стояла задача подобрать достойную замену, и они не ударили в грязь лицом, выпустив функцию ПРОСМОТРX (XLOOKUP), которая представляет собой не только улучшенную версию ВПР, но ещё и замену ГПР, ИНДЕКС и ПОИСКПОЗ. Если хотите узнать о других полезных функциях Excel, то рекомендуем записаться на бесплатный онлайн-курс «Аналитика в Excel».
Пару слов про функцию ПРОСМОТРX
Прежде чем приступать к работе с функцией ПРОСМОТРХ, нужно учесть 3 момента:
1. Версию Excel: на данный момент, осень 2020 года, ПРОСМОТРX доступен только пользователям Office 365, хотя поговаривают, что эту брешь залатают в Standalone-версии Microsoft Office 2021.
2. Буква Х в названии функции – английская, поэтому велика вероятность ошибки при дотошном вводе; лучше всего использовать автозавершение ввода функций с помощью клавиши Tab.
3. Перебирать аргументы и диапазоны (не только в ПРОСМОТРХ, но и в других функциях) намного быстрее с зажатой клавишей Ctrl, чем с помощью ручного ввода точек с запятой.
Про атрибуты и возможности функции мы поговорим уже в боевых условиях, разбираясь с тем, что же всё-таки умеет делать ПРОСМОТРХ.
Левый ВПР? Я могу орудовать, где угодно!
Наверняка большинство из вас знает, что ВПР выдаёт ошибку, когда нужно забрать данные левее искомой ячейки. Для ПРОСМОТРХ это не проблема, ведь функция считает везде, где только заблагорассудится.
К примеру, на одном листе у нас имеется список сотрудников и годовые оклады, при этом нужно посчитать их бонусы, забрав желаемые данные со второго листа.
Загвоздка заключается в том, что столбец с процентами бонусов находится левее столбца с реквизитами сотрудников, по которым как раз и будет осуществляться поиск.
«Загвоздка?!» – удивляется ПРОСМОТРХ. «Да ещё чего!».
Специально используем «Мастер функций» комбинацией Shift+F3 (или кнопочка Fx слева от строки ввода формул), чтобы показать аргументы.
Искомое значение – это ячейка А2, то есть Gary Miller, с него мы начнём выводить бонусы.
Просматриваемый массив – это столбец с сотрудниками на втором листе, то есть где функция ищет искомое значение. Закрепляем абсолютными ссылками.
Возвращаемый массив – тот самый столбец бонусов, которые мы хотим достать. Тоже забираем его со второго листа, зафиксировав с помощью F4.
Если ничего не найдено – уникальный и необязательный аргумент, позволяющий задать любое значение при неудачных результатах поиска, например, пробел (“ “) или 0, можно пропустить.
Режим сопоставления – классическая указка Excel для поиска точных или приблизительных значения. В данном случае выбираем 0 – точный поиск.
Протягиваем результат в ячейке ниже и сразу обращаем внимание на 0 – это те сотрудники, которых ПРОСМОТРХ не обнаружил в списке.
Кстати, если изначально функция везде выдаёт вам нули, то нужно проверить формат ячеек и проставить там числовой
Чтобы посчитать годовой оклад, можно умножить наши результаты на показатели столбца В, то есть дописываем в формулу *B2:
Протягиваем и получаем максимальный бонус сотрудников.
Иди домой, ГПР!
Раз мы сказали о том, что ПРОСМОТРХ умеет работать в любых направлениях, то и с заменой ГПР, которая считает по строкам, тоже проблем не будет.
Заберём аналогичные данные для бонуса по сотрудникам, как делали ранее, но теперь обратимся к горизонтально развёрнутой табличке на другом листе. Естественно, строка с бонусом находится выше строки с сотрудниками, что в случае с обычным ГПР нам принесло бы ошибку. Сейчас же мы действуем по накатанной схеме:
Указываем ячейку с сотрудником. Затем выбираем диапазон с сотрудниками, в котором ПРОСМОТРХ находит искомое значение. Фиксируем по нажатию F4.
Далее указываем строку с бонусами, тоже фиксируем через F4.
Если ничего не найдено, ставим 0.
Точность поиска – тоже 0, точный поиск.
Получаем идентичный результат, который мы можем с чистой совестью перемножить на годовое жалованье сотрудников. Добавляем к формуле *B2:
В итоге мы должны получить те же самые значения, что и с прошлой таблицей. Тоже перемножаем их на годовой оклад, дабы узнать максимальный бонус.
Кажется, на нём были очки…
Представим, что необходимо достать все реквизиты сотрудника, но мы знаем лишь его фамилию (или какие-то другие отличительные знаки). Попробуем извлечь данные человека по фамилии Willard в отдельной ячейке.
Если мы начнём поиск с ячейки, содержащей одну только фамилию Willard, выберем столбец с сотрудниками в качестве искомого и возвращаемого массива, то… получим ошибку. В идеале наша формула должна выглядеть следующим образом, смотрите:
Второй аргумент – искомый массив, то есть столбец с именами: A2:A19
Третий аргумент – возвращаемый массив, то есть опять столбец с именами: A2:A19
Четвёртый аргумент – пропускаем
Пятый аргумент – крайне важный в нашем случае. Сейчас мы должны выбрать цифру 2, которая учитывает поиск по служебным символам, то есть обращает внимание на введённую звёздочку в первом аргументе
Вот мы и получили James Willard.
«Двойной ПРОСМОТРХ» или «Зависимые выпадающие списки»
Для создания зависимых выпадающих списков, то есть, когда по значению из одного списка открываются соответствующие значения другого, больше не нужно хитрых комбинаций с формулами. Хватит ПРОСМОТРХ и парочки списков.
Сперва добавим в нашу таблицу новый столбец под названием «Итоговый платёж», в котором будет осуществляться суммирование столбцов B и C.
Теперь добавим выпадающий список в ячейку G7.
Вкладка «Данные» – «Проверка данных». Тип данных – список.
В поле «Источник» указываем диапазон с нашими сотрудниками из столбца А. Жмём ОК.
Проверяем. Список работает.
Ниже, в ячейке G8, создаём второй выпадающий список, вот только в поле «Источник» указываем заголовки столбцов из шапки таблицы, то есть протягиваем строку от Годового оклада до Итогового платежа. Нажимаем ОК.
Тоже работает. Едем дальше.
Всё готово для встраивания двойного ПРОСМОТРХ.
Переходим в ячейку G9, хотя вы можете выбрать абсолютно любую ячейку.
Начинаем вводить формулу.
Научитесь использовать все прикладные инструменты из функционала MS Excel.
Мы ищем имя из выпадающего списка выше, то есть ячейка G7 – это первый аргумент.
Затем выбираем диапазон сотрудников – это второй аргумент.
В качестве возвращаемого массива выбираем все остальные столбцы, потому что нам понадобятся все данные. И здесь – внимание!
На место третьего аргумента первой функции ПРОСМОТРХ мы пишем ещё один ПРОСМОТРХ. Уже в нём указываем первым аргументом ячейку F8 (там перебираются заголовки столбцов).
Второй аргумент – это шапка таблицы.
Третий аргумент – уже обозначенный диапазон всех столбцов, кроме сотрудников (разумеется, без шапки).
Обойдёмся без поразительной точности и закрываем формулу.
Готово. Теперь, в зависимости от выбранного сотрудника в первом списке, переключаются его показатели в различных столбцах из второго. Шикарно!
Как насчёт подсветки выбираемых имён?
В качестве бонуса можете прикрутить сюда условное форматирование, чтобы оно подсвечивало строку по выбранному имени. Мы же смотрим отчётность, это было бы крайне удобно!
Если мы должны найти строку по определённому показателю, то, естественно, выбор падёт на функцию ПОИСКПОЗ. Искать мы будем позицию по имени сотрудника в ячейке F7 и подсвечивать ту строку таблицы, в которой это имя/фамилия находится. Получится очень эффектно.
Обводим всю нашу таблицу. Переходим на вкладку «Главная», потом «Условное форматирование», затем «Создать правило».
Выбираем «Использовать формулу для определения форматируемых ячеек».
Сперва зададим формат. Зальём каким-нибудь цветом. Окрасим шрифт. Должно выйти неплохо. Теперь переходим к прописыванию формулы.
Обратите внимание, что в первом аргументе (искомое значение) мы фиксируем только столбец, поскольку нам нужно, чтобы функция перемещалась только по строкам.
$F$7 – наша зафиксированная ячейка с именем из выпадающего списка.
0 – просматриваемый массив, то есть ПОИСКПОЗ находит первое значение, равное искомому.
Нажимаем ОК и ОК далее.
Переключаем имена и любуемся подсветкой.
ПРОСМОТРХ вместо задания условий
Возникла ситуация, когда следует начислить определённые бонусы в зависимости от жалования сотрудников. Отдельная маленькая табличка уже находится на листе. В одном столбце – ориентировочное жалование, в другом – бонусы в процентах.
Если зарплата равна 10 000 – никакого бонуса, 30 000 – 5% бонусом, 50 000 – 8% бонусом и так далее. Определим, кому какой бонус полагается с помощью ПРОСМОТРХ.
Создадим новый столбец «Новый бонус» на основном листе.
Пишем в первой ячейке нового столбца формулу:
Первым аргументом выбираем ячейку В2 из столбца с окладами сотрудников.
В качестве второго аргумента указываем столбец с жалованием из бонусной таблички с другого листа, то есть у нас будет Лист2!F13:$F$17.
Возвращаемый массив – уже бонусный проценты, то есть бонусный столбец из бонусной таблички с другого листа – Лист2!G13:$G$17.
Пропускаем аргумент, если мы ничего не находим, сейчас это необязательно.
И самое главное – пятый аргумент, который отвечает за точность поиска. Указываем там -1, то есть точное совпадение или следующее меньшее значение.
Теперь нужно проверить результаты.
Гари Миллеру досталось 10% бонусов. Идём на бонусную табличку и смотрим.
Его оклад составляет 60 000 – это 10%. Следующий оклад для бонусов уже 100 000 и 15% соответственно. Что сделал Excel: он нашёл 60 000 и затем отобрал следующее минимальное значение, то есть 10%.
Никаких условий и никаких подборов. Одна только функция ПРОСМОТРХ помогла нам решить целую вереницу задач.
Научитесь использовать все прикладные инструменты из функционала MS Excel.
В данной статье, на простых примерах, описаны варианты использования функции ВПР MS Excel. Важные аспекты и возможные ошибки, которые возникают при использование данной функции. Функция ВПР в Excel.
Как вызвать функцию ВПР. Функция ВПР в Excel
В первую очередь разберемся, как вызвать данную функцию. Выбираем закладку Формулы. Находим кнопку Вставить функцию. И нажимаем ее. Так же, можно вызвать функцию ВПР, сочетанием клавиш Shift + F3.
Появляется диалоговое окно Вставка функции. В строке Поиск функции вводим ВПР. Нажимаем найти. По результатам поиска, в пункте Выберите функцию, появляется ВПР. Нажимаем на нее левой кнопкой мыши два раза или нажимаем ОК. Появляется непосредственно диалоговое окно функции ВПР – Аргументы функции.
Теперь перейдем непосредственно к вариантам применения функции ВПР.
Первый вариант использования функции ВПР.
Для примера возьмем две таблице. В одной Таблице №1 будет перечень с названиями конфет и будет указана их цена за кг. В другой, Таблица №2, тот же перечень, но с указанием их количества в кг. Наша задача добавить в Таблицу №2, в столбец Цена, цену конфет из Таблицы №1, чтобы в итоге получить стоимость. Названия конфет в разных таблицах находятся в разных местах, поэтому просто скопировать цену конфет с одной таблице в другую не получиться.
Перед тем, как вызвать функцию ВПР, выбираем нужную нам ячейку, в которой будет находиться наша формула функции и соответственно значение, которое мы хотим увидеть. В нашем случае это ячейка G3. Эта ячейка находиться в столбце Цена, Таблица №2. Функция ВПР позволит взять из Таблицы №1 цену Конфеты А и вставить эту цену в столбец Цена, Таблицы №2, напротив Конфеты А.
Вызываем функцию ВПР, как описано выше.
Аргументы функции. Функция ВПР в Excel.
Искомое_значение.
Значение поиска, которое должно быть найдена в указанном нами диапазоне, в строке Таблица. В нашем примере мы указываем Конфеты Ж (ячейка Е3, Таблица №2). Так как это значение идет первое в столбце Название конфет, Таблица №2. (Это не принципиально, но удобно). Это значение, которое будет искать наша функция в Таблице №1.
Что бы выбрать нужную нам ячейку с значением, достаточно просто стать курсором в строку Искомое_значение, а потом клацнуть левой кнопкой мыши, по нужной ячейке в таблице ( В нашем примере ячейка Е3).
Таблица.
Можно присвоить нашему диапазону имя, и прописать его в строке Таблица.
Как это сделать. Выбираем нужный нам диапазон. Таблица №1. Выбираем закладку Формулы, кнопка Задать имя. Нажимаем. Появляется диалоговое окно Создание имени. Пишем любое имя. Но нужно его запомнить. Например Конфеты. Нажимаем ОК.
В строке Таблица, вместо диапазона нужно будет ввести имя, которое мы присвоили – Конфеты
Номер_столбца.
Функция ВПР осуществляет поиск значение в самом левом столбце таблицы указанного диапазона поиска. Функция присваивает этому столбцу номер 1, по умолчанию. В нашем примере самый левый столбце, это Название конфет в Таблице №1. А в строке Номер_столбца, нам нужно указать, какой номер по порядку имеет столбец, из которого нужно перенести данные. В нашем примере это столбце Цена в Таблице №1. Он «второй» по порядку, если считать слева на право, от столбца Название конфет, Таблица №1. Поэтому в строку Номер_столбца мы пишем цифру 2. Если бы столбец Цена, Таблица №1 был бы расположен по порядку не на втором месте, а предположим на десятом, то мы соответственно указывали бы в строке Номер_столбца цифру 10.
Интервальный _просмотр.
В этой строке мы пишем цифру ноль «0». Это значит, что функция ВПР будет осуществлять поиск точных совпадений между значениями поиска (Искомое_значение) и значениями в крайнем левом столбце диапазона поиска (Таблица). В нашем примере поиск точных совпадений будет происходить между столбцом Название конфет, Таблица №1, и столбцом Название конфет в Таблице №2.
Если мы поставим цифру один «1», функция будет осуществлять поиск не точного совпадения, а приближенного к нашему критерию поиска.
Вот как это выглядит все вместе.
Протягиваем формулу по всему столбцу Цена в Таблице №2. Все цены перенесены с Таблице №1 в Таблицу №2.
Второй вариант использования функции ВПР.
У нас есть Таблица №1 и Таблица №2. Каждая таблица состоит из одного столбца. Для понимания алгоритма работы функции ВПР, в данном случае, таких простых таблиц достаточно. Столбцы содержат практически одинаковые данные. При этом, нам нужно сравнить их и узнать, какие данные есть в Таблице №2, но нет в Таблице №1.
Справа от Таблицы 2, в ячейку G3, вставляем функцию ВПР. Это расположение взято в качестве примера, можно использовать любой другой столбец и оформление.
В диалоговом окне, Аргументы функции прописываем следующие данные:
Искомое_значение. Это значение ячейки из Таблицы №2, наличие которой мы проверяем в Таблице №1. В нашем примере, это ячейка F3 (Значение 9).
Таблица. В данном случае мы указываем не диапазон всей таблицы, а только диапазон конкретного столбца, который мы сравниваем. Можно выделять столбец в таблице. А можно выделять весь столбец листа. В том случае, если в нем больше нет других данных. Вместо диапазона можно указать заданное имя столбца (Задаем имя).
Номер_столбца. Поскольку в таблице указан конкретный столбец, то здесь мы ставим цифру 1, так как искомое значение и данные, которые мы хотим перенести совпадают.
Интервальный _просмотр. Здесь ставим ноль «0», так как хотим, чтобы функция искала точные совпадения.
Можно проверить с точностью наоборот. И найти какие данные есть в Таблице №1 но нет в Таблице № 2.
Обратите внимание. Функция ВПР в Excel.
Функция ВПР осуществляет поиск значений (это значения, которые указаны в строке Искомое_значение) в первом (самом левом) столбец таблицы, диапазон которой указан в строке Таблица.
Это особенность, так же говорит нам о том, что значения, которые мы хотим перенести в нашу таблицу, должны быть справа, от столбца, в котором происходит поиск по заданному критерию.
Если в диапазоне Таблица указан один, конкретный столбец, то функция ВПР проверяет только его. И данное правило не обязательно.
Можно осуществлять поиск на разных Листах. Алгоритм работы такой же. Формула функции будет выгладить вот так: =ВПР(E6;Лист1!$B$1:$C$11;2;0). В нашем примере формулы функция ВПР расположена на Листе 2, а поиск значения и перенос данных с диапазона поиска происходит на Листе 1. Вместо диапазона можно использовать Заданное имя. Например Конфеты. Тогда формула функции будет выглядеть вот так: =ВПР(E6;Конфеты;2;0).
Возможные ошибки.
Читайте также: