エクセル関数（マクロ） 抽出方法について

質問

列Ａ～Ｄに職員NO、所属、家族、名前の入っている名簿（左側)を、並び替えをする際に、下記右表のように、家族区分の”1”のみを取り出して、一人一レコードにしたいのですが、何か良い関数とかまたはマクロでの考え方とかをお教えいただければと思います。

（元シート）｜（抽出シート）
職員No.｜所属｜家族｜氏名｜ 職員NO順
120 002 1 Ｂ ｜ Ａ
120 002 2 Ｂ ｜ Ｂ
134 010 1 Ｃ ｜ Ｃ
134 010 2 Ｃ ｜ Ｄ
134 010 3 Ｃ
152 050 1 Ｄ ｜ 所属・職員NO順
152 050 2 Ｄ ｜ Ｂ
152 050 3 Ｄ ｜ Ｃ
152 050 4 Ｄ ｜ Ｄ
152 050 1 Ｄ ｜ Ａ
100 200 1 Ａ

※出来ればオートフィルタを使用せずに関数や
マクロで対応したいと考えています。

回答No.4

Wendy02 · ベストアンサー

＞もともとひとつの職員番号が家族単位で重複して存在していたものを、ひとつの職員番号で一つの行としたいのです。

これで意味が分かりました。できれば、標準モジュールに貼り付けるのがベストです。なお、家族区分は、抽出されません。また、これは、職員No.順でしか、抽出されません。こちらのマクロは、最初に見つけたものを、抽出する仕組みですから、二重の出力はしません。

Sub Compaction()
　Dim Sh1 As Worksheet, Sh2 As Worksheet
　Dim Rng As Range, c As Range
　Dim RowNo As Variant
　Set Sh1 = Sheet1　'元シート
　Set Sh2 = Sheet2　'抽出シート
　Application.ScreenUpdating = False
　With Sh2
　　.Range("A1").CurrentRegion.ClearContents
　　Set Rng = Sh1.Range("A1", Sh1.Range("A65536").End(xlUp))
　　Rng.AdvancedFilter _
　　Action:=xlFilterCopy, _
　　CopyToRange:=.Range("A1"), _
　　Unique:=True
　　.Range("A1").CurrentRegion.Sort _
　　Key1:=.Range("A2"), _
　　Order1:=xlAscending, _
　　Header:=xlGuess, _
　　OrderCustom:=1, _
　　MatchCase:=False, _
　　Orientation:=xlTopToBottom
　　'元シートからフィールド行のコピー
　　Sh1.Range("B1:D1").Copy Sh2.Range("B1")
　　For Each c In .Range("A2", Range("A65536").End(xlUp))
　　　RowNo = Application.Match(c.Value, Rng, 0)
　　　If Not IsError(RowNo) Then
　　　　c.Offset(, 1).Value = Sh1.Cells(RowNo, 2).Value
　　　　'c.Offset(, 2).Value = Sh1.Cells(RowNo, 3).Value '家族区分
　　　　c.Offset(, 3).Value = Sh1.Cells(RowNo, 4).Value
　　　End If
　　Next
　End With
　Application.ScreenUpdating = True
End Sub

お礼

ご回答ありがとうございました。
いろいろ対応方法を教えていただき試しましたが、
最終的にマクロでの方法にしました。

回答No.3

BLUEPIXY

職員NO順
=SQL.REQUEST("DSN=Excel Files;DBQ=抽出.xls",,3,"SELECT DISTINCT 氏名 FROM 抽出.table WHERE 家族=1",TRUE)

所属・職員NO順
=SQL.REQUEST("DSN=Excel Files;DBQ=抽出.xls",,3,"SELECT 氏名 FROM 抽出.table WHERE 家族=1 ORDER BY 所属,職員Ｎｏ．",TRUE)
の様にできます。
SQL.REQUESTはアドインをＭＳからダウンロードしてインストールする必要があります。（SQL.REQUESTのヘルプにサイトへのリンクがあります）この例では、ワークシート上の関数として使用していますが、マクロからも使用できます。
抽出.xlsはブック名
tableは、表の範囲名
後の方は、152 050 1 Ｄが２つあるので、２つ表示されます。

お礼

ありがとうございます。
試してみようと思ったのですが、私の使っているＰＣは
2000だったので、ダウンロードできませんでした。
ＰＣを購入したらぜひ試してみようと思います。

回答No.2

moon_piyo

152-050-1-D が重複していますがこれは2レコードになってもよいのでしょうか？

見出しをのぞくデータはA2:D12にある。
職員Noは000～999の範囲である。
という条件の下で、
E2:E6 に職員No順
E8:E12 に所属、職員No順で氏名をとりだす

下記のセルに式を入れてください。
ただし配列数式として入力するために [enter] ではなく [ctrl]+[shift]+[enter]で決定してください

E2: =IF(COUNTIF($C$2:$C$12,1)>=ROW()-1,INDEX(D:D,MOD(SMALL(IF($C$2:$C$12=1,VALUE($A$2:$A$12)*65537+ROW($A$2:$A$12)),ROW()-1),65537)),"")
E8: =IF(COUNTIF($C$2:$C$12,1)>=ROW()-7,INDEX(D:D,MOD(SMALL(IF($C$2:$C$12=1,VALUE($B$2:$B$12)*65537*1000+VALUE($A$2:$A$12)*65537+ROW($A$2:$A$12)),ROW()-7),65537)),"")

E2、E8のセルを4つずつ下方コピーします
やりたいこととあってるといいのですが...

お礼

ありがとうございます。
配列数式というのをはじめて知りまして、試してみたのですが、まだいまいち理解が出来ず断念しました。
もっと知識を深めたいと思います。
またお聞きすることもあるかと思いますが、その際は
よろしくお願い致します。

回答No.1

Wendy02

＞家族区分の”1”のみを取り出して、一人一レコード
という意味が、どのようになるのか、理解できません。レコードというのは、データベースの１つのまとまりのあるデータのことですが、Excelで、どう扱うのでしょうか？

示された図も、
＞（元シート）｜（抽出シート）
＞120 002 1 Ｂ ｜ Ａ
＞職員No.｜所属｜家族｜氏名｜ 職員NO順
元シートと抽出シートの区切れが、所属と家族の間にあって、シートのデータの構造が見当がつきません。

補足

分かりづらくてすみません。
列A・・・職員NO.
列B・・・所属NO.
列C・・・家族区分
列D・・・氏名

と入っています。行数は5000くらいです。
これを職員NOで並び替えをすると、通常、職員番号順に
並び替えされると思いますが、その際に、家族区分1以外を無視して、並び替えが出来ればと考えています。
結果的に、もともとひとつの職員番号が家族単位で重複して存在していたものを、ひとつの職員番号で一つの行としたいのです。
説明がへたくそですみません。
いかがでしょうか？