勤務表ピリオドで入力した日付の同じ行に,１が自動的に入るようにしたい! が,できなくて困ってます。

Excel 勤務表に日付を入力した時、自動的に、関数で同じ行の日付の箇所に１がたつようにしたくて試行錯誤しております。日付の入力を例えば10/16.18...と入力することになっているので、この　/　と、ピリオドを抜き出すと、ただの数字つながってしまうし..毎月手入力するのも目がチカチカして入力間違いするしで、困ってます。
よい方法を教えてください。。。。お願いします

質問者からの補足コメント

• レスありがとうございます。
  もともと、手入力用だったのだとおもいますが、これで作成するようにとの、指令なのです。

  No.1の回答に寄せられた補足コメントです。 補足日時：2020/11/23 20:38

• 

  早速、実行してみました。すごいです。感動です。
  
  そこで、問題が１つ出てきました。１０月16～31までは変わらず2桁なので、これでスムーズにできますが、
  11月が１～15までで、桁数が変わるため例えば13とC列に入力すると、１と３にも1が立ってしまいます。私なりに試行錯誤してますが、うまくいきません。
  どの様にクリアすべきかご教授お願い致します。

  No.2の回答に寄せられた補足コメントです。 補足日時：2020/11/23 20:51

No.8 ベストアンサー 回答者： goomania 回答日時： 2020/11/24 17:09

>10/16.18 の　”10/”　は必要ですか？

という、No.3さんのご指摘のとおり、この課題では”10/”は必要ないと思われますので、
No.7さんもおっしゃっている前提条件

>まず前提条件として、D1～S1セルでは16～31と、U1～AI1セルでは
>1～15の数値が記載されているとします。

だとすると

D2に
=COUNT(FIND("."&D$1&".",SUBSTITUTE(IF(D$1<16,$C2,$B2),"/",".")&"."))
という数式を記述し、合計以外のセルにこの数式をオートフィル等でコピーすればよいと思います。
D列～AJ列の2行目以下をセルの書式設定「ユーザー設定」で「#」とすることで0を表示しない設定にすればよいのではないでしょうか。

書式設定がめんどうなら、数式自体を
=IF(COUNT(FIND("."&D$1&".",SUBSTITUTE(IF(D$1<16,$C2,$B2),"/",".")&".")),1,"")
とする手もあります。

なお、シフト表を作成するタイミングにもよりますが、実務としては過去に遡って作成することないとは思われますので、B1セルには「2020/10」などと手入力することとします。
こうすることで、B1セルにはシリアル値が入力されることになります。C1セルには「=EOMONTH(B1,1)」の関数を記述します。B1、C1の両セルとも、セルの書式設定のユーザー設定で月表示「m"月"」にしておきます。
手入力にすることで、前月中に本表を作成する場合、当月前半に本表を作成する場合などいずれの場合にも対応できます。

さらにNo.7さんのご指摘のとおり、Q1～S1については条件付き書式で、その月に存在しない日は非表示にするとよいと思います。
No.7さんのC1にTODAY()関数を使用する方法から、B1に手入力する方法に変更していますので、「数式を使用して、書式設定するセルを決定」を選択し、数式を「=DAY(EOMONTH($B$1,0))<Q$1」として、「書式」をユーザー定義の書式 「;;;」としてください。

この回答へのお礼

早速のレスありがとうございます。
出来ました。すごいです。
IF(D$1<16,$C2,$B2)これが気が付かず、まだまだ未熟です。。
ありがとうございました。

お礼日時：2020/11/27 00:41

No.7 回答者： enunokokoro 回答日時： 2020/11/24 01:19

たぶん、ピリオドの区切り処理部分はこちら↓と同じですね。

https://oshiete.goo.ne.jp/qa/12009878.html
一桁でも二桁でも対応はできますが、問題は年を跨ぐ場合と月末の
日付を考慮するべきなのかや、合計の列をどうするかなども検討を
する必要があります。

No.3さんへのお礼を読むと、毎月16日から開始して翌月15日締めの
1ヶ月分のみを処理するだけのようですね。
ある意味で、万年カレンダーのようなことで対応しているようですね。
（1ヶ月分印刷したら、翌月分は同じ表で月だけ変えるような処理）

まず前提条件として、D1～S1セルでは16～31と、U1～AI1セルでは
1～15の数値が記載されているとします。

C1セルは今日の日付" =TODAY "を月のみの表示に、B1セルでは
今日の日付から先月末" =EOMONTH(TODAY(),-1) "で月表示させ、
毎月の作業に対応させます。

条件付き書式にて、P1～S1セルまでを対象に、当月末の日付よりも
大きい場合に非表示になるようにします。
　条件としての式　=DAY($B$1)<P$1
　ユーザー定義の書式　;;;
これで月末が28日より大きい日付がない場合に非表示になります。

D2セルには以下の数式を入れます。
　=IF(ISNUMBER(FIND("."&DAY(D$1)&".","."&MID(IF(D$1>=16,$B2,$C2),FIND("/",IF(D$1>=16,$B2,$C2))+1,LEN(IF(D$1>=16,$B2,$C2)))&".")),1,"")
1を出す範囲にオートフィルで設定。合計列は別途書き換えます。
ちょっと長い数式ですが、(IF(D$1>=16,$B2,$C2）で月に対応した
B列とC列の値を切り替えているだけなので、内容としては単純です。

これでB2セルなどに"10/16.18"を文字列として入力をすれば、対応
したセルのみ1が表示されます。

この回答へのお礼

丁寧なレス有難うございました。
初めての関数もあり勉強になりました。

お礼日時：2020/11/27 00:50

No.6 回答者： mike_g 回答日時： 2020/11/23 17:25

[No.5]の一部再掲

２．計算簡単化の爲に、範圍 B2:C5 の入力桁数を揃えて、
￣￣mm/dd.dd とする事にした。
３．d に書式設定し、且つ、
￣￣式 =DATE($B$1,LEFT($B$2,2),COLUMN(P1)) を入力した
￣￣セル D1 を右方に(S列迄)オートフィル。

この回答へのお礼

レスありがとうございます。
やってみます。

お礼日時：2020/11/27 00:54

No.5 回答者： mike_g 回答日時： 2020/11/23 17:21

添附圖參照(Excel 2019)

Sheet1 に於いて、
１．範圍 B2:C5 には月情報が在るので、セル B1 には西暦年を
￣￣入力し、セル C1 に式 =B1+(LEFT(C2,2)>LEFT(B2,2)) を
￣￣入力(添附圖の 2021 は表示ミスで、2020 が正しい)。
２．計算簡単化の爲に、範圍 B2:C5 の入力桁数を揃えて、
￣￣mm/dd.dd とする事にした。３．d に書式設定し、且つ、
￣￣式 =DATE($B$1,LEFT($B$2,2),COLUMN(P1)) を入力した
￣￣セル D1 を右方に(S列迄)オートフィル。
４．d に書式設定し、且つ、式
￣￣=DATE($C$1,LEFT($C$2,2),COLUMN(A1)) を入力したセル
￣￣ U1 を右方に(AI列迄)オートフィル。

Sheet2 に於いて、
５．式 =Sheet1!B1 を入力したセル B1 を右方に(AI列迄)オート
￣￣フィル
６．次式を入力したセル B2 を右隣にオートフィル
￣￣=IFERROR(DATE(Sheet1!B$1,LEFT(Sheet1!B2,2),MID(Sheet1!B2,(ROW(A$1)-1)*3+4,2)),"")
７．式 =IF(OR(D$1=$B2,D$1=$C2),1,0) を入力したセル D2
￣￣を右方にオートフィル
８．範圍 D2:AI2 を下方に必要なだけオートフィル

Sheet3 に於いて、
９．次の入力項目以外は、ステップ５～８に同じ
￣￣B2: =IFERROR(DATE(Sheet1!B$1,LEFT(Sheet1!B2,2),MID(Sheet1!B2,(ROW(A$2)-1)*3+4,2)),"")

Sheet1 に於いて、
10．条件付き書式]で數値 0 のフォント色を白に設定し、且つ、
￣￣式 =SUM(Sheet2:Sheet3!D2) を入力したセル D2 を右方に(AI列迄)オートフィル
11．式 =SUM(D2:S2) でセル T2 を上書きし、
￣￣式 =SUM(U2:AI2) をセル AJ2 に入力
12．範圍 D2:Aj2 を下方に必要なだけオートフィル

No.4 回答者： mike_g 回答日時： 2020/11/23 13:29

確認させて下さい。

勤務日は各月必ず２日ずつで、1日だけとか３日以上とかはアリエナーイと？
斯様な事は最初にキチンとねッ！

No.3 回答者： banzaiA 回答日時： 2020/11/23 09:13

添付されている表には、10月と11月しかないのですが、12月以降はどうするのですか？

C列とD列の間に12月の列を挿入するのではないですか？
そうだとすると、＃２で回答されている数式を都度訂正の必要がありそうですね。
＃１で回答されているように、表そのものを根本的に考え直した方が早いような気がします。
また、添付の表を使用するにしても、10月の列に
10/16.18 の　”10/”　は必要ですか？

この回答へのお礼

レスありがとうございます。
指定されてくる書式の入力方がこの方法なのです。
ちなみに、毎回16～15が1月のシフトとなりますので、この場合は、12月は入力しないのです。

お礼日時：2020/11/23 20:57

No.2 回答者： ずんだしらたま 回答日時： 2020/11/23 02:48

式が長いですが、こんな感じで如何でしょう？

※11月分も同じように出来ますが、参照先は11月の日付セルに置き換えてください。

この回答へのお礼

レスありがとうございます。
勉強になります。

お礼日時：2020/11/27 00:55

No.1 回答者： くんこば 回答日時： 2020/11/23 01:48

関数じゃ無理。

VBA使う手もあるが、それよりも構造から変えた方がいい。
まぁどちらにしても、素人が人に聞いて作れるもんじゃない。
誰かに作ってもらうか、仕組みを一から考え直すしかない。

この回答へのお礼

レスありがとございます。
指定の書式なので仕方なく作成してますが、私も、同感です。

お礼日時：2020/11/23 22:04