役立つ! 総務マガジン

Excel関数と条件付き書式で大事な情報を自動強調

名簿の中で条件を満たす人だけ色を変えて表示する

一覧表を作るとき、ある条件に合うデータだけセルの背景色を変えて目立たせたい、というケースはよくあります。このとき、手作業では手間が掛かり、ミスも起きがちですが、関数と条件付き書式を組み合わせることで、条件の判定と背景色の変更を自動的に行うことができます。

[2019年 4月22日公開]

社員名簿で勤続10年目の社員がすぐに分かるようにしたい

条件に合うデータだけ背景色を変える場合の例として、今回は「社員名簿の中で、今年勤続10年目になる社員の行だけ背景色を変えて目立たせる」という操作を行います。勤続年数の区切りに表彰や記念品の贈呈などを行っている企業も多いでしょう。そこで、社員の中で今年度は誰が該当するのかを、名簿を見たときにすぐに分かるようにします。

社員名簿に各社員の入社日を入力しておき、まず、一人一人の年度末時点での勤続年数を関数で計算します。そして、その結果が「10」の社員は、行の色を変えるようにします。

DATEDIF関数で勤続年数を求める

勤続年数を求めるには、社員の入社日と、その社員が現在勤務している年度の最終日の差を計算します。この計算には、第5回でも取り上げた、開始日と終了日の差を求める「DATEDIF」(デートディフ)関数を使います。

DATEDIF関数の構文
DATEDIF(開始日,終了日,単位)
引数「単位」で指定する値
“Y” 満年数
“M” 満月数
“D” 満日数
“YM” 1年に満たない月
“YD” 1年に満たない日

開始日には各社員の入社日、終了日には年度末の日付を指定し、単位は「Y」(満年数)を指定することで、勤続年数を計算できます。以下の手順では、年度末の日を名簿の欄外にあるセルL4に入力し、DATEDIF関数の最終日として参照しています。

(1)勤続年数を計算するためDATEDIF関数を入力する

名簿に「勤続年数」の列(J列)、セルL4に年度末の日付を入力したセルを設けて、関数を入力していきます。DATEDIF関数は[fx](関数の挿入)ボタンなどから入力できないため、セルや数式バーに手入力します。B列が入社日なので、セルJ4に入力する式は「=DATEDIF(B4,$L$4,"Y")」です。

(2)勤続年数が求められた

関数の計算結果が表示され、勤続年数が求められました。

(3)関数を全員分コピーする

フィルハンドルをダブルクリックして、入力した数式を名簿の最後までコピーします。

条件付き書式で行全体の書式を設定する

各社員の勤続年数を求め終わったら、続いて条件付き書式を設定します。通常の条件付き書式では対象のセルのみに書式が適用されますが、ここでは、対象のセルの値に基づいて、そのセルがある行全体の書式を変更するようにします。

条件付き書式でよく使う[セルの強調表示ルール]では、対象のセル自身の値に基づき書式を設定することしかできません。しかし、セル範囲を選択して[新しいルール]から設定することで、ほかのセルの値に基づいて書式を設定できるようになります。

(1)条件付き書式を設定したい表全体を選択する

条件付き書式を設定したい表を範囲選択します。このとき、タイトル行を含めないように注意してください。

(2)条件付き書式の設定を開始する

[ホーム]タブの[条件付き書式]ボタン→[新しいルール]の順にクリックします。

(3)条件を設定する

[新しい書式ルール]ダイアログボックスが表示されました。[ルールの種類を選択してください]から[数式を使用して、書式設定するセルを決定]を選び、[次の数式を満たす場合に値を書式設定]のテキストボックスに「=$J4=10」と入力します。続けて[書式]ボタンをクリックします。

ここでは条件を判定する基準となるセルを「$J4」と、列番号を固定した複合参照にしているのがポイントです。同じ行のセルは全てJ列の値によって条件が判定されるため、行単位で条件付き書式が設定されるようになります。

(4)書式を設定する

[セルの書式設定]ダイアログボックスが表示されたら[塗りつぶし]タブをクリックし、[背景色]から設定したい色を選択します。その後[OK]ボタンをクリックします。

(5)設定を適用する

[新しい書式ルール]ダイアログボックスに戻ったら[OK]ボタンをクリックし、条件付き書式の設定を完了します。

(6)条件付き書式が適用された

条件付き書式の設定が完了しました。勤続年数が「10」である社員の行の、セルの背景色が変わったことを確認しましょう。

応用範囲が広い条件付き書式で分かりやすい表に

条件付き書式は便利な機能ですが、複雑な条件の場合は設定が難しいため、結局手動で作業していた、という方も多いのではないでしょうか。今回のように関数と条件付き書式を組み合わせることで、複雑な条件による行単位の書式設定を自動的に行うことができると分かりました。

こうした条件付き書式の応用範囲は広く、顧客情報の管理で「会員リストの中で5年目の会員だけ強調表示する」といったことも簡単にできます。もちろん条件に設定できるデータは期間だけではないので、営業所ごとの光熱費のデータを集計して、前月の光熱費と前年度の同月の光熱費を比較したうえで「光熱費が前年度の同月を5%以上超過した営業所を強調表示する」といったことも可能です。条件付き書式をうまく活用して、重要な情報が一目で伝わる表を作りましょう。

協力メディア

できるネット(https://dekiru.net)

もっと関数のスキルを磨きたい! という人はぜひ大塚商会のExcel教室へ

大塚商会では、企業の人材育成をトータルにサポートする「人材育成支援サービス」をご提供しています。ITやCAD、ビジネススキルなどさまざまなジャンルをご用意しており、もちろんExcelを学ぶコースも充実しています。

Excelを基礎から応用まで体系的に学べる「標準コース」や、関数やマクロなど目的に応じて学べる「目的別コース」をご用意しています。大塚商会の研修施設で定期開催されているオープンコースのほか、貴社の業務に合わせてカスタマイズした企業研修(ご来場または出張)も可能です。

Excel研修の詳細情報を見る

Excelをもっと便利に活用するにはOffice 365をおすすめします

マイクロソフトの「Office 365」は、ExcelやPowerPointをはじめとしたOffice製品、メール、スケジュール管理などをクラウド上で使用できます。非常に利便性の高いシステムを実現することが可能です。

Office 365 について見る