补充实验EXCEL2010高级操作详解.docx

补充实验 EXCEL2010高级操作一、实验目的1．了解在EXCEL中导入外部数据的方法。2．进一步理解函数的概念，掌握相关函数的使用方法。3．掌握高级筛选的技巧。二、实验内容1．从外部文件中导入数据，创建初步的职工基本信息表、工资基本情况表以及部门表。2．按照要求，利用公式和函数对数据进行处理，补充形成完整的职工基本信息表。3．按照要求对职工基本信息表中的数据进行计算和筛选。三、操作指导按照前述方法创建工作簿文件，保存为“基本信息表.xlsx”，并将表sheet1重命名为“基本信息”，表sheet2重命名为“工资信息”，表sheet3重命名为“部门信息”。1．从文本文件“基本信息”中导入数据。（1）选择“基本信息”表，单击“数据”选项卡，在“数据”选项卡中“获取外部数据”组中单击“自文本”按钮（图1），打开“导入文本文件”对话框，选定“基本信息.txt”文件，单击“确定”按钮，打开“文本导入向导”窗口。（2）在“文本导入向导第1步”窗口中选择“分隔符号”（图2），单击“下一步”按钮，打开“文本导入向导第2步”窗口（图3），设置分列数据包含的分隔符号，此处选择“Tab键”，单击“下一步”，打开“文本导入向导第3步”窗口，设置各列的数据格式。注意：如果选择“常规”数据格式，则将数字转化为数值类型，日期转换为日期型，其他转换为文本类型。（3）在“文本导入向导第3步”窗口中，单击选中“身份证号”一列，设置数据格式为“文本”；选中“入职时间”一列，设置数据格式为“日期”（图4），单击“完成”按钮，打开“导入数据”对话框。（4）选中“A1”单元格，设定数据导入的起始位置（图5），单击“确定”按钮，完成数据导入工作。采用上述方法，在其余两个工作表中分别导入文本文件工资信息.txt和部门信息.txt。2．根据身份证号，完善出生日期信息根据国家规定，身份证号从第7位开始的8位数字代表了出生日期。本题中，“身份证号”列为文本型数据，所以我们只需要从身份证号的第7位开始截取8位字符就能得到出生日期信息，MID函数具有截取文本的功能。但是此时得到的生日信息仍然是文本型的数据，我们需要将其转换成相应的日期型数据，以便按要求显示成日期格式，或者进行日期型数据的运算，这需要使用DATE函数。MID函数功能：返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。一般格式：MID(text, start_num, num_chars)参数说明：Text 必需。提取字符的文本字符串,可以是具体的值，如“中国人”，也可以是文本型的单元格引用，如B2，C3等。Start_num必需。文本中要提取的第一个字符的位置。文本中第一个字符start_num为 1，依此类推。Num_chars必需。指定希望 MID 从文本中返回字符的个数。如获取GH1001号员工的出生年份信息，就可使用函数：MID(C2,7,4)DATE函数功能：Excel 将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39447 天。DATE函数实际上的返回值就是这个序列号，如果将单元格格式设置为数字，则显示这个序列号；否则，则显示相应的日期。一般格式：DATE(year,month,day)参数说明：Year必需。year 参数的值可以包含一到四位数字。Excel 将根据计算机所使用的日期系统来解释 year 参数。为避免出现意外结果，建议对 year 参数使用四位数字。例如，以“1907”作为年值。Month 必需。一般使用1-12之间的整数，表示一年中从 1 月至 12 月（一月到十二月）的各个月。将DATE函数和MID函数结合起来，就能达到我们的目的。例如，表示“GH1001”员工出生日期的公式为：DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2))操作步骤如下：（1）在E1单元格中输入“出生日期”；（2）选中E2单元格，在公式栏中输入：=DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2))。（3）单击回车键，得到员工“GH1001”的出生日期。（4）选中E2单元格，拖动填充手柄，使用公式复制功能完成其他员工的出生日期信息录入。（5）选中E2：E22区域的单元格，设置其格式为年月日，如图6所示。3．根据身份证号，完善性别信息。根据国家规定，身份证号的第17位数字代表性别，如果是偶数，表示此人是女性，否则表示此人是男性。据此，解决思路为：①截取身份证号的第17位数字，可用MID函数。②判断该数字是奇数还是偶数，可用MOD函数。③根据MOD函数的计算结果是否为0，输出“男”