重要なポイント
- Excel のセル参照は、数式で特定の結果を実現するために、相対参照、絶対参照、または混合参照にすることができます。
- 相対参照は別のセルにコピーされると変更されますが、絶対参照は変わりません。
- 混合参照は、数式内の列または行のいずれかをロックします。
Excel で大量のデータを扱う場合、相対参照、絶対参照、または混合参照を使用して、反復的な手動作業を省略し、数式が正しく動作することを確認し、わずか数回の簡単な操作で大量のデータを取得できます。これらの Excel 参照型の使用方法を見てみましょう。
Excelのセル参照とは何ですか?
別のセルまたはセル範囲内に含まれる情報を取得する場合は、セル参照を使用します。たとえば、次の数式はセル A1 と A2 を参照し、それらの内容を加算して結果を生成します。
=SUM(A1+A2)
次のタイプのセル参照を使用して、特定の結果を達成できます。
- デフォルトでは、Excel の参照は 相対的 であり、セルの相対位置を参照します。セル A2 に関係する数式をセル A1 に入力している場合は、入力しているセルの 1 つ下のセルを参照していることになります。数式を別のセルにコピーすると、参照は前の場所と同じ相対アクションを自動的に実行します。
- 絶対 参照を使用することもできます。これは、数式を別のセルにコピーしても変更されません。絶対参照を使用してセルを参照する数式を作成すると、数式が使用される場所では常に同じセルが参照されます。
- 最後に、この 2 つを組み合わせた 混合 参照を使用できます。
これらについてさらに詳しく見てみましょう。
Excel で相対参照を使用する方法
ここに示す例では、その人が各月に各アイテムにいくら費やしたかがわかります。彼らが今やりたいことは、各月の合計支出を計算することです。
まず、最初の計算を行うセル (この場合は H2) をクリックし、この計算を作成する数式を入力します。この例では、次の式を使用します。
=SUM(E2+E12+E22)
これにより、計算内でセル E2、E12、および E22 が参照され、それらが加算されます。これは相対参照です。セル H2 の数式は、左に 3 列 (E2)、左に 3 列で下に 10 行 (E12)、および左に 3 列で下に 20 行あるセルを参照しています ( E22)、それぞれ。
ここで、同じ数式をコピーして別の場所に貼り付けると、貼り付けた場所と同じ相対位置にある値が取得されます。 残りのデータを同じ相対参照で完成させることもできます。
ここでわかるように、セル H11 の数式は次のとおりです。
=SUM(E11+E21+E31)
これは、相対参照が元の場所から 9 行下に配置されるためです。今後は、各月の数式を入力するのではなく、数式を 1 回入力するだけで、Excel ワークシート上の任意の場所に同じ相対参照を適用することができます。
Excel で絶対参照を使用する方法
この例では、会社の会計士は、従業員の労働時間数と固定時給に基づいて従業員に支払う金額を計算したいと考えています。
まず、最初の合計を計算するセル (この場合は E2) をクリックし、適切な数式の入力を開始します。この例では、会計士はセル A2 の時給に、D2 の Jacinda が働いた合計時間を乗算したいと考えています。
=SUM(A2
ただし、ここで相対参照を使用すると、他の従業員の合計に数式を適用するときに、数式はラウルについてはセル A3、ルーシーについてはセル A4 などを参照することになり、必要な結果が得られなくなります。
したがって、Excel が正しいセル (この場合は A2) を参照し続けることを確認する必要があります。これを行うには、参照の各部分の前にドル記号を挿入する必要があります。
=SUM($A$2
これにより、他の場所で同じ数式を使用した場合、常に A2 が参照されることが Excel に通知されます。
ドル記号を手動で入力する代わりに、セル参照を入力した後に F4 キーを押すと (キーボードによっては、 ファンクション キーの機能を学ぶ 必要がある場合があります)、ドル記号が自動的に表示されます。
「 Excel で相対参照、絶対参照、および混合参照を使用する方法」に関するベスト動画選定！
これで数式を完成させることができます。この場合、次のようになります。
=SUM($A$2*D2)
式 (D2) の 2 番目の部分で絶対参照が使用されていないことに注意してください。これは、セル A2 に各従業員のそれぞれの時間を掛けたいためで、その値は列 D の各人の時間と相対的である必要があります。
セル A2 が絶対参照されることがわかっているので、数式 か、Excel のオートフィル機能を使用して、他のセルに数式を適用できます。
Excel で混合参照を使用する方法
数式内で列ではなく行をロックする必要がある場合や、その逆の場合があります。たとえば、この場合、会計士は、各従業員が個別の税率に基づいて支払わなければならない税金を計算したいと考えています。
したがって、列 E (税率) の値はロックする必要がありますが、行はロックする必要はありません。値は従業員ごとに異なるためです。また、年への参照は適応できる必要があるため、ロックする必要はありません。
最初の計算を行うセル (この場合はセル G2) をクリックし、数式を入力します。上の例では、会計士は Arjun の 2021 年の未払い税を計算することから始めたいと考えているため、次の式を入力します。
=SUM(B2*E2)
次に、列 E をロックする必要があります。数式を修正せずに右側にオートフィルを実行すると、Arjun の 2022 年の納税額が正しく計算されません。 (セル B2 をロックしていないため) 正しい年収合計が正確に取得されますが、数式はセル F2 も参照します。これを行うには、数式内の列 E への参照の前にドル記号を追加します。
=SUM(B2*$E2)
ここでは絶対的な参照を使用したくありません。これは、他の従業員に支払うべき税金を計算するときに、各従業員の個別の税率の代わりに Arjun の税率が使用されることを意味するためです。本質的に、列 E のみをロックするということは、税率を維持しながら、各人の異なる税率に相対的に調整できることを意味します。
これで、オートフィル関数を使用して Arjun の納税額の合計を完成させることができます。これにより、列 E は計算でロックされたままになりますが、各年の合計は数式が配置された場所に相対的に移動することがわかります。
列 E をロックしましたが、数式内で行を相対的に残したので、個々の税率に基づいて残りの従業員の合計を自信を持って完成させることができます。結果内の任意のセルをクリックすると、数式バーに数式が表示され、正しいセルが参照されているかどうかを確認できることに注意してください。
Excel でさまざまな種類のセル参照を切り替える方法
Excel シート内で相対参照、絶対参照、および混合参照を簡単に切り替えたい場合は、毎回ドル記号を入力する必要はありません。代わりに、修正するセルをクリックし、数式バーで切り替えたい数式の部分をクリックします (参照の直前、参照の途中、または参照の直後をクリックすると機能します)。
この場合、セル G11 のセル参照タイプを切り替える必要があるため、そのセル参照上またはその隣のどこかをクリックする必要があります。
次に、F4 キーを押すと、セル参照タイプが変更されることがわかります。
まず絶対参照に切り替わります。
$G$11
次に、行がロックされた混合参照:
G$11
次に、列がロックされた混合参照:
$G11
そして最後に、デフォルトの相対参照に戻ります。
G11
同じ数式内の複数の参照を同時に切り替えたい場合は、数式内の関連する参照を強調表示し、同じ手順に従います。
これで完了です。ドル記号が存在するだけで、Excel でのセル参照を迅速かつ正確に整理できることを誰が知っていたでしょうか。
