ＥＸＣＥＬの重複データから最大と合計を抽出する方法

現在ＥＸＣＥＬで顧客の受注データの管理をしています。
リストは購入日毎の入力になっている為、同じ顧客が複数回注文した場合、データが複数存在します（例　Ａさんが1/3、2/4、3/5に注文した場合、Ａさんの名前で3つのデータが存在する）。

このデータの中から、Ａさんのように購入データが複数存在する人を抽出した上で
(1)抽出した人それぞれの注文データの最大購入額
(2)抽出した人それぞれの注文データの合計購入額
の両方が分かる数式を探しています・・・

そんな便利な数式があるかどうかも分かりませんが、どなたか詳しい方がいらっしゃったら
教えて頂きたいと思います。
よろしくお願い致します。

No.4 ベストアンサー 回答者： MackyNo1 回答日時： 2011/12/28 21:52

B列（B2セル以下）に名前が入力されていて複数回出現する人だけの一覧を作成するなら以下のような関数で表示できます。

=INDEX(B:B,SMALL(INDEX(((COUNTIF($B$2:$B$100,$B$2:$B$100)=1)+(MATCH($B$2:$B$100&"",$B$2:$B$100&"",0)<>ROW($B$2:$B$100)-1))*1000+ROW($B$2:$B$100),),ROW(A1)))&""

C列に金額が入力されているなら、その最大値は以下の式になります（F２セル以下に上記の関数を入力した場合）。
=IF(F2="","",MAX(INDEX(($B$2:$B$100=F2)*$C$2:$C$100,)))

合計は以下の式です。
=IF(F2="","",SUMIF($B$2:$B$100,F2,$C$2:$C$100))

この回答へのお礼

早々にご回答頂きまして、ありがとうございました！
挑戦してみます！

他の方も、ご回答頂きありがとうございました！！

お礼日時：2011/12/29 19:20

No.3 回答者： tom04 回答日時： 2011/12/28 21:29

こんばんは！

すでに回答は出ていますが・・・
他の方法の一例です。

オートフィルタを兼用するとSUBTOTAL関数で対応できると思います。

↓の画像はB列の「Aさん」でオートフィルタをかけた後の結果です。

E2セルに
=SUBTOTAL(4,C:C)

F2セルに
=SUBTOTAL(9,C:C)

という数式を入れています。

※　結果表示させたいセルはオートフィルタをかけても非表示にならない行にしておきます。

ご希望の方法でなかったらごめんなさいね。m(_ _)m

No.2 回答者： imogasi 回答日時： 2011/12/28 20:29

（１）ある条件をつけて、それに該当するデータ行の他列データの最大は配列数式でも、使わないと出来ない。

例　=MAX(IF(A2:A13="a",B2:B13,0))　といれてＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲを同時押しする
（２）作業列に条件に合うデータのみ関数で表示して最大をとれば配列数式にしなくても良いが、複数条件（複数顧客など）
の場合は無力
（３）ユーザー関数をＶＢＡで組むと出来るが、質問者のお呼びで無いだろう。
セルに=MAXIF($A$2:$A$14,"b",$B$2:$B$14)のようなのを入れる。ｂは顧客氏名など。
標準モジュールに（ＭＡＸＩＦは小生が勝手につけた名前）
最大値を求める基本的なロジックである。
Function MAXIF(x, y, z)
i = 1
mx = 0
Dim cl As Range
For Each cl In x
If cl = y And z.Cells(i) > mx Then
mx = z.Cells(i)
Else
End If
i = i + 1
Next
MAXIF = mx
End Function
とりあえず、Ｂ列データは正の数とする。
－－－
実際は存在しないが、Ｇｏｏｇｌｅででも「エクセル　ＭＡＸＩＦ」で照会すると結構沢山の記事がある。ニーズが強い証拠だろう。
読んで見たら。

No.1 回答者： keithin 回答日時： 2011/12/28 19:26

Ａ列に日付

Ｂ列に顧客名
Ｃ列に数量
があるとして

Ｅ２に「Ａさん」の記載から集計すると
Ａさんの過去最大：
=MAX(IF($B$2:$B$100=E2,$C$2:$C$100))　
をコントロールキーとシフトキーを押しながらEnterで入力する
Ａさんの合計：
=SUMIF(B:B,E2,C:C)
こちらはふつーに入力する
と出ます。



＃参考
Ｅ列に「複数履歴の顧客」を用意する簡単な方法：
D2に
=IF(COUNTIF($B$2:B2,B2)=2,B2,"")
を記入してリストの下端までコピーし，名前が出てきたのを並べ替えて集めてＥ列にコピーして貼り付けます

ただし，前述した数式は購入履歴が一度きりのお客さんについても，別に支障なく計算できます。


＃参考
複数回とか関係ない全顧客のリストを準備するには，ご利用のエクセルのバージョンが不明ですが，Excel2007以降を使っているならデータタブの「重複の削除」，Excel2003以前を使っているならデータメニューのフィルタの「フィルタオプションの設定」で重複を除いて抽出します。間違っても関数でリストを取りだそうなんてアプローチはするものじゃないので，気をつけてください。


＃参考
ピボットテーブルレポートを使うことで，今回ご質問の内容はムズカシイ数式は一切使わず，顧客リストの抽出も全部お任せで，ついでに年ごとに区切った集計とかの複雑な組合せでもエクセルに完全オマカセで集計させることも出来ます。
今回は初心者さんらしく「数式でどーしてもヤリタイ」ようなので特に説明はしませんが，勉強してみるとエクセルを使える幅が広がってイイです。