[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『保存期間から廃棄日付を出す』（バンククラーク）
バンククラークと申します。
どうしても判らないことがあるので教えてください！
保存期間が1ヶ月、3ヶ月･･････1年、3年など、バラバラの書類があり、列１に保存期間を
入力すれば、列２に2002/10/15日から数えて保存期間経過後の破棄日時が表記される、という表を作らねばならないのですが、
引用元の列１のセルには「1年」とか「6ヶ月」とか記入し、列２のセルには「2003/04/15」とか表記しなくてはなりません。
月と年が混在しており、下記のような計算式を作ったところ､列１が年の表記には対応できるのですが、月の場合は上手くいきません。ご指導お願いします。
例 セルＡ１ 1年
セルＢ１ 2003/10/15
セルＢ１のステータス･･････MID(Ａ1,1,1)*10000+20021015
どうすれば、セルＡ１に 6ヶ月と入れても1年と入れても、セルＢ１にきちんと
2002年10月15日からセルＡ１の期間を足した日付が出るようになるのでしょうか？
よろしくお願いします。
どういう計算で年数を10000倍しているのでしょうか？
教えてください。
（kazu）
バンククラークです。さっそくのお返事、ありがとうございます！
判り難い表現で申し訳ありませんでした。
セルＢ１の書式設定の表示設定を、ユーザー定義で「0000"/"00"/"00」としています。
そうすると、セルＢ１に20021010という数字を入力すれば、2002/10/10と反映します。
セルB１にＭＩＤ（Ａ１，1,1）＊10000とすれば、セルA1「３年」と入力した場合、
頭の３だけ拾って、それを一万倍すると20021010+30000で、セルＢ１には20051010
という数字が飛んできて、ユーザー定義により2005/10/10と表記されるという
仕組みにしたのですが、これでは「6ヶ月」には対応できません。
その後、すこし変えてみました。
2002年10月15日をシリアル値で表記すると37544となるので、セルＡ１に入力するのは
「０．５年」とか、「3.0年」とかにして、MID(A1,1,3)*365+37544)をセルB1に飛ばし、
セルB2の書式設定を日付にすると、見事に2002年10月15日を基準に、保存期間を足して
廃棄日を表記することが出来ましたが、これでは割り切れない月数や、小数点が2桁以上に
なると機能しません。ほとほと困り果てています。お願いいたします。
セルA1に『1ヶ月』セルB1に『=today()』が入っているとき
セルC1に『=YEAR(B1)&"/"&MONTH(B1)+MID(A1,1,1)&"/"&DAY(B1)』
と入力すると一見うまく行くのですが、足し算するので14月とかが出てきて
しまいます。そこでセルC1を
『=IF(MONTH(B1)+MID(A1,1,1)>12,YEAR(B1)+1&"/"&MONTH(B1)+MID(A1,1,1)-12&"/"&DAY(B1),YEAR(B1)&"/"&MONTH(B1)+MID(A1,1,1)&"/"&DAY(B1))』
にすると少しうまく行きますが、10ヶ月11ヶ月の計算がおかしいことが見つかります。
そこでセルC1を
『=IF(MID(A1,2,1)="ヶ",IF(MONTH(B1)+MID(A1,1,1)>12,YEAR(B1)+1&"/"&MONTH(B1)+MID(A1,1,1)-12&"/"&DAY(B1),YEAR(B1)&"/"&MONTH(B1)+MID(A1,1,1)&"/"&DAY(B1)),IF(MONTH(B1)+MID(A1,1,2)>12,YEAR(B1)+1&"/"&MONTH(B1)+MID(A1,1,2)-12&"/"&DAY(B1),YEAR(B1)&"/"&MONTH(B1)+MID(A1,1,2)&"/"&DAY(B1)))』
にすれば、うまくゆきますが、これでもまだ、年の対応ができませんし、10月31日の
1ヶ月先が11月31日になってしまうなど問題が発生します。。すでに式が276文字に
なってすぐに限界1024文字を迎えます。要するに数式ではかなり無理と言うことです。
私はユーザー関数で作るしかないと思います。
[シニア]さん！ヘルプ。何か良い知恵はありませんか？
（kazu）
古い関数のマニュアルを見たら、方法がありました。
EDATE(<開始日>,<月>)
開始日から起算して、指定した月だけ、前または後の日付に対するシリアル値を
計算します。
セルA1に『1ヶ月』セルB1に『=today()』セルC1に
『=IF(MID(A1,2,1)="年",EDATE(B1,MID(A1,1,1)*12),IF(MID(A1,2,1)="ヶ",EDATE(B1,MID(A1,1,1)),EDATE(B1,MID(A1,1,2))))』
でほぼOKだと思います。
（kazu）
失敗失敗。
EDATE はアドインを追加する必要があります。EDATE が#NAME になる方は
[ツール]メニュー→[アドイン]で[分析ツール]にチェックを入れてアドインを追加して
下さい。
（kazu）
A1セルに整数部が年で、小数二桁が月として入力する方法で設定してみました。
A1セルに3年は3、1ヶ月は0.01、２年３ヵ月は2.03と入力するとします。
A1の書式設定をユーザ定義で[ G/標準"(年/月)" ]とします。
B1に開始日として 2002/10/15 と入力します。
C1に =DATE(YEAR(B1)+INT(A1),MONTH(B1)+MOD(A1,1)*100,DAY(B1)) で廃棄日付が表示されます。
※A1セル幅が狭いと小数二桁が四捨五入された表示になります。
このような設定で如何でしょうか？
A1セルに既に11年､5年、11ヶ月等と入力されていて、開始日から指定期間後の廃棄日付は既に解決済みですが、
C1に
=DATE(YEAR(B1)+IF(RIGHT(A1,1)="年",LEFT(A1,FIND("年",A1)-1),0),MONTH(B1)+IF(RIGHT(A1,1)="月",LEFT(A1,FIND("ヶ",A1)-1),0),DAY(B1))
としても、求められます。
(シニア)
[シニア]さんいつもいつもありがとうございます。
ひやーっ！。なるほど、年：整数部と月：小数部にわけますか、完全脱帽です。
（kazu）
kazuさん、シニアさん、どうもありがとうございました！さすがですね！
エクセル関数って、定型どおりではないパターンになってくると、後は知識と感性の
両方が必要なんですね。
もっと精進します！ （バンククラーク）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.