エクセルでこのような事ができますか？

質問

エクセルでマクロとか使わずに簡単に以下の作業ができる方法がありますか？

　　　　　A　　　　　　B
1　　　田中
2　　　山本
3　　　中村
4　　　岡田
5　　　鈴木
　　　　　↓
　　A列にずっと1000人ぐらいの名前がある。
　　このシートをもとに下のような１から６の番号をB列につけたシートを
　　作りたいのですが・・・

　　　　　A　　　　　　B
1　　　田中　　　　　1
2　　　田中　　　　　2
3　　　田中　　　　　3
4　　　田中　　　　　4
5　　　田中　　　　　5
6　　　田中　　　　　6
7　　　山本　　　　　1
8　　　山本　　　　　2
9　　　山本　　　　　3
10　　山本　　　　　4
11　　山本　　　　　5
12　　山本　　　　　6
13　　中村　　　　　1
14　　中村　　　　　2
　　　　　↓
　　　6000行まで入ることになります。

回答No.5

mshr1962 · ベストアンサー

#4のmshr1962です。慌ててて数式を間違えました。
A1=OFFSET(Sheet1!$A$1,QUOTIENT(ROW()-1,6),0)
です。
FLOORの場合は、6行毎のデータ(1,7,13...)です。

お礼

ご回答ありがとうございます。

Sheet2のA1に教えていただいように入力してみました。
出力は　＃NAME?　でした。
Sheet2のA1セルのコピペです。あってますよね？
　　　↓
=OFFSET(Sheet1!$A$1,QUOTIENT(ROW()-1,6),0)

ある列を参照して6回づつ同じ名前を縦に入れていくのは不可能のような気が
してきたのですが・・・。

補足

会社のエクセル(2003)で教えていただいたとおりやったらできました。
エクセル2002（自宅）にはQUOTIENTという関数がありませんでした。

回答No.7

pbforce

名前の左の数字はセルに入力されていないのですね？

私の回答は名前の左の数字がセルに入力されている場合の回答です。

ですので【名前】の範囲は【1】のセルから【鈴木】のセルとなります。

お礼

再度ご回答ありがとうございます。

＞名前の左の数字はセルに入力されていないのですね？

はい、行番号です。

pbforceさんが想定されたようにA列に1から5を入れて、名前をB列にずらした
シートにしてから再度シート２のA1に下記式をいれましたが、出力は＃N/Aでした・・・。
　　↓
=VLOOKUP(ROUNDUP(A2/6,0),Sheet1!A1:B5,2)

補足

シート２のA列にも１から数字を並べた後にB1に教えていただいたように
＝VLOOKUP(ROUNDUP(A2/6,0),Sheet1!$A$1:$B$5,2)をいれたらできました。
何度もありがとうございました。
A列に１～の数字を入れなくてもできる方法を２名の方に教えていただきましたので
（A列に数字を入れる操作はたいした作業ではありませんが・・・）申し訳ありませんが
その方にポイントを差し上げたいと思いますのでご容赦下さい。

回答No.6

imogasi

名前の列
=INDIRECT("A"&INT((ROW()-1)/6)+1)
番号の列
=MOD(ROW()-1,6)+1
でできるようです。

お礼

ご回答ありがとうございます。

教えていただいた式をC列にいれました・・・できました。

式の意味は・・・後で考えます。

回答No.4

mshr1962

A1=OFFSET(Sheet1!$A$1,FLOOR(ROW()-1,6),0)
B1=MOD(ROW()-1,6)+1
で6000行までコピーする。
確定する場合は、A,B列をコピーして、形式を選択して貼り付けで値とする。

回答No.3

pbforce

A列用の関数は

=VLOOKUP(ROUNDUP(A2/6,0),名前,2)

※名前とは最初のシートの
1　　　田中
2　　　山本
3　　　中村
4　　　岡田
5　　　鈴木
の部分です。

B列は

=IF(MOD=A2,6)=0,6,MOD(A2,6))

でいかがでしょうか？

お礼

ご回答ありがとうございます。

Sheet2のA1に教えていただいように入力してみました。
もとの表はSheet1にあり、質問文のようにA1からA5までに5名の
名前を入れてあります。（5名は試し用です）

　　　　　　　　　　シート２のA1に入力したもの
　　　　　　　　　　　　　　　　↓
　　　　　　　　　　　　　　　　　A　　　　　　　　　　　　　　　　　 　B
1　　　　=VLOOKUP(ROUNDUP(A2/6,0),Sheet1!A1:A5,2)
2

出力は　＃N/A　でした。ご回答いただいた”名前”の部分は”シート１のA1からA5”
を指定すればいいのですよね？

回答No.2

onepanchan

=IF(MOD(ROW(),6)=0,6,MOD(ROW(),6))

B列全てに上の数式を入力すればいけると思います。
ROW()は行番号、MODは6で割った余りです。
この方法だと、途中でメンバを挿入・削除しても
１～６が順番に割り振られます。

お礼

ご回答ありがとうございます。

どうも質問の仕方が悪かったようですみません。
1～6を上から繰り返すB列はB1からB6まで１から6を手で入力して、
B7に”＝B1”と6つ上のセルを指定してB7セルをB6000までコピー
してやれば簡単にできるのですが問題はA列なのです。
（最初からそう質問すべきでした）

6回づつ同じ名前を並べるA列を簡単にやる方法がわからないのです。
（質問の簡素のため6回にしましたが、実は30回づつ同じ名前を並べます）
今は上から下へ通し番号をつけてから行方向にコピーを繰り返して30回分になったところで
（まとめてコピペしますからコピー作業は５回ぐらい？）通し番号でソートした後
通し番号を消しています。

回答No.1

kokorone

シート１をシート２にコピーし、Ａ列でソートする
んですよね？
問題のＢ列ですが、
１）ただ単純に１から６の単純な並びなら、
　　Ｂ２に
　　＝ｉｆ（Ｂ１＋１＞６，１，Ｂ１＋１）
　　を代入後、Ｂ列全体にドラッグすれば
　　いいと思います。
２）Ａ列の同一名のカウントであれば、
　　Ｂ１に１を入れ、
　　Ｂ２に
　　＝ｉｆ（Ａ１＜＞Ａ２，１，Ｂ１＋１）
　　を代入後、Ｂ列全体にドラッグすれば
　　いいと思います。

お礼

ご回答ありがとうございます。

どうも質問の仕方が悪かったようですみません。
1～6を上から繰り返すB列はB1からB6まで１から6を手で入力して、
B7に”＝B1”と6つ上のセルを指定してB7セルをB6000までコピー
してやれば簡単にできるのですが問題はA列なのです。
（最初からそう質問すべきでした）

6回づつ同じ名前を並べるA列を簡単にやる方法がわからないのです。
（質問の簡素のため6回にしましたが、実は30回づつ同じ名前を並べます）
今は上から下へ通し番号をつけてから行方向にコピーを繰り返して30回分になったところで
（まとめてコピペしますからコピー作業は５回ぐらい？）通し番号でソートした後
通し番号を消しています。