エクセルでの列データ操作について

質問

例として、A1からA1000まで数値データがあったとします。そこからA1のデータをB1にA5のデータをB2へA10のデータをB3といったようにA列のデータを３とか５といったように任意の数で等間隔のデータをB列に順に貼り付けたいのですが、何か良い方法がありますか？出来れば関数でまかなえれば大変助かります。　マクロに関しては知識が乏しいので厳しいです。ポイントとして、任意の等間隔（数式の中で数値を入れ替えれる）で間隔を変更出来る様にしたいです。　ご教授よろしくお願いします。

回答No.6

esupuresso · ベストアンサー

＞A1のデータをB1に・・・　←セルB1に「=B1」の数式を入力
OFFSET関数を使用してセルB2セルから下へ「=OFFSET($A$1,ROW(A1)*5,0,1)」の式を入れますが、この式ですとA列のセルが1000行以下未入力の場合、「0」を返してきますのでIF関数を使って以下の式をセルB2から下に入れます
=IF(OFFSET($A$1,ROW(A1)*5,0,1)=0,"",OFFSET($A$1,ROW(A1)*5,0,1))
　　↑この数式をコピーしてB2に貼り付け、後は必要な行数分だけ数式のコピーをします
「ROW(A1)*5」←この部分の5は5行分を意味しますので3行おきにセル番地を返したい場合は「ROW(A1)*3」と数式内の数値の部分（IF関数を使ってますので2箇所）を編集します

もう少し簡単な方法として空きセル（例えばD２セルなど・・・）間隔を開けたい分の数値を入力しておいて次の式をB２以下に入れます
=IF(OFFSET($A$1,ROW(A1)*$D$2,0,1)=0,"",OFFSET($A$1,ROW(A1)*$D$2,0,1))の式を使い、セルD2に行数分の数値を手入力します（例えば３とか５とか７とか・・・）
「３」と入力すると3行間隔の数値を返します　「５」と入力すると5行間隔の数値を返してきます

回答No.5

keithin

んーと？
＞A列のA１、A６、A１１のような等間隔の数値データをA1はB1にA６はB2にA1１はB3にといった感じです。
＞その等間隔は後で数値を入れ替える事でA列の必要な間隔を変更できるようにしたいのです。よろしくお願いします。

それは補足にあらず最初のご質問で書かれて内容の繰り返しなので，それならANo.1で回答したまんまのやり方で出来ます。
実際に手を動かしてエクセル上に実施してみての「よろしく」なのですか？

もう一度お話ししますので，次の通りに今度は実際に手を動かして試してみてください。
　Ｂ１セルに，ANo.1で回答した式を入れます。
　Ｂ１セルを下向けにオートフィルドラッグでコピーします。
　Ｅ１セルに，必要な間隔の数字を記入します。

Ｅ１セルを使いたくないときは
=INDEX(A:A,(ROW(B1)-1)*$E$1+1)
のE1の所に３や５を入れて
=INDEX(A:A,(ROW(B1)-1)*5+1)
のようにして，オートフィルドラッグします。

良く読んで気をつけて欲しかったのは，結果の式を「Ｂ１」じゃなく例えば「Ｃ２から下に配置したい」とか，元のデータ並びが「Ａ１開始」じゃなく実は「Ａ２開始」だった場合は，回答の通りの式では出来ないので調整が必要だと言うことです。

＃念のため補足
「Ａ１」の次が「Ａ５」で次が「Ａ１０」では『等間隔』では無いというのは，単なるご相談の書き間違いとしてスルーしました。

回答No.4

MackyNo1

＞等間隔というのはA列のセルが等間隔ということですが（データはA1からA1000くらいまであります）、上記数式でよいでしょうか？

ご希望のデータが表示できる数式を提示しています。

例えばD1セルに間隔(例えば5)、F1セルに開始行番号(例えば1)が入力されているなら、以下のような式になります。

=INDIRECT("A"&ROW(A1)*$D$1-$D$1+$F$1)

実際にセルに入力して下方向にオートフィルコピーしてみてください。

データがない部分は「０」と表示されますので、これを表示したくないならIF関数で空白表示するか、返す値が文字列なら以下のように数式の後に「&""」を付けてください。

=IF(元の式=0,"",元の式）
=INDIRECT("A"&ROW(A1)*5-4)&""

回答No.3

MackyNo1

＞A列のA１、A６、A１１のような等間隔の数値データをA1はB1にA６はB2にA1１はB3にといった感じです。　その等間隔は後で数値を入れ替える事でA列の必要な間隔を変更できるようにしたいのです。

A列の特定のセルから一定間隔でデータを表示したいならINDIRECT関数を使う以下の式がわかりよいかもしれません。

=INDIRECT("A"&ROW(A1)*5-4)

上記の数式の「５」が間隔で、「-4」の部分はA列の一番上のセル位置を調整する数字で、A1セルなら「５－４」ですので「-4」となりA10セルが開始位置なら「５＋５」で「+5」になります。

補足

私の理解不足もあるかと思いますが、等間隔というのはA列のセルが等間隔ということですが（データはA1からA1000くらいまであります）、上記数式でよいでしょうか？あと、上記数式はオートフィルでB1セルからB2　B3と連続で貼り付けは出来るのでしょうか？A列のデータが１０００程度ありますので、オートフィルが使えないと大変かと。。　あとで数値を入れ替えるというのは、A列の取り出したいセルの間隔の事です。例えば３の倍数のみとか５の倍数のセルとかですが、足し算で１　６　１１　１６番目のセルデータの取り出し等等。　何度も申し訳ありません。　お礼が明日になったらすみません。

回答No.2

MackyNo1

ROW関数を組み合わせればいろいろな数式ができますが、最も直感的でわかりよい操作は、B1セルに「'=A1」B2セルに「'=A5」と1つ目のセルと2つ目のセルを参照する数式を最初に「’」を付けて入力し、この2つのセルをオートフィルコピーして、最後にこの列を選択して「データ」「区切り位置」で「完了」します。

あるいはB1セルに「=A1」と入力し、A1:A4セルを選択して下方向にオートフィルコピーしてご希望の数式を飛びとびに入力しておき、Ctrl+Gのショートカットでジャンプダイアログを出し、「セル選択」から「空白セル」にチェックを入れ、そのまま右クリックから「削除」で「上方向にシフト」する方法も簡単かもしれません。

回答No.1

keithin

INDEX,INDIRECT,OFFSETなどの関数とROW関数を組み合わせて，大概の事ができます。

計算例：
E1セルに３とか５とかを入れて（勿論数式中に直接数字を記入して作成しても良い）
B1:
=INDEX(A:A,(ROW(B1)-1)*$E$1+1)
を下にコピー

実際には
＞そこからA1のデータをB1に

「どこ番地のセルの値」を「どこのセルに書き出させたい」のかによって，細かい数式のチューニングが変わります。皆さんから教わった式の計算手順を追えなかった時は，正確に今実際にあなたがやりたい「どこのセルにどこのセルを」の正しい情報を添えて改めてご相談を投稿し直してください。

補足

お世話になります。追記します。A列のA１、A６、A１１のような等間隔の数値データをA1はB1にA６はB2にA1１はB3にといった感じです。　その等間隔は後で数値を入れ替える事でA列の必要な間隔を変更できるようにしたいのです。よろしくお願いします。