売上の一覧を取引先ごとに集計したら、同じ会社が3行に分かれていた。「株式会社A」「(株)A」「A社」。入れた人が違うだけで、Excelには別の会社に見えます。
「表記を統一してください」と注意しても、来月また同じことが起きます。人は間違えるものだからです。
入力規則は、「このセルには、この選択肢からしか入れられない」とExcelに決めさせる機能です。セルを選ぶと▼が出て、一覧から選ぶ。あれです。間違った文字は、そもそも入りません。注意する必要がなくなります。
この記事で作るもの
例で、売上の入力表です。

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

すでにデータがあって、取引先の一覧を手で書くのが面倒なら、UNIQUE関数で重複を除いた一覧を作れます(UNIQUE関数の使い方)。ただし、表記ゆれがある状態で作ると「株式会社A」「(株)A」が両方出てくるので、一度目で見て直します。
STEP2:一覧をテーブルにする(行を足しても壊れないように)
ここが、あとで効いてくるところです。
選択肢のシートで、一覧のどこかを選んで Ctrl+T を押します。「テーブルの作成」の画面が出るので、「先頭行をテーブルの見出しとして使用する」にチェックが入っているのを確認して OK。
これをやっておくと、取引先が増えたときに一覧の下に1行足すだけで、プルダウンにも自動で出てきます。テーブルにしないで範囲を「A2:A5」のように決めると、6行目に足した取引先はプルダウンに出ず、「一覧に無いから手で打つ」が始まって、元に戻ります。
STEP3:入力表の列に、入力規則を付ける
- 入力表の「取引先」の列を選ぶ
B2から、入力する予定の行まで(B2:B500 くらい広めで構いません)。
- データタブ → データの入力規則
を押す。
- 「入力値の種類」で「リスト」を選ぶ
- 「元の値」の欄をクリックして、選択肢シートの取引先の列(見出し以外)を選ぶ
=選択肢!$A$2:$A$5のように入ります。 - OK を押す
入力表のB列のセルを選ぶと、▼が出て一覧から選べます。
区分の列も、同じ手順で「元の値」を選択肢シートのB列にします。
「元の値」にテーブルの列を指定する書き方
STEP2でテーブルにしても、「元の値」に直接 =テーブル1[取引先] と書くとエラーになります。
代わりに、数式タブ → 名前の定義 で、名前「取引先一覧」、参照範囲 =テーブル1[取引先] を作っておき、「元の値」には =取引先一覧 と書きます。これで、行を足しても範囲が自動で広がります。
できあがり

一覧に無い文字を打つと、「この値は、このセルに定義されているデータ入力規則の制限を満たしていません」という案内が出て、入りません。この案内の文章は、データの入力規則 → エラーメッセージ のタブで、「取引先は一覧から選んでください。無ければ選択肢シートに追加してください」のように分かりやすく変えられます。
失敗しやすいところ
すでに入っているデータは直らない
入力規則は「これから入れるもの」を止める仕組みです。すでに入っている「(株)A」はそのまま残ります。データタブ → データの入力規則の▼ → 無効データのマーク を押すと、一覧に無いセルに赤い丸が付くので、それを見て直します。
コピー&貼り付けはすり抜ける
別の場所からセルをコピーして貼り付けると、入力規則ごと上書きされて、一覧に無い文字が入ることがあります。貼り付けるときは、右クリック → 「値の貼り付け」を使うか、貼り付けのあとに「無効データのマーク」で確認します。
選択肢を入力規則の画面に直接書く
「元の値」に 販売,修理,工事 のようにカンマ区切りで直接書く方法もあります。手軽ですが、選択肢を増やすたびに入力規則の画面を開き直すことになり、どこに書いたか分からなくなります。選択肢は、見える場所(選択肢シート)に置きます。
入力規則が効くのは「選択肢が決まっている列」だけ
入力規則は万能ではありません。効くのは、入る値が決まっている列です。取引先、区分、担当者、支店名、品目の分類。
金額や数量のような「毎回違う数字」には、リストは使えません。その代わり、「入力値の種類」で「整数」を選んで「0以上1,000,000以下」のように範囲を決めると、桁を1つ多く打ってしまうような間違いは止まります。
日付の列なら「日付」を選び、「今日以前」のように決めておくと、「2062年」と打ってしまう間違いが止まります。
今日やってみること
- 集計が合わない原因になっている列を1つ選ぶ
取引先か区分、どちらかが多いはずです。
- 「選択肢」シートを作って、一覧を書く
既存データから UNIQUE で出して、表記ゆれを直します。
- 一覧を Ctrl+T でテーブルにする
行を足しても壊れないようにするためです。
- 入力表のその列に、リストの入力規則を付ける
- 「無効データのマーク」で、すでに入っているずれを見つけて直す
ここまでやると、来月の集計が合います。
次に読むとよい記事
- 選択肢の一覧を既存データから作る → UNIQUE関数の使い方
- 表記ゆれの原因になる余分なスペースを消す → TRIM関数の使い方
- 取引先ごと・区分ごとの合計を出す → SUMIFS関数の使い方
- 事務でよく使う関数の一覧 → 事務でよく使うExcel関数まとめ
「集計が合わないのは、入れる人が注意していないから」という相談を受けると、SUPRAXではまず入力の入り口を直します。注意する回数を減らすのも、業務改善です。現状のヒアリングと課題整理は無料です。
この記事について
SUPRAXは、林業・製造・運送の現場経験をもとに、中小企業の業務改善・AI活用・GISの支援を行っています。 記事の内容について「うちの場合はどうか」を聞きたい方は、無料相談をご利用ください。
あわせて読みたい
UNIQUE関数の使い方|重複を除いた一覧を作る
「取引先の一覧を作って」と言われて、明細の列をコピーして貼り付けて「重複の削除」。できた一覧はその時点のもので、翌月また同じ作業。 UNIQUE(ユニー…
SUMIFS関数の使い方|複数条件の合計、月別×取引先別の集計表、0になるときの直し方
「A社の9月分だけ合計したい」。フィルタで絞って右下の合計を電卓に打ち直していませんか。 SUMIFS(サムイフズ)を1つ書けば、条件に合う行だけを足し…
TRIM関数の使い方|合計が合わない原因のスペースを消す
条件を正しく書いたのに合計が0になる。検索しても見つからない。 原因で一番多いのが、画面では見えない余分なスペースです。 これを取り除くのが TRIM(…
事務でよく使うExcel関数まとめ|目的から探せる一覧
Excelの関数は500以上あります。ですが、事務作業で本当に使うのは20個ほどです。 このページは、その20個を「何をしたいか」から引ける形にまとめた…