Googleスプレッドシートで配列数式を使用する方法

2023 年の初めに、Google はスプレッドシートにいくつかの新しい関数を導入しました。その中には配列を操作するための 8 つの関数も含まれます。これらの関数を使用すると、配列を行または列に変換したり、行または列から新しい配列を作成したり、現在の配列を追加したりできます。

配列の操作がより柔軟になり、基本的な ARRAYFORMULA 関数を超えて、 Google スプレッドシートの数式でこれらの配列関数を使用する方法を見てみましょう。

目次

ヒント: Microsoft Excel も使用している場合、これらの関数の一部は見覚えがあるかもしれません。

配列の変換: TOROW と TOCOL

データセット内に単一の行または列に変換する配列がある場合は、TOROW 関数と TOCOL 関数を使用できます。

各関数の構文は同じで、TOROW(array,ignore,scan)とTOCOL(array,ignore,scan)で、どちらも最初の引数のみが必要です。

• 配列: 「A1:D4」の形式で変換する配列。
• Ignore : デフォルトでは無視されるパラメーターはありません (0) が、空白を無視するには 1、エラーを無視するには 2、または空白とエラーを無視するには 3 を使用できます。
• Scan : この引数は、配列内の値を読み取る方法を決定します。デフォルトでは、この関数は行ごとにスキャンするか、値 False を使用しますが、必要に応じて True を使用して列ごとにスキャンすることもできます。

TOROW 関数と TOCOL 関数とその公式を使用した例をいくつか見てみましょう。

この最初の例では、配列 A1 から C3 を取得し、次の式のデフォルト引数を使用して行に変換します。

=トロウ(A1:C3)

ご覧のとおり、配列は一列に並んでいます。デフォルトのscan引数を使用したため、関数は左から右 (A、D、G)、下に読み取り、完了するまで再び左から右 (B、E、H) に読み取り、行ごとにスキャンします。

配列を行ではなく列で読み取るには、scan引数にTrueを使用します。無視引数は空白のままにします。式は次のとおりです。

=TOROW(A1:C3,,TRUE)

これで、関数が配列を上から下 (A、B、C)、上から下 (D、E、F)、および上から下 (G、H、I) に読み取ることがわかります。

TOCOL 関数も同様に機能しますが、配列を列に変換します。同じ範囲 A1 から C3 を使用し、デフォルトの引数を使用した数式は次のとおりです。

=TOCOL(A1:C3)

ここでも、 scan引数のデフォルトを使用して、関数は左から右に読み取り、結果をそのまま提供します。

配列を行ではなく列で読み取るには、次のようにscan引数にTrueを挿入します。

=TOCOL(A1:C3,,TRUE)

これで、関数が配列を上から下に読み取ることがわかります。

行または列から新しい配列を作成: CHOSEROWS および CHOSECOLS

既存の配列から新しい配列を作成することもできます。これにより、別のセル範囲の特定の値のみを含む新しいセル範囲を作成できます。このためには、CHOOSEROWS および CHOSECOLS Google Sheets 関数を使用します。

各関数の構文は、CHOOSEROWS (配列、row_num、row_num_opt)とCHOOSECOLS (配列、col_num、col_num_opt) に似ており、どちらも最初の 2 つの引数が必要です。

• 配列: 「A1:D4」としてフォーマットされた既存の配列。
• Row_numまたはCol_num : 返す最初の行または列の番号。
• Row_num_optまたはCol_num_opt : 返す追加の行または列の番号。Google では、行を下から上に返すか、列を右から左に返すには負の数値を使用することを推奨しています。

CHOOSEROWS および CHOSECOLS とその式を使用した例をいくつか見てみましょう。

この最初の例では、配列 A1 から B6 を使用します。行 1、2、および 6 の値を返したいと考えています。式は次のとおりです。

=CHOOSEROWS(A1:B6,1,2,6)

ご覧のとおり、新しい配列を作成するためにこれら 3 行を受け取りました。

別の例として、同じ配列を使用します。今回は行 1、2、および 6 を返しますが、2 と 6 の順序は逆になります。正または負の数値を使用しても、同じ結果が得られます。

負の数値を使用する場合は、次の式を使用します。

=CHOOSEROWS(A1:B6,1,-1,-5)

説明すると、1 は最初に返される行、-1 は 2 番目に返される行、つまり下から 1 行目、-5 は下から 5 行目です。

正の数を使用すると、次の式を使用して同じ結果が得られます。

=CHOOSEROWS(A1:B6,1,6,2)

CHOSECOLS 関数も同様に機能しますが、行ではなく列から新しい配列を作成する場合に使用する点が異なります。

配列 A1 から D6 を使用すると、次の式で列 1 (列 A) と 4 (列 D) を返すことができます。

=CHOOSECOLS(A1:D6,1,4)

これで、これら 2 つの列のみを含む新しい配列が完成しました。

別の例として、列 4 から始まる同じ配列を使用します。次に、列 1 と列 2 を最初に 2 (列 B) で追加します。正または負の数値を使用できます。

=CHOOSECOLS(A1:D6,4,2,1)

=CHOOSECOLS(A1:D6,4,-3,-4)

上のスクリーンショットでわかるように、数式バーではなくセルに数式が表示されており、両方のオプションを使用しても同じ結果が得られます。

注: Google では、結果の配置を逆にするために負の数値を使用することを推奨しているため、正の数値を使用しても正しい結果が得られない場合は、この点に留意してください。

ラップして新しい配列を作成する: WRAPROWS および WRAPCOLS

既存の配列から新しい配列を作成し、それぞれに特定の数の値を含む列または行をラップする場合は、WRAPROWS 関数と WRAPCOLS 関数を使用できます。

各関数の構文は同じで、WRAPROWS (範囲、カウント、パッド)とWRAPCOLS (範囲、カウント、パッド) で、どちらも最初の 2 つの引数が必要です。

• Range : 配列に使用する既存のセル範囲。「A1:D4」の形式で指定します。
• Count : 各行または列のセルの数。
• Pad : この引数を使用して、空のセルにテキストまたは単一の値を配置できます。これにより、空白セルに対して表示される #N/A エラーが置き換えられます。テキストまたは値を引用符で囲みます。

WRAPROWS および WRAPCOLS 関数とその数式を使用した例をいくつか見てみましょう。

この最初の例では、セル範囲 A1 から E1 を使用します。各行に 3 つの値を含む行をラップする新しい配列を作成します。式は次のとおりです。

=WRAPROWS(A1:E1,3)

ご覧のとおり、正しい結果 (各行に 3 つの値) を含む新しい配列ができました。配列内に空のセルがあるため、#N/A エラーが表示されます。次の例では、 pad引数を使用してエラーをテキスト「None」に置き換えます。式は次のとおりです。

=WRAPROWS(A1:E1,3,”なし”)

これで、Google スプレッドシートのエラーの代わりに単語が表示されるようになりました。

WRAPCOLS 関数は、既存のセル範囲から新しい配列を作成することによって同じことを行いますが、行ではなく列をラップすることによってそれを行います。

ここでは、同じ配列 A1 から E3 を使用し、各列に 3 つの値を含む列をラップします。

=WRAPCOLS(A1:E1,3)

WRAPROWS の例と同様に、正しい結果が得られますが、セルが空であるためにエラーも発生します。この式では、pad引数を使用して「Empty」という単語を追加できます。

=WRAPCOLS(A1:E1,3,”空”)

この新しい配列は、エラーではなく単語を使用したほうが見栄えがよくなります。

結合して新しい配列を作成: HSTACK と VSTACK

最後に説明する 2 つの関数は、配列を追加する関数です。HSTACK と VSTACK を使用すると、2 つ以上のセル範囲を追加して、水平方向または垂直方向に 1 つの配列を形成できます。

各関数の構文は同じで、HSTACK (range1、range2、…)とVSTACK (range1、range2、…)で、最初の引数のみが必要です。ただし、ほとんどの場合、別の範囲を最初の引数と組み合わせる 2 番目の引数を使用します。

• Range1 : 配列に使用する最初のセル範囲。「A1:D4」の形式で指定します。
• Range2,… : 配列を作成するために最初のセル範囲に追加する 2 番目のセル範囲。3 つ以上のセル範囲を結合できます。

HSTACK と VSTACK を使用した例とその式をいくつか見てみましょう。

この最初の例では、次の式を使用して範囲 A1 ～ D2 と A3 ～ D4 を結合します。

=HSTACK(A1:D2,A3:D4)

データ範囲が結合されて単一の水平配列を形成していることがわかります。

VSTACK 関数の例として、3 つの範囲を組み合わせます。次の式を使用して、範囲 A2 ～ C4、A6 ～ C8、および A10 ～ C12 を使用します。

=VSTACK(A2:C4,A6:C8,A10:C12)

これで、単一セル内の数式を使用して、すべてのデータを含む 1 つの配列が作成されました。

配列を簡単に操作

SUM 関数や IF 関数など、特定の状況ではARRAYFORMULA を使用できますが、これらの Google スプレッドシートの追加の配列数式を使用すると時間を節約できます。これらは、単一の配列数式を使用して、シートを希望通りに正確に配置するのに役立ちます。

このような非配列関数を使用したチュートリアルの詳細については、Google スプレッドシートでCOUNTIF関数またはSUMIF 関数を使用する方法を参照してください。