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

Excel の JavaScript ライブラリには、ブック内の表、列、行、その他の範囲に自動のデータの入力規則をアドインで追加できる API が用意されています。 データ検証の概念と用語を理解するには、ユーザーが Excel UI を使用してデータ検証を追加する方法に関する次の記事を参照してください。

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

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

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

• rule — 範囲の有効なデータを構成するものを定義します。 「DataValidationRule」を参照してください。
• errorAlert— ユーザーが無効なデータを入力した場合にエラーがポップアップするかどうかを指定し、アラート テキスト、タイトル、およびスタイルを定義します。たとえば、、informationwarning、、 などです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 — オブジェクトで指定されたその他の検証に加えて、10 進数が BasicDataValidation 必要です。
• textLength — オブジェクト内の検証の詳細を BasicDataValidation セルの値の 長さに 適用します。

次に、入力規則を作成する例を示します。 このコードについては、次の点に注意してください。

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

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」を参照してください。

2 つの三項演算子もあります。 between と notBetweenです。 これらを使用するには、省略可能な 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();
})

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

カスタムの入力規則式を指定するには、DataValidationRule オブジェクトの custom プロパティを使用します。 次に例を示します。 このコードについては、次の点に注意してください。

• ワークシートの A 列と B 列に Athlete Name と Comments という列がある、2 列のテーブルがあると仮定します。
• Comments 列の冗長性を軽減するために、アスリート名を含むデータを無効にします。
• 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 です。 つまり、カスタム のメッセージ、タイトル、スタイルを設定または設定showAlertfalseするカスタム アラートを作成しない限り、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)