この記事の概要
Google Sheetsで以下のようなものを作りたくなったのですが、苦労したので記事にしました。
- いわゆるスキルマップ
- 期ごとにスナップショットをとり、前の期と比べてどの項目が上がったかが分かる
- 社員番号順に並べたとしても異動や退社で繰り上がりが発生するため、メンバーリストの順番はアテにできない
完成イメージ
| 現在の期（2023） | 前の期（2022） |
|---|---|
 |
 |
- 鈴木がリストから消え、渡辺が追加された
- これによりリストの並びが同一でなくなった
- スキルが上がったものは背景色がついた
実施したこと
大雑把に言えば、完成イメージの現在の期（2023）のシートに条件付き書式を設定しただけです。
範囲はB2:F6（スコアの記載されている領域）で、書式ルールは次より大きい、式は以下です。
（本当はインデントなんかありませんが、1行だとあまりにも見づらいのでこのように記しました）
=INDEX(
INDIRECT("'2022'!A:F"),
MATCH(
INDIRECT(ADDRESS(ROW(), 1)),
INDIRECT("'2022'!A:A"),
0
),
COLUMN()
)
具体的な仕組みを記載します。
何度も出てくるINDIRECT
条件付き書式の式で、通常の書き方（例：'2022'!A:F）では別シートを参照できません。
ところがINDIRECTを通すと取得できるため、多用しています。
最初のINDEX
INDEXには参照するセル範囲, 行の番号, 列の番号を渡します。
今回の場合は以下を渡しています。
- 参照するセル範囲
-
2022のシート全体
-
- 行の番号
-
2022のシートの中で、2023のシートと氏名が一致する行
-
- 列の番号
- 自分自身の列番号
MATCHによる氏名の検索
MATCHには検索キー, 検索対象の1次元配列, 検索の種類を渡します。
- 検索キー
- 氏名を表している
- 例えば
2023のシートのB5セルでは、自分自身である5行目と、1列目であるA列目をあわせてA5セルを読み、高橋がキーとなる
- 検索対象の1次元配列
-
2022のシートのA列、つまり氏名の書いてある列
-
- 検索の種類
- 完全一致
- デフォルトだと
検索キー以下の最も大きい値を返すので、明示する必要がある
ROW(), COLUMN()
MATCHの中でも使っているし、INDEXの最後の引数にも渡しています。
自分自身の行や列の値を取得できます。
ちなみに
条件の中にメンバーリストの順番はアテにできないを入れていましたが、もし順番が保証されていれば以下の式で実現できました。
=INDEX(INDIRECT("'2022'!A:F"), ROW(), COLUMN())
最後に
「前の期のシートの内容をコピーして、今の期のシートに貼り付けて、値の大小だけ比べる」ようなやり方をとればもっと簡単でした。
しかし、同じ内容が2ヶ所に書かれていると大抵事故るので、このような作りにしてみました。
もっとスマートにできる気もしますが、ひとまずこれで良しとします。
最後まで読んでくださってありがとうございます！
Twitterでも情報を発信しているので、良かったらフォローお願いします！
Devトークでのお話してくださる方も募集中です！