MS Excel中的外部联接 [英] outer join in MS Excel

问题描述

您是否知道如何使用外部联接来联接两个表?知道可以在SQL中执行此操作，但是我现在需要Excel.

do you have an idea how to join two tables with the outer join ? Know to do this in SQL, but I need Excel now.

我在一列中列出了所有员工 我有每位员工的任务表.我想创建一个函数，该函数将在表中填充缺少的雇员.

I have a list of all employees in one column I have a table of task for every employee. I would like to create a function, that will populate this table with missing employees.

表1-所有员工列表

Fernando
Hector
Vivian
Ivan

表2-实际任务列表

Fernando, task A, 5 hours
Vivian, task B, 8 hours

我想要实现的结果

Fernando, task A, 5 hours
Vivian, task B, 8 hours
Hector, , 0 hours
Ivan, , 0 hours

非常感谢任何想法.

推荐答案

如果使用ADO，则确实存在Excel中的左联接这样的情况.

There is indeed such a thing as a left join in Excel if you use ADO.

转到VBA编辑器(Alt-F11)，然后向"Microsoft ActiveX数据对象2.8库"添加引用(工具">引用").创建一个新的普通模块(插入">模块")并添加以下代码:

Go to the VBA editor (Alt-F11) and add a reference (Tools > References) to "Microsoft ActiveX Data Objects 2.8 Library". Create a new normal module (Insert > Module) and add this code:

Option Explicit

Sub get_employees()

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

' This is the Excel 97-2003 connection string. It should also work with
' Excel 2007 onwards worksheets as long as they have less than 65536
' rows
'With cn
'    .Provider = "Microsoft.Jet.OLEDB.4.0"
'    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
'        "Extended Properties=Excel 8.0;"
'    .Open
'End With

With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=""Excel 12.0 Macro;IMEX=1;HDR=YES"";"
    .Open
End With

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM [Sheet1$] LEFT JOIN [Sheet2$] ON [Sheet1$].[EMPLOYEE] = " & _
    "[Sheet2$].[EMPLOYEE]", cn

Dim fld As ADODB.Field
Dim i As Integer

With ThisWorkbook.Worksheets("Sheet3")
    .UsedRange.ClearContents
    i = 0
    For Each fld In rs.Fields
        i = i + 1
        .Cells(1, i).Value = fld.Name
    Next fld
    .Cells(2, 1).CopyFromRecordset rs
    .UsedRange.Columns.AutoFit 
End With

rs.Close
cn.Close

End Sub

保存工作簿，然后运行代码，您应该在Sheet3上获得一个左联接列表.您会看到Employee列已重复，但是可以通过适当地修改SELECT子句来解决.您还将拥有空白单元格，而不是没有匹配的0小时

Save the workbook and then run the code and you should get a left-joined list on Sheet3. You'll see that the Employee column is duplicated but you can sort that out by amending the SELECT clause appropriately. You'll also have blank cells rather than 0 hours where there is no match

编辑:我已在代码注释中保留了Excel 97-2003连接字符串的详细信息，但已更改了代码以改为使用Excel 2007及更高版本的连接字符串.我还添加了代码以输出字段名称并在记录集输出后自动调整列

edit: I've left the details of the Excel 97-2003 connection string in the code comments but have changed the code to use the Excel 2007 onwards connection string instead. I've also added code to output the field names and autofit the columns after the recordset has been output

这篇关于MS Excel中的外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！