Excel - 複数の値を検索するための複雑なインデックス マッチ

Question

テーブル上でかなり複雑な検索を実行し、複数の値を返そうとしています。

含まれていないID場合は、それぞれをグループとして取得する必要がありますService Codeプラスまたは含まれている場合例外Sales無視してください。Service しない一致する場合は無視します。それ以外はすべてリストに出力する必要があります。

私には手に負えない状況にあり、どこから始めればいいのかさえ分かりません。

---

つまり、プラスサービスでありSales、Service例外作成されました。A、B、C のデータはまったく重要ではありませんが、そこにあります。

Answer 1

さて、VBAに挑戦してみました。何をしたいのか少し混乱してしまいました例外が存在するため、現在、例外がある場合はそのグループ全体がスキップされます。これが希望する動作でない場合はお知らせください。数式が存在するというあなたの意見は正しいかもしれませんが、おそらくこれは愚かなほど過剰に設計されているように見えます。しかし、誰かの役に立つことを願っています。

Sub CalculateList()
    Dim startRowCount As Integer: startRowCount = 2     'Row to start on
    Dim columnId As Integer: columnId = 1               'Column containing ID
    Dim OutputColumn As Integer: OutputColumn = 7       'Column to output into

    '------ Begin

    Cells(1, OutputColumn).Select                       'Selects the output cell, incase there are any successes

    Dim rowCount: rowCount = startRowCount              'Sets our start row count for the loop
    Dim RowsContainingId()                              'Array containing the row ID's matching the current ID of the loop
    ReDim RowsContainingId(1)
    Dim CompletedIds()                                  'Array containing the ID's that have already been done
    ReDim CompletedIds(1)

    Do                                                  'Begin loop
nextNumberStart:                                        'Label to go back to if we hit an already completed ID
        Dim CurrentID As String: CurrentID = Cells(rowCount, columnId).Value            'Get the CurrentID of this loop
        If (CurrentID = "") Then                        'If we hit a blank, we're done, so...
            Exit Do                                     'Exit the loop
        End If
        If (IsInArray(CurrentID, CompletedIds)) Then    'Check if this ID has already been handled
            rowCount = rowCount + 1                     'It has, so select the next row
            GoTo nextNumberStart:                       '...And begin the loop again
        End If

        GetAllMatchingRowIds CurrentID, RowsContainingId, startRowCount, columnId   'Runs sub lower down to populate the array with all the rows with this ID
        If (TestIdMeetsCriteria(RowsContainingId)) Then     'Test these rows to see if it meets your criteria (function below)
            ActiveCell.Value = CurrentID                'It's a success, so write this ID in our active cell
            ActiveCell.Offset(1, 0).Activate            'And select the cell one row down, ready to write again if needed
        End If


        CompletedIds(UBound(CompletedIds) - 1) = CurrentID      'Add this ID to our completed list
        ReDim Preserve CompletedIds(UBound(CompletedIds) + 1)   'Grow our array one more ready for next completed item

        Erase RowsContainingId                          'Wipe out our Matching ID array for next loop
        ReDim RowsContainingId(1)
        rowCount = rowCount + 1
    Loop
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)           'Checks if a string is in the array
End Function

Function TestIdMeetsCriteria(arr As Variant) As Boolean
    Dim row
    Dim HasException, ValidPlus: HasException = False: ValidPlus = False        'Set our bools to False
    For Each row In arr                                             'Loop through each row containing our ID
        If (LCase(Cells(row, 2).Value) = "plus") Then               'Check for "Plus" (case insensitively)
            If (LCase(Cells(row, 3).Value) = LCase(Cells(row, 4).Value)) Then   'We got Plus, do we have matching Service and Sales (case insensitive)?
                ValidPlus = True                                    'We do. Set ValidPlus to true for comparison later
            End If
        ElseIf (LCase(Cells(row, 2).Value) = "exception") Then      'Now check if any rows contains Exception (case insensitive)
            HasException = True                                     'We do. Set HasException to true for later comparison
        End If
    Next
    TestIdMeetsCriteria = Not (HasException) And ValidPlus          'We finished the loop. If HasException is still false, and ValidPlus is now true, it was a success, otherwise a failure
End Function

Sub GetAllMatchingRowIds(idToFind As String, arr As Variant, startRowId As Integer, columnId As Integer)
    Dim rowCount: rowCount = startRowId
    Do                                                          'Loop through our ID column rows
        If (Cells(rowCount, columnId).Value = idToFind) Then    'Check if the ID is the one we're after
            arr(UBound(arr) - 1) = rowCount                     'It is, add it to our array
            ReDim Preserve arr(UBound(arr) + 1)                 'Grow our array for next lop
        End If
        rowCount = rowCount + 1                                 'Next row
    Loop Until IsEmpty(Cells(rowCount, columnId).Value)         'Keep looping until we hit a blank
    ReDim Preserve arr(UBound(arr) - 2)                         'Remove additional entries in array we don't need
End Sub

結果：

Answer 2

私が正しく理解していれば、ID のサービス コードにプラス記号が含まれていない場合に ID コードを除外することを除いて、上記のようなリストが必要なのですね。さらに、販売とサービスが一致しない場合は、exceptionサービス コードにプラス記号が含まれていない限り、それらは含まれません。

ヘルパー列について考えたことはありますか?

これを行う最も簡単な方法は、列 A + B を列 E に連結して、ID 番号とサービス コードの行を作成することです。

次に、行 E の countif ステートメントを使用して、 whereIDおよびPLUS=をチェックできますtrue。

次に、名前と例外を確認する必要があります。

次に、ネストされた IF ステートメントを使用して、一致、例外、プラスをチェックできます。

Eでは明らかに=CONCATENATE(a2,b2)

Fは次のようになります=if(countif(E:E,CONCATENATE(a2,"PLUS"))>0,"PLUS","")

Gは名前を確認するために使用する必要があるので、=Concatenate(a2,if(c2=d2,"Match","ERROR")

名前がすべて正しいか、例外が存在するかを確認するために使用する必要がある H。

=if(and(countif(g:G,concatenate(a2,"ERROR")>0,NOT(countif(e:e,concatenate(a2,"EXCEPTION")>0))),"No match","match")

最後に、Iでは、Plus = Plus、名前一致 = Matchを探すことができます。

=IF(AND(F2="Plus",H2="Match"),"Include","Exclude")

これにより（私の式が正しければ、結果は異なる場合があります！）、リストを「INCLUDE」でフィルタリングできるようになります。これで、 と がIDまたはserviceのsaleいずれかに一致し、のサービスコードエントリのException少なくとも1つに が含まれるリストが作成されます。IDplus

Answer 3

これは VBA よりも柔軟なソリューションですが、多少は手動ワークフローのハイブリッドです。

「営業担当によるサービス」というタイトルの列TRUEを追加しますFALSE

=C2=D2

次に「例外あり」というタイトルの別のTRUE列FALSE

=COUNTIFS(A:A, A2, B:B, "Exception")

Serviced by Sales = TRUE次に、とでテーブルをフィルタリングするだけですHas exception = FALSE。 リストを取得するには、ID をコピーして別の場所に貼り付け、 を実行しますData > Remove Duplicates。

リストのリアルタイム レポート生成を期待している場合は、VBA ソリューションをお勧めします。