役立つ! 総務マガジン

Excelでの表記揺れをリストと関数で防ぐ!

備品管理表を誰でも正確に入力できるようにしよう

一つの備品管理表を複数人で、しかも総務以外の部門の社員も記入するケースでは、備品や消耗品などの品名や記入者の部署名が統一されていないと、発注の作業や利用傾向の把握がうまくできません。誰でも正確にデータを入力できる仕組みを作っておきましょう。

[2018年 9月26日公開]

誰が入力しても表記揺れが発生しない表を作る

総務の仕事には、社員全員が滞りなく業務を行えるように配慮することも含まれます。備品や消耗品の管理もその一つ。適切な在庫を確保するため、毎月「何が幾つ減っているのか」「どの部署がどれほど使っているのか」といった情報を集めておきたいところです。それには備品管理表のExcelファイルを用意し、社員に記入してもらう方法があるでしょう。

とはいえ、急いでいる社員が品名をうろ覚えで入力したり、部署名を略称で入力したりするのもよくあること。それでは集計が大変になるので、Excelの機能や関数を活用し、誰でも正確に入力できる仕組みを作っておきましょう。備品管理表の表記揺れだけでなく、お互いのストレスも減らすことができますよ。

部署名を選択式のドロップダウンリストに

Excelの標準機能である「データの入力規則」の一つ、ドロップダウンリストを使えば、部署名のように決まった種類のデータをリストから選択するだけで入力できるようになります。マウス操作のみでいいことや、リストにないデータを入力不可にできることから、少ない操作手順で正確に部署名を入力できるようになります。

(1)[データの入力規則]ダイアログボックスを表示する

備品管理表の「部署」列を選択し、[データ]タブの[データツール]グループにある[データの入力規則]ボタンをクリックします。

(2)[入力値の種類]で[リスト]を選択する

[データの入力規則]ダイアログボックスが表示されました。[設定]タブの[入力値の種類]ボックスから[リスト]を選択します。

(3)[データの入力規則]ダイアログボックスを折り畳む

[元の値]を設定するボックスが表示されるので、[↑]ボタンをクリックして[データの入力規則]ダイアログボックスを折り畳みます。ここではドロップダウンリストの項目を別のシートに用意してあるので、[リスト]シートを表示します。

(4)ドロップダウンリストのデータがあるセル範囲を設定する

あらかじめ作成しておいた「部署名リスト」のデータが入っているセルを選択し、[↓]ボタンをクリックしてセル範囲を指定します。[Enter]キーを押してもOKです。

(5)[データの入力規則]ダイアログボックスでの設定を完了する

折り畳まれていた[データの入力規則]ダイアログボックスが元のサイズに戻りました。[OK]をクリックして設定を完了します。

(6)ドロップダウンリストの内容を確認する

「部署」列にドロップダウンリストが設定され、セルに[▼]ボタンが表示されました。

[▼]ボタンをクリックするとドロップダウンリストが表示されます。この中の項目を選択するだけで、正確な部署名を入力できるようになりました。

VLOOKUP関数を組み合わせて発注も楽にする

備品管理表では、なくなりそうな消耗品をすぐに業者に発注できるよう、品名に対応した「品番」まで管理できるのが理想的です。しかし、社員に品番まで記入してもらうのは気が引けますし、そもそも正確な入力は期待できません。

そこで、ドロップダウンリストで選択した品名を基に、その消耗品の品番を取り出せる仕組みを「VLOOKUP(ブイルックアップ)関数」で作ってみましょう。

VLOOKUP関数は、指定したデータに対応する値を別の表から取り出すことができる関数で、縦方向(Vertical)にデータを検索していきます。必要な引数は四つあり、どのデータを検索するかを設定する[検索値]、検索対象となる表を設定する[範囲]、取り出したい値の位置を設定する[列番号]、そして検索方法を定義した[検索の型]となっています。

ただ、VLOOKUP関数は検索値に何も入力されていないとエラーが表示されてしまい、少々格好が悪くなります。IF関数でそれを回避する方法もお教えしましょう。

VLOOKUP関数の構文
VLOOKUP(検索値, 範囲, 列番号, 検索の型)
IF関数の構文
IF(条件, 真の場合, 偽の場合)

(1)「品名」列にドロップダウンリストを設定する

「品番」を検索する元となる「品名」列に、ドロップダウンリストを設定します。先ほどの手順と同じように、[データの入力規則]ダイアログボックスで設定を行いましょう。

(2)VLOOKUP関数を設定する

「品番」を表示したいセルにVLOOKUP関数を設定していきます。セルB5をクリックし、[fx]ボタンからVLOOKUP関数を挿入しましょう。[検索値]にセルC5を設定し、[範囲]には「リスト」シートに作成しておいた品名・品番の対応表を絶対参照で設定します。

ここでは品番が表の2列目に記載されているので、[列番号]には「2」と入力します。最後の引数[検索の型]は、検索値と完全に一致するデータだけを探す「FALSE」に設定しておきます。

VLOOKUP関数が設定されましたが、検索値となる「品名」のセルに何も入力されていないため、現時点ではエラーが表示されてしまいました。

(3)IF関数でエラー値が表示されないように設定する

VLOOKUP関数を設定したセルを編集し、数式バーでIF関数を次のように追加します。

=IF(C5="","",VLOOKUP(C5,リスト!$E$3:$F$37,2,FALSE))

これは、「もしセルC5(品名)が空白なら空白を表示する。そうでなければVLOOKUPの計算結果を表示する」という意味です。

IF関数の働きにより、VLOOKUP関数のエラーが消えました。

(4)品名に対応する品番を表示できた

ドロップダウンリストから「品名」を選択すると、VLOOKUP関数の働きによって「品番」が表示されるようになりました。

Excelの機能と関数で、人に優しい仕組みづくりを

総務としては、書類やファイルへの記入は常に社内ルールに従ってほしいと思うものです。しかし、事業部門の社員は現場の業務で忙しく、それが通じないこともあるでしょう。「社内のことはなるべく総務など管理部門に任せたい」という現場の本音を想定し、それに沿った仕組みにしておけば、お互いに気持ちよく業務に臨めます。

今回はドロップダウンリストとVLOOKUP関数、IF関数を使った備品管理表を例にしましたが、こうしたテクニックは請求書や見積書など、さまざまな分野で応用できます。ぜひ思い出して使ってみてください。

協力メディア

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

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

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

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

Excel研修の詳細情報を見る

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

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

Office 365 について見る

おすすめの記事