こんにちは、おかちゃんせんせいです！
久しぶりの番外編として、Googleスプレッドシートに関するトラブルで解決に苦労したことについて、今回まとめることにしました。
今回のキーワードは
QUERY関数とARRAYFORMULA関数の組み合わせで起きるトラブル
です。
目次
Googleスプレッドシート独自の関数
以前のブログでもQUERY関数とARRAYFORMULA関数についてはご紹介しましたが、両方ともExcelにはなくGoogleスプレッドシート独自の関数になります。
【データ集計】ExcelとGoogleスプレッドシートを使い分けるポイントとは？〜やりたいことを整理してみる〜
この２つの関数は、ただデータ入力するだけの場合は全く出番はないですが、データ集計——特にシート間・別スプレッドシートをまたがって集計したい場合に重宝する関数で、知っておいて損はない関数です。
私は最近知ったのですが、QUERY関数は別範囲のデータを縦に結合できたり、横に結合できたりで何でもあり。
Excelだったらデータを結合するにはVLOOKUP・XLOOKUPやINDEX・MATCHを駆使して参照するしかありませんが、GoogleスプレッドシートではQUERY関数を一つのセルに入力するだけで欲しいデータの形で集計できます。
もちろん使うこなすために必要な知識はあります。
私の場合は下記サイトを参考にしてQUERY関数を覚えましたので、データ集計で関数が煩雑になっている方、もっと効率よくデータ集計をしたいと考えている方は是非この機会に習得してみてはいかがでしょうか。
では、本題に移ります。
そんな便利な二つの関数ですが、それぞれの仕様によってこちらの意図通りに関数の結果が反映されない場合があります。
「関数は間違っていないはずなのに、結果がバグってる！？」
という厄介な事態に遭遇している方は参考にしてみてください。
そこで今回は、私が直近で遭遇した２つの組み合わせによって起きる３つのトラブルについて備忘も兼ねて整理します。
- 参照元のデータが欠如されてしまうケース
- 関数を設定したセルに複数のデータが入ってしまうケース
- データが間違っていないはずなのにエラーが出てしまうケース
①参照元のデータが欠如されてしまうケース
トラブル
下記の関数をN1セルに入力すれば、通常であれば他の列と同様にJ列の情報が参照できる——はずなのですが、上図のようになぜかデータが表示されない場合があります。
=QUERY(ARRAYFORMULA(J1:J),”select *”)
「他の列は正しく表示されているのに、なぜこの列だけ！？」
というトラブルに遭遇。
原因
自分であれこれ触ってみてもよくわからなかったのでネットで調べたところ、原因はまさかのGoogleスプレッドシートの仕様でした。。。
1 つの列に異なる種類のデータが含まれている場合は、その列に大多数含まれる種類のデータをクエリに使用します。小数の種類のデータは NULL 値とみなされます。
Googleヘルプサイト
つまり、今回のケースでは日付データとaaaという文字列があり、日付データの方が多いので、aaaという文字列がNULL値と判別されたようです。
解決方法
ネット検索でヒットする解決方法としては、
TO_TEXT()で文字列化させたデータをQuery関数にわたす
という方法です。
ただ、上記の方法では関数の設定に制限ができてしまうので、注意しなければなりません。
そこで
「表示形式の問題であれば、表示形式を何とかすれば解決するんじゃないか？」
と思い、あれこれいじり続けて見つけた他の解決策は、
参照元の該当列の表示形式を「書式なしテキスト」にする
です。
この方法であれば関数を別途いじることもなく、制限を受けることもなくなり、しかも表示形式を「書式なしテキスト」に変えるだけで済むので、個人的にはこちらの方がオススメです！
②関数を設定したセルに複数のデータが入ってしまうケース
トラブル
続いてのトラブルは、なぜかズレて表示されてしまうように見えるケースです。
これも①と同様の関数を適用していますが、なぜこのような現象が起きるのでしょうか？
原因
このケースも本当に厄介ですね。。。
私が扱っているデータでも、日付が入る場合と入らない場合があって、このケースに遭遇しました。
このケースではこうなってしまう原因が違うので、表示書式を変更しても解決しません。
こちらについても自力解決が難しかったのでネット検索したところ、システム側で見出行以下も見出行と判断してしまったことが原因でした。
つまり、今回のケースではデータ（B2:B3）が欠落したわけではなく、下図のようにF1セルに欠落したかのように見えたデータが含まれてしまっているのです。
解決方法
このケースはある意味QUERY関数のバグのようなものなので、QUERY関数を修正することで解決します。
具体的には、QUERY関数の第３引数を1と明記します。
=QUERY(ARRAYFORMULA(J1:J),”select *”,1)
第３引数は省略可能な任意の引数で、見出行のあるなしを判別させる引数です。
なので、今回のケースでは1行目のデータを見出行と指定することで、他の行も見出行と認識されるのを防ぐことができます。
③データが間違っていないはずなのにエラーが出てしまうケース
トラブル
最後のトラブルは、QUERY関数内でARRAYFORMULA関数を結合させたときに起きたトラブルです。
今回のケースでは、下記３つの範囲データを縦に結合させています。
・同一スプレッドにあるシート1のA列からC列
・同一スプレッドにあるシート2のA列からC列
・別スプレッドにあるシート1のA列からC列
=QUERY({ARRAYFORMULA(‘シート1’!A:C);
ARRAYFORMULA(‘シート2’!A:C);
ARRAYFORMULA(IMPORTRANGE(K1,”シート1!A:C”))},
“select * where Col1 is not Null” )
原因
このケースについて、まずはエラーの内容について検索してみました。
ARRAY_LITERAL の配列リテラルで、１つ以上の行の値が見つかりませんでした。
調べた結果、ヒットした記事で上記エラーが表示されるケースとして考えられるのが、まとめると２つあることがわかりました。
けれど、私のケースでは結果が0件になることはないし、列数もA:Cと同じなのでどちらのケースにも該当しませんでした。
色々検索ワードを変えても該当するようなケースが見つからなかったので途方に暮れていたところ、
「もしかして、大前提となる設定ができていないことが原因ではないか？」
ということに気づき設定をしたところ、予想が的中してエラーを回避することができました。
原因は何だったのかと言いますと、
アクセス権限の未付与
でした。
解決方法
今回はIMPORTRANGE関数がキーポイントになります。
IMPORTRANGE関数は別スプレッドシートのデータを参照したいときに活用する関数です。
この関数を使うときに、通常であれば「アクセスを許可」を促すダイアログが表示されます。
しかし、IMPORTRANGE関数を初めて使うときに、QUERY関数の中で使うとそのダイアログが表示されない場合があります。
その表示されない場合とは、QUERY関数とARRAYFORMULA関数を組み合わせて使うケースです。
つまり、エラーの原因は、
アクセスを許可していない別スプレッドシートのデータが参照できなくて、結果が0件と認識されてしまった
ということです。
なので、解決策としては
どこのセルでもいいので、QUERY関数内で参照したい別スプレッドシートをIMPORTRANGE関数で参照する式を用意すること
です。
例えば、
=IMPORTRANGE(“スプレッドシートID”,”シート1!A1″)
でもOKです！
そうすると、「アクセスを許可」するダイアログが表示されるので、「アクセスを許可」をクリックすることで権限が付与され、正常に別スプレッドシートが参照できるようになるのです。
トラブルに遭遇したときには、自分の検索力が試されます。
大抵のことは検索すれば解決方法は見つかりますが、その解決方法を辿り着くためにどのようなキーワードで検索すればいいのかが鍵になります。
今回は「QUERY ARRAYFORMULA バグる」とか、「QUERY ARRAYFORMULA おかしい」、「QUERY ARRAYFORMULA エラー」などで調べましたが、検索ワードを変えても解決方法になかなか辿り着かなかったとき、みなさんならどうしますか？
詳しい人が近くにいれば質問することもできますし、ネットの質問コーナーで投げかけてもいいかもしれません。
私の場合は、関数に関するトラブルであれば「絶対に関数の仕様で理解できていないことがあるんじゃないか？」と思うようにしていて、関数の仕様を改めて見直すようにしています。
今回のケースでは、IMPORTRANGE関数を見直すことで解決できました。
このアプローチで今のところ解決できなかったことはないので、これからももしネット検索しても解決方法がなかなか見つからなかったことがあれば、備忘も兼ねて記事に整理することにしますね！
今回の記事がみなさんのお役に立つことができれば幸いです。
最後まで読んでいただき、ありがとうございました！
一つ目は、
侍エンジニア塾
です。
VBAやJavaScript関連でネット検索したことがある方であれば、一度は見かけたことある名前かと思います。
JavaやPython、C言語、VBA、JavaScriptなど、様々なプログラミング言語をオンラインで習得するためのサービスを提供しているところなので、自分が習得したい言語がもしありましたら、無料体験レッスンを受けてみると良いかもしれません。
そこでプロ講師に相談をして、習得するプログラミング言語に対して直接質問するのがベストです。
ストアカ
です。
こちらはあえてプログラミング学習専門ではなく、もっと手軽に学ぶ機会を得るサービスをチョイスしてみました。
VBAやJavaScriptなどのワンツーマンレッスンを開催している講師が多数登録しているので、まずは出費を抑えて学習したいと考えている方にお勧めです。
↓ 今日も１日ポチッと ↓
↑ いつもボタンクリ応援・口コミ応援ありがとうございます ↑
