【Excel】この条件で集計を3つに分けたい…??(謎

質問

今、お小遣い帳のようなのを作っているのですが…
以下の条件で集計したい場合、Ｄ～Ｆ列に入れる式は、
どのようにすれば…　というか、式で可能ですか？(謎
分からなくて困ってます。よろしくお願いいたします☆

・１行目は項目名です。
・２行目には前の月の繰越金を入力します。
・Ａ列で、文末に●があるものの集計はＥ列に出す。
・Ａ列で、文末に■があるものの集計はＦ列に出す。
・Ａ列で、●も■も無い項目の集計はＤ列に出す。

図にすると↓こんな感じです。※変になるかも；(>_<)

┌─┬──┬──┬──┬──┬──┬──┬
│　 │　Ａ　│　Ｂ │　Ｃ　│　Ｄ　│　Ｅ　│　Ｆ　│
├─╋━━┿━━┿━━┿━━┿━━┿━━┿
│１ ┃摘要│収入│支出│合計│●計│■計│
├─╂──┼──┼──┼──┼──┼──┼
│２ ┃繰越│　　　│　　　│1000│1000│1000│
├─╂──┼──┼──┼──┼──┼──┼
│３ ┃…●│1000│　　　│　　　│2000│　　　│
├─╂──┼──┼──┼──┼──┼──┼
│４ ┃……│ 500│　　　│1500│　　　│　　　│
├─╂──┼──┼──┼──┼──┼──┼
│５ ┃…■│　　　│ 500│　　　│　　　│ 500│
├─╂──┼──┼──┼──┼──┼──┼

ちなみに、●と■には、それぞれ違う銀行名を入れる予定です。
（ つまり、お小遣いと同じシートで預金も管理したいかなーって。）
お願いします☆

回答No.2

shiotan99 · ベストアンサー

No.1です。

> Ａ列で、文末に●があるものの集計はＥ列に出す。
> Ａ列で、文末に■があるものの集計はＦ列に出す。

＜文末に＞という条件なら、No.1の数式にある "*●*" と "*■*"　は、"*●"、"*■" に変えてください。● ■ の前だけに 半角の * （ アスタリスク ）をつけます。（ ● ■ は銀行名に置き換えてください ）　

"*●*" のように前後にアスタリスクをつけると、文末に限らず＜●を含む＞という条件になります。

要は↓な感じになります。
E3に
=IF(COUNTIF($A3,"*●"),E$2+SUMIF($A$3:$A3,"*●",$B$3:$B3)-SUMIF($A$3:$A3,"*●",$C$3:$C3),"")

F3に
=IF(COUNTIF($A3,"*■"),F$2+SUMIF($A$3:$A3,"*■",$B$3:$B3)-SUMIF($A$3:$A3,"*■",$C$3:$C3),"")

それと、D3に入れる式を忘れてました。
D3に

=IF(A3="","",IF(SUM(COUNTIF($A3,{"*●","*■"})),"",$D$2+SUM($B$3:B3)-SUM(SUMIF($A$3:A3,{"*●","*■"},$B$3:B3))-SUM($C$3:C3)+SUM(SUMIF($A$3:A3,{"*●","*■"},$C$3:C3))))

と入れて、気が済むまで下にフィルコピーしてください。
※これもアスタリスクのつけ方に注意してください。

お礼

しおたん99さん、ありがとうございます☆
おかげ様で思い通りに出来ましたぁ～～!!!!
これで銀行から引き出したり預けたりが分かるし、
残高も一目瞭然だし、とても便利になりました♪
もっと早くお礼したかったのですが（ こんなにも
分かり易く説明してくださっているにも係わらず！）
微妙に てこずってて遅くなっちゃいました^^;
理解できれば一気に「あー」ってなるんですけどね。
「あー」ってなるまでが大変ですよね（ 意味不明 ）
こんなに長い式になるとはビックリでした。
しおたん99さんは凄いです。
本当に、どうもありがとうございました☆
また機会があったら、お願いしますm(_ _)m

回答No.3

shiotan99

すみません、№1 & №2です。

ごていねいなお礼をいただいて恐縮です。
実は質問が締め切られてからあらためて考えてみると、№2のような長ったらしい式を使わなくてももっとカンタンな方法があることに気づいてしまいました（ ←いつものことですが‥ (-_-;)
一応お伝えしておこうと思い、事務局に回答の追加をお願いした次第でございます。
お手数をおかけしますが、以下の方法を試してみてください。

【 必須条件 】
・D2、E2、F2の繰越欄に必ず金額が入力されていること。
（ たとえ預金の繰越残高が 0円であっても、0 と入力してくださいっ！ヾ(^^;)ぉぃ ）

★ D3に
=IF(OR(A3="",SUM(COUNTIF(A3,{"*●","*■"}))),"",LOOKUP(MAX($D$2:D2)+1,$D$2:D2)+B3-C3)

または
=IF(OR(A3="",SUM(COUNTIF(A3,{"*●","*■"}))),"",LOOKUP(10^9,$D$2:D2)+B3-C3)

と入力（ どちらでもかまいません。以下同じ ）

★ E3に
=IF(COUNTIF($A3,"*●"),LOOKUP(MAX(E$2:E2)+1,E$2:E2)+$B3-$C3,"")

または
=IF(COUNTIF($A3,"*●"),LOOKUP(10^9,E$2:E2)+$B3-$C3,"")

と入れたあと、↑の式を 右の F3にフィルコピーして ●の銀行名を■の銀行名に変更。

★ F3の式が↓のようになっていれば OKです。
=IF(COUNTIF($A3,"*■"),LOOKUP(MAX(F$2:F2)+1,F$2:F2)+$B3-$C3,"")

または
=IF(COUNTIF($A3,"*■"),LOOKUP(10^9,F$2:F2)+$B3-$C3,"")

D3、E3、F3の式を必要なだけ下にフィルコピー
以上で №2の式と同じ結果になると思います。
*
*
かんたんに説明しておきますと‥
=LOOKUP(検査値,範囲) という式は、範囲内の＜どの数値よりも大きな値＞を検査値として指定してやると、その範囲内のいちばん最後の数値を返します。（←たぶんヘルプを見てもこんなことは書かれていないと思いますが‥）

たとえば
=LOOKUP(MAX(F1:F10)+1,F1:F10)

または
=LOOKUP(10^9,F1:F10)

とすると、F1：F10 の範囲で、F3、F5、F7 だけに数値が入っていて他は空白だとすれば、F7に入っている数値が返る、ということです。

MAX(F1:F10) で、F1：F10 の中のいちばん大きな数値が求められるので、それに1を足してやれば、F1：F10の範囲内の＜どの数値よりも大きな値＞を指定したことになります。
もうひとつの 10^9 は 10の9乗、つまり10億ということです。範囲内に10億以上の数値がなければ、これで範囲内の＜どの数値よりも大きな値＞を指定したことになります。
預金残高が10億円以上ある場合は、もっと増やして 10^13 とかにしてください。
*
以下は余計かもしれませんが‥
たとえば J2セルに
=IF(COUNT(D:D),LOOKUP(10^9,D:D),"")

と入れると、D列全体の中でいちばん最後の（ いちばん下の ）数値が返ります。
これを右の K2、L2にコピーしてやれば、J2 に D列の最新残高、K2 に E列の最新残高、L2 に F列の最新残高が表示されると思います。
ご質問の A列からF列がタテに長い表なら、いちいち下にスクロールして探さなくても J2：L2 を見るだけで、最新の残高が確認できて便利ではないでしょうか。
*
ただ、この回答がちゃんと届くのか（ ゆかchanさんが気づいてくれるのか ）がちょっと気がかり‥

お礼

まぁ！いらっしゃいませ(*^^*)

>> 事務局に回答の追加をお願いした次第で

恐縮です(>_<)
このように誠実な方がいらっしゃったとは(驚)
もっとユックリ開いておけば良かったですね^^;
正直わたし自身は結果が同じならいいかなぁって
思ってたけど(ォィ) ここには検索して他の人も
見に来るし、助かると思います。
あ。でも教えてもらったのに書き換えました☆

>> LOOKUP(検査値,範囲) という式は、＜中略＞
>> その範囲内のいちばん最後の数値を返します。

LOOKUP関数は使ったことあったけど、
ゼンゼン知らなかったです！！(喜)

>> 預金残高が10億円以上ある場合は、

その場合は多分、こんな小遣い帳なんか
作らずにバンバン使うと思います…(笑)

>> 以下は余計かもしれませんが‥

あー、それはチョットやりたかったのです！
ありがとうございます♪♪
シオタン99さんの説明は凄く分かりやすいです☆
新しいほうの質問も答えて頂いて嬉しかったです。
…今から、そちらのほうのお礼も書きますね(笑)

回答No.1

shiotan99

こんにちは～

よくわかりませんが・・

E3に↓

=IF(COUNTIF($A3,"*●*"),E$2+SUMIF($A$3:$A3,"*●*",$B$3:$B3)-SUMIF($A$3:$A3,"*●*",$C$3:$C3),"")

F3に↓

=IF(COUNTIF($A3,"*■*"),F$2+SUMIF($A$3:$A3,"*■*",$B$3:$B3)-SUMIF($A$3:$A3,"*■*",$C$3:$C3),"")

と入れて、それぞれ必要なだけ下にフィルコピー、ということでしょうか？