Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Расчет даты выработки ресурса при неравномерном распределении наработки, Подскажите формулу(лы) для вычисления даты выработки ресурса
 
#1
18.01.2019 16:13:51
Кто подскажет решение? Есть исходные данные агрегатов: номер агрегата, его наработка на текущий момент (в часах), его предельный ресурс ( в часах) и отдельной таблицей планируемая среднемесячная наработка (часов в месяц) в каждом месяце года. Необходимо, не применяя макросы, в колонке "Дата отработки ресурса" напротив каждого агрегата вычислить дату отработки его ресурса. В прилагаемом примере я применил для расчета усредненную наработку за весь период (сентября 2018 г по декабрь 2019 г.). Но такой расчет некорректный потому, что если у агрегата маленький остаток ресурса то, соответственно, его средняя наработка будет отличаться, поскольку он отработает ресурс ранее, а по месяцам года средняя наработка существенно отличается (за последние месяцы 2018 года наработка значительно больше чем в начале следующего года. Может нужно как-то применить фактор сезонности?
Изменено: viktorrom21 - 18.01.2019 18:28:31
 
 
 
#2
18.01.2019 19:40:20
viktorrom21,

Эх, не успеваю, выкладываю как есть (в Power Query). Не факт, что вернусь к задаче в ближайшие дни, думаю, коллеги уже подскажут к тому времени. Остался последний шаг, хотя самый сложный, пожалуй. В запросе Дата отработки ресурса в столбце Наработка в каждой ячейке вложенная таблица. В этой таблице есть столбец Running Sum. Соответственно, дата, в которую сумма в данном столбце впервые превысит остаток ресурса по каждому агрегату и будет искомой Датой отработки ресурса. Хотя по-хорошему ещё надо сравнить с Предельным сроком службы, но это мелочи.

P.S. Доделал файл. Под датой отработки ресурса понимал дату, когда ресурс полностью исчерпается (возможно, Вам нужна дата, предшествующая этой). Также расширил таблицу Средняя наработка в месяц, исходя из того, что норма наработки ресурса в будущем может меняться.  
Изменено: Aleksei_Zhigulin - 21.01.2019 12:29:09
 
 
 
#3
19.01.2019 10:30:03
Сперва сделал все в одной формуле через MULT но массивы не маленькие и пересчет долгий может получится, по этому в отдельную таблицу промежуток вынес
=IFERROR(MIN(INDEX(Sheet1!A:A;MATCH([@[Остаток ресурса]]+VLOOKUP([@[Наработка по состоянию на:]];Sheet1!A:C;3;);Sheet1!C:C));[@[Предельный срок службы]]);[@[Предельный срок службы]])

Чуть оптимизировал через MMULT ( версия _1) но видно как все медленно считает.

Сменил концепцию, MMULT Присутствует, но масштаб другой, шевелится быстрее. ( версия _2 ) Наверно можно еще пооптимизировать, но уже не так интересно стало.

Не удержался Оптимизнул  (версия 3)
=IFERROR(MIN([@[Предельный срок службы]];EOMONTH(--("1 " & INDEX($2:$2;FinishMonth+1));-1)+([@[Остаток ресурса]]-RestInFirstMonth+SUM($B$3:INDEX($3:$3;StartMonth+1))-SUM($B$3:INDEX($3:$3;FinishMonth)))/(INDEX(AveragesPerMonth;FinishMonth)/DAY(EOMONTH(--("1 " & INDEX($2:$2;FinishMonth+1));0))));[@[Предельный срок службы]])

ну и в именах 4 переменных
RestInFirstMonth =(EOMONTH('План выработки ресурса'!$B8;0)-'План выработки ресурса'!$B8)*INDEX('План выработки ресурса'!$3:$3;StartMonth)/DAY(EOMONTH('План выработки ресурса'!$B8;0))
StartMonth =MATCH(TEXT('План выработки ресурса'!$B8;"ММММ ГГГ");'План выработки ресурса'!$2:$2;)-1
FinishMonth =IFERROR(MATCH('План выработки ресурса'!$E8-RestInFirstMonth+SUM('План выработки ресурса'!$B$3:INDEX('План выработки ресурса'!$3:$3;StartMonth+1));MMULT(--(COLUMN(AveragesPerMonth)<TRANSPOSE(COLUMN(AveragesPerMonth)));TRANSPOSE(AveragesPerMonth)));StartMonth)
AveragesPerMonth =INDEX('План выработки ресурса'!$3:$3;2):INDEX('План выработки ресурса'!$3:$3;COUNTA('План выработки ресурса'!$3:$3))
Изменено: БМВ - 21.01.2019 23:23:19
По вопросам из тем форума, личку не читаю.
 
 
 
#4
21.01.2019 17:11:42
Цитата
Aleksei_Zhigulin написал:
В этой таблице есть столбец Running Sum
Спасибо, Aleksei_Zhigulin! Вариант с в Power Query мне больше нравится, но я не полностью разобрался с тем как была создана настраиваемая функция "fnAddRunningSum" и как добавлена в таблицу "Дата отработки ресурса". У вас англоязычная версия Excel, а у меня Power Query русский. Если можно пошагово расписать, как вы ее применили? Может где-то есть пример создания настраиваемых функций в Power Query ?  
Изменено: viktorrom21 - 21.01.2019 17:12:17
 
 
 
#5
21.01.2019 19:26:36
viktorrom21, Прежде всего, у меня тоже русскоязычная версия Excel. Функция fnAddRunningSum как раз таки не моя, позаимствовал отсюда:
https://community.powerbi.com/t5/Desktop/How-to-do-a-running-Sum-by-group-in-Power-Query/td-p/290123

Цитата
как вы ее применили?
Видимо, Вы про этот шаг:
Код
= Table.TransformColumns(#"Added Custom", {"Наработка", each fnAddRunningSum(_)})
Что делает функция fnAddRunningSum? Она берёт на входе таблицу и добавляет к ней дополнительный столбец с Суммой накопительным итогом. Соответственно, в данном случае в каждой ячейке столбца Наработка у нас вложенная таблица, к ней мы и применяем функцию fnAddRunningSum. Чтобы лучше разобраться, что происходит на каждом шаге, создайте в той же книге такой запрос:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица20"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Дата отработки ресурса"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Номер агрегата", Int64.Type}, {"Наработка по состоянию на:", type date}, {"Наработка текущая на указанную дату", type number}, {"Предельный ресурс", Int64.Type}, {"Остаток ресурса", type number}, {"Предельный срок службы", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Наработка", (z)=> Table.SelectRows(Наработка, each [День] > z[#"Наработка по состоянию на:"])),
    Source1 = Table.Buffer(#"Added Custom"{0}[Наработка]),
    TableType = Value.Type(Table.AddColumn(Source1, "Running Sum", each null, type number)),
    Cumulative = List.Skip(List.Accumulate(Source1[Средняя наработка в день],{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + cost})),
    AddedRunningSum = Table.FromColumns(Table.ToColumns(Source1)&{Cumulative},TableType)
in
    AddedRunningSum

Цитата
Может где-то есть пример создания настраиваемых функций в Power Query ?
Из ссылок по этой теме вспоминается только это, но если погуглить, уверен, найдутся и другие:
https://bengribaudo.com/blog/2017/12/08/4270/power-query-m-primer-part3-functions-function-values-pa...

P.S. Из русскоязычных материалов часто ссылаются на этот:
http://finalytics.pro/inform/m-powerquery2/
Изменено: Aleksei_Zhigulin - 21.01.2019 19:48:56
 
 
 
#6
22.01.2019 10:45:51
viktorrom21, А чем формульное решение не подошло,  Я _3 подправил, ошибка закралась, сейчас все считает, единственное что расхождение на 1 день с решением от Aleksei_Zhigulin,  и вопрос творческий, что правильно и стоит ли обращать на это внимание.
По вопросам из тем форума, личку не читаю.
 
 
 
#7
22.01.2019 18:08:08
Спасибо всем за помощь и потраченное время!  
Я выложил только маленькую часть большого файла. В исходном файле большое количество привязанных друг к другу агрегатов и, соответственно, взаимосвязанных по наработке . Поэтому, в первую очередь мне было важно получить предложение по принципу расчета наработки во времени. В моем большом файле много данных обрабатываются с помощью Power Query (более 20 различных запросов и отдельных таблицы до 20 000 строк ).  Формульный вариант расчета можно было бы применить если бы формула была простой и ее нужно было бы применять для однотипных таблиц. Но в моем случае для разных взаимосвязанных таблиц различной структуры расчет с помощью Power Query более удобно применять.
Надеюсь, что мой предпочтительный выбор не обидит других участников обсуждения, я безусловно благодарен всем и надеюсь, что полученные подсказки обязательно мне пригодятся в последствии.
 
 
 
#8
22.01.2019 19:34:58
Цитата
viktorrom21 написал:
не обидит других участников обсуждения, я безусловно благодарен всем
конечно не обидит, говорю за всех :-) (оглянулся, а ведь нет никого ) :-).

Цитата
viktorrom21 написал:
ного данных обрабатываются с помощью Power Query
это поясняет наличие именно таблицы со средними, ох как она путалась под ногами у меня при написании формул.

Ну а задача была интересная, и даже в формульном варианте есть несколько методов решения, может кому и пригодится.
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Наверх