【Excel】関数でできますか？（訂正版）

質問

Excel2003を使用しています。
　　E列→摘要欄　H列→借方金額　I列→貸方金額　K列→差引残高
　1行　　あ　　　　　　　　　　　　　 　　　　　100
　2行　　い　　　　　　100
　3行　　う　　　　　　 500
　4行
　5行　月　計　　　　 600　　　　 　 　　　　100　　　　　　　 500
　6行
　7行
　8行　　か　　　　　　　　　　　　　　 　　 　 300
　9行　　き　　　　　　 400
10行　　 く　　　　　　 500
11行
12行　月　計　　　　　900　　　　 　 　 　　 300　　　　 　 　1100
13行　累　計　　　　1500　　　　 　 　 　　 400
　　　　 　：
　　　　 　：
というふうに金額が入力されているとき、K列の差引残高に
E列に“月　計”と入力されたら、
[直前の月　計の差引残高セル]＋[借方金額]－[貸方金額]という
数式を入れたいのですが、どんな関数を使えばできますか？
上記の例でいくと、
K12セルに[=K5+H12-I12]の計算結果が表示されるように
したいのですが。。。
よろしくお願いします。

回答No.7

moon_piyo

No6です
ありゃりゃだめでしたか。

えーと
FALSEがでたときのK列にはいってたセルの式はまだおもちでしょうか
もしまだ残っているのであれば補足欄に貼り付けていただけると
今後の参考になるのですが...

補足

申し訳ありません。
他の方法でも試してみるために、消去してしまいました。。。
先程、お礼を送る際に数式も貼付しておけばよかったですね。
教えていただいた数式でエラーが出た際、一部変更してみたりもしたのですが
うまくいきませんでした。

回答No.6

moon_piyo

こんにちは

K1: =IF(E1="月 計",IF(COUNTIF(E$1:E1,"月 計")>1,OFFSET(K$1,LARGE(INDEX((E$1:E1="月 計")*ROW(E$1:E1),),2)-1,0),0)+H1-I1,"")

K1の内容を下方コピー

お礼

実際に試してみましたが、うまくいきませんでした。。。
直前の月計を拾えていないのと“月　計”と入力されていないセルは
“FALSE”が表示されました。
お手数かけて申し訳ありません。ありがとうございました。

回答No.5

siddhaartha

すみません。1つ訂正です。

×　K列は[=IF(E12="月　計",SUMIF(D$1:OFFSET(D12,-1,0),D12-1,K$1:OFFSET(K12,-1,0))+H12-I12,"")]

○　K列は[=IF(E12="月　計",SUMIF(C$1:OFFSET(C12,-1,0),C12-1,K$1:OFFSET(K12,-1,0))+H12-I12,"")]

月の列をCではなくDで書いてしまっていました。
これでもダメであれば月の部分が単純な数値ではなく
"9月"とか、書式が文字になっていたりしているはずです。

ちなみに、初めの回答の方法でもダメでしたか？

お礼

D12→C12には気づきましたので、修正して試しましたが
うまくいきませんでした。(月の部分は数値です)
ちなみにNo.3の方法でも試してみたところ、L列に何番目の月計かを
持たせるところまではうまくいきましたが、それから先がダメでした。
何度もお手数かけてすみませんでした。ありがとうございました。

回答No.4

siddhaartha

C列に月があるのであれば、私の前回の回答での「L列に何番目の月計かを持たせる」が不要に出来るかもです。

月の書式が分からないので憶測ですが、仮にC1～5、C6～12にそれぞれ"9","10"と入っているならば
K列は[=IF(E12="月　計",SUMIF(D$1:OFFSET(D12,-1,0),D12-1,K$1:OFFSET(K12,-1,0))+H12-I12,"")]
ようするに、9月の行である1～5行目のK列には月計以外の行はK列が空白だから1～5行目のK列をSUMした値が前月の残高であるという考えです。

と、ここまで書いておいて気づいたのですが、年が変わったら同じ月もありますよね～＾＾；
考えが浅かったです。やっぱり初めの回答の方がいいですね。

お礼

再度回答ありがとうございます。
年が変わったらファイルも新しくしますので、同じ月はありません。
No.4の方法で試してみたのですが、当月の差引残高のみの計算結果で
直前の月計を拾えず、うまくいきませんでした…(>_<)
マクロの使用も考えて、また質問を出してみようと思います。
お世話になりました。

回答No.3

siddhaartha

「"直前の"月計の残高」を取得するのが難しいですねぇ・・・。
A,B,C列かどこかに月を持つセルがあれば足がかりになるのですが。

これまたマクロでなく関数で実現しようと思ったら、私なら（姑息な手段ですが）こうします。

まず空いている列（例ではL列）に何番目の月計かを持たせます。
L列 [=IF(E12="月　計",MAX(L$1:OFFSET(L12,-1,0))+1,"")]⇒全行にコピー
これでL5とL12にそれぞれ1,2が入りますよね。

よって、直前の月計は当月の月計行の（L列-1）の番号が入っている行になります。
K列 [=IF(E12="月　計",SUMIF(L$1:OFFSET(L12,-1,0),L12-1,K$1:OFFSET(K12,-1,0))+H12-I12,"")]

１列増えちゃいますけど、非表示行か何かにしておけば気にならないと思います。

マクロでするなら、コードはあえて書きませんが考え方としては
セルのチェンジイベントか何かのタイミングでチェンジ行から上へ検索（ループ）して
セルの値が"月　計"なら当該行のK列の数値を持ってきて、計算ってな感じです。

補足

>A,B,C列かどこかに月を持つセルがあれば足がかりになるのですが。
とは、月を入力しているセルがあれば…ということですか？
実はというのも変ですが、C列に「月」、D列に「日」を入力しています。
日付まで必要ないかと思い、書かなかったのですが。。。
この「月」を使って良い方法があるようでしたら、教えてください。

回答No.2

noname#37676

こんにちは。
isse3さんの回答の場合、12行目ではＯＫですが、月を重ねるごとに
Ｋ列の「月　計」の値にズレが生じます。ご確認ください。

「数式」と「条件付書式」を組み合わせて、こんな方法はいかがでしょうか？
例）［K12］・・・以下をＫ列の範囲（列全体でもＯＫ！）に
●数式=IF(OR(H12="",I12=""),K11,K11+H12-I12)
●条件付書式（[書式]メニューから[条件付書式]を開く）
「数式が」「=AND(E12<>"累　計",E12<>"月　計")」→「書式」の「フォント　色」を「白」→「ＯＫ」

「月　計」以外の行もＫ列に数値が入りますが、条件付書式を使用して「月　計」以外は表示しないように（白色フォント）設定しています。
ちなみに、［累　計］行の数式は「=SUMIF($E:$E,"月　計",K:K)」

いかがでしょうか？

お礼

回答ありがとうございました。
教えていただいたとおりにしてみたのですが、やり方が悪いのか
うまくいきませんでした。
やはり、関数では無理があるのでしょうか…

回答No.1

isse3

K12の計算式
=IF(E12="月計",SUM(K$1:OFFSET(K12,-1,0))+H12-I12,"")
でどうでしょう？
K12の入力が完了したなら、ドラッグコピーでK1までコピーしたら大丈夫なハズですよ。

補足

回答ありがとうございます。
教えていただいた方法を試してみたところ、E列に“月　計”と
入力されている行のすべてのKセルの数値が加算されました。
E列に“月　計”と入力されたら、
[直前の月　計のKセルの数値]＋[借方金額]－[貸方金額]を求める
数式をK列に入れたいのです。
例では月　計は２個まで書いてそれ以降は・・・・としか書いていませんが
実際は１個のときもあれば１０個のときもありえます。
なので、もしE20セルに“月　計”と入力されたなら
K20セルには、[=K12+H20-I20]の計算結果が表示されるように
したかったのです。
説明不足のようで申し訳ありません。