Excel SQL 쿼리에서 '테이블' 사용 요약-excel-php.cn

이 글은 excel에 대한 관련 지식을 제공합니다. 주로 영역 테이블, 크로스 워크북 테이블 등 SQL 쿼리에서의 테이블 사용을 소개합니다. 모든 분들께 도움이 되기를 바랍니다.

Excel SQL 쿼리에서 '테이블' 사용 요약

관련 학습 권장 사항: excel 튜토리얼

오늘은 SQL 문의 Excel 테이블에 대해 이야기하겠습니다.

1. 영역을 테이블로

엑셀 워크시트와 데이터베이스 데이터 테이블에는 많은 차이점이 있습니다. 가장 중요한 점은 데이터베이스 데이터 테이블은 행과 열로 구성되어 있다고 이해할 수 있지만, 엑셀 워크시트는 2개로 구성되어 있다는 점입니다. 이러한 셀은 고유한 주소 표현 방식, 즉 A1 또는 R1C1을 가지며 A2:H8과 같이 데이터 연결 셀 범위를 형성할 수도 있습니다.

그럼 질문은, Excel 워크시트의 일부만 계산해야 한다면 이를 SQL로 어떻게 표현해야 할까요?

이런 종류의 문제는 매우 흔합니다.

예를 들어 많은 사람들의 엑셀 제목 행은 표의 첫 번째 행이 아닌 두 번째 행에 있습니다...

아래 그림과 같이

Excel SQL 쿼리에서 테이블 사용 요약

이때, 우리는 A2:F 열 영역의 셀을 사용하면 전체 Excel 워크시트 대신 필드 이름을 사용하여 데이터를 처리하기가 더 쉽습니다...

또 다른 예는 한 테이블에 두 개 이상의 "테이블"이 있습니다... 이 문장은 무엇을 의미하나요?

아래 그림을 참조하세요

Excel SQL 쿼리에서 테이블 사용 요약

그림에 표시된 테이블에는 "교사 테이블"과 "학생 테이블"이 모두 있습니다. SQL 참조만 사용하여 A2의 교사 테이블 데이터를 계산하려는 경우: D8 ...

...Excel의 SQL은 실제로 워크시트의 셀 범위를 "테이블"로 사용하는 것을 지원합니다.

위 그림에 표시된 문제의 경우 SQL은 다음과 같이 작성할 수 있습니다.

SELECT 姓名,学科 FROM [数据表$A2:D8]

로그인 후 복사

쿼리 결과는 다음과 같습니다.

Excel SQL 쿼리에서 테이블 사용 요약

첫 번째 경우에는 데이터가 셀 A2에서 시작한다는 것을 알고 있지만 F 열의 어느 셀이 Grid로 끝나는지 알 수 없는 경우 SQL은 다음과 같이 작성할 수 있습니다.

SELECT 姓名,爱好 FROM [学生表$A2:F]

로그인 후 복사

또한 테이블의 전체 D:G 열 데이터를 계산하기 위해 SQL 참조가 필요한 경우 SQL은 다음과 같이 작성할 수 있습니다.

SELECT * FROM [学生表$D:G]

로그인 후 복사

위의 엑셀 워크시트 영역 표현 방법, 즉 워크시트 이름 + 달러 기호 $ + 상대 참조 상태의 셀 주소를 정리하고 마지막으로 대괄호로 묶습니다.

그냥 보라색이에요.

이 섹션에 대한 팁:

[학생 테이블 $A2:F], 이 명령문은 마지막 데이터가 존재하는 열 A2에서 열 F까지의 셀 범위를 참조할 수 있지만 여기에는 제한적인 전제 조건이 있습니다. 자체 연결되지 않은 상태입니다. 소위 셀프 조인은 SQL을 사용하여 자체적으로 연결해야 하는 통합 문서를 의미합니다. 자체 연결 상태에서 A2:F의 최대 표현은 A2:F65536행입니다. 이때 필요한 참조 행이 65536행을 초과하는 경우 전체 테이블 모드를 사용하세요.

2. 통합 문서 간 테이블

잘 알려진 문제는 통합 문서 간 데이터를 처리할 때 Excel 기능이 매우 피곤하다는 것입니다(VLOOKUP 등). 사용량을 계산하려면 먼저 관련 통합 문서를 열어야 합니다.

예, VLOOKUP 함수는 관련 통합 문서를 열 필요가 없으며 여러 통합 문서에서 사용할 수 있습니다. 또한 VLOOKUP 수식을 작성한 후에는 참조하는 통합 문서를 삭제하더라도 계산이 중단되지 않습니다. 수식이 있는 통합 문서에 관련 데이터를 캐시했지만 VLOOKUP 모드는 복잡한 함수 중첩을 지원하지 않기 때문입니다... 손가락을 튕겨보세요. 관심이 있으시면 이에 대해 별도로 이야기하겠습니다.

...에헴, 다시 SQL로~~

...현재 워크북의 테이블을 모두 처리하기 전에 공유한 SQL 문입니다. 처리해야 할 데이터가 다른 워크북에 있는 경우 어떻게 표현해야 할까요? SQL?

예를 들어, "Student Table"에서 "Grade Table"의 모든 데이터를 가져옵니다. 존경합니다.

OLE DB 방법인 경우(이 방법에 대해서는 이 튜토리얼 시리즈의 1장을 참조하세요) SQL 문은 다음과 같습니다

SELECT * FROM [D:\EH小学\学生表.xlsx].[成绩表$]

로그인 후 복사

FROM 뒤에 지정된 테이블 문자열은 두 부분으로 구성됩니다. 지정된 통합 문서의 저장 경로 +접미사가 붙은 전체 통합 문서 이름, 마지막 대괄호는 워크시트 이름이고 두 개의 대괄호는 마침표(.)로 연결됩니다.

VBA+ADO를 통해 SQL 문을 사용하는 경우...

책장 전면에 붙은 경고문: VBA 기초가 부족한 어린이는 다음 내용을 건너뛰시기 바랍니다...

OLE DB 방식에 비해 VBA+ADO 방법은 더 유연해야 합니다. 또한 ADO를 사용하여 지정된 통합 문서에 대한 링크를 직접 생성하고 열 수 있으므로 SQL 문에서 통합 문서의 전체 이름 등을 지정할 필요가 없습니다.

코드 참조는 다음과 같습니다

Sub ADO_SQL()
&#39;适用于除2003版以外的高版本Excel
Dim cnn As Object, rst As Object
Dim strPath As String, strCnn As String, strSQL As String
Dim i As Long
Set cnn = CreateObject("adodb.connection")
strPath = "D:\EH小学\学生表.xlsx" &#39;指定工作簿
strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath
cnn.Open strCnn &#39;创建并打开到指定工作簿的链接
strSQL = "SELECT * FROM [成绩表$]" &#39;strSQL语句，查询成绩表的所有数据
Set rst = cnn.Execute(strSQL) &#39;执行strSQL
Cells.ClearContents
For i = 0 To rst.Fields.Count - 1
Cells(1, i + 1) = rst.Fields(i).Name
Next
Range("a2").CopyFromRecordset rst
cnn.Close
Set cnn = Nothing
End Su

로그인 후 복사

위 코드의 7번째 줄은 연결해야 하는 워크북의 전체 이름을 직접 지정하며, SQL 문에서 특별한 처리가 필요하지 않습니다.

但更多的情况是，ADO创建的链接是一个工作簿，需要获取的数据在另一个或多个工作簿，例如两个工作簿之间的数据查询统计。此时通常使用的代码如下

Sub ADO_SQL2()
&#39;适用于除2003版以外的高版本Excel
Dim cnn As Object, rst As Object
Dim strPath As String, strCnn As String, strSQL As String
Dim i As Long
Set cnn = CreateObject("adodb.connection")
strPath = ThisWorkbook.FullName &#39;代码所在工作簿的完整名称
strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath
cnn.Open strCnn &#39;创建到代码所在工作簿的链接
strSQL = "SELECT * FROM [Excel 12.0;DATABASE=D:\EH小学\学生表.xlsm].[成绩表$]"
Set rst = cnn.Execute(strSQL) &#39;执行SQL
Cells.ClearContents
For i = 0 To rst.Fields.Count - 1
Cells(1, i + 1) = rst.Fields(i).Name
Next
Range("a2").CopyFromRecordset rst
cnn.Close
Set cnn = Nothing
End Sub

로그인 후 복사

代码中第7行创建了当前工作簿的链接，SQL语句中又指定了另外一个工作簿的链接。SQL语句如下

SELECT * FROM [Excel 12.0;DATABASE=D:\EH小学\学生表.xlsx].[成绩表$]

로그인 후 복사

FROM指定表的字符串有两部分组成。第一个中括号中，Excel 12.0是目标工作簿的版本号，第2章时我们讲过，Excel 12.0适用于除了2003以外的所有Excel版本。DATABASE指定的是数据源工作簿的路径和名称。第2个中括号内是工作表名。两个中括号之间使用英文点号相连。

看起来似乎VBA+ADO方法的SQL语句比OLE DB法更复杂？确实如此，不过前者的功能也更强大。比如，它可以通过VBA对象的属性、方法，循环和判断语句等，有条件的筛选工作簿和工作表……相比之下，OLE DB中的SQL语句就是纯手工常量模式了。当然，更重要的是，前者不但可以查数据，还可以增改删数据，后者却只限于查。