[[20170226132644]] 『条件に一致するセル位置を取得する方法について』(keikei757) >>BOT

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『条件に一致するセル位置を取得する方法について』(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列)のみ改行された複数のデータが入ります

検索条件1:ステータスが「公開」
検索条件2: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

 '検索条件1:ステータスが「公開」
 Const status As String = "公開"
 '検索条件2: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列は URL がセル内改行されているのでは?

 で、いずれにしても、別列に展開する際には、必要なセル幅はちゃんと設定されているような気がしますけどね。
 (ふつうはそうしますよね。じゃないと、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

 こんばんわ。

 >検索条件2: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が公開だった場合は、抽出するんですか?(ご提示の「検索条件2」に該当します)
 それとも抽出しない?(ご提示の式では抽出しません)

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


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.