Страницы: 1
RSS
Пропуски дат в сводной таблице
 
Всем привет. Подскажите, пожалуйста, как можно в сводной таблице настроить отображение непрерывного ряд дат внутри одного месяца, выбранного фильтром.
Данные есть не на все даты, но нужно чтобы в сводной таблице показывались все даты в том, числе пропущенные.Спасибо.

P.S.: Пробовал с группировкой дат и отображением пустых значений, но тогда не работает фильтрация за 1 месяц. Непрерывные даты показываются за весь год..
 
Параметры Вашей сводной таблицы настроены так, что она (таблица) помнит все удаленные из полей значения.
  • Временно добавьте в источник данных ("Исходник") все даты от 01.08.2019 по 30.09.2019 (можно занести две последовательных даты и протянуть ячейки)
  • Обновите сводную таблицу
  • Уберите лишние даты из источника. Обновите таблицу.
  • В параметры поля "Дата" поставьте "Отображать пустые элементы"
Изменено: sokol92 - 23.08.2019 18:26:23
Владимир
 
sokol92, спасибо. К сожалению, мне такой вариант не подходит, так как нужно, чтобы работало после обновления таблицы и на произвольный ряд дат...
 
вариант нв PQ:
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    typ = Table.TransformColumnTypes(from,{{"Дата", type date}}),
    list = List.Dates(List.Min(typ[Дата]),Number.From(
                        Date.EndOfMonth(List.Max(typ[Дата]))-Date.StartOfMonth(List.Min(typ[Дата])))+1
                                                        ,#duration(1,0,0,0)),
    table = Table.FromList(list, Splitter.SplitByNothing()),
    join = Table.NestedJoin(table,"Column1",typ,"Дата","количество"),
    expand = Table.ExpandTableColumn(join, "количество", {"Кол-во"}),
    add = Table.AddColumn(expand, "Период", each Date.ToText([Column1],"yyyy, MMMM")),
    zero = Table.ReplaceValue(add,null,0,Replacer.ReplaceValue,{"Кол-во"}),
    name = Table.RenameColumns(zero,{{"Column1", "Дата"}}),
    to = Table.TransformColumnTypes(name,{{"Дата", type date}, {"Кол-во", Int64.Type}})
in
    to
Изменено: buchlotnik - 23.08.2019 19:03:08
Соблюдение правил форума не освобождает от модераторского произвола
 
Спасибо. Но макросы и PQ, тоже не подходят к сожалению..
 
В данные введите просто даты без данных...
Изменено: Настя_Nastya - 23.08.2019 23:17:35
 
Цитата
Roomon написал:
макросы и PQ, тоже не подходят
Так вы не стесняйтесь, заранее поясните какие методы решения вашей задачи не подходят.
Вот вариант решения при помощи сводных таблиц на базе модели данных Power Pivot:
Создаем сводную на базе модели данных, лезем в управление моделью данных и создаем там автокалендарь. Связываем его с вашей основной таблицей. Потом пишем меру, которая будет создавать контекст вычислений для дат без данных и обеспечивать вывод нужных строк в сводную:
Код
Сумма для всех дат:=var minD = CALCULATE(MIN('Исх'[Дата]);ALL('Calendar'[Date])) 
var maxD = CALCULATE(MAX('Исх'[Дата]);ALL('Calendar'[Date])) 
return
SUMX( 'Calendar'; IF( 'Calendar'[Date] >= minD && 'Calendar'[Date] <= maxD; SUMX(RELATEDTABLE('Исх');[Кол-во]) + 0) )

И условным форматированием закрашиваем нули в цвет фона, если нули хочется видеть, то этот пункт не обязателен.
В процессе изготовления решения ни VBA, ни PQ не пострадал.
Удачи.
Изменено: PooHkrd - 24.08.2019 02:04:04 (добавил файл)
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Так вы не стесняйтесь, заранее поясните какие методы решения вашей задачи не подходят.
По умолчанию подразумеваю обычный эксель без всяких надстроек и макросов. Согласен, нужно было пояснить  :)
Спасибо за файл. Работает так как нужно. С моделями данных и PowerPivot никогда не работал, буду изучать.
А существуют варианты сделать это без всяких надстроек и макросов, чтобы работало в Excel 2007?
 
Цитата
Настя_Nastya написал:
В данные введите просто даты без данных...
Спасибо, но хотелось бы сделать файл, в который пользователи будут вставлять сами фактические данные и смотреть отчёт. В вашем варианте им нужно будет описывать логику вставки пустых значений, что лишь больше запутает их.
 
Доброе время суток
Цитата
Roomon написал:
По умолчанию подразумеваю
, а правила требуют
Цитата
2.2. Опишите максимально подробно вашу задачу и желаемый результат. Желательно уточнить вашу версию Excel.
Вы уверены, что именно под вас должны подстраиваться форумчане? Вы же не в магазине :)
 
Цитата
Андрей VG написал:
Вы уверены, что именно под вас должны подстраиваться форумчане? Вы же не в магазине
Исправляюсь  :)  Есть возможность настроить отображение непрерывного ряд дат внутри одного месяца, выбранного фильтром в сводной таблице в Excel 2007, без использования PP, PQ и макросов? Отображение пустых элементов даёт что-то похожее, но только без учета выбора месяца в фильтре..  
 
Roomon, остается только MS Query. Работает с версии Экселя 2003. Но это уже не ко мне.
Вам в любом случае нужно как-то напихать в кэш сводной таблицы даты, которые вы хотите увидеть. Если их там нет, то сводной вам и показывать нечего.
Вот горшок пустой, он предмет простой...
 
Еще раз поискал решение и понял, что если вынести в фильтр "Месяцы", которые появляются после группировки дат, а не "Месяц", который я сам создал, то фильтрация работает и можно отобразить даты внутри месяца без пропусков. Единственное, что мешает, так это отсутствие возможности настроить формат отображения сгруппированной даты. Особенно мешают элементы < и > в фильтре, но их обычным способом не убрать.
Файл с тем, что получилось прикладываю, вдруг кому пригодится.
Страницы: 1
Наверх