[ChatGPT事例]Excelの複雑な配列関数の作成

概要・感想

余りにも要件にドンピシャでビビりました。関数そのまま使えました。

プロンプト

部内での備品管理のため、Microsoft Formsで、「備品名(選択)」「貸出日(日付)」「返却日(日付)」の3設問があるグループフォームを作成します。 その結果が収集されるクラウド上のExcelに、縦見出し:日付、横見出し:備品名となっているような別シートを作成し、各セル内に現在借りている人の名前が表示されるようにするための関数を考えてください。

GPT-4の回答

Microsoft Formsで集めたデータを、縦見出しに日付、横見出しに備品名を持つ別シートで表示するには、ExcelのIF関数とCOUNTIFS関数を組み合わせて使用します。

まず、Microsoft Formsの結果が収集されるシート名が "FormResults" であると仮定し、そのシートに以下の列があるとします。

A: 備品名 B: 貸出日 C: 返却日 D: 借りている人の名前

次に、別シートで縦見出し（日付）と横見出し（備品名）を設定します。このシート名を "Summary" とします。A1セルから右に横見出しとなる備品名をリストします。例えば、A1に「プロジェクター」、B1に「ノートパソコン」など。
A2セルから下に縦見出しとなる日付をリストします。例えば、A2に「2023-04-01」、A3に「2023-04-02」など。

これで "Summary" シートの基本レイアウトができました。次に、各セルに現在借りている人の名前が表示されるように関数を設定します。

以下の関数をB2セルに入力し、そのまま右下の角をドラッグして表全体にコピーします。

ChatGPT GPT-4

=IF(COUNTIFS(FormResults!$A:$A, Summary!B$1, FormResults!$B:$B, "<="&Summary!$A2, FormResults!$C:$C, ">"&Summary!$A2)>0, INDEX(FormResults!$D:$D, SMALL(IF((FormResults!$A:$A=Summary!B$1)*(FormResults!$B:$B<=Summary!$A2)*(FormResults!$C:$C>Summary!$A2),ROW(FormResults!$A:$A)-MIN(ROW(FormResults!$A:$A))+1), 1)), "")

この関数は、条件を満たす借りている人の名前を表示するか、条件を満たさない場合は空白を表示します。借りている人が複数いる場合、先に入力された人の名前が表示されます。

以上で、各セルに現在借りている人の名前が表示されるようになります。ただし、この関数は配列関数なので、関数を入力した後に、Ctrl + Shift + Enter キーを押して確定させる必要があります（Excelのバージョンによっては不要な場合もあります）。

ChatGPT GPT-4