エクセルで求めたセルの左隣りのセルの値を返したいです

質問

お世話になります
INDEXとMatchを使い行の一番右(左から順にセルが埋まるので右端のセルが一定では無い)の値を返すようにしました。以下です。

しかし、一番右から２番目のセルの値(求めたものの左隣り)もほしいのです。
その場合はどうしたらよいのでしょうか？

=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))))

宜しくお願い致します。

回答No.1

merlionXX · ベストアンサー

> INDEXとMatchを使い行の一番右(左から順にセルが埋まるので右端のセルが一定では無い)の値を返すようにしました。

数値にも文字列にも対応したすばらしい式ですね。感心しました。

> 一番右から２番目のセルの値(求めたものの左隣り)もほしいのです。

こんな数式を作れる人が何をおしゃいますことやら・・・。
左隣ならINDEXを１つ少なくするだけのことですから

=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)))-1)

でいいんじゃないでしょうか？

お礼

あああ。そんな簡単なことで良いのですね。

もともとの式は似て非なるものを前任の方が残して行ったので、それをそれを紐解いて応用して作成したものなのです。
あるものを理解し紐解いて応用できたのですが、そこにない情報をどのように組み入れるのかがさっぱりわからず。。。。

なるほどぉ。。。見るとわかるのですが、作り出せないので感動しました。
ありがとうございました。

回答No.8

maron--5

◆こんな方法もありますよ
=INDEX(1:1,LARGE(INDEX((1:1<>"")*(COLUMN(1:1)),),2))
★後ろの数字が一番右から何番目かになります

お礼

本当にいろいろな方法が考えられるものですね。これは単純化されていて良いですね。

次回にまたこの回答例を参考にいろいろ自分でも応用してみようと思います。

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

回答No.7

howarage

左隣が空白の場合にも対処するにはMAXの代わりに、LARGE関数使えばよかったのですね。

｛=INDEX(1:1,LARGE(IF(ISBLANK(1:1),0,COLUMN(1:1)),2))｝

教えて！は本当に勉強になります。

お礼

本当にいろいろな方法が考えられるものですね。

次回にまたこの回答例を参考にいろいろ自分でも応用してみようと思います。

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

回答No.6

howarage

みなさんいろいろなやり方を考えますねぇ

僕もひとつ考えました。配列数式を使いますが、式が直感的でわかりやすかなと思います。

｛=INDEX(1:1,MAX(IF(ISBLANK(1:1),0,COLUMN(1:1))))｝

左隣はやはりINDEXの-1なんですが、その左隣が空白の場合はどうすればいいのか。。。正直、悩んでます。

お礼

本当にいろいろな方法が考えられるものですね。

次回にまたこの回答例を参考にいろいろ自分でも応用してみようと思います。

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

回答No.5

noname#176215

左から順に入っているのなら 既に出ている方法(COUNTを使う方法)
でできそうですが せっかくなので

> =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))))
この式は

=LOOKUP(10^16,CODE(1:1),1:1)

こうすることもできますね。ひとつ前なら

=INDEX(1:1,LOOKUP(10^16,CODE(1:1),COLUMN(1:1)-1))

お礼

なるほどぉぉぉ。

本当に勉強になりますねぇ。。。。

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

回答No.4

imogasi

ＩＮＤＥＸ関数の列を示す引数のところにー１すれば済む。
同行一番右は
=INDEX(A1:Z1,1,COUNTA(A1:Z1))　（Ｚ列までが入力範囲として）
もし式複写するなら　=INDEX($A1:$Z1,1,COUNTA($A1:Z$1))など
または
=INDEX(1:1,1,COUNTA(1:1))
その左はー１列すればよい
=INDEX(A1:Z1,1,COUNTA(A1:Z1)-1)
または
=INDEX(1:1,1,COUNTA(1:1)-1)

お礼

そのようですね。下の回答を拝見して学びました。
このマイナス１が曲者でした。

このような使い方ができるとは知らなかったので、とても役に立ちました。

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

回答No.3

onlyrom

よくもまあこんな複雑な式を作りましたねぇ。
２，３ケ月後に見て、直ぐ分かるのだろうかと人事ながらちょと心配。。。(^^;;;

途中に未入力セルがない前提なら、、、

●右端の値

　　=INDEX(1:1,0,COUNTA(1:1))

●右端よりひとつ前の値

　　=INDEX(1:1,0,COUNTA(1:1)-1)

●右端より二つ前の値

　　=INDEX(1:1,0,COUNTA(1:1)-2)

外しましたらご容赦願！
以上です。

お礼

ありがとうございます。
そうなんです。未入力セルがなければ一番右はこれだと私も思うのです。
ところが、空白があるもので、これだとだめなんです。

でも、右寄りひとつ前の値への応用は#1の場合と同じでこれが私にはわからなかったので、大変勉強になりました。

回答No.2

okormazd

複雑な式なので、にわかには理解できませんでした。
で、
こんなのでは駄目なんですか。

一番右
=INDIRECT(ADDRESS(1,COUNTA(1:1)))
左隣
=INDIRECT(ADDRESS(1,COUNTA(1:1)-1))

お礼

ありがとうございます。
INDIRECTは使ったことがない関数で、今パラパラと本をめくってなるほどと思いました。
次回何かの折にぜひ使用したいものです。

ところが今回は空白があるものでこれだとだめなんです。

でも、右寄りひとつ前の値への応用は#1の場合と同じでこれが私にはわからなかったので、大変勉強になりました