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

(元シート)|(抽出シート)
職員No.|所属|家族|氏名| 職員NO順
120 002 1 B | A
120 002 2 B | B
134 010 1 C | C
134 010 2 C | D
134 010 3 C
152 050 1 D | 所属・職員NO順
152 050 2 D | B
152 050 3 D | C
152 050 4 D | D
152 050 1 D | A
100 200 1 A

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

このQ&Aに関連する最新のQ&A

A 回答 (4件)

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



これで意味が分かりました。できれば、標準モジュールに貼り付けるのがベストです。なお、家族区分は、抽出されません。また、これは、職員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
    • good
    • 0
この回答へのお礼

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

お礼日時:2005/04/21 21:27

職員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 所属,職員No.",TRUE)
の様にできます。
SQL.REQUESTはアドインをMSからダウンロードしてインストールする必要があります。(SQL.REQUESTのヘルプにサイトへのリンクがあります)この例では、ワークシート上の関数として使用していますが、マクロからも使用できます。
抽出.xlsはブック名
tableは、表の範囲名
後の方は、152 050 1 Dが2つあるので、2つ表示されます。
    • good
    • 0
この回答へのお礼

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

お礼日時:2005/04/19 10:20

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つずつ下方コピーします
やりたいこととあってるといいのですが...
    • good
    • 0
この回答へのお礼

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

お礼日時:2005/04/21 21:33

>家族区分の”1”のみを取り出して、一人一レコード


という意味が、どのようになるのか、理解できません。レコードというのは、データベースの1つのまとまりのあるデータのことですが、Excelで、どう扱うのでしょうか?

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

この回答への補足

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

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

補足日時:2005/04/18 19:05
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!


人気Q&Aランキング