Excelで条件にマッチする行番号を文字列で返す関数をつくってみた。

はじめに

元ネタは『Excelで列の値が条件に一致する行を別の場所に抜き出して表示する』です。
元ネタに、以下のようにコメントを付けたのですが、MATCHだと "条件" で検索することはできないが、
COUNTIF　だと "条件" が書けます。これが利用できないかと考えました。

(A2)　=IF(COUNTIF(F2,"条件")=1,COUNTIF(F$2:F2,"条件"),"")
(A3)　=IF(COUNTIF(F3,"条件")=1,COUNTIF(F$2:F3,"条件"),"")
...

最初のプログラム

RANGEの範囲をOFFSETで移動させ、RESIZEで１セルにしたので、返る値は０か１です。
条件に合った場合は１なので、行数をコンマ付きで文字列Sに追加します。

Function ML(R, V, Optional S = "") ' MATCH LINES
    Dim I: For I = 1 To R.Rows.Count
        If WorksheetFunction.CountIf(R.Offset(M - 1).Resize(1, 1), V) = 1 Then S = CC(S, I, ",")
    Next I
    ML = S
End Function

Function CC(ParamArray PA())
    On Error Resume Next
    Dim S: S = ""
    Dim I: For I = LBound(PA) To UBound(PA)
       S = S & PA(I)
    Next I
    CC = S
End Function

少し改良

WorksheetFunction が煩いので関数CIFで包み、関数MLの返り値の最後のコンマを取り除きます。
更に、引数Lに行番号の文字列を与えると、検索範囲を絞れるようにします。
全件検索を L="0" としました。そうしないと、検索範囲なしの L="" と区別がつかなくなります。

Function CIF(R, M, V, Optional S = 0)
    On Error Resume Next
    S = WorksheetFunction.CountIf(R.Offset(M - 1).Resize(1, 1), V)
    CIF = S
End Function

Function ML(R, V, Optional L = "0", Optional S = "") ' MATCH LINES
    On Error Resume Next
    Dim A, I, J
    
    If L = "0" Then
        For I = 1 To R.Rows.Count
            If CIF(R, I, V) = 1 Then S = CC(S, I, ",")
        Next I
    ElseIf L = "" Then
        
    Else
        A = Split(L, ",")
        For I = LBound(A) To UBound(A)
            J = A(I)
            If CIF(R, J, V) = 1 Then S = CC(S, J, ",")
        Next I
    End If
    If S <> "" Then S = Left(S, Len(S) - 1)
    ML = S
End Function

CIF,MLの使用例

(O1)  =CIF(M02N_[O],3,7000)                [1]
(O2)  =CIF(M02N_[O],3,7001)                []
(O3)  =ML(M02N_[O],7000)                   [3,11,20,27,28,29,122,123,124]
(O4)  =ML(M02N_[O],7001)                   []
(O5)  =ML(M02N_[済],1,ML(M02N_[O],7000))   [11,20,27,28,29,122,123,124]
(O6)  =ML(M02N_[済],1,ML(M02N_[O],7001))   []

(O5)の使用例は、順番が判り易くありません。複数の条件を適用順に定義できる関数を作成します。

引数の順序を並べかえた関数を定義する。

引数の並びを分かり易いように、MLS(R1,V1,R2,V2,....)　としました。

Function MLS(ParamArray PA())                       ' MATCH LINES IFS
    Dim S: S = "0"
    Dim I: For I = LBound(PA) To UBound(PA) Step 2
        S = ML(PA(I), PA(I + 1), S)
    Next I
    MLS = S
End Function

MLSの使用例

(O7)  =MLS(M02N_[O],7000,M02N_[済],1)     [11,20,27,28,29,122,123,124]
(O8)  =MLS(M02N_[O],7000,M02N_[済],0)     [3]
(O9)  =MLS(M02N_[O],7001,M02N_[済],1)     []
(O10) =MLS(M02N_[O],7001,M02N_[済],0)     []
(O11) =MLS(M02N_[O],">7000",M02N_[済],0)  [1,2,4]

MLSの改良

引数の個数が奇数か偶数かにより、引数の並びの意味を変えてみる。

(奇数の場合) MLS(L,R1,V1,R2,V2,...)
(偶数の場合) MLS(R1,V1,R2,V2,...)

Function MLS(ParamArray PA())                       ' MATCH LINES IFS
    Dim S: S = IFC(WorksheetFunction.IsOdd(UBound(PA) - LBound(PA)), "0", PA(LBound(PA)))
    Dim J: J = IFC(S = "0", 0, 1) + LBound(PA)
    Dim I: For I = J To UBound(PA) Step 2
        S = ML(PA(I), PA(I + 1), S)
    Next I
    MLS = S
End Function

Function IFC(C, V, Optional S = "")
    If C Then IFC = V Else IFC = S
End Function

以上