Excelデータ分析-データ検証

データ検証は、Excelの非常に便利で使いやすいツールであり、ワークシートに入力された入力データにデータ検証を設定できます。

ワークシートの任意のセルについて、次のことができます。

何を入力する必要があるかについての入力メッセージを表示します。
入力する値を制限します。
選択する値のリストを提供します。
エラーメッセージを表示し、無効なデータ入力を拒否します。

識別されたリスク情報を入力および追跡するために使用できる次のリスクトラッカーを検討してください。

このトラッカーでは、次の列に入力されたデータが事前設定されたデータ制約で検証され、入力されたデータは検証基準を満たしている場合にのみ受け入れられます。そうしないと、エラーメッセージが表示されます。

Probability
Impact
リスクカテゴリー
リスクソース
Status

[リスクエクスポージャー]列には計算値があり、データを入力することはできません。コラムもS. No. 行を削除しても調整される計算値を持つように設定されています。

ここで、そのようなワークシートを設定する方法を学習します。

ワークシートの構造を準備する

ワークシートの構造を準備するには-

空白のワークシートから始めます。
行2にヘッダーを配置します。
列ヘッダーを行3に配置します。
列ヘッダーの場合、確率、影響、およびリスクエクスポージャー-
- セルを右クリックします。
- ドロップダウンから[セルのフォーマット]をクリックします。
- [セルの書式設定]ダイアログボックスで、[配置]タブをクリックします。
- オリエンテーションの下に90と入力します。
各列ヘッダーの行3、4、および5のセルを結合して中央揃えにします。
行2〜5のセルの境界線をフォーマットします。
行と列の幅を調整します。

ワークシートは次のようになります-

リスクカテゴリに有効な値を設定する

セルM5〜M13に、次の値を入力します（M5は見出しで、M6〜M13は値です）

カテゴリ値
利用者
お客様
管理
スケジュール
スケジュール
環境
製品
事業

[リスクカテゴリ（H6）]列の下の最初のセルをクリックします。
リボンの[データ]タブをクリックします。
[データツール]グループの[データ検証]をクリックします。
ドロップダウンリストから[データ検証...]を選択します。

[データ検証]ダイアログボックスが表示されます。

[設定]タブをクリックします。
検証基準の下で、 Allow: ドロップダウンリスト、オプションを選択 List。

表示される[ソース：]ボックスで範囲M6：M13を選択します。
表示される[空白を無視する]および[セル内]ドロップダウンのチェックボックスをオンにします。

リスクカテゴリの入力メッセージを設定する

[データ検証]ダイアログボックスの[入力メッセージ]タブをクリックします。
チェックボックスをオンにします Show input message セルが選択されたとき。
[タイトル：]の下のボックスに、「リスクカテゴリ：」と入力します。
[入力メッセージ]の下のボックスで、リストからリスクのカテゴリを選択します。

リスクカテゴリのエラーアラートを設定する

エラーアラートを設定するには-

[データ検証]ダイアログボックスの[エラーアラート]タブをクリックします。
[無効なデータが入力された後にエラーアラートを表示する]チェックボックスをオンにします。
[スタイル：]ドロップダウンで[停止]を選択します
[タイトル：]の下のボックスに、「無効なエントリ：」と入力します。
[エラーメッセージ]の下のボックスに、「ドロップダウンリストから値を選択してください」と入力します。
[OK]をクリックします。

リスクカテゴリのデータ検証を確認する

リスクカテゴリで選択した最初のセルについて、

データ検証基準が設定されています
入力メッセージが設定されています
エラーアラートが設定されています

これで、設定を確認できます。

データ検証基準を設定したセルをクリックします。入力メッセージが表示されます。ドロップダウンボタンがセルの右側に表示されます。

入力メッセージが正しく表示されます。

セルの右側にあるドロップダウンボタンをクリックします。ドロップダウンリストが表示され、選択可能な値が示されます。
ドロップダウンリストの値を、ドロップダウンリストの作成に使用された値と照合します。

両方の値のセットが一致します。値の数が多い場合は、ドロップダウンリストの右側にスクロールダウンバーが表示されることに注意してください。

ドロップダウンリストから値を選択します。セルに表示されます。

有効な値の選択が正常に機能していることがわかります。

最後に、無効なエントリを入力して、エラーアラートを確認してください。

セルにPeopleと入力し、Enterキーを押します。セルに設定したエラーメッセージが表示されます。

エラーメッセージを確認します。
再試行またはキャンセルのいずれかを選択できます。両方のオプションを確認します。

これで、セルのデータ検証が正常に設定されました。

Note −メッセージのスペルと文法を確認することは非常に重要です。

リスクカテゴリ列に有効な基準を設定する

これで、[リスクカテゴリ]列のすべてのセルにデータ検証基準を適用する準備が整いました。

この時点で、2つのことを覚えておく必要があります-

使用可能なセルの最大数の基準を設定する必要があります。この例では、ワークシートが使用される場所に基づいて、10〜100の範囲で変更できます。
不要なセル範囲または列全体の基準を設定しないでください。これにより、ファイルサイズが不必要に大きくなります。これは、過剰フォーマットと呼ばれます。外部ソースからワークシートを入手する場合は、このチュートリアルの「問い合わせ」の章で学習する余分なフォーマットを削除する必要があります。

以下の手順に従ってください-

リスクカテゴリの下の10個のセルの検証基準を設定します。
これは、最初のセルの右下隅をクリックすることで簡単に実行できます。
表示される+記号を押したまま、下に引きます。

選択したすべてのセルにデータ検証が設定されます。

選択した最後の列をクリックして確認します。

列リスクカテゴリのデータ検証が完了しました。

リスクソースの検証値を設定する

この場合、内部と外部の2つの値しかありません。

[リスクソース（I6）]列の下の最初のセルをクリックします
リボンの[データ]タブをクリックします
[データツール]グループの[データ検証]をクリックします
ドロップダウンリストから[データ検証...]を選択します。

[データ検証]ダイアログボックスが表示されます。

[設定]タブをクリックします。
[検証基準]の[許可：]ドロップダウンリストで、[リスト]オプションを選択します。
表示される[ソース：]ボックスに「Internal、External」と入力します。
表示される[空白を無視する]および[セル内]ドロップダウンのチェックボックスをオンにします。

リスクソースの入力メッセージを設定します。

リスクソースのエラーアラートを設定します。

リスクソースの下で選択された最初のセルの場合-

データ検証基準が設定されています
入力メッセージが設定されています
エラーアラートが設定されています

これで、設定を確認できます。

データ検証基準を設定したセルをクリックします。入力メッセージが表示されます。ドロップダウンボタンがセルの右側に表示されます。

入力メッセージは正しく表示されます。

セルの右側にあるドロップダウン矢印ボタンをクリックします。ドロップダウンリストが表示され、選択可能な値が示されます。
値が入力したものと同じであるかどうかを確認します–内部と外部。

両方の値のセットが一致します。ドロップダウンリストから値を選択します。セルに表示されます。

有効な値の選択が正常に機能していることがわかります。最後に、無効なエントリを入力して、エラーアラートを確認してください。

セルにFinancialと入力し、Enterキーを押します。セルに設定したエラーメッセージが表示されます。

エラーメッセージを確認します。これで、セルのデータ検証が正常に設定されました。
リスクソース列に有効な基準を設定する
データ検証基準を[リスクソース]列のセルI6〜I15に適用します（つまり、[リスクカテゴリ]列と同じ範囲）。

選択したすべてのセルにデータ検証が設定されます。列リスクソースのデータ検証が完了しました。

ステータスの検証値を設定する

リスクソースの検証値の設定に使用したのと同じ手順を繰り返します。
リストの値をOpen、Closedに設定します。
[ステータス]列のセルK6〜K15にデータ検証基準を適用します（つまり、[リスクカテゴリ]列と同じ範囲）。

選択したすべてのセルにデータ検証が設定されます。列ステータスのデータ検証が完了しました。

確率の検証値を設定する

リスク確率スコアの値は1〜5の範囲で、1が低く、5が高くなります。値は、1から5までの任意の整数（両方を含む）にすることができます。

[リスクソース（I6）]列の下の最初のセルをクリックします。
リボンの[データ]タブをクリックします。
[データツール]グループの[データ検証]をクリックします。
ドロップダウンリストから[データ検証...]を選択します。

[データ検証]ダイアログボックスが表示されます。

[設定]タブをクリックします。
[検証基準]の[許可：]ドロップダウンリストで、[整数]を選択します。

[データ]の下から選択します。
[最小]の下のボックスに1を入力します。
[最大]の下のボックスに5と入力します。

確率の入力メッセージを設定する

確率のエラーアラートを設定し、[OK]をクリックします。

確率で選択した最初のセルについて、

データ検証基準が設定されます。
入力メッセージを設定します。
エラーアラートが設定されます。

これで、設定を確認できます。

データ検証基準を設定したセルをクリックします。入力メッセージが表示されます。この場合、入力値がリストからではなく範囲内に設定されているため、ドロップダウンボタンはありません。

入力メッセージが正しく表示されます。

セルに1から5までの整数を入力します。セルに表示されます。

有効な値の選択は正常に機能しています。最後に、無効なエントリを入力して、エラーアラートを確認してください。

セルに6と入力し、Enterキーを押します。セルに設定したエラーメッセージが表示されます。

これで、セルのデータ検証が正常に設定されました。

確率列に有効な基準を設定します。
データ検証基準を[確率]列のセルE6〜E15に適用します（つまり、[リスクカテゴリ]列と同じ範囲）。

選択したすべてのセルにデータ検証が設定されます。列Probabilityのデータ検証が完了しました。

影響の検証値を設定する

Impactの検証値を設定するには、確率の検証値の設定に使用したのと同じ手順を繰り返します。

[影響]列のセルF6〜F15にデータ検証基準を適用します（つまり、[リスクカテゴリ]列と同じ範囲）。

選択したすべてのセルにデータ検証が設定されます。列Impactのデータ検証が完了しました。

計算値を使用して列のリスクエクスポージャーを設定する

リスクエクスポージャーは、リスク確率とリスク影響の積として計算されます。

リスクエクスポージャー=確率*影響

セルG6に= E6 * F6と入力し、Enterキーを押します。

E6とF6が空であるため、セルG6に0が表示されます。

セルG6〜G15に数式をコピーします。セルG6-G15に0が表示されます。

リスクエクスポージャーの列は計算値を対象としているため、その列へのデータ入力を許可しないでください。

セルG6-G15を選択します
右クリックして、表示されるドロップダウンリストで[セルの書式設定]を選択します。[セルの書式設定]ダイアログボックスが表示されます。
[保護]タブをクリックします。
オプションを確認してください Locked。

これは、これらのセルでデータ入力が許可されないようにするためです。ただし、これはワークシートが保護されている場合にのみ有効になります。これは、ワークシートの準備ができた後の最後の手順として実行します。

[OK]をクリックします。
セルG6〜G15に陰影を付けて、計算値であることを示します。

シリアル番号値のフォーマット

S.No.列に入力するのはユーザーに任せることができます。ただし、S。No。値をフォーマットすると、ワークシートの見栄えが良くなります。さらに、ワークシートがフォーマットされている行数が表示されます。

セルB6に= row（）-5と入力し、Enterキーを押します。

1はセルB6に表示されます。セルB6〜B15に数式をコピーします。値1〜10が表示されます。

セルB6-B15をシェーディングします。

要約

プロジェクトはほぼ完了です。

データカテゴリ値を含む列Mを非表示にします。
セルB6-K16の境界線をフォーマットします。

ワークシートタブを右クリックします。
メニューから[シートの保護]を選択します。

[シートの保護]ダイアログボックスが表示されます。

[ワークシートとロックされたセルの内容を保護する]オプションをオンにします。
シートの保護を解除するには、[パスワード]の下にパスワードを入力します-
- パスワードでは大文字と小文字が区別されます
- パスワードを忘れた場合、保護されたシートを復元することはできません
- ワークシートの名前とパスワードのリストをどこかに保存しておくことをお勧めします
[このワークシートのすべてのユーザーに次のことを許可する]で、[ロック解除されたセルを選択する]チェックボックスをオンにします。