[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『1対多対応で多のほうに検索をかけて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様のご提案が一番理解できております。当座はこちらを使って行きたいと思います。これから勉強して、ねむねむ様の関数、β様のマクロを理解できるようになったらこちらを使わせていただきます。
皆さま本当にありがとうございました。ありきたりな感謝の言葉で申し訳ないです。
(hassann) 2016/06/18(土) 02:45
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.