超大数组统计各分组进入销量top 10%频次的Excel实现方案

方案1：仅用基础Excel公式（无需转置，零代码） #

你的原始数据结构为：第1行B列起是1-5000的组ID，A列第2行起是1-10000的试验编号，B2到对应5000列10001行的区域是各组各次试验的销售额，按以下步骤操作即可：

• 第一步：新增分位辅助列。在所有数据的最右侧新增一列，表头命名为单次试验90%分位线
• 第二步：计算单次试验分位值。在辅助列第2行（对应第1次试验）输入公式：=PERCENTILE.INC(B2: [你实际最后1组的列号]2,0.9)，回车后下拉填充到第10001行，即可得到每次试验所有组销售额的90%分位线，数值大于这条线的就是当次top10%的组
• 第三步：统计各组达标次数。在第1行组ID的下方空白行（比如第10002行）的B列输入公式：=SUMPRODUCT(--(B2:B10001>$[辅助列列号]2:$[辅助列列号]10001))，回车后横向拖动填充到最后一组的对应列，这一行的数值就是对应组进入top10%的总次数

注意：公式里带$的部分是绝对引用，不要删掉，否则拖动填充时会出错

方案2：简单VBA（无需辅助列，一键生成结果） #

如果数据量大不想新增辅助列，可以用简单VBA实现：

• 按Alt+F11打开VBA编辑器，点击顶部菜单栏「插入」-「模块」
• 把以下代码粘贴到弹出的编辑框中：

Sub 统计Top10次数()
    Dim dataArr, resArr, perLine, i As Long, j As Long
    ' 读取所有试验的销售额数据，替换成你实际的数据区域即可
    dataArr = Range("B2:AXF10001").Value
    ReDim resArr(1 To 1, 1 To UBound(dataArr, 2))
    ' 逐次试验计算分位并统计达标组
    For i = 1 To UBound(dataArr, 1)
        perLine = WorksheetFunction.Percentile_Inc(Application.Index(dataArr, i, 0), 0.9)
        For j = 1 To UBound(dataArr, 2)
            If dataArr(i, j) > perLine Then resArr(1, j) = resArr(1, j) + 1
        Next j
    Next i
    ' 结果输出到组ID下方的空白行，替换成你要的输出位置即可
    Range("B10002").Resize(1, UBound(dataArr, 2)).Value = resArr
    MsgBox "统计完成"
End Sub

• 按F5直接运行，结果会自动填充到你指定的输出位置

内容的提问来源于stack exchange，提问作者AntB