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

質問

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

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

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

回答No.4

MackyNo1 · ベストアンサー

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))

お礼

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

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

回答No.3

tom04

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

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

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

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

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

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

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

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

回答No.2

imogasi

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

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

Ｅ２に「Ａさん」の記載から集計すると
Ａさんの過去最大：
=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以前を使っているならデータメニューのフィルタの「フィルタオプションの設定」で重複を除いて抽出します。間違っても関数でリストを取りだそうなんてアプローチはするものじゃないので，気をつけてください。

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