Макрос вместо впр в excel
Кто-нибудь подскажет как заменить формулу ВПР на тоже самое только в VBA? И повлияет ли это при работе в сети..
Приведите хоть какой-нибудь маленький примерчик в exel..
дальше постараюсь домучать и примень это самостоятельно
Задаю этот вопрос потому как постоянно имею проблему с этой формулой, может все таки на скриптах это будет на много надежнее..
Скорость вычисления.
10000 ячеек с формулами:
ВПР - меньше 1с
ЗаменаВПР - 5 с
Разницу между функциями вижу только в том, что ВПР вместо пустой ячейки Y выводит 0 - что несколько неверно.
Для того, кто действительно имеет представление о стандартной функции рабочего листа =ВПР(), разница видна даже невооружённым взглядом, а для тех кто не видит, приготовлен небольшой скриншот.
Похоже тема так или иначе должна свестись к обсуждению и выявлению достоинств и недостатков функции ВПР,
которые приносят arturhome проблемы, и о нюансах работы которой так хорошо осведомлен pashulka.
arturhome
Код ЗаменаВПР не претендует на абсолютную замену ВПР (хотя бы количеству аргументов) и является заготовкой для вас, если выберете путь VBA.
Взял кирпич подбросил вверх, а он упал и ударил меня по башке, стало больно. Теперь господа, необходимо создать нового Бога, который в дальнейшем не будет допускать подобного.
Может быть всё-таки стоит перестать винить в бедах, которые люди творят собственными руками, весь мир, а обратить свой взор на свои собственные недостатки, например, раз и навсегда разобраться с форматами ячеек, содержащими числовые значения.
pashulka, спокойнее! Был задан вопрос - был дан ответ! ВПР - клёвая штука, никто не спорит! Но. Я тоже делал замену для ВПР по просьбе пользователей! Это вынужденные меры! О них речь и идет. Если интересно, могу описать свою ситуацию, а Вы можете предложить решение на ВПР.
Avsha
спасибо попробую применить на практике, возможно это болие действенный вариант
Sokl, Попытка полностью повторить стандартную функцию рабочего листа =ВПР() говорит только о кривизне рук пользователей и не более. И не надо призывать меня к спокойствию, а вопросы бывают разные и не всегда надо решать их подобным способом.
pashulka я очень рад за вас что вы достигли своим умом и сообразительностью высот, которые обычный юзер при его дилетантском подходе.. суть проблемы ищет в кривизне программы а не его рук.. Но есть люди которым действительно нужна помощь таких как Вы что бы облегчить существование в этом безумном и постоянно обновляющемся мире ПК.
Если уж речь пошла об этом.. предложите свой вариант решения проблемы.. она у меня действительно есть..
Если у Вас получится заменить стандартную функцию ВПР средствами VBA причем что бы эту функцию не пришлось выставлять а пользоваться только скриптами.. с постоянно увеличивающим диапазоном и обновлением с рабочей книгой по сети..
если есть такой способ плиз помогите .. потому как при работе с более 25000 записями у меня почему то это занимает очень большое время что бы обнавится с рабочей книгой.. второе это исправлять ошибки за чайниками которые каким то образом умудряются испортить все ..поэтому и требуется использование формулы ВПР средствами VBA..
может это и не решит все проблемы но может какую то часть
не большой примерчик.. но думаю что есть другой способ.. потому как есть не большие нюансы..вствлять нужно не всю строку а определенные части и в нужном месте. нужно что бы рабочая книга была все время открыта вобщем я понимаю что ВПР это делает как нельзя лучше . но она тоже не совершенна
Если речь идёт о стандартной функции рабочего листа =ВПР(), то её не нужно заменять на пользовательские функции.
Если же идёт о использовании макросов, например, для копирования ячеек вместе с параметрами форматирования, то это не имеет отношения к стандартным функциям листа, а пример поиска и копирования нескольких ячеек см. ниже :
Имеется данная табличка. Мне необходимо копировать содержимое строк по порядку, если в первой ячейке строки имеется дата например равная 25.05.2013. Т.е. на чистом листе мы получим:
A | 22334 |
E | 232345 |
H | 454545 |
N | 676776 |
P | 89797 |
S | 34423 |
K | 5656 |
L | 77687 |
Z | 7868 |
М | 89898 |
Вся загвозка в том, что диапазон таблицы должен обязательно быть фиксированным.
Но так он будет вприть только первую строку, и если тянуть таблицу на чистом листе вниз сами понимаете получим снова первую строку в каждой следующей. А мне нужно содержимое всех строк с этой датой. И чего скрывать, затем нужно такое же содержимое с другой датой.
Как быть? Спасибо.
Аналоги или ускорение ВПР через макрос
Есть лист, в который простейшим ВПР-ом подтягиваются данные из другого листа. Но. на листе.
Макрос впр по 3 столбцам
Здравствуйте. - Помогите преобразовать макрос впр с рекордера в нормальный вид. - вместо 0 и.
Макрос ВПР с перебором множества одинаковых файлов
Добрый день! Очень нужна помощь! Задача следующая: Есть файл-шаблон со столбиками "ИНН".
Где-то была UDF ВПР2() - там можно указать каое по счёту совпадение нужно брать.
Но быстрее конечно будет макрос, фильтрующий данные.
А вообще, чем фильтр не нравится?
выделить диапазон, в котором будут ответы (сразу нужное количество ячеек в столбце, можно с запасом), ввести эту формулу, нажать Ctrl + Shift + Enter
в формуле $L$2:$L$17 - диапазон со значениями, которые нужно выбирать; K2:K17 - диапазон с условием (у вас это первый столбец); $O$2 - ячейка со значением, которое нужно искать в диапазоне с условием
здесь СТРОКА()-4 -4 означает что диапазон с результатом находится на 4 строки ниже, чем диапазон для поиска условий.
Привет всем!! Огромное спасибо за ответы. Я бы хотел прояснить ситуацию как можно конкретнее. Поэтому я подготовил точный пример. Во вложении будет файлик, с которым можно будет по экспериментировать.
Вложение 273777
А сейчас я расскажу механику отчета над которым решается вопрос ВПР или Макрос:
1. Имеется некая сводная табличка. Она выгружается автоматически в фаил экселя на первый лист.
В диапазоне столбцов =F:H находится необходимая нам табличка (=F14:H28) с данными.
Не мало важно сказать, что данные в сводной таблице, каждый день выгружаются с совершенно разным набором строк.
Т.е. Строк в каждой таблице может добавиться/уменьшится на 2-3, а вот в столбцах таблицы останутся на своих местах. Пример:
Как видите перемещается наша таблица (=F14:H28) построчно, но остается в своем диапазоне столбцов =F:H
2. Далее как уже необходимо по заданию. Мы должны вытащить из нашей таблички (=F14:H28) Brend и DISC на необходимые нам даты на следующий лист.
слева 21 справа 22.
Главное помнить, что с каждой выгрузкой сводной таблицы, наша табличка перемещается по строчкам, а также может менять их количество.
Надеюсь описал процесс понятно Спасибо!
P.s. макрос форумчанина показался мне сложным, не могли бы вы прокомментировать код?
Какие-то проблемы на форуме, теперь это не возможно.
И еще раз)). Не грузились вложения. Я бы хотел прояснить ситуацию как можно конкретнее. Поэтому я подготовил точный пример. Во вложении будет файлик, с которым можно будет по экспериментировать.
А сейчас я расскажу механику отчета над которым решается вопрос ВПР или Макрос:
1. Имеется некая сводная табличка. Она выгружается автоматически в фаил экселя на первый лист.
В диапазоне столбцов =F:H находится необходимая нам табличка (=F14:H28) с данными.
Не мало важно сказать, что данные в сводной таблице, каждый день выгружаются с совершенно разным набором строк.
Т.е. Строк в каждой таблице может добавиться/уменьшится на 2-3, а вот в столбцах таблицы останутся на своих местах. Пример:
Как видите перемещается наша таблица (=F14:H28) построчно, но остается в своем диапазоне столбцов =F:H
2. Далее как уже необходимо по заданию. Мы должны вытащить из нашей таблички (=F14:H28) Brend и DISC на необходимые нам даты на следующий лист.
слева 21 справа 22.
Главное помнить, что с каждой выгрузкой сводной таблицы, наша табличка перемещается по строчкам, а также может менять их количество.
Надеюсь описал процесс понятно Спасибо!
P.s. макрос форумчанина показался мне сложным, не могли бы вы прокомментировать код?
1) На ваш вопрос - ВПР или макрос, мой ответ - макрос.
2) вы показали на вашем примере в файле Excel, что данные копируются с листа Сводная на лист "Выборка" - 1-я дата в столбец В, 2-я дата в столбец М, а их всего 2 даты будет? или дат может быть 50 и копировать их так же нужно через 9 столбцов друг от друга?
Если немного изменить структуру исходных данных (см. лист "Данные" здесь использован динамический диапазон - см Диспетчер имен -Data), то можно обойтись сводной таблицей (лист "Свод данных" )
вот вариант с формулой массива - на листе выборка проставить нужные даты, в исходном листе данные могут находится в диапазоне со 2-й строки до 50-й (либо увеличить диапазон в формуле)
1) На ваш вопрос - ВПР или макрос, мой ответ - макрос.
2) вы показали на вашем примере в файле Excel, что данные копируются с листа Сводная на лист "Выборка" - 1-я дата в столбец В, 2-я дата в столбец М, а их всего 2 даты будет? или дат может быть 50 и копировать их так же нужно через 9 столбцов друг от друга?
Только 2 даты. И диапазон из 3 столбцов, где содержится таблица и перемещается по строкам, изменяться не будет.
Добавлено через 10 минут
Если немного изменить структуру исходных данных (см. лист "Данные" здесь использован динамический диапазон - см Диспетчер имен -Data), то можно обойтись сводной таблицей (лист "Свод данных" )
К сожалению в том и сложность задачи, что структуру не нужно менять. А запись данных по датам на другом листке должна происходить в простые таблицы, потому что в будущем они будут тоже использованы для впр, т.е. важно чтобы их диапазоны не пересекались.
Добавлено через 3 минуты
вот вариант с формулой массива - на листе выборка проставить нужные даты, в исходном листе данные могут находится в диапазоне со 2-й строки до 50-й (либо увеличить диапазон в формуле)
Не могли бы более подробно разъяснить, данную формулу, я новичек в Excel. c ЕСЛИОШИБКА все понятно. Хотелось бы понимать, что от для чего и откуда. Спасибо! Первый ваш пост прочитал, есть что добавить?
Добавлено через 3 минуты
Большое спасибо очень наглядно. Мне в самом начале и показалось, что без массивов данных и записи в них не обойдется. Сможете также прокомментировать формулу:
Почему вы не выбрали ВПР?
по формулам лучше обрататься к автору формулы.
P.S. По мне лучше макрос, чем формула, допустим у вас будет 10.000 значений. Макрос очень быстро скопирует данные на другой лист, а формула . она должна постоянно быть записана в ячейках листа, она постоянно будет пересчитываться и "подвешивать" файл, её нужно постоянно контролировать - до какой строки она протянута (вдруг данных больше, чем протянута формула) и т.д. Макрос написал один раз и забыл. Нажал Alt+F8, запустил макрос - результат готов и ничего не "подвешивает" файл.
ВПР не выбрала, т.к. он по умолчанию берет только первое найденное значение.
=ЕСЛИОШИБКА(ИНДЕКС(Сводная!G2:G50;НАИМЕНЬШИЙ(ЕСЛИ($B$1=Сводн ая!F2:F50;СТРОКА(Сводная!G2:G50)-1;"");СТРОКА()-2));"")
ИНДЕКС - возвращает значение из исходной таблицы, которое находится на пересечении указанной строки и стобца (столбец у нас один, поэтому мы его дополнительно не указываем), здесь Сводная!G2:G50 - исходная таблица, из которой нам нужно значение;
НАИМЕНЬШИЙ(ЕСЛИ($B$1=Сводная!F2:F50;СТРОКА(Сводная!G2:G50)-1;"");СТРОКА()-2)- возвращает наименьший номер строки, в которой выполняется наше условие:
ЕСЛИ() - проверка нашего условия,
СТРОКА(Сводная!G2:G50)-1 -вернет номер строки (в выбранном диапазоне) в которой это условие выполнилось (все варианты) (минус 1, т.к. 1 первую строку занимает заголовок - чтобы первая строка выбранного диапазона обозначилась как 1-я строка);
СТРОКА()-2 - этот параметр в указывает какой по порядку наименьший номер строки нам нужен (-2, т.к. 2 первые строки листа занимает заголовок)
поскольку это формула массива, то получается что для первой строки, в которой должен быть результат, выводится значение для первого выполнения условия, для второй строки - второе выполнение условия и т.д.
= Мир MS Excel/vba вместо ВПР - Мир MS Excel
Войти через uID
Войти через uID
Добрый день.
Покажите пример Ваших данных (и какой нужен результат) - подскажем.
Обычно такие задачи делаются массивами и словарём.
Добрый день.
Покажите пример Ваших данных (и какой нужен результат) - подскажем.
Обычно такие задачи делаются массивами и словарём. Hugo
subjugator, как вариант, аналог ВПР на VBA связка Find и Offset
Function VPR(ByRef cell As Range, ByRef rng As Range, col&)
VPR = rng.Find(cell, , xlValues, 1).Offset(0, IIf(col + Abs(col), col - 1, 1 + col))
End Function
subjugator, как вариант, аналог ВПР на VBA связка Find и Offset
Function VPR(ByRef cell As Range, ByRef rng As Range, col&)
VPR = rng.Find(cell, , xlValues, 1).Offset(0, IIf(col + Abs(col), col - 1, 1 + col))
End Function
Function VPR(ByRef cell As Range, ByRef rng As Range, col&)
VPR = rng.Find(cell, , xlValues, 1).Offset(0, IIf(col + Abs(col), col - 1, 1 + col))
End Function
архив, заполнение - 1, заполнение - 2 - это название листов! На листе (архив) есть столбцы с D2 по J2 в которые каждый день вносятся значения, которые имеют порядковый номер (столбец A) и номер позиции (столбец С)! На листах (заполнение - 1) и (заполнение - 2), в строке (2) перечислены все возможные значения(в примере их 10). После внесения данных на лист (архив) - на лист (заполнение - 1) должен идти порядковый номер группы значений для каждого значения(пример: в (архиве) D2=1 и находится в строке с (№)=1 - значит на лист (заполнение -1) в ячейке С3=1. В (архиве) G3=1 и находится в строке (№)=2 - значит на лист (заполнение -1) в ячейке C4=2 и т.д На лист (заполнение - 2): в (архиве) D2=1 и (№ позиции ) =1 - значит на лист (заполнение -2) в ячейке С3=1. В (архиве) G3=1 и находится в строке (№)=8 - значит на лист (заполнение -1) в ячейке C4=8 и т.д Строка в состав которой входит это значение должна копироваться на лист пронумерованный соответствующим значением с подсветкой этого значения(пример на листах (1) и (2))
архив, заполнение - 1, заполнение - 2 - это название листов! На листе (архив) есть столбцы с D2 по J2 в которые каждый день вносятся значения, которые имеют порядковый номер (столбец A) и номер позиции (столбец С)! На листах (заполнение - 1) и (заполнение - 2), в строке (2) перечислены все возможные значения(в примере их 10). После внесения данных на лист (архив) - на лист (заполнение - 1) должен идти порядковый номер группы значений для каждого значения(пример: в (архиве) D2=1 и находится в строке с (№)=1 - значит на лист (заполнение -1) в ячейке С3=1. В (архиве) G3=1 и находится в строке (№)=2 - значит на лист (заполнение -1) в ячейке C4=2 и т.д На лист (заполнение - 2): в (архиве) D2=1 и (№ позиции ) =1 - значит на лист (заполнение -2) в ячейке С3=1. В (архиве) G3=1 и находится в строке (№)=8 - значит на лист (заполнение -1) в ячейке C4=8 и т.д Строка в состав которой входит это значение должна копироваться на лист пронумерованный соответствующим значением с подсветкой этого значения(пример на листах (1) и (2))
только вместо листов используются отдельные файлы.
Такая задача может быть решена с помощью макроса? subjugator
архив, заполнение - 1, заполнение - 2 - это название листов! На листе (архив) есть столбцы с D2 по J2 в которые каждый день вносятся значения, которые имеют порядковый номер (столбец A) и номер позиции (столбец С)! На листах (заполнение - 1) и (заполнение - 2), в строке (2) перечислены все возможные значения(в примере их 10). После внесения данных на лист (архив) - на лист (заполнение - 1) должен идти порядковый номер группы значений для каждого значения(пример: в (архиве) D2=1 и находится в строке с (№)=1 - значит на лист (заполнение -1) в ячейке С3=1. В (архиве) G3=1 и находится в строке (№)=2 - значит на лист (заполнение -1) в ячейке C4=2 и т.д На лист (заполнение - 2): в (архиве) D2=1 и (№ позиции ) =1 - значит на лист (заполнение -2) в ячейке С3=1. В (архиве) G3=1 и находится в строке (№)=8 - значит на лист (заполнение -1) в ячейке C4=8 и т.д Строка в состав которой входит это значение должна копироваться на лист пронумерованный соответствующим значением с подсветкой этого значения(пример на листах (1) и (2))
только вместо листов используются отдельные файлы.
Такая задача может быть решена с помощью макроса? Автор - subjugator
Дата добавления - 15.06.2014 в 20:44
WorksheetFunction.VLookup – это метод VBA Excel, который ищет значение в крайнем левом столбце таблицы (диапазона, двумерного массива) и возвращает значение ячейки (элемента массива), находящейся в указанном столбце той же строки. Метод соответствует функции рабочего листа =ВПР (вертикальный просмотр).
Синтаксис
Синтаксис метода WorksheetFunction.VLookup в VBA Excel:
Параметры
Описание параметров метода WorksheetFunction.VLookup:
Параметр | Описание |
---|---|
Arg1 (Lookup_value) | Обязательный параметр. Значение, которое необходимо найти в первом столбце таблицы. |
Arg2 (Table_array) | Обязательный параметр. Таблица с двумя или более столбцами данных. Используется ссылка на диапазон, имя диапазона или массив. |
Arg3 (Col_index_num) | Обязательный параметр. Номер столбца, значение из которого возвращается. |
Arg4 (Range_lookup) | Необязательный параметр. Логическое значение, указывающее, должен ли метод VLookup искать точное совпадение или приблизительное. |
Значения параметра Arg4 (Range_lookup), задающие точность сопоставления:
Значение | Точность сопоставления |
---|---|
True | Значение по умолчанию. Метод WorksheetFunction.VLookup находит точное или приблизительное совпадение Arg1 со значением в первом столбце. Если точное совпадение не найдено, используется самое большое значение, меньшее Arg1. Значения в первом столбце таблицы должны быть отсортированы по возрастанию. |
False | Метод WorksheetFunction.VLookup находит только точное совпадение. Сортировка значений первого столбца таблицы не требуется. Если точное совпадение не найдено, генерируется ошибка. |
Если значение параметра Arg1 является текстом, а Arg4=False, тогда в строке Arg1 можно использовать знаки подстановки (спецсимволы): знак вопроса (?) и звездочку (*). Знак вопроса заменяет один любой символ, а звездочка соответствует любой последовательности символов. Чтобы знак вопроса (?) и звездочка (*) обозначали сами себя, перед ними указывается тильда (~).
Научитесь использовать все прикладные инструменты из функционала 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, выберем столбец с сотрудниками в качестве искомого и возвращаемого массива, то… получим ошибку. В идеале наша формула должна выглядеть следующим образом, смотрите:
В первый аргумент, перед искомой ячейкой G2, дописываем “*”. Звёздочка – это служебный символ, которого мы сцепляем амперсандом (&) с ячейкой G2 и таким образом говорим программе, чтобы осуществлялся поиск не только Willard, но и всего остального содержимого ячейки до Willard. То есть первый аргумент у нас примет вид: «*»&G2
Второй аргумент – искомый массив, то есть столбец с именами: 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.
Читайте также: