
今社内ツールとして、フィルターを掛けたデータをもとにグラフを作ろうとしています。
フィルター抽出とマクロを連動させる方法はあるでしょうか?
※worksheet_changeイベントでいけると思いましたが、セルを編集しないとダメでした。
流れは、
1、あるシート内データ(既にフィルターは設定されている)を任意の条件で抽出する。
(1で抽出したらそれを検知してマクロが動き)←これのやり方が分からない
2、データをカウントして別シートの表に転記(カウントロジックは別途作成予定)
3、2の表を元にグラフが勝手に更新
3については関数で表とリンクさせて表のデータが変われば(フィルターが操作されたら)グラフも連動するようにしたいです。
グラフ作成までマクロと考えていますが、修正対応のためなるべくシンプルな方法をとる予定でセル関数でもいいかと考え中です。
現場の要望がボタン(フォームコントロール)を使いたくない(フィルター操作だけで3まで実行されるような仕組みにしたい)のですが、方法が思いつきません。
全て関数だけでやれる気がしなくもないですが、2のカウント処理は(検知方法があれば)マクロの方が複雑にならない気がします(ただ、具体的にどうコードを組めばいいか分かりません)。
ご存知の方がいましたら、ご教授お願いします。
No.7ベストアンサー
- 回答日時:
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
No.6
- 回答日時:
No3です
実態がよくわからないままですけれど・・・
>Worksheet_Changeでカウントするコードを書く~
単にカウントするだけなら、No3にも記したようにSUBTOTALのCOUNTやCOUNTAで十分と思われます。
一方で、
>運用中の関数が長すぎてマクロの方が簡単なのでは~
表示行をカウントするだけではないのでしょうか?
No1の補足の、
>AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
を見ても、単に表示行数をカウントしているだけのように見えます。
・・というわけで、何が問題なのかさっぱり理解できません。
しかも、B2セルに単純に値をセットしているだけのようなので、B2に関数式を入れておけば、それで済む問題のようにも思えます。
まぁ、COUNTやCOUNTAでは空白セルが無視されますので、「表示中の空白セルもカウント対象とする」ような場合には少々面倒ですけれど・・・
フィルターを利用しているような場合で、そのようなケースはあまり想像はできませんけれど。
(必ず値のある列が存在すると想像しますので、その列をカウントすればすむ)
とは言え、表示行数をカウントしたところで、それだけでグラフになるとも思えないので、実態が不明のままではこれ以上は何ともわかりません。
度々、有難うございます。
実はグラフには関係ないかもしれませんが、運用中の関数には(個人的に)長いと感じる(ifが入れ子になっていたり)ものがありました(大枠の記憶で質問しているので、関数だけで解決するかもしれません)。
しばし検討します。
No.5
- 回答日時:
No.2です。
>それではないです(フィルター操作が引き金です)。
それは、失礼いたしました。
ただ、「グラフのシートのWorksheet_Activateイベントプロシジャ」と書いたつもりなので、「フィルターのあるシートを選択した瞬間にマクロが実行される」には当たらないと思います。
Calculateイベントプロシジャみたいに、頻繁に発生するイベントに書いちゃうと操作が重くなってしまいそうなので、この案を提示させていただきました。もし、操作が重くなっても気にならない程度の処理であれば、この案は無視してください。
「ただ、~」の意味ですが・・・、
このツールの操作方法は、まず、フィルタをかけるシートで絞り込んだ後、グラフシートを選択して(アクティブにして)、グラフの状況を確認するはずです。なので、このタイミングでグラフが更新されれば良いと考えました。
しかし、もし別ウィンドウでグラフシートを表示しっぱなしにしているような通なユーザがいる場合、グラフシートをアクティブにすることが無いので、Worksheet_Activateが発生せず(グラフが更新されず)、「不具合だ」って言ってくる可能性があるということです。
度々、有難うございます。
>それは、失礼いたしました。
いえ、こちらの表現不足です。
>~、グラフの状況を確認するはずです。
ですね。
仰られている通な使い方をされたら、動作は保証しないと言います。
もう暫し検討します。
No.4
- 回答日時:
No.1の者です。
>ブックを開くときはマクロの起動させないため
→これは、どの様な意味なのでしょうか?
マクロ無効や、イベントDisableにすると元に戻すタイミングが難しいかと。
例えばですが、
●Thisworkbook
Private Sub Workbook_Open()
ここで、フィルターの状態を保持
End Sub
●Sheet(フィルター)
Private Sub Worksheet_Calculate()
フィルターの状態を比較
状態が変わっているなら処理を実行。
状態が変わっていなければ処理を抜ける。(何もしない)
End Sub
度々、有難うございます。
>ブックを開くときはマクロの起動させないため
→これは、どの様な意味なのでしょうか?
A:方法を試したら、ブックを開いたら(閉じても)フィルター操作しなくてもcalculate(calculate自体は初めて知りました)が実行されたので、フィルター操作の時だけにするためでした(Nowが関連しているのでしょう、きっと)。
>ここで、フィルターの状態を保持
→フィルターされている表をまるごと別シートに一時的にコピーしておくという意味でしょうか(で、比較するときはセルごとに比較)?
(こちらの都合ですが)大元のデータ表が行と列が多いので、セル1つずつ比較だと結構時間が掛かかる、ですね。。(比較のロジックは自分で工夫してよ、の話ですが)
No.3
- 回答日時:
こんにちは
詳細が不明なので、はっきりとはしませんけれど・・・
きっかけ(トリガー)は、ユーザのフィルター操作という事でしょうか?
計算内容が「合計」や「カウント」程度であるなら、フィルターに連動できる関数(SUBTOTALやAGGREGATEなど)で計算可能だと思います。
それでまかなえる範囲の計算であればマクロは不要であろうと想像します。
グラフの元になる表さえ連動できれば、グラフ自体は自動的に表に連動しますので。
「そんな簡単な計算内容ではない」というのであれば、フィルタオプションを利用する形式にしておいて、
「オプションを変更すれば、フィルタ操作も含めてマクロで自動で行う」
というような方法も考えられそうです。
(オプション部分の変更で、Worksheet_Changeイベントが発生します)
「フィルタオプションも嫌」ということであるなら、既出の回答のように再計算が発生するような仕掛けをしておいて、Calculateイベントで拾うかでしょうか・・・
お返事有難うございます。
きっかけはフィルター操作です。
仰る通りマクロはいらないかもと想像してました。
更に言うと既に別の人間が関数だけで運用しているツールがあるので、それを真似すればいいのですが、
自分がグラフに慣れてないことと運用中の関数が長すぎてマクロの方が簡単なのではと思っていました。
シートの構成としては、
Sheet1:データ元の表(インプット)
Sheet2:抽出後のカウントされた表データ(アウトプット)※フィルター操作の度に(結果が同じでも)更新
Sheet3:Sheet2を元にグラフ更新(円グラフ、棒グラフを月、商品ごとに分けて更にシートも分ける)
フィルターオプションが初見です。
「オプションを変更すれば、フィルタ操作も含めてマクロで自動で行う」
は、
・詳細設定で設定を済ませとく(使い方はこれから調べるとこ)。
↓
・Worksheet_Changeでカウントするコードを書く
でしょうか?
No.2
- 回答日時:
フィルタをかけるシートとグラフのシートが別シートであるなら、グラフのシートのWorksheet_Activateイベントプロシジャでもいける気がします。
ただ、「新しいウィンドウを開く」で、両シートを同時に開いているような通なユーザがいる場合は、要注意ですが・・・。
お返事有難うございます。
シートは別になります(今のところ)。
ただ、Worksheet_Activateだとフィルターのあるシートを選択した瞬間にマクロが実行されるので、それではないです(フィルター操作が引き金です)。
ただ、試してみます(「ただ、~」はどういう流れでしょうか?イメージが想像できませんでした)。
No.1
- 回答日時:
こんにちは。
1つの可能性ですが、どこかのセルに、=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
後半で仰ってること(念のために~)のイメージが湧きませんでしたが、追加で教えて頂ければ幸いです。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel(エクセル)でフィルター抽出後、非表示の行を計算しないで、合計を算出する方法 【内容】 添 4 2023/01/30 17:17
- Excel(エクセル) [Excel2016] 相関表等の自動作成 2 2022/08/01 20:34
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2023/08/03 12:30
- Visual Basic(VBA) Sheet2の日付をキーにオートフィルターで2023年1月のデータを抽出し、Sheet3へ書き出すた 2 2023/03/06 23:57
- Excel(エクセル) excelで可視セルのみ置換 3 2022/08/04 11:02
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2023/08/03 11:27
- その他(コンピューター・テクノロジー) VBA初心者です。 仕事の残業を少しでも減らしたく 最近勉強を始めたのですが フィルターを一気にかけ 4 2022/08/15 20:58
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Excel(エクセル) Indirect関数について、Formulatextで抽出した数式を参照したい。 1 2022/12/15 11:16
- Excel(エクセル) エクセルの散布図で新たに入力した値のデータラベルが空欄になる現象 1 2022/04/26 09:31
このQ&Aを見た人はこんなQ&Aも見ています
-
VBAで保存しないで閉じると空のBookが残る
Excel(エクセル)
-
Calculateイベントでセルを指定したい
Word(ワード)
-
エクセルのフィルターを複数シートに連動させたいです。 エクセルファイルに15シートあります。 そのう
Excel(エクセル)
-
-
4
VBAでエクセルシートを更新(リフレッシュ)する方法を教えて下さい。
Excel(エクセル)
-
5
エクセルVBAでオートフィルター最上行を取得するには
Excel(エクセル)
-
6
エクセル:マクロ「Application.CutCopyMode = False」って?
Excel(エクセル)
-
7
”戻り値”が変化したときに、マクロを実行したい
Visual Basic(VBA)
-
8
【エクセルVBA】 A1セルを参照してオートフィルタを抽出するChangeイベントが連続処理できない
Excel(エクセル)
-
9
マクロ オートフィルタの検索値を所定のセルから参照
Excel(エクセル)
-
10
EXCELで特定のセルに表示された項目をヘッダーやフッターに出力するには
Excel(エクセル)
-
11
数式による空白を無視して最終行を取得するマクロ
Excel(エクセル)
-
12
VBAのオートフィルターで該当行がない場合に処理を止めたい
Excel(エクセル)
-
13
エクセルのエラーメッセージ「400」って?
Visual Basic(VBA)
-
14
VBAでループ内で使う変数名を可変にできないか。
Visual Basic(VBA)
-
15
エクセルVBAでシートモジュールでのパブリック変数
Excel(エクセル)
-
16
VBA エンターキーでイベントに入りたい。
PowerPoint(パワーポイント)
-
17
エクセル イベントマクロ Changeイベントを複数作りたい
Access(アクセス)
-
18
複数の文字列のいずれかが含まれていたらTRUEを返す関数について
Excel(エクセル)
-
19
メッセージボックスを前面に表示させるには?
Visual Basic(VBA)
-
20
エクセルVBAでcommit,rollback
PowerPoint(パワーポイント)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで特定の列が0表示の場...
-
特定のPCだけ動作しないVBAマク...
-
Excel_マクロ_現在開いているシ...
-
Excel・Word リサーチ機能を無...
-
メッセージボックスのOKボタ...
-
Excel マクロ VBA プロシー...
-
エクセルに張り付けた写真のフ...
-
Excel VBAからAccessマクロを実...
-
一つのTeratermのマクロで複数...
-
ExcelVBAでPDFを閉じるソース
-
Word VBA 表中の空白行を削除す...
-
マクロの連続印刷が突然不可能...
-
TERA TERMを隠す方法
-
Excel マクロでShearePoint先の...
-
ExcelのVBA。public変数の値が...
-
EXCELマクロでのThisisWor...
-
VBAにて別ワークブック上の実行...
-
Excelのマクロボタンをダブルク...
-
エクセルで別のセルにあるふり...
-
Excelのセル値に基づいて図形の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
特定のPCだけ動作しないVBAマク...
-
Excel・Word リサーチ機能を無...
-
エクセルで特定の列が0表示の場...
-
Excel_マクロ_現在開いているシ...
-
一つのTeratermのマクロで複数...
-
メッセージボックスのOKボタ...
-
Excel マクロ VBA プロシー...
-
ExcelのVBA。public変数の値が...
-
エクセルで別のセルにあるふり...
-
エクセルに張り付けた写真のフ...
-
Excel マクロでShearePoint先の...
-
TERA TERMを隠す方法
-
Excelのセル値に基づいて図形の...
-
Excel VBAからAccessマクロを実...
-
ExcelVBAでPDFを閉じるソース
-
マクロ実行時、ユーザーフォー...
-
オートフィルターとExcelマクロ...
-
wordを起動した際に特定のペー...
-
特定文字のある行の前に空白行...
-
エクセルで縦に並んだデータを...
おすすめ情報
No1の方に最初にお返事頂いたので、その方をBAとさせていただきます。
質問に付き合って頂き、有難うございます。
No1の方に最初にお返事頂いたので、その方をBAとさせていただきます。
質問に付き合って頂き、有難うございます。