Power Pivot - Среднее время на предмет

Question

Я пытаюсь подсчитать в среднем, сколько времени занимает изготовление каждого предмета.

Вот таблица данных:

Date        Item    Quantity   Operator
01/01/2014  Item1   3          John
01/01/2014  Item2   5          John
02/01/2014  Item1   7          Bob
02/01/2014  Item2   4          John
03/01/2014  Item1   2          Bob
07/01/2014  Item2   3          John

01.01.2014 Джон изготовил 3 из Item 1 и 5 из Item 2 . Если бы мы были только первые 2 строки , мы можем предположить , что она занимает 0.375 дней , чтобы сделать Item 1 и 0,625 дней , чтобы сделать Item 2

Я хочу быть в состоянии вычислить это в среднем, используя все данные и принимая во внимание операторов, очевидно работающих над различными пунктами.

Спасибо

Answer 1

С помощью Excel Solver я смог найти время производства на единицу, которое наилучшим образом (с точки зрения квадрата ошибки) данных. Для этого подготовьте свой рабочий лист следующим образом (при условии, что данные, которые вы предоставили, находятся в столбцах AD):

• В J1 поставить метку «Рабочее время в день:»
• В K1 указано количество рабочих часов в день (например, 8).
• В J3 поставить метку «Время изготовления на единицу продукции:»
• В J4 поставить метку "Item1", а в J5 "Item2"
• Установите некоторые начальные значения в K4 и K5 (например, 1 и 2).
• В столбце E добавьте "Время производства", используя формулу в E2: =VLOOKUP(B2;$J$4:$K$5;2;FALSE)*C2 и скопируйте и вставьте его, вычисляя время производства на единицу, умноженное на количество предметов.
• В столбце F добавьте "Идентификатор рабочего дня", используя формат в F2: A2&D2 и скопируйте и вставьте его, что создает уникальный идентификатор для каждого рабочего дня, который работал (нам нужно добавить рабочие часы в день в столбце Г).
• В столбце G добавьте "Рабочее время в день", используя формулу в G2: SUMIF($F$2:$F$7;F2;$E$2:$E$7)
• В столбце H добавьте "Квадрат отклонения рабочего времени", используя формулу в H2: (G2-$K$1)^2
• Наконец, добавьте общее количество столбца H, добавив H8: SUM(H2:H7)

Теперь запустите решатель минимизации H8 , изменив K4:K5 . По сути, это пытается найти лучшее время производства на единицу, что объясняет производство в день.

С математической точки зрения есть некоторые предостережения и подводные камни, но это (более или менее) отвечает на ваш вопрос.

Answer 2

Я начал смотреть на решение Power Pivot на основе ваших примеров данных, но быстро понял, что вы не включили почти достаточно информации о том, как рассчитать ваш результат. Я бы прочитал первые две строки, как указание на то, что для каждого предмета требуется 0,125 дней (т. Е. Количество 1 для каждого предмета).

И как это должно работать, когда рассматриваются первые 4 строки, с пробелами и другими операторами? Как насчет пробелов в днях, например, 4-го, 5-го, 6-го? Пожалуйста, добавьте ожидаемые результаты для всех сценариев и объясните метод расчета.

Во всяком случае, я создал рабочий прототип, чтобы начать обсуждение. Вы можете попробовать его, используя мой демонстрационный файл: "Power Pivot demo - среднее время на item.xlsx" на моем One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

Мой расчет DAX:

Среднее количество дней на элемент:= DISTINCTCOUNT([Дата]) / СУММА ([Количество])