Excelで数式を入力できることは誰もが知っています。 (結局、数式がないスプレッドシートはどうなるでしょうか?)数式でアドレス参照を使用する場合、セル、行、または列を挿入または削除すると、それらの参照は自動的に更新され、それらの変更はアドレス参照に何らかの影響を及ぼします。たとえば、次の簡単な式を考えてみましょう。

=IF(A7=B7,"YES","NO")

B7の上にセルを挿入すると、数式はExcelによって自動的に調整され、次のように表示されます。

=IF(A7=B8,"YES","NO")

ただし、Excelで数式を調整したくない場合はどうなりますか?アドレスにドル記号を追加しようとするかもしれませんが、これは後でコピーされる数式のアドレスにのみ影響します。数式に影響するセルを挿入または削除しても、数式自体には影響しません。

数式参照を作成するための最良の方法は?非調整?さまざまなワークシート関数を使用するように数式自体を変更することです。たとえば、セルC7で次の数式を使用できます。

=IF(INDIRECT("A"&ROW(C7))=INDIRECT("B"&ROW(C7)),"YES","NO")

この数式は、数式が表示されるセルに基づいてアドレスを作成します。ROW関数はセルの行番号を返し(この場合はC7なので、値7が返されます)、INDIRECT関数を使用して参照します。 A7やB7などの構築されたアドレス。 A7またはB7の上にセルを挿入(または削除)しても、セルC7の参照は、まったく新しいアドレスを簡単に作成するため、邪魔されません。

別のアプローチは、OFFSET関数を使用して、同様のタイプの参照を作成することです。

=IF(OFFSET($A$1,ROW()-1,0)=OFFSET($B$1,ROW()-1,0),"YES","NO")

この数式は、単にそれがどこにあるか(列C)を調べ、その左側にあるセルの値を比較します。列Aまたは列Bのいずれかにセルを挿入または削除した場合でも、この数式は同様に影響を受けません。

最後のアプローチ(そしておそらく最も巧妙なアプローチ)は、名前付きの数式を使用することです。これは、ほとんどの人がめったに使用しないExcelの命名機能の機能です。次の手順に従ってください:

。セルC2を選択します。

。 「挿入」メニューから「名前」を選択し、サブメニューから「定義」を選択します。

Excelに[名前の定義]ダイアログボックスが表示されます。 (図1を参照)

。 [ブック内の名前]ボックスに、CompareMeという名前を入力します。 (必要に応じて、別の名前を使用できます。)

。 [参照先]ボックスにあるものをすべて消去し、次の式に置き換えます。

。 [OK]をクリックします。

この時点で、名前付き数式を作成しました。これで、次の方法で列Cの任意のセルで使用できます。

=CompareMe

元の数式が設計されたとおりに、左側の2つのセルにあるものを比較します。さらに良いことに、セルを挿入または削除しても数式は自動的に調整されません。

_ExcelTips_は、費用効果の高いMicrosoftExcelトレーニングのソースです。

このヒント(2876)は、Microsoft Excel 97、2000、2002、および2003に適用されます。Excel(Excel 2007以降)のリボンインターフェイス用のこのヒントのバージョンは、次の場所にあります。

link:/ excelribbon-Non-adjusting_References_in_Formulas [数式内の非調整参照]