31 Функции Excel ИНДЕКС и ПОИСКПОЗ (INDEX MATCH), как более гибкая альтернатива ВПР (VLOOKUP)

Связка функций Excel ИНДЕКС и ПОИСКПОЗ (на английском INDEX MATCH) является более гибкой заменой ВПР (VLOOKUP), т.к. позволяет производить поиск в столбцах слева от критерия. Файл с примером находится на странице 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

[@irinalabun3650]

Спасибо огромное за объяснение? Я никак не могла понять эту формулу, вы единственный, кто смог понятно для меня объяснить

[@tatina]

Большое спасибо! Без Вашего видео никак не могла разобраться с этими функциями❤

[@Afanasiy_Nikitin]

Очень полезное сочетание, спасибо, немного тяжелее на этапе понимания, но намного удобнее чем Vlookup

[@statanaliz]

Да, комбинация ИНДЕКС+ПОИСКПОЗ во многих ситуациях более удобна, чем ВПР.

[@navigante-789]

спасибо. очень полезно и понятно

[@Дмитрий-э9в8в]

Спасибо. Очень в доступной форме.

[@semyon__ykt]

Оо спасибо вам большое, выручили, так доходчиво объясняете, удачи!!!

[@ВладимирБузняков]

Дмитрий, Вы молодец!!! P.S. Понимаю, что это Вы знаете и без меня, но всё-таки...

[@statanaliz]

Спасибо за похвалу. Но еще есть, куда развиваться. После переезда сайта продолжу делать ролики, оставайтесь на связи.

[@alexeygavshin4297]

Спасибо огромное,очень доступно объяснили! Реально полезная функция.

[@german_novotiable]

Спасибо, 2.30 минуты уже понял как это использовать. А до этого смотрел, читал статьи на английском и не мог понять почему так лучше... Хотя скорее всего вы подвели так сказать conclusion и в голове сложился пазл.

[@алексейвидлога-б1о]

Выручили, большое спасибо!!!

[@blackbon196]

Спасибо вам большое. Вы мне очень помогли

[@ТОВПромкомерсгруп]

благодарю! все четко и понятно, а главное работает и удобно!

[@statanaliz]

Спасибо за отзыв. Старался )

[@МаринаАйкенова]

Спасибо! Ждем новые видео!

[@tigranhakobyan3077]

Спасибо!

[@statanaliz]

Не за что!

[@ЮрийКоржов-с3т]

Дмитрий удачи, спасибо

[@statanaliz]

Спасибо, взаимно, удачи )

[@АнастасияКожурина-я3о]

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

[@zhomagram]

Полезная формула. Часто использую ВПР при работе с базами данных, размером до 100 Мегабайт. Ваша формула =ЕслиОшибка поможет с ошибками #Н/Д .

[@ЭдуардЗвонарёв-л9щ]

Спасибо большое за видео, ооочень полезное! :)

[@Roman_Kabanov]

Спасибо! Учусь на ваших видео!

[@statanaliz]

Рад, если помогает! )

[@Albertus99]

Гениально спасибо!

[@statanaliz]

Спасибо!

[@islomeshmurodov5578]

Очень Понятное видео

[@statanaliz]

Спасибо за отзыв!

[@cyklonification]

Спасибо!

[@ppr8117]

Доброго времени суток, Дмитрий! С наступающими Вас праздниками! Спасибо за Ваши обучающие видео. А могли бы Вы, в перспективе, к Вашим видео урокам, прилагать файл-пример? Спасибо

[@statanaliz]

Добрый вечер. Спасибо, и Вас с наступающим. Да, уже просили. На сайте подвешу и укажу ссылку по роликом.

[@АйдайОмуракунова-п1р]

Спасибо Вам большое 🙏🏻

[@statanaliz]

Да пожалуйста )

[@ekaterinayurasova1601]

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

[@statanaliz]

Спасибо за отзыв.

[@islomeshmurodov5578]

Я шас етот видео исползивол проста круто . )))👏👏👏👏👏

[@statanaliz]

Пожалуйста. Спасибо за комментарий!

[@ДмитрийКоробкин-г3ш]

Спасибо. Помогло

[@aydaryakup4540]

Спасибо!

[@statanaliz]

Пожалуйста!

[@Calmasastone]

Thx mate

[@АлександрАрбузенков]

Интересно было посмотреть

[@statanaliz]

Благодарю за отзыв!

[@bukaev.m]

Спасибо за понятный урок. Очень помогли)

[@statanaliz]

Спасибо за отзыв!

[@Андрей-е1т3я]

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

[@dalero9925]

Красавчик

[@bekkul.zhumabekov]

Спасибо

[@ВикаИвлеева-ц7ч]

Спасибо, правда копирование формул индекс и поискпоз не получилось, если использовать ctrl c/ctrl v формула сбивается

[@ДмитрийМедведев-в2э]

Спасибо за простое и понятное объяснение. Можно у вас узнать какой программой вы пользуетесь при создании видео для рисования стрелок и прямоугольников?

[@statanaliz]

Пожалуйста. Программа - camtasia studio

[@piyoda]

good bro

[@ВалентинаВыборнова-л1в]

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

[@oleksiikucheriavykh2890]

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

[@statanaliz]

Вам надо в общей таблице сложить все операции по дням из других таблиц? Если да, то подойдет функция СУММЕСЛИ kzbin.info/www/bejne/bpjJkppvhtV9j7s

[@ЭммаГрицай-х9ш]

спасибо

[@Snapejke]

Приветствую. Огромное спасибо за Ваш труд. Смотря ваши видео у меня возник вопрос, а есть ли поиск аналогичный ВПР, но по буквам? Возникла такая необходимость найти 800 человек из 10000 по емэйл адресам.

[@statanaliz]

Не совсем понял. Все адреса записаны в одной ячейке? Опишите подробнее условие задачи.

[@Snapejke]

@@statanaliz Все адреса записаны в одной ячейке, все 800, а те 10000, среди которых нужно их отыскать, в другой ячейке, справа.

[@Snapejke]

Дмитрий спасибо большое за обратную связь. Я разобрался со своей проблемой с помощью ВПР. Допускал ошибку в диапазоне, поэтому формула не работала.

[@statanaliz]

@@Snapejke Я бы для начала разделил адреса на ячейки. Потом применил бы стандартные способы.

[@statanaliz]

@@Snapejke Ок, рад, если разобрались сами )

[@AnKl_Sam]

Дмитрий, а планируете сделать ролик о применении данной функции при работе с двумя листами?

[@statanaliz]

Уточните, пожалуйста, вопрос. Что на этих листах должно быть?

[@AnKl_Sam]

@@statanaliz , исходные данные. Но я уже и сам разобрался! Просто изначально пугали названия листов))

[@statanaliz]

@@AnKl_Sam Отлично, что разобрались. )

[@erkinovqudratilla1559]

если на 2ом таблице два капусты то как исправить ощибки

[@Оксана-ь5ч6л]

Здравствуйте. А если название из одной таблицы полное, а из другой нет. Например: 1. Молоко, 2. Молоко пастеризованное. Будет ли работать данная формула? С ВПР у меня не получается.

[@shamwej9233]

как в столбике I выводить текс формулы?

[@sergeyvakhranev6695]

А как правильно сделать поиск по 2-м разным критериям в одном столбце? Например в столбце 4 найти значения 20 и 35

[@НадеждаГушкан]

Добрый день. Подскажите,пожалуйста, в данном случае формула возвращает первую строку с искомым значением. Что делать если строк с таким значением несколько и результат надо просуммировать?

[@analuna3228]

Здравствуйте Дмитрий, Спасибо вам большое за курс, урок за уроком прохожу ваш курс. Узнала много нового! Скажите, пожалуйста, а как быть, если слева от ключевого поля находится более одной колонки? Как в таком случае указать номер колонки? На примере вашего же файла я добавила колонку E и F для второй таблицы (доллар / гривна)

[@statanaliz]

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

[@analuna3228]

@@statanaliz Все получилось! Спасибо Вам большое! Перехожу к следующему уроку!

[@_deatmoroz]

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

[@statanaliz]

Да, см.функцию СУММЕСЛИ

[@_deatmoroz]

@@statanaliz Спасибо, ее получается, можно вставить в эту общую формулу?

[@statanaliz]

@@_deatmoroz СУММЕСЛИ используется вместо всей этой конструкции из ролика.

[@_deatmoroz]

@@statanaliz Понятно, мне наверное лучше тогда этой формулой, воспользоваться после основной, у меня именно в правой колонке длинные повторяются

[@OkGo_0]

Спасибо теперь рубежный контроль на 95 балов сдам

[@azat_reisende]

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

[@statanaliz]

Добрый день. Попробуйте ВПР kzbin.info/www/bejne/pH-4qWporNJ_oNU.

[@igrikum]

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

[@ИрмаЛлл]

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

[@statanaliz]

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

[@alexeymaslov3370]

@@statanaliz Дмитрий, а можно ли использовать пользоваться этими функциями в случае, если столбец поиска является вариабельным значением? Например, в этом видео-примере выбирать столбец с месяцем по какому-то условию? Или это можно реализовать в Умной таблице?

[@АндрейШатунов-я2п]

Что значит "ссылки следует закрепить", и как? На видео не видно действий

[@statanaliz]

То есть сделать ссылки абсолютными с помощью вставки знака $ перед названием столбца и строки. Можно воспользоваться горячей клавишей F4.

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

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

[@statanaliz]

Примените функцию ЕСЛИ, либо скройте нули. Разные способы скрытия нулей показаны в уроке kzbin.info/www/bejne/l5_FdXmfltqcfbM

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

@@statanaliz Я не слишком силён в формулах, но на сколько мог применить: ЕСЛИ(ЕПУСТО и т.д. то скрыть нули получилось, но появилась другая проблема, попадая на заполненную ячейку формула выдаёт ИСТИНА, ЛОЖЬ, но никак не содержимое ячейки... в общем не получается пока.

[@statanaliz]

@@СпециальныйагентСтивенс-ФБР Долго объяснять словами)). Пришлите лучше файл с примером и образец решения мне на ящик. Адрес в описании канала.

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

@@statanaliz Разобрался, спасибо за помощь.

[@statanaliz]

@@СпециальныйагентСтивенс-ФБР Рад, если помог. Удачи! )

[@zzgiiiyywq]

У меня два разных файла. С большим объёмом . Но функция не работает. Выдаёт #знач! Что это за ошибка? Что значит неправильный тип данных?

[@bristolzoo845]

А можно ли указать аргумент - ячейку в которой a1&a2 ??)) Как это можно сделать?

[@statanaliz]

Не совсем понял. Нужно в качестве критерия склеить две ячейки? Можно, да.

[@bristolzoo845]

@@statanaliz а как - если не секрет?

[@bristolzoo845]

@@statanaliz для поиск поз во вложенном индексе ?

[@statanaliz]

@@bristolzoo845 Не секрет, но нужно смотреть на данные. Опишите точнее задачу или пришлите пример с образцом правильного решения мне на почту, я посмотрю. Адрес в описании канала.

[@statanaliz]

@@bristolzoo845 Надо на данные смотреть. Можете прислать пример с образцом правильного решения. Я посмотрю. Адрес в описании канала.

[@ДмитрийСкляренко-щ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

[@statanaliz]

Здравствуйте. А можно словами объяснить, что необходимо посчитать? Возможно, тут другой подход нужен.

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

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

[@LiaPlo]

Добрый день, подскажите, пож-та, как в столбце I отображается формула из столбцa H (min 2:03). Спасибо.

[@statanaliz]

Добрый день. Функция Ф.ТЕКСТ

[@Julialoves100]

Езепов Дмитрий Дмитрий, у меня так и не получилось!((( какой формат задавать? Нашла Ваши примеры, там показано на датах, а на формулах не понимаю((((

[@statanaliz]

@@Julialoves100 Это ж поиск. Причем тут форматы?

[@Julialoves100]

@@statanaliz я теперь еще сильнее не поняла)) могли бы Вы написать формулу, по которой в графе "формула" появляется собственно формула? простите за тавтологию.

[@statanaliz]

@@Julialoves100 зайдите по ссылке под роликом, попадаете на сайт, где можно скачать файл из этого урока.

[@piyoda]

go only go forward bro

[@Midavok]

Примечание: вместо функции ИНДЕКС можно использовать СМЕЩ.

[@statanaliz]

можно, но не нужно. СМЕЩ - летучая функция, т.е. постоянно пересчитывается. Может затормозить работу файла.

[@АлексейСоков-ь8и]

2022

[@ФархатА-к5я]

Здравствуйте. Подскажите пожалуйста как сделать ВПР ссылкой? Например есть 2 большие таблицы на одной или нескольких страницах в экселе. Необходимо найти код из первой в таблице во второй (первая таблица ТМЗ например, а вторая - это заявитель). Чтоб посмотрев на товар, можно было нажать на ссылку и она отправляла в ячейку заявителя. Подскажите пожалуйста ? В обеих таблицах есть неповторяемый код товара

[@statanaliz]

Здравствуйте. Чтобы в одной ячейке и ВПР, и ссылка - не знаю, наверное никак без макросов. Но можно попробовать вывести рядом с ответом (по ВПР) ссылку с помощью функции ГИПЕРССЫЛКА. Для этого нужно только обнаружить адрес. Сам не пробовал.

[@ФархатА-к5я]

@@statanaliz , Спасибо большое за ответ. Пробовал результат ВПР скрыть и рядом сделать гиперссылку, но к сожалению она ссылается на саму ячейку с формулой ВПР. Если вдруг появится вариант, дайте знать пожалуйста. Очень важно к сожалению для меня. Спасибо большое.

[@statanaliz]

@@ФархатА-к5я Вот так? yadi.sk/i/n-AHLXec2jQryQ Может, не самое элегантное решение, но работает. Нужно только поменять название книги, листа корректировку к ссылке исходя из реального расположения диапазона.

[@ФархатА-к5я]

@@statanaliz, да так тоже можно. Скажите пожалуйста как вы так сделали ? Чтоб находил совпадение и если есть, то ссылка. Как вы показали.

[@statanaliz]

@@ФархатА-к5я там же формула. Посмотрите, как работает по частям. Вначале находится адрес, потом он подставляется в функцию ГИПЕРССЫЛКА. Чего не хватает, подправляем вручную.

[@Lucerbius]

Хороший урок. А то читаешь все эти ужасные описания функций и фиг поймёшь, что да как там. Пока пытаюсь понять как работает этот поиск по массиву (поиск нужного значения, который стоит самым последним. А мне нужно предпоследний, либо просто исключать ненужные позиции из массива по дополнительному "флагу", в виде плюсика в соседнем столбце например) =МАКС(ЕСЛИ(C:C=-C16;СТРОКА(C:C)-СТРОКА(ИНДЕКС(C:C;1;1))+1))

[@loowoozy5534]

Бл ну почему людям так сложно говорить простыми словами? Обязательно надо академическим языком что-то говорить??