エクセル2003 あらかじめセルに入力されている文字を含まない結果を抽出して表示したい

質問

エクセル初心者で少しずつ勉強しております。
説明がうまくできるか心配ですがご相談にのってください。

下記のような条件を満たすマクロを作成したく思っております。

困っているのはタイトルとおり
『あらかじめセルに入力されている文字を含まない結果を抽出して表示したい』

例　全抽選者の中から一部のグループ『2』を抽選者対象から除外したリストを作成したい場合

抽出前

除外したいグループ
(2)　
　　全抽選者リスト

グループ番号　　氏名
1　　　　　　　　　田中
1　　　　　　　　　鈴木
3　　　　　　　　　池田
2　　　　　　　　　平田
3　　　　　　　　　根本
2　　　　　　　　　高木

抽出後
グループ番号　　氏名
1　　　　　　　　　田中
1　　　　　　　　　鈴木
3　　　　　　　　　池田
3　　　　　　　　　根本

これでお分かりになるでしょうか。
よろしくお願いいたします。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.7

tom04 · ベストアンサー

No.4・5です！
↓のような配列で良いのですかね？

一応そういうことだとして回答させていただきます。

一例です。
作業用の列を使わせてもらいます。

余計なお世話かもしれませんが、中の列は該当するデータがほしいのだと解釈して
作業用の列を2列設けました。

まず、E8セルに
=IF(C8=$P$1,ROW(A1),"")
F8セルに
=IF(C8<>$P$1,ROW(A1),"")
という数式を入れ、E8・F8セルを範囲指定し、F8セルのフィルハンドルで下へコピーします。

そして、除外の列になりますが、
X8セルに
=IF(COUNT($E$8:$E$27)<ROW(A1),"",INDEX(C$8:C$27,SMALL($E$8:$E$27,ROW(A1))))
という数式を入れ、列方向と行方向にコピーすると
P1セルに入力したものが表示されると思います。

そして、余計なお世話の列の方ですが、
N8セルに
=IF(COUNT($F$8:$F$27)<ROW(A1),"",INDEX(C$8:C$27,SMALL($F$8:$F$27,ROW(A1))))
という数式を入れ、これも列・行方向にコピーすると
除外されたデータ以外が表示されます。

以上、セルの正確な配置はあっているかどうか判りませんが、
参考になれば幸いです。m(__)m

お礼

完璧です！！

こんな遅い時間までありがとうございました
ならびにすべてご指導いただきありがとうございました。

まだまだエクセルは駆け出しで勉強が必要だと思っております。
また壁にぶち当たったときには相談にのっていただければ幸いです。

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

回答No.8

MackyNo1

セルの番地が違ったのですね。
P1に除外番号が入っているなら,X7セルに以下の式を入力して、右方向に１つ下方向にオートフィルします。

=INDEX(D:D,SMALL(INDEX(($D$8:$D$27=$P$1)*1000+ROW($D$8:$D$27),),ROW(A1)))&""

画像がはっきりしないので番号が入っているセルをD列名前が入っているセルをE列としましたが、もし違ったら列番号を適宜変更してください。

該当データを表示するには「$D$8:$D$27=$P$1」の部分を「$D$8:$D$27〈〉$P$1」に変更してください。

お礼

完璧です！！

こんな遅い時間までありがとうございました
ならびにすべてご指導いただきありがとうございました。

まだまだエクセルは駆け出しで勉強が必要だと思っております。
また壁にぶち当たったときには相談にのっていただければ幸いです。

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

回答No.6

MackyNo1

関数で表示してもよいなら、配列数式を使う手もありますが、数式の意味がわからないと応用が利きにくくなるだけでなく、抽出データが多いと再計算に時間がかかり動きが重くなります。

あまりお勧めしませんが、数式の意味がわからなくても、そのまま答えを出したいなら、以下のような関数をN71セルに入力して右方向及び下方向にオートフィルして下さい(N59セルに除外する数字を入力）。

=INDEX(N:N,SMALL(INDEX(($N$62:$N$67=$N$59)*1000+ROW($N$62:$N$67),),ROW(A1)))&""

ちなみに、検索条件をセルに入力するようなケースでは、数式または検索条件をそのままセルに入力するフィルタオプションの設定のほうが応用範囲が広くてよいと思います（少し操作ノウハウがありますがマクロでやりたければ、この操作を記録するだけでOKです）。

お礼

MackyNo1さま

丁寧なご回答ありがとうございます。
ならびにみなさんありがとうございます。

なんだか自分で質問している内容までおかしくなってしまっていました。
すみません。
もう一度整理して詳細をお送りします。

失礼かと思いますが今一度だけご回答をおねがいいたします。
画像をお送りアップしました。

写真の左側が元のデータの応募者です
このデータをもとに
画面中央に『抽選者リスト』（除外グループを除いた）
画面左側に『除外者リスト』
と抽出したシートを作りたいところです。

※ちなみにグループを指定している数字は読み方でいうと『まるいち』、『まるに』、『まるさん』...という整数ではありません。
除外グループの元の値はＰ１に入力しています。

大変もうしわけございませんがお時間があればご回答をお願いいたします。

回答No.5

tom04

No.4です！
たびたび失礼します。

前回の配列数式の場合は
INDEX関数内の範囲指定の行数と、IF関数の中の行数が一致しないとエラーになると思います。

↓の画像のような配列になっているわけですよね？

もしそうであれば、N71セルに
=IF(COUNT($N$62:$N$67)-COUNTIF($N$62:$N$67,$O$58)<ROW(A1),"",INDEX(N$62:N$67,SMALL(IF($N$62:$N$67<>$O$58,ROW($1:$6)),ROW(A1))))
という数式を入れて見てください。
前回同様、配列数式にして列・行方向にオートフィルでコピーすれば
希望に近い形にならないでしょうか？

尚、余計なお世話かもしれませんが、
下側の右にある表は配列数式ではなく、作業用の列を使い
その値に基づいてデータを返す方法になりますが、
これも一緒に説明しておきます。

作業列のP62セル（もし隣に作業用の列が作成できないのであれば、少し離れたところでも良いですので、行を揃えて）に
↓の数式を入れてみてください。
=IF(N62<>$O$58,ROW(A1),"")
これを下へオートフィルでコピーします。

Q71セルに（配列数式ではありません）
=IF(COUNT($P$62:$P$67)<ROW(A1),"",INDEX(N$62:N$67,SMALL($P$62:$P$67,ROW(A1))))
という数式を入れ、これも列・行方向にオートフィルでコピーすると
配列数式と同様の結果が出ると思います。

以上、長々と書きましたが
何度もごめんなさいね。m(__)m

お礼

tom04さま

丁寧なご回答ありがとうございます。
ならびにみなさんありがとうございます。

なんだか自分で質問している内容までおかしくなってしまっていました。
すみません。
もう一度整理して詳細をお送りします。

失礼かと思いますが今一度だけご回答をおねがいいたします。
画像をお送りアップしました。

写真の左側が元のデータの応募者です
このデータをもとに
画面中央に『抽選者リスト』（除外グループを除いた）
画面左側に『除外者リスト』
と抽出したシートを作りたいところです。

※ちなみにグループを指定している数字は読み方でいうと『まるいち』、『まるに』、『まるさん』...という整数ではありません。
除外グループの元の値はＰ１に入力しています。

大変もうしわけございませんがお時間があればご回答をお願いいたします。

回答No.4

tom04

こんばんは！
横からお邪魔します。

VBAではないので、参考にならなかったら読み流してください。

↓の画像の
G2セル以外のデータを表示させるように関数でやってみました。
尚、配列数式になってしまいますので
もし、この画面から数式をコピー＆ペーストしただけではエラーになると思います。
貼り付け後、F2キーを押すか、数式バー内で一度クリックします。
編集可能になりますので
Shift＋Ctrl＋Enterキーで確定すると、数式の前後に｛　｝マークが入り
配列数式になります。

D2セルに
=IF(COUNT($A:$A)-COUNTIF($A:$A,$G$1)<ROW(A1),"",INDEX(A$2:A$1000,SMALL(IF($A$2:$A$1000<>$G$1,ROW($1:$999)),ROW(A1))))
という数式を入れ、配列数式にして
列方向と行方向にオートフィルでコピーすると
画像のような感じになります。

尚、数式は1000行まで対応できるようにしています。

以上、長々と失礼しました。m(__)m

お礼

tom04さま

ありがとうございます。
関数でも抽出ができるんですね。
単独のテストではうまくいきましたが私の持っている表に関数を貼り付けて関数のセル番号等を変更しましたがうまく動きません。

ほかの方からもご指摘をうけましたが
もう少し基本の部分を勉強しなければと反省しております。

なにしろ期限付きの資料提出がせまっていて自分ではどうにもならず、こちらに質問させていただいた次第です。

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

回答No.3

MackyNo1

>当方の行いたい手順としまして抽出前に除外グループの番号をセルに入力して、その後抽出するマクロを貼り付けたボタンを実行するというかたちで行いたいとおもっております。

もし、マクロで実行したいなら、皆さんからの回答例を見て、少なくともこの程度のコードの修正は自分でできないと、今後運用上で問題が出ると思います（まさに丸投げの質問となりますね）。

たとえば、私の回答なら自動記録されたコードの「２」の部分を、「Range("N59").Value」に変更すればうまくいくと思います。

ちなみに、ご希望の操作は、一般機能の「フィルタオプションの設定」という機能でセルに入力した値や数式を条件として、簡単に抽出できますので具体的方法を提示できますが、（ご自分では修正できなくても）どうしてもマクロにこだわる必要がありますか？

＃私の経験では、まずエクセルの一般機能を使いこなせるようになってから、ＶＢＡを勉強するという手順にしたほうがエクセルを効率的に学習できると思います。

回答No.2

MackyNo1

>エクセル初心者で少しずつ勉強しております。

初心者でしたら、まずエクセルの基本的な機能を理解してからマクロを利用するようにしましょう。

ご質問の、内容を実行するには以下のような操作をします（エクセルのバージョンが記載されいませんので2003で説明します）。

グループ番号のセルを選択し、「データ」「フィルタ」「オートフィルタ」にして、グループ番号のセルの右側にでてくる▼をクリックし「オプション」で「２」「と等しくない」と設定し、「OK」します。
このセル範囲を選択し、必要なら新規シートに貼り付ければ完成です。

もし、この操作をマクロで実行したいなら、上記の操作を新しいマクロの記録でコードを記録します。

「ツール」「マクロ」「新しいマクロの記録」で上記の手順を行い、他シートに抽出データを貼り付ける場合は、データ範囲を下方向に空白セルを含めて大きめに選択し、最後に元シートで「データ」「フィルタ」「すべて表示」でフィルタ操作を解除する操作まで行い、「記録終了」すれば完成です。

お礼

MackyNo1さま

ご回答ありがとうございました。
オートフィルタでのマクロ記録は行いました。
当方の行いたい手順としまして
抽出前に除外グループの番号をセルに入力して、その後抽出するマクロを貼り付けたボタンを実行するというかたちで行いたいとおもっております。

こちらの言葉足らずで申し訳ございませんでした。
とても丁寧にご回答いただき感謝しております。
ありがとうございました。

回答No.1

KURUMITO

全抽選者リストのデータがＡ列とＢ列の2行目からあるとして、それをＤ列とＥ列に抽出表示させるとしたら次のようなマクロでもよいでしょう。
Sub グループ抽出()

Dim RowA As Integer
Dim RowB As Integer

RowA = 1
RowB = 1
Saku = 2

Do
RowA = RowA + 1
If Cells(RowA, 1) <> Saku Then
RowB = RowB + 1
Cells(RowB, 4) = Cells(RowA, 1)
Cells(RowB, 5) = Cells(RowA, 2)
End If
Loop Until Cells(RowA, 1) = ""

End Sub

お礼

KURUMITOさま

ありがとうございました。
マクロの動作は確認できました。
行、列番号をお伝えするのを忘れておりました。
実際の列、行番号をお送りしますので今一度ご回答いただければ幸いです。

グループ番号　Ｎ列　氏名　Ｏ列として
作業手順
１．削除したいグループ番号は抽出をする前にセル（N58）に入力します。
２．抽出されたデータはＮ列の69行目以降に表示させたい。

除外したいグループ　　 <---------N58（セル番号）　　　　　　　　　
(2)　　　　　　　　　　　　　　<---------N59（セル番号）
全抽選者　　
グループ番号　氏名　　　<--N61 <--O61
1　　　　　 　田中　　　　　　<--N62 <--O62　　
1　　 　　　　鈴木　　　　　　　｜
3 　　　　　　池田　　　　　　　｜
2 　　　　　　平田　　　　　　　｜
3 　　　　　　根本　　　　　　　｜
2 　　　　　　高木 　　　　　 <--N67 <--O67

抽出後　　　　　　　　　 　 <--N69 <--O69　　　
グループ番号　氏名 　　 <--N70 <--O70
1　　　　　 　田中　　　　　　　｜
1　　　　　 　鈴木　　　　　　　｜
3　　　　　 　池田　　　　　　　｜
3　　　　　 　根本 　　　 　 <--N74 <--O74

はじめからこのように質問をすればよかっと反省しております。
お時間が空いたときにでもご回答いただければ幸いです。
よろしくお願いいたします。