Excelの関数で悩んでいます。

質問

Excel の関数で悩んでいます。
預かっている物があるとして、年間の管理簿において預かり日から経過日数は計算できています。
預かり日から経過日数が35日以内の預かり期間が月において何日あるか。また35日を超える預かり期間が月において何日あるかを計算したいです。
例えば9月分を集計する場合、A列は預かり日、B列はこれまでの経過日数、C列に9月の内35日以内の預かり期間が何日あるか、D列に9月の内35日を超える預かり日数が何日あるか表示したく、

A列　 B列　C列　D列
6/2　 121　0　　30
8/3　 59　 6　　24
9/1　 30　 30　 0　　

上記となるようなC列、D列の関数がわかりません。良い方法をご存じの方、よろしくお願いいたします。

回答No.6

HohoPapa · ベストアンサー

以下のように理解しました。

指定した1か月間に、
500円/日を課金する日と1000円課金する日が何日あるかを求める。

預かる期間は月をまたぐ場合もある。
預かる日数が３５日までなら単価が500円、それを超えると単価が1000円になる。
お返しする日は任意。
（猫ちゃんでも預かるのかな）
つまり、３５日間より短くなることもあれば、長くなることもある。

複数か月にまたがる場合であっても、1か月単位で課金する。
つまり、複数か月に及ぶ場合は、複数回の課金になる。

私なりにシートを構成し、計算式を埋めてみました。

的を射ているようであれば、
いろいろな日付を埋め、期待通りか？を
検証してみてください。

E2:集計期間の初日を埋めてください。
E2セルに2019/10/1を埋めれば、10月の計算になります。

F2=EOMONTH(E2,0)
F5=E5+$H$2

H5=IF(OR(F5<$E$2,E5>$F$2),0,
IF(G5="",MIN(F5,$F$2)-MAX($E$2,E5)+1,
IF(G5<$E$2,0,MIN(F5,G5,$F$2)-MAX($E$2,E5)+1)))

J5=IF(OR(F5>$F$2,E5>$F$2),0,
IF(G5="",$F$2-MAX(F5+1,$E$2)+1,
IF(OR(F5>G5,G5<$E$2),0,MIN(G5,$F$2)-MAX(F5+1,$E$2)+1)))

F5,H5,J5を必要数下方向に複写

回答No.7

SI299792

＞像の貼り方がいまだわからず、また説明がうまくできなく
ここは質問者の画像追加ができません。新しい質問を作るか、どこか別サイトに画像を置き、アドレスを載せるかです。股は質問でやった様に直接データを載せるかです（スペースがあれば別セルと判断するので、ずれていてもいいです）

＞C列5行目が32と出るのはなぜでしょうか？
月をまたぐ場合どうするかを貴殿が上げていないので想像で作りました。
貴殿のデータでは30日になっています。
これに合わせるためには、翌日起算にしました。
（開始日は本日の１か月前にしました）
当日起算にすると、この例で31日になってしまいます。

当日起算にすると、経過日数、超越期間にも影響します。
また、開始日をいつにするかも問題になります（手入力でいいのなら問題ないが）
この場合こうなってほしいというものをもっと例を上げていただければ、貴殿の希望するものができると思います。

＞別シートにて月を指定するとその月に預かっている物だけ集まり表示
は私は回答する気はありません。後追加の場合、新しい質問を作るべきでしょう。

回答No.5

SI299792

済みません。前回答は数字を見誤っていました。
今日は10/4ですが、その場合どうなるのでしょうか。
この点を質問したつもりなのですが、それに対する返信はありませんでした。HohoPapaさんもそこが解らないようです。

考えられるパターンを例示します。
（日付を独立させてあるので、１行目の数式だけ変更で対応可能です）

①日が変わっても変化しない
B1: 2021/9/1
D1: =EOMONTH(B1,0)+1
B3: =D$1-A3
C3: =MIN(B3,MAX(A3+35-B$1,0))
D3: =MAX(0,D$1-MAX(B$1,A3+35))
下へコピペ。

②１か月前の9/4 ～本日10/4計算。
B1: =EDATE(D1,-1)
D1: =TODAY()

③１か月前の9/1 ～本日10/3計算。
B1: =EOMONTH(D1,-2)+1
D1: =TODAY()

＞別シートにて月を指定するとその月に預かっている物だけ集まり表示
これに関しては、画像付での説明がないと作りようがありません。
新しい質問を立てた方がいいです。どうせただですから。

補足

ありがとうございます。
画像の貼り方がいまだわからず、また説明がうまくできなく申し訳ありません。
今回の例示いただきました②につきまして、C列5行目が32と出るのはなぜでしょうか？
9/4～10/4の間の集計していただいていると理解してよろしければ、以下のようにC列5行目が31日となるのであれば求めたい形かと思います。
私の理解・説明不足もあり今更ですが、開始日も含み日数が経過することから、求めたい日数が＋1となっております。

開始日　2021/9/4　本日　2021/10/4
預かり日　経過日数 預かり期間 超越期間
6月2日 　125 　　　　0 　　　　31
8月3日 　63　　　　 　 3　　　　　28
9月1日 　34　 　　　　31 　　　　0

非常に私が思っているものに近くなっており、再三のご質問で恐縮ですが、ここの部分についてご教授いただきたくお願いいたします。

回答No.4

HohoPapa

作成しようとしている表をどのように利用するのか?
によって変わるでしょうから
第三者にはよくわかりませんが。。。
添付画像のようにパターン分けして考えてみてははいかがでしょうか。

9月2日以降の場合を考慮すると、直感的には
Ｃ，Ｄの２つの列で表現するのは困難と感じます。

ともあれ、どのような仕様にするかを決め
画像を含め再質問することを期待します。

また、その折、
>別シートにて月を指定すると
>その月に預かっている物だけ集まり表示されるようにしたいです。
といった動作には、おそらくVBAが必要ですので、
VBAの使用可否も明らかにしてほしいです。

補足

度々ありがとうございます。
やはりVBAの使用が必要になりますか汗
用途としては、まさしくパターン分けしていただいたような考え方なのですが、これを月ごとに集計し請求書を作成しようと考えていました。
35日以内の預かり物は単価500円、36日以上の預かり物は単価1000円と分かれております。
友人がこれらを紙に書き出し月ごとに請求書を作成している姿をみて何とかできないかと考えだしましたが、思っていたより複雑に感じ質問させていただいた次第です。

回答No.3

HohoPapa

No2です。

なお書きを訂正します。

なお、9月を調査する場合でかつ、Ａ列が9月2日以降の時を
考慮していません。
どのような仕様にすればいいか提示してくれれば
対応できるかもしれません

回答No.2

HohoPapa

添付画像を前提に、以下はいかがでしょうか。

A1=2021/9/1
B1=DAY(EOMONTH(A1,0))
C1=35

C2=IF(A2+$C$1-$A$1<0,0,MIN($B$1,A2+$C$1-$A$1))
D2=$B$1-C2

C2:D2を必要数下方向に複写

なお、9月を調査する場合でかつ、Ａ列が10月以降の時を
考慮していません。
どのような仕様にすればいいか提示してくれれば
対応できるかもしれません

補足

ご回答ありがとうございます。
まさしく求めたい数字となっております。
考えていた仕様はこのシート(管理簿)に預かり物が9/2以降も増えていきデータが追加されていきます。
別シートにて月を指定するとその月に預かっている物だけ集まり表示されるようにしたいです。
尚、返却された物はE列等に返却日も入力しそこまでは別シートに集計できるようにし、返却以降は当然集計されないようにしたく考えています。
情報が小出しとなり申し訳ありません。
考えれば考えるだけ自分には手に負えないと感じています。
皆さんの知恵をお貸しください。

回答No.1

SI299792

基準の日付をどうするか解らないのですが、手入力するものとします。

B1: 2021/9/1（基準月です、年月日で入れて下さい）
セルの書式設定、ユーザー定義｢m"月"｣
D1: =EOMONTH(B1,0)+1
セルの書式設定、ユーザー定義｢m"月"｣
A3: 2021/6/2
B3: =D$1-A3
C3: =MIN(B3,MAX(A3+35-B$1,0))
D3: =B3-C3
B3～D3: セルの書式設定、標準
纏めて下へコピペ。

もしかしたら当日を基準にして、
今日が10/2だから 9/1預かりの物は31日になるのかもしれません。
その場合 8/3預かりはどうなるのか補足していただければ式を直します。

補足

早速のご回答ありがとうございます。
説明下手で申し訳ありませんが、預かり日から35日以内の日数が9月中に何日あるか。また預かり日より36日以上預かっている日数が9月中に何日あるか。を求めたいため、C列とD列を合わせると30日になるようにしたいです。
8/3預かりの物については、9月の内、預かり期間が35日以内の期間は9/1～9/6の6日間となり、9/7～9/30までは36日以上の預かり期間で24日間となる。よってC列に6、D列に24と求めるようにしたいです。

伝わりますでしょうか？
何卒よろしくお願いいたします。