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

Sdílet
Vložit
  • čas přidán 4. 06. 2017
  • Связка функций Excel ИНДЕКС и ПОИСКПОЗ (на английском INDEX MATCH) является более гибкой заменой ВПР (VLOOKUP), т.к. позволяет производить поиск в столбцах слева от критерия. Файл с примером находится на странице statanaliz.info/osnovnye-form...
    ► Корпоративный тренинг "Продвинутый уровень MS Excel":
    statanaliz.info/trening-prodv...
    ► Онлайн курс "Продвинутый уровень MS Excel":
    statanaliz.info/kurs-prodvinu...
    **************************
    💳 Поддержать канал деньгами
    statanaliz.info/donate/
    ** Мой сайт об Excel и статистике **
    statanaliz.info/
    ** Странички и аккаунты в соцсетях **
    Facebook: / statanaliz.info
    Вконтакте: id_statanaliz_info
    Твиттер: / statanaliz_info
    Telegram: t.me/statanaliz

Komentáře • 135

  • @tatina
    @tatina Před rokem +3

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

  • @user-ev8fp1xg9h
    @user-ev8fp1xg9h Před 4 lety +5

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

  • @alexeygavshin4297
    @alexeygavshin4297 Před rokem +1

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

  • @Afanasiy_Nikitin
    @Afanasiy_Nikitin Před 3 lety +10

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

    • @statanaliz
      @statanaliz  Před 3 lety +1

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

  • @navigante-789
    @navigante-789 Před 4 lety +9

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

  • @semyon__ykt
    @semyon__ykt Před rokem +1

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

  • @user-hw6im9te6w
    @user-hw6im9te6w Před rokem

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

  • @user-fz5lb5ud3u
    @user-fz5lb5ud3u Před 3 lety

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

    • @statanaliz
      @statanaliz  Před 3 lety

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

  • @user-jv2pt7rm6b
    @user-jv2pt7rm6b Před 6 lety +2

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

  • @user-dg7xb3wv7s
    @user-dg7xb3wv7s Před rokem

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

  • @user-hj2wz4nn9h
    @user-hj2wz4nn9h Před rokem

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

  • @user-nr4qn4sz9v
    @user-nr4qn4sz9v Před 4 lety

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

    • @statanaliz
      @statanaliz  Před 4 lety

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

  • @roman_r1
    @roman_r1 Před 4 lety

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

    • @statanaliz
      @statanaliz  Před 4 lety

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

  • @blackbon196
    @blackbon196 Před 5 měsíci

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

  • @cyklonification
    @cyklonification Před 6 lety +2

    Спасибо!

  • @user-if9wy6ri1o
    @user-if9wy6ri1o Před 5 lety +10

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

    • @statanaliz
      @statanaliz  Před 5 lety +5

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

  • @ppr8117
    @ppr8117 Před 6 lety +3

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

    • @statanaliz
      @statanaliz  Před 6 lety +1

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

  • @Albertus99
    @Albertus99 Před 3 lety

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

  • @user-cl1bq8nd6g
    @user-cl1bq8nd6g Před 2 lety

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

  • @ekaterinayurasova1601
    @ekaterinayurasova1601 Před 4 lety

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

  • @dalero9925
    @dalero9925 Před 3 měsíci

    Красавчик

  • @german_novotiable
    @german_novotiable Před rokem

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

  • @user-me8iu5gw7z
    @user-me8iu5gw7z Před 4 lety

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

    • @statanaliz
      @statanaliz  Před 4 lety

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

  • @user-mb3pq5xn7h
    @user-mb3pq5xn7h Před 2 lety

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

  • @islomeshmurodov5578
    @islomeshmurodov5578 Před 3 lety

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

  • @Calmasastone
    @Calmasastone Před rokem

    Thx mate

  • @user-bx2rl4pc3j
    @user-bx2rl4pc3j Před 3 lety

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

  • @bekkul.zhumabekov
    @bekkul.zhumabekov Před 8 měsíci

    Спасибо

  • @islomeshmurodov5578
    @islomeshmurodov5578 Před 3 lety

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

    • @statanaliz
      @statanaliz  Před 3 lety

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

  • @user-jg4bw9jl8d
    @user-jg4bw9jl8d Před 2 lety

    спасибо

  • @user-zq8qj5wx1z
    @user-zq8qj5wx1z Před 2 měsíci

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

  • @zhomagram
    @zhomagram Před 7 měsíci

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

  • @piyoda
    @piyoda Před 4 měsíci

    good bro

  • @user-bw8by5kv7d
    @user-bw8by5kv7d Před 3 měsíci +1

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

  • @sergeyvakhranev6695
    @sergeyvakhranev6695 Před rokem

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

  • @user-cn8dz7xl5j
    @user-cn8dz7xl5j Před 4 lety +3

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

    • @statanaliz
      @statanaliz  Před 4 lety +1

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

  • @user-jr3ro4lv8t
    @user-jr3ro4lv8t Před rokem

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

  • @user-bb7ht7jc3x
    @user-bb7ht7jc3x Před 5 měsíci

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

  • @user-uy3wh4ip6e
    @user-uy3wh4ip6e Před rokem

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

  • @igrikum
    @igrikum Před 4 lety

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

  • @AnKl_Sam
    @AnKl_Sam Před 3 lety

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

    • @statanaliz
      @statanaliz  Před 3 lety

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

    • @AnKl_Sam
      @AnKl_Sam Před 3 lety

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

    • @statanaliz
      @statanaliz  Před 3 lety

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

  • @OkGo_0
    @OkGo_0 Před měsícem

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

  • @oleksiikucheriavykh2890
    @oleksiikucheriavykh2890 Před 4 lety +1

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

    • @statanaliz
      @statanaliz  Před 4 lety

      Вам надо в общей таблице сложить все операции по дням из других таблиц? Если да, то подойдет функция СУММЕСЛИ czcams.com/video/8gfag9QpJYY/video.html

  • @analuna3228
    @analuna3228 Před 3 lety

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

    • @statanaliz
      @statanaliz  Před 3 lety +1

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

    • @analuna3228
      @analuna3228 Před 3 lety

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

  • @erkinovqudratilla1559

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

  • @user-wn3td7wb8j
    @user-wn3td7wb8j Před 5 lety +1

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

    • @statanaliz
      @statanaliz  Před 5 lety

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

    • @alexeymaslov3370
      @alexeymaslov3370 Před 2 lety

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

  • @azat_reisende
    @azat_reisende Před 4 lety

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

    • @statanaliz
      @statanaliz  Před 4 lety

      Добрый день. Попробуйте ВПР czcams.com/video/nNUx72wmLjs/video.html.

  • @_deatmoroz
    @_deatmoroz Před rokem

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

    • @statanaliz
      @statanaliz  Před rokem

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

    • @_deatmoroz
      @_deatmoroz Před rokem

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

    • @statanaliz
      @statanaliz  Před rokem

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

    • @_deatmoroz
      @_deatmoroz Před rokem

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

  • @Snapejke
    @Snapejke Před 4 lety +1

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

    • @statanaliz
      @statanaliz  Před 4 lety

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

    • @Snapejke
      @Snapejke Před 4 lety +1

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

    • @Snapejke
      @Snapejke Před 4 lety +1

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

    • @statanaliz
      @statanaliz  Před 4 lety

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

    • @statanaliz
      @statanaliz  Před 4 lety

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

  • @zzgiiiyywq
    @zzgiiiyywq Před 9 měsíci

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

  • @user-zu8lu7mm3y
    @user-zu8lu7mm3y Před 4 lety +1

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

    • @statanaliz
      @statanaliz  Před 4 lety +2

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

  • @LiaPlo
    @LiaPlo Před 5 lety

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

    • @statanaliz
      @statanaliz  Před 5 lety

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

    • @Julialoves100
      @Julialoves100 Před 5 lety

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

    • @statanaliz
      @statanaliz  Před 5 lety

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

    • @Julialoves100
      @Julialoves100 Před 5 lety

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

    • @statanaliz
      @statanaliz  Před 5 lety

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

  • @user-gw2lg9ff4i
    @user-gw2lg9ff4i Před 4 lety +1

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

    • @statanaliz
      @statanaliz  Před 4 lety

      Примените функцию ЕСЛИ, либо скройте нули. Разные способы скрытия нулей показаны в уроке czcams.com/video/anbDFiauiGQ/video.html

    • @user-gw2lg9ff4i
      @user-gw2lg9ff4i Před 4 lety +1

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

    • @statanaliz
      @statanaliz  Před 4 lety

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

    • @user-gw2lg9ff4i
      @user-gw2lg9ff4i Před 4 lety +1

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

    • @statanaliz
      @statanaliz  Před 4 lety

      @@user-gw2lg9ff4i Рад, если помог. Удачи! )

  • @bristolzoo845
    @bristolzoo845 Před 3 lety

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

    • @statanaliz
      @statanaliz  Před 3 lety

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

    • @bristolzoo845
      @bristolzoo845 Před 3 lety

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

    • @bristolzoo845
      @bristolzoo845 Před 3 lety

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

    • @statanaliz
      @statanaliz  Před 3 lety

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

    • @statanaliz
      @statanaliz  Před 3 lety

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

  • @user-oh9uf3ht3o
    @user-oh9uf3ht3o Před 3 lety

    Здравствуйте. Помогите пожалуйста, как можно в 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
      @statanaliz  Před 3 lety

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

    • @user-oh9uf3ht3o
      @user-oh9uf3ht3o Před 3 lety

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

  • @user-qn5by5iv7u
    @user-qn5by5iv7u Před 2 lety

    2022

  • @piyoda
    @piyoda Před 4 měsíci

    go only go forward bro

  • @Midavok
    @Midavok Před 5 lety +2

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

    • @statanaliz
      @statanaliz  Před 5 lety

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

  • @user-mn1np8ln9d
    @user-mn1np8ln9d Před 5 lety

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

    • @statanaliz
      @statanaliz  Před 5 lety

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

    • @user-mn1np8ln9d
      @user-mn1np8ln9d Před 5 lety

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

    • @statanaliz
      @statanaliz  Před 5 lety

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

    • @user-mn1np8ln9d
      @user-mn1np8ln9d Před 5 lety

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

    • @statanaliz
      @statanaliz  Před 5 lety

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

  • @Lucerbius
    @Lucerbius Před 2 lety

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

  • @Jonsky32
    @Jonsky32 Před 2 lety

    Станом на сьогодні ...=ПРОСМОТРX(

  • @tigranhakobyan3077
    @tigranhakobyan3077 Před 2 lety +1

    Спасибо!

  • @aydaryakup4540
    @aydaryakup4540 Před 4 lety +1

    Спасибо!