Ayuka Tech Lab
Excelの入力ミスって、気をつけているつもりでも、なぜか起きちゃいますよね。
先に結論を言うと、入力ミスは「気をつける」だけでは防ぎきれません。
でも、Excelの機能で「ミスが起きにくい仕組み」を作っておけば、ミスは減らせるし、確認も楽になります。
私は市役所に約15年勤めていて、そのうち4年は経理を担当していました。
紙のデータをExcelに転記する作業が多くて、紙の表に定規を当てて列を見やすくしたり、前後のデータを目で確認してから入力したりしていました。
それでも、途中で話しかけられたり、窓口の対応が入ったりすると、前後のデータがわからなくなるんですよね。
そのたびに「ここまで入力したよね」と、確認しながら進めることが多かったです。
この記事では、入力ミスが起きる理由、すでに入力したデータのミスの見つけ方、今日からできる「ミスを防ぐ仕組み」3つをお伝えします。
※画面や操作は、私のWindows版Excelで確認した範囲です。
画面が違うときは、Microsoftの公式ヘルプも見てくださいね。
入力ミスはなぜ起きる？気をつけるだけでは防げない理由
入力ミスは、あなたの注意が足りないから起きる、とは限りません。
Excelで起きやすいミスの種類
Excelの入力ミスは、私の整理では「入力の誤り」「重複」「うっかり上書き」の3つに分けられます。
私が市役所で多く見たのは、数字の入れ違いや桁の間違い、行のずれ、二重入力でした。
先頭の0が消えるミスもありました。
- 数字の入れ違いや桁の間違い(入力の誤り)
- 1行ずれて入力してしまう(入力の誤り)
- 先頭の0が消えてしまう(入力の誤り)
- 同じデータを二重に入力してしまう(重複)
- 入力済みのセルに、気づかずに別の値を入れてしまう(うっかり上書き)
先頭の0は、標準の書式のセルに「0123」と入力すると消えます。
入力する前にセルの書式を「文字列」にしておくと、0が残ります。
人の注意力には限界がある
人の注意力には限界があるので、気合いで防ごうとするより、ミスが起きにくい環境を作るほうが、現実的で長続きしますよ。
まずは今あるミスを見つけて、そのあと仕組みを作る
先に今あるミスを見つけて、そのあと仕組みを作ります。
入力ミスの見つけ方(すでに入力した分の確認)
重複した入力を色で見つける(条件付き書式)
二重入力は、条件付き書式を使うと、色で見つけられます。
私も、入力が終わって提出前に見直すときに、ミスに気づくことが多かったです。
気づいたあとは、請求書番号みたいな固有のキーをもとに、重複しているデータがないかを、条件付き書式などで確認していました。
手順はこんな感じです。
- 確認したい列(請求書番号の列など)の、見出し行を除いたデータ部分を選択します。
- 「ホーム」タブの「条件付き書式」から、「セルの強調表示ルール」→「重複する値」を選びます。
- 書式(色)を選んで「OK」を押します。
全角・半角の混在に気づく
全角と半角が混ざっていると、検索や集計で漏れが出ることがあります。
私のExcelでは、全角の「ＩＮＶ-001」と半角の「INV-001」が1つずつある表で半角を探すと、COUNTIF・SUMIF・VLOOKUPのどれも半角の行だけが対象でした。
条件付き書式の「重複する値」は、私のExcelでは全角の「ＡＢＣ」と半角の「ABC」を同じものとして扱い、どちらにも色がつきました。
ただ、色だけでは、どちらが全角かは分かりません。
混在そのものに気づきたいときは、「データ」タブの「フィルター」を押して、列見出しの▼から項目一覧を開きます。
私のExcelでは、全角と半角は別々の項目として並びました。
同じ内容に見える項目が2つ並んでいたら、元の資料と見比べて、正しい表記に直しましょう。
見つけた重複を削除する(元データを残してから)
重複が見つかったら、「データ」タブの「重複の削除」で、まとめて消せます。
Microsoftのサポートでは、削除したデータは完全に削除されると案内されています。
だから、必ず元データを残してからやりましょう。
手順はこんな感じです。
- 元のシートをコピーして、別のシートに保存しておきます。
- 重複を削除したい範囲を選択します(見出し行は含めても除いてもOKです)。
- 「データ」タブの「データツール」にある「重複の削除」を押します。
- 選択範囲の隣のセルにもデータがあると範囲の確認画面が出るので、迷ったら初期のまま「選択範囲を拡張する」で進みます。
- 次の「重複の削除」の画面で、対象の列にチェックが入っているかを確かめて、「OK」を押します。
「先頭行をデータの見出しとして使用する」は、初期でONとは限らないので、画面で状態を確認してください。
私のExcelでは、見出しを含めて選んでもチェックなしで、列の名前が「列 A」「列 B」になることがありました。
見出しがあるのにチェックが入っていなければ、入れましょう。
私のExcelで試したところ、全部の列にチェックを入れると、全部の項目が同じ行だけが削除されました。
列Aだけにチェックを入れると、番号が同じなら内容が違う行も削除されました。
内容が違う行があるときは、削除する前に、本当に二重入力か元の資料で確かめてくださいね。
私のExcelでは、同じ値が複数あるときは、いちばん上のデータが残りました。
全角と半角が混ざった行は、私のExcelで試したところ、別扱いで消えませんでした。
先にフィルターなどで表記をそろえてから削除しましょう。
今日からできる「ミスを防ぐ仕組み」を3つ紹介
ミスを防ぐ仕組みは、入力する前に設定しておく「データの入力規則」が中心です。
「データ」タブの「データツール」にある「データの入力規則」を開くと、「設定」タブで条件を選べます。
入力規則は、設定したあとに入力するデータから効きます。
すでに入っているデータには、エラーが出ません。
①プルダウン(ドロップダウン リスト)で表記ゆれと入力の誤りを防ぐ
プルダウンを使うと、選ぶだけで入力できるので、表記ゆれや入力の誤りを防ぎやすくなります。
私も、同じ内容が続くときや、あとでフィルターで検索したいデータのときに、入力規則やプルダウンを使っていました。
入力する側も選ぶだけで手間が減るし、あとで確認するときも楽になりましたよ。
手順はこんな感じです。
- 選択肢にしたい項目を、シートのどこかに入力しておきます。
- プルダウンを設定したいセルを選択します。
- 「データの入力規則」の「設定」タブを開き、「入力値の種類」を「リスト」に変えます。
- 「元の値」に項目の範囲を指定して、「OK」を押します。
②数値・日付・文字数の範囲を制限する
数値や日付、文字数に範囲を決めておくと、範囲から外れた入力をその場で止めやすくなります。
手順はこんな感じです。
- 制限をかけたいセルを選択します。
- 「データの入力規則」の「設定」タブで、「入力値の種類」から「整数」「日付」「文字列(長さ指定)」などを選びます。
- 制限条件(「次の値の間」など)を選び、整数なら最小値と最大値、日付なら開始日と終了日、文字列なら文字数を指定します。
たとえば、数量が1〜100の列なら、「整数」で最小値を1、最大値を100にしておくと、1000と入力したときにエラーを出せます。
私のExcelでは、「エラー メッセージ」タブの「スタイル」の初期値が「停止」だったので、何も変えなくても、範囲外の入力は止まりました。
③重複入力を防ぐ
重複入力は、入力規則の「ユーザー設定」を使うと、入力した時点で止めやすくなります。
手順の一例はこんな感じです。
- 対象の列(たとえばA列)で、見出し行を除いた、いちばん上のデータのセル(A2)を最初にクリックして、そこから下へ(たとえばA100まで)範囲を選択します。
- 「データの入力規則」の「設定」タブで、「入力値の種類」を「ユーザー設定」にします。
- 「数式」に「=COUNTIF(A:A,A2)=1」と入力して、「OK」を押します。
数式の「A:A」はA列全体、「A2」はいま入力するセルの値、「=1」は同じ値が1つだけならOK、という意味です。
A列以外なら「A」をその列の記号に、データが3行目以降から始まるなら「A2」を「A3」にしてください。
私のExcelでは、数式の「A2」は最初にクリックしたセルを基準にした位置で保存され、下から上へ選ぶと数式がずれて、重複してもエラーになりませんでした。
なお、私のExcelでは、半角どうしの完全な重複はエラーになりましたが、全角で入れた同じ番号はエラーになりませんでした。
つまり、③だけでは全角と半角の混在までは防げないので、入力済みの混在は、さっきのフィルターで確認しましょう。
すでに入力してある分の重複は、条件付き書式でも確認できます(私のExcelでは、全角と半角は同じ扱いで色がつきました)。
まとめ
仕組み化でミスは減らせる
入力ミスは、「気をつける」だけに頼らず、仕組みと人の目の両方を重ねることで減らせます。
仕組みにも限界はあります。
私のExcelでは、入力規則を設定したセルにCtrl+Vで貼り付けると、貼り付け先の規則は残りませんでした。
コピー元に規則がなければ消え、別の規則があれば置き換わり、エラーも出ません。
「値のみ貼り付け」なら規則は残りますが、貼った値はチェックされません。
だから、貼り付けで入力したあとは、人の目での見直しが欠かせません。
見直しは、こんな感じです。
- 件数の一致
- 合計額の一致
- 別の人による確認
私の職場では、入力する人と確認する人は分かれていました。
私は、確認をお願いする前に、件数の一致と、合計額がある資料では合計額の一致を確認していました。
プラスαの知識
入力が終わったセルを「シートの保護」でロックしておくと、あとから間違って上書きするのを防ぎやすくなります。
手順はこんな感じです。
- 引き続き入力したいセルを選択し、「ホーム」タブの「配置」グループの右下の矢印から、「セルの書式設定」を開きます。
- 「保護」タブで「ロック」のチェックを外します。
- 「校閲」タブの「シートの保護」を選び、許可する操作を選んで「OK」を押します。
Microsoftのサポートでも、セルは初期状態でロックされていて、シートを保護するまでは効果がないと案内されています。
私のExcelでは、初期設定のままシートを保護すると、フィルター(列の▼)・並べ替え・条件付き書式が使えませんでした。
「シートの保護」の画面で「並べ替え」と「オートフィルターの使用」にチェックを入れて許可すると、フィルターと並べ替えは使えました。
条件付き書式を使いたいときは、保護する前に設定するか、保護を解除してからにしましょう。
さいごに
入力ミスは、気をつけるだけでは防ぎきれませんが、仕組みを作れば減らせます。
最初から全部を設定しなくても大丈夫です。
まずは、いちばんミスが多い列に、プルダウンを1つ設定するところから始めてみてください。
