EXCEL 月末の値の取得

質問

家計簿をEXCELにて作成しているのですが、１年の月末時点での残金が知りたく、色々な関数を試しましたがどうもうまく抽出できません。

下の様に１つの口座の入出金を記録しているのですが、ここから、
◆１月　\1,077,256
◆２月　\856,059
　・　　　・
　・　　　・
と１２月までの各月末のデータを抽出しようとした場合、どの様にすればよろしいのでしょうか？

どなたかご存じの方、教えて下さい。

　　A　　　　　　B　　　　　　C
2009/1/5　　　項目名　　\897,389
2009/1/10　　項目名　　\887,389
2009/1/23　　項目名　　\1,145,746
2009/1/26　　項目名　　\1,077,256
2009/2/2　　　項目名　　\957,652
2009/2/12　　　項目名　　\887,389
2009/2/25　　　項目名　　\833,652
2009/2/27　　　項目名　　\856,059
2009/3/12　　　項目名　　\651,079
　　・　　　　　　・　　　　　・
　　・　　　　　　・　　　　　・
　　・　　　　　　・　　　　　・

回答No.5

noname#99913 · ベストアンサー

No.1です。次の式をどうぞ。

1月：=VLOOKUP(DATE(2009,1,31),A:D,4,TRUE)
2月：=VLOOKUP(DATE(2009,2,28),A:D,4,TRUE)
3月：=VLOOKUP(DATE(2009,3,31),A:D,4,TRUE)

お礼

自己解決しました。
日付の参照部分を参考に、

　=INDEX(D4:D1000,MATCH(DATE($J$1,L$1+1,0),A4:A1000))

で値を出す事ができました。
ヒントをいただき、ありがとうございます。

補足

また他にもカテゴリ別などで集計する為に、
　J1・・・年号のみ数値で入力済　/　例：【2009】
　L1～W1・・・月を数値で入力済　/　例：【L1/1 : M1/2 : N1/3】
をしておりますので、

　=VLOOKUP(DATE(YEAR(J1),MONTH(L1)+1,0),A:D,4,TRUE)
　※J1には2009、L1には1の値が入っています

という様に変更してみましたが、＃N/Aで値が帰ってきます。
何か数式間違っているのでしょうか？

回答No.6

hallo-2007

No4です。
J1・・・年号のみ数値で入力済　/　例：【2009】
　L1～W1・・・月を数値で入力済　/　例：【L1/1 : M1/2 : N1/3】
をしておりますので、
と数値で入れているのであれば
　=VLOOKUP(DATE(YEAR(J1),MONTH(L1)+1,0),A:D,4,TRUE)
でなくて
　=VLOOKUP(DATE(J1,L1+1,0),A:D,4,TRUE)
で十分でしょう。
右へコピィしてつかうのであれば
=VLOOKUP(DATE($J1,L1+1,0),$A:$D,4,TRUE)
としておきましょう。

補足

ありがとうございます。
コピーの部分、参照させていただきました。

皆様、色々ありがとうございます。

回答No.4

hallo-2007

◆１月　\1,077,256
◆２月　\856,059
のつきの部分に　2009/1/1　とか日付を入れて表示形式、ユーザー定義で
yy年mm月　として09年01月と表示させておきます。
後はVLOOKUP関数で
=VLOOKUP(DATE(YEAR(E2),MONTH(E2)+1,0),A2:C10,3)
としておけば月末に最も近い日付の値がでると思います。

補足

ありがとうございます。
試してみましたが、実際の構造が、増減を見る為に

　A列　：　シリアル値で日付
　B列　：　項目名
　C列　：　月の資金増減を見る為に、資金増減値
　D列　：　残高

タイトル、口座情報がヘッダに有る為に、実際の開始行は【A4】からです。

になっており、このままの式を適用すると、C列の値を引いてしまいます。
何とか現在、対策考えておりますが、何か良い方法ご存じでしたら教えて下さい。

回答No.3

KURUMITO

例えばお示しのデータがＡ，Ｂ,C列の1行目からあるとして、Ｆ1セルには2009年1月と入力し下方にオートフィルドラッグすれば2009年2月と続けて表示されますね。
Ｇ1セルには次の式を入力し下方にオートフィルドラッグすればよいでしょう。
=IF(F1="","",VLOOKUP(DATE(YEAR(F1),MONTH(F1)+1,0),A:C,3,TRUE))

補足

ありがとうございます。
試してみましたが、実際の構造が、増減を見る為に

　A列　：　シリアル値で日付
　B列　：　項目名
　C列　：　月の資金増減を見る為に、資金増減値
　D列　：　残高

タイトル、口座情報がヘッダに有る為に、実際の開始行は【A4】からです。

また他にもカテゴリ別などで集計する為に、
　J1・・・年号のみ数値で入力済　/　例：【2009】
　L1～W1・・・月を数値で入力済　/　例：【L1/1 : M1/2 : N1/3】
の様に横方向で年号と月を持っていて、これを変更する事で翌年度でも対応できる様にシートを作成してあります。

そこで教えていただきました関数をこれに当てはめて改変しましたが、
C列の値を引いてしまいます。何とか現在、対策考えておりますが、何か良い方法ご存じでしたら教えて下さい。

回答No.2

_Kyle

A列の値はシルアル値（日付を表す数値）で入ってるんですよね？

仮に、E1セル以下に、"１月","２月","３月",…と文字列形式で月数が入力されているならば、

　F1セル　：　=VLOOKUP(DATE(2009,SUBSTITUTE(E1,"月","")+1,0),A$1:C$999,3,TRUE)

として、下方にフィル。
------------------------------------
月数が1,2,3,…と数値で入力されているならば、単に
　F1セル　：　=VLOOKUP(DATE(2009,E1+1,0),A$1:C$999,3,TRUE)　
でOK。
------------------------------------
※DATE(2009,E1+1,0) でその月の最終日の日付が得られるので、
　VLOOKUPのTRUE型で、その日付を超えない最大の日付を探します。
※31とか28とか月の日数にあわせて数式を変える必要はありません。

ご参考まで。

補足

ありがとうございます。
試してみましたが、実際の構造が、増減を見る為に

　A列　：　シリアル値で日付
　B列　：　項目名
　C列　：　月の資金増減を見る為に、資金増減値
　D列　：　残高

タイトル、口座情報がヘッダに有る為に、実際の開始行は【A4】からです。

また他にもカテゴリ別などで集計する為に、
　J1・・・年号のみ数値で入力済　/　例：【2009】
　L1～W1・・・月を数値で入力済　/　例：【L1/1 : M1/2 : N1/3】
の様に横方向で年号と月を持っていて、これを変更する事で翌年度でも対応できる様にシートを作成してあります。

そこで教えていただきました関数をこれに当てはめて改変しましたが、
C列の値を引いてしまいます。何とか現在、対策考えておりますが、何か良い方法ご存じでしたら教えて下さい。

回答No.1

noname#99913

次の式を試してください。

1月：=VLOOKUP(DATE(2009,1,31),A:C,3,TRUE)
2月：=VLOOKUP(DATE(2009,2,28),A:C,3,TRUE)
3月：=VLOOKUP(DATE(2009,3,31),A:C,3,TRUE)

補足

ありがとうございます。

試してみましたが、実際の構造が、増減を見る為に

A列　：　シリアル値で日付
B列　：　項目名
C列　：　月の資金増減を見る為に、資金増減値
D列　：　残高

タイトル、口座情報がヘッダに有る為に、実際の開始行は【A4】からです。

になっており、このままの式を適用すると、C列の値を引いてしまいます。
何とか現在、対策考えておりますが、何か良い方法ご存じでしたら教えて下さい。