Community で人気のコメント(3) 最初の出現箇所を見つけて日付を返す数式（COLLECT関数）

jmyzk_cloudsmart_jp

Smartsheet Community （英語版）で人気のコメントのうち、「いいね！」と思ったものをご紹介します。

しばらくサボっていましたが、第3回目は、コミュニティーアンバサダー（大使）のニック・コルナ (@Nick Korna)さんがコニー・コクラン（@Connie Cochran）の質問に答えて回答した「最初の出現箇所を見つけて日付を返す数式」についての記事です。

はじめに

同一の質問に同一人物から複数の回答がある場合、最初の回答や最新の回答を参照したい場合があります。

コクランさんの場合は、同一クライアントに複数のイベントがあり、最初の出現のイベント日を参照したい、という内容の質問です。

質問と回答の要約

質問と回答の要約は以下の通りです：

質問

Connie Cochranさん (2024年2月3日) クライアント名を見てイベント日を返す数式を作成しようとしていますが、クライアント名が重複している場合は、最初の出現のイベント日だけを返すようにしたいです。しかし、解析エラーが出ます。助けてください。

回答

Nick Kornaさん (2024年2月3日) 

最初の出現の日付を取得したい場合は、以下のように INDEX COLLECT を使用できます：

=INDEX(COLLECT([Event Date]:[Event Date], [Client Name]:[Client Name], [Client Name]@row), 1)

クライアントの最も早い日付を取得したい場合は、以下のように MIN COLLECT を使用できます：

=MIN(COLLECT([Event Date]:[Event Date], [Client Name]:[Client Name], [Client Name]@row))

追加の質問

Connie Cochranさん これをアーカイブシートの同じ列を見て、両方から最も早い日付を取得するようにする方法はありますか？

追加の回答

Nick Kornaさん アーカイブシートから最も早い日付を取得するためにもう一つ MIN COLLECT を使用し、両方の MIN COLLECT を MIN 内に入れることができます。これにより、絶対に最も早い日付を取得できます。最初に個別の列でテストし、正しい結果を確認してからすべてを1つにまとめることをお勧めします。

解説

INDEX関数は範囲（ 評価対象となるセルのグループ）に対して行のインデクスまたは列のインデックスを指定して範囲の中から特定のアイテムを取得できます。

https://help.smartsheet.com/ja/function/index

通常はMATCH関数を組み合わせて、INDEX(MATCH())の形式で使うことが一般です。

https://help.smartsheet.com/ja/function/match

すなわち、MATCH関数で取得した範囲内の値の相対位置をINDEX関数の行のインデックスとするわけです。

したがって、質問の最初の出現のイベント日知りたい場合は、以下の式でも取得可能です。

=INDEX([Event Date]:[Event Date], MATCH(Client@row, [Client Name]:[Client Name]))

(なお、私のデモシートはSheet Summary のClientで選んだクライアントについて、評価する方式なので、Client@row はClient#となります。）

ただ、行や列のインデックスを指定する方法は、MATCHでなくてもいいので、コルナさんが示しているようにCOLLECT関数で予め自分の欲しい範囲を作っておいて、行のインデックスを１で指定する、という方法も可能となります。

=INDEX(COLLECT([Event Date]:[Event Date], [Client Name]:[Client Name], Client@row), 1)

このCOLLECTを利用すれば、範囲に対して評価する関数、例えば、MIN、MAX、COUNTなどを利用でき便利です。質問にある最も早い日付を取得する場合、日付は数値として扱えるので、MINで評価してやれば取得可能です。MATCHだと一つだけの相対位置が返されるのに対して、COLLECTは欲しい範囲の評価対象セルグループを取得できるので、そのセルグールに対する評価が可能となります。

https://help.smartsheet.com/ja/function/collect

コルナさんの式

=MIN(COLLECT([Event Date]:[Event Date], [Client Name]:[Client Name], [Client Name]@row))

再質問にあるアーカイブシートも含めて最も早い日付を取得の質問の回答を下のデモシートに作っておきました。（なお、別シートでなく、同じシートのアーカイブクライアント、アーカイブイベント日付を含めての最も早い日付を取得しています。）

=MIN(

COLLECT([Event Date]:[Event Date], [Client Name]:[Client Name], Client#), 

COLLECT([Archive Event Date]:[Archive Event Date], [Archive Client Name]:[Archive Client Name], Client#)

)

なお、「両方の MIN COLLECT を MIN 内に入れる」ことも可能ですが、上の式のようにまとめてMINの評価をして、個々のCOLLECTのMINは省略しても、結果は同じです。
上の式は見やすくするために改行しています。MINは数値又は範囲を「,」で区切って評価対象とすることができます。

（下のSmartsheetのリンクをクリックして公開デモシートを開き、Sheet SummaryのClientを変更して、各式の動作を確認できます。）

https://app.smartsheet.com/b/publish?EQBCT=c6293596bc1e48b6ad551be0d945675d

まとめ

COLLECT関数は別の関数の中で使用し、ある範囲内にて、提供された条件に合致する特定の値を収集します。今回の例のようにMIN関数と組み合わせると、条件に合致するものの中から最小のものを取得できます。
よく使うのはJOIN関数、COUNT関数、INDEX関数などで、JOINの場合はCOLLECTで収集した文字を繋げる、COUNTは数を数える、INDEXは特定の位置のものを取得する、ことができます。