Excel Cube関数を用いたEUC帳票ツールの検討

0. はじめに

0-1. この記事を読めば

• わかる
  • CubeValue, CubeMember関数を用いた帳票ツールの一例
• わからない
  • Cube関数の詳細
  • Power Query，Power Pivotの使い方

0-2. 誰のための記事か

社内でエンジニアとして他のユーザーが使うEUCツールを開発している方々の参考になればと考えている．特に異なる部署などにありメンテナンスのタイミングが少なく，継続性・柔軟性に重点を置いて開発している現場を想定する．

0-3. 忙しい方に向けて

帳票は以下のステップで作成する：

1. Power Queryでcsvファイルを取込み，
2. Power Pivotで集計メジャーを作成，
3. Cube関数で社内フォーマットにあわせた帳票を作成．

1,2ではそれぞれでしか行えない処理を任せ，3で前月比の計算や書式の定義を行うものとする．

それにより最終的には以下がクエリの更新とToday関数をもとに自動作成され，そして何よりユーザーが柔軟に帳票フォーマットを変更できる状態を目指す．そのためには極力プルダウンの選択によりレポートを作成してもらうことが効果的だと考えた．

また荒削りではあるがインターフェイスの全体像を表す．オレンジ背景が手動入力セル，赤枠・太字が自動入力セルを表している．

このツールを作成した際に意識した点について次章で解説したい．

1. ツールの設計と意識したこと

本ツールではExcelの標準機能（アドイン含む）であるPower Query，Power Pivot，Cube関数を用いている．これらはそれぞれ「収集」「計算」「インターフェイス」のシートで取り扱う．前の2つには最低限の処理を任せ，Cube関数によりユーザーが状況に応じてフォーマットを変更する設計としている（ピボットテーブルではこれが叶わないと感じる）．

1-1. Power Query

基本的にユーザーがクエリを編集することは想定しないため，ピボット解除などのクエリでしかできない処理のみを担当させる．ただ本ツールで取り込んだデータはすでにロング型となっているため，特段の処理はさせずデータを読み込んだだけのものとなっている．

「F_国内債券_時価評価」クエリについては「収集」シートに読み込み元データを確認できるようにし，「D_銘柄分類」クエリは接続のみに留めた．

（「収集」シートより）

1-2. Power Pivot

同様にユーザーがメジャーを作成することは想定しない．本ツールではあらかじめSum式とSumX式のみを用いて簡単な加重平均のメジャーを作成したが，必要に応じてより高度なものをエンジニアが用意してもよいだろう．ただ前月比などのメジャーは後述するCube関数に任せることで柔軟性を確保できると考えている．

ピボットテーブルは「計算」シートに出力し，Cubu関数で取り出した値の確認に使う．

（「計算」シートより）

1-3. Cube関数

ここではPower Pivotで作成したデータモデルからCubeValue関数とCubeMember関数でメジャーの集計値を取り出してレポートを作成していく．

まずおまじないとして以下のセル名と値を設定する．これはCubeValue関数を用いる際に必須のものとしてユーザーには理解してもらう．

B17(Omajinai) = ThisWorkbookDataModel

レポートは「N_Mode」セルで基準日の設定方法（自動/手動）を選択すると赤枠内が自動更新されるように作成した．

手動の場合は「N_KijunBi_Manual」セルに「ListKijunBi」セルからプルダウンで年月メンバーを選択してもらう形とした（データ タブ->データの入力規則->リスト を活用）．これによりユーザーの判断コストの削減が期待できる．

D20:D31(ListKijunBi)
= CUBEMEMBER(Omajinai,
             "[C_カレンダー].[年月].[" & TEXT("202*/*/1","yyyy年MM月") & "]"
             )

上記式の中の「*」はセルに応じて値を入力する（上では2024/4~2025/3を入力）．

次に「N_Mode」セルと「N_KijunBi_Manual」セルに応じて「基準日」セルを決める．自動の場合は先月が，手動の場合は「N_KijunBi_Manual」セルの年月が選択されるようにする．

D6(基準日)
= IF(N_Mode="自動",
     CUBEMEMBER(Omajinai,
                "[C_カレンダー].[年月].["&TEXT(EOMONTH(TODAY(),-1),"yyyy年MM月")&"]"
                ),
     N_KijunBi_Manual)

また基準日に応じて前月末と前四半期末が計算される．

次に集計対象のメジャーをプルダウンから設定可能とするため，メジャーメンバーのリストを用意し名前を「ListMeasures」とする．

B20:B24(ListMeasures) = CUBEMEMBER(Omajinai, "[Measures].[*]")

基準日のリストと同様にデータの入力規則で「ListMeasures」を入力し，メジャーを選択できるようにする．

最後に分類のディメンジョンについてもメンバーを作成する．ここはリストから選択する形とせず，縦に列挙する形としてみた．

C8:C12 = CUBEMEMBER(Omajinai, "[D_銘柄分類].[分類].[*]")

ここまで準備すると基準日の集計値をCubeValue関数により取り出せるようになる．ユーザーには以下式を用いて柔軟にレポーティングしてもらうことを想定している．

CubeValue関数は調べた範囲においてスピルや配列数式が使えないため，$による古典的なオートフィルで隣接セルを埋めていく．同様に前月，前四半期末の値も取り出すことができる．

最後はスピルを使って簿価・時価等を転記し，金額単位(Unit)を調整する．このような微調整は途中のDAX式ではなく，最後に行うことで保守性が高まると考える．また可能な限りスピル式を用いることで転記ミスを減らす．

（一部列を非表示）

前月比，前四半期末比については減算のスピル式を用いる．

（一部列を非表示）

以上でレポートが完成する．

2. まとめ

Excelで大量のデータを集計してレポーティングをするときはピボットテーブルではなくCube関数を用いることで継続性・柔軟性が高まると考える．

具体的にはCubeMember関数のリストにセル名を付与し，データの入力規則の対象とすることで，ユーザーがプルダウンでの選択から値を取り出しセルに格納する運用が可能である．この運用は複雑なM言語やDAX式を導入しないことから保守性についても向上することが期待される．

-1. 終わりに

現場を見ていると定例的な帳票作成といえどもフォーマットの変更や特定月のみの手補正の追加は広く見られる．その度にエンジニアがM言語やDAX式，VBAを修正していく運用は現実的でないだろう．またそれをできるエンジニアがいつまでもいるとは限らない．

そうなるとユーザー自身が関数やセル書式で出力を修正していく必要が生じ，その時Cube関数による集計値のセル格納が効果を発揮すると感じた．まとまりのない文章ではあるが今回はCube関数を用いたEUCツールの一例を残した．