Excelの関数で困っています。

質問

以前にも質問させて頂いたのですが
今３０日＝一ヶ月として、日付の計算をしています。
例（１２月１日＋３０=１月１日、２月１日＋４０＝３月１１日となるようにしたいのです。）

条件としてセルは三つまでしか使えません。

A1 起算日
B1 間の日数
C1 たされた結果の日付

今この関数を使って計算をしています。
A1+MATCH(B1,INDEX(DAYS360($A$1,$A$1+ROW(INDIRECT("A1:A"&B1+6))),,),1)
この関数をもっと簡単にして欲しいと言われたのですが…。
色々調べてみたのですが、まったく分からなかったので
また質問させて頂きました。

かなり困ってますのでよろしくお願い致します。

回答No.26

Wendy02

晦日の計算、月の晦日（末尾）+30, +60 とすると、次の月の1日になるようです。改めて、同じと書いたことを訂正させていただきます。

お礼

すいません。仕事で離れていて、せっかく質問したのにこのページが見る時間がありませんでした。本当は一人一人お礼を書きたいのですが、ちょっと人数が多いのでまとめて書かさせて頂きます。
あやふやな質問の中皆様本当にありがとうございました。
上司に一応何で簡単にしたいのかとか、色々条件をを明確にしないと出来ないと、突っぱねたんですが、ただ長いから短く出来ないの？ぐらいの意味しかないそうです。
出来ないなら出来ないで、今の関数で計算できてるから別にいいよ。
俺は関数とかよく分からないし。ただ短い方がなんかいいんじゃないかと
思ってね。っと言われました…。
ご迷惑をおかけしてすいません。
皆様本当にありがとうございました。

回答No.25

RiRiEL

#2です。

たくさん回答があるので混乱しているかと思いますが、
とりあえず、
　＞C1に=A1+B1と入れた場合、2007/12/31となってしまうと思うんですが？違うんでしょうか？？
にお答えいたします。

確かに、C1に"=A1+B1"と入れた場合、C1は2007/12/31となります。
Excelの日数計算は「片落」だと思われます。
計算の開始日と終了日のうち、
「片落」はどちらか一方の日数を含む
「両入」は両方の日を含む

ちなみにこの計算を「両入」で計算する場合（電卓で計算）、
12/30になります。

お分かりいただけましたか？

回答No.24

i_september

ANo.22です。追記致します。
thugs13様の関数と平行テストしていましたら、2007/01/01から510日後に2008/5/31が発生しました。
私の予想ですが、このケースは1日からの計算でも途中日からでも、多分同じ結果と思われます。つまり、1日からの計算結果が正しければ途中日からの結果も正しい結果になるかと予想します。

回答No.23

i_september

こんにちは。ANo.22です
2/29と2/30の最終分岐部分を追加して作成してみました。（本当に出来るのか気になりましたので）

=IF((TEXT(DATE((YEAR(A1)+ROUNDDOWN((MONTH(A1)+ROUNDDOWN((B1+DAY(A1)-1)/30,0)-1)/12,0)),(MOD(MONTH(A1)+ROUNDDOWN((B1+DAY(A1)-1)/30,0)-1,12)+1),(MOD(B1+DAY(A1)-1,30)+1)),"yyyymd"))<>(YEAR(A1)+ROUNDDOWN((MONTH(A1)+ROUNDDOWN((B1+DAY(A1)-1)/30,0)-1)/12,0))&(MOD(MONTH(A1)+ROUNDDOWN((B1+DAY(A1)-1)/30,0)-1,12)+1)&(MOD(B1+DAY(A1)-1,30)+1),(DATE((YEAR(A1)+ROUNDDOWN((MONTH(A1)+ROUNDDOWN((B1+DAY(A1)-1)/30,0)-1)/12,0)),(MOD(MONTH(A1)+ROUNDDOWN((B1+DAY(A1)-1)/30,0)-1,12)+1)+1,1)-1),(DATE((YEAR(A1)+ROUNDDOWN((MONTH(A1)+ROUNDDOWN((B1+DAY(A1)-1)/30,0)-1)/12,0)),(MOD(MONTH(A1)+ROUNDDOWN((B1+DAY(A1)-1)/30,0)-1,12)+1),(MOD(B1+DAY(A1)-1,30)+1))))

実用的ではないですが、結果としては希望の内容かと思いますが、如何でしょうか。
関数では複雑で何がなんだか分かりませんが、VBに分解して置き換えるとすっきりと見えてきます。
2007/1/1と2007/1/30から2000日まで結果確認しました。
回答にはなっていませんが、非常に面白い内容で勉強になりました。
thugs13さん有難う御座います。

回答No.22

noname#79209

#15です。

その上司の方の考え方には、厳密な意味での規則性は無いんじゃないかと思えてきました。

大雑把に「３０日」は「ひと月」と考えているだけだと思えてきました。
３５日なら、翌月の５日。
３０日なら、翌月の１日。
多分、上のように答えると思います。ここで、もう規則性が失われています。
３１日なら、どうなのか（翌月１日なのか、翌月２日なのか）是非お聞きしてみたいです。

回答No.21

i_september

こんにちは。
私も出来るかやってみましたが、こんな関数になってしまいました。

=DATE((YEAR(A1)+ROUNDDOWN((MONTH(A1)+ROUNDDOWN((B1+DAY(A1)-1)/30,0)-1)/12,0)),(MOD(MONTH(A1)+ROUNDDOWN((B1+DAY(A1)-1)/30,0)-1,12)+1),(MOD(B1+DAY(A1)-1,30)+1))

この関数では、２月２９日と２月３０日が必ず発生します。
この月と日が発生した時に、２８日(または２９日)をセットする分岐をさらに追加することになりますので、もっと複雑になりそうです。
前に書かれた方がいましたが、私もユーザー関数が分かり易いかと思います。
尚、thugs13さんの関数と比較テストしていましたら、2007/12/30から30日後に2008/1/31が発生しましたので、ご報告致します。

回答No.20

Wendy02

thugs13 様

#7,#11の回答者です。

>今３０日＝一ヶ月として、日付の計算をしています。

#10の補足：
>1月1日に５９を足した場合は、うるう年ならば、2月29日になります。
>違うならば3月1日になります

うるう年でないなら、3月1日でよいのですか？
原則と違うと思いますので、なし崩しに、私の数式は、意味のないものになってしまいます。

30日を1ヶ月とするのに、2ヶ月になっていないので、2月28日というものになるのではなかったのではないでしょうか？

#7 の VBAのユーザー定義関数は、失敗していましたので、新たに作り直しました。ただし、今までの数式を、ユーザー定義関数の中に入れてしまったので、原則をそのまま投影しています。しかし、別に、関係ないとは思いますが、揮発・不揮発などは関係ありませんから、ご安心ください。（^^;

数式は、このように入れます。簡単で済むと思います。

=DaysMon30(A1,B1)

この数式のチェックリスト
30日、1ヶ月という原則を、維持しています。
調べてみると、分かるはずです。
--------------------------------------------------------
2007/1/1 ＋ 59日 は、1ヶ月29日ですが、2ヶ月に満たないので、
　　　　　　2007/2/28 で、3/1 にはなりません。
2007/1/1 ＋ 60日は、2ヶ月 で、2007/3/1 になります。
2008/1/1　＋59日は、2008/2/29 になります。
2008/1/1　+ 121日 は、4ヶ月と1日で、2008/5/2 で、5/1 にはなりません。
2008/1/31　 + 120日 は、4ヶ月で、2008/5/31 で、5/1 にはなりません。
2008/1/31　 + 121日 は、4ヶ月と1日で、2008/6/1 です。
2007/12/8 + 60日は、 2ヶ月後ですから、2008/2/8 です。
--------------------------------------------------------------

Public Function DaysMon30(rng As Range, AdDate As Long) As Long
　　Dim orgDate As Long
　　Dim newDate As Long
　　Dim i As Long
　　If rng.Value2 > 0 Then
　　　　orgDate = rng.Value2
　　　　newDate = orgDate
　　　Do
　　　　 newDate = newDate + 1
　　　　 'NASD 方式
　　　　 i = WorksheetFunction.Days360(orgDate, newDate, False)
　　　　 Loop Until i > AdDate
　　　　　DaysMon30 = newDate - 1
　　End If
End Function

--------------------------------------------
標準モジュールへの取り付け方：

Alt　+　 F11 (Altを押しながらF11）を押すと、Visual Basic Editor 画面が出てきます。
次に、メニューの[挿入]－[標準モジュール]と開けて、クリックすると、画面が現れますので、以下のコードを貼り付けて、

Alt　+　Q で、画面を閉じます。

後は、一般関数と同じように使用できます。
グローバルで使いたい場合は、アドインにすると良いです。ただし、少しコツが必要です。
--------------------------------------------

なお、前回からの流れで、望む・望まぬは別としては、やむをえず、私は、書いてしまいましたが、私個人としては、これ以上、数式での解決は、もう不可能です。ロジックを端折ることは不可能だからです。

本来は、どういう目的で使うか確認しておけばよかったのかもしれません。一体、何のために必要なのか、それがはっきりすれば、少しは違っていたかもしれません。

さきほど、調べてみましたが、「360日カレンダー方式」は、証券・金融市場で使われる方式にあるわけですが、それに、DAYS360 を当てはめたら、うまく行ったわけで、その方式自体まで疑問視する必要もないのですが、その法則やロジックが違うなら、提示していただかないと、特例を作ると、かならず別の計算で矛盾が生じてしまうと思います。場合によっては、1ヶ月違ってしまうように思います。上記のチェックリストを参照していただければ分かります。

「360日カレンダー方式」は、英語ですが、以下に説明があります。
http://en.wikipedia.org/wiki/360-day_calendar

US/NASD 方式は、証券業協会で決定ですが、Microsoft Excelの　DAYS360 は、SIAには、準拠しておりません、となっています。私は、この方面には詳しくありません。

一応、個別の計算自体よりも、原則論を提示して、それに合わせて、矛盾なく行えるようにすればよいと思います。1ヶ月を2ヶ月と数えないというような特例は、ややこしくしくしています。それを言う筋ではないとは思いますが、この流れで、少し感じました。

回答No.19

imogasi

#18にもなってすごいですね。
前の質問の時から意味があいまいだったので、また難しいいこともあり回答してないが
＝＝＝
Ｘ=日付、Ｙ＝日数として
◎を特殊な演算子と考えて
Ｘ◎Ｙを求める。
ーー
しかし
●Ｘの値の「集合」は｛1/1,2/1.3/1,4/1,5/1・・・12/1}ーーー（１）月初日だけ
これに対する考えのもの
｛1/1,1/3,1/5,・1/31,・2/1,2/4/2/5・・・｝ーーー（１）’途中日あり
●Ｙの値の「集合」は{30,40,50・・・｝ーーーー（２）
これに対するのは｛20,30,32,35,40,45,・・・｝ーーーー（２）’
３０以上はどんな日数もあリ
で良いのか（＝（１）、（２）でよいか）どうか質問者はあいまいのままでは無いですか。
式の簡単・複雑に影響すると思うからはっきりさせるのが先決では。
ーー
◎演算子は
Ｘは月初日しかないとして、Ｙが３０の場合
月日数が３０の場合、翌月１日
月日数が３１の場合、翌月１日
月日数が２８，２９の場合、翌月１日
ーー
Ｙが４０とかだと
４０＝３０＋１０として（３０単位に分割し）
３０について前者の結果を求める。
求めた結果に、端数の処理をする（２段階演算）。
Yが６０日なら
＆０＝３０＋３０で
スタート月に前者の演算をし、結果にもう一度同じ演算をする。

回答No.18

hallo-2007

横からすみません。
その１
>この関数をもっと簡単にして欲しいと言われたのですが…。
といわれるのは、質問者が式の意味を、きちんと説明できないので、
採用してよいかどうか、判断できないのでは、ないでしょうか。
その２
翌月の10日目を40とするなども、理にかなっていません。
その３
逆に、どの様な業務に、この式を使われるのかを、説明してもらうと
別の方向からアドバイスが出来そうです。
その４
察するに、1日目から翌月10日、15日などを求めようとしている事から
請求に対する支払日、入金予定日などでは
その５
翌月10日を４０にするのではなく、列を2列利用して、月後、日を指定する方法
列が1列しか取れないのであれば、1-10（翌月10日）など文字型で表記するルールの採用

このあたりが整理できれば、求める日付の式も簡単になります。
間の日数も、他の人からみてもわかりやすいはずです。
上司の言われる解りやすいシートになると思いますし、説明も簡単に
なり、引継ぎのトラブルもなくなるでしょう。

的外れでしたら、忘れてください。

回答No.17

noname#176215

#3のものです。

> A1+MATCH(B1,INDEX(DAYS360($A$1,$A$1+ROW(INDIRECT("A1:A"&B1+6))),,),1)
まず この式を「あまりよくない」と書いた理由は INDIRECT関数と配列計
算を組み合わせてしまっていることが主なものです。 他に代替が利く関数
もあるのに揮発性関数を配列計算に選択するのは 使い方として非常にまず
いものだと思います。INDEXなら 起動時の再計算こそあれ まだましです。

次に DAYS360を選択したことで 閏年の計算が面倒になったことです。
というか私が主張したいのは

> ひとまず上の説明を見ている限り 「３０日＝一ヶ月」という条件を忘
> れた方が良さそうですけどね。
これに尽きます。計算をしやすくする為に「３０日＝一ヶ月」と云ってい
るのか 本当に NASD方式で計算しなければならない計算なのかを考える必
要があると思います。

今までの条件だけで見れば（計算に使う日付は必ず初日）ごく単純な計算
式で求める答えがでると思います。

=DATE(YEAR(A1),MONTH(A1)+INT(B1/30),MAX(MOD(B1,30),1))

今まで出た質問の条件だけを考えるなら
・計算に使う日付は必ず初日
・３０で割り切れる時の日付は「１日」
・それ以外は ３０の商の整数部を月に足し 残りを日付とする

だと思うのですが。