【Excel】入力規則は貼り付けやオートフィルですり抜ける｜店舗コードが変わった話と対策

誰でも入力できるExcelでは、入力ミスを防ぐためにデータの入力規則を設定しています。しかし入力規則は、貼り付けやオートフィルで入れた値はチェックしません。

実務では、プルダウンで選ぶ店舗がオートフィルで別の店舗コードに変わり、そのExcelを参照している別の資料の数値がずれたことがありました。この記事では、そのときの話と、実際に入れた対策を紹介します。

設定していた入力規則

日付の欄に、日付以外の文字列が入力されたことがあったため、入力規則を設定しました。

「停止」にしておくと、ルールに合わない値を入力したときにエラーが出て、その値は入力できません。

店舗は、「101:本店」のように店舗コードと店舗名をつなげた候補から、プルダウンで選ぶようにしていました。候補にない値を入力すると、エラーになります。

プルダウンの作り方は、こちらの記事でも紹介しています。

入力規則がチェックするのは、セルに直接入力したときと、プルダウンから選んだときだけです。次の2つの場合は、エラーが出ずに入ってしまいます。

別のセルからコピーして貼り付けると、値と一緒に入力規則も上書きされます。そのため、日付の欄に文字列を貼り付けてもエラーにならず、そのセルの入力規則もなくなります。

セルの右下の小さな四角を下にドラッグすると、上のセルを下にコピーできます。ただし、文字の中に数字があると、その数字が1つずつ増えます。

「101:本店」を下にドラッグすると、次のようになります。

「102:本店」「103:本店」はプルダウンの候補にはありませんが、オートフィルではエラーが出ません。店舗名は本店のままなので、見ただけでは気づきにくい変化です。

実務では、このExcelを別のExcelからパワークエリで参照していました。「数値がずれる」と報告があり、調べたところ、オートフィルで店舗コードが変わっていたことが分かりました。パワークエリ側では店舗コードで集計していたため、別の店舗の数値として数えられていました。

誰でも入力するExcelなので、入力する人全員に「オートフィルを使わないで」とお願いするのは難しいです。そこで、店舗コードと店舗名の組み合わせが合っているかを式で確かめ、合っていないときはセルを赤くするようにしました。この方法なら、貼り付けでもオートフィルでも、入力した人がその場で気づけます。

店舗コードと店舗名の表を、「店舗マスター」という名前のテーブルにしておきます。店舗コードは数値です。

店舗がB列にある場合、別の列（ここではD列）に次の式を入れます。

=IF(B2="","",XLOOKUP(LEFT(B2,3)*1,店舗マスター[店舗コード],店舗マスター[店舗名],"")=MID(B2,5,100))

式の中身は次のとおりです。

文字列の数字を *1 で数値にする方法は、こちらの記事で紹介しています。

先ほどの例では、次のようになります。

102の店舗名は駅前店、103は郊外店なので、本店と合わずFALSEになります。

最後に、条件付き書式でFALSEの行の店舗のセルを赤くします。

これで、オートフィルや貼り付けで店舗コードと店舗名が合わなくなったセルが、赤く表示されます。