在vba-excel中的Sql查询中需要帮助

I have data in Parent sheet in excel and I need to populate the Child Sheet.

我在excel的父表中有数据,我需要填充子表。

 Parent Sheet P

Name   Gender   Grades   Frequency 
John    Male       A         3
John    Male       B         1
John    Male       C         2
Jay     Male       B         6
Sarah   Female     B         5
Sarah   Female     C         1
Maria   Female     A         4
Maria   Female     D         2

There is a clear possibility that all students won't have all types of grades. For E.g Maria has two D grades which no other student has.

很明显,所有学生都不会拥有所有类型的成绩。对于E.g,玛丽亚有两个D级,其他学生没有。

Child sheet (Male) M
Name    Grade A   Grade B  Grade C  Grade D 
John       3         1         2       0
Jay        0         6         0       0

Child sheet (Female) F 
Name    Grade A    Grade B  Grade C  Grade D 
Sarah      0        5         1       0
Maria      4        0         0       2

This is how I want. I have already made the sheets with the headers and unique names and Gender,so just need to fill the grades frequency data. I want to use SQL to do it. I have two things to write.

这就是我想要的。我已经制作了带有标题和唯一名称以及性别的表格,因此只需要填写成绩频率数据。我想用SQL来做。我有两件事要写。

'For Male Sheet
rs.open "SELECT [P$].Frequency FROM [P$] WHERE Name like (SELECT [M$].NAME FROM [M$])

M.Cells(3,1).CopyFromRecordset rs

Will this do the job? Also Will the recordset return Grade D value as 0 in case of John or Grad A, Grade C, Grade D value 0 in case of Jay? Please advice .Thanks.

这会起作用吗?如果是约翰或格拉德A,那么记录集会将D级值返回0,对于杰伊,C级,D级值是0吗?请指教。谢谢。

2 个解决方案

#1

Example Using Pivot Table. I think SQL is overkill here since Excel has built in functionality.

使用数据透视表的示例。我认为SQL在这里有点过分,因为Excel内置了功能。

#2

Consider using a relational database as opposed to a flatfile spreadsheet to query your potentially 1 million + entries. Excel does not naturally have an SQL query feature unless called on using VBA or an Add-In.

考虑使用关系数据库而不是flatfile电子表格来查询可能的100万+条目。除非使用VBA或加载项调用,否则Excel自然不具有SQL查询功能。

If using a PC, with MS Access (Excel's Office sibling) you could easily run a crosstab query which essentially aggregates and pivots your recordset:

如果使用PC,使用MS Access(Excel的Office同级),您可以轻松运行交叉表查询,该查询基本上聚合并转动您的记录集:

TRANSFORM Sum(ParentSheet.Frequency) AS SumOfFrequency
SELECT ParentSheet.[Name]
FROM ParentSheet
GROUP BY ParentSheet.[Name]
PIVOT 'Grade ' & ParentSheet.Grades;

And add a where clause to split by genders:

并添加一个where子句按性别分割:

MALE

TRANSFORM Sum(ParentSheet.Frequency) AS SumOfFrequency
SELECT ParentSheet.[Name]
FROM ParentSheet
WHERE ParentSheet.Gender = 'Male'
GROUP BY ParentSheet.[Name]
PIVOT 'Grade ' & ParentSheet.Grades;

FEMALE

TRANSFORM Sum(ParentSheet.Frequency) AS SumOfFrequency
SELECT ParentSheet.[Name]
FROM ParentSheet
WHERE ParentSheet.Gender = 'Female'
GROUP BY ParentSheet.[Name]
PIVOT 'Grade ' & ParentSheet.Grades;

As seen above, simply import your Parent worksheet into a database table called ParentSheet. You can even export the gender-specific queries into your child sheets as csv, txt, xml, or back into xlsx.

如上所示,只需将父工作表导入名为ParentSheet的数据库表即可。您甚至可以将特定于性别的查询导出到子表单中,如csv,txt,xml或返回到xlsx。