オートフィルターとExcelマクロの連動方法について

今社内ツールとして、フィルターを掛けたデータをもとにグラフを作ろうとしています。
フィルター抽出とマクロを連動させる方法はあるでしょうか？
※worksheet_changeイベントでいけると思いましたが、セルを編集しないとダメでした。

流れは、
１、あるシート内データ（既にフィルターは設定されている）を任意の条件で抽出する。
（１で抽出したらそれを検知してマクロが動き）←これのやり方が分からない
２、データをカウントして別シートの表に転記（カウントロジックは別途作成予定）
３、２の表を元にグラフが勝手に更新

３については関数で表とリンクさせて表のデータが変われば（フィルターが操作されたら）グラフも連動するようにしたいです。
グラフ作成までマクロと考えていますが、修正対応のためなるべくシンプルな方法をとる予定でセル関数でもいいかと考え中です。

現場の要望がボタン（フォームコントロール）を使いたくない（フィルター操作だけで３まで実行されるような仕組みにしたい）のですが、方法が思いつきません。
全て関数だけでやれる気がしなくもないですが、２のカウント処理は（検知方法があれば）マクロの方が複雑にならない気がします（ただ、具体的にどうコードを組めばいいか分かりません）。

ご存知の方がいましたら、ご教授お願いします。

質問者からの補足コメント

• No1の方に最初にお返事頂いたので、その方をBAとさせていただきます。
  質問に付き合って頂き、有難うございます。

  No.5の回答に寄せられた補足コメントです。 補足日時：2022/01/09 22:33

• No1の方に最初にお返事頂いたので、その方をBAとさせていただきます。
  質問に付き合って頂き、有難うございます。

  No.6の回答に寄せられた補足コメントです。 補足日時：2022/01/09 22:34

No.7 ベストアンサー 回答者： mygoonickname 回答日時： 2022/01/09 22:01

No.1の者です。

例えばですが、Publicで変数を宣言、WorkbookOpenで、フィルターで表示
されているA列のデータ数を最初に変数に格納しておく。
Worksheet_Calculateで、フィルターで表示されているデータ数の比較を
行ない、データ数が変わってないならExitで抜ける。
実際に全てテストはできていませんが、下記の様な感じでどうでしょうか？

●標準モジュール
Public myFilCnt As Long

●Thisworkbook
Private Sub Workbook_Open()
フィルターのあるシートを選択した状態で、
myFilCnt = WorksheetFunction.Subtotal(3, Range("A1").CurrentRegion.Columns(1))
End Sub

●Sheet(フィルターのあるシート)
Private Sub Worksheet_Calculate()
Dim N As Long
N=WorksheetFunction.Subtotal(3, Range("A1").CurrentRegion.Columns(1))
If myFilCnt=N Then Exit sub’データ数が変わっていないなら、抜ける。
myFilCnt=N’新しいデータ数を保持
状態が変わっているので、処理を行う。
End Sub

この回答へのお礼

再三、有難うございます。
出来ました（質問している内に気づいた点がありますが、それはこちらで何とかします）。

お礼日時：2022/01/09 22:34

No.6 回答者： fujillin 回答日時： 2022/01/09 21:01

No3です

実態がよくわからないままですけれど・・・

＞Worksheet_Changeでカウントするコードを書く～
単にカウントするだけなら、No3にも記したようにSUBTOTALのCOUNTやCOUNTAで十分と思われます。

一方で、
＞運用中の関数が長すぎてマクロの方が簡単なのでは～
表示行をカウントするだけではないのでしょうか？
No1の補足の、
＞AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
を見ても、単に表示行数をカウントしているだけのように見えます。
・・というわけで、何が問題なのかさっぱり理解できません。
しかも、B2セルに単純に値をセットしているだけのようなので、B2に関数式を入れておけば、それで済む問題のようにも思えます。

まぁ、COUNTやCOUNTAでは空白セルが無視されますので、「表示中の空白セルもカウント対象とする」ような場合には少々面倒ですけれど・・・
フィルターを利用しているような場合で、そのようなケースはあまり想像はできませんけれど。
（必ず値のある列が存在すると想像しますので、その列をカウントすればすむ）

とは言え、表示行数をカウントしたところで、それだけでグラフになるとも思えないので、実態が不明のままではこれ以上は何ともわかりません。

この回答へのお礼

度々、有難うございます。
実はグラフには関係ないかもしれませんが、運用中の関数には（個人的に）長いと感じる（ifが入れ子になっていたり）ものがありました（大枠の記憶で質問しているので、関数だけで解決するかもしれません）。
しばし検討します。

お礼日時：2022/01/09 22:14

No.5 回答者： ママチャリ 回答日時： 2022/01/09 20:25

No.2です。

＞それではないです（フィルター操作が引き金です）。
それは、失礼いたしました。

ただ、「グラフのシートのWorksheet_Activateイベントプロシジャ」と書いたつもりなので、「フィルターのあるシートを選択した瞬間にマクロが実行される」には当たらないと思います。

Calculateイベントプロシジャみたいに、頻繁に発生するイベントに書いちゃうと操作が重くなってしまいそうなので、この案を提示させていただきました。もし、操作が重くなっても気にならない程度の処理であれば、この案は無視してください。

「ただ、～」の意味ですが・・・、
このツールの操作方法は、まず、フィルタをかけるシートで絞り込んだ後、グラフシートを選択して（アクティブにして）、グラフの状況を確認するはずです。なので、このタイミングでグラフが更新されれば良いと考えました。
しかし、もし別ウィンドウでグラフシートを表示しっぱなしにしているような通なユーザがいる場合、グラフシートをアクティブにすることが無いので、Worksheet_Activateが発生せず（グラフが更新されず）、「不具合だ」って言ってくる可能性があるということです。

この回答へのお礼

度々、有難うございます。

＞それは、失礼いたしました。
いえ、こちらの表現不足です。

＞～、グラフの状況を確認するはずです。
ですね。
仰られている通な使い方をされたら、動作は保証しないと言います。
もう暫し検討します。

お礼日時：2022/01/09 22:10

No.4 回答者： mygoonickname 回答日時： 2022/01/09 18:55

No.1の者です。

＞ブックを開くときはマクロの起動させないため
→これは、どの様な意味なのでしょうか？
マクロ無効や、イベントDisableにすると元に戻すタイミングが難しいかと。

例えばですが、
●Thisworkbook
Private Sub Workbook_Open()
ここで、フィルターの状態を保持
End Sub

●Sheet(フィルター)
Private Sub Worksheet_Calculate()
フィルターの状態を比較
状態が変わっているなら処理を実行。
状態が変わっていなければ処理を抜ける。（何もしない）
End Sub

この回答へのお礼

度々、有難うございます。

＞ブックを開くときはマクロの起動させないため
→これは、どの様な意味なのでしょうか？
A:方法を試したら、ブックを開いたら（閉じても）フィルター操作しなくてもcalculate（calculate自体は初めて知りました）が実行されたので、フィルター操作の時だけにするためでした（Nowが関連しているのでしょう、きっと）。

＞ここで、フィルターの状態を保持
→フィルターされている表をまるごと別シートに一時的にコピーしておくという意味でしょうか（で、比較するときはセルごとに比較）？
（こちらの都合ですが）大元のデータ表が行と列が多いので、セル１つずつ比較だと結構時間が掛かかる、ですね。。（比較のロジックは自分で工夫してよ、の話ですが）

お礼日時：2022/01/09 21:27

No.3 回答者： fujillin 回答日時： 2022/01/09 13:16

こんにちは

詳細が不明なので、はっきりとはしませんけれど・・・

きっかけ（トリガー）は、ユーザのフィルター操作という事でしょうか？

計算内容が「合計」や「カウント」程度であるなら、フィルターに連動できる関数（SUBTOTALやAGGREGATEなど）で計算可能だと思います。
それでまかなえる範囲の計算であればマクロは不要であろうと想像します。
グラフの元になる表さえ連動できれば、グラフ自体は自動的に表に連動しますので。

「そんな簡単な計算内容ではない」というのであれば、フィルタオプションを利用する形式にしておいて、
　　「オプションを変更すれば、フィルタ操作も含めてマクロで自動で行う」
というような方法も考えられそうです。
（オプション部分の変更で、Worksheet_Changeイベントが発生します）

「フィルタオプションも嫌」ということであるなら、既出の回答のように再計算が発生するような仕掛けをしておいて、Calculateイベントで拾うかでしょうか・・・

この回答へのお礼

お返事有難うございます。
きっかけはフィルター操作です。
仰る通りマクロはいらないかもと想像してました。
更に言うと既に別の人間が関数だけで運用しているツールがあるので、それを真似すればいいのですが、
自分がグラフに慣れてないことと運用中の関数が長すぎてマクロの方が簡単なのではと思っていました。

シートの構成としては、
Sheet1：データ元の表（インプット）
Sheet２：抽出後のカウントされた表データ（アウトプット）※フィルター操作の度に（結果が同じでも）更新
Sheet３：Sheet２を元にグラフ更新（円グラフ、棒グラフを月、商品ごとに分けて更にシートも分ける）

フィルターオプションが初見です。
「オプションを変更すれば、フィルタ操作も含めてマクロで自動で行う」
は、
・詳細設定で設定を済ませとく（使い方はこれから調べるとこ）。
↓
・Worksheet_Changeでカウントするコードを書く
でしょうか？

お礼日時：2022/01/09 19:17

No.2 回答者： ママチャリ 回答日時： 2022/01/09 13:02

フィルタをかけるシートとグラフのシートが別シートであるなら、グラフのシートのWorksheet_Activateイベントプロシジャでもいける気がします。

ただ、「新しいウィンドウを開く」で、両シートを同時に開いているような通なユーザがいる場合は、要注意ですが・・・。

この回答へのお礼

お返事有難うございます。
シートは別になります（今のところ）。
ただ、Worksheet_Activateだとフィルターのあるシートを選択した瞬間にマクロが実行されるので、それではないです（フィルター操作が引き金です）。
ただ、試してみます（「ただ、～」はどういう流れでしょうか？イメージが想像できませんでした）。

お礼日時：2022/01/09 18:54

No.1 回答者： mygoonickname 回答日時： 2022/01/09 11:59

こんにちは。

１つの可能性ですが、どこかのセルに、=NOW()　の様な関数を入れて、
Calculateイベントで拾うのは、どうでしょうか？
それ以外は、すぐに思いつかないです。
念のために、ブックを開いた時のフィルターの状態などを取得しておいて、
Calculateで、フィルターの状態が変わったかを確認し、処理を行うなど。

Private Sub Worksheet_Calculate()
MsgBox "処理！"
End Sub

この回答へのお礼

早々のお返事有難うございます。
半分上手くいきましたので、ご連絡と思いましたが、
残り半分が考え中です（ファイルオープンだけで反応してしまう）。

サンプルを作って試しましたが、まだ完成していません。

'閉じる前にカウント結果を保存しておく（ブックを開くときはマクロの起動させないため）
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Range("B1") = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1

End Sub

'このシートにフィルターが掛かっている
Private Sub Worksheet_Calculate()

If Range("B1") = AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 Then
' カウント数が同じなら何もしない（これを入れないと開いただけでマクロが動く）
→（当たり前ですが）フィルターを掛けても結果的にカウント数が同じだと（反応させたくても）無反応。
Else
Range("B1") = AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
MsgBox "test"
End If

End Sub

後半で仰ってること（念のために～）のイメージが湧きませんでしたが、追加で教えて頂ければ幸いです。

お礼日時：2022/01/09 18:39