エクセル新関数を利用したクロス集計を改善した例【Twitterで発信したTIPS】
administrator2023/5/5のツイートの解説記事です。
Microsoft365の新関数の仕様を調査していて分析に使えそうだなと集計した例がこちら。
Lens.orgのデータを使った分析なのですが、出願日や出願国などのような１セル１データであれば２つの関数(SORT/UNIQUE)を組み合わせるだけでユニークな値を抽出できます。さらに、縦横の入れ替えも１つの関数(TRANSPOSE)でできます。この関数を使わずに同じ処理をしようとするとINDEXとMATCHの２つの関数を使って数式を作る必要があるのですが式を楽に作れるようになりました。
これらのセルの値を使いCOUNTIFS関数で集計することでクロス集計ができます。この例では出願国と年の集計をしています。このシートでの分析により出願の中心時期は2007年頃であり、欧米に出願が多くドイツ(DE)やオーストリア(AT)の出願も多いことが可視化できます。
個人的にはこれで満足していたのですが、他の記事でもアドバイスをもらった「はけた＠できるExcel2021（@excelspeedup）」さんが改善方法をアドバイスしてくれました。
スピルというexcel2019に追加された機能を使うことで、数式の指定をかなり楽にできるようになりました。
また、このような数式にすることでデータがある行だけを対象に集計してスピルで必要な行分だけ表示できるようにしています。
エクセル関数の道は深いですね。
