Интересно и познавательно.

AlienSx, еще раз спасибо. Несколько вариантов - есть из чего выбрать.

Вопрос offtop, если можно.
Верно ли я понимаю, что если просматриваемый столбец и столбец с условиями содержат не фразы, а только слова, то проще и быстрее будет воспользоваться объединением таблиц join?

Коллеги, спасибо всем за реальную помощь!

AlienSx, спасибо за видео. Я тоже думал использовать Split. :)
Ваш второй вариант работает. Мне он более понятен, т.к использует знакомую List.Accumulate.

Насколько понимаю, оба Ваших Варианта создают доп. столбцы одновременно – это должно ускорять выполнение запроса при больших объемах.
А также – Ваш код несложно доработать, если количество этих доп. столбцов изменится. Верно?

Мои объемы раза в 4 меньше. Поэтому быстродействие не так важно.

С List.Accumulate.  Вы говорите, что эта функция не подходит для этой задачи.
Коллега прислал решение, где она используется.  Сообщение 4 в этом чате выше.

Я пока не так глубоко знаю PQ и поэтому хотел бы разобраться более подробно.

AlienSx, , и Вам большое спасибо!
Ваш код формирует сразу 2 столбца. Это здорово. Хотя, если понадобиться три, мне будет сложновато его подредактировать...

Подскажите, пожалуйста, в данном случае, в чем преимущество  List.PositionOf?
Спасибо.

Ivan.Z, большое спасибо!
Ваш код работает. Вы очень помогли.

У меня 2016 Excel. При открытии файла было предупреждение, что запрос сделан в более старшей версии Excel и может не работать. Я проверил - на первый взгляд, все работает. Такое сообщение можно игнорировать?

Если есть возможность, пожалуйста, расскажите кратко, почему несколько операторов "in"? Спасибо.

AlienSx, на самом деле у меня есть выписка из клиент банка. Мне нужно ее преобразовать в таблицу, которую я потом загружаю в учетный файл Excel. В зависимости от назначения платежа определяются Контрагент, Статья бюджета и др. поля (столбцы) для аналитики. Эти столбцы добавляются к существующей выписке. Оригинальное назначение платежа тоже остается.

Если найдется два соответствия, то брать первое. Такая ситуация, конечно, возможна. Тогда придется регулировать ее порядом строк в таблице соответствия? Я не тестировал этот случай.

По поводу моего файла. Хоть я и делаю замену, PQ создает новый столбец, и это ок. Этот способ я нашел в видео от Николая Павлова на этом сайте. И хотел заменить Text.Replace на что-то другое, но не получилось. А сам файл выложил, чтобы лучше объяснить свой вопрос.

Добрый день!

Обрабатываю банковскую выписку. Хочу автоматизировать заполнение дополнительных столбцов, значения в которых зависит результатов поиска соответствия.

Подскажите, пожалуйста, как реализовать на PQ создание такого столбца по таблице соответствия.

Чтобы избежать множества if, пытался решить задачу в помощью List.Accumulate. Но смог заменить только часть текста, а не всю ячейку.

Подробности в прилагаемом файле.
Спасибо.

См., пожалуйста, файл - картинка в сообщении #4.  Строки 22 и 23.

С помощью PQ? Так это и есть для меня сложный вопрос - как это сделать, если не вводить в исходные данные дополнительные поля?

Да. Я тоже все больше склоняюсь к тому, что надо в исходные данные добавить столбец (столбцы)...

Дача у нас одна. Поэтому две локации:  квартира и дача.

И поле "Дата", видимо, тоже надо задействовать...

AlienSx,   еще раз  спасибо Вам за помощь!
Пойду грызь гранит PQ. :)

Вот здесь как раз и остановился, т.к. не знаю, как это реализовать в PQ...
А как эта функция помогает обращаться к предыдущей строке?
Подскажите, пожалуйста, как...

PS
Когда начал, в том числе с Вашей помощью, копать глубже, стало понятно, что задача совсем не тривиальная...

25-го с 9-30 до 10-00 находился за рулем. Потом была остановка. Возобновил движение в 14-00 и прибыл на место в 16-30. Поэтому считаем, что 25-е число надо вывести в итоговую таблицу.
В этом случае в итоговую таблицу нужно вывести только 25-е число.

PS
Коллеги, спасибо за вопросы. Внимательный взгляд со стороны помогает и проблему понять, и вопрос более четко сформулировать.

PPS
Наверное, пришло время сформулировать задачу «человеческим» языком. Или надо было сделать это раньше?

Есть дача, на которой, как обычно это бывает, много дел (покос травы, мелкий ремонт, обрезка деревьев и др.). И вот задумался, а сколько времени на даче посвящено именно отдыху (прогулки, купание, чтение, шашлык, телевизор и т.п.). Я веду собственный тайм-шит, в котором каждый день расписан с точностью до 30 минут. Данные уже за несколько лет. И хотел получить какой-то более-менее удобный способ выбрать из этой таблицы только те дни, в которые находился на даче, включая дни приезда и отъезда. И потом проанализировать время (длительность) в % на дела и отдых.

AlienSx, но поскольку часть работы уже проделана, и не хочется останавливаться на полдороги, хотелось бы решить задачу при условии, что мы не ездим «одним днем».

AlienSx, Вы верно заметили. Мне следовало оговорить это в условиях задачи.

Я подразумевал, что поездок «одним днем» не бывает. И поэтому в запросе включил шаг «Удаление дубликатов». Чтобы 25 мая было только «в одну сторону».

Но в реальной жизни, конечно, могут быть поездки «туда \ обратно» в течение одних суток. Или сложнее – уехали утром 25-го, а приехали в 1 час ночи 26-го. Это значит, что либо надо в исходные данные вводить признак направления, либо придумывать более сложный алгоритм, который анализирует последовательность признака «Дорога».

Во втором случае, наверное, Power Query не самый удобный инструмент?

Павел \Ʌ/, , спасибо за отклик.

Если я Вас правильно понял, и надо просто считать вхождения слова «Дорога» и определять четные \ нечетные, то результат будет с ошибкой. «Дорога» в конце одних суток и «Дорога» в начале следующих определятся как два разные дороги, а по сути это одна.

В приложенном файле на картинке постарался проиллюстрировать эту мысль. Дорога «туда» была в ночь с 24-го на 25-е (две строки в таблице), а дорога «обратно» - 26-го числа днем.

Или Вы о другом? Покажите, пожалуйста, на примере, что Вы имеете в виду. Спасибо.

PS
Уже отправил сообщение и понял, что надо уточнить задачу.  Ведь ночная дорога может быть не только "туда", но и "обратно".  И это тоже надо как-то отлавливать.

Добрый день, коллеги!

Есть список событий – таблица: Дата, Начало, Окончание, Длительность, Группа. Необходимо выбрать дни, которые находятся между группой «Дорога» включительно.

Имеется в виду, что сначала едем «туда», потом «обратно». И надо проанализировать дни, когда мы были «там», включая дни приезда \ отъезда. При этом надо взять все события за выбранные дни.

Реализовал это на Power Query. Мой запрос выбирает из списка именно такие дни для дальнейшего анализа. См. приложенный файл. Там четыре вкладки: исходные данные, схема решения,  решение и вопрос. В реальной таблице около 35,000 строк.

В принципе, все работает. Но недавно столкнулся с проблемой. Если в полночь ты находишься "в дороге", то алгоритм неправильно определяет дни «туда \ там \ обратно». Прошу помощи, что можно с этим сделать? Без макросов.

Спасибо.

Большое спасибо StepanWolkoff,   #5   и  Андрей VG,     #6.    Ваши примеры  очень помогли.

Добрый день!
Вопрос все еще актуален.

Подскажите, пожалуйста, ссылка на файл на Яндекс диске - уникальна? Имеет ли значение, кто ее "возьмет" - Владелец или Программист?


Например, где?

Добрый день!

Знаю, что не рекомендуется повторять вопрос в разных форумах. Приношу извинения. Но не получил ответа на дружественном форуме.

Файл источник и файл с запросом PQ находятся на Яндекс Диске на аккаунте «Владельца». Владелец синхронизировал Яндекс Диск с личным ПК. Владелец предоставил полный доступ «Программисту» к папкам с этими файлами, который также синхронизировал эти папки со своим личным ПК.

Владелец сам вносит изменения в файл источник. Он не знает PQ, ему нужна только «кнопка Обновить», чтобы можно было пользоваться результатом. Запрос должен написать Программист, который работает на удаленке.

Вопрос.
Можно ли и если «можно», то как написать запрос, чтобы он работал и у Владельца, и у Программиста?

Спасибо.

Ссылка http://www.excelworld.ru/forum/2-50998-1

Добрый день!
Если еще можно в этой теме? уточняющий вопрос:
что лучше: доп. столбцы с вычислениями и сводная или все вычисления "спрятать"  в PQ и делать сводную на основе запроса, как показал Behruz A.N.?

с PQ немного знаком.
Вы сделали запрос и указали его в качестве источника для сводной.  Верно ?

Behruz A.N., спасибо!
Объясните, пожалуйста, как Вы это сделали. Макрос?

Добрый день!
Подскажите, пожалуйста, можно ли в вычисляемом поле в сводной настроить формулу с условием ЕСЛИ по тексту.
Формулу вида: = если( Документ = "Банковская выписка"; Сумма * -1; Сумма)
Спасибо.

Если еще актуально.
Все три функции дают одинаковый результат, если:
между платежами 365 дней,
в функции СТАВКА установлено, что выплата производится в конце периода.
Почти одинаковый, т.к. все же есть погрешность при применении итеративного метода.

Добрый день!
Этот вопрос обсуждался давно.
Сейчас что-то изменилось?

Есть файл Excel, он лежит на Яндекс Диске моего товарища. У меня есть полный доступ к папке.
Можно ли в запросе PQ сделать ссылку на этот файл? Если «да», то как?

Вопрос возник с решением отказаться от Гугл и поиском чем заменить функцию IMPORTRANGE.
Спасибо.

Prosvetov, добрый вечер! Вам нужно адрес заменить на название города? Вот такие формулы в Excel смогут помочь?

Сергей Федоров,  поискав решение,  понял, что данную задачу нельзя решить с помощью PQ. Но можно добавить в результат запроса (уже в Excel) столбец с формулой типа:
или для "умной таблицы":
Возможно, не самое удачное решение, но зато без макросов.

Добрый день!
Можно ли поднять эту давнюю тему или надо создать новую?
Возможно ли решение задачи без макросов?
Я получаю в выгрузке текст, но он не становиться формулой, пока не зайдешь в ячейку и не нажмешь Enter.
Спасибо.