Excel の数式内のセルを参照する場合、デフォルトの参照タイプは相対参照です。 これらの参照は、数式が他のセルにコピーされると、相対的な列と行に基づいて変更されます。 数式がコピーされる場所に関係なく、参照を一定に保ちたい場合は、絶対参照を使用する必要があります。

• 絶対参照とは
• 絶対参照の作り方
• 例では絶対参照を使用する
• 合計に対する割合を計算します
• 値を探して、対応する一致値に戻ります
• Kutoolsを使用して2クリックでセル参照を絶対にバッチ作成します
• 相対参照と混合参照
• 覚えておくべきこと

サンプルファイルを無料でダウンロード

ビデオ: 絶対参照

絶対参照とは

絶対参照は Excel のセル参照の一種です。

数式が他のセルにコピーされたときに相対位置に基づいて変化する相対参照と比較して、絶対参照は数式がどこにコピーまたは移動されても一定のままです。

絶対参照は、数式内の列参照と行参照の前にドル記号 ($) を追加することで作成されます。 たとえば、セル A1 の絶対参照を作成するには、それを $A$1 として表す必要があります。

絶対参照は、複数のセルにコピーされる数式内の固定セルまたは範囲を参照したいが、参照を変更したくない場合に便利です。

たとえば、範囲 A4:C7 には製品の価格が含まれており、セル B2 の税率に基づいて各製品の支払うべき税金を取得するとします。

"=B5*B2" のような数式で相対参照を使用すると、オート フィル ハンドルを下にドラッグしてこの数式を適用すると、間違った結果が返されます。 セル B2 への参照は、数式内のセルに対して相対的に変化するためです。 ここで、セル C6 の数式は「=B6*B3」であり、セル C7 の数式は「=B7*B4」です。

ただし、数式「=B2*$B$5」でセル B2 への絶対参照を使用すると、オートフィル ハンドルを使用して数式を下にドラッグしたときに税率がすべてのセルで同じになるため、結果は正しくなります。

相対参照の使用		絶対参照の使用

絶対参照の作り方

Excel で絶対参照を作成するには、式の列と行の参照の前にドル記号 ($) を追加する必要があります。 絶対参照を作成するには、次の XNUMX つの方法があります。

ドル記号をセル参照に手動で追加する

セルに数式を入力するときに、絶対にしたい列と行の参照の前にドル記号 ($) を手動で追加できます。

たとえば、セル A1 と B1 の数値を加算して両方を絶対値にしたい場合は、数式を「=$A$1+$B$1」と入力します。 これにより、数式が他のセルにコピーまたは移動されたときに、セル参照が一定のままになります。

または、セル内の既存の数式の参照を絶対参照に変更する場合は、セルを選択し、数式バーに移動してドル記号 ($) を追加します。

ショートカット F4 を使用して相対参照を絶対参照に変換する

1. 数式が含まれるセルをダブルクリックして編集モードに入ります。
2. 絶対にしたいセル参照にカーソルを置きます。
3. イベント F4 列参照と行参照の両方の前にドル記号が追加されるまで、キーボードの キーを押して参照タイプを切り替えます。
4. イベント 入力します キーを押して編集モードを終了し、変更を適用します。

F4キーで相対参照、絶対参照、混合参照を切り替えることができます。

A1 → $A$1 → A$1 → $A1 → A1

数式ですべての参照を絶対にしたい場合は、数式バーで数式全体を選択し、 F4 キーを押して、列参照と行参照の両方の前にドル記号が追加されるまで、参照タイプを切り替えます。

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

例では絶対参照を使用する

このパートでは、Excel 式で絶対参照をいつ、どのように使用するかを示す 2 つの例を示します。

例 1 合計のパーセンテージを計算する

各果物の売上を含むデータ範囲 (A3:B7) があり、セル B8 にこれらの果物の総売上高が含まれているとします。ここで、合計に対する各果物の売上のパーセンテージを計算する必要があります。

合計のパーセンテージを計算するための一般的な式:

Percentage = Sale/Amount

次のように、式で相対参照を使用して最初の果物の割合を取得します。

=B4/B8

自動塗りつぶしハンドルを下にドラッグして他の果物の割合を計算すると、#DIV/0! エラーが返されます。

自動塗りつぶしハンドルをドラッグして数式を下のセルにコピーすると、相対参照 B8 は、相対位置に基づいて他のセル参照 (B9、B10、B11) に自動的に調整されます。 また、セル B9、B10、B11 は空 (ゼロ) で、除数がゼロの場合、数式はエラーに戻ります。

この場合、エラーを修正するには、数式内のセル参照 B8 を絶対 ($B$8) にして、数式をどこに移動またはコピーしても変更されないようにする必要があります。 これで、式は次のように更新されました。

=B4/$B$8

次に、自動塗りつぶしハンドルを下にドラッグして、他の果物の割合を計算します。

例 2 値を検索し、対応する一致値に戻る

D4:D5 で名前リストを検索し、範囲 (A4:B8) で指定されたスタッフ名と対応する年収に基づいて対応する給与を返すとします。

ルックアップの一般式は次のとおりです。

=VLOOKUP(lookup_value, table_range, column_index, logical)

数式で相対参照を使用して値を検索し、対応する一致値を次のように返す場合:

=VLOOKUP(D4,A4:B8,2,FALSE)

次に、オート フィル ハンドルを下にドラッグして下の値を検索すると、エラーが返されます。

塗りつぶしハンドルを下にドラッグして数式を下のセルにコピーすると、数式内の参照が自動的に 4 行下に調整されます。 その結果、テーブル範囲 A8:B5 への参照は A9:B5 になります。 「Lisa: が範囲 A9:BXNUMX に見つからないため、数式はエラーを返します。

エラーを回避するには、式で相対参照 A4:B8 の代わりに絶対参照 $A$4:$B$8 を使用します。

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

次に、自動入力ハンドルを下にドラッグして、Lisa の給与を取得します。

Kutoolsを使用して2クリックでセル参照を絶対にバッチ作成します

手動で入力するか、F4 ショートカットを使用するかに関係なく、Excel で一度に変更できる数式は XNUMX つだけです。 Excel で何百もの絶対的な数式でセル参照を作成する場合は、 参照の変換 のツール Kutools for Excel 2 回クリックするだけでジョブを処理できます。

セル参照を絶対にする数式セルを選択し、 クツール > もっと見る (FX) > 参照の変換。 次に、 絶対に オプションをクリックして Ok or 申し込む。 これで、選択した数式のすべてのセル参照が絶対参照に変換されました。

相対参照と混合参照

絶対参照の他に、相対参照と混合参照という XNUMX つの参照タイプがあります。

相対参照 Excel のデフォルトの参照型であり、行および列の参照の前にドル記号 ($) がありません。 また、相対参照を含む数式を他のセルにコピーまたは移動すると、参照は相対位置に基づいて自動的に変更されます。

たとえば、セルに「=A1+1」などの数式を入力し、自動入力ハンドルを下にドラッグしてこの数式を次のセルに入力すると、数式は自動的に「=A2+1」に変更されます。

混合参照 は、絶対参照と相対参照の両方で構成されます。 つまり、混合参照では、数式がコピーまたは入力されるときに、ドル記号 ($) を使用して行または列のいずれかを固定します。

九九を例にとると、行と列には 1 から 9 までの数字がリストされており、それらを掛け算します。

まず、セル C3 の数式「=B2*C3」を使用して、セル B1 の 3 と最初の列の数値 (1) を掛けます。 ただし、自動入力ハンドルを右にドラッグして他のセルを埋めると、最初の結果を除くすべての結果が間違っていることがわかります。

これは、数式を右側にコピーすると、行の位置は変更されませんが、列の位置が B3 から C3、D3 などに変更されるためです。その結果、右側のセル (D3、E3、実際に「=B3*D2」、「=B3*E2」などにしたい場合は、「=C3*D2」、「=D3*E2」などに変更してください。

この場合、ドル記号 ($) を追加して、「B3」の列参照をロックする必要があります。 次のように式を使用します。

=$B3*C2

ここで、数式を右にドラッグすると、結果は正しくなります。

次に、セル C1 の数値 2 に、下の行の数値を掛ける必要があります。

数式を下にコピーすると、セル C2 の列の位置は変わりませんが、行の位置は C2 から C3、C4 などに変わります。その結果、下のセルの数式は「=$B4C3」に変わります。 「=$B5C4」など、誤った結果が生成されます。

この問題を解決するには、自動入力ハンドルを下にドラッグして数式を入力するときに行参照が変更されないように、「C2」を「C$2」に変更します。

=$B3*C$2

オートフィルハンドルを右または下にドラッグして、すべての結果を取得できるようになりました。

覚えておくべきこと

• セル参照のまとめ

  タイプ	例	まとめ
  絶対参照	$ A $ 1	数式が他のセルにコピーされても変更しない
  相対参照	A1	数式を他のセルにコピーすると、行と列の両方の参照が相対位置に基づいて変更されます。
  混合参照	$A1/A$1	数式が他のセルにコピーされると行参照が変更されますが、列参照は固定されます/数式が他のセルにコピーされると列参照は変更されますが、行参照は固定されます。

• 一般に、数式を移動しても絶対参照が変更されることはありません。 ただし、絶対参照は、ワークシートの上部または左から行または列が追加または削除されると自動的に調整されます。 たとえば、数式「=$A$1+1」では、シートの先頭に行を挿入すると、数式は自動的に「=$A$2+1」に変更されます。

  

• 　 F4 キーで相対参照、絶対参照、混合参照を切り替えることができます。