mysql - para - Excel Equivalente de la consulta SQL "SELECT A.one, B.two FROM A INNER JOIN B ON A.three=B.three"

mysql for excel tutorial

Question

¿Cómo se hace SQL "select join on" en excel? Digamos que quiero una columna de la tabla A, una columna dos de la tabla B y ambas tienen la columna tres.

Yo lo haría:

SELECT A.one, B.two
FROM A
INNER JOIN B ON
A.three = B.three

Pero en lugar de tablas, tengo algunas pestañas en una hoja de Excel. ¿Cómo hago el equivalente de la consulta anterior en Excel?

Answer 1

Suponiendo que sus hojas son las siguientes:

Sheet1

Sheet2

En la Cell A2 de Sheet3 ingrese la fórmula

=IFERROR(INDEX(Sheet1!$A$2:$A$8,MATCH(SMALL(IF(COUNTIF(Sheet1!$C$2:$C$10,Sheet2!$C$2:$C$10),Sheet2!$C$2:$C$10),ROW(1:1)),Sheet1!$C$2:$C$8,0)),"")

y en la Cell B2 de Sheet3 ingrese la siguiente fórmula

=IFERROR(INDEX(Sheet2!$B$2:$B$8,MATCH(SMALL(IF(COUNTIF(Sheet1!$C$2:$C$10,Sheet2!$C$2:$C$10),Sheet2!$C$2:$C$10),ROW(1:1)),Sheet1!$C$2:$C$8,0)),"")

Ambas fórmulas son fórmulas de matriz así que confírmalas presionando Ctrl + Shift + Enter . Arrastra / copia abajo según sea necesario. Ver imagen para referencia.

-------------------------------------------------- -------------------------------------------------- -------------------

Si también desea mostrar la tercera columna de las dos primeras hojas en Sheet3 (que es ID en mi hoja de muestra), ingrese la siguiente fórmula en Cell A2

=IFERROR(SMALL(IF(COUNTIF(Sheet1!$C$2:$C$10,Sheet2!$C$2:$C$10),Sheet2!$C$2:$C$10),ROW(1:1)),"")

Esta es también una fórmula de matriz. En la Cell B2 ingrese

=IFERROR(INDEX(Sheet1!$A$2:$A$8,MATCH(A2,Sheet1!$C$2:$C$8,0)),"")

Y en la Cell C2 ingresa

=IFERROR(INDEX(Sheet2!$B$2:$B$8,MATCH(A2,Sheet2!$C$2:$C$8,0)),"")

Arrastra / copia abajo según sea necesario. Ver la imagen a continuación.

Obtuve esto de la respuesta de @ ScottCraner aquí .

Hay otra forma de lograr esto sin utilizar fórmulas y VBA. Vea si esto ayuda.

Answer 2

Puede usar VBA y ADO para consultar las hojas de trabajo directamente con SQL. Simplemente agregue el siguiente código a un Sub en un módulo de VBA. El código es principalmente repetitivo a excepción de la línea rs.Open y la línea With Worksheets , que debe modificar para adaptarse a su situación particular.

'' Set up connection
Dim cn As Object
Set cn = CreateObject("ADODB.Connection")

'' Connection string for Excel 2007 onwards .xlsm files
With cn
   .Provider = "Microsoft.ACE.OLEDB.12.0"
   .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=""Excel 12.0 Macro;IMEX=1"";"
    .Open
End With

'' Connection string for Excel 97-2003 .xls files
'' 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;IMEX=1"";"
''    .Open
''End With

'' Create and run the query
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")

'' Join the two worksheets - assumed that these are named "Sheet1"
'' and "Sheet2", the columns are named "one", "two" and "three"
'' and that the results should be output to "Sheet3"
rs.Open "SELECT [Sheet1$].[one], [Sheet2$].[two] " & _
    "FROM [Sheet1$] INNER JOIN [Sheet2$] " & _
    "ON [Sheet1$].[three] = [Sheet2$].[three];", cn

'' Output the field names and the results
Dim fld As Object
Dim i As Integer

'' Change the worksheet to whichever one you want to output to
With Worksheets("Sheet3")
    .UsedRange.ClearContents

    For Each fld In rs.Fields
        i = i + 1
        .Cells(1, i).Value = fld.Name
    Next fld

    .Cells(2, 1).CopyFromRecordset rs
End With

'' Tidy up
rs.Close
cn.Close

El código podría cambiarse para utilizar el enlace anticipado en su lugar al agregar una referencia a "Biblioteca de Microsoft ActiveX Data Objects 2.8" (vaya a Herramientas> Referencias en el editor de VBA para hacer esto). Cambiaría las siguientes líneas que declaran e inicializan varios objetos ADO:

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

Dim rs As ADODB.Recordset

Dim fld As ADODB.Field