いまさら聞けないExcelの使い方講座

【Excel】在庫が少なくなるとエクセルの管理表が教えてくれる！備品の発注を忘れないためのテクニック

トップスタジオ

2019年10月28日 06:55

いつの間にか在庫が底をついていて、毎回慌てて発注……！

　どんな職場でも、消耗品の在庫状況の管理は大切な仕事ですよね。Excelを少しずつ使いこなせるようになってきて、次の「備品在庫数管理表」のような表をExcelで作って在庫管理に役立てている人もいるのではないでしょうか。この表では、A列に備品の名称、B列には10月前半から繰り越した個数、C列～R列には在庫数の変化（在庫が減った場合はマイナス、入荷して増えた場合はプラス）が入力されています。S列には現在の在庫数（①）としてB列～R列に入力されている値の合計を求める数式が入力されています。また、T列には最低確保しておきたい在庫数（②）が入力されており、最低数を下回る前に発注するのが望ましいとされているとします。

　でも、先ほどの表をよく見ると、在庫数が最低数に近づいていたり、最低数を下回ってしまっている項目がありますよね。数式を使って在庫数は正しく管理されていても、在庫切れ間近になって慌てて発注するのではあまり在庫管理をしている意味がなくなってしまいます。こんな時、表の中で在庫が残り少なくなってきた項目に自動で色がついて目立つようになったらわかりやすいと思いませんか？

　今回は、Excelの条件付き書式を使って、在庫が残り少なくなった項目に色を付けてわかりやすくする方法を解説します。

基準となるセルの値以下になったらセルに条件付き書式で色を付ける

　では、先ほどの「備品在庫数管理表」で、S列の「在庫数」がT列の「最低数」以下になった時にS列のセルに色が付くように設定してみましょう。

　まず、条件付き書式を設定したいセル範囲（ここではセル範囲S3:S7）（①）を選択し、［ホーム］タブ（②）→［条件付き書式］（③）→［新しいルール］（④）をクリックします。

　［新しい書式ルール］ダイアログボックスが表示されます。［ルールの種類を選択してください］欄で［指定の値を含むセルだけを書式設定］（⑤）を選択し、［次のセルのみを書式設定］欄に3つ並んでいる入力欄のうち、中央の欄で［次の値以下］（⑥）を選択し、右側の欄に「=$T3」（⑦）と入力します。これで、「S列の各セルが隣のT列のセルの値以下である場合に書式設定をする」というルールが設定されました。ここまでの入力ができたら、その下の［書式］（⑧）をクリックします。

　［セルの書式設定］ダイアログボックスが表示されます。ここでは、先ほど入力した条件を満たした場合にセルに適用する色やフォントなどの書式を設定します。ここでは、条件を満たした場合のセルをオレンジ色で塗りつぶすことにしましょう。［塗りつぶし］タブ（⑨）をクリックし、［背景色］の一覧から塗りつぶしたい色（ここではオレンジ色）（⑩）を選択します。色を選択できたら、［OK］（⑪）をクリックします。

　再び［新しい書式ルール］ダイアログボックスが表示されるので、［プレビュー］欄に先ほど設定した背景色が表示されたことを確認し、［OK］（⑫）をクリックします。

　最初に選択したセル範囲に条件付き書式が設定され、T列の「最低数」以下になっているセルがオレンジ色で塗りつぶされました（⑬）。

条件付き書式を複数設定した時には注意が必要

　前項では在庫数が最低数以下になった時にセルに色が付くように設定しましたが、在庫数が最低数に近づいた時に別の色が付いてわかるようになっていると、より実用的ですよね。Excelでは同じセル範囲に対して複数の条件付き書式を設定することもできるので、このように場合によって異なる書式を適用できます。
先ほどの在庫数管理表で、S列の「在庫数」がT列の「最低数」＋5個を下回った時に、S列のセルが黄色で塗りつぶされるように設定してみましょう。前項と同様に、条件付き書式を設定したいセル範囲（ここではセル範囲S3:S7）（①）を選択し、［ホーム］タブ（②）→［条件付き書式］（③）→［新しいルール］（④）をクリックします。

　［新しい書式ルール］ダイアログボックスが表示されます。［ルールの種類を選択してください］欄では［指定の値を含むセルだけを書式設定］（⑤）を選択します。［次のセルのみを書式設定］欄では、3つ並んだ入力欄のうち中央の欄で［次の値より小さい］（⑥）を選択し、右側の欄には「=$T3+5」（⑦）を入力します。これで、S列の値がT列の値＋5個を下回ったときに書式が設定されるようになります。ここまで入力できたら、［書式］（⑧）をクリックします。

　［セルの書式設定］ダイアログボックスが表示されるので、［塗りつぶし］（⑨）タブをクリックし、［背景色］の一覧から塗りつぶしたい色（ここでは黄色）（⑩）を選択します。ここまで設定出来たら、［OK］（⑪）をクリックします。

　再び［新しい書式ルール］ダイアログボックスが表示されるので、［プレビュー］欄に先ほど指定した色が表示されたことを確認し、［OK］（⑫）をクリックします。

　すると、最低数＋5個を下回っているS列のセルが先ほど設定した色で塗りつぶされました（⑬）。しかし、在庫数が最低数を下回っているセルS6やセルS7に、前項で設定したオレンジ色が表示されなくなってしまいました。

　Excelでは、後から設定した条件付き書式の優先度が高くなります。複数の条件に当てはまる場合は優先度の高い書式のみが適用されるため、この例では上のような結果になったのです。よって、必要に応じて、適用する条件付き書式の優先度を入れ替えることが必要です。ここでは、最初に設定した条件付き書式（在庫数が最低数以下になった時にセルがオレンジ色になる）を優先させたいため、優先度を入れ替えてみましょう。条件付き書式が設定されているセル範囲（ここではセル範囲S6:S7）（⑭）を選択し、［ホーム］タブ（⑮）→［条件付き書式］（⑯）→［ルールの管理］（⑰）をクリックします。

　［条件付き書式ルールの管理］ダイアログボックスが表示され、これまでに設定した条件付き書式の一覧が表示されます。上に表示されているものほど優先度の高い条件付き書式です。では、2つの条件付き書式の優先度を入れ替えましょう。本項で設定した条件付き書式（⑱）をクリックして選択し、その上に表示されている［↓］（⑲）ボタンをクリックします。

　2つの条件付き書式の優先度が入れ替わったことを確認し、［OK］（⑳）をクリックします。

　条件付き書式の優先度が変更され、在庫数が最低数を下回った時にはセルがオレンジ色で塗りつぶされるようになりました（㉑）。

　発注して在庫数に余裕がある状態になると、条件付き書式は適用されなくなります（㉒）。

　これなら、在庫が底をつく前にExcelの表が教えてくれるので、慌てて発注するということもなくなりそうですね。