たとえばこんな表、ありません？
横に2020年度・2021年度・2022年度……ってずらっと広がってて、端っこの方に「〇〇年度の予算消化率（実績÷予算）」を出したい！みたいなやつ。
〇〇年度はドロップダウンで選べるようにしてあって、選んだ年度に合わせて数式も自動で変わってほしいわけ。
「選んだ年度が表の何列目にあるか」はMATCH関数で出せる。たとえば上の図で2025年度なら =MATCH($Q$5,A5:P5,0) で「8」って返ってくる。そこまではいい。
……でも、8って数字でH列を参照するにはどうすりゃいい？ってなりません？わたしはなった。
とりあえず解決したので備忘録がてらに残しておきます。
＊もくじ＊
結論：INDEX関数を使うと列を「番号」で指定できる
INDEX関数は「縦横の交差点にある値を取り出す関数」。これを使って、列を番号で指定させます。
つまり「A列からP列の範囲で、〇行目・8列目の値をください」ってお願いができる。MATCH関数で出した番号をそのまま渡せるのがポイントです。
INDEX関数のおさらい
基本の形はこうです。
=INDEX(範囲, 行番号, 列番号)- 範囲：参照したいセルの範囲
- 行番号：範囲の中で何行目か
- 列番号：範囲の中で何列目か ← ここにMATCHの結果を渡す！
「範囲の中で」というのがポイントで、シート全体の列番号じゃなくて指定した範囲の中での何列目かです。シートの列番号と一致したほうが分かりやすいのでA列から始まる範囲にするのを推奨。
実際の数式はこう
$R$5にMATCH関数を仕込んでおく
まず前段階として$R$5に列番号を出しておきます。数式はこう。
=MATCH($Q$5,A5:P5,0)- $Q$5：検索したい値が入ってるセル（例：2025年度）
- A5:P5：検索する範囲
- 0：完全一致で検索
この表だとQ5が「2025年度」なので $R$5 に「8」が入ります。
INDEX関数で数字で列を表現する
消化率をだす数式はH列/Q列ですが、今回はHの部分を可変にするためにこんな式になります。
=IFERROR(INDEX(A:P,ROW(),$R$5)/Q6,"")ひとつずつ見ていきます。
INDEX(A:P, ROW(), $R$5) の部分
- A:P：実績データが入っている範囲（A列〜P列全体）
- ROW()：この数式が入っているセルの行番号を自動で取得。行をコピーしてもズレないので便利
- $R$5：MATCHで出した列番号が入っているセル。ドロップダウンで年度を変えるとここの数字も変わるので、INDEX参照先も自動で切り替わる
つまり「A〜P列の中で、この行・$R$5列目にある値」を取り出してくる。
÷Q6 の部分
取り出した実績値を予算（Q6）で割って、消化率を出しています。※Q列には予算が入っているという前提です。
IFERROR(〜,””) の部分
予算が0だったり空白だったりするとエラーになるので、その対策。エラーのときは空白を表示。
さらにネストしてもOK
ちなみに、$R$5を経由せずにINDEXの中にMATCHをそのまま入れ子にもできます。
=IFERROR(INDEX(A:P,ROW(),MATCH($Q$5,A5:P5,0))/Q6,"")こっちの方が表的にはスマートなんだと思いますが、私の場合、後でシートを見返したときに「どういう数式だっけ？？？」ってなるのが目に見えているのであえて段階を踏む形にしています。
まとめ
「列番号の数字でセルを参照したい」ときはINDEX関数を使う、でした。
- MATCH関数 → 年度が何列目かを数字で出す
- INDEX関数 → その数字を使って列を指定してセルの値を取り出す
ちなみにINDIRECT関数でも似たようなことはできるみたいなんですが、INDIRECTだとMATCHで出した列番号（数字）をさらにADDRESS関数でアルファベットに置き換えて、と数式が長くなってしまうので止めました。
同じような表で困ってた方の参考になれば！
