英文:
How do I import multiple rows into a different tab in Google Sheets, based on whether they contain a keyword?
问题
我有一个博客数据库，都基于不同的业务单位（包括标题、主题、进展、发言人等信息）。
我想创建单独的选项卡，可以自动从每个业务单位的行中提取数据（即在特定列中包含关键词“银行”、“能源与公用事业”或“零售”的行）。
不幸的是，尽管查看了一些教程和示例，我还没有找到任何可以帮助的VLOOKUP、IMPORTRANGE或INDEX/MATCH等函数。
示例：
我在这里创建了一个示例电子表格：示例电子表格链接
我想能够搜索第C列，找到包含关键词“银行”的每一行，并自动将所有这些行导入到一个名为“银行”的单独选项卡。然后，该公式可以重新用于不同的业务单位的不同选项卡。
是否有一个可以让我做到这一点的公式或解决方案？
谢谢！
我尝试了VLOOKUP、XLOOKUP、INDEX/MATCH、QUERY(IMPORTRANGE)在单独的工作表中，以及其他相关函数，但未能使这些函数适用于我的数据库。大多数努力都导致错误代码或无法解析（尽管我非常愿意承认我可能犯了错误）。
英文:
I have a database of blogs, all based around different business units (including information like headline, topic, progress, spokesperson, etc).
I'd like to create separate tabs into which I can automatically pull the rows for each business unit (i.e. containing the keyword for "Banking", or "Energy & Utilities", or "Retail" in a certain column).
Unfortunately, despite looking at some tutorials and examples, I haven't been able to find any VLOOKUP, IMPORTRANGE, or INDEX/MATCH (etc.) functions that can help.
Example:
I've created an example spreadsheet here: https://docs.google.com/spreadsheets/d/19lHTxLaAgtHz5wg0sLLVw1YSpEXvQycL2PUd6Grs4X0/edit?usp=sharing
I'd like to be able to search column C for every row which contains the keyword "Banking" and automatically import all of these rows into a separate tab, named 'Banking'. That formula could then be repurposed for different tabs for each Business Unit.
Is there a formula or solution that would let me do this?
Thank you!
I attempted VLOOKUP, XLOOKUP, INDEX/MATCH, QUERY(IMPORTRANGE) in a separate sheet, and other related functions, but was unable to repurpose these functions to work for my database. Most efforts resulted in error codes or an inability to parse (although I'm more than willing to admit I probably made a mistake).
答案1
得分: 0
在你的“银行”表的A1单元格中使用以下公式：
=FILTER('主表'!A:E,'主表'!C:C="银行")
英文:
In A1 of your Banking sheet use:
=FILTER('Master sheet'!A:E,'Master sheet'!C:C="Banking")
答案2
得分: 0
在您的“银行”选项卡中，您可以尝试以下任何一种：
=QUERY('主工作表'!A:E, "WHERE C='银行'", 1)
或
={'主工作表'!A1:E1;FILTER('主工作表'!A:E, '主工作表'!C:C="银行")}
或
=LAMBDA(z,{chooserows(z,1);filter(z,choosecols(z,3)="银行")})('主工作表'!A:E)
英文:
You can try either of these in your Banking tab:
=QUERY('Master sheet'!A:E,"WHERE C='Banking'",1)
OR
={'Master sheet'!A1:E1;FILTER('Master sheet'!A:E,'Master sheet'!C:C="Banking")}
OR
=LAMBDA(z,{chooserows(z,1);filter(z,choosecols(z,3)="Banking")})('Master sheet'!A:E)
答案3
得分: 0
Sure, here are the translated parts:
- "=QUERY(IMPORTRANGE("19lHTxLaAgtHz5wg0sLLVw1YSpEXvQycL2PUd6Grs4X0","Master Sheet!A:E")," Select * where Col3 Contains 'Banking' ")"
Translated to Chinese:
"=QUERY(IMPORTRANGE("19lHTxLaAgtHz5wg0sLLVw1YSpEXvQycL2PUd6Grs4X0","主表!A:E")," 选择 * where Col3 包含 'Banking' ")"
- "=QUERY(IMPORTRANGE("19lHTxLaAgtHz5wg0sLLVw1YSpEXvQycL2PUd6Grs4X0","Master Sheet!A:E")," Select * where Col3='Banking' ")"
Translated to Chinese:
"=QUERY(IMPORTRANGE("19lHTxLaAgtHz5wg0sLLVw1YSpEXvQycL2PUd6Grs4X0","主表!A:E")," 选择 * where Col3='Banking' ")"
英文:
=QUERY(IMPORTRANGE("19lHTxLaAgtHz5wg0sLLVw1YSpEXvQycL2PUd6Grs4X0","Master Sheet!A:E")," Select * where Col3 Contains 'Banking' ")
or
=QUERY(IMPORTRANGE("19lHTxLaAgtHz5wg0sLLVw1YSpEXvQycL2PUd6Grs4X0","Master Sheet!A:E")," Select * where Col3='Banking' ")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论