Excel 2000应用输入操作技巧.docVIP

Excel 2000应用输入操作技巧 Excel 2000应用输入操作技巧 1.利用身份证号码获取公民出生日期 使用Excel进行人事管理，常常需要输入身份证号码、出生年月日等数据。由于身份证号码内含有相关信息，可以使用Excel函数从身份证号码中提取出生年月日，从而避免重复劳动以及可能的输入错误。 现行的身份证号码左起第7至12个字符表示出生年月日(存在Y2K问题)，我们可以使用MIDB函数从身份证号码的特定位置，分别提取出生年、月、日，再用CONCATENATE函数将提取出来的文字合并起来，就能得到对应的出生年月日。 具体公式为：=CONCATENATE(″19″,MIDB(A4,7,2),″年″,MIDB(A4,9,2),″月″,MIDB(A4,11,2),″日″)式中“19”是针对现行身份证号码中存在Y2K问题设置的，它可以在提取的出生年份前加上19。三个MIDB函数的操作对象存放在A4单元格，分别从左起第7、9、11个字符开始提取2个字符，即得到出生年、月和日，最后由CONCATENATE函数将所有字符合并起来。 需要注意的是：CONCATENATE函数和MIDB函数的操作对象均为文本，所以存放身份证号码的单元格必须事先设为文本格式，然后再输入身份证号。 2.利用身份证号码获取公民性别 这个公式可以从工作表的身份证号码中提取对应的性别。具体公式为：=IF(RIGHTB(a1,1)=″1″,″男″,IF(RIGHTB(a1,1)=″3″,″男″,IF(RIGHTB(a1,1)=″5″,″男″,IF(RIGHTB(a1,1)=″7″,″男″,IF(RIGHTB(a1,1)=″9″,″男″,″女″)))))，公式中C列存放文本格式的身份证号码，函数RIGHTB提取字符串右端给定长度的字符。根据身份证号码编号规则，该字符为奇数时为男，否则为女。经过IF函数五次判断，凡RIGHTB(c1,1)值为“1”、“3”等奇数的均为男，否则为女。 需要注意的是：RIGHTB(c1,1)提取C1单元格存放的身份证号码右起第一个字符，由于它是由文本格式的单字节数字组成的，一个字符占用一个字节，故以1为单位计算要提取的字符数。如果是普通汉字或双字节数字，一个字符要占用两个字节，应以2为单位计算要提取的字符数。 3.成绩等第计算 学校采用等第评定考试成绩，一般的标准为：考试分数高于或等于85分为A等；考试分数低于85分高于或等于70分为B等；考试分数低于70分高于或等于60分为C等；考试分数低于60分为D等；没有参加考试的不划等第。用Excel计算等第的公式如下(其中分数存放在C列，计算结果存入D列)：=IF(C2=85,″A″,IF(C2=70,″B″,IF(C2=60,″C″,IF(ISNUMBER(C2),″D″,IF(ISBLANK(C2),″ ″)))))，这是一个典型的IF函数嵌套公式，式中第二个IF语句是第一个 IF 语句的参数，第三个 IF 语句则是第二个IF语句的参数，以此类推。如果第一个逻辑判断表达式C1=85 为TRUE(真)，则D1单元格被填入“A”；如果第一个逻辑判断表达式C1=85不成立，则计算第二个IF语句“IF(C1=70”；以此类推直至计算结束。其中ISNUMBER函数在C1为空时返回FALSE(假)，接着执行最后一个IF语句，否则在C1单元格中填入“D”。ISBLANK函数C1为空时返回TRUE(真)，则C1单元格被填入一个空格。使用ISNUMBER函数和ISBLANK函数，可防止某个学生没有参加考试(即考试成绩为空)，但仍然给他评定为D等的情况发生。 如果成绩等第划分标准发生了变化，只须改变逻辑判断式中的值(85、70、60)即可，故上式在需要划分等第的场合具有一定的应用价值。IF函数常用的比较操作符如^： 4.利用IF函数计算工资增加金额 IF函数是Excel中最有用的函数之一，它可以解决办公事务处理中的许多问题。请看以下实例： 某单位要给职工增加工资，其标准为：工龄少于等于5年为20元；工龄多于5年少于等于10年为40元；工龄多于10年少于等于15年为70元；工龄多于15年少于等于20年为100元；工龄多于20年少于等于25年为140元；工龄多于25年少于等于30年为180元；工龄多于30年为240元。 此问题的处理方法是：建立工作表，其中A列输入职工姓名，B列输入工龄，C列存放计算出来的增资金额，A1、B1、C1单元格中输入列标题。再让单元格指针停留在C2单元格，按笔者以前介绍过的方法输入公式：=IF(B2=5,2