excel - 基于 VLookup 的动态数据验证列表

Question

我正在尝试将自定义“折扣”列表添加到我的电子表格中。

我有一个包含所有数据的表格，并且包含标准“使用”值(value)的成本，以及 5% 折扣和 10% 折扣的值(value)。

例子:

+---------+-------------------+------+------------+-------------+
|  Code   |       Role        | Used | Used - 5%  | Used - 10%  |
+=========+===================+======+============+=============+
| Test001 |     Employee      | 5.67 |            |             |
+---------+-------------------+------+------------+-------------+
| Test002 | Junior Technician | 9.80 |    9.31    |    8.38     |
+---------+-------------------+------+------------+-------------+
| Test003 |  Project Manager  |  15  |            |             |
+---------+-------------------+------+------------+-------------+
| Test004 |     Engineer      |  20  |     19     |    17.10    |
+---------+-------------------+------+------------+-------------+

然后我有一个数据验证列表，它返回所有其他“角色”以供选择。在它的背面，这填充了成本单元格。

例子:

+----------+----------+----------+-------+
|   Role   | VLOOKUP  | Discount | Cost  |
+==========+==========+==========+=======+
| Employee |          |          | 5.67  |
+----------+----------+----------+-------+
| Engineer |  5%,10%  |   10%    | 15.10 |
+----------+----------+----------+-------+

我想要做的是有一个列表填充 5%，如果有该选项，则为 10%。我想在没有 vba 的情况下实现这一点(我可以使用 vba 轻松实现这一点，但试图将其全部保存在工作表中)

我的 VLOOKUP使用以下内容填充列:

=CONCATENATE(IF(VLOOKUP(A2,INDIRECT("Test[[Role]:[Used - 10%]]"), 3, FALSE) <> "", "5%", ""), 
IF(VLOOKUP(A2,INDIRECT("Test[[Role]:[Used - 10%]]"), 4, FALSE) <> "", ",10%", ""))

尝试进行数据验证时会出现问题。它接受公式(尝试使用上述方法在数据验证中无济于事)但仅使用 5%,10% 的一个值填充下拉列表而不是将其解释为 csv。

我目前正在使用它来尝试填充折扣下拉菜单

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0, -1)

Answer 1

可以假设您的 Excel 版本可以访问动态函数 FILTER 和 UNIQUE。让我们来看看几件事，这里是 google doc证明这一点的地方。我还包括一个在线excel file *。

无需在设置表 (A:E) 中计算成本。您可以只使用一个字符来标记可用性(在某些版本中，当 =“x” 工作正常时，很难使 FILTER 与 <>""等比较一起工作)。

您可以使用 FILTER、INDEX 和 MATCH 获得一系列可用折扣。请参阅 Col P。您使用 INDEX/MATCH 返回包含折扣的数组的单行(在本例中为 D:E)，然后使用该行过滤具有友好折扣名称的顶行 (D1:E1)并将其作为数组返回。

没有必要按照您的方式连接折扣列表。您可以使用 TEXTJOIN、FILTER、INDEX 和 MATCH。请参见 Col I。您只需将生成折扣名称数组的计算(步骤 2)包装在 TEXTJOIN 中以获取字符串。

验证是通过引用步骤 2 的输出来完成的。我不认为数据验证对话框可以处理完整的公式，所以我将它指向 Cols O:Q。 Col O 包含在验证中，因此您可以在列表顶部获得一个空白位置，但 Google Docs 似乎将其删除。

您可以只计算所选选项的折扣成本。请参阅 Col K。我将原始成本包含在 Col L 中，以便您查看。

您将需要一个 Microsoft 帐户才能查看