tkykmw code

学びたいことを学ぶ

プログラマ的な発想でExcelを使うときに必要な機能

Excelといいつつ使っているのはGoogle Spreadsheetだが、どちらも共通して使えるはず)

Excelは計算式をコピーしたとき、セル参照を自動で割り当てなおしてくれる。

しかしプログラマ的には、自動割り当てではまったく物足りない、セルの位置など踏まえて自分で参照関係を作りたい!としばしば不満を抱く。

そういう場合に使える機能をリストアップしてみた。

セルの絶対参照

$A$3$J$29 のように、カラム名$ で囲むと絶対参照となり、計算式をコピーしても常に同じセルを参照する。あるセルを定数定義のように扱いたいときに便利。

$A$2:$E$5 のようにセル範囲も参照可能。

現在位置を知る COLUMN, ROW関数

現在のセルの位置を数値インデックスで取得する。A1セルが (1, 1) である。

数値インデックスをセル参照文字列に変換する ADDRESS 関数

COLUMNROW をもとに計算した数値インデックスを、A5 のような通常のセル参照文字列に変換する。

例えば現在列の1行目を文字列で取得するには ADDRESS(1, COLUMN()) とする。

行はもともと数字なので、列の変換が不要なら、文字列連結を使っても結果は同じ。例えば現在行のA列なら "A" & ROW() でも良い。

通常は下記の INDIRECT 関数と組み合わせて使用する。

文字列からセル参照を行う INDIRECT 関数

文字列から動的なセル参照を行う。プログラマ的な発想においては要となる関数。

=INDIRECT("A1")=A1 と同じ。

例えば常に同じ列の一つ上のセルの値を参照するには、 =INDIRECT(ROW()-1, COLUMN()) とすればよい。

連想配列的に値を参照する VLOOKUP

この関数をちゃんと説明するのは面倒くさいが、要は次のようなイメージ。

data = {
  "key1": [100, 200, 300],
  "key2": [400, 500, 600],
  "key3": [700, 800, 900],
};
    
// 500 を取得する
found = vlookup("key2", data, 3, false);

まず左端列が検索用のキーになっているデータ表を用意する。上の疑似コードでは連想配列として表現してみた。

vlookup はこの表から値を取り出す。第1引数が検索するキーの値、第2引数がデータ表を表す範囲、第3引数が左から数えたインデックス(検索キーの列もカウントすることに注意)、第4引数は……ふつうはfalseでいい(完全一致で検索する指定)。