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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

жоско, спасибо, твоим видосом и гпт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его столбца

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вы лучший!!!

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

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

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

Круто...

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

ангел хранитель

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

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

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

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

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

Спасибо!

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

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

Здравствуйте. Можете ли подсказать. В инете нашел формулу, которая сравнивает две текстовые ячейки на момент различия. (КОД =ПСТР(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) Результат = Текс первой ячейки - Текс второй ячейки Мне нужно чтобы, если нет разницы, в Результате = пусто или текст "Нет изменений". Буду признателен за помощь.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

гениально!

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

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

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

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

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

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

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

А как быть если мне нужно искать не точное совпадение, а промежуток чисел, например 10000-11000.

У меня формула со склеиванием не работает! Если делаю по одному столбцу - все хорошо! Склеивание - не работает ((( Когда запускаю поэтапное вычисление, то выдает #ЗНАЧ! при вычислении ПОИСКПОЗ() В чем может быть проблема?

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

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

Супер супер.

А как быть, если нужно найти значение в третьем столбце,по первому и(или) второму ,но по всей книге,а не на одном листе!?

Минуточку, а сортировать по алфавиту исходную таблицу для функции ВПР в Excel 2016 больше не нужно? А в 2013?

А как в одной ячейки ВПР можно вместить несколько дат

Есть надстройка Lookup. Советую

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

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

сопаставлять с помощью power query работать в разы приятнее

всё отлично, но мне необходимо найти сумму не первого повторяющегося значения, а ПОСЛЕДНЕГО... всё голову уже сломал. проблема в том, что функция индекс определяет только первое значение в списке...