エクセルでの最終セルの表示方法

質問

エクセルで困っております。
例）
(1)列にA1からA10まで数字を入れる枠を設定します。
(2)A1,A2,A3と数字を入れて、A4,A5は空欄とします。
(3)その場合、A11に表示するのはA3の数字が表示する。
(4)また続いてA6に数字を入れるとA11はそのA6の数字が表示する。
といったように列の最終の数字を表示させるにはどうすればいいでしょうか。
今は、列についての質問ですが、それが行に変わっても同じでしょうか。
もしかしたら簡単な内容かもしれませんが、どうも初心者なもんでどのような関数を使えばいいのか分かりません。
よろしくお願いします。

回答No.13

noname#204879 · ベストアンサー

[NO.11回答に対する補足]に対するコメント

正直に申し上げて、説明するのに聊か疲れて来ました。(^_^)

》 ISNUMBERとCOLUMNをなぜかける（＊）のでしょうか。

11．{1,2,3,0,0,6,0,0,0} を得るため。

》 (6,4,2,5)はどこへ行ったのでしょうか。

12．何処へも行きません。というか、そんなことを考える必要は何もありません。

》 ２番目に大きい数値は５になると思うのですが…。

13．{1,2,3,0,0,6,0,0,0} の各数値を見れば明らかなように、数値を大きい順に並べると 6,3,2,1,0 で、５なんて何処にもありゃしません。

》 やっぱり、＊(かけ算)をする意味が分かりません。

14．既に第11項で述べました。

》 うーんやはりＡ：ＩとＢ：Ｊの違いがよく分かりません。

う～ん、困りましたね。第10項の説明をよ～く読んで、としか言いようがないですね。
Excel のヘルプでINDEX関数の「INDEX 書式 1」を勉強してください。
=INDEX(配列,行番号,列番号) が書式です。私が提示した式は =INDEX(B1:J1,0,…) のように（いま気が付いたけど、0 は 1 とすべきでしたか。結果的には 0 でも問題ないようだけど）、「配列」は B1:J1 です。「…」の部分は「列番号」を指定する次第。１列目、２列目、…、９列目が、それぞれ B1、C1、…、J1 ですよね。当然のことながら「列番号」の最小値は 1 です。1 を返す COLUMN関数は COLUMN(A) で、9 を返す COLUMN関数は COLUMN(J) です。

お礼

すんまへん！
長期間回答いただき有難うございました。
もっと勉強しないといけないことがいっぱいあるようです。
ご迷惑をおかけしました。
２０ポイントでは少ないかも・・・。
ちなみに、このような疑問を解決する方法（サイトやテキスト）ってあるのでしょうか。

回答No.12

imogasi

＃６です。補足の
＞初心者としては非常に難しいご回答かと思います。
いえいえ簡単です。
d = Range("A11").End(xlUp).Row
がミソですが、たった１行です。時々他のVBAの質問の他の方の回答にも出てきます。
この意味は、A11をマウスでポイントしておいて、ENDキーを押し、その後↑キーを押すと、A列の一番下のデータのあるセルをポイントされると思います。このことを使い、最下のデータ行番号を取得し、そのセルの値を得ることを、コード化しただけです。

回答No.11

noname#204879

[NO.10回答に対する補足]に対するコメント

概ね理解しておられるので安心しました。そして、コメントし易いように箇条書きにされたことに敬意を表します。

》 ●COLUMN(A:I)は、AからIのうち一番左のセルの番号を返す。
》 　"1"いわば左が小さく右が大きいという意味・・・？

１．=COLUMN(A1)、COLUMN(B1)、…、COLUMN(I1) はそれぞれ 1、2、…、9 を返して来ます。
従って配列数式内の COLUMN(A1:I1) は、配列{1,2,…,9} を返して来ます。COLUMN(A:I) は COLUMN(An:In) と同じことです。

》 ●ISNUMBER(B1:J1)は、B1からJ1が数値であることを調べる。

２．そのとおり。

》 ●LARGE(ISNUMBER(B1:J1)*COLUMN(A:I),2)は、B1からJ1のうち大きい方
》　（COLUMN(A:I)で一番左が小さい）から数えてから数えて2番目の数字を返す。

３．仰るとおりですが、繰り返しますと次のようになります。
４．セル B1、C1、D1、G1 にそれぞれ数値 6、4、2、5 が入力されていたと仮定します。
５．ISNUMBER(B1:J1) は配列 {TRUE,TRUE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE} を返して来ます。
６．従って ISNUMBER(B1:J1)*COLUMN(A:I) は、配列{TRUE*1,TRUE*2,TRUE*3,FALSE*4,FALSE*5,TRUE*6,FALSE*7,FALSE*8,FALSE*9} のことであり、この結果は配列 {1,2,3,0,0,6,0,0,0} になります。
７．LARGE({1,2,3,0,0,6,0,0,0},1) は配列の最大値 6 を、LARGE({1,2,3,0,0,6,0,0,0},2) は２番目に大きい数値 3 を返して来ます。

》 ●INDEX(B1:J1,0,LARGE(ISNUMBER(B1:J1)*COLUMN(A:I),2))は、B1からJ1の
》 　うち、この行の右から数えて2番目に大きい数字を返す。

８．結果的には同じことですが、左から 3 番目にあるセル D1 の内容を返すということです。

》 ISNUMBER(B1:J1)とCOLUMN(A:I)を＊で表現している意味が分かりません。

９．既に６で説明しました。

》 COLUMN(A:I)だけで、ずれている理由が分かりません。

10．既に１で述べたように、配列 {1,2,3,4,5,6,7,8,9} の代用に用いただけで、何かからずらしたということではありません。例えば、配列 {27,28,29} が欲しければ、COLUMN(AA:AC) とする次第。しかし、場合に依っては、此れに限らず、ROW(A27:A29) とか ROW(27:29) でもＯＫだと思います。

補足

詳しい説明ありがとうございました。
概ね正解だと聞いてうれしいです。
(1)ところで、４の仮定から５の説明は理解できたのですが、
６の『従って ISNUMBER(B1:J1)*COLUMN(A:I) は、』の部分において、ISNUMBERとCOLUMNをなぜかける（＊）のでしょうか。
(2)６の『結果は配列 {1,2,3,0,0,6,0,0,0} になります。』ですが、４で仮定した数値(6,4,2,5)はどこへ行ったのでしょうか。
７の結果は最大値は確かに６ですが、２番目に大きい数値は５になると思うのですが…。確かに配列 {1,2,3,0,0,6,0,0,0}が答えであれば７，は分かるのですが…。やっぱり、＊(かけ算)をする意味が分かりません。
(3)１０で「何かからずらしたということではありません。」ということであれば、COLUMN(B:Ｊ)でも問題なく答えが出るということですよねぇ。
うーんやはりＡ：ＩとＢ：Ｊの違いがよく分かりません。

回答No.10

noname#204879

[NO.9回答に対する補足]に対するコメント

{=INDEX(B1:J1,0,LARGE(ISNUMBER(B1:J1)*COLUMN(A:I),2))-INDEX(B1:J1,0,LARGE(ISNUMBER(B1:J1)*COLUMN(A:I),1))}

》 どうも意味がわからないんですよねぇ…。

何処がわかるのかも分からない？

補足

私なりにいろいろ調べて勉強しました。「なるほど！」ってことから始まり、「自分の力でここまでの関数を作り上げるのは厳しいなぁ～」というのが感想です。
話が横にそれましたが、解読してみました。
=INDEX(B1:J1,0,LARGE(ISNUMBER(B1:J1)*COLUMN(A:I),2))このうち、
●COLUMN(A:I)は、AからIのうち一番左のセルの番号を返す。"1"いわば左が小さく右が大きいという意味・・・？
●ISNUMBER(B1:J1)は、B1からJ1が数値であることを調べる。
●LARGE(ISNUMBER(B1:J1)*COLUMN(A:I),2)は、B1からJ1のうち大きい方（COLUMN(A:I)で一番左が小さい）から数えてから数えて2番目の数字を返す。
●INDEX(B1:J1,0,LARGE(ISNUMBER(B1:J1)*COLUMN(A:I),2))は、B1からJ1のうち、この行の右から数えて2番目に大きい数字を返す。
というのでどうでしょうか。
しかし、ISNUMBER(B1:J1)とCOLUMN(A:I)を＊で表現している意味が分かりません。また、=INDEXやISNUMBERがBからJなのに、COLUMN(A:I)だけで、ずれている理由が分かりません。ずらしていることについて何か意味があるのですね。そこがよく分からない点です。

回答No.9

noname#204879

[NO.8回答に対する補足]に対するコメント

ひねる必要はないけど、式の意味を理解しないとォ…
{=INDEX(A1:J1,0,LARGE(ISNUMBER(A1:J1)*COLUMN(A:J),2))-INDEX(A1:J1,0,LARGE(ISNUMBER(A1:J1)*COLUMN(A:J),1))}

補足

何度もありがとうございます。
おっしゃるとおり、式の意味を理解しないとダメですねぇ～。

あつかましく追加（最後の質問になるかと…）の質問です。
この式の範囲をB1からJ1にして変更して、結果をK1に表示しようとした場合
式をそのままにしてA1をB1に変更しただけでは、
結果が正しく表示できません。
なぜでしょう？
下記の通りです。
{=INDEX(B1:J1,0,LARGE(ISNUMBER(B1:J1)*COLUMN(B:J),2))-INDEX(B1:J1,0,LARGE(ISNUMBER(B1:J1)*COLUMN(B:J),1))}

どうも意味がわからないんですよねぇ…。

回答No.8

noname#204879

[NO.7回答に対する補足]に対するコメント

{=INDEX(A1:A10,LARGE(ISNUMBER(A1:A10)*ROW(1:10),2),0)-INDEX(A1:A10,LARGE(ISNUMBER(A1:A10)*ROW(1:10),1),0)}

補足

ご回答ありがとうございました。
引き続きの質問です。
列を行に変えた場合、これまでのご回答を参考に下記のように書き換えたのですが、うまくいきません。
単純に変更しただけで、あまりひねりは加えていません。
度々の質問、ごめんなさい。
{=INDEX(A1:J1,0,LARGE(ISNUMBER(A1:J1)*COLUMN(A:J),2),0)-INDEX(A1:J1,0,LARGE(ISNUMBER(A1:J1)*COLUMN(A:J),1),0)}

よろしくお願いします。

回答No.7

noname#204879

[NO.4回答に対する補足]に対するコメント

》 私なりに試してみたのですが、上手くいきませんでした

式の内容を理解しておかないと応用できないから、そういうことになりますね。

{=INDEX(A1:J1,0,MAX(ISNUMBER(A1:J1)*COLUMN(A:J)))}

補足

早々と回答いただきありがとうございました。
おっしゃるとおり「式の内容を理解しておかないと応用できない」ですね。
勉強したいと思います。
そう言った中で、追加の質問があります。

最終の入力セル(A6)の値はmike_gさまのご回答でうまくいきましたが、
最終の入力セルの一つ前の入力セル(A3)との差(A3-A6)を算出したいのですが、
可能でしょうか。

回答No.6

imogasi

着信の遅れで、＃２のご回答がわからず、同じような回答になったので、別解を上げます
（１）Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 And Target.Row < 11 Then
d = Range("A11").End(xlUp).Row
Cells(12, "A") = Cells(d, "A")
End If
End Sub
シートモジュールのChangeイベントに貼り付ける。
（２）または、標準モジュールに
Function de(a As Range)
Application.Volatile
d = a.End(xlUp).Row
de = Cells(d, "A")
End Function
を貼り付ける（ユーザー関数を作る）。
セルに関数式　=de(A10)を入れる。

補足

度々回答をありがとうございました。
しかし私、初心者としては非常に難しいご回答かと思います。
今後の勉強のために、時間やスペースが許す限り詳しく教えていただければ幸いです。

回答No.5

sige1701

=LOOKUP(10^17,A1:A10)

補足

素早い回答ありがとうございました。バッチリです。
ところで、あまりに簡単な数式で驚いております。
ちなみにこの意味を教えていただけないでしょうか。
今後の参考にさせていただきたいと思います。

回答No.4

noname#204879

No.2 の追加コメントです。

次式でもＯＫです。
{=INDEX(A1:A10,MAX(ISNUMBER(A1:A10)*ROW(1:10)),0)}

補足

素早い回答ありがとうございました。バッチリです。
ところで、これを（行）に変えたらどうなるのでしょうか。
(1)列にA1からJ1まで数値を入れる枠を設定します。
(2)A1,B1,C1と数値を入れて、D1,E1は空欄とします。
(3)その場合、K1に表示するのはC1の数字が表示する。
(4)また続いてF1に数字を入れるとK1はそのF1の数字が表示する。
一応私なりに試してみたのですが、上手くいきませんでした。
よろしくお願いします。

回答No.3

imogasi

結構難しい.
例データ
もちろん途中で空白行ありはOK。
A1:A10
31
2

23

55

A１：A10以外のセルに
=INDEX(A1:A10,MAX(IF(A1:A10<>"",ROW(A1:A10))),1)
と入れてSHIFT＋CTRL+ENTERの３つのキーを同時に押す。（配列数式）
結果
55

補足

素早い回答ありがとうございました。バッチリです。
ところで、これを（行）に変えたらどうなるのでしょうか。
(1)列にA1からJ1まで数値を入れる枠を設定します。
(2)A1,B1,C1と数値を入れて、D1,E1は空欄とします。
(3)その場合、K1に表示するのはC1の数字が表示する。
(4)また続いてF1に数字を入れるとK1はそのF1の数字が表示する。
一応私なりに試してみたのですが、上手くいきませんでした。
よろしくお願いします。