任意の複数のセルから文字を抽出する法

質問

エクセルの勉強中です。

質問があります。
エクセルには縦の列と横の行があるようですが、
その任意のセルに入った文字列を抽出することは可能でしょうか。
例えばB列に入った５行目、８行目、１１行目、１４行目といったようにある配列に並んだセル内の文字を別の新規のD列に順番にD1、D2、D3、D4のセルに並べていくという方法などです。
同じファイルでなくても別の任意のファイル内の指定の列に順に入れていくといった作業などです。

同様にC列に入った４行目、８行目、１２行目、１６行目内のセル内の文字を今度は
新規のE列のE1,E2,E3,E4に順に並べていくなど、上記のような作業を一度に行えればそのような方法があるのか、お知りになる方よろしくお願いします。
その目的の列ごとに行っていく方法でもかまいません。

回答No.1

MackyNo1 · ベストアンサー

>例えばB列に入った５行目、８行目、１１行目、１４行目といったようにある配列に並んだセル内の文字を別の新規のD列に順番にD1、D2、D3、D4のセルに並べていくという方法

飛ばして表示するセルに規則性があるなら、いろいろなアプローチがありますが、以下のような文字列数式を利用した方法が直感的でわかりよいかもしれません。

D1セルに「'=B5」、D2セルに「'=B8」と数式の前にアポストロフィー「'」を入力した数式を入力し、この2つのセルを選択して、下方向にオートフィルコピーして、そのまま「データ」「区切り位置」で「完了」してください。

通常の関数で対応する場合は、以下のようなINDIRECT関数で表示することができます。

=INDIRECT("B"&(2+3*ROW(1:1)))

ただし上記のINDIRECT関数は他ブックを参照するときは、そのブックを開いていないとエラーになりますので注意してください。

他ブックを参照する場合は、以下のようなOFFSET関数を利用することができます。

=OFFSET($B$2,ROW(1:1)*3,0)

お礼

早い回答にて感謝します。

今は、試すことができない環境にあるのですが、
早速試してみます。

ありがとうございます。!!

回答No.6

bunjii

>他のファイルから抽出する方法があればその、抽出元のファイルもしくはsheet名を関数に入れ込むことにより解決するのでしょうか。
はい、ファイルが同時に開かれている場合はファイル名とシート名をセルのアドレスに付加して参照させることになります。
ファイルが閉じた状態ではファイル名を保存先のフォルダーのフルパスを付加したものにしないと対象セルが見付からないことになります。

同時に開いているファイルの場合は =[Book2.xls]Sheet1!A1 のような形式です。
閉じたファイルの場合は次のようになります。
='C:\Users\Bunjii\Documents\[Sheet2.xls]Sheet1'!A1

お礼

ありがとうございます。

どなたの回答もほんとによいものばかりで
実際どれをベストアンサーとするのは難しく、
実際、どの方法にて作業を行っていくのがいいのかまだ決めかねているところです。

他のファイルからの抽出は他の展開や利用が色々考えれて、広がりを感じさせてくれます。

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

回答No.5

MackyNo1

上記を実行したあと、必要行まで上記の関数がコピーされるのみなのですが、

同じ数式になりますがROW関数の値がセルによって異なるので問題ありません。
ただし、ROW関数のカッコ内に何も入力しないと、そのセルの行番号を取得しますので、入力するセルが変わると、返す値が異なることになります（D1セルに入力すれば「1」が返りますが、D3セルに入力した場合は「3」が返ります）。

したがって1行目以外のどのセルに入力してもB5セルからの移動量を0、3、6・・・としたいなら、すでに回答したようにROW(1:1)またはROW(A1)のような数式にすることをお勧めします。

=OFFSET($B$5,(ROW(1:1)-1)*3,0)

このような数式にしておけば、どのセルに入力しても（この数式のROW関数で返す値は必ず1なので）同じ数式で対応できるだけでなく、行が変わると数式も変化しますので、数式が直感的に理解しやすいかもしれません。

回答No.4

bunjii

>上記を実行したあと、必要行まで上記の関数がコピーされるのみなのですが、そのあと、どうしたら実行されるのでしょうか。
？？？？
もしかしてD1セルに D1=OFFSET($B$5,(ROW()-1)*3,0) と入力しましたか？
数式バーにはD1を除いた =OFFSET($B$5,(ROW()-1)*3,0) と入力するのがExcelの決まりごとになっています。
添付の画像を参考にしてください。

お礼

ありがとうございます。

=

の記号が抜けて入力した結果によるものでした。

あと、No1さんが書いているように他ブックというのでしょうか。
他のファイルから抽出する方法があればその、抽出元のファイルもしくはsheet名を関数に入れ込むことにより解決するのでしょうか。

No1さんの書いている関数でも挑戦してみたのですが、同じファイル内ですと、
みなさんのどれもの関数がうまく働くことが出来、どれも使用できそうだと認識しました。

が他ファイルからの抽出方法が出来ることが可能なのでしょうか。

回答No.3

bunjii

>例えばB列に入った５行目、８行目、１１行目、１４行目といったようにある配列に並んだセル内の文字を別の新規のD列に順番にD1、D2、D3、D4のセルに並べていくという方法などです。
提示の条件では初期値がB5で3行飛びの値と言う規則性を利用して次の式で良いでしょう。

D1=OFFSET($B$5,(ROW()-1)*3,0)
D1を下へオートフィルで必要行までコピーします。

>同様にC列に入った４行目、８行目、１２行目、１６行目内のセル内の文字を今度は新規のE列のE1,E2,E3,E4に順に並べていくなど
この条件も前項と同様に初期値がC4で4行飛びの値と言う規則性がありますので前項の式を変形して次の式が使えます。

E1=OFFSET($C$4,(ROW()-1)*4,0)

飛び飛びの規則性が無い場合は参照する列を使うことで抽出できますので条件を整えて再質問してください。

補足

ほんとにエクセルのことが自分自身わかってないのですが。

D1=OFFSET($B$5,(ROW()-1)*3,0)
D1を下へオートフィルで必要行までコピーします。

上記を実行したあと、必要行まで上記の関数がコピーされるのみなのですが、
そのあと、どうしたら実行されるのでしょうか。

回答No.2

mshr1962

>例えばB列に入った５行目、８行目、１１行目、１４行目といったようにある配列に並んだセル内の文字を別の新規のD列に順>番にD1、D2、D3、D4のセルに並べていくという方法などです。
D1=INDEX($B:$B,ROW()*3+2)
とか
D1=OFFSET($B$2,ROW()*3)
とか
D1=INDIRECT("B"&ROW()*3+2)
で下方にコピー

>同様にC列に入った４行目、８行目、１２行目、１６行目内のセル内の文字を今度は
>新規のE列のE1,E2,E3,E4に順に並べていくなど

E1=INDEX($C:$C,ROW()*4)
とか
E1=OFFSET($C$1,ROW()*4-1)
とか
E1=INDIRECT("C"&ROW()*4)
で下方にコピー

※もし、参照元のセルが空白の場合に0が表示されるのが駄目なら、上記の式の最後に　&""　としてください。

お礼

早い回答にて感謝します。

今は、試すことができない環境にあるのですが、
早速試してみます。

ありがとうございます。!!