月末の残高を入れたいのですが式を教えてください。

質問

銀行名・日付・残高が入ってるシートがあるのですが
'5月の待つ時点の合計残高を求める方法を教えてください。

例えば、
三菱東京ＵＦＪ銀行　2013/05/20　30,000
りそな銀行　2013/05/22　100,000
三菱東京ＵＦＪ銀行　2013/05/25　40,000
みずほ銀行　2013/05/26　10,000
みずほ銀行　2013/05/31　15,000

と入ってるのですが、
三菱東京ＵＦＪ銀行の5月末の残高は40,000
りそな銀行の5月末の残高は100,000
みずほ銀行の5月末の残高は15,000
なので、155,000円になると思うのですが
それを求める式はどう作ればいいのでしょうか？

「5/31」の各銀行の残高を探す式を作ろうと思いましたが
日付が必ず5/31ではないから、まずそこで躓いてしまいました。

できれば関数で一つのセルに5月末の残高を入れたいのですが式を教えてください。
今回は5月ですが6月にも応用できる方法も教えていただければ助かります。

回答No.1

tom04 · ベストアンサー

こんばんは！
一発で！とはいきませんが・・・
各銀行毎の最終残高を求めてそれを合計する方法が一番手っ取り早いと思います。

↓の画像でE列の銀行名はあらかじめ入力済みだとして、D2セルに
=INDEX($C$1:$C$1000,MATCH(MAX(IF(A$1:A$1000=E2,B$1:B$1000)),B$1:B$1000,0))

これは配列数式になってしまいますので、Shift+Ctrl+Enterで確定！
この画面から数式をコピー＆ペーストする場合は　数式をドラッグ　→　右クリック　→　コピー
→　D2セルを選択　→　数式バー内で一度クリック　→　右クリック　→　貼り付け　
→　そのまま（編集可能のまま）Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
これを下へオートフィルでコピー！

最後に合計欄は単純に
SUM関数で合計しています。

※　前提条件として5月分のデータだけしかない！としています。
（6月分のデータがある場合は最新のデータが表示されてしまいます。）

他に良い方法があればごめんなさいね。m(_ _)m

お礼

す、すげー
この短時間で作れるなんてすごすぎます！
ありがとうございました！

回答No.4

KURUMITO

回答Ｎｏ３です。単に月末の残高をＤ列に表示させるのでしたらＤ２セルに次の式を入力して下方にドラッグコピーすればよいでしょう。

=IF(COUNTIF(A2:INDEX(A:A,MATCH(DATE(YEAR(B2),MONTH(B2)+1,0),B:B,1)),A2)=1,C2,"")

お礼

ありがとうございました。

回答No.3

KURUMITO

取引銀行の数が変わってもその名前を自動的表示させ、しかも複数の月にわたってもその月末での残高を自動的に表示させることができます。
お示しのデータがシート１に有り、Ａ１セルには取引銀行名、Ｂ１セルには日付、Ｃ１セルには残高とそれぞれ項目名が有り、２行目から下方にデータが入力されているとします。
Ｄ列には作業列を設け、Ｄ２セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)<=COUNTA(A:A)-1,IF(COUNTIF(A$2:A2,A2)=1,ROUNDDOWN(MAX(D$1:D1),-7)+10000000,IF(COUNTIF(A$2:A2,A2)>1,ROUNDDOWN(INDEX(D$1:D1,MATCH(A2,A$1:A1,0)),-7),0))+DATE(YEAR(B2),MONTH(B2),1)*100+COUNTIF(A2:INDEX(A:A,MATCH(DATE(YEAR(B2),MONTH(B2)+1,0),B:B,1)),A2),IF(ROW(A1)<=COUNTA(A:A)-1+INT(MAX(D$1:D1)/10000000),(ROW(A1)-(COUNTA(A:A)-1))*10000000,""))

Ａ列での銀行目が入力されるよりもさらに下の行までドラッグしてください。
シート２に各月末での残高を銀行名とともに自動的に表示させます。
Ａ１セルには銀行名とでも項目名を入力したのちにＡ２セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(A$1:A1,INDEX(Sheet1!$A:$A,MATCH(IF(ISNUMBER(SMALL(Sheet1!D:D,RANK((ROW(A1)+1)*10000000,Sheet1!D:D,1)-1)),SMALL(Sheet1!D:D,RANK((ROW(A1)+1)*10000000,Sheet1!D:D,1)-1),IF(COUNTIF(A$1:A1,MAX(Sheet1!D:D))>0,"",MAX(Sheet1!D:D))),Sheet1!$D:$D,0)))>0,"",INDEX(Sheet1!$A:$A,MATCH(IF(ISNUMBER(SMALL(Sheet1!D:D,RANK((ROW(A1)+1)*10000000,Sheet1!D:D,1)-1)),SMALL(Sheet1!D:D,RANK((ROW(A1)+1)*10000000,Sheet1!D:D,1)-1),IF(COUNTIF(A$1:A1,MAX(Sheet1!D:D))>0,"",MAX(Sheet1!D:D))),Sheet1!$D:$D,0)))

B1セルには今年の５月からでしたら2013年５月と入力します。数式バー上では2013/5/1の表示になっていることが必要です。Ｂ１セルのデータを右横方向にドラッグすることで２０１３年６月・・・のように変わりますね。
そこでＢ２セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ISERROR(INDEX(Sheet1!$C:$C,MATCH(ROW(A1)*10000000+B$1*100+1,Sheet1!$D:$D,0))),"",INDEX(Sheet1!$C:$C,MATCH(ROW(A1)*10000000+B$1*100+1,Sheet1!$D:$D,0)))

これで銀行名も自動的に表示されますのでシート１でのデータが漏れなく表示されることになります。

お礼

ありがとうございました。

回答No.2

MarcoRossiItaly

E1　5
E3　三菱東京UFJ銀行
F3　=index(c:c,max(index((A$3:A$1000=e3)*(month(B$3:B$1000)=E$1)*row(A$3:A$1000),)))
F6　=sum(f3:f5)

同じ月でいちばん下の行を読み取ります。他の月の行が続いていても問題ありません。「2013 年 5 月末日」などとしたい場合は、「*(year(B$3:B$1000)=2013)」などを上式に追記してください。数式の確定に Ctrl+Shift+Enter の操作は不要です。

お礼

ありがとうございました。