Excelの動的配列数式(2)

【動的配列数式】
動的配列数式を簡単にいうと、1つの数式で隣接するセルに複数の値が出力される仕組みのこと。
従来のExcelは、関数を入力したセルに1つの値を出力していたが、2020年くらいに動的配列数式が導入されたらしい。
この時に、範囲を出力する関数、FILTER, UNIQUE, SORT, SORTBY, SEQUENCE, RANDARRAY が加わったようだ。

動的配列数式では、入力に範囲を指定できるようになった。(配列計算)
例えば、下のシートで、
A列(A3:A12)に入力された数値の10倍の数値をB列(B3:B12)に出力する処理を考える。
昔は、B3セルに =A3*10を入力して、B3セルをB4からB12にコピーしていた。
今は、B3セルに =A3:A12*10を入力すると、B3:B12に値が出力される。

条件式でも使えるので、C3セルに =IF(A3:A12<30, "LO", "HI") と入力すると、A3:A12の値が30より小さい場合は"LO"を出力し、30以上の場合は”HI"を出力する。

Excelの配列式 (2022/01/06) でExcelの動的配列について書いた。

その中で、生徒を科目ごとに5段階評価した一覧表から、最も良い評価の生徒を抜き出すという例を取り上げた。
この処理は、単に評価5を抽出すればよいわけではなく、5がなければ4が最も良い評価になるというところだ。
この処理を、配列数式を使わないで書くと、補助列が必要になって、後から処理が分からなくなる。
これを、動的配列数式を使用すると補助列が不要になるし、数式が減るので後から処理を追いやすい。

=INDEX(FILTER($A3:$E22,RANK(B3:B22,B$3:B$22)<=$H$1),,1)
という配列数式を示したのだが、わかりにくい。

【テーブルの構造化参照】　※2
処理対象の一覧表(A1:E21)をテーブル(成績一覧)にすると、
・A1:E21 → 成績一覧 
・A1:E1  →  成績一覧[#見出し] 
・A2:E21 → 成績一覧[#データ] 
・A2:A21 → 成績一覧[算数] 
で参照できる。(構造化参照)


=INDEX(FILTER($A2:$E21,RANK(B2:B21,B$2:B$21)<=$H$1),,1) ^※1
は
=CHOOSECOLS(FILTER(成績一覧[#データ],RANK.EQ(成績一覧[算数],成績一覧[算数])<=$H$1),1) ^※1
と書ける。(CHOOSECOLSとINDEXは範囲の1列目を抜き出す関数)
↑の配列数式をG3セルに書くと算数の1位がG3セルから下に抽出される。
G3セルをH3からJ3にコピーすると、成績一覧[算数]が自動的に成績一覧[国語]、成績一覧[理科]、成績一覧[社会]に変換される。

関数で範囲を指定する際に、A2:E21のような形式で入力しようとすると、テーブルのシートを行ったり来たりしなければならないし、テーブルが大きい場合は、マウスで選択するのも難しい。
構造化参照で書くと、式が入力しやすいし、分かりやすくなる。

【COPILOT関数】
Copilot有料ライセンスがあると、COPILOT関数が使える。
=COPILOT(
　"対象となるテーブルには,生徒毎に算数,国語,理科,社会,の教科の評価が記録されている。
　このテーブルから,算数,国語,理科,社会の教科毎に評価順位が高い側の
　1位の生徒を抽出してください。
　同じ順位に複数の生徒がいる場合はすべて抽出してください。
　評価は5が最も高く、1が最も低い。
　出力は教科と同じ行に生徒を出力します。
　生徒の評価(数値)は出力しない"
,成績一覧
)

↑をG2セルに入力すると、G2:K5に結果が出力される。


プロンプトは何回か書き直した。
ざっくり分析する場合や、出力と元データを見比べながら検証できるなら良いが、
元データが大きくなって、目視で検証できない場合は、AIの結果に頼るのは危険だ。
ハルシネーションを含んだAIの出力で、成績が決まってしまうと考えると、コワイ。

AIを使うなら、この結果を出力するための式を出力させるのが良いと思う。

---

※1RANK.EQ関数は、動的配列関数ではなく、配列スカラー関数で、第一引数に配列をを与えた場合に出力も配列になる。
※2 テーブルにデータを追加した場合範囲が自動的に拡張されるため、成績一覧[#データ] 範囲を変更しなくてよいという利点がある。

---

最近の投稿
【Yoshiのよしなしごと】【Yoshiのブログ】【よしなしごと】【AI】【Excel】