Excel SQL クエリでの「テーブル」の使用法の概要

この記事では、excel に関する関連知識を提供します。主に、リージョン テーブル、クロスワークブック テーブルなど、SQL クエリでのテーブルの使用方法を紹介します。皆様のお役に立てれば幸いです。

関連する学習の推奨事項: excel チュートリアル

今日は、SQL ステートメント内の Excel テーブルについて説明します。

1. テーブルへの領域

Excel ワークシートとデータベース データ テーブルには多くの違いがあります。最も重要な点は、データベース データ テーブルは行と列で構成されるものとして理解できるのに対し、データベース データ テーブルは行と列で構成されるものとして理解できることです。 Excel ワークシートは一連のセルで構成されており、これらのセルには A1 または R1C1 という固有のアドレス表現方法があり、A2:H8 のようにデータが接続されたセル範囲を形成することもできます。

それでは、Excel ワークシートの一部のみを計算する必要がある場合、それを SQL でどのように表現すればよいのでしょうか?

この種の問題は非常に一般的です。

たとえば、多くの人は Excel のタイトル行が表の 1 行目ではなく 2 行目にあります...

下図に示すように

現時点では、Excel ワークシート全体ではなく、フィールド名を使用してデータを処理しやすくするために、列 A2:F のセル範囲を計算したいと考えています...

別の例、a テーブルの内側と外側に 2 つ以上の「テーブル」があります... この文は何を意味しますか?

下の図を参照してください

図に示されているテーブルには、「教師テーブル」と「生徒テーブル」の両方があります。 SQL で A2:D8 の教師テーブル データを参照および計算したいだけです...

... Excel の SQL は、実際にはワークシートのセル範囲を「テーブル」として使用することをサポートしています。

上の図に示されている問題の場合、SQL は次のように記述できます:

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

クエリの結果は次のとおりです:

# #最初のケースでは、データがセル A2 で始まることはわかっていますが、列 F のどのセルが終了するかはわかりません。SQL は次のように記述できます。

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

さらに、SQL リファレンスが必要な場合は、テーブルの列 D:G 全体のデータを計算するには、SQL を次のように記述できます。

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

Excel ワークシート領域、つまりワークシート名、ドル記号を表現する上記の方法を要約します。 $、相対参照状態のセル アドレス、そして最後に角括弧で囲まれます。

ただ、姜子。

このセクションのヒント:

[Student table $A2:F]、このステートメントは、最後のデータが含まれる列 A2 から列 F までのセル範囲を参照できると言います。存在しますが、これには非自己接続状態という限定された前提条件が適用されます。いわゆる自己結合とは、SQL を使用してワークブック自体をリンクする必要があることを指します。自己リンク状態では、A2:F の最大表現は A2:F65536 行ですが、この時に必要な参照行が 65536 行を超える場合は、全テーブルモードを使用してください。

2. クロスワークブック テーブル

よく知られている問題は、いくつかのルックアップ参照関数 (VLOOKUP など) を除いて、クロスワークブック データを処理するときに Excel 関数が非常に疲れることです。 )、ほとんどの関数は、計算して使用する前に、関連するワークブックを開く必要があります。

はい、VLOOKUP 関数は関連するブックを開く必要がなく、複数のブック間で使用できます。また、VLOOKUP 数式を作成した後、参照しているブックを削除しても、VLOOKUP 関数は機能しません。計算。これは、数式が配置されているブック内の関連データがキャッシュされているためですが、VLOOKUP モードは関数の複雑な入れ子をサポートしていません... 指を鳴らしてください。ご興味があれば、別の日に別の機会に個別にお話します。 。

...ああ、SQL の話に戻ります~~

...前に共有した SQL ステートメントはすべて、現在のワークブックのテーブルを処理するために使用されます。は他のブックにあります。SQL を表現するにはどうすればよいですか?

たとえば、「生徒テーブル」の「成績テーブル」のデータをすべて取得します。感心させないでください。

これが OLE DB メソッドの場合 (このメソッドについては、このシリーズのチュートリアルの第 1 章を参照してください)、SQL ステートメントは次のとおりです。

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

FROM の後に指定されたテーブル文字列は、2 つの文字列で構成されます。最初の角括弧の内側は、指定されたワークブックのストレージ パスにサフィックスを付けた完全なワークブック名​​です。最後の角括弧の内側はワークシート名で、2 つの角括弧はピリオド (.) で接続されています。

VBA ADO を通じて SQL ステートメントを使用している場合...

警告の前にある本棚をノックしてください: VBA の基礎が不十分な子供用の靴、次の内容はスキップしてください...

OLE DB メソッドと比較すると、VBA ADO メソッドは非常に柔軟です。ADO を使用して、指定されたブックへのリンクを直接作成して開くことができるため、SQL ステートメントでブックの完全な名前を指定する必要はありません。 、など。

コード リファレンスは次のとおりです。

Sub ADO_SQL()
'适用于除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" '指定工作簿
strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath
cnn.Open strCnn '创建并打开到指定工作簿的链接
strSQL = "SELECT * FROM [成绩表$]" 'strSQL语句，查询成绩表的所有数据
Set rst = cnn.Execute(strSQL) '执行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()
'适用于除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 '代码所在工作簿的完整名称
strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath
cnn.Open strCnn '创建到代码所在工作簿的链接
strSQL = "SELECT * FROM [Excel 12.0;DATABASE=D:\EH小学\学生表.xlsm].[成绩表$]"
Set rst = cnn.Execute(strSQL) '执行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语句就是纯手工常量模式了。当然，更重要的是，前者不但可以查数据，还可以增改删数据，后者却只限于查。

相关学习推荐：excel教程

以上がExcel SQL クエリでの「テーブル」の使用法の概要の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。