関数で出来ますか～？ 第１７問 月額小計を簡単に求める方法はないでしょうか？ 解答例

問題送った後、丸で考えていなかったんですが、問題考えた時のイメージは式二つで
100超で大したバリエーションは無いと思ってました。
で、いきなり皆さんが自然体モードで100切りで、私は色々やっても100を切れない状態で
正直あせりました＾＾；
書式使用で50台って方が3人もいらっしゃるのでなんともなんですが、一応一週間もがいた
結果を晒します。

書式使用：30+31の61
=IF(N(B6),B6-DAY(B6)＞=B5,1)*B5
=IF(D5,SUM(C$4:C5)-SUM(E$4:E4))
D列の書式　m"月小計";;　E列の書式　#,##0;;;

なんとかならないかともがいてどうにもならなかった、
書式使用：31+31の62
=ISERR(0/(B6-DAY(B6)＜B5)/B6)*B5
=IF(D5,SUM(C$4:C5)-SUM(E$4:E4))

書式不使用：45+37の82
=TEXT(IF(N(B6),B6-DAY(B6)＞=B5,1)*B5,"m月小計;;")
=IF(D5＞"",SUM(C$4:C5)-SUM(E$4:E4),"")

物凄く長いのですが、初期に作成した書式使用で57+58の115
これは、サブトータル表示の判定も、サブトータルの算定方法も
割合と気に入ってます＾＾
=(COUNTIF(B5:B$34,"＜"&DATE(YEAR(B5),MONTH(B5)+1,0))=1)*B5
=IF(D5,SUMIF(B$5:B5,"＞"&DATE(YEAR(B5),MONTH(D5),),C$5:C5))

土曜日の晩までにもう少しなんとかしたいと思ってますが、もうアイデア
涸れたみたいですねーー

　　　　　　　　　　　　　　　　　　　　　　　　　（y sakudaさん談)

Comments

[投稿情報: y sakuda]

締め切り10分前に思いついた手です。
今回の提示シートはD4:E4が結合シートになっていて、E4は0ですのでそれを利用し、E５の式を
{=IF(D5,SUM(C$5:C5-E$4:E4))}
で28文字にできました。
さて、皆さんの回答楽しみです＾＾

[投稿情報: む印]

今回は、終始 からくちさんのリードで短縮出来た次第です^^;

■書式なし、合計８０文字
Ｄ５：=TEXT((B6&-9>EOMONTH(B5,0)&-9)*B5,"m月小計;;")

Ｅ５：=IF(D5="","",SUM(C$5:C5)-SUM(E$4:E4))
　

■表示形式のみ 合計５６文字
Ｄ５：=(B6&-9>EOMONTH(B5,0)&-9)*B5
（Ｄ列表示形式･･･　m"月小計";;）

Ｅ５：{=SUM(C$5:C5-E$4:E4)*(D5>0)}
（Ｅ列表示形式･･･　0,000;;）

■条件付書式+表示形式 合計４５文字
Ｄ５：=TEXT(B5,"m月小計;;")
（Ｄ列条件付書式･･･数式が=E5=0 でフォント色を白）

Ｅ５：=(D5<>D6)*SUMIF(D:D,D5,C:C)
（Ｅ列表示形式･･･　0,000;;）

からくちさんとｍｉｎさんの究極技が楽しみ^^

あと、くまぷーさんの書式なし６７が
どんなのだったか知りたいです！

[投稿情報: ジョー３]

sakudaさん、、凄いですーー
・・・今回、、、燃えてましたねーー

む印さん・・・
EOMONTH・・・・忘れてました。

・・・・・・
まだかぜでくるしんでるじょー。。。

[投稿情報: くまぷー]

>あと、くまぷーさんの書式なし６７が
>どんなのだったか知りたいです！

たいしたモンじゃないんですが
こんなんです(^^ゞ

=IF(MONTH(B5)=MONTH(N(B6)),"",MONTH(B5) & "月小計")

=IF(D4="",E4+C5,C5)

いやあ・・・所詮は勘違いの失敗作ですし・・・(^^ゞ

[投稿情報: む印]

>=IF(D4="",E4+C5,C5)
なるほど、くまぷーさんすごいです！！
この式のおかげで、究極２４文字の謎が解けましたｄ＾＾
（19文字→１４文字に）

[投稿情報: ますたあ]

出題と私のイメージが違っていたようです。

もう少し質問すればよかった．．．。(-_-;)

現金出納簿のような感じで、月ごとに最期に集計で切ればよかったのかな。

私は、一つのセルに月ごとに集計するのかと思い込んでしまいました。
ですので、今回数式は未提出という事で．．．。^^;

[投稿情報: かず]

こんばんは～

月がとんだ場合が気になって…　６５＋６５

=IF(OR(B8="",TEXT(B8,"yymm")=TEXT(B9,"yymm")),"",TEXT(B8,"m月小計"))
{=IF(D5="","",SUM((TEXT(B$5:B5,"yymm")=TEXT(B5,"yymm"))*C$5:C5))}

条件付書式+表示形式は…　３＋５２
=B5 表示形式『m"月小計"』
{=SUM((TEXT(B$5:B5,"yymm")=TEXT(B5,"yymm"))*C$5:C5)}
いずれも、条件付書式で、
=OR(B5="",TEXT(B5,"yymm")=TEXT(B6,"yymm"))
隠した数値を、使い込むなんて気が付きませんでした。

今回もまた、最初に思い付いた式が、頭に重く付きましたｗ

[投稿情報: y sakuda]

む印さんのはEOMONTHですか・・・・
EOMONTHは考えたのですが、第2引数0と言う発想が浮かばなかった・・・・
ここでもう負けてますねーー
>・・・今回、、、燃えてましたねーー
燃えてたというより、夜逃げしないで済むように必死ｗ
ジョー３のはどんなんでしょうか？

>いやあ・・・所詮は勘違いの失敗作ですし・・・(^^ゞ
わたしも最初Monthでやっていたのですが、Month(0)が1月になるというのでその始末で字数が増えてました
それと、今回は良いことにしたのですが、年が変わった時にも対応しようとすると苦労しますね。

>私は、一つのセルに月ごとに集計するのかと思い込んでしまいました。
ますたーさんがどんなイメージもたれてたのか良く分からない。
ちょっとそれも見たい気分です。

>月がとんだ場合が気になって…　６５＋６５
かずさんのは正攻法ですねーー
私も一時、Text（　,"m") で月を求めることも考えたのですが、やはり文字数が増えるのでやめました。

[投稿情報: からくち]

この問題は違反行為（条件付き書式）を使わない限り
Ｄ５の問題で
Ｅ５は何でもいいような・・・
例えば
{=SUM(C$5:C5-E$4:E4)*(D5>0)}

{=SUM(C$5:C5-E$4:E4)*(D5>"")}

=IF(D5="","",SUM(C$5:C5)-SUM(E$4:E4))
などなど・・・いろいろ

式のみ
Ｄ５＝：
：＝TEXT((B5-DAY(B5)<>B6-DAY(B6))*B5,"m月小計;;")

：＝IF(B5-DAY(B5)<>B6-DAY(B6),MONTH(B5)&"月小計","")

：＝IF(B5-DAY(B5)<>B6-DAY(B6),TEXT(B5,"m月小計"),"")

：＝REPT(TEXT(B5,"m月小計"),B5-DAY(B5)<>B6-DAY(B6))

：＝TEXT((DATEDIF(0,B6,"m")<>DATEDIF(0,B5,"m"))*B5,"m月小計;;")

書式のみ使用
Ｄ５＝：
：＝(TEXT(B6,"ym")<>TEXT(B5,"ym"))*B5

：＝OR(EOMONTH(B5,0)B6)*B5

：＝(B6-DAY(B6)<>B5-DAY(B5))*B5

違反行為
書式・条件付き書式ともに使用
Ｄ５＝MONTH(B5)
Ｅ５＝SUMIF(D:D,D5,C:C)

Ｄ５＝B5
Ｅ５＝(B5-DAY(B5)

Ｄ５＝MONTH(B5)
Ｅ５＝(D4=D5)*E4+C5

＜条件付き書式＞
Ｄ５＝LOOKUP(DATE(YEAR(B5),MONTH(B5)+1,0),B$5:B$34)=B5
フォントの色付け

[投稿情報: からくち]

＞Ｄ５＝B5
＞Ｅ５＝(B5-DAY(B5)

Ｄ５＝B5
Ｅ５＝(B5-DAY(B5) の誤りです
訂正いたします

[投稿情報: jyo-3]

>>私は、一つのセルに月ごとに集計するのかと思い込んでしまいました。
>ますたーさんがどんなイメージもたれてたのか良く分からない。

実は私も最初そうしてた。
ｄ６，ｅ６のみ使用・・・
月が変わると、最新の月集計・・・・
なんて、

でも質問して直しました。

１月はわざと手抜きでした。大体自分の
ファイルはそんな感じ・・・・

・・・・・・・・・

感じは、くまぷーさんに似てます

[投稿情報: からくち]

＞Ｄ５＝B5
＞Ｅ５＝(B5-DAY(B5)

Ｄ５＝B5
Ｅ５＝(B5-DAY(B5)＜B4)*E4+C5

忘れていました・・・↑

[投稿情報: y sakuda]

からくちさんの相変わらずバラエティに富んでいてすごいですが、B35に入ってる"総計"考えてくれてない。
ここにわざと文字列入れて、"シート変更不可"の条件つけて多少やりづらくしてるつもりでした＾＾；

>月が変わると、最新の月集計・・・・
ああ、そう言う意味ですか。
問題提示のサンプルがまずかったですね＾＾；

[投稿情報: y sakuda]

からくちさん
>＝OR(EOMONTH(B5,0)B6)*B5
↑ORの中がおかしいんですが、正しくはどうなんでしょうか？

[投稿情報: minmax]

あんまり頑張らなかったので、こんなんです。
もう出ているようなものですが、、
D5
54文字=IF(TEXT(B5,"ym")=TEXT(N(B6),"ym"),"",MONTH(B5)&"月小計")
E5
37文字=IF(D5＞"",SUM(C$5:C5)-SUM(E$4:E4),"")
実務でやるとしたら自分はこの式でやると思う。

あと、
＞Ｄ５＝MONTH(B5)
（１０文字）
＞Ｅ５＝SUMIF(D:D,D5,C:C)
（１８文字）
をR1C1でやると、
D5=MONTH(C2)
E5=SUMIF(C4,C4,C3)
でE5が１６文字になります。
これはつまんないずるでした^^;
（条件付き書式は何とでもなると思うからから省略してます。）

[投稿情報: からくち]

＞B35に入ってる"総計"考えてくれてない。
いえいえ
それは１番最初に

＞故に
＞少々 見てみぬ振りをしていただけるのなら
＞ 中略
＞気が付かないで貰えれば
と
反則・違反・詐欺行為の式も含まれることを勝手に宣言！
したのですが

＞多少やりづらくしてるつもりでした＾＾；
それでは バレナイ訳が無い

やっぱ 悪いことは出来ない

＞>＝OR(EOMONTH(B5,0)B6)*B5
=OR(EOMONTH(B5,0)＜B6,B5＞B6)*B5

[投稿情報: y sakuda]

＞少々 見てみぬ振りをしていただけるのなら
www
基本的なアルゴリズムとしてはどうでも良いのですが、このサイト、障害物を置いておくと面白いのが出てきますからｗ

>=OR(EOMONTH(B5,0)＜B6,B5＞B6)*B5
ありがとうございました

[投稿情報: くまぷー]

お早うございます。

やはり、いろいろ出てきましたね～
あまりに沢山あるので式のみのものしか見ていないのですが
それでも頭の痛くなるレベルのものが
ほとんです(^^ゞ

＞Month(0)が1月になる

う～ん、これは指摘されるまで全然、
考えもしなければ、思いつきもしませんでした。

今回の問題は解答の制限を取り払えば
今までの中で一番、元祖エクセルコンテストの
問題に近かったのではないかと思います。

[投稿情報: minmax]

おはようございます。
＞エクセルコンテストの問題に近かったのではないかと思います。
とくまぷーさんがおっしゃるとおり、今回の問題は、先回の問題とは正反対でより実務に近い問題でしたね。
したがって、基本的には理解しやすいものが良いのかなと思っていました。

パソコンに向かう時間が少なかったので頭で考える事が多く、
＞Ｅ５＝(D4=D5)*E4+C5
という、なるほどなーの発想は出てきませんでした。
同様に、DAY、EOMONTH、DATEDIFは頭にありましたが、
B6-DAY(B6)は考えなかったし、EOMONTHも条件設定には単純に使えないと思いました。
DATEDIF(0,B6,"m")<>DATEDIF(0,B5,"m")の発想も意外。。

む印さんの
＞(B6&-9>EOMONTH(B5,0)&-9)
一目見ただけでは「-9」が何をやっているのか全く分からなかった。
すごい発想に驚きました。
で、B列がH158.5.1以降には対応していないということが、やっとわかりました。。

かずさんの
＞=IF(OR(B8="",TEXT(B8,"yymm")=TEXT(B9,"yymm")),"",TEXT(B8,"m月小計"))
なんで、B8なのと思ったらちょっとずれているだけでしたね^^
ちょっとビックリした。

で、自分のこと・・
今回、初めてR1C1形式で式を組み立ててみて、
＞D5=MONTH(C2)
＞E5=SUMIF(C4,C4,C3)
こんな式になった。
見た目、普通の式みたいだな～って思って、
R1C1形式って宣言しないでこっそり投稿してビックリさせようかなって思ったくらい。
R1C1形式なんて普段使う人いるのかな～？
VBA良くやる人は使うのかもな～

[投稿情報: minmax]

一応追記
＞H158.5.1以降には対応していない
S2.4.30以前もですね。
今回の場合あり得ませんけど＾＾

それにしても、今回の問題もみなさんがんばりましたね～
正直、ちょっと意外でした。

[投稿情報: y sakuda]

>先回の問題とは正反対でより実務に近い問題でしたね。
実務で使う場合は、やはり素直じゃないと後で困る（後任が）ことになりますね。
そうすると、
１．配列数式はだめ
２．条件付書式でE列の数字の色を変えるのはだめ
３．分析ツールもできたら避ける
４．D列の判定も少なく共一言でコンセプトが説明できる（例えば　「D6-Day(D6)　でD6の前月末日が得られる」物である必要がある

まあ、一言で言えば、作業列を使うなという時点で終わってますがｗ
そういう意味では、質問掲示板の回答の説明抜きでしゃれた式が提示されているのには昔から物凄く抵抗感があります。
質問者が趣味でやってる、テクニックを磨きたいと言う場合はかまわないのですが、そういう手の人は始めからROMすることはあっても殆ど質問者としては出てこないと思ってますから・・・・・・

[投稿情報: y sakuda]

＞＞H158.5.1以降には対応していない
＞S2.4.30以前もですね。
どういう意味なんでしょう？
知らなかったんですが、入力時はH100以上を和暦で入れても反応しませんね。
ただし、西暦で入れて書式をかませればちゃんと和暦表示するんですね。
でもH158 とかS2って何が問題なんですかね？

[投稿情報: minmax]

＞後で困る（後任が）ことになりますね。
確かに。。
実務でもエクセルブックを最初から作ることは、ここ数年ほとんど無いのですが、作るときは、あんまり難しい事をしないことにしています。
っていうか、やってしまうと自分の後任者がメンテ出来なくて転出先でもそのファイルのメンテをするはめになってしまう。
一発もののファイルは別ですけど。

[投稿情報: y sakuda]

私も職場でチョコチョコいろいろ提供してますが・・・・・
（Excelだけではなくスクリプトなども）
でも、私が居なくなったらあきらめろと言ってありますｗ

[投稿情報: minmax]

＞でもH158 とかS2って何が問題なんですかね？
私に解説する能力はありませんが、
B6&-9>EOMONTH(B5,0)&-9
の「&-9」は指定行の下の行に日付の入力がなかった場合にTRUEと判定する核の部分ですが、
EOMONTH(B5,0)のシリアル値が９から始まる場合と８以下で始まる場合で結果が異なります。

出来れば、む印さんでもだれでも良いので、解説してもらいたいくらいです。

="-9">"90002-9"・・・FALSE
="-9">"89971-9"・・・TRUE
↑何で結果が違うの？

ついでに、、
="-9">"9"・・・TRUE
="-9">"8"・・・TRUE
↑これもよくわからん。
="-9">"90"・・・FALSE
="-9">"80"・・・TRUE
↑なんで？
エクセルの不思議に書こうかな？
それとも常識？

[投稿情報: ますたあ]

＞ちょっとそれも見たい気分です。
ｗｗｗｗｗwww。^^;

まあ今回は大ボケだったので勘弁してください。(^^ゞ

[投稿情報: む印]

minさんが気付かれた部分
本来＆９だけを双方の後ろに付けても同じ効果なんですが、
-（マイナス）を付けるとその-は無意味になり不思議で､理屈はよく解らないけど面白いと思いそのまま提出しました。(^-^)b

む印の「む」は無責任の「む」！(^^;

[投稿情報: y sakuda]

>↑何で結果が違うの？

なるほど・・・・・・
しかし？？？

[投稿情報: minmax]

マイナスが無意味になったとは思えないけど…
出先なので後で実験します。

む印の"む"は無尽蔵の"む"？

[投稿情報: y sakuda]

この「関数でできますかーー」のシリーズなんとなく始まったので、最初の頃の記憶が怪しく（参加してないのも結構あったし）なったので、
http://kumapooh.justblog.jp/blog/cat4400146/index.html
でチェックしてたのですが・・・・・・
Q11のかごめの問題が見つからない＾＾
カテゴリ違いでどこかにまぎれてるのかな？

[投稿情報: くまぷー]

>Q11のかごめの問題が見つからない＾＾

カテゴリを指定するのを忘れていました。
既出の問題・解答はこちらに一覧がありますのでご利用下さい。

http://www.geocities.jp/kuma_pooh1958/index.htm

http://kumapooh2007.hp.infoseek.co.jp/

[投稿情報: y sakuda]

あははっ、あちこちにトップページ作ってますねーー
ありがとうございました。
で、次の出題予定日は？

[投稿情報: くまぷー]

>で、次の出題予定日は？

火曜日あたりを考えています。

[投稿情報: y sakuda]

>火曜日あたりを考えています。
楽しみにしてます＾＾

今回のは私自身想像もしていなかった簡潔な式に行き着き、実質的な成果が得られました＾＾