スプレッドシート内のデータをコピー、削除、移動すると、条件付き書式設定の数式や範囲が自動的に調整されることがわかりました。これは良いアイデアですが、私にとっては奇妙な方法で物事を壊してしまいがちです
これを避けるために、スプレッドシート全体に適用されるルールを書いてみたり、列ヘッダーをキーオフしてチェックしたいデータを強調してみたりしました
例=AND(A$1="Check This Column For Blanks),ISBLANK(A1))を=$1:$1048576に適用
しかし、シート全体にルールを明示的に適用しても、シートの中で作業しているうちに自動的に調整（そうすることで変な壊れ方をしてしまう）されていました
これを回避するにはどうすればいいのでしょうか？
26 Iszi 2013-09-20
セルの移動、挿入、削除など、いかなる状況下でも変化してはいけない範囲が必要なときは、名前付き範囲とINDIRECT関数を使っていました
例えば、範囲を常にセル A1:A50 に適用したい場合は、名前付き範囲をネームマネージャーで定義しました
名前マネージャで、新しい範囲を追加し（新規作成をクリック）、参照先：フィールドで、INDIRECT関数を使用して、=INDIRECT("A1:A50")や=INDIRECT("Sheet!A1:A50")などのように、必要なセルの範囲を指定します。範囲は技術的には単なるテキスト引数なので、いくらセルを並べ替えてもExcelが範囲を更新することはありません
また、これは少なくともExcel 2010とExcel 2013で動作します。私のスクリーンショットは2013年のものですが、過去に2010年にこの正確なテクニックを使用したことがあります
Caveats
この不変性は、あなたをトリップさせることもあることを覚えておいてください。例えば、シートの名前を変更すると、名前のついた範囲が壊れてしまいます
かなりの数のセルでこの手法を使用した場合、パフォーマンスが若干低下することに気付きました。私が仕事で使用しているモデルでは、数千の異なるセル範囲にまたがる名前付き範囲でこのテクニックを使用しており、これらの範囲内のセルを更新するときにExcelは少し遅く感じます。これは私の気のせいかもしれませんし、ExcelがINDIRECTに追加の関数呼び出しをしているという事実かもしれません
8 John Bensin 2013-09-20
ルールはとても簡単に破られてしまうことがわかりましたが、ここではルールを破られそうで破られない方法を試してみましょう
セル内のテキストを変更することができます。行を追加する必要がある場合は、テーブルの最後にデータを追加し、ソートし直します。行を削除する必要がある場合は、テキスト/数字だけを削除してからテーブルをソートし直します
これは、列に適用される条件付き書式設定がある場合に動作し、通常は列全体の書式設定を設定します（例：$F:$F）。部分的な範囲の書式設定をしている場合でも、これは動作するはずです。ただし、追加/削除やリソ-トが完了した後、書式設定したいデータがすべて元の範囲のパラメータの範囲内にあることを確認してください
私にとっても大きな挫折です
これが役に立つといいですね
2 Paul 2015-06-05
私はSOではなく、同じ問題に頻繁に直面しています
条件付き書式設定 (CF) パネルの ‘適用先’ フィールドは常に動的に動作します。つまり、すべての参照は常に =$A$1:$A$50 形式に変換されます
痛いですね
1 Leandro Ferreira Fernandes 2014-08-20
条件付き書式設定ルールでINDIRECT関数とROW関数を使うことで、Excelで新しいルールを作って範囲を変更するという問題がなくなることがわかりました
例えば、私は、小切手帳のスプレッドシートで、ある行から次の行に月が変わったときに、行間に行を追加したいと思っていました。そこで、CFルールの私の計算式は
=MONTH(INDIRECT("C"&ROW()))<>MONTH(INDIRECT("C"&ROW()-1))
ここで、私のスプレッドシートのC列には日付が含まれています。範囲に特別なことをする必要はありませんでした（範囲名などを定義する必要はありませんでした）
そこで、元のポスターの例では、CFルールの「A1」や「A$1」の代わりに「A1」を使います
INDIRECT("A"&ROW())
1 CRS 2017-01-27
私は非常に似たような問題を経験しました。行を追加したり、数式をコピーダウンしたり、列や行のサイズを調整してシートの書式設定を行うマクロをいくつか作ってみましたが、この問題が発生したのは2つのうちの1つです。この問題が発生するのは、2つの場面のうちの1つです
1) INSIDEの何かがこの範囲外でカット/ペーストされている場合
2) 「適用先」の中にマージされたセルがあり、行や列のいずれかが調整されている場合
マージされたセルの問題では、エクセルはすべてのセルのマージを解除し、条件付きのアプリケーションを再計算し、すべてのセルを調整（行の追加や削除など）してから、それらを再びマージしなければならないようです。私たちには見えませんが、それが適用される方法のようです
これで解決できるかもしれないと思った
-T
0 Terrence 2016-01-05
これを簡単に解決する方法があります
範囲内にあるセル（範囲が壊れていないセル）に移動して、「書式設定」をクリックしてから、列全体に貼り付けます。ここでも壊れた場所が表示されますので、範囲が壊れたセルの上でフォーマットペインターを行う必要があります。さて、これも少し長く感じるかもしれませんが、簡単なマクロを作ってみましょう
0 Girish Nagarhalli 2017-10-09
条件付き書式設定式では、R1C1記法とINDIRECT関数を使用します。 例1
If same row column A = 1
=IF($A1=1,1,0) becomes
=IF(INDIRECT("RC1",FALSE)=1,1,0)
例2. If same row column A = 2 AND next row column A = 3
=IF(AND(($A1=2),($A2=3)),1,0) becomes
=IF(AND((INDIRECT("RC1",FALSE)=2),(INDIRECT("R[1]C1",FALSE)=3)),1,0)
0 Karen B 2018-01-12
レポートのスプレッドシートを生成することがユースケースであるならば、これはうまく機能するはずです
あなたも手動ですべてのデータ入力を行った後、最終的なデータの簡単なリフレッシュを実行しても構わないと思っている場合は、1回限りのセットアップ手順のカップルでExcelの過剰な有用性をバイパスすることができます
データが正常に配置されていると仮定します（行単位）
- すべてのデータを1つのシートに入力します。このチュートリアルでは、シートを
Inputと名付けます。このシートには条件付き書式を適用しないでください。値を自由にシフトさせてください(例: 削除/挿入/コピー/貼り付け) - 別の空のシートを作成し、好きな名前を付けます(例:
Output)。列幅やヘッダーの書式設定などのグローバル書式設定を手動でコピーします(ヘッダーの内容ではなく、書式設定だけです) - 次の式の
$A$1をヘッダー行の開始位置に置き換え、出力したいすべての列と行をコピーします。=IF(LEN(OFFSET(Input!$A$1,ROW()-1,COLUMN()-1))>0,OFFSET(Input!$A$1,ROW()-1,COLUMN()-1),"")(IF(LEN(...)>0,...,"")ステートメントは、Excel がデータ型を決定し、それ以外の場合は空のセルに0を使用し、-1の用語はオフセットと順序のセマンティクスに由来するので必要です) Outputシートに条件付き書式を適用します
これは、InputデータシートをOutputシートにコピーするもので、Excelが自動バッチングするセル参照を一切含まないので、Outputシート上で条件付き書式設定を自信を持って定義することができます
Inputデータのサイズが変わると、Outputシート内で数式が適用される範囲を手動で拡張する必要があります
任意のセルをマージしないことをお勧めします
P.S. この質問は何度も聞かれています（ExcelのクローンであるGoogle Sheetsにも当てはまります）ので、それらに目を通してみると、より良い解決策が見つかるかもしれません
- エクセルの条件付き書式の断片化
- (これは) 条件付き書式設定の式や範囲を自動的に変更しないようにするにはどうすればいいですか？
- カットペースト時の条件付き書式を保持します
- Excel：スプレッドシート全体に条件付き書式設定を永続的に適用するには？
- なぜExcelは私の条件付き書式設定を虐殺するのですか？
0 Elaskanator 2018-08-03
フォーマットなし(Paste > Paste Special > Unicode Textなど)で貼り付けた場合、特殊なフォーマットはコピーされないので、”Applies to “の範囲は変更されないはずです
行/列は削除できますが、挿入すると “Applies To “の範囲が分割されてしまいます
最後の行/列を選択し、「カーソル」の左下にある小さな四角を「コピー・ドラッグ」して領域を拡張します。この方法では、「適用先」の範囲はそのままになります。(この方法では、挿入のように行や列の数式は拡張されないことに注意してください)
しかし、フォーマットなどを使わずに貼り付けることを覚えるのは、まったくもって面倒です。普段はしていません
私は時々、フォーマットしたい領域の名前を定義することもあります。これはルールの中では使われませんが、1つのルールを除いてすべてのルールを削除して、”Applies to” セクションでエリア名を使うことで、ルールをすっきりさせることができます
MacでExcel16.3を使ってテストしてみました（Office2016かな…うーん）
0 Erk 2019-11-01
