Мне нужна помощь в сценарии, над которым я буду работать в Excel. Любая помощь будет высоко оценена.
У меня будет постоянно растущий диапазон дат для определенных элементов, и мне нужно создать таблицу Excel, чтобы определить среднее количество дней между ними для каждого элемента. По сути, это упрощенный пример того, как я планирую табулировать данные:
Item Code | Date
A.ITEM | January 15, 2017
B.ITEM | January 16, 2017
A.ITEM | January 22, 2017
C.ITEM | January 25, 2017
A.ITEM | January 31, 2017
C.ITEM | February 2, 2017
B.ITEM | February 12, 2017
B.ITEM | February 24, 2017
C.ITEM | March 7, 2017
Затем я создам еще одну таблицу, которая будет отображать среднюю продолжительность между датами по каждому пункту. Я представляю, что это будет выглядеть так:
Item Code | Average Life Span
A.ITEM | 9 days
B.ITEM | 20.5 days
C.ITEM | 21.5 days
Какая формула мне нужна, чтобы сделать вторую таблицу возможной? Я уже некоторое время ломаю голову, и поскольку я не очень хорошо знаком с функциями дат в Excel, я до сих пор не знаю, как это сделать. Возможно ли это вообще?
Спасибо!
решение1
Обратите внимание, что среднее значение разностей равно всего лишь (макс-мин)/количество: (d1-d2) + (d2-d3) + (d3-d4) +... = d1-dn
При этом вы можете использовать такую формулу:
(MAX(d1:dn)-MIN(d1:dn))/COUNT(d1:dn)
Однако это поместит все даты в один горшок, поэтому вам нужно дополнительно отфильтровать по вашим кодам - вместо того MAX(d1:dn), чтобы просто использовать MAX(IF(a1:an=code,d1:dn,0)как матричную формулу. Добавьте аналогичные if для MIN и COUNT (или используйте COUNTIF); обратите внимание, что для MIN значение else не может быть 0, но должно быть чем-то очень большим.
решение2
В этом примере Код товара находится в столбце A, Дата — в столбце B, а затем я добавляю новые данные. Строка 1 — заголовки.
Во-первых, вам нужно будет сортировать данные каждый раз при добавлении новой строки, используя двухуровневую сортировку:
- сначала по коду товара
- второй по дате
Затем в столбце справа от «Даты» добавьте расчет «Время между» и перетащите/заполните его вниз:
=IF(A2=A1,B2-B1,"")
Затем усреднить время. Перечислите коды элементов A, B, C в другом месте - я поместил их в столбец F в своем тесте. Рядом с A введите и перетащите/заполните вниз:
=AVERAGEIF($A$2:$A$6491,F2,$C$2:$C$6491)
Я получил:
- А: 8
- Б: 19,5
- С: 20,5
(для A: 22 января - 15 января - 7 дней, 31 января - 22 января - 9 дней, среднее значение - 8 дней)