エクセル関数を使うと、日付に祝日を表示させることができます。例えば、カレンダーを作成するときに、祝日が表示されていると便利です。一目で何日が、何の祝日だか分かります。この方法を使うと、簡単に、自動で日付に祝日を表示させることができます。それには、まず祝日一覧表を作成します。カレンダーにはTEXT関数で曜日を表示させます。そこまでできたら、VLOOKUP関数を使って、日付に祝日を表示させるだけです。手順通り進めると、簡単に、自動で、日付に祝日を表示させる表を、完成することができます。
目次
日付に祝日を表示させる手順
次の手順で進めていくと、自動で、日付に祝日を表示させるカレンダーが作成できます。
1．祝日一覧表を作成
2．祝日一覧の表の名前を定義する
３．TEXT関数を使って日付に曜日を表示させる
４．エクセルVLOOKUP関数を使って、日付に祝日を表示
ファイル名： 「2018年カレンダー」
シート名 ： 「カレンダー」、「祝日一覧表」
祝日一覧表を作成
エクセル関数を使って、日付に祝日を表示させる式を作成する前に、下準備をします。
① 「2018年カレンダー」ファイルに、「カレンダー」と「祝日一覧表」の、２つのシートを作成しましょう。
② 「2カレンダー」シートに、2018年のカレンダーを作成していきます。
③ 「祝日一覧表」シートに、2018年の祝日の一覧表を作成します。下の表の、祝日の一覧表を作成しましょう。
祝日一覧の表の名前を定義する
次に、祝日一覧の表の名前を定義します。「名前の定義」から、「祝日一覧表」と名前を付けましょう。
名前の定義
「数式」タブ→「定義された名前」グループ→「名前の定義」→「名前の定義(D)」
１．「祝日一覧表」シートの、セルの範囲「A1:B21」を選択しましょう。
２．名前の定義▼をクリックして、「名前の定義(D)」を選択します。
３．「新しい名前」ダイアログボックスが開くので、名前に「祝日一覧表」と入力します。
４．参照範囲は、「=祝日一覧表!$A$1:$B$21」となっていることを確認して、「OK」ボタンを押しましょう。
５．「A1:B21」に、「祝日一覧表」と名前が付きました。
こちらの記事もご覧ください。⇒エクセル関数を使って日付の曜日を表示！特定の曜日だけ表示させる
名前の定義は「名前ボックス」からでも設定できます。
１．「名前ボックス」は、数式バーの左にあるボックスです。
２．名前をつけたいセルの範囲「A1:B21」を選択して、「名前ボックス」に名前を入力します。
３．「祝日一覧表」と入力して、「Enter」キーで確定させます。
４．「A1:B21」に、「祝日一覧表」と名前が付きました。
次は、TEXT関数を使って、日付に曜日を表示させましょう。
エクセルTEXT関数を使って日付に曜日を表示させる
「カレンダー」シートに、表を作成します。
A列～C列は、関数を使わずに入力できる項目ですので、あらかじめ入力して完成させておきましょう。
D列とE列は、空欄のままです。D列に、TEXT関数を使って曜日を表示させます。
D2に、1月1日の、曜日を表示させる式を入力しましょう。
D2 =TEXT(C2,”aaa”)
D2に、「月」が表示されます。
D2の式をドラッグして、「D3:D16」にコピーしましょう。
すると、曜日が表示されました！
これで、VLOOKUP関数が使えるように下準備できました！
完了です。
ここから、VLOOKUP関数を使って、日付に祝日を表示させていきましょう。
エクセルVLOOKUP関数を使って、日付に祝日を表示させる
E2に、祝日を表示させる式を入力しましょう。
E2 =VLOOKUP(C2,祝日一覧表,2,FALSE)
E2には、「元日」が表示されます。
範囲には、名前の定義をした「祝日一覧表」と入力します。
「祝日一覧表」と入力することによって、範囲に、名前の定義の参照範囲、「祝日一覧表!$A$1:$B$21」が、入力されたことと同じになります。
名前の定義をしていない場合は、下の式になります。
E2 =VLOOKUP(C2,祝日一覧表!$A$1:$B$21,2,FALSE)
ですが、上のE2の式では、祝日でない日付は、エラー値が返されてしまいます！
そこで、エラーを表示させないようにIFERROR関数を使います。
E2 =IFERROR(VLOOKUP(C2,祝日一覧表,2,FALSE),””)
この式で、IFERROR関数でエラーになった場合は、空白が返されます。
祝日でない日は空白になります。
E2の式をドラッグして、下にコピーしましょう。
2018年度のカレンダーが完成です！
お疲れ様でした。
エクセルTEXT関数
TEXT関数は、表示形式コードを使用して、数値の表示方法を変更することができます。数値を読み取りやすい表示に変更できる、便利な関数です。
TEXT関数の書式と引数
=TEXT(数値, 表示形式)
| 数値 | 数値、数式、または数値を含むセルの参照を指定します。 |
| 表示形式 | 数値書式を、”yy/mm/dd” など、引用符で囲んだテキスト文字列として指定します。 |
TEXT関数の使用例
エクセルTEXT関数で、曜日を表示させる例です。
B2 =TEXT(A2,”aaa”)
B3 =TEXT(A3,”aaaa”)
B4 =TEXT(A4,”ddd”)
B5 =TEXT(A5,”dddd”)
B6 =TEXT(A6,”m月d日(aaa)”)
B2～B6に上の式を入力します。
表の通り、曜日が表示されます。
こちらの記事もご覧ください。⇒エクセル関数で日付から年だけ、月だけ、日だけ抽出（変換）！YEAR・MONTH・DAY関数を使用
エクセルの表示形式
| 表示形式 | 表示例 |
| aaa | 金 |
| aaaa | 金曜日 |
| (aaa) | (金) |
| ddd | Fri |
| dddd | Friday |
エクセルVLOOKUP関数
エクセルVLOOKUP関数は、指定した範囲の１列目で数値や文字列などを検索し、指定した列と同じ行にある値を返します。
VLOOKUP関数の書式と引数
=VLOOKUP(検索値, 範囲, 列番号,[ 検索の型])
| 検索値 | 検策値となるセルを指定します。 |
| 範囲 | 検索する範囲を指定します。その場合、検索する列を範囲の一番左にします。検索の型にTRUEを指定、または省略した場合、正確な値を返すために範囲の左端の列を昇順で並び替えておく必要があります。 |
| 列番号 | 範囲の先頭列から数えた、列番号を指定します。 |
| 検索の型 | FALSEまたは”０”を指定すると、一致する値のみ返されます。一致する値が見つからない場合は、エラー値#N/A が返されます。TLUEまたは”１”を指定すると完全一致の値、または近似値が返されます。省略した場合もTRUEになります。完全一致の値が見つからないと、検索値未満の最大値が返されます。 |
VLOOKUP関数の使用例
F2に、範囲「A2:C6」より店舗で検索し、「今月売上」を返す式を入力しましょう。
F2 =VLOOKUP(E2,$A$2:$C$6,2,FALSE)
「635,000」が返されます。
G2に、範囲「A2:C6」より店舗で検索し、「先月売上」を返す式を入力しましょう。
G2 =VLOOKUP(E2,$A$2:$C$6,3,FALSE)
「 608,100」が返されます。
「F2:G2」を選択して、そのまま下にドラッグしましょう。
式を「F3:G6」にコピーします。
「今月売上」と「先月売上」が表示されました！
エクセルIFERROR関数
数式がエラーの場合、指定した値を返します。それ以外は数式を返します。
IFERROR関数の書式と引数
IFERROR(値, エラーの場合の値)
| 値 | エラーかどうかチェックする値を指定します。 |
| エラーの場合の値 | 数式がエラーと判断された場合に返す値を指定します。 |
IFERROR関数の使用例
D2に、「B2*C2」がエラーの場合、「エラー」を返す式を入力しましょう。
D2 =IFERROR(B2*C2,”エラー”)
「B2*C2」の数式の結果は「エラー」にはなりません。
D2には、「B2*C2」の数式の結果、「1,320」が返されます。
D3に、「B3*C3」がエラーの場合「エラー」を返す式を入力しましょう。
D3 =IFERROR(B3*C3,”エラー”)
エラー値「#VALUE!」になるので、「エラー」が表示されます。
エクセル関数を使って日付に祝日を表示させる時の注意
エクセル関数を使って日付に祝日を表示させる方法を説明しましたが、関数を使って日付に祝日を表示させる場合、その年の祝日一覧表を作成する必要があります。年によって日が変わる祝日もあるので、正確なデータをそろえましょう。
こちらの記事もご覧ください。⇒エクセル関数を使って日付の曜日に自動的に色を付ける方法
