从列表中选择多个值并忽略空单元格

Question

我有以下Excel电子表格：

      A           B        C           D          E         F        G       
1              USER1     USER2      MICHAEL    SANDRA    JAMES     CAITLIN
2 Product A    Michael   James       Michael              James
3 Product B    Sandra    Caitlin                Sandra              Caitlin
4 Product C    James     Caitlin                          James     Caitlin
5 Product D    Michael   Sandra      Michael    Sandra

在列中d：g 列出了产品的4个用户。如果用户使用产品，则其名称出现在单元格d2：g5 中。如果他/她不使用该产品，则单元格保持空。

in 列B：C 我想实现Emtpy单元格，并列出了最多2个用户。

您知道可以通过单元格D2：G5 选择2个用户并在列中显示它们的公式/div>

我不知道您可以使用任何快速公式。

您最好使用VBA。您可以在其中写一些紧凑而简洁的东西。如果您的矩阵大小会增长，则VBA绝对是路要走。

但是，如果您坚持使用可用公式在Excel中进行操作，并且矩阵保持相对较小，则是一种方法：

您将需要H和I列以获取"掩码"信息。您可以将这些列隐藏在完成的工作表中。

      A           B        C           D          E         F        G           H           I 
1              USER1     USER2      MICHAEL    SANDRA    JAMES     CAITLIN     MASK1       MASK2
2 Product A    Michael   James       Michael              James                 1010        10
3 Product B    Sandra    Caitlin                Sandra              Caitlin     101         1
4 Product C    James     Caitlin                          James     Caitlin     11          1
5 Product D    Michael   Sandra      Michael    Sandra                          1100        100

在单元格中，您可以创建第一个掩码：

=IF(D2 <> "",1000,0)+IF(E2 <> "",100,0)+IF(F2 <> "",10,0)+IF(G2 <> "",1,0)

这是一个位置掩码，在每个数字列中放置一个1个数据。

在用户1个单元B2中，您可以将嵌套在公式中：

=IF(H2>=1000,D2,IF(H2>=100,E2,IF(H2>=10,F2,IF(H2>=1,G2,""))))

这使用掩码查找数据的第一次出现并将其放在User1列中。

找到第二个用户更加棘手。但是我们要做的就是在第I列中创建第二个掩码。在单元格i2中，您可以使用此公式：

=IF(LEN(TEXT(H2,0))>1,VALUE(RIGHT(TEXT(H2,0),LEN(TEXT(H2,0))-1)),0)

让我为您打破该公式。我们希望将H2中的第一个掩码转换为文本，以便我们可以修剪第一个字符(与User1相对应(，因为，我们不再需要User1数据。使用TEXT(H2,0)

=IF(LEN(TEXT(H2,0))>1,VALUE(RIGHT(TEXT(H2,0),LEN(TEXT(H2,0))-1)),0)
                                  ^^^^^^^^^^

但是要进行正确的修剪以删除最合适的字符，我们需要同一文本字符串减去的长度1. LEN(TEXT(H2,0))-1

=IF(LEN(TEXT(H2,0))>1,VALUE(RIGHT(TEXT(H2,0),LEN(TEXT(H2,0))-1)),0)
                                             ^^^^^^^^^^^^^^^^^

使用正确功能中的那些可以为我们提供删除用户的新面具。

=IF(LEN(TEXT(H2,0))>1,VALUE(RIGHT(TEXT(H2,0),LEN(TEXT(H2,0))-1)),0)
                            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

，但我们不能在文本字符串上进行数学，因此我们必须使用值函数将其转换回一个数字。

=IF(LEN(TEXT(H2,0))>1,VALUE(RIGHT(TEXT(H2,0),LEN(TEXT(H2,0))-1)),0)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

这是我们可以使用User2的新的第二个蒙版。但是，我们还需要测试错误值。如果所有单元格都是空白的，则它会有错误，因为您在正确的函数中不能长度为0。因此，我们需要测试文本的长度是否大于1。 LEN(TEXT(H2,0))>1

=IF(LEN(TEXT(H2,0))>1,VALUE(RIGHT(TEXT(H2,0),LEN(TEXT(H2,0))-1)),0)
    ^^^^^^^^^^^^^^^^^

将所有内容都包含在if语句中，现在您拥有用于用户的新蒙版。

现在，如果通过在单元格I2中使用掩码：

中的掩码，则是一个简单的嵌套。

=IF(I2>=100,E2,IF(I2>=10,F2,IF(I2>=1,G2,"")))

我们能够将1000的测试扔掉，因为我们知道这本来是为user1捕获的。

现在，只需将单元格B2，C2，H2和i2复制到另外三行，您的所有数据都会显示。

您可以隐藏列H，如果您愿意。

因此，总的来说，这并不困难(如果您的矩阵仍然很小(。进行这项工作只需要4个公式。而且它们仅具有适度的复杂性。

，但再次，如果您的矩阵最终会增长的大小，那么在VBA中写下这一点肯定会更好。

希望这有所帮助。：(

没有公式的可能没有vba或类似公式有点乏味(尽管没有这样标记(：

在C和D之间插入足够的额外列，以便能够将D：G的所有d：g复制到C中。
将d：g的内容复制到c中，然后移动d：g的内容(到其他行或其他纸/书籍(。
选择C：F(或同等(中的填充行，主页>编辑> find＆amp;选择，转到特别...，空白(仅( ok 。
右键单击选定的单元格之一...复制d：g回到e2。

选择的两个将是左侧最远的。