２つの条件で重複しないデータを抽出

質問

シート１
１｜品名　種類　名前　データ
２｜A　１　あ　21
３｜B　２　い　11
４｜F　２　あ　51
５｜A　１　い　21　
６｜B　２　あ　64
７｜A　１　あ　84

シート２
１｜品名　A
２｜種類　１
３｜
４｜名前
５｜あ
６｜い

シート１のデータから、シート２の２つの条件（A1:B2)で、重複しない名前を表示する方法を教えて下さい。
上記のデータでいうと、品名Ａであり種類が１の名前を重複なしで特定の位置（A5）から詰めて縦に表示するようにして、
シート１のデータ、シート２の条件を変更したらA5からの名前が自動更新されるようにしたいです。

以下できたら追加したいこと
1.表示する名前の順番が、シート２の条件で集計したデータ値が多い名前順
2.シート２のB2の種類に０を入れると種類の条件無しで表示

実際はA5からの名前の行は数行を結合したもので右側には複数の行でデータ項目別になって、名前が入力された時に日毎のデータを自動表示しています。
全部で名前の種類は３０以上ありますが、品名の条件をつければ名前は１０種類以下になります。
グラフは名前別で表示しており、不要な名前が増えると表示しずらいので、必要な名前だけのデータを表示するようにしたいです。

どうかよろしくお願いします。

回答No.3

tom04 · ベストアンサー

No.1です！
補足を読ませていただきました。
データは10000行位あり、今後も増える予定だというコトですので、
VBAでの方法はどうでしょうか？

※　実は前回、
＞1.表示する名前の順番が、シート２の条件で集計したデータ値が多い名前順
＞2.シート２のB2の種類に０を入れると種類の条件無しで表示
の件を無視していましたので、

Sheet1・Sheet2の配置はお示し通りになっていて、Sheet3を作業用に使用しています。
（Sheet3は現在なにも使用していないという前提です。）

画面左下にあるSheet2のSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてSheet2のB1・B2セルのデータを変更してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim i, j As Long
Dim ws1, ws3 As Worksheet
Set ws1 = Worksheets(1)
Set ws3 = Worksheets(3)
Application.ScreenUpdating = False
If Target.Row <= 2 And Target.Column = 2 Then
If WorksheetFunction.CountBlank(Range("B1:B2")) Then Exit Sub
i = Cells(Rows.Count, 1).End(xlUp).Row
If i > 4 Then
Range(Cells(5, 1), Cells(i, 1)).ClearContents
End If
ws1.Columns("A:D").Copy Destination:=ws3.Cells(1, 1)
ws3.Columns("A:D").Sort key1:=ws3.Cells(1, 4), order1:=xlDescending
ws3.Columns(1).Insert
If Cells(2, 2) = 0 Then
For i = 2 To ws3.Cells(Rows.Count, 2).End(xlUp).Row
If ws3.Cells(i, 2) = Cells(1, 2) And _
WorksheetFunction.CountIf(ws3.Columns(1), ws3.Cells(i, 4)) = 0 Then
ws3.Cells(i, 1) = ws3.Cells(i, 4)
End If
Next i
For j = 2 To ws3.Cells(Rows.Count, 1).End(xlUp).Row
If ws3.Cells(j, 1) <> "" Then
Cells(Rows.Count, 1).End(xlUp).Offset(1) = ws3.Cells(j, 1)
End If
Next j
Else
For i = 2 To ws3.Cells(Rows.Count, 3).End(xlUp).Row
If ws3.Cells(i, 2) = Cells(1, 2) And ws3.Cells(i, 3) = Cells(2, 2) And _
WorksheetFunction.CountIf(ws3.Columns(1), ws3.Cells(i, 4)) = 0 Then
ws3.Cells(i, 1) = ws3.Cells(i, 4)
End If
Next i
For j = 2 To ws3.Cells(Rows.Count, 1).End(xlUp).Row
If ws3.Cells(j, 1) <> "" Then
Cells(Rows.Count, 1).End(xlUp).Offset(1) = ws3.Cells(j, 1)
End If
Next j
End If
ws3.Cells.ClearContents
Application.ScreenUpdating = True
End If
End Sub 'この行まで

※　B2セルが「0」の場合は「種類」は無視して、「品名」ごとの最大データの人を表示！
※　B2セルが「0」以外の場合は「品名」・「種類」が一致する最大データの人を表示！
という考え方にしています。

お役に立てば良いのですが・・・m（＿ ＿）m

お礼

ご回答有難うございます。
わざわざプログラムを書いて頂きありがたいです。
実際の形式にするのにすぐには無理そうなので、あとでとっておきます。
とりあえず教えて頂いた追加列で種類判定もつけてうまくいっております。
有難うございました。

回答No.2

MackyNo1

補助列なしに関数で重複のない名前データを表示するなら（最大10件まで）、以下のような複雑な数式を使う必要があります。
ただし配列数式ですので入力後Ctrl＋Shift+Enterで確定してください。

=INDEX(Sheet1!C:C,SMALL(IF((MATCH(Sheet1!$A$2:$A$10000&Sheet1!$B$2:$B$10000&Sheet1!$C$2:$C$10000,Sheet1!$A$2:$A$10000&Sheet1!$B$2:$B$10000&Sheet1!$C$2:$C$10000,)=ROW($A$2:$A$10000)-1)*($B$1&$B$2=Sheet1!$A$2:$A$10000&Sheet1!$B$2:$B$10000),ROW($A$2:$A$10),10000),ROW(A1)))&""

一般的に、上記のような配列数式はデータ範囲を大きくしたり、数式で表示するセルが多くなると再計算に時間がかかりシートの動きが重くなるなどのデメリットがあります。
今回のような表示件数が10件までで良いなら、おそらくそれほど大きな影響はないかもしれませんが、シートの動きが重く感じたら、シートの計算方法を「手動」に設定しておき、必要に応じてF9キーで再計算するような対応をしてください。

一方、1万行もあるデータに対して、補助列の多数のセルにCOUNTIF関数やMATCH関数など比較的メモリーを多く消費する関数を入力すると、パソコンがハングアップすることがありますので、今回のようなケースでは補助列を使う方法はあまりお勧めできません。

しかし、一般的に配列数式を駆使して表示する場合は、数式そのものの意味がわかりにくく、数式の変更などのメンテナンスができないと思いますので（表示条件が複雑になるほど数式が複雑になります）、関数ではなく、フィルタオプションの設定などの一般機能を利用した方法で、該当データを抽出する方法を利用されることをお勧めします。

お礼

ご回答有難うございます。
分かりやすく書いて頂き勉強になりました。
配列数式ではかなり重くなったので、別の方法でためそうと思います。

回答No.1

tom04

こんばんは！
色々方法はあると思いますが、一例です。

↓の画像のようにSheet1に作業列を2列設けています。
（目障りであれば遠く離れた列にするか、作業列を非表示にします）

Sheet1の作業列1E2セルに
=IF(COUNTBLANK(A2:C2),"",A2&"_"&B2&"_"&C2)
作業列2のF2セルに
=IF(AND(COUNTIF($E$2:E2,E2)=1,A2&"_"&B2=Sheet2!$B$1&"_"&Sheet2!$B$2),ROW(),"")
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

Sheet2のA5セルに
=IF(COUNT(Sheet1!F:F)<ROW(A1),"",INDEX(Sheet1!C:C,SMALL(Sheet1!F:F,ROW(A1))))
という数式を入れオートフィルで下へコピーすると
画像のような感じになります。

参考になれば良いのですが・・・m（＿ ＿）m

お礼

早速のご回答有難うございます。
画像まで付けて頂きとても参考になりました。ちょっと理解に時間かかりそうですが、実際の条件に修正して試してみようと思います。

あと書いてなかったので補足させていただきます。
実際シート１のデータは、月のデータで、列数が５０くらいあり、行数は多くて１００００くらいあります。
毎週くらいに更新して追加していく予定で、更新するたびに行数が増えていきます。
もし作業列の追加がない例があれば是非ご教授お願いします。