☰

複雑な条件付き書式

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016 conditional-formatting
複雑な条件付き書式

私は Excel の経験が豊富な CPA ですが、この質問 (私自身の質問) に対する答えがわかりません。以下の例に条件付き書式を適用する方法はありますか。

例: =sum(B4,C28,A32,B40) という数式があります ---> 数式で使用されているセルを自動的に強調表示する条件付き書式ルールをセル B4、C28、A32、および B40 に適用する方法はありますか?

^ これは、私が実行している銀行照合に非常に役立ちます。手動で強調表示する代わりに、手動でこれを行う必要があるセルがたくさんあります。私は、照合を可能な限り自動化することを固く信じています (実際、私の照合のほとんどは、ほとんどの照合項目を自動的に特定する、私が作成したドラッグ アンド ドロップ テンプレートです)。

注: 現在、手動でペアリングする必要があるセルに =isformula(xxxxx) を使用しています。次に、すべてがキャプチャされ、何も見逃されていないことを確認するために強調表示します。

答え1

あなたが説明していることは、スプレッドシートの監査のようです。Excel には、それを支援する組み込みツールがいくつかあります。

数式リボンの「参照元のトレース」を使用して、現在のセルが依存するセルに線を引くことができます。矢印を削除するには、「矢印の削除」を使用します。

ここに画像の説明を入力してください

または、[数式] リボンの [数式を表示] をクリックして (または Ctrl+` (米国キーボードの数字 1 の左側にあるバックティック) を押して)、数式ビューを使用することもできます。これにより、結果ではなくセルの数式が表示され、数式で使用されているセルが強調表示されます。

ここに画像の説明を入力してください

条件付き書式は、スプレッドシート内のすべてのセルに条件付き書式を適用する必要があるため、良い方法ではありません。そうすると、処理がかなり遅くなります。

答え2

FORMULATEXT()はい、これを基礎として使用できます:

=IFERROR(FIND(SUBSTITUTE(CELL("ADDRESS",INDIRECT(ADDRESS(ROW(),COLUMN()))),"$",""),FORMULATEXT($C$1)),0)<>0

上記では、調べている数式が存在するセルがわかっていることを前提としています。$C$1 を使用していますが、もちろん任意のセルに変更することもできます。

任意の単一セルに対して条件付き書式（「CF」）ルールを作成できます。ただし、そのセル内で実行してください。つまり、そのセルが選択されている間です。次に、セルをコピーし、CF に含める他のセルに書式を貼り付けます。

数式が入ったセルがあり、それが直接参照するセルにデータを入力することを想定していますSUM()。どこかに入力しているリストがある場合は、CF 数式を作成するときにそれを直接参照できます。

数式は、ADDRESS()選択されたセル、つまり最終的に CF が配置されるセルに対して、テキスト セル参照を作成するために使用します。次に、を使用してINDIRECT()それを「実際の」セル参照にします。最後に、を使用してSUBSTITUTE()$ を削除します (「A1」になり、「$A$1」ではなくなります)。

これは、セルを追加する関数FIND()で検索する材料になります。は、その数式のテキストを取得するために使用されます。「SUM(" および ")" は許容されるセル参照の一部ではないため、それらについては何もする必要はなく、括弧内で参照されているセルを検索するだけです。 は、失敗したかどうかを確認し、失敗した場合は 0 の結果を生成できないため、結果として 0 を返します。つまり、この手順から正の整数または 0 が取得されることを意味します。SUM()FORMULATEXT()FIND()IFERRORFIND()

最後に、CF はそれが 0 かどうかをチェックし、0 でない場合は、セルを好きなように強調表示します。

これは、誰も検討していないような、より広範な機能の一部です。名前付き範囲を作成して()、それに追加し、括弧内に「実際の」関数の引数と同じように好きなものを入力できます。名前付き範囲をサポートするいくつかの関数では、Excel にプログラムされた関数のように見え、動作する名前付き範囲が提供されますSUM()。引数は任意です。同僚のお気に入りとしては、ourLOOKUP()(会社名、「当社」ではありません) が INDEX/MATCH 引数を取り、 のように構築された関数に入れるというものがありますVLOOKUP()。

つまり、UDF は本質的に、VBA をあまり知らなくても、または VBA の使用が許可されていない場所に遭遇しなくても使用できます。

初めてやるにはちょっと意地悪だし、デリケートだけどいいことだ。

()しかし、私が推測しているように、に入力すれば、上記でハイライト処理が行われますSUM()。 回答は、より大きなタスクに対処するものではなく、質問されているハイライトのみを対象としています。

関連情報