INDIRECT 横に再度抽出したい

Question

INDIRECT("'"&$A$2&"'!I2")
=INDIRECT("'"&$A$2&"'!I"&ROW(A2))
=INDIRECT(ADDRESS(ROW(I2),COLUMN(I2),,,$A$2)) だと縦、横どちらにも対応するか。
以上のように、お教え頂いたものをオートフィルできました。しかし、これを横にしてしたいと
思いやってみますが、どうしてもうまくできません。データは下記に示します。
 　  I J  K  L  M
4	13	6	7	12	9	10
4	14	14	15	20	17	18
4	15	22	23	28	25	26
4	16	30	31	36	33	34
4	17	38	39	44	41	42
このように、=INDIRECT(ADDRESS(ROW(I2),COLUMN(I2),,,$A$2)) で抽出しました。

縦の　I６～３８を横にしたい。その他のところも　J７～３９・K１２～４４・L９～４１・
M１０～４２も同じように縦を横に長く表示したい。また、抽出する別シートの元データは
上記のような配列になっている。この他に７０ページほどのデータがあるので、関数か
マクロでどうにかなりませんか。よろしくお願いします。

goomania · Accepted Answer

ご質問の内容が概ね明確になったので、数式を考えてみました。
表示用シートのC2セルに

=INDIRECT(ADDRESS(MOD(COLUMN(A1)-1,6)+2,INT((COLUMN(A1)-1)/6)+9,,,$A2))

という数式を記述し、下方向、右方向へオートフィルすれば、ご質問者のご希望に沿った結果になるのではないかと思います。

表示用シートC2は元シートのI2に対応するので元シートの「２行目かつ９列目」に対応することになります。
表示セルがC2から右方向へ１つ進むと、元シートのセルは下方向へ１つ進み、行数は「２、３、４、５、６、７」を繰返すことになります。
つまり、2～7を繰返す数式を作ればよいので、

MOD(COLUMN(A1)-1,6)+2

という数式を作成し、元シートの行数を算出したうえで、ADDRESS関数の行数としています。

さらに、元シートのデータは６個ワンブロックで、表示用シートにC2から右にデータが６個表示されると、元シートの次の列に進みます。
I2の列数は「９」で、初期値は「9」ですから、６データ毎に９から１つづつ増える数式を作ればよいので、

INT((COLUMN(A1)-1)/6)+9

という数式を作成し、元シートの列数を算出したうえで、ADDRESS関数の列数としています。
この数式は「表示用シートの表示位置のセルの列数が元シートの行数と列数に対応する」という点に着目したものです。

goomania · Answer

No.1です。
ご質問者の補足コメントの意味が理解できません。
その理由は元の表の内容を承知していないからです。
今回のご質問では元の表の内容については一切説明がなく、全くわかりません。
No.1の回答は「私の推測」に基づくものです。
普通なら複数の回答がつくであろう、このサイトの質問に私の回答しかついていないのはご質問の内容が把握できないからだと思います。

>=INDIRECT(ADDRESS(COLUMN($B$9),ROW($B$9),,,$A2))
>検索値を$A3～$A50にしたオートフィルでできます。

COLUMN($B$9),ROW($B$9)という表記は「絶対参照」であり、参照セルを固定したい場面で使用するものです。
ご質問者の場合、縦、横にオートフィルを使って元シートのデータを反映させる狙いだと推測していますので、参照セルを固定したら「元シートの一つのセルがオートフィル全体に表示されるだけ」ということになります。
それなのに、「$A3～$A50にしたオートフィルでできます」とおっしゃており、何ができているのかわかりません。
また、検索値とは何のことですか?上記の数式に検索値はありませんが･･･
数式末尾の,,,$A2のことをおっしゃっているなら「検索値」ではなく「シート名」の参照セルです。

>縦のデータj～M列も横にする場合は、その都度(COLUMN(B9),ROW(B9)
>の値を変更しなければ、行へのオートフィルが使えません。

そもそものご質問は

>縦のI6～38を横にしたい。その他のところも
>J7～39･K12～44･L9～41･M10～42も同じように縦を横に長く表示したい。

つまり、補足説明にある

>縦のデータj～M列も横にする場合は、

を想定して回答したものです。

それなのに、

>その都度(COLUMN(B9),ROW(B9)の値を
>変更しなければ、行へのオートフィルが使えません。

とおっしゃっており、回答者さんが何をやっているのかが不明です。

まず、お尋ねしたいのは以下の4点です。
(1)「=INDIRECT(ADDRESS(ROW(I2),COLUMN(I2),,,$A$2)) だと縦、横どちらにも対応するか。」という別シートから転記する数式の回答を得たようですが、上記数式は元シートの「どのセル」を表示用シートの「どのセル」に転記するという回答なのでしょうか?
また、この回答を得たご質問自体はどのサイトにあるのですか?URLを教えてください。
(2)ご質問で説明されているI列～M列の内容の左にある、縦に並んだ5個の「4」、「13～17」の数字はなにを意味しているのですか?
また、これはセルに表示されているものなのですか?
(3)ご質問で説明されている表の内容は「元の表の内容」なのか、「関数を使って表示させた結果」なのかどちらでしょうか?
(4)補足コメントにある
>４　別シートにある縦行のデータを横列に変更する。
>５　別シートにある縦行のデータを横列に変更する。
>６　別シートにある縦行のデータを横列に変更する。
>・
>・
>50
>こんな感じです。
とのご要望ですが、上記の説明で理解できる人はいないと思います。
頭にある数字は何を意味しているのですか?縦行のデータは「別シート」の「どこに」あり、横列に表示するのは表示用シートの「どこに」表示するのですか?

goomania · Answer

行番号と列番号を入れ換えるだけです。元の数式、

=INDIRECT(ADDRESS(ROW(I2),COLUMN(I2),,,$A$2))　は、

=INDIRECT(ADDRESS(ROW(2:2),COLUMN(I:I),,,$A$2))

と記述することもできます。
つまり、「2行目かつ9列目のセル」を意味しています。
これを「COLUMN関数」で「2【行】目」、「ROW関数」で「9【列】目」を指定すればよい訳ですから、

=INDIRECT(ADDRESS(COLUMN(B:B),ROW(9:9),,,$A$2))

となります。これを元の数式と同じ表記にすると

=INDIRECT(ADDRESS(COLUMN(B9),ROW(B9),,,$A$2))

となります。

INDIRECT 横に再度抽出したい

ご質問の内容が概ね明確になったので、数式を考えてみました。

No.1です。

行番号と列番号を入れ換えるだけです。

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング