EXCELのINDEX関数（数行ごと抽出）について

質問

たくさん検索をして、数行ごとの値を抽出するしくみはおよそ解りました。
（OFFSETやINDIRECT関数もふまえてどれが最適かピンときていない;;）
ひとまずINDEX関数を使ってみようかと思いましたが、
例えば「8行ごと抽出」の場合、
先頭がA1…でなく、数行飛ばして1つ目がＡ6にある時はどうすればよいでしょうか。
なかなか検索で出て来なくてエラーで困っています。よろしくお願いします。

回答No.5

kagakusuki · ベストアンサー

回答No.1、2です。

　後になってから考えてみましたが、

>最初に表示するB2セルにはA6セルの値を表示させたいのですから、行数は6のままでそこへ何も足したり引いたりする必要はありません。

という観点、即ち、「B2セルとの行数の差に基づいて参照先のA列のセルの行番号を変える」という観点から考えますと、B2セルに入力する関数には、

ROWS(B$2:B2)

を使うよりも

ROW()-ROW(B$2)

を使って次の様にした方が考え方として解りやすいかも知れません。

=IF(INDEX($A:$A,ROW($A$6)+(ROW()-ROW(B$2))*8)="","",INDEX($A:$A,ROW($A$6)+(ROW()-ROW(B$2))*8))

お礼

さらなる補足をありがとうございます！昨夜というか今朝の頭では数式によろめきそうでしたが、今噛み砕きまして確かにスマートな気はしました。
何を使ってどう組み合わせて解りやすく…って大切なポイントですよね。こだわりは私にもよくわかります。

しかし、そもそも私がINDEXにこだわったのでご苦労をおかけしまして;; そこにお付き合い頂いて、速攻で頂いた回答により速攻で仕事が10倍くらい進みました^^ というお礼を含める意味でベストとさせて頂きました☆

回答No.4

msMike

B2: =OFFSET(A$6,(ROW(A1)-1)*8,)

お礼

msMike さん、シンプルな回答に驚きを隠せません（汗;）やっと今試すことができました。
質問者の「INDEXでいく」にこだわらず、「目的」から行けば、これがベストな答えですね。。自分が試した時（OFFSETも）失敗したのは、別で触れている-1の意味が解らずいじってしまったのが大きな原因で、その結果OFFSETの優先順位を下げてしまったのが残念な脳みそでした( ；∀；)
（※目的からいうベストアンサーです、2つつけられないので何とぞお許し下さい～;;）

回答No.3

bunjii

>先頭がA1…でなく、数行飛ばして1つ目がＡ6にある時はどうすればよいでしょうか。
A列の値から目的の値が複数あるときのすべてをB列に抽出したいときの論理を考えれば良いのでしょうか？
例えば0から99までの値がランダムにA1セルからA20に入力されているものとしたとき、50以上の値を上から順に抽出するようなときは次のような数式で良いと思います。
B1=IF(COUNTIF(A$1:A$20,">=50")>=ROW(),INDEX(A:A,LARGE(INDEX((A$1:A$20>=50)*ROW(B$1:B$20),0),COUNTIF(A$1:A$20,">=50")-ROW()+1)),"")
添付画像はExcel 2013で検証した結果ですが他のバージョンでも再現できるはずです。

お礼

bunjii さん、なんと画像まで添付して下さって( ；∀；)恐縮です。先に頂いた回答を使ってもう8時間ほど作業をすすめておりました。
なるほど、これはこれでまた使える技ですね！！ネタ帳にメモりました。
いやー、関数の深さには終わりなき楽しさがあって、苦しいと楽しいの背中合わせです。すごいアイデアをありがとうございました^^♪

回答No.2

kagakusuki

>引数にある「-1」はどういう意味なのでしょうか？

　まず、

ROW($A$6)

の所でA6セルの行数である6が指定されます。
　最初に表示するB2セルにはA6セルの値を表示させたいのですから、行数は6のままでそこへ何も足したり引いたりする必要はありません。
　ところがROWS関数の値は1から始まっていますので、それを0から始まる値に変換するために

ROWS(B$2:B2)

から1を引いた形にしているわけです。
　その次のB3セルになるとROWS関数の所は

ROWS(B$2:B3)

となり、その結果は2になりますから、そこから1を引いた1に8を掛けた値である8が、

ROW($A$6)

の結果である6に加わる事で14となり、

INDEX($A:$A,

と組み合わせる事で、A列の14行目のセルであるA14セルの値を取得する事が出来るわけです。

お礼

なるほど…すごくよくわかりました。
趣味が多くて色んなデータを管理する上で関数や書式もたくさん使いましたが、なにせ初めてな関数で自分だけでは大きな壁でした。
回答いただいてから今もずーっとその続きをやっていました(笑)
一気に進んで感激です、ほんとうにご親切に丁寧にありがとうございました。

回答No.1

kagakusuki

御質問文の内容が今一つ説明不足なため、質問者様がやりたい事が不明瞭ですが、例えば
B2セルにA6セルの値を表示させ、
次のB3セルにはA6の8行下のA14セルの値を表示させ、
そのまた次のB4セルにはA14の8行下のA22セルの値を表示させ・・・
という形にしたいという事なのでしょうか？
　もしそれで宜しければ、B2セルに次の関数を入力してから、B2セルをコピーして、B3以下に貼り付けると良いと思います。

=IF(INDEX($A:$A,ROW($A$6)+(ROWS(B$2:B2)-1)*8)="","",INDEX($A:$A,ROW($A$6)+(ROWS(B$2:B2)-1)*8))

お礼

kagakusuki さん、速攻でお返事ありがとうございます、まさにコレです！！感激で、貴方様が神様に見えます( ；∀；)

はぁ…IFをかませるなんて、思いつきもしませんでした。未熟者です。

あの、この3日考えてどうにもわからない点を1つ教えて下さいませんか？
引数にある「-1」はどういう意味なのでしょうか？

例えば参照した最初の位置から上下に-2とか-3とか+5とか変化するのかと思いきや、検索するとみんな-1なので、理解不能でおります。
（これでも、そこそこのことは解るので専門用語でもOKですm(__)m）