Страницы: 1
RSS
Сортировка сводной таблицы по времени (часы и минуты)
 
Здравствуйте. В целом, проблему выделил красным цветом в файле. Теперь опишу:
У меня данные загружаются с 9:45 утра (чаще всего с 9:59). На основе исходной информации строится сводная на втором листе. Мне надо, чтобы время сортировалось правильно, т.е. в строке "ЧАС" начиная с 9, а в строке "МИНУТА" - с 0 (у меня диапазон минут не 1-60, а 0-59). И так по каждому часу.

Насколько я понял путем экспериментов, сводная смотрит, какое число в столбце МИНУТА первое в исходной таблице, ее и ставит в сводной перед нулем, который должен быть первым. Т.е. в зависимости от того, какое значение идет первым в столбце МИНУТА на первом листе, это значение первым же ставится в сводной, затем идет ноль и все остальные числа по порядку.

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

P.S. Сортировка в сводной не решение. Либо я как-то не так сортирую. Потому что я группирую минуты, как   правило, в 5минутный диапазон, а в этом случае группировка идет так:
0-410-1415-19
А должно быть так:
0-45-910-14
Изменено: vandesun - 24.11.2019 14:18:21
 
Цитата
vandesun написал: надо, чтобы время сортировалось правильно
Так для этого более подходит формат дата/время, а у вас данные разнесены на три поля формата общего. Как вариант - попробуйте их свести вместе и поработать в сводной - группировка/сортировка будет более понятной.
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
Я не увидела проблемы. Сгруппировала, разгруппировала, создала новую сводную, всё вроде нормально.
Цитата
vandesun написал:
идет так:0-410-1415-19А должно быть так:0-45-910-14
Видимо, в промежутке 5-9 не было значений. Если хотим отображать в любом случае, в параметрах поля Минуты установить галку Отображать пустые элементы
 
Цитата
Pelena написал: Я не увидела проблемы..
Это потому, что вы создали сводную на основе уже заполненной исходной таблицы. Если исходную таблицу очистить, закрыть, открыть и заново заполнить, а потом уже создать сводную или обновить уже существующую, то проблема остается.
По поводу группировки - проблемы нет, если не сортировать данные в сводной. Если, допустим, отсортировать минуты по возрастанию (это как вариант решения моей проблемы), и уже после сгруппировать, то интервал 5-9 будет в конце (а это в моей работе недопустимо). Хотя данные там есть, даже в прикрепленном файле видно же, что есть данные.

Цитата
Z написал: для этого более подходит формат дата/время... попробуйте их свести вместе и поработать в сводной...
Попробую поколдовать с форматом дата/время, хотя я пробовал в этом направлении - не получалось. К тому же, сам файл большой и много времени ушло на его создание (не в плане сложности, а в плане объема проделанной работы). Хотелось, не ломая файл и тем более не создавая новый, решить мою проблему.
 
Цитата
vandesun написал:
или обновить уже существующую, то проблема остается
Обновил сводную, составленную Еленой, не увидел перемещения интервала 5-9. Можно на Power Pivot сделать. Там есть возможность задать по какому столбцу будет в источнике определятся порядок
 
Не нужно жать на кнопку цитировния без необходимости [МОДЕРАТОР]

Видимо, вы просто обновили. У меня перемещается в конец, если пустую исходную заполнить и только после этого обновить (как мне и надо в работе). По поводу Power Pivot не приходилось сталкиваться, обязательно почитаю. Почему-то в вашем файле я не могу перегруппировать по 15, скажем (опять же по работе нужна такая возможность)...
 
Можете хотя бы коротко описать как вы сделали свой файл? Как появился интервал минут? И можно ли перегруппировывать?
 
Нужно на вкладке Power Pivot зайти в Управление. Там всё видно, что сделано. 15 минутный интервал - сделал. Если интересно, то начните с чтения Power Pivot — обзор и обучение
 
Спасибо, power pivot мне кажется интересным хотя бы тем, что там можно анализировать более миллиона строк. Обязательно изучу. Но конкретно в моей проблеме, я чувствую, это не подойдет. По сути, мне нужно решение, которое бы не приводило к дополнительным действиям при работе с файлом. Лучше всего - это где-то тыкнуть, сохранить и забыть. Потому что у меня в оригинальном файле 12 сводных, больше половины из которых частично скрыты для визуального удобства. Так что мне будет крайне неудобно каждый раз снова и снова отображать строки и столбцы и в каждой сводной перенастраивать.
Вообще, решение есть у меня. Сама проблема в том, что сводная смотрит, какое число первым стоит в столбце МИНУТА в источнике, по нему и сортирует. В прикрепленном файле первым стоит 59, поэтому и в сводной тоже в каждом часе сначала 59, затем уже по порядку 0, 1, 2 и т.д. до 58. Если будет, к примеру, 45, значит, вначале будет 45, а потом 0, 1, 2 ... 44, 46 и дальше. Чтобы это исправить мне надо просто сгруппировать эти минуты в сводной, а потом разгруппировать, тогда порядок минут будет правильным от 0 до 59. Но каждый раз отображать скрытые строки и столбцы, потом в каждой из 12 сводных группировать и разгруппировать, затем опять скрывать строки и столбцы - это долго и неудобно.
В общем, как-то так.
 
Цитата
vandesun написал:
сводная смотрит, какое число первым стоит в столбце МИНУТА в источнике, по нему и сортирует
Это так у Вас настроены свойства поля в #1. Выделите в сводной поле "Минута", далее Данные/Сортировка
Владимир
 
sokol92, здравствуйте.
Цитата
sokol92 написал:
Выделите в сводной поле "Минута", далее Данные/Сортировка
я поначалу так же сделал, но потом, внимательнее прочитав вопрос ТС, понял, что еще важно сгруппировать минуты с интервалом в 5. Если сделать сортировку минут, а потом группировку с интервалом 5, то уже некорректно сортировка располагается. У Pelena, в примере группировка корректкная, предполагаю, что применена ручная сортировка. Поэтому Андрей VG, предложил альтернативу на PP.
 
Здравствуйте, Владимир! О группировке Елена написала в #3 "Отображать пустые элементы". Дополнительно еще фильтром нужно скрыть элементы "<0", ">60".
PP - замечательное средство, но и обычные сводные таблицы должны с подобными задачами справляться.
Владимир
 
Немного странно, задаю вопрос вместо ТС :)
sokol92, а как в таком случае отсортировать по порядку группировку "5-9"? Она в конец списка встает после "55-59" (вот на этом момент я заступорился). Результат во вложении.
 
Vladimir Chebykin Перетащите ручками, и все ок!
 
Цитата
skais675 написал:
Перетащите ручками, и все ок!
я об этом писал выше:
Цитата
Vladimir Chebykin написал:
У  Pelena , в примере группировка корректкная, предполагаю, что применена ручная сортировка
Вопрос в том, не слетит ли она при изменении набора данных? На мой взгляд - слетит, и необходимо решение, которое будет работать корректно при любых обстоятельствах.
 
Цитата
Vladimir Chebykin написал:
У  Pelena , в примере группировка корректная, предполагаю, что применена ручная сортировка
на самом деле нет, поэтому я и написала, что не увидела проблемы. Я просто разгруппировала и снова сгруппировала с интервалом в 5 минут
 
Прошу прощения, я "на автомате" поменял заголовок "5-9" на "05-09", поскольку интервалы сортируются как тексты по алфавиту. При последующих обновлениях (с этой же группировкой) все должно быть нормально.
Изменено: sokol92 - 25.11.2019 14:34:52
Владимир
 
Никак не получается из того, что было предложено выше. Как правильно заметил Vladimir Chebykin, при изменении набора данных все слетает (а именно, если удалить все исходные данные, закрыть файл, потом открыть и заново загрузить). Специфика информации, с которой я работаю, заключается в том, что данные начинаются, как правило, с 9:50-9:59 утра, к сожалению, не с первой минуты часа (тогда бы проблемы не было вообще). Если никак не сортировать ничего, то каждый час начинается не с первой минуты, а в зависимости от того, с какой минуты загрузились данные в 9 часов. Допустим, что с 9:57. Тогда все последующие часы также начинаются с 57-ой минуты, а потом идет первая, вторая и дальше по порядку. Причем это не глюк моего файла, я несколько раз заново создавал, результат тот же. Если сортировать минуты по возрастанию, чтобы избавиться от вышеназванной проблемы, тогда возникает другая. Мне надо периодически группировать минуты в разных интервалах, в том числе по 5 минут. После сортировки группировка ломается: идет в следующем порядке: 0-4, 10-14, 15-19. Период 5-9 идет в самом конце часа. Если не сортировать минуты, тогда группировка строится правильно.
Про разгруппировать и сгруппировать без сортировки как предложила Pelena я знал и до этого, также понятно, что можно перетаскивать ручками. Но это все полумеры, когда приходится часто это делать, можно забыть в спешке, что уже отразится на результатах. Да и времени занимает подобное много. Потому я ищу решение, которые бы потребовалось выполнить один раз, и чтоб дальше все работало корректно без моих вмешательств. В прикрепленном файле sokol92 также все слетает при загрузке новых данных.
 
Давайте проверим.
Берете пример из #17, меняете данные на листе Табл (диапазон "Таблица 1"), обновляете сводную таблицу (не делая в ней никаких других изменений). Выкладываете файл, в котором "все слетело".
Владимир
 
Скачал ваш файл, удалил исходную информацию, скопировал данные за другой день и обновил сводную. Понятно, что если дальше ничего не делать, то сделанная вами группировка по 5 минут не слетит. Но я за день много раз меняю минутные интервалы туда-сюда, поэтому применил рабочий сценарий: сменил группировку на 10 минут, а потом обратно на 5 и больше ничего, но все опять слетело.
 
Когда меняете на интервал в 5 минут примените "трюк" с заголовком из #17 и еще раз обновите таблицу.
Владимир
 
Если я вас правильно понял про трюк, то надо поменять заголовок на 05-09 и обновить сводную (или я не так понял трюк?) Тогда интервалы сортируются в правильном порядке, но только единожды. При повторном группировании на другой интервал, а потом обратно на 5 минут проблема снова вылезает. И просто менял заголовок, и с дополнительным сокрытием элементов ">0", "<60", все равно второй раз не дается. Или вы имели в виду каждый раз менять заголовок, когда надо перегруппировать на 5-минутный интервал? Тогда это не то решение, которое нужно. Как написал товарищ Vladimir Chebykin: "... необходимо решение, которое будет работать корректно при любых обстоятельствах."
 
Добавьте в модуль книги из #20 и группируйте хоть по 2 минуты: :)

Код
Option Explicit
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim pf As PivotField, pi As PivotItem, v, flChange As Boolean
    On Error Resume Next
    Set pf = Target.PivotFields("Минута")
    On Error GoTo 0
    If Not pf Is Nothing Then
      Application.EnableEvents = False
      For Each pi In pf.PivotItems
        v = pi.Caption
        If v Like "#-*" Then v = "0" & v
        If v Like "##-#" Then v = Replace(v, "-", "-0")
        If v <> pi.Caption Then
          pi.Caption = v
          flChange = True
        End If
      Next pi
        
      If flChange Then Target.RefreshTable
      Application.EnableEvents = True
    End If
End Sub
Владимир
 
Да, вроде ваше макрос работает. Вечером еще раз протестирую подробнее, но уже понятно, что именно что-то типа такого решения я и искал) Спасибо!
Один вопрос еще. Если загрузить новые данные, потом обновить сводную и сгруппировать по 5 минут, то с первого раза не получается, просто ничего не происходит, и только при повторном группировании все как надо. Нет ли возможности сделать, чтобы макрос с первого раза группировал? Но это просто желание довести, замечу, уже рабочее решение до идеала) В любом случае огромное спасибо!
 
Беру свои слова обратно. Перепроверил, предложенный макрос срабатывает с первого раза. Больше ничего не надо. Спасибо еще раз!
 
Успехов!
Владимир
Страницы: 1
Наверх