構造化参照式

Spread.Sheetsでは、テーブル内で構造化参照による数式がサポートされます。

構造化参照による数式では、キーワード、およびテーブルの列名を使用して、テーブル内のセル範囲を参照します。

次の図は、構造化参照を構成する各コンポーネントを示します。この式は、売上合計と税額を加算します。

1. テーブル名には、実際のテーブルデータ（ヘッダ行と合計行が含まれる場合はこれらを除外）を参照するために、分かりやすい名前を指定します。

2. 列指定子は列ヘッダから抽出され、角かっこで囲まれます。列指定子は列データ（列ヘッダと合計行が含まれる場合はこれらを除外）を参照します。

3. 特殊項目指定子を使用すると、合計行など、テーブル内の特殊な部分を参照できます。

4. テーブル指定子は、構造化参照の外側の部分であり、テーブル名の後ろに角かっこで囲みます。

5. 構造化参照とは、テーブル名で始まり、テーブル指定子で終了する文字列全体を指します。

特殊項目を使用すると、テーブルのさまざまな領域を参照し、数式内で簡単に使用できます。次の表は、構造化参照で使用可能な特殊項目指定子の一覧を示します。

「#This Row」項目は「@」として簡略表記できます。たとえば、=DeptSales[#This Row]は=DeptSales[@]と同じです。

「#Data」項目は「#Headers」または「#Totals」と組み合わせて使用できます。次の表に、例を示します。

特殊項目を指定しない場合は、次の表に示すとおりのデータとみなされます。

列指定子は、列ヘッダおよび合計を除く、データ列全体への参照を表します。次の表は、列指定子の種類を示します。

テーブル、列、および特殊項目の指定子はすべて、それぞれ一対の角かっこ[ ]で囲む必要があります。内部に他の指定子を含む指定子の場合は、内部の指定子を一対の角かっこで囲み、さらにその周囲を一対の角かっこで囲む必要があります。複数のポリシーが競合する場合は、最高～最低の優先順位に従います。

角かっこの用法には、以下のルールが適用されます。

1. 指定子をいっさい使用しない場合は、テーブル指定子の角かっこを省略できます。たとえば、"=DeptSales[]"と"=DeptSales"は同じです。

2. 特殊項目が1つだけ、または列指定子が1つだけの場合は、テーブル指定子の角かっこは省略できます。たとえば、"=DeptSales[[#Data]]"と"=DeptSales[#Data]"は同じであり、"=DeptSales[[TaxAmt]]"と"=DeptSales[TaxAmt]"は同じです。

3. 列指定子を使用する場合、または複数の特殊項目を組み合わせて使用する場合は、特殊項目を一対の角かっこで囲む必要があります。たとえば、"=DeptSales[#Data, [TaxAm]]"や"=DeptSales[#Data, #Totals]"は許容されません。

4. テーブルの列ヘッダに、次のいずれかの特殊文字が使用されている場合は、列ヘッダ全体を角かっこで囲む必要があります。スペース、タブ、改行、キャリッジリターン、コンマ（,）、コロン（:）、ピリオド（.）、左角かっこ（[）、右角かっこ（]）、ポンド記号（#）、単一引用符（'）、二重引用符（"）、左中かっこ（{）、右中かっこ（}）、ドル記号（$）、キャレット（^）、アンパサンド（&）、アスタリスク（*）、プラス記号（+）、等号（=）、マイナス記号（-）、より大記号（>）、より小記号（<）、除算記号（//）。たとえば、"#column1"という列名がある場合、"=Table1[#column1]"という式は許容されません。"=Table1['#column1]"とする必要があります。

5. 列範囲の指定子を使用する場合は、いずれかの列指定子を角かっこで囲む必要があります。たとえば、"=Table1[column1:column2]"は許容されません。これは、"=Table1[[column1]:column2]"、"=Table1[column1:[column2]]"、または"=Table1[[column1]:[column2]]"とする必要があります。

6. The '@'特殊項目の角かっこは省略できます。たとえば、"=DeptSales[@, TaxAmt]"と"=DeptSales[[@], TaxAmt]"は同じです。

また、次の汎用ルールも適用されます。

1. テーブル、列、および特殊項目の指定子はすべて、大文字と小文字が区別されません。

2. 以下の記号には特別な意味があるため、エスケープ文字として単一引用符（'）を付ける必要があります。左角かっこ（[）、右角かっこ（]）、ポンド記号（#）、単一引用符（'）。たとえば、"=Table1[colu#mn1]"は許容されず、"=Table1[colu'#mn1]"とする必要があります。

3. 特殊項目や列指定子を組み合わせて使用する場合は、それぞれをコンマ（,）で区切ります。たとえば、"=DeptSales[[#Data], [#Totals], TaxAmt]"のように指定します。

4. 特殊項目と列指定子の並び順は、優先順位を意味するものではありません。たとえば、"=DeptSales[[#Data], [#Totals], TaxAmt]"と"=DeptSales[[#Totals], TaxAmt, [#Data]]"は同じです。

5. '@'特殊項目の後のコンマは省略できます。たとえば、"=DeptSales[@, TaxAmt]"と"=DeptSales[@TaxAmt]"は同じです。

テーブル内の数式に構造化参照を追加するには、setColumnDataFormulaおよびsetColumnFormulaメソッドを使用します。テーブル領域の外部にある数式からテーブルデータを参照することもできます。たとえば、activeSheet.getCell(7,1).formula("SUM(Table1[SubTotal])");という数式を作成できます。列とセルの数式において、最後に設定した数式が常に優先されます。

数式内の列指定子は、ヘッダおよびフッタ内のセルには適用されません。

コードの使用

次のサンプルコードは、テーブルを作成し、構造化参照式を使用して集計を行います。

activeSheet.tables.add("Table1", 0, 0, 4, 3, GC.Spread.Sheets.Tables.TableThemes.dark1);
activeSheet.getCell(0,0).text("Value1");
activeSheet.getCell(0,1).text("Value2");
activeSheet.getCell(0,2).text("SubTotal");
activeSheet.getCell(1,0).text("1");
activeSheet.getCell(2,0).text("2");
activeSheet.getCell(3,0).text("3");
activeSheet.getCell(1,1).text("5");
activeSheet.getCell(2,1).text("5");
activeSheet.getCell(3,1).text("5");
var sTable = activeSheet.tables.findByName("Table1").setColumnDataFormula(2, "=[Value1]*[Value2]");
sTable.showFooter(true);
//set footer value
sTable.setColumnValue(0, "Total");
sTable.setColumnFormula(2, "SUM(Table1[SubTotal])");
activeSheet.getColumn(0).width(80);
activeSheet.getColumn(1).width(80);
activeSheet.getColumn(2).width(80);
//activeSheet.getCell(7,1).formula("SUM(Table1[SubTotal])");