EXCEL应用技巧.doc

打开Excel工作表，建立如下表格所示的表格：ABCDEFGHIJKLM姓名性别生日年龄年级语文数学外语政治平均工资奖励总额王明男2899892871设置生日为11-28-1998的形式，并根据生日计算出学生的年龄。/2对平均分>=90分，80<=平均分<90，60<=平均分<80的奖励金额数放在单元格N5..N7中，请利用公式计算出每位同学赢得的奖金。/31年级学生工资为80元，2年级工资为100元，3年级工资为110元，请计算出每位同学的工资数，并根据工资数和奖励数计算每位同学的工资总额。解答：1.计算年龄:=INT(((YEAR(NOW())-YEAR(C2))*12+MONTH(NOW())-MONTH(C2))/12)(注意学生生日在C列)2.计算工资:=IF(LEN(E2)>0,CHOOSE(E2,80,100,110),"")(注意学生年级在E列)3.奖励:=IF(J2>=90,$N$5,IF(J2>=80,$N$6,IF(J2>=60,$N$7,"")))(平均分在J列)隔行求和问题若有20列（只有一行），需没间隔3列求和，该公式如何做？前面行跳跃求和的公式不管用。解答：假设a1至t1为数据（共有20列），在任意单元格中输入公式：=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1))/按ctrl＋shift＋enter结束即可求出每隔三行之和。排名问题在A1:F6区域有下面一个表格：班级姓名政治语文数学总分1小东909090270/2明明959290277/3小英968991276/4小刘959092277/5小红959192278/要在K1:K3的单元格中分别显示总分最高的同学的班级、姓名、总分，在L1:L3的单元格中分别显示总分第二的同学的班级、姓名、总分，在M1:M3的单元格中分别显示总分第三的同学的班级、姓名、总分，注意期中277分的有两人,不要出现第二名与第三名都是"明明"的结果.解答：定义A2:A6区域为班级；定义B2:B6区域为姓名；定义F2:F6区域为总分K1={INDEX(班级,MATCH(LARGE(总分+1-ROW(总分)/100,ROW($A1)),总分+1-ROW(总分)/100,0))}L1={INDEX(姓名,MATCH(LARGE(总分+1-ROW(总分)/100,ROW($A1)),总分+1-ROW(总分)/100,0))}M1={INDEX(总分,MATCH(LARGE(总分+1-ROW(总分)/100,ROW($A1)),总分+1-ROW(总分)/100,0))}注：只要向下复制，便可得到1~6名的情况了。该公式可以无限排列。另注：这是数组公式，不需要输入“{}”，在输完公式后，请按ctrl+shift+enter，应将后面的100改成100000，比较好。工作表间的查找与复制我的工作是在一个2000多个员工的纪录（包括各种资料，如身份证号，工作记录等）的excel工作表A中查找另一份200多个员工名单，把员工在A表中的各项纪录复制到另一个表上去，我听说用vba可以很容易的简化大量的复制工作，不知哪位高手可以指点指点？？解答：如下表sheet1的内容abc姓名工号部门张三100mm/李四101mm2/.......表sheet2abc姓名部门/李四/。。。要在sheet2表中将sheet1表的相同人员资料复制过来可在b表单元格b2中输入”=vlookup(a2,sheet1!a1:gg2000,3,false)最好将sheet1的数据区先定义一下如何以进阶筛选命令筛选出“序号”为单数、双数、或者是任意间距值的records序号姓名性别年龄1李1男30/2李2女25/3李3女22/4李4男31/：：：：/99李99男28解答：在“序列”与“姓名”之间插入一个空列，在其第一格输入“条件”。在“条件”下面输入公式“=IF(MOD(A5,2)=1,"单数","双数")”，用“自动筛选”。在两个工作表之间查找相同的内容并复制到新表有两张工作表，内容都是电话号码、用户名称，怎样对两张工作表进行比较？（把第一张工作表和第二张工作表中电话号码相同的项目复制到新工作表中或加上标记）解答：先备份工作簿。假设SHEET1、SHEET2两张表的结构相同，A列为用户名，数据由第二行开始。新建SHEET3并在其A2单元格中输入=IF(ISNA(MATCH(Sheet2!$A2,Sheet1!$A:$A,0)),"DELETTHISROW",Sheet2!A2)并向下、向右填充。完成后SHEET3中多了SHEET2和SHEET1共有的用户名，以及由"DELETTHISROW"填充的数据行。剩下的就简单了！用字母在单元格里面填入平方米解答1：在编辑状态下，选中“2”，