Меня зовут Александр и я экселеголик. Экселезависимость у меня около 8 месяцев. Недавно открыл для себя ВПР. И вот теперь у меня есть ВЫБОР! Спасибо большое Николай!
Николай, Вы, как всегда, даже для "давно решённых" задач наглядно показываете новые и, обычно, более быстрые и удобные варианты решения! Огромное Вам спасибо!!!
случайно наткнулась на ваше видео, но теперь я ваш фанат) Надеюсь, что найду решение своей проблемы в ваших видео уроках. ваши видеоуроки одни из немногих, которые слушаешь без перемотки. Творческих успехов вам, Николай. Да и не только творческих ;-)
Николай, я уже очень много лет плотно работаю в Excel, знаю много фишек и функций. Но Вы удивляете простотой и гибкостью Ваших приёмов!👍👍👍 Как Вам удаётся найти, разсекретить и скомпоновать это всё?😃
Очень интересное и познавательное видео. спасибо! P.S. Являюсь обладателем книги "Microsoft Excel. Готовые решения - бери и пользуйся!", а вот второй книги "Microsoft Excel: Мастер формул" на сайте не нашёл.
Давно нечего нового не открывал для себя в Excel. Но вариант использования функции ВЫБОР с возможностью склеивать {1;2} / или менять порядок {2;1} столбцов меня удивил. Спасибо! Николай! Интересно, а можно заменить{1;2} ссылками на ячейки со значениями, типо ="{"&G2&";"&G3&"}"
подскажите Николай, у меня такая ситуация Может ли одна из функций ВПР, ПОИСКПОЗ или другие искать не по 1 столбцу, а по двум (в чем суть) у меня база телефонов, у кого-то он один, у кого-то 2 и есть у кого 3 - впихнуть в одну ячейку - не то, поэтому пришлось добавить напротив каждого сотрудника по 3и ячейки, у большинства 1 номер, но есть те, у кого есть 2 и 3 номера - они записаны в рядом стоящих ячейках по одному. По одному столбцу находит все - офрмула работает на ура. Но, к примеру, когда я ввожу для поиска номер телефона со сторого столбца или третьего - мне выдает Н/Д, задаю поиск $E$3:$F$178 - чтобы типа искало в 2х столбцах - выдает снова ошибку. Вопрос: как сделать так чтобы искало номера телефонов не только в первом, но и во втором и в третьем столбце одновременно? Заранее спасибо
Николай, у меня возник вопрос по поводу работы вашей функции VlookupS(в последнем примере): под результаты ее работы предусмотрели 8 ячеек для вывода результатов поиска. Что произойдет при выводе результатов(в данном таблице), если результатов у функции VlookupS будет больше восьми, например 12 ?То есть "лишние" четыре результата поиска будут просто "утеряны"?
И как заранее предусмотреть необходимое количество ячеек для вывода, если мы не знаем, какое количество результатов будет получено в результате работы функции VlookupS ? Например, количество результатов поиска будет равно 1000 и все они будут нам нужны для дальнейшего анализа или работы с ними. Спасибо.
интересное видео, спасибо большое, VLookUp + Choose отличная штука, а можно что то похожее сделать для HLookUp? Например, если строка поиска идет под строкой значений?
Привет Николай если в функции впр вести название неполностью пропустит допустим цвет товара ошибка н/д выходит, как выйти из этой ситуации погли бы это показать.
Добрый вечер. А как посмотреть исходный код функции поискпоз. А то мне не очень понятно,почему для поиска данных нужно упорядочивать массив, а для поиска заведомо несуществующего значения в рамках поиска последней ячейки - не нужно.
Разделители в массивах констант различаются в зависимости от региональных настроек ПК. Как и разделители между целой и дробной частью и разделители тысяч и т.д.
Это порядковые номера склеиваемых столбцов. Если из видео не очень понятно, то сходите по ссылке в описании и почитайте статью (там и файл-пример есть).
На таблице в 500 000 строк при тестировании на быстродействие результаты были примерно такие: ВПР - 5.5 сек ИНДЕКС+ПОИСКПОЗ - 5.3 сек СУММЕСЛИ вместо ВПР - 19 сек ВПР+ВЫБОР - 34 сек Конкретные числа зависят от компа и разрядности Office, само-собой. Макрофункцию VLOOKUPS не тестировал, но будет совсем долго, подозреваю.
я правильно понимаю: если ячейки в текстовом формате, то ИНДЕКС+ПОИСКПОЗ не работает, а так же не работает VLookUp + Choose... необходимо формат в numbers переводить
Почему у меня ИНДЕКС+ПОИСКПОЗ не работает с разными листами?или он и не должен? когда ИНДЕКС+вычисляемая ячейка на одном листе, а ПОИСКПОЗ (что искать-на том же листе, а где искать-на другом) ПС: много у вас полезных видео, но выходят очень редко..+они сильно начинают отставать по необходимости.. как и писали ниже Power Query много что упрощает, и даже мои, ну очень поверхностные знания по нем, меня сильно выручают..
Здравствуйте, как можно приобрести Ваши книги? Могут ли мне оказать платную поддержку для простых на мой, возможно ошибочный взгляд операций. Для таких, как Вы, специалистов это не займет много времени, я думаю. Приблизительно надо следующее: на листе 1 в шапке таблицы в ячейке А1 Наименование, в ячейке D1 Сумма. В теле таблицы наименования повторяются с точностью, а суммы меняются. Необходимо на новом листе извлекать из Листа 1 Наименование в одну конкретную ячейку, которая будет зафиксирована в алфавитном порядке, с возможностью добавления в случае появления нового наименования и добавлять суммы из столбца D первого листа, по этому наименованию. Смысл в том, что нужна простая формула, насколько это возможно, однажды мне написали макрос, но таблица стала неудобной для перемещения столбцов и прочего. Благодарю заранее, мне нравится как Вы доступно изъясняетесь.
Извините, небольшая поправка. Если на втором листе зафиксированные наименования, то я при помощи СУММЕСЛИ этого добилась. А вот возможности не делать этого вручную, а чтобы Excel делал сам выборку из наименований не могу никак найти. А это очень важно для контроля ошибки в буквенной части наименований. Один незаметный пробел и сумма уже неверная...
Левый ВПР в 2017-м?! Серьезно?! Лучше бы Николай обновил тему консолидация данных с разных листов с помощью Power Query, которая по умолчанию идет в Excel 2016, Office 365.
Вадим Окладников Nikolay Pavlov скорее сделает 100-е видео по функции ВПР и ГПР, чем Power Query. Сертифицированный тренер и эксперт в Excel нам всё азбуку объясняет, как сайентологи доказывают людям, что они не так говорят на своём родном языке, и дурачат их.
Спасибо за мнение, Николай учтет :) Для информации: за весь прошлый год из 74 проведенных мной корпоративных тренингов Excel 2016 встретился аж целых 6 раз.
Умные таблицы тоже не часто используют, но видео по ним Николай исправно выкладывал:) А если серьезно, то консолидация данных с разных листов, а также книг, без использования макросов стала возможна только при использовании Power Query.
Дык, я ж не спорю. У меня тут просто свой план по видеоурокам на 2017 год есть - и я по нему планомерно работаю. Про Power Query там есть, честное слово :)
к сожалению эксель уже давно не актуален в использовании... слишком глупая и не логичная программа, как при работе с таблицами, так и если брать те же таблицы из сети интернет, прогружая их через вкладку данные. Как минимум, эксель не умеет работать с числами, вида 1:1, он их переименовывает в дату. Скажете, так поставьте текстовый формат.. Но тут тоже не задача, при обновлении этих данных, которые берутся с определенного сайта, формат автоматом меняется, и это еще раз доказывает, что эксель самая убогая программа которую вообще можно было придумать. Есть масса других примеров, где эксель только увеличивает твою работу и время на эту работу.
Меня зовут Александр и я экселеголик. Экселезависимость у меня около 8 месяцев. Недавно открыл для себя ВПР. И вот теперь у меня есть ВЫБОР! Спасибо большое Николай!
Главное - не пробовать Power Query. С него не соскочить уже никак 😁
Здравствуйте! Благодарю! Четко, внятно, без музыкального сопровождения и с хорошей дикцией! Лучший из лучших.
Спасибо :)
Здравствуйте, Николай, всегда с Вами, в восторге от вашей подачи информации. Благодарю Вас, что делитесь знаниями 😊❤
Николай, Вы, как всегда, даже для "давно решённых" задач наглядно показываете новые и, обычно, более быстрые и удобные варианты решения! Огромное Вам спасибо!!!
Вы самый полезный человек на планете! Спасибо брат!!! Вы мой БРАТ!)))
Наконец я научился левому ВПР с помощью Индекс и Поискпоз! Спасибо!
случайно наткнулась на ваше видео, но теперь я ваш фанат) Надеюсь, что найду решение своей проблемы в ваших видео уроках. ваши видеоуроки одни из немногих, которые слушаешь без перемотки. Творческих успехов вам, Николай. Да и не только творческих ;-)
Спасибо! :)
Николай, ВПР и ВЫБОР это супер!) Спасибо!)
Очень интересное сочетание choose и vlookup. Думаю пригодится)
Спасибо Николай, как всегда выручил!
Красота все это.
Думал знаю про ВПР всё, ВПР+выбор просто супер
Спасибо большое Вам!!! Очень помогли
Николай, я уже очень много лет плотно работаю в Excel, знаю много фишек и функций. Но Вы удивляете простотой и гибкостью Ваших приёмов!👍👍👍 Как Вам удаётся найти, разсекретить и скомпоновать это всё?😃
😉
Были времена, когда надо было индексы и поискпозы писать) но просмотрх класс теперь)
Спасибо вам огромное
Спасибо 👍
Спасибо за видеоурок.
Очень интересное и познавательное видео. спасибо!
P.S. Являюсь обладателем книги "Microsoft Excel. Готовые решения - бери и пользуйся!", а вот второй книги "Microsoft Excel: Мастер формул" на сайте не нашёл.
Давно нечего нового не открывал для себя в Excel. Но вариант использования функции ВЫБОР с возможностью склеивать {1;2} / или менять порядок {2;1} столбцов меня удивил. Спасибо! Николай!
Интересно, а можно заменить{1;2} ссылками на ячейки со значениями, типо ="{"&G2&";"&G3&"}"
Напрямую - вряд ли. Но можно попробовать засунуть все это в ДВССЫЛ :)
Побывал =ДВССЫЛ("{"&G2&";"&G3&"}"), что-то не помогло ..
Зачем делать ссылку на ячейку с постоянной величиной, если эта величина никогда не станет переменной.
По аналогии с ИНДЕКС и ПОИСКПОЗ можно использовать комбинацию ДВССЫЛ, АДРЕС и ПОИСКПОЗ.
Можно, но учтите, пожалуйста, что ДВССЫЛ - волатильная функция, т.е. тормозить это дело будет в разы сильнее, к сожалению :(
подскажите Николай, у меня такая ситуация
Может ли одна из функций ВПР, ПОИСКПОЗ или другие искать не по 1 столбцу, а по двум (в чем суть) у меня база телефонов, у кого-то он один, у кого-то 2 и есть у кого 3 - впихнуть в одну ячейку - не то, поэтому пришлось добавить напротив каждого сотрудника по 3и ячейки, у большинства 1 номер, но есть те, у кого есть 2 и 3 номера - они записаны в рядом стоящих ячейках по одному.
По одному столбцу находит все - офрмула работает на ура.
Но, к примеру, когда я ввожу для поиска номер телефона со сторого столбца или третьего - мне выдает Н/Д, задаю поиск $E$3:$F$178 - чтобы типа искало в 2х столбцах - выдает снова ошибку.
Вопрос: как сделать так чтобы искало номера телефонов не только в первом, но и во втором и в третьем столбце одновременно?
Заранее спасибо
МОЩНО!!!!!!!!!!!!!!!!!!!
Мне кажется, сами создатели Эксель так круто не могут))).
Могут-могут, я видел :)
Супер!
Николай, у меня возник вопрос по поводу работы вашей функции VlookupS(в последнем примере): под результаты ее работы предусмотрели 8 ячеек для вывода результатов поиска. Что произойдет при выводе результатов(в данном таблице), если результатов у функции VlookupS будет больше восьми, например 12 ?То есть "лишние" четыре результата поиска будут просто "утеряны"?
И как заранее предусмотреть необходимое количество ячеек для вывода, если мы не знаем, какое количество результатов будет получено в результате работы функции VlookupS ? Например, количество результатов поиска будет равно 1000 и все они будут нам нужны для дальнейшего анализа или работы с ними.
Спасибо.
Нужно ввести формулу с запасом, а ошибки на лишних ячейках можно скрыть функцией ЕСЛИОШИБКА (IFERROR), например.
интересное видео, спасибо большое, VLookUp + Choose отличная штука, а можно что то похожее сделать для HLookUp? Например, если строка поиска идет под строкой значений?
Круть!
Привет Николай если в функции впр вести название неполностью пропустит допустим цвет товара ошибка н/д выходит, как выйти из этой ситуации погли бы это показать.
Посмотрите www.planetaexcel.ru/techniques/7/5636/
Добрый вечер. А как посмотреть исходный код функции поискпоз. А то мне не очень понятно,почему для поиска данных нужно упорядочивать массив, а для поиска заведомо несуществующего значения в рамках поиска последней ячейки - не нужно.
Здравствуйте, хотел спросить у вас по поводу функции выбор: почему в английской версии {1\2} а не {1;2} как написано в самом экселе? Благодарю
Разделители в массивах констант различаются в зависимости от региональных настроек ПК. Как и разделители между целой и дробной частью и разделители тысяч и т.д.
Спасибо Вам за вопрос! Из него я узнал, почему у меня этот вариант не работает!)))
У меня английская версия.
Николай как с вами связаться?
Про функцию выбор хотелось бы еще, первый раз с ней сталкиваюсь, где она еще может быть применена?
www.planetaexcel.ru/techniques/25/2639/
@@planetaexcel Спасибо !!
отлично. )
А как получит ссылки на последние ячейки(в нижней строке) динамического диапазона?
Вопрос по второму способу. Что значит в формуле 1.2?
Это порядковые номера склеиваемых столбцов. Если из видео не очень понятно, то сходите по ссылке в описании и почитайте статью (там и файл-пример есть).
Добрый день, можете подсказать что делать если номер заказа вводишь от руки и выходит ошибка н/д
Может быть у вас номера в таблице в виде текста?
Если Диапазон строк будет более 800 тыс, скажите будет сильно думать при обработке?
На таблице в 500 000 строк при тестировании на быстродействие результаты были примерно такие:
ВПР - 5.5 сек
ИНДЕКС+ПОИСКПОЗ - 5.3 сек
СУММЕСЛИ вместо ВПР - 19 сек
ВПР+ВЫБОР - 34 сек
Конкретные числа зависят от компа и разрядности Office, само-собой.
Макрофункцию VLOOKUPS не тестировал, но будет совсем долго, подозреваю.
я правильно понимаю: если ячейки в текстовом формате, то ИНДЕКС+ПОИСКПОЗ не работает, а так же не работает VLookUp + Choose... необходимо формат в numbers переводить
ИНДЕКС+ПОИСКПОЗ оказался в разы быстрее варианта через ВЫБОР
Почему у меня ИНДЕКС+ПОИСКПОЗ не работает с разными листами?или он и не должен? когда ИНДЕКС+вычисляемая ячейка на одном листе, а ПОИСКПОЗ (что искать-на том же листе, а где искать-на другом)
ПС: много у вас полезных видео, но выходят очень редко..+они сильно начинают отставать по необходимости.. как и писали ниже Power Query много что упрощает, и даже мои, ну очень поверхностные знания по нем, меня сильно выручают..
у меня и на одном листе не работает, даже при полном копировании. Office 2016
Ты такой красивый
в чем разница между VLOOKUPS и VLOOKUP3?
6:25
Объясните пожалуйста, почему # заказа (и соотв. остальные данные в строке) нельзя просто найти ctrl+f или даже фильтром? В чем будет ошибка? Спасибо
Здравствуйте, как можно приобрести Ваши книги? Могут ли мне оказать платную поддержку для простых на мой, возможно ошибочный взгляд операций. Для таких, как Вы, специалистов это не займет много времени, я думаю. Приблизительно надо следующее: на листе 1 в шапке таблицы в ячейке А1 Наименование, в ячейке D1 Сумма. В теле таблицы наименования повторяются с точностью, а суммы меняются. Необходимо на новом листе извлекать из Листа 1 Наименование в одну конкретную ячейку, которая будет зафиксирована в алфавитном порядке, с возможностью добавления в случае появления нового наименования и добавлять суммы из столбца D первого листа, по этому наименованию. Смысл в том, что нужна простая формула, насколько это возможно, однажды мне написали макрос, но таблица стала неудобной для перемещения столбцов и прочего. Благодарю заранее, мне нравится как Вы доступно изъясняетесь.
Извините, небольшая поправка. Если на втором листе зафиксированные наименования, то я при помощи СУММЕСЛИ этого добилась. А вот возможности не делать этого вручную, а чтобы Excel делал сам выборку из наименований не могу никак найти. А это очень важно для контроля ошибки в буквенной части наименований. Один незаметный пробел и сумма уже неверная...
Может надо сделать первый лист сделать сводной таблицей и потом вводить суммесли? Но сводная таблица не терпит пустых ячеек...
Левый ВПР в 2017-м?! Серьезно?! Лучше бы Николай обновил тему консолидация данных с разных листов с помощью Power Query, которая по умолчанию идет в Excel 2016, Office 365.
Вадим Окладников Nikolay Pavlov скорее сделает 100-е видео по функции ВПР и ГПР, чем Power Query. Сертифицированный тренер и эксперт в Excel нам всё азбуку объясняет, как сайентологи доказывают людям, что они не так говорят на своём родном языке, и дурачат их.
Спасибо за мнение, Николай учтет :)
Для информации: за весь прошлый год из 74 проведенных мной корпоративных тренингов Excel 2016 встретился аж целых 6 раз.
Умные таблицы тоже не часто используют, но видео по ним Николай исправно выкладывал:)
А если серьезно, то консолидация данных с разных листов, а также книг, без использования макросов стала возможна только при использовании Power Query.
Дык, я ж не спорю. У меня тут просто свой план по видеоурокам на 2017 год есть - и я по нему планомерно работаю. Про Power Query там есть, честное слово :)
2023
А как же ПРОСМОТР?
Да даже тупо через ЕСЛИ и массивные скобки можно сделать.
Сколько же можно эту ВПР мурыжить? С тех пор, как есть ИНДЕКС + ПОИСКПОЗ, все эти ВПР и ГПР, как минимум, не актуальны.
Если вы смотрели видео, то там кроме ИНДЕКС+ПОИСКПОЗ еще 4 способа разбирается как бы...
к сожалению эксель уже давно не актуален в использовании... слишком глупая и не логичная программа, как при работе с таблицами, так и если брать те же таблицы из сети интернет, прогружая их через вкладку данные. Как минимум, эксель не умеет работать с числами, вида 1:1, он их переименовывает в дату. Скажете, так поставьте текстовый формат.. Но тут тоже не задача, при обновлении этих данных, которые берутся с определенного сайта, формат автоматом меняется, и это еще раз доказывает, что эксель самая убогая программа которую вообще можно было придумать. Есть масса других примеров, где эксель только увеличивает твою работу и время на эту работу.