転職先で周りから「できる！」と言われるためのOfficesoft極意まとめ

Excel（エクセル）のデータ入力を簡単に自動反映！　4つの関数テクニック

データベースを作成する際は、Excelの機能や関数を駆使してできるだけ自動化をします。でも、日々の売上や経費、顧客情報など、どうしても“手入力”を必要とする場面は多々ありますよね。そんな時に覚えておきたいのが、できるだけミスなく速く入力できる表の作成方法です。

リスト入力や自動表示を駆使して、データ入力の精度とスピードを大幅改善！

どんなにタイピングが得意でも、変換ミスや入力ミスは起こるもの。リストから選択入力したり、紐付く情報は自動表示させたりして、決められたデータ以外は入力できないようにしてしまいましょう。
ただ、リスト入力の選択肢が多いと、「選択が面倒！」「手入力したほうが早い！」となりがちです。
選択肢が多い場合、最初に選択するデータに合わせて次の列に表示されるリストの選択肢を変化させるような、絞り込みのリスト入力ができる表を作成すると便利です。
では、実際の例を使って作成方法を見ていきましょう。

絞り込みリストの作成：INDIRECT関数

まずリストのデータを一覧にしたマスターシートを用意し、そこでリストのデータに名前の定義を行います。

ここでは、区分のデータ内容が下に続く「購入商品NO」のリストの名前となるように定義します。それを用いて、新たに作成する表で区分からネックレスを選択すると、「購入商品NO」の列ではネックレスだけのリストが表示されるようにINDIRECT関数を使って設定していきます。INDIRECT関数は指定したセルに入力されているセル参照を用いて、間接的なセルの指定を行います。

1. マスターシートで区分から婦人服までのデータを選択し、［数式］タブの［選択範囲から作成］をクリックします。
2. ［上端行］のみにチェックを入れ［OK］ボタンをクリックします。
   


3. それぞれの列に1行目のフィールド名が名前として定義されます。Ctrl+F3で定義した名前を確認できます。《マスターシートの作成》
   


4. 別シートに、データを反映したい表を用意し、リストから選択入力するように設定したい列全体（C列）を選択します。
   


5. ［データ］タブ→［データツール］グループ→［データの入力規則］をクリックします。
6. ［設定］タブに次のように入力します。
   入力値の種類：リスト
   元の値：＝区分（F3を押して定義された名前の一覧から「区分」を選択します。）
   


7. ［OK］ボタンをクリックすると、C列のセルに▼が表示されリストからの選択入力ができるようになります。
   


8. 「購入商品NO」の列（D列）を選択します。
   


9. ［データ］タブ→［データツール］グループ→［データの入力規則］をクリックします。
10. ［設定］タブに次のように入力します。
    入力値の種類：リスト
    元の値：INDIRECT関数を直接入力し、引数に絞り込みたいリスト名の入ったセル（C1）を指定。
    


11. ［OK］ボタンをクリックすると、D列のセルに▼が表示されリストからの選択入力ができるようになります。
    

INDIRECT関数は定義された名前の範囲を選択します。参照先のセルに「ネックレス」と入力されれば、ネックレスと名前が定義された範囲を選択、「ヘアー」と入力されればヘアーと名前が定義された範囲を選択します。

よって、区分のデータ内容により、「購入商品NO」に表示されるリストのデータは変化します。

ポイント

作成した表の1行目（フィールド名）に設定されたデータの入力規則（リスト入力）は、まとめて削除しておきましょう。

1行目を行選択して、［データ］タブ→［データツール］グループ→［データの入力規則］→［入力規則マークのクリア］をクリックします。
マスターのリストに追加がある場合は、マスターのリストをテーブルに変換（［挿入］タブ→［テーブル］グループ→［テーブル］）しておくことで、追加したデータがリスト入力の一覧に自動的に追加表示されていきます。

付随する情報を自動入力：VLOOKUP関数

リスト入力で「購入商品NO」を入力したら、購入商品名・単価が自動で表示されるようにVLOOKUP関数を設定します。VLOOKUP関数は、指定したデータに紐付くデータを、指定した範囲の指定した列から検索して表示します。

まず、参照元となる購入商品名と単価の一覧表を作成し、表全体に「商品マスター」と名前を定義しておきましょう。この時、表の左端列には、検索する「購入商品NO」を配置します。VLOOKUP関数を使用する時は、必ず左端列を検索するデータ（検索値）としなければなりません。

1. 購入商品名を表示したいセル（E2）を選択し、［数式］タブ→［関数ライブラリ］グループ→［検索／行列］から、VLOOKUP関数を起動します。
   


2. 次のように引数を入力します。
   検索値：「購入商品NO」が入力されたセル（D2）を列のみ絶対参照（$D2）にします。
   範囲：F3を押して定義された名前の一覧から「商品マスター」を選択します。
   


3. 取り出したいデータが何列目にあるかを特定するため、列番号にカーソルがある状態で名前ボックスからMATCH関数を起動します。
4. MATCH関数は指定したデータが指定した範囲の左から何列目にあるのかを数値で表示します。次のように引数を入力します。
   検査値：検索したいフィールドの名前が入ったセル（E$1）を指定。
   検査範囲：どの表のフィールドから探すのかを指定（参照元となる商品マスターの1行目を範囲指定し、絶対参照）。
   照合の種類：0またはFALSE（＝検査値と等しい値を検索）（省略可能）
   
   
   


5. 数式バーのVLOOKUPのスペル内をクリックしてVLOOKUPの引数画面に戻ります。
   検査方法：0（＝検査値と完全一致の値を検索）
   


6. ［OK］をクリックすると購入商品名が表示されます。
7. 数式を単価の列にもコピーすると、同様に「購入商品NO」に対応した単価が表示されます。
   

VLOOKUP関数は、指定した「購入商品NO」に対する購入商品名を参照範囲から検索しており、MATCH関数は、購入商品名のフィールドが参照範囲の何列目にあるかを特定する役割を担っています。

エラー値を非表示にする：IFERROR関数

数式をコピーしていくと、「購入商品NO」が未入力のセルはエラー値が表示されます。

そこでIFERROR関数を用いると、結果がエラーの時には空白を表示させるように設定できます。

1. 数式バーの「＝」の後にカーソルを置き、「IFERROR(」と入力します。
2. 数式の最後にカーソルを置き、「,””)」（カンマ・ダブルクォーテーション2つ・終わりカッコ）を入力します。
   =IFERROR（VLOOKUP($D2,商品マスター,MATCH(E$1,マスター!$H$1:$J$1,0),0),""）
   「もしもVLOOKUP関数の結果がエラーなら、空白を表示」という意味になります。
3. 確定したら数式を行・列へコピーします。
   

ポイント

リスト作成の時と同様に、商品マスターに追加がある場合はVLOOKUP関数の検索範囲を拡張する必要があります。定義された名前（商品マスター）の範囲変更の手間をなくすために、商品マスターの範囲をテーブルに変換しておきましょう。テーブルを使用すればデータを追加しても自動的に範囲が拡張されるようになります。

フリガナ・郵便番号も自動入力：PHONETIC関数

氏名を入力した時に、フリガナが自動で表示されるようにするには、PHONETIC関数を使用します。同様に、郵便番号を自動入力するには、住所入力の際に郵便番号変換（※）を利用し、PHONETIC関数を用いて郵便番号を取り出して表示できるようにします。
ただ、郵便番号を入力して変換しているので、取り出した郵便番号は全角になります。
全角文字を半角に変換する関数であるASC関数を組み合わせて、半角で表示できるようにしましょう。
（※）郵便番号変換を利用する際は、Microsoft IMEの郵便番号辞書を有効にします。

1. フリガナを入力するセル（J2）を選択し、［数式］タブ→［関数ライブラリ］グループ→［情報］から、PHONETIC関数を起動します。
2. 次のように引数を入力します。
   参照：フリガナを表示したい氏名の入ったセル（I2）を指定。
   


3. ［OK］をクリックして列に数式をコピーしましょう。氏名を入力すると自動でフリガナが表示されます。《フリガナの自動入力》
   


4. 次に、住所のセルに郵便番号を入力して変換キーを押し、都道府県から始まる住所を選択します。
   


5. 郵便番号を表示したいセル（M2）を選択します。
6. ［数式］タブ→［関数ライブラリ］グループ→［文字列操作関数］から、ASC関数を起動します。
7. 引数の枠内にカーソルがある状態で、名前ボックスからPHONETIC関数を起動します。
8. 住所のセル（N2）をクリックして指定し、［OK］をクリックすると、郵便番号が半角で表示されます。《郵便番号の自動入力》

ご紹介した関数を使って、入力の手間を省く、入力のミスをなくす。そして、誰が見ても分かりやすい数式で表を組み立てていきましょう。ちょっとした工夫で日常業務がとても快適になりますよ。

編集／株式会社スペースシップ