Трюк Excel 16. ВПР для множества значений (без использования функции ВПР)
ฝัง
- เผยแพร่เมื่อ 27 ก.ย. 2024
- Два способа создания списка из всех значений, соответствующих указанному критерию (множественный ВПР). Первый способ - с помощью комбинации функций ИНДЕКС и НАИМЕНЬШИЙ (формула массива), второй - ИНДЕКС и АГРЕГАТ (обычная формула). Другие трюки Excel и файл с примером можно скачать здесь statanaliz.inf...
► Корпоративный тренинг "Продвинутый уровень MS Excel":
statanaliz.inf...
► Онлайн курс "Продвинутый уровень MS Excel":
statanaliz.inf...
**************************
💳 Поддержать канал деньгами
statanaliz.inf...
** Мой сайт об Excel и статистике **
statanaliz.info/
** Странички и аккаунты в соцсетях **
Facebook: / statanaliz.info
Вконтакте: id_stat...
Твиттер: / statanaliz_info
Telegram: t.me/statanaliz
Поверить не могу, что я этот видел! Каждая секунда этого видео на весь золота! Уже несколько лет я не мог решить эту проблему ВПР, а оказывается есть такое крутое решение! Спасибо огромное автору!
Благодарю за комментарий и за похвалу. В Excel можно всякое ) Рад, что Вы нашли решение своей задачи!
Автору большой респект за доходчивое объяснение темы, а также за грамотные примеры с формулами.
Приспосабливаю данный урок к своим задачам и не перестаю удивляться и восхищаться вашими знаниями и щедростью. Что вы поделились универсальной формулой, в которой не нужно использовать доп столбцы или строки. Она везде будет работать. И можно сэкономить кучу времени. Спасибо еще раз!!!
Обожаю ваши краткие ёмкие howto. Вы лучше всех описываете функции экселя в русскоязычном сегменте. Продолжайте, пожалуйста, делайте еще!
Спасибо за отзыв. Буду делать еще )
Более понятного объяснения нигде и ни у кого не видел, всё последовательно и аргументировано. Благодарю за видео!
больше года искал , в конце концов, я нашел . Благодарю.
Очень крутой урок. Хорошо, что в Гугл таблицах есть функция filter, которая выдаст сразу весь список счетов по указанному критерию. ))
Ваш комент спас мое время!
Второй способ получился более универсальный из за простоты (без массива). Всегда выручают.
Через год снова помог решить вопрос 😄.
Благодарю за ваш труд 🤝
Примите большую благодарность за раскрытие темы. Очень помогло.
да не за что
Огромное спасибо за полезную инфу,Дмитрий. Всех Вам благ!
Что бы я без Вас делала... Моя самая глобальная благодарность самоучки, нашедшей Вас в состоянии своего самого примитивного познания возможностей ексель, когда знаю, что программа мощная, но пользоваться на тот момент умела сложением и вычитанием в ней. Ну, возможно ещё делением и умножением))) Благодаря Вам я стала фанатом и бесконечным ценителем этой программы. Системно, фундаментально, предметно и доступно. Вы мой лучший гуру:)
Спасибо большое за такой отзыв. Очень важно видеть результат моих зрителей и учеников. Оставайтесь на связи. Будут другие видео.
Спасибо, что так подробно делитесь знанияии! Очень помог этот урок!
Превосходно! Супер! Спасибо огромное, Дмитрий! Молодец! Агрегат - то, что надо!
АГРЕГАТ - отличная функция. Жаль, не все знают.
С удовольствием смотрю ваши видео. Подача материала на уровне.👍
Спасибо за отзыв )
Очень полезная и интересная комбинация, следует тренироваться для полного освоения. Спасибо.
Пару раз повторите, лучше на своем примере. Также рекомендую сохранить где-нибудь ссылку на этот урок, т.к. в памяти все держать невозможно. Не уверен, что сам с ходу вспомню этих формулы )))
первый раз когда про индекс объяснили так, что стало понятно! спасибо огромное!!! подписалась на вас и буду ждать новых уроков по Экселю
Спасибо, познавательно! Для небольших таблиц сойдет. Сводная проще составляется и быстрее вычисляет при больших таблицах
Рассмотренный способ не отменяет сводных таблиц, а дает дополнительный инструмент для подтягивания данных.
Езепов Дмитрий, эт точно.
Спасибо, очень помогли! На протяжении недели искала как бы решить задачу с поиском позиции по условиям. Буду смотреть остальные уроки однозначно. Я работаю не в Экселе, а в гугл таблицах, но суть то ведь не меняется. Подстроила вашу формулу под свои требования и вуаля- 13 взаимосвязанных таблиц не нужно ручками перебирать, чтобы найти и исправить то, что изменилось
Спасибо за комментарий. Посмотрите еще урок про объединение запросов в Power Query th-cam.com/video/WKRL3NE926M/w-d-xo.html
Отличный урок! Автор - гуру.
Гуру- Что это значит? Но слово прикльно звучит.
Похоже на какой-то секретный шифр по раскодировке Вселенной. Я попробовал. Не понятно до конца - как, но - работает. Благодарю.. от души!
Еще раз видео посмотрите. Рано или поздно станет понятно )
Спасибо! Замечательно и подробно объясняете все моменты!!!
Топовый урок, большое Вам спасибо.
Класс! Мозги у тебя хорошо работают. Однозначно видео в избранное.
спасибо, почувствовал себя тупым)) буду осваивать уроки попроще. но за труд уважение! натоящий специалист это не тот, кто знает много, а тот, кто делится знаниями
Спасибо. Да, этот урок следует освоить после изучения обычного ВПР и аналогов.
надо пробовать на практике и все пойдет
Почему о твоей светлой голове не знают люди!
я взял по твоему примеру и нашел способ прописать формулу, где товар один, а интересует последняя дата, итог: Все четко, спасибо! Лайк поставил. Можно было бы еще отдельно установить опцию для Ютуберов, которые осознают что показывают - туда бы тоже поставил Лайк.
Крепкого тебе здоровья и не пользуйся презервативами! Желаю, чтоб таких людей было больше ))
Спасибо за такой емкий комментарий )) И тебе всех благ )
Ничего не поняла но это круто ))
Иногда приходится по 2-3 раза пересматривать, но оно того стоит ))
Езепов Дмитрий спасибо Вам большое за ваш труд !
vivienroshe
да, это вот одно их самых сложных, над чем надо голову поломать - такие вот формулы брутальные.) Но если покорить их - это уже довольно серьёзный уровень.
Так что сейчас постараюсь понять.)
Сходу ни%уя не ясно, вне всякого сомнения
Дмитрий, очень крутой трюк! спасибо большое за видео и инструкцию!!!
Столкнулся с трудностью небольшой: Формула: =ЕСЛИ($AE$35:$AE$48=$A$35;СТРОКА(AE35:AE48)-СТРОКА($AE$35)+1;)
по отдельности все корректно:
Логическое выражение $AE$35:$AE$48=$A$35 выдает "{ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА}"
Если истина СТРОКА(AE35:AE48)-СТРОКА($AE$35)+1 - выдает {1:2:3:4:5:6:7:8:9:10:11:12:13:14}
По идее должно быть {ЛОЖЬ:2:ЛОЖЬ:ЛОЖЬ:5:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:13:14}, а выдает {0:2:0:0:5:0:0:0:0:0:0:0:13:14}
Из-за этого функция Наименьший выкатывает все значения в столбик - куча нулей, и после 2,5,13,14...
как вместо 0 сделать ЛОЖЬ?
Андрей, а можете подсказать, как после проверки с F9, вернуть формулы и чтобы в ячейке показывал расчет, а не "ложь" или "истина"? я сижу парюсь не могу вернуть. плиз подскажите
Удивительно, насколько упрощает жизнь новая функция FILTER() в Excel 365. Формула: FILTER(B2:B11;A2:A11=F1;"")
НО для понимания логики работы Excel видео очень полезно. Ну и для тех, у кого нет подписки 365...
Спасибо!
Супер! Спасибо! Очень интересно!
Полезно для общего развития=)
Хотя для маленьких объёмов можно и простыми фильтрами обойтись=)
В этом и был фокус - автоматизировать вывод значений по заданному критерию/фильтру.
Спасибо за видео!
Однозначно лайк!) Автору респект!)
Очень круто! Спасибо большое за материал, очень помог в работе
Пожалуйста. Очень рад, если урок оказался полезным. Спасибо за отзыв! Удачи! )
Спасибо большое! Сейчас все перепишу и буду тренироваться. Спасибо, за показ клавиш, которые не озвучиваются. Тоже очень нужная вещь! Еще раз , большое спасибо за ваш труд!
Вам спасибо, что посмотрели и написали отзыв )
Большое Спасибо!!!
Да уж , спасибо за труд. Интеллектуальный труд - самый трудный. Знаю каково это конструировать такие трюки формулами, но эти хитросплетения - это же ,по мне, так - верх конструирования
)) Спасибо за поддержку.
Огромное спасибо. Очень помогли понять эти функции.
в очередной раз преисполнился благодарности PQ. До неё тоже приходилось исхитряться формулами и прочее, а сейчас же - ткнул пару раз мышкой и готово)
Это да. Только этот ролик не про PQ ))
ВНИМАНИЕ! КОМУ МОЖЕТ БЫТЬ ПОЛЕЗЕН ЭТОТ ПРИЁМ!
На заметку менеджерам, которые делают почтовую рассылку своей продукции с формированием заказа в Excel.
Я не раз получал такую "неудобную" рассылку, где в прайсе нужно отметить количество заказываемой продукции ...
В итоге отправитель получал весь прайс (множество строк) с количеством, отмеченных мной, продукции. Ему необходимо было "промотать" весь прайс, перенести отмеченные позиции, проставить стоимость и сформировать мой заказ .... крайне неудобно!
В ответ на предложение, я отправлял готовую схему, которую в данном уроке довел до педагогического совершенства Дмитрий!
Суть сводилась к следующему:
1. На первом листе Прайс, на втором - лист Заказа.
2.. В первом листе в столбце Заказа я отмечал количество заказанных позиций - ставил количество товара или признак заказа (любую метку).
3. Лист заказа формируется автоматом, с указание моих реквизитов (почта, телефон, адрес)
4. И если у вас настроен почтовый клиент - прайс автоматом уйдет по адресу ...
Еще раз спасибо, Дмитрий!
Формула конечно интересная сама по себе, комбинировать и получать результат всегда круто, но проще намного фильтр, или загнать таблицу в сводную и там всё выдаст))
Если для решения одной задачи один раз, то да, быстрее в фильтр или сводную. Но если такая задача возникает по 5 раз на дню, то формула, наверное, более эффективное решение ).
А как сделать в сводной ?
ВАУ а так можно было?))))) очень классный урок, спасибо также за показ клавиш!
Оказывается, так тоже разрешается )) Спасибо за комментарий.
Я ваш канал бухгалтерам буду советовать к обязательному ознакомлению
Спасибо ))
прям вообще понравилось объяснение, особенно подсказка что делать с большими базами данных) огромное спасибо, только вот не понимаю как сделать чтоб функция наименьший не сравнивала ячейки с ячейкой(пустой либо ячейкой с ошибкой) а так всё отлично!! огромное спасибо!
Спасибищще!
Дмитрий, очень полезное видео, спасибо!
Очень рад. Вам спасибо!
Спасибо, узнал много нового с "нуля!"
Рад, что помог узнать новое. Спасибо за комментарий.
Большое спасибо. очень помог Ваш ролик.
Отлично. Очень рад. Спасибо за комментарий!
супер, все понятно, наглядно и работает
!
Это просто гениально
Ничего не понял, но очень интересно! Мне надо разобраться с функцией СТРОКА)))
спасибо, вы сделали мой день
Спасибо Вам большое.
повторюшка Дмитрий. Идею доработал
Все за нас уже давно придумали.
Спасибо за информацию. Мне показалось, что формула возвращала значения не заданому критерию. Вместо счет фактур яблок, она возвращала картошку и другие.
Вообще супер, подскажите только что сделать если допустим надо показать только значения которые не повторяются а не все которые есть
Ни чего не понятно, но было интересно. Спасибо
Спасибо большое, отличный урок
Но есть вопрос, как вывести множество значений без повторений?
Как если бы в примере были бы повторения по счёт-фактуре у какого-нибудь товара
на этапе ввода функции Агрегат еще работает, а когда дальше дополняешь - уже нет. не могу понять в чем ошибка
Можете прислать файл с примером, я посмотрю. В комментарии трудно понять. Адрес в описании канала.
Попробуйте столбцы указать наоборот =индекс(a2:a11;поискпоз(f2;b2:b11;0))
Что это дает
Класс спасибо очень пригодилось!
Спс за обясненее Индекс и ПоискПоз
Пожалуйста. Рад, если помог разобраться.
Молодец! жду еще ролики
Спасибо! А как можно сделать так что бы счёт фактуры можно было протянуть горизонтально, а формулу вниз по всем товарам. Так как например если 1000 значений (товаров) постоянно менять вместо яблоко что то будет занимать время (
Большое спасибо, за отличный урок!
Подскажите, пожалуйста, если будет время, как бы решалась такая же задача, если бы нужно было найти определенную счет-фактуру с дополнительны параметром, например, при цене 70? А то ломаю голову никак не соображу :(
Скажите, если ещё добавить один столбец с "местоположением товара", и + к этому находить товар по местоположению и счёт-фактуре. (Так сказать еще просортировать по третьему столбцу?)
Чудесно!
Подскажите как добавить несколько условий в формулу??? Вы мне очень помогли и очень еще поможете ответом )))
Круто
Спасибо за комментарий )
Добрый день, подскажите а можно сделать чтоб номера счет фактур протаскивались не вниз, а вправо при первом варианте решения?
Привет интересует применение функции агрегат но в диапазоне значений например от 1 до 10 как второй параметр для поиска
пример в ячейке информация которая содержит несколько "/" знаков Подскажите как в Эксель подсчитать количество.
Спасибо большое за такую формулу!
Подскажите , пожалуйста, можно ли ее как-то усовершенствовать, если мне нужно чтобы (если рассматривать на примере) один и тот же номер счет-фактуры выходил несколько раз подряд, т.к. мне рядом нужно вставить еще дату с суммой из соседних ячеек, и где-то их например 2, а где то 3 раза необходимо повторить?
Сомневаюсь, что такое кому-то понадобится. Мне за 20 лет такое никогда не нужно было. Всем нужна сумма
Есть и другое мнение.
Поверьте, не всем нужны суммы. Я например долго не могла придумать, как оперативно контролировать время открытия и закрытия смен по 156 кассовым аппаратам, чтобы кассиры не выходили за пределы 24 часов. Если в течение суток смена открывалась и закрывалась например дважды, то второй такой раз функция ВПР просто повторяла данные, что искажало реальную картину, а с применением этого видео проблема решится.
Здравствуйте, если искомое значение несколько (1000 ячеек) - тогда как правильно написать формулу? Я всё выбрал, везде ложь выходит
Тоже надо было решить такую задачу, но вашего урока к сожалению не нашёл в нужный момент. Использовал ВПР в комбинации со СМЕЩ для таблицы в которой ищем (т.е. когда находится первое совпадение, смещаем таблицу поиска на номер строки)
Спасибо, что поделились своей наработкой. Я СМЕЩ стараюсь не применять. Эта функция постоянно пересчитывается, что тормозит работу файла.
Сколько же еще таит в себе эксель))
Спасибо! Очень доходчиво объясняете!
А если требуется заполнить не одну строку (новую таблицу) по значению из другой таблице?
Подскажите, как применить в гугл таблицах. Оба примера НЕ РАБОТАЮТ в гугл таблицах. Функцию АГРЕГАТ гугл таблицы и вовсе не знают
Отличный способ! Но не практичный, тем более если массив более 1 000 строк. Сводная Таблица - проще, быстрее, нагляднее и практичнее))) Отдельная благодарность за АГРЕГАТ
Практичность - понятие растяжимое. Сводную таблицу нужно обновлять, а формула сама обновляется )). Поэтому тут все по потребностям. АГРЕГАТ, да, - отличный.
Формула, то обновляется))), но для неё нужно иметь запас ячеек и постоянно контролировать их количество при добавлении данных, что черевато возникновением ошибки. Сводная Таблица - эту ошибку исключает автоматически.
Я же и говорю, что дело в конкретной задаче. Сводная таблица обладает преимуществом в быстроте обработки, но для решения узкой задачи (например, при подготовке интерактивного отчета) формула может стать отличным решением. Здесь нет правильного ответа. Правильный ответ в том, чтобы знать несколько вариантов и выбрать оптимальный.
Оптимальный вариант - Сводная Таблица!)))
1. Исключает возможность возникновения ошибки из-за не протянутой вовремя формулы.
2. Формула споткнётся, если вместо "Яблоки" будет "ЯблокО", а сводная обратит на это внимание.
Способ в копилку, но пользователи должны знать о возможных "подводных камнях"
Благодарю за Ваш труд!
ВПР и им подобные всегда обладают подводными камнями ), например, неточное написание критерия. Но в таком случае можно ловко ввернуть выпадающий список и интерактивный отчет станет мегаинтерактивным ) А сводная таблица - это вообще сырой материал. Красивый отчет можно сделать лишь используя функцию ПОЛУЧИТЬ ДАННЫЕ СВОДНОЙ ТАБЛИЦЫ. Спасибо за Ваши рекомендации другим зрителям этого ролика.
Спасибо.
Просьба рассмотреть данную функцию, если поиск не в строках, а в столбцах. Т.е список товаров не сверху вниз, а слева на право
Достаточно поправить формулу, чтобы вместо СТРОКА была функция СТОЛБЕЦ. Ну, и корректировку на начальное значение изменить.
Дякую!!!
Вкладка данные- фильтр- развернуть список в первом столбце там где щаголовок- оставить галочку где яблоки- готово
а можно с двумями условиями
Очень полезно, спасибо! Подскажите, пожалуйста, что можно изменить в формуле, чтобы полученные значения отображались в строках , а не в столбце?
если еще актуально) замените слово СТРОКА на СТОЛБЕЦ (в примере с "наименьшим" последние три слово СТРОКА)
Не работает
@@МарияГалимова-т5ъ Спасибо! Помогли))
А можно как-то расширить эту формулу не по одному значению поиска, а поиска значений по списку . Пример: списку товаров (яблоки, бананы, помидоры) присвоить все значения счет. фактур в которых они встречаются, т.е. не вносить данные в конкретную ячейку поиска, а свприть две таблицы (все со всеми). Груба говоря у меня есть две таблицы одинаковой структуры, мне надо под каждую строку значений одной таблицы добавить найденные по общему критерию строки из другой таблицы, а потом работать уже с этой новой сводной.
С наступающим Новым годом! Может кто-то подскажет. Как сделать так, чтобы диапазон формул расширялся сам. То есть, я не могу знать сколько счетов-фактур у меня будет, может быть 3, а может быть 100, при этом мне необходимо формировать это все в документ, который будет печататься людьми, которые не очень понимают в экселе и в таком случае не хотелось бы видеть пустые строки
Приветствую, можете помочь с таблицей ?? Есть вопрос на который не могу найти ответ. Если есть 10 минут ответьте на мой комментарий.
Насколько же проще это делать в SQL... Спасибо
А если листов с таблицами много? Можно как-то усовершенствовать такой поиск, чтобы выводились данные по всей книге?
Прямо, чтобы по всем листам книги? Наверное, только в VBA.
Подскажите, пожалуйста, как скормить функции индекс массив? Как я понял она выдает только одно значение и массив теряется.
ответил в ВК
Дмитрий приветствую Вас! Помогите пожалуйста с ситуацией.
Веду в Эксель примитивный склад учет. Но столкнулся с тем что, к примеру в первой накладкой спички стоили 10 рублей, а в последней накладной они же стоят 15 рублей, и в расходе товара через функцию ВПР, Эксель подтягивает самую первую цену, что создает неудобства в пользовании. Как можно сделать так чтобы Эксель выдавал среди всех дубликатов - самую высокую входящую цену?
В какой ситуации это будет лучше обычного фильтра? Кроме очень широкой номенклатуры товаров.
Да в любой, где не нужны все поля исходных данных, а только одно поле, например. Это ж каждый сам решает, нужно оно или нет.
Очень сложно,но очень полезно
Требует вдумчивого осмысления, согласен )
откуда вы это узнали???))))
Да уже и сам не помню. Наверное, где-то подсмотрел ))
Добрый день! Подскажите пожалуйста, в данном примере- вы выписывали в столбец перечень номеров счетов. А если взять пример, что с самого начала мы искали суммы продаж по тому или иному овощу, и вместо перечня, нам нужна их сумма в одной ячейке? Не могу додумать формулу
Добрый день. За нас уже все придумали - СУММЕСЛИ th-cam.com/video/8gfag9QpJYY/w-d-xo.html
Спасибо за урок. Подскажите как можно во втором способе сделать так чтобы выводились все накладные в которых есть яблоко зеленое, яблоко красное. Не получается заменить "яблоко" на "яблоко *". Заранее спасибо
спасибо за урок! А как вывести накладные( в вашем примере) в одну ячейку через какой-то символ, скажем точка с запятой? Знаю. что есть функция объединить, но у меня ее нет в екселе, к сожалению... Как можно обойти ее при помощи других функций? Заранее благодарна)
Спасибо большое, давно искал! А как сделать выборку по трём позициям например: картошка, бананы и яблоки?
Надо формулу корректировать. Попробовал изменить условие, и инвойсы начинают повторяться. С ходу придумать не удалось.
Доброго времени суток, Вы делаете в формуле " = F2 ", и тогда он выбирает только лишь по соответствию, а как написать правильно условие, чтобы он выбирал по "содержит" слово "яблоки"? Подскажите, пожалуйста!
Здравствуйте. Вместо выражения, проверяющего точное совпадение $A$2:$A$11=$F$2, используйте поиск в тексте ЕЧИСЛО(ПОИСК($F$2;$A$2:$A$11)).
А как автоматически удалять лишние цветные строки под счет-фактурой?
VBA только. Либо вручную фильтром.
Есть ли возможность полученный результат объединить и записать в одну ячейку, как в "Трюк Excel 16. ВПР для множества значений (без использования функции ВПР)"?