将矩阵（？）传递给Excel中的VBA

one common solution in Excel to SUMIF() with multiple conditions goes like this (this formula counts all cases in which column A has the value in C1 and column B has the value in D1):

Excel中的一个常见解决方案是SUMIF(),具有多个条件(这个公式计算所有情况,其中列A的值在C1中,列B的值在D1中):

=SUMPRODUCT((A1:A9=C1)*(B1:B9=D1))

I, now, need a function that concatenated strings from lines fulfilling multiple conditions. There is a great solution for one condition at http://www.ms-office-forum.net/forum/showthread.php?t=273352

我现在需要一个函数来连接满足多个条件的行的字符串。 http://www.ms-office-forum.net/forum/showthread.php?t=273352对于一个条件有一个很好的解决方案

I, however, want to check multiple conditions - and therefore use the SUBPRODUCT() trick from above. My problem is: How do I have to define the parameter in the function to get an Array? This is what I got so far:

但是,我想检查多个条件 - 因此使用上面的SUBPRODUCT()技巧。我的问题是:如何在函数中定义参数以获取数组?这是我到目前为止所得到的:

' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit

Public Function CONCATIF(Kriterium, Inhalte)
    Dim mydic As Object
    Dim L As Long
    Set mydic = CreateObject("Scripting.Dictionary")

    For L = 1 To UBound(Kriterium)
        If Kriterium(L, 1) = 1 Then
            If Inhalte(L, 1) <> "" Then
              mydic(L) = Inhalte(L, 1)
            End If
        End If
    Next
    CONCATIF = Join(mydic.items, vbCrLf)
End Function

This works fine, if I select one columns for parameter 1. But as soon as I include a product formula (like above), only a Variant/Double with the value 1 is passed for Kriterium.

如果我为参数1选择一列,这样可以正常工作。但是只要我包含一个产品公式(如上所述),就只会为Kriterium传递一个值为1的Variant / Double。

=CONCATIF(A1:A9; E1:E9)                    Works fine (if column A is 0/1 coded)
=CONCATIF((A1:A9=C1)*(B1:B9=D1); E1:E9)    Does not work at all

Any suggestions? Thank you!

有什么建议么?谢谢!

2 个解决方案

#1

The problem was because formula:

问题是因为公式:

=CONCATIF((A1:A9=C1)*(B1:B9=D1); E1:E9)

need to be called by pressing CTRL+SHIFT+ENTER

需要通过按CTRL + SHIFT + ENTER来调用

#2

For the sake of completeness, here's the complete Excel VBA Macro to concatenate strings from a range (may have multiple string columns), if criteria in other columns are matched.

为了完整起见,这里是完整的Excel VBA宏,用于连接范围中的字符串(可能有多个字符串列),如果其他列中的条件匹配。

' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit


Public Function CONCATIF(Kriterium, Inhalte)
    Dim mydic As Object
    Dim L As Long
    Dim C As Long
    Dim N As Long
    Dim cols As Long
    Set mydic = CreateObject("Scripting.Dictionary")
    cols = Inhalte.Columns.Count
    N = 1

    For L = 1 To UBound(Kriterium)
        If Kriterium(L, 1) = 1 Then
            For C = 1 To cols
                If Not IsEmpty(Inhalte(L, C)) Then
                    mydic(N) = "* " & Inhalte(L, C)
                    N = N + 1
                End If
            Next
        End If
    Next
    CONCATIF= Join(mydic.items, vbCrLf)
End Function

Use as follows - and don't forget to enter by CTRL+SHIFT+ENTER

使用如下 - 并且不要忘记通过CTRL + SHIFT + ENTER输入

=CONCATIF((A1:A:9="male")*(B1:B9="young"); C1:D9)

Sample data

    A       B       C           D
01  male    young   Comment 1   Comment 2
02  male    old     Comment 3   Comment 4
03  female  young   Comment 5   Comment 6
04  female  old     Comment 7   Comment 8
05  male    young   Comment 9   Comment A

Results in

* Comment 1
* Comment 2
* Comment 9
* Comment A

For Google: This shall also be found as VERKETTENWENN()

对于谷歌:这也应该被发现为VERKETTENWENN()

#1