Excelの参照形式を理解しておくと、集計表などを作成するときに労力をかけずに作成できるようになります。
参照形式には、絶対参照、相対参照、複合参照(行のみ固定、列のみ固定)があります。
掛け算の九九表を作成しながら、複合参照の練習をしてみてください。
また、複合参照を使用せずに、もっと簡単に作成する方法もあります。
この記事の目次
掛け算九九表の準備
掛け算九九表を作成する準備をします。
列幅も行の高さも同じ碁盤目の表を作成します。
セル[B3]から下へ1から9まで入力し、セル[C2]から右へ1から9まで入力します。
オートフィルを使用すると簡単ですね。
また、[表示]タブから[ページレイアウト]表示にすると列幅も行の高さもセンチメートルで指定できるので、列幅と行の高さの調整は簡単になると思います。
設定したい行や列を範囲選択してまとめて指定します。
参考[表示]タブの[ページレイアウト]については、以下の記事で解説しています。
-
列幅と行の高さをセンチやミリ単位で調整するにはページレイアウトで
Excelには、[標準]、[改ページプレビュー]、[レイアウト]の3つの表示モードがあります。この表示モードを切り替えることで、作業を効率よ ...
碁盤目の九九表
以下のような碁盤目の九九表の準備ができました。
行と列に1から9までの見出しを付けた表です。
この表のセル[C3]のみに数式を入力して、それを横と縦にドラッグして数式をコピーして完成させます。
[F4]キーと参照形式
キーボードの[F4]キーを使って参照形式のパターンを変更できます。
[F4]キーを押す度に以下の流れで切り替わります。
[F4]キーを1回も押していない状態は相対参照の状態になります。
[F4]キーを押す時の数式バーのカーソル位置は、[=]の左側以外であればOKです。
[$]は、後ろに位置する行や列を固定します。
F4キーを押す回数 | セル表記 | 参照方法 |
---|---|---|
1回 | =$A$1 | 絶対参照(列も行も固定) |
2回 | =A$1 | 複合参照 行のみ絶対参照(行のみ固定) |
3回 | =$A1 | 複合参照 列のみ絶対参照(列のみ固定) |
4回 | =A1 | 相対参照(列も行も固定しない) |
絶対参照
セル[C3]に[=B3]と入力して、[F4]キーを1回押します。
数式を入力する時は、IMEの入力モードは[半角英数]モードにします。
行と列が固定されます。
このまま、横や縦にセル[C3]をコピーしても、セルには[=$B$3]となります。
セルの参照先は変わりません。行も列も固定している状態です。
複合参照(行のみ固定)
セル[C3]に[=B3]と入力して、[F4]キーを2回押します。
行が固定されます。ここでは3行目に固定されるということになります。
セル[C3]をセル[E3]までドラッグして、数式をコピーしてみます。
セル[E3]の数式は、[=D$3]となります。
列番号は動きますが、行は固定されています。
左右方向に移動すると、列番号の参照先が変わります。
セル[C3]をセル[C5]までコピーしてみます。
セル[C5]の数式は、[=B$3]となります。
列は固定されていませんが同じ列番号なのでそのままで、行は3行目に固定されています。
上下方向に移動しても参照先は変わりません。
複合参照(列のみ固定)
セル[C3]に[=B3]と入力して、[F4]キーを3回押します。
列が固定されます。ここではB列に固定されるということになります。
セル[C3]をセル[E3]までコピーしてみます。
セル[E3]の数式は、[=$B3]となります。
行は固定されていませんが同じ行番号なのでそのままで、列はB列に固定されています。
左右方向に移動しても参照先は変わりません。
セル[C3]をセル[C5]までコピーしてみます。
セル[C5]の数式は、[=$B5]となります。
上下方向に移動すると、行番号の参照先が変わります。
相対参照
[F4]キーを1回も押していない場合は、相対参照の状態です。
[F4]キーを4回押すと、1回も押していない状態と同じになります。
行も列も固定されていない状態です。
セル[C3]をセル[E3]までコピーすると、セル[E3]は[=D3]となります。
セルの列番号の参照先が2つ右へ移動します。
セル[C3]をセル[C5]までコピーすると、セル[C5]には[=B5]となります。
セルの行番号の参照先が下へ2つ移動します。
参考[F4]キーで切り替える相対参照、絶対参照、複合参照については、以下の記事でも解説しています。
-
相対参照と絶対参照と複合参照は[ F4 ]キーで切り替え
セルの参照方法は、相対参照、絶対参照、複合参照があります。 絶対参照と複合参照では、[$]を挿入する必要があります。 このとき、直接入力して ...
九九表を完成させるための数式をセル[C3]へ入力
以下の表は、セル[B3]からセル[B11]と、セル[C2]からセル[K2]には1から9までの数字を入力しています。
行列の見出し数字を参照先として、セル[C3]へ数式を入力します。
セル[C3]には、[=B3*C2]とします。
まず、数式バーに半角英数で[=]を入力した後、セル[B3]セルを選択します。数式バーには[=B3]と表示されます。
アスタリスク(*)を入力して、セル[C2]をクリックします。
アスタリスクは、以下のキーです。[Shift]キーと一緒に押します。
数式内のアスタリスク(*)の左[B3]の動き
掛け算の[B3]の動きを考えます。
前半の[B3]は、上下方向のみ動いてくれるといいですね。
なので、[B3]を列固定の複合参照にするため[B3]内にカーソルを移動して、[F4]キーを3回押します。
上下方向のみ動いてもらうには、列を固定した参照形式[$B3]が最適となります。
数式内のアスタリスク(*)の右[C2]の動き
掛け算の[C2]の動きを考えます。
後半の[C2]は、左右方向のみ動いてくれるといいですね。
なので、[C2]を行固定の複合参照にするため[C2]内にカーソルを移動して[F4]キーを2回押します。
左右方向に動いてもらうには、行を固定した参照形式[C$2]が最適となります。
よって、セル[C3]の数式は以下のようになります。
=$B3*C$2
セル[C3]の数式をコピー
この数式をセル[K11]までコピーします。
セル[C3]を選択して、セル[K3]までフィルハンドルをドラッグして数式をコピーします。
以下のようになります。
そのまま、フィルハンドルをセル[K11]まで下へドラッグします。
フィルハンドルをダブルクリックしてもOKです。
以下のように九九表が完成します。
九九表の各セルには、正しい答えが表示されていると思います。
参考SUM関数で、絶対参照と相対参照を組み合わせて、累計を求めることもできます。
-
SUM関数を使った累計の求め方(絶対参照と相対参照の組み合わせ)
SUM関数を使った累計を求める方法を解説します。 絶対参照と相対参照の学習にもなるので、読んでいただければと思います。 累計を求めるには、以 ...
絶対参照や複合参照は、条件付き書式の数式で利用することも多いです。
-
行または列単位の最大値に書式設定するには(表全体にまとめて設定)
Excelの条件付き書式で選択範囲内の最大値に塗りつぶしなどの書式設定をするのは、容易にできます。 列ごとや行ごとの最大値に書式設定したい場 ...
-
チェックボックスを使って条件付き書式のオンとオフを切り替える
フォームコントロールのチェックボックスを使って、チェックがオンであれば条件付き書式の書式を設定して、オフにすると解除するような仕組みを作るこ ...
-
重複しない一意の値を求める方法と複数列で重複をチェックする方法
Excelで2つの列にデータが入力されていて、そのデータの差分を求めたい場合がありますね。 A列には分析用データとして別表に貼り付け済みのI ...
数式の確認
コピーされた数式を確認してみます。
[数式]タブの[ワークシート分析]グループにある[数式の表示]をクリックします。
ショートカットキーは、[Ctrl]+[Shift]+[`](アクサングラーブ)です。数式の表示と非表示の切り替えができます。[`]は、[@]のキーと同じです。
なので、[Ctrl]+[Shift]+[@]と覚えたほうがいいかもしれません。
数式が入力されたセルの数式が表示されます。
各セルに入力されたセルを確認してみてください。
セル[K11]には、[=$B11*K$2]となっています。
数式の表示を数式の結果に戻すには、再度、[数式の表示]をクリックします。
参考数式の表示については、以下の記事で解説しています。
-
計算結果ではなくセルに数式を表示するには(印刷も可能)
数式をワークシートのセルに表示したいということはありますね。 数式のエラーを見つけたい、どこに数式が入っているのか分からない、などという時に ...
また、数式ではなく数式の結果のみを値として貼り付けることもできます。
-
数式の結果のみ貼り付けるには[値]の貼り付け
Excelで、以下のような表があるとします。D列には、B列とC列の合計値を求める関数が入力されています。 このとき、数式で求めた結果の値をそ ...
配列数式で九九表を作成
実は、もっと簡単に作成する方法があります。
まず、数式を入力する範囲を選択します。ここでは、セル[C3]からセル[K11]までを範囲選択します。
セル[C3]の数式バーに[=]を入力して、セル[B3] からセル[B11]までをドラッグします。
数式バーに直接入力してもかまいません。
数式バーは[=B3:B11]となります。
アスタリスクを入力した後、セル[C2]からセル[K2]までをドラッグします。
この後、キーボードから[Ctrl]+[Shift]+[Enter]を押します。
一気に九九表が完成します。
セル[C3]の数式は、中括弧{ }で囲まれた形になります。
{=B3:B11*C2:K2}
参考数式を削除する場合は、すべての範囲を選択して[Delete]キーを押します。1つのセルを削除することはできません。
1つのセルを削除しようとすると、[配列の一部を変更することはできません。]というメッセージが表示されます。
Excel2021と365ではスピル機能で簡単作成
Excel2021とMicrosoft365のExcelでは、セル[C3]に以下の数式を入力して[Enter]キーを押すだけでOKです。
範囲は、ドラッグで指定してかまいません。
=B3:B11*C2:K2
スピル機能によって、範囲を指定するだけで自動的に配列数式が入力されます。
セル[C3]の数式を削除すると、すべての数式が消えます。
スピル機能を利用した数式を配列定数を使って入力
スピル機能を利用して数式バーに以下のように入力して求めることもできます。
以下の数式を1から9まで入力すると、掛け算の九九表を作ることができます。
[;](セミコロン)で区切った配列が縦、[,](カンマ)で区切った配列が横になります。
={1;2;3;4;5}*{1,2,3,4}
配列数式とスピルについては、Microsoftの記事を参照してください。
配列数式のガイドラインと例 - Microsoft サポート(Microsoft)
動的配列数式とスピル配列の動作 - Microsoft サポート(Microsoft)
参考配列数式は、以下の記事でも使っています。
-
行列を入れ替えて貼り付けるには(関数TRANSPOSEなら連動もできる)
表の行と列を入れ替えたいと思ったことはありませんか? Excelでは、簡単に入れ替えることができます。 下のような表があります。1月から12 ...
スピルは便利な機能なので、使える環境の人はぜひ使ってみてください。
-
条件に一致するデータを抽出できるFILTER(フィルター)関数
FILTER関数を使用して条件に合うデータを抽出することができます。 数式は先頭のセルに入力すると、スピル機能によって自動で数式が入力されま ...
-
データ検索ならXLOOKUP関数で縦横に検索(IFERROR関数は不要)
Excel2021とMicrosoft 365 のExcelでは、[XLOOKUP(エックスルックアップ)関数]が使用できます。 これは、V ...
-
SORT関数とSORTBY関数を使い分けて別表に並べ替えた結果を表示する
SORT(ソート)関数とSORTBY(ソートバイ)関数を使用すると、表内のキーを基準に別表に並べ替えた結果を表示できます。 データが変更され ...
-
UNIQUE関数で重複しない値をリスト化して表内の該当セルを塗りつぶす
Excel2021とMicrosoft 365のExcelでは、UNIQUE(ユニーク)関数を使用することができます。 UNIQUE関数を使 ...