見出し画像
Photo by wsd983320987

【Excel】スピルで遊んでみた

すばひ

2023年になって急にワークシート関数が楽しくて仕方がありません。
丹精込めたワークシート関数をSEの知り合いに見せて「これ誰が保守できるん？」と言われたのは至上の喜びでした。

そんな成果物は最悪の成果物なのですが、ワークシート関数ってネストすればするほど愛着が湧いてしまうんですよね。。。困ります。
ただ、今回は複雑なワークシート関数ではなくスピルによる動的な集計を試してみたのでそのまとめになります。

※関数の紹介が目的ではなくて家計簿データの集計で遊んだ結果になるので、ちょっと元データが分かりづらいかもしれません。

スピルで遊んでいきます

そもそも「スピル」ってなに？

スピル とは　[検索]

書いてる通りなのですが、私の解釈としては「1つのセルに入力した関数で範囲結果を返すもの」という認識でいます。
使い方としては概ね、引数に範囲をわたすことでその範囲に応じたセルに自動で同様の関数が設定されていきます。

例として、次のような状態を用意します。

この時B2セルに「=A2:A6+1」と入力してみます。

B3~6にも自動でセットされました。
極端な例ですが、これがスピルです。

そして、スピルを入力したセルはスピル範囲演算子（#）を付与して参照することでスピルしてる範囲が範囲参照の対象範囲として認識されます。
例として、C2に「=SUM(B2#)」と入力してみます。

このように、スピルしてる範囲（B2～6）の合計結果が返ってきます。
これだけだと普段書いてるワークシート関数をコピペしたりオートフィルしてる手間が省けるだけなのでどう役立つかが全く想像できません。

ただ、ここ最近（結構前から）は範囲を返すワークシート関数が増えているようでそういったワークシート関数と組み合わせることでVBAを使用しなくても動的な集計をすることができます。

スピルで集計項目の自動作成

作業元は私の家計簿の明細データを元にします。
次のデータ例には収入・支出でそれぞれ大カテゴリ(Major_item)・中カテゴリ(Medium_item)が含まれています。
さらに、計算用のフラグ(Calculation_target 1:計算対象 0:計算外)があります。

この中から、
①収支フラグ=支出
②計算対象であること
③大カテゴリ・中カテゴリで一意の組合せ
の3つの条件を満たす集計元リストをスピル（ワークシート関数）で生成してみます。

使用するワークシート関数は、FILTER・UNIQUE・SORTです。
各ワークシート関数の役割は
　・FILTER：①・②の条件絞込み
　・UNIQUE：③の絞込み
　・SORT：大カテゴリの並替え
を担当しています。

入力するスピルは↓の感じで。

=SORT(
  UNIQUE(
   FILTER(CSV_DATA[[Major_item]:[Medium_item]]
        ,(CSV_DATA[収支フラグ]="支出")*(CSV_DATA[Calculation_target]=1))
    )
  ,,1)

*CSV_DATAはテーブル名です。
各ワークシート関数の引数や使い方はそれぞれでググった方がいいと思いますのでそれぞれのワークシート関数の説明は省きます。

それでは、下記のようにA3に上記の関数を入力します。
この時2列の範囲を指定しているのでA列：B列（大カテゴリ：中カテゴリ）にスピルの結果が返ってきます。

全量の結果です。
A3に関数を入力するだけでこのような結果が返ってきます。
全部をだす必要はないのですが、集計大カテゴリ・中カテゴリ組み合わせの一意要素がしっかり取得できてます。というエビデンスです。

これだけでもとても便利ですが、
これなら「重複の削除でぱぱっとやったほうが楽では？」になります。
変動することのない静的なデータが相手なら正直それをやったほうが楽だし慣れてる方も多いと思うのでそっちのほうがいいと思います。

スピルの魅力としては、動的な変化が発生することだと思っています。
ここで、集計元のテーブルのデータを加工してみます。
G92の中カテゴリを「雑費」から「テスト」に変更してみます。

・変更前

変更前：集計元テーブル
変更前：スピル結果

・変更後

変更後：集計元テーブル
変更後：スピル結果

集計元を変更しただけで、スピル結果の行が増えていることが確認できます。

スピル範囲に応じた集計関数の作成

さて、集計用の項目がデータの増減で変動することが確認できました。
それでは次にこのスピル範囲を参照するSUMIFを組んでいきます。

今回は大カテゴリ・中カテゴリの組み合わせを日別に集計していくことを目的とします。

B1が年のパラメータとして機能していて、
C2から4/1.2.3..と年度の日付を生成しています。
明細データに対し
・$B$1+C2の日付
・A列のカテゴリ名
・B列のカテゴリ名
を条件としてC3～の範囲に集計結果を返す関数を組んでいきます。

用意するSUMIFSは以下の通り、各引数にコメントを入れて紹介します。

=SUMIFS(DataTable!$D:$D -- 集計範囲（集計対象の金額列）
       ,DataTable!$B:$B -- 条件範囲1（集計対象の日付列）
       ,C2:ND2          -- 条件1（集計シートの日付範囲を指定）
       ,DataTable!$F:$F -- 条件範囲2（集計範囲元の大カテゴリ）
       ,TAKE(A3#,,1)    -- 条件2（集計シートのカテゴリスピル範囲から先頭1列目（大カテゴリ）を指定）
       ,DataTable!$G:$G -- 条件範囲3（集計範囲元の中カテゴリ）
       ,TAKE(A3#,,-1)   -- 条件3（集計シートのカテゴリスピル範囲から末尾1列目（中カテゴリ）を指定）
       ,DataTable!$A:$A,1)*-1 -- この条件3と最後の*-1は集計元データの都合で入れている条件なので関係無いです。

集計結果

F57が正常な集計か確認
元データで手動加算して4388なので一致してることを確認

これでスピル範囲で生成したカテゴリ要素に対しての集計関数を作成することができました。
もちろん、仮に上記の中カテゴリを1セル変更してみても以下のように自動で範囲が変動して集計されます。

1310円の行の中カテゴリをテストに変更
56行目に中カテゴリ「テスト」が増えている

これでVBA無しでワークシート関数による動的集計ができました。
素晴らしいですね。

スピル範囲を参照する時に躓いたこと

範囲の指定方法

SUMIFSのカテゴリ条件の指定に苦戦しました。
上記のTAKE関数で指定している箇所です。
最初にUNIQUE関数で集計項目を作成したあと、
SUMIFS関数を用意している際、カテゴリ条件として
「UNIQUE関数の結果を入力した位置～生成された最後の位置」
を範囲として入力しました。
この入力の場合、動的範囲ではなく静的範囲のため集計範囲の自動拡張が行われません。

例えば、現在だとUNIQUE関数をA3に入力してB84まで結果が返ってきています。

列は映ってないですがA,B列です

この時、SUMIFSの大カテゴリ条件絞込み時にA3:A84と入力します。
（中カテゴリはB3:B84）
そうすると日付の横軸に84行目までSUMIFSの集計関数がスピルで生成されます。
しかし、集計元のカテゴリが増えた場合、85行目以降にカテゴリが追加された場合にSUMIFSの範囲は自動拡張されません。

このミスのおかげでスピルが少しわかった気がしました。
動的に生成された範囲を参照するのであれば動的な範囲として指定しないといけないということです。
書いてみると当然なのですが・・・

そして、TAKE関数を見つけるまでが困りました。
カテゴリの条件にスピル範囲（A3#）を指定しても当然うまくいきません。

ならば、と集計元に大カテゴリ+中カテゴリの集計キーを生成しておき、
カテゴリの条件にTEXTJOIN（A3#）でどうだろうかと試してみました。
これで1意の検索キーが生成されないかと思いましたがTEXTJOINは範囲を返す関数ではないので、範囲全てを結合した結果が返ってきてそりゃそうだと諦めました。

範囲の一部を指定するような関数が無いか調べたところTAKE関数が見つかった。という流れになります。

TAKE関数の落とし穴

落とし穴、というよりは勝手に落ちただけなのかもしれません。
TAKE関数は引数に渡した範囲から行・列を何行使用しますか？
という関数であって、何行目？何列目？を指定する関数ではなかったということです。
今回の集計ではたまたま、集計カテゴリが2つ＝範囲にして2列だったので先頭から1列（大カテゴリ）、末尾から1列（中カテゴリ）という指定で集計できています。
仮に集計元に小カテゴリもある場合は、TAKE関数をTAKE関数でネストしていかないといけないと思われます。
こうなると美しさが損なわれるのでいまいちですね。
※範囲から指定行列を除外するDROP関数もあるらしい・・・ここの問題の根本解決にはなりませんが

そもそもワークシート関数でやる必要性について

言ってしまうとVBAでええやん。で終わってしまう話になりがちなのですがワークシート関数で組んだ場合、ブラウザでの閲覧でも動的な範囲が利用・生成できる。というメリットがあります。

これはweb版Excelの画面です。

先程と同じように4/4の外食のレコードを1つテストに変更してみます。

先程と同じように自動で集計項目・集計関数が生成されています。
これはわりと便利というか今後役立つシーンがでてくるのではないかと思います。


以上がスピルで遊んでみたまとめになります。

この記事が気に入ったらサポートをしてみませんか？