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
2
Example Using Pivot Table. I think SQL is overkill here since Excel has built in functionality.
使用数据透视表的示例。我认为SQL在这里有点过分,因为Excel内置了功能。
#2
1
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。
#1
2
Example Using Pivot Table. I think SQL is overkill here since Excel has built in functionality.
使用数据透视表的示例。我认为SQL在这里有点过分,因为Excel内置了功能。
#2
1
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。