以前、海外赴任していた頃の話です。部下たちのExcel作業が遅いように感じたので朝ミーティングでプチ研修を行っていました。
Excelで「九九の一覧表を作ってみて」とお題を出したことがあります。1×1から9×9までの81マスの表です。しばらく待っても終わらなかったため、それぞれの画面を覗いてみると、頭で計算した答えを1マスずつ手打ちしていました。
九九なら暗算でもできるので、数式を使う発想自体が浮かばなかったようです。自分の頭で計算して、81回、その数字をキーボードで打ち込む。間違ってはいませんが、入力ミスの恐れがあるし、時間がかかるし、何より疲れます。
その後、実演しました。1マスに数式を作り、あとはコピーで残り80マスを一瞬で埋める。かかった時間は30秒ほど。「え、もうできたの？」という驚きの声が上がり、ちょっとした盛り上がりになりました。
「計算は自分の頭で、入力は手で」という発想のままだと、マス目が増えるほど作業時間は増えていきます。数式とコピー、そして参照の仕組みを知っているだけで、その手間はほぼゼロになり、かつ入力ミスの可能性も激減します。
今回はこの「参照」を用いて、数値を色分けした「ヒートマップ」を作る方法を紹介します。
完成イメージ
暑い夏に関連した指標として、今回は不快指数を取り上げます。
気温と湿度から不快指数を計算し、色分けした表がこちらです。
見た目は複雑に見えますが、実際に数式を入力するのは表の左上1セル（C6）だけ。あとはコピー（フィル）だけで残りの全セルを埋められます。
その後「条件付き書式」を設定し、色分けを行います。
基礎：相対参照/絶対参照の考え方
Excelには相対参照、絶対参照、複合参照といった３つの参照があります。順に見ていきましょう。
相対参照
Excelであるセルをコピーし、別のセルへそのままペーストすると、数式の中の参照が自動的にズレていきます。これが相対参照です。
例えばA1に1、B1に2が入力された状態で、A2に「=A1」と入力すると、A1が参照されて「1」と表示されます。このA2セルをコピーしてB2へペーストすると、B2には「＝B1」が入力され「2」が表示されます。「1つ上のセルを見る」という位置関係がコピーされているイメージです。
これに対して、位置をズラしたくない場合に使うのが絶対参照や複合参照です。
$を行や列の前につけることで、その部分だけ固定されます。
絶対参照
コピー＆ペーストしても参照元が変わらず、常に同じセルを参照します。これが絶対参照です。
A2に=$A$1と入力すると、A1が参照されます。このセルをA4、D2、D4のどこへコピーしても、参照先は常にA1のままです。行・列ともに$で固定されているため、コピー先がどこであっても動きません。
複合参照
$を列の前につけるか行の前につけるかで動き方が変わります。これが複合参照と呼びます。
A2に=A$1（行だけ固定）と入力してみます。
- A4へコピーすると、列は変わらずA1のまま参照が動きません。
- D2やD4へコピーすると、行は1のまま固定されつつ列だけがズレるため、D1が参照されます。
「1」の前に$をつけたことで、行だけが固定されている状態です。
逆に列だけを固定したい場合は$A1のように書きます。この場合は縦にコピーしても常にA列を参照し続け、横にコピーすると列がズレます。A$1とはちょうど逆の動きで、列だけが固定されている状態です。
参照のまとめ
まとめると以下のようになります。
- A1 → 相対参照。コピーした方向・距離に応じて行・列の参照先が移動する。
- $A$1→ 絶対参照。行列ともに固定され、コピーしても常にA1を参照する。
- A$1 → 複合参照、行だけ固定（1は固定、列はズレる）
- $A1 → 複合参照、列だけ固定（Aは固定、行はズレる）
ちなみにこの参照は、セル参照を選択した状態（セル上でダブルクリックもしくはF2を押した状態）でキーボードの「F4」キーを押すと、順番に切り替えられます。
ヒートマップのようなクロス集計表を作るときは、この複合参照が威力を発揮します。
基礎：数式を全セルにコピー（フィル）する
数式を1マス作ったら、あとはコピー（フィル）で広げるだけです。
- 数式を入力したセルを選択
- セル右下の■（フィルハンドル）にカーソルを合わせる
- ドラッグして必要な範囲まで伸ばす
行方向・列方向どちらにも伸ばせるので、まず横に1行分フィルし、その1行をまとめて下方向にフィルすれば、表全体を一瞬で埋められます。
埋めたい範囲を選択したうえで、ショートカットの Ctrl+D（下方向にコピー）、Ctrl+R（右方向にコピー）を使うと、２動作ですべてのセルを埋められるため、さらなる効率化が期待できます。
実践：気温×湿度から不快指数表を作る
不快指数を例として実際に手を動かしてみましょう。
① 見出し行・見出し列を作る
1行目に気温（20〜45）、A列に湿度（30〜100）を入力します。ここは単純な連番なので、フィルで一気に作れます。
② 交点のセルに数式を入れる
交点となる左上のセル（B2）に、不快指数の計算式を入力します。
=0.81*B$1+0.01*$A2*(0.99*B$1-14.3)+46.3
ここでの参照の使い分けがポイントです。
- B$1（気温）→ 行を固定。横にコピーしても常に1行目（気温の見出し行）を参照し続け、縦にコピーすると列はそのまま・行だけ動くので、常にその列の気温を参照します。
- $A2（湿度）→ 列を固定。縦にコピーしても常にA列（湿度の見出し列）を参照し続けます。
この2つを組み合わせているからこそ、B2の数式をそのまま表全体にフィルするだけで、すべてのマスが「その行の気温×その列の湿度」を正しく参照してくれます。もし普通の相対参照のままフィルすると、コピーするたびに見出し行・見出し列の参照までズレてしまい、正しい表になりません。
③ 表全体にフィル
B2の数式ができたら、数式を入力する範囲を選択し、下方向にフィル（Ctrl+D）して1列分を埋め、その列を選択したまま右方向にフィル（Ctrl+R）すれば、表全体が一瞬で完成します。
実践：条件付き書式で色をつける
数値ができたら、いよいよ色付けです。
「ホーム」タブ→「条件付き書式」→「カラースケール」から設定できます。
カラースケールの限界
カラースケールは2色・3色のグラデーションで自動的に色をつけてくれる便利な機能ですが、あくまで最小値〜最大値の間を機械的に按分しているだけです。「70未満は快適、70〜74はやや不快」のように、区切りごとに色を変えたい場合には向いていません。
区切りごとに色を変えたい場合
閾値で色を明確に分けたいときは、カラースケールではなく「セルの強調表示ルール」→「指定の値より大きい／小さい」などを使い、条件を1つずつ手動で設定していきます。
例えば今回の不快指数なら、
- 70未満 → 青
- 70〜74 → 緑
- 75〜79 → 黄
- 80〜84 → 橙
- 85以上 → 赤
という5つのルールを個別に作ることになります。カラースケールより手間はかかりますが、「快適・やや不快・不快」といった意味のまとまりがハッキリ見えるようになり、資料として人に見せる場合はこちらの方が伝わりやすいです。
Excelで作ったヒートマップを変更するには
ここまでの方法で、任意の2軸から表を作り、色をつけるところまではできます。ただし実務では、こんな要望を受けることもよくあります。
「気温の刻み、1℃じゃなくて0.5℃にできる？」
「範囲も0〜50℃まで広げてほしい」
Excelでこれをやろうとすると、見出し行・見出し列を作り直し、数式のフィル範囲も全部作り直しになります。行数・列数が変わるたびに、表そのものを組み直す必要が出てくるのが、相対参照ベースの表の弱点です。
この「範囲や刻みを変えるたびに作り直す」手間は、Power Queryという機能を使えば大幅に削減できます。刻み幅や範囲を数値として設定しておけば、それを書き換えるだけで表が自動的に作り直される仕組みです。
Power Query / Power BIを使った方法は、以下の記事で詳しく解説しています。
まとめ
Excelでヒートマップを作る方法を紹介しました。
ポイントは3つです。
参照は知っているかどうかで作業時間が何十倍も変わる、強力な機能です。今回のヒートマップに限らず、日々の業務でも積極的に使ってみると業務効率が大幅に改善します。
コメント