如何找出占销售额 5% 的商店数量

Question

假设我有一张表，其中一列是“商店地址”，另一列是“销售额”。我想要一张显示以下内容的表格：

• 总销售额的 5% 来自顶级 X1 商店
• 总销售额的 10% 来自顶级 X2 商店
• 总销售额的 15% 来自顶级 X3 商店
• 总销售额的 20% 来自顶级 X4 商店......等等

我该怎么做？听起来很简单，但我不知道该怎么做。

Answer 1

这里有一种方法。假设您有一个包含 25 家商店的列表（单元格中），B2:B26以及它们的销售额（单元格中）C2:C26。

B2:C26首先按销售额（B 列）对合并范围进行降序排序。

添加第三列，用于显示已排序商店列表的累计销售额百分比。第一个单元格的公式D2为：

  =SUM($C$2:C2)/SUM($C$2:$C$26)

将公式向下拖到单元格中D26，该公式应等于 100%。生成的表格可能如下所示：

结果表也将有两列，一列用于您指定的断点 - 5％、10％、15％和20％ - 一列用于计算商店数量的公式。

请注意，如果您的商店数量很少，比如本例中的 25 家，那么您只需目测列表即可获得计数并结束。使用公式进行计数的好处是，如果您有很多商店或很多断点，它们会让您轻松获得计数。

尝试确定一个精确的公式来计算商店数量会引发一个有趣的问题。如果累计销售额没有整齐地落入您指定的分类中，您会如何处理这种情况？如果一家商店占销售额的 4.9%，那么说这家商店贡献了 5% 的销售额似乎是合理的。但如果这家商店只占销售额的 3.6%，而第二家商店贡献了 3.2% 的销售额，情况又会如何呢？那么，是一家商店还是两家商店贡献了 5% 的销售额？

对于该情况下的 1 家商店计数，您可以使用以下公式（假设结果表的百分比断点列表从单元格开始F2）：

  =COUNTIF($D$2:$D$21,"<="&F2)

将此公式向下复制将生成以下示例数据的结果表：

如果您想要获得（假设的）两家商店的数量，结果表的公式将是：

  =COUNTIF($D$2:$D$21,"<="&I2)+IF(ISERROR(VLOOKUP(I2,$D$2:$D$21,1,0)),1,0)

这有一个附加术语，用于计算使累计销售额精确达到百分比断点所需的额外商店。

Answer 2

不确定您到底想要什么，但您可以通过将每家商店的销售额除以所有商店的总销售额来确定每家商店对总销售额的贡献百分比。示例公式可能如下所示：

=B2/SUM($B$2:$B10)

这假设您的销售额在列中，B一直到第 10 行。这$将允许您将公式向下拖动而不更改总数。要么将此数字格式化为百分比，要么乘以 100 以获得百分比等值。从这里，您可以挑选出哪些符合您的 5%、10% 等标准。

Answer 3

这是一个 VBA 用户定义函数，可实现相同的功能。它执行以下操作：

1) 获取垂直范围内的数字，并将它们复制到数组中。根据参数“符号”，它将丢弃负数、丢弃正数或同时取正数和负数。

2）获取另一个函数对数组从最小到最大进行排序（未提供）。

3) 从最大到最小迭代数组，同时对每个元素求和，以找出构成 X% 所需的元素数量。

警告：由于我使用了四舍五入，我怀疑它在处理非常小的数字时不会起作用。

Function ProjectsInPercent(FromRange As Range, TopPercent As Double, sign As Integer) As Double
    Dim values()  As Variant 'array to hold values
    Dim row As Integer
    Dim sum As Double
    Dim percentOfSum As Double
    Dim tmpVal As Double
    sum = 0

ReDim values(1 To FromRange.rows.Count)

If (FromRange.rows.Count > UBound(values, 1)) Then
    ProjectsInPercent = "Too many rows in range"
ElseIf (TopPercent < 0 Or TopPercent > 1) Then
    ProjectsInPercent = "Percentile should be between 0 and 1"
End If

For row = 1 To UBound(values, 1)
    tmpVal = FromRange.Cells(row, 1).value
    If IsNumeric(tmpVal) Then
        If sign > 0 Then
            tmpVal = IIf(tmpVal > 0, tmpVal, 0)
        ElseIf sign < 0 Then
            tmpVal = IIf(tmpVal < 0, -tmpVal, 0)
        End If
        sum = sum + tmpVal
        values(row) = tmpVal
    Else
        values(row) = 0
    End If
Next row

Call QuickSort(values, 1, UBound(values, 1))

percentOfSum = sum * TopPercent
sum = 0
For row = 1 To UBound(values, 1)
    tmpVal = values(UBound(values, 1) - row + 1)
    If Round(sum + tmpVal, 1) >= Round(percentOfSum, 1) Then
        ProjectsInPercent = row + (percentOfSum - sum - tmpVal) / tmpVal
        Exit For
    End If
    sum = sum + tmpVal
Next row

End Function