Google スプレッドシートのクエリ(条件集計)について
- 評価
- クリップ 0
- VIEW 2,687
現在Googleスプレッドシートを使い他のスプレッドシートから
名前を参照して集計をしているのですが、日付と名前をを条件抽出をしたいですが
やり方はわかりません。
現在の名前での抽出するための関数がこちらになります。
=iferror(sum(QUERY(IMPORTRANGE("スプレッドシートキー","タブ名!A:L"),"select Col集計対象列番号 where Col名前列番号 ='"&担当セル番号&"'",1)),0)参照先のスプレッドシートには
タイトル:4月集計報告表
日---担当-----集計項目
1 | A 3 |
1 | B 4 |
4 | A 1 |
5 | A 0 |
7 | C 5 |
8 | A 6 |
9 | C 2 |
9 | B 1 |
このような表記です。
※日の列は4/1なら1と表記されています。
これが約40シートあります。
上記関数だと現在の集計は
タイトル:4月集計まとめ
担当---集計合計---集計合計---集計合計---約40行---合計
A | 10
B | 5
C | 7
D | 0
E | 0
F | 0
G | 0
一覧表にまとめられます。（月間）
](e738446c8c1175c805d7f293ec325b6a.png)
これを日付条件を付けて集計を取りたいです。
日付の条件を付ける（正確には日付ではなく1～7の数字）
タイトル:4月1週(1~7)まとめ
担当---集計合計---集計合計---集計合計---約40行---合計
A | 4
B | 4
C | 5
D | 0
E | 0
F | 0
G | 0
上記画像のように週間一覧表を自動集計をしたいです。
以前同じような質問させていただきましたら
=IFERROR(QUERY(IMPORTRANGE("スプレッドシートキー","範囲"),"select Col2,sum(Col3) where Col1>=1 and Col1<=7 group by Col2 label sum(Col3) ''"),"")
1,7をセル参照でやりたい場合は、
=IFERROR(QUERY(IMPORTRANGE("スプレッドシートキー","範囲"),"select Col2,sum(Col3) where Col1>="&参照セル&" and Col1<="&参照セル&" group by Col2 label sum(Col3) ''"),"")
と回答を頂いたのですが、
こちらだと対象の項目しか表示されることが無く、
対象結果の転載という感じでした。なので都度タブを制作し、一覧表にsumifを使わなくてはなりません。この方法もいいのですが、なかなか工数のかかるもので、更にスプレッドシートがかなり
重くなります。
集計先の一覧表にある担当を参照させ、条件に合ったもの（1~7日）を合計して
反映させ、それ以外は0として返すということをしたいです。
-
気になる質問をクリップする
クリップした質問は、後からいつでもマイページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
クリップを取り消します
-
良い質問の評価を上げる
以下のような質問は評価を上げましょう
- 質問内容が明確
- 自分も答えを知りたい
- 質問者以外のユーザにも役立つ
評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。
質問の評価を上げたことを取り消します
-
評価を下げられる数の上限に達しました
評価を下げることができません
- 1日5回まで評価を下げられます
- 1日に1ユーザに対して2回まで評価を下げられます
質問の評価を下げる
teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。
- プログラミングに関係のない質問
- やってほしいことだけを記載した丸投げの質問
- 問題・課題が含まれていない質問
- 意図的に内容が抹消された質問
- 過去に投稿した質問と同じ内容の質問
- 広告と受け取られるような投稿
評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。
質問の評価を下げたことを取り消します
この機能は開放されていません
評価を下げる条件を満たしてません
質問の評価を下げる機能の利用条件
この機能を利用するためには、以下の事項を行う必要があります。
- 質問回答など一定の行動
-
メールアドレスの認証
メールアドレスの認証
-
質問評価に関するヘルプページの閲覧
質問評価に関するヘルプページの閲覧
checkベストアンサー
+1
以前回答した者で恐縮ですが、
たとえばC列４行目から始まるなら、セルC4にこの式を入れます。
=IFERROR(ARRAYFORMULA(VLOOKUP(A4:A,QUERY(IMPORTRANGE("スプレッドシートキー","シート名!A1:C"),"select Col2,sum(Col3) where Col1>=1 and Col1<=7 group by Col2"),2,FALSE)),0)
氏名が空白のセルがあり、その対応値として０が入るのを嫌うならもう少し手を加えます。
=ARRAYFORMULA(IF(A4:A<>"",IFERROR(ARRAYFORMULA(VLOOKUP(A4:A,QUERY(IMPORTRANGE("スプレッドシートキー","シート名!A1:C"),"select Col2,sum(Col3) where Col1>=1 and Col1<=7 group by Col2"),2,FALSE)),0),""))
ARRAY式なので１式だけで下方に全部効きます。
予め氏名がA列に固定されている状況なら上記の回答ができたのですがその辺が曖昧だったような感じなので前回は不十分な回答になってしまっていたかもしれません。
なお、事前にIMPORTRANGEのアクセス許可を済ませてから式を入れます（その辺はご存知だとは思いますが念のため）。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
15分調べてもわからないことは、teratailで質問しよう！
- ただいまの回答率 88.33%
- 質問をまとめることで、思考を整理して素早く解決
- テンプレート機能で、簡単に質問をまとめられる
2017/07/11 11:32
前回は私の説明不足の中ご協力いただきながらも今回もご回答いただきましてありがとうございます。
解決いたしました！
本当に助かりました。