| Home | What's New |
生産性向上 基本テクニック |
トラブル回避 テクニック |
生産性向上 リンク |
personal.xls 強化講座 |
生産性向上 ツール |
Site Map | Guest Book |
地球風 画像館 |
Q&A
Salon EXCELの質問はこちらへ |
|
11/16/2003 -
|
||
| １９．入力規則を過信するな！ |
||
|
・
|
データの入力規則を設定することで「入力するデータの種類、値の範囲を制限する方法」を、ちょっと便利テク９で紹介しました。また、応用テクニックとして、「重複データの入力を制限する」方法もちょっと便利テク１７で紹介しました。 この入力規則は手軽に使えるのですが、「入力規則を設定したから、想定外のデータは入力されることは無い」と過信してはいけません。入力規則を設定しても、規則外のデータを入力することは可能なんです。 業務で使う入力データシートなどでは、規則外のデータが入力されてしまうこともあらかじめ考慮して、その影響が大きなトラブルになるならば対策をとっておきましょう。 |
|
|
・
|
まず最初に、入力規則を設定したのに規則外のデータを入力できてしまうケースには、以下のようなものがあります（他にもまだあるかもしれませんが）。 | |
(1)任意のセル・行・列をコピーして、入力規則を設定したセル（あるいは行・列）に貼り付ける。 (2)任意のセル（またはセル範囲）を選択して、入力規則を設定したセル範囲の部分にドラッグ＆ドロップする。 (3)ショートカットの Ctrl+D や Ctrl+R で、入力規則を設定したセル範囲に隣接する上や右のセルをコピーする。 (4)入力規則を設定していないセルから入力規則を設定した範囲を含むセル範囲を選択し、規則外のデータを入力してから、Ctrl+Enter で確定する。 （選択範囲に全て同じ値を一括して入力する） (5)セルに計算式を入力する。 （計算式を入力した時に計算結果が入力規則で設定した条件に当てはまらないとエラーになるが、計算式を入力した時には条件に当てはまってエラーにならなければ、その後計算式が参照するセルの値を変化してその計算結果が条件に当てはまらなくなってもエラーにはならない） (6)入力規則を一時的に解除、または条件変更して入力する。 (7)マクロ（VBA）でセルに値をセットする。 |
||
| 結構ありますよね。気づかないうちに無効なデータが入ってしまう危険性が沢山あるということです。 |
||
|
・
|
このページは「トラブル回避テクニック」なので、このような危険性をどのように回避するかを紹介しなくてはならないのですが、これがかなり難しいです。 以下ではマクロを使った回避テクニックも一応紹介していますが、マクロは誰でも使えるわけではありませんし、そもそもマクロを無効にしてファイルを読み込まれたら全く無力です。やはり、マクロを使わずに回避する方法を模索していくべきと考えています。 |
|
|
(1)
|
まず(1)のコピー・ペーストですが、 例えばマクロを使って、以下のようなコードを入力規則を設定したシートのモジュールに追加すると、コピーや切り取りを無効にする（つまり貼り付けを出来なくする）ことが可能です。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (Application.CutCopyMode <> False) Then
Application.CutCopyMode = False
End If
End Sub
|
|
|
(2)
|
ドラッグ&ドロップも、マクロで出来なくすることは可能です。ThisWorkbookのモジュールに以下のコードを追加します。
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CellDragAndDrop = False
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CellDragAndDrop = True
End Sub
なお、オプション設定で ただし、これはブック（ファイル）単位ではなくEXCEL全体について（読み込んだファイルすべてについて）ドラッグ&ドロップが出来なくなります。具体的には、選択したセル範囲の外枠をマウスで選択して移動させることが出来なくなります。  |
|
|
(3) (4) (5)
|
||
| Ctrl+D , Ctrl + R
, Ctrl + Enter などで入力規則を設定したセルへの値のセットを禁止するのは、マクロを使えば不可能ではないと思いますが、やはり難しいと思います。 personal.xls強化講座２で紹介している方法を使って、これらのショートカットを無効にすることも出来ますし、入力規則を設定したシートのマクロ設定で細かく制御することも可能ではあります。しかし、入力規則の設定内容・設定範囲ごとにマクロを書かなくてはならないので一般的な回避方法とは言えませんし、やはりマクロを無効にしにてファイルを読み込まれたら無力です。 |
||
|
そこで、マクロを使わない回避方法として、入力規則を二重にチェックする方法を紹介します。 これは、入力規則の設定で無効なデータの入力は禁止するが、それでも無効なデータが入力された場合に、それを検知して入力者にエラーを認識させる。という方法です。 |
||
 たとえば、右図の様にA2からC7に整数1〜10という入力規則を設定したとします。 このように設定しても、1より小さい数字、10より大きい数字、あるいは数字以外の文字が入力されてしまう可能性があります。 そこで、別途セルA1に、A2からC7にそのような無効データが入力されているかをチェックするセルを用意してみます。 具体的には、下図のように、 =IF(SUM(IF(ISTEXT(A2:C7),1,0))+COUNTIF(A2:C7,">10")+COUNTIF(A2:C7,"<1"),"Error!","")と入力してCtrl+Shift+Enterで確定します。つまり、配列数式です。（配列数式についてはコチラを参照してください） 式の意味としては、「A2からC7の範囲のセルで、文字が入力されているセル、10より大きい数字のセル、1より小さいセルの数が0より大きい（存在する）とき、Error!と表示する」となります。 |
||
 |
||
 右図は実例です。
上のようにA1にチェック式を入れた後で、・ まずA3に1を入力し、 ・ A2に =A3+A4 という数式を入力し、 ・ A4に10を入力しました。 A3の1、A4の10、それからA2に数式を入れた時点では計算結果は1ですから、いずれも入力規則には引っかからずに入力できます。しかし、A4に10を入れた時点でA2の計算結果は11になります（10を超えてしまう）が、入力規則ではエラーにはなりません。 ここで、A1にセットしたチェック式が11という無効データを検知して、Error! と表示しています。 （右図では、セルA1の書式を赤字の太字に設定しています） A2からC7の入力範囲以外は「シートの保護」をしてしまえば、このA1のチェック式は変更・削除できませんから、チェック式も誤って消してしまって入力者が無効データの入力に気づかない、ということは無いでしょう。 |
||
 左の図は、入力規則で「文字列（長さ指定）、10文字以下」を設定した場合の二重チェックの例です。チェックセルのA1には、
=IF(SUM(IF(LEN(A2:C7)>10,1,0))>0,"Error!","")
と入れて、Ctrl+Shift+Enterで配列数式の確定をしています。 セルA2は、 =A3&A4 という数式を入れて、10文字より長いエラーデータを発生させています。 |
||
 左の図は、A10からA15までのセル範囲に”担当者”という名前を付け、その名前を使って入力規則を設定した例です。（名前付けを使った入力規則の設定についてはコチラも参照してください）このようにリストから選択する入力規則を設定した場合の二重チェックは、下図のようになります。チェックセルのA1には、 =IF(SUM(IF(ISBLANK(A2:C7)+COUNTIF(担当者,A2:C7)=0,1,0))>0,"Error!","") と入れて、Ctrl+Shift+Enterで配列数式の確定をしています。 |
||
 式の意味としては、ブランクでもなく「担当者」にも見当たらないセルの値があればError!と表示する、となります。 |
||
 このように、入力規則の設定条件によって二重チェックに使うセルの数式は変わってきますが、うまく二重チェックを設定できれば、気づかないまま無効なデータを入力してしまう危険性を減らすことが可能でしょう。なお、「ワークシート分析」ツールバーの右から2番目にある「無効データのマーク」ボタンを押すと、右図の様に入力規則に合致していない無効データが赤い○印でマークされますので、エラー個所を調べるときに便利です。 |
||
