ＭＳ Exelで次の機能を作りたいのですが。

質問

たとえばひとつの列のＢ3からＢ10のセルに適当に数値が，あるいは文字がはいっている(但し、ブランクのセルもある)として、　そのうちＢ10に近い（Ｂ10も含んで)セルにあるデータをＢ11に入れる機能。　どのように関数を組み合わせたらよいか悩んでいます。DO　I=1　to　10　のような繰り返しが出来ないので　それに置き換わる計算式を考えてみているのですが、思いつきません。このような計算が簡単に思いつかれた方おしえてください。VBAは考えていません。エクセルの関数を使った方法で出来ないか、ということです。
宜しくおねがいします。

回答No.5

nishi6 · ベストアンサー

行数が不定のようなので作り直しました。
「=IF(COUNTA(B3:B10)>0,OFFSET($A$1,MAX(IF(B3:B10>0,ROW(B3:B10)))-1,COLUMN(B10)-1),"")」
配列数式ですので登録するときは、CtrlキーとShiftキーを押しながらEnterキーを押して下さい。上式の「B3:B10」は任意の範囲にして下さい。どのようなセル範囲についても表示可能と思います。（列は1列です）

指定した配列数式内で登録された最大の行番号を求め、A1セルからの座標位置を決めています。(他の方法もあった？)
Columnの引数は列座標を求めるためです。自分以外の同一列のセルを指定してください。
countAは配列数式内が全て未登録の場合の対応です。

お礼

Nishi6さん、有難うございます。肝はMAX(ROWS(B3:B10)),Column(B10))という風に理解しました。これで行が挿入されて変更になっても、自動的に対応されそうですね。IF(B3:B10>0,…は何を調べているのでしょうか？また機会がありました教えてください。返事が遅くなり申し訳ありませんでした。

回答No.6

kbonb

こんにちは

1.B3:B10内で最下行にある数値または文字列を返す数式

=INDEX(B3:B10,MAX(ROW(B3:B10)*(B3:B10<>"")-ROW(B3)+1))
Ctrl+Shift+Enterで入力

2.B3:B10内で最下行にある数値を返す数式

=INDEX(D3:D10,MATCH(MAX(D3:D10)+1,D3:D10,1))

3.B3:B10内で最下行にある文字列を返す数式

=INDEX(B3:B10,MATCH("*",B3:B10,-1))

参考URL：

http://www2.odn.ne.jp/excel/

お礼

ご回答有難うございます。Nishi6さんのご回答とほぼ同じ内容と個人的に独断で判断させていただき、この件はCloseさせていただきます。有難うございました。

回答No.4

nishi6

無理やり作ってみました。(IFを使わないで)
B11セルにコピーしてください。
未登録のセル番号の最大値(B3が1)を求めてchooseで抽出しています。行数が増えたら変更する必要があります。
=CHOOSE(MAX((B3<>"")*1,(B4<>"")*2,(B5<>"")*3,(B6<>"")*4,(B7<>"")*5,(B8<>"")*6,(B9<>"")*7,(B10<>"")*8)+1,"",B3,B4,B5,B6,B7,B8,B9,B10)
VBAは考えていないようですが、ユーザー定義関数なら関数的に使えますが・・・・
Public Function Toroku_Last(rg As Range)
Dim cot As Integer
With rg
For cot = 0 To 7
If .Offset(-cot, 0) <> "" Then
Toroku_Last = .Offset(-cot, 0)
Exit For
End If
Next
End With
End Function
B11セルは'=Toroku_Last(B10)'としてください。

回答No.3

Kaz003

データが３行から１０行までなら
例えばB11に
=IF(B10="",IF(B9="",IF(B8="",IF(B7="",IF(B6="",IF(B5="",IF(B4="",B3,B4),B5),B6),B7),B8),B9),B10)
とすればいいですね。　
C列以降は同様に数式コピーでいけます。

ただ、私感になりますがもっとデータ行数が増えたりすると if(if(if・・・もどうかと思うので、先の回答の方が使えるような気がします。　この場合必ずしも隣のセルである必要はありませんので例えばデータがB列からZ列まであるとしても、AA列以降とか（あるいは別のsheetとか)に同じ大きさの判定用領域があればいいわけですし、EXCELの数式コピーは容易ですので、お勧めだと思うのですが・・・

補足

有難うございます。B3からB10と言ったのは例示に過ぎません。実際の私の表はもっと長い列になっています。
エクセルでは関数のネステングは16(?)までになっているとありましたのでそれ以上の長い列には途中でいったんどこかにセーブしないと長続きしません。また隣の列でなくともという意見もわかりますが、個人的にはもっとスマートな方法で其れが達成されないかなということ希望があります。宜しくお願いします。

回答No.2

Kaz003

例えばC3のセルに
=IF(B3<>"",ROW(B3),0)と入力しC10までフィルすると
Bnが空白でない時の行番号が数値としてCnに返されます。
で、B11に
=INDEX(B3:B10,MATCH(MAX(C3:C10),C3:C10,1),1)
とすると
B10に近いセルでブランクでないセルの値（数値、文字列）
がB11に出ます。

補足

Kaz003 さん及びarataさん、ご回答ありがとうございます。補足させていただきますと、B3からB10のようなData列がB列の隣にも10数列並んでいると考えてください。したがって、B列の隣に計算を助ける列を新たに入れることは考えていません。但しB10と結果をいれるB11
の間に一セルくらいの計算を手助けするセルを入れることは吝かではありません。でも望むらくは、B11にエクセルで提供されている関数を組み合わせた計算式をいれることで目的を達成できたらと考えています。　宜しくお願いします。

回答No.1

arata

質問の内容では、仕様が良くわからないのですが、

□Ｂ１０からＢ３に向かってブランクでないセルを探していって、最初のブランクでないセルの値をＢ１１に入れる

ということでしょうか？

それとも、

□Ｂ３からＢ９に入っている値で、Ｂ１０に最も近い値（近似値）のものをＢ１１に入れると言うことでしょうか？

前者であれば、こんな感じにすればいいのでは？

Ｃ３：=B3
Ｃ４：=IF(B4="",C3,B4)
Ｃ５：=IF(B5="",C4,B5)
Ｃ６：=IF(B6="",C5,B6)
：
省略
：
Ｃ９：=IF(B9="",C8,B9)
Ｃ１０：=IF(B10="",C9,B10)
Ｂ１１：=C10

後者だとしたら、数値以外が混じると難しいですね。
数字だけなら出来ると思いますけど・・・

補足

Arataさんご回答ありがとうございます。最初の質問におこたえします。そうです。B10からB3にむかってブランクでないセルを探しにいって最初のセルの内容をB11に戻したいということです。kz003さんの補足にもかきましたが、B列の隣には似たような列がさらにいくつか並んでいて、B列の隣は計算補助としては使えないと考えてください。　宜しくお願いします。