Excel：残高の計算方法

質問

Excelの残高の計算方法について教えてください。

画像の左は「損益計算」シート、右は「月別結果」シートです。
「損益計算」シートは省略しています。
「損益計算」シートのEは終了日時の年、Fは終了日時の月、Gは終了日時の日、Hは終了日時の時刻、Qは残高です。
「月別結果」シートのAは年、Bは月、Mは収支、Nは残高です。
いずれのシートもデータは行番号「3」から始まりです。

「損益計算」シートの年（E）、月（F）の最終行と「月別結果」シートの年（A）、月（B）に一致する場合、「損益計算」シートの残高（Q）を「月別結果」シートの残高（N）に表示させたいのですが、残高が一致しません。

2011年1月の残高：「損益計算」シートの場合542,498、「月別結果」シートの場合549,807、
2011年2月の残高：「損益計算」シートの場合587,732、「月別結果」シートの場合652,229、
2011年3月の残高：「損益計算」シートの場合674,520、「月別結果」シートの場合747,684、
2011年4月の残高：「損益計算」シートの場合799,922、「月別結果」シートの場合843,504、
2011年5月の残高：「損益計算」シートの場合893,616、「月別結果」シートの場合932,951、
2011年6月の残高：「損益計算」シートの場合1,063,244、「月別結果」シートの場合1,143,798、
2011年7月の残高：「損益計算」シートの場合1,368,267、「月別結果」シートの場合1,633,258、
2011年8月の残高：「損益計算」シートの場合1,863,098、「月別結果」シートの場合2,169,823、
2011年9月の残高：「損益計算」シートの場合2,626,244、「月別結果」シートの場合3,038,066、
2011年10月の残高：「損益計算」シートの場合3,597,069、「月別結果」シートの場合4,206,500、
2011年11月の残高：「損益計算」シートの場合4,480,164、「月別結果」シートの場合5,310,789、
2011年12月の残高：「損益計算」シートの場合5,617,960、「月別結果」シートの場合5,617,960、
2012年1月の残高：「損益計算」シートの場合6,543,278、「月別結果」シートの場合549,807、
となってしまいます。

ちなみに「月別結果」シートのN3には、

=IF(OR(A3="",B3=""),"",IF(COUNTIFS(損益計算!$E$3:$E$1048576,A3,損益計算!$F$3:$F$1048576,B3)=0,"",INDEX(損益計算!$Q$3:$Q$1048576,MIN(COUNTIF(損益計算!$F$3:$F$1048576,"<=" & B3),COUNTIF(損益計算!$E$3:$E$1048576,"<=" & A3)))))

という関数式を入力しています。

どのような関数式を入力すれば、「損益計算」シートの年月の最終行の残高を「月別結果」シートの年月と一致する残高を表示させることができるでしょうか？

回答よろしくお願いします。

回答No.3

mt2008 · ベストアンサー

日付は年・月・日をバラバラにするよりちゃんと日付として扱った方が扱いやすいですよ。
見えにくいと思いますが、添付の図では損益計算シートのR列と月別結果のＯ列を作業列として使っています。
損益計算シートのR3：=DATE(E3,F3,G3)
月別結果のO3：=LOOKUP(DATE(A3,B3+1,0),損益計算!R:R)
月別結果のN3：=INDEX(損益計算!Q:Q,MATCH(O3,損益計算!R:R,0))

お礼

回答ありがとうございます。

>日付は年・月・日をバラバラにするよりちゃんと日付として扱った方が扱いやすいですよ。
なるほど、分かりました。

回答者様の通り式を入れてみたところ全て一致しました。
ありがとうございました。

回答No.5

mt2008

ANo.3です。

失礼しました。
その月の最終日に2回以上の入力があると正しい値になりませんので
以下の様に式を変えて下さい。

損益計算シートのR3：=DATE(E3,F3,G3)+H3
月別結果のO3：=LOOKUP(DATE(A3,B3+1,1)-"0:00:01",損益計算!R:R)

お礼

回答ありがとうございます。

>その月の最終日に2回以上の入力があると正しい値になりませんので
以下の様に式を変えて下さい。

なるほど。
同月の最終日に複数の入力があることもあります。

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

回答No.4

bunjii

>どのような関数式を入力すれば、「損益計算」シートの年月の最終行の残高を「月別結果」シートの年月と一致する残高を表示させることができるでしょうか？
提示の数式では目的に合う行番号を抽出できません。
次の数式を試してください。
=INDEX(損益計算!Q:Q,SUMPRODUCT(MAX((損益計算!E$3:E$3000=A3)*(損益計算!F$3:F$3000=B3)*ROW(A$3:A$3000))),1)
または
=INDEX(損益計算!Q:Q,MAX(INDEX((損益計算!E$3:E$3000=A3)*(損益計算!F$3:F$3000=B3)*ROW(A$3:A$3000),0)),1)
この数式は大量のデータを扱うと応答しなくなりますので注意してください。

お礼

回答ありがとうございます。

関数を入力してみました。
ばっちり全て数値が一致しました。
ありがとうございます＾＾

Excel2010なので、「E$3000」、「F$3000」、「A$3000」の数値を最終行の「1048576」にすれば問題ないですよね。

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

回答No.2

Nouble

p.s.
Excelの計算で
無理クリ一致させれるか
は、解りません
無理でしょう

が、
どうすれば
参照すべき対象を
間違いなく特定できるか

に、付いては解ります

その前に、蛇足説明です。
Excelでは時間は連続した値として扱われます。

人は
2月32日
とか、言うと、変ですが
Excelは、此を受け付けてくれ
2月末の４日後
と認識してくれます。

12月32日も同じで
翌年元日と認識してくれます

また、同様に

3月1日から1引くと
2月末日になります。

と、いう事で　戻りますが、
「月別結果」シート中の、とある行、Nの
其の月末日は
=DATE(AN,BN+1,1)-1
（※注:Nは任意の行ナンバー、つまり数字です）
で、表せます。

次に、
「損益計算」シート中の、とある行、Mの
日付は
=DATE(EM,FM,GM)+TIMEVALUE(HM)
で、求まります。
（※注:Mは任意の行ナンバー、つまり数字です）
ですので、

「損益計算」シートの中から
DATE(A(N-1)(B(N-1)+1,1)＜=DATE(EM,FM,GM)+TIMEVALUE(HM)＜=DATE(AN,BN+1,1)-1
（※注:(N-1)はNの一行上の行ナンバーです）
此にあてはまる中で
最大の日付が記載された行です

最大を求めるのは
MAX、叉はLARGEです

但し、この時
各々の初めの引数は
MAX(INDEX(………,,))
LARGE(INDEX(………,,),1)
と、いう風にINDEXで、挟んでください
で、ないと
Excelが素直には計算しません。

此処迄で、何日の何時のものを
探せばいいか
が、解りました。

後は、
此の値が何行目に書かれているか探し、
OFFSET等で参照すればいい。
と、言う事です。

お礼

回答ありがとうございます。

参考になりました。
ありがとうございました。

回答No.1

Nouble

端的に二つ言わせてください

countifsを会計で使うのは止めましょう
極一部しか、恐らく知りませんが信頼できない関数です。

各々の帳票の値が違える
との事ですが、

各々の帳簿各値の
数値の根拠たる
計算元の帳簿との照合は
済んでます？

もし照合して合っているなら
此処で小手先の計算で
合わそうとしたら
計算元の帳簿と
合わなくなりますよ？

合わないのは
小口現金出納を加味し忘れ
とかでは？

でも
どうしても合わないなら
雑損益で計上するしかないのでは？

小手先で合わせようとすると
膨大にあるだろう他帳票にまで
るいが及び、
作業量がとんでもなく
膨大な量になりますよ？

しかも、会計なら
偽りの申告は
罪に問われませんか？

貴方、捕まりませんか？

お止めになられた方が無難ですよ

尚、
何故countifsの信頼性が低いか
に、ついては
別途補足欄ででも
お申し付け頂ければ、
お答えします。

もう一度言います
各々の計算根拠たる
元帳と照らして合うなら
Excelの範疇じゃないですよ

小手先の計算で何とかしたら
駄目な範囲ですよ

下手したら捕まりますよ？

お礼

回答ありがとうございます。

>countifsを会計で使うのは止めましょう
極一部しか、恐らく知りませんが信頼できない関数です。

分かりました。使わないようにします。
ありがとうございました。