1. 引数に「単一のセル」を与える組み込み関数の繰り返しを ARRAYFORMULA で置きかえる
値を 2 乗する POWER 関数の繰り返し
前回、演算子を利用した「数式の繰り返し」を ARRAYFORMULA で置き換えた。今回は「組み込み関数の繰り返し」を置きかえてみる。
例えば、A 列に入力した値を B 列で 2 乗する計算について考える。
べき乗を計算する「組み込み関数」は、POWER 。
A1 セルの値を2乗するには、B1 セルに
=power(A1, 2)
と入力する。 A 列の値に対して、繰り返し計算を行うには、B1 セルの数式を下方向へコピーする。
ARRAYFORMULA で置きかえる
上記の「関数の繰り返し」と同じ結果になるように、ARRAYFORMULA を利用して数式を置きかえたい。
そのためには、B1 セルに次のように記述する。
=ARRAYFORMULA( POWER(A:A, 2))
演算子のときと同じように、POWER 関数を呼び出すときに与えた「値」を「範囲」に置き換えた。それを ARRAYFORMULA に与えると、A 列にある各々の値に対して POWER 関数を適用したことになる。
2. VLOOKUP 関数の繰り返しを ARRAYFORMULA で置きかえる
VLOOKUP 関数の繰り返し
次に、データを参照するための関数 VLOOKUP の繰り返しを ARRAYFORMULA で置きかえる。
ここでは「日付から曜日を返す」例を用いる。書き方は以下を参照。
A 列に「日付」を入力すると、B 列に「曜日」が返る。1行目には「見出し」行がある。
「日付」を入力した列の隣、B2 セルには以下のように入力する。
=vlookup( weekday(A2), 曜日, match("曜日名",曜日_属性, 0), false)
繰り返し計算を行うには、B2 セルの数式を下方向へコピー。
ARRAYFORMULA で置きかえる
さて、「関数の繰り返し」と同じ結果になるように、 ARRAYFORMULA を利用して数式を置きかえたい。
数式の書き方は、以下を参考にした。
B2 セルに、次のように記述する。
=arrayformula( vlookup( weekday( offset(A2,0,0,rows(A:A)-1,1)), 曜日, match("曜日名",曜日_属性, 0) * sign(row( offset(A2,0,0,rows(A:A)-1,1))), false))
VLOOKUP の第 1 引数
ARRAYFORMULA で置きかえた式は、直感的に分かりにくいので、1引数ずつ確認する。
最初に vlookup 関数の第1引数。元の呼び出しは、
weekday(A2),
ARRAYFORMULA に与えるには、「値」を「範囲」に変更する。
weekday(A:A),
ただし、今回の例では「見出し」行があるので、1行目を計算対象から除かなければならない。そのためには、範囲を返す OFFSET 関数を利用する。
よって、以下のように記述する。
weekday(offset(A2, 0, 0, rows(A:A) - 1, 1)),
offset 内の ROWS 関数は、指定された範囲の行数を返す。
rows(A:A) - 1
と書くことにより、「見出し」を除いた高さを求めることができる。
VLOOKUP の第 2 引数
第2引数の「曜日」は、VLOOKUP で値を探す範囲。
これは変わらないので、そのまま与える。
VLOOKUP の第 3 引数
第3引数の元の呼び出しは、範囲「曜日_属性」から “曜日名” のあるインデックスを得た。
match("曜日名", 曜日_属性, 0),
ここでは具体的に 2 が返ってくる。
やっかいなのはこの引数。 ARRAYFORMUAL に与えるには、以下のように複雑な式となる。
match("曜日名",曜日_属性, 0) * sign(row(offset(A2, 0, 0, rows(A:A) - 1, 1))),
-
offset 関数が返すのは、上記の weekday 関数に与えた式と同じく、日付が記入された列の「見出し」を除いた範囲。
-
ROW 関数は、セルの行番号を返す。
全体の意味が分かりづらいので、 順を追って計算を確認する。
まず、ROW 関数を ARRAYFORMULA に与えたときの結果を表示してみる。
=arrayformula(row(A:A))
行番号に対応した値が表示された。
次に、SIGN 関数を計算に含める。
=arrayformula(sign(row(A:A)))
この関数は、「正の数なら 1 , 負の数なら –1 , 0 なら 0 」を返す。よって、行番号を与えると、行番号は正の数なので、全て 1 が返り、全体では 1 の配列が得られる。
ここで、ARRAYFORMULA の中で「任意の値を掛ける」計算を含めてみる。例えば、2 を掛ける計算を含めると、2 の配列が返される。
=arrayformula(2 * sign(row(A:A)))
つまり、第 3 引数に与えた計算は、match 関数で得られる値の配列を求めたことになる。
match("曜日名",曜日_属性, 0) * sign(row(offset(A2, 0, 0, rows(A:A) - 1, 1))),
繰り返すが、このように配列が必要なのは、ARRAYFORMULA 関数に与える関数の引数は、「値」ではなく「範囲」であるため。
VLOOKUP の第 4 引数
上記の第3引数で行なった計算を考えると、第4引数も同じような計算が必要に思えてくる。
しかし、この引数は元のままで変更する必要はない。なぜだろう?
3. 「新しい Google スプレッドシート」(2013/12 以降)
ところで、上記の「日付から曜日を返す」例を、「新しい Google スプレッドシート」(2013/12 以降)で試したところ、第3引数で必要だった
* sign(row(offset(A2, 0, 0, rows(A:A) - 1, 1))),
を付けなくても同じ結果が得られた。
=arrayformula( vlookup( weekday( offset(A2,0,0,rows(A:A)-1,1)), 曜日, match("曜日名",曜日_属性, 0), false))
うーん、なんでだろう。。(+_+)
「Google スプレッドシートで SUM 関数の繰り返しを「行列の積」で置きかえる」につづく…
0コメント:
コメントを投稿