问题描述
我有一张Google电子表格，用于收集各种表格中的学生的标记以创建一张完整的标记。
每位教师在自己的电子表格中输入标记，作为工作表名称。任何一张都有两列。 Roll＃&分数。使用纸张编号（表格名称），Google Sheet Key，Roll＃&标记我手动创建公式来使用Sort，ArrayFormula& ImportRange函数。这工作。
但手动创建公式是容易出错的方法。另外，我想教每个老师如何做到这一点。
这是主表。
我想要在单元格G23中添加公式，以便它基于单元格G2中的条目动态创建ImportRange ：它上面的G22。如果有3个名称，则创建3个ImportRanges，如果有5个名称，则动态创建5个ImportRanges，以便通过包含ArrayFormula& amp; amp;依次排序。
现有公式是我手动创建的公式。有用。我所需要的就是拥有公式，这样如果在G2-G22中添加了新的教师名称，就会在ArrayFormula中添加一个新的ImportRange。
由于导入范围函数具有输出数组的能力，因此它不能在数组中迭代。
结合你的公式，使用这个公式：
= =排序（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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！