Excel の範囲にデータの入力規則を追加するAdd data validation to Excel ranges

Excel の JavaScript ライブラリには、ブック内の表、列、行、その他の範囲に自動のデータの入力規則をアドインで追加できる API が用意されています。The Excel JavaScript Library provides APIs to enable your add-in to add automatic data validation to tables, columns, rows, and other ranges in a workbook. データの入力規則の概念と用語を把握するには、ユーザーが Excel UI によってデータの入力規則を追加する方法に関する次の記事を参照してください。To understand the concepts and the terminology of data validation, please see the following articles about how users add data validation through the Excel UI:

データの入力規則のプログラムによる制御Programmatic control of data validation

Range.dataValidation プロパティは DataValidation オブジェクトを取得しますが、これは Excel でデータの入力規則をプログラムにより制御するためのエントリ ポイントになります。The Range.dataValidation property, which takes a DataValidation object, is the entry point for programmatic control of data validation in Excel. DataValidation オブジェクトには 5 つのプロパティがあります。There are five properties to the DataValidation object:

  • rule — 範囲の有効データの構成要素を定義します。rule — Defines what constitutes valid data for the range. DataValidationRule」を参照してください。See DataValidationRule.
  • errorAlert — ユーザーが無効なデータを入力した場合にエラーがポップアップ表示されるかどうかを指定し、アラートのテキスト、タイトル、スタイルを定義します。たとえば、情報提供警告停止 などです。errorAlert — Specifies whether an error pops up if the user enters invalid data, and defines the alert text, title, and style; for example, Informational, Warning, and Stop. DataValidationErrorAlert」を参照してください。See DataValidationErrorAlert.
  • prompt — ユーザーが範囲の上にカーソルを動かすとダイアログが表示されるかどうかを指定し、表示されるダイアログ メッセージを定義します。prompt — Specifies whether a prompt appears when the user hovers over the range and defines the prompt message. DataValidationPrompt」を参照してください。See DataValidationPrompt.
  • ignoreBlanks — データの入力規則を範囲内の空白セルに適用するかどうかを指定します。ignoreBlanks — Specifies whether the data validation rule applies to blank cells in the range. 既定値は true です。Defaults to true.
  • type — WholeNumber、Date、TextLength などの入力規則のタイプの読み取り専用 ID です。これは rule プロパティを設定すると間接的に設定されます。type — A read-only identification of the validation type, such as WholeNumber, Date, TextLength, etc. It is set indirectly when you set the rule property.

注意

プログラムによって追加されたデータの入力規則は、手動で追加したデータの入力規則と同様に動作します。Data validation added programmatically behaves just like manually added data validation. 具体的に言うと、データの入力規則は、ユーザーがセルに値を直接入力した場合、またはブックの別の場所からセルをコピーして貼り付けたときに、の貼り付けオプションを選択した場合にのみトリガーされます。In particular, note that data validation is triggered only if the user directly enters a value into a cell or copies and pastes a cell from elsewhere in the workbook and chooses the Values paste option. ユーザーがセルをコピーしてデータの入力規則のある範囲内に単に貼り付けた場合は、データの入力規則はトリガーされません。If the user copies a cell and does a plain paste into a range with data validation, validation is not triggered.

入力規則を作成するCreating validation rules

範囲にデータの入力規則を追加するには、コードで Range.dataValidation にある DataValidation オブジェクトの rule プロパティを設定する必要があります。To add data validation to a range, your code must set the rule property of the DataValidation object in Range.dataValidation. これには 7 つの省略可能なプロパティを持つ DataValidationRule オブジェクトが必要です。This takes a DataValidationRule object which has seven optional properties. DataValidationRule オブジェクトにはこれらのプロパティの 1 つのみを設定できます。No more than one of these properties may be present in any DataValidationRule object. 設定したプロパティにより、入力規則のタイプが決まります。The property that you include determines the type of validation.

Basic および DateTime 入力規則のタイプBasic and DateTime validation rule types

最初の 3 つの DataValidationRule プロパティ (つまり、入力規則のタイプ) は、その値として BasicDataValidation オブジェクトをとります。The first three DataValidationRule properties (i.e., validation rule types) take a BasicDataValidation object as their value.

  • wholeNumberBasicDataValidation オブジェクトで指定された他の任意の入力規則と整数が必要です。wholeNumber — Requires a whole number in addition to any other validation specified by the BasicDataValidation object.
  • decimalBasicDataValidation オブジェクトで指定された他の任意の入力規則と 10 進数が必要です。decimal — Requires a decimal number in addition to any other validation specified by the BasicDataValidation object.
  • textLengthBasicDataValidationオブジェクトの入力規則の詳細をセルの値の 長さ に適用します。textLength — Applies the validation details in the BasicDataValidation object to the length of the cell's value.

次に、入力規則を作成する例を示します。Here is an example of creating a validation rule. このコードについては、次の点に注意してください。Note the following about this code:

  • operator は二項演算子 "GreaterThan" です。The operator is the binary operator "GreaterThan". 二項演算子を使用する際は必ず、ユーザーがセルに入力しようとする値は左側のオペランドになり、formula1 で指定された値は右側のオペランドになります。Whenever you use a binary operator, the value that the user tries to enter in the cell is the left-hand operand and the value specified in formula1 is the right-hand operand. そのため、この規則では、0 より大きい整数のみが有効になります。So this rule says that only whole numbers that are greater than 0 are valid.
  • formula1 はハードコーディングされた値です。The formula1 is a hard-coded number. コーディングの時点でその正しい値がわからない場合は、その値に Excel の数式 (文字列) を使用することもできます。If you don't know at coding time what the value should be, you can also use an Excel formula (as a string) for the value. たとえば、"=A3" や "=SUM(A4,B5)" を formula1 の値にすることもできます。For example, "=A3" and "=SUM(A4,B5)" could also be values of formula1.
Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            wholeNumber: {
                formula1: 0,
                operator: "GreaterThan"
            }
        };

    return context.sync();
})

その他の二項演算子のリストについては、「BasicDataValidation」を参照してください。See BasicDataValidation for a list of the other binary operators.

また、2 個の三項演算子、"Between" と "NotBetween" もあります。There are also two ternary operators: "Between" and "NotBetween". これらを使用するには、省略可能な formula2 プロパティを指定する必要があります。To use these, you must specify the optional formula2 property. formula1 and formula2の値はバウンディング オペランドです。The formula1 and formula2 values are the bounding operands. ユーザーがセルに入力しようとする値は、第三の (評価済み) オペランドです。The value that the user tries to enter in the cell is the third (evaluated) operand. "Between" 演算子の使用例を次に示します。The following is an example of using the "Between" operator:

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            decimal: {
                formula1: 0,
                formula2: 100,
                operator: "Between"
            }
        };

    return context.sync();
})

次の 2 つのルール プロパティは、値として DateTimeDataValidation オブジェクトをとります。The next two rule properties take a DateTimeDataValidation object as their value.

  • date
  • time

DateTimeDataValidation オブジェクトは BasicDataValidation と同様に構成されています。つまり、プロパティ formula1formula2、および operator があり、同じ方法で使用されます。The DateTimeDataValidation object is structured similarly to the BasicDataValidation: it has the properties formula1, formula2, and operator, and is used in the same way. ただし、数式プロパティで数値が使用できない代わりに ISO 8606 datetime 文字列 (または Excel の式) を入力できる点が異なります。The difference is that you cannot use a number in the formula properties, but you can enter a ISO 8606 datetime string (or an Excel formula). 次に、2018 年 4 月の最初の週の日付として有効な値を定義する例を示します。The following is an example that defines valid values as dates in the first week of April, 2018.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");

    range.dataValidation.rule = {
            date: {
                formula1: "2018-04-01",
                formula2: "2018-04-08",
                operator: "Between"
            }
        };

    return context.sync();
})

リスト入力規則のタイプList validation rule type

有限リストからの値のみを有効な値として指定するには、DataValidationRule オブジェクトの list プロパティを使用します。Use the list property in the DataValidationRule object to specify that the only valid values are those from a finite list. 次に例を示します。The following is an example. このコードについては、次の点に注意してください。Note the following about this code:

  • "Names" という名前のワークシートがあり、"A1:A3" の範囲の値が名前になっていると仮定します。It assumes that there is a worksheet named "Names" and that the values in the range "A1:A3" are names.
  • source プロパティは有効な値のリストを指定します。The source property specifies the list of valid values. 文字列引数は名前を含む範囲を参照します。The string argument refers to a range containing the names. "Sue, Ricky, Liz" など、カンマで区切られたリストを割り当てることもできます。You can also assign a comma-delimited list; for example: "Sue, Ricky, Liz".
  • inCellDropDown プロパティは、ユーザーがセルを選択したときにセルにドロップダウン コントロールを表示するかどうかを指定します。The inCellDropDown property specifies whether a drop-down control will appear in the cell when the user selects it. true に設定した場合、ドロップダウンには source からの値のリストが表示されます。If set to true, then the drop-down appears with the list of values from the source.
Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");   
    var nameSourceRange = context.workbook.worksheets.getItem("Names").getRange("A1:A3");

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

    return context.sync();
})

カスタムの入力規則のタイプCustom validation rule type

カスタムの入力規則式を指定するには、DataValidationRule オブジェクトの custom プロパティを使用します。Use the custom property in the DataValidationRule object to specify a custom validation formula. 次に例を示します。The following is an example. このコードについては、次の点に注意してください。Note the following about this code:

  • ワークシートの A 列と B 列に Athlete NameComments という列がある、2 列のテーブルがあると仮定します。It assumes there is a two-column table with columns Athlete Name and Comments in the A and B columns of the worksheet.
  • Comments 列の冗長性を軽減するために、アスリート名を含むデータを無効にします。To reduce verbosity in the Comments column, it makes data that includes the athlete's name invalid.
  • SEARCH(A2,B2) は A2 内の文字列の開始位置 (B2 内の文字列での) を返します。SEARCH(A2,B2) returns the starting position, in string in B2, of the string in A2. A2 が B2 に含まれていない場合は数値を返しません。If A2 is not contained in B2, it does not return a number. ISNUMBER() はブール値を返します。ISNUMBER() returns a boolean. そのため、formula プロパティは、コメント列の有効なデータがアスリート名列内の文字列を含まないデータであることを示します。So the formula property says that valid data for the Comment column is data that does not include the string in the Athlete Name column.
Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");
    var commentsRange = sheet.tables.getItem("AthletesTable").columns.getItem("Comments").getDataBodyRange();

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

    return context.sync();
})

入力規則のエラー アラートを作成するCreate validation error alerts

ユーザーがセルに無効なデータを入力しようとした際に表示される、カスタムのエラー アラートを作成できます。You can a create custom error alert that appears when a user tries to enter invalid data in a cell. 次に簡単な例を示します。The following is a simple example. このコードについては、次の点に注意してください。Note the following about this code:

  • style プロパティは、ユーザーが情報アラート、警告、または "停止" アラートを取得するかどうかを決定します。The style property determines whether the user gets an informational alert, a warning, or a "stop" alert. ユーザーによる無効なデータの追加を実際に防止するのは Stop のみです。Only Stop actually prevents the user from adding invalid data. いずれにせよ、WarningInformation のポップアップには、ユーザーが無効なデータを入力できるオプションがあります。The pop-up for Warning and Information has options that allow the user enter the invalid data anyway.
  • showAlert プロパティの既定値は true です。The showAlert property defaults to true. つまり、showAlertfalse に設定するか、カスタムのメッセージ、タイトル、スタイルを設定するカスタム アラートを作成しない限り、Excel ホストは (Stop タイプの) 汎用アラートをポップアップ表示します。This means that the Excel host will pop-up a generic alert (of type Stop) unless you create a custom alert which either sets showAlert to false or sets a custom message, title, and style. このコードでは、カスタムのメッセージとタイトルを設定します。This code sets a custom message and title.
Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");

    range.dataValidation.errorAlert = {
            message: "Sorry, only positive whole numbers are allowed",
            showAlert: true, // default is 'true'
            style: "Stop", // other possible values: Warning, Information
            title: "Negative or Decimal Number Entered"
        };

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

    return context.sync();
})

詳細については、「DataValidationErrorAlert」を参照してください。For more information, see DataValidationErrorAlert.

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

ユーザーがデータの入力規則が適用されたセルの上でカーソルを動かすか、データの入力規則が適用されたセルを選択した場合に表示される、説明用のダイアログを作成できます。You can create an instructional prompt that appears when a user hovers over, or selects, a cell to which data validation has been applied. 例を次に示します。The following is an example:

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("B2:C5");

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

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

    return context.sync();
})

詳細については、「DataValidationPrompt」を参照してください。For more information, see DataValidationPrompt.

範囲からデータの入力規則を削除するRemove data validation from a range

範囲からデータの入力規則を削除するには、Range.dataValidation.clear() メソッドを呼び出します。To remove data validation from a range, call the Range.dataValidation.clear() method.

myrange.dataValidation.clear()

消去する範囲とデータの入力規則を追加した範囲とが、まったく同じになる必要はありません。It isn't necessary that the range you clear is exactly the same range as a range on which you added data validation. 同じでない場合は、2 つの範囲に重複するセルがあると、それらのセルのみが消去されます。If it isn't, only the overlapping cells, if any, of the two ranges are cleared.

注意

範囲からデータの入力規則を削除すると、ユーザーが手動で範囲に追加したデータの入力規則も削除されます。Clearing data validation from a range will also clear any data validation that a user has added manually to the range.

関連項目See also