小学老师求助，如何从不同工作表(格式相同)的若干单元格中提取值，汇总表显示不同行

heima1106

请教各位高手，我有一个班级50个小学生的 《学籍基本信息表》一页excle电子表，格式一模一样，每个学生都自己填好了自己的信息。我现在收集起来50个，要求统一转为教育系统的“可上传文件”，就是所谓的 《学籍基本信息表》信息转为 每个学生一行、按顺序整齐排列的字段信息。请问有什么函数没？如果不行，VBA的话，可否帮忙编写几个字段取值的示例？能给个指导、示范一下，我一定认真学习、积极宣传推广我们论坛！！

附件，以学生 肖方成（化名和信息） 为例，行数据字段的表中，A2-G2红色字体，系统已有。现在问题就是，怎样提取《肖方成 (学籍表格式）》中，不同单元格的值。以及再有第2个学生的学籍表格式，怎样提取同样位置、不同单元格的值？

lsc900707

典型的多簿单表信息合并。

heima1106

lsc900707 发表于 2019-9-20 20:16
典型的多簿单表信息合并。

感谢版主亲自指导啊。
不好意思啊，我只会excle常用函数、vba基本是空白，小白一个  您可否给示范编一段vba，填入几个字段范例，我一定学习演练

moonlit123

当前文件夹.zip (48.93 KB, 下载次数: 6)

2019-9-20 21:41 上传

点击文件名下载附件

有些数据对应规则还没搞懂
（比如P列留守儿童 和S列农村留守儿童）
（Q列城务人员随迁子女和T列随迁子女）
没搞懂对应一表里的哪里。
后面反正找地方自己找吧。
我做了个大概的结构。


另外：《肖方成 (行数据格式).xls》原文件有vba加密，我就没用这个表做。 自己新建了个工作簿1.xlsm做的。
《肖方成 (学籍表格式).xls》有“受保护的视图”，貌似不能直接vba读取数据。我是重新打开后另存为新文件才能用vba读取数据的，不明白相关安全设置怎么搞。

绿谷龙芽

=OFFSET(Sheet1!$A$1,1,MATCH(SUBSTITUTE(直接打印为1页A4纸张!B5,"★","")&"*",Sheet1!$A$1:$BX$1,0)-1)

自己再改改试试，仅提供一个思路

lsc900707

heima1106 发表于 2019-9-20 20:53
感谢版主亲自指导啊。
不好意思啊，我只会excle常用函数、vba基本是空 ...

举例太少，多几个实例吧。
其实我感兴趣的是你为什么要亮明你小学老师的身份！

用心修炼

'你的系统表中“是否留守儿童”和“是否农村留守儿童”不知道是不是同一个字段，或者说这两个字段的内容是不是同时取值学生信息表中的“是否留守儿童”对应的信息
'在学生信息表中，找不到“班号”这一栏，代码中暂把这个设为空
由于你的表格设置了权限而且VBE窗口也设置了密码，无法在你的表格中测试，不过我复制了你的数据在我的电脑上测试没有问题。把你的50个学生的工作簿放在同一个文件夹下，即可运行代码。希望能帮助到你
Option Explicit



Sub mytest()
Const name_1 As String = "姓名": Const name_2 As String = "性别": Const name_3 As String = "身份证件类型": Const name_4 As String = "身份证件号"
Const name_5 As String = "民族": Const name_6 As String = "国籍/地区": Const name_7 As String = "港澳台侨外": Const name_8 As String = "户口性质"
Const name_9 As String = "户口所在地行政区划": Const name_10 As String = "现住址": Const name_11 As String = "是否独生子女": Const name_12 As String = "是否留守儿童"
Const name_13 As String = "是否进城务工人员随迁子女": Const name_14 As String = "是否孤儿": Const name_15 As String = "是否农村留守儿童": Const name_16 As String = "是否随迁子女"
Const name_17 As String = "籍贯": Const name_18 As String = "健康状况": Const name_19 As String = "政治面貌": Const name_20 As String = "校区号"
Const name_21 As String = "班号": Const name_22 As String = "入学年月": Const name_23 As String = "入学方式": Const name_24 As String = "就读方式"
Const name_25 As String = "通信地址": Const name_26 As String = "家庭地址": Const name_27 As String = "联系电话": Const name_28 As String = "邮政编码"
Const name_29 As String = "是否受过学前教育": Const name_30 As String = "是否残疾人": Const name_31 As String = "是否需要申请资助": Const name_32 As String = "是否享受一补"
Const name_33 As String = "是否烈士或优抚子女": Const name_34 As String = "上下学距离": Const name_35 As String = "上下学交通方式": Const name_36 As String = "是否乘坐校车"
Const name_37 As String = "曾用名": Const name_38 As String = "身份证件有效期": Const name_39 As String = "血型": Const name_40 As String = "特长"
Const name_41 As String = "学籍辅号": Const name_42 As String = "班内学号": Const name_43 As String = "学生来源": Const name_44 As String = "电子信箱"
Const name_45 As String = "主页地址": Const name_46 As String = "是否由政府购买学位": Const name_47 As String = "成员1姓名": Const name_48 As String = "成员1关系"
Const name_49 As String = "成员1关系说明": Const name_50 As String = "成员1现住址": Const name_51 As String = "成员1户口所在地行政区划": Const name_52 As String = "成员1联系电话"
Const name_53 As String = "成员1是否监护人": Const name_54 As String = "成员1身份证件类型": Const name_55 As String = "成员1身份证件号": Const name_56 As String = "成员1民族"
Const name_57 As String = "成员1工作单位": Const name_58 As String = "成员1职务": Const name_59 As String = "成员2姓名": Const name_60 As String = "成员2关系"
Const name_61 As String = "成员2关系说明": Const name_62 As String = "成员2现住址": Const name_63 As String = "成员2户口所在地行政区划": Const name_64 As String = "成员2联系电话"
Const name_65 As String = "成员2是否监护人": Const name_66 As String = "成员2身份证件号": Const name_67 As String = "成员2民族": Const name_68 As String = "成员2工作单位"
Const name_69 As String = "成员2职务"
Dim arr, brr(1 To 100, 1 To 74), d_title As Object
Dim myfloder As FileDialog, floder_path$, myfile$, wb As Workbook
Dim i&, j&
Set d_title = CreateObject("scripting.dictionary")
arr = Range("A1:BV1")

For i = 1 To UBound(arr, 2)
    d_title(arr(1, i)) = i
Next i
Set myfloder = Application.FileDialog(msoFileDialogFolderPicker)
With myfloder
     .Title = "请选择学生信息工作簿所在的文件夹！"
     .AllowMultiSelect = False
     If .Show Then
        floder_path = .SelectedItems(1)
     Else
        MsgBox "未选择文件夹，退出程序": Exit Sub
     End If
End With
myfile = Dir(floder_path & "\*xls")  'xls此处后缀名根据你的版本修改
Application.ScreenUpdating = False
Do While myfile <> ""
   j = j + 1
   Set wb = Workbooks.Open(floder_path & "\" & myfile)
   With wb.Worksheets(1)
        brr(j, d_title(name_1)) = .Range("C5").Value: brr(j, d_title(name_2)) = .Range("C6").Value: brr(j, d_title(name_3)) = .Range("F5").Value
        brr(j, d_title(name_4)) = "'" & .Range("F6").Value: brr(j, d_title(name_5)) = .Range("C10").Value: brr(j, d_title(name_6)) = .Range("C11").Value
        brr(j, d_title(name_7)) = .Range("F5").Value: brr(j, d_title(name_8)) = .Range("F14").Value: brr(j, d_title(name_9)) = "'" & .Range("F13").Value
        brr(j, d_title(name_10)) = .Range("C22").Value: brr(j, d_title(name_11)) = .Range("C27").Value: brr(j, d_title(name_12)) = .Range("C29").Value
        brr(j, d_title(name_13)) = .Range("C30").Value: brr(j, d_title(name_14)) = .Range("C31").Value: brr(j, d_title(name_15)) = .Range("C29").Value
        brr(j, d_title(name_16)) = .Range("C30").Value: brr(j, d_title(name_17)) = .Range("C9").Value: brr(j, d_title(name_18)) = .Range("F9").Value
        brr(j, d_title(name_19)) = .Range("F8").Value: brr(j, d_title(name_20)) = "": brr(j, d_title(name_21)) = "": brr(j, d_title(name_22)) = "'" & .Range("F17").Value
        brr(j, d_title(name_23)) = .Range("F18").Value: brr(j, d_title(name_24)) = .Range("F19").Value: brr(j, d_title(name_25)) = .Range("C23").Value
        brr(j, d_title(name_26)) = .Range("C24").Value: brr(j, d_title(name_27)) = "'" & .Range("C25").Value: brr(j, d_title(name_28)) = "'" & .Range("F22").Value
        brr(j, d_title(name_29)) = .Range("C28").Value: brr(j, d_title(name_30)) = .Range("F28").Value: brr(j, d_title(name_31)) = .Range("F30").Value
        brr(j, d_title(name_32)) = .Range("C31").Value: brr(j, d_title(name_33)) = .Range("C32").Value: brr(j, d_title(name_34)) = .Range("C34").Value
        brr(j, d_title(name_35)) = .Range("C35").Value: brr(j, d_title(name_36)) = .Range("F34").Value: brr(j, d_title(name_37)) = .Range("C14").Value
        brr(j, d_title(name_38)) = .Range("C15").Value: brr(j, d_title(name_39)) = "": brr(j, d_title(name_40)) = .Range("F15").Value: brr(j, d_title(name_41)) = "'" & .Range("C17").Value
        brr(j, d_title(name_42)) = .Range("C18").Value: brr(j, d_title(name_43)) = .Range("F20").Value: brr(j, d_title(name_44)) = .Range("F23").Value
        brr(j, d_title(name_45)) = .Range("F24").Value: brr(j, d_title(name_46)) = .Range("F29").Value: brr(j, d_title(name_47)) = .Range("C37").Value
        brr(j, d_title(name_48)) = .Range("C38").Value: brr(j, d_title(name_49)) = .Range("C39").Value: brr(j, d_title(name_50)) = .Range("C42").Value
        brr(j, d_title(name_51)) = "'" & .Range("F37").Value: brr(j, d_title(name_52)) = "'" & .Range("F38").Value: brr(j, d_title(name_53)) = .Range("F39").Value
        brr(j, d_title(name_54)) = .Range("F40").Value: brr(j, d_title(name_55)) = "'" & .Range("F41").Value: brr(j, d_title(name_56)) = .Range("C40").Value
        brr(j, d_title(name_57)) = .Range("C41").Value: brr(j, d_title(name_58)) = .Range("F42").Value: brr(j, d_title(name_59)) = .Range("C44").Value
        brr(j, d_title(name_60)) = .Range("C45").Value: brr(j, d_title(name_61)) = .Range("C46").Value: brr(j, d_title(name_62)) = .Range("C49").Value
        brr(j, d_title(name_63)) = "'" & .Range("F44").Value: brr(j, d_title(name_64)) = "'" & .Range("F45").Value: brr(j, d_title(name_65)) = .Range("F46").Value
        brr(j, d_title(name_66)) = "'" & .Range("F48").Value: brr(j, d_title(name_67)) = .Range("C47").Value: brr(j, d_title(name_68)) = .Range("C48").Value
        brr(j, d_title(name_69)) = .Range("F49").Value
   End With
   wb.Close 0
   myfile = Dir
Loop
Range("A2").Resize(j, 74) = brr
d_title.RemoveAll
Set d_title = Nothing
Erase arr, brr
Application.ScreenUpdating = True
End Sub

jjmysjg

用心修炼 发表于 2019-9-20 22:58
'你的系统表中“是否留守儿童”和“是否农村留守儿童”不知道是不是同一个字段，或者说这两个字段的内容是 ...

运行代码，选择文件夹，之后没有数据

jjmysjg

moonlit123 发表于 2019-9-20 21:43
有些数据对应规则还没搞懂
（比如P列留守儿童 和S列农村留守儿童）
（Q列城务人员随迁子女和T列随迁 ...

留下记号，谢谢

武松打鼓

网上或本坛子里有这种多合一表的工具呢