我想使用来自两个表的数据创建一个下拉菜单。
由于这是不可能的，我正在考虑将这两个表合并在一起，然后使用合并后的表来创建下拉菜单。
这是我的两张桌子。
表名称：ProductList
| ITEM DESCRIPTION | ITEM # | UNIT PRICE |
|------------------ |---------- |------------ |
| Item 1 | 1221 | £12 |
| Item 2 | 2314 | £12 |
| | | |
| Item 3 | 32132132 | £12 |
| | | |
表名称：ProductList2
| ITEM DESCRIPTION | ITEM # | UNIT PRICE | COVERAGE | SIZE |
|------------------ |-------- |------------ |---------- |------ |
| Item 4 | 12432 | £12 | 534 | |
| Item 5 | 43554 | £12 | 34 | 2 |
| | | | | |
| Item 6 | 5454 | £12 | 34 | 2 |
| | | | | |
因此，我希望合并上述两个表，但不合并任何重复项。此外，我想忽略表中的任何空白行……
理想情况下，我不想使用 VB - 我想使用纯的Excel 公式…
这是我目前拥有的：
=IFERROR(INDEX(ProductList[ITEM DESCRIPTION],ROWS(K$3:$K3)),IFERROR(INDEX(ProductList2[ITEM DESCRIPTION],ROWS(K$3:$K3)-ROWS(ProductList[ITEM DESCRIPTION])),"”))
这可行但不会忽略空行......
这是我第一次正确使用 EXCEL，因此我将非常感激任何答案 - 并且更感激任何解释。
PS. 我使用的是 Mac Os 版本的 Excel（但也可以使用 Windows 版本）
答案1
这将在 Excel 2010 或更高版本中运行，但恐怕我不能说它是否也适用于 Mac。
还有更多通用解决方案可用，它们适用于任意数量的表（而不仅仅是两个表）中的数据。但是，它们必然相当复杂。而且，由于您实际上只有两个表，因此最好为此提出一种替代（尽管灵活性较差）解决方案。
=IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))+SUMPRODUCT(0+(LEN(ProductList2[ITEM DESCRIPTION])>0)),"",IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0)),INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:1)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))),COLUMNS($A:A)),INDEX(ProductList,AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1)),COLUMNS($A:A))))
根据需要向下和向右复制。
这可以简化，但同样，所涉及的技术有点复杂。我无意居高临下；我只是认为您可能会找到一个稍长但可能更容易理解且更有用的解决方案。
至于解释：
那个部分：
SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))
计算商品描述列产品列表表非空白。COUNTIF是为此目的的通常选择，尽管使用该函数的两种可能设置要求您范围内的空白是“真正的”空白（因此不是空字符串“”，例如由于这些单元格中的公式而产生的），或者条目具有一致的数据类型。由于我无法通过检查您的样本来确定这两个事实，因此使用空白测试SUMPRODUCT（无论上述答案是什么，它肯定会起作用）是更严格的选择。
相似地：
SUMPRODUCT(0+(LEN(ProductList2[ITEM DESCRIPTION])>0))
计算商品描述列产品列表2表格非空白。
因此，初始条款：
IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))+SUMPRODUCT(0+(LEN(ProductList2[ITEM DESCRIPTION])>0)),""
意味着，在行中超出非空白条目总数的商品描述两个表的列，将返回空白。
更好的方法是将这两个构造输入到工作表中的几个单元格中，并在公式中引用它们。这样，它们只需要计算一次，而不是通过公式的每次迭代进行计算。例如，如果您输入 G1：
=SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))
在上半年：
=SUMPRODUCT(0+(LEN(ProductList2[ITEM DESCRIPTION])>0))
那么主要公式就变成：
=IF(ROWS($1:1)>$G$1+$H$1,"",IF(ROWS($1:1)>$G$1,INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:1)-$G$1),COLUMNS($A:A)),INDEX(ProductList,AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1)),COLUMNS($A:A))))
并且效率会更高。
请注意，这种涉及引用计数的设置比可能耗费大量资源的设置更可取IFERROR，您可以在此处找到原因的解释：
无论如何，如果这个初始子句是FALSE，那么我们继续第二个条件语句，即：
IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))
这与上面的方法类似，告诉我们，在行中，非空白条目总数超出商品描述列产品列表表，我们就会知道要集中精力于另一张表，即产品列表2。否则，如果上述情况属实FALSE，我们将注意力转向第一个表格，产品列表。
我将看两个例子，因为尽管每个例子的构造几乎相同，但其中一个例子存在细微但必要的差异。
使用您发布的数据并以第一行的公式为例，即：
IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0)),INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:1)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))),COLUMNS($A:A)),INDEX(ProductList,AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1)),COLUMNS($A:A)))
我们看到初始子句，即：
IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))
即：
IF(1>3
显然FALSE，因此我们继续讨论由值_if_false本声明中的子句IF，即：
INDEX(ProductList,AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1)),COLUMNS($A:A))
AGGREGATELARGE（仅限 2010 或更高版本）为我们提供了一个不错的、非 CSE 的替代构造，以替代带有或 的标准 CSE 设置SMALL。
那个部分：
ROW(ProductList)-MIN(ROW(ProductList))+1
是用于生成从 1 到表中行数的整数数组的标准构造产品列表。它是有效的，因为，例如假设这个表占据了第 4 行到第 7 行（即总共 4 行），我们将有：
{4;5;6;7}-MIN({4;5;6;7})+1
IE：
{4;5;6;7}-4+1
IE：
{4;5;6;7}-3
IE：
{1;2;3;4}
按要求。
那个部分：
LEN(ProductList[ITEM DESCRIPTION])>0
检查该列中的每个条目是否具有非零长度（即非空白），并解析为：
LEN({"Item1";"Item2";"";"Item3"})>0
IE：
{5;5;0;5}>0
IE：
{TRUE;TRUE;FALSE;TRUE}
然后我们计算分数：
(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0)
即：
({1;2;3;4})/({TRUE;TRUE;FALSE;TRUE})
获得：
{1;2;#DIV/0!;4}
因为，当通过任何合适的数学运算（这里是除法）强制执行时，布尔值TRUE/FALSE都会被强制转换为其数值等价物（TRUE=1，FALSE=0）。
实际上，任何不满足长度大于零条件的条目（即空白单元格）现在都被视为错误。
并且，通过将AGGREGATE的第二个参数设置为 6，我们指示该函数忽略范围内的任何错误值，因此我们获得了一种从考虑中消除任何空白单元格的方法。
像这样：
AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1))
即：
AGGREGATE(15,6,{1;2;#DIV/0!;4},1)
返回 1 （该数组中的最小值，15 是与SMALLfor等价的参数AGGREGATE）。
我们现在将该值作为行号参数为INDEX，使得：
INDEX(ProductList,AGGREGATE(15,6,{1;2;#DIV/0!;4},1),COLUMNS($A:A)
即：
INDEX(ProductList,1,1)
正如所期望的，返回“Item1”。
注意使用：
COLUMNS($A:A)
为了列号INDEX，当将此公式复制到右边时，将依次变为：
COLUMNS($A:B)
即2，
COLUMNS($A:C)
即 3，
等等，这样我们就可以从表中的后续列返回值，而不必在每次迭代中手动插入所引用的列。
再举一个例子，将公式向下复制五行后的公式，即：
IF(ROWS($1:5)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0)),INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:5)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))),COLUMNS($A:A)),INDEX(ProductList,AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:5)),COLUMNS($A:A)))
将，因为这次初始条款决定：
IF(5>3
也就是说TRUE，我们考虑另一种构造，即：
INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:5)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))),COLUMNS($A:A))
这实际上是相同的，不过我们在这里需要小心一点钾参数被传递给AGGREGATE。如果我们像以前一样使用，只需：
AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:5))
我们会有：
AGGREGATE(15,6,({1;2;3;4;5})/({TRUE;TRUE;FALSE;TRUE;FALSE}),ROWS($1:5))
即：
AGGREGATE(15,6,{1;2;#DIV/0!;4;#DIV/0!},ROWS($1:5))
但是，因为：
ROWS($1:5)
为 5，并且由于数组：
{1;2;#DIV/0!;4;#DIV/0!}
不包含第五个最小值，则上述将返回错误。
通过首先从这个值 5 中减去另一个表中非空白条目的数量，我们确保获得正确的参数。因此，我们使用：
ROWS($1:5)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))
即：
5-3
即 2.
因此我们现在有：
AGGREGATE(15,6,{1;2;#DIV/0!;4;#DIV/0!},2)
即 2。
最后，我们的构造：
INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:5)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))),COLUMNS($A:A))
即：
INDEX(ProductList2,2,1)
按要求返回“Item5”。
最后一点是关于优化计算速度。正如将两个计数公式输入到实际工作表中的单元格中更有效一样，使以下部分更有效：
ROW(ProductList)-MIN(ROW(ProductList))+1
和：
ROW(ProductList2)-MIN(ROW(ProductList2))+1
每个仅计算一次。
虽然由于技术原因，这些与上述两种SUMPRODUCT构造不同，无法输入到实际的工作表单元格中，但它们可以存储在名称管理器中（公式标签）。
因此，如果你将它们定义为例如阿里1和阿里2， 和阿里1存在：
=ROW(ProductList)-MIN(ROW(ProductList))+1
和阿里2存在：
=ROW(ProductList2)-MIN(ROW(ProductList2))+1
主要公式将变得更易读、更高效：
=IF(ROWS($1:1)>$G$1+$H$1,"",IF(ROWS($1:1)>$G$1,INDEX(ProductList2,AGGREGATE(15,6,Arry2/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:1)-$G$1),COLUMNS($A:A)),INDEX(ProductList,AGGREGATE(15,6,Arry1/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1)),COLUMNS($A:A))))
问候