如何忽略Excel公式中的过滤掉的数据 [英] How to ignore filtered-out data in Excel formula

问题描述

我使用索引/匹配从相关表中获取数据以填充第一个表。在我的相关表中，我已经过滤出值，但过滤掉的值仍然填充在我的第一个表中。如果索引/匹配不够聪明，只能抓取过滤的值，那么我该如何解决这个问题（公式首选，但VBA可以接受）才能获得过滤的值。

I am using an Index/Match to get data from a related table to populate in the first table. In my related table I have filtered out values, but the filtered out values are still populating in my first table. If Index/Match is not smart enough to only grab the filtered values, how can I work around this (formula preferred, but VBA acceptable) to get only the filtered values.

这是我当前的公式：

=INDEX(Table_owssvr__1[MyValues],MATCH([@[ID]],Table_owssvr__1[ID],0))

推荐答案

通过以下方式获得此工作：

I have been able to get this working by the following:

1）创建三个工作表，一个用于客户，一个用于购买，一个用于purchaseforclient。

1) Create three worksheets, one for clients, one for purchases, and one for purchasesforclient.

2）创建一个宏将过滤的值复制到新的工作表中：

2) Create a Macro to copy filtered values to a new worksheet:

Sub Purchases()
Dim Rng As Range
Set Rng = Worksheets("Comments").Columns("A")
Set Rng = Rng.Resize(65535, 1).Offset(1, 0)
Set Rng = Rng.Resize(, 5).SpecialCells(xlCellTypeVisible)
Rng.Copy Worksheets("PurchasesforClient").Range("A2")
End Sub

3）当我通过一个更新购买过滤器，我通过创建小计字段并在下面触发宏来执行步骤2中的宏。由于它是一个公式，它需要进行计算。这是嵌入在采购表中作为过滤发生的VBA，其中B23是在应用过滤器后计算项目数量时更改的小计字段：

3) When I update the purchases via a filter, I run the macro in step 2 by creating a subtotal field and triggering the macro as follows. Since it is a formula, it requires a calculation to occur. This is embedded in the purchases sheet as VBA where the filtering is occurring, where B23 is the subtotal field that changes when it counts the amount of items once a filter is applied:

Public CurrentValue As Double

Private Sub Worksheet_Activate()
CurrentValue = Application.WorksheetFunction.Sum(ActiveSheet.Range("B23"))
End Sub

Private Sub Worksheet_Calculate()
If Application.WorksheetFunction.Sum(Range("B23")) <> CurrentValue Then Purchases
End Sub

4）我在使用客户工作表中使用现在过滤的值对于我的索引/匹配公式在客户端工作表中。这使我能够按日期，购买类型等动态过滤，并在客户端工作表中更新信息

4) I use the now filtered values in the purchasesforclient worksheet for my index/match formula in the clients worksheet. This allows me to dynamically filter by date, purchase type, etc. and have updated information in the clients worksheet

这篇关于如何忽略Excel公式中的过滤掉的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！