合并来自不同工作表的特定行值 [英] consolidate specific row values from different sheets

问题描述

我正在寻找一种在满足某些验证条件时合并多个电子表格中特定行值的方法.

I'm looking for a way to consolidate specific row values from multiple spreadsheets when certain validation are met.

方案:我目前有Sheet1，Sheet2，Sheet3 我想要的是在满足验证条件时将Sheet2和Sheet3中的数据合并到Sheet1中.

Scenario: I currently have Sheet1, Sheet2, Sheet3 what I want is to consolidate the data from sheet2 and sheet3 into sheet1 when the validation is met.

Sheet1:

Sheet2:

Sheet3:

基于上面的示例，我在sheet1单元格A2中使用了此功能

Based on the example above, I used this function in sheet1 cell A2

=iferror(query(Sheet2!A2:E, "where E = 'New Player' ",0),"")

因此，如果E列类型"为新播放器"(验证)，它将自动从Sheet2中添加行值.

so what happens is it automatically adds the row values from Sheet2 if Column E "Type" is "New Player" (validation).

我想要的是，如果类型为"New Player"，我还想添加sheet3行数据.

What I want is I would also like to add sheet3 row data if its type is "New Player".

也许有一种嵌套查询功能的方法?还是可以使用脚本来完成?我发现一些脚本可以复制行，但无需验证并且不能从多个工作表复制.

maybe there's a way to nest query function? or can this be done using script? I found some scripts that is able to copy rows but without the validation and not from multiple sheets.

推荐答案

假设您在同一电子表格中进行查询，则类似的事情应该起作用

Assuming you are querying within the same spreadsheet, something like this should work

=iferror(query({Sheet2!A2:E; Sheet3!A2:E}, "where Col5 = 'New Player' ",0),"")

并且，如果您想选择返回哪些列(例如A，B，C，但其余不返回)

And if you want to pick which columns to return (example A, B, C but not the rest)

=iferror(query({Sheet2!A2:E; Sheet3!A2:E}, "Select Col1, Col2, Col3 where Col5 = 'New Player' ",0),"")

这篇关于合并来自不同工作表的特定行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！