excel - 如何使用立方体函数过滤Excel中的多个维度？

Question

我已阅读这篇文章 ( use Cube Functions to get filtered dimensions )，它非常有帮助，但我想再添加一级过滤。

假设我的数据在 PowerPivot 中如下所示:

Month     Category     Product #     Revenue
January   Trucks       00000001      $50000
January   Trucks       00000002      $75000
January   Cars         00000005      $45000
January   Cars         00000008      $90000
January   Trucks       00000003      $10000
February  Cars         00000005      $10000

所以基本上我有汽车或卡车，我想返回 1 月、2 月等月份每个类别中最畅销的 2 种产品。

如果我只过滤一维，我就可以轻松找到最畅销的产品。这样我就可以找到一月份最畅销的产品(卡车或汽车)。我使用了上面提供的链接中的方法。但我想添加一层，并说，只查找一月份最畅销的卡车。

我该如何去做呢？我希望我可以使用“非空”并根据需要添加每个过滤维度/条件，但也许我不明白语法应该如何。

Answer 1

我创建了一个应该满足您的要求的自定义函数，因此避免了车辆销售数据多维报告所需的多个嵌套多维数据集函数的复杂设计和维护。

另一个优点是，使用此方法，可以轻松创建和编辑多个变体，以便为 future 的报告需求提供附加功能。

用法与Excel内置的Rank函数类似:

• 从工作表中调用该函数，指定月份和/或类别，以及您需要返回的排名结果“排名编号”。

For example, to get the Top (#1) selling Truck for January, you could use formula:

=GetTopSeller ( "January", "Trucks", 1 )

or, to get the 10th best selling car for the month listed in cell A1, you could use formula:

=GetTopSeller ( A$1, "Cars", 10 )

<小时/>

下图显示了语法和用法，以及测试函数时使用的示例数据集，以及示例输出基于样本数据*。

Option Explicit
'Written by ashleedawg@outlook.com for https://stackoverflow.com/q/47213812

Const returnForNoValue = "" 'could be Null, 0, "(Not Found)", etc

Public cnn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String

'Additional features which can be easily added on request if needed:
' add constants to specify Revenue or Product ID
' allow annual reporting
' allow list of vehicle types, months, etc
' make case insensitive


Public Function GetTopSeller(sMonth As String, sCategory As String, _
    sMonthRank As Integer) As Variant() '1=ProductID  2=Revenue

    Dim retData(1 To 2) As Variant

    strSQL = "Select Month, Category, [Product #], Revenue from [dataTable$] " & _
        "WHERE [Month]='" & sMonth & "' AND [Category]='" & sCategory & "' _
        Order by [Revenue] DESC"

    ' close Excel Table DB _before_ opening
    If rs.State = adStateOpen Then rs.Close
    rs.CursorLocation = adUseClient

    ' open Excel Table as DB
    If cnn.State = adStateOpen Then cnn.Close
    cnn.ConnectionString = "Driver={Microsoft Excel Driver " &
        "(*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
        ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
    cnn.Open

    ' find appropriate data
    With rs
        .Open strSQL, cnn, adOpenKeyset, adLockOptimistic
        .MoveFirst
        If (.RecordCount <= 0) Or (.RecordCount < sMonthRank) _
            Or (sMonthRank = 0) Then GoTo queryError

        'move the Nth item in list
       .Move (sMonthRank - 1)
        retData(1) = ![Product #]
        retData(2) = !Revenue
    End With

    'return  value to the user or cell
    GetTopSeller = retData
Exit Function

queryError:
    'error trapped, return no values
    retData(1) = returnForNoValue
    retData(2) = returnForNoValue
    GetTopSeller = retData

End Function

<小时/>

下面是将函数复制到工作簿中的说明，从而使其可以作为工作表函数进行访问。或者，可以将示例工作簿另存为附加组件，然后通过创建对该附加组件的引用从任何工作簿进行访问。

<小时/>

如何将 VBA 函数添加到工作簿:

1. 选择下面的 VBA 代码，然后按 Ctrl+C 进行复制。

2. 在 Excel 工作簿中，按 Alt+F11 打开 VBA 编辑器(又名 VBE)。

3. 单击 VBE 中的插入菜单，然后选择模块。

4. 按 Ctrl+V 粘贴代码。

5. 单击 VBE 中的调试菜单，然后选择 **编译项目*。这会检查代码是否有错误。理想情况下，“什么也不会发生”，这意味着它没有错误并且可以正常运行。

6. 通过单击 VBE 右上角的“✘”关闭 VBE 窗口。

7. 保存工作簿。 您的新功能现在可以使用!

<小时/>

该功能的使用应该是不言自明的，但如果您需要更改、遇到问题或有任何疑问，请随时与我联系。

祝你好运!