Excel VBA中的索引和聚集功能

发布于 2025-02-11 12:03:17 字数 446 浏览 2 评论 0原文

我正在尝试将一个单元格公式转移到VBA，因为否则它总是在重新计算，即使我停用Excel选项，它也会在我重新打开文件时返回。这就是为什么我想将该公式移至VBA，只有在我按下按钮时才会发生这种情况，这更聪明。

我有一个带有数据的主表，我将其汇总和索引并在表中的另一个表上表达。 - ＆gt; A列到S在主表中，在聚合表中，我只有A，C，E，G，H，I，J，K，L，M和P列，

我想搬到VBA IS以下内容：

=IFERROR(INDEX(Endkontrolle!$A:$S;AGGREGATE(15;6;ROW(Endkontrolle!$A:$S)/((FIND($B$3;Endkontrolle!$F:$F;1)>0)*(Endkontrolle!$S:$S="x"));ROW()-32)-0;1);"")

有人可以帮助我将该公式转换为VBA脚本吗？

非常感谢

原文

I'm trying to move an in cell formula to VBA, because otherwise it's always recalculating, even when I deactivate the excel option, it comes back when I reopen the file. That's why I want to move that formula to VBA, where it happens only when I press a button, which is much smarter.

I have a master table with data, which I aggregate and index and express it on another sheet in a table. -> column A to S are in the master table, in the aggregated table, I will only have column A,C,E,G,H,I,J,K,L,M and P

The formula I want to move to VBA is the following:

=IFERROR(INDEX(Endkontrolle!$A:$S;AGGREGATE(15;6;ROW(Endkontrolle!$A:$S)/((FIND($B$3;Endkontrolle!$F:$F;1)>0)*(Endkontrolle!$S:$S="x"));ROW()-32)-0;1);"")

Can somebody help me translate that formula to VBA script?

thank you very much

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

萝莉病 2025-02-18 12:03:17

尝试此代码：

Sub Button1_Click2()
    
    'Declarations.
    Dim RngTable As Range
    Dim RngTarget As Range
    Dim StrColumnsIndex As String
    
    'A string is used to stores the index of the columns to be copied.
    StrColumnsIndex = "1;3;5;7;8;9;10;11;14;15;16"
    
    'RngTable is set as the range that will host the aggregated table.
    Set RngTable = Sheets("Aggregated sheet").Range("A33:K34") '< EDIT THIS LINE ACCORDGLY TO YOU NEED
    
    'Clearing RngTable.
    RngTable.ClearContents
    
    'Checking if StrColumnsIndex and RngTable are compatible.
    If UBound(Split(StrColumnsIndex, ";")) + 1 <> RngTable.Columns.Count Then
        MsgBox "The number of columns requested via StrColumnsIndex and the number of columns avaiable in RngTable do not match. Redefine the variables properly. The aggregated table will not be updated.", vbCritical + vbOKOnly, "Variable mismatch"
        Exit Sub
    End If
    
    'Covering each cell in RngTable.
    For Each RngTarget In RngTable
        
        'The result is reported in each cell. The [row] element of the INDEX is obtained by subtracting _
        RngTable.Row from the RngTarget.Row and adding one. This way each row is properly reported. The _
        [col] element of the INDEX is obrained by splitting StrColumnsIndex using the difference between _
        the RngTarget.Column and RngTable.Column as index. This way each requested column as listed in _
        StrColumnsIndex is reported.
        'RngTarget.Formula = "=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x""))," & RngTarget.Row - RngTable.Row + 1 & ")-0," & Split(StrColumnsIndex, ";")(RngTarget.Column - RngTable.Column) * 1 & "),"""")"
        RngTarget.Value = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x""))," & RngTarget.Row - RngTable.Row + 1 & ")-0," & Split(StrColumnsIndex, ";")(RngTarget.Column - RngTable.Column) * 1 & "),"""")")
        
        'If RngTarget contains nothing then it's assumed there are no more results to be reported and the macro is terminated.
        If RngTarget.Value = "" Then Exit Sub
        
    Next
    
End Sub

Try this code:

Sub Button1_Click2()
    
    'Declarations.
    Dim RngTable As Range
    Dim RngTarget As Range
    Dim StrColumnsIndex As String
    
    'A string is used to stores the index of the columns to be copied.
    StrColumnsIndex = "1;3;5;7;8;9;10;11;14;15;16"
    
    'RngTable is set as the range that will host the aggregated table.
    Set RngTable = Sheets("Aggregated sheet").Range("A33:K34") '< EDIT THIS LINE ACCORDGLY TO YOU NEED
    
    'Clearing RngTable.
    RngTable.ClearContents
    
    'Checking if StrColumnsIndex and RngTable are compatible.
    If UBound(Split(StrColumnsIndex, ";")) + 1 <> RngTable.Columns.Count Then
        MsgBox "The number of columns requested via StrColumnsIndex and the number of columns avaiable in RngTable do not match. Redefine the variables properly. The aggregated table will not be updated.", vbCritical + vbOKOnly, "Variable mismatch"
        Exit Sub
    End If
    
    'Covering each cell in RngTable.
    For Each RngTarget In RngTable
        
        'The result is reported in each cell. The [row] element of the INDEX is obtained by subtracting _
        RngTable.Row from the RngTarget.Row and adding one. This way each row is properly reported. The _
        [col] element of the INDEX is obrained by splitting StrColumnsIndex using the difference between _
        the RngTarget.Column and RngTable.Column as index. This way each requested column as listed in _
        StrColumnsIndex is reported.
        'RngTarget.Formula = "=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x""))," & RngTarget.Row - RngTable.Row + 1 & ")-0," & Split(StrColumnsIndex, ";")(RngTarget.Column - RngTable.Column) * 1 & "),"""")"
        RngTarget.Value = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x""))," & RngTarget.Row - RngTable.Row + 1 & ")-0," & Split(StrColumnsIndex, ";")(RngTarget.Column - RngTable.Column) * 1 & "),"""")")
        
        'If RngTarget contains nothing then it's assumed there are no more results to be reported and the macro is terminated.
        If RngTarget.Value = "" Then Exit Sub
        
    Next
    
End Sub

回复收藏 0 原文

何止钟意 2025-02-18 12:03:17

谢谢你。我实现了它，它适用于1行。如果我想从主表中添加下一个数据集，则仅重复上一行中的内容。我该如何实现，它列出了我超过1行的汇总数据？

预期结果：
它选择了相关的数据行，并列出了它（根据发现标准不同的数据）

实际结果：
它仅选择1行，然后重复第二行

，现在我定义了以下代码：

Sub Button1_Click()  
Cells(33, 1) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,1),"""")")
Cells(33, 2) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,3),"""")")
Cells(33, 3) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,5),"""")")
Cells(33, 4) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,7),"""")")
Cells(33, 5) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,8),"""")")
Cells(33, 6) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,9),"""")")
Cells(33, 7) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,10),"""")")
Cells(33, 8) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,11),"""")")
Cells(33, 9) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,14),"""")")
Cells(33, 10) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,15),"""")")
Cells(33, 11) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,16),"""")")


Cells(34, 1) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,1),"""")")
Cells(34, 2) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,3),"""")")
Cells(34, 3) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,5),"""")")
Cells(34, 4) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,7),"""")")
Cells(34, 5) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,8),"""")")
Cells(34, 6) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,9),"""")")
Cells(34, 7) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,10),"""")")
Cells(34, 8) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,11),"""")")
Cells(34, 9) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,14),"""")")
Cells(34, 10) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,15),"""")")
Cells(34, 11) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,16),"""")")

end sub

Thanks for that. I implemented it and it works for 1 row. If I want to add the next data set from the main table, that does only repeat the content from previous row. How can I achieve, that it lists me more than 1 line of aggregated data?

Expected result:
it picks the relevant rows of data and lists it (different data according the find criteria)

Actual result:
it picks only 1 row and repeats it for the second line

Now I defined following code:

Sub Button1_Click()  
Cells(33, 1) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,1),"""")")
Cells(33, 2) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,3),"""")")
Cells(33, 3) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,5),"""")")
Cells(33, 4) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,7),"""")")
Cells(33, 5) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,8),"""")")
Cells(33, 6) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,9),"""")")
Cells(33, 7) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,10),"""")")
Cells(33, 8) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,11),"""")")
Cells(33, 9) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,14),"""")")
Cells(33, 10) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,15),"""")")
Cells(33, 11) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,16),"""")")


Cells(34, 1) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,1),"""")")
Cells(34, 2) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,3),"""")")
Cells(34, 3) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,5),"""")")
Cells(34, 4) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,7),"""")")
Cells(34, 5) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,8),"""")")
Cells(34, 6) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,9),"""")")
Cells(34, 7) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,10),"""")")
Cells(34, 8) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,11),"""")")
Cells(34, 9) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,14),"""")")
Cells(34, 10) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,15),"""")")
Cells(34, 11) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,16),"""")")

End Sub

回复收藏 0 原文

忆梦 2025-02-18 12:03:17

这是主表“ Endkontrolle”中的数据示例：

日期	产品	员工	...	日期范围
22.04.2022	Moti	akah	...	x
23.04.2022	Moti_bg	akah	...	x
26.04.2022	Moti	Akah	...

在报告页面上，我想列出多达20行数据，这些数据位于“ Endkontrolle”工作表的日期范围（'x'）中。
在上面的示例中，它应列出第1行1+2，而不是3。

Here's a sample of the data in the main table "Endkontrolle":

Date	Product	Employee	...	Date Range
22.04.2022	MOTI	AKAH	...	x
23.04.2022	MOTI_BG	AKAH	...	x
26.04.2022	MOTI	AKAH	...

On the reporting page, I would like to list down up to 20 rows of Data, which are in the Date Range ('x') from the "Endkontrolle" worksheet.
In the upper example, it should list row 1+2, but not 3.

回复收藏 0 原文

~没有更多了~