英文:
Multiple columns in QUERY with "and" in Google Sheets
问题
Here is the translation of the provided content:
如何在查询中选择2列而不是1列来搜索指定的值？当前的公式只搜索Col4，我需要在屏幕1上的突出显示的位置搜索Col4和Col5。
为了清晰起见，让我们来看一下“Data”工作表的截图2。在当前的公式中，输出只针对第4列（屏幕上的红色）是的，但如何使输出同时针对第4列和第5列（屏幕上的蓝色）呢？我尝试了不同的选项，包括插入“或”，但没有成功。
截图1
截图2
A7单元格中的公式：
=QUERY({Data!A2:E}," where (Col4='"& B1&"' or Col5='"& B1&"') "&IFS(
C3,"and Col2='"& B3&"'"&IF( C2,"and Col4='"& B2&"'"",)&"",
E3,"and Col2='"& D3&"'"&"",
C2,"and Col4='"& B2&"'"",
NOT(AND(E3,C3))," "&IF( C2,"and Col4='"& B2&"'"",)&"")&"")
英文:
How to choose 2 columns in QUERY instead of one to search for specified values? The current formula searches only one Col4, I need to search both Col4 and Col5 in the highlighted places on the screen 1.
For clarity, let's take a screenshot 2 of the "Data" sheet. In the current formula, the output is only for column 4 (red colour on screen), but how do I make the output for both column 4 and column 5 (blue colour on screen)? I tried different options, including inserting "or", but it didn't work
Screenshot 1
Screenshot 2
Formula in A7:
=QUERY({Data!A2:E}," where (Col4='"& B1&"' or Col5='"& B1&"') "&IFS(
C3,"and Col2='"& B3&"'"&IF( C2,"and Col4='"& B2&"'",)&"",
E3,"and Col2='"& D3&"'",C2,"and Col4='"& B2&"'",
NOT(AND(E3,C3))," " &IF( C2,"and Col4='"& B2&"'",)&"")&"")
答案1
得分: 1
=QUERY({Data!A2:E},"where (Col4='"& B1&"' or Col5='"& B1&"') "&IFS(
C3,"and Col2='"& B3&"'"&IF( C2,"and (Col4='"&B2&"' or Col5='"&B2&"')",)&"",
E3,"and Col2='"& D3&"'" C2,"and (Col4='"&B2&"' or Col5='"&B2&"')",
NOT(AND(E3,C3))," " &IF( C2,"and (Col4='"&B2&"' or Col5='"&B2&"')",)&"")
英文:
Answer:
=QUERY({Data!A2:E},"where (Col4='"& B1&"' or Col5='"& B1&"') "&IFS(
C3,"and Col2='"& B3&"'"&IF( C2,"and (Col4='"&B2&"' or Col5='"&B2&"')",)&"",
E3,"and Col2='"& D3&"'", C2,"and (Col4='"&B2&"' or Col5='"&B2&"')",
NOT(AND(E3,C3))," " &IF( C2,"and (Col4='"&B2&"' or Col5='"&B2&"')",)&"")&"")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论