Динамическая выборка функциями ИНДЕКС и ПОИСКПОЗ

Как использовать функции ИНДЕКС и ПОИСКПОЗ (INDEX и MATCH) для извлечения нужных данных из списка вместо классической функции ВПР (VLOOKUP). Подробная статья и файл-пример planetaexcel.ru...
Заходите в гости ↓↓↓
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Мои книги planetaexcel.ru...
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru...

[@НиколайНиколаевич-ц2м4б]

Просто и профессионально подано и разъяснено! Все просто так КАК НАДО!!! Вообщем лучшие уроки! Спасибо!))

[@Rgddjji]

Очень полезно,в калькуляциях постоянно пользовался только ВПРом, данный урок дал большую гибкость в работе с БД. Огромное спасибо, вы меняете жизнь к лучшему!

[@userYT.1]

Все началось с того, что я устала делать листы ознакомления для каждого объекта свой, разный персонал. И все это в Ворде... Сделала в эксель, потом подумала почему бы не автоматизировать - выбрал, например, объект и притянуло персонал этого объекта. Стала искать обучалки. Теперь меня не оторвать от этого контента. Столько всего полезного сделала, не только примитивные листы ознакомления. Спасибо за то, что бесплатно делитесь знаниями. Да ещё так доходчиво.

[@КсенияЕрмолаева-е9с]

Как же я рада что нашла Ваш канал, это лучшие уроки за всю мою жизнь, ну честное слово!

[@ОлегКлёпов-ь6ф]

Николай,спасибо!Вы достаточно понятно объясняете,что является редкостью в интернете.Всегда смотрю Ваши видеоуроки.

[@KetevanKhokhlova]

Мой респект Вам. Мне, чайнику, удалось решить мои проблемы лучше чем ожидалось. Теперь у меня отличная программа. У вас редкий дар преподавания. Было бы здорово, если бы сделали новый урок по созданию вводных форм в excel

[@arzuakhmatova1266]

Николай,спасибо огромное. Очень практично,наглядно и доступно объясняете!

[@rapid861]

Периодически, по мере необходимости, просматриваю ваши уроки (примеры) на сайте, практически всегда всё понятно и доступно Вами поясняется. Большое Вам спасибо, за ваш труд!

[@kirilsmorodin402]

Спасибо огромное, Николай! Всё, что я давно искал - в одном уроке.

[@wowwow912]

Николай спасибо за ваши уроки! Очень доступно и просто.....

[@foe839]

Какое прекрасное видимо!!! Как я давно его искала

[@MrLinael]

Спасибо вам. Воплотил желаемое по итогам просмотра вашего видео.

[@ВасилийФомин-о5э]

Спасибо за видео. Кстати думаю удобнее реализовывать подобное при помощи тех же функций ПОИСКПОЗ и ИНДЕКС, но в другом порядке и вместе. Сначала ИНДЕКС, потом ПОИСКПОЗ. Что-то типа такого =ИНДЕКС(Таблица2[Фамилия];ПОИСКПОЗ(8985;Таблица2[Артикул];0)). Это как в видео про третий вариант зависимых списков. Тогда и данные будут автоматом вноситься в выпадающий список - то же плюс. Еще одним недостатком этого метода является то, что ПОИСКПОЗ в вашем случае ищет номер строки, а не реальный номер клиента (это просто здесь так совпало, что номер строки=номеру клиента), а вдруг одного клиента удалят??? тогда сместятся все клиенты на единицу. А так все равно спасибо огромное. Спустя две недели обучению Excel уже начинаю активно разбираться в тонкостях!!!

[@irinatsoy6494]

Николай, спасибо большое!

[@mayil66]

Когда знаешь -Все просто ! Вот это верно )) Спасибо )) Супер!

[@Elenakruzhilina]

Мне понравилась и манера речи и голос очень приятный, что редкость :)

[@ruslangavrilov7514]

Как всегда Спасибо за отличное видео!

[@Albertus99]

Спасибо Маэстро!

[@ЕвгенийСоломатов-ь3щ]

Спасибо, Николай.

[@ОльгаСеребренникова-р9я]

Спасибо за хороший пример! Все понятно!

[@Віталік-в8р]

Супер!!!Все уроки супер!!!

[@sergatolmachff4805]

Именно то что искал, спасибо!

[@ДмитрийЕмельянов-в2з6с]

ужас как раньше извращались)) а мне до сих пор попадаются люди, которые утверждают, что разницы между современным офисом и 2003 принципиальной нет =DD xlookup forever 😎😎😎

[@user-em9md2nv4x]

А ни чего и не изменилось. До сих пор можно рисовать таблицы в Word'е. 😁

[@sabitdzaymytbaev9848]

Спасибо! очень помогло, побольше уроков

[@КрасноеиЧерное]

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

[@yurd563]

Очень познавательно. Спасибо!

[@Ulia_Ya]

согласна на все 1000% спасибо!!

[@lady__fox9482]

Николай, сколько смотрела видео только на ваш голос усваивается инфа, на всех остальных переключаю на второй минуте. Спасибо

[@Souren1974]

Спасибо. Мне понравился материал.

[@planetaexcel]

Ну, скорость усвоения у всех разная. Некоторые, вон, жалуются, что слишком резво :)

[@lika5315]

Спасибо! Все получилось!

[@alegorets1]

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

[@user-alexrotten]

Классно! Спасибо!

[@LAMM12345]

На 9:12 можно было сделать еще изящнее. У вас на D16, D17 и D18 уже написаны названия столбцов. Надо только вычислить их порядковый номер в таблице, вернее в строке 1, добавить это вычисление в формулу и тогда нет необходимости добавлять столбец F16:F18. Как это вы не заметили? Напишите, пожалуйста, как бы вы это вычислили.

[@lelyakustova1273]

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

[@АндрейКостюхин-п8ш]

Здравствуйте как в экселе расчет квартиры по этажности Спасибо!

[@maksutabdulkhassanov2879]

круто!!!. Мне тоже понравился.

[@dima68196]

Николай, можете сделать обзор на функцию get distance?

[@planetaexcel]

Первый раз про неё слышу :)

[@anastassiyayelinaguzik4214]

респект и большое спасибо

[@valentinaklassen445]

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

[@РоманМ-ч2т]

Николай, спасибо! Как продолжить массив с автозаменой итоговых значений, или нужно прописывать его вручную?

[@AzizIbragimov]

Спасибо. Круто

[@12353083]

Добрый день. А как можно использовать функцию поиск поз, если в ячейке задан интервал? Например, есть 3 строки с значением в ячейке 1 -3 3 - 5 5 -7 как прописать функцию, если искомое значение будет равно 4? то есть если искомое значение равно 4, то функция определяет - 2 строку

[@LLIaiTaH_B_Tpycax]

А как перенести ненулевые значения на другой лист при помощи формулы, учитывая, что на исходнике нужны пустые строки?

[@olatera]

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

[@igrikum]

Здравствуйте, интересует подробнее [тип совпадения], такая ситуация: Искомое значение ЛЮБОЕ ЦЕЛОЕ ЧИСЛО, просматриваемый массив, к примеру, 25-50-100-150-200-300-400-600-800-1000, и функция выбирает только наименьшее подходящее, например, искомое значение 148 и функция подбирает из массива 100, вместо более подходящего 150. Как задать параметры ,что бы функция искала позицию наиболее близкую по значению??? Спасибо

[@EricHichhok]

Добрый день! Как сделать чтобы впр брал в качестве искомого значения сразу две ячейки? Грубо говоря, хочу, чтобы ВПР искал по двум параметрам сразу и выводил одно итоговое значение.

[@yarFatal]

Хороший вопрос)

[@Победительпожизни-щ2д]

Можно ли сделать поиск, который будет реагировать на часть введенного текста? условно имеется список с 20 000 значениями и при вводе в ячейку фрагмента слова, например "ук", список сузится до имеющихся значений "Уксус", "Укус", "услуги" ???

[@planetaexcel]

Легко :) www.planetaexcel.ru/techniques/1/9645/

[@riwwwqb]

Большое спасибо! Николай, подскажите, пожалуйста, как изменить формулу, если по выбранному из списка значению имеется множественный результат (причём заранее не известно, сколько строк подойдут).

[@planetaexcel]

+Rita Burina Тогда нужна другая техника - см. видео про Мульти-ВПР kzbin.info/www/bejne/aafLi5Z8a9R_Zqc

[@nurzat83]

а как сделать так, чтобы источник, на которую ссылается ВПР, менялся автоматически в зависимости от значения в ячейки? допустим,на главном листе у меня есть список магазинов и отдельно каждый магазин на отдельном листе. нужно подставить к каждому магазину итог продаж за месяц. проблема в том, что у каждого магазина данные с продажами за месяц в разных местах. заранее спасибо!

[@Ulia_Ya]

темп, как раз то, что нужно) нам, новичкам, и так тяжело) вот, например, для чего это видео, я так и не поняла....итог поиска - все данные из одной строки.....почему нельзя просто использовать стандартные ctrl+f...

[@vald390]

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

[@planetaexcel]

+Val D Вам нужна Мульти-ВПР kzbin.info/www/bejne/aafLi5Z8a9R_Zqc

[@СпециальныйагентСтивенс-ФБР]

Если в таблице извлекаемых данных есть пустые ячейки формула выдаёт значение 0, как сделать что бы формула выдавала так же пустую ячейку?

[@dmitryf.8325]

Добрый день. Николай, что можно сделать, если в массиве артикул (применительно данного видео) встречается несколько раз и нужно выбрать именно последний т.к. есть даты. Я пробовал и ВПР и ИНДЕКС и ПОИСКПОЗ, ничего не помогает. данные идут не по-порядку. Спасибо.

[@воскресныйпапа-и8м]

ВПР почему то работает не всегда точно: в одной и той же таблице, в одном и том же поисковом столбце, распознает искомое значение и выдает результат нотолько для 70-80% при последнем аргументе=1, а все остальные при тех же условиях- дает ошибку, в оставшихся же 20-30% искомых значений, ввдает правильный результат только при последнем аргументе функции =0! Иначе выдает н#д.... Как это понять?! Приходится после работы функции, во всех ячейках где "н#д" менять значение аргумента на противоположный. И все работает.. что это может быть?

[@planetaexcel]

:)

[@DennisBelarus]

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

[@planetaexcel]

+Денис Александрович Да, но не всегда можно менять местами столбцы (файл защищен, в общем доступе и т.д.)

[@aleksandrchernega9943]

Пытаюсь перенести данные из таблички на другой лист (в бланк) по этой методике, но на рубеже, где у Вас "имя клиента" - он мне пишет #перенос

[@MrNoubi]

а можно сделать так чтобы искался не весь артикул , а только первые две или 3 цифры ?

[@МихаилРотенберг-р2э]

А есть возможность выполнить мне одну задачу с индексом, поископоз, строка? 4 года назад я это реализовал, а теперь туплю. то ли корона, то ли просто деменция)))

[@Milka59]

Николай, у меня огромная таблица, мне подходит данная функция, но данные для заполнения находятся на разных листах и в разных файлах. Возможно ли дополнение формулы или нужно применить другую?

[@yuriytheone]

Блять ну напиши на VBA... Чё ты тупишь?

[@aydincafarov814]

спасибо

[@yulyach1446]

Спасибо. А если под одинаковым артиклом встречаются два и более разных клиента как это отразится в формуле? Очень нужно!!!. Спасибо

[@finist6749]

Добрый день! Если еще актуально - такую ситуацию разобрал в своем видео "Как настроить ИНДЕКС с Повторяющимися значениями в Excel"

[@sergeyflyhead8713]

Спасибо...

[@ВолковАнтон-ь9п]

Ну конечно, в Сургуте самые богатые клиенты))))

[@ДмитрийСкляренко-щ9г]

Здравствуйте. Помогите пожалуйста, как можно в Excel реализовать продвинутую формулу через ИНДЕКС и ПОИСКПОЗ с многократно повторяющейся формулой на 100 строк вниз, и которая при расчетах динамически ссылается на n=20 следующих строк вниз (n- задается в отдельной ячейке)? Привожу пример такой формулы набранной вручную: =(ABS(G3-F3)+ABS(G3-F4)+ABS(G3-F5)+ABS(G3-F6)+ABS(G3-F7)+ABS(G3-F8)+ABS(G3-F9)+ABS(G3-F10)+ABS(G3-F11)+ABS(G3-F12)+ABS(G3-F13)+ABS(G3-F14)+ABS(G3-F15)+ABS(G3-F16)+ABS(G3-F17)+ABS(G3-F18)+ABS(G3-F19)+ABS(G3-F20)+ABS(G3-F21)+ABS(G3-F22))/20

[@ДмитрийСкляренко-щ9г]

=СРОТКЛ(СМЕЩ(M7;0;0;N$3;1)).

[@АнсарСерикбаев-б4н]

Здравствуйте Николай. А здесь можно ли было использовать диспетчер данных на случай того, что таблица будет расти?

[@planetaexcel]

А что такое "диспетчер данных"? Не очень понял вопрос :)

[@АнсарСерикбаев-б4н]

@@planetaexcel именуемый диапазон, чтобы при написании формулы ссылаться на него. На случай, когда таблица будет увеличиваться

[@mariavinogradova1083]

Добрый день! Подскажите как можно использовать функцию Индекс и Поискпоз одновременно с сумм. Задача: необходимо не только найти данные из нужной строки и нужной колонки, но еще и суиммировать данные из строк с одинаковым наименованием. такой вариант не работает =СУММ(ИНДЕКС($D$4:$EF$497;ПОИСКПОЗ($B307;$C$4:$C$497;0);ПОИСКПОЗ(K$3;$D$1:$EF$1;0)))

[@СамыйКрасивый-с2г]

Здравствуйте! НЕ подскажите через какую формулу можно осуществить решение такой задачи. Если в первую столбец вводим Название товара, во второй его кол-во, то формула должна будет из таблицы выводит сумму этого товара на данное кол-во. Цена товара на количества не линейная. Спасибо

[@СамыйКрасивый-с2г]

А все. Разобрался. Индекс+ 2 поискпоз. Ну и иф эррор в началае, чтоб нд не вылетало

[@turqayceferov5016]

А можно ли в АРТИКУЛ-е для выпад списка использовать ACTİVEX?

[@planetaexcel]

Можно, почему нет? Только выпадающий список ActiveX - это отдельный графический элемент, его надо привязывать к какой-нибудь ячейке. И уже на неё ссылаться в формуле потом.

[@turqayceferov5016]

@@planetaexcel Спасибо за внимание

[@ЯнЧагинов]

Николай, день добрый, выше в видео все реализовано для таблицы которую не расширяют. Скажите как сделать чтобы с добавлением в таблицу новых "артикулов" список "проверки данных" автоматически дополнялся?

[@ВасилийФомин-о5э]

+Ян Чагинов Для этого надо создать умную таблицу. Кликнуть на главной "Форматировать как таблицу" и выбрать любой шаблон, а в окне Проверка данных в Источнике выбрать заголовок таблицы(юлиже к верху ячейки с заголовком) (там такая черная стрелка направленная острием вниз появится). После если захочешь добавить новые артикулы, они автоматом добавляются в выпадающий список. Таблица то умная!!!

[@andrersittodron8471]

У меня не работает эта формула в с 5 по 21 строку, не могу понять почему. Пишет #ЗНАЧ.. Подскажите в чем может быть проблема.

[@aiperikurmanbaeva4543]

Могу ли я скачать себе это видео?

[@adastra79]

Восемь лет спустя все равно это элегантное решение

[@MrNoubi]

А еще чтобы количество строк с таким артикулом в отдельной ячейке высчитывал

[@planetaexcel]

Найдет первого в списке

[@ИльнурИльясов-и5ю]

А когда не знаешь и находишься в процессе познания ой как тяжело...)

[@assa777alex]

Очень полезно, но хотелось бы качество видео повыше и темп рассказа побыстрее. И без озвучки очевидных действий типа: "И наааажииимаааееемм кноопппккууу ООООККеейй" Реально утомляет!

[@StepanovIhorPanorama]

А как мне найти клиента если он повторяется несколько раз? И все результаты показать у виде списка… Помогите не могу сделать:(

[@StepanovIhorPanorama]

Идентификатор клиента - это эго мобильный и он моден быть несколько раз

[@planetaexcel]

Посмотрите у меня видео про Мульти-ВПР

[@StepanovIhorPanorama]

@@planetaexcel я вам написал у фейсбуке, отпишите мне, у меня не получилось

[@StepanovIhorPanorama]

@@planetaexcel все получилось))) спасибо..!

[@dkonstruktor]

"просто" - проще было арт. сделать 2м столбцом и ВПР повешать.. два клика мышкой.

[@АлександрМошкин-ш5э]

а еще проще на первую строчку повесить фильтр и искать по артикулу нужную строку

[@Livan_1983]

Николай, слушай Павел проще объяснил у него более понятно.

[@Evgenii-Ivanov]

+

[@MyGlamour12]

(y)

[@vstolice]

а как сделать что если артикулы одинаковые и чтобы выводил все похожие. допустим артикул 9604, номер 5, Тарасов, Сургут. НО есть такой же артикул,но не Тарасов,а Иванов, а в итоге вывел итог ещё и на Иванова рядом.

[@vstolice]

все, спасибо не надо, просто убираем из формулы 0 и все.

[@Виталий-ь8м7в]

5:14 - просматриваемый массив должен быть ОДНОМЕРНЫМ = или строкой, или столбцом .... БЛИИИН, у меня массив из 16'000 столбцов и 98 строк ... и КАК быть ??? какая ж тогда функция ищет В ДАПАЗОНЕ МАССИВА ????!!!

[@LeXa840xxx]

Очень помогло, спасибо громное!

[@yuriytheone]

Судя по твоей аве тебе уже ничего не поможет...

[@yerkanatsharipov1053]

Все классно, но при функции поискоз, почему показывается строка 2? По какому принципу считает эксель?

[@AskoLd1ee]

ПоискПоз + Индекс = замена ВПР, понравилось :)

[@dianakopaihora7760]

внятная речь, доступно, оптимистичная фраза в конце

[@planetaexcel]

+Диана Копайгора "оптимистичная фраза в конце " - улыбнуло :)

[@gallardab5774]

Здорово! А как соединить несколько таблиц в одну, где в одном столбце фамилии, а в другом числа? Но фамилии могут повторяться и нужно повторы вместе с числами объединить?

[@АлександрМоин-я7м]

может мне кто поможет......... из нескольких таблиц нужно извлекать данные на отдельный лист. Могу скинуть табличку моет кто помоет. Заранее спасибо........

[@planetaexcel]

Вам помогут сводные таблицы - см. www.planetaexcel.ru/techniques/8/130/