はじめに
今年もアドベントカレンダーはじまりました！
この記事はNE Advent Calendar 2023のカレンダー1枚目1日目の記事です。
NE株式会社のエンジニアが自分の興味あるテーマについて自由に書いていきます。
毎年一緒に参加して盛り上げてくれる皆さんに感謝です 
概要
例えばこんな元データがあったとします。（プルリク数を集計するようなシート）
このデータを人別・月別に集計します。
関数としては以下のように組みました。
- 名前の欄（A2:A）にてE2（Aさん）のもの
- マージ月の欄（C2:C）にてF1（202309）のもの
=countifs(A2:A, E2, C2:C, F1)
正しく集計され「2」というカウントになりました。
良さそうなので他の人や月にも反映します。
数式をコピーして他のセルに貼り付けてみると。
おや、おかしいですね。
全部0になりました。
貼り付けた数式を見てみると
=countifs(A3:A, E3, C3:C, F2)
- 名前の欄はA2:AにしたいのにA3:Aになってしまっている。
- 検索する人はBさん（E3）なので合ってる。
- マージ月の欄C2:CにしたいのにC3:Cになってしまっている。
- 検索する月は202309（F1）にしたいのにF2になってしまっている。
セル１個下にコピペすると相対参照され、数値の部分が1プラスされます。
E2からE3は意図しているのでいいですが、他の部分は1プラスされたくないですよね。
こうなってしまうとコピペで展開できず1セル毎に微調整が必要になりイライラしますよね。
これを解消しましょう！
相対参照と絶対参照
相対参照
普通にA2とかを指定するとコピペした際に相対的に値が変動します。
下のセルだと2から3
右のセルだとAからB
のように相対的にAの横だからBやろ、みたいな感じでいい感じにやってくれます。
これが都合が良い場合はこれでいいですね。
絶対参照
相対だと都合が悪い場合に使うのが絶対参照です。
これは「$」マークを使って表現します。
例えばA2という参照があったとして以下のように記述できます。
| 記法 | 意味 |
|---|---|
$A$2 |
Aも2も絶対参照。コピペしても動かない。 |
$A2 |
Aが絶対参照。左右にズレてもAのまま。2に関しては上下に動く。 |
A$2 |
2が絶対参照。上下にズレても2のまま。Aに関しては左右に動く。 |
これをうまいこと利用することでコピペで意図した形にすることができます。
実践
先程の関数は以下のように変えてみました。
=countifs($A$2:$A, $E2, $C$2:$C, F$1)
これで他のセルにコピペしてみると…
無事にやりたい参照で反映できました！
少し解説すると
- A2:Aは人の列として固定の参照範囲なので全て固定
- C2:Cについても同様に全固定
- E2は検索する人の名前
- これについては左右はズレて欲しくないが上下はズレて欲しい
- そのため
$E2としてEのみ固定し、2,3,4と動くようにした
- F1は検索する月
- これについては左右はズレて欲しいが上下にはズレて欲しくない
- そのため
F$1として1のみ固定し、F,G,Hと動くようにした
まとめ
相対参照と絶対参照をうまく使い分けてスプレッドシートレベルを上げていきましょう！
おわりに
今年も完走目指して頑張りましょー！
この後もNEのアドカレをお楽しみに！！