Excelワザ_予定表の作成(土日祝日に色を付ける)
★使用する関数
1.=DATE(年,月,日)
DATE関数の引数(カッコ内)に、「年」、「月」、「日」を指定すると、その値がシリアル値に変換されます。
エクセルでは、DATE(デイト)関数を使って、日付を求めることができます。
エクセルの日付は「シリアル値」という値で管理されています。シリアル値は、1900年1月1日を「1」として、そこから日付が増えるごとにシリアル値も1ずつ増えていきます。
シリアル値の例:
1900年1月2日のシリアル値は「2」
2018年4月1日のシリアル値は「43191」(1900年1月1日から4万日以上がたっているということになります。)
エクセルでは、1つのセルに「2018/4/1」と入力すると、それが自動的に日付と認識され、シリアル値に変換されます。ところが、セルを分けて「2018」、「4」、「1」と入力すると、それぞれ単独の数字として認識されてしまいます。
そこで、DATE関数を利用して、各セルに分断されている数字を日付のシリアル値に変更します。
2.=TEXT(値,表示形式)
「値」として指定された数値を、表示形式付けて変換した結果が返されます。
3.=WEEKDAY(シリアル値,種類)
WEEKDAY関数は、シリアル値に対応した「曜日の数字」を、以下の種類のように表示します。種類は1~3で指定しますが、指定する種類によって、「戻り値の最初の数字や対応する曜日」が違います。
種類の例:
1(省略) | 1:日曜日 | 2:月曜日 | 3:火曜日 | 4:水曜日 | 5:木曜日 | 6:金曜日 | 7:土曜日 |
2の場合 | 1:月曜日 | 2:火曜日 | 3:水曜日 | 4:木曜日 | 5:金曜日 | 6:土曜日 | 7:日曜日 |
3の場合 | 0:月曜日 | 1:火曜日 | 2:水曜日 | 3:木曜日 | 4:金曜日 | 5:土曜日 | 6:日曜日 |
4.=INDIRECT(参照文字列) 参照文字列をもとにセルを間接参照する
セル参照を表す文字列を利用して、そのセルの参照を求めます。戻り値として返されるのは、参照先のセルの値ではなく、セル参照であることに注意してください。<手順>
1.以下のように入力しましょう。
2.セルA1にデータの入力規則→設定→リスト→2018~2022までカンマで区切って入力。
|
セルA2 |
=DATE(A1,A2,1)と入力。
6.セルの書式設定→ユーザー定義で「d”日”」にします。
→「1日」と表示される。7.セルA5に =A4+1と入力します。
オートフィルでA34まで式をコピーします。
8.セルB4にTEXT関数
★土曜日に水色、日曜日に黄色の色をつけましょう。
9.セル範囲A4:C34まで選択します。
10.ホームタブ→スタイルグループ→条件付書式→新しいルール→数式を使用して…
=WEEKDAY($A4,1)=7……セルの塗りつぶしを水色
=WEEKDAY($A4,1)=1……セルの塗りつぶしを黄色
例)=WEEKDAY(2018/1/1)→月曜日なので「2」。
土曜日は水色、日曜日は黄色で塗りつぶしができます。
★さらに、祝日にピンクの色を付けましょう。
11.範囲に名前を付けます。
(Web上の祝日一覧の表を利用しました。)
祝日シートのセルA3:A22→選択
→名前ボックスで「祝日2018」と入力し、Enter2回(名前の1文字目は数字はだめです。)
同様に、「祝日2019」「祝日2020」「祝日2021」「祝日2022」を名前定義。
12.条件付書式を追加します。
セル範囲A4:C34を選択し、
→条件付き書式
→ルールの管理
→新規ルール
→数式を使用して…
=COUNTIF(INDIRECT($D$1),$A4)>=1
INDIRECT関数で返されるセル参照範囲(祝日&2018~2022)の中で、A列に入力されている数字が
「1」以上だったら、書式の色を付ける。
by school-pc | 2018-07-19 11:30 | Comments(0)