Excel の範囲にデータの入力規則を追加する

Excel JavaScript API を使用して、データ品質を適用します。 ルールを適用し、プロンプトとエラー アラートに対して Excel の検証 UI に依存します。 この記事では、ルールの種類を定義し、プロンプトとエラー アラートを構成し、検証を削除または調整する方法について説明します。 Excel の組み込み検証 UI の背景が必要な場合は、これらの記事を確認してください。

• セルに対するデータの入力規則の適用
• データの入力規則の詳細
• Excel でのデータの入力規則の説明と例

データの入力規則のプログラムによる制御

Range.dataValidation プロパティは DataValidation オブジェクトを取得しますが、これは Excel でデータの入力規則をプログラムにより制御するためのエントリ ポイントになります。 オブジェクトには、次の 5 つのプロパティがあります。

• rule — 範囲の有効なデータを構成するものを定義します。 「DataValidationRule」を参照してください。
• errorAlert — ユーザーが無効なデータを入力した場合にエラーがポップアップするかどうかを指定し、アラート テキスト、タイトル、およびスタイル ( information、 warning、 stopなど) を定義します。 「DataValidationErrorAlert」を参照してください。
• prompt — ユーザーが範囲の上にマウス ポインターを置いてプロンプト メッセージを定義するときにプロンプトを表示するかどうかを指定します。 「DataValidationPrompt」を参照してください。
• ignoreBlanks — データ検証規則が範囲内の空白セルに適用されるかどうかを指定します。 既定値は true です。
• type — WholeNumber、Date、TextLength などの検証型の読み取り専用の識別。 rule プロパティを設定すると、間接的に設定されます。

注:

プログラムによって追加されたデータの入力規則は、手動で追加したデータの入力規則と同様に動作します。 具体的に言うと、データの入力規則は、ユーザーがセルに値を直接入力した場合、またはブックの別の場所からセルをコピーして貼り付けたときに、値の貼り付けオプションを選択した場合にのみトリガーされます。 ユーザーがセルをコピーしてデータの入力規則のある範囲内に単に貼り付けた場合は、データの入力規則はトリガーされません。

検証規則を作成する

範囲にデータの入力規則を追加するには、コードで Range.dataValidation にある DataValidation オブジェクトの rule プロパティを設定する必要があります。 これには 7 つの省略可能なプロパティを持つ DataValidationRule オブジェクトが必要です。 DataValidationRule オブジェクトにはこれらのプロパティの 1 つのみを設定できます。 設定したプロパティにより、入力規則のタイプが決まります。

Basic および DateTime 入力規則のタイプ

最初の 3 つの DataValidationRule プロパティ (つまり、入力規則のタイプ) は、その値として BasicDataValidation オブジェクトをとります。

• wholeNumber — BasicDataValidation オブジェクトで指定されたその他の検証に加えて、整数が必要です。
• decimal — BasicDataValidation オブジェクトで指定されたその他の検証に加えて、10 進数が必要です。
• textLength — BasicDataValidation オブジェクトの検証の詳細をセルの値の 長さに 適用します。

次の例では、検証規則を作成します。 重要なポイント:

• operatorは、二項演算子greaterThanです。 二項演算子を使用する際は必ず、ユーザーがセルに入力しようとする値は左側のオペランドになり、formula1 で指定された値は右側のオペランドになります。 この規則では、0 より大きい整数のみが有効であると言います。
• formula1 はハードコーディングされた値です。 コーディング時に値が何であるかがわからない場合は、Excel 数式を文字列 ("=A3" や "=SUM(A4,B5)" など) として使用することもできます。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            wholeNumber: {
                formula1: 0,
                operator: Excel.DataValidationOperator.greaterThan
            }
        };

    await context.sync();
});

その他のバイナリ演算子については、「 BasicDataValidation」 を参照してください。

betweenとnotBetweenの 2 つの三項演算子もあります。 これらを使用するには、省略可能な formula2 プロパティを指定します。 formula1 and formula2の値はバウンディング オペランドです。 ユーザーがセルに入力しようとする値は、第三の (評価済み) オペランドです。 "Between" 演算子を使用する例を次に示します。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            decimal: {
                formula1: 0,
                formula2: 100,
              operator: Excel.DataValidationOperator.between
            }
        };

    await context.sync();
});

次の 2 つのルール プロパティは、値として DateTimeDataValidation オブジェクトをとります。

• date
• time

DateTimeDataValidation オブジェクトは BasicDataValidation と同様に構成されています。つまり、プロパティ formula1、formula2、および operator があり、同じ方法で使用されます。 ただし、数式プロパティで数値が使用できない代わりに ISO 8606 datetime 文字列 (または Excel の式) を入力できる点が異なります。 2022 年 4 月の最初の週の日付として有効な値を定義する例を次に示します。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            date: {
                formula1: "2022-04-01",
                formula2: "2022-04-08",
                operator: Excel.DataValidationOperator.between
            }
        };

    await context.sync();
});

リスト入力規則のタイプ

DataValidationRule オブジェクトの list プロパティを使用して、値を有限セットに制約します。 次のコード サンプルでは、次の例を示します。 重要なポイント:

• "Names" という名前のワークシートがあり、"A1:A3" の範囲の値が名前になっていると仮定します。
• source プロパティは有効な値のリストを指定します。 文字列引数は名前を含む範囲を参照します。 "Sue, Ricky, Liz" などのコンマ区切りのリストを割り当てることもできます。
• inCellDropDown プロパティは、ユーザーが選択したときにドロップダウン コントロールをセルに表示するかどうかを指定します。 true場合は、ドロップダウンが表示され、sourceの値の一覧が表示されます。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");   
    let nameSourceRange = context.workbook.worksheets.getItem("Names").getRange("A1:A3");

    range.dataValidation.rule = {
        list: {
            inCellDropDown: true,
            source: "=Names!$A$1:$A$3"
        }
    };

    await context.sync();
})

カスタムの入力規則のタイプ

カスタム検証式を指定するには、 custom プロパティを使用します。 次に例を示します。 重要なポイント:

• ワークシートの A 列と B 列に、 アスリート名 と コメント の列を含む 2 列のテーブルがあることを前提としています。
• [コメント] 列の詳細を減らすために、ルールによって、アスリートの名前を含むデータが無効になります。
• SEARCH(A2,B2) は、A2 の文字列の B2 の開始位置を返します。 A2 が B2 に含まれていない場合、数値は返されません。
• ISNUMBER() はブール値を返します。 そのため、 formula プロパティには 、コメント の有効なデータは 、アスリート名 の文字列を含まないデータであることが示されています。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let commentsRange = sheet.tables.getItem("AthletesTable").columns.getItem("Comments").getDataBodyRange();

    commentsRange.dataValidation.rule = {
            custom: {
                formula: "=NOT(ISNUMBER(SEARCH(A2,B2)))"
            }
        };

    await context.sync();
});

入力規則のエラー アラートを作成する

無効なデータが入力されたときにユーザーをガイドするエラー アラートを作成します。 次の例では、基本的なアラートを作成します。 重要なポイント:

• style プロパティは、ユーザーが情報アラート、警告、または "停止" アラートを取得するかどうかを決定します。 ユーザーによる無効なデータの追加を実際に防止するのは stop のみです。 warningとinformationのポップアップには、ユーザーが無効なデータを入力できるようにするオプションがあります。
• showAlert プロパティの既定値は true です。 つまり、showAlertをfalseに設定するか、カスタム メッセージ、タイトル、スタイルを設定するカスタム アラートを作成しない限り、Excel は一般的なアラート (stop 型) をポップアップ表示します。 このコードでは、カスタムのメッセージとタイトルを設定します。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");

    range.dataValidation.errorAlert = {
            message: "Sorry, only positive whole numbers are allowed",
            showAlert: true, // The default is 'true'.
              style: Excel.DataValidationAlertStyle.stop,
            title: "Negative or Decimal Number Entered"
        };

    // Set range.dataValidation.rule and optionally .prompt here.

    await context.sync();
});

詳細については、「DataValidationErrorAlert」を参照してください。

入力規則のプロンプトを作成する

ユーザーがセルを選択したときに表示される指示プロンプトを作成します。 この例では、データを入力する前に、正の数値の検証についてユーザーに伝えます。

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:C5");

    range.dataValidation.prompt = {
            message: "Please enter a positive whole number.",
            showPrompt: true, // The default is 'false'.
            title: "Positive Whole Numbers Only."
        };

    // Set range.dataValidation.rule and optionally .errorAlert here.

    await context.sync();
});

詳細については、「DataValidationPrompt」を参照してください。

範囲からデータの入力規則を削除する

範囲からデータ検証を削除するには、 Range.dataValidation.clear() メソッドを呼び出します。

myrange.dataValidation.clear();

クリアする範囲は、データ検証を追加した範囲と正確に一致する必要はありません。 2 つの範囲が完全に一致しない場合、重複するセルのみがクリアされます。

注:

範囲からデータの入力規則を削除すると、ユーザーが手動で範囲に追加したデータの入力規則も削除されます。

次の手順

• 検証とイベントの組み合わせ: イベント。
• より強力な視覚キューの 条件付き書式 を追加します。

関連項目

• Office アドインの Excel JavaScript オブジェクト モデル
• DataValidation Object (JavaScript API for Excel)
• Range オブジェクト (JavaScript API for Excel)