エクセルでフィルターのような関数

質問

シート1（入力シート）
　A　　　B　　C　　　D
Aさん　80点　A地区　15歳
Bさん　70点　B地区　16歳
Cさん　60点　C地区　17歳
Dさん　80点　B地区　16歳
Eさん　90点　C地区　15歳
と作りました
今まではフィルターを使いコピーをして貼り付けで
やっていたのですが・・何もしないで印刷だけをするようにしたいと思っています。
シート2
　A　　B
A地区　Aさん

B地区　Bさん
　　 　Dさん
C地区　Cさん
　　　 Eさん
B列にVLOOKUPで関数入力したのですが・・
１人しかあがってきません。
何かよい方法ありましたら教えてください。
宜しくお願いします。

回答No.7

imogasi · ベストアンサー

＃４です。
（データ）質問文からコピーして、書式ーセル－「折り返して全体を表示する」のチェックをはずしますと、Ａ１：Ａ５の行ごとのコピー貼りつけになります。
その後、データ－区切り位置－スペース区切りでデータを各列のセルに分離しデータを作りました。
第一行に見出しはありません。Ａ１：Ｄ５で
Aさん　 80点　 A地区　 15歳
Bさん　 70点　 B地区　 16歳
Cさん　 60点　 C地区　 17歳
Dさん　 80点　 B地区　 16歳
Eさん　 90点　 C地区　 15歳
（結果）
回答のコードをコピー貼りつけして、実行した。
結果は下記の通り。
A地区　 Aさん　 80点　 15歳
B地区　 Bさん　 70点　 16歳
Dさん　 80点　 16歳
C地区　 Cさん　 60点　 17歳
Eさん　 90点　 15歳
もとデータに、第一行に見出しが入っていませんか。
あまり良く判らないようでしたら、スキップしてください。
＃５には関数による回答もいれています。

回答No.6

telescope

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

シート1のシートタブを右クリック[移動またはコピー]を選択。
[コピーを作成する]にチェックを入れる。
新しいシートのC列を「切取り」、A列に[切り取ったセルを挿入]
「データ」－「並べ替え」で最優先する列を列Aにして並べ替える。

地区名が上の行と同じ場合にフォントの色を白に設定します。
まず、「ツール」－「オプション」の「全般」タブで[R1C1参照形式を使用する]チェックボックスをオンにします。
A列の2行目以降のセルを選択して
「書式」－「条件付き書式」で
[セルの値が]－[次の値に等しい]を選択して
=r[-1]c
と式を入力します。
「R1C1参照形式を使用する」にチェックを入れていないとエラーになります。
（1行目のセルにこの式を入れると最下行(65535行）を参照してしまいますのでご注意）

[書式]ボタンをクリックして[フォント]の[色]を白にします。
「R1C1参照形式を使用する」のチェックをオフにします。

C列、D列を削除するか、必要な部分だけを印刷範囲に設定して印刷します。

回答No.5

imogasi

関数による解。
これって関数によるソートですね。
（例データ）Sheet2のA1:D８（E列は後述の関数の結果）
Aさん　 80点　 A地区　 15歳 11
Bさん　 70点　 D地区　 16歳 14
Dさん　 80点　 B地区　 16歳 12
Cさん　 60点　 C地区　 17歳 13
Eさん　 90点　 B地区　 15歳 12
Eさん　 91点　 C地区　 16歳 13
Eさん　 92点　 C地区　 17歳 13
Eさん　 93点　 A地区　 18歳 11
地区をコード化するため（コード化すると出現順序を左右（規制）出来るメリットあり。またあとでSMALL関数を使うので。）
A11:B14に（どこでも良いが）
A地区　 11
B地区　 12
C地区　 13
D地区　 14
を作る。地区は９９個以下とする。
E１セルに=VLOOKUP(C1,$A$11:$B$14,2)といれE8まで
複写する。上記E列のようになる。
F1に=E1&TEXT(COUNTIF($C$1:C1,C1),"00")といれF8まで複写する。一地区での人数は９９人以下と仮定。９９９人
以下なら"000"にする。
1101
1401
1201
1301
1202
1302
1303
1102
となる。
G1に=VALUE(F1)といれてG8まで複写する。
上記の数字化したものとなる。
（式を=VALUE(E1&TEXT(COUNTIF($C$1:C1,C1),"00"))とすれば１列で済む。）
ここでSheet2に行って
A1に=SMALL(Sheet2!$G$1:$G$8,ROW())
A8まで複写する。下記A列のとおりとなる。
（A列）　（B列）　（C列）　（D列）　（E列）
1101 Aさん　 80点　 A地区　 15歳
1102 Eさん　 93点　 A地区　 18歳
1201 Dさん　 80点　 B地区　 16歳
1202 Eさん　 90点　 B地区　 15歳
1301 Cさん　 60点　 C地区　 17歳
1302 Eさん　 91点　 C地区　 16歳
1303 Eさん　 92点　 C地区　 17歳
1401 Bさん　 70点　 D地区　 16歳
B1に=OFFSET(Sheet2!$A$1,MATCH(A1,Sheet2!$G$1:$G$8,0)-1,0)といれB8まで複写。
C1に=OFFSET(Sheet2!$A$1,MATCH(A1,Sheet2!$G$1:$G$8,0)-1,1)といれC8まで複写。
D1に=OFFSET(Sheet2!$A$1,MATCH(A1,Sheet2!$G$1:$G$8,0)-1,2)といれD8まで複写。
E1に=OFFSET(Sheet2!$A$1,MATCH(A1,Sheet2!$G$1:$G$8,0)-1,3)といれE8まで複写。
最後の引数だけが０－＞１－＞２－＞３と変化していることに注意してください。
結果は上記の通りです。
地区をB列に持ってくるなら、D列の式をB列にセットし、
上記B列の式をD列に持ってくれば良い。
地区の２件目以後は空白にすることも可能だが、長くなるので省略します。

回答No.4

imogasi

この問題はエクセル関数向きの問題ではありません。
回答が出ていますが、理解するのも骨がおれるし、一部仮定（地区を横にならべるとか、一地区の人数を定数と仮定とか）が入ってます。
難しい所以は、関数はデータをセットする（受け取る）セル側で、関数を入力しないといけないが、どこのセルから持ってくれば良いか、他のセルの状況で変るので、計算しにくいところにあります。VBAならどこへでも、データは
セットできます。
エクセルといえば関数で、自動的なので、魅力的なのはわかりますが、並べ替えや抜出など、セル（データ）位置を変える問題はエクセルは苦手です。
ということで、マクロの記録を手直ししたようなコードを挙げておきます。入力が増えたり変ったりした時、実行キッカケは人手となりますが、実行は瞬時に自動でなされます。
Sub test01()
'---最下行数取得
d = Range("A1").CurrentRegion.Rows.Count
'----ソートC列でソート
Set a = Range(Cells(1, "A"), Cells(d, "F"))
a.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin
'----列移動C->E,AB->BC,E->A
Columns("c:c").Cut Destination:=Columns("e:e")
Columns("A:B").Cut Destination:=Columns("B:C")
Columns("E:E").Cut Destination:=Columns("A:A")
'----A列の体裁整形同一地区は空白に
For i = 2 To d
If Cells(i, "A") = Cells(i - 1, "A") Then
Cells(i, "A") = ""
End If
Next i
End Sub

補足

回答ありがとうございます。
いろいろやっていますが・・

知識不足のためか、エクセルを使うことが８年ぶりなもんで、てこずっています。

実行をすると名前が上がらず
A地区　　A地区
　　　　　A地区
　　　　　A地区
B地区　　B地区
　　　　　B地区
と上がってきてしまいます。
間違っているのはわかるのですが・・
どこが間違っているのか・・
すみませんお願いですが・・
宜しくお願いします。

回答No.3

jindon

#1です。

シート2に抽出でしたね。

シート2の
A1=A地区, B1=B地区, C1=C地区, と入力して

A2:
=IF(ISERROR(SMALL(IF(Sheet1!$C$1:$C$5=A$1,ROW(Sheet1!$C$1:$C$5)),ROW(A1)))=FALSE,INDEX(Sheet1!$A$1:$D$5,SMALL(IF(Sheet1!$C$1:$C$5=A$1,ROW(Sheet1!$C$1:$C$5)),ROW(A1)),1),"")

Ctrl+Shift+Enterで確定後、

コピー+ペーストしてください。

回答No.2

toshihiko_ooishi

Sheet2!A1=A地区
Sheet2!A6=B地区
Sheet2!A11=C地区
として、それぞれの人を
Sheet2!B1:B5
Sheet2!B6:B10
Sheet2!B11:B15

に５名ずつ抽出できるようにしたばあい、
Sheet2!B1:B5を選択し、

=IF(COUNTIF(OFFSET(Sheet1!$C:$C,0,0,COUNTA(Sheet1!$C:$C),1),Sheet2!A1)<ROW()-ROW(A1)+1,"",INDEX(Sheet1!$A:$A,SMALL((OFFSET(Sheet1!$C:$C,0,0,COUNTA(Sheet1!$C:$C),1)=A1)*ROW(OFFSET(Sheet1!$C:$C,0,0,COUNTA(Sheet1!$C:$C),1)),ROWS(OFFSET(Sheet1!$C:$C,0,0,COUNTA(Sheet1!$C:$C),1))-COUNTIF(OFFSET(Sheet1!$C:$C,0,0,COUNTA(Sheet1!$C:$C),1),Sheet2!A1)+ROW()-ROW(A1)+1)))

でCtrl+Shift+Enter。

これを、Sheet2!B6:B10,Sheet2!B11:B15にコピー。

回答No.1

jindon

F1=A地区, G1=B地区, H1=C地区, と入力して

F1:
=IF(ISERROR(SMALL(IF($C$1:$C$5=F$1,ROW($C$1:$C$5)),ROW(A1)))=FALSE,INDEX($A$1:$D$5,SMALL(IF($C$1:$C$5=F$1,ROW($C$1:$C$5)),ROW(A1)),1),"")
として、Ctrl+Shift+Enterで確定。

G1までフィルライト（コピー+ペースト)
F1:H1を必要分フィルダウン