相対セル参照と絶対セル参照、および書式設定

このレッスンでは、セル参照、数式をコピーまたは移動する方法、セルの書式設定について説明します。はじめに、セル参照によって我々が何を意味するのかを明確にしましょう。それは式と関数の力と汎用性の多くを支えるものです。セル参照がどのように機能するかを具体的に把握することで、Excelスプレッドシートを最大限に活用することができます。!

学校のナビゲーション

1. なぜ式や関数が必要なのか?
2. 式の定義と作成
3. 相対セル参照と絶対セル参照、および書式設定
4. 知っておくべき便利な機能
5. ルックアップ、チャート、統計、およびピボットテーブル

注意： セルがスプレッドシート内の正方形の1つであり、縦横に並んでいる文字と数字で参照される列と行に配置されていることを、すでに知っていると思います。.

セル参照とは?

「細胞参照」は、他の細胞が参照する細胞を意味する。たとえば、セルA1に= A2があるとします。それからA1はA2を参照します.

セル参照をさらに調べることができるように、行と列についてレッスン2で述べたことを確認しましょう。.

スプレッドシート内のセルは行と列によって参照されます。列は垂直で、文字でラベル付けされています。行は水平で、番号が付いています.

スプレッドシートの最初のセルはA1です。つまり、列A、行1、B3は2列目、3行目などにあるセルを表します。.

セル参照について学ぶために、行、列として書くこともありますが、これはスプレッドシートでは有効な表記法ではなく、単に物事を明確にするためのものです。.

セル参照の種類

セル参照には3種類あります.

絶対 - セルを他のセルにコピーまたは移動しても、セル参照は同じままです。これは行と列を固定することで行われるため、コピーまたは移動しても変わりません。.

相対 - 相対参照とは、セルアドレスをコピーまたは移動すると変更されることを意味します。すなわち、セル参照はその位置に関連しています.

混合 - セルをコピーまたは移動するときに行と列のどちらを固定するかを選択できるので、一方が変更され、もう一方は変更されません。たとえば、行参照を固定してからセルを2行下の4列に移動しても、行参照は同じままです。これについては後でさらに説明します。.

相対参照

先ほどの例を参考にしてみましょう - セルA1に= A2という単純な式があるとします。これは、セルA2に入力されたものが何であれ、セルA1にExcelが出力されることを意味します。セルA2に「A2」と入力したので、ExcelはセルA1に値「A2」を表示します。.

それでは、スプレッドシートに追加のデータ用のスペースを確保する必要があるとします。上に列を追加し、左に行を追加する必要があるため、スペースを空けるためにセルを右下に移動する必要があります。.

セルを右に移動すると、列番号が増えます。下に移動すると、行番号が増えます。それが指すセル、セル参照も変わります。これを以下に示します。

この例を続けて、下の図を見て、セルA1の内容を2つ右に、4つ下にコピーすると、セルC5に移動したことになります。.

セルを2列右と4列下にコピーしました。これは、横に2つ、下に4つ参照するセルを変更したことを意味します。 Ａ１ ＝ Ａ２は今やＣ５ ＝ Ｃ６である。 A2を参照する代わりに、今すぐセルC5がセルC6を参照します。.

セルC6が空なので、表示された値は0です。セルC6に「I am C6」と入力すると、C5に「I am C6」と表示されます。

例：テキスト式

別の例を試してみましょう。フルネームを姓と名に分割しなければならなかったレッスン2から覚えていますか。この式をコピーするとどうなりますか?

式= RIGHT（A3、LEN（A3） - FIND（ "、"、A3） - 1）を書くか、セルC3にテキストをコピーします。実際のセル、テキストのみをコピーしないでください、テキストをコピーしないでください、そうでなければそれは参照を更新します.

スプレッドシートの上部にある「fx」と表示されている横のボックスにあるセルの内容を編集できます。そのボックスは、セルの幅よりも長いため、編集する方が簡単です。.

今我々は持っています：

複雑なことは何もありませんが、セルC3に新しい式を書いたところです。 C3をセルC2とC4にコピーします。以下の結果を確認してください。

今、私たちはアレクサンダーハミルトンとトーマスジェファーソンのファーストネームを持っています.

カーソルを使用してセルC2、C3、およびC4を強調表示します。セルB2にカーソルを合わせて内容を貼り付けます。何が起こったのか見てください。エラーが出ます：“ #REF”。?

C列からB列にセルをコピーすると、参照を1列左に更新しました= RIGHT（A2、LEN（A2） - FIND（“、”、A2） - 1）.

A2へのすべての参照をAの左側の列に変更しましたが、列Aの左側には列がありません。したがって、コンピュータはユーザーが何を意味するのかわかりません.

たとえば、B2の新しい式は= RIGHT（#REF！、LEN（#REF！） - FIND（“、”、＃REF！） - 1）で、結果は#REFです。

数式をさまざまなセルにコピーする

セルをコピーするのは非常に便利です。1つの式を書いてそれを広い領域にコピーすると、参照が更新されるからです。これにより、各セルが正しい場所を指すように編集する必要がなくなります。.

「範囲」とは、複数の細胞を意味します。たとえば、（C1：C10）はセルC1からセルC10までのすべてのセルを意味します。だからそれはセルの列です。別の例（A1：AZ1）は、列Aから列AZまでの最上行です。.

範囲が5列10行にまたがる場合は、左上のセルと右下のセルに書き込むことで範囲を指定します（例：A1：E10）。これは、行と列を横切る正方形の領域で、列の一部や行の一部ではありません。.

これは、1つのセルを複数の場所にコピーする方法を示す例です。予算を立てるために、その月の予想支出をスプレッドシートに表示したいとします。このようなスプレッドシートを作ります。

セルC3（= B3 + C2）の式を残りの列にコピーして、予算の現在の残高を計算します。コピーすると、Excelがセル参照を更新します。結果は以下の通りです。

ご覧のとおり、新しいセルが更新されるたびに 相対 セルC4はその式を= B4 + C3に更新します。

セルC5は、= B5 + C4に更新されます。

絶対参照

セルを移動またはコピーしても絶対参照は変わりません。絶対参照をするために$記号を使います - それを覚えておくために、アンカーとしてドル記号を考えてください.

たとえば、任意のセルに数式= $ A $ 1と入力します。列Aの前の$は列を変更しないことを意味し、行1の前の$はセルを他のセルにコピーまたは移動したときに列を変更しないことを意味します。.

下の例でわかるように、セルB1には相対参照= A1があります。B1をその下の4つのセルにコピーすると、相対参照= A1が左側のセルに変わるため、B2はA2、B3になります。これらのセルには明らかに値が入力されていないので、出力はゼロです。.

ただし、C1のように= $ A1 $ 1を使用してその下の4つのセルにコピーすると、参照は絶対値になるため、変更されることはなく、出力は常にセルA1の値と等しくなります。.

以下の例のように、あなたがあなたの興味を追跡しているとしましょう。 C4 = B4 * B1の式は、「金利」*「残高」=「1年あたりの利子」です。

これで、あなたは予算を変更し、投資信託を購入するためにさらに2,000ドルを節約しました。それが固定金利ファンドであり、同じ金利を支払っているとします。スプレッドシートに新しいアカウントと残高を入力し、セルC4からセルC5に数式= B4 * B1をコピーします。.

新しい予算は次のようになります。

新しいミューチュアルファンドは1年あたり0ドルの利子を稼いでいますが、金利は明らかに5パーセントであるため、これは正しくありません。.

Excelは、数式が参照するセルを強調表示します。上の図では、金利（B1）への参照が空のセルB2に移動しています。行と列の参照を固定するためにドル記号を使用して$ B $ 1と書くことで、B1への参照を絶対にする必要がありました。.

以下に示すように、C4の最初の計算を= B4 * $ B $ 1と書き換えます。

その式をC4からC5にコピーします。スプレッドシートは次のようになりました。

数式を1セル下にコピーした、つまり行を1つ増やしたので、新しい数式は= B5 * $ B $ 1です。金利がセルB1に固定されているため、ミューチュアルファンドの金利は正しく計算されます。.

これは、セルを参照するために「名前」を使用できる場合の良い例です。名前は絶対参照です。たとえば、セルB1に「金利」という名前を付けるには、セルを右クリックしてから「名前の定義」を選択します。

名前は1つのセルまたは範囲を参照することができ、式の中で名前を使用することができます。たとえば、= interest_rate * 8は= $ B $ 1 * 8と同じです。.

混合参照

混合参照はいつですか どちらか 行 または 列は固定されています.

たとえば、あなたが農家で予算を組んでいるとします。あなたは飼料店を所有していて種子を売っています。あなたはトウモロコシ、大豆、そしてアルファルファを植えるつもりです。下のスプレッドシートは1エーカーあたりのコストを示しています。 「1エーカーあたりのコスト」=「1ポンドあたりの価格」*「1エーカーあたりの種子のポンド」 - それはそれがあなたが1エーカーを植えるのにかかる費用です.

セルD2に= $ B2 * C2として1エーカーあたりのコストを入力します。ポンド当たりの価格を固定したいと言っています。その式を同じ列の他の行にコピーします。

今、あなたは種子のあなたの目録の価値を知りたいです。在庫の価値を知るには、1ポンドあたりの価格と在庫のポンド数が必要です。.

2つの列を追加します。「在庫の種のポンド」、「在庫の値」です。次に、セルD2をF4にコピーして、元の数式の最初の部分（$ B2）の行参照がrowに更新されます。 4しかし、列は固定されたままです。

列は絶対列で行は相対列であるため、これは混合参照です。.

循環参照

循環参照は、式がそれ自体を指す場合です.

たとえば、c3 = c3 + 1と書くことはできません。この種の計算は「繰り返し」と呼ばれ、繰り返します。 Excelはすべてを1回しか計算しないため、繰り返し処理をサポートしていません。.

セルB5にSUM（B1：B5）を入力してこれを試すとします。

警告画面がポップアップ表示されます。

あなたがそれに気付かないかもしれないので、Excelはあなたにあなたがスクリーンの底に循環参照を持っているとあなたに言うだけです。循環参照があり、スプレッドシートを閉じて再度開くと、ポップアップウィンドウに循環参照があることが表示されます。.

循環参照がある場合は、スプレッドシートを開くたびに、ポップアップウィンドウで循環参照があることをExcelから通知されます。.

他のワークシートへの参照

「ワークブック」は「ワークシート」の集まりです。簡単に言えば、これは同じExcelファイル（ワークブック）に複数のスプレッドシート（​​ワークシート）を含めることができるということです。以下の例からわかるように、このサンプルワークブックには多数のワークシートがあります（赤）。.

デフォルトのワークシートは、Sheet1、Sheet2などの名前です。 Excel画面の下部にある「+」をクリックして新しいものを作成します。.

Excelプログラム画面の下部に表示されているワークシートのタブを右クリックし、名前の変更を選択して新しい名前を入力することで、ワークシートの名前を「融資」や「予算」などの便利な名前に変更できます。.

または、タブをダブルクリックして名前を変更することもできます。.

ワークシート参照の構文は= worksheet！cellです。 2つのワークシートで同じ値が使用されている場合は、この種の参照を使用できます。その例は次のとおりです。

• 今日の日付
• ドルからユーロへの通貨換算レート
• ワークブック内のすべてのワークシートに関連するものすべて

ワークシート "loan"を参照しているワークシート "interest"の例を以下に示します。セルB1.

「ローン」ワークシートを見ると、ローン金額の参照がわかります。

次へ…

相対、絶対、混合など、セル参照をしっかりと把握してください。確かにたくさんあります.

今日のレッスンはこれで終わりです。レッスン4では、Excelを日常的に使用するために知っておきたい便利な機能について説明します。.