См., пожалуйста, файл - картинка в сообщении #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.
Спасибо.

PooHkrd, большое спасибо!
Зафиксировал в Закладках.

Xel, еще раз спасибо! Все получилось.
Да, Вы правы - надо читать и повышать уровень. Сейчас больше отталкиваюсь от текущих задач.
Буду признателен, если Вы сможете поделиться ссылками (если не это противоречит Правилам Форума).

Xel, спасибо за простую идею – слияние запросов.
Но этот способ оставляет в итоговом столбце пустые ячейки со значением null для тех элементов, которые не упомянуты в таблице замен.

Можно ли потом сделать новый пользовательский столбец и заполнить его по формуле IF (или ЕСЛИ)? (Как я бы сделал это в Excel).

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

Добрый день!

В видео от Николая Павлова был описан простой способ замены текста
https://www.planetaexcel.ru/techniques/7/13923/
Однако он имеет недостаток: меняется не вся ячейка целиком, а текст «внутри» ячейки. В результате могут появиться ненужные дублирования.

К примеру, имеем пары для замены:
«Амортизация» – новое значение «Амортизация ОС и НМА»
«Амортизация НМА» – новое значение «Амортизация ОС и НМА»

Результат выглядит так:
Амортизация ОС и НМА
Амортизация ОС и НМА НМА - здесь есть дублирование "НМА"

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

Спасибо.

Ок. Спасибо.

Понятно.

и спасибо за пример.

PS Конечная цель, проанализировать, как я использую свое время. Сколько в % времени уходит на тот или иной вид работ, как эти %% зависят от дня недели и\или времени суток, и др. В планах сделать что-то типа Dashbord: сводные таблицы, графики. Возможно, в дальнейшем – визуализация в BI…

спасибо за комплимент,

В моих планах было сделать следующее:
1) Все строки с ошибками скопировать на отдельный лист, в каждой строке справа добавить ячейку с тестовым описанием ошибок. Пример: «Длительность равна 0:00, Лишние пробелы в Поле «Группа работ».
2) В исходном листе заливкой указать все ячейки(!) с ошибками.
3) В исходном листе выбрать Автофильтр по Столбцу «Строка», т.е показать все ошибки. Установить курсор в начало такой отфильтрованной таблицы. Примечание: Автофильтр можно установить по номерам строк. Номер строки планировал взять из соответствующего столбца, т.е. не адрес ячейки.
3.1) При каждом запуске макроса чистить вышеуказанную заливку. И устанавливать Автофильтр на исходную таблицу в режиме «Показать Все».
4) Если ошибок нет – лист ошибок не создавать. Установить курсор в последнюю строку исходной таблицы.

Здесь, Вы можете заметить, есть дубляж: и отдельно ошибки на листе, и еще Автофильтр строк с ошибками в исходной таблице.

vikttur, спасибо за совет.

Одно уточнение, пожалуйста. Мне следовало бы сразу сказать обо всех деталях. Извините, не хотел заранее «грузить».
Проверка ячеек идет не только по числам, но и по тексту: ищем ячейки с пробелами в начале или в конце, и еще ищем пустые ячейки. А также для двух числовых столбцов выполняется проверка, что число из нее входит в массив допустимых значений. Я выложил файл, где есть выдержка из таблицы. В следующем сообщении.

Это как-то меняет подход к решению задачи?

Mershik, В моем случае – это можно назвать TimeSheet. Пример и пояснения в файле. Я веду его уже год с небольшим. Сейчас стал анализировать (графики, сводные таблицы) и обнаружил ошибки. Конечно, можно все поправить руками. Но мне стало интересно – как решить эту задачу макросом. Появилась мотивация начать изучать макросы. За год в реальном файле уже 7975 строк.

Добрый день!

Есть таблица в Эксель. 9 столбцов, примерно 8 тыс. строк, в потенциале 20 тыс. Каждый столбец должен заполняться по определенным правилам. Задача – проанализировать таблицу и скопировать на другой лист все строки, в которых есть ошибки с указанием ошибки или списка ошибок, если в одной строке их несколько. Из 9 столбцов – только 5 подлежат проверке, в остальных содержаться формулы.

Пока планирую методом перебора каждой строки в цикле. Примерно так. Это набросок, а не реальный код.



Прошу дать совет, как лучше организовать алгоритм. Возможно, есть более быстрый и оптимальный, чем тот, который использую, т.е. чем перебор в цикле каждой строки. Я новичок в VBA, поэтому прошу строго не судить и ответить подробнее, по возможности

Заранее большое спасибо.