エクセルの各範囲で最大値を求めたい。

質問

エクセルの各範囲で最大値を求めたい。

エクセルのA列に商品名、B列に数量が書かれています。これを商品名を基準に「並べ替え」をすると、次のような表(A列）（B列）になりました。

　　　　　（A列）　　　　　（B列）　　　（C列）
　　　　　商品名　　　　　数量　 最大値
　　　　　　いちご　　　　　18　　　　 42
　　　　　　いちご　　　　　42　　
　　　　　　いちご　　　　　35
　　　　　　いちご　　　　　22
　　　　　オレンジ　　　　　18　　　　　　33
　　　　　オレンジ　　　　 25
　 　 　 オレンジ　　　　　 33
　　 　 　バナナ　 　　　　21　　　　　　27
　　　　　 バナナ　　 　　　27　　　　　　

　ここで、各商品の数量の最大値を、C列の、各商品の
先頭行に表示したい。（　上記表示　）

　データは大量にあり、並べ替えの結果、同じ商品が何行になるかはやってみないとわかりません。
　人が、同じ商品の範囲を指定して、最大値を求めていくことは、量的に不可能です。
自動的に求める方法を教えてください。
よろしくお願いします。

回答No.3

G_Amino · ベストアンサー

prmoonさんの配列数式を使う方法は汎用性が高く、いろいろと応用がきくのですが、高等テクニックで少々難しいです。そこで、もう少しローテクでなんとかする方法を考えてみました。

１．並べ替える際に、「２番目に優先されるキー」として「数量」を降順に指定して並べ替える。
２．Ｃ列の先頭行(C2)に下記の式を入力し、他の行にコピーする。
　　=IF(A2=A1,"",B2)

要するに、各商品名の先頭行に数量の最大値がくるようにしておいて、その行のみＣ列に数量を表示するわけです。

お礼

早速のご回答ありがとうございます。
ご指示の通りに並べ替えをして、C列に指定の式を入力すると、各商品名の先頭行のC列に、数量の最大値を表示できました。
prmoonさんのお言葉を借用させていただきますと、質問の回答として完璧で、シンプルで見事です。ありがとうございました。
関連質問ですが、各商品名の先頭行（数量の最大値を表示した行）のみ残して、他の行を削除する方法を教えてください。VBAでもなんとか理解できると思いますが、出来るだけローテクでお願いします。

回答No.6

G_Amino

行削除の件ですが、私は「オートフィルタ」好きなので、それを使った方法を紹介します。
※削除を伴う操作の前には、ファイルを保存しておくことをおすすめします。

１．Ｃ列全体を選択して[編集(E)]-[コピー(C)]した後、続けて[編集(E)]-[形式を選択して貼り付け(S)...]を選択し、[値(V)]にチェックを付けて[OK]をクリック。
２．次に表全体を選択しておいて、[データ(D)]-[フィルタ(F)]-[オートフィルタ(F)]を選択。列見出しに下向き三角が表示されますので、「最大値」の欄の下向き三角をクリックして「(空白セル)」を選んでください。最大値が空白の行のみが残り、その他の行は非表示になります。
３．残った行をすべて選択し削除してください。
４．もう一度、[データ(D)]-[フィルタ(F)]-[オートフィルタ(F)]を選択してオートフィルタを解除すると、最大値が表示されている行が再表示されます。

注意点としては、「１」をしておかないと、Ｃ列に表示させた最大値がエラー表示になってしまいます。
ちょっと、ごちゃごちゃしていますが、オートフィルタは簡単な操作で、わりあい複雑な条件の抽出ができるので、いろいろ試してみてください。

お礼

ありがとうございました。
これで一連の問題が解決しました。初心者にわかるように順を追って説明いただき、助かりました。解決方法は色々なやりかたがあるんですね。本当にありがとうございました。

回答No.5

noname#18862

行削除の件ですが、下記の方法はどうでしょう。

表を範囲選択する（任意の範囲）→「編集」→「ジャンプ」→「セル選択」をクリック→「空白セル」にチェックを入れる→「ＯＫ」を押す

これで空白のセルが選択された状態になるので、後は右クリックで削除か、ショートカットキーなら「Ctrl」＋「－」ボタンで削除してください。

なお、「ジャンプ」へのショートカットキーは「Ctrl」＋「G」です。

参考になれば幸いです。

お礼

ありがとうございました。
これで一連の問題が解決しました。初心者にわかるように順を追って説明いただき、助かりました。解決方法は色々なやりかたがあるんですね。本当にありがとうございました。

回答No.4

noname#18862

＃１です。度々すみません。

下記のURLを参考までに・・・。しかし、G_Aminoさんの方法が、質問の回答として完璧で、シンプルで見事です！

参考URL：

http://t_shun.at.infoseek.co.jp/My_Page/Excel-REF/ref_page4-1.htm

お礼

お礼： 早速のご回答ありがとうございます。
「配列数式」については知りませんでした。
G.Aminoさんのお言葉を借用させていただきますと、配列数式を使う方法は汎用性が高く、いろいろと応用がきくということで、勉強して参考にさせていただきます。「私のエクセル便利帳」の必需品になりそうです。ありがとうございました。
関連質問ですが、各商品名の先頭行（数量の最大値を表示した行）のみ残して、他の行を削除する方法を教えてください。VBAでもなんとか理解できると思いますが、出来るだけローテクでお願いします。

回答No.2

noname#18862

＃１です。

中盤の表の作り方が間違っていました。

　　　E　　　　　D
１　商品名 最大値
２　いちご
３　オレンジ
４　バナナ

本来、EとDは逆です。でも式はこの表にあわせて作っていますのでご了承ください。実際には、Wesleyさんの表に合わせて式を作成してください。申し訳ございません。

回答No.1

noname#18862

はじめまして。

質問の回答になるかどうかわかりませんが、それぞれの商品の最大値を求める、と解釈しまして下記の方法はどうでしょうか。この方法ではデータの並べ替えは必要ありません。

いちごの最大値
=MAX(IF(商品名の列範囲="いちご",数量の列範囲,""))

と入力して[Ctrl]+[Shift]+[Enter]キーを押します。すると、

{=MAX(IF(商品名の列範囲="いちご",数量の列範囲,
""))}

というように式の両端に中括弧（{}）がつきます。これは「配列数式」というもので、複数の条件での集計に便利です。

あとは、式の「いちご」の部分を「オレンジ」「バナナ」に変えると、それぞれの最大値が求められます。

ちなみに式の「MAX」を「SUM」に変えると、それぞれの商品の合計が求められたり、「AVERAGE」で平均を求めることも可能です。

たとえば下記のように

　　　E　　　　　D
１　商品名 最大値
２　いちご
３　オレンジ
４　バナナ

という表を作り、セルＤ２に

{=MAX(IF(商品名の列範囲=E2,数量の列範囲,
""))}

という配列数式を作成すれば、後は下にコピーするだけでオレンジ・バナナの最大値も求められる配列数式がコピーされます。この場合、商品名と数量の範囲指定は絶対参照にしておいてください。

参考になれば幸いです。また見当違いの回答であればお許しください。