エクセルで列番号に法則性がある場合の連続コピペの仕方

エクセルで表を作成していて困っているのですが、

A1,C1,E1・・・と、列番号が1つ飛びのセルの数字を
B1,B2,B3・・・に順番に貼り付けていきたいのです。

数式でB1=A1、B2=C1と一つずつなら書き込むことで
可能ではありますが、同じ数式で貼り付ける等の
上手い方法があれば教えてください。

No.7 ベストアンサー 回答者： chie65536 回答日時： 2008/05/23 14:38

訂正の訂正。

=INDIRECT("Sheet1!"&ADDRESS(1,ROW()*5-18))
ではなく
=INDIRECT("Sheet1!"&ADDRESS(4,ROW()*5-18))
です。

この回答へのお礼

丁寧な御説明ありがとうございます。
ようやく数式が合致しました！！
chie65536さん、ありがとうございました！！

お礼日時：2008/05/23 18:50

No.6 回答者： chie65536 回答日時： 2008/05/23 14:35

訂正。

ROW()*5-3
ではなく
ROW()*5-18
です。同様に
=INDIRECT("Sheet1!"&ADDRESS(1,ROW()*5-2))
ではなく
=INDIRECT("Sheet1!"&ADDRESS(1,ROW()*5-18))
です。

なお
=INDIRECT("Sheet1!"&ADDRESS(1,ROW()*5-18))
は「１～３行目には置けない」です。「ROW()*5-18」は「１～３行目に置くと、負の数になる」のでエラーになります。

No.5 回答者： chie65536 回答日時： 2008/05/23 14:31

=INDIRECT(ADDRESS(1,ROW()*2-1))

について。

ROW()
は「それが書いてある場所の行番号」です。
１行目に書けば「１」
２行目に書けば「２」
３行目に書けば「３」
です。

ROW()*2
は「それを２倍」です。
１行目に書けば「２」
２行目に書けば「４」
３行目に書けば「６」
です。

ROW()*2-1
は「そこから１を引く」です。
１行目に書けば「１」
２行目に書けば「３」
３行目に書けば「５」
です。

ADDRESS(行番号,列番号)
は「指定した行、列の、セル参照文字列を作る」です。
１行目の１列目を指定すると「$A$1」
１行目の３列目を指定すると「$C$1」
１行目の５列目を指定すると「$E$1」
です。

従って
ADDRESS(1,ROW()*2-1)
は「それが書いてある場所の行に従って、セルの列が変化する文字列を作る」です。
１行目に書けば「$A$1」
２行目に書けば「$C$1」
３行目に書けば「$E$1」
です。

INDIRECT(文字列)
は「文字列で指定した位置にあるセルの内容を返す」です。
文字列に$A$1を指定すれば「A1セルの中身」
文字列に$C$1を指定すれば「C1セルの中身」
文字列に$E$1を指定すれば「E1セルの中身」
です。

従って
INDIRECT(ADDRESS(1,ROW()*2-1))
は「それが書いてある場所の行に従って、列を変化させて、指定した位置にあるセルの内容を返す」です。
１行目に書けば「A1セルの中身」
２行目に書けば「C1セルの中身」
３行目に書けば「E1セルの中身」
です。

＞B4,G4,L4・・・と、列番号が5列ずつで、
＞別シートのB4,B5,B6・・・へ順位貼り付けたいです。
であれば
４行目にあれば２（Ｂ列の列番号は２）
５行目にあれば７（Ｇ列の列番号は７）
６行目にあれば１２（Ｌ列の列番号は１２）
ですから
ROW()*2-1
ではなく
ROW()*5-3
です。そして、参照するのは別シートになりますから「$B$4」ではなく「Sheet1!$B$4」などのように、シート名も付けなくてはなりません。

参照元シート（Ｂ４、Ｇ４、Ｌ４があるシート）のシート名が「Sheet1」の場合は、
=INDIRECT("Sheet1!"&ADDRESS(1,ROW()*5-2))
を、別シートのＢ４、Ｂ５、Ｂ６へ貼りつけて下さい。

No.4 回答者： mu2011 回答日時： 2008/05/23 14:06

NO3です。

>B4,G4,L4・・・と、列番号が5列ずつで
⇒こんな感じでしょうか。
　=INDEX(別シート!$4:$4,1,(ROW(A1)-2+ROW(A1))*2+ROW(A1)+1)

この回答への補足

申し訳ありません。
エラーになってしまいます。

補足日時：2008/05/23 17:59

この回答へのお礼

私のために時間を割いてくださり、
誠にありがとうございました！！
お陰様で解決しました！！

お礼日時：2008/05/23 18:51

No.3 回答者： mu2011 回答日時： 2008/05/23 13:02

NO2です。

>実際はA1,F1,K1・・・と5列ずつ
⇒最初から５列と記載すべきではないでしょうか。
=INDEX($1:$1,1,(ROW(A1)-2+ROW(A1))*2+ROW(A1))

この回答への補足

やはり、実際が違うためか、稼動しませんでした。
ですので、実際の配列を表記させていただきます。

B4,G4,L4・・・と、列番号が5列ずつで、
別シートのB4,B5,B6・・・へ順位貼り付けたいです。

何度も申し訳ありません、御検討願います。

補足日時：2008/05/23 13:32

この回答へのお礼

mu2011さん、お手数掛けまして申し訳ありません。
これほどの複雑な数式だと想定しておりませんでしたので。
ただ今、自分の表で数式が稼動するか実施中です。

お礼日時：2008/05/23 13:28

No.2 回答者： mu2011 回答日時： 2008/05/23 11:23

一例です。

B1に=INDEX($1:$1,0,ROW(A1)+ROW(A1)-1)を設定、下方向にコピーで如何でしょうか。

この回答へのお礼

素早い対応ありがとうございます。
質問では分かりやすくA1,C1,E1と例を挙げましたが、
実際の私の表に当てはめようとしても
上手くいかないのです。。。
実際はA1,F1,K1・・・と5列ずつなので、どのように
その数式が変換されていくのですか？？

お礼日時：2008/05/23 12:26

No.1 回答者： chie65536 回答日時： 2008/05/23 11:22

Ｂ１に

=INDIRECT(ADDRESS(1,ROW()*2-1))
の式を書いて、下方向のＢ２、Ｂ３、Ｂ４～に必要なだけセルのコピー。

この回答へのお礼

素早い対応ありがとうございます。
質問では分かりやすくA1,C1,E1と例を挙げましたが、
実際の私の表に当てはめようとしても
上手くいかないのです。。。
INDIRECTやADDRESS、ROWなど、意味や根拠などを
付けて頂けるとありがたいです。

お礼日時：2008/05/23 12:24