Поиск и подстановка по 2 критериям (ВПР по 2 столбцам)

Три способа искать и подставлять данные в Excel по двум и более критериям, т.е. реализовать ВПР (VLOOKUP) по нескольким столбцам. Скачать пример planetaexcel.ru/techniques/2/224/
Заходите в гости ↓↓↓
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Мои книги planetaexcel.ru/books/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/

[@user-wf3xm6zs7r]

🤘😝🤘Фантастика! Когда написала в нужной мне таблице эту формулу, чуть до потолка не подпрыгнула. Спасибо Вам, Николай, что столько времени тратите на видео уроки! ❤всё не только просто, но и с большой пользой!

[@Andrew_3]

Вы волшебник! Только у вас нашел то что искал. Последняя формула очень крутая.

[@1raskumar]

Двойной щелчок для протягивания значений-вот это номер.ну Николай!ну ваще.А фишка с суммой всех одинаковых ячеек это вообще космос.

[@vs_gaming3013]

Благодарю! Пожалуй, лучший материал по экселю во всем KZbin

[@shokirboboev4023]

Как всегда Николай все коротко, четко, понятно и просто!!! ОГРОМНЫЙ РЕСПЕКТ ВАМ!

[@user-wj9ih4vj4x]

Спасибо огромное, Николай!!! Вы тренер высокого класса!!!!

[@eugenelugovoi1313]

Спасибо, помогло. Кстати, о последнем способе. Представьте что соответствие по 2-м (и более) критериям нужно находить не для одной строки (как в примере), а для списка товаров из 10к+ позиций и соответствие искать в списке из 100к+ строк. При больших объемах данных тормозить будет именно динамическая склейка столбцов, из-за которой кстати и приходится применять ctrl+shift+enter. Если нужные столбцы (2-й параметр ПОИСКПОЗ/MATCH) "склеить" в отдельный и указать его в формуле вместо склейки, то скорость будет намного выше. То есть получается такой комбинированный подход.

[@user-xe5vd2wu1v]

Узнала много полезно, начиная с азов, наверное : "протягиваем весь столбик двойным щелчком по крестику" . Да эта ваша фраза, мне часы времени сэкономит!!! И это только начало видео. Крутяк. Спасибо!

[@user-hs1of2zb1k]

я от Вашей подачи просто в восторге :) спасибо большое!

[@DILSHOD.SHUKUROV]

Николай, спасибо вам большое за тренинг. Удачи вам в жизни и в работе!!!

[@lubimec007]

Как же я рад этому видео! Спасибо большое!!!

[@marinar7458]

Все понятно! без воды...Спасибо огромное!!!

[@mariden5810]

Спасибо. Всё чётко. Доходчиво и без лишних слов.

[@user-px2gj9tk8m]

Николай, огромное вам спасибо!!! все ясно и понятно.

[@dimk7983]

Спасибо, коротко и всё понятно. Альт038 - & без переключения раскладки на английскую

[@jiakellerman]

Предельно понятно все изложено! Огромная благодарность автору!

[@user-mo6hh8ss4g]

Спасибо большое! Очень полезное видео доступным языком с понятной логикой!

[@AlexRTW]

Как всегда, простое шикарное объяснение

[@user-tm6ub8ne8n]

Николай, благодарю за информацию 😊Очень полезно!

[@user-qd7ov8oy1b]

Огромное спасибо! Десятки просмотренных видео и на конец то получилось.

[@gel1603]

Давно искал "двойной ВПР", спасибо за ролик))

[@kitaiskiigorod]

Спасибо большое!!! Обычным ВПР пользовался, но вот не мог такую задачу сделать.

[@lainel888]

Николай, от души спасибо!

[@bimbom1982]

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

[@user-uo1mx5sj4q]

Спасибо за видео!

[@Abdulhakim1114]

Очень полезно для меня СПАСИБО ОГРОМНОЕ!

[@kuatle]

1:36 Способ 1. ВПР и дополнительный столбец склейки 4:54 Способ 2. Выборочное суммирование с помощью СУМЕСЛИММН(), только для уникальных сочетаний критериев 7:19 Способ 3. Формула массива с помощью {ИНДЕКС(...;ПОИСКПОЗ())}

[@user-df3vr2he3n]

Гений! 4 дня парился над этим) спасибо!

[@goldenlady004]

Спасибо большое, все просто и понятно!

[@TheSANHOP]

Огромное спасибо за ролик) решил рабочую доп. задачу на квартал)

[@278Elena]

Учитель вы от Бога!))

[@user-tq7tl4nk2d]

Спасибо, все понятно и интересно!

[@ersultan90]

все просто и понятно, огромное спасибо!

[@EricHichhok]

Очень помогли этим видео! Большое спасибо!

[@abidinalibekov8124]

Спасибо Вам, очень помог третий вариант) P.S. диз поставили те кто часами с затекшей спиной прописывают в каждой ячейке =если(И(А1=B2;C1=D2);ИСТИНА;.....…...

[@sergeya.419]

Отличное решене! Спасибо!

[@user-oz1ku3in9o]

Идеально мне для работы! Спасибо!

[@user-mr6qi3xn5f]

Огромнейшее спасибо!

[@serhiiivanov5530]

Круто! Очень полезно! Спасибо

[@piuttostoegoista9094]

Чтобы я делала без этого видео,большое спасибо!

[@irynamitceva5280]

Дякую!!! Чому я раніше вашого відео не подивилася, цілий день потратила на формулу

[@narekhovanesyan1643]

Спасибо вам !

[@stmsolution]

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

[@juliashar6781]

Спасибо, все отлично работает!

[@user-fi5hq8tu5f]

Можно попросить вас рассказать и показать попроще о создании сайта на основе обновляемых данных файла (можно дашборда) Excel-какие есть варианты таблиц на сайтах и как потом "эксплуатировать" сайт, модерировать комменты и что-то еще, сколько времени, людей и денег может на это уйти. Можно рассмотреть сайты продаж авто по годам и странам (страна-страница). Также интересно можно ли сделать кнопки-крестики для группировки строк-столбцов в таблице сайта. Удачи и спасибо за видео!!!!!

[@user-eb4gw1ho2j]

жоско, спасибо, твоим видосом и гпт3.5 смог собрать это херабору в гуглтаблицу =ARRAYFORMULA(IFERROR(INDEX(INDIRECT("Total_af_blogger_monthly!P2:P"); MATCH(A2&B2; INDIRECT("Total_af_blogger_monthly!A2:A")&INDIRECT("Total_af_blogger_monthly!B2:B"); 0)); 0+0)) а задача то простая была, просто чекнуть совпадение 4 столбцов между собой (по 2 на каждом из 2 листов) и выдать соответствующее значение из 3его столбца

[@user-gy3of1tn9c]

Большое Вам спасибо!

[@chanelKartmen]

Очень помогли ! Смотрю это видео из 2021 года

[@gennadiikundalevich7764]

Наверное Вы и сами знаете, но нашёл в power query, что при объединении запросов можно выбрать несколько столбцов и получится "впр по 2 критериям" или больше.

[@olegstrek8904]

Николай, спасибо. Нужно было сделать матрицу товарного запаса по точкам и товарам исходя из истории продаж, никак не мог придумать, помогла функция СУММАЕСЛИМН. P.S. Книгу Вашу уже купил

[@user-hs1of2zb1k]

здравствуйте, поделитесь своим примером, пожалуйста 😇

[@dmitryivanov3200]

Просто супер!!!

[@user-pr2np8ng1i]

Огромное спасибо!

[@yurd563]

Спасибо!

[@user-zv6yb3mz6o]

Очень красиво!) Спасибо. А если например, есть таблица, в которой заголовки столбцов - даты, строки - это конечный набор из 10-20 строчек, и нужно просуммировать все значения в ячейках по каждому месяцу? Даты-названия столбцов иду по порядку

[@user-bh6qf1wh1j]

Спасибо, решила свою задачу! Сама не могла сообразить))

[@springtrap4140]

круто В столбце «Состояние» теперь всегда отображается причина сбоя. Раньше нужно было включить check_box Большие строки запроса или выбрать запрос в таблице.

[@natatata2997]

Спасибо!очень помогли!

[@TheBlackCat__]

Спасибо большое!

[@user-im8zr2dy4s]

Вы лучший!!!

[@user-oy1ts1rr3q]

Замечательные уроки, поставлена речь, без эээ-ммм и паразитов, спасибо за высокий уровень организации! Николай, вопрос (не смогла найти в уроках) как вернуть данные из нескольких столбцов сразу, не из одного.

[@planetaexcel]

Спасибо, Алла :) Если нужно сразу несколько столбцов, то либо использовать несколько подобных формул (как в этом видео), либо использовать новую функцию ФИЛЬТР (только в Office 365).

[@jiakellerman]

Отличное видео! Очен

[@pv4096]

Спасибо огромное!!!

[@user-yh2gy2cz4i]

спасибо за видео

[@userdpua]

Первое, что пришло в голову, можно просто перемножить массивы, заключив их в обычные круглые скобки вместе с условием: =(B2:B99=условие1) * (C2:C99=условие2) * (D2:D99). Будет найдено первое совпадение. А если поместить эту конструкцию в функцию СУММПРОИЗВ, все совпадения будут просуммированы. Интересно было бы услышать о недостатках такого способа. По работе приходилось сталкиваться с задачами, решение которых одной формулой без предрасчетов удавалось выполнить только так.

[@Voronza]

Андрей Юрич мне тоже было бы интересно узнать о возможных недостатках такого подхода с СУММПРОИЗВ. Также предложу способ номер 2 доработать до формы: =СУММЕСЛИМН(C2:C99;A2:A99;условие1;B2:B99;условие2)/СЧЁТЕСЛИМН(A2:A99;условие1;B2:B99;условие2) здесь используются 2 встроенные функции, которые оптимизированы в Excel на уровне машинного кода, и только одна алгебраическая операция вычисления. Защита от задубливания записей.

[@user-ku2je2yq5b]

СПАСИБО ВАМ ОГРОМНОЕЕЕЕЕЕЕ!!!!!🎉🎉🎉

[@1451262]

спасибо, выручили))

[@3250095]

гениально!

[@user-gw2lg9ff4i]

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

[@user-xp4zp3cm6b]

Формулу массива можно использовать и без предварительного форматирования в умную таблицу. Все точно так же работает.

[@agentbzoommzz7568]

Круто...

[@user-rl4pz8rw1i]

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

[@user-du3cm9oj4v]

Подскажите, как можно связать две таблицы по диапазону даты и времени? в Вашем примере указан месяц, а у меня конкретное время нужно найти в диапазоне 1 часа

[@avadim2606]

Здравствуйте, есть вопрос. Дано таблица с наименованием товаров и ценами на них (по горизонтали в шапке) И колонками покупателей, которых может быть неограниченное количество в т.ч. повторяющиеся В колонках ставят количество купленного товара (1,2,3 и т.д.) Требуется найти сумму покупки по горизонтальной строке с учётом количества и его стоимости

[@user-tm4gl2nj9c]

Привет Николай помоги мне узнать что такое индекс карты?

[@andriytest4599]

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

[@finist6147]

Здравствуйте Николай! Как с Вами можно связаться в личке? Нужна помощь в создании документа эксель.

[@dushmandushman3541]

как в екселе подогнать одно число к другому? например в одном столбце 36,00,ее нужно подогнать к числу 56,00 в другом столбце. чтоб получилось к примеру 56,00в 1 столбике 56,00 во 2 столбике. до сотых долей или до минимального приближенного значения. или формулу какую то в третий столбец чтоб копирнуть потом

[@user-xg5yw8jf4k]

Николай, во-первых, хочу тебя поблагодарить! Объясняешь всё очень толково и доступно. И решение своих вопросов я нахожу, как правило, только в твоих видео. Спасибо! Во-вторых, прошу помочь. С помощью видео выше я таки смог найти значение по 3 критериям поиска (многомерная таблица: столбец - год закупки и категория товара, строка - направление товара). Это у меня получилось сделать в одной ячейке. Но мне нужно протянуть эту формулу вниз и влево, чтобы она заработала в других годах/категориях/направлениях. Абсолютными ссылками пользуюсь, но результата нет. Хотя перепроверяю всё до мелочей, должно получаться, а не выходит. Что посоветуешь?

[@Rice8520]

kzbin.info/www/bejne/h6bEfWucoKtoqKM

[@user-re7zi8mo8d]

Николай, здравствуйте. Нужен Ваш совет или помощь в подборе формулы. Пример: рулон марли длинной 2,7 м стоит 100 руб. Если продаётся 1 м, то стоимость должна составлять 100 руб. Если продается 3 м, то стоимость должна составлять 100*2 = 200 руб Т.е. нужно продавать марлю кратно 2,7 м. Я попробовала применить формулу ОКРУГЛТ, но она считает только от половинного метража (от 1,35 м) и от 0,1 до 1,35 м мы продать не можем. Какую формулу лучше применить?

[@user-yq4qe4zh1q]

вот ещё вариант, в справочнике создается колонка, а что ищем просто скрепляем в формуле, в моём случае выглядит так ВПР(D3&D5&D6;Упаковка!A2:E37;5;ЛОЖЬ)

[@user-ft1tx1sz3m]

Несколько лет я тратил часы для ручного счета, теперь минуты😁спасибо

[@user-qf9rc3cf1x]

Подскажите, как сделать постановку цены не по 2 параметрам, а по 4? 🥲

[@user-es6xx4ty7j]

Здравствуйте. Можете ли подсказать. В инете нашел формулу, которая сравнивает две текстовые ячейки на момент различия. (КОД =ПСТР(B4;1;СУММ(--(ПСТР(B4;СТРОКА(ДВССЫЛ("1:"&МИН(ДЛСТР(B4);ДЛСТР(C4))));1)=ПСТР(C4;СТРОКА(ДВССЫЛ("1:"&МИН(ДЛСТР(B4);ДЛСТР(C4))));1)))+30)&" - "&ПСТР(C4;1;СУММ(--(ПСТР(B4;СТРОКА(ДВССЫЛ("1:"&МИН(ДЛСТР(B4);ДЛСТР(C4))));1)=ПСТР(C4;СТРОКА(ДВССЫЛ("1:"&МИН(ДЛСТР(B4);ДЛСТР(C4))));1)))+30) Результат = Текс первой ячейки - Текс второй ячейки Мне нужно чтобы, если нет разницы, в Результате = пусто или текст "Нет изменений". Буду признателен за помощь.

[@kselnaga7303]

здравствуйте, а если надо получить список например учащихся, по классам, ведь ВПР получает только первое значение, а надо получить еще дальнейшее значение ФИО из одного и того же класса, т.е. все ФИО например из 1а класса

[@timuru90]

Супер супер.

[@user-zj4xn7bd1x]

Николай, как снять циклические

[@user-ms9bk9om6d]

Добрый день Николай. А как применить формулу индекс и поискпоз для списка? Каждую позицию номенклатур (более 200) склеивать с месяцем не вариант(

[@MiRaysgift]

Всем привет. Возможно это сообщение кому-то поможет. Данные формулы используются для операций 3 к 2 ( столбцам), в случаях когда нужна операция 2 к 2, необходимо привести к такой формуле : - ВПР(D11;Q10:R13;2;0) - сравниваем ячейку ( искомую) из таблицы , выводим из строки найденной 2 столбец, точный поиск) - СУММЕСЛИМН(R10:R14;Q10:Q14;D11) - суммируем результаты из искомого столбца ,при сравнении столбца с ячейкой - ИНДЕКС(R10:R14;ПОИСКПОЗ(Q10:Q14;D11;0)) -аналогично

[@navar4ik]

Спасибо большое, но есть нюанс! При переключении товара или месяца, цена не меняется автоматически, как решить эту проблему?

[@Mirat_Muzafarov]

Добрый день. Вопрос к знатокам Excel)) Как можно объединить 2 таблицы отличающиеся по одному столбцу? Например есть таблица с столбцами Дата, Цена, Товар, Поставщик и таблица с столбцами Дата, Цена, Товар и Количество. И мне нужно узнать какой поставщик какое количество оформил. Итоговая таблица с столбцами Дата, Цена, Товар, Поставщик, Количество. Как можно реализовать, чтобы проверялись все столбцы и данные были корректные? Спасибо)

[@user-oj9th4ir2t]

Здравствуйте! Помогите решить проблему. Использую РАНГ для определения мест спортсменов. Всё работает отлично, расставляет места и дублирует места при одинаковых результатах, но есть два нюанса: 1. Как использовать эту функцию если у двух или более участников РАНГ выводит одинаковые места, и нужно чтобы в случае одинаковых мест учитывалась колонка коэффициента но только для тех у кого одинаковые места. 2. Как использовать Ранг для определения мест результатов бегов. Например такие - 1.12,35 (минуты. секунды, сотые секунды) где после первого знака точка а после третьего запятая (именно такие знаки). РАНГ не понимает такого формата записи. А мне нужно чтобы понимал.

[@balelcom8499]

Здравствуйте, проблема у меня такая при привязывании даты к числу у меня, получается не дата с числом, а кодовый номер даты с числом? Ничего не получается при попытке Форматирования даты.

[@balelcom8499]

Всё решил А2=СЦЕПИТЬ(ТЕКСТ(В2;"ДД.ММ.ГГГГ");В3)

[@Rice8520]

Дополнительно объединять искомые значения в отдельной ячейке (G3) нет необходимости. Это можно сделать прямо в формуле.

[@user-wc3mw6bu4n]

Простой пример ТарифыЖКХ, взять их из интернета (парсинг) и выбрать по нескольким условиям. Попробуйте.

[@tk-ih1ng]

Как вариант ко второму варианту с наличием дубликатов по двум параметрам, с использованием суммеслимн делённое на счётеслимн.

[@aynakarakhanova2431]

Последний способ очень хороший. Но в Вашем файле обе таблицы находятся на одном листе. А если нужно искать в другом файле? У меня не получилось. Есть ньюансы? Или Вы не отвечаете в этом форуме?

[@Rice8520]

Открываете нужный файл, указываете из него диапазон поиска, заканчиваете формулу и спокойно закрываете файл. У меня так во всех файлах длинные пути прописаны, ибо искать каждый раз по Ctrl+F не комильфо, когда у тебя сотни строк данных.

[@ibm.kostya]

как узнать меняющейся максимальное значение ячейки по дате?

[@Antagonopolis]

Доброго дня, Николай. Подскажите пожалуйста... Есть лист организованный в виде двух параллельных таблиц. В каждой по 5 столбцов (A,B,C,D,E и G,H,I,J,K) и пока около 1000 строк. В каждой ячейке записано не числовое значение. Пусть для примера это будут названия шахматных фигур [Пешка, Офицер, итд]. Как найти в таблице определённую последовательность ячеек и её повторения Например [Слон] [Пешка] [Ладья] [Конь] [Ферзь] и каким то образом получить результат [выделить цветом все совпадения или вывести все номера строк]? Буду рад любой помощи

[@planetaexcel]

Я бы добавил еще один столбец с формулой проверки типа: =ЕСЛИ(И(A1="Слон";B1="Пешка";С1="Ладья" и т.д.); 1; 0) И потом отфильтровал все ячейки с 1.

[@Antagonopolis]

@@planetaexcel Так по итогу и сделал вчера)) Однако же благодарю за помощь

[@Shukhrat.sharipov]

привет а нельзя ли место индекс прописать гпр и не использовать массив? ;-) например =гпр(с1;с:с;поискпоз итд

[@Shukhrat.sharipov]

и таким образом обманим гпр чтобы он показывал ту строку

[@Romari077]

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

[@planetaexcel]

Используйте функцию ВПР