Excelのスピル範囲演算子を使うと"#VALUE!"エラーとなる数式の直し方(TRANSPOSE/FILTER/INDIRECT/TEXTJOIN)

Question

前提

環境

Excel Online

参加表

以下のようなテーブルを作っています。
※名前・メールアドレスはダミーデータです。

図1:参加表テーブル

以下のようにデータを入力しています。

• 参加者列に名前
• メールアドレス列にメールアドレスを入力する
• それ以降の列を日付で追加する
• 各参加者の行の参加できる列に○を記載する

メールアドレスのとりまとめ

日別で○のついた参加者のメールアドレスを取りまとめ、コピペでメール宛先を設定するために以下の流れを考えています。

1. 日付を[参加表]テーブルの[#見出し]から取得
   TRANSPOSE(FILTER(参加表[#見出し],ISNUMBER(TEXT(参加表[#見出し],"yyyymmdd")+0)))
2. 1.で取得した日付から、[参加表]テーブルの日付列を参照
   INDIRECT("参加表["&{1.で取得した日付}&"]"
3. 2.で取得した[参加表]の日付列に○がついている行の[メールアドレス]列を取得
   FILTER(参加表[メールアドレス], {2.で取得した日付列}="○" )
4. 3.で取得した[メールアドレス]列の配列を連結（これをコピペしてメール宛先を設定）
   TEXTJOIN(";",TRUE, {2.で取得した[メールアドレス]列の配列})

これにより、以下の画像のような出力を想定しています。

図2:想定の出力

これを実現するために、以下の数式を利用しています。

列名	数式
日付	TRANSPOSE(FILTER(参加表[#見出し],ISNUMBER(TEXT(参加表[#見出し],"yyyymmdd")+0)))
メールアドレスまとめ	TEXTJOIN(";",TRUE,FILTER(参加表[メールアドレス],INDIRECT("参加表["&{日付列のセル}#&"]")="○"))

発生している問題

メールアドレスのとりまとめにといて、2.の部分の数式に対して以下の現象が出ています。

1. スピル範囲演算子を利用した場合に#VALUE!エラーになる。
2. スピル範囲演算子を利用しない場合は想定の出力(図2)が表示される。

図3:スピル範囲演算子の有無による動作の違い

実現したいこと

問題点にある 1. の問題点を解決し、スピル範囲演算子を利用した数式を実現したいです。

2.の場合はスピル範囲演算子を利用しないと日付列の行数分この数式をコピーをする必要があったり、事前に多くの行数に数式を設定しておくなどが必要であり、それらを回避したいと考えています。

試したこと

• #VALUE!エラーについての調査 エラー値 #VALUE! を修正する
  • 本件の対処法が見つからず断念

補足情報

スピルを使いだしたのがつい最近なので、認識間違っているかもしれません。
基本的な見落としでしたら申し訳ありませんが、ご回答をお願い致します。

Answer 1

INDIRECTの引数に配列は渡せないので、BYROW関数で行毎に処理するようにすればどうでしょう。

=BYROW(G3#,LAMBDA(d,TEXTJOIN(";",TRUE,FILTER(参加表[メールアドレス],INDIRECT("参加表["&d&"]")="○",""))))

=BYROW(G3#,LAMBDA(d,TEXTJOIN(";",TRUE,FILTER(参加表[メールアドレス],INDIRECT("参加表["&d&"]")="○",""))))

---

ちなみに、日付列は、下記の式でもいいですね。

=TRANSPOSE(DROP(参加表[#見出し],,2))

DROPは2022年8月アップデートで追加された関数です。

Microsoft、「Excel」に14の新関数を追加 ～文字列・配列操作が簡単に - 窓の杜

Comments

[Shau]

ご回答ありがとうございます。

> INDIRECTの引数に配列は渡せない
この知識が足りていませんでした。ありがとうございます。
提案いただいたBYROWを利用した数式で想定の挙動を実現できました。
また、日付部分のDROPを利用した回答方法もシンプルで分かりやすい対処法で助かりました。

解決しましたので、ベストアンサーとさせていただきます。
ありがとうございます。