Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » Spooker
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1
Подготовка данных для детализированной сводной таблицы "План-Факт" в Power Query если строк данных "Факт" больше чем строк "План"
 
#
04.07.2019 23:24:22
Добрый вечер!
Мне кажется, что таких тем уже не один десяток, но очень прошу помочь решением (или ссылкой на аналогичную тему)
Excel 2010, есть исходные таблицы "План" и "Факт" , связываются по одному из столбцов детализации и месяцу. Таблица "Факт" детализирована на один уровень глубже, чем "План".
При обычном джойне в PQ данные плана конечно же задваиваются (см. "Яблоки"):
Необходимо представить данные примерно в таком виде сводной как на второй картинке (показать все уровни детализации):
Файл-пример во вложении
Изменено: Spooker - 04.07.2019 23:37:20
 
Перейти    
Power Query: как отфильтровать все строки с одинаковым id, если значение хотя бы в одной строке соответствует заданному условию.
 
#
11.06.2018 19:10:39
О, сколько всего интересного! Спасибо!
belsergey, List.Min , точно! я же не додумалась.
Андрей VG, спасибо за объяснение, буду разбираться. Справка Microsoft у меня открыта, но не всегда все понимаю.
 
Перейти    
Power Query: как отфильтровать все строки с одинаковым id, если значение хотя бы в одной строке соответствует заданному условию.
 
#
11.06.2018 18:08:15
Андрей VG, спасибо!
Если возможно, объясните, пожалуйста, вот эту запись  ...(subTable) =>...
Я уже три недели переделываю свои старые таблицы под PQ, но пока мне трудно отойти от логики формул Excel и освоить синтаксис М.
 
Перейти    
Power Query: как отфильтровать все строки с одинаковым id, если значение хотя бы в одной строке соответствует заданному условию.
 
#
11.06.2018 14:40:57
Добрый день. Есть таблица с данными грузооборота вида id/наименование/вес/кол-во мест/ и т.д. С одним id (номером документа) могут вносится несколько строк наименований.
Мне нужно исключить из дальнейших расчетов все строки с одним id, если хотя бы одна из ячеек такой группы строк пустая (незаполненная).
У меня получился вот такой код:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="данные"]}[Content],
    AddedCustom = Table.AddColumn(Source, "Custom", each if [Наименование]<>null and [вес]<> null and [#"кол-во мест"]<> null and [категория]<> null then [id] else null ),
    GroupedRowsByID = Table.Group(AddedCustom, {"id"}, {{"CountID", each Table.RowCount(_), type number}, {"tab", each _, type table}}),
    GropedRowsByNulls = Table.Group(AddedCustom, {"Custom", "id"}, {{"CountNulls", each Table.RowCount(_), type number}}),
    Joined = Table.Join(GroupedRowsByID, {"id"},GropedRowsByNulls, {"id"}),
    #"Added Conditional Column" = Table.AddColumn(Joined, "Custom.1", each if [CountID] <> [CountNulls] then null else [CountID]),
    Filtered = Table.SelectRows(#"Added Conditional Column", each ([Custom.1] <>null)),
    Expanded = Table.ExpandTableColumn(Filtered, "tab", {"Наименование", "вес", "кол-во мест", "категория"}, {"Наименование", "вес", "кол-во мест", "категория"}),
    #"Removed Other Columns" = Table.SelectColumns(Expanded,{"id", "Наименование", "вес", "кол-во мест", "категория"})
in
    #"Removed Other Columns"
Как мне кажется, конструкция очень громоздкая (и некрасивая).
Вопрос - можно ли решить задачу за меньшее число шагов?
 
Перейти    
Выборка уникальных записей из умной таблицы формулой
 
#
18.09.2017 23:11:42
_Igor_61, спасибо!
Влад, то, что нужно!  
 
Перейти    
Выборка уникальных записей из умной таблицы формулой
 
#
18.09.2017 22:30:52
)) Я опасаюсь, что подменю одну проблему другой (сможет ли/не забудет ли другой пользователь разрешить работу макросов в файле).
 
Перейти    
Выборка уникальных записей из умной таблицы формулой
 
#
18.09.2017 21:54:16
Я сейчас так и делаю. Но дело в том, что я эту формулу пытаюсь применить в другой очень большой таблице, в которую ежедневно вносятся данные несколькими людьми, и они (люди) забывают обновить формулы, а в результате - неверные данные в сводных таблицах.
 
Перейти    
Выборка уникальных записей из умной таблицы формулой
 
#
18.09.2017 21:33:57
Добрый день,
При преобразовании диапазона из этого примера http://www.planetaexcel.ru/techniques/14/103/ в умную таблицу получаю ошибку при добавлении строки внутрь таблицы (во вложенном файле ячейка А8, аргумент вложенной функции МАКС), которая исправляется только вручную.
Как правильно заменить адреса ячеек на ссылки умной таблицы, чтобы пересчет уникальных элементов происходил автоматически?
 
Перейти    
Рассчитать время выполнения работы без учета выходных дней и нерабочего времени
 
#
15.09.2017 14:38:41
Нужно именно время, а не продолжительность (например с 12.00 до 13.00)
 
Перейти    
Рассчитать время выполнения работы без учета выходных дней и нерабочего времени
 
#
15.09.2017 14:33:24
Вот так, но на самом деле у меня считает астрономические часы с 9.00 до 18.00. Для подсчета рабочих часов нужно знать время обеденного перерыва.
 
Перейти    
Посчитать переработку
 
#
15.08.2017 01:47:53
У меня вот так получилось:
 
Перейти    
Формула для подсчета количества дней в пересекающихся диапазонах (с дополнительным условием)
 
#
15.08.2017 01:23:01
Цитата
С.М. написал:
". . . пустые ячейки, логические значения и текст в массиве или ссылке игнорируются."

Я не просто забыла перечитать - я в первый раз обратила на это внимание   :cry: . Позор, конечно. Формула_Boroda_, суперкомпактная. Спасибо-спасибо всем вам!
 
Перейти    
Формула для подсчета количества дней в пересекающихся диапазонах (с дополнительным условием)
 
#
14.08.2017 22:37:49
я своего монстра "причесала", получилось вот так, вполне читабельно, по крайней мере, первую неделю)):
=ЕСЛИ(ЕСЛИ([@[Дата выдачи]]="";$C$2+1-МАКС([@[Дата поступления]]+5;$C$1);МАКС(МИН([@[Дата выдачи]]+1;$C$2+1)-МАКС([@[Дата поступления]]+5;$C$1);))<0;0;ЕСЛИ([@[Дата выдачи]]="";$C$2+1-МАКС([@[Дата поступления]]+5;$C$1);МАКС(МИН([@[Дата выдачи]]+1;$C$2+1)-МАКС([@[Дата поступления]]+5;$C$1);))))

tolstak, у меня формула выдала совсем некорректные значения,
С.М., результаты совпадают (только у вас нужно прибавить 1 день, т.к. , например с 1.06. по 10.06. - это 10 дней, а простым вычитанием получается 9. И это очень круто - запрятать формулы в имена диапазонов).
Спасибо!
Изменено: Spooker - 14.08.2017 22:46:26
 
Перейти    
Формула для подсчета количества дней в пересекающихся диапазонах (с дополнительным условием)
 
#
13.08.2017 12:25:26
Добрый день. Прошу помощи в решении такой задачи: есть дата поступления на склад, даты выбытия может не быть. Первые 5 дней хранения после поступления не учитываются. Нужно вычислить по каждой строке, сколько дней хранения попадают в отчетный период (С1:С2). Задача как будто стандартная, но у меня получается формула-монстр из вложенных ЕСЛИ,МИН и МАКС, а результат вычислений - некорректный.
 
Перейти    
Подсчет остатков разных наименований груза на нужную дату
 
#
16.02.2017 13:26:23

Добрый день,

Все же не спасли. Или я спасательный круг не вижу). Но поняла, что задачу сформулировала не точно.

К сожалению, я не совсем разобралась с синтаксисом СУММПРОИЗВ, особенно с таким, как в этой формуле – смогла только разделить по каждому крану (в столбцы «Счет остатка по КПП-10», «Счет остатка по ЛТ-62»). Дальше у меня только циклические ссылки получались.

Сейчас СУММПРОИЗВ:

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

- ищет наименование груза на дату, предшествующую текущей на один день. Получается, я не предусмотрела ситуацию, когда одно из наименований груза есть в остатке, но при этом следующий приход груза происходит не на следующий день, а через несколько дней. Т.о. необходимо, чтобы формула искала последнее вхождение по наименованию независимо от даты, и находила остаток.

Заранее благодарю.
 
Перейти    
Подсчет остатков разных наименований груза на нужную дату
 
#
15.02.2017 20:18:00
vladjuha,  это же просто супер! Вы просто спасли меня! Спасибо-спасибо-спасибо!
 
Перейти    
Подсчет остатков разных наименований груза на нужную дату
 
#
15.02.2017 16:13:17

Добрый день!

Очень нужна помощь.

Пытаюсь автоматизировать отчетность по ж/д участку перевалки грузка. Есть бумажная форма отчета, заданная «сверху», менять ее нельзя (в вложенном примере – лист «печатная форма»). Этот отчет подается ежедневно, еженедельно и ежемесячно.

Предполагается, что оператор ежедневно будет вносить данные в умную таблицу, далее обновлять сводную, настраивать фильтры по дате и переносить копированием часть данных в печатную форму. Оператор – не очень опытный пользователь, и работает в Excel 2007.

Проблемы:

1. Вид данных, которые нужны для отчета. Как видно из формы, есть две единицы измерения - ед. и тн, при этом данные по единицам (кол-ву вагонов) подаются не суммарные за отчетный период, а количеством на конец дня, указанного последним в периоде отчета.

Неизящное, но все-таки решение – вручную выбирать нужные числа на листе с данными.

2.  Подсчет остатков груза для каждого наименования на текущую дату.

Берется как «Остаток груза на площадках» на предшествующую дату + «Выгрузка из ж/д вагонов в запас» на текущую дату – «Погрузка из запаса на ав/маш» на текущую дату.

Вот эту проблему я решить не могу совсем. Даже если преобразовать «умную» таблицу в диапазон, формулы при сортировке слетают.

 
Перейти    
Страницы: 1
Наверх