[[20170226215630]] 『マクロ処理 フィルターオプション一発■』(マリオ) >>BOT

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

 

『マクロ処理 フィルターオプション一発■』(マリオ)

 元トピックは、こちら
[[20170226132644]]
 『条件に一致するセル位置を取得する方法について』(keikei757)

 このトピは、マクロのフィルター処理が、どようなものなのかを検証するためのものです。
 どのようなコートを書いたらいいか、ご教授ください。

 ■必須条件
 シート関数を使用せずに、マクロのWorksheet_Changeイベントを使用して次のやりたいことを実現したい。
 なお、コードには、フィルターオプションを使用すること。

 ■やりたいこと
 下記の表1を使用して、下記の表2の結果を得たい(結果を表示する場所は、★別シートでもよい)。
 表2は、表1において、
 下記の検索条件1、および検索条件2に合致する★「件数」と、★「一致行」及び★「一致タイトル」を表示しています。

 検索条件1:B列の3行目以降のステータスが「公開」であること。
 検索条件2:C列の3行目以降のURLが「http://www.yahoo.co.jp/」を含むこと
 (ただし、下記表で、A5は空白ですが、サンプルBの文字列が入っているものとして取り扱う。)
 (ただし、下記表で、B5は空白ですが、非公開の文字列が入っているものとして取り扱う。)
 (以下、空白である部分は、同様に取り扱う。)

 ■取り扱うデータ(表1)
    |[A]      |[B]       |[C]                                    |
 [2]|タイトル |ステータス|URL                                    |
 [3]|サンプルA|公開      |http://www.yahoo.co.jp/                |
 [4]|サンプルB|非公開    |http://news.yahoo.co.jp/pickup/6231286 |
 [5]|         |          |http://www.yahoo.co.jp/                |
 [6]|サンプルC|公開      |http://www.yahoo.co.jp/                |
 [7]|         |          |http://news.yahoo.co.jp/pickup/623128  |

 ■結果(表2)
    |[D]   |[E]   |[F]         
 [2]|一致数|一致行|一致タイトル
 [3]|     2|     3|サンプルA   
 [4]|      |     6|サンプルC   
 [5]|      |      |            
 [6]|      |      |            
 [7]|      |      |   

< 使用 Excel:Excel2013、使用 OS:Windows10 >


 このレイアウトをフィルターオプションで処理するには、1発ではなく
 前後に
 空白領域へのデータ埋め込みと埋め込んだデータのクリアの3発になりますが、可能です
 ただし、マリオさんが関連トピでアップしたように 文字列の後にスペースが、こっそり(?)つけられているとか
 空白セルに見えるのに、スペースが入っているという『罠』はなしですよ。

 それと、関連トピの要件ではなかったのにマリオさんが追加した、該当のものの行番号記載は、また別ですね。
 フィルターオプションではできませんので。

 それもやりたいですか?

 ただ、やはり、こだわりたいのは なぜイベント処理なのか???

 データって、一行だけをいれておしまいじゃなく、何かのエビデンスから 何行か追加することが圧倒的に多いと思いますよ。
 また、1行だけだったとして A列に入力し、次には B列に入力、最後に C列に入力しますよね。
 イベント処理だと、3回動きますけど?

 イベント処理の理由を聞かせていただければ、参考コードをアップします。

 あぁ、それはそれとして、このレイアウトで、フィルターオプションを使わないコード案、
 関連トピのマリオさんのコードを拝見した後ちょこっと書いたものがあります。
 ご参考までに以下。

 Sub Sample1()
    Const URL As String = "http://www.yahoo.co.jp/"
    Dim r As Range
    Dim c As Range
    Dim v As Variant
    Dim x As Long
    Dim d1 As String
    Dim d2 As String

    Set r = Range("C3", Range("C" & Rows.Count).End(xlUp)).EntireRow.Columns("A")
    ReDim v(1 To r.Rows.Count, 1 To 2)
    For Each c In r.Cells
        If Not IsEmpty(c) Then d1 = c.Value
        If Not IsEmpty(c.Offset(, 1)) Then d2 = c.Offset(, 1).Value
        If d2 = "公開" And c.Offset(, 2).Value Like "*" & URL & "*" Then
            x = x + 1
            v(x, 1) = c.Row
            v(x, 2) = d1
        End If
    Next
    Range("E3:F" & Rows.Count).ClearContents
    Range("D3").Value = x
    If x > 0 Then Range("E3").Resize(x, 2).Value = v

 End Sub

( β) 2017/02/26(日) 22:12


 追伸

 関連トピで質問者さんの提示にはなかったのですが、マリオさんが タイトル行を2行目に設定しましたね。
 (今回もそうなっていますが)

 エクセル処理を簡単に効率的に行おうとするとレイアウトデザインがキモになりますが、マクロ処理であれ
 操作であれ、A列から始まる1行目がタイトル行 というものが最も面倒がなくなります。
 (領域定義に余計な神経を使わずに済みますので)

 あえて2行目にしているのは何か理由があるのですか?
 それとも、マリオさんの趣味ですか?

( β) 2017/02/26(日) 22:49


 こんばんわ。

 敢えてしてるのでしたらすいません。

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

 元トピでは数式中にCHAR(10)とレイアウトはセル内改行とはっきり分かりますけど、
 マリオさんの提示のレイアウトでは2行になっていますが、
 これは元トピのようなレイアウトなら、フィルターオプションのコードを書くのに何の問題も無いけど、
 このレイアウトみたいな場合はどのように書いたらいいか分からない。
 と言う事でしょうか?

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


 関連トピでは セル内改行の件を、マリオさんあてコメントしましたので、それはそれとして理解され、
 このトピは、マリオさんが想定したレイアウトの場合に、どのような処理ができるかという問いかけかと
 思います。(ですよね? マリオさん)

 ただ、現れた行番号表示は、いってみれば、マリオさんの【後出しジャンケン】(?)のようなもので
 これが必須なら、D列あたりに行番号をセットしておいて、そこも抽出ということになるんでしょうね。

( β) 2017/02/26(日) 23:18


 >これが必須なら、D列あたりに行番号をセットしておいて、そこも抽出ということになるんでしょうね。

 >■必須条件
 >シート関数を使用せずに、

 関数が使えればまだMATCHなどで簡単にセット出来るけど、関数不可ならβさんの案が一番ベターでしょうね。

 何か無理に難しい条件を作ってるけど、実際の帳票や仮にシステムなんかでも、
 こんなに制約だらけの要件をこなさないといけない場合ってあるのかなぁ?

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


 とりあえずイベント処理ではなく標準モジュール版です。
 アップした Sample1 に比べ、コード量が やや多めに感じるかもしれませんが、ほとんどが
 マリオさんの無茶ブリ(?)要件のための補正処理です。

 なによりも、データ量がそこそこある場合、コテコテループ処理に比べて、AdvancedFilterメソッドは圧倒的に
 処理効率が優れています。しかも 実質 1行で処理できます。
 機能的にもオートフィルターに比べて格段に柔軟かつ多様な条件設定ができます。
 また、あまり注目されていませんが、転記先に、前回の転記結果が残っていた場合、転記する件数が前回より少なかった場合に
 前回のものが残ってしまう。 なので、事前にクリアしておく なんてことを通常やるわけですが、フィルターオプションでは
 その必要がありません。AdvancedFilterメソッドが、それをやってくれますから。

 なお、レイアウト、提示のものを、ちょこっと変えています。
 また、抽出すべきURLはコード内ではなく、セル上に記述します。このほうが、今度はこのURLを抽出したいといった場合に
 コード修正なしで処理できますからUIとしても、よろしいかと思います。

 フィルターオプションの抽出文字列の与え方は、いろいろあります。(ありすぎるくらいですが)
 ●●から始まるもの(前方一致)、●●を含桃の、●●と完全一致するもの 等々。
 以下のレイアウトサンプルは ●●から始まるもの という与え方にしています。

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

 で、コードです。

 Sub Sample2()
    Dim r As Range
    Dim b As Range

    Application.ScreenUpdating = False

    Set r = Range("C2", Range("C" & Rows.Count).End(xlUp)).EntireRow.Columns("A:D")

    '準備
    r.Value = Application.Trim(r)   'マリオさんのいじわるスペース対応
    r.Columns("D").Value = r.Row
    r.Columns("D").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
    r.Columns("D").Cells(1).Value = Range("H1").Value
    Range("F2").Value = Range("B2").Value
    Range("F3").Value = "'=公開"
    On Error Resume Next
    Set b = r.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not b Is Nothing Then b.FormulaR1C1 = "=R[-1]C"

    '抽出処理 これ1行
    r.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("E2:F3"), CopyToRange:=Range("H1:I1")
    Range("G2").Value = Range("H" & Rows.Count).End(xlUp).Row - 1

    '後始末
    If Not b Is Nothing Then b.ClearContents
    r.Columns("D").ClearContents
    Range("F2:F3").ClearContents

 End Sub

( β) 2017/02/27(月) 00:53


このトピは、マクロのフィルター処理が、どようなものなのかを検証するためのものです。

ども^^
頑張って勉強されてますね^^

でも、マリオさん、

「マクロ」とはなんでしょう?

いろいろなテクニックやコードを追いかけるのもいいですが、
ここらで一度立ち返って、そんなことを考えてみるのも悪くないと思います^^

答えを聞いてから次の話をしましょう^^

(まっつわん) 2017/02/27(月) 08:14


 まっつわんさんから、するどい切り口が提示されましたね。
 (まっつわんさんからは、私自身にも、同様の疑問を投げかけられることが少なくないです)

 まっつわんさんが投げかけられたことは、ある面では非常に重要かつ的を得ていることだと
 思いますが、『マクロとは何ぞや』という考え方にも左右されると思っています。

 以下は、マリオさんが、まっつわんさんの投げかけられた課題に対してコメントを返される
 その時の検討材料にでもしていただければ幸甚です。
 (ある意味、マリオさんへの応援コメントでもありますし、辛口コメントにもなるかもしれません)

 VBAがリリースされる前から、『マクロ』という考え方は存在していましたね。
 言語的には、今、我々が Excel4マクロ と呼んでいる、エクセル操作に特化した簡易コードを
 組み合わせたもの、もちろん、マクロの自動記録でも生成されました。

 これは、正真正銘の『マクロ』、シート上で定常的な操作を行う、毎回、セル領域を選択して
 メニューから操作を選んで『同じ一連の作業を行う』のが面倒だ。
 なので、この一連の操作を、『束ねて』記憶させておき、それを呼び出して、ワンタッチで
 一連の『エクセル操作』を完了させたい。

 そういうときに用いた、『操作を束ねた』、つまり『操作をマクロ化した』ものでした。

 一方、1990年に、MSがVBAを、『プログラミング・プラットフォーム』としてリリースしたわけです。
 これは、従来の『エクセル操作を束ねた動きをコード化する』ためのものではなく、
 あくまで『ふつうのプログラミングが可能』な、プラットフォームですね。

 もちろん、従来の『マクロ』という考え方も、このプラットフォーム上で、Excel4言語ではなく
 VBA言語を使ったコードとして生成する、あるいは準備しておくことで継承されています。

 ですから、

 ●従来のエクセル操作の便利ツールとしてVBAを利用したいという人にとっては、エクセルが持つ
  様々な強力な機能だけを用いながら組み立てるのがベスト。

 ●そうではなく、あくまで『プログラミング』として、プログラムを作っていきたい、その奥を極めたいと
  そう思っている人もいるわけで、そういう人にとっては、
 『たまたま、簡便なVBAというプラットフォームを使っているだけ』
  そういう観点でいえば、ゴリゴリと自前のコードでプログラムを組み立てるのも、非難されることではないですね。

 ●ただし、対象物がエクセルであるケースがほとんどでしょうから、エクセルが持つ効率的に処理できる機能(メソッド)を
  簡便に利用することができるなら、それにこしたことはないですね。
  (ただ、個人的な感想としては、たとえばピボットは操作としては非常に柔軟かつ強力ですけど、これをVBAコードで
   記述しようとすると、煩雑で、わかりにくくなる。ピボットを使うための、無理やりコードというもののオンパレード)

 ●おそらく、ここが、ポイントの1つでしょうね。生成されたコードはわかりにくくても、ピボット操作で自動生成されるので
  問題ないじゃないかと考える人と、あくまでプログラムコードとして可読性、保守性も含めたものとして考える人の違い。

 ●別の言い方をすれば、マクロ記録でできる処理手順なら、まず、それを使うべき。
  それでは足りないところのみを補足的にVBAコードで埋める。そう考える人もいれば、
  最初から、テーマに適したコードを組み立てる。その中には、部品としてマクロ記録で生成されたものも使う。
  こう考える人もいる。前者が正しいアプローチだとはいいきれないということだと思います。

 ★一方で。
  Q/A掲示板にあがってくる質問は、今、困っている人、将来は困らないように上達したい人に、解決の糸口を与え
  その人が『将来』、ステップアップしていくお手伝いをする。これが本意だと思っています。
  その人が取り組んでいるコード処理等を全く無視して、様々な便利機能も組み込んだ、あたかも市販のソフト的なものを提示して
  だまって、これを使え という回答スタンスには ?? と首をかしげざるを得ないところもあります。
  (本トピは、マリオさんが、マリオさんのために建てたトピですから、これにはあてはまらないですけど)

( β) 2017/02/27(月) 10:24


>(まっつわんさんからは、私自身にも、同様の疑問を投げかけられることが少なくないです)
そんなことしましたっけ?(汗)

基本他の回答者の方に意見や批評をするつもりもありませんし、したつもりもありませんが
(ちょいちょいしてるのかなぁ^^;)、
まぁ、それぞれ考え方や感じ方の違いはありますし
言葉として表現も違ってるかも知れませんが、
「思いを伝える」ということがプログラミングの練習だと思っているので、
いろいろ思ったことを機会があるたびに書いてはいます。

>マクロ処理 フィルターオプション一発
>マクロのフィルター処理が、どようなものなのか

この辺の発言において、「マリオさんは「マクロ」ってどういうイメージなのかなぁ。。。」と、
興味が出てきたので聞いてみたいと思いました。

βさんからVBAの歴史について簡単にお話がありますのでそちらも参考にして、
ぜひ、「自分の言葉」で表現してみてください^^
もちろん、Webで検索しまくって答えを探すのは大いにありです。
(むしろ、そうやって自分なりの答えを見つけて欲しいです。)

返事があれば答え合わせをしてみましょう^^

(まっつわん) 2017/02/27(月) 11:06


あ、僕が、一番に引っかかるところを書いてませんでした。。。。

僕の中では・・・
あ、でもそれは答え合わせをしてからでいいですか。。。

(まっつわん) 2017/02/27(月) 11:09


 >βさん

 3発ですか(^^♪ サンパツ  『罠』対応は、こだわりです。
 ■Not IsEmpty(c) Then→Len(Trim(c)) <> 0 Then にしないと…(^^♪

 >なぜイベント処理なのか???
 シート関数に対抗心があったので(^^♪
[[20170226132644]] で、βさんが、
 >Set sh = ThisWorkbook.ActiveSheet
 >これは、コードを見た人を混乱させるだけ。
 とありましたが、そもそも、コードはmoduleに書いてました。
 それを無理やり、シートのコード記述欄に持ってきたのです。
 「Set sh = ThisWorkbook.ActiveSheet」は、その名残です。
 意地悪ですね。分かってるくせに(^^♪

 しかも、
[[20170226132644]] のWorksheet_Changeコード(マリオ) 2017/02/26(日) 18:23 ですが、
 If Not myDic.exists(c.Row) Then
 は不要だし(^^♪なぜなら、Forループ内で、c.Row(行番号)が重複することは絶対にないから。
 「重複してなければ辞書登録というロジック」を使ってないなら
 辞書使う必要は、まったくなかったです。

 *******************************************************
 βさんのSample1ですが、
 v配列でシートへの書き込みを1回で済ませてるので、高速ですね。
 コーディングの好みですが、moduleにコード書くなら、
 シートは指定しておきたいとは思いますが…。
 Range("D3").Value = x
 は、アクティブシートに対しての処理ですもんね。

 Set r = Range("C3", Range("C" & Rows.Count).End(xlUp)).EntireRow.Columns("A")
 の書き方は、いいすね。

 Range("C3", Range("C" & Rows.Count).End(xlUp))→ex.)「C3:C7」
 「.EntireRow」を付けると、ex.)「A3:XFD7」
 さらに、「.Columns("A")」をつけているので、ex.)「A3:A7」
 みたいな感じですね。

 ReDim v(1 To r.Rows.Count, 1 To 2)
 では、多めに配列を用意してますね。r.Rows.Countは、この場合5
 前にもこの点で話し合ったことあったと思いますが、
 何度もReDim Preserveして、配列の上限を変えるより
 はじめに、多めに配列を用意しておいた方が、
 処理速度的には優れてるんでしょうね。

 Range("E3").Resize(x, 2).Value
 の書き方は、いいすね。xは、この場合2

 ここまで、読んでみて、そういえば、このトピは、
 >このトピは、マクロのフィルター処理が、どようなものなのかを検証するためのものです。
 として立てたことを思い出しました。
 本件事案で、フィルター処理を使うコードを、いくつか試してみたいと思います。

(マリオ) 2017/02/27(月) 11:11


 >βさん
 >A列から始まる1行目がタイトル行 というものが最も面倒がなくなります。
 >あえて2行目にしているのは何か理由があるのですか?
 >それとも、マリオさんの趣味ですか?

 私が、エクセルで作る書類で、2行目とか5行目とか、
 タイトルを1行目にしてないものが多いので(^^♪

 フィルター処理をするコードは、タイトルが1行目で、
 データが2行目以降であるデータに対して、有効だったような…。
 うる覚え…。だから、★あえて、タイトルを2行目にしているんです。
 シートをコピーして、タイトル行を1行目にしてから、フィルター
 処理する手間が増えると思いますが…。

(マリオ) 2017/02/27(月) 11:24


 >フィルター処理をするコードは、タイトルが1行目で、
 >データが2行目以降であるデータに対して、有効だったような…。
 >うる覚え…。だから、★あえて、タイトルを2行目にしているんです。

 タイトルが何行目であってもフィルター処理(オートフィルター、フィルターオプション)は可能ですよ。
 ただ、タイトルがA列から始まる1行目であれば、リスト領域を Ramge("A1").CurrentRegion で簡単に指定できるという
 それだけです。

 でも、この領域指定が、簡単で紛れがない、そういうシートレイアウトをデザインするというところから
 処理プログラムは始まっていると、そう思いますね。

 シート修飾は、お説の通り。逆に、私が回答するレスでも、ちょこちょこ指摘はしています。
 複数シートを相手にする処理では必須ですね。

 ただ、一般的に、質問に上がるケースは、対象シートをアクティブにした状態で
 処理するといったものが多いので、まず、物事の軽重から、処理ロジックそのものの
 お手伝いをする、その先に、必要ならシート修飾の話もする という手順ですかね。

 スペースの罠、実は、けっこうありますよね。実務では。
 データが、取引先から送られてきたような場合、この罠で、あれこれ悩んで質問してくる人も少なくない。

 ただ(これも、先に★でコメントしたことにつながりますけど)それって、一般的な質問者さんにとっては二の次。
 まず、質問者さんの質問ポイントにフォーカスして手伝い、解決後に、ところで・・
 と、参考情報としてアップすべきことなんだろうなと。

 たとえば、

 『シートの中に何か所か "金額" と入っているセルがあります。その右隣りのセルに金額があるので、それをリスト化したい』

 こんな質問があって、たとえば Findメソッドを使って処理するとして、もし、
 スペースの罠を考慮すると、処理前に、シート全体をTrimしなければいけない?
 あるいは、部分一致で "金額" を捜した後、見つかったセルをTrimして "金額"と完全一致しているかを
 調べなければいけない?

 そういったコードって見たことありますか?

 要は、まず、何が重要か、次に、例外として、時々おかしな状態のデータがあるとすれば
 そこを 例外処理として追加する。
 そういった 軽重 のテーマであって、なんでもかんでも、これでもかというくらいに
 あれこれちりばめると、質問者さんの質問テーマを解決するのに、根本的には
 どうしなければいけなかったのかがわからないまま、でもマリオさんのコードを動かすとできた!
 ありがとうございました ということになって 質問者さんのレベルアップには
 ほとんど役に立たないことになる恐れがあるということを心配しています。

(β) 2017/02/27(月) 11:47


 >syさん
 シート関数に注目してなかったので、CHAR(10)を使用していることに
 今気づきました(^^♪CHAR(10)で、「改行のLf」文字に変換、ですね。 
 芸人たむけんのギャグに「チャー」ってありましたけど、
 CHAR(10)は、何て読むんですかね?

 書きなれてない、フィルター処理のコードを練習したいってだけです。
(マリオ) 2017/02/27(月) 12:14

 >β さん

 >関連トピでは セル内改行の件を、
 セル内改行を見落としていました。

 >ですよね? マリオさん
 はい、レイアウトは、関連トピのものではなく、上記トップのものを
 使用していきたいと思います。

 >現れた行番号表示は、いってみれば、マリオさんの
 >【後出しジャンケン】(?)
 別トピですからね。後だしではないと思いますが。
 行番号表示もしたいです。
(マリオ) 2017/02/27(月) 20:11

 >sy さん
 こんなに制約だらけの要件をこなさないといけない場合ってあるのかなぁ?
 ないでしょうね。
(マリオ) 2017/02/27(月) 20:14

 >後だし

 本トピのことではないですよ。
 本トピは、あくまで、最初にマリオさんがアップしたレイアウトですね。

 後出し は、関連トピのことで、質問者さんのテーマであれば、フィルターオプションで、さらっとでき
 質問者さんも、『今後の武器』を1つ手に入れて、解決 というはずだったんですが、マリオさんから
 さらに、こんな情報あったらいいだろう という『親ごころ』で追加レイアウトが提示され、あたかも、
 回答するなら、このレイアウトを基本にしましょう なんて流れになって、課題があやふやになったというあたりを
 指しています。

 ところで、マリオさんのレイアウトをフィルターオプションで処理するコードを
 Sample2 としてアップしてますが、いかがでしたか?

(β) 2017/02/27(月) 20:17


 >β さん
 今、Sample2を見てるのですが…。
 【フィルターオプション(AdvancedFilterメソッド)】と【オートフィルター】は、違うのですね。

 >●●を含桃の
 「●●を含むもの」ですよね。

 Sheet1に、次のレイアウト(■表1)を記述後に、ベータさんのSub Sample2()  「( β) 2017/02/27(月) 00:53 」
 のコードを走らせてみたのですが、

 実行時エラー 1004
 抽出した範囲にはフィールド名がないか、または無効なフィールド名です。
 となり、コードの
 r.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("E2:F3"), CopyToRange:=Range("H1:I1")
 のところが、黄色くなって、レイアウトが表2のようになります。何がいけないのでしょうか?

 ■表1
     |[A]      |[B]       |[C]                                   |[D]|[E]                    
 [1]|         |          |                                      |   |抽出用                 
 [2]|タイトル |ステータス|URL                                   |   |URL                    
 [3]|サンプルA|公開      |http://www.yahoo.co.jp/               |   |http://www.yahoo.co.jp/
 [4]|サンプルB|非公開    |http://news.yahoo.co.jp/pickup/6231286|   |                       
 [5]|         |          |http://www.yahoo.co.jp/               |   |                       
 [6]|サンプルC|公開      |http://www.yahoo.co.jp/               |   |                       
 [7]|         |          |http://news.yahoo.co.jp/pickup/623128 |   |                       

 ■表2
    |[A]      |[B]       |[C]                                   |[D]|[E]                    |[F]       
 [1]|         |          |                                      |   |抽出用                 |          
 [2]|タイトル |ステータス|URL                                   |  0|URL                    |ステータス
 [3]|サンプルA|公開      |http://www.yahoo.co.jp/               |  3|http://www.yahoo.co.jp/|=公開     
 [4]|サンプルB|非公開    |http://news.yahoo.co.jp/pickup/6231286|  4|                       |          
 [5]|サンプルB|非公開    |http://www.yahoo.co.jp/               |  5|                       |          
 [6]|サンプルC|公開      |http://www.yahoo.co.jp/               |  6|                       |          
 [7]|サンプルC|公開      |http://news.yahoo.co.jp/pickup/623128 |  7|                       |          
(マリオ) 2017/02/27(月) 20:50

お邪魔します。
マクロの前に、まずは手作業でフィルタオプションの操作を勉強してはいかがでしょうか。
そうすれば、コードの意味がわかると思います。
なぜ、皆さんが、前トピでフィルタオプションを推奨したかも。

(マナ) 2017/02/27(月) 20:59


 >まっつわん さん
 >「マクロ」とはなんでしょう? 

 なぞなぞですね。

 拡張子がxlsmまたは、xlsで、VBAのコードが記述されたブック
 って感じのイメージなんですが…。
 マクロブックって、言いませんか?
 「マクロ=プログラム」みたいなイメージですが…。
 違いますかね?
(マリオ) 2017/02/27(月) 21:21

 >抽出した範囲にはフィールド名がないか、または無効なフィールド名です。

 『なお、レイアウト、提示のものを、ちょこっと変えています。』とコメントしましたが
 そこはよろしいですか?

 特に I1。マリオさんのレイアウトでは G1 に 一致タイトル となっているところ。
 場所だけではなく タイトル文字列 を タイトル にしています。

 いやだ! 一致タイトルのままがいい ということなら、そうしますけど。
 また、レイアウト変更もいやだ! 最初に提示したレイアウトのままで処理したい!! ということなら、それも対応しますけど? 

 ただ、何度かコメントしているように、テーマからフィルターオプション処理を思い浮かべる --> それに適したレイアウトをデザインする。

 プログラミングというものは、そのレイアウトデザインからスタートしていると思います。
 もちろん、レイアウトが取引先から送られてきて、そのあたりが全く考慮されていないレイアウトという場合もありますけどね。
 そういう場合は、それなりに工夫しながら、でも「フィルターオプション」でしょう。

(β) 2017/02/27(月) 21:32


 > 「マクロ=プログラム」みたいなイメージですが…。
 > 違いますかね?

 ( β) 2017/02/27(月) 10:24 で、まっつわんさんの問いかけに対する参考メモをアップしたつもりでしたけど
 参考にならなかったようですねぇ・・・・・・・

(β) 2017/02/27(月) 21:35


 >β さん
  ( β) 2017/02/27(月) 10:24 を今読みました。
 マクロは、「エクセル自身が、操作手順をコード化したもの」って感じですかね?

 >●別の言い方をすれば、マクロ記録でできる処理手順なら、まず、それを使うべき。
 >それでは足りないところのみを補足的にVBAコードで埋める。そう考える人もいれば、
 >最初から、テーマに適したコードを組み立てる。その中には、部品としてマクロ記録で生成されたものも使う。
 >こう考える人もいる。前者が正しいアプローチだとはいいきれないということだと思います。

 わたしは、前者をとる場合と、後者をとる場合と、まちまちですね。

 >★一方で。〜。
 おっしゃていることは、分かります。
 コーディング作業をしていると、質問者本位じゃなくなっていることに、
 自分自身で気づくことも多いです。
 質問者の意向から、ずれてるな〜と思いながら、書き込みしたりしてましたが、
 ちょっとズレテルナと思ったら、別トピをたてるべきでしたね。
(マリオ) 2017/02/27(月) 21:42

 >1004

 あのぉ・・・

 >>Sheet1に、次のレイアウト(■表1)を記述後に、ベータさんのSub Sample2()  「( β) 2017/02/27(月) 00:53 」

 つまり、私が提示したレイアウト(A〜I)とは異なるレイアウト(A〜F)で実行したんですね?
 それであれば、エラーになるのは当たり前では?
 

(β) 2017/02/27(月) 22:01


 >β さん

 >『なお、レイアウト、提示のものを、ちょこっと変えています。』とコメントしましたが
 >そこはよろしいですか?

 よろしくなかったです。

 G1=一致数
 H1=一致行
 I1=タイトル

 は必須みたいですね。これも、レイアウトの一部ですよね?

 Sample2が、どういうコードなのかを、今から、見ていきたいと思います。
 とりあえず、G1,H1,I1に文字を入れたら、Sample2が走ってくれたことを今確認しました。

(マリオ) 2017/02/27(月) 22:06


 もし、A〜F のレイアウトで、それを元に、必要な 一致数、一致行、一致タイトル を生成するということなら
 それも、もちろん可能ですよ。もちろん『フィルターオプション』で。

( β) 2017/02/27(月) 22:09


 >まっつわん さん

 >「マリオさんは「マクロ」ってどういうイメージなのかなぁ。。。」と、

 単に、「マクロ=プログラムのコード」として捉えてました。

 「マクロ=エクセル自身が操作をコード化したもの」として、
 「個人が手作業でコーディングしたもの」とは別のものとして
 捉えた方がいいんですかね?
(マリオ) 2017/02/27(月) 22:15

 >マナ さん

 >マクロの前に、まずは手作業でフィルタオプションの操作を勉強してはいかがでしょうか。 
 本件で、手作業で、何をしたらいいかイメージが湧きません。
 βさんのSample2を、今、確認しています。

(マリオ) 2017/02/27(月) 22:45


> 本件で、手作業で、何をしたらいいかイメージが湧きません。

それは、フィルタオプションの機能を理解できていないからです。
何だかフィルタオプションの基本を通り越して
いきなり応用編にトライしているように思えます。

そういう勉強の仕方もあるのかもしれませんが…

(マナ) 2017/02/27(月) 22:52


↑つられて、ちぐはぐな返信してしまいました。

おそらくフィルタオプション自体を一度も使ったことがないのかなと思い
まずは、どんなものかマクロでなく、手作業で確認してはどうですか
ということで、必ずしも本件操作を手作業でという意味ではないです。

βさんのマクロも、手作業での操作をコード化しているだけですが。

(マナ) 2017/02/27(月) 23:13


 > 「マクロ=エクセル自身が操作をコード化したもの」として、
 > 「個人が手作業でコーディングしたもの」とは別のものとして
 > 捉えた方がいいんですかね?

 おそらく、問いかけはそのベクトルではなく、今から自分が書くコードは、エクセルシートを扱う処理を束ねた【マクロ】なのか
 そうではなく、純粋プログラムを書く必要があって、その中に、一部分 エクセルシートに対する処理も含まれるのか。

 そういう切り口だと思います。どちらをフォーカスしてコードを組み立てようとしているのか。
 後者だとしたら、本当に前者の考え方ではだめなのかと自問自答してみる。

 そうすれば、あぁ、エクセル上の手作業をコード化すればいいだけの処理なんだなというテーマかもしれない。
 その時には頭を切り替えて、使えそうなエクセル機能を思い浮かべながら『操作の組み立て』を行う。
 で、それをコード化していく。
 もちろん、それには、エクセルが持つ、数多くの機能(たとえばフィルターオプション)を熟知していなければ、思いつくこともできませんよね。

 なので、コードの勉強も重要ですけど、エクセルの機能そのものをもっと勉強しましょうと。

 そういうアプローチが、エクセルVBAでは、有効ではないだろうかと、そう 問いかけておられるような気がします。

 いずれにしても、マナさん指摘のように、ネットの解説ページも参考にしながら
 まず、基本的なフィルターオプション操作を体験してみたらいかがでしょう。
 で、次には、じゃぁ、こんな抽出はできないだろうかと、応用問題を自分で作って
 やはり『手作業』で、取り組んでみる。

 フィルターオプションを実現するコードはどう書くのかという興味は、ちょっとがまんして忘れて。

 そうすると、おぉ!フィルターオプションっておもしろい! すごい!
 ということで、きっと 好きになると思います。

 コードは、そのあとでも、充分に間に合いますよ、マリオさんなら。

( β) 2017/02/27(月) 23:40


お、僕がスナックで、500mlのペットボトルのジンジャエールを飲んで
2時間過ごして、17,000円払っている間に考えてくれましたね^^;

> 単に、「マクロ=プログラムのコード」として捉えてました。
> 「マクロ=エクセル自身が操作をコード化したもの」として、
> 「個人が手作業でコーディングしたもの」とは別のものとして
> 捉えた方がいいんですかね?

なるほど^^

ある意味、簡単に考えすぎです。
ある意味、難しく考えすぎです。

ぼくのイメージでは、

マクロとは、
「作業の自動化」です。

難しく考えなくても、
マクロの記録で記録された物を利用しようが、
1からコードでゴリゴリ書こうが、
例えば、毎日の定型の業務を自動化する=マクロ化
ではないですか?

マクロとは、関連する複数の操作や手順、命令などを一つにまとめ、 必要に応じて呼び出すことができるようにする機能のこと。

アプリケーションソフトでは、利用者が複数の操作手順を一連の手続きとして一つにまとめ、 自動的に実行できるようにする拡張機能をマクロという。 文書内の複数の個所や複数の文書に同じ操作を行わなければならない場合などに、 一連の操作をマクロとして登録しておけば、マクロを呼び出すだけで記録した操作を 自動的に実行してくれる。

>引用: IT用語辞典 e-Words (http://www.incept.co.jp/

簡単に考えすぎな面は、
>マクロ処理 フィルターオプション一発
↑これなんですが、

たとえば、マクロを使うひとは、
「クリック一発!で作業を終わらせたい。」と思うでしょうが、
「作り手側のマリオさん」が「一発」というのは、
すごく違和感があるのです。

つまりマクロ処理とは一連の作業の集合なのに、
一発(=一行または限りなくそれに近いコード)で、
表現しようというのはどういう事なんだろうと。。。
そして、もっというと、
コードで書くと1行のように見える、フィルターオプションなんですが、
これはもともとシート上で使うように用意されているものを、
VBAでもコントロールできるようにしてあるだけです。
なので、
抽出元のデータベース、条件の入力、抽出結果の表示
これらは、すべてシート上に事前に用意しておかなければならないのです。
つまり、

フィルターオプションを実行するためにその命令が実行できる前提条件
(抽出元のデータベース、条件の入力、抽出結果の表示)3つを揃えてから

○○.AdvancedFilter Action:=・・・・

としなければなりません。
なので、その点からも「一発」というイメージでフィルターオプションを
扱えないと思ったので、ちょっと、ちゃちゃ入れたくなりました。

マナさんからも指摘がありますが、
「フィルターオプション」ぜひ手動で使えるようになってください。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter3.htm

やってみるとわかると思いますが、
手動だと、
抽出条件をセルに入れなきゃいけないので、
ホント見た目が不細工です。
たぶん、マリオさんのセンスで行くとボツ、
できれば、VBAで同じ機能を自作したいと思うでしょう。

ただし、VBAでエクセル君を制御するなら、
見せたくないものは、見せなくて済みますし、
前提条件さえ揃えれば、
楽にそして高速に処理が行えます。
ぜひ操れるようになってください。

========================================
さて、ここで次の質問です。
作業をマクロにする意義はどこにあるでしょうか?
1つは、

1時間かかってた作業が数秒で終わる。=作業の高速化・効率化

ですが、
もう一つ意義があります。
それは、なんでしょうか?

(まっつわん) 2017/02/28(火) 09:10


> おそらく、問いかけはそのベクトルではなく、今から自分が書くコードは、エクセルシートを扱う処理を束ねた【マクロ】なのか
> そうではなく、純粋プログラムを書く必要があって、その中に、一部分 エクセルシートに対する処理も含まれるのか。
> そういう切り口だと思います。どちらをフォーカスしてコードを組み立てようとしているのか。
> 後者だとしたら、本当に前者の考え方ではだめなのかと自問自答してみる。
> そうすれば、あぁ、エクセル上の手作業をコード化すればいいだけの処理なんだなというテーマかもしれない。
> その時には頭を切り替えて、使えそうなエクセル機能を思い浮かべながら『操作の組み立て』を行う。
> で、それをコード化していく。
> もちろん、それには、エクセルが持つ、数多くの機能(たとえばフィルターオプション)を熟知していなければ、思いつくこともできませんよね。
> なので、コードの勉強も重要ですけど、エクセルの機能そのものをもっと勉強しましょうと。
> そういうアプローチが、エクセルVBAでは、有効ではないだろうかと、そう 問いかけておられるような気がします。

βさんもいろいろ考えてくれたようで、恐縮します。
僕なんかは、VBAを知れば知るほど、
一般機能に興味が湧いてきて、すべての機能を把握しているわけではないですが、
(ゴールシークとかシナリオとか、触ったことないですが^^;)
なんでこんな機能をだれも教えてくれないんだと、
Webを検索してたりしたんですが、

誰かがマリオさんに、「こんなマクロをお願い。」と頼んできたとします。
その時に、場面によって、
「エクセル君お願い」
「dictionary君、お願い。」
「正規表現君、お願い。」
と、他の誰かが書いてくれたプログラムを利用できると、
「自分が開発で楽が出来るよ」という話です。
元々エクセル君は優秀なソフトです。
利用できるものは利用しましょう^^
そういうアプローチが、エクセルVBAでは、有効ではないでしょうか^^
ですがそれはまた、別の話(←10:37に追記)

本題のテーマにもサンプルを作って言及してみましょうか。。。
(まっつわん) 2017/02/28(火) 09:41


アップ済みのSample2 をベースにして、結果としての G列:I列は、マクロ内で作り上げる。
最初は空白でもOK。

ただ、1つ変更しましょう。
Sample2 は、URL を指定して実行、公開 という抽出条件はコード内でコンスタントにしていましたが
このURLで公開のものを抽出してみようとか、非公開のものを抽出してみようと、操作者が任意に指定できるように
公開、非公開も操作者が入力して実行ということにしましょう。
以下の例では URLと同じく 前方一致 で抽出しますが、これもURL同様、完全一致 あるいは 含むもの といった入力は可能です。
また(これはフィルターオプションの機能ですけど)、抽出用のURLやステータスを空白にすれば、その条件についてはすべて抽出ということになります。

最初は以下のようなレイアウトです。
E3,F3 に操作者が抽出したいものを入力して実行します。

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

コードです。
Sample2とほとんどかわりません。

 Sub Sample3()
    Dim r As Range
    Dim b As Range

    Application.ScreenUpdating = False

    Set r = Range("C2", Range("C" & Rows.Count).End(xlUp)).EntireRow.Columns("A:D")

    '準備
    r.Value = Application.Trim(r)   'マリオさんのいじわるスペース対応
    r.Columns("D").Value = r.Row    '行場番号列の作成
    r.Columns("D").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
    r.Columns("D").Cells(1).Value = "一致行"
    On Error Resume Next
    Set b = r.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not b Is Nothing Then b.FormulaR1C1 = "=R[-1]C"
    '抽出結果のタイトル生成
    Range("G1").Value = "抽出結果"
    Range("G2").Value = "一致数"
    Range("H2").Value = r.Columns("D").Cells(1).Value   '一致行
    Range("I2").Value = r.Columns("A").Cells(1).Value   'タイトル
    '抽出処理 これ1行
    r.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("E2:F3"), CopyToRange:=Range("H2:I2")
    Range("G3").Value = Range("H" & Rows.Count).End(xlUp).Row - 2

    '後始末
    If Not b Is Nothing Then b.ClearContents
    r.Columns("D").ClearContents

 End Sub

(β) 2017/02/28(火) 11:25


シートを以下のように用意します。

「検索用」シート>>

            [A]                 [B]                       
      ┌──────┬────────────┐          
  [1] │ステータス:│公開                    │          
      ├──────┼────────────┤          
  [2] │URL:       │http://www.yahoo.co.jp/ │          
      └──────┴────────────┘          
  [3]                                                     
      ┌──────┬────────────┬────┐
  [4] │一致数      │行番号                  │タイトル│
      └──────┴────────────┴────┘

「リスト」シート>>

        [A]         [B]         [C]                                       
      ┌─────┬─────┬────────────────────┐
  [1] │タイトル  │ステータス│URL                                     │
      ├─────┼─────┼────────────────────┤
  [2] │サンプルA │公開      │http://www.yahoo.co.jp/                 │
      ├─────┼─────┼────────────────────┤
  [3] │サンプルB │非公開    │http://news.yahoo.co.jp/pickup/6231286  │
      ├─────┼─────┼────────────────────┤
  [4] │          │          │http://www.yahoo.co.jp/                 │
      ├─────┼─────┼────────────────────┤
  [5] │サンプルC │公開      │http://www.yahoo.co.jp/                 │
      ├─────┼─────┼────────────────────┤
  [6] │          │          │http://news.yahoo.co.jp/pickup/623128   │
      └─────┴─────┴────────────────────┘

「作業用」シート>>

        [A]         [B]       
      ┌─────┬────┐
  [1] │ステータス│URL     │
      └─────┴────┘
  [2]                         

  [3]                         
      ┌─────┬────┐
  [4] │行番号    │タイトル│
      └─────┴────┘

※フィルターオプションを使わなければ作業用シートは不要です。
(メモリー上で値の操作は出来るので)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngCriteria As Range
    Dim rngList As Range
    Dim rngResult As Range
    Dim vResult As Variant
    Dim iRow As Long
    Dim iCol As Long

    '抽出条件のセル範囲取得
    Set rngCriteria = Me.Range("A1:B2")

    'イベント処理するか条件分岐
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, rngCriteria.Columns(2)) Is Nothing Then Exit Sub

    'データベースのセル範囲取得
    Set rngList = Worksheets("リスト").Range("A1").CurrentRegion
    'データを綺麗にする
    SetDataCleanUp rngList

    '結果出力先セル範囲取得
    Set rngResult = Me.Range("B4:C4")
    Application.EnableEvents = False
    On Error Resume Next
    Intersect(Me.UsedRange, Me.UsedRange.Offset(4)).ClearContents
    On Error GoTo 0

    '抽出
    vResult = GetExtractedData(rngCriteria, rngList, rngResult)

    '結果の貼付
    iRow = UBound(vResult, 1)
    iCol = UBound(vResult, 2)
    rngResult.Offset(1).Resize(iRow, iCol).Value = vResult
    rngResult.Offset(1, -1).Resize(1, 1).Value = iRow
    Application.EnableEvents = True
End Sub

Function GetExtractedData(ByVal prngCriteria As Range, _

                          ByVal prngList As Range, _
                          ByVal prngResult As Range) As Variant

#Const cnsTest = 1 ' ←フィルターオプション:0 、ループ:1

#If cnsTest = 1 Then

    'ループ
    Dim v As Variant
    Dim i As Long
    Dim j As Long
    Dim r() As Variant
    Dim key1 As String
    Dim key2 As String

    key1 = prngCriteria(1, 2).Value
    key2 = prngCriteria(2, 2).Value
    v = prngList.Value
    ReDim r(1 To 2, 1 To UBound(v, 1))

    For i = LBound(v, 1) + 1 To UBound(v, 1)
        If v(i, 2) = key1 Then
            If v(i, 3) Like key2 & "*" Then
                j = j + 1
                r(1, j) = i - 1
                r(2, j) = v(i, 1)
            End If
        End If
    Next
    ReDim Preserve r(1 To 2, 1 To j)
    GetExtractedData = WorksheetFunction.Transpose(r)
#Else
    'フィルターオプション
    Dim ws As Worksheet
    Dim rngWork As Range
    Set ws = Worksheets("作業用")
    Set rngWork = ws.Range("A4:B4")
    '行番号追加
    With prngList
        .Columns(.Columns.Count + 1).ClearContents
        .Cells(1, .Columns.Count + 1).Value = "行番号"
        With .Columns(.Columns.Count + 1).Offset(1).Resize(.Rows.Count - 1)
            .Cells(1).Value = 1
            .Cells(1).AutoFill .Cells, xlFillSeries
        End With
        Set prngList = .Resize(, .Columns.Count + 1)
    End With
    '抽出条件変換
    With prngCriteria
        .Copy
        ws.Range("A1").PasteSpecial Transpose:=True
        Set prngCriteria = ws.Range("A1").CurrentRegion
    End With

    'フィルターオプション
    prngList.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=prngCriteria, CopyToRange:=rngWork

    '行番号を消す
    prngList.Columns(prngList.Columns.Count).ClearContents

    '結果を返す
    With rngWork.CurrentRegion
        GetExtractedData = Intersect(.Cells, .Offset(1)).Value
    End With
#End If

End Function

Sub SetDataCleanUp(ByVal Rng As Range)

    Dim aa As Range
    Dim a As Range

    With Rng
        .Replace " ", ""
        .Replace vbLf, ""
    End With
    On Error Resume Next
    Set aa = Rng.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If aa Is Nothing Then Exit Sub

    For Each a In aa.Areas
        With a
            .Rows(0).AutoFill Application.Range(.Rows(0), .Cells), xlFillCopy
        End With
    Next
End Sub

>(ただし、下記表で、A5は空白ですが、サンプルBの文字列が入っているものとして取り扱う。)
>(ただし、下記表で、B5は空白ですが、非公開の文字列が入っているものとして取り扱う。)
んと、簡易データベースとしてエクセルを使うときは、
こういうデータは許されません。
1行1件でちゃんと完結してなければなりません。
あと、表を2行目から作るのもどうかと思います。
たぶん見た目重視だろうと思いますが、、、、
こういうデータベース的なデータは変わったことをしないで、
愚直に素直な方が無難でしょう。。。(不都合はないとは思いますが。。。)
(テキストファイルに変換したら、頭の空白行は無視されるんですねー。
列はどうなんだろー。。。)

ほか、半角スペースなどの見えない文字も、
検索&抽出に邪魔になるので、排除します。

急いで作ったのであまり推敲はしてません。
無駄があったりするかも知れません。
元データが変わると不具合もあるかも知れません。

参考になれば。。。

(まっつわん) 2017/02/28(火) 13:46


 >β さん

 βさんのSample2、理解できました。「抽出処理 これ1行」以外のところの方が価値ありでした(^^♪、

 フィルタオプション(AdvancedFilter)とは、なんぞや?
 という視点でSample2を見ると…、
 下記表(★表101)に対して、Sample2bを実行しているわけですね(わざわざ記述する必要もないと思いましたが…)
 実行すると、「H2=3,H3=6,I2=サンプルA,I3=サンプルC」の値を取得

 Sub Sample2b()
    Dim r As Range
    Set r = Range("C2", Range("C" & Rows.Count).End(xlUp)).EntireRow.Columns("A:D")

    r.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("E2:F3"), _
    CopyToRange:=Range("H1:I1")
  End Sub

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

 ******************************************************************
 Sample2bの、
 〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓
 r.AdvancedFilter Action:=xlFilterCopy, _
   CriteriaRange:=Range("E2:F3"), _
   CopyToRange:=Range("H1:I1")
 〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓
    のところは、
    '■抽出元のセル範囲  (タイトルを含む、2行以上)                             ●AdvancedFilterの前のr
    '■抽出条件のセル範囲(タイトルを含む、2行)                                 ●CriteriaRange:=の後ろ
    '■抽出先のセル範囲   (タイトルを含む、1行)(抽出条件の列数と同じ列数)    ●CopyToRange:=の後ろ
    なんですね。

 ここまで、分かったところで、手作業をマクロの記録してみました。
    すると、
      Range("A2:D7").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "E2:F3"), CopyToRange:=Range("H1:I1"), Unique:=False
 と記録されました。

 Unique:=Falseが、既定値なので、省略しているのですね。

 フィルタオプション(AdvancedFilter)については、下記のURL先「よねさんのWordとExcelの小部屋」に詳しく載ってますね。

 Excel(エクセル)基本講座:フィルタ オプションの設定(データ抽出)
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter3.htm
 Excel(エクセル) VBA入門:フィルタオプション(AdvancedFilter)でのデータ抽出
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_advancedfilter.html

 ところで、Excel2013では、データtabの「並べ替えとフィルター」の項目の
 「詳細設定」を左クリックして、「指定した範囲」のラジオボタンを押すと、★sample2実行後は、
 「リスト範囲、検索条件範囲、抽出範囲」に既に値が入ってますが、
 これをすべてクリアにするには、マクロで、どう記述すれば、いいんでしょうか?
 クリアにしなくても、問題はなさそうですが…。

(マリオ) 2017/02/28(火) 14:49


 >マナ さん

  マナさんが、まずは手作業でのフィルタオプション(AdvancedFilter)を「マクロの記録」してみてはどうか
 とおっしゃてましたが…、順序が逆でしたね。遠回りしました。

 まだ、「(マナ) 2017/02/27(月) 23:13」 までしか、読んでません…。書込みが多いっす(+_+)
(マリオ) 2017/02/28(火) 14:51

 >まっつわん さん

 >お、僕がスナックで、500mlのペットボトルのジンジャエールを飲んで 
 >2時間過ごして、17,000円払っている間に考えてくれましたね^^; 

 それはそれは、ボッ…、ボイン?良心的なお店でしょうから、
 高麗人参エキスが入ってるジンジャーエールなんでしょうね。

 >ホント見た目が不細工です。 
 >たぶん、マリオさんのセンスで行くとボツ、 

 いやいや、元データがあるシートを「新規ブックにコピー」
 してから、フィルタオプション(AdvancedFilter)しますので、
 いいんです。不細工で。
 後で新規ブックの不細工な部分を消しますから。

 >作業をマクロにする意義はどこにあるでしょうか?
 >1つは、 
 >1時間かかってた作業が数秒で終わる。=作業の高速化・効率化 
 >ですが、 
 >もう一つ意義があります。 
 >それは、なんでしょうか? 

 例えば、そのマクロに少し手を加えれたコードを使用することで、
 決まったエクセル操作でも、【取り扱うセル範囲が異なる場合】に、
 対応できる点ですかね。
 それとも、自分でコードを考えなくていい点ですかね。

(マリオ) 2017/02/28(火) 15:31


 >> 「詳細設定」を左クリックして、「指定した範囲」のラジオボタンを押すと、★sample2実行後は、
 >> 「リスト範囲、検索条件範囲、抽出範囲」に既に値が入ってますが、
 >> これをすべてクリアにするには、マクロで、どう記述すれば、いいんでしょうか?
 >> クリアにしなくても、問題はなさそうですが…。

 マクロじゃなくても、操作でフィルターオプションを実行し、続いてまた条件を変えて実行するとします。
 そうすると、ダイアログに、前回の指定領域が表示されていますよね。
 なので、すべて再設定しなくてもいい。

 そのシートの名前の管理を見てください。

 Criteria だの Extract だの、自分で設定した覚えのない名前が登録されてますよね。
 この名前定義を参照して、前回のものをダイアログ上にセットしているんです。

 なお、リスト範囲は、前回のものを記憶していません。
 たまたま、操作実行時にアクティブセルが存在するCurrentRegion を候補として表示しています。

 エクセルには「小さな親切 大きな世話」という部分が少なくないのですが、まぁ、この場合は、大きなお世話というと
 エクセルがかわいそう? 小さな親切 という範囲で受け止めてあげてください。 

 目障りなら、Criteria だの Extract を 名前定義から削除してください。
 そうすると、でてこなくなります。

(β) 2017/02/28(火) 15:48


> それはそれは、ボッ…、ボイン?良心的なお店でしょうから、
> 高麗人参エキスが入ってるジンジャーエールなんでしょうね。
wwwwwwww

下手くそなギター弾き語って、おねーちゃんにおだててもらって、
気分よく帰れたからよしですわぁwwww

> 例えば、そのマクロに少し手を加えれたコードを使用することで、
> 決まったエクセル操作でも、【取り扱うセル範囲が異なる場合】に、
> 対応できる点ですかね。
> それとも、自分でコードを考えなくていい点ですかね。
意図が通じてませんね。。。^^;

マクロ化するってことは、
「操作者に間違った操作をさせない。」
ようにすることでもあるんです。
こちらの方が僕としては重要だと感じでます。

フィルターオプションなんかたまにいじると、
いつもエラーが出て、
「あれー?どうやるんだっけぇ???」っていつもイライラさせられます。
これをマクロにすると、間違えないし、ついでにいろんな編集も、
ボタン1回押下でやってくれる。
それがマクロのいいところですよね^^

でも、マクロにも悪いところがあって、
「元に戻す」機能が使えなくなってしまいますよね。
「元に戻す」はエクセルにはなくてはならない機能だと思うんですよねー。

最後にもう一つ。

「プロシージャ」とはなんぞや?

用語を突き詰めて考えてみると、VBAの世界が開けてくると思うんですよねー。。。。

(まっつわん) 2017/02/28(火) 15:58


 >β さん

 Sample2,Sample3、ありがとうございます。

 r.Value = Application.Trim(r)
 を自分のコードで取り扱う場合は、
 ★新規ブック(または新規シート、または別ブック、または別シート)
 でTrimするようにします。
 βさんのSample2,Sample3は、あくまでサンプルですので、
 元データをTrim処理することで、よいのですが。
(マリオ) 2017/02/28(火) 16:05

 >まっつわん さん

 (まっつわん) 2017/02/28(火) 13:46 のコードを今見ている
 途中ですが、コードを貼り付ける場所は、「検索用」シートのコード記述欄だけでしょうか?

 最近知ったのですが、
 Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Count > 1 Then Exit Sub
 End Sub
 を記述しているシートは、 
 xlsmファイルでは、エラーになる可能性がありますね。

 ★シートの全セルをコピーして、全セルに貼り付けると
 実行時エラー6,オーバーフローしました。になりますね。

 『勤務表 当直5人ランダムしたいです 別人』(トラ)
[[20170217080857]]
 の(マリオ) 2017/02/25(土) 11:37  で書いたことを下記に書きます。
 _
 _
 _
 ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
 ★Countプロパティ自体が、★Long型で宣言されているために起こるエラー
 ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
  Excel2007以降のエクセルを使用しているなら、
  Private Sub Worksheet_Change(ByVal Target As Range)に記述するコードの
 If Target.Count > 1 Then Exit Sub
 を
 If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then Exit Sub '★
 などに変更した方が、いい

     '〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓
     '詳細解説
http://excel2007.officetanaka.com/?eid=566684
    'Countプロパティ自体が、Long型で宣言されている!
    'If Target.Rows.Count > 1 Then Exit Sub
    'だと、例えば、シートの全セルをコピーして、全セルに貼り付けると、
    'Long型の許容範囲を超えるのでエラー(オーバーフロー)となる。

                'Long型の許容範囲は、2,147,483,647個(約20億個)
    'Excel2007以降は、全セルの合計は17,179,869,184個(約170億個)
                 'Excel2007以降は、行数は1,048,576個(約100万個)
                    'Excel2007以降は、列数は16,384個(約1万個)
    '〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓

(マリオ) 2017/02/28(火) 16:46


> xlsmファイルでは、エラーになる可能性がありますね。
なりますね。
なるけど、そんな広い範囲に一度に値を入れることってあります?

まぁ、万が一ですか。。。そこはマリオさんが取捨選択でよりよいと思うやり方を選択してください。
以後、なにか考えておきます^^
ご指摘ありがとうございます。

(まっつわん) 2017/02/28(火) 17:30


あぁ

> 'イベント処理するか条件分岐
> If Target.Count > 1 Then Exit Sub
> If Intersect(Target, rngCriteria.Columns(2)) Is Nothing Then Exit Sub

   'イベント処理するか条件分岐
   Set target = Intersect(target, rngCriteria.Columns(2))
    If target Is Nothing Then Exit Sub
    If target.Count > 1 Then Exit Sub

こんな感じでいいかな。。。
柔軟にいきましょう^^

(まっつわん) 2017/02/28(火) 17:37


 >なるけど、そんな広い範囲に一度に値を入れることってあります? 

 セル全体を選択してDeleteキーを押してしまうってことは、ありがちだと思いますね。

 そもそもが セル数を xl2007でど〜んと増やしたのに、Countプロパティが Long型のままというのは
 だれが考えても MSのチョンボでしょうね。
 これぐらい、すぐにパッチをあててくれればよさそうなんですけどね。

 ただ、アプリ側で この値を Long型で保持しているケースはありますね。
 なので、MSがパッチを当ててもアプリでずっこけるということは避けられないかぁ・・・

 まぁ、その時はその時でアプリ側のデータ型を変更せざるを得ないでしょうけど。

(β) 2017/02/28(火) 17:42


 > まっつわん さん
 
(まっつわん) 2017/02/28(火) 13:46 のPrivate Sub Worksheet_Changeの
 コードですが、私には難しいです。

 コード記述は、検索用シートのコード記述欄のみですよね?

 そもそも、結果がどこに出力されるんでしょうか?
 #If cnsTest = 1 Then
 の先頭についている「#」は、何なんざんしょ?
(マリオ) 2017/02/28(火) 19:45

> コード記述は、検索用シートのコード記述欄のみですよね?
そうです。
4行目のタイトルの下に出るはずですが。。。

>コードですが、私には難しいです。
コメントも書いてますし、
ブレークポイントを置いて、ステップ実行をしてどこを通るか、
また、変数の値がどうなっているか、
いちいち確認して、解読してください。
そんなに難しくないです。
勝手に難しく考えておられるだけだと思います。

> #If cnsTest = 1 Then
> の先頭についている「#」は、何なんざんしょ?

 #Const cnsTest = 1 ' ←フィルターオプション:0 、ループ:1

                  ↑
        ここの数字を
まぁ、気にせず、1か0かに書き換えて実行してみてください。

コンパイルの意味が解れば↓が参考に、
https://www.moug.net/tech/acvba/0050012.html
https://msdn.microsoft.com/ja-jp/library/tx6yas69.aspx

まぁ、実行するコードを切り替えるように書いているだけです。
いろいろ試してみて、疑問があったらドンドンきいてください。

つまり、VBAに無い命令や無い関数を自作しただけです。
で、AdvancedFilterに仕事を任せるように、
自分で書いたプロシージャに仕事を任せます。

(まっつわん) 2017/02/28(火) 21:27


> ┌──────┬────────────┐
> [1] │ステータス:│公開 │
> ├──────┼────────────┤
> [2] │URL: │http://www.yahoo.co.jp/
> └──────┴────────────┘
> [3]
> ┌──────┬────────────┬────┐
> [4] │一致数 │行番号 │タイトル│
> └──────┴────────────┴────┘

あぁ、
「ステータス:」「URL:」の「:」を消してください。
じゃないとフィールターオプションするときにコケますね^^;;
リストの方は付いてないので。。。^^;

コードも分かり難いということなら、もう少し見直したいなぁ。。。

(まっつわん) 2017/03/02(木) 09:26


 >まっつわん さん
 (まっつわん) 2017/02/28(火) 13:46 以降の書き込みについては、
 ちょっと、小休止中です(+_+)
 脳みそバーンです(寛平ちゃんのギャク)
(マリオ) 2017/03/02(木) 15:22

>ちょっと、小休止中です(+_+)

なるほど、じゃ、ちょっと横道に^^;

「プロシージャ」とは、、、

検索したら、こんな感じに書いてありますが、、、、
http://vba-code.net/basic/procedure/

グーグル翻訳してみます。
プロシージャ → procedure
反対にまた翻訳します。
procedure → 手順

手順 の同義語
名詞
作業, 手続, プロシージャ, 順序, 手続き, やり方
径路, 行き方, プロセス, 経過, 工程, 過程, 途方

こんな感じに出てきます。
つまり、マクロやプログラムは、
作業の手順を羅列した、作業手順書みたいなものと考えられます。
なので、こうやれば同じ結果がでますよと、
手順を書けばいいと思います。
もちろん、プログラミング独特のテクニックもありますが、
も少し肩の力を抜いて、
日本語で説明するように、VBAに向かわれたらいいかなと思いました。

(まっつわん) 2017/03/02(木) 18:40


コメント返信:

[ 一覧(最新更新順) ]


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