動的配列・スピル入門
TOROW関数を見て理解する
配列や範囲の値を1行に並べ替えて返す関数です。
3つのポイント
- 元になる配列を指定する
- 空白やエラーを無視するか指定する
- 値を読み取る方向を決めて1行へスピルする
空白を除き、列ごとの順番で1行に並べる
サンプル数式
- 配列
- 無視する値
- 列で走査
いまの結果：
先頭行：「りんご」 ｜ 「バナナ」 ｜ 「みかん」 ｜ 120 ｜ 95
入力・条件：3項目
- 配列A2:C4（3行 × 3列）先頭行：「りんご」 ｜ 120 ｜ 8
- 無視する値E2：1
- 列で走査F2：TRUE
配列を1行に並べ直した結果です。
サンプルデータ
※ Excel と Google スプレッドシートで結果や挙動が異なる場合、Excel の仕様を基準にしています。
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 商品 | 売上 | 在庫 | 無視 | 列で走査 | 結果 | ||||||||||
| 2 | りんご | 120 | 8 | りんご | バナナ | みかん | 120 | 95 | 110 | 8 | 6 | |||||
| 3 | バナナ | 95 | ||||||||||||||
| 4 | みかん | 110 | 6 |
E2、F2に値を入力すると、結果と処理順が変わります。
3つの引数
色付きの丸数字は、サンプル数式と引数ガイドで同じ引数を示しています。表にも同じ番号があれば、その引数で指定しているセルを示します。
A2:C4表のセル: A2:C41行へ並べ替える元の配列です。この例ではA2:C4の3行×3列を使います。
E2表のセル: E20〜3で無視する値の種類を指定します。この例では1なので空白を除外します。
F2表のセル: F2TRUEなら列ごと、FALSEなら行ごとに読み取ります。この例ではTRUEを指定します。
この数式を日本語にすると
A2:C4を列ごとに読み取り、E2の1で空白を除外します。8個の値をH2:O2へ1行でスピルします。
この説明は、最初に表示されているサンプルデータをもとにしています。入力値を変更しても説明文は変わりません。
処理の流れ
手順1〜4を順番に確認しましょう。表内の小さな角丸数字は、対応する手順を示します。
元の配列を確認する
A2B2C2A3B3C3A4B4C4A2:C4の3行×3列を並べ替える元配列として使います。
無視する値を確認する
E21なので、空白セルを無視して並べます。
読み取る方向を確認する
F2TRUEなので、列方向に順番に読み取ります。
1行に並べ替える
H2:O2列方向に読み取り、空白セルを除き、1行×8列の結果をH2:O2へスピルします。
対応アプリ
- Excel
- Google スプレッドシート
関数自体が対応するアプリです。教材例の数式や再計算が、両アプリで同じように動くことを保証するものではありません。
この例の注意点
- ignoreを0にするとすべて保持し、1は空白、2はエラー、3は空白とエラーを無視します。
- scan_by_columnをFALSEにすると行ごと、TRUEにすると列ごとに値を読み取ります。
- 空白を保持する場合、この教材ではExcelの配列結果と同様に空白セルを0として表示します。
- この例ではignoreとscan_by_columnを書き換えると、並ぶ値とスピル範囲がその場で変わります。
一般構文
対応：Excel / Google スプレッドシート ・ 構文：引数名に差あり
- すべての値が除外されるケースでは、Excelは#CALC!を返しますが、Google スプレッドシートでは空白表示となるケースがあります。
- ignoreのdomain不正とscan_by_columnのtyped errorが競合するケースなどで、ExcelとGoogle スプレッドシートのエラー結果が異なる場合があります。
Excel
=TOROW(array, [ignore], [scan_by_column])[] は省略できる引数です。
省略時
ignore→ 0省略すると、空白やエラーを含むすべての値を保持します。scan_by_column→ FALSE省略すると、行ごとの順番で値を読み取ります。
Google スプレッドシート
=TOROW(array_or_range, [ignore], [scan_by_column])[] は省略できる引数です。
省略時
ignore→ 0省略すると、空白やエラーを除外しません。scan_by_column→ FALSE省略すると、行ごとに左から右へ読み取ります。
公式資料・確認範囲
公式資料
- Microsoft Excel 公式構文
- Google スプレッドシート 公式対応状況・構文・省略時の扱い
実機確認
- Excel for the web / Google スプレッドシート計算結果・入力の扱い・エラーを確認済み
未確認
配列展開
次に学ぶ関数
WRAPROWS
1行に並べた次は、指定した個数で折り返して表に戻します。
次に学ぶ