No.9ベストアンサー
- 回答日時:
これで最後にします。
(本当に申し訳ありません)配列数式は再計算に時間が掛かりそうなので、
配列数式を使わないようにしました。
こちらは後から挿入しても大丈夫だと思いますし、
シート1の1行目以外なら追加しても削除しても問題ないと思います。
B1セルに
1班
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
シート1の最終行(65536行)に入力すると#REF!というエラーが出ます。
(すべて表示はされます。)
A列で数式にエラー処理を入れると長くなるので、
B列以降の数式で対応するようにしてあります。
A列は非表示にして下さい。
A2セルとA3セルの数式が変わってしまったので、
一気にコピー貼り付けできなくなってしまいましたが、
再計算処理は、配列数式を使うより、早いと思います。
普通の数式ですので、関数を調べて頂けば分かると思いますが、
もし、説明が必要なら補足してください。
(#5でも分かる通り、うまく説明できないと思いますが)
その時には、またお邪魔させて頂きます。
いろいろテストしてみたつもりですが、間違えていたらすみません。
長い時間考えていただいたんですね!?
カンゲキですヘ(≧▽≦ヘ)
これでいこうと思います。
また不具合が発生したら、ここに来るかもしれませんので、そのときはまた、考えてくださいね
本当にありがとうございました。
No.11
- 回答日時:
質問:【Excel97】全ワークシートの一括ページ設定
というのがでてました
こちらを見てみてください♪
参考URL:http://oshiete1.goo.ne.jp/kotaeru.php3?q=74101
うれしいです♪
調べてくださったんですね!?
ほんとにカンゲキしてます・・(・ω・、) ウルウル
これでやります♪
本当にありがとうございました。
No.10
- 回答日時:
#4 です
おそくなってすみません
もしかして質問の表のABCとは列番号のABCですか?
それでしたら表の一番上に一行挿入して
班 名前 電話番号
という項目名をつけてください
そうすると
シート1の表をアクティブにして
データ→ピボットテーブルレポート→Excelのリストを選び「次へ」
表全体が選択されていることを確認して「次へ」
行フィールドに「班」をドラッグ
データフィールドにも「班」をドラッグ
データフィールドの「班」が「データの個数:班」となっているのを確認
これで別シートに班名とその人数が表示されますので
人数が入ったセルをダブルクリックするだけで別シートにその班の人だけのリストが作られます
という具合になりませんでしょうか?
もしエラーがでるのでしたら、どの時点ででるのかを補足願います
ありがとうございました。
ばっちりできました。
データを変更したあと、班ごとの名簿を作成したときには、また新しいシートにできあがるんですね!?
印刷用にフォームを決めたい(列の幅とか)ときには、ちょっと面倒ですね。
これもまた、何かいい方法があるのでしょうか?
もしあるのなら、ついでに教えてもらえますか?
長々と質問して、ごめんなさい d(>ω<;)
No.8
- 回答日時:
何度もすみません。
書き忘れてしまいました。
=IF($A2="","",OFFSET(Sheet1!B$1,$A2-1,0))
の方の、$A2の部分を
$配列数式を入れたセル
(A3セルなら$A3,B2セルなら$B2)
に変更してください。(2ヶ所)
#7の挿入する必要がなくなってから
というのは、シート2の方です。
シート1は追加したり、削除したりすると
Sheet1!$A$1:$A$300
の部分が変化するのは、普通の関数と同じです。
INDIRECT("Sheet1!$A$1:$A$300")のようにすれば、
変化しなくなるとは思いますが、そこまでする必要があるかどうかですね。
今、ちょっとシート1を入力してみたのですが、
再計算に時間が掛かりそうです。
(私のパソコンが古いだけかもしれませんが)
この方法を使う様であれば、入力時には、
「ツール」→「オプション」→「計算方法」タグの
「計算方法」を「手動」にして
入力が終わってから、「自動」、もしくは[F9]で
再計算させた方がいいかもしれません。
失礼しました。
No.7
- 回答日時:
すいません。
ボタンを押してから気づきました。
今度は、シート1の行を挿入したり、削除するとおかしくなってしまうと思います。
挿入する必要がなくなってから、
リストの1番最初になるセルに
=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)))
の式をいれてもらうのが一番簡単でした。
1班と入力するセルを変更する場合は、数式の中の
$B$1の部分(2ヶ所)を変更してください。
大変申し訳ありません。
No.6
- 回答日時:
簡単な例で説明します。
でも関数に拘る余り、相当複雑で、全セルに式が入ります。本当はVBAでやりたい。
班番号は1桁とします。(11班などなしとする。)
(データ)
(A列)(B列)(C列)
1a11
2b21
1c12
3d31
2e22
3f32
1g13
2h23
1I14
1j15
(関数式)
=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は
1a
1c
1g
1I
1j
となります。
項目数が多い場合はC、d列にOFFSET関数の第3引数を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の部分が2班の2に変ったわけです。
A1:B3は
2b
2e
2h
以下Sheet4には3班分を同じく持ってくる。
実は下方向へ複写する時、行を下へ複写しすぎると#N/Aが出ます。これを
防ぐには更に関数式を複雑にする必要がありますが、説明を長くしないため省略します。
ありがとうございます。
関数の意味が全然わかりませんので、それを調べるところから始めます(・ω・、)
考えてくださって、ありがとうございました。
No.5
- 回答日時:
>1行挿入すると数式がうまく動かないのですが・・
なんかよくわからない現象が出ますね。
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))
です。(2ヶ所のROW(Sheet1!A1)の部分が1に変更されています。)
・COUNTIF(Sheet1!$A$1:$A$300,$B$2)
Sheet1!$A$1:$A$300の中に$B$2(1班)が何件あるか
・IF(COUNTIF(Sheet1!$A$1:$A$300,$B$2)<1,"",
件数が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(1班)の行は、
行番号を返し、違う場合は""(長さ0の文字列)を返す
・SMALL(・・・,1)
その中で一番小さな数字(行番号)を取得します。
A3セルは、2番目の値を取得したいのですが、(以下の様に)
=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
- 回答日時:
ピボットテーブルを使うのが楽だと思います
シート1の表をアクティブにして
データ→ピボットテーブルレポート→Excelのリストを選び「次へ」
表全体が選択されていることを確認して「次へ」
行フィールドに「A」をドラッグ
データフィールドにも「A」をドラッグ
データフィールドの「A」が「データの個数:A」となっているのを確認
(合計:Aなどとなっている場合はダブルクリックして「データの個数」に変更)→完了
これで別シートに班名とその人数が表示されますので
人数が入ったセルをダブルクリックするだけで別シートにその班の人だけのリストが作られます
班の数だけダブルクリックが必要ですが、コピー&ペーストより楽だと思いますよ
この回答への補足
ご回答、ありがとうございました。
試してみたら、『フィールド名が違う』とかいうメッセージが出るのです。これはどういうことなんでしょうか?
ピボットテーブルの作成先は『新しいワークシート』で良いんですよね?
もう少し、詳しく教えていただけますか?
No.3
- 回答日時:
#1さんのソートで解決すれば、読み飛ばしてください。
Sheet1に名簿 Sheet2に表示するとして
1.B1セルに 1班 と入力
2.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でもいいですが、再計算に時間が掛かります。
3.B2セルに
=IF($A2="","",OFFSET(Sheet1!B$1,$A2-1,0))
と入力して、[ENTER]を押す。
4.B2セルの値を右へ必要分コピーする
5.1行目のデータを2行目以降必要分コピーする。
6.A列を非表示にする。
でどうでしょう?
Sheet2をコピーしてSheet3に貼り付けてB1セルを 2班 にすれば、
2班分が表示されます。
班の数字が全角か、半角か注意してください。
A列を使わず、作ることもできると思いますが、
すべての列で配列数式を使う事になり、
再計算に時間が掛かると思いますのでやめました。
おかしかったり、分からなければ補足してください。
この回答への補足
お礼を書いたあと、早速、問題が発生しました!
sheet2の表の最初の行に、タイトルを入れたいのです。
『 班 名前 電話番号・・』
という感じで。
1行挿入すると数式がうまく動かないのですが・・
どこを直せばよいのですか?
ごめんなさいm(@´_`@)m
すごいです!!!
なんだか意味がさっぱりわかりませんが、ばっちり成功しました♪
でも、もし何か問題が発生したときに、修正できそうにないのが少々不安ではありますが・・
とにかく、ありがとうございました。
No.2
- 回答日時:
簡単なマクロとフィルタオプションを組合わせて作って見られたらいかがでしょうか?
まずシート2にシート1の項目名をそのままコピーしておき、その条件を与える項目名の右に条件を入力します。
例 シート2のA1のセルに班名と入力し、B1のセルに1班と入力しておきます。
シート2を開いて、
1.ツール→マクロ→新しいマクロの記録をクリックし、マクロ名を決めます。(班分けとか・・)
2.データ→フィルタ→フィルタオプション
3.指定した範囲にチェックマーク
リスト範囲にはシート1のデータ部分(このとき、あとから追加することも考えて多めに範囲選択してもいいと思います)
抽出条件 シート2の項目名と条件を入れた2つのセル(上記例 A1とB1)
抽出範囲 取り出したい場所(取り出したい項目列分範囲指定しておく)
4.マクロの終了ボタンクリック
あとは、このマクロを実行するたびに条件にあったデータを取り出してくれると思います。
ついでに表示→ツールバー→フォームの中のボタンにこのマクロを登録しておけば、ボタンクリックするだけでマクロが実行できます。
この際、シート2の上でボタンを作ってください
早速のご回答、ありがとうございました。
マクロを使うのは良いのですが、違うパソコンで使うとき(他の人が使うとき)マクロを有効にできないことがあるんですw(;_;)w
できれば、マクロを使用せずにやりたいので・・・・
自分のPCだけで使うときに、試してみたいと思います。
ありがとうござました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
ゆるやかでぃべーと タイムマシンを破壊すべきか。
これはディベートの論題だと仮定したうえでの回答お願いします。あなたは、その末にタイムマシンを壊してしまうのか、使い道を探すのかどうかを考えてもらいたいです。
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
【Excel97】全ワークシートの一括ページ設定
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルについて
-
Excelの警告について
-
if関数。半角文字や全角文字で...
-
Googleスプレッドシートで、名...
-
(マクロ)シートを保護してもマ...
-
エクセルの関数
-
【Excel】効率的な関数式の組み...
-
エクセルの数式について教えて...
-
Excelの更新日時が自動で更新さ...
-
エクセル 入力があった場合のみ...
-
【Excel】 1つのセルの日にちを...
-
エクセルでファイルの最終更新...
-
ショートカットキー
-
Excelでの勤怠表の関数を教えて...
-
エクセルで80万行、50列位のデ...
-
エクセルについての質問です。 ...
-
【Excel】年月の値によって日の...
-
考えた式の戻り値が期待通りに...
-
FからI列で期限切れ及び期限7日...
-
エクセルについての質問です。 ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
エクセルデーターから必要な項...
-
エクセルでファイルの最終更新...
-
複数のテキストファイルをexcel...
-
Excelの複数条件の関数
-
【マクロ】ファイル名の一括変...
-
EXCELの散布図で日付が1900年に...
-
マクロの処理が遅くなった
-
Excelの時刻の不思議
-
エクセルでの2項目比較および...
-
Excelマクロで空白セルを詰めて...
-
エクセルの数式バーのフォント...
-
ExcelでASCを使って全角を半角...
-
エクセルで80万行、50列位のデ...
-
今まで文字化けなく開けていたc...
-
エクセルのことで教えてくださ...
-
エクセルVBA 月の中で、月~土...
-
Excelでの表の作り方
-
Excel セルにおけるフォント設...
-
エクセルの質問です。 F列からL...
おすすめ情報