Résumer l'utilisation de 'table' dans les requêtes Excel SQL

Cet article vous apporte des connaissances pertinentes sur excel Il présente principalement l'utilisation des tables dans les requêtes SQL, y compris les tables de zones, les tables inter-classeurs, etc.

Recommandations d'apprentissage associées : Tutoriel Excel

Aujourd'hui, nous allons parler de la table Excel dans les instructions SQL.

1. Zone dans le tableau

Il existe de nombreuses différences entre la feuille de calcul Excel et le tableau de données de base de données. Le point le plus important est que le tableau de données de base de données peut être compris comme étant composé de lignes et de colonnes, tandis que la feuille de calcul Excel est composée de lignes et de colonnes. d'une cellule, et ces cellules ont une méthode d'expression d'adresse unique, c'est-à-dire A1 ou R1C1. Elles peuvent également former une plage de cellules connectées aux données, telle que A2:H8.

Alors la question est : si nous n'avons besoin de calculer qu'une partie d'une feuille de calcul Excel, comment devons-nous l'exprimer en SQL ?

Ce genre de problème est très courant.

Par exemple, la ligne de titre Excel de nombreuses personnes ne se trouve pas dans la première ligne du tableau, mais dans la 2ème ligne...

Comme le montre l'image ci-dessous

À ce stade, nous voulons calculer le cellules dans la colonne A2: zone F, afin qu'il nous soit plus facile d'utiliser des noms de champs pour traiter les données au lieu de la feuille de calcul Excel entière...

Autre exemple, il y a deux "tableaux" ou plus dans un seul tableau... Que signifie cette phrase ?

Voir l'image ci-dessous

Dans le tableau affiché dans l'image, il y a à la fois une "table enseignant" et une "table étudiant" si nous voulons uniquement que la référence SQL calcule les données de la table enseignant de A2 : D8 ...

...SQL dans Excel prend en charge l'utilisation de la plage de cellules de la feuille de calcul comme "tableau".

Pour le problème présenté dans l'image ci-dessus, le SQL peut être écrit comme :

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

Les résultats de la requête sont les suivants :

Dans le premier cas, nous savons que les données commencent dans la cellule A2, mais nous Je ne sais pas quelle cellule de la colonne F termine la grille, SQL peut être écrit comme :

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

De plus, si nous avons besoin d'une référence SQL pour calculer les données de toute la colonne D:G du tableau, SQL peut être écrit comme :

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

Résumez les méthodes d'expression ci-dessus de la zone de la feuille de calcul Excel, c'est-à-dire le nom de la feuille de calcul + le signe dollar $ + l'adresse de la cellule dans l'état de référence relatif, et enfin entre crochets.

Juste du violet.

Conseils pour cette section :

[Tableau d'étudiant $A2:F], nous disons que cette instruction peut faire référence à la plage de cellules de la colonne A2 à la colonne F où existent les dernières données, mais cela a une condition préalable restrictive, à savoir est un état non auto-connecté. Ce que l'on appelle l'auto-jointure fait référence au classeur que SQL doit utiliser pour se lier. Dans l'état d'auto-liaison, l'expression maximale de A2:F est A2:F65536 lignes ; si les lignes de référence requises à ce moment dépassent 65 536 lignes, veuillez utiliser le mode table entière.

2. Tableaux inter-classeurs

Un problème bien connu est que les fonctions Excel sont très fatiguées lors du traitement des données inter-classeurs, à l'exception de quelques fonctions de référence de recherche (telles que RECHERCHEV, etc.), la plupart des fonctions sont disponibles. Vous devez ouvrir le classeur concerné avant de pouvoir calculer l'utilisation.

Oui, la fonction RECHERCHEV n'a pas besoin d'ouvrir le classeur concerné et peut être utilisée dans plusieurs classeurs. De plus, une fois la formule RECHERCHEV écrite, même si vous supprimez le classeur auquel elle fait référence, cela ne l'empêchera pas de calculer cela. est Parce qu'il a mis en cache les données pertinentes dans le classeur où se trouve la formule, mais le mode RECHERCHEV ne prend pas en charge l'imbrication complexe de fonctions... Claquez des doigts, si vous êtes intéressé, nous en parlerons séparément un autre jour.

...Ahem, revenons à SQL~~

...Les instructions SQL que nous avons partagées auparavant sont toutes utilisées pour traiter les tables du classeur actuel. Si les données que nous devons traiter se trouvent dans d'autres classeurs, comment devraient-elles le faire. on exprime le SQL ?

Par exemple, récupérez toutes les données de la « Tableau des notes » dans la « Tableau des étudiants. Je vous admire.

S'il s'agit de la méthode OLE DB (reportez-vous au chapitre 1 de cette série de didacticiels pour cette méthode), l'instruction SQL est la suivante

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

La chaîne de table spécifiée après FROM se compose de deux parties. chemin de stockage du classeur spécifié. +Le nom complet du classeur avec suffixe, le dernier crochet est le nom de la feuille de calcul et les deux crochets sont reliés par un point (.).

Si vous utilisez des instructions SQL via VBA+ADO...

Un avertissement sur le devant de la bibliothèque : les enfants ayant de mauvaises bases VBA, veuillez ignorer le contenu suivant...

Par rapport à la méthode OLE DB, la La méthode VBA+ADO doit être plus flexible. De plus, elle peut utiliser ADO pour créer et ouvrir directement un lien vers le classeur spécifié, de sorte que l'instruction SQL n'a pas besoin de spécifier le nom complet du classeur, etc.

La référence du code est la suivante

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

La 7ème ligne du code ci-dessus spécifie directement le nom complet du classeur qui doit être connecté, et il n'y a pas besoin de traitement spécial dans l'instruction 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教程

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!