Excelで複数テーブルをPandasっぽく集計できるようにする

概要

レポートに測定結果やシミュレーション結果の数値集計をExcelファイルにまとめて提出する人は多いと思います。
Excel2021以降の機能の構造化参照は、データの集計に便利ですが、デフォルトの機能だけだと
数式の記述が冗長になって見通しが悪いし、
使い勝手も悪いのでPythonのPandasっぽく便利に使うためのTips

本記事の想定対象読者

1. 定型フォーマットのCSV, TSVファイルを多数集計してレポート書く必要がある人
2. レポートにJupyterNotebookを添付して提出すると嫌な顔される仕事場の人
3. Excelのマクロが職場セキュリティ的に禁止されている人
4. Python in Excelが使えない職場の人

やりたいこと

こういう定型書式CSVファイルを多数読み込んで

こういう集計を楽に作成したい。できれば集計ファイルが増えても
集計部分は極力「範囲選択ー＞Ctrl+d」でどうにかしたい。

Excel標準機能の問題

例として、Excel標準機能でTEST1という名前のテーブルを読み込んで集計するときテーブル名と列名を指定して集計関数で集計します。

これの問題が何かといえば、
テーブル名と列名を数式ベタ打ちする必要がある　ことです。
見本のダミーデータはタイムスタンプ、温度とセンサ３つのデータを、４温度（４テーブル）分を集計している想定です。

しかし、実務でセンサが10個に増えて、集計対象のテーブル数１０を超えたとき、
(1+10)*10=110回以上数式を入力する必要があります。
「集計数式全部をベタ打ちする気力ありますか？」
PandasでDataFrameの集計を触ったことがある人間は発狂すると思います。ソースは私。

これを名前で登録すれば便利と思うもの

Excel2021以降だと、リボンUIの数式タブのところに「名前の定義」というのがでているので、
名前の定義とLAMBDA式を組み合わせると、実質的にマクロ無しでExcel上で使えるユーザー定義関数を作れます。

下記表の名前とLAMBDA式を登録すると幸せになれると思います。
なお、文字列を含むテーブル全体への演算は式が冗長になるので今回は省略

名前例	Pandasでの操作	式
TAB	df	=LAMBDA(TAB,INDIRECT(TAB))
TCOL	df['列名']	=LAMBDA(TAB,COL,INDIRECT(CONCAT(TAB,"[",COL,"]")))
DIFF	df.diff()	=LAMBDA(TAB,TAB-OFFSET(TAB,-1,0))

使用例

上述の定義した関数でテーブル名と列名指定をセル参照できるようにした例がこちら

使用例1：セル指定でテーブル列のMIN()関数適用

例示したC4セルで意図した操作はpandasで書くと次のイメージです。

example_df_min.py

import pandas as pd

# この行は事前のテーブルの読み込み
df_TEST1=pd.read_csv('TEST1.csv")

# C4セルで行っている操作
B3=df_TEST1
df=B3
C3='TEMP'
df[C3].min()

使用例2：セル指定でテーブル列のdiff()関数適用

例示したD3セルで意図した操作はpandasで書くと次のイメージです。

example_df_diff.py

import pandas as pd

# この行は事前のテーブルの読み込み
df_TEST1=pd.read_csv('TEST1.csv")

# D3セルで行っている操作
B3=df_TEST1
df=B3
D2='TEMP'
df[D2].diff()

まとめ

Excelで手軽にpandasっぽい操作をしたくて３種類ほど、マクロ無で使えるユーザー関数を定義してみました。
DIFFを定義したのはスピルで使える数値列の微分向け関数がなかったからです。