Excelを使って最大値を取得しよう、という単純な内容であれば、MAX関数を使っておしまい・・・となりますが、今回は、指定した文字列に対応したデータの最大値を取得してみたいというお話です。4つの実現方法を挙げてみました。
対象データ
以下のような商品一覧データに対して、商品A、商品B、商品Cの最大値を取得したいという要件を実現したいと思います。
A | B | |
1 | 商品名 | 金額 |
---|---|---|
2 | 商品A | 1000 |
3 | 商品A | 1050 |
4 | 商品B | 500 |
5 | 商品B | 560 |
6 | 商品C | 700 |
7 | 商品A | 900 |
8 | 商品B | 480 |
データベースで実現する
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引数になります。
A | |
9 | 商品名 |
10 | 商品A |