条件付きで最大値を抽出

エクセルで最大値の抽出が上手くいかず困っています。

地域コード.......品名.........数量
0001 ...............A...........100
0001................B............90
0001................C...........110
0002 ...............B............50
0002............... D............80
0002............... E............20
0003................A...........300
0003................B...........150

※.....は空白を空けるための記号なので気にしないで下さい

上記データを使って以下のように各地域で最も数量が多い品名の
一覧表を作成したいのですが、うまくいかず苦戦しております。

地域コード.......品名.........数量
0001................C...........110
0002............... D............80
0003................A...........300

別シートに0001、0002、0003・・・と入力しておいて
その隣の列にその地域で最も数量が多い品名と数量を抽出しようと
VLOOKUPやDMAX等を使っているのですが上手くいきません。

どなたかご助言お願いします。

No.2 回答者： WindFaller 回答日時： 2015/02/10 12:19

こんにちは。

DMAXでできますが、レイアウト的にきれいには収まらないです。

地域コード
0001　書式[0000]

数量
=DMAX($A$1:$C$9,$C$1,$E1:$E2)

品名
=DGET($A$1:$C$9,$B$1,$E1:$F2)

結果は、
地域コード　　　数量　　　品名　　
0001　　　　　　110　　　C
地域コード　　　数量　　　品名
0002　　　　　　80　　　D
地域コード　　　数量　　　品名
0003　　　　　　300　　　A

のようになります。数式は、最初の2行をコピーして、2行目ごとに貼り付けていくだけです。
後は、地域コードを書き換えるだけです。
品名が後にくるのは、クライテリアを[地域コード]と[数量]両方を使うからです。
実際にレポートを作る場合は、コピーして、「値」貼り付けなどして加工したらよいかと思います。
それから、DGET は、同じ条件が複数ある場合は、#NUM!というエラーがでます。

*******
そこで、もうちょっとうまい使い方があるような気もしますが、ピボットテーブルを使ってみました。
画像ではちょっと見づらいかもしれません。

列ラベル
品名

行ラベル
地域コード

値
数量で、集計を最大値にする

ピボットテーブルから、地域選択などのデータを取り出すのは、

0001　　C　　　110

真ん中の品名を出す部分だけ、(同じシート内の場合）

※コピーすると、MATCH関数の中は、以下のようになってしまいます。
=INDEX($B$2:$F$2,1,MATCH(GETPIVOTDATA("数量",$A$1,"地域コード",ROW(A1)),B3:G3,0))
このような数式になります。

やはり上記と同じように、レポートに出すような場合は、コピーして「値」貼り付けにしたほうがよいでしょう。

この回答へのお礼

お礼が遅くなってしまい申し訳ありません。
ピポッドを使うという方法もあるのですね。
お陰様で完成しました。

お礼日時：2015/03/29 00:05

No.1 回答者： tom04 回答日時： 2015/02/09 21:17

こんばんは！

一例です。

↓の画像のようにSheet1のデータをSheet2に表示するとします。
画像ではSheet2のB2セルに
=IFERROR(INDEX(Sheet1!B$1:B$1000,MATCH(MAX(IF(Sheet1!$A$1:$A$1000=$A2,Sheet1!$C$1:$C$1000)),Sheet1!$C$1:$C$1000,0)),"")
これは配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　B2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。
このB2セルを列・行方向にフィルハンドルでコピー！
これで画像のような感じになります。

※　配列数式はPCにかなりの負担をかけますので、極端にデータ量が多い場合はおススメしません。
とりあえず1000行まで対応できる数式にしていますが、3000行程度であれば問題ないと思います。
もっとデータ量が多い場合は作業用の列などを設けるなど
別の方法を考える必要があります。

まずはこの程度で・・・m(_ _)m

この回答へのお礼

早速のご回答ありがとうございました。
ご丁寧に画像まで付けて頂いて助かります。
早速明日会社で試してみます。

お礼日時：2015/02/09 22:52