代码之家 › 专栏 › 技术社区 › gideonzane

动态命名范围的实际工作方式

named-ranges excel-formula dynamic

0

gideonzane · 技术社区 · 6 年前

所以我的问题是关于动态命名范围的功能。我做了一些研究,我所看到的一切似乎都涉及到解决具体问题,但没有一个是我遇到的问题。所以,我要说明我想做什么,希望你们中的一位能够帮助我理解为什么我会得到我得到的结果!

我正在尝试使用动态命名范围创建数据验证列表。我更喜欢不使用宏,这样我就可以为不懂计算机的同事创建一个更加用户友好的界面。我遇到的问题是,当我使用公式从范围(位于单独的隐藏表上)中提取时,公式不是提取范围中的所有项目,而是从同一行中提取一个项目。

例如,如果我的命名范围中有价值6美元的“苹果” 水果 ,公式为:

`=SEARCH(fruits, "I ate fifteen bananas today.")`

将编译为

`=SEARCH("apples", "I ate fifteen bananas today.")`

而不是

=SEARCH({"pears";"bananas";"mangos";"grapes";"apricots";"apples";"oranges";"raspberries"}, "I ate fifteen bananas today.")

我尝试将公式作为数组输入,但没有效果。我不一致地让我的公式在DNR中的每一项中运行,而不是只运行一项,我想澄清一下它们在Excel中的作用。我有没有遗漏什么模式?我将包括一些我尝试过的其他公式,以获取更多信息;我不知道这些是否有用,但更多的信息就是更多的信息。

我从一个单独的工作表中提取这些信息,因为我不想发布学生信息。这是我正在设计的一款游戏,但问题完全相同(我实际上是用一款来帮助构建另一款——tee-hee-hee)。

提前感谢任何能够提供一些见解的人!我通过你们在线学习了大部分知识,这是我第一次在Excel上提问。干杯

附加代码

    `=SUMPRODUCT(--ISNUMBER(SEARCH(keywords, INDEX(skillsActive, MATCH(B3, OFFSET(skillsActive, 0,2),0)))))`

我在这个网站上找到了这个公式(我不记得线程了,但是,很抱歉程序员!)。它搜索 DNR,但将所有内容都转换为0或1,我希望它将结果准备为字符串,并基于这些结果创建一个列表。

    `{=IF(B2="","",IF(ISERROR(SEARCH(B2,skillsActive))=FALSE, OFFSET(skillsPassive, 0, 1), FALSE))}`

范围 技能活动 也扩展了我希望的方式,但它不是DNR;它被命名为range,其单元格值是原始字符串。 地下二层 是的列表 关键词 使用数据验证。此公式仅返回 技能活动 -它要么返回士兵如果B2=“盲”,或 错误的 对于任何其他结果。

1 回复 | 直到 6 年前

1

Chronocidal 6 年前

使用 SEARCH Array Formula ,理想情况下 AGGREGATE . 我们可以使用 SUMPRODUCT 强制在法线公式中计算阵列公式。

=SUMPRODUCT(AGGREGATE(15,6,SEARCH(fruits, "I ate fifteen bananas today."),1))

分解:

SEARCH(fruits, "I ate fifteen bananas today.")
在 ,其计算结果为数组公式: SEARCH({"pears";"bananas";"mangos";"grapes";"apricots";"apples";"oranges";"raspberries"}, "I ate fifteen bananas today.")
并成为:
{#VALUE!;15;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

AGGREGATE(15,6,{#VALUE!;15;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!},1)
第二个论点是 6 ,因此我们放弃所有错误值:
AGGREGATE(15,6,{15},1)
第一个参数是 15 1 )告诉我们要检索哪个元素,在本例中是最小的:
15

=SUMPRODUCT(15)
这个 SUMPRODUCT公司 主要是强制将其作为数组公式进行计算。它将每个参数中的相应元素相乘,然后对结果求和:
=15