Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
PQ: Как свернуть номера в диапазоны по условию, Список номеров свернуть в диапазоны при двух условиях
 
#1
08.05.2019 11:59:23
Добрый день, коллеги. Перед праздником подкинули задачку. Есть табличка: код, номер, статус. Номера в данном коде могут идти подряд несколько штук или по одному и при этом иметь тот или иной статус. Никак не могу свернуть номера в диапазоны с учетом кода и статуса. Прошу помощи у знатоков Excel.
Excel непознаваем как атом.
 
 
 
#2
08.05.2019 12:37:28
Группируете одновременно по полям Код - Статус (так можно). В один столбец - минимум из списка номеров, в другой - максимум. Это одно действие. Все.

Доработайте по вкусу.

P.S. У вас правда в результате есть разбивка на поддиапазоны. Это уже усложняет задачу, но, в принципе, вам нужно добавить временный столбец, который будет зависеть от смены статусов. И добавить его третьим в группировку.

Либо у вас ошибка в ИД - нужны ваши пояснения:
4712200052
4712200083
4712200092
4712200122
Изменено: Alexey_Spb - 08.05.2019 12:44:41
 
 
 
#3
08.05.2019 12:57:17
Alexey_Spb,к сожалению, решение неверное: уже во 2-й строке у Вас диапазон номеров со статусом 2 начинается с 1220004 и заканчивается 1220013, а внутри него есть номер 220008 со статусом 3, 220009 со статусом 3, номер 220010 отсутствует и еще диапазон 220012-220013 со статусом 2. Т.е. нужны именно последовательности диапазонов (или отдельных номеров) со своими статусами.  
Excel непознаваем как атом.
 
 
 
#4
08.05.2019 13:03:11
Цитата
mtts54 написал:
решение неверное: уже во 2-й строке
так вы скажите по какому признаку определить когда один и тот же номер будет и в Начало диапазона и в Окончание диапазона
 
 
 
#5
08.05.2019 13:06:46
mtts54, вы когда руками заполняли как определили что значение в В4 будет равно значению в С4 и так же в В5=С5 ?
 
 
 
#6
08.05.2019 13:15:48
artyrH, artyrH, когда в исходной таблице он одинок со своим статусом (нет предыдущего и последующего номера с таким же статусом), тогда этот номер является и началом и концом диапазона с данным статусом.
Excel непознаваем как атом.
 
 
 
#7
08.05.2019 13:19:16
Alexey_Spb, в исх.данных ошибки нет. Эти номера могут идти последовательно или поодиночке и еще иметь свои статусы. Спасибо за интерес к теме.
Excel непознаваем как атом.
 
 
 
#8
08.05.2019 13:49:46
mtts54, так?
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    group = Table.Group(Source, {"Код", "Статус"}, {{"min", each List.Min([Номер])}, {"max", each List.Max([Номер])}, {"count", each List.Count([Номер])}}, 0),
    reorder = Table.ReorderColumns(group,{"Код", "min", "max", "count", "Статус"})
in
    reorder
 
 
 
#9
08.05.2019 14:15:34
Aleksei_Zhigulin, спасибо, почти так. Номера 220010 со статусом 2 не существует, у Вас он присутствует в диапазоне 220009-220013, всё остальное правильно (в реальных данных номеров сотни тыс, и пропусков номеров много). Добавить проверку на непрерывность номеров нетрудно, но как потом ее привинтить в код - вот в чем вопрос  :cry:  
Excel непознаваем как атом.
 
 
 
#10
08.05.2019 14:20:29
Автор, некогда сделать алгоритм... Идея такая.

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

Далее группируете по этому столбцу.

Aleksey_Zhigulin - не так, смотрите мой пример (я сначала так же подумал, как и вы).
 
 
 
#11
08.05.2019 14:32:40
Можно и без аккумуляторов. Код навеян недавним решением от Андрея VG:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Код", Int64.Type}, {"Номер", Int64.Type}, {"Статус", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Индекс", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Пользовательская", each [Номер]-[Индекс]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Код", "Статус", "Пользовательская"}, {{"Начало", each List.Min([Номер]), type number}, {"Окончание", each List.Max([Номер]), type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Пользовательская"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Код", "Начало", "Окончание", "Статус"})
in
    #"Reordered Columns"
Вот горшок пустой, он предмет простой...
 
 
 
#12
08.05.2019 15:06:10
Вариант PooHkrd самый оптимальный ИМХО. Вот вариант через группировки (одной обойтись не получилось):
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    group = Table.Group(Source, {"Код", "Статус", "Номер"}, {{"temp", each _}, {"rowCount", Table.RowCount}}, 0, (a,b)=> Number.From(if b[Код]=a[Код] and b[Статус]=a[Статус] then b[Номер]<>a[Номер]+1 else true)),
    group1 = Table.Group(group, {"Код", "Статус", "Номер", "rowCount"}, {{"min", each List.Min(Table.Combine([temp])[Номер])}, {"max", each List.Max(Table.Combine([temp])[Номер])}, {"count", each List.Count(Table.Combine([temp])[Номер])}}, 0, (a,b)=> Number.From(if b[Код]=a[Код] and b[Статус]=a[Статус] then b[Номер]<>a[Номер]+a[rowCount] else true)),
    final = Table.SelectColumns(group1,{"Код", "min", "max", "count", "Статус"})
in
    final
 
 
 
#13
08.05.2019 15:29:30
PooHkrd, великолепно!  :excl: Огромное спасибо! :D  

С праздником всех Планетян!
Excel непознаваем как атом.
 
 
 
#14
08.05.2019 15:48:42
mtts54, перед индексацией рекомендую сделать шаг с сортировкой таблицы по номерам. Иначе алгоритм не сработает.
Изменено: PooHkrd - 08.05.2019 15:48:53
Вот горшок пустой, он предмет простой...
 
 
 
#15
13.05.2019 17:02:24
PooHkrd, С сортировкой понятно, спасибо. Однако код делает ошибку в том случае, когда в непрерывном диапазоне номеров меняется статус номера с 1 на 2 и обратно (либо с 2 на 1 и обратно): указывает весь диапазон с первым статусом и "внутренний" диапазон со вторым статусом. Код от Aleksei_Zhigulin,  работает правильно, пока количество подряд идущих номеров с неизменным статусом не более 4-х. При бо'льшем количестве данные выводятся во второй, третьей (видимо и т.д.) строках. В общем-то не критично, т.к. общее количество номеров и их статусы определяются правильно. Может, поможет еще одна группировка (в реальных данных сотни непрерывных номеров с одним и тем же статусом)? Попробую. Спасибо, PooHkrd, и Aleksei_Zhigulin !  Что-то файл не прицепляется... :cry:  попробую позже.
Изменено: mtts54 - 13.05.2019 17:03:39 (присоединение файла.)
Excel непознаваем как атом.
 
 
 
#16
13.05.2019 17:39:01
Так попробуйте:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Код", Int64.Type}, {"Номер", Int64.Type}, {"Статус", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Номер", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Индекс", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Пользовательская", each [Номер]-[Индекс]),
    Custom1 = Table.FromColumns( Table.ToColumns(#"Added Custom") & Table.ToColumns( #table({"Статус"}, {{null}}) & Table.RemoveLastN(Table.SelectColumns(#"Added Custom",{"Статус"}), 1 ) ), Table.ColumnNames(#"Added Custom") & {"Пред.Статус"} ),
    #"Added Custom1" = Table.AddColumn(Custom1, "Пользовательская.1", each if [Статус]-[Пред.Статус] <> 0 then [Индекс] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Пользовательская.1"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Код", "Статус", "Пользовательская", "Пользовательская.1"}, {{"Начало", each List.Min([Номер]), type number}, {"Окончание", each List.Max([Номер]), type number}, {"Кол-во", each Table.RowCount(_), type number}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Код", "Начало", "Окончание", "Статус", "Кол-во"})
in
    #"Removed Other Columns"

Шаг Custom1 это добавление смещенного на 1 строку столбца со статусами, за счет этого можно для группировки добавить еще один столбец с признаком. Того же эффекта можно добиться и при помощи приема с двумя индексами + слияние (упомянутом выше), но на большом массиве мой вариант должен отработать шустрее.
Изменено: PooHkrd - 13.05.2019 17:49:36
Вот горшок пустой, он предмет простой...
 
 
 
#17
13.05.2019 17:57:33
Цитата
PooHkrd написал:
Шаг Custom1 это добавление смещенного на 1 строку столбца
спасибо за прием. а есть вариант со смещением на одну строку вверх?
 
 
 
#18
13.05.2019 18:21:40
artyrH, а в чем разница?
Если разобраться в механизме формирования смещенного столбца, то вместо
Код
Table.ToColumns( #table({"Статус"}, {{null}}) & Table.RemoveLastN(Table.SelectColumns(#"Added Custom",{"Статус"}), 1 ) )

нужно написать
Код
Table.ToColumns( Table.RemoveFirstN(Table.SelectColumns(#"Added Custom",{"Статус"}), 1 ) & #table({"Статус"}, {{null}}) )

и будет вам счастье.
Если нужно сместить на более чем одну строку, то это вам домашнее задание.  ;)
Если что, то источник мудроты отсюда. Там же есть целая самописная функция для данных преобразований, из которой я и выдрал кусок.
Изменено: PooHkrd - 13.05.2019 18:31:20
Вот горшок пустой, он предмет простой...
 
 
 
#19
13.05.2019 18:42:21
PooHkrd, спасибо, Вашего разъяснения достаточно.домашнее задание выполняется добавлением ,{null} и изменением цифры
 
 
 
#20
14.05.2019 09:03:54
PooHkrd, попробовал Ваш код #16. В тестовом файле отработал безупречно. Под реальный файл заточу код чуть позже. Спасибо!

Испытал на реальном файле 30 тыс. строк. Получил 1250 диапазонов, код отработал корректно. Еще раз спасибо!
Изменено: mtts54 - 14.05.2019 15:58:40 (Добавление текста.)
Excel непознаваем как атом.
 
 
 
Страницы: 1
Наверх