GoogleスプレッドシートでFILTERした範囲を結合する

またGoogleスプレッドシートの話です。

このような2つのテーブルがあるとします。

これらのテーブルをもとに

• num が奇数の行だけを抜き出して縦方向に結合したテーブルA
• num が偶数の行だけを抜き出して縦方向に結合したテーブルB

を作りたいとしましょう。

結論ファースト

これでいけます。

テーブルA

=FILTER(
  {
    IFNA(FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=1),{"",""});
    IFNA(FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=1),{"",""})
  },
  {
    IFNA(FILTER($A$3:$A$5,MOD($A$3:$A$5,2)=1),{""});
    IFNA(FILTER($D$3:$D$5,MOD($D$3:$D$5,2)=1),{""})
  }<>""
)

テーブルB

=FILTER(
  {
    IFNA(FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=0),{"",""});
    IFNA(FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=0),{"",""})
  },
  {
    IFNA(FILTER($A$3:$A$5,MOD($A$3:$A$5,2)=0),{""});
    IFNA(FILTER($D$3:$D$5,MOD($D$3:$D$5,2)=0),{""})
  }<>""
)

解説

テーブルA

テーブルAを作る手順をやっていきましょう。

まずはテーブル1から奇数の行だけを抜き出してみます。FILTERを使えば簡単です。

=FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=1)

同じ要領でテーブル2からも奇数の行だけを抜き出してみましょう。

=FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=1)

この2つを縦方向に結合しましょう！

縦方向の結合は ={範囲;範囲} という式で書くことができます。

={
  FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=1);
  FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=1)
}

お〜、できました！（ほんとか？）

テーブルB

この勢いで、テーブルBも作っていきましょう。 テーブルBは偶数の行を抜き出したいので、↑の式を MOD(ほげ,2) = 0 で書き換えれば良いはずですね。

={
  FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=0);
  FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=0)
}

あら〜？エラーになってしまいました。

それもそのはず、FILTER関数は条件に一致する行が見つからないとき #N/A を返します。 ref: FILTER - ドキュメント エディタ ヘルプ

今回はテーブル1に偶数の行がないので、

={
  FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=0);  <- ここで #N/A エラーになる
  FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=0)
}

という状況です。

配列の #N/A エラーを回避する

#N/A エラーを回避する方法としてIFNA関数があります。

https://support.google.com/docs/answer/9365944?hl=ja IFNA 関数 - ドキュメント エディタ ヘルプ

IFNA関数の第一引数には「#N/A エラーになる可能性がある値」を、第二引数には「第一引数が #N/A だった場合に代わりに表示する値」を指定します。

つまり、今回の場合では

={
  IFNA(FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=0),ほげ);
  IFNA(FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=0),ふが)
}

という感じにしてあげれば、テーブル1またはテーブル2に偶数の行がなくても #N/A エラーは発生しなくなる、ということですね！

さて、次に問題となるのは、↑の ほげ や ふが になにを入れればいいの？ということです。

いまIFNA関数の第一引数に渡している FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=0) と FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=0) は、どちらもN行2列の配列です（それぞれ条件を満たす行があれば）。

このN行2列の配列を縦方向に結合しようとしているわけです。

複数の配列を縦方向に結合するとき、列の数が一致している必要があります。

つまり、 #N/A エラーになった時も常に同じ列数の配列を返すようにしてあげれば良い、ということですね。

今回は1行2列の空文字列の配列 {"",""} を返すことにしましょう。

={
  IFNA(FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=0),{"",""});
  IFNA(FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=0),{"",""})
}

お〜、ここまできました。

無駄な空行を無くす

↑の画像は、テーブル1に偶数の行がないために1行2列の空文字列の配列 {"",""} が表示されていて、不要な空行が残ってしまっています。

最後にまたFILTER関数を使って

=FILTER(
  該当の範囲, 
  該当の範囲の1列目<>""
)

という式を作れば完了です。

これを書き換えると、こうなります。

=FILTER(
  {
    IFNA(FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=0),{"",""});
    IFNA(FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=0),{"",""})
  },
  {
    IFNA(FILTER($A$3:$A$5,MOD($A$3:$A$5,2)=0),{""});
    IFNA(FILTER($D$3:$D$5,MOD($D$3:$D$5,2)=0),{""})
  }<>""
)

はい、これが「結論ファースト」で示したテーブルBの式ですね！

改めてテーブルA

さて、テーブルAはテーブルBの奇数版なのでMODのところだけ書き換えて

=FILTER(
  {
    IFNA(FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=1),{"",""});
    IFNA(FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=1),{"",""})
  },
  {
    IFNA(FILTER($A$3:$A$5,MOD($A$3:$A$5,2)=1),{""});
    IFNA(FILTER($D$3:$D$5,MOD($D$3:$D$5,2)=1),{""})
  }<>""
)

ですね！

おわりに

GoogleスプレッドシートでFILTERした範囲を結合する方法をまとめました。

「配列の #N/A エラーを回避する」というのがミソですね。

ではまた！