Excelフィルタ系ライブラリ「セル位置」の原則と例外
このスレッドをきっかけに、Excelフィルタ系ライブラリを深堀りました。
https://winactor.com/questions/?q=フィルター後、計算しません。何が原因なのでしょうか。
「セル位置」設定の際に注意を要する点を整理しました。
フィルタ結果に応じた再計算を伴う、SUBTOTALみたいな関数を併用する際に効いてきます。
長めですが、結論だけでも十分です。
太字を中心に眺めていただくと、必要な場面で情報を探すきっかけになったり、ハマらずに済むはずです。
後半の▼以下が検証結果です。
検証で見出した結論
- 原則
- WinActorからExcelフィルタ系ライブラリを使う場合は、予め表を孤島にしておくべし
- セル位置には表内左上のセル番地を指定すべし
- 例外
- 表を孤島にできない事情がある場合は、セル位置に行範囲を指定すべし
- フィルタ結果に伴うSUBTOTAL等の再計算が必要な場合は、Excelフィルタ系ライブラリを直列で複数並べてダメ押しすべし
検証環境
- WinActor:7.2
- Excel:Microsoft 365(2019相当)
今回の検証は7.2だけです。
Excel起因っぽいので、結論はWinActorのバージョンを問わず有効なはずです。
特定済みの挙動と再現条件
- ★は別回答で特定済みの症状
- Zawawaさんが特定されたのは☆☆のケースで、表が孤島になっている限り有効
(海なし県、埼玉みたいな表だと▲)
「セル位置」相当のRangeオブジェクトに関する主なリファレンスサイトの要約
- 表内のどこのセルでも良い
- 左上のセルが分かりやすい
- 範囲でも良い
- Excelはブランクセルで囲われた範囲を表とみなす
※ 行指定「Rows(3)」の例は見つかるものの、3:3の例は見つけられず、、
※ Rows(3)がおっけーなら、3:3も許されるはず
同じ操作のマクロ実行結果
- 選択行をカラム行と位置付けてフィルタできた
- 孤島である必要はなかった
- 1度で正しく再計算された
- 選択行以下で、ブランクセルで囲われた範囲に対して、フィルタがかかった
- VBAコード最適化の過程で、再計算されない症状を再現できた
▼ 別回答と同じ表での検証結果
1行目:SUBTOTAL
2行目:カラム名
3行目以下:データ
- 2:2 → ★
- A2:C2 → ★
- A2 → 1行目にフィルタがかかり、期待通りの挙動ならず▲
- 2 → 指定されたセルが見つかりません
- A2:C20 → 1度で正しく再計算された
マクロでも確認
Rows(2:2).Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$C$20").AutoFilter Field:=2, Criteria1:="40"→ 1度で正しく再計算された
プルダウン表示のくだりを省略
ActiveSheet.Rows(2:2).AutoFilter Field:=2, Criteria1:="40"
→ ★
- WinActorのExcelフィルタ系ライブラリで、セル位置に2:2やA2:C2をセットした場合にSUBTOTALの再計算が走らないのは、Excelと同じ挙動で、少なくともWinActorのバグという訳ではなかった
- WinActorライブラリもExcel VBAも、少なくとも使いかたで回避可能
Excelの扱いかたの問題なのかバグなのか、時間が取れればリサーチしてみようと思います。
情報が見つからなかったら、Microsoftコミュニティにあたりに質問してみようかと。
▼ 2行目に空行を挿入した構成で再検証
1行目:SUBTOTAL
2行目:空行
3行目:カラム名
4行目以下:データ
- 3:3 → ★
- 4:4 → 4行目にフィルタがかかった
- A3:C3 → ★
- A4:C4 → 4行目にフィルタがかかった
- A3 → 1度で正しく再計算された☆☆
- A4、B4、C4、A20、B20、C20 → 1度で正しく再計算された☆
- A3:C21 → 1度で正しく再計算された
- A3:C20 → 1度で正しく再計算された、絞込みキーが「セル位置」の範囲外、21行目にあってもヒットした
- B3:C21 → 1度で正しく再計算された、A列にフィルタのプルダウンはないものの結果に影響なし
- B5:B10 → 5行目にフィルタがかかった
- B5:C10 → 5行目にフィルタがかかった
※ いずれも表に存在しない列番号を指定したら、RangeクラスのAutoFilterメソッドに失敗
オートフィルタのリファレンス
Office TANAKA - Excel VBA Tips[オートフィルタ[書き方の基本]]
http://officetanaka.net/excel/vba/tips/tips155.htm
【VBA入門】オートフィルターを設定・解除する方法を徹底解説! | 侍エンジニアブログ
https://www.sejuku.net/blog/71689
【Excel VBA】オートフィルタの設定・絞り込み・解除の方法
https://works.forward-soft.co.jp/blog/detail/10294
【EXCEL VBA】フィルターを使ってデータを絞りこむ方法 | きままブログ
https://masagoroku.com/【excel-vba】フィルターを使ってデータを絞りこむ方法
Range.AutoFilter メソッド (Excel) | Microsoft Docs
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.autofilter
【エクセルマクロ】オートフィルタの範囲設定:4つの使用例を紹介するよ | kirinote.com
https://kirinote.com/excelvba-autofilter-set/
[Excel VBA]フィルターの適用範囲を調べるには? | 日経クロステック(xTECH)
https://xtech.nikkei.com/it/atcl/column/15/090100207/090100047/
セルのAutoFilterメソッド - Excel VBA
https://www.239-programing.com/excel-vba/basic/basic06H.html