1. ホーム
  2. ブログ
  3. 入力規則(プルダウン)の作り方|打ち間違いを「注意」ではなく「入れられなく」する

入力規則(プルダウン)の作り方|打ち間違いを「注意」ではなく「入れられなく」する

売上の一覧を取引先ごとに集計したら、同じ会社が3行に分かれていた。「株式会社A」「(株)A」「A社」。入れた人が違うだけで、Excelには別の会社に見えます。

「表記を統一してください」と注意しても、来月また同じことが起きます。人は間違えるものだからです。

入力規則は、「このセルには、この選択肢からしか入れられない」とExcelに決めさせる機能です。セルを選ぶと▼が出て、一覧から選ぶ。あれです。間違った文字は、そもそも入りません。注意する必要がなくなります。

この記事で作るもの

例で、売上の入力表です。

Excelの画面(売上入力シート)。見出し:日付、取引先、区分、金額
赤が「人によって書き方が違う」セル。取引先と区分の2列を、プルダウンからしか選べない形にします。

STEP1:選択肢の一覧を、別のシートに作る

先に、選べる言葉の一覧を作ります。入力表と同じシートの端に書く人が多いのですが、別のシートにしておくのをおすすめします。入力する人が誤って消したり、並べ替えに巻き込まれたりするのを防げるからです。

Excelの画面(選択肢シート)。見出し:取引先、区分
「選択肢」という名前のシートに、列ごとに一覧を書きます。見出しは1行目、選択肢は2行目から。

すでにデータがあって、取引先の一覧を手で書くのが面倒なら、UNIQUE関数で重複を除いた一覧を作れます(UNIQUE関数の使い方)。ただし、表記ゆれがある状態で作ると「株式会社A」「(株)A」が両方出てくるので、一度目で見て直します。

STEP2:一覧をテーブルにする(行を足しても壊れないように)

ここが、あとで効いてくるところです。

選択肢のシートで、一覧のどこかを選んで Ctrl+T を押します。「テーブルの作成」の画面が出るので、「先頭行をテーブルの見出しとして使用する」にチェックが入っているのを確認して OK。

これをやっておくと、取引先が増えたときに一覧の下に1行足すだけで、プルダウンにも自動で出てきます。テーブルにしないで範囲を「A2:A5」のように決めると、6行目に足した取引先はプルダウンに出ず、「一覧に無いから手で打つ」が始まって、元に戻ります。

STEP3:入力表の列に、入力規則を付ける

  1. 入力表の「取引先」の列を選ぶ

    B2から、入力する予定の行まで(B2:B500 くらい広めで構いません)。

  2. データタブ → データの入力規則

    を押す。

  3. 「入力値の種類」で「リスト」を選ぶ
  4. 「元の値」の欄をクリックして、選択肢シートの取引先の列(見出し以外)を選ぶ

    =選択肢!$A$2:$A$5 のように入ります。

  5. OK を押す

    入力表のB列のセルを選ぶと、▼が出て一覧から選べます。

区分の列も、同じ手順で「元の値」を選択肢シートのB列にします。

!

「元の値」にテーブルの列を指定する書き方
STEP2でテーブルにしても、「元の値」に直接 =テーブル1[取引先] と書くとエラーになります。
代わりに、数式タブ → 名前の定義 で、名前「取引先一覧」、参照範囲 =テーブル1[取引先] を作っておき、「元の値」には =取引先一覧 と書きます。これで、行を足しても範囲が自動で広がります。

できあがり

Excelの画面(売上入力シート)。見出し:日付、取引先、区分、金額
取引先と区分は一覧から選ぶだけ。「(株)A」と打とうとしても、はじかれます。

一覧に無い文字を打つと、「この値は、このセルに定義されているデータ入力規則の制限を満たしていません」という案内が出て、入りません。この案内の文章は、データの入力規則 → エラーメッセージ のタブで、「取引先は一覧から選んでください。無ければ選択肢シートに追加してください」のように分かりやすく変えられます。

失敗しやすいところ

!

すでに入っているデータは直らない
入力規則は「これから入れるもの」を止める仕組みです。すでに入っている「(株)A」はそのまま残ります。データタブ → データの入力規則の▼ → 無効データのマーク を押すと、一覧に無いセルに赤い丸が付くので、それを見て直します。

!

コピー&貼り付けはすり抜ける
別の場所からセルをコピーして貼り付けると、入力規則ごと上書きされて、一覧に無い文字が入ることがあります。貼り付けるときは、右クリック → 「値の貼り付け」を使うか、貼り付けのあとに「無効データのマーク」で確認します。

×

選択肢を入力規則の画面に直接書く
「元の値」に 販売,修理,工事 のようにカンマ区切りで直接書く方法もあります。手軽ですが、選択肢を増やすたびに入力規則の画面を開き直すことになり、どこに書いたか分からなくなります。選択肢は、見える場所(選択肢シート)に置きます。

入力規則が効くのは「選択肢が決まっている列」だけ

入力規則は万能ではありません。効くのは、入る値が決まっている列です。取引先、区分、担当者、支店名、品目の分類。

金額や数量のような「毎回違う数字」には、リストは使えません。その代わり、「入力値の種類」で「整数」を選んで「0以上1,000,000以下」のように範囲を決めると、桁を1つ多く打ってしまうような間違いは止まります。

日付の列なら「日付」を選び、「今日以前」のように決めておくと、「2062年」と打ってしまう間違いが止まります。

今日やってみること

  1. 集計が合わない原因になっている列を1つ選ぶ

    取引先か区分、どちらかが多いはずです。

  2. 「選択肢」シートを作って、一覧を書く

    既存データから UNIQUE で出して、表記ゆれを直します。

  3. 一覧を Ctrl+T でテーブルにする

    行を足しても壊れないようにするためです。

  4. 入力表のその列に、リストの入力規則を付ける
  5. 「無効データのマーク」で、すでに入っているずれを見つけて直す

    ここまでやると、来月の集計が合います。

次に読むとよい記事


「集計が合わないのは、入れる人が注意していないから」という相談を受けると、SUPRAXではまず入力の入り口を直します。注意する回数を減らすのも、業務改善です。現状のヒアリングと課題整理は無料です。

#Excel#入力規則#プルダウン#入力ミス#集計

この記事について

SUPRAXは、林業・製造・運送の現場経験をもとに、中小企業の業務改善・AI活用・GISの支援を行っています。 記事の内容について「うちの場合はどうか」を聞きたい方は、無料相談をご利用ください。

無料で相談してみる

あわせて読みたい

UNIQUE関数の使い方|重複を除いた一覧を作る

「取引先の一覧を作って」と言われて、明細の列をコピーして貼り付けて「重複の削除」。できた一覧はその時点のもので、翌月また同じ作業。 UNIQUE(ユニー…

記事一覧へ戻る