[[20170226132644]]　『条件に一致するセル位置を取得する方法について』（keikei757）　ページの最後に飛ぶ

『条件に一致するセル位置を取得する方法について』（keikei757）

条件に一致するセル位置を取得する方法について教えてください。

下記のようなデータが存在する場合

タイトル(A列) ステータス(B列) URL(C列)
サンプルA 、公開、http://www.yahoo.co.jp/
サンプルB 、非公開、"http://news.yahoo.co.jp/pickup/6231286
　　　　　　　　　　　　http://www.yahoo.co.jp/"
サンプルC 、公開、"http://www.yahoo.co.jp/
　　　　　　　　　http://news.yahoo.co.jp/pickup/623128"

※URL(C列)のみ改行された複数のデータが入ります

検索条件１：ステータスが「公開」
検索条件２：URLが「http://www.yahoo.co.jp/」を含む

次のような関数で条件に合致する件数を取得できますが、
条件に合致したタイトル(上記例で言うとサンプルA,サンプルC)を取得する方法がわかりませんでした。

=COUNTIFS(C:C,"*http://www.yahoo.co.jp/",B:B,”公開”)+COUNTIFS(C:C,"*http://www.yahoo.co.jp/"&CHAR(10)&"*",B:B,”公開”)

どなたか対応方法について教えていただけると助かります。

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

 苦手なのでベタベタの式ですけど。

 =IFERROR(INDEX(A:A,SMALL(IF(B$1:B$100="公開",IF(COUNTIF(C$1:C$100,"*http://www.yahoo.co.jp/*")>0,ROW(A$1:A$100))),ROW(A1))),"")

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

 式の中の 100 は、データ行数に対して十分に大きな値にしてください。

（ β） 2017/02/26(日) 15:03

 一応お聞きしますけど、
 フィルターオプション（フィルターの詳細設定）ではダメなんですかね？

 ところで、
 > =IFERROR(INDEX(A:A,SMALL(IF(B$1:B$100="公開",IF(COUNTIF(C$1:C$100,"*http://www.yahoo.co.jp/*")>0,ROW(A$1:A$100))),ROW(A1))),"")

 ↑ だと、C列に「http://www.yahoo.co.jp/」がどこかに1行でもあれば、
 あとはB列が「公開」にさえなっていれば、すべて抽出されませんか？

 勘違いでしたらすみません。
（笑） 2017/02/26(日) 16:37

 >> あとはB列が「公開」にさえなっていれば、すべて抽出されませんか？

 ひやぁ！！

 御意！

 To keikei757 さん

 私の回答は無視してください。ペコリ。

（ β） 2017/02/26(日) 17:29

 仕切り直しで、さらに、やぼったくなりますが、たといえば

 E1 : =IF(AND(B1="公開",COUNTIF(C1,"*AAA*")>0),A1,"")  これをふつうにEnterで。

 F1 : =IFERROR(INDEX(E:E,SMALL(IF(E$1:E$100<>"",ROW(A$1:A$100)),ROW(A100))),"")　これは Ctrl/Shift/Enter で。

 E1:F1 を下にズルズルフィルコピーすると、F列に結果がでますね、一応。

（ β） 2017/02/26(日) 17:45

 >keikei757 さん

 マクロを使用するなら、こんな感じでしょうか…。

 【マクロの概要】
 A列3行目～C列最終行のセル範囲で、変更があったら、
 D～F列を削除後に、結果を「D2:F4」に記述します。
 ただし、ﾃﾞｰﾀは、下記の表（タイトルが「A2:C2」で、データが「A3:C7」）
 のようになっているものとします。
 ■B4を「非公開」から、「公開】に変更したら、どう変化するか確認してください。

    |[A]      |[B]       |[C]                                    |[D]   |[E]   |[F]         
 [2]|タイトル |ステータス|URL                                    |一致数|一致行|一致タイトル
 [3]|サンプルA|公開      |http://www.yahoo.co.jp/                |     2|     3|サンプルA   
 [4]|サンプルB|非公開    |http://news.yahoo.co.jp/pickup/6231286 |      |     6|サンプルC   
 [5]|         |          |http://www.yahoo.co.jp/                |      |      |            
 [6]|サンプルC|公開      |http://www.yahoo.co.jp/                |      |      |            
 [7]|         |          |http://news.yahoo.co.jp/pickup/623128  |      |      |

 マクロは、使用しているシートのコード記述欄に記述してください。

 ■■■■■ ↓ココから
 Option Explicit

 '検索条件１：ステータスが「公開」
 Const status As String = "公開"
 '検索条件２：URLが「http://www.yahoo.co.jp/」を含む
 Const URL As String = "http://www.yahoo.co.jp/"
 'C列の開始行(タイトルを除く)
 Const sr As Long = 3

 Private Sub Worksheet_Change(ByVal Target As Range)
     If sr < 2 Then End
     Dim sh As Worksheet, fr As Long, myRng As Range
     Set sh = ThisWorkbook.ActiveSheet
     fr = sh.Cells(Rows.Count, "C").End(xlUp).Row 'C列最終行
     Set myRng = Range("A" & sr & ":C" & fr) '指定範囲
     If Intersect(Target, myRng) Is Nothing Then Exit Sub '指定範囲外なら終了

    '******* 情報を取得する **********************************************
     Set myRng = sh.Range(sh.Cells(3, "C"), sh.Cells(fr, "C"))
     Dim buf1 As String, buf2 As String, strA As String, strB As String
     Dim cnt As Long, c As Range, myDic As Object
     Set myDic = CreateObject("Scripting.Dictionary")
     For Each c In myRng
         If Len(Trim(c.Offset(0, -1))) <> 0 Then
            buf1 = c.Offset(0, -1): buf2 = c.Offset(0, -2)
         End If
         strB = buf1: strA = buf2
         If InStr(c, URL) > 0 And strB = status Then '一致したら
            '〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓
            'カウント
             cnt = cnt + 1
            '一致行、一致タイトルを辞書に登録
             If Not myDic.exists(c.Row) Then myDic.Add c.Row, strA
            '〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓
         End If
     Next c

    '******* D:F列の値を全削除、タイトルを書き込む ***********************
     sh.Columns("D:F").ClearContents
     sh.Range("D" & sr - 1) = "一致数"
     sh.Range("E" & sr - 1) = "一致行"
     sh.Range("F" & sr - 1) = "一致タイトル"
    '******* 取得した情報を書き込む **************************************
     sh.Range("D" & sr) = cnt
     Dim i As Long, myKey, myItem
     myKey = myDic.keys: myItem = myDic.items
     For i = 0 To UBound(myKey)
         sh.Cells(i + 3, "E").Value = myKey(i)
         sh.Cells(i + 3, "F").Value = myItem(i)
     Next
    '******* 後処理 ******************************************************
     sh.Columns("D:F").AutoFit '列幅自動調整
     Set sh = Nothing
     Set myRng = Nothing
     Set myDic = Nothing
 End Sub
 ■■■■■ ↑ココまで

（マリオ） 2017/02/26(日) 18:23

 サンプルを見る限り、そんな頻繁に更新されるようなものとも思えないし、
 フィルターオプションで十分なような気がしますが、

 一応、数式案。

 元データ、1行目が見出し、2行目からデータだとして、E列に抽出

 E2 =IFERROR(INDEX(A:A,SMALL(IF(($B$2:$B$100="公開")*ISNUMBER(FIND("http://www.yahoo.co.jp/",$C$2:$C$100)),ROW($A$2:$A$100)),ROW(A1))),"")

 Ctrl＋Shift＋Enter で確定し、下コピー

 範囲は実際のデータに合わせてください。
 サンプルと実際のレイアウトが違っていても、最後の ROW(A1) を変更しないように。

 ■作業列を使ってもいいのなら（D列：作業列、F列：抽出）

 D2 =IF(COUNTIFS(B2,"公開",C2,"*http://www.yahoo.co.jp/*"),ROW(),"")

 普通にEnterだけで確定し、データ最終行まで下コピー

 F2 =IFERROR(INDEX(A:A,SMALL(D:D,ROW(A1))),"")

 普通にEnterだけで確定し、必要なだけ下コピー
 これも最後の ROW(A1) を変更しないように。

 ＞F1 : =IFERROR(INDEX(E:E,SMALL(IF(E$1:E$100<>"",ROW(A$1:A$100)),ROW(A100))),"")
 ＞E1:F1 を下にズルズルフィルコピーすると、F列に結果がでますね、一応。

 本当ですか？
（笑） 2017/02/26(日) 18:54

 >>本当ですか？

 ひやぁ、ひゃぁの二乗です！！！

 テスト時 10行でやっていて、アップ時それらをすべて 100 にした際に　ROW(A1) の 1 まで 100 にしてました！

 F1 : =IFERROR(INDEX(E:E,SMALL(IF(E$1:E$100<>"",ROW(A$1:A$100)),ROW(A1))),"")

 ですね。

 度重なるご指摘深謝です。

（ β） 2017/02/26(日) 20:38

 To マリオさん

 keikei757 さんのコードから推察するに、おそらく各行は1行で、C列は　ＵＲＬ　がセル内改行されているのでは？

 で、いずれにしても、別列に展開する際には、必要なセル幅はちゃんと設定されているような気がしますけどね。
 (ふつうはそうしますよね。じゃないと、A:C も　列幅自動調整しなきゃいけない？）
 なので、AutoFit は余計でしょう。

 で、1行なので、(笑)さんの提言通り　マクロ処理ならフィルターオプション一発じゃないですか？

 仮に、マリオさんが想定したようなレイアウトだったとしても、Dictionary を絡ませる必要は
 ほとんどないのでは？
 あらかじめ配列をつくっておいて、そこに該当のものをセットしていき、そこからD列にどさっと転記すれば
 すむ話のようなきがしますが？

 それと・・・・いちゃもんではないですけど、本件はイベント処理をする性質のものではないのでは？

 いちゃもん（？）の続きですけど、もし　イベント処理のシートモジュールに書かれたコードなら
 Set sh = ThisWorkbook.ActiveSheet
 これは、コードを見た人を混乱させるだけ。
 シートモジュールですから、シート修飾なしの領域は、そのシートにきまってますから。

 あえて sh.なんたら　と記述したければ Me.なんたら　でしょ？

 無理やりイベント処理をしていることも含めて、単純に、さらっと処理できるものを
 難解なコードに仕立て上げるのは、いかがなもんでしょうかね？

（ β） 2017/02/26(日) 20:48

 >βさん

 >(笑)さんの提言通り　マクロ処理ならフィルターオプション一発じゃないですか？

 βさん、すいません。フィルターオプションを使用したマクロ、慣れてないのですが、
 どのようなコードを書いたら、いいか教えてもらえませんでしょうか？
 お願い致しますm(_ _)mペコ

 別トピを立ち上げました。
[[20170226215630]]　『マクロ処理 フィルターオプション一発■』（マリオ）
（マリオ） 2017/02/26(日) 22:00

 こんばんわ。

 >検索条件２：URLが「http://www.yahoo.co.jp/」を含む 
 この条件で言ったら皆さんの式で良いと思うんですが。

 質問者さんの式では、末尾に"がついているような場合はダメになっていますね。
 >=COUNTIFS(C:C,"*http://www.yahoo.co.jp/",B:B,”公開”)+COUNTIFS(C:C,"*http://www.yahoo.co.jp/"&CHAR(10)&"*",B:B,”公開”)

 どちらが本当なのか確かめた方が良いかも知れませんね。

 サンプル2が公開だった場合は、抽出するんですか？(ご提示の「検索条件２」に該当します)
 それとも抽出しない？(ご提示の式では抽出しません)

（sy） 2017/02/26(日) 23:05

コメント返信：

[ 一覧(最新更新順) ]

[[20170226132644]] 『条件に一致するセル位置を取得する方法について』（keikei757） ページの最後に飛ぶ

[[20170226132644]]　『条件に一致するセル位置を取得する方法について』（keikei757）　ページの最後に飛ぶ