難しいExcelの計算式ですが…

質問

例

顧客番号　　契約日　　商品金額　　状況
1001　　　　　10/1　　　10,000円　　契約
1002　　　　　10/2　　　20,000円　　契約
1003　　　　　10/3　　　10,000円　　契約
1002　　　　　10/3　　　-20,000円　　キャンセル
1004　　　　　10/4　　　20,000円　　契約
1002　　　　　10/6　　　20,000円　　契約

上記のようなデータベースがあるとして、
別シートに

顧客番号　　契約日　　商品金額
（手入力）　　（自動）　（自動）

を作りたいのです。

商品金額の自動は、関数ＤＳＵＭを使い、なんとか出来ましたが、契約日が出来ないのです。

顧客番号の最新の契約日を出したいだけなのですが。。

条件　ピボットテーブルを使わない。

わかる方がいましたらお願いします。。

回答No.1

WWolf · ベストアンサー

契約日が確実に日付順であればＶＢＡでもかんたんですが、関数でＤＭＡＸというのがあります。
例えば
Ａ列　　　Ｂ列
No.　　　 日付
1　　　　10/01
5　　　　08/03
4　　　　02/04
3　　　　06/05
4　　　　11/12
5　　　　07/19
5　　　　12/03
1　　　　03/15
3　　　　05/04
とある場合、仮にＣ２に条件、Ｄ２に結果を表示したい場合・・・Ｃ１にNo.、Ｄ２に=DMAX(A1:B10,2,C1:C2)
とすると条件（Ｃ１）に5であれば12/3、条件を1にすれば10/1とでます。

お礼

すばやい返答ありがとうございます。
ＤＭＡＸで簡単に出来ました。
ありがとうございました。

回答No.4

KenKen_SP

配列数式を使ってみては？

仮に、

データシート名：[DATA]
A列：顧客番号　B列：契約日 … で2行目から10行目までデータがあるとします。

別シート：A1:顧客番号　B1:契約日　

とした場合、
B1に次の式を書込み、[Ctrl] + [Shift] + [Enter] で計算式を確定させます。

＝MAX(IF(DATA!A2:A10=A1,DATA!B2:B10,0))

計算式が {=MAX(IF(DATA!A2:A10=A1,DATA!B2:B10,0))} となればOKです。

お礼

すばやい返答ありがとうございます。
簡単に出来ることができました。
色々な方法がありますね。
参考になります。ありがとうございました。

回答No.3

imogasi

全然難しくない問題では無いでしょうか。
誤解しているかも知れないが。
（例データ）Sheet1のA1:D7
顧客番号 契約日 商品金額 状況
1001 1月10日 10,000円 契約
1002 2月10日 20,000円 契約
1003 3月10日 10,000円 契約
1002 3月10日 -20,000円 キャンセル
1004 4月10日 20,000円 契約
1002 6月10日 20,000円 契約
（関数式：Sheet2）Sheet２のA1:D1に
顧客番号 契約日 商品金額 状況
B列の書式を日付にしておく。
B2に=IF(A2="","",VLOOKUP($A2,Sheet1!$A$2:$D$7,COLUMN(B2),FALSE))といれる。
C2、D2に式を複写する。
B2:D2の式を、D2で＋ハンドルを出して、下に引っ張って複写する。
（B2:D2だけで良いなら、下の行への複写は必要なし）
COLUMN()を使わず、B2を=IF(A2="","",VLOOKUP($A2,Sheet1!$A$2:$D$7,2,FALSE))
C2を2を3に、D2は2を4にしても同じ。

お礼

すばやい返答ありがとうございます。
簡単に出来ることができました。
色々な方法がありますね。
参考になります。ありがとうございました。

回答No.2

jindon

一行目がタイトルで
A2から顧客番号
B2から契約日
C2から商品金額
D2から状況

として、

E1に顧客番号（表題） E2に検索顧客番号を入力
F1に契約日（表題）
F2に
=IF(E2="","",MAX(IF((A2:A7=E2)*(D2:D7<>"キャンセル"),B2:B7)))
として、Ctrl + Shift + Enter で確定（配列式）
{式}の形になりますので確認してください。
G1に商品金額（表題）
G2に
=IF(E2="","",SUMPRODUCT((A2:A7=E2)*(B2:B7=F2),C2:C7))

E2に顧客番号を入力すると
A列が入力された顧客番号で、D列がキャンセルではない
F2に契約日の最大値（最新日）
A列が入力された顧客番号で、B列がF2の日付の
G2に商品金額
を返します

注意：同じ顧客番号で、最新日が同日のレコードがある場合は、先のものが返ります

お礼

すばやい返答ありがとうございます。
簡単に出来ることができました。
色々な方法がありますね。
参考になります。ありがとうございました。