こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセルの関数について

いつもお世話になります。
エクセルの関数について教えてください。

【やりたいこと】
・1つのエクセル内に複数のシートを用います。
・「シート1」に「指定日」を入力します。
・「シート2」以降、各シートで用意されている3つのセルに、
「その月の期首」「その月の期末」「期首から期末までの日数」を反映させたいのです。
「指定日」の翌日から起算して56日目に達するまでです。
指定日のみ西暦で入力、反映は和暦です。

【例(1)】
・「シート1」に指定日として「20160629」と入力。
その入力に対して期待する反映は、
・「シート2」に用意されている3つのセルに「280630」「280630」「1」と出ること。
・「シート3」に用意されている3つのセルに「280701」「280731」「31」と出ること。
・「シート4」に用意されている3つのセルに「280801」「280824」「24」と出ること。

【例(2)】うるう年を考慮。
・「シート1」に指定日として「20160131」と入力。
その入力に対して期待する反映は、
・「シート2」に用意されている3つのセルに「280201」「280229」「29」と出ること。
・「シート3」に用意されている3つのセルに「280301」「280327」「27」と出ること。


よろしくお願いします。

投稿日時 - 2016-10-05 09:15:46

QNo.9238414

すぐに回答ほしいです

質問者が選んだベストアンサー

回答No.5の補足を読み返してみました。
>・例外として、結果として反映日Cが指定日Bよりも後になった場合は、指定日Bから反映日Dまで
これが回答No.6の補足で「【例5】がうまく反映しません。」と言うことなのでしょうか?
シート2!A1は「シート1!A1-41またはシート1!B1の何れか小さい方」と言うことでしたら次の数式に変更してください。
シート1!A1-41 → MIN(シート1!A1-41,シート1!B1)
シート2からシート6までのA1セルについて数式の修正が必要になります。
この訂正をしたときに回答No.5の補足である「出産日の翌日から起算します。」と算出された日数に矛盾が生じませんか?
例5で算出される合計日数は57日であり、出産の翌日から起算して56日との間で矛盾するような気がします。

投稿日時 - 2016-10-09 22:45:15

ANo.8

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(8)

ANo.7

>ご教授いただいた数式で試してみたのですが、【例5】がうまく反映しません。
新たな条件があるのですか?
シート2!A1セルはシート1!のA1セルの42日前と言う条件で計算しています。
A1セルに出産予定日を入力した時点ではB1セルが空欄(未入力)であればその対応も必要でしょう。
また、B1セルは出産するまではA1セルと同じ日付(出産予定日)を入力してあり、出産した時点で日付を訂正すると言う条件を先に提示して措かないと数式に反映されません。
条件を見直して確定した仕様を提示してください。
Aさんの常識がBさんの非常識と言うこともありますのであなたがやりたいことを常識で判断する要素と思われることも詳しく提示してください。

投稿日時 - 2016-10-09 07:22:21

ANo.6

>実は指定日がもうひとつあります。出産休暇の関係で、出産“予定日”があるためです。
質問の内容が不完全のため追加の質問が必要になったのですよね。
このサイトでは違反行為になるようです。
後出し条件が次々と出てくると質問が際限なく続いてしまいます。

>それぞれの反映は数値で大丈夫です。A1とB1はeemmddで、C1はddで表示形式を設定することにます。
C1は日付ではなく日数なので表示形式を[標準]にしてください。

>・反映日Cから反映日Dまでの、それぞれの月ごとに期首と期末を管理したいと思っています。
それぞれの月は「期首」と「期末」ではなく「月初」と「月末」ではないでしょうか?

>大変ややこしくて申し訳ございません。
冷静に考えれば簡単なことですが、あなたの説明のしかたがややこしいだけのようです。
出産予定日の42日前から出産後の56日までを月毎の日数計算したいだけのようですね。
前後の1ヶ月に満たない部分の日数を正しく計算出来れば中間月はその月の1日から末日までになります。

シート2!
A1=シート1!A1-41
B1=EOMONTH(A1,0)
C1=B1-A1+1
シート3!
A1=EOMONTH(シート1!A1-41,0)+1
B1=IF(EOMONTH(A1,0)>シート1!B1+56,シート1!B1+56,EOMONTH(A1,0))
C1=B1-A1+1
シート4!
A1=IF(EOMONTH(シート1!A1-41,1)+1<シート1!B1+56,EOMONTH(シート1!A1-41,1)+1,"")
B1=IF(A1="","",IF(EOMONTH(A1,0)>シート1!B1+56,シート1!B1+56,EOMONTH(A1,0)))
C1=IF(COUNT(A1:B1)=2,B1-A1+1,"")
シート5!
A1=IF(EOMONTH(シート1!A1-41,2)+1<シート1!B1+56,EOMONTH(シート1!A1-41,2)+1,"")
B1=IF(A1="","",IF(EOMONTH(A1,0)>シート1!B1+56,シート1!B1+56,EOMONTH(A1,0)))
C1=IF(COUNT(A1:B1)=2,B1-A1+1,"")
シート6!
A1=IF(EOMONTH(シート1!A1-41,3)+1<シート1!B1+56,EOMONTH(シート1!A1-41,3)+1,"")
B1=IF(A1="","",IF(EOMONTH(A1,0)>シート1!B1+56,シート1!B1+56,EOMONTH(A1,0)))
C1=IF(COUNT(A1:B1)=2,B1-A1+1,"")

シート7!は不要と思いますが必要でしたらシート6!の数式を準用してください。
EOMONTH(シート1!A1-41,3)+1 → EOMONTH(シート1!A1-41,4)+1

投稿日時 - 2016-10-08 20:52:39

補足

bunjiiさん
ご回答ありがとうございます。
ご教授いただいた数式で試してみたのですが、【例5】がうまく反映しません。
ご回答お待ちしております。。。

【例5】指定日Aが指定日Bの後にくる、かつ反映日Cも指定日Bよりも後になるパターン
シート1の、
A1セルに指定日A「2016/5/25」と入力
B1セルに指定日B「2016/4/1」と入力
【例5の反映】
シート2の、
A1セルに「280401」と反映
B1セルに「280430」と反映
C1セルに「30」と反映
シート3の、
A1セルに「280501」と反映
B1セルに「280527」と反映
C1セルに「27」と反映

投稿日時 - 2016-10-09 01:49:23

ANo.5

回答No.4で補足を要請しましたが読んでいないのでしょうか?
勝手解釈で例を挙げてみます。
前提条件がシート1のA2セルへ日付のシリアル値を入力し、表示形式を"yyyymmdd"としてセルの値の表示が 20160629 となるようにしたときシート2のA2セルへ次の数式を設定します。このとき表示形式を"eemmdd"にすると
=シート1!A2+1 → 280630
シート2のB2セルに次の数式設定して、表示形式を"eemmdd"にすると
=EOMONTH(A2,0) → 280630
EOMONTH関数でシート2のA2を含む当月末日を算出しています。
シート2と同様にシート3のA2セルへは起算日の翌月の初日を算出します。
=EOMONTH(シート1!A2+1,0)+1 → 280701
この段階では起算日から56日以上の日付になりませんのでIF関数での条件分岐を必要としません。
シート3のB2セルでは起算日によって月末までに56日を経過する場合がありますので条件分岐で求める日付を変えることにします。
=IF(EOMONTH(A2,0)<=シート1!A2+56,EOMONTH(A2,0),シート1!A2+56) → 280731
=IF(EOMONTH(A2,0)>シート1!A2+56,シート1!A2+56,EOMONTH(A2,0)) → 280731
前者と後者は論理式の結果が逆転しますので分岐先の数式を入れ替えているだけです。
シート4のA2とB2セルの数式ではIF関数の論理式でシート2とシート3のC2セルに算出してある日数を使いますのでそれぞれの数式を次のように設定しておきます。
シート2のC2=B2-A2+1 → 1
シート3のC2=B2-A2+1 → 31
シート4のA2セルでは起算日の翌々月の初日なので起算日の日付によっては56日を経過していることもあります。
従って、IF関数で返す値を分岐します。計算結果は日付のシリアル値か文字数0の文字列なのでセルの表示形式を"eemmdd"とします。
=IF(SUM(シート2:シート3!C2)<56,EOMONTH(シート1!A2+1,1)+1,"") → 280801
シート4のB2セルもA2セルと同様にIF関数で分岐します。
=IF(SUM(シート2:シート3!C2)<56,シート1!A2+56,"") → 280824
シート4のA2とB2セルが0文字長の文字列のとき#VALUE!エラーを発生させないようにIF関数で返り値を分岐します。
=IF(COUNT(A2:B2)=2,B2-A2+1,"") → 24

シートA2セルへの入力する値が文字列の"20160629"や数値の20160629の場合は前述する数式内の「シート2!A2」の代わりに次の数式に置き換える必要があります。
DATE(LEFT(シート1!A2,4),MID(シート1!A2,5,2),RIGHT(シート1!A2,2))
また、シート2からシート4のA2とB2の値を文字列に変換するにはTEXT関数を使いますがテキストに変換するとA2からB2の算出時に日時のシリアル値へ再変換しなければなりませんのでセルに格納する値をどのようにするか適切に判断すべきです。
他のアプリケーションと連携する場合は他のセルで計算し結果を文字列に変換すると効率的になります。
データの扱い方を適切にされるよう配慮してください。

投稿日時 - 2016-10-06 19:43:25

補足

bunjiiさま、返事が遅くなりまして申し訳ございません。
それぞれの数式と、それらの働きまで書いていただき、大変助かりました。
的確かつ丁寧な回答ありがとうございます。

補足質問してもよろしいでしょうか?
bunjiiさんのやり方を応用することで、あとの残りは自分で応用できるかと思ったのですが、いろん

なパターンが出てきてしまい、数式を組み立てることが出来ませんでした。
実は指定日がもうひとつあります。出産休暇の関係で、出産“予定日”があるためです。


【補足要請に対する答えです】
・指定日の入力には、文字列ではなく、2016/6/29と入力することとします。
・各シートに用意されている3つのセルは、それぞれA1、B1、C1とします。
 それぞれの反映は数値で大丈夫です。A1とB1はeemmddで、C1はddで表示形式を設定するこ

とにます。
・反映パターンによって、シートの跨り枚数が変わってくると思います。
 仮にシート3で完結したときはシート4以降は各々文字数0の文字列("")でお願いします。
 また、用意するシートの最大枚数ですが、7枚で間に合うと考えています。(それ以上になるよう

な指定日AおよびBを入力することが無いため)
・エクセルのバージョンは2010です。申し訳ございません。


【こちらの追加の補足説明です・・・】
・「出産予定日」=「指定日A」とします。
・「出産日」=「指定日B」とします。
・出産予定日の42日前の日にちを「反映日C」とします。
 ※カウント方法は、出産予定日の当日から起算します。
・出産日の56日後の日にちを「反映日D」とします。
 ※カウント方法は、出産日の翌日から起算します。


【やりたいこと】
・指定日Aと指定日Bを入力することで、反映日Cおよび反映日Dを自動計算させたいのです。
・反映日Cから反映日Dまでの、それぞれの月ごとに期首と期末を管理したいと思っています。
・指定日Aが指定日Bの前になるときもあれば、後になるときもあるので、いろんなパターンが生ま

れ、反映日Cから反映日Dの間が単純に42+56になるわけではありません。
・例外として、結果として反映日Cが指定日Bよりも後になった場合は、指定日Bから反映日Dまで

の、それぞれの月ごとに期首と期末を管理します。(例5のパターンです)



【例1】指定日Aと指定日Bが同日パターン
シート1の、
A1セルに指定日A「2016/4/1」と入力
B1セルに指定日B「2016/4/1」と入力
【例1の反映】
シート2の、
A1セルに「280220」と反映
B1セルに「280229」と反映
C1セルに「10」と反映
シート3の、
A1セルに「280301」と反映
B1セルに「280331」と反映
C1セルに「31」と反映
シート4の、
A1セルに「280401」と反映
B1セルに「280430」と反映
C1セルに「30」と反映
シート5の、
A1セルに「280501」と反映
B1セルに「280527」と反映
C1セルに「27」と反映

【例2】指定日Aが指定日Bの前にくるパターンその1
シート1の、
A1セルに指定日A「2016/3/25」と入力
B1セルに指定日B「2016/4/1」と入力
【例2の反映】
シート2の、
A1セルに「280213」と反映
B1セルに「280229」と反映
C1セルに「17」と反映
シート3の、
A1セルに「280301」と反映
B1セルに「280331」と反映
C1セルに「31」と反映
シート4の、
A1セルに「280401」と反映
B1セルに「280430」と反映
C1セルに「30」と反映
シート5の、
A1セルに「280501」と反映
B1セルに「280527」と反映
C1セルに「27」と反映

【例3】指定日Aが指定日Bの前にくるパターンその2
シート1の、
A1セルに指定日A「2016/2/25」と入力
B1セルに指定日B「2016/4/1」と入力
【例3の反映】
シート2の、
A1セルに「280115」と反映
B1セルに「280131」と反映
C1セルに「17」と反映
シート3の、
A1セルに「280201」と反映
B1セルに「280229」と反映
C1セルに「29」と反映
シート4の、
A1セルに「280301」と反映
B1セルに「280331」と反映
C1セルに「31」と反映
シート5の、
A1セルに「280401」と反映
B1セルに「280430」と反映
C1セルに「30」と反映
シート6の、
A1セルに「280501」と反映
B1セルに「280527」と反映
C1セルに「27」と反映

【例4】指定日Aが指定日Bの後にくるパターンその1
シート1の、
A1セルに指定日A「2016/5/1」と入力
B1セルに指定日B「2016/4/1」と入力
【例4の反映】
シート2の、
A1セルに「280321」と反映
B1セルに「280331」と反映
C1セルに「11」と反映
シート3の、
A1セルに「280401」と反映
B1セルに「280430」と反映
C1セルに「30」と反映
シート4の、
A1セルに「280501」と反映
B1セルに「280527」と反映
C1セルに「27」と反映

【例5】指定日Aが指定日Bの後にくる、かつ反映日Cも指定日Bよりも後になるパターン
シート1の、
A1セルに指定日A「2016/5/25」と入力
B1セルに指定日B「2016/4/1」と入力
【例5の反映】
シート2の、
A1セルに「280401」と反映
B1セルに「280430」と反映
C1セルに「30」と反映
シート3の、
A1セルに「280501」と反映
B1セルに「280527」と反映
C1セルに「27」と反映


大変ややこしくて申し訳ございません。
ご回答お待ちしております。。。

どうぞよろしくお願いいたします。

投稿日時 - 2016-10-08 16:16:17

お礼

補足の補足です。
シート1の適当な他のセルに計算用セルを作成することは可能です。

よろしくお願いいたします。

投稿日時 - 2016-10-08 16:24:44

ANo.4

>「シート1」に指定日として「20160629」と入力。
Sheet1!A2へ文字列として20160629と入力されていると考えてよろしいですか?
一般的には日付のシリアル値として2016/6/29と入力して数値の42550に変換されたものを使います。
1900/1/1=1とした経過日数が日付のシリアル値です。

>「シート2」に用意されている3つのセルに「280630」「280630」「1」と出ること。
3つのセルとはSheet2のA2、B2、C2と考えて良いですか?
A2とB2のセルの値は文字列ですか?、それとも数値でしょうか?
一般的には日付のシリアル値を使いますが和暦の場合はM、T、S、Hと年号のローマ字表記の先頭1文字を付加します。また、年、月、日の仕切り文字として「.」を入れます。
日付のシリアル値を使うのは年、月を跨ぐ日数の計算が容易になるように配慮された方法です。
あなたが提示した値は文字列や数値に関わらず余分な中間計算が必要になり数式が複雑になります。
日数のC2セルはB2-A2で良いと思いますが結果は数値で良いのでしょうか?

>「シート3」に用意されている3つのセルに「280701」「280731」「31」と出ること。
>「シート4」に用意されている3つのセルに「280801」「280824」「24」と出ること。
指定日の値によってSheet3で完結する場合とSheet4へ跨ることもありますがSheet3で完結したときはSheet4は各々文字数0の文字列("")でよろしいのですか?

尚、Excelのバージョンが記載されていませんがExcel 2007以降でよろしいでしょうか?

的確な回答を得るためには以上の補足が必要になります。

投稿日時 - 2016-10-05 20:50:27

ANo.3

 Sheet1のどのセルに指定日が入力されているのという事や、Sheet2以降の各シートのどのセルに「その月の期首」、「その月の期末」、「期首から期末までの日数」を表示させれば良いのかという事が全て不明なため、取りあえず仮の話として、Sheet1のA1セルに指定日が入力されていて、Sheet2以降の各シートのB1セルに「その月の期首」を、B2セルに「その月の期末」を、B3セルに「期首から期末までの日数」を、それぞれ表示させるものとします。


 まず、Sheet2のB1セルに次の関数を入力して下さい。

=IF(AND(SUM(Sheet1!$A$1)>"1904/1/1"+0,ISNUMBER(TEXT(Sheet1!$A$1,"0000""/""00""/""00")+0)),TEXT(TEXT(Sheet1!$A$1,"0000""/""00""/""00")+1,"eemmdd"),"")

 次に、Sheet2のB2セルに次の関数を入力して下さい。

=IF($B$1="","",TEXT(EOMONTH(TEXT($B$1,"平成00年00月00日"),0),"eemmdd"))

 次に、Sheet2のB3セルに次の関数を入力して下さい。

=IF($B$1="","",$B$2-$B$1+1)

 次に、Sheet3のB1セルに次の関数を入力して下さい。

=IF(Sheet2!$B$2="","",IF(TEXT(Sheet2!$B$2,"平成00年00月00日")-TEXT(Sheet1!$A$1,"0000""/""00""/""00")<56,TEXT(TEXT(Sheet2!$B$2,"平成00年00月00日")+1,"eemmdd"),""))

 次に、Sheet3のB2セルに次の関数を入力して下さい。

=IF($B$1="","",TEXT(MIN(EOMONTH(TEXT($B$1,"平成00年00月00日"),0),TEXT(Sheet1!$A$1,"0000""/""00""/""00")+56),"eemmdd"))

 次に、Sheet3のB3セルに次の関数を入力して下さい。

=IF($B$1="","",$B$2-$B$1+1)

 同様に、Sheet4以降のシートのB1セルには、Sheet3のB1セルに入力した関数の中において

Sheet2

となっている箇所を、各々のシートの1つ前のシート名に置き換えた関数を入力して下さい。
 次に、Sheet3のB2~B3のセル範囲をコピーして、Sheet4以降のシートのB2~B3のセル範囲に貼り付けて下さい。


 以上です。

投稿日時 - 2016-10-05 12:48:59

ANo.2

追加です。
Sheet2~Sheet4のセルA2、A3の書式はユーザー定義で
eemmdd
としておきます。

投稿日時 - 2016-10-05 10:31:30

ANo.1

こんにちは
「Sheet1」に指定日がセルA2として、
「Sheet2」の
セルA1に
=DATEVALUE(LEFT(Sheet1!A2,4)&"/"&MID(Sheet1!A2,5,2)&"/"&RIGHT(Sheet1!A2,2))+1
セルA2に
=EOMONTH(DATEVALUE(LEFT(Sheet1!A2,4)&"/"&MID(Sheet1!A2,5,2)&"/"&RIGHT(Sheet1!A2,2))+1,0)
セルA3に
=A2-A1+1
「Sheet3」の
セルA1に
=Sheet2!A2+1
セルA2に
=IF((EOMONTH(Sheet2!A2+1,0)-A1+1)+Sheet2!A3>56,Sheet2!A1+55,EOMONTH(Sheet3!A1,0))
セルA3に
=A2-A1+1
「Sheet4」の
セルA1に
=IF((Sheet2!A3+Sheet3!A3)=56,"",Sheet3!A2+1)
セルA2に
=IF(A1<>"",Sheet2!A1+55,"")
セルA3に
=IF(A1<>"",A2-A1+1,"")
で、どうでしょうか?

投稿日時 - 2016-10-05 10:29:29

あなたにオススメの質問