EXCEL应用技巧

EXCEL应用技巧 打开Excel工作 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf ，建立如下表格所示的表格: a b c A B C D E F G H I J K L M 工号 部门 姓名 姓名 性别 生日 年龄 年级 语文 数学 外语 政治 平均 工资 奖励 总额 张三 100 mm /李四 101 mm2 /....... 王明 男 2 89 98 92 87 表sheet2 1 设置生日为11-28-1998的形式，并根据生日计算出学生的年龄。 /2 对平均分>=90分，80<=平均分<90，a b c 60<=平均分<80的奖励金额数放在单元格N5..N7中，请利用公式计算出每位同学赢得的奖金。 /3 1年级姓名 部门 /李四 /。。。 学生工资为80元，2年级工资为100元，3年级工资为110元，请计算出每位同学的工资数，并根据工资要在sheet2表中将sheet1表的相同人员资料复制过来可在b表 单元格b2中输入 数和奖励数计算每位同学的工资总额。 ”=vlookup(a2,sheet1!a1:gg2000,3,false) 解答:1. 计算年龄:=INT(((YEAR(NOW())-YEAR(C2))*12+MONTH(NOW())-MONTH(C2))/12) (注意学生生日在最好将sheet1的数据区先定义一下 ) C 列 如何以进阶筛选命令筛选出“序号”为单数、双数、或者是任意间距值的records 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列) 序号 姓名 性别 年龄 1 李1 男 30 /2 李2 女 25 /3 李3 女 22/4 李4 男 31 /: : : : /99 李99 男 28 隔行求和问题 解答:在“序列”与“姓名”之间插入一个空列，在其第一格输入“条件”。在“条件”下面输入公式若有20列(只有一行)，需没间隔3列求和，该公式如何做,前面行跳跃求和的公式不管用。 “=IF(MOD(A5,2)=1,"单数","双数")”，用“自动筛选”。 解答:假设a1至t1为数据(共有20列)，在任意单元格中输入公式: 在两个工作表之间查找相同的内容并复制到新表 =SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1)) /按ctrl，shift，enter结束即可求出每隔三行之 和。 有两张工作表，内容都是电话号码、用户名称，怎样对两张工作表进行比较,(把第一张工作表和第二张 工作表中电话号码相同的项目复制到新工作表中或加上标记) 排名问题 解答:先备份工作簿。 在A1:F6区域有下面一个表格: 假设SHEET1、SHEET2 两张表的结构相同，A 列为用户名，数据由第二行开始。新建SHEET3 并在其A2 单 班级 班级管理量化考核细则初中班级管理量化细则班级心理健康教育计划班级建设班级德育计划 姓名 政治 语文 数学 总分 =IF(ISNA(MATCH(Sheet2!$A2,Sheet1!$A:$A,0))," DELET THIS ROW ",Sheet2!A2) 并向下、元格中输入 1 小东 90 90 90 270 /2 明明 95 92 90 277 /3 小英 96 89 91 276 /4 小刘 95 90 92 277/5 小红 95 91 向右填充。 92 278 /要在K1:K3的单元格中分别显示总分最高的同学的班级、姓名、总分，在L1:L3的单元格中分别完成后SHEET3中多了SHEET2 和SHEET1 共有的用户名，以及由"DELET THIS ROW"填充的数据行。剩下的显示总分第二的同学的班级、姓名、总分，在M1:M3的单元格中分别显示总分第三的同学的班级、姓名、就简单了～ 总分 ，注意期中277分的有两人,不要出现第二名与第三名都是"明明"的结果. 用字母在单元格里面填入平方米 解答:定义A2:A6区域为班级 ;定义B2:B6区域为姓名;定义F2:F6区域为总分 K1={INDEX(班级,MATCH(LARGE(总分+1-ROW(总分)/100,ROW($A1)),总分+1-ROW(总分)/100,0))} 解答1:在编辑状态下，选中“2”，按鼠标右键，选择“设置单元格格式”，选“上标” L1={INDEX(姓名,MATCH(LARGE(总分+1-ROW(总分)/100,ROW($A1)),总分+1-ROW(总分)/100,0))} 解答2:按[ALT]+数字键178[ENTER] M1={INDEX(总分,MATCH(LARGE(总分+1-ROW(总分)/100,ROW($A1)),总分+1-ROW(总分)/100,0))} 注:只要向下复制，便可得到1~6名的情况了。该公式可以无限排列。另注:这是数组公式，不需要输入求工龄 “{}”，在输完公式后，请按ctrl+shift+enter，应将后面的100改成100000，比较好。 =DATEDIF(B2,TODAY(),"y") 工作表间的查找与复制 =DATEDIF(B2,TODAY(),"ym") =DATEDIF(B2,TODAY(),"md") 我的工作是在一个2000多个员工的纪录(包括各种资料，如身份证号，工作记录等)的excel工作表A中 =DATEDIF(B2,TODAY(),"y")&"年"&DATEDIF(B2,TODAY(),"ym")&"月"&DATEDIF(B2,TODAY(),"md")&"日" 查找另一份200多个员工名单，把员工在A表中的各项纪录复制到另一个表上去，我听说用vba可以很容 易的简化大量的复制工作，不知哪位高手可以指点指点,, 解答:如下表sheet1的内容 班级 姓名 政治 语文 数学 总分 从身份证号码中提取性别 1 小东 90 90 90 270 /2 明明 95 92 90 277 /3 小英 96 89 91 276 /4 小刘 95 90 92 277 /5 小红 95 91 92 278 /要在K1:K3的单元格中分别显示总分最高的同学的班级、姓名、总分/在L1:L3的单元格中分Q: A1单元格中是15位的身份证号码，要在B1中显示性别(这里忽略15位和18位身份证号码的判别) 别显示总分第二的同学的班级、姓名、总分 /在M1:M3的单元格中分别显示总分第三的同学的班级、姓名、B1=if(mod(right(A1,1),2)>0,"male","female")请问这个公式有无问题，我试过没发现问题。但在某个网总分 /注意期中277分的有两人,不要出现第二名与第三名都是"明明"的结果. 站看到作者所用的是如下公式: B1=if(mid(A1,15,1)/2=trunc(mid(A1,15,1)/2),"female","male") A: dongmu 答:道理都是一样的，不过你的公式比那个公式优质。 定义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))} Q:问题1是这样的: -ROW(总分)/100,ROW($A1)),总分+1-ROW(总分)/100,0))}注:只要向 M1={INDEX(总分,MATCH(LARGE(总分+11、在A1:A30单元格区域中输入500至600之间的数值; 2、在B1单元格中输入500; 有无办法让B2所在行都呈红色字体 3、在C1单元格中插入公式: COUNTIF(A1:A30,">=INDEX(B1,1)*0.9") 解答:假设你有一个B列和一个A1的值，你的目的是，如果B2,A1的话，整个B列都为红色显示～ 回车后C1单元格中显示的结果是0，为何不能把A1:A30中输入的数大于或等于500*0.9的单元格个数统计设置如下:先选定整个b列，也就是在B列列标处单击(废话,^_^)，选择格式,条件格式 出来,错在哪里，该如何更正, 出现条件格式对话框，单击左边的下拉列表，里面只有两项，单元格数值和公式，选中公式，右边就可以问题2有下面一个表格: 输入任何可以返回逻辑值的公式了。输入这个公式=($B$2=$A$1)。千万注意要用绝对引用，因为如果是相班级 姓名 政治 语文 数学 总分 对的，excel又自作主张的一个一个判断了，就没有作用了。(绝对正确并且好用) 1 小东 90 90 90 270 /2 明明 95 92 90 277 /3 小英 96 89 91 276 /要在K1:K3的单元格中分别显示总 分最高的同学的班级、姓名、总分 .该如何设置公式, A1单元格为出生日期，可用=DATEDIF(A1,NOW(),"y")计算其年龄 答案1:建议你好好看看COUNTIF的帮助，真是搞不明白，为什么在B1里输入500而不是450呢, COUNTIF 不支持一个以上的条件，你应该用DCOUNT。 当做日报表累计数自动加 答案2: K1=INDEX(A2:F4,MATCH(K3,F2:F4,0),1) 当做日报表时,怎样让月累计数自动加上? 就是要月累计自动加上今天的当日收入数,今天只输入当日收入,K2=INDEX(A2:F4,MATCH(K3,F2:F4,0),2) 我想用用前一天的月累计数加上今天的当日收入数为今天的月累计数.情况是一月一个工作薄,每一个工作K3=MAX(F2:F4) 薄下30个工作表,用每一天的日期为报表名.我想把月累计的公式写为 =sheet17!c5 中的 17 用 解答3: day(now())-1 的值去取代他,该怎么办呢? 以下为报表格式,谢谢大家帮我想一想. 问题一:公式改为:=COUNTIF(A1:A30,">=" & INDEX(B1,1)*0.9) 部门-------当日收入 -----------月累计 问题二:在K1:K3中分别输入如下公式(假设A1为"班级"): 团队收入 12.12 123.00 /写字间收入 147,258.00 147.147/房内吧收入 147,258,369.00 =LOOKUP($K$3,$F$2:$F$4,A2:A4) 解答:=SUM('Sheet1:Sheet30'!C5) =LOOKUP($K$3,$F$2:$F$4,B2:B4) 在你需要月汇总的单元格填入上述公式，其作用是将工作表1到工作表30的“C5”单元格的值全部累加起=LARGE(F2:F4,1) 来，而“C5”单元格应填入当日的收入数。 解答4 又问:月累计的公式写为 =sheet17!c5 中的 17 用 day(now())-1 的值去取代他,该怎么办呢? ” 第一题:=COUNTIF(A1:A30,">="&B1*0.9) 答:用公式: =INDIRECT(ADDRESS(1,1,1,1,"sheet"&DAY(NOW())-1)) 第二题:K1==INDIRECT("A"&MATCH(MAX(F1:F4),F1:F4)) K2=INDIRECT("B"&MATCH(MAX(F1:F4),F1:F4)) K3=MAX(F2:F4) 排名问题 在A1:F6区域有下面一个表格: