查询多个范围并自动添加一列以了解每一行的来源 [英] Query several ranges and add automatically a column to know the source of each row

问题描述

我正在尝试在Google Spreadsheets中实现以下目标.

I am trying to achieve the following in Google Spreadsheets.

首先，我要查询几个范围(同一电子表格的不同工作表中).我尝试了类似=query(arrayformula({indirect(E2:E10)}),"select * where Col1 <>''")这样的公式，但没有成功

First, I want to query several ranges (in different sheets from the same spreadsheet). I tried a formula like this =query(arrayformula({indirect(E2:E10)}),"select * where Col1 <>''") with no success

在E2:E10中，我有一个范围列表. F列包含一个名称，该名称描述E列中值的来源.

In E2:E10 I have a list of ranges. Column F contains a name that describes the source of the value in Column E.

我的第二个问题是我需要在该查询的输出中添加一列，以告诉我每一行的来源.

My second problem is that I need to add a column to the output of that query that tells me the origin of each row.

如果来源是按国家/地区划分的3列范围，则我需要合并这些表格并将该国家/地区添加到每一行.

If the sources are ranges of 3 columns by country I need to merge those tables and add that country to each row.

推荐答案

+ Ben Liebrand的所有功劳，在这里帮助了我: https://support.google.com/docs/profile/3464

All credits to +Ben Liebrand who helped me out here: https://support.google.com/docs/profile/3464

我只想开始说，indirect()函数不能按预期在arrayformula()函数中工作.因此，您将需要采取另一种方法.我可以理解您要尝试执行的操作，因此我在电子表格中添加了另一个TAB，以演示另一种方法.我知道这最初是您要尝试的特定设计，所以我对您的设计进行了一些更改.也许您可以看一下我提供的产品，也许您可​​以调整设计.

"I just want to start of by saying that the indirect() function does not work in an arrayformula() function as expected. So you will need to take another approach. I can understand what you are trying to do so I added another TAB in your spreadsheet to demonstrate another approach. I know it was initially a specific design you were trying so I made some changes to what you had. Maybe you can take a look at what I have offered and maybe you can tweak your design.

我知道我提供的内容非常粗糙，但是您还会注意到，我从范围表中的范围中删除了末端行说明符.

I know what I am offering is just very rough but you will also notice that I removed the end row specifier from your ranges in the range table.

不要以我的示例为最终结果，但我只是想表明您试图与indirect()函数一起使用的范围不起作用.

Don't assume my example to be the final result but I was just trying to show that the range you were trying to use with the indirect() function will not work.

因此，希望这会对您如何处理此问题有新的认识.

So hopefully this will give you a new idea of how you can maybe handle this.

我的公式还将国家/地区添加到输出中的每个表中.我的公式看起来像这样

My formula also adds the country to each of the tables in the output. My formula looks like this

=query(ArrayFormula({
if(len(indirect(regexextract(F2,"\w+\!\w+")&":A")),G2,),indirect(F2);
if(len(indirect(regexextract(F3,"\w+\!\w+")&":A")),G3,),indirect(F3);
if(len(indirect(regexextract(F4,"\w+\!\w+")&":A")),G4,),indirect(F4);
if(len(indirect(regexextract(F5,"\w+\!\w+")&":A")),G5,),indirect(F5);
if(len(indirect(regexextract(F6,"\w+\!\w+")&":A")),G6,),indirect(F6);
if(len(indirect(regexextract(F7,"\w+\!\w+")&":A")),G7,),indirect(F7)
})," select * where Col1 <> '' ")

希望这对您有帮助"

我希望对社区有用 杰罗尼莫

And I hope is useful to the community Gerónimo

这篇关于查询多个范围并自动添加一列以了解每一行的来源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！