Excel：将两个不同的复杂公式放在一起（独特的不同列表和拆分）

Question

大家好，你们这些了不起的人，

我想做两件事，

• 从一长串重复值中填充一个独特的列表，
• 提取用连字符分隔的文本的组成部分，

我在不同的地方找到了每个问题的解决方案。

这里有独特的列表：http://www.get-digital-help.com/2009/03/30/how-to-extract-a -unique-list-and-the-duplicates-in-excel-from-one-column/

公式为

`{=INDEX(A2:A65000,MATCH(0,COUNTIF($B$1:B1,A2:A65000),0))}`  

其中 B 列是填充唯一列表的位置

---

，并且从此处提取（拆分文本）< /strong>: http://spreadsheetpage.com/index.php/tip/the_versatile_split_function/< /a>

Function ExtractElement(str, n, sepChar)
'   Returns the nth element from a string,  
'   using a specified separator character  
    Dim x As Variant  
    x = Split(str, sepChar)  
    If n > 0 And n - 1 <= UBound(x) Then  
       ExtractElement = x(n - 1)  
    Else  
        ExtractElement = ""  
    End If  
End Function  

下面的公式演示了如何在公式中使用 ExtractElement 函数。

=ExtractElement("546-339-909-944",3,"-")  

此公式返回 909，即字符串中的第三个元素（使用“-”作为分隔符）。

---

这些很棒并且解决了我想做的很多事情。但我也尝试将这两个功能一起完成到另一个专栏。

我有一个列，其中包含以下值：

香蕉 - 黄色 - 水果
太阳 - 黄色 - 星星
血 - 红色 - 液体
出口 - 红色 - 标牌

我试图得到结果

黄色
红色

我希望使用公式来完成这一切，并且不想使用辅助列。我不介意 VBA（如您所见，这里的第二个链接是 vba）。

任何帮助都会受到赞赏。谢谢一百万！

斯里拉姆

Answer 1

对于您所展示的具体情况，我修改了您的公式以使用公式而不是使用 VBA 代码来提取颜色。

=INDEX(RIGHT(LEFT(SUBSTITUTE(A2:A65000,"-","|",2),FIND("|",SUBSTITUTE(A2:A65000,"-","|",2))-1),LEN(LEFT(SUBSTITUTE(A2:A65000,"-","|",2),FIND("|",SUBSTITUTE(A2:A65000,"-","|",2))-1))-FIND("-",LEFT(SUBSTITUTE(A2:A65000,"-","|",2),FIND("|",SUBSTITUTE(A2:A65000,"-","|",2))-1))),MATCH(0,COUNTIF($B$1:B1,RIGHT(LEFT(SUBSTITUTE(A2:A65000,"-","|",2),FIND("|",SUBSTITUTE(A2:A65000,"-","|",2))-1),LEN(LEFT(SUBSTITUTE(A2:A65000,"-","|",2),FIND("|",SUBSTITUTE(A2:A65000,"-","|",2))-1))-FIND("-",LEFT(SUBSTITUTE(A2:A65000,"-","|",2),FIND("|",SUBSTITUTE(A2:A65000,"-","|",2))-1)))),0))

就像你的一样，这是一个数组公式；所以按 Ctrl-Enter。

然后您可以复制并删除从 B2 向下粘贴此公式，当您得到 #VALUES 时，不再有唯一值。此外，如果您修改 A 列中的值，并且唯一值增加，则可能需要将公式复制到更多单元格。

Answer 2

使用 ADO 的示例。

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim i As Integer

''http://support.microsoft.com/kb/246335

strFile = ActiveWorkbook.FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

''The connection string is set up for 
''headers, so you can use column names
''You can substitute a named range 
''for [Sheet8$]
strSQL = "SELECT DISTINCT " _
& "Mid(ColName,Instr(ColName,""-"")+1," _ 
& "InStrRev(ColName,""-"")-Instr(ColName,""-"")-2) " _
& "FROM [Sheet8$]"

rs.Open strSQL, cn, 3, 3

''Copies the results to a worksheet
Worksheets("Sheet7").Cells(2, 1).CopyFromRecordset rs

Answer 3

您的问题中有一些有用的链接 - 谢谢。

我使用您的示例数据执行了以下操作
香蕉 - 黄色 - 水果
太阳-黄色-星星
血 - 红色 - 液体
出口 - 红色 - 标牌

第 1 步
将其复制到记事本中并在 Excel 中打开它并运行向导以提取分隔符“”和“-”。这将数据作为文本放置在 3 个相邻列中，然后我将其命名为 A、B 和 C 列中的 list1、list2 和 list3（分别位于 A2:A5、B2:B5、C2:C5 范围内）。最好在工作簿中使用单独的工作表来执行此操作。我把第一排保持干净。

第 2 步
使用从单元格 D2 开始的以下公式将步骤 1 中的列表合并到一列中（在我的例子中现在为 D 列）：

{=IFERROR(INDEX(List1,ROWS($D$1:D1)),IFERROR(INDEX(List2,ROWS($D$1:D1)-ROWS(List1)),IFERROR(INDEX(List3,ROWS($D$1:D1)-ROWS(List1)-ROWS(List2)),"")))}

这是一个数组公式，应使用 CTRL + SHIFT + ENTER 输入，然后向下复制。
我将这个范围称为 ListSource (d2:d13)。我再次没有使用第一行。

第3步
为了仅提取 E 列中从单元格 E2 开始的重复项，此公式发挥了作用

{=IFERROR(INDEX(ListSource,MATCH(0,COUNTIF($E$1:E1,ListSource)+IF(COUNTIF(ListSource,ListSource)>1,0,1),0)),"")}

，它又是一个数组公式，应使用 CTRL + SHIFT + ENTER 输入并向下复制到该列中的其他单元格中。
同样，尽管您可以添加一些标题，但第一行中没有任何内容。

这产生了所需的输出
黄色的
红色

您可能认为在步骤 1 中使用导入向导严格来说并不是一种编程解决方案，因此可以自动执行此步骤。

希望这有帮助。