用Excel列表 实现批量录入功能EXCEL基本教程

用Excel列表 实现批量录入功能EXCEL基本教程（共4篇）

篇1：用Excel列表 实现批量录入功能EXCEL基本教程

我们在使用Excel的过程中，通常需要输入大量的数据，这是保证我们顺利完成各项工作的基础。但是，在录入数据的过程中，尤其是录入大量数据的时候，经常会出现一些不经意的录入错误，而这，会严重影响我们所得到的结果正确性。那么，怎样才能有效地控制这种录入错误的发生呢？除了细心、细心、再细心以外，在Excel中完成必要的设置，以最大可能地减少错误的发生也是极为重要的。

一、设置数据列表

有时我们需要录入的数据是某些重复数据中的一个，比如单位员工所属的部门。单位中部门个数是有限的，如果我们都通过键盘手工录入每位员工的工作部门，那自然是费时费力，还容易出错的。因此，我们不如为这些部门指定一个数据列表。录入时只需要在下拉列表中单击选择相应的部门，就可以了。这不仅可以提高录入速度，还会使得录入的质量得到保证。

首先选中要填写员工部门的所有单元格，点击功能区“数据”选项卡“数据工具”功能组中的“数据有效性”按钮，在弹出的菜单中点击“数据有效性”命令，打开“数据有效性”对话框。

点击对话框中“设置”选项卡，在“允许”下方的下拉列表中选择“序列”选项，然后在下面“来源”输入框中输入各部门名称（人事部,一车间,二车间,生产部,技术部,办公室），部门之间用英文的逗号隔开，如图1所示。点击“确定”按钮关闭对话框。

图1 Excel设置序列

我们也可以在工作表的空白单元格某列中分别输入各个部门名称，比如在H1:H6单元格区域。然后在“来源”下的输入框中输入“=$H$1:$H$6”，也可以得到同样的效果。

现在将鼠标定位于刚才选中的那些单元格区域任一单元格，就会在右边出现一个下拉箭头，点击它就会出现刚才我们所设置的下拉列表，如图2所示，单击其中的项目就可以完成输入了。

图2 Excel中的下拉列表

二、在其它工作表中使用

如果我们希望能在其它的工作表单元格区域中使用这个部门下拉列表，那么我们可以使用自定义名称完成这个任务。

在空白单元格列中录入相应部门名称，比如H1:H6单元格区域，

然后选中此单元格区域，点击功能区“公式”选项卡“定义的名称”功能组中的“定义名称”按钮，打开“新建名称”对话框。如图3所示，在“名称”右侧的输入框中输入名称，比如“bumen”。在“范围”下拉列表中选择“工作簿”，而在“引用位置”右侧的输入框中会自动使用我们选中的单元格区域。确定后，就可以为我们所选的单元格区域指定“bumen”的名称了。

图3 Excel新建工作簿

现在要做的，就是在选定工作表的相应单元格区域后，再打开“数据有效性”对话框，然后在“来源”输入框中输入“=bumen”，就可以在当前的工作表中使用这个部门列表了。

如果觉得这个自定义名称的方法有些罗嗦的话，那下面的方法就简单多了。

选中已经设置好数据有效性的单元格，然后按下“Ctrl+C”键进行复制。再将鼠标定位于目标单

关 键 字：EXCEL

篇2：用Excel列表 实现批量录入功能EXCEL基本教程

下面是具体操作步骤。

一、在格式工具栏中添加样式列表

1.在Excel的任一工具栏上单击右键，从弹出菜单中选择“自定义”命令。

2.单击“自定义”对话框中的“命令”选项卡。

3.在“类别”下方列表中单击“格式”，然后从右侧“命令”列表中将“格式”列表图标拖放到格式工具栏字体大小列表的右侧，如图1所示。

图1

4.单击“关闭”按钮。如图2所示，现在格式工具栏上多了一个样式列表。

图2

二、在样式列表中添加“文本自动换行”样式

1.在某个单元格中输入一些文本，然后按快捷键Ctrl+1，打开“单元格格式”对话框，

2.单击对话框中的“对齐”选项卡，然后选中“文本控制”下方的复选框“自动换行”，如图3所示。单击“确定”按钮关闭该对话框。这两步即为通常情况下实现单元格内容自动换行的方法。

图3

3.单击格式工具栏样式列表中的输入框，注意不是单击右侧的小箭头，输入“文本自动换行”，如图4所示，并按回车键。

图4

4.经过以上步骤，实际上已经建立了快速应用“自动换行”格式的快捷方式，当我们需要为其它单元格应用“自动换行”时，选中单元格，然后从样式列表中选择“文本自动换行”即可。

篇3：用Excel列表 实现批量录入功能EXCEL基本教程

在Excel教学过程中, 我们要在多媒体电子教室收取学生作业。而在课堂上, 对学生的课堂作业要进行及时批改评分是很费时间的。这样就不能客观公正的对学生及时进行评价, 对教学中存在的问题不能及时发现。怎样才能对学生的作业及时批改评分, 笔者查阅了有关vb程序自动化控制Excel的有关资料, 编写了一个小系统, 可以对学生的作业进行评分。

1系统实现的关键技术

1.1自动化控制

自动化使应用程序能够对另一个应用程序中实现的对象进行操作, 或者将对象公开以便可以对其进行操作。自动化服务器是向其他应用程序 (称为自动化客户端) 公开可编程对象 (称为自动化对象) 的应用程序。自动化服务器有时称为自动化组件。

许多Microsoft的应用程序是自动化服务器, 包括Access、Word、Excel、PowerPoint、、FrontPage等。这意味着您可以再使用这些特色丰富的应用程序作为您的应用程序的服务器。

公开自动化对象使客户端能够直接访问通过服务器可用的对象和功能, 从而自动完成某些过程。当应用程序提供的功能对其他应用程序有用时, 这样公开对象是有益处的。

要让vb编写的程序作为客户端, Excel程序作为应用程序服务器, 让vb程序能自动化控制Excel程序, 首先应在vb程序中引用Microsoft Excel类型库。方法是:从“工程”菜单中选择“引用”命令;选择Microsoft Excel 11.0 Object Library (office 2003不同的版本, 库的版本也不一样) ;选择“确定”。然后, 在vb程序中创建Excel向用户公开的对象, 在通用对象的声明过程中定义Excel对象:Excel.Application 、 Excel.WorkBook 、Excel.Worksheet 、Range、chart等。Excel.Application对象相当Excel应用程序, Excel.WorkBook对象对应于用户工作簿, Excel.Worksheet对象对应于工作表, range对象是选取的区域, chart对象对应于图表。这样, vb程序就可以通过Excel服务器实现对对象的自动化控制, 完成对对象的属性的提取或判别。这也是实现对学主作业评分的最关键的技术。

1.2Excel中宏的录制和编辑功能

Excel对象库中基本对象的属性直接反映了对象的特征或状态, 通过对对象属性值和题目所要求的对象属性进行比对, 可以判断出学生作业做得对或者错。对象的属性非常多, 虽然我们可以通过对象浏览的方法和系统的帮助查阅到我们编程所需的对象属性, 但是费时费力, 编程的效率非常低。Excel 提供了非常强大的宏编辑功能, 我们可以通宏来解决这个问题。

在Excel工作簿中录制宏, 按题目的要求对指定的工作簿进行编辑, 将对象进行的操作和对象状态或属性的改变以代码的形式存储中宏中。打开宏, 将对象属性的或状态改变的代码提取、转换成对象属性或状态的测试语句, 也就是变成对学生作业评分的语句, 这样就提高了编程的效率和准确度。

例如:将a1:e1单元格区域字体设置为楷体

以下为宏代码

Range ("A1:E1") .Select

With Selection.Font

.Name = "楷体_GB2312"

.FontStyle = "常规"

.Size = 14

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ColorIndex = xlAutomatic

End With

提取转换后的评分代码是

Range ("A1:E1") .Select

If Selection.Font.Name = "楷体_GB2312" then zf=zf+1 else ss=ss+”字体设置不正确”

如果正确设置了, 那么总分增加一定的值, 否则, 将出错原因记录在字符串ss中

1.3公共对话框的应用

要批量选择学生作业工作簿, 需要设计批量选取工作簿文件的对话框, 这会增加很大的工作量。Visual basic 提供了通用对话框 (common dialog box) 控件, 使得设计对话框十分方便。在默认情况下, 通用对话框控件不在工具箱中。在使用之前, 应将其添加到工具箱中。具体方法是在“工程”菜单中选“部件”命令, 打开“部件对话框。在“部件”对话框的“控件”选项卡中, 从列出的控件中找到microsoft common dialog control 6.0, 使在前面复选框中打下 “√”, 再单击“应用”按钮, 通用对话框就出现在在工具箱中, 就可以使用该控件了。

2系统的实现

2.1系统的功能模块

系统有四个模块, 分别是:① 选择评分工作簿:选取要批量评分的学生工作簿;② 评分:批量对学生的Excel作业进行评分, 并把学生的姓名、总分和出错原因写入“评分.xls”工作簿中;③ 查看结果:打开“评分.xls”工作簿, 查看评分的结果;④ 退出:退出系统。

2.2调用通用对话框批量选取学生作业工作簿的实现

通用对话框可以提供6种不同形式的对话框, 本系统使用的是打开对话框。为了批量选取学生作业工作簿, 将对话框的flags设置为512, 运行时允许用户选取多个文件, 所选的工作簿的路径和文件名在CommonDialog1.FileName中, 文件名之间以空格分隔。本系统将提取出路径和工作簿的文件名保存在数组myfilename中。为了便于记录成绩, 学生作业工作簿用自己的姓名作为文件名。

Private Sub Command3_Click ()

CommonDialog1.DialogTitle = "打开文件"

CommonDialog1.ShowOpen

'CommonDialog1.Filter = "所有文件 (*.*) |*.*|工作簿 (*.xls) |*.xls|"

CommonDialog1.Flags = 512 '允许多选

myfilename1 = CommonDialog1.FileName

j = 1

For i = 1 To Len (myfilename1)

If Mid$ (myfilename1, i, 1) <> " " Then myfilename (j) = myfilename (j) + Mid$ (myfilename1, i, 1) Else j = j + 1

Next i

‘将文件名置于数组中 多文件时取出各个文件名和路径置于数组中, 第一单元是路径;单文件时取出文件及路径名在数组的第一单元中

End Sub

2.3对学生作业进行批量评分并记录的实现

首先创建一个Excel.Application对象, 并用open方法打开“评分.xls”工作簿对象, 并激活工作表“sheet1”对象, 并选择“sheet1”的所有单元格, 清除单元格的内容, 并在第一行的三个单元格中输入“姓名”、“总分”、“出错原因”。因为对单个学生作业和对多个学生作业进行评分时, 提取文件名的方法不同, 所以分成两部分, 一部分是对多个学生作业, 进行评分, 另一部分是对一个学生进行评分。因为两部分只有提取文件名有一些不同, 所以本文只介绍对多个学生作业进行评分的部分。创建一个循环, 每循环一次打开一个学生作业工作簿对象, 并激活工作表对象 (Worksheet) , 选择其中的对象rang、cells、chart等对象, 把对象的属性或状态和题目所要求的对象的属性或状态进行比对, 主要是用判断语句, 如果正确, 则总分加一定分值。如果出错, 则将出错原因存入字符串ss中。最后, 激活“评分.xls”工作簿对象的“sheet1”对象, 将结果写入激活“评分.xls”工作簿对象的“sheet1”对象的单元格中。循环结束, 则将完成对所有学生的批量评分, 并将结果记录在““评分.xls”中

以下是程序代码:

Public j As Integer

Public myfilename1 As String

Public myfilename (100) As String (一个班最多100个学生)

Dim aa As Excel.Application

Dim bb As Excel.Workbook

Dim bb1 As Excel.Workbook

Dim cc As Excel.Worksheet

Dim cc1 As Excel.Worksheet

Dim ss As String

Private Sub Command1_Click ()

If j >= 2 Then

Set aa = CreateObject ("Excel.Application")

aa.Visible = True

Set bb1 = aa.Workbooks.Open (App.Path & "评分.XLS")

Set cc1 = bb1.Worksheets ("sheet1")

cc1.Activate

cc1.Cells.Select

Selection.Clear

cc1.Cells (1, 1) = "姓名"

cc1.Cells (1, 2) = "总分"

cc1.Cells (1, 3) = "出错原因"

For m = 2 To j

myfilename1 = myfilename (1) & myfilename (m) '多文件时取出各个文件名置于数组中, 第一单元是路径, 其它各单元是文件名

Set bb = aa.Workbooks.Open (myfilename1) 打开要评分的学生作业文档

Set cc = bb.Worksheets ("sheet1")

zf = 0 记录总分

ss = "" ‘记录出错原因

Form1.Show

Range ("b15") .Select

If Selection.Formula = "=SUM (B3:B14) " Then

zf = zf + 1

Else

ss = ss + "1计算不正确"

End If

Range ("f3") .Select

If Selection.Formula = "= (B3+C3+D3+E3) /3" Then

zf = zf + 1

Else

ss = ss + "2计算不正确"

End If

If Selection.NumberFormat = "$#, ##0.000;$-#, ##0.000" Then

zf = zf + 1

Else

ss = ss + "数据格式不正确"

End If

Range ("A1:e1") .Select

If Selection.HorizontalAlignment = xlCenterAcrossSelection Then

zf = zf + 1

Else

ss = ss + "设置了跨列居中不正确"

End If

If Selection.Font.Name = "楷体_GB2312" Then

zf = zf + 1

Else

ss = ss + "设置了楷体不正确"

End If

If Selection.Font.Size = "18" Then

zf = zf + 1

Else

ss = ss + "设置字号不正确"

End If

If Selection.Font.ColorIndex = 5 Then

zf = zf + 1

Else

ss = ss + "设置了字符颜色错误"

End If

cc1.Activate

l1 = Len (myfilename (1) )

l2 = Len (myfilename1)

cc1.Cells (m, 1) = Mid$ (myfilename1, l1 + 1, l2 - l1 - 4) ‘学生作业用学生自己的名字命名, 取得学生姓名

cc1.Cells (m, 2) = zf

cc1.Cells (m, 3) = ss

'Form1.Print ss 打印评分说明

Set cc = Nothing

bb.Save

bb.Close

Set bb = Nothing

Next m

Else 单个学生的评分与上基本相同 (略)

2.4查阅评分结果

打开“评分.xls”工作簿, 让用户查看评分的结果。

因为习题是千变万化的, 所以评分程序也要不断更新。为提高效率, 我们要充分利用Excel的宏功能, 实现快速编程。另外, 我们也可以根据教学内容编写习题集和相应的评分程序集。

参考文献

[1]谭浩强, 袁玫, 薛淑斌.visual basic程序设计[M].北京:清华大学出版社, 2006.

篇4：用Excel列表 实现批量录入功能EXCEL基本教程

一、自定义格式

选中要输入性别的单元格区域，点击右键，在弹出的菜单中点击“设置单元格格式”命令，打开“设置单元格格式”对话框。点击对话框的“数字”选项卡，在左侧的“分类”列表中选中“自定义”，然后在右侧的“类型”输入框中输入“[=1]“男”;[=2]“女””，如图1所示。确定后，只要在这些单元格中输入数字“1”，则会显示为“男”，输入数字“2”，则显示为“女”。这样比较起来，输入一个数字比起输入汉字，那当然要简单得多了。

图1(点击看大图)

这种方法，在屏幕上显示的是“男”、“女”，不过，在编辑栏中可以看到，仍然是“1”、“2”。

二、查找替换

使用查找替换的方法也可以实现上面的这种效果。在输入时，“男”就输入“1”，而“女”则输入“2”。全部输入完成后，选中这些单元格区域，然后按下“Ctrl+F”快捷键，打开“查找和替换”对话框。点击“替换”选项卡，在“查找内容”输入框中输入“1”，在“替换为”输入框中输入“男”，如图2所示，然后点击“全部替换”按钮，就可以将选中区域中的全部数字“1”替换为“男”了，

用同样的方法，将“2”替换为“女”，大功告成。

图2(点击看大图)

这种方法输入时只需要输入数字，方便快捷。替换后则可以换成相应的文本，屏幕显示与实际内容也一致，比起第一种方法可以避免某些因屏幕显示与实际内容不一致造成的麻烦。

三、公式设置

这种方法需要加上辅助列。比如性别输入应该在D2:D100单元格区域，而我们使用C列作为辅助列。输入时，在C列完成相应的输入过程。仍然是“男”为“1”、“女”为“2”。也可以只在应为“男”时输入“1”，则为“女”时不必输入。全部完成后，在D2单元格中输入公式“=IF(C2=1,“男”,“女”)”，然后拖动D2单元格的填充句柄至D100单元格。松开鼠标就可以得到所需要的内容了，如图3所示。我们可以选中C列单元格区域，点击右键，然后在弹出菜单中点击“隐藏”命令，将C列隐藏起来。

图3