Excel JavaScript API を使用して範囲を操作する (高度)Work with ranges using the Excel JavaScript API (advanced)

この記事は、「Excel JavaScript API を使用して範囲を操作する (基本)」の情報に基づいており、コード サンプルでは Excel JavaScript API を使って範囲のより高度なタスクを実行する方法を示します。This article builds upon information in Work with ranges using the Excel JavaScript API (fundamental) by providing code samples that show how to perform more advanced tasks with ranges using the Excel JavaScript API. Range オブジェクトがサポートするプロパティとメソッドの完全な一覧については、「Range Object (JavaScript API for Excel)」を参照してください。For the complete list of properties and methods that the Range object supports, see Range Object (JavaScript API for Excel).

Moment-MSDate プラグインを使用した日付の操作Work with dates using the Moment-MSDate plug-in

Moment JavaScript ライブラリにより、日付とタイムスタンプが便利に使用できるようになります。The Moment JavaScript library provides a convenient way to use dates and timestamps. Moment-MSDate プラグインは、日付と時刻の形式を Excel に適したものに変換します。The Moment-MSDate plug-in converts the format of moments into one preferable for Excel. これは、NOW 関数から返される形式と同じです。This is the same format the NOW function returns.

次のコードは、範囲 B4 に時刻のタイムスタンプを設定する方法を示しています。The following code shows how to set the range at B4 to a moment's timestamp:

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");

    var now = Date.now();
    var nowMoment = moment(now);
    var nowMS = nowMoment.toOADate();

    var dateRange = sheet.getRange("B4");
    dateRange.values = [[nowMS]];

    dateRange.numberFormat = [["[$-409]m/d/yy h:mm AM/PM;@"]];

    return context.sync();
}).catch(errorHandlerFunction);

これは、次の例に示すように、セルから日付を取得して、その日付を時刻などの形式に変換するのと同様の手法です。It is a similar technique to get the date back out of the cell and convert it to a moment or other format, as demonstrated in the following code:

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");

    var dateRange = sheet.getRange("B4");
    dateRange.load("values");

    return context.sync().then(function () {
        var nowMS = dateRange.values[0][0];

        // log the date as a moment
        var nowMoment = moment.fromOADate(nowMS);
        console.log(`get (moment): ${JSON.stringify(nowMoment)}`);

        // log the date as a UNIX-style timestamp
        var now = nowMoment.unix();
        console.log(`get (timestamp): ${now}`);
    });
}).catch(errorHandlerFunction);

アドインでは、わかりやすい形式で日付が表示されるように、範囲の書式を設定する必要があります。Your add-in will have to format the ranges to display the dates in a more human-readable form. たとえば、"[$-409]m/d/yy h:mm AM/PM;@" では時刻が "12/3/18 3:57 PM" のように表示されます。The example of "[$-409]m/d/yy h:mm AM/PM;@" displays a time like "12/3/18 3:57 PM". 日付と時刻の数値書式の詳細については、「表示形式のカスタマイズに関するガイドラインを確認する」の記事で「日付と時刻の表示に関するガイドライン」を参照してください。For more information about date and time number formats, please see the "Guidelines for date and time formats" in the Review guidelines for customizing a number format article.

複数の範囲を同時に操作するWork with multiple ranges simultaneously

Rangeareasオブジェクトを使用すると、アドインで複数の範囲に対して一度に操作を実行できます。The RangeAreas object lets your add-in perform operations on multiple ranges at once. これらの範囲は、連続していても連続していなくても構いません。These ranges may be contiguous, but do not have to be. RangeAreas については、「Excel アドインで複数の範囲を同時に操作する」にさらに詳しい説明があります。RangeAreas are further discussed in the article Work with multiple ranges simultaneously in Excel add-ins.

範囲内の特殊なセルを検索するFind special cells within a range

範囲getSpecialCellsOrNullObjectメソッドは、セルの特性とセルの値の種類に基づいて範囲を検索します。The Range.getSpecialCells and Range.getSpecialCellsOrNullObject methods find ranges based on the characteristics of their cells and the types of values of their cells. これらのメソッドでは両方とも、RangeAreas オブジェクトが返されます。Both of these methods return RangeAreas objects. 次に示すのは、TypeScript データ型ファイルの、このメソッドのシグネチャです。Here are the signatures of the methods from the TypeScript data types file:

getSpecialCells(cellType: Excel.SpecialCellType, cellValueType?: Excel.SpecialCellValueType): Excel.RangeAreas;
getSpecialCellsOrNullObject(cellType: Excel.SpecialCellType, cellValueType?: Excel.SpecialCellValueType): Excel.RangeAreas;

次の例では、getSpecialCells メソッドを使用して、数式を含むすべてのセルを検索します。The following example uses the getSpecialCells method to find all the cells with formulas. このコードの注意点は次のとおりです。About this code, note:

  • 検索が必要なシートの部分を制限するために、まず Worksheet.getUsedRange を呼び出し、その範囲に関してのみ getSpecialCells を呼び出します。It limits the part of the sheet that needs to be searched by first calling Worksheet.getUsedRange and calling getSpecialCells for only that range.
  • getSpecialCells メソッドは RangeAreas オブジェクトを返すため、数式を含むセルはすべて、連続していないセルであっても、ピンク色になります。The getSpecialCells method returns a RangeAreas object, so all of the cells with formulas will be colored pink even if they are not all contiguous.
Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var usedRange = sheet.getUsedRange();
    var formulaRanges = usedRange.getSpecialCells(Excel.SpecialCellType.formulas);
    formulaRanges.format.fill.color = "pink";

    return context.sync();
})

対象の特性を含むセルが範囲内に存在しない場合、getSpecialCells によって ItemNotFound エラーがスローされます。If no cells with the targeted characteristic exist in the range, getSpecialCells throws an ItemNotFound error. この場合、制御のフローが catch ブロックに移ります (存在する場合)。This diverts the flow of control to a catch block, if there is one. catchブロックがない場合、エラーによってメソッドは停止します。If there isn't a catch block, the error halts the method.

対象の特性を含むセルが常に存在するはずである場合、そうしたセルが存在しないなら、コードを使ってエラーをスローする必要があるかもしれません。If you expect that cells with the targeted characteristic should always exist, you'll likely want your code to throw an error if those cells aren't there. 一致するセルがないということが有効なシナリオでは、コードでこのような可能性があるかどうかを確認し、あれば、エラーをスローせずに適切に処理するようにしておく必要があります。If it's a valid scenario that there aren't any matching cells, your code should check for this possibility and handle it gracefully without throwing an error. getSpecialCellsOrNullObject メソッドと、返された isNullObject プロパティを使用して、この動作を実現できます。You can achieve this behavior with the getSpecialCellsOrNullObject method and its returned isNullObject property. 次のサンプルでは、このパターンを使用しています。The following example uses this pattern. このコードの注意点は次のとおりです。About this code, note:

  • getSpecialCellsOrNullObject メソッドは常にプロキシ オブジェクトを返します。そのため、通常の JavaScript 使用環境では null となることはありません。The getSpecialCellsOrNullObject method always returns a proxy object, so it is never null in the ordinary JavaScript sense. ただし一致するセルが見つからなかった場合、オブジェクトの isNullObject プロパティは true に設定されます。But if no matching cells are found, the isNullObject property of the object is set to true.
  • isNullObject プロパティをテストするに、context.sync を呼び出します。It calls context.sync before it tests the isNullObject property. これは、すべての *OrNullObject メソッドとプロパティの必要条件です。プロパティを読み取るためには常に、そのプロパティをロードして同期する必要があるためです。This is a requirement with all *OrNullObject methods and properties, because you always have to load and sync a property in order to read it. ただし、明示的isNullObject プロパティをロードする必要はありません。However, it is not necessary to explicitly load the isNullObject property. load がオブジェクトに対して呼び出されていない場合であっても、プロパティは context.sync によって自動的にロードされます。It is automatically loaded by the context.sync even if load is not called on the object. 詳細については、「*OrNullObject メソッド」を参照してください。For more information, see *OrNullObject.
  • このコードをテストするには、最初に数式を含まないセルの範囲を選択してからコードを実行します。You can test this code by first selecting a range that has no formula cells and running it. 次に、少なくとも 1 つのセルが数式を含む範囲を選択してからコードを再実行します。Then select a range that has at least one cell with a formula and run it again.
Excel.run(function (context) {
    var range = context.workbook.getSelectedRange();
    var formulaRanges = range.getSpecialCellsOrNullObject(Excel.SpecialCellType.formulas);
    return context.sync()
        .then(function() {
            if (formulaRanges.isNullObject) {
                console.log("No cells have formulas");
            }
            else {
                formulaRanges.format.fill.color = "pink";
            }
        })
        .then(context.sync);
})

わかりやすくするため、この記事内のすべての他の例では、getSpecialCells メソッドを getSpecialCellsOrNullObject の代わりに使用しています。For simplicity, all other examples in this article use the getSpecialCells method instead of getSpecialCellsOrNullObject.

セルの値の型に応じて対象のセルを絞り込むNarrow the target cells with cell value types

Range.getSpecialCells() メソッドと Range.getSpecialCellsOrNullObject() メソッドでは、対象セルをさらに絞り込むためにオプションとして使用される 2 番目のパラメーターを承諾します。The Range.getSpecialCells() and Range.getSpecialCellsOrNullObject() methods accept an optional second parameter used to further narrow down the targeted cells. この 2 番目のパラメーターは、特定の種類の値を含むセルのみを指定するために使用される Excel.SpecialCellValueType パラメーターです。This second parameter is an Excel.SpecialCellValueType you use to specify that you only want cells that contain certain types of values.

注意

Excel.SpecialCellValueType パラメーターは、Excel.SpecialCellTypeExcel.SpecialCellType.formulas または Excel.SpecialCellType.constants の場合にのみ使用できます。The Excel.SpecialCellValueType parameter can only be used if the Excel.SpecialCellType is Excel.SpecialCellType.formulas or Excel.SpecialCellType.constants.

単一のセル値の型のテストTest for a single cell value type

Excel.SpecialCellValueType 列挙型には、次の 4 つの基本型があります (このセクションで後述する他の値の組み合わせに加えて)。The Excel.SpecialCellValueType enum has these four basic types (in addition to the other combined values described later in this section):

  • Excel.SpecialCellValueType.errors
  • Excel.SpecialCellValueType.logical (ブール型)Excel.SpecialCellValueType.logical (which means boolean)
  • Excel.SpecialCellValueType.numbers
  • Excel.SpecialCellValueType.text

次の例では、数値定数である特殊なセルを検索し、そのセルをピンク色にします。The following example finds special cells that are numerical constants and colors those cells pink. このコードの注意点は次のとおりです。About this code, note:

  • リテラル数値を持つセルのみ強調表示されます。It will only highlight cells that have a literal number value. 数式 (結果が数字の場合であっても)、ブール値、テキストを持つセル、およびエラー状態にあるセルは強調表示されません。It will not highlight cells that have a formula (even if the result is a number) or a boolean, text, or error state cells.
  • コードをテストするには、リテラル数値を持ついくつかのセル、他の型のリテラル値を持ついくつかのセル、そして数式を持ついくつかのセルをそれぞれワークシートに含めるようにしてください。To test the code, be sure the worksheet has some cells with literal number values, some with other kinds of literal values, and some with formulas.
Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var usedRange = sheet.getUsedRange();
    var constantNumberRanges = usedRange.getSpecialCells(
        Excel.SpecialCellType.constants,
        Excel.SpecialCellValueType.numbers);
    constantNumberRanges.format.fill.color = "pink";

    return context.sync();
})

複数のセル値の型のテストTest for multiple cell value types

テキスト値のセルすべてとブール値 (Excel.SpecialCellValueType.logical) のセルすべてなど、セル値の型を複数操作する必要がある場合もあります。Sometimes you need to operate on more than one cell value type, such as all text-valued and all boolean-valued (Excel.SpecialCellValueType.logical) cells. Excel.SpecialCellValueType 列挙型には、結合された型の値があります。The Excel.SpecialCellValueType enum has values with combined types. たとえば、Excel.SpecialCellValueType.logicalText は、すべてのブール値のセルとテキスト値のセルを対象としています。For example, Excel.SpecialCellValueType.logicalText targets all boolean and all text-valued cells. Excel.SpecialCellValueType.all は既定値であり、返されるセル値の型は制限されません。Excel.SpecialCellValueType.all is the default value, which does not limit the cell value types returned. 次の例では、結果が数値またはブール値となる数式を含むすべてのセルが色付けされます。The following example colors all cells with formulas that produce number or boolean value.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    var usedRange = sheet.getUsedRange();
    var formulaLogicalNumberRanges = usedRange.getSpecialCells(
        Excel.SpecialCellType.formulas,
        Excel.SpecialCellValueType.logicalNumbers);
    formulaLogicalNumberRanges.format.fill.color = "pink";

    return context.sync();
})

Copy and pasteCopy and paste

このメソッドは、Excel UI のコピーと貼り付けの動作をレプリケートしますThe Range.copyFrom method replicates the copy-and-paste behavior of the Excel UI. copyFrom が呼び出される範囲オブジェクトがコピー先になります。The range object that copyFrom is called on is the destination. コピーされるソースは、範囲または範囲を表す文字列のアドレスとして渡されます。The source to be copied is passed as a range or a string address representing a range.

次のコード サンプルでは、A1:E1 のデータを G1 で始まる範囲にコピーします (この貼り付けは G1:K1 で終わります)。The following code sample copies the data from A1:E1 into the range starting at G1 (which ends up pasting into G1:K1).

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    // copy everything from "A1:E1" into "G1" and the cells afterwards ("G1:K1")
    sheet.getRange("G1").copyFrom("A1:E1");
    return context.sync();
}).catch(errorHandlerFunction);

Range.copyFrom には、省略可能なパラメーターが 3 つあります。Range.copyFrom has three optional parameters.

copyFrom(sourceRange: Range | RangeAreas | string, copyType?: Excel.RangeCopyType, skipBlanks?: boolean, transpose?: boolean): void;

copyType では、ソースからコピー先にコピーされるデータを指定します。copyType specifies what data gets copied from the source to the destination.

  • Excel.RangeCopyType.formulas では、ソースのセルの数式が転送され、それらの数式の範囲の相対配置は保持されます。Excel.RangeCopyType.formulas transfers the formulas in the source cells and preserves the relative positioning of those formulas’ ranges. 任意の数式以外のエントリはそのままコピーされます。Any non-formula entries are copied as-is.
  • Excel.RangeCopyType.values では、データ値と、数式の場合は数式の結果をコピーします。Excel.RangeCopyType.values copies the data values and, in the case of formulas, the result of the formula.
  • Excel.RangeCopyType.formats では、フォント、色、およびその他の書式設定を含む、範囲の書式設定をコピーしますが、値はコピーしません。Excel.RangeCopyType.formats copies the formatting of the range, including font, color, and other format settings, but no values.
  • Excel.RangeCopyType.all (既定のオプション) では、データと書式設定の両方がコピーされます。見つかった場合、セルの数式は保持されます。Excel.RangeCopyType.all (the default option) copies both data and formatting, preserving cells’ formulas if found.

skipBlanks では、空白セルをコピー先にコピーするかどうかを設定します。skipBlanks sets whether blank cells are copied into the destination. true の場合、copyFrom ではソースの範囲にある空白セルはスキップされます。When true, copyFrom skips blank cells in the source range. スキップされたセルでは、コピー先の範囲内の対応するセルにある既存のデータを上書きすることはありません。Skipped cells will not overwrite the existing data of their corresponding cells in the destination range. 既定値は false です。The default is false.

transpose では、ソースの場所へのデータの行と列の入れ替えを行うかどうかを決定します。transpose determines whether or not the data is transposed, meaning its rows and columns are switched, into the source location. 行と列を入れ替える範囲は対角線で反転されるため、行 123 が列 ABC になります。A transposed range is flipped along the main diagonal, so rows 1, 2, and 3 will become columns A, B, and C.

次のコード サンプルと画像は、この動作をシンプルなシナリオで示しています。The following code sample and images demonstrate this behavior in a simple scenario.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    // copy a range, omitting the blank cells so existing data is not overwritten in those cells
    sheet.getRange("D1").copyFrom("A1:C1",
        Excel.RangeCopyType.all,
        true, // skipBlanks
        false); // transpose
    // copy a range, including the blank cells which will overwrite existing data in the target cells
    sheet.getRange("D2").copyFrom("A2:C2",
        Excel.RangeCopyType.all,
        false, // skipBlanks
        false); // transpose
    return context.sync();
}).catch(errorHandlerFunction);

前の関数が実行される前。Before the preceding function has been run.

範囲のコピー メソッドが実行される前の Excel のデータ

前の関数が実行された後。After the preceding function has been run.

範囲のコピー メソッドが実行された後の Excel のデータ

重複の削除Remove duplicates

指定した列に重複するエントリがある行を削除するには、このメソッドを使用します。The Range.removeDuplicates method removes rows with duplicate entries in the specified columns. このメソッドは、値が最小のインデックスから、範囲内の最大値のインデックス (上から下) までの範囲にある各行を処理します。The method goes through each row in the range from the lowest-valued index to the highest-valued index in the range (from top to bottom). 任意の行で、指定された 1 つまたは複数の列が範囲より前に表示されている場合、その行は削除されます。A row is deleted if a value in its specified column or columns appeared earlier in the range. 範囲にある削除された行の下の行が上に移動します。Rows in the range below the deleted row are shifted up. removeDuplicates は、範囲外にあるセルの位置には影響しません。removeDuplicates does not affect the position of cells outside of the range.

removeDuplicates は、どの重複をチェックするかを示す列インデックスを表す number[] を受け取ります。removeDuplicates takes in a number[] representing the column indices which are checked for duplicates. この配列は、0 から始まり、ワークシートではなく範囲を基準にしています。This array is zero-based and relative to the range, not the worksheet. メソッドには、最初の行がヘッダーであるかどうかを指定するブール値のパラメーターもあります。The method also takes in a boolean parameter that specifies whether the first row is a header. true の場合、重複について考慮するとき最初の行は無視されます。When true, the top row is ignored when considering duplicates. メソッドremoveDuplicatesは、削除RemoveDuplicatesResultされた行数と、残っている一意の行の数を指定するオブジェクトを返します。The removeDuplicates method returns a RemoveDuplicatesResult object that specifies the number of rows removed and the number of unique rows remaining.

範囲のremoveDuplicatesメソッドを使用する場合は、次の点に注意してください。When using a range's removeDuplicates method, keep the following in mind:

  • removeDuplicates は、関数の結果ではなくセルの値を考慮します。removeDuplicates considers cell values, not function results. 2 つの異なる関数が同じ結果として評価される場合、セルの値は重複と見なしません。If two different functions evaluate to the same result, the cell values are not considered duplicates.
  • 空のセルは、removeDuplicates に無視されることはありません。Empty cells are not ignored by removeDuplicates. 空のセルの値は、その他の値と同様に扱われます。The value of an empty cell is treated like any other value. つまり、範囲に含まれる空の行は RemoveDuplicatesResult に含まれることになります。This means empty rows contained within in the range will be included in the RemoveDuplicatesResult.

次の例では、最初の列に重複する値があるエントリを削除する方法を示します。The following sample shows the removal of entries with duplicate values in the first column.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var range = sheet.getRange("B2:D11");

    var deleteResult = range.removeDuplicates([0],true);
    deleteResult.load();

    return context.sync().then(function () {
        console.log(deleteResult.removed + " entries with duplicate names removed.");
        console.log(deleteResult.uniqueRemaining + " entries with unique names remain in the range.");
    });
}).catch(errorHandlerFunction);

前の関数が実行される前。Before the preceding function has been run.

範囲の重複を削除するメソッドが実行される前の Excel のデータ

前の関数が実行された後。After the preceding function has been run.

範囲の重複を削除するメソッドが実行された後の Excel のデータ

アウトラインのデータをグループ化するGroup data for an outline

行またはセル範囲の列は、アウトラインを作成するためにまとめてグループ化することができます。Rows or columns of a range can be grouped together to create an outline. これらのグループを折りたたんで展開し、対応するセルを非表示にして表示することができます。These groups can be collapsed and expanded to hide and show the corresponding cells. これにより、トップ行のデータの簡単な分析が容易になります。This makes quick analysis of top-line data easier. これらのアウトライングループを作成するには、範囲グループを使用します。Use Range.group to make these outline groups.

アウトラインには階層を設定できます。小さなグループは、より大きいグループの下にネストされています。An outline can have a hierarchy, where smaller groups are nested under larger groups. これにより、アウトラインを異なるレベルで表示できるようになります。This allows the outline to be viewed at different levels. 表示されるアウトラインレベルを変更するには、 showOutlineLevelsメソッドを使用してプログラムで実行できます。Changing the visible outline level can be done programmatically through the Worksheet.showOutlineLevels method. Excel では8レベルのアウトライングループのみがサポートされることに注意してください。Note that Excel only supports eight levels of outline groups.

次のコードサンプルでは、行と列の両方に対して2つのレベルのグループを持つアウトラインを作成する方法を示します。The following code sample shows how to create an outline with two levels of groups for both the rows and columns. 次の図は、そのアウトラインのグループを示しています。The subsequent image shows the groupings of that outline. コードサンプルでは、グループ化されている範囲に、アウトラインコントロールの行または列が含まれていないことに注意してください (この例の場合は "集計")。Note that in the code sample, the ranges being grouped do not include the row or column of the outline control (the "Totals" for this example). グループは、コントロールのある行または列ではなく、折りたたまれる内容を定義します。A group defines what will be collapsed, not the row or column with the control.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");

    // Group the larger, main level. Note that the outline controls
    // will be on row 10, meaning 4-9 will collapse and expand.
    sheet.getRange("4:9").group(Excel.GroupOption.byRows);

    // Group the smaller, sublevels. Note that the outline controls
    // will be on rows 6 and 9, meaning 4-5 and 7-8 will collapse and expand.
    sheet.getRange("4:5").group(Excel.GroupOption.byRows);
    sheet.getRange("7:8").group(Excel.GroupOption.byRows);

    // Group the larger, main level. Note that the outline controls
    // will be on column R, meaning C-Q will collapse and expand.
    sheet.getRange("C:Q").group(Excel.GroupOption.byColumns);

    // Group the smaller, sublevels. Note that the outline controls
    // will be on columns G, L, and R, meaning C-F, H-K, and M-P will collapse and expand.
    sheet.getRange("C:F").group(Excel.GroupOption.byColumns);
    sheet.getRange("H:K").group(Excel.GroupOption.byColumns);
    sheet.getRange("M:P").group(Excel.GroupOption.byColumns);
    return context.sync();
}).catch(errorHandlerFunction);

2レベルの2次元のアウトラインがある範囲

行または列グループのグループを解除するには、グループ化を解除するメソッドを使用しますTo ungroup a row or column group, use the Range.ungroup method. これにより、アウトラインから最上位レベルが削除されます。This removes the outermost level from the outline. 同じ行または列の種類の複数のグループが指定された範囲内の同じレベルにある場合、それらすべてのグループはグループ解除されます。If multiple groups of the same row or column type are at the same level within the specified range, all of those groups are ungrouped.

関連項目See also