Поднял настроение комментарием :) В строке формул поставь курсор на адрес какой-нибудь ячейки, и нажми несколько раз клавишу F4 - посмотришь, что будет ;) Удачи!
Просто невероятно! Спасибо Вам. Ненависть коллег еще больше усилилась после нового приема! Странно,но мне пришлось убрать -1, чтобы формула заработала как надо в моем случае!
Спасибо, Николай! Ещё нередко встречалась на форумах такая формула для поиска последнего совпадения: =ПРОСМОТР(2;1/(диапазон_столбец=искомое);диапазон_столбец_чего_выводим) (скопировал с Planetaexcel, от Максима Зеленского) А я сам раньше для решения такой задачи делал обратную сортировку таблицы, чтобы последние стали первыми, и потом ВПР.
Функции, работающие с динамическими массивами, это офигенно удобные штуки. Давно ими пользуюсь в Google Sheets, наконец-то и в excel появились. Жаль только, пока Office 365 станет популярным, ни один год пройдет. Я фрилансю на эксельке и когда делаю для кого-то, кто пользуется старой версией excel, не использую их. А эти кто-то 100% моих клиентов)))
Огромное спасибо! С удовольствием совершенствуюсь благодаря Вам и вашим урокам. Не могли вы подсказать, возможно ли шапку таблицы вмонтировать в верхний колонтитул.
Николай, благодарю вас за такую полезность! Ваша формула меня спасла. Работала исправно до последнего момента. Почему-то везде работает хорошо, но в одном столбце капризничает. Где есть совпадение выводит сумму, где нет - название столбца. Подскажите что делать в такой ситуации?
В способе с PowerQuery можно "срезать путь": если после сортировки по дате удалить дубликаты по столбцу с клиентами, то останутся только первые, т.е. самые актуальные записи по каждому клиенту 💡
Не надо усложнять) Если нет столбца по которому можно сортировать в обратном хронологическом порядке, то просто добавляем этот столбец (1,2,3 и т.д.) и сортируем по этому столбцу от большего к меньшему) ну и стандартный впр)
Николай, подскажите, пожалуйста, где искать ошибку, если при использовании последнего способа после сортировки по убыванию дат и группировки по "клиенту" в таблице Подробности записи тем не менее оказываются не отсортированными по убыванию, т.е. первой идет самая ранняя дата?
Как всегда здорово. Николай, вы не знаете почему в редакторе PQ не работает замена значений по маске ? Может какой синтаксис другой ? Есть столбец с менеджерами. В начале всегда фамилия, а потом то имя, то инициалы, то еще как-то. Достаточно только фамилии. В обычном excel'е делаю замену по маске " *" на "" (без кавычек), оставляя тем самым только нужные фамилии. А в PQ не получается, замена не производится. Не знаете причину ?
Николай топ конечно эксперт. Может Вы знаете ответ на мой вопрос или могет ли эксель такое, перерыл все форумы и книги ваши тоже.. Суть вопроса- составляется диаграмма ганта с использованием условного форматирования( учился на вашем примере), так вот есть ли возможность автоматического заполнения заливаемых ячеек ещё числом рабочих трудящихся в этот день, от чего будет формироваться график движения рабочей силы. Приходится вручную расставлять кол-во людей по этим дням.) Буду благодарен, кто может сталкивался.
Буквально пару недель назад возникла такая необходимость извлечения последней даты из журнала. Вопрос был интересный, поэтому загуглился за пять минут. А в подавляющем большинстве задач я не пользуюсь диапазонами, а указываю целые столбцы.
Привет, можно ли в Power query отформатировать неверный ввод процентных значений. Иногда люди вводят "6,2%" вместо "0,62%". И если я конвертирую 6,2 то у меня выходит неверный показатель 6200%. Можно ли как-то при форматировании это изменить?
Сортируете по дате «по убыванию» самая свежая дата будет вверху И потом удаляете дубликаты Вот и все Удаление дубликатов оставляет самые «верхние» значения так оно работает
подскажите пожалуйста, как можно суммировать даты с одной страницы в табель другой страницы? пожалуйста объясните... на одной странице ежедневный часовой табель , на второй странице ежемесячный расчётный лист - одна из ячеек под названием "насчитано по табелю" ссылается на первый лист... очень жду вашей помощи... заголовки первой страницы - Дата - ВремяНач. - ВремяКон. - ИтогоЧасов - , заголовки второй страницы - Месяц - НачисленоПоТабелю -
Приветствую.Столкнулся с такой задачей есть таблица, где по вертикали номера вагонов а по горизонтали даты прохождения ТО в шапке(ТО1 ; ТО2 ; ТО3 итд...) , Надо чтоб по номеру вагона функция определяла крайнюю дату прохождения ТО ...Но у каждого вагона в данный момент проходит разные ТО поэтому крайняя дата в разных столбцах....Пока что сделал так ...сбоку отдельным столбцом функцией наибольший вывел крайнюю дату , а уже этот столбец про ВПРэрил...но хотелось бы собрать это в единую формулу .....этакий ВПР 2 D но где заранее неизвестен столбец!)
Что делать если необходимо найти все совпадения? допустим в таблице несколько однофамильцев, нам нужен тот который находится посредине. Как сделать, чтобы ВПР пропускал первого найденного и так далее пока не дойдет до нужного?
только на слабых машинах, но у вас большое преимущество учитесь писать оптимальные запросы чтоб не перегружали систему это очень развивает навыки )))))
Эксель должен тормозить только когда обновляете запросы. В обычном состоянии PQ на работу Excel никак влиять не должен. Проверьте обновления - может в них дело?
@@planetaexcel Очень буду благодарна, если наведете на правильное направление. Я кстати заказала у Вас книгу по PQ, многие фишки в PQ нравятся, но жутко все виснет, что начинает возникать мысль о переходе на старые обычные варианты формул. Очень надеюсь, что книга убедит меня не сбегать от pq. Суть вопроса: при агрегирование всего лишь 30 тысяч строк всё жутко виснет. Агрегирование идёт таблицы самой с собой по трем, в идеале 5 ключевым столбцам. Что можно сделать? Внизу обработка показывает уже 10 Гб и концов не видно (((. Ещё один важный вопрос: есть основная таблица, к ней через объединение берутся запчасти из других таблиц. Я это делаю последовательно, т.е. основная таблица+запчасть от другой является запросом для следующего запроса. Это очень неудобно. Наверное, лучше основную таблицу объединять каждый раз по отдельности с другой, а потом уже все вместе объединить, используя все столбцы основной как ключевые поля? Или вообще лучше перейти на power pivot, но я им совсем не владею (((. Очень буду признательна, если хотя бы ответите на один из моих вопросов!!!!! 🙇🏻♀️🙇🏻♀️🙇🏻♀️🙇🏻♀️😭
@@mandarina2016 проблемы при расчете запроса могут возникать различными причинам: сложность самого запроса, количество обрабатываемых данных, форматы источника данных, наличие параллельных процессов, и еще много чего. что влияет именно у вас вопрос... похожую проблему обсуждали на планете эксель www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=116604&TITLE_SEO=116604-power-query-otkryvaet-fayly-istochniki-bez-komandy&MID=966767#message966767 может вам поможет, или выложите свой пример на форуме там помогут разобраться
А подскажите, пожалуйста, как сделать формулу, чтобы искало последнее значение сначала на текущем листе, но выше вводимого значения, а если не находится, то на другом листе? У меня таблица, где каждому новому заданию присваивается номер, соответственно, я ввожу имя клиента, а в ячейке справа от него новый номер задачи должен выскочить, последний +1. Но последняя задача по клиенту могла быть как в текущем месяце (на этом же листе), так и в прошлом (на другом листе). Для отдельных случаев я написала, а как объединить? У меня было такое предположение, но оно почему-то не работает: =IFERROR(XLOOKUP(B2;$B$1:$B1;$C$1:$C1;;0;-1);XLOOKUP(B2;'Ноябрь 2022'!$B$2:$B$62;'Ноябрь 2022'!$C$2:$C$62;;0;-1))+1 Что не так? Буду очень благодарна за ответ!
Когда утро понедельника начинается с хорошего!
Николай, спасибо огромнючее за Ваш труд!
👍
Николай, спасибо огромное! Очень полезный выпуск. А "проэфчетырить" - это что-то 😀
Поднял настроение комментарием :)
В строке формул поставь курсор на адрес какой-нибудь ячейки, и нажми несколько раз клавишу F4 - посмотришь, что будет ;) Удачи!
@@АлексейГогузев-ж5и да я знаю, что будет - разные варианты закрепления адреса долларом.
@@Коля-н3з А, ну извини, не хотел тебя задеть... :)
@@АлексейГогузев-ж5и все норм ;)
Просто невероятно! Спасибо Вам. Ненависть коллег еще больше усилилась после нового приема! Странно,но мне пришлось убрать -1, чтобы формула заработала как надо в моем случае!
в очередной раз хочется сказать,- НИКОЛАЙ -ЧЕЛОВЕЧИЩЕ!!!
Отлично! Который раз убеждаюсь в силе PQ.
Николай, я Вас очень люблю!
Спасибо! Подача материала 👍, многое узнал из Ваших примеров - применяю на практике! ЖдемС нового материала!
Николай как волшебник!
Как всегда круто!
Спасибо! Отличное обучающее видео! обязательно попробую на практике!!! - Это искал!
Огромное спасибо! низкий поклон! большой респект! снимаю шляпу! всех благ Вам Учитель!
Ого! Супер круто! Спасибо огромное! Сейчас буду применять. Что-то точно должно сработать!
Вчера посмотрел это видео, а уже сегодня оно пригодилось на практике. Спасибо, Николай!
Очень круто.
Даже знаю - как это буду применять.
Спасибо!!!
Вы отличный преподаватель, очень интересно объясняете
Круто, Николай огромное Вам спасибо за этот крутой урок.
Отсылка к "Доводу" - филигранный юмор, браво!
:D
Три раза пересмотрел этот фильм, но до конца так и не понял всю логику. Надеюсь, в этом видео у вас такого не будет :)
@@planetaexcel лучше не искать смысла там где его нет. Сюжет понятен только автору и его престарелой бабушке...
Благодарю. Осталось дождаться обновлений
благодарю вас за этот урок ! Без этого мне бы приходилос пройтис 10000 строк и в ручную собират оплату и заказа ! Вы мне экономили уйму времени
👍👍👍👍👍
Спасибо, Николай!
Ещё нередко встречалась на форумах такая формула для поиска последнего совпадения:
=ПРОСМОТР(2;1/(диапазон_столбец=искомое);диапазон_столбец_чего_выводим)
(скопировал с Planetaexcel, от Максима Зеленского)
А я сам раньше для решения такой задачи делал обратную сортировку таблицы, чтобы последние стали первыми, и потом ВПР.
Спасибо, Виктор! Век живи - век учись! А у Максима я у учусь постоянно :)
Функции, работающие с динамическими массивами, это офигенно удобные штуки. Давно ими пользуюсь в Google Sheets, наконец-то и в excel появились. Жаль только, пока Office 365 станет популярным, ни один год пройдет. Я фрилансю на эксельке и когда делаю для кого-то, кто пользуется старой версией excel, не использую их. А эти кто-то 100% моих клиентов)))
К сожалению, да - подтверждаю. Из моих клиентов-компаний на Office 365 пока процентов 10% (и это в Москве).
Гениально! )) Спасибо большое, Николай !
Хайпуем даже в Экселе)) прелестно!
Как всегда круто, понятно , да еще несколько вариантв исполнения
Огромное спасибо!
С удовольствием совершенствуюсь благодаря Вам и вашим урокам.
Не могли вы подсказать, возможно ли шапку таблицы вмонтировать в верхний колонтитул.
Большое спасибо за видеоурок!
ПРОЭФЧЕТЫРИТЬ
О, видео с моего любимого канала приехало! Спасибки )
Как всегда все очень круто!
17:27 Ну или воспользоваться функцией Table.Max:
= Table.Max( [Подробности] , each [Дата покупки] )
И тогда можно ничего не сортировать предварительно
Можно во внешний Filter не заворачивать, а просто указать 3 параметр у Index: INDEX(SORT(FILTER($A$2:$D$24,$C$2:$C$24=G5),2,-1),1,{1,4}).
Николай, благодарю вас за такую полезность! Ваша формула меня спасла. Работала исправно до последнего момента. Почему-то везде работает хорошо, но в одном столбце капризничает. Где есть совпадение выводит сумму, где нет - название столбца. Подскажите что делать в такой ситуации?
Низкий поклон
Видео не смотрел, но за картинку лайк
В способе с PowerQuery можно "срезать путь": если после сортировки по дате удалить дубликаты по столбцу с клиентами, то останутся только первые, т.е. самые актуальные записи по каждому клиенту 💡
Отлично!
Не надо усложнять) Если нет столбца по которому можно сортировать в обратном хронологическом порядке, то просто добавляем этот столбец (1,2,3 и т.д.) и сортируем по этому столбцу от большего к меньшему) ну и стандартный впр)
Просмотрx - явно не усложнение
Добрый день. Подскажите, пожалуйста, а зачем добавлять -1?
Николай, подскажите, пожалуйста, где искать ошибку, если при использовании последнего способа после сортировки по убыванию дат и группировки по "клиенту" в таблице Подробности записи тем не менее оказываются не отсортированными по убыванию, т.е. первой идет самая ранняя дата?
Это прекрасно)))
Прочитал все комментарии, ничего нового не сказать, осталось найти Николая, поймать и... обнять!!!
Как всегда здорово.
Николай, вы не знаете почему в редакторе PQ не работает замена значений по маске ? Может какой синтаксис другой ?
Есть столбец с менеджерами. В начале всегда фамилия, а потом то имя, то инициалы, то еще как-то. Достаточно только фамилии.
В обычном excel'е делаю замену по маске " *" на "" (без кавычек), оставляя тем самым только нужные фамилии. А в PQ не получается, замена не производится. Не знаете причину ?
Николай топ конечно эксперт. Может Вы знаете ответ на мой вопрос или могет ли эксель такое, перерыл все форумы и книги ваши тоже.. Суть вопроса- составляется диаграмма ганта с использованием условного форматирования( учился на вашем примере), так вот есть ли возможность автоматического заполнения заливаемых ячеек ещё числом рабочих трудящихся в этот день, от чего будет формироваться график движения рабочей силы. Приходится вручную расставлять кол-во людей по этим дням.) Буду благодарен, кто может сталкивался.
Буквально пару недель назад возникла такая необходимость извлечения последней даты из журнала. Вопрос был интересный, поэтому загуглился за пять минут. А в подавляющем большинстве задач я не пользуюсь диапазонами, а указываю целые столбцы.
14:53 К дате можно также формат даты прикрутить, тогда дата автоматом будет появляться дальше при обновлении таблицы.
И, как итог, часто бывает нужна накопительная сумма с указанием последней даты, поэтому часто юзается sumif(s).
Привет, можно ли в Power query отформатировать неверный ввод процентных значений. Иногда люди вводят "6,2%" вместо "0,62%". И если я конвертирую 6,2 то у меня выходит неверный показатель 6200%. Можно ли как-то при форматировании это изменить?
Сортируете по дате «по убыванию» самая свежая дата будет вверху
И потом удаляете дубликаты
Вот и все
Удаление дубликатов оставляет самые «верхние» значения так оно работает
В этом случае можно и не удалять, впр все равно возьмёт только первое вхождение, остальные совпадения отсеются и так
Спасибо
подскажите пожалуйста, как можно суммировать даты с одной страницы в табель другой страницы? пожалуйста объясните... на одной странице ежедневный часовой табель , на второй странице ежемесячный расчётный лист - одна из ячеек под названием "насчитано по табелю" ссылается на первый лист...
очень жду вашей помощи...
заголовки первой страницы - Дата - ВремяНач. - ВремяКон. - ИтогоЧасов - ,
заголовки второй страницы - Месяц - НачисленоПоТабелю -
Приветствую.Столкнулся с такой задачей есть таблица, где по вертикали номера вагонов а по горизонтали даты прохождения ТО в шапке(ТО1 ; ТО2 ; ТО3 итд...) , Надо чтоб по номеру вагона функция определяла крайнюю дату прохождения ТО
...Но у каждого вагона в данный момент проходит разные ТО поэтому крайняя дата в разных столбцах....Пока что сделал так ...сбоку отдельным столбцом функцией наибольший вывел крайнюю дату , а уже этот столбец про ВПРэрил...но хотелось бы собрать это в единую формулу .....этакий ВПР 2 D но где заранее неизвестен столбец!)
Ну, идея с отдельным столбцом - вполне ОК, я бы также делал, наверно. Не надо стараться запихнуть всё в одну формулу :)
До Нолана у Булгакова - АБЫРВАЛГ "Собачье сердце"
Точно! :)
как всегда 👍
А есть вариант с поиском в разных диапазонах на разных листах?
ПРОСМОТРХ может искать на одном листе, а выдавать результаты с другого - без проблем.
Вам отправлял письмо ! С просьбой
лайк до просмотра)))
👍👍👍🔥
Что делать если необходимо найти все совпадения? допустим в таблице несколько однофамильцев, нам нужен тот который находится посредине. Как сделать, чтобы ВПР пропускал первого найденного и так далее пока не дойдет до нужного?
Делал уже видос про это - см. "Мульти-ВПР" :)
А у всех Эксель тормозит, если PQ включать в работу?
только на слабых машинах, но у вас большое преимущество учитесь писать оптимальные запросы чтоб не перегружали систему это очень развивает навыки )))))
У меня ооочень тормозит ((( машинка хорошей считается. Наверное, я как-то неграмотно и неоптимально запросы делаю.
Эксель должен тормозить только когда обновляете запросы. В обычном состоянии PQ на работу Excel никак влиять не должен. Проверьте обновления - может в них дело?
@@planetaexcel Очень буду благодарна, если наведете на правильное направление. Я кстати заказала у Вас книгу по PQ, многие фишки в PQ нравятся, но жутко все виснет, что начинает возникать мысль о переходе на старые обычные варианты формул. Очень надеюсь, что книга убедит меня не сбегать от pq. Суть вопроса: при агрегирование всего лишь 30 тысяч строк всё жутко виснет. Агрегирование идёт таблицы самой с собой по трем, в идеале 5 ключевым столбцам. Что можно сделать? Внизу обработка показывает уже 10 Гб и концов не видно (((. Ещё один важный вопрос: есть основная таблица, к ней через объединение берутся запчасти из других таблиц. Я это делаю последовательно, т.е. основная таблица+запчасть от другой является запросом для следующего запроса. Это очень неудобно. Наверное, лучше основную таблицу объединять каждый раз по отдельности с другой, а потом уже все вместе объединить, используя все столбцы основной как ключевые поля? Или вообще лучше перейти на power pivot, но я им совсем не владею (((. Очень буду признательна, если хотя бы ответите на один из моих вопросов!!!!! 🙇🏻♀️🙇🏻♀️🙇🏻♀️🙇🏻♀️😭
@@mandarina2016 проблемы при расчете запроса могут возникать различными причинам: сложность самого запроса, количество обрабатываемых данных, форматы источника данных, наличие параллельных процессов, и еще много чего. что влияет именно у вас вопрос... похожую проблему обсуждали на планете эксель www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=116604&TITLE_SEO=116604-power-query-otkryvaet-fayly-istochniki-bez-komandy&MID=966767#message966767 может вам поможет, или выложите свой пример на форуме там помогут разобраться
Если я не могу владеть EXCEL - НИКТО НЕ МОЖЕТ.
Радиации много выделяется при инвертировании 🤣
После этой формулы выскакивает н/д, но точно знаю что данные есть
написали вам письмо на почту с вопросом
Было как то привычно "провэпээрить", а теперь что: как то скучно "просмотреть"
ПросмотретьХЭ!
@@planetaexcel Так вот как оно теперь в профессиональном сообществе называется, запомню 😁
th-cam.com/video/a3ARImSTxuQ/w-d-xo.html - Представьте мое удивление (для подписчиков Nikolay Pavlov) , когда увидел аватарку автора))))
голос очень отличается
Мы даже как-то виделись - в соседних аудиториях тренинги вели на Каланчевке. Долго разбирались кто чей студент, помню :)
Возьми в ученики
2023
Шайтанама)
На листе ДМ неправильный результат
ИНДЕКС($A$2:$A$24;МАКС(ЕСЛИ($B$2:$B$24=G5;СТРОКА($B$2:$B$24)-1))) - 65 символов; ИНДЕКС($A$2:$A$24;МАКС(($B$2:$B$24=G5)*(СТРОКА($B$2:$B$24))-1)) - 63 символа, Привет из избушки )))))))))))))))))))))))
Огонь! Спасибо!
@@planetaexcel на самом деле это вам огромное спасибо, Николай, всему учился на вашей Планете Эксель ))))
Не годится никакой.
Нужна формула, но с массивами немерино жрет ресурсов, как только щаписей стаеовитя ч3ть больше 10
Зачем нигнер на заставке?
Скучно... уже было....Где видео по Лямба-функции ? :)
Мне ее еще не завезли :)
А подскажите, пожалуйста, как сделать формулу, чтобы искало последнее значение сначала на текущем листе, но выше вводимого значения, а если не находится, то на другом листе?
У меня таблица, где каждому новому заданию присваивается номер, соответственно, я ввожу имя клиента, а в ячейке справа от него новый номер задачи должен выскочить, последний +1. Но последняя задача по клиенту могла быть как в текущем месяце (на этом же листе), так и в прошлом (на другом листе). Для отдельных случаев я написала, а как объединить?
У меня было такое предположение, но оно почему-то не работает:
=IFERROR(XLOOKUP(B2;$B$1:$B1;$C$1:$C1;;0;-1);XLOOKUP(B2;'Ноябрь 2022'!$B$2:$B$62;'Ноябрь 2022'!$C$2:$C$62;;0;-1))+1
Что не так?
Буду очень благодарна за ответ!