エクセル 最新日付以外のデータを合計する

質問

下表があるとしまして、
氏名 日付 買上金額
鈴木 5月5日 10
鈴木 5月6日 60
鈴木 5月7日 100
鈴木 5月9日 400
田中 5月4日 100
田中 5月7日 200
山田 5月1日 600
山田 5月2日 200
山田 5月3日 300
1970
氏名毎に、最新日付以外の合計を計算したいのです。
この場合、鈴木さんが170　田中さんが100　山田さんが800の合計1070がそれぞれの氏名の最新以外データの合計となりますが、これらを自動的に計算できないでしょうか？
よろしくおねがいします。

回答No.3

happypoint · ベストアンサー

こんにちは。

以下の方法でできます。
大きく分けて2ステップです。
データはあらかじめ日付順でソートされているものとします。

１）作業列を作る

最新データの場合のみ買上金額にかかわらず０を返し、
それ以外の場合は買上金額そのままを返す式を、
別な列に入力します。

たとえば例で言えばA～C列に氏名、日付、買上金額ですので、
D列を作業列としましょう。
D1セルに「買上金額2」などと見出しをつけてください。

D2セルに次の式を入力します。
　=IF(A2=A3,C2,0)
そしてこの式をすべての行にコピーします。
すると、最新データの行のみ、0となり、
それ以外の行は、買上金額が表示されます。

２）集計機能で名寄せして集計する

下準備ができたらいよいよ集計です。
リスト内にカーソルを置いた状態で、

　データ-集計

で、集計の設定画面が開きます。
ここで、
　「グループの基準」　を　「氏名」
　「集計の方法」　を　「合計」
を指定します。

そして、
　「集計するフィールド」では、
　「買上金額2」のフィールドにのみ、
チェックがつくようにします。

そして、OKを押せば自動的に集計されます。

補足

完璧です。本当に助かりました。これでできます！！エクセルの天才はひらめきが違うと思いました。すごい！

回答No.2

coco1

データの数が日々変わるのか、等色々問題点があり一概にはいえませんが、色々な方法があります。とりあえず、手っ取り早い方法を一つだけ。
表のA1に「氏名」、B1に「金額」～C10に山田の「300」まで順次入力されているものとして説明します。
まず、D～E列を作業列として利用します。
D2～D5に「=IF(MAX($B$2:$B$5)=B2,1,0)」、D6～D7に「=IF(MAX($B$6:$B$7)=B6,1,0)」、D8～D10に「=IF(MAX($B$8:$B$10)=B8,1,0)」と入力します。
次に、E2～E10に「=IF(D2=0,C2,0)」と入力します。
すると、E列には、最終日付を除いた金額だけがピックアップされますから、メニューのデータ→集計で計算すればよいです。
列の増減がある場合は、counta関数やindirect関数を工夫することで対応できます。

補足

ありがとうございます。どうにか行指定が自動的にできれば最高なのですが・・・・・。ＲＯＷ関数をつかって２行目とか３行目とわかっても、それをつかって例えば、＝Ｂ２とか＝Ｂ３とか入力できないし。。。鈴木から鈴木の行指定ができればいいんですが・・・・・。

回答No.1

taknt

一人一人 計算するってことかな？

=SUM(C1:C4)-VLOOKUP(MAX(B1:B4),B1:C4,2,FALSE)

というふうに 合計から 最大値分を引けばいいでしょう。

補足

ありがとうございます。一人一人は計算しませんから、問題は、VLOOKUP(MAX(B1:B4),B1:C4,2,FALSE）における行指定をどうするかです。VLOOKUP関数を使ってやるというのは思いつきもしませんでした。行指定をどうすればいいのか、悩みますが、とても最高になりました。