EXCELで右側から検索したい

質問

EXCELで例えば下記のようなデータが入力されています。

セルA セルB セルC ・・・
○　　●　　　　　○　　○
　　　○　　●　　　　　◆

この状態で、値の入っているセルのうち一番右側にあるセルを
取り出したい場合、どうすればいいでしょうか。
空白はどこに入るか、いくつ入るか予測出来ません。

教えてください。よろしくお願いします。

回答No.5

nishi6 · ベストアンサー

再度の回答です。複数行も対応できたみたいです。（まだ簡単になる可能性がありますが・・・）

1行目から100行目の例です。式の中の『100』が100行目を表します。

●値が数値のみなら

　　=MAX(OFFSET(A1:A100,0,MAX(IF(1:100<>"",COLUMN(1:100)))-1,100,1))

　一番右の列の複数行に数値があれば、大きいほうがでます。

●値が文字列のみなら

　　=OFFSET(A1,MATCH("",OFFSET(A1:A100,0,MAX(IF(1:100<>"",COLUMN(1:100)))-1,100,1),-1)-1,
　　　　　　　　　　　　　　　　　　　　 MAX(IF(1:100<>"",COLUMN(1:100)))-1)

　式はつながっています。一番右の列の複数行に文字があれば、下にあるほうがでます。

上記２式とも配列数式です。数式バーから登録する時、Ctrl+Shift+Enter とします。
失敗したらＦ2キーを押して編集モードにして、再度、Ctrl+Shift+Enter とします。
配列数式として登録できれば、算式が {　} で囲まれます。
（Ctrl+Shift+Enter : 登録する時、CtrlキーとShiftキーを押しながらEnterキーを押します）

●値が数値と文字列の混在なら

　上２式をひねり回せば出そうですが、最初のユーザー定義関数が楽ですね。

横方向に考えること、また、複数行と複数列をいっしょに対象にするのはけっこう難しいですね。

お礼

再三丁寧なアドバイスありがとうございます。
最初に教えて頂いたやり方で上手く出来ました。
それにしても仰る通り、横方向に考えるのは難しいです。
値は文字列のみなのでよかったですが、数値との混在だと・・・

ホントに助かりました。
また何かあった時はよろしくお願いします！

回答No.4

imogasi

私なら目視で,一番右側の列にデータが有る個所をみつけて
例えばＳ列なら２４と考えて２４＋１＝２５を考えます。
Sub test01()
For j = 1 To 100 '１００行のデータと仮定
For i = 25 To 1 Step -1
If Worksheets("sheet1").Cells(j, i) <> "" Then
Worksheets("sheet1").Cells(j, 26) = i
Exit For
Else
End If
Next i
Next j
End Sub
と極く簡単なＶＢＡを組みます。
Ｔ列に列番号で出していますが、それはそれで都合が良い時がありますし、「Ｊ３」などの表現にも数ステップで
変えられます。データのないセルは値が""で有るものと仮定しています。

回答No.3

kbonb

こんにちは

> 文字か数値か不明

　以下のページがご参考になるのでは？

【エクセル技道場】－関数－最下行にある数値または文字列を返す数式
http://www2.odn.ne.jp/excel/waza/function.html#SEC66

参考URL：

http://www2.odn.ne.jp/excel/waza/function.html#SEC66

お礼

とても参考になりました。
ありがとうございます。

また機会がありましたらよろしくお願いします。

回答No.2

nishi6

質問が行単位なら、
　=INDEX(1:1,MAX(IF(COUNT(1:1),MAX(MATCH(MAX(1:1)+1,1:1,1))),IF(COUNTIF(1:1,"*"),MATCH("",1:1,-1))))
で、１行目の１番右のセルは求まります。文字か数値か不明なので両方調べています。（値を返す関数）
2行目以降も同じようにできます。(上では『1:1』が1行目の意味です)

ただし、複数行の一番右となると、上の式を変形して、行数分計算する事になりそうです。
面倒なので、ユーザー定義関数を書いてみました。

１～３行目の一番右のセルを求めるには　=LastColumn(1,3) のように使います。
ただ、『一番右側にあるセルを取り出したい』の意味がセルの値が必要か、セルの座標なのかわかりませんので両方出るようにしてあります。（最後から２、3行目で必要な方の『'』を取ります）

ツール→マクロ→Visual Basic Editor でVBE画面に移り、挿入→標準モジュールで標準モジュールを挿入し、そのコードウインドウに、下記コードをコピーして貼りつけます。

ここから
↓
Function LastColumn(startRow As Long, endRow As Long)
　　Dim rw As Long '行カウンタ
　　Dim MostRigrhtCell As Range '指定範囲でもっとも右のセル
　　Dim workColumn1 As Integer '列カウンタ１
　　Dim workColumn2 As Integer '列カウンタ２

　　Application.Volatile '自動再計算関数

　　workColumn1 = 0
　　For rw = startRow To endRow
　　　　If Range("IV" & rw).Text = "" Then
　　　　　　'一番右のIVに入力がなければ、左側を調べる
　　　　　　workColumn2 = Range("IV" & rw).End(xlToLeft).Column
　　　　Else
　　　　　　'一番右のIVに入力があればそこが一番右
　　　　　　workColumn2 = 256
　　　　End If
　　　　'今の位置が一番右か調べる
　　　　If workColumn1 < workColumn2 Then
　　　　　　workColumn1 = workColumn2
　　　　　　Set LastColumn = Cells(rw, workColumn1)
　　　　End If
　　Next
　　'『一番右側にあるセルを取り出したい』の意味が不明
　　'　　セルの値とセルの座標を表示してみた。
　　'　　不要ならばどちらかを消去します
　　LastColumn = LastColumn.Value & ":" & LastColumn.Address(0, 0)
　　'LastColumn = LastColumn.Value 'こちらはセルの値
　　'LastColumn = LastColumn.Address(0, 0) 'こちらはセルの座標
End Function

回答No.1

kbonb

こんにちは

　以下のページがご参考になるのでは？

【エクセル技道場】－関数－最下行の文字列を返す（途中に未入力セルあり）
http://www2.odn.ne.jp/excel/waza/function.html#SEC65

参考URL：

http://www2.odn.ne.jp/excel/waza/function.html#SEC65