転職ノウハウ 応募企業の探し方、面接のポイントから、円満退職の秘訣まで。あなたの転職を成功に導くためのノウハウを紹介!
このエントリーをはてなブックマークに追加

Excelでミスなく速く! データ入力は4つの関数テクニックで効率アップ

タイトル画像

《この記事で扱う関数》

  • 絞り込みリストの作成:INDIRECT関数
  • 付随する情報を自動入力:VLOOKUP関数
  • エラー値を非表示にする:IFERROR関数
  • フリガナ・郵便番号も自動入力:PHONETIC関数

データベースを作成する際は、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]をクリックすると、郵便番号が半角で表示されます。《郵便番号の自動入力》
  9. 説明画像

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

【その他の関連関数】

JIS関数 ROW関数 COLUMN関数 HLOOKUP関数 LOOKUP関数

ライティング/四禮 静子(しれい しずこ)

日本大学芸術学部卒業。CATVの制作ディレクターを退職後、独学でパソコンを学び、下町浅草に完全マンツーマンのフォーティネットパソコンスクールを開校して17年目。講座企画からテキスト作成・スクール運営を行う。行政主催の講習会・企業に合わせたオリジナル研修や新入社員研修も行っている。
著書に「ビジネス力がみにつくExcel&Word」(発行:翔泳社)、2016年にはWord・Excelの新刊を2冊同時出版予定。http://www.forty40.com

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

バックナンバー

Excel関数で文字列を自在に操るワザを覚えて、どんなデータもすっきり見やすく!

転職先で周りから「できる!」と言われるためのOfficesoft極意まとめ Excel関数で文字列を自在に操るワザを覚えて、どんなデータもすっきり...

パワポで差を付ける! プレゼンテーションの質を上げる秘密のテクニック4つ

転職先で周りから「できる!」と言われるためのOfficesoft極意まとめ パワポで差を付ける! プレゼンテーションの質を上げる秘密のテクニック4...

パワポのスライド制作時間が劇的に短くなるコマンド&アドイン厳選4つ

転職先で周りから「できる!」と言われるためのOfficesoft極意まとめ パワポのスライド制作時間が劇的に短くなるコマンド&アドイン厳選4つ...


適職をディグる! ジョブリシャス診断(適職診断)
履歴書の添削を受ける
  • マイナビ転職 グローバル
  • マイナビ転職 エンジニア求人サーチ【IT】
  • マイナビ転職 エンジニア求人サーチ【ものづくり】
  • マイナビ転職 女性のおしごと
マイナビ転職 適性診断
マイナビ転職 適性診断

会員登録(無料)


メカラボ
土地柄タイプ診断
オフィシャルBOOK
シゴトサプリ