时间戳

首页 > 解决方案 > 宏来计数过滤器不同的唯一值

excel - 宏来计数过滤器不同的唯一值

问题描述

我有这样的表，我必须使用宏，因为我的表每天都在变化（SSAS）所以我使用宏来自动过滤，

在此处输入图像描述

我可以根据 E 列（小计）上的相同供应商名称、PONuber 和日期来计算金额。

在此处输入图像描述

然后过滤以显示 Subtotal AMount >500

我只想显示 >500 行（E 列），并弹出消息来计算 PONumber（B 列）有多少唯一 PO 编号（仅计算可见行）

在此处输入图像描述

我一直被困在如何只计算可见的唯一采购订单号并将其显示在弹出消息中

这是我的宏

Sub FilterCOunt_Click()
Dim Condition As Variant
Dim AVal As Variant
Dim LastRow As Long
Dim Hide, popup  As Long
Dim message  As String

Dim sht As Worksheet
'----------------------------
Dim dictionary As Object
Set dictionary = CreateObject("scripting.dictionary")
'---------------------------
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.AskToUpdateLinks = False
        Application.DisplayAlerts = False
        Application.Calculation = xlAutomatic
        Application.ScreenUpdating = False
        Application.StatusBar = False
'------------------
Columns.EntireColumn.Hidden = False
Rows.EntireRow.Hidden = False
Columns("E:Z").EntireColumn.Delete
Range("E:Z").EntireColumn.Insert
Range("E1").Value = "Sub Total >500 "

Set sht = ActiveSheet

LastRow = sht.Range("B" & Rows.Count).End(xlUp).Row
'-------------------


For i = 2 To LastRow ' with last row count =SUMIFS(I:I,A:A,A8,B:B,B8,C:C,C8)

     AVal = "A" & i

     BVal = "B" & i

     CVal = "C" & i
     Worksheets("Sheet3").Range("E" & i).Formula = "=SUMIFS(D:D,A:A," & AVal & ",B:B," & BVal & ",C:C," & CVal & ")"

Next i

With sht.Range("E1:E" & LastRow)
.AutoFilter
.AutoFilter field:=1, Criteria1:=">=500"

End With
'----------Count Pop UP
Dim CountPO As Long
Range("G1").FormulaArray =  "=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B2,ROW(B2:B22)-ROW(B2),1)),IF(B2:B22<>"",MATCH(""&B2:B22,B2:B22&"",0))),ROW(B2:B22)-  ROW(B2)+1),1))"


MsgBox "We Found " & CountPO & " PO Open(s)", _
vbInformation, "PO Found"
End Sub

这是计算它的公式

{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B2,ROW(B2:B22)-ROW(B2),1)),IF(B2:B22<>"",MATCH("~"&B2:B22,B2:B22&"",0))),ROW(B2:B22)-ROW(B2)+1),1))}

标签： excelvba

解决方案

如果您通过 SSAS 从数据库中提取，您可以使用 Power Query 将您的 SSAS 数据模型链接到 Excel，您可以从那里使用 DistinctCount 在 Dax 中插入计算度量。

Count:=Calculate(DistinctCount(TableName[PONumber]),TableName[Amount]>500)

或者，如果您想全面了解您指定的问题，您可以添加一个测量列，然后您可以使用 Power Pivot 在刷新数据模型时实时过滤您的标准，完全不需要 VBA。

顺便说一句，要记住 VBA 是解决方案的大锤，请在想到宏解决方案之前使用 DataModel 工具记住，VBA 是一种应用程序编程语言，许多 IT 安全系统会禁用它，因为它会为恶意软件打开系统，您可以从字面上更改 VBA 中的任何文件或程序，包括调用删除系统文件

同时，在需要用户访问 LAN 安全性的锁定文件中设置 DataModel 比允许您的计算机具有开放的编程访问权限要容易得多。

推荐阅读