ＥＸＣＥＬで条件に合った行のデータを抜き出して、重複も削除

質問

excelに関する質問です
　| A |　B　|
1|001|斉藤|
-+--+------
2|001|渡辺|
-+--+------
3|001|田中|
-+--+------
4|001|斉藤|
-+--+------
5|002|飯田|
-+--+------
6|002|矢田|
-+--+------
7|002|飯田|
-+--+------
8|001|渡辺|
-+--+------
9|001|田中|

10| 001 |　斉藤
11| 　　. |　渡辺
12| 　　. |　田中

A1:B9にデータが入っています。
A10に番号「001」を入れるとB10:B12に
その番号のデータを検索してきて入るように
関数を利用したいのですが、上手くいきません
（A10に番号「002」を入れると飯田、矢田のデータが表示される）

MATCH関数とindirect関数、index関数などを使い、検索結果が重複して表示されるようには出来たのですが
やり方を教えて下さい、お願いします。

回答No.5

NoBi18

似たようなテクニックを業務で多用しています。
補助セルをデータの両サイドに作ります。
タイトル行も作ります。元データと抽出行の間にも、空白行を入れておきます。イメージは下のようになります。

　|A| B | C　|　D
--+-+---+----+------
１|0 No　氏名 No+氏名
２|1 001 斉藤 001斉藤
３|2 001 渡辺 001渡辺
４|3 001 田中 001田中
５|3 001 斉藤 001斉藤
６|3 002 飯田 002飯田
７|3 002 矢田 002矢田
８|3 002 飯田 002飯田
９|3 001 渡辺 001渡辺
10|3 001 田中 001田中
11|
12|1 001 斉藤
13|2 　　渡辺
14|3 　　田中
15|4 　　#N/A

各セル範囲(の左上のセル)には以下の数式が入ります。数式を記さないところは単なる文字や数字です。

【D2:D10】=$B2&$C2
【A2:A10】=A1+($B2=$B$12)*ISNA(VLOOKUP($D2,$D$1:$D1,1,FALSE))*1
【C12:C15】=VLOOKUP($A12,$A$2:$C$10,3,FALSE)

まず、【D2:D10】にNoと氏名を合体した文字列を作ります。
【A2:A10】が肝です。条件に合致するデータを数え上げています。
($B2=$B$12)…NoがB12のセル(上の例では001)と等しい時にTrue(=1)
ISNA(VLOOKUP($D2,$D$1:$D1,1,FALSE))…Noと氏名が同じ組合せが1つ上の行までに既出でない時にTrue
(例えば、5行目の001 斉藤さんは2行目で既出なのでFalse)
その2つを掛け算した部分(＝式のA1+を抜かした部分)は、上記2つの条件に当てはまる時(=抽出対象である時)に1になります。A1+をつけることにより、単なる0と1ではなく、その累計を取っています。
ですので、1行目のタイトル(特に【A1】の0)は飾りではなく、必要不可欠です。
最後に、【C12:C15】で条件にあったデータを取ってきます。単なるVLOOKUPなので、データがなければ#N/Aが表示されます。

ちなみに、ISNA関数を取り除くと、udagawaさんがお作りになった関数と同じ結果になると思います。

回答No.4

Wendy02

#3です。

#もう、ユーザー定義関数のほうが楽だと思います。

以下は、配列数式より、マシかなっていうレベルのものです。あまり、計算スピードは速くありません。
ひさびさに、Dictionary オブジェクトを使ってみました。

'標準モジュール設定
'------------------------------------------------------------
Function FindValues(findtxt As String, myArea As Range, indx As Long) As String
'Dictionary を使った、重複を省く検索,
' findtxt 検索文字列, myArea　2列の範囲, indx は、検索後のデータの序数
　Dim objDic As Object
　Dim dicAry As Variant
　Dim outAry As Variant
　Dim i As Long
　Dim j As Long
　Dim c As Variant
　Set objDic = CreateObject("Scripting.Dictionary")
　i = 1
　For Each c In myArea.Columns(1).Cells
　　On Error Resume Next
　　objDic.Add c.Value & "," & c.Offset(, 1).Value, i
　　If Err.Number = 0 Then
　　　i = i + 1
　　Else
　　　Err.Clear
　　End If
　Next
　dicAry = objDic.keys
　 outAry = Filter(dicAry, findtxt & ",")
　FindValues = Mid(outAry(indx - 1), InStr(outAry(indx - 1), ",") + 1)
End Function
'------------------------------------------------------------

ワークシートには以下のように、一般の関数と同じように入れます。

B10:
フィルダウン・コピー
=FindValues($A$10,$A$1:$B$9,ROW(A1))

回答No.3

Wendy02

こんばんは。

重複データがあるわけですよね。
まず、最初に、それを排除するということから、検索をするというのは、かなり負担が大きいような気がします。最初に、重複を、[フィルタオプションの設定]コマンドで排除というわけにはいかないのでしょうか？

>MATCH関数とindirect関数、index関数などを使い、検索結果が重複して表示されるようには出来たのですが

それなら、そのまま当てはめられるはずです。

そうでないなら、補助セルを使わないと、たぶん、ネストの制限に引っかかるような気がします。

補助セル
D1:（検索ヒット数)

=SUM((IF(MATCH($A$1:$A$9&$B$1:$B$9,$A$1:$A$9&$B$1:$B$9,0)=ROW($A$1:$A$9),$A$1:$A$9,"")=$A$10)*1)

要『配列の確定』

B10:

=IF($D$1<ROW(A1),"",INDEX($B$1:$B$9,SMALL((IF(MATCH($A$1:$A$9&$B$1:$B$9,$A$1:$A$9&$B$1:$B$9,0)=ROW($A$1:$A$9),$A$1:$A$9,"")=$A$10)*ROW($A$1:$A$9),ROWS($A$1:$A$9)-$D$1+ROW(A1)),1))

要『配列の確定』

※『配列の確定』
一旦、数式のところで、F2を押して、再度、SHIFT キーとCTRL キーを押しながら、ENTER を押すと、式が実体化して、値が出てきます。

このレベルになると、もう、ユーザー定義関数のほうが楽だと思います。

回答No.2

noname#204879

》 関数を利用したいのですが、…

私なら、こういうときのためにある[フィルタオプションの設定]を使うけど、面倒な関数に固執する理由は何ですか？

》 MATCH関数とindirect関数、index関数などを使い、
》 検索結果が重複して表示されるようには出来たので
》 すが…

ほほ～。どのように出来たのですか？出来るだけそれを利用したもので考えてみたいです。

回答No.1

qyb

先ず、002と入力した時に「飯田」か「矢田」を区別するルールを作りましょう。