何故割り算なのでしょうか？

ここの回答によく出てくる対象データをすべてピックアップする
（下記は横に並べていく例）
=IFERROR(INDEX($25:$25,1/LARGE(INDEX(($D26:$F26="")/COLUMN($D:$F),),COLUMN(A1))),"")という関数ですが、
途中の算式でCOLUMNで割った値を判定に使ったのちにCOLUMNに戻す処理があります。
何故COLUMNをそのまま使っていないのでしょうか？

ちなみに割るのを止めて
=IFERROR(INDEX($25:$25,LARGE(INDEX(($D26:$F26="")*COLUMN($D:$F),),COLUMN(A1))),"")にしたところ、必要な答えは出ましたが対象の無い空白欄には
「ワークシートの限界を超えてスピルはできません」エラーとなりました。
わざわざ割り算している理由は、此のエラーの回避なのかなと思いますが、
掛け算では何故限界を超えるエラーが出るのも不明ですので併せて、どなたか教えてください。

質問者からの補足コメント

• 200７で試したところ、本来計算エラーで空欄になるところに値が表示されました。
  INDEXの値（カラム値）が０（対象無し）なので、もちろん誤りですが、
  なぜその値が出てくるのかわからないし、
  試す都度違う値がでたりしてよくわかりません。
  此れがエラーになってくれればいいのですが。
  365のスピルエラーもこれが原因だと思います。
  但し、０データをエラーに出来たとしても降順（LARGE）表示は何か不自然。
  昇順（SMALL)にすると０データが先に来てしまい、ますます困る。
  結論：掛け算方式は使えない。
  というわけで割り算型を考案した人は賢いという結論になりました。
  以上　皆様お騒がせしました。

    補足日時：2022/11/09 19:32

No.5 ベストアンサー 回答者： fujillin 回答日時： 2022/11/09 22:15

No4です。

以下は、2019以前での話です。

＞試す都度違う値がでたりしてよくわかりません。
INDEX関数の挙動に少しおかしなところがあって,
例えば
　=INDEX($25:$25,0)
として適当なセルに入力して、水平方向にフィルコピーしてみるとわかると思います。
（エラーにはなりません）
これを、
　=INDEX($D$25:$F$25,0)
として同様にしてみると、今度はある部分（D:F列）はエラーになりませんが、他の列ではエラーになります。
これらを観察すると、インデックス値「0」の場合の評価がどうなっているかわかると思います。
（インデックスが0なので、本来はエラーを返すのが正しいと思いますが・・・）


SMALL関数を利用して、除算を用いずに抽出しようとするなら、
　SMALL(IF($D$26:$F$26="",COLUMN($D$26:$F$26),1E5),COLUMN(A1))
のような考え方の方がわかりやすいと思います。
IF関数の部分は、
「26行目が空白ならその列番号を、そうでなければ1E5（＝列の最大値以上の数）を返す」
といった意味になりますが、残念ながらIF関数は配列計算に対応していないので、上の式はそのままではエラーになります。
これを計算させるには、配列数式として定義する必要があります。

一気にまとめてしまえば、
=IFERROR(INDEX($25:$25,SMALL(IF($D$26:$F$26="",COLUMN($D$26:$F$26),1E5),COLUMN(A1))),"")
の式を入力して、Ctrl+Shift+Enterを同時押しして確定させることで、配列数式として入力が可能です。
その上で、右方にフィルコピーすれば、最初にご提示の式と同様の結果が得られます。
(列数が1E5以下なので、1E5列を参照しようとするとエラーになることを利用しています）
とは言え、配列数式は必ずしも扱いやすいものでは無いので、これを避けるために考え出されたのが除算を2回行う方法と考えられます。


別法として、AGGREGATE関数を利用して、
=IFERROR(INDEX($25:$25,AGGREGATE(15,6,COLUMN($D$26:$F$26)/($D$26:$F$26=""),COLUMN(A1))),"")
とすることで、配列数式にしなくても同様の結果を得ることが可能です。
（こちらは、0除算がエラーになることを利用しています）
（AGGREGATE関数は、多分、2010以降だと思いますが・・）

この回答へのお礼

解説ありがとうございます。

1）2007で、フィルコピー試すと、指定範囲に関わらず関数入力したセルの
カラムに連動した値が返ってくるという不思議な結果（これが試す都度
違った理由）でした。０を参照エラーにしてくれれば曲がりなりにも使えて、疑問も湧かなかったのに、です。
又365で試すと、指定行全部の値が返ってきたのでシートから溢れエラーになったのだと思いますので、２つの疑問は解決？しました。

２）SMALL関数も昨日試しました。条件が１かブランクだったので、「IF(($D26:$F26)=1,"",COLUMN($D:$F)」として365で試して上手くいったのですが、2007では一つ目の値しか持ってこられず、バージョンの限度だろうと終わってました。が、Ctrl+Shift+Enterを失念していました。

>配列数式は必ずしも扱いやすいものでは無いので、これを避けるために考え出されたのが除算を2回行う方法と考えられます。
そうですね。修正すると元に戻るし面倒です。
３）乗りかかった船で、無理やり配列を充てる方法も試しました。
=IFERROR(INDEX($1:$1,AGGREGATE(15,6,IF(($D$26:$F$26)=1,"",SEQUENCE(,3,4)),COLUMN(A:A))),"")
が、やっぱり除算型のほうがシンプルです。

お礼日時：2022/11/10 10:30

No.6 回答者： goomania 回答日時： 2022/11/10 18:17

既に問題は解決しているので、全くの余談ですが、INDEX関数のインデックスが0のとき、一次元配列であれば「INDEX関数は列全体または行全体の値の配列を返す」という仕様だっと記憶しています。

https://support.microsoft.com/ja-jp/office/index …

従って、シートの25行目A25～J25に「=column()」を記述してから、 fujillinさんの回答にある
=INDEX($25:$25,0)
をA1に記述して、水平方向にフィルコピーしてみると
1,2,3,4,5,6,7,8,9,10,0,0,0･･･
と表示されることになりますし、
=INDEX($D$25:$F$25,0)
をA2に記述して、水平方向にフィルコピーしてみると
D:F列は4,5,6となり、エラーになりませんが、
他の列ではエラーになります。

ここで、A1を
=AGGREGATE(15,6,INDEX($25:$25,0),COLUMN())
に書き換えて水平方向にフィルコピーしてみると
1,2,3,4,5,6,7,8,9,10,#NUM!,#NUM!,#NUM!･･･
となり、A2を
=AGGREGATE(15,6,INDEX($D$25:$F$25,0),COLUMN())
に書き換えて水平方向にフィルコピーしてみると
4,5,6,#NUM!,#NUM!,#NUM!,#NUM!･･･
となります。

つまり、
=AGGREGATE(15,6,$25:$25,COLUMN())
=AGGREGATE(15,6,$D$25:$F$25,COLUMN())
と同じ結果を返していることがわかります。

このように、AGGREGATE関数のような配列を扱える関数では、セル範囲を配列と解釈してくれますので、わざわざINDEX関数のインデックス0を使って配列を記述しても、ただ数式が長くなるたげであまり意味がないことになります。

=AGGREGATE(14,6,($25:$25<5)*COLUMN(A1:J1),2)･･････①
=AGGREGATE(14,6,INDEX(($25:$25<5)*COLUMN(A1:J1),0),2)･････②

①の数式は、EXCELで上記のシートに記述すると、3が返る数式ですが、互換ソフトといわれるKingSoftのWPSスプレッドシートでは、敢えて②のように記述しないと正しい結果が得られません。

これは、INDEX関数の仕様としては正しい使い方なのかも知れません。

EXCELでも②のように記述してもよいとは言え、前述の理由もあって、やる人は極めて少ないと思います。

この回答へのお礼

補足ありがとうございます。

「0にすると列全体または行全体の値の配列を返す」仕様の意味が
ピンとこなかったのですが、下記のABを並べた結果
なんとなくわかりました。
A:INDEX($25:$25,0)　今回発生
B:INDEX(($25:$25<5)*COLUMN(A1:J1),0)　上記②

本来はBのように条件配列を造るための仕様であり、
最初からAの使い方をすることは無い。
というのも,最初からなら=($25:$25)で出来る。
今回掛け算式を試したら、計算値の値が０となり、
Aパターンになった結果、旧バージョンでは訳の分からない答えに
なったり時にはエラーになったりという現象があちこちで出てきた。
この解釈で合ってますか？

此れは経験がないと理解できないですね。
しかも３６５になるとA列を除きシートあふれでスピルエラーになる。
旧バージョンの式が使えなくなるので、止めるわけにもいかないですが、
せめて配列が使える新バージョンでは、「代入で０だったら＃REFにする」などにして欲しいですね。

お礼日時：2022/11/10 22:18

No.4 回答者： fujillin 回答日時： 2022/11/09 14:54

こんにちは

＞「ワークシートの限界を超えてスピルはできません」エラーとなりました。
ご提示の関数は、もともとスピルに対応していないバージョンを想定した式と推測されます。

2019以前のバージョンで試してみればわかりますが、ご提示の後者の式でもエラーは発生しません。（エラー処理を行っているので）
ただし、抽出の順序が異なってしまいますし、正しい結果も得られません。
割り戻しているのは、この両者をまとめて回避するためと考えられます。
（詳細については、No2様の回答をご覧ください）

一方で、スピル機能を利用できる環境であるなら、式をフィルコピーする必要はありませんし、それに対応した便利な関数も用意されていますので、ご提示のような面倒な式を使う必要もありません。
ご提示の例で言えば、
　=FILTER(D25:F25,D26:F26="","")
をひとつのセルに入力すれば済むはずです。

この回答へのお礼

はい、FILTERは了解していますが、これは素朴な疑問という類です。

式を見ただけで、いきなり掛け算方式を実行した訳では無く
両方の中間結果も確認していますし、
予想通り正解はでました。
但しスピルエラーのおまけが出ました。
掛け算で出来るのなら、とっくにやってるよという声は、
予想してましたが、わざわざ割り戻してるのには
何か理由があるはずなので、もし理由がわかる方がいたら
という趣旨です。スピルエラーについても、同様です。

新旧で配列の動きの違いがあるのでしょうね。
2007があるので、そちらで掛け算方式の動作を確認すれば
少なくとも旧バージョンでは使えない理由がわかると思うので試します。

お礼日時：2022/11/09 16:23

No.3 回答者： angkor_h 回答日時： 2022/11/09 14:12

No.2です。

> はい、分解した結果、…
ならば、どの段階でエラーとなったか、が見つかったはずです。

No.2 回答者： angkor_h 回答日時： 2022/11/09 13:53

関数を組み合わせた関数(多重関数)は、そのままでは解明が困難です。

解明するためには、複数の関数の計算順に従って、
一つの関数毎に実行し、その結果を各列に表示していく、
これが一番有効的です。
お試しください。

この回答へのお礼

はい、分解した結果、最後に疑問が残ったので質問しました。

お礼日時：2022/11/09 14:04

No.1 回答者： ichigen-ichigen 回答日時： 2022/11/09 13:51

そういう作りだからです。