”表示形式（数値／書式なしテキスト、など）” が違うと、queryで別シートから値を取得することはできないのか？

Question

スプレッドシートAのデータを、
スプレッドシートBに　次のようなクエリ関数で取得しています。

QUERY(importrange("●●●●…","シート1!A2:H9000"),"select Col6 where Col2 like '"A45"' and Col3 like '"E77"'")

基本的にはうまく言っているのですが、

A　元データの該当列の表示形式が、　
メニュー＞表示形式＞数字＞数値

B　クエリ式が入っている該当列の表示形式が、　
メニュー＞表示形式＞数字＞書式なしテキスト

などのようにずれていると、
「クエリが空で出力しました」
というエラーになってしまいます。

元データは、
一部が数値
一部がテキストで構成されています。

テキストを取得しようと思うと、
数値が「書式なしテキスト」として取得されてしまうため、その後の計算処理ができなくなります。

数値として取得をしようとすると、
今度はクエリが「空です」のエラーになってしまいます。

・・・

解決方法として、
●そもそもクエリを使わずに、GASでコピーをするなどの処理
●数字データだけは、別の列に引っ張るような操作をする
・・・などを考えているのですが、

もしも、よりよい
もっとシンプルな、解決策があればお教えいただきたく　投稿いたしました。

どうぞよろしくお願い致します。

Comments

[sawa]

>数値が「書式なしテキスト」として取得されてしまうため、その後の計算処理ができなく
どういう計算処理かわからないですが、ここの数式をいじって回避するのが手っ取り早いのでは

[yoichiyy]

ご説明不足で失礼いたしました。
もう少し申し上げますと、上記のようなクエリで取得した数字が４つあります。
この４つの平均を出すというだけのaverage関数です。

このような状況で、
「ここの数式をいじって回避する」
もしも、具体的な解決策があれば、お教え下さいませ。

sawa様、早速のご回答・アドバイスに、感謝いたします。

[sawa]

>この４つの平均を出すというだけのaverage関数
可能なら具体的な式をください。それを 文字列化した数字に対応する形にしますので。

[yoichiyy]

「文字列化した数字に対応する形」という　方法があるのですね！

関数自体は非常にシンプルで、こちらです。

=average(KG42:KG45)

KG４２、４３、４４、４５に、
それぞれ、質問投稿で提示した　QUERY式が入っております。
ここに、文字列化された数式が入っております。

「文字列化した数字」という概念を得ただけでも、すでに大きな収穫です。
sawa様、どうもありがとうございます。

Answer 1

Query関数は一つの列に 文字列と数値が混在している場合、数値の方が多いと文字列を無視するという謎仕様である為、一度対象列を全て文字列化する等で回避する必要があります。

今回のご質問はQuery関数は動いていて文字列としては抽出できているようなので、
その後の計算の方の式を変更することで 文字列化 した数字を数値として扱えるようにします。

書式なしテキストで文字列化した数字は *1 をすれば数値になります。
ただ 数字じゃない文字列に *1 しちゃうとエラーになるので、そこだけ回避すればOKです。

=average(ARRAYFORMULA(IFERROR(KG42:KG45*1,KG42:KG45)))

Comments

[yoichiyy]

arrayformulaの活用といい、感動でございます。
質問をしてみて、本当によかったです。
sawa様、本当にどうもありがとうございました！