今回はエクセルで作ったデータベースの入力制限を見ていきます。
入力制限をしっかりと行うことでデータの集計や思わぬトラブルを未然に防ぐことができますから是非ともおさえておきましょう。
おすすめ！ 社会人におすすめのExcel学習法はこちら https://www.udemy.com/course/masukawa_040/入力の内容を制限しないとどうなるか
入力する項目を制限しなかった場合にどうなるかを考えましょう。
表記の問題
例えば次の表を見てください
| A株式会社 | 正式表記 |
| Ａ株式会社 | Aが全角 |
| A（株） | 略式表記 まる括弧は全角 |
| A(株) | 略式表記 まる括弧は半角 |
| A㈱ | 略式表記 環境依存文字 |
全てA株式会社を表記したものですが、これひとつ見てもさまざまな表記が考えられます。Aの部分が半角や全角、株式会社の部分が文字だったり略式だったりがありえます。また、略式の表記でも（）が半角か全角、環境依存文字などもあり、一つの会社を見るだけでも色々な表記があり得るということがわかります。
これをこのまま集計すると下の図のようになります。比較のためにB株式会社を統一表記で入力してあります。
左がデータベースで真ん中がピボットテーブルで作った集計用の加工データ、右がグラフになります。
A株式会社の表現でさまざまな形を認めているため、集計のピボットテーブルやグラフでA株式会社が別のデータとして認識されてしまい正しく集計されていません。
一方でB株式会社は表記の揺れを認めさせないようにしたので集計がしっかりと行われています。入力内容で色々な表記を認めさせないことで集計なども簡単にできます。
セルの属性の問題
パソコンは私たちと違う発想でデータを考えます。
例えば「１」という数字を見た際に私たち人間は数字としての「１」と文字としての「１」を無意識のうちに分けて考えています。しかしパソコンのデータベースではこの二つをしっかりと区別して入力してあげないと理解してくれません。
数字を入れたつもりで文字として考えさせてしまうと計算できないということになります。
下の図ではセルのB12は一見￥10,000ということで他のB列と同じように見えるので計算できそうですが、文字列として入力させているのでパソコンは計算してくれません。B株式会社の合計は50000ですが集計やグラフでは40000となっているのがわかると思います。
データベースは他の人も使うと考えておく
ということで、データベースにはしっかりとパソコンの考え方に沿って入力をしないといけないということになります。
と考える人もいるかもしれませんが、それは違います。
データベースは他の人も使うということをしっかりと考えておかないといけません。仮に今は自分しか使っていないとしても、急な出張で他の人に入力させないといけないなどさまざまなケースが考えられます。
エクセルに疎い人や自分の考えで入力してしまう人はその場しのぎで入力してしまうことを考えると、事前に入力項目を制限しておくということが大事な考え方でしょう。
また、ルールを統一させておくということも有効な考えですが、「株式会社の表記は（株）にせず、株式会社と表記する」というルールを全員がしっかりと覚えておいてくれるかというと大抵の場合は覚えてくれません。仮に覚えてくれていたとしても、ルールが増えていけばルールを覚えるという作業が発生しいつかキャパオーバーになってしまいます。また、使う人が変わっていくということを考えると全てのルールが伝承されない、これまでになかった発想で入力するということも考えられます。今回の例で言えばAと株式会社の間にスペースを入れる人もいるかもしれません。
ルールを覚えさせるということは、発想としてはいいのですが、実行可能かというと難しいと言わざるを得ないでしょう。
入力内容を制限する具体的方法
ということで入力内容を制限していきましょう。
入力の表記ゆれを制限する
まずは、入力の表記ゆれを制限してみましょう。
表記ゆれの制限はデータタブの中の入力規制で行うことができます。
入力規制の中のソースに入力させたい項目を「,」で区切って入力させることができます。
例：A株式会社,B有限会社,C商事
この時、ドロップダウンリストから選択するをオンにしておくことで直接入力のほかドロップダウンから選択したい項目を選択することができます。ドロップダウンにおける表示の順番は入力規制の中のソースに入力した際の左からの順番になります。
直接入力で文字の頭が同じものを自動的に識別して予測して表示してくれます。ドロップダウンは便利なので特段の事情がない限りはオンにしておいた方が利便性が高まります。
入力規制による表記ゆれの制限の基本は以上となりますが次のように考える方もいるかと思います。
確かに入力規制の機能のみを使うとかなりメンテナンス性が悪いと言えます。
そこで活用したいのがテーブル機能とINDIRECT関数です。
テーブル機能はエクセルによるデータベース構築の基本になります。下記の記事でテーブル機能を学べますのでテーブル機能を知らない方は読んでみてください。
テーブルで入力項目専用のサブのデータベースを作り、そのサブのデータベースをINDIRECT関数を使うことでテーブルのリストと連携させ、入力項目の制限をさせることができます。次の画像をご覧ください。
左が作りたいマスターのデータベース（テーブル名：マスター）で右がサブのデータベース（テーブル名：サブ）になります。サブのデータベースに請求先を入力することでマスターの入力項目を制御します。（サブの？？列はあとで使いますので一旦無視してください）
設定方法はマスターで入力内容を制限したいセルを一つ選んで、入力規制からリストを選び次のように入力します。
=INDIRECT("参照テーブル名[参照先列名]")
（今回の例だと＝INDIRECT("サブ[請求先マスタ]")）
こうすることで選択したセルにおける入力項目が制限されます。
これの便利なところは、入力の順番も好きなように変えられるということです。
サブのデータベースに??列を作っておきましたがここにマスターで表示させたい順番などを入力しておき、フィルターなどの昇順などで順番を変えることにより、このサブのデータベースの順番通りにドロップダウンで表示してくれます。
一つのセルに入力すればテーブル内においてはテーブルが自動で判断し今後データベースが増えていく際にも自動で適用されます。ただし、テーブルの外で入力してしまってから自動拡張でテーブルの中に含まれる場合はサブのデータベースの入力内容以外も認めてしまいますので注意が必要です。（詳しくは下に記載してあります。）
データの入力規制は名前の通り入力の際に入力できるかできないかを判断してくれる機能になります。そのため、すでに入力してある内容については判断してくれません。
テーブルの外で入力する場合のセルにはテーブルから1行下の部分までは入力の判断をしてくれますがそれ以降はしてくれませんので気をつけましょう
セルの属性を制限する
セルの属性を制限する場合にも入力規制を使いましょう。
データタブの入力規制の中から規制を選び入力させたい項目を選択します。
今回は整数を選びます。整数にすることで数字のみの入力を認めることになり、文字列としての数字は入力できないようになります。また数値に範囲を設けることもできますから事前に設定しておけば明らかな異常値などの入力を防ぐこともできます。
でも表示も￥10,000のように表示させたいな
このように入力内容と表示内容を変えたいという場合もあると思います。その場合は入力を数字で制限して表示形式を通貨や会計など好みの表示形式を選ぶことで目的は達成できます。
入力については数字や文字のほかにも日付や時間しか入力させないということも可能です。状況によって入力内容を制限していきましょう。
おすすめ！ 社会人におすすめのExcel学習法はこちら https://www.udemy.com/course/masukawa_040/
