[[20160616205153]]　『1対多対応で多のほうに検索をかけて１の方を表示ａx（hassann）　ページの最後に飛ぶ

『1対多対応で多のほうに検索をかけて１の方を表示する』（hassann）

EXCEL 2010で以下の様な場合どのような検索をしたらよいのでしょうか。

    A       B       
1  XXX     abcd
2          bcda
3          kabc
4          cdab
5          dabc
6  YYY     abcd
7          bcda
8          dabc
9          cdab
10 ZZZ     bcda
11         cdab
12         dabc
13         kabc
14         abcd

A列が大項目、B列が小項目です。小項目にKを含んでいる大項目を関数で抜き出したいのです。
COUNTIFでc列にB列にkを含んでいる行番号を表示させてその行のA列の空白を無視した直近の上のセルを表示させようとしたのですがどうにも出来ませんでした。A列の大項目は400程度、b列の小項目は多い物で20個、少ない物で2個と一定はしておらず、Kを含んだ小項目が何番目に出てくるのかも決まっていません。
全体の行としては7000行を超えます。
セルは全て独立しています、結合は使っておりません。
どのような計算式が可能なのかご教示いただけると有り難いです。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 作業列を使っていいなら

 C1 : =A1
 C2 : =IF(A2="",C1,A2)

 C2 を下にフィルコピーしておいて

 D1 : =IFERROR(INDEX(C:C,SMALL(IF(ISNUMBER(SEARCH("k",B$1:B$1000)),ROW(B$1:B$1000)),ROW(A1))),"")

 これを　Ctrl/Shift/Enter　で配列数式として入力して、下にフィルコピー　ではいかがですか。

（β） 2016/06/16(木) 22:58

 ↑　あっ！　大項目ブロック内に、k　を含む小項目が複数あると　同じ大項目が複数でてしまいますね。

 さらに

 E1 : =D1
 E2 : =IFERROR(VLOOKUP("*",IF(COUNTIF(E$1:E1,D$1:D$1000)=0,D$1:D$1000),1,FALSE),"")
 これを Ctrl/SHift/Enter で入力して下にフィルコピー。

 でも、まだるっこしいですね。

 専門家さんからの回答をお待ちください。

 捨ててください。

（β） 2016/06/16(木) 23:00

ものすごいシンプルに（β）さんがおっしゃっている

 C1 : =A1
 C2 : =IF(A2="",C1,A2)
に更に作業列をD列に追加して、
 D1 : =IF(ISERROR(FIND("k",B1)),"",C1)
でコピー。

D列の空白以外をフィルタするっていうことではだめなんでしょうか。
大項目の重複は重複の削除でとれますし。

（chiki） 2016/06/17(金) 10:35

 作業列を使う。

 C1セル
 =IF(COUNTIF(B1,"*K*"),ROW(A1),"")

 C2セル
 =IF(COUNTIF(B2,"*K*"),IF(MATCH(1,INDEX(0/(A$1:A1<>""),0),1)=IFERROR(LOOKUP(10^16,C$1:C1),""),"",MATCH(1,INDEX(0/(A$1:A1<>""),0),1)),"")
 C2セルを下へフィルコピー。

 D1セル
 =IFERROR(INDEX(A:A,SMALL(C:C,ROW(A1))),"")
 D1セルを下へフィルコピー。

 作業列を使わない式も組み立ててみたが7000件くらいの量でデータを一行変更するたびに再計算に1・2分かかってしまった。

 たぶん数十行（から数百行？）程度であれば使い物になるだろう。
（ねむねむ） 2016/06/17(金) 10:49

 7000行あるなら、あきらめて（？）マクロ処理がいいかも。

 Sub Test()
    Dim c As Range
    Dim dic As Object
    Dim cat As String
    Set dic = CreateObject("Scripting.Dictionary")
    Columns("C").ClearContents
    With Range("A1").CurrentRegion
        For Each c In .Columns("B").Cells
            If Not IsEmpty(c.Offset(, -1)) Then cat = c.Offset(, -1).Value
            If InStr(c.Value, "k") > 0 Then dic(cat) = True
        Next
    End With
    If dic.Count > 0 Then Range("C1").Resize(dic.Count).Value = WorksheetFunction.Transpose(dic.keys)
 End Sub

（β） 2016/06/17(金) 12:03

β様、chiki様、ねむねむ様

皆さま貴重なお時間を割いての回答をありがとうございました。
β様には三度も書き込んでいただき、そのうえマクロまで組んでいただきました。
chiki様には関数だけで何とかしようと凝り固まっていた頭に柔軟性を示唆していただきました。
また、ねむねむ様の関数は私にはとてもエレガントに映りますが私には勉強不足でまだ完全理解できないでいます。β様のマクロも同様に見たことのないコマンドばかりで、正直、自分の非力さを痛感しています。まことに申し訳ないのですが現状では私にはchiki様のご提案が一番理解できております。当座はこちらを使って行きたいと思います。これから勉強して、ねむねむ様の関数、β様のマクロを理解できるようになったらこちらを使わせていただきます。
皆さま本当にありがとうございました。ありきたりな感謝の言葉で申し訳ないです。

（hassann） 2016/06/18(土) 02:45

コメント返信：

[ 一覧(最新更新順) ]

[[20160616205153]] 『1対多対応で多のほうに検索をかけて１の方を表示ａx（hassann） ページの最後に飛ぶ

[[20160616205153]]　『1対多対応で多のほうに検索をかけて１の方を表示ａx（hassann）　ページの最後に飛ぶ