エクセルの班分け名簿

質問

エクセル2000を使っています。

シート1に名簿を作っていて、その名簿の一人一人を班分けしています。

　Ａ　　Ｂ　　Ｃ
１班　Ａ子　090-xxx-xxxx
２班　Ｂ子　090-xxx-xxxx
１班　Ｃ子　090-xxx-xxxx
３班　Ｄ子　090-xxx-xxxx
　・・・・・

シート2には１班の人だけ
シート3には２班の人だけ・・・
といったように、自動的にリンクさせる方法はないでしょうか？

３００名ぐらいの名簿なので、いちいちオートフィルで抜き出してコピーするのが大変なのです。

教えてください！！

回答No.9

taisuke555 · ベストアンサー

これで最後にします。（本当に申し訳ありません）
配列数式は再計算に時間が掛かりそうなので、
配列数式を使わないようにしました。
こちらは後から挿入しても大丈夫だと思いますし、
シート１の１行目以外なら追加しても削除しても問題ないと思います。
B1セルに
１班

A2セルに
=MATCH($B$1,Sheet1!$A:$A,0)

A3セルに
=MATCH($B$1,OFFSET(Sheet1!$A$1,A2,0,65536-A2,1),0)+A2
A4セル以降は、A3セルをコピー貼り付け

B2セルに
=IF(ISERROR($A2),"",OFFSET(Sheet1!B$1,$A2-1,0))
右にコピー貼り付け
下にコピー貼り付け

前の回答は、A列にエラーが出ないようにしてあったのですが、
データが無くなると#N/A
シート１の最終行（65536行）に入力すると#REF!というエラーが出ます。
（すべて表示はされます。）
A列で数式にエラー処理を入れると長くなるので、
B列以降の数式で対応するようにしてあります。
A列は非表示にして下さい。

A2セルとA3セルの数式が変わってしまったので、
一気にコピー貼り付けできなくなってしまいましたが、
再計算処理は、配列数式を使うより、早いと思います。

普通の数式ですので、関数を調べて頂けば分かると思いますが、
もし、説明が必要なら補足してください。
（＃５でも分かる通り、うまく説明できないと思いますが）
その時には、またお邪魔させて頂きます。

いろいろテストしてみたつもりですが、間違えていたらすみません。

お礼

長い時間考えていただいたんですね！？
カンゲキですヘ(≧▽≦ヘ）

これでいこうと思います。
また不具合が発生したら、ここに来るかもしれませんので、そのときはまた、考えてくださいね

本当にありがとうございました。

回答No.11

k0000

質問：【Excel97】全ワークシートの一括ページ設定
というのがでてました
こちらを見てみてください♪

参考URL：

http://oshiete1.goo.ne.jp/kotaeru.php3?q=74101

お礼

うれしいです♪
調べてくださったんですね！？
ほんとにカンゲキしてます・・(・ω・、) ウルウル

これでやります♪
本当にありがとうございました。

回答No.10

k0000

#4 です
おそくなってすみません
もしかして質問の表のABCとは列番号のABCですか？
それでしたら表の一番上に一行挿入して
班　名前　電話番号
という項目名をつけてください
そうすると

シート１の表をアクティブにして
データ→ピボットテーブルレポート→Excelのリストを選び「次へ」
表全体が選択されていることを確認して「次へ」
行フィールドに「班」をドラッグ
データフィールドにも「班」をドラッグ
データフィールドの「班」が「データの個数：班」となっているのを確認

これで別シートに班名とその人数が表示されますので
人数が入ったセルをダブルクリックするだけで別シートにその班の人だけのリストが作られます

という具合になりませんでしょうか？

もしエラーがでるのでしたら、どの時点ででるのかを補足願います

お礼

ありがとうございました。
ばっちりできました。

データを変更したあと、班ごとの名簿を作成したときには、また新しいシートにできあがるんですね！？
印刷用にフォームを決めたい（列の幅とか）ときには、ちょっと面倒ですね。
これもまた、何かいい方法があるのでしょうか？

もしあるのなら、ついでに教えてもらえますか？
長々と質問して、ごめんなさい　d(>ω<；)

回答No.8

taisuke555

何度もすみません。
書き忘れてしまいました。

=IF($A2="","",OFFSET(Sheet1!B$1,$A2-1,0))

の方の、$A2の部分を
$配列数式を入れたセル
(A3セルなら$A3,B2セルなら$B2)
に変更してください。（２ヶ所）

＃７の挿入する必要がなくなってから
というのは、シート２の方です。
シート１は追加したり、削除したりすると
Sheet1!$A$1:$A$300
の部分が変化するのは、普通の関数と同じです。
INDIRECT("Sheet1!$A$1:$A$300")のようにすれば、
変化しなくなるとは思いますが、そこまでする必要があるかどうかですね。

今、ちょっとシート１を入力してみたのですが、
再計算に時間が掛かりそうです。
（私のパソコンが古いだけかもしれませんが）

この方法を使う様であれば、入力時には、
「ツール」→「オプション」→「計算方法」タグの
「計算方法」を「手動」にして
入力が終わってから、「自動」、もしくは[Ｆ９]で
再計算させた方がいいかもしれません。

失礼しました。

回答No.7

taisuke555

すいません。
ボタンを押してから気づきました。
今度は、シート１の行を挿入したり、削除するとおかしくなってしまうと思います。

挿入する必要がなくなってから、
リストの１番最初になるセルに
=IF(COUNTIF(Sheet1!$A$1:$A$300,$B$1)<ROW(A1),"",SMALL(IF(Sheet1!$A$1:$A$300=$B$1,ROW(Sheet1!$A$1:$A$300),""),ROW(A1)))

の式をいれてもらうのが一番簡単でした。
１班と入力するセルを変更する場合は、数式の中の
$B$1の部分（２ヶ所）を変更してください。

大変申し訳ありません。

回答No.6

imogasi

簡単な例で説明します。でも関数に拘る余り、相当複雑で、全セルに式が
入ります。本当はVBAでやりたい。
班番号は１桁とします。（１１班などなしとする。）
（データ）
（A列）（B列）（C列）　
1 a 11
2 b 21
1 c 12
3 d 31
2 e 22
3 f 32
1 g 13
2 h 23
1 I 14
1 j 15
（関数式）
=A1&COUNTIF($A$1:A1,A1)
をいれ、C2以下C10まで複写します。
上記C列のごとくになります。
（Sheet2で）
A1に関数式=OFFSET(Sheet1!$A$1,MATCH(1&ROW(A1),Sheet1!$C$1:$C$10,0)-1,0)
B1に関数式
=OFFSET(Sheet1!$A$1,MATCH(1&ROW(B1),Sheet1!$C$1:$C$10,0)-1,1)
をいれ、A2:A5、B2:B5に複写する。
A1:B5は
1 a
1 c
1 g
1 I
1 j
となります。
項目数が多い場合はC、ｄ列にOFFSET関数の第３引数を2,3,・・・にして
入れます。
（Sheet3で）
A1に関数式=OFFSET(Sheet1!$A$1,MATCH(2&ROW(A1),Sheet1!$C$1:$C$10,0)-1,0)
B1に関数式
=OFFSET(Sheet1!$A$1,MATCH(2&ROW(B1),Sheet1!$C$1:$C$10,0)-1,1)
をいれ、A2:A3、B2:B3に複写する。
2&ROW(B1)の2の部分が２班の２に変ったわけです。
A1:B3は
2 b
2 e
2 h
以下Sheet4には３班分を同じく持ってくる。
実は下方向へ複写する時、行を下へ複写しすぎると＃N/Aが出ます。これを
防ぐには更に関数式を複雑にする必要がありますが、説明を長くしないため省略します。

お礼

ありがとうございます。
関数の意味が全然わかりませんので、それを調べるところから始めます(・ω・、)

考えてくださって、ありがとうございました。

回答No.5

taisuke555

＞１行挿入すると数式がうまく動かないのですが・・
なんかよくわからない現象が出ますね。
A2セルを以下に変更して、[CTRL]+[SHIFT]+[ENTER]を押してみてください。
これなら挿入しても大丈夫だと思います。

=IF(COUNTIF(Sheet1!$A$1:$A$300,$B$2)<ROW(Sheet1!A1),"",SMALL(IF(Sheet1!$A$1:$A$300=$B$2,ROW(Sheet1!$A$1:$A$300),""),ROW(Sheet1!A1)))

＞もし何か問題が発生したときに、修正できそうにないのが少々不安ではありますが・・
確かにそうですね。
（配列数式は、慣れてしまえば、それ程苦ではないのですが
　慣れるまでがちょっと大変です。）

うまく説明できるかわかりませんが、出来るだけ解説してみます。

A2セルのみなら
=IF(COUNTIF(Sheet1!$A$1:$A$300,$B$2)<1,"",SMALL(IF(Sheet1!$A$1:$A$300=$B$2,ROW(Sheet1!$A$1:$A$300),""),1))
です。（２ヶ所のROW(Sheet1!A1)の部分が1に変更されています。）

・COUNTIF(Sheet1!$A$1:$A$300,$B$2)
Sheet1!$A$1:$A$300の中に$B$2（１班）が何件あるか
・IF(COUNTIF(Sheet1!$A$1:$A$300,$B$2)<1,"",
件数が１件より少なければ、何も表示しない

・SMALL(IF(Sheet1!$A$1:$A$300=$B$2,ROW(Sheet1!$A$1:$A$300),""),1))
これが配列数式の部分で、
・IF(Sheet1!$A$1:$A$300=$B$2,ROW(Sheet1!$A$1:$A$300),"")
Sheet1!$A$1:$A$300の中で$B$2（１班）の行は、
行番号を返し、違う場合は""（長さ０の文字列）を返す
・SMALL(・・・,1)
その中で一番小さな数字（行番号）を取得します。

A3セルは、２番目の値を取得したいのですが、（以下の様に）
=IF(COUNTIF(Sheet1!$A$1:$A$300,$B$2)<2,"",SMALL(IF(Sheet1!$A$1:$A$300=$B$2,ROW(Sheet1!$A$1:$A$300),""),2))
コピーしても値が変わりません。
手作業は面倒なので、
ROW(Sheet1!A1)　　前回はROW(A1)
で1,2,・・・の代わりをしています。
ROW(Sheet1!A1) = A1セルの行番号 = 1
下にコピーすると
ROW(Sheet1!A2) = A2セルの行番号 = 2
と手で修正しなくても変更されるようになります。

B2セルの
=IF($A2="","",OFFSET(Sheet1!B$1,$A2-1,0))
は普通の関数ですので、OFFSET関数を調べてもらえば、分かると思います。
セル番地に$が付いているものと、付いていないものがありますが、
コピーした時にセル番地を変更するか、しないかです。
実際に右にコピーした関数、下にコピーした関数が、
コピー元からどの様に変化したかを確認してみてください。

長々書いてみましたが、やっぱり、うまく説明できませんでした。すいません。
配列数式を勉強してみてください。

回答No.4

k0000

ピボットテーブルを使うのが楽だと思います
シート１の表をアクティブにして
データ→ピボットテーブルレポート→Excelのリストを選び「次へ」
表全体が選択されていることを確認して「次へ」
行フィールドに「Ａ」をドラッグ
データフィールドにも「Ａ」をドラッグ
データフィールドの「Ａ」が「データの個数：Ａ」となっているのを確認
（合計：Ａなどとなっている場合はダブルクリックして「データの個数」に変更）→完了

これで別シートに班名とその人数が表示されますので
人数が入ったセルをダブルクリックするだけで別シートにその班の人だけのリストが作られます
班の数だけダブルクリックが必要ですが、コピー＆ペーストより楽だと思いますよ

補足

ご回答、ありがとうございました。

試してみたら、『フィールド名が違う』とかいうメッセージが出るのです。これはどういうことなんでしょうか？

ピボットテーブルの作成先は『新しいワークシート』で良いんですよね？

もう少し、詳しく教えていただけますか？

回答No.3

taisuke555

＃１さんのソートで解決すれば、読み飛ばしてください。
Sheet1に名簿 Sheet2に表示するとして

１．B1セルに 1班 と入力
２．A2セルに
　　=IF(COUNTIF(Sheet1!$A$1:$A$300,$B$1)<ROW(A1),"",SMALL(IF(Sheet1!$A$1:$A$300=$B$1,ROW(Sheet1!$A$1:$A$300),""),ROW(A1)))
　　と入力して、[ENTER]のかわりに[CTRL]+[SHIFT]+[ENTER]を押す。
　　（配列数式というものです）
　　$A$1:$A$300は、実際のデータにあわせてください。
　　$A$1:$A$65535でもいいですが、再計算に時間が掛かります。
３．B2セルに
　　=IF($A2="","",OFFSET(Sheet1!B$1,$A2-1,0))
　　と入力して、[ENTER]を押す。
４．B2セルの値を右へ必要分コピーする
５．１行目のデータを２行目以降必要分コピーする。
６．A列を非表示にする。

でどうでしょう？

Sheet2をコピーしてSheet3に貼り付けてB1セルを　２班　にすれば、
２班分が表示されます。

班の数字が全角か、半角か注意してください。

A列を使わず、作ることもできると思いますが、
すべての列で配列数式を使う事になり、
再計算に時間が掛かると思いますのでやめました。

おかしかったり、分からなければ補足してください。

お礼

すごいです！！！

なんだか意味がさっぱりわかりませんが、ばっちり成功しました♪

でも、もし何か問題が発生したときに、修正できそうにないのが少々不安ではありますが・・
とにかく、ありがとうございました。

補足

お礼を書いたあと、早速、問題が発生しました！

sheet2の表の最初の行に、タイトルを入れたいのです。
　『　班　名前　電話番号・・』
という感じで。
１行挿入すると数式がうまく動かないのですが・・

どこを直せばよいのですか？
ごめんなさいm(@´_｀@)m

回答No.2

noname#9486

簡単なマクロとフィルタオプションを組合わせて作って見られたらいかがでしょうか？

まずシート２にシート１の項目名をそのままコピーしておき、その条件を与える項目名の右に条件を入力します。
例　シート２のＡ１のセルに班名と入力し、Ｂ1のセルに１班と入力しておきます。

シート２を開いて、
１.ツール→マクロ→新しいマクロの記録をクリックし、マクロ名を決めます。（班分けとか・・）

２.データ→フィルタ→フィルタオプション

３.指定した範囲にチェックマーク
　 リスト範囲にはシート１のデータ部分（このとき、あとから追加することも考えて多めに範囲選択してもいいと思います）
　　抽出条件　　シート２の項目名と条件を入れた２つのセル（上記例　Ａ１とＢ１）
　　抽出範囲　取り出したい場所（取り出したい項目列分範囲指定しておく）

４.マクロの終了ボタンクリック

あとは、このマクロを実行するたびに条件にあったデータを取り出してくれると思います。

ついでに表示→ツールバー→フォームの中のボタンにこのマクロを登録しておけば、ボタンクリックするだけでマクロが実行できます。
この際、シート２の上でボタンを作ってください

お礼

早速のご回答、ありがとうございました。
マクロを使うのは良いのですが、違うパソコンで使うとき（他の人が使うとき）マクロを有効にできないことがあるんですw(；_；)w

できれば、マクロを使用せずにやりたいので・・・・

自分のＰＣだけで使うときに、試してみたいと思います。
ありがとうござました。

回答No.1

noname#108001

こんな方法は如何でしょう。

まず、シート１の名簿をＡを最優先させて並べ替えます。　そうすると班ごとに固まるはずです。

次に１班なら１班だけを範囲指定してシート２にコピー。
または、シートごと班の数だけコピーして、シート２は１班の人を残して他は削除、シート３は２班の人だけ残して他は削除、これを繰り返します。

試してみてください。

お礼

早速のお返事、ありがとうございました。
仕事から帰るのが遅くなって、お返事が遅れてごめんなさい。
データの入れ替えが激しいので、なるべく手作業を減らしたいんです(+。+)
でもわかりやすい説明を、どうもありがとうございました。