用动态ImportRange填充ArrayFormula [英] Fill ArrayFormula with dynamic ImportRange

问题描述

我有一张Google电子表格，用于收集各种表格中的学生的标记以创建一张完整的标记。

每位教师在自己的电子表格中输入标记，作为工作表名称。任何一张都有两列。 Roll＃&分数。使用纸张编号（表格名称），Google Sheet Key，Roll＃&标记我手动创建公式来使用Sort，ArrayFormula& ImportRange函数。这工作。
但手动创建公式是容易出错的方法。另外，我想教每个老师如何做到这一点。

这是主表。
https://docs.google.com/spreadsheets/d / 1IYOxZOZEnaQP8p69IsN5wDOtJhQBml1qARjj4yqA4Ds / edit？usp = sharing

我想要在单元格G23中添加公式，以便它基于单元格G2中的条目动态创建ImportRange ：它上面的G22。如果有3个名称，则创建3个ImportRanges，如果有5个名称，则动态创建5个ImportRanges，以便通过包含ArrayFormula& amp; amp;依次排序。

现有公式是我手动创建的公式。有用。我所需要的就是拥有公式，这样如果在G2-G22中添加了新的教师名称，就会在ArrayFormula中添加一个新的ImportRange。

解决方案

div>

由于导入范围函数具有输出数组的能力，因此它不能在数组中迭代。

结合你的公式，使用这个公式：

  = =排序（ARRAYFORMULA（{ &安培; JOIN（;，ARRAYFORMULA（ IMPORTRANGE（  &安培; VLOOKUP（FILTER（G2：G20，G2：G20<> 中！），表Sheet 3 $ A $ 2：$ B，2,0）&& G1&！A2：B）））&}），1，True，2，True）
  

然后您可以手动将结果粘贴到G23中或使用脚本粘贴公式

I have a Google Spreadsheet that collects marks of students from various sheets to create a complete marksheet.

Each teacher enters marks in his/her own spreadsheet with paper number as sheet names. Each sheet of any has just two columns. Roll# & Marks. Using combination of paper number (sheet name), Google Sheet Key, Roll# & Marks I MANUALLY create formulas to collate data using Sort, ArrayFormula & ImportRange functions. This works. But Manually creating formulas is error prone approach. Plus, I want to teach each teacher how to do this.

This is the Master Sheet. https://docs.google.com/spreadsheets/d/1IYOxZOZEnaQP8p69IsN5wDOtJhQBml1qARjj4yqA4Ds/edit?usp=sharing

What I want is to add formula in cell G23 so that it creates ImportRange dynamically based on the entries in cells G2: G22 above it. If there are 3 names, it creates 3 ImportRanges, if there are 5 names, it creates 5 ImportRanges dynamically so that these ImportRanges are merged by enveloping ArrayFormula & is in turn sorted.

Existing formula is what I created manually & it works. All I want is to have the formula so that if a new teacher name is added in G2-G22, a new ImportRange is added within ArrayFormula.

解决方案

As the importrange function has the ability to output arrays, it can't be iterated across an array.

To combine your formula, use this formula:

="=sort(ARRAYFORMULA({"&JOIN(";",ArrayFormula("IMPORTRANGE("""&VLOOKUP(FILTER(G2:G20,G2:G20<>""),Sheet3!$A$2:$B,2,0)&""","""&G1&"!A2:B"")"))&"}),1,True,2,True)"

then you could paste the result in G23 manually or use script to paste formula

这篇关于用动态ImportRange填充ArrayFormula的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！