質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Google

Googleは、アメリカ合衆国に位置する、インターネット関連のサービスや製品を提供している企業です。検索エンジンからアプリケーションの提供まで、多岐にわたるサービスを提供しています。

Q&A

解決済

2回答

1235閲覧

Googleスプレッドシート で Query関数の構文でセル参照を使いたい

sawa

総合スコア3002

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Google

Googleは、アメリカ合衆国に位置する、インターネット関連のサービスや製品を提供している企業です。検索エンジンからアプリケーションの提供まで、多岐にわたるサービスを提供しています。

0グッド

1クリップ

投稿2018/10/03 05:40

編集2018/10/03 05:43

GASでなくスプレッドシート関数の質問となります。

Query関数を利用して、会社の売上データを格納したマスタのスプレッドシートから、
条件に合致したものを 色々な並び順で表示させたいと思っています。

=IF(CONCATENATE(B2:E2,B4)="","",QUERY(IMPORTRANGE("売上マスタスプレッドシートのURL","'売上推移表'!A1:CI1100"),"Select Col6,Col9,Col68,Col67,Col66,Col69,Col70,Col71,Col72,Col73,Col74,Col75,Col76,Col77,Col78,Col79,Col80,Col81,Col85,Col86,Col87 where Col1 is not Null and Col68 like '"& IF(B2="","%","%"&B2&"%") &"' and Col67 like '"& IF(C2="","%","%"&C2&"%") &"' and Col66 like '"& IF(D2="","%","%"&D2&"%") &"' and Col9 like '"& IF(E2="","%","%"&E2&"%") & "' Order by Col69 desc",FALSE))

少し省略していますが、こんな感じの関数で

B2 営業部(プルダウンで選択)
C2 営業課(プルダウンで選択)
D2 担当営業 (自由記述)
E2 お客様名 (自由記述)

とセルを選択することで抽出条件を絞りこみ合致するデータを表示しています。

うまくいかない箇所は 並び順の Order by ~ のところです。

営業部や営業所、担当営業で抽出した際、の並び順を
合致条件と同じように 「セル参照」で 今期売上順 や 前期売上順 などの切替を
ユーザーが セルの プルダウンのセルで 気軽にできるようにしたいのですが、
どうやっても Order by のところ にセルを参照する関数をもってくるとエラーとなります。

例:B4セルで プルダウンで 並び順を 選択できるようにしておき、
別シートで VLOOKUPで 選択に応じた Order by~の構文を読み込ませています。
VLOOKUP(B4,'マスタ'!C2:E15,3,FALSE)

現状は上記のQuery関数をさらにSORTでくくって並び順を切替させているのですが、
できれば全件表示でなく Limit で50件等に絞って表示させたいと思っており、
そうなると Query関数内で 並びをコントロールする必要があります。

Order by~ の部分は セル参照でコントロールは出来ないものでしょうか?
詳しい方、ご教授いただけますと幸いです。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

※ 動作未検証です

まず、F2に「"","営業部","営業課","担当営業","お客様名"」を選択項目として入れておきます。
つぎに、"' Order by Col69 desc"のところを
"' Order by" & IF(F2 = "営業部", "Col68", IF(F2="営業課","Col67",IF(F2="担当営業","Col66",IF(F2="お客様名","Col9","Col69"))))&"desc"
のように変えます。

これで、OrderのあとのCol__を変更できるのではないでしょうか?

投稿2018/10/03 06:09

papinianus

総合スコア12705

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sawa

2018/10/03 09:28

ご回答ありがとうございます。試してみましたがエラーとなりました。 が、ミスの発見のきっかけをつくっていただけましたこと感謝いたします。 単純に関数で結合した時にスペースがなくなっていたようです。。 出来上がった構文が " Order byCol68desc" となってしまうからで、 頭のところを "' Order by " & と後ろにもスペースを入れ 最後も &" desc" と前にスペースを入れることで 正しく機能しました。 よくよく考えれば単純な誤りでしたね。失礼いたしました。
guest

0

自己解決

自己解決となりました
セル参照で結合する際にスペースが無くなっており、
"Orde byCol69desc" といった具合になっていたようです。

GAS

1=IF(CONCATENATE(B2:E2,B4)="","",QUERY(IMPORTRANGE("売上マスタスプレッドシートのURL","'得意先別売上推移表'!A1:CI1100"),"Select Col6,Col9,Col68,Col67,Col66,Col69,Col70,Col71,Col72,Col73,Col74,Col75,Col76,Col77,Col78,Col79,Col80,Col81,Col85,Col86,Col87 where Col1 is not Null and Col68 like '"& IF(B2="","%","%"&B2&"%") &"' and Col67 like '"& IF(C2="","%","%"&C2&"%") &"' and Col66 like '"& IF(D2="","%","%"&D2&"%") &"' and Col9 like '"& IF(E2="","%","%"&E2&"%") & "' Order by " & VLOOKUP(B4,'マスタ'!C2:E13,2,FALSE) &VLOOKUP(B4,'マスタ'!C2:E13,3,FALSE)&" LIMIT 50",FALSE))

とし、マスタシート側でVLOOKUPの参照を

式1 式2
売上順_今期 Col69 desc
売上順_前期 Col70 desc
売上順_2期前 Col71 desc
売上 上昇額_対前期 Col74 desc
売上 下落額_対前期 Col74 asc

↑Col__の後ろには全てスペース入り
といった感じで用意することで、希望した順序で並べた50件だけを表示することができました。

単純ミスで失礼いたしました。

投稿2018/10/03 09:36

編集2018/10/03 09:38
sawa

総合スコア3002

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問