ご訪問ありがとうございます。
Googleシートマスターのひろしです。
前回
年と月の入力で切り替わる万年カレンダーを月曜から始まるようにする(Gスプレッドシート)
technical.verybestcbp.com
今回は、とっても価値の高い方法をお伝えします。
これを知ることであなたは、
- COUNTIF,DATE,EOMONTH,SEQUENCE,INDIRECT,Arrayformula関数等の基本的な使い方がわかります。
- 動画に沿ってやることでシフト表が出来上がります。
- この動画一本でかなりスキルアップできます。
なので、サクッとマスターして
というわけで、動画を見ながらじっくり取り組んでください
こちらの動画↓を参考にGoogleスプレッドシート版を作成しました。
動画はこちら
シートはこちら
*クリックすることでシートが開きます。(コピーしてお使いください)
ポイント
テスト的な人名の作成
姓と名で分ける
SPLIT関数を使用して区切り文字にスペースを指定しましょう。
SPLIT(テキスト, 区切り文字, [各文字での分割], [空のテキストを削除])
月末日の表示
EOMONTH関数を使用します。月は同じ月なので0を指定します。
EOMONTH(開始日, 月)
日付の表示
月によって表示する個数が異なるのでSEQUENCE関数を使用すると便利です。
SEQUENCE(行数, [列数], [開始値], [増分量])
行数は1、列数がその月の日数なので月末ー月初あるいは、EOMONTHをTEXT関数の日付を表示
text(SEQUENCE(1,text(F3,“dd”),F2),“ddd”)することで取得できます。例) text(F3,“dd”)
開始値は月初のF2を指定すればあとは、自動的にその月の日付が表示されます。
→選択後に削除(DELETE)しています。
曜日の表示
TEXT関数の”ddd”を使用します。ただ、D6の値をTEXT関数でくくっても1日めの曜日しか
表示されません。
text(SEQUENCE(1,text(F3,“dd”),F2),“ddd”)
そこでArrayformulaします。そうすれば表示されます。
SEQUENCE関数とArrayformulaは相性がいいですね。
祝日の追加
月と日別の表で土日祝日に色を自動で付ける(Excelとの違い)
technical.verybestcbp.com
条件付き書式には名前付き範囲をINDIRECTします。
=countif( INDIRECT("祝日"),D$6)=1
その他メモ
| 20名分のエリア | オートフィル |
| 人の名前 | split |
| 年、月入力エリア | 色を付ける |
| 月初日、 | DATE |
| 月末日の表示 | EOMONTH |
| 日付の入力 | SEQUENCE |
| 幅の変更、列の追加 | 40 |
| セルの結合、表示形式の変更 | |
| 月ごとの日付にする | 月末ー月初 |
| 曜日の表示 | Arrayformula |
| 曜日に色を付ける | |
| 休の日の背景色 | |
| 祝日の追加 | |
| 祝日を追加 |
=countif( INDIRECT(“祝日”),D$6)=1
|
| 行・列の固定 | |
| 人数の調整 |
朝、昼、夜、深夜、休
|
| 5:00 | |
| リストから選択できるようにする | ABCD休 |
| サンプルをコピペする | |
| 出勤数 | |
| 一ヶ月の総日数 | |
| B28 朝、昼、 | TRANSPOSE |
| 必要人数チェック | |
| 条件付き書式の延長 | |
| COUNTIF(範囲,0,”◯”,”✕” | |
| すべて0の日をクリアする |
=COUNTIF(AF$28:AF$31,0)=4
|
| 希望休暇日欄を作成する | AJ6-AN6 |
| 行挿入します。 | |
| 休暇希望日 | |
| 休み希望日入力 | |
| 入力欄に色を付ける | |
| 希望日をテスト的に入力する |
RANDBETWEEN
|
| 値のコピペ | |
| 日付データにする | |
| オートフィルでもいいが | Arrayformula |
| 表示を日付のみにする | |
| 入力がない場合は、空白にする | |
| 入力制限の解除 |
警告を表示にする
|
| 一旦クリア | |
| 休の日なら休 |
=if(countif($AJ8:$AN8,D$6)=1,”休”)
|
| 乱数を発生させる |
RANDBETWEEN
|
| 判別 | SWITCH |
|
=SWITCH( RANDBETWEEN(1,5),1,”A”,2,”B”,3,”C”,4,”D”,5,”休”)
|
|
| 希望の休に色を付ける |
=countif($AJ8:$AN8,D$6)>0
|
| 29日以降は空の判別を追加する |
=if(AF6=””,””, if(countif($AJ8:$AN8,AF$6)>=1,”休”,SWITCH( RANDBETWEEN(1,5),1,$O$2,2,$P$2,3,$Q$2,4,$R$2,5,$S$2)))
|
| 希望休暇日を非表示に | |
| DELキーで変わらないことを確認 | |
| センタリング、境界線を太くする | |
| シートをコピーしマスターにする | |
| 値を貼り付け | SHIFT+Ctrl+V |
| マスターからコピー | |
| 年を参照する | =’マスタ’!E1 |
| 月をシート名から取得する | |
| 注意 | すばやくやる |
| 参考 シートのコピーとシート名の変更 |
最後までご覧いただきありがとうございます。
つぎはこちら
セルの色が自動的に変わるガントチャートを作成する(Gスプレッドシート)
technical.verybestcbp.com
参考動画
