在Excel中计算订单下单的频率(最小/最大/平均) [英] Calculating frequecy (Min/Max/Average) of order placement in Excel
问题描述
我需要分析过去1年中的每周订购频率，以找出每种产品的最小/最大/平均订购频率. 无论是新的还是旧的，系统都应将订单在当年的第一个匹配项计算为订单的起始周.最小订购频率是连续订购周之间的差额.如果第一个订单在wk 3中，第二个订单在wk6中，则意味着订单频率为3周(=> 6-3).订单可以在过去52周中的任何一周出现.平均订单频率=(52-第一订单周)/有订单的周数.
I need to analyze Weekly order frequencies over last 1 year period to find out what is the min/max/average frequencies of orders for each product. whether it is new or old,system should calculate the first occurrence of the order in the year as the starting week of the order. Min order frequency is difference between successive ordering weeks. If the first order is in wk 3 and the second order is in wk6, implies the order frequency is 3 weeks (=>6-3). Orders can be at any week in the past 52 weeks. Average order frequency = (52 - First order week) / no of weeks that have orders.
附加excel以更好地理解该问题.
Attaching the excel for better understanding the issue.
+---------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+----------------+-------------------------+-----+-----------------------------------+--+
| Product | wk1 | wk2 | wk3 | wk4 | wk5 | wk6 | wk7 | wk8 | wk9 | wk10 | wk11 | wk12 | wk13 | wk14 | wk15 | wk16 | wk17 | wk18 | wk19 | wk20 | wk21 | wk22 | wk23 | wk24 | wk25 | wk26 | wk27 | wk28 | wk29 | wk30 | wk31 | wk32 | wk33 | wk34 | wk35 | wk36 | wk37 | wk38 | wk39 | wk40 | wk41 | wk42 | wk43 | wk44 | wk45 | wk46 | wk47 | wk48 | wk49 | wk50 | wk51 | wk52 | Order start wk | Order frequency (Weeks) | | | |
+---------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+----------------+-------------------------+-----+-----------------------------------+--+
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Min | Max | Average | |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | (End wk - Start week)/No of times | |
| SKU 1 | | | | | | | | | y | | y | | y | | y | | y | | y | | y | | y | y | | | y | | y | | y | | y | | | | | | y | | y | | y | | y | | y | | y | | y | | 9 | 1 | 6 | 2.15 | |
| SKU 2 | | | | | | | y | | | | | | y | | | | | | y | | | | | | y | | | | | | y | | | | | | y | | | | | | y | | | | | | y | | | | 1 | 0 | 0 | 7.29 | |
| SKU 3 | | | | | | | | | | | | | | | y | | | | | | | | | | | | | | | | y | | | | | | | | y | | | | | | | | y | | | | | | 15 | 8 | 15 | 9.25 | |
+---------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+----------------+-------------------------+-----+-----------------------------------+--+
推荐答案
因此，如前所述，@ Barry Houdini解决了寻找最长的零序列的问题，这些序列被优雅地分隔为一个
So as mentioned @Barry Houdini solves the problem of finding the longest sequence of zeroes separated by ones elegantly here
只需更改一下即可检查是否有重复的空白单元格(以"y"分隔).唯一的事情是您不想在第一个"y"之前包含单元格，并且(尽管尚不清楚)可能不想在最后一个"y"之后包含空白单元格.
You only have to change it slightly to check for repeated blank cells separated by 'y'. The only thing is that you don't want to include cells before the first 'y', and (although this isn't clear) may not want to include blank cells after the last 'y'.
MIN的公式变为
=MIN(IF((ROW(A$1:INDEX(A:A,COUNTA(B4:BA4)+1))>1)*(ROW(A$1:INDEX(A:A,COUNTA(B4:BA4)+1))<COUNTA(B4:BA4)+1),FREQUENCY(IF(B4:BA4="",COLUMN(B4:BA4)),IF(B4:BA4="y",COLUMN(B4:BA4)))))+1
并且MAX的公式变为(相同)
and the formula for MAX becomes (the same)
=MAX(IF((ROW(A$1:INDEX(A:A,COUNTA(B4:BA4)+1))>1)*(ROW(A$1:INDEX(A:A,COUNTA(B4:BA4)+1))<COUNTA(B4:BA4)+1),FREQUENCY(IF(B4:BA4="",COLUMN(B4:BA4)),IF(B4:BA4="y",COLUMN(B4:BA4)))))+1
您需要在其中加1来使结果与问题一致，因为@Barry的公式计算空白的数量，但是OP希望两个连续的y之间有间隔.生成ny + 1个元素的数组，其中ny是y的数量.这是因为FREQUENCY函数返回的数组包含n + 1个元素，其中n是切点的数量(
where you need to add 1 to make the results agree with the question because @Barry's formula counts numbers of blanks but OP wants interval between two successive y's. An array of ny+1 elements is generated where ny is the number of y's. This is because the FREQUENCY function returns an array with n+1 elements where n is the number of cut points (bins_array in documentation and because the column numbers of cells containing y are used as cut points so there are ny of them.
这两个都是数组公式，需要使用 Ctrl Shift Enter
These are both array formulas and need to be entered with CtrlShiftEnter
平均值的公式只是
=(COLUMNS(B4:BA4)-MATCH("y",B4:BA4,0))/COUNTA(B4:BA4)
这篇关于在Excel中计算订单下单的频率(最小/最大/平均)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
