Excel の数式における $ 記号の威力を過小評価しないでください

Posted on 8 3月 10:47 pm

Microsoft Excel は通常、「1 つ左側のセルを見てください」という方向で考えます。数式を移動して参照が失われるまでは、これで問題ありません。ドル ($) 記号の使用方法を理解することで、いつ永久住所を参照し、いつ移動ターゲットを参照するかを Excel に指示することができます。つまり、より強固な数式を作成できることになります。

私は販売トラッカーを使用しています。価格と数量はメインデータセットにあり、税金と手数料率は別の「定数」セルにあります。目標は、グリッド上でドラッグしても正確な数式を構築することです。

税金と手数料の定数を含む販売トラッカーと製品データテーブルを示す Excel スプレッドシート。

読み進めるには、Excel ワークブックの無料コピーをダウンロードしてください。リンクをクリックすると、画面の右上隅にダウンロードボタンが表示されます。

相対参照: 数式を一緒に動かしましょう

同じ相対ロジックを列の下に適用します

$ 記号の魅力に触れる前に、$ 記号なしで Excel がどのように動作するかを理解する必要があります。 B6 のような標準セル参照は、実際には単なる方向のセットにすぎません。たとえば、次のように入力すると、 =B6*C6 細胞に D6Excel は、「2 スペース左のセルを取得し、1 スペース左のセルと掛けます。」と考えます。

この相対的なロジックが Excel を非常に強力にしているのです。数式は固定アドレスではなく位置に基づいているため、行ごとに計算を書き直す必要はありません。

セル内 E6、数式を入力します =C6*D6 そして押します Ctrl+Enter 現在のセルを選択したまま数式をコミットします。これにより、ウィジェット A の小計が計算されます。
クリックしてドラッグすると、 フィルハンドル セルの右下隅にある E6 セルまで E8。
セルを選択 E7、式が次のように調整されていることに注目してください。 =C7*D7。

数式を 1 行下に移動したため、Excel は一致するように方向を 1 行下に移動しました。セル E6 をコピーしてセル K15 に貼り付けると、数式は =I15*J15 に変わります。

塗りつぶしハンドルを手動でドラッグするのではなく、ダブルクリックします。隣接する列にデータがある限り、このダブルクリックトリックにより、Excel はデータセットの最後まで数式を入力します。

絶対参照: 参照用のアンカーを作成します。

一貫して同じセルを参照する

相対参照は、セル B3 の「Global Tax」乗数など、単一の固定セルを参照する必要があるまでは最適です。ここで相対的な式を使おうとすると、物事はすぐに崩れてしまいます。

入力すると =E6*B3 細胞に F6、最初の行で機能します。ただし、下にドラッグすると、次の数式が表示されます。 F7 になる =E7*B4。セル B4 が空であるため、計算ではゼロが返されます。

ウィジェット売上トラッカーを示す Excel シート。空のセルを指しているために相対参照式が壊れています。

これを修正するには、 $記号 数式をセルに固定するには B3:

セル内 F6、タイプ =E6*$B$3 そして押します Ctrl+Enter。 B の前の $ は列をロックし、3 の前の $ は行をロックします。
クリックしてドラッグすると、 フィルハンドル まで F8。
セルを選択 F7。こうなったことがわかります =E7*$B$3。相対的な合計座標は移動しましたが、税座標は配置した場所に正確に残りました。

$ 記号を手動で入力するのではなく、カーソルを数式バーのセル参照に触れた状態で、 F4 絶対参照のために一度。ただし、もう一度押すと、混合参照が表示されます (詳細は以下を参照)。

プロのヒント: 定数を参照するよりクリーンな方法

$B$3 で仕事は完了しますが、プロは代わりにセルに名前を付けることを好むことがよくあります。セルを選択した場合 B3 そして入力してください グローバル税金 に ネームボックス (数式バーの左側にある小さなフィールド)、数式は次のようになります。 =E6*グローバル_税。

名前付き範囲はデフォルトで絶対です。つまり、$ 記号のような見た目の煩雑さなしに、所定の位置にロックされたままになります。

Excel で手動で範囲に名前を付けるのをやめる: はるかに高速な方法があります

ヘッダーを読みやすい数式のデータに自動的にマッピングすることで、スプレッドシートを変換し、貴重な時間を節約します。

混合参照: セミロックされたグリッドをマスターする

行または列をいつ修正するかを選択します

混合参照は、Excel で高効率のスプレッドシートを構築する秘訣です。トラッカーでは、列 F の合計に基づいて 2 つの異なるコミッション層 (C3 の層 1 と D3 の層 2) を計算したいと考えています。列ごとに異なる数式を記述する代わりに、1 つの数式を記述してグリッド全体にドラッグできます。

セル内 G6、タイプ =$F6*3カナダドルそして押します Ctrl+Enter。 $F は列をロックするため、数式を列 H にドラッグすると、引き続き列 F の合計が表示されます。$3 は行をロックするため、下にドラッグすると、引き続き行 3 の手数料率が表示されます。
クリックしてドラッグすると、 フィルハンドル から G6 に H6。
G6 と H6 の両方を選択したまま、 フィルハンドル まで H8。
セルで結果を確認してください H8。式は次のようになります =$F8*D$3。

$ 記号が戦略的に配置されているため、Excel は数式のどの部分を移動し、どの部分を静止させるべきかを正確に認識しました。

条件付き書式設定でさまざまな参照タイプを使用する

$ の威力は標準のセル数式に限定されるものではなく、高度な条件付き書式設定の背後にあるエンジンでもあります。 Excel のデフォルトの「ドリフト」ロジックがグリッドと同様にダイアログ数式フィールドにも適用されることを認識していないため、多くの人がここで問題に遭遇します。

列 F の税込合計が 100 ドルを超える場合、行のすべてのセルを緑色にするとします。この場合、混合参照を使用する必要があります。 $ 記号がないと、各セルは独自の相対参照を評価するため、書式設定が不整合になります。

行う必要があるのは次のとおりです。

データを選択してください B6 に H8 (ヘッダー行ではありません)。
で家タブ、クリック 条件付き書式設定を選択し、 新しいルール。
クリック 数式を使用して書式設定するセルを決定する。
で この式が当てはまる場合に値をフォーマットします フィールド、タイプ =$F6>100。
クリック形式をクリックし、塗りつぶしの色 (薄緑など) を選択して、 わかりました。

列 ($F) をロックしたため、行内のすべてのセルは F 列の値を参照して、緑色に変えるかどうかを決定する必要があります。

売上トラッカーを示す Excel スプレッドシート。合計値に基づく条件付き書式ルールにより、ウィジェット C の行が緑色で強調表示されています。

データ検証ルールを設定するときなど、Excel ダイアログで数式フィールドを表示するときは常に、これと同じロジックが適用されます。

Excel の条件付き書式が壊れる理由 (およびそれを迅速に修正する方法)

ルールマネージャーを監査し、断片化した範囲を統合し、安定した Excel テーブルに切り替えることで、ルールの肥大化を防ぎます。

$ 記号は数式のハンドルとして機能し、どの参照を固定し、どの参照を移動するかを制御できます。ただし、これは、作業が複雑になるにつれて頻繁に使用される Microsoft Excel シンボルの 1 つにすぎません。これらの文字の使い方を学ぶと、スプレッドシートがより堅牢になり、計算が正確に保たれ、ワークフローが無限に効率化されます。