こんにちは、高崎＠アノテーション です。
はじめに
スプレッドシートで業務関連の集計をしておりまして、効率的な集計はないものか、と弊社 Slack に導入されている生成 AI bot にたずねてみたらすんなり解決した話を記事にします。
Slack に導入されている生成 AI bot については下記のブログをご参照ください。
前提
こんな感じの集計表があったとして（詳細を記載すると NDA 抵触する可能性がありますので非常にざっくりした表ですが）、集計方法の遷移を順次記載していきます。
集計方法
1. 最初の集計方法
このシートとは別に建てたシート（例：「集計」）に
品目の合計を計算する。
というのが最初の要件でした。
なお、「集計」シートは下記のような感じです。
ここは Excel でも経験があるので、そんなに難しくはなさそうで、
=SUMIF('記録'!B:B, B3, '記録'!C:C)として最初の行はりんごを集計、その後下2つコピペして、
こんな感じで集計できますね。
2. 追加の集計要件
「集計」シートに
品目の合計を計算するが、対象担当が記載されていないものは計算しない。
「集計」シートはこんなレイアウトです。
E 列に対象メンバーを記入し、記入されたメンバー限定で集計したい、というものです。
当初考えていたのが、SUMIFS を使って
=SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$3)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$4)という感じなのですが、対象メンバーが増えるごとに書き足しが面倒ですし、いっそ、
=SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$3)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$4)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$5)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$6)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$7)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$8)+…と、増加されるメンバーを多めに設定するという考えもそれ以上の対象メンバーが増えたときは一緒だし、良い感じのエレガントな集約方法は無いものか、と考えておりました。
いつもなら google センセイにお伺いするのですが、今回は冒頭に書いた 生成 AI 機能を持つ Slack ボットに聞いてみようと思い、色々と会話してみました。
ほぉ。
=SUM(FILTER(C2:C9, (A2:A9="りんご") * (REGEXMATCH(B2:B9, TEXTJOIN("|", true, F2:F)))))という提案を頂きましたので、これをちょっと改良し、
=SUM(FILTER('記録'!C:C, ('記録'!B:B=B3) * (REGEXMATCH('記録'!D:D, TEXTJOIN("|", true, E:E)))))で実行。
うまくいきました。
これに E 列にメンバーを追加すると、
こんな感じで値が変わりますし、一方でメンバーを減らすとこんな感じで減る…
(´・ω・｀)
あり。
FILTERでエラーになってそのまま表示出来なかったので、ISERRORを使って少し長くなりますが、
=IF(ISERROR(FILTER('記録'!C:C, ('記録'!B:B=B3) * (REGEXMATCH('記録'!D:D, TEXTJOIN("|", true, E:E))))), 0, SUM(FILTER('記録'!C:C, ('記録'!B:B=B3) * (REGEXMATCH('記録'!D:D, TEXTJOIN("|", true, E:E))))))とFILTERのエラーを検知してやれば、
うまく行きましたね。
おまけの要件
「記録」シートを月ごとに整理したい
「記録」シートを例えば2024-05記録みたいなシート名にして月ごとに集計したい場合、シート名を単純に変えると上述の式のシート名の記載が変わってくれるのですが、月ごとに2024-05記録 2024-06記録…としたい時に、
この対象月のセル（赤枠）を変更しただけで集計したい、というものです。
毎回月が変わるごとにそれぞれのセルを対象月のシート名に変えるのは面倒ですよね？
ここは AI に尋ねるまでもなくINDIRECTを使えば解決。
=IF(ISERROR(FILTER(INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!C:C"), (INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!B:B")=B5) * (REGEXMATCH(INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!D:D"),TEXTJOIN("|", true, E:E))))), 0, SUM(FILTER(INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!C:C"), (INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!B:B")=B5) * (REGEXMATCH(INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!D:D"), TEXTJOIN("|", true, E:E))))))…かなーり長い式になりましたが、同じような集計ができますし、シートを月ごとに変割った時に C2 セルの箇所を変更すれば、その月の集計ができる、という形で対応出来ました。
まとめ
- 生成 AI を使うと google で調べる手間が省けました。
- 改めて時間があった時に上記の内容を google で調べたのですが4時間ほどかかってもあまり成果が得られず。
- （おまけで使った）
INDIRECTは Excel やスプレッドシートを使う上で非常に便利です。
アノテーション株式会社について
アノテーション株式会社はクラスメソッドグループのオペレーション専門特化企業です。
サポート・運用・開発保守・情シス・バックオフィスの専門チームが、最新 IT テクノロジー、高い技術力、蓄積されたノウハウをフル活用し、お客様の課題解決を行っています。
当社は様々な職種でメンバーを募集しています。
「オペレーション・エクセレンス」と「らしく働く、らしく生きる」を共に実現するカルチャー・しくみ・働き方にご興味がある方は、アノテーション株式会社 採用サイトをぜひご覧ください。
3
