VLOOKUP関数と条件付き書式を使って「表」を時間帯に分類する

VLOOKUP関数を使って「時間帯を表示」し、さらに条件付き書式を使えば、表を「時間帯別に色分け」することができます

　「時間」のフィールド（項目）がある「表」で、時間帯別に色分けすればとても表が見やすくなります。

　数式で「時間帯」を認識させるには、VLOOKUP関数の「近似値」のオプションを使えば簡単にできます。

　VLOOKUP関数の「近似値」では 「６時～７時５９分は "A時間帯"  」と判別してくれます。

　　　

　しかし、条件付き書式を使って、「時刻（時間帯）」を条件式にするときは注意が必要です。

ヒヤリハットの記録表に「時間帯」を表示し、さらに「時間帯別に色分け」しました

　「時間」を元にVLOOKUP関数を使って、「時間帯」のフィールドを作成し「A～G」の時間帯を表示させ、その「時間帯」を元に条件付き書式の新しいルールを作成し時間帯別に色を付けます。

「A～G」の時間帯別に「色」を付けています

　　　　

 
  VLOOKUP関数を使って「時間帯」を表示します

　VLOOKUP関数の「近似値」のオプションを使って、時間帯別に分ける数式を作成します。

　条件付き書式の条件を作成する際の「基準」にするために、時間帯別に記号を付けます。

表に「名前」を設定します

1. 表全体を範囲選択します
2. 「数式」タブを選択
3. 「選択範囲から作成」をクリック
4. 「選択範囲から名前…」で「上端行」にチェックを入れる

　　　　

時間帯一覧の表に「名前」を付けます

　フィールドごとに名前を付けておくことによって、条件式の設定が簡単になり、またどんな設定をしたかが分かり易くなります。

　　　　

セルに「名前」を付けると何が便利になるのか（その１） - Excelの機能を活用して、事務作業の省力化や経営分析をする

名前が設定されました

　名前ボックスに、フィールド名で「範囲」が表示されます。

　　　　

VLOOKUP関数を使って「時間帯」を表示させます 

1. 数式を入力するセルを選択します
2. 検索値の欄で「F３キー」を押します
3. 名前の貼り付けボックスが表示されるので「時間」を選択し入力します
4. 範囲の欄で同じく「F３キー」を押して、「時間帯一覧」を選択し入力します
5. 列番号「２」と入力します
6. 検索方法で「true」と入力します

　　　※ 「true」を選択すると、上から下へ次の値までの近似値が検索されます

　　　※ 「FALSE」を選択すると「完全一致」を検索します

　　　※ F３キーを押しても「名前の貼り付けボックス」が表示されない場合は、手入力します。

　　　　

「時間帯」を表示する数式を下方向にコピーします

　選択したセルの右下に表示される「フィルハンドル」をダブルクリックすることで、そのセルに入力されている数式（内容）が下方向にコピーされます。

　フィルハンドルをダブルクリックすることで、数式が下方向にコピーされました。

　　　　

　VLOOKUP関数には「TRUE（近似値）」のオプションがあるので、「時間帯」に簡単に対応できます。

条件付き書式で、「時刻を条件式」として設定する時は注意が必要です。

　時刻を条件式として設定する時には、時刻をダブルクォーテーション「  "  」でくくる必要があります。

　ダブルクォーテーションでくくられた「時刻」は文字列として認識されるので、比較する時刻（シリアル値）と正しく比較することができません。

　そこで、ダブルクォーテーションでくくられた「時刻」に「 ＊１」と「１を乗じる」することでシリアル値と認識してくれるようになります。

　　　数式「 ＝ "9:00" ＊ 1  」

条件付き書式を設定する

　条件付き書式の「新しいルール」で、数式を使って条件付き書式を作成します。

1. 条件付き書式を設定する範囲を選択します
2. 「ホーム」タブを選択
3. 「条件付き書式」をクリック
4. 開いたボックスから「新しいルール」をクリック

　　　　

条件を設定します

　比較するセルを「複合参照」で「列を固定」することと、ダブルクォーテーションでくくった「時刻」に「 ＊１」と１を乗ずることで「シリアル数値に変換」する点に注意する。

1. 新しい書式ルールのボックスで「数式を使用して、書式設定するセルを決定」を選択
2. 次の数式を満たす場合に･･･に、数式を入力「  = $C3 >= "6:00" ＊1  」
3. 書式に塗りつぶしの色を設定

　　　　

その他の「時間帯」にも条件を設定します

　B～Gの時間帯についても同様の方法で条件付き書式を作成します。

　　　　

VLOOKUP関数で「時間帯」を表示し、条件付き書式で「色分け」されました

　VLOOKUP関数で表示した「時間帯」を条件付き書式の「条件」にして、表が時間帯別に色分けされました。

　　　　　

　このように集計表に「時刻の要素」がある時は、それを関数を使って「時間帯で記号表示」したり、条件付き書式を使って「色分け」することでとても判別しやすい表になります。 

　このように「時刻」を使って数式を作成する際には、Excel上では「時刻がシリアル値として扱われる」ため、時刻を条件式に用いる際にはダブルクォーテーション「 " 」でくくって文字列扱いにする必要があります。

　そのため、そのまま「表の時刻と対比」させると「シリアル値と文字列の比較」になるため比較結果が正常値になりません。

　そこで、ダブルクォーテーション「 " 」）で囲んだ「時刻」に1を乗じて（＊１）シリアル値に変更することにより結果が正常値になります。

　この方法は、Excelで勤怠管理をする時にも必要なテクニックになります。

k-ohmori9616.hatenablog.com