雪ん子パースペクティヴ

確かに、Power BIでも日付は階層になるわ。

はろー、yukiです。

Excelマクロでテーブル操作する時、日付型の列は数値型や文字列型と扱いが異なります。

例えば、下図のようなケースです。

この図を見れば、日付が階層になっているのは明らかですが、これが多次元配列というのは知っていましたか？

まず、基本的にシステムでは、日付を取り扱う時はシリアルで考えます。

すなわち、下記の要領です。

20240101, 20240102, 20240103, .., 20250101, 20250102, 20250103, .., 20260101, 20260102, 20260103, .., yyyymmdd

こうすることで、翌日翌月翌年の計算は、シンプルな加算で済みます。

次に、日付を多次元配列で表します。

[2024, 2025, 2026, .., year[1, 2, 3, .., month[1, 2, 3, .., day]]]

配列は[] を使いますが、3つをネスト（入れ子）にすることで多次元を表します。

上記は、一次元に「日」、二次元に「月」、三次元に「年」の構成になっています。

2つを比較してみると、人が「年月日」を考える時、どちらかと言えば後者（多次元配列）で考えていることが分かります。

また、Excelテーブルにおいても、日付フィルターは多次元配列の扱いになります。

正確に言うと、数値や文字列の列とは扱いが異なります。

日付型の列でフィルタリングする時には、下図の例を使います。

　※2026年1月1日、2025年2月、2024年の3か所にチェック

OfficeScriptを使った確認

上図のテーブルに対し、下記のコードを実行します。

　※getCriteriaメソッドでフィルター条件を取得

function main(workbook: ExcelScript.Workbook) {
    const table = workbook.getTable("verifyDateFilter");
    // 0: No, 1: abc, 2: date
    const criteria = table.getAutoFilter().getCriteria()[2];

    if (criteria) {
        console.log(JSON.stringify(criteria, null, 2));
    }
}

まず、フィルターをしていない時のログです。

　※フィルター条件を入れると、2つまでは"criterion1"と"criterion2"に入り、それ以上は"values"に入ります

// 1. フィルターしていない場合
{
  "@odata.type": "Microsoft.ExcelServices.FilterCriteria",
  "color": null,
  "criterion1": null,
  "criterion2": null,
  "filterOn": "BottomItems",
  "subField": null,
  "dynamicCriteria": "Unknown",
  "icon": null,
  "operator": "And",
  "values": null
}

数値型の列（No）でフィルターした結果です。

// 2. No列の"1", "2"にチェックを入れた場合（"criterion"に値が入る）
{
  "@odata.type": "Microsoft.ExcelServices.FilterCriteria",
  "color": null,
  "criterion1": "=1",
  "criterion2": "=2",
  "filterOn": "Custom",
  "subField": null,
  "dynamicCriteria": "Unknown",
  "icon": null,
  "operator": "Or",
  "values": null
}

// 3. No列の"1", "2", "3"にチェックを入れた場合（"values"に値が入る）
{
  "@odata.type": "Microsoft.ExcelServices.FilterCriteria",
  "color": null,
  "criterion1": null,
  "criterion2": null,
  "filterOn": "Values",
  "subField": null,
  "dynamicCriteria": "Unknown",
  "icon": null,
  "operator": "And",
  "values": [
    "1",
    "2",
    "3"
  ]
}

文字列型の列（abc）でフィルターした結果です（同じ箇所は省略）。

// 4. abc列の"a", "b"にチェックを入れた場合
{
  "criterion1": "=a",
  "criterion2": "=b",
}

// 5. abc列の"a", "b", "c"にチェックを入れた場合
{
  "values": [
    "a",
    "b",
    "c"
  ]
}

日付型の列（date）でフィルターした結果です。

// 6. date列の"2026年1月1日"にチェックを入れた場合
{
  "@odata.type": "Microsoft.ExcelServices.FilterCriteria",
  "color": null,
  "criterion1": null,
  "criterion2": null,
  "filterOn": "Values",
  "subField": null,
  "dynamicCriteria": "Unknown",
  "icon": null,
  "operator": "And",
  "values": [
    {
      "@odata.type": "Microsoft.ExcelServices.FilterDatetime",
      "specificity": "Year",
      "date": "2026-01-01"
    }
  ]
}

// 7. date列の"2026年1月1日", "2025年2月", "2024年"にチェックを入れた場合
{
  "@odata.type": "Microsoft.ExcelServices.FilterCriteria",
  "color": null,
  "criterion1": null,
  "criterion2": null,
  "filterOn": "Values",
  "subField": null,
  "dynamicCriteria": "Unknown",
  "icon": null,
  "operator": "And",
  "values": [
    {
      "@odata.type": "Microsoft.ExcelServices.FilterDatetime",
      "specificity": "Day",
      "date": "2026-01-01"
    },
    {
      "@odata.type": "Microsoft.ExcelServices.FilterDatetime",
      "specificity": "Month",
      "date": "2025-02-01"
    },
    {
      "@odata.type": "Microsoft.ExcelServices.FilterDatetime",
      "specificity": "Year",
      "date": "2024-01-01"
    }
  ]
}

6. の結果にあるように、日付型の列でフィルタリングをすると、他列とは違い、フィルター条件が単一であっても"criterion1"が使われません。

日付型の列では、フィルターは年, 月, 日の単位でフィルタリングができます。

7. の結果にあるように、それぞれの単位でフィルタリングした場合、"specificity"パラメーターによって明示的にそれらが表されます。

なお、この出力結果では、日付フィルターは"values"内でspecificityラベルを付して一次元的に処理されていることに留意。

Excelを分解した確認

Excelのファイル名変更から、拡張子を.zipに変えることで、Excelを構造的に調べます。

テーブルのxmlファイルは、 xl > tables 内にtable1.xml があります。

数値型の列でフィルターしたものです。

ーーーーー

<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" id="1" name="verifyDateFilter" displayName="verifyDateFilter" ref="A1:C7" totalsRowShown="0">

<autoFilter ref="A1:C7">
<filterColumn colId="0">
<filters>
<filter val="1"/>
<filter val="2"/>
</filters>
</filterColumn>
<filterColumn colId="1"/>
<filterColumn colId="2"/>
</autoFilter>
<tableColumns count="3">
<tableColumn id="1" name="No"/>
<tableColumn id="2" name="abc"/>
<tableColumn id="3" name="date" dataDxfId="0"/>
</tableColumns>
<tableStyleInfo name="TableStyleMedium9" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0"/>
</table>

ーーーーー

フィルターは"filters"タグで確認できます。

<filter val="1"/>, <filter val="2"/>とあるように、数値の1, 2でフィルタリングされているのが分かります。

文字列型の列でフィルターしたものです（重複箇所は省略）。

結果は数値型の列と変わりません。

ーーーーー

<autoFilter ref="A1:C7">
<filterColumn colId="0"/>
<filterColumn colId="1">
<filters>
<filter val="a"/>
<filter val="c"/>
<filter val="e"/>
</filters>
</filterColumn>
<filterColumn colId="2"/>
</autoFilter>

ーーーーー

<filter val="a"/>, <filter val="c"/>, <filter val="e"/>の通り、文字列を条件にしたフィルタリングです。

日付型の列でフィルターしたものです（重複箇所は省略）。

ーーーーー

<autoFilter ref="A1:C7">

<filterColumn colId="0"/>
<filterColumn colId="1"/>
<filterColumn colId="2">
<filters>
<dateGroupItem year="2026" month="1" day="1" dateTimeGrouping="day"/>
<dateGroupItem year="2025" month="2" dateTimeGrouping="month"/>
<dateGroupItem year="2024" dateTimeGrouping="year"/>
</filters>
</filterColumn>
</autoFilter>