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

问题描述

我有一个 Google 表格，可以从各种表格中收集学生的分数以创建完整的分数表格.每位教师在他/她自己的电子表格中输入分数，并以纸张编号作为表格名称.any 的每张纸只有两列:Roll# 和 Marks.使用纸张编号(纸张名称)、Google Sheets Key、Roll# 和 Marks 的组合，我手动创建了使用 Sort、ArrayFormula 和 IMPORTRANGE 函数整理数据的公式.这有效.

I have a Google Sheets that collects marks of students from various sheets to create a complete mark sheet. Each teacher enters marks in his/her own spreadsheet with paper number as sheet names. Each sheet of any has just two columns: Roll# and Marks. Using combination of paper number (sheet name), Google Sheets Key, Roll# and Marks I MANUALLY create formulas to collate data using Sort, ArrayFormula and IMPORTRANGE functions. This works.

但是手动创建公式是一种容易出错的方法.另外，我想教每位老师如何做到这一点.

But manually creating formulas is an error prone approach. Plus, I want to teach each teacher how to do this.

我想要的是在单元格 G23 中添加公式，以便它根据其上方的单元格 G2:G22 中的条目动态创建 IMPORTRANGE.如果有3个名字，就创建3个IMPORTRANGE，如果有5个名字，就动态创建5个IMPORTRANGE，使这些IMPORTRANGE通过封装ArrayFormula进行合并和排序.

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 three names, it creates three IMPORTRANGEs, if there are five names, it creates five IMPORTRANGEs dynamically so that these IMPORTRANGEs are merged by enveloping ArrayFormula and sorted.

现有公式是我手动创建的，并且有效.我想要的只是有一个公式，这样如果在 G2-G22 中添加了新的教师姓名，则在 ArrayFormula 中添加了一个新的 IMPORTRANGE.

Existing formula is what I created manually and 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.

推荐答案

由于 importrange 函数具有输出数组的能力，因此无法在数组中迭代.

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)"

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

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

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