[[20090827114410]]　『row関数でセル指定』（クロすけ）　ページの最後に飛ぶ

『row関数でセル指定』（クロすけ）

 こんにちは。
 我ながら情けない質問ですが、以下の場合の書き方を教えていただけないでしょうか。

 例えば
 =VLOOKUP("aaa",A1:A3,1,FALSE)
 の「A1」の「1」の部分をrow関数の戻り値で指定したいのですが、
 =VLOOKUP("aaa","A" & row() & ":A3",1,FALSE)
 では「#VALUE!」エラーです。(引数が違うんですね。はい･･･)

 どうぞ、よろしくお願いします。

 INDIRECT関数を使って下さい。

 「数式をフィルドラッグして使おう」と思って居られるなら
 相対参照にしておくのでも良いのではないかと思います。

 1行目に入れるなら。
 =VLOOKUP("aaa",$A1:$A$3,1,FALSE)
                  ~「1」の前は $ をつけない。

 「突然どのセルに貼り付けても自行以降の範囲を参照する様ﾉ」
 と言う事なら、やはり INDIRECT関数と
 組み合わせて使うのが良いと思います。

 (HANA)

 "A" & row() & ":A3"
この部分の結果は "A1:A3" という文字列であって、A1:A3　で得られるセル参照とは
違った意味になります。セルアドレスを表現する文字列を元にセル参照を行う関数と
してINDIRECT関数があります。
 =VLOOKUP("aaa",INDIRECT("A" & row() & ":A3"),1,FALSE)
　
タイピングはHANAさんに軍配が上がるようです。
(みやほりん)(-_∂)b

 =VLOOKUP("aaa",A1:A3,1,FALSE)

 質問のために無理やりつくった数式なのでしょうか？

  「本当にやりたいこと」「実際の数式」で質問したほうが
 解決が早いように思います。

 （えくせる青年）2009/8/27 12:15

 >=VLOOKUP("aaa",A1:A3,1,FALSE)
 提示された式に、何となく違和感を覚えますが・・・

 参考までに
 =VLOOKUP("aaa",INDEX(A:A,ROW()):B10,2,0)
 =VLOOKUP("aaa",INDEX(A:A,ROW()):$B$10,2,0)

 因みに、質問で提示されている式で　セル範囲後段が　
 絶対参照か相対参照なのかが不明ですね

 INDIRECT　関数では後半の A3 固定されますが　?

 By しげちゃん

 沢山のレスをありがとうございます！

 「#VALUE!」エラーが出ている事で、文字列にするの？row()の戻り値はセル形式？
 となっていました。（TEXT関数試してみたり･･･）
 INDIRECT関数を、しげちゃんさんの忠告を気にしつつ試してみます。

 えくせる青年さん、しげちゃんさんの仰るとおり、質問の為の簡易式でしたので
 実際にやりたい事に当てはめ、うまくいかないようでしたら
 サンプル・目的等含めて改めて質問させていただきます。

 ご回答いただいた皆様ありがとうございます。とりいそぎ、お礼まで。
 試した結果はご報告したいと思います。（クロすけ）

 試してみました！INDIRECT関数でやりたい事が実現できました。
 今更、質問の式と全く違うものを出すのも気が引けますが、
 質問の式がアレ過ぎたので、ご報告も兼ねてやりたかったことを記しておきます。
 いつもこちらのサイトを拝見しながら、質問の仕方も大事だよな～
 と身に沁みていたはずなのに･･･

 *やりたかったこと
 固定長のデータを、区切りデータに変換する。

 sheet1　→元データ
 	[A]
 [1]	236800001くろすけズマンション　　　　　　　　　　東京都･･･
 [2]

 sheet2 →　区切り位置テーブル
 	[A]	[B]
 [1]	開始	バイト数
 [2]	1	1
 [3]	2	8
 [4]	10	20

 sheet3　→　取り出し先
 	[A]	[B]	[C]
 [1]	2	36800000	くろすけズマンション　　　　　　　　
 [2]			
 [3]

 A1に
 =LEFT(Sheet1!$A1,Sheet2!$B$2)
 B1に
 =MID(Sheet1!$A1,INDIRECT("Sheet2!A" & COLUMN()+1),INDIRECT("Sheet2!B" & COLUMN()+1))
 B1を必要分横にフィルドラッグ
 1行目を必要分下にフィルドラッグ

 で、取り出せました。
 INDIRECT関数と、質問の仕方をマスターしたいと思います。
 皆様ありがとうございました！(クロすけ)

本題は解決なんですが、しげちゃんさんご提示の式が理解できないので伺ってもよろしいでしょうか？

 レイアウトを以下にして
 	[A]	[B]
 [1]	aaa	
 [2]	bbb	
 [3]	ccc

 B1にご提示いただいた
 =VLOOKUP("aaa",INDEX(A:A,ROW()):B10,2,0)　や
 =VLOOKUP("aaa",INDEX(A:A,ROW()):$B$10,2,0)
 を入れてみましたが「数式を計算できません・・・循環参照を形成しています」とエラーが出ます。

 INDEX関数を調べたところ、B1に
 =INDEX(A:A,ROW())
 と入れると自分の行番号と同じ行のA列の値をひっぱってくるようです。(この場合、aaa)
 =VLOOKUP("aaa",INDEX(A:A,ROW()):B10,2,0)
 のINDEX部分をF9してみると
 =VLOOKUP("aaa","aaa":B10,2,0)
 と、見慣れない記述になるのですが、これはどのように使用したら良いんでしょうか。

 そもそも、私が何か勘違いしていると思うのでご指摘いただけると
 今後の勉強になるので、どうぞよろしくお願いいたします。(クロすけ)

 これって、
 Sheet3の
 　1列目は Sheet2のA2からB2文字数分
 　2列目は Sheet2のA3からB3文字数分
 　3列目は Sheet2のA4からB4文字数分
 が表示されれば良い

 って事ですか？

 でしたら、INDIRECT関数よりもINDEX関数の方が
 良いかもしれません。

 例えば、Sheet3の
 A2
=INDEX(Sheet2!$A$2:$A$4,COLUMN())
 A3
=INDEX(Sheet2!$B$2:$B$4,COLUMN())
 として横へフィルコピーすると
   1   2   10
   1   8   20
 の様な結果が得られます。

 (HANA)

 いえ、取り出したいのはsheet1のA列の文字列です。

 1行目でいうと、sheet1のA1に
 「236800001くろすけズマンション　　　　　　　　　　東京都･･･」
 という文字列が入っているので、これをsheet3に区切りながら取り出したいという事です。
 区切る為の指定位置が、sheet2に入っている数字になります。

 sheet3のA1は
 「sheet1のA1の文字を、左1文字目から1文字取り出したもの」
 →　2
 B1は
 「sheet1のA1の文字を、左2文字目から8文字取り出したもの」
 →　36800000
 C1は
 「sheet1のA1の文字を、左10文字目から20文字取り出したもの」
 →　くろすけズマンション　　(←空白あり)

 です。(クロすけ)
 ※セル位置がぐちゃぐちゃになってしまったので、3：42修正

 ＞いえ、取り出したいのはsheet1のA列の文字列です。
 えっと、そう思っていますが。。。
 書き方が悪かったですか？

 つまり、
 　　Sheet3の1列目は Sheet1のA1の文字列の内 Sheet2のA2(1)からB2(1)文字数分
 　　Sheet3の2列目は Sheet1のA1の文字列の内 Sheet2のA3(2)からB3(8)文字数分
 　　Sheet3の3列目は Sheet1のA1の文字列の内 Sheet2のA4(10)からB4(20)文字数分
 　が表示されれば良い
 ですよね？

 A列の値を順に取り出すのが　=INDEX(Sheet2!$A$2:$A$4,COLUMN())
 　横にフィルドラッグする事で、順に 1,2,10 の値が得られます。
 B列の値を順に取り出すのが　=INDEX(Sheet2!$A$2:$A$4,COLUMN())
 　横にフィルドラッグする事で、順に 1,8,20 の値が得られます。

 ですから、MID関数に組み込んでいただければ良いと思うのですが？

 (HANA)

 HANAさん、すみません。MID関数に組み込むの意図を汲み取れていませんでした。
 sheet3に
 =MID(Sheet1!$A1,INDEX(Sheet2!$A$2:$A$4,COLUMN()),INDEX(Sheet2!$B$2:$B$4,COLUMN()))
 を入れて、取り出すことが出来ました。
 INDEX関数もマスター出来るように復習しておきます！（クロすけ）

 ＞INDIRECT関数よりもINDEX関数の方が良いかもしれません。
 と書いた理由を一つだけ上げておきます。

 B1セルにINDIRECT関数を使用した式
 B2セルにINDEX関数を使用した式
 を入れて下さい。

 まず、B1セルをアクティブにして 数式バーの隣の[ fx ]を押します。
 すると
 　－MID-－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
         文字列 [ Sheet1!$A1                     ] = "236800001くろすけ
       開始位置 [ INDIRECT("Sheet2!A"&COLUMN()+1 ] = 可変
         文字数 [ INDIRECT("Sheet2!B"&COLUMN()+1 ] = 可変
   －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
                                                   = 可変
   －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
   数式の結果 =    可変

 と表示されます。

 次に、B2セルをアクティブにして 数式バーの隣の[ fx ]を押すと
 　－MID-－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
         文字列 [ Sheet1!$A1                     ] = "236800001くろすけ
       開始位置 [ INDEX(Sheet2!$A$2:$A$4,COLUMN( ] = 2
         文字数 [ INDEX(Sheet2!$B$2:$B$4,COLUMN( ] = 8
   －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
                                                   = "36800001"
   －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
   数式の結果 =    36800001

 と表示されますね？

 数式が希望する結果を返してくれないときに　原因を探す事になるのですが
 下の様に表示されていたら、どこで思いがけない事が起きているのか
 見つけやすいと思います。

 質問の時は質問文だけを書くのではなく
 それで何をやりたいのかも　一緒に書いておかれるのが
 良いと思います。

 「○○をするために　××が出来れば良いと考えたので
 　××をする方法を知りたい」
 と言った感じで。

 すると、「いや、××より、△△ってやった方が良いよ」
 って話につながる可能性が出てくると思います。

 因みに、Sheet2のA列を挿入して
 Sheet2	[A]	[B]	[C]
[1]	列	開始	バイト数
[2]	1	1	1
[3]	2	2	8
[4]	3	10	20
 の様に、どの列番号の時に参照したいデータなのか書いておくと
 Sheet3のA1に、VLOOKUP関数を使って
=MID(Sheet1!$A1,VLOOKUP(COLUMN(),Sheet2!$A$2:$C$4,2,FALSE),VLOOKUP(COLUMN(),Sheet2!$A$2:$C$4,3,FALSE))
 こんな感じで出来ますね。

 (HANA)

 HANAさん、ご説明いただきありがとうございます。

 leftやvlookupなど決まった関数や、サンプルそのままをアドレス替えてしか
 使ってこなかったので勉強になりました。
 質問の仕方も肝に銘じます。

 VLOOKUPを使用した方法など、工夫の幅は本当に広いですね～

 INDEX関数などを調べた結果、しげちゃんさんご提示の式の使い方も理解できました。

	[A]	[B]	[C]
[1]	aaa	1	ここに「=VLOOKUP("aaa",INDEX(A:A,ROW()):B10,2,0)」
[2]	bbb	2	
[3]	ccc	3

 ですね。
 これもサンプルを載せずに適当な式で質問したせいで、
 自分でわからなくなったという反省すべき内容でした。大変失礼しました。
 レスを下さった皆様、本当にありがとうございました。(クロすけ)

コメント返信：

[ 一覧(最新更新順) ]

[[20090827114410]] 『row関数でセル指定』（クロすけ） ページの最後に飛ぶ

[[20090827114410]]　『row関数でセル指定』（クロすけ）　ページの最後に飛ぶ