［Excel］データ集計をどう行うか？ 実際の自治体データをもとに考える⑧ 検算で集計ミスがないか確認する

実際の自治体データ（茨城県の旅館業の許可状況）を元に集計をしてみるこのシリーズの過去記事は、マガジン「実際の自治体データで集計をしてみる」でどうぞ。

前記事「データ集計をどう行うか？　実際の自治体データをもとに考える⑦　許可の種類別の件数を集計する（COUNTIFS関数）」で「営業の種類」別の件数を出しました。

ファイルはこちらです。

しかし、この集計、間違っているおそれがあります。
例えば、計算式が違っている。
下の例は、「下宿」の件数欄の計算式が間違っていいます。
本来は、F3セルの「下宿」を参照して、同じものが入っているセルの個数を「DATA」のH列から算出するのですが、参照元がE3の「簡易宿所」になっています。

上の例であれば、すぐ左の「簡易宿所」と件数が同じなので、おかしいことに気付きます。
でも、項目がたくさんあると、そんなミスに気付かない場合もあります。

また、「DATA」一覧の「許可の種類」欄に空欄があると、間違った件数が出てしまいます。

上のように、「旅館・ホテル」と入るべき欄が空欄になっていると・・・

「旅館・ホテル」の件数にも間違いが生じます。

項目名と違うデータが入っている場合もあります。

なぜか、「旅館・ホテル」が「ホテル」になっていると・・・

やはり、「旅館・ホテル」の件数に誤りが出ます。

他にも、色々な理由でミスは起こりえます。
しかし、計算式を入れて安心してしまい、ミスに気付かない場合があります。というか、データが多いとミスにきづけません。

ミスに気付くには、一つの計算方法だけではダメです。
他の計算方法もやってみて、その結果が合っているか確認すべきです。
いわゆる「検算」です。

検算の方法はいろいろあります。
「データ集計をどう行うか？　実際の自治体データをもとに考える④　「営業の種類」別の数を、計算式を使わないで、どう出す？」で示した方法も、検算として使えます。
でも、これだと、「DATA]シートに変更があると（月次や年次のデータ追加時等）に、その都度、検算をしなければいけません。
また、手作業での検算は、「忘れる」「間違う」リスクがあります。

「ミス」が起こることを前提にチェックの仕組みを作っておく

ミスはいつだって起こりえます。
ミスが起きることを前提に、チェックする仕組みを作っておくことが大事です。
この場合の「仕組み」とは、意識しなくても、手作業をしなくても、ミスが分かる、ということです。

検算の計算式を入れておく

そのためには、検算の計算式を入れておきます。
計算式を入れておけば、「自動で」「間違いなく」検算が出来ます。

検算の計算式は、集計表により変わりますが、ここでは次のように考えてみます（こういう「考え」をすることが、計算式を作る第一歩です。）。

E列「営業施設の名称」の件数が、H列「営業の許可」の各種類の件数の合計と同じなら、計算式やデータのミス（空欄や誤表記）はない。

これを具体的な計算式にしてみます。
E列「営業施設の名称」の件数をH3に出します。

単純な件数を出す　COUNTA関数を使う

単純な件数を出すには　COUNTA関数を使います。
Microsoftサポートによると、COUNTA関数は、
「範囲に含まれる空白ではないセルの個数」を出す（返す）関数です。
数値も文字列も含めて個数を出します。
なお、最後のAがない COUNT関数は、数値の個数を出します。
今回は、「名称」という数値ではないもの（＝文字列）の数を数えるので、COUNTA関数を使います。

まず、H3セルに　=c と入れます。

関数候補が出るので、↓ で選んで、Tabで確定します。
=u から何文字入れても構いません。たくさん入れた方が、選択肢が少なくなって選びやすくなります。
私は、=counta( と直接入力しています。よく使う関数なので覚えています。

次に、「DATA」列に移動して、E列「許可施設の名称」を選択します。
具体的には「E」のところをクリックします。
行の指定は不要です。

これで、Enterを押せば、計算式が出来上がります（最後の「 ) 」は入れなくても大丈夫です。自動で入ります。）。
しかし、以下の表を見ると、「計」と検算の数値が違います。

これは、「検算」の計算式　=COUNTA(DATA!E:E)　には
「DATA」シートの表頭（ヘッダー）の「許可施設の名称」も個数として含まれるため、これも１件として計上されてしまっています。

そのため、H3の計算結果から１を引く必要があります。
これは
=COUNTA(DATA!E:E)-1　で計算できます。
H3セルを選択して、ダブルクリック（非推奨）またはF2（推奨）し、
後ろに -1 を付けるだけです（いずれも半角）。
Enterで確定します。

これで、「計」と検算が同じ答えになりました。

検算としては、この２つの数値を見比べればいいのですが、
「見比べて、同じかどうか」を判断するには、集中力を使います。
確認箇所が１つだけだったり、数値の桁が少なかったりすれば、さほどの手間ではありませんが、確認箇所がいくつかあったり、数値の桁数が大きい（あるいは小数点以下が並ぶ）場合、「数値を見比べて、同じか確認する」ことは負担です。見逃しのリスクもあります（99,936と99,336など）。

その負担を減らすには、「計」と「検算」が合っているか自動で表示させます。
やり方は、とっても簡単です。
IF関数を使います。
検算の右に「判定」と入れ、
その下に以下のように入力します。
=IF(G4=H4,"OK","相違!!")

IF関数は「エクセル関数　基本のキ」の一つなので、今更かもしれませんが、
=if(条件、条件に合致する場合、合致しない場合）で記述します。
=if(○=■,A,B)
なら、「〇と■が同じ」なら「A」と表示、同じでなければ「B」を表示します。
A・Ｂには、数値やセル番号、計算式、文字列などが入ります。
文字の場合は「””」で挟みます。
数値やセル番号、計算式は「””」で挟む必要はありません。

順番としては、
① =if( と手入力（すべて半角）
② マウスでG4セルを選択 →「=if(G4」と入る。
③ = を手入力
④ H4セルをクリック → 「=IF(G4=H4」と入る。
⑤ 「,"OK","") 」と手入力し、Enter
⑥ F2をクリックし、=IF(G4=H4,"OK","")の ”” の間にカーソルを移動し、相違!! と入力（別の表現でも構わない。）
⑦ Enter で確定

これで出来上がりです。
「計」と「検算」が合っていれば、「OK」と表示されます。
２つの数値を見比べなくても、「OK」と出ていれば合っているとわかるので、集中力を浪費することはありません。

もし、「旅館・ホテル」が「ホテル」になっていたり、空欄があったり、そもそも各項目の計算式が間違って「計」が違っていると・・・

「計」と「検算」の数値が違うので、「判定」欄には「相違!!」と出ます。

なお、「計」と「検算」が合っている場合、わざわざ「ＯＫ」と出さなくていいかもしれません。なぜなら、本来は合致して当然だから。
「計」と「検算」が違っているときだけ、「相違!!」と表示する方がシンプルです。
その場合は、=IF(G4=H4,"","相違!!")と記述します。
G4とH4が同じなら空欄（””）を表示し、違っていれば「相違!!」を表示する、という記述です。
この場合、I4セルの文字を赤にしておくと、エラーが目立ちます。

これで、計算ミスがないかどうかの表示ができました。

でも、この表が「提出表」だとすると、この「検算」や「判定」の部分まで印刷して提出するわけにはいきません。
さりとて「列の非表示」は好ましくありません。むしろ、御法度。
「非表示」にすると、後任が引きついても気づかないおそれもあります。
印刷の度に「非表示」にして、また「表示」に戻すのも手間です。
どうすればいいでしょうか？

それは次回お示しします。

今回のファイルです。ご参考にどうぞ。