☰

0. 警告

tag-icon • tag-icon microsoft-excel
0. 警告

我创建了一个电子表格，其中有一个可供选择的名称下拉菜单。

这些名称存储在工作表的一系列单元格中。每个名称在相邻的单元格中都有一个优先级编号。

从这些单元格中，我创建了一个表，其中的名称按优先级数字的值排序。

然后，下拉菜单（在另一张工作表上）按正确的优先顺序列出名称。

我想要做的是，当在下拉列表中选择一个名称时，我希望该名称在下次使用电子表格时移动到列表的底部。

在我的脑海里，我需要让每个名字所附带的优先级数字在被选中后变成最高值。

例如：

玛丽 1 史蒂夫 2 大卫 3 罗伯特 4

Mary 被选中了，所以我希望顺序是

玛丽 4 史蒂夫 1 大卫 2 罗伯特 3

我尝试过各种公式，但不知道如何改变这些数字来改变下拉列表的顺序。

有什么建议么？

答案1

0. 警告

这可能不是最有效的解决方案。它是 100% 公式，无需 VBA，但在 Excel 中来回反转数组非常耗时，所以你会得到这个庞大的公式。

1. 设定初始名单

这只是一个纯文本名称列表，按照您希望它们在下拉列表中出现的顺序排列。如果有人从未在您的实际数据中选择过姓名，此列表将确保他们仍显示在下拉列表中。我在名为的工作表中执行了此操作Lists。

2. 设置数据表

这是人们从下拉列表中选择姓名的地方。这可能已经为您设置好了。我制作了一个名为 的表格Data。

3. 设置下拉列表源的位置

我使用了列表表中的另一列。

4. 输入这个怪物公式

=LET(originalNames,Lists!A2:A5,chosenNames,Data!A2:A20,originalRows,ROWS(originalNames),chosenRows,ROWS(chosenNames),seq,SEQUENCE(originalRows+chosenRows),allNames,IF(seq<=originalRows,INDEX(originalNames,seq),INDEX(chosenNames,seq-originalRows)),reversedNames,INDEX(allNames,SEQUENCE(ROWS(allNames),,ROWS(allNames),-1)),uniqueNames,UNIQUE(FILTER(reversedNames,reversedNames<>0)),INDEX(uniqueNames,SEQUENCE(ROWS(uniqueNames),,ROWS(uniqueNames),-1)))

您需要在一开始就编辑范围引用，但仅此而已。该LET()函数允许您定义variable name, variable value，以便稍后引用它。这让我们可以像这样分解公式：

  • originalNames,Lists!A2:A5保存您制作的硬编码名称列表。
  • chosenNames,Data!A2:A20保存迄今为止用户输入的所有姓名。这可以引用表中的字段或以任何其他方式引用单个数据列。
  • originalRows,ROWS(originalNames)计算原始列表中的行数。稍后有用。
  • chosenRows,ROWS(chosenNames)计算数据列表中的行数。稍后有用。
  • seq,SEQUENCE(originalRows+chosenRows)设置一个数组，其值从 1 到我们需要的总行数。
  • allNames,IF(seq<=originalRows,INDEX(originalNames,seq),INDEX(chosenNames,seq-originalRows))首先将两个数组与硬编码列表连接起来，然后再将数据列表连接起来。
  • reversedNames,INDEX(allNames,SEQUENCE(ROWS(allNames),,ROWS(allNames),-1))反转该列表。
  • uniqueNames,UNIQUE(FILTER(reversedNames,reversedNames<>0))过滤掉所有空白（现在已转换为0），然后仅提取唯一值，以便每个名称只出现一次。
  • INDEX(uniqueNames,SEQUENCE(ROWS(uniqueNames),,ROWS(uniqueNames),-1))反转最终列表并打印出来。最后一个参数LET()没有与变量名配对，因为它是输出。

我们现在得到的是一个列表，其中首先包含硬编码列表中从未选择的所有名称（按照您最初输入的顺序），然​​后包含数据中选择的唯一名称，最后是最新的名称。

5. 根据此结果定义动态命名范围

我的公式带有Lists!C2标题，C1因此我为动态命名范围编写了公式，如下所示：

=OFFSET(Lists!$C$2,0,0,COUNTA(Lists!$C:$C)-1)

它从开始C2，然后根据该列中的值的数量使列表更高（不包括顶部的标题，因此-1靠近末尾）。

按钮经理

6. 定义下拉列表以基于此命名范围

我将动态命名范围命名为prioritizedNames，因此我的数据验证如下所示：

验证

7. 最终结果

列表表：

列表

数据表：

数据

答案2

解决方案@Engineer Toast非常有趣，但公式可以缩短一点。您可以使用 VSTACK 来合并列表，并使用 SORTBY 来反转顺序。

=LET(originalNames,Lists!$A$2:$A$5, chosenNames,Data!$A$2:$A$20, 
     allNames,VSTACK(originalNames,chosenNames),
     reversedNames,SORTBY(allNames,SEQUENCE(ROWS(allNames)),-1),
     uniqueNames,UNIQUE(FILTER(reversedNames,reversedNames<>0)),
     SORTBY(uniqueNames,SEQUENCE(ROWS(uniqueNames)),-1))

另外，下拉列表可以用动态引用来定义：

=Lists!$C$2#

相关内容