31 Функции Excel ИНДЕКС и ПОИСКПОЗ (INDEX MATCH), как более гибкая альтернатива ВПР (VLOOKUP)
Вставка
- Опубліковано 4 чер 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
Спасибо. Очень в доступной форме.
Большое спасибо! Без Вашего видео никак не могла разобраться с этими функциями❤
Спасибо огромное,очень доступно объяснили! Реально полезная функция.
Очень полезное сочетание, спасибо, немного тяжелее на этапе понимания, но намного удобнее чем Vlookup
Да, комбинация ИНДЕКС+ПОИСКПОЗ во многих ситуациях более удобна, чем ВПР.
спасибо. очень полезно и понятно
Оо спасибо вам большое, выручили, так доходчиво объясняете, удачи!!!
Выручили, большое спасибо!!!
благодарю! все четко и понятно, а главное работает и удобно!
Спасибо за отзыв. Старался )
Спасибо большое!!
Спасибо, 2.30 минуты уже понял как это использовать. А до этого смотрел, читал статьи на английском и не мог понять почему так лучше... Хотя скорее всего вы подвели так сказать conclusion и в голове сложился пазл.
Спасибо! Ждем новые видео!
Спасибо!
Спасибо большое за видео, ооочень полезное! :)
Дмитрий удачи, спасибо
Спасибо, взаимно, удачи )
Спасибо! Учусь на ваших видео!
Рад, если помогает! )
Спасибо вам большое. Вы мне очень помогли
Дмитрий, Вы молодец!!! P.S. Понимаю, что это Вы знаете и без меня, но всё-таки...
Спасибо за похвалу. Но еще есть, куда развиваться. После переезда сайта продолжу делать ролики, оставайтесь на связи.
Доброго времени суток, Дмитрий!
С наступающими Вас праздниками!
Спасибо за Ваши обучающие видео. А могли бы Вы, в перспективе, к Вашим видео урокам, прилагать файл-пример?
Спасибо
Добрый вечер. Спасибо, и Вас с наступающим. Да, уже просили. На сайте подвешу и укажу ссылку по роликом.
Спасибо за видео.
Спасибо за отзыв.
Спасибо Вам большое 🙏🏻
Да пожалуйста )
Гениально спасибо!
Спасибо!
Полезная формула. Часто использую ВПР при работе с базами данных, размером до 100 Мегабайт. Ваша формула =ЕслиОшибка поможет с ошибками #Н/Д .
Красавчик
Спасибо. Помогло
Интересно было посмотреть
Благодарю за отзыв!
Спасибо
Очень Понятное видео
Спасибо за отзыв!
спасибо
Thx mate
Добрый день. Подскажите,пожалуйста, в данном случае формула возвращает первую строку с искомым значением. Что делать если строк с таким значением несколько и результат надо просуммировать?
Спасибо за понятный урок. Очень помогли)
Спасибо за отзыв!
Интересно, а если бы капуста в искомом столбце была два или три раза и с разной ценой, и нужно было найти и среднее значение цены?!
Подскажите пожалуйста, как работать с этими функциями, если в одной части таблицы (там куда подставлять ответ) искомые значения для сопоставления расположены в столбик, а в другой - массив из строк и столбцов
А как правильно сделать поиск по 2-м разным критериям в одном столбце? Например в столбце 4 найти значения 20 и 35
Я шас етот видео исползивол проста круто . )))👏👏👏👏👏
Пожалуйста. Спасибо за комментарий!
Спасибо за простое и понятное объяснение. Можно у вас узнать какой программой вы пользуетесь при создании видео для рисования стрелок и прямоугольников?
Пожалуйста. Программа - camtasia studio
Дмитрий, а планируете сделать ролик о применении данной функции при работе с двумя листами?
Уточните, пожалуйста, вопрос. Что на этих листах должно быть?
@@statanaliz , исходные данные. Но я уже и сам разобрался! Просто изначально пугали названия листов))
@@AnKl_Sam Отлично, что разобрались. )
good bro
Здравствуйте, интересует подробнее [тип совпадения], такая ситуация: Искомое значение ЛЮБОЕ ЦЕЛОЕ ЧИСЛО, просматриваемый массив, к примеру, 25-50-100-150-200-300-400-600-800-1000, и функция выбирает только наименьшее подходящее, например, искомое значение 148 и функция подбирает из массива 100, вместо более подходящего 150. Как задать параметры ,что бы функция искала позицию наиболее близкую по значению??? Спасибо
Спасибо, правда копирование формул индекс и поискпоз не получилось, если использовать ctrl c/ctrl v формула сбивается
Здравствуйте. А если название из одной таблицы полное, а из другой нет. Например: 1. Молоко, 2. Молоко пастеризованное. Будет ли работать данная формула? С ВПР у меня не получается.
Спасибо теперь рубежный контроль на 95 балов сдам
Здравствуйте Дмитрий,
Спасибо вам большое за курс, урок за уроком прохожу ваш курс. Узнала много нового!
Скажите, пожалуйста, а как быть, если слева от ключевого поля находится более одной колонки? Как в таком случае указать номер колонки? На примере вашего же файла я добавила колонку E и F для второй таблицы (доллар / гривна)
Добрый день. Спасибо за отзыв. В том то и дело, что при использовании комбинации ИНДЕКС/ПОИСКОПОЗ не нужно указывать номер колонки, как в ВПР. Нужно по отдельности указать колонку с критерием и затем колонку с данными, где бы она ни была (но должно совпадать по строкам).
@@statanaliz Все получилось! Спасибо Вам большое! Перехожу к следующему уроку!
Дмитрий, можете подсказать, как правильно записать функцию автозаполнения таблицы, когда есть сводная таблица по дням и несколько развернутых таблиц доходов-расходов. В сводной таблице один день - одна строка. В развёрнутых таблицах в разные дни разное количество строк за день.
Вам надо в общей таблице сложить все операции по дням из других таблиц? Если да, то подойдет функция СУММЕСЛИ ua-cam.com/video/8gfag9QpJYY/v-deo.html
если на 2ом таблице два капусты то как исправить ощибки
Здравствуйте. А возможно ли использовать эту связку при поиске строки по 2 критериям? Например: в первом столбце наименование товара, в другом столбце даты. Нужно найти строку, где совпадает и товар, и дата?
Критерий для поиска можно склеить из разных ячеек, а вот столбец для поиска может быть только один.
@@statanaliz Дмитрий, а можно ли использовать пользоваться этими функциями в случае, если столбец поиска является вариабельным значением? Например, в этом видео-примере выбирать столбец с месяцем по какому-то условию? Или это можно реализовать в Умной таблице?
Спасибо, очень доходчиво. А если во второй таблице, в колонке Н, допустим абрикос встречается два раза, с разной ценой, можно сделать так, чтобы в колонке С, складывалось значение цены напротив Абрикоса?
Да, см.функцию СУММЕСЛИ
@@statanaliz Спасибо, ее получается, можно вставить в эту общую формулу?
@@_deatmoroz СУММЕСЛИ используется вместо всей этой конструкции из ролика.
@@statanaliz Понятно, мне наверное лучше тогда этой формулой, воспользоваться после основной, у меня именно в правой колонке длинные повторяются
Добрый день, подскажите, пож-та, как в столбце I отображается формула из столбцa H (min 2:03). Спасибо.
Добрый день. Функция Ф.ТЕКСТ
Езепов Дмитрий Дмитрий, у меня так и не получилось!((( какой формат задавать? Нашла Ваши примеры, там показано на датах, а на формулах не понимаю((((
@@Julialoves100 Это ж поиск. Причем тут форматы?
@@statanaliz я теперь еще сильнее не поняла)) могли бы Вы написать формулу, по которой в графе "формула" появляется собственно формула? простите за тавтологию.
@@Julialoves100 зайдите по ссылке под роликом, попадаете на сайт, где можно скачать файл из этого урока.
Добрый день, Дмитрий. Спасибо за видео, очень понятно объясняете. Правда никак не могу найти решение, по своему заданию. Есть таблица, при заполнении цифры, нужно чтобы в соседней ячейки приписывались данные, которые соответствуют этой цифре. Как это прописать, не могу понять..
Добрый день. Попробуйте ВПР ua-cam.com/video/nNUx72wmLjs/v-deo.html.
У меня два разных файла. С большим объёмом . Но функция не работает. Выдаёт #знач! Что это за ошибка? Что значит неправильный тип данных?
Приветствую. Огромное спасибо за Ваш труд. Смотря ваши видео у меня возник вопрос, а есть ли поиск аналогичный ВПР, но по буквам? Возникла такая необходимость найти 800 человек из 10000 по емэйл адресам.
Не совсем понял. Все адреса записаны в одной ячейке? Опишите подробнее условие задачи.
@@statanaliz Все адреса записаны в одной ячейке, все 800, а те 10000, среди которых нужно их отыскать, в другой ячейке, справа.
Дмитрий спасибо большое за обратную связь. Я разобрался со своей проблемой с помощью ВПР. Допускал ошибку в диапазоне, поэтому формула не работала.
@@Snapejke Я бы для начала разделил адреса на ячейки. Потом применил бы стандартные способы.
@@Snapejke Ок, рад, если разобрались сами )
Что значит "ссылки следует закрепить", и как? На видео не видно действий
То есть сделать ссылки абсолютными с помощью вставки знака $ перед названием столбца и строки. Можно воспользоваться горячей клавишей F4.
Здравствуйте. Помогите пожалуйста, как можно в 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 =СРОТКЛ(СМЕЩ(M7;0;0;N$3;1)).
А можно ли указать аргумент - ячейку в которой a1&a2 ??))
Как это можно сделать?
Не совсем понял. Нужно в качестве критерия склеить две ячейки? Можно, да.
@@statanaliz а как - если не секрет?
@@statanaliz для поиск поз во вложенном индексе ?
@@bristolzoo845 Не секрет, но нужно смотреть на данные. Опишите точнее задачу или пришлите пример с образцом правильного решения мне на почту, я посмотрю. Адрес в описании канала.
@@bristolzoo845 Надо на данные смотреть. Можете прислать пример с образцом правильного решения. Я посмотрю. Адрес в описании канала.
Если в таблице будут пустые ячейки, то попадая на них формула будет выдавать значение 0, как сделать что бы выдавал пустую ячейку вместо 0?
Примените функцию ЕСЛИ, либо скройте нули. Разные способы скрытия нулей показаны в уроке ua-cam.com/video/anbDFiauiGQ/v-deo.html
@@statanaliz Я не слишком силён в формулах, но на сколько мог применить: ЕСЛИ(ЕПУСТО и т.д. то скрыть нули получилось, но появилась другая проблема, попадая на заполненную ячейку формула выдаёт ИСТИНА, ЛОЖЬ, но никак не содержимое ячейки... в общем не получается пока.
@@user-gw2lg9ff4i Долго объяснять словами)). Пришлите лучше файл с примером и образец решения мне на ящик. Адрес в описании канала.
@@statanaliz Разобрался, спасибо за помощь.
@@user-gw2lg9ff4i Рад, если помог. Удачи! )
2022
Примечание: вместо функции ИНДЕКС можно использовать СМЕЩ.
можно, но не нужно. СМЕЩ - летучая функция, т.е. постоянно пересчитывается. Может затормозить работу файла.
go only go forward bro
Здравствуйте. Подскажите пожалуйста как сделать ВПР ссылкой? Например есть 2 большие таблицы на одной или нескольких страницах в экселе. Необходимо найти код из первой в таблице во второй (первая таблица ТМЗ например, а вторая - это заявитель). Чтоб посмотрев на товар, можно было нажать на ссылку и она отправляла в ячейку заявителя. Подскажите пожалуйста ? В обеих таблицах есть неповторяемый код товара
Здравствуйте. Чтобы в одной ячейке и ВПР, и ссылка - не знаю, наверное никак без макросов. Но можно попробовать вывести рядом с ответом (по ВПР) ссылку с помощью функции ГИПЕРССЫЛКА. Для этого нужно только обнаружить адрес. Сам не пробовал.
@@statanaliz , Спасибо большое за ответ. Пробовал результат ВПР скрыть и рядом сделать гиперссылку, но к сожалению она ссылается на саму ячейку с формулой ВПР. Если вдруг появится вариант, дайте знать пожалуйста. Очень важно к сожалению для меня. Спасибо большое.
@@user-mn1np8ln9d Вот так? yadi.sk/i/n-AHLXec2jQryQ Может, не самое элегантное решение, но работает. Нужно только поменять название книги, листа корректировку к ссылке исходя из реального расположения диапазона.
@@statanaliz, да так тоже можно. Скажите пожалуйста как вы так сделали ? Чтоб находил совпадение и если есть, то ссылка. Как вы показали.
@@user-mn1np8ln9d там же формула. Посмотрите, как работает по частям. Вначале находится адрес, потом он подставляется в функцию ГИПЕРССЫЛКА. Чего не хватает, подправляем вручную.
Хороший урок.
А то читаешь все эти ужасные описания функций и фиг поймёшь, что да как там.
Пока пытаюсь понять как работает этот поиск по массиву (поиск нужного значения, который стоит самым последним. А мне нужно предпоследний, либо просто исключать ненужные позиции из массива по дополнительному "флагу", в виде плюсика в соседнем столбце например)
=МАКС(ЕСЛИ(C:C=-C16;СТРОКА(C:C)-СТРОКА(ИНДЕКС(C:C;1;1))+1))
Станом на сьогодні ...=ПРОСМОТРX(
Спасибо!
Не за что!
Спасибо!
Пожалуйста!