Google Sheets 具有不同范围的嵌套查询语句

发布于 2025-01-18 06:27:01 字数 1092 浏览 2 评论 0原文

我有两个具有相同列的数据透视表，但过滤器不同

PT1 (P3:V) - 显示所有 CR 数据 PT2 (Y3:AF) - 仅显示 RESC CR 数据

我有一个页面以“更漂亮的格式”总结此数据，并在 B5 中有一个查询，该查询将根据 C2 中选择的内容查询数据。如果他们选择全部或按特定审阅者姓名（均查询 PT1），我可以使用以下公式让它查询所有内容：

=IF(C2="All", QUERY({'Pivot Table 5'! P3:W1001}, "选择 P、Q、S、T、U、V、W"), QUERY({'数据透视表 5'!P3:W1001}, "选择 P, Q, S, T, U, V, W WHERE P = '"&$C$2&"'"))

我试图添加到公式中，以便它仅查询 RESC CR，如果“全部在 C2 (PT2) 中选择“RESC CRs”。

这是我尝试使用的公式：

=IFS(AND(C2="All"), QUERY({'Pivot Table 5'!P3:W1001}, "SELECT P, Q, S, T, U, V, W"), AND(C2="All RESC CRs"), QUERY({'Pivot Table 5'!Y3:AY1001}, "SELECT AR, AS, AU, AV, AW, AX, AY"), AND(C2<>"All", C2<>"All RESC CRs"), Query({'Pivot Table 5'!P3:W}, "SELECT P, Q, S, T, U, V, W WHERE P = '"&$C$2&"'"))

这是示例表： https://docs.google.com/spreadsheets/d/18SC_hQbWeFlYedLN5k0fuuwqgi8fdyW1A4MCfFTpVME/edit?usp=sharing

我假设这是因为第二个查询的查询范围不同？非常感谢任何帮助！

原文

I have two pivot tables with the same columns, but different filters

PT1 (P3:V) - shows all CR data
PT2 (Y3:AF) - shows only RESC CR data

I have a page that summarizes this data in a "prettier format" and have a query in B5 that will query data based on what is selected in C2. I was able to get it to query all if they select all or by specific reviewer's names (both query PT1), using the following formula:

=IF(C2="All", QUERY({'Pivot Table 5'!P3:W1001}, "SELECT P, Q, S, T, U, V, W"), QUERY({'Pivot Table 5'!P3:W1001}, "SELECT P, Q, S, T, U, V, W WHERE P = '"&$C$2&"'"))

I am trying to add to the formula so that it queries only RESC CRs if "All RESC CRs" is selected in C2 (PT2).

Here is the formula I am trying to use:

=IFS(AND(C2="All"), QUERY({'Pivot Table 5'!P3:W1001}, "SELECT P, Q, S, T, U, V, W"), AND(C2="All RESC CRs"), QUERY({'Pivot Table 5'!Y3:AY1001}, "SELECT AR, AS, AU, AV, AW, AX, AY"), AND(C2<>"All", C2<>"All RESC CRs"), Query({'Pivot Table 5'!P3:W}, "SELECT P, Q, S, T, U, V, W WHERE P = '"&$C$2&"'"))

Here is the sample sheet: https://docs.google.com/spreadsheets/d/18SC_hQbWeFlYedLN5k0fuuwqgi8fdyW1A4MCfFTpVME/edit?usp=sharing

I'm assuming it is because the query range for the second query is different? Any help is greatly appreciated!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

亽野灬性zι浪 2025-01-25 06:27:01

尝试：

=IF(C2="All",           QUERY('Pivot Table 5'!P3:W, "select P,Q,S,T,U,V,W"), 
 IF(C2="All RESC CRs"), QUERY('Pivot Table 5'!Y3:AY, "select AR,AS,AU,AV,AW,AX,AY"), 
                        QUERY('Pivot Table 5'!P3:W, "select P,Q,S,T,U,V,W where P = '"&C2&"'")))

更新：

=IF(C2="All",          QUERY('Pivot Table 5'!P3:W, "select P,Q,S,T,U,V,W", ), 
 IF(C2="All RESC CRs", QUERY('Pivot Table 5'!P3:W, "select P,Q,S,T,U,V,W where R contains 'RESC'",),
                       QUERY('Pivot Table 5'!P3:W, "select P,Q,S,T,U,V,W where P = '"&C2&"'",)))

try:

=IF(C2="All",           QUERY('Pivot Table 5'!P3:W, "select P,Q,S,T,U,V,W"), 
 IF(C2="All RESC CRs"), QUERY('Pivot Table 5'!Y3:AY, "select AR,AS,AU,AV,AW,AX,AY"), 
                        QUERY('Pivot Table 5'!P3:W, "select P,Q,S,T,U,V,W where P = '"&C2&"'")))

update:

=IF(C2="All",          QUERY('Pivot Table 5'!P3:W, "select P,Q,S,T,U,V,W", ), 
 IF(C2="All RESC CRs", QUERY('Pivot Table 5'!P3:W, "select P,Q,S,T,U,V,W where R contains 'RESC'",),
                       QUERY('Pivot Table 5'!P3:W, "select P,Q,S,T,U,V,W where P = '"&C2&"'",)))