Excelで指定した文字列に合ったデータの最大値を取得したい

Excelを使って最大値を取得しよう、という単純な内容であれば、MAX関数を使っておしまい･･･となりますが、今回は、指定した文字列に対応したデータの最大値を取得してみたいというお話です。4つの実現方法を挙げてみました。
　
　
　

対象データ

以下のような商品一覧データに対して、商品A、商品B、商品Cの最大値を取得したいという要件を実現したいと思います。

データベースで実現する

Excelで実現する前に、ちょいとズルしてAccessなどのデータベースを使ってこれを実現してみましょう。

商品Aの最大値を取得するなんて、データベースだったら簡単ですね。

SELECT MAX(金額) FROM 商品一覧 WHERE 商品名='商品A';

こんな風にGROUP BYを使えば、商品毎も簡単に実現できますね。

SELECT MAX(金額) FROM 商品一覧 GROUP BY 商品名;

小計機能を使って実現

ExcelでもデータベースのGROUP BYのような機能が用意されています。
表のどこかにマウスカーソルを当て、「データ >> 小計」をクリックします。デフォルトは小計なので商品毎の合計が表示されますが、ダイアログの設定を変更すれば簡単に最大値を表示できます。

ピボット機能を使って実現

カテゴリや商品などの項目単位で分析をしたいときには、ピボット機能がとても便利ですね。
表のどこかにマウスカーソルを当て、「挿入 >> ピボットテーブル」で、行ラベルに商品名を設定し、値に金額を設定すれば実現可能です。
こちらもデフォルトは合計になってしまっているので、金額をクリック、値フィールドの設定から最大値を選択しましょう。

VBAで実現

難しい実現方法についても考えてみました。VBAを使ってみようと。
なるべく汎用的な関数を考えてみました。以下の関数を呼び出すには、上記の例の場合「GetMaxRecoed(1, "商品A", 2)」これでOKなはず。ちなみに25行目の不等号を逆にすれば最小値も取得可能です。

lngKeyColum

検索対象文字列の入った列番号（例えばA列であれば、1が入ります）

strKey

検索文字列（先の例で言うと「商品A」などの検索したい文字列を入れます。）

lngDataColum

最大値を取得したい数値データの入った行

Function GetMaxRecoed(ByVal lngKeyColum As Long, ByVal strKey As String, ByVal lngDataColum As Long) As Double
    ' 変数の宣言
    Dim dblOutput As Double     ' 戻り値（計算をしないためExcelの数値型としてDoubleを指定）
    Dim lngLastLine As Long     ' 格納されているデータの最終行
    Dim lngLoop As Long         ' ループ用変数
    Dim flgFirst As Boolean     ' 初回フラグ
    
    ' 変数の初期化
    lngLastLine = ActiveSheet.UsedRange.Rows.Count  ' 格納されているデータの最終行を取得
    flgFirst = True                                 ' 初回フラグをセット
    
    ' 最終行まで動かす
    For lngLoop = 1 To lngLastLine
        ' 現在の行が対象となる行なのか文字列比較して確認
        If ActiveSheet.Cells(lngLoop, lngKeyColum).Value = strKey Then
            ' [エラー処理]対象となるセルのデータが数値か
            If IsNumeric(ActiveSheet.Cells(lngLoop, lngDataColum).Value) Then
                ' 初回のみ単純にコピー
                If flgFirst = True Then
                    dblOutput = ActiveSheet.Cells(lngLoop, lngDataColum).Value
                    flgFirst = False    ' 初回フラグを解除
                End If
                    
                ' 2回目以降は現在格納されている数値よりも大きければ置換
                If dblOutput < ActiveSheet.Cells(lngLoop, lngDataColum).Value Then
                    dblOutput = ActiveSheet.Cells(lngLoop, lngDataColum).Value
                End If
            End If
        End If
    Next
        
    GetMaxRecoed = dblOutput

End Function

DMAX関数で実現

最後に、ExcelのDMAX関数って、今回、調べて初めて知りましたが、こんなデータベースを扱うような関数があるのですね。
条件式を以下の様に設定するとセルの中に「=DMAX(A1:B8, B1, A9:A10)」と記入すれば最大値が取得できます。
取得したデータの第1引数がデータの領域、第2引数が最大値を求めたい行のフィールド名（該当列の一番上の項目）、条件式が第3引数になります。