エクセル再質問です

質問

A1からA10まで計算式が入ったセルがあります、
A1を最初、A10を最後とします、
このセルにランダムに計算された数字が表示されす、たとえば数字が表示されたセルがa3、a5、a6、a7
だったとします、このセルのうち最後のa7に表示された数字をb1に表示する方法を教えてください、
との質問をしました、
実際にはa1から順番にa1には=d11,a2には=d22等と他のセルから持ってきています、以下の教えてもらった数式で新たなシートでテストしたら正常に動作します、でも実際のシート上では#REF!のエラーになります、=INDEX($A$1:$A$10,MAX(IF(LEN($A$1:$A$10)=0,0,ROW($A$1:$A$10))))
また上記算式で=0,0,ROWの=0,0,は何を表しているのでしょうか、実際の数字は十万単位で小数点以下四桁程で表示されます、よろしくお願いします、

回答No.1

comv · ベストアンサー

こんばんは

示された式についてではなく代替の式ですが

=LOOKUP(10^17,A1:A10)
よかったら試してみて下さい

もし全てのセルに数値が入らない時には　#N/Aエラー
となりますからそれを回避する必要があるなら

=IF(COUNT(A1:A10),LOOKUP(10^17,A1:A10),"")

とすれば、数値がない時には　なにも表示しません

補足

本当にありがとうございます、
A1からA10には他のセルから持ってきています、
(A1には=d11,A2には=d22等です)そのため数字が入らないセルには0が表示されます、そのためにA10が0の表示だとb1には0が表示されます、0では無い数字を表示させるためにはどのようにしたら良いでしょうか、
また数式中の10^17の意味を教えてください、

回答No.4

taisuke555

＞併せて10の17乗を使うわけを教えてほしかっです。
私の説明が合っているかわかりませんが、一応の解説です。

「ヘルプ抜粋」
VLOOKUP(検索値, 範囲, 列番号, 検索の型)

範囲　　　　検索の型 に TRUE を指定した場合、範囲 の左端の列のデータは、
　　　　　　昇順に並べ替えておく必要があります。

検索の型　　TRUE を指定するか省略すると、検索値 が見つからない場合に、
　　　　　　検索値 未満で最も大きい値が使用されます。

・普通、検索の型をTRUEにする場合、ソート（小さい順）に並んでいる必要があります。

ですから、=LOOKUP(10^17,A1:A10) は、
EXCEL側でデータが昇順に並んでいると思い込む訳です。

という事は、10^17より大きい値が無ければ、一番下に入力されている値が一番近い値と判断し、表示するという事になります。

10^17でなくても10^18や記入される値より大きな値ならば問題ないと思います。（推測）

という事だと思いますが・・・間違っていたらすみません。

お礼

本当にありがとうございました、

回答No.3

taisuke555

＞0では無い数字を表示させるためにはどのようにしたら良いでしょうか
配列数式です。上記と同じ方法なので、
[Enter]のかわりに、[Ctrl]+[Shift]+[Enter]を押してください。

=OFFSET(A1,MAX(IF(A1:A10<>0,ROW(A1:A10)-1,0)),0)

質問の式と似たような感じですが、
OFFSETは
OFFSET(基点、下方向への数、右方向への数）
で基点のセルから下方向へいくつ、右方向へいくつの場所のセルの値を返します。

下方向への数が
MAX(IF(A1:A10<>0,ROW(A1:A10)-1,0)です。
この部分が配列数式の部分で、
もしA1が0以外ならば、A1の行番号－１を返し、A1が0以下ならば0を返し、それをA10まで繰り返し、最大の数（行番号）を返します。

　　　Ａ　　　Ｂ
１　　０　　　=IF(A1<>0,ROW(A1)-1,0)=0
２　　０．２　=IF(A2<>0,ROW(A2)-1,0)=1
３　　０　　　=IF(A3<>0,ROW(A3)-1,0)=0
４　　０　　　=IF(A4<>0,ROW(A4)-1,0)=0
５　　０　　　=IF(A5<>0,ROW(A5)-1,0)=0
６　　０　　　=IF(A6<>0,ROW(A6)-1,0)=0
７　　０　　　=IF(A7<>0,ROW(A7)-1,0)=0
８　　１．３　=IF(A8<>0,ROW(A8)-1,0)=7
９　　０　　　=IF(A9<>0,ROW(A9)-1,0)=0
10　　０　　　=IF(A10<>0,ROW(A10)-1,0)=0
11　　　　　　=MAX(B1:B10) = 7
という作業（B列）を１つの式で行っています。

右方向へは今回は必要ないので、０になっています。
配列数式の考え方は説明するのが難しいですが、分かっていただけたでしょうか？

質問の式(INDEX)は範囲を指定しているので、範囲外を参照しようとすると、[#REF!]が表示されます。

＃１さんの方法で０以外のというのはできないと思います。（推測）
A1セルの=D11を
=IF(D11<>0,D11,"")のように0は表示しないようにすれば、できると思います。
＞数式中の10^17の意味を教えてください
　１０の１７乗という意味です。
　VLOOKUPの性質を生かした書き方ですね！
　説明は長くなりそうなので、comvさんにお任せします。

長々と失礼しました。

お礼

ありがとうございました、併せて10の17乗を使うわけを教えてほしかっです。

回答No.2

he-goshite-

>また上記算式で=0,0,ROWの=0,0,は何を表しているのでしょうか、

IF関数の内容です。
IF(○，×，△）の，○の部分（条件）に
LEN($A$1:$A$10)=0

×の部分（条件が真のとき）にとる値として
0

また，△の部分（条件が偽のとき）にとる値として
ROW($A$1:$A$10)
が指定されています。

お礼

ありがとうございました、