メインコンテンツへスキップ

Excel絶対リファレンス(作り方と使い方)

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

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


ビデオ: 絶対参照

 


絶対参照とは

 

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

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

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

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

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

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

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

相対参照の使用   絶対参照の使用
doc 絶対参照 3 1   doc 絶対参照 4 1

絶対参照の作り方

 

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

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

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

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

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

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

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

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

絶対参照 f4 トグル 1

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

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

絶対参照 f4 トグル 2


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

 

このパートでは、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 までの数字がリストされており、それらを掛け算します。

doc 絶対参照 15 1

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

doc 絶対参照 16 1

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

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

=$B3*C2

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

doc 絶対参照 17 1

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

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

doc 絶対参照 18 1

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

=$B3*C$2

doc 絶対参照 19 1

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

doc 絶対参照 20 1


覚えておくべきこと

 
  • セル参照のまとめ

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

    $A1/A$1

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

  • 最大XNUMXWの出力を提供する F4 キーで相対参照、絶対参照、混合参照を切り替えることができます。

最高のオフィス生産性向上ツール

人気の機能: 重複を検索、強調表示、または識別する   |  空白行を削除する   |  データを失わずに列またはセルを結合する   |   数式なしのラウンド ...
スーパールックアップ: 複数の基準の VLookup    複数の値の VLookup  |   複数のシートにわたる VLookup   |   ファジールックアップ ....
詳細ドロップダウン リスト: ドロップダウンリストを素早く作成する   |  依存関係のドロップダウン リスト   |  複数選択のドロップダウンリスト ....
列マネージャー: 特定の数の列を追加する  |  列の移動  |  Toggle 非表示列の表示ステータス  |  範囲と列の比較 ...
注目の機能: グリッドフォーカス   |  デザインビュー   |   ビッグフォーミュラバー    ワークブックとシートマネージャー   |  リソースライブラリ (自動テキスト)   |  日付ピッカー   |  ワークシートを組み合わせる   |  セルの暗号化/復号化    リストごとにメールを送信する   |  スーパーフィルター   |   特殊フィルター (太字/斜体/取り消し線をフィルター...) ...
上位 15 のツールセット12 テキスト 工具 (テキストを追加, 文字を削除する、...)   |   50+ チャート 種類 (ガントチャート、...)   |   40+ 実用的 (誕生日に基づいて年齢を計算する、...)   |   19 挿入 工具 (QRコードを挿入, パスから画像を挿入、...)   |   12 変換 工具 (数字から言葉へ, 通貨の換算、...)   |   7 マージ&スプリット 工具 (高度な結合行, 分割セル、...)   |   ... もっと

Kutools for Excel で Excel スキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、生産性を向上させ、時間を節約するための300以上の高度な機能を提供します。  最も必要な機能を入手するにはここをクリックしてください...

kteタブ201905


Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations