【解決】GoogleスプレッドシートのArrayformula関数を使いながら、フィルターを活用できるようにする方法

こんにちは、おかちゃんせんせいです！

先日Googleスプレッドシートでデータ表を作成しているときに、メンテナンス性の観点からArrayformula関数を使うことに決めました。
Arrayformula関数は以前ブログでご紹介した関数です。

しかし、いざArrayformula関数を使おうと思ったとき、Arrayformula関数の欠点にぶち当たりました。

それが、
フィルターを設定して並び替えをすると関数が崩れてしまう問題
です。

解決方法

Arrayformula関数は便利だがフィルターによる並び替えに弱い

データについては、A列・B列は手入力する箇所で、C列・D列はF〜H列にある別表からVLOOKUP関数でデータ参照する想定です。

C列・D列にただVLOOKUP関数を入れればいいだけですが、毎回行が増えるごとに数式を下の行に適用する作業は地味に面倒です。

そこでArrayformula関数の出番！
２行目に関数を入れておくだけで、適応したい行まで関数を自動反映させることができます。

しかし、フィルターを設定して、例えばA列を降順で並び替えると、下図のように数式が崩れてしまい意図したように並び替えができません。

なぜそうなってしまうかというと、並び替え前にC2セルに入れた関数がC5行目に移動してしまい、2〜4行目までが関数が反映されなくなってしまったからです。

つまり、上記のような事態を回避するようになんらか工夫してあげる必要があるのです。

ネット検索してみて結果・・・

問題解決するためにネット検索してみると、一番上にヒットした記事はこちら。
ただ、下記記事では「ARRAYFORMULA関数が入っているとフィルタはかけられない」という回答で終わっています。

参考

Arrayformula関数を使用したうえでフィルターをかけて順番を変更する方法はありますか？ – 添付画像の右の表をフィルターで在庫数降… – Yahoo!知恵袋

Yahoo!知恵袋

関連記事の中に同じような記事があったので、こちらでは「式をアレンジして1行目のタイトル行に式を入れれば大丈夫です」という回答に対して、質問者が「できましたー」とコメントしていますが……

パッと数式を見た限り、データ全体を見ていないのもありわかりにくい！
自分がやりたいことに置き換えにくい。。。

参考

Googleスプレッドシートでソートをするとセルの値が消えてしまいます列Xから列Zまでの3列の行ごとの合計値を計算する列… – Yahoo!知恵袋

Yahoo!知恵袋

と、いうことで、あれこれ手を動かしていたら解決方法を２つ見つけましたので、情報共有しますね。

解決方法１．１行目のタイトル行に数式を入れる

１つ目の解決方法は、先述した通りの方法で１行目のタイトル行に数式を入れる方法です。
ただこの時に数式を少し工夫する必要があります。

数式をただ入れるだけでなく、１行目は項目名になるようにif(s)関数で条件分岐させて、２行目以降から並び替えをしたいデータが表示されるように工夫します。

解決方法２．２行目に数式を入れて行を非表示にする

２つ目の方法は、ある意味Googleスプレッドシートの特性を活かした方法ですw

まずは、ヘッダー項目（１行目）の下に空白行を挿入し、空白行に関数を入れます。
ただ、この状態で並び替えをしてしまうと、２行目の空白部分が認識されてしまい、また関数が崩れてしまう可能性があります。

なので、関数が入れ終わったら、空白行（２行目）を非表示にします。
すると、空白行が認識されずに、並び替えができるようになります。

まとめ

解決方法１つ目は数式が少し複雑になることと、２つ目は空白行を作成して非表示にするという手間があります。
けれど、どちらかの方法を試すことで、Arrayformula関数を使いつつフィルターによる並び替えが可能になります。

今回の記事が同じ悩みを抱いた方にとって役立てば幸いです。


最後まで読んでいただき、ありがとうございました！

【特集】独学では開発言語の習得が難しいと感じている方向け

↓ 今日も１日ポチッと ↓

　　

↑ いつもボタンクリ応援・口コミ応援ありがとうございます ↑