はじめに
株式会社アイスリーデザインでPM（プロジェクトマネージャー）を務めているSoLです。アイスリーデザインではオフショア拠点との窓口対応、開発プロジェクトのPM（プロジェクトマネージャー）、開発部門のPMOなど長年対応しています。特に開発部門の管理業務ではGoogleスプレッドシートを活用する機会が多く、様々な機能を活用しています。今回は、AIに知見を借りつつ私が最近実践したGoogleスプレッドシートの活用方法をいくつかご紹介したいと思います。
日々の業務でGoogleスプレッドシートを使っていると、「特定の条件に合致する複数の値を抜き出したい」「別々の表を一つにまとめたい」といった場面に直面することがあります。従来のVLOOKUPでは対応しきれないケースも多く、複雑な処理には手間がかかってしまいがちです。
この記事では、FILTER関数や名前付き関数といった比較的新しいスプレッドシートの関数や機能を駆使して、プロジェクトごとの参加メンバー一覧の抽出や、複数のテーブルの統合・自動化を実現する方法をご紹介します。
「毎回似たような処理を手作業でやっている…」そんな方にこそ、ぜひ読んでいただきたい内容です。
特定の範囲から特定条件に合致する複数の値を抽出したい
例えば、各プロジェクトに誰が、いつ、どれだけ稼働したかを示す、以下のようなデータが存在するとします。
先のテーブルを見ると、プロジェクトAに携わっているメンバーは我孫子さん、仙石さん、南雲さんの3名。そしてプロジェクトBに携わっているメンバーは楫野さん、大河内さんの2名ですが、関数を使ってプロジェクトごとの参加メンバーを抽出してみたいと思います。
皆さんは特定条件に該当する値を抽出する場合、どのような手段を使っているでしょうか？恐らく多くの方がすぐ思い浮かべるのはVLOOKUP関数ではないでしょうか。
=VLOOKUP(検索値, 検索範囲, 表示する値の列番号, TRUE or FALSE)
しかしながら、VLOOKUP関数は、特定条件に合致する一つの値しか表示してくれません。この関数は指定された検索範囲の特定列を縦方向に検索し、最初にヒットした値のみ結果として返す特性を持ちます。
このようなケースに適した関数がないか、Geminiに以下の通り質問してみます。
―特定の範囲から特定条件に合致するすべての値を抽出する場合に有効な関数はなんですか？
すると、以下の回答が返ってきました。
FILTER関数の利用を提案されました。「FILTER関数」は、2021年以降にExcelやスプレッドシートで追加された比較的新しい関数となります。これまでは各列にフィルター機能を適用し、該当の値が存在する列内でデータを絞り込み表示することは可能でしたが、FILTER関数を使えば該当の値が存在する列とは全く別の場所にデータを抽出してくれます。
=FILTER(配列,含む,[空の場合])
- 配列：フィルター処理するセル範囲または配列を指定（必須）
- 含む：抽出する条件を指定（必須）
- 空の場合：指定した条件にあうデータが空のときに返す値を指定（省略可能）
- ※省略すると「#CALC!」が指定される。
例えば、もしプロジェクトAに携わっているメンバー全員の値を抽出したい場合、以下のFILTER関数で抽出することができます。
=FILTER($C$2:$C$16, $B$2:$B$16=B2)
そうすると、以下のように結果が表示されます。
該当するすべての結果が抽出されました。
しかしながら、同じ値は必要ありません。重複を削除するため、次にUNIQUE関数を使います。
=UNIQUE(範囲)
今回のケースでは、以下のように利用します。
=UNIQUE(FILTER($C$2:$C$16, $B$2:$B$16=B12))
重複が削除されました。
これをプロジェクトBにも適用してテーブル化すると、以下のように整理できます。
ちなみに、仮に横向きに値を表示したい場合はTRANSPOSE関数を利用します。
=TRANSPOSE(配列)
以下のように挿入します。
=TRANSPOSE(UNIQUE(FILTER($C$2:$C$16, $B$2:$B$16=B2)))
参加メンバーが重複のない状態で、横並びで表示されました。
共通の列の値を持つ二つのテーブルを統合したい
次に、先のテーブルと同じプロジェクト情報を持つ次のような別データが存在するとします。
先ほどのテーブルがメンバーごとの稼働日時ベースで一行一行表示されていたのに対し、こちらはプロジェクトごとに予算と消化コストが表示されています。これらの表を一つの表に統合してみたいと思います。
一度、Geminiに統合する方法について質問してみます。
―プロジェクト名をキーにして、シート1のテーブルとシート2のテーブルを一つのテーブルに統合する数式を作成してみてください。
質問の仕方にもよるかもしれませんが、期待した結果は得られませんでした。
今度はChatGPTに実際の表の画像付きで質問してみます。すると、以下のような数式を返してくれました。
実際に利用すると、以下の結果が得られました。
プロジェクト名をキーにして、2つのテーブルが統合されています。
A～D列がメンバーの稼働状況を示すテーブルで、E～F列がプロジェクトごとの予算と消化状況を示すテーブルとなります。
ちなみに今回やや複雑で長い関数を作成しましたが、今後同様の処理を行いたいときに、毎回この関数を0から書くのは面倒だと思います。ここで、2022年に追加された「名前付き関数」という新機能を利用します。この機能を利用すると、既存の関数以外の独自のカスタム関数を登録することが可能です。
「新しい関数を追加」を押下すると、以下のように関数追加画面が表示されます。
「数式の定義」の欄にChatGPTから提案された数式を入力し、今後利用しやすいようにするために「引数のプレースホルダ」を設定しておきます。
この内容で、「MY_FUNCTION1」という名前の関数を新規登録します。すると、以下の通りMY_FUNCTION1が関数として利用できるようになっているのを確認できます。
これで、今後はデータ元となる2つのテーブルが同じフォーマットで用意される場合は、MY_FUNCTION1関数によって両者を統合できます。
データ元のフォーマットが変わる場合には数式の修正が必要になることが予想されますのでご注意ください。
また、名前付き関数機能にはカスタム関数を他のスプレッドシートからインポートする機能も搭載されています。今回作成した関数を他のスプレッドシートでも活用したい場合にはインポート機能が便利かと思います。
最後にプロジェクト情報を整理するため、もう一作業を加えます。統合されたテーブルの各列先頭行に名前をつけて、先にご紹介したFILTER関数、UNIQUE関数、TRANSPOSE関数を利用して「消化コスト」列の横に参加メンバーを横並びで一覧表示します。
上記の統合されたテーブルでピボットテーブルを作成し、計算式を組み込んでプロジェクト単位の利益と利益率を表示させます。
仕上げに先ほど表示させた横並びの参加メンバーを、FILTER関数でピボットの右側に抽出すると、以下のようなプロジェクトごとの利益率と参加メンバーが一覧化された表を作成できます。
もっとも、今回の例だとプロジェクト数も参加メンバーも少ないため、わざわざこんなに数式を使って一覧化する必要はありませんが、膨大なプロジェクト数や人数を管理する場合には、上記のような運用が有効かと思います。
最後に
Googleスプレッドシートは非常に高機能なツールです。今後も機能がアップデートされて、より自由度の高いカスタマイズができるように改良されていくでしょう。ですが問題はそれらをいかに上手く使いこなすかです。そんな時に、AIはどんな場面でどんな機能を活用すべきか、的確な助言を行ってくれるでしょう。
ぜひ参考になさってください。
