たとえばひとつの列のB3からB10のセルに適当に数値が,あるいは文字がはいっている(但し、ブランクのセルもある)として、 そのうちB10に近い(B10も含んで)セルにあるデータをB11に入れる機能。 どのように関数を組み合わせたらよいか悩んでいます。DO I=1 to 10 のような繰り返しが出来ないので それに置き換わる計算式を考えてみているのですが、思いつきません。このような計算が簡単に思いつかれた方おしえてください。VBAは考えていません。エクセルの関数を使った方法で出来ないか、ということです。
宜しくおねがいします。
No.1
- 回答日時:
質問の内容では、仕様が良くわからないのですが、
□B10からB3に向かってブランクでないセルを探していって、最初のブランクでないセルの値をB11に入れる
ということでしょうか?
それとも、
□B3からB9に入っている値で、B10に最も近い値(近似値)のものをB11に入れると言うことでしょうか?
前者であれば、こんな感じにすればいいのでは?
C3:=B3
C4:=IF(B4="",C3,B4)
C5:=IF(B5="",C4,B5)
C6:=IF(B6="",C5,B6)
:
省略
:
C9:=IF(B9="",C8,B9)
C10:=IF(B10="",C9,B10)
B11:=C10
後者だとしたら、数値以外が混じると難しいですね。
数字だけなら出来ると思いますけど・・・
この回答への補足
Arataさんご回答ありがとうございます。最初の質問におこたえします。そうです。B10からB3にむかってブランクでないセルを探しにいって最初のセルの内容をB11に戻したいということです。kz003さんの補足にもかきましたが、B列の隣には似たような列がさらにいくつか並んでいて、B列の隣は計算補助としては使えないと考えてください。 宜しくお願いします。
補足日時:2001/01/18 08:46No.2
- 回答日時:
例えば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.3
- 回答日時:
データが3行から10行までなら
例えば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.4
- 回答日時:
無理やり作ってみました。
(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.5ベストアンサー
- 回答日時:
行数が不定のようなので作り直しました。
「=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
- 回答日時:
こんにちは
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させていただきます。有難うございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルでIF関数中にIFERROR関数を使いたいのですが???? 5 2022/04/08 13:24
- Excel(エクセル) エクセルVBAでセルに表示されているとおりの数値を取得したい(時間の計算結果) 1 2022/03/30 17:52
- Excel(エクセル) エクセルの関数式を教えてください。 2 2022/11/29 21:09
- Excel(エクセル) 関数EXACT(文字列,文字列)とexcelVBA 3 2022/04/14 15:07
- Excel(エクセル) エクセルで教えて下さい。 2 2022/05/18 13:00
- Excel(エクセル) エクセルで 例えばA1のセルに1+1 と入力すると B1のセルに2 と答えが出る関数があったと思いま 8 2022/12/16 20:57
- Excel(エクセル) Excelで数式をそのままコピーしたい どうすればいいですか? 4 2022/09/16 02:16
- Visual Basic(VBA) Excel VBA 最終行を取得しVlookup関数をコピーする方法をコーディングで教えてください。 3 2023/05/11 13:14
- Visual Basic(VBA) vbaエクセルマクロ RemoveDuplicatesについて RemoveDuplicatesを使 3 2023/02/28 01:13
- Excel(エクセル) 【再度】Excelの関数について教えてください。 4 2023/07/28 13:06
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
Microsoft1Officeの互換ソフト...
-
Excel ピボットテーブルで日付...
-
エクセル関数を教えてください
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
LOOKUP関数を使えばいいのでし...
-
エクセル 白黒印刷で白線を印刷...
-
【関数】先頭だけにある、半角...
-
【関数】適切な文字数の数字を...
-
Excelのチェックボックスの使い...
-
エクセルでの作業計算方法について
-
Excelのpivotについて質問です
-
WPS OFFICEでの縦書きについて
-
時間によってファイル名が変わ...
-
エクセルのセルに同じ大きさの...
-
Aというブックの1というシート...
-
エクセルの順位別一覧表の自動...
-
西暦や和暦の表示をyyyymmdd表...
-
【マクロ】エクセルにかいてあ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報