風柳メモ

ソフトウェア・プログラミング関連の覚書が中心

エクセルで揮発性の数式を含むセルを抽出する試み



はじめに

エクセルのワークシートで使える関数の中には揮発性関数というものがあり、これらは再計算の度に再評価(実行)されてしまいます。

揮発性関数の中には OFFSETINDIRECT といった便利な関数も含まれているためについ使ってしまいがちですが、かといってこれらを多用していると動作が重くなる要因のひとつとなります。
場合によっては、他の関数に置き換える等の対処を考えたほうが良いかも知れません。
自動再計算を行わないという対処法もありますが、これはこれで何かと不便です。

そこで、調査用として、シート中の揮発性の数式を含む(再計算時に常に再評価されてしまう)セルを抽出する方法について考えてみました。

f:id:furyu-tei:20201105095343p:plain
サンプルマクロの実行結果例

なお、他の方々から知見を得るために、クイズ形式でツイートもしています。

もっとうまい方法をご存じの方、コメントやこのツイートへのリプライででも教えて下さいませ。

試し方

標準モジュールに下記のサンプルコードを貼り付けて、目的のシートをアクティブにした状態でマクロ(MarkVolatileCells)を実行すると、揮発性の数式を持つセルにメモが添付されます。

サンプルコード


解説

引数の値をそのまま返すような単純なユーザー定義関数(UDF)を作成した場合、その引数にセル参照を指定しておくと、再計算時には参照先が揮発性の場合にのみ当該 UDF が呼び出されることになります。

サンプルソースコードの MarkVolatileCells() では、この性質を利用して、

  1. 作業用に、対象シート(target_sheet)を複製
  2. 複製した作業用のシート(temp_sheet)中で数式を持つセル(=target_sheetでも同じアドレスのセルは数式を持つ)に対し、数式を target_sheet の同じアドレスのセルへの参照を引数としたUDF(ReferCellValue)に置換
    置換を行う前には自動計算状態(Application.Calculation = xlCalculationAutomatic)にして、全ての ReferCellValue が一度呼び出される(実行される)ようにしておく
  3. 手動計算状態に切り替え(Application.Calculation = xlCalculationManual)た後、コレクション(VolatileAddressCollection)を初期化する
  4. 強制的に再計算(temp_sheet.Calculate)を行うと、参照先が揮発性の場合にのみ ReferCellValue が呼び出されるため、このアドレスをコレクション(VolatileAddressCollection)に記録
  5. 記録されたアドレスに対応する target_sheet 中のセルにメモを添付

という処理を行っています。

注意事項

上記の手法は当方が思いつきで試してみたものであり、正しく揮発性の数式を持つセルが検出できるのかについての保証はありません。あくまで参考として考えて下さい。

また、数式が指定されているセルに対してしか使えないため、

  • 条件付き書式内で揮発性数式が使われている場合
  • リンクした図の場合

等のケースについては検出できません。

ちょっとだけカユイところに手が届くブラウザ拡張/スクリプト

覚え書きを兼ねて、今後も更新される可能性のある自作のブラウザ拡張機能/スクリプト類をまとめてみました。

Amazon.co.jp のカユイところに手が届くブラウザ拡張/スクリプト

覚え書きを兼ねて、今後も更新される可能性のある自作の Amazon.co.jp 用ブラウザ拡張機能/スクリプト類をまとめてみました。

船を出すのなら九月…その船は今どこにふらふらと浮かんでいるのか?

九月というと反射的に、中島みゆきさんの「船を出すのなら九月」とか
アルバム「生きていてもいいですか」に収録に収録されています

生きていてもいいですか【リマスター(HQCD)】

生きていてもいいですか【リマスター(HQCD)】

シギサワカヤさんの「九月病」
九月病 上巻 (ジェッツコミックス)

九月病 上巻 (ジェッツコミックス)

九月病 下巻 (ジェッツコミックス)

九月病 下巻 (ジェッツコミックス)

とかを連想してしまう、業の深い(?)自分がいます。
いずれも名作ですので、体験したことのない方はぜひご体感ください。
なお、心身ともに良好なときに視聴することをおすすめします


船を出すのなら九月 [作詞・作曲:中島みゆき]

船を出すのなら九月 誰も見ていない星の九月
人を捨てるなら九月 人は皆 冬の仕度で夢中だ

あなたがいなくても 愛は愛は愛は
愛は まるで星のようにある

船を出すのなら九月 誰も皆 海を見飽きた頃の九月

夢をとばすなら九月 たくさんの愛がやせる九月
海へ逃げるなら九月 知らぬまに夜が誘いをのばしてる

あなたがいなくても 愛は愛は愛は
愛は どうせ砂のようにある

人を捨てるなら九月 誰も皆 冬を見ている夜の九月
船を出すのなら九月 誰も皆 海を見飽きた頃の九月

宙船(そらふね) [作詞・作曲:中島みゆき]

その船を漕いでゆけ おまえの手で漕いでゆけ
おまえが消えて喜ぶ者に おまえのオールをまかせるな

その船は今どこに ふらふらと浮かんでいるのか
その船は今どこで ボロボロで進んでいるのか
流されまいと逆らいながら
船は挑み 船は傷み
すべての水夫が恐れをなして逃げ去っても
その船を漕いでゆけ おまえの手で漕いでゆけ
おまえが消えて喜ぶ者に おまえのオールをまかせるな

その船は自らを宙船(そらふね)と 忘れているのか
その船は舞い上がるその時を 忘れているのか
地平の果て 水平の果て
そこが船の離陸地点
すべての港が灯りを消して黙り込んでも
その船を漕いでゆけ おまえの手で漕いでゆけ
おまえが消えて喜ぶ者に おまえのオールをまかせるな

何の試験の時間なんだ 何を裁く秤(はかり)なんだ
何を狙って付き合うんだ 何が船を動かすんだ
何の試験の時間なんだ 何を裁く秤なんだ
何を狙って付き合うんだ 何が船を動かすんだ
その船を漕いでゆけ おまえの手で漕いでゆけ
おまえが消えて喜ぶ者に おまえのオールをまかせるな
その船を漕いでゆけ おまえの手で漕いでゆけ
おまえが消えて喜ぶ者に おまえのオールをまかせるな

こちらはアルバム「ララバイSINGER」に収録されています

ララバイSINGER

ララバイSINGER

このブログにしては異色の記事ですが、せっかくはてなブログでJASRAC管理の歌詞を掲載できるようになったのに、そのことに気がついてからもそういえばまだ一度も利用したことなかったなとちょっと思ったので……。

関連

Twitterのカユイところに手が届くブラウザ拡張/スクリプト


覚え書きを兼ねて、今後も更新される可能性のある自作のTwitter用ブラウザ拡張機能/スクリプト類をまとめてみました。