Excel: вычислить среднее количество дней между диапазонами дат

Question

Мне нужна помощь для сценария, над которым я буду работать с 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

Какая формула мне понадобится, чтобы сделать второй стол возможным? Я уже некоторое время ломаю голову, и, поскольку я не очень хорошо знаком с функциями Date в Excel, я до сих пор не знаю как. Это вообще возможно?

Спасибо!

Answer 1

Обратите внимание, что среднее из различий просто (max-min)/count: (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, но должно быть очень большим.

Answer 2

В этом примере код товара находится в столбце A, дата - в столбце B, а затем я добавляю новые данные. Строка 1 - заголовки.

Во-первых, вам нужно сортировать данные каждый раз, когда вы добавляете новую строку, с двухслойной сортировкой:

• сначала по коду товара
• второй по дате

Затем в столбце справа от даты добавьте расчет времени между и перетащите / заполните вниз:

=IF(A2=A1,B2-B1,"")

Затем усреднить время. Перечислите коды предметов A, B, C в другом месте - я поместил их в столбец F в моем тесте. Рядом с A введите и перетащите / заполните вниз:

=AVERAGEIF($A$2:$A$6491,F2,$C$2:$C$6491)

Я получил:

• A: 8
• B: 19,5
• С: 20,5

(для A: 22 января - 15 января - 7 дней, 31 января - 22 января - 9 дней, в среднем 8 дней)