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

Excelで数式セル参照を一定に保つ方法は？

このチュートリアルでは、数式内のセル参照を定数 (絶対) 参照に変更して、数式が他の場所にコピーされたときにセル参照が変更されないようにする方法を説明します。

以下のスクリーンショットは、品目の合計コストを計算する式における 2 つの異なるタイプのセル参照を示しています。
  • 左側の数式では、割引率の参照が各行の下に誤ってシフトされています。からコピーする場合 D3 〜へ D4 & D5、それは参照します F4 & F5 固定割引率の代わりに F3、誤った計算につながります D4 & D5.
  • スクリーンショットの右側には、 $ F $ 3 は定数 (絶対) 参照です。携帯電話に連動した割引率を維持します F3、数式がワークブック内のどこにコピーされたかは関係ありません。したがって、式を列の下にコピーすると、すべての品目の割引率が一定に保たれます。

F4キーで数式セルの参照を一定に保つ

数式内で一定のセル参照を維持するには、F4 キーを押して列文字と行番号の前に $ 記号を追加するだけです。その方法は次のとおりです。

  1. 数式を含むセルをクリックします。
  2. 数式バーで、定数にしたいセル参照内にカーソルを置きます。ここでリファレンスを選択します F3.
  3. F4 キーを使用して、絶対参照に到達するまで参照タイプを切り替えます。これにより、 ドル記号 ($) 列文字と行番号の前に追加します。ここ F3 に変更されます $ F $ 3。次に Enter を押して変更を確認します。

これで、ブック内の数式がどこにコピーされたとしても、割引率がセル F3 にリンクされたままになります。

Note： を押すたびに、 F4 キーはさまざまな参照状態を循環します: 絶対 ($ A $ 1)、絶対列と相対行の混合 ($ A1)、相対列と絶対行の混合 ($ 1)、相対 (A1).

数回クリックするだけで、すべてのセル参照が範囲内で絶対参照になります。

上記の方法は、セルを 1 つずつ処理するのに役立ちます。複数のセルに絶対参照を一度に適用したい場合は、次の方法を強くお勧めします。 参照の変換 の特徴 Kutools for Excel。この機能を使用すると、数回クリックするだけで、1 つのセル範囲または複数のセル範囲内のすべてのセル参照を簡単に絶対参照にすることができます。

後 Kutools for Excelのインストール、に行きます クツール タブ、選択 その他 > 参照の変換 を開く 数式参照の変換 ダイアログボックス。次に、次のことを行う必要があります。

  1. すべてのセル参照を定数にしたい数式を含む範囲を 1 つまたは複数選択します。
  2. 選択する 絶対に オプションを選択し、次にクリックします。 OK 変換を開始する。

その後、選択した範囲内の数式のすべてのセル参照が直ちに定数参照に変更されます。

Note: この機能を使用するには、以下をインストールする必要があります Kutools for Excel まずはお願いします クリックしてダウンロードし、今すぐ 30 日間の無料トライアルをお試しください.

デモ：Kutools forExcelで数式セル参照を一定に保つ

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

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

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

説明

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

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
Comments (12)
Rated 5 out of 5 · 1 ratings
SM
SM
This comment was minimized by the moderator on the site
Thank you for this post, it was very helpful
Reply
0
0
Rated 5 out of 5
Muresan ALIN
Muresan ALIN
This comment was minimized by the moderator on the site
Not working if you have index match or vlooup in the formula
Reply
0
0
Austin314
Austin314
This comment was minimized by the moderator on the site
That Kutools "Convert Formula References" is perfect, exactly what I need. Unfortunately I can't install that, but it's nice to know someone else needed it and made an extension for it. At least it gives me some closure knowing I'll never find it in base Excel haha. Thanks.
Reply
Report
0
0
crystal
crystal     Austin314
This comment was minimized by the moderator on the site
Hi Austin,Did you get any error prompt when installing Kutools for Excel? 
Reply
Report
0
0
Austin314
Austin314     crystal
This comment was minimized by the moderator on the site
Hi Crystal! Oh, thanks for asking. I was on my Work PC so I knew I would be unable to install anything like this without going through the process of getting it approved with IT, presenting a solid reason to the team as to why we need to pay for additional software, etc. It would be a major headache, and I'm certain I would lose the argument. (Make no mistake, I'm all for it) But, unfortunately I settled for the smaller headache of doing what Kutools' "Convert Formula References" process would do, to a few hundred cells by hand. When I've made it to the big leagues, and I'm the boss, I'll ensure that my whole team is outfitted with Kutools. It's an awesome addition, for sure. 
Reply
Report
0
0
crystal
crystal     Austin314
This comment was minimized by the moderator on the site
Hi Austin314,Thank you very much for your feedback and I look forward to the day when your whole team is outfitted with Kutools.^_^
Reply
Report
0
0
Bharat
Bharat
This comment was minimized by the moderator on the site
Superb.....
Reply
0
0
Andrew
Andrew
This comment was minimized by the moderator on the site
Does not work. If I insert a row above the cell, the formula "tracks" the cell to its new location. This is not what your title implies. I need a formula that reads = B1 and if data gets inserted into row B:B then the formula reads = B1 and returns the new value. Absolute referencing does not do this.
Reply
0
0
Martin
Martin     Andrew
This comment was minimized by the moderator on the site
Andrew, that is exactly what I want too..... how can we hold the cell reference Completely constant in these circumstances?
Reply
0
0
Austin314
Austin314     Martin
This comment was minimized by the moderator on the site
Andrew, Martin! .....I came here for something else but I think I've got what you need.  You can also reference cells using the INDIRECT() function. For your case, if you go to cell C1 (or wherever) and set the equation in C1 to be =INDIRECT("B1"), then it will always return whatever is in B1, no matter what happens to B1. So then, if you insert a column of data into column B, your equation would move over to cell D1, and would continue to pull data from the brand new value in B1. The older referenced value is now in C1, with the column of data before. You can also perform mathematic operations on it as you would any other cell reference. Happy Spreadsheets :)
Reply
Report
0
0
Angelo Antonino
Angelo Antonino     Martin
This comment was minimized by the moderator on the site
Use the INDEX() function and for the Array use a range of columns to cover all your data (assuming no column inserts/deletes will happen)
Reply
0
0
Gavin Parker Rose
Gavin Parker Rose
This comment was minimized by the moderator on the site
Financial ratios are one of the most common tools of managerial decision making. A ratio is a comparison of one number to another—mathematically, a simple division problem. Financial ratios involve the comparison of various figures from the financial statements in order to gain information about a company's performance. It is the interpretation, rather than the calculation, that makes financial ratios a useful tool for business managers. Ratios may serve as indicators, clues, or red flags regarding noteworthy relationships between variables used to measure the firm's performance in terms of profitability, asset utilization, liquidity, leverage, or market valuation.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations