搜索
EXCEL---信息技术考核报名汇总表(2010-05-3115:21:08)标签：教育分类：EXCEL学习案例背景2005年受天津市教委委托组织部分准备当年申报职称的中小学教师参加信息技术考核培训，设计的报名汇总表中包括报名序号、教师姓名、性别、年龄、区县、任职单位、身份证号码和备注八个字段，具体操作流程是将报名通知和报名汇总表发给各区县教育局人事科，各区县教育局布置各学校组织教师报名，各区县汇总后将本区县报名汇总表excel电子表发给市继续教育中心，继续教育中心汇总成全市报名表，根据报名情况组织培训和考核工作。因为信息技术考试采取网上考核，其中“报名汇总表”的数据字段中的“身份证号码”一项作为上机考核的个人信息之一，必须填写准确，否则无法进入考核平台。在组织信息技术考试时，个别考生在输入自己的身份证号码后仍然无法进入考核平台，这是如何造成的呢？通过本单元的学习，您就会知道答案。通过本节的学习，主要应掌握数字的自定义格式和数据有效性的应用方法。关键技术点要实现本案例中的功能，学员应该掌握以下EXCEL技术点。●基础知识：图案，数字的文本格式，数字的自定义格式，数据有效性设置下拉选框●函数应用OR函数，LEN函数最终效果展示2.3.1创建信息技术考核报名汇总表Step1输入、处理表格标题。首先创建一个新的工作簿Book1，按照“最终效果展示”图输入表头和表格标题，并进行相应格式设置，然后按键确认输入。Step2设置报名序号的自定义格式①选中A3:A12单元格区域，按快捷键弹出“单元格格式”对话框，切换到数字选项卡，②在左侧的“分类”列表框中选择“自定义”，在右侧的“类型”框里拖动鼠标选中默认的“G/通用格式”，然后按键删除，③输入“2005”0000,然后单击“确定”按钮，此时“报名序号”的自定义格式就设置好了。Step3输入报名序号①选中A3单元格，输入有效数字“0001”，按键确认输入，这时编辑栏内显示的值为“1”。②在A4：A12单元格区域陆续输入相应报名序号的后4位中的有效数字后，分别按键确认输入。（例如报名序号为“20050003”，只需输入有效数字“3”即可）Step4输入“姓名”选中单元格区域A3:A12，输入上述10人的姓名，Step5应用数据有效性设置“性别”的下拉选框①选中单元格区域C3:C12，单击菜单“数据”→“有效性”打开”数据有效性”对话框，②切换到设置选项卡。单击“允许”右侧的下拉箭头按钮，在下拉列表中选择“序列”，在来源文本框中输入序列的来源“男，女”（注意输入男与女之间的逗号时输入法须在英文状态下），然后单击确定按钮。Step6输入性别①单击D3单元格，在D3单元格的右边会有一个下拉箭头，单击它就会有下拉列表框，在下拉列表框中选取“男”，单元格就输入了该性别。②用同样方法在单元格区域D4：D12输入考生的相应性别。Step7输入年龄、区县和任职单位陆续输入上述10人的年龄、所属区县和任职单位三个字段的个人信息。Step8设置“身份证号“的数据有效性①选中G3:G12单元格区域，单击菜单“数据”→“有效性”弹出”数据有效性”对话框，②切换到设置选项卡。单击“允许”右侧的下拉箭头按钮，在下拉列表中选择“自定义”选项，然后在“公式”文本框中输入公式：“=OR(LEN(G3)=15,LEN(G3)=18)。③切换到“输入信息”选项卡，在“标题”文本框中输入“请输入15位或18位的身份证号码”。④在切换到“出错警告”选项卡，单击样式右侧的下拉箭头按钮，从下拉列表中选择“警告”，然后在标题文本框中输入“输入错误”在错误信息文本框中输入：“您输入的身份证号码位数有误，请重新输入。”⑤单击“确定”按钮即可完成“身份证号码“的数据有效性的设置。Step9设置单元格区域的文本格式①选中单元格区域G3:G11，按快捷键弹出“单元格格式”对话框，切换到数字选项卡，②在左侧的“分类”列表框中选择“文本”，按键确认即可完成准备输入数字的单元格区域G3:G11的“文本“化设置。Step10输入身份证号码①选中单元格区域G3:G11，陆续输入前9人的身份证号码。②选中单元格G12，输入该考生的身份证号码“120105195301213812”输出的结果是数字的科学计数法形式“1.20105E+17”（G12单元格仍然是数字格式，没设成文本格式）③光标移到G10单元格，单击常用工具栏“格式刷”，在单击单元格G12，这时单元格G12左上角也出现了一个绿色小三角，这是文本格式的标志。④双击单元格G12，内容显示为“120105195301213000”。Step11保存并命名工作簿单击常用工具栏”保存”按钮，将文件命名为“信息技术考核汇总表”。就如“最终效果展示”图显示的那样，前15位数字正确无误，后三位数字812却变成了三个零。“案例背景“中提到个别考生在准备上机考试输入个人身份证号码后仍不能进入考核平台，这是因为考核平台提前输入的该考生的身份证号码是120105195301213000”而考生本人不知道，考核时自然要输入正确的号码“120105195301213812“，而系统不能接受，因此该考生不能进入考核平台。那么到底是什么原因造成系统把18位数字的后三位数字变成三个零呢?在本节的”关键知识点讲解“中详细讲解。Step2设置表格边框选中单元格区域A2:G12，按快捷键弹出“单元格格式”对话框，切换到边框选项卡，在“颜色“选项框中选择“绿色”，在“线条样式”选项框中，外边框“选择”双线“，”内部“选择”虚线“，然后单击确定按钮即可完成边框设置。Step3设置文字格式选中准备设置文字格式的单元格区域A2:G12，单击常用工具栏“字体“选项框选择”宋体“，”字号“选项框选择14号，单击”居中“按钮，即可完成文字格式的设置。Step4调整表格列宽单击菜单“格式”→“列”，从下拉菜单中选择“最合适的列宽”。至此表格美化工作全部完成。关键知识点讲解1.案例公式解析因为要在G列输入身份证号码，因此必须输入15位数字或者18位数字，为避免错误地多输或者少输，因此在G3单元格设置数据有效性，期中G3单元格数据有效性的公式为”=OR(LEN(G3)=15,LEN(G3)=18)，就是Excel保证该单元格只接受15位或18位的文本字符串。输入的不是15位或18位系统就报错提示。2.单元格的字符容量每个单元格最多可容纳32000个字符。但是，通过上面的操作我们得知在一个数字格式的单元格中输入长度11位以下的数字，系统能够直接显示输入结果，输出即输入。11位以上的数字会Excel会默认以科学记数法形式来表示，对于15位以上数字，从第16位开始系统会自动将后面的数字全部默认为“0”。这到底是怎么回事呢？原来Excel能够处理的数字的最大精度为15位，因此多余15位的数字只能当做0来保存。如果您输入的数字位数超过15位数，必须把他当作文本而不是数字来输入，在Excel里面有两个方法可以把数字强制转换成文本。⑴就像案例中做的那样，单击常用工具栏“格式”→“单元格”，从弹出“单元格格式”对话框中，切换到数字选项卡，选择文本，即可把该单元格设置为本文格式。⑵再输入数字前先输入一个单引号“‘”，这个符号只是一个标示符，表示其后面的内容是文本字符串，而符号本身没有任何意义，尽管你能够在编辑栏中看见他，但其实它并不属于单元格内容的一部分。需要注意的是上面的步骤不能够颠倒次序，也就是说如果与偶已经输入了长度15位的数字，再改变单元格格式为文本是没有作用的。案例中出现的个别靠不能够进入考核平台的问题，鼓励就是颠倒了顺序造成的。3.逻辑运算逻辑判断是指一个有具体意义，并能够判定真或假的陈述语句。他是函数公式的基础，不仅关系到公式的正确与否，也关系到解题思路的繁简。只有只有逻辑条理清晰也能写出简洁有效的公式。常用的逻辑关系有三种，即“与”、“或”、“非”。在Excel中逻辑值只有两个，即“TRUE“和’FALSE”,它们代表“真”和“假“，用数字表示的话可以分别看作是1（或非0数字）和0。Excel中用于逻辑运算的函数主要有AND函数、OR函数和NOT函数。4.OR函数函数名称：OR主要功能：返回逻辑值，仅当所有参数值均为逻辑“假（FALSE）”时返回函数结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”。使用格式：OR(logical1,logical2,...)参数说明：Logical1,Logical2,Logical3……：表示待测试的条件值或表达式，最多30个。应用举例：在C62单元格输入公式：=OR(A62=60,B62=60)，确认。如果C62中返回TRUE，说明A62和B62中的数值至少有一个大于或等于60，如果返回FALSE，说明A62和B62中的数值都小于60。特别提醒：如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“#VALUE!”或“#NAME”。5.AND函数函数名称：AND主要功能：返回逻辑值：如果所有参数值均为逻辑“真（TRUE）”，则返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。使用格式：AND(logical1,logical2,...)参数说明：Logical1,Logical2,Logical3……：表示待测试的条件值或表达式，最多这30个。应用举例：在C5单元格输入公式：=AND(A5=60,B5=60)，确认。如果C5中返回TRUE，说明A5和B5中的数值均大于等于60，如果返回FALSE，说明A5和B5中的数值至少有一个小于60。特别提醒：如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“#VALUE!”或“#NAME”。6.NOT函数函数名称：NOT主要功能：求出一个逻辑值或逻辑表达式的相反值。如果您要确保一个逻辑值等于其相反值，就应该使用NOT函数。使用格式：NOT(logical)参数说明：Logical是一个可以得出TRUE或FALSE结论的逻辑值或逻辑表达式。如果逻辑值或表达式的结果为FALSE，则NOT函数返回TRUE；如果逻辑值或表达式的结果为TRUE，那么NOT函数返回的结果为FALSE。应用举例：如果A1=6、A2=8，那么公式“=NOT(A1+A2=14)“返回FALSE7.LEN函数函数名称：LEN主要功能：统计文本字符串中字符数目。使用格式：LEN(text)参数说明：text表示要统计的文本字符串。应用举例：假定A41单元格中保存了“我今年28岁”的字符串，我们在C40单元格中输入公式：=LEN(A40)，确认后即显示出统计结果“6”。特别提醒：LEN要统计时，无论中英文，全角字符，还是半角字符，每个字符均计为“1”；与之相对应的一个函数——LENB，在统计时半角字符计为“1”，全角字符计为“2”。EXCEL学习---班主任工作应用在学校管理工作中，班主任属于基层一线管理人员，属于没有级别的“主任”，虽然没有职级，但是在学校管理工作中却起着至关重要的作用，因为学校对于学生的管理工作多数都是通过班主任来实施的，可以说班主任的工作质量直接的反应学校的管理水平。学生管理工作内容繁多、事无巨细，而班主任却必须事必躬亲。完成这些重复性的管理工作往往耗时费力，那么如何提高工作效率呢？相信随着学习的不断深入，Excel会为您提供有效地帮助。3.1一百只青蛙案例背景上世纪90年代当本人刚接触Excel时，曾看过微软公司出的一本介绍该公司产品性能的一本名为“21世纪知识工作者完备手册”的宣传手册，其中在“Excel”篇中有一个十分有趣的案例叫“一百只青蛙”。案例中应用函数精妙的设计了公式，使本人从此对Excel产生了浓厚的兴趣。我把它作为第二篇的开篇内容介绍给大家，希望也能够引起学员们对学习Excel的兴趣。案例的内容是这样的：“一只青蛙一张嘴，两只眼睛四条腿，扑通一声跳下水；两只青蛙两张嘴，四只眼睛八条腿，扑通扑通跳下水；三只青蛙三张嘴，六只眼睛十二条腿，扑通扑通扑通跳下水；…….一百只青蛙一百张嘴，二百只眼睛四百条腿，扑通…..扑通跳下水。要求