041 大量のリスト入力は2段階で絞り込む

　データの入力規則機能を使えば、プルダウン形式のリストから入力させることができますが、全社員名など項目が多い場合にはかえって探すのに時間がかかってしまいます。部署名を選択すると、該当部署の社員名のみが表示されるリストを作っておけば効率的。
　セル範囲に名前が付けられる「名前の定義」機能 とINDIRECT関数を組み合わせて2段階のリストを作成します。F1セルで部署名を選択するとF2セルで担当者名のリストが現れるよう表を作成しておきます。

　1. 部署ごとの社員リストを作る。セルに名前が入力されている場合は、そのセル(A1セル)を選択した状態で「数式」タブ→「名前の定義」をクリック(名前欄に自動で「北日本営業部」と入力される)
　
　2.「参照範囲」の欄を一度すべて削除し、マウスで「A2:A5」のセル範囲

　3.他の営業部も同様に「名前の定義」をする
　4. F1セルを選択し、「データ」タブ→「データの入力規則」をクリック。「入力値の種類」で「リスト 」を選択し、「元の値」ボックスをクリックし、「A1:A3」を選択してOKをクリック
　5. F2セルを選択し、「データの入力規則」「入力値の種類」で「リスト」を選択、「元の値」ボックスに「=INDIRECT($F$1)」と入力して「OK」をクリック(F1セルの絶対参照はつけなくても可)を選択して「OK」をクリック

6 .「元の値はエラーと判断されます。...... 」とアラートが表示されるので「はい」をクリック

7. F1セルで部署名を選択すると、F2セルでその部署の担当者名が選択できる

「名前の定義」をしても、画面表示は何も変わらないので実感が少ないかもしれませんが、付けた名前は、名前が付けられたセル範囲を選択したときに「名前ボックス」に表示されます。逆に、「名前ボックス」に付けた名前を入力すると、その範囲が選択されます。

「数式」タブ→「名前の管理」でも確認ができます 。


名前ボックス

1万人の業務効率を劇的に改善したExcel速技BEST100/PHP研究所

￥1,836
Amazon.co.jp

040 入力のバラつき(表記のゆれ)を防ぐためフォーマットに入力規則を設定する

　Excelで社内の申請書などを運用している、あるいは複数人で分かれて入力作業を行うなどの場合、あらかじめ入力規則を設定することで、入力ミスを防ぐ仕組み作りが可能です。入力する側が楽になるのはもちろん、集計する側もチェックやデータ修正の手間が省け、双方の作業時間短縮につながります。
　データの入力規則 は 、「データ」タブ→「データの入力規則」から設定を行います。

　・半角英数字で入力させる
　メールアドレスや電話番号など半角英数字で入力させたい項目は、IMEモー ドをオフ、もしくは無効に設定します。
　IMEモードオフ:入力者が意図的に全角に変換して打つことができる。基本的 には英数で入力するが、まれに全角で入力する必要のある場合に適。
　IMEモード無効:英数字でしか入力できない。絶対に英数字でしか入力させたくない場合に適。

　「日本語入力」タブをクリック。「オフ(英語モード)」もしくは「無効」を選択する

　・あらかじめ選択肢を設定する
　選択肢が限られている場合は事前に設定しておきます。
　〈1~12など決まった数字を打たせたい場合〉
　「設定」タブをクリック。「入力値の種類」で「整数」を選択。最小値に「1」、最大値に「12」を入力してOKをクリック

　〈男・女の選択など選択肢の数が少ない場合〉
　「入力値の種類」で「リスト」を選択。「元の値」ボックスに「,」区切りで 「男,女」と入力してOKをクリック

〈担当者など選択肢の数が多い場合〉
「入力値の種類」で「リスト」を選択。「元の値」ボックスを一度クリックした後、あらかじめシート状に用意した選択肢のセル範囲を選択してOKをクリック

・エラーメッセージを編集する
セルに入力制限をかけておくと 、それ以外の値を入力したときに以下のようなデフォルトのエラーメッセージが出ます。わかりづらく、不親切です。このメッセージを編集します。

「エラーメッセージ」タブで、「タイトル」欄と「エラーメッセージ」欄に表示させたいメッセージを入力

1万人の業務効率を劇的に改善したExcel速技BEST100/PHP研究所

￥1,836
Amazon.co.jp

038 前年比が100%未満だったら自動的に色をつける方法

　分析の基本である「分けて比べる」上で最も重要な指針の1つが前年比。資料作成の際、前年比が100%を切る部分にはハイライトを付けて目立たせるなど工夫が求められます。
　「条件付き書式」 機能を活用して、100%未満のセルに自動的に色を付ける仕組みを設定します。前年比が100%を下回るセルを赤で塗りつぶし、文字色を白で表示するには、

　1.「E2:E9」のセル範囲を選択する
　2.「ホーム」タブ→「条件付き書式」→「新しいルール」をクリック
　

　3.「数式を使用して、書式設定するセルを決定」をクリック。「次の数式を満たす場合に値を書式設定」の入力ボックスを一度クリックしてから、代表セルであるE2セルをクリック
　4.「=$E$2」と入力されるので[F4]を3回押して「$」をはずす。「=E2<100%」という論理式を完成させる

　5.「書式ボタン」をクリックし、書式を設定してOKをクリック

　セル範囲を選択したときに白くなっているセル(図の場合ではE2セル)を代表セルといい、そのセルを基準に条件付き書式を設定します。
　4.で入力した条件式は「=E2<1」でも同じ意味になりますが、ルールをあとで管理するときのわかりやすさを重視し、「100%」と入力するようにしましょう。
　このような設定は、その都度行うのではなく、あらかじめ設定して、フォーマット化しておくことが時短においては大切てす。

1万人の業務効率を劇的に改善したExcel速技BEST100/PHP研究所

￥1,836
Amazon.co.jp