文章分类 -  Excel

Excel常用函数公式及技巧
摘要: 谈谈Excel输入的技巧 在Excel工作表的单元格中,可以使用两种最基本的数据格式:常数和公式。常数是指文字、数字、日期和时间等数据,还可以包括逻辑值和错误值,每种数据都有它特定的格式和输入方法,为了使用户对输入数据有一个明确的认识,有必要来介绍一下在Excel中输入各种类型数据的方法和技巧。   【1】输入文本   Excel单元格中的文本包括任何中西文文字或字母以及数字、空格和非数字字符的组合,每个单元格中最多可容纳32000个字符数。虽然在Excel中输入文本和在其它应用程序中没有什么本质区别,但是还是有一些差异,比如我们在Word、PowerPoint的表格中,当在单元格中输入文本后,按回车键表示一个段落的结束,光标会自动移到本单元格中下一段落的开头,在Excel的单元格中输入文本时,按一下回车键却表示结束当前单元格的输入,光标会自动移到当前单元格的下一个单元 阅读全文
posted @ 2012-02-15 11:11 .NET快速开发框架 阅读(18155) 评论(1) 推荐(0) 编辑
摘要: 查找顺序公式 =LOOKUP(2,1/(A1:A200),A1:A20) =MATCH(7,A1:A20) =VLOOKUP(7,A1:B11,2) 怎样实现精确查询 用VLOOKUP =VLOOKUP(B11,B3:F7,4,FALSE) 用LOOKUP =LOOKUP(B11,B3:B7,E3:E7) 用MATCH+INDEX =INDEX(E3:E7,MATCH(B11,B3:B7,0)) 用INDIRECT+MATCH =INDIRECT("E"&MATCH(B11,B3:B7,0)+2) 用OFFSET+MATCH =OFFSET(E3,MATCH(B11,B3:B7,0)-1,0) 用INDIRECT+ADDRESS+MATCH =INDIRECT(ADDRESS(MATCH(B11,B4:B7,0)+3,5)) 用数组公式 阅读全文
posted @ 2012-02-15 11:10 .NET快速开发框架 阅读(30964) 评论(0) 推荐(0) 编辑
摘要: 求平均值 如在列中有一组数字:10、7、9、27、2 =AVERAGE(A2:A6) 上面数字的平均值为11 行公式=AVERAGE(B2:D2) 如何实现求平均值时只对不等于零的数求均值? =AVERAGE (IF(A1:A50,A1:A5)) 阅读全文
posted @ 2012-02-15 11:10 .NET快速开发框架 阅读(14520) 评论(0) 推荐(0) 编辑
摘要: 取整数函数 907.5;1034.2;1500要改变为908;1035;1500公式为: =CEILING(A1,1) 907;1034;1500要改变为910;1040;1500公式为: =CEILING(A1,10) 如果要保留到百位数,即改变为1000;1100;1500公式为: =CEILING(A1,100) 数值取整 在单元格中要取整数(只取整数不用考虑四舍五入)用什么函数呀?例如:10/4只要显示2就可以了!要考虑负数的因数呢?例如:(-10/4)要显示-2而不是-3?怎么办? =TRUNC(A1,0) =ROUNDDOWN(A1,0) 求余数的函数 比如:A1=28,A2=(A1÷6)的余数=4,请问这个公式怎么写? 解答:=MOD(28,6) 阅读全文
posted @ 2012-02-15 11:09 .NET快速开发框架 阅读(46692) 评论(0) 推荐(0) 编辑
摘要: 统计数值大于等于80的单元格数目 在C17单元格中输入公式: =COUNTIF(B1:B13,"=80") 确认后,即可统计出B1至B13单元格区域中,数值大于等于80的单元格数目。 计算出A1里有几个abc A1: abc-ded-abc-def-abc-ded-ded-abc , 如何计算出A1里有几个abc 公式=(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc") 有条件统计 如何统计当A115时不进行统计? 个数: =IF(A115,"",COUNTIF(B2:B10,"15,"",SUMIF(B2:B10,"=8.5")) 阅读全文
posted @ 2012-02-15 11:07 .NET快速开发框架 阅读(30872) 评论(0) 推荐(0) 编辑
摘要: 个调税公式 =MAX(($A1-1900)*{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45} -{0,0,25,125,375,1375,3375,6375,10375,15375}) {0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45} 为税率 {0,0,25,125,375,1375,3375,6375,10375,15375} 为税收扣除数 上列公式的简化式 : =MAX(应纳税所得额*0.05*{1,2,3,4,5,6,7,8,9} -25*{0,1,5,15,55,135,255,415,615},0) 算物价的函数 物价的那个三七作五,二舍八入的尾数处理,做一个函数。就是小数点后面第二位如果是1,2的就舍掉,如果是3,4,5,6,7的都变为5,如果是8,9的小数点第一位 阅读全文
posted @ 2012-02-15 11:06 .NET快速开发框架 阅读(34307) 评论(0) 推荐(0) 编辑
摘要: 常用的自定义格式 单元格属性自定义中的“G/通用格式“和”@”作用有什么不同? 设定成“G/通用格式“的储存格,你输入数字1..9它自动认定为数字,你输入文字a..z它自动认定为文字,你输入数字1/2它会自动转成日期。 设定成“@“的储存格,不管你输入数字1..9、文字a..z、1/2,它一律认定为文字。 文字与数字的不同在於数字会呈现在储存格的右边,文字会呈现在储存格的左边。 常用的自定义格式拿出来大家分享 阅读全文
posted @ 2012-02-14 13:37 .NET快速开发框架 阅读(15498) 评论(0) 推荐(0) 编辑
摘要: 通过条件格式将小计和总计的行设为不同的颜色 答:输入=RIGHT(RC,1)="计";设定字体、边框、图案;确定。 如何实现这样的条件格式 有一个excel表单,若当其中一栏数值超过某一值,使整个一行底色为某一颜色(比如红色),用条件格式不能实现 注意公式为=$A1100,而不是=A1100 先选定整行再设置条件格式... 列标"A"用绝对引用, 行标"1"用相对引用, 用"格式刷"刷下去... 阅读全文
posted @ 2012-02-14 13:36 .NET快速开发框架 阅读(27871) 评论(0) 推荐(0) 编辑
摘要: 数字如何拆分 我有一组数据,如123,59等,假如这些数据均在A列,我现在需要将123或者59这样的数据拆成到B,C,D列。 B1=MID(TEXT($A1,"000"),COLUMN(A1),1) 往右拖 =MID(REPT(0,3-LEN($A1))&$A1,COLUMN(A1),1) 单元格中的数据拆分 如何将一个单元格中的11位数据拆分11各单元格(每个单元格一个数字)。 如:01234567890 变为:0,1,2,3,4,5,6,7,8,9,0(一个单元格一个数字),文字也同样。用以下公式: =MID($A1,COLUMN(A1),1) 向右拖 =MID($A$2,COLUMN()-1,1) 向右拖 =MID($A$2,COLUMN(),1) 向右拖 也可以,选中区域后“数据”-----分列,“固定列”-----看到尺寸的时候分别在0 阅读全文
posted @ 2012-02-14 13:35 .NET快速开发框架 阅读(37630) 评论(0) 推荐(0) 编辑
摘要: 在Excel中,经常会碰到要制作的复杂表头,其中包含有斜线和文字(见下图画红圈处),许多初学者往往对此束手无策,还有的干脆胡乱调整,由于采取的方法不当,结果却是花了很长的时间却达不到理想的效果。 阅读全文
posted @ 2012-02-09 11:29 .NET快速开发框架 阅读(2200) 评论(0) 推荐(1) 编辑
摘要: 如何提取一串数字中的几位数字(字符) 如:050326 提取后3位数字 =RIGHT(A1,3) “3”是提取3位,如果改“4”,则提取4位。 =RIGHT(A3,LEN(A3)-3) =MID(A3,4,3) =REPLACE(A3,1,3,"") 提取中间的4位数字,“5032” =MID(A1,2,4) =MID(A3,(LEN(A3)-4)/2+1,4) 要提取 050324 中的 502 怎么提取?即:第二、三两位和第五位数字 =MID(A3,2,2)&MID(A3,5,1) =MID(A3,2,1)&MID(A3,3,1)&MID(A3,5,1) 阅读全文
posted @ 2012-02-09 11:29 .NET快速开发框架 阅读(5671) 评论(0) 推荐(0) 编辑
摘要: 复制粘贴中回车键的妙用 1、先选要复制的目标单元格,复制后,直接选要粘贴的单元格,回车OK; 2、先选要复制的目标单元格,复制后,选定要粘贴的区域,回车OK; 3、先选要复制的目标单元格,复制后,选定要粘贴的不连续单元格,回车OK。 一次选中批注单元格   按下Ctrl+Shift+O (字母 O)组合键,可以一次性选定所有带批注的单元格。 一次在所有单位格中插入批注 1选择你已经做批注的单元格 2复制 3选择你要做相同批注的所有单元格 4编辑〉选择性粘贴〉批注 阅读全文
posted @ 2012-02-09 11:28 .NET快速开发框架 阅读(3376) 评论(0) 推荐(0) 编辑
摘要: Excel打印中如何不显示错误值符号 在“页面设置”-“工作表”-“错误单元格打印为”中, 将“显示值”改为“空白”即可。 对于一些不可打印的字符的处理 对于一些不可打印的字符(在Excel显示中类似空格),直接用替换方法不容易去掉。 可以这么做: =SUBSTITUTE(CLEAN(A1)," ","") 用那个函数可将个位数前面的零值显示出来? 如果单元格A1的内容是5,在A2用那个函数可将A1的内容变为05? (Text或value也可,总之个位数的零也显示,例:5变05,15则15) 可以用=TEXT(A2,"00") 或将单元格格式自定义为00 阅读全文
posted @ 2012-02-09 11:27 .NET快速开发框架 阅读(7143) 评论(0) 推荐(0) 编辑
摘要: EXCEL中如何删除*号 在录入帐号是录入了*号,如何删除。 可以用函数 SUBSTITUTE(a1,"*","") 查找~*,替换为空。 将字符串中的星号“*”替换为其它字符 在查找栏输入~* 替换为“-”即可。 阅读全文
posted @ 2012-02-09 11:26 .NET快速开发框架 阅读(11419) 评论(0) 推荐(0) 编辑
摘要: 一个具有11项汇总方式的函数SUBTOTAL =SUBTOTAL(9,$B$2:B2) 在数据筛选求和上有意想不到的功能,11项功能为:1、求平均数,2、求计数,3、求计数值(自动筛选序列)4、求最大值,5、求最小值,6、求乘积,7、求总体标准偏差,8、求标准偏差、9、求和,10、求方差,11、求总体方差。 自动排序 =SUBTOTAL(3,$B$2:B2)*1 =IF(A2A1,1,N(C1)+1) 阅读全文
posted @ 2012-02-08 16:37 .NET快速开发框架 阅读(47616) 评论(0) 推荐(1) 编辑
摘要:求最精简的自动排名公式 =RANK(E2,$E$2:$E$21) =RANK(A2,$A$2:$A$9,0) =RANK(A2,$A$2:$A$10)+COUNTIF($A$2:$A2,$A2)-1(如果数据列中数值有相同) =RANK(F10,$F10:$Q10)+COUNTIF($F10:F10,F10)-1 =INDEX(A:A,1/MOD(LARGE(E$2:E$21+1/ROW($2:$21),ROW(1:1)),1)) =LOOKUP(1,0/(($F$2:$F$21=A27)*(COUNTIF(D$26:D26,$A$2:$A$21)=0)),$A$2:$A$21)=INDIRECT("A"&RIGHT(LARGE(($E$2:$E$21*100+ROW($A$2:$A$21)),ROW(A1)),2)) =RANK(C2,OFFSET($C$1,MA 阅读全文
posted @ 2012-02-08 16:37 .NET快速开发框架 阅读(12842) 评论(0) 推荐(1) 编辑
摘要: 自动显示当前日期公式 =YEAR(NOW()) 当前年 =MONTH(NOW()) 当前月 =DAY((NOW())) 当前日 如何在单元格中自动填入当前日期 Ctrl+; 如何判断某日是否星期天 =WEEKDAY(A2,2) =TEXT(A1,"aaaa") =MOD(A1,7)2 阅读全文
posted @ 2012-02-08 16:36 .NET快速开发框架 阅读(66890) 评论(0) 推荐(2) 编辑
摘要: 根据出生年月计算年龄 =DATEDIF(A1,TODAY(),"y") =DATEDIF(A1,TODAY(),"y")&"周岁" =DATEDIF(A1,NOW(),"y") 根据出生年月推算生肖 中国人有12生肖,属什么可以推算出来。即用诞生年份除以12,再用除不尽的余数对照如下:0→猴,1→鸡,2→狗,3→猪,4→鼠,5→牛,6→虎,7→兔,8→龙,9→蛇,10→马,11→羊例如:XXX出生于1921年,即用1921年除以12,商得数为160,余数为1,对照上面得知余数1对应生肖是鸡,XXX就属鸡。 =MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(YEAR(A2),12)+1,1) (2007) 阅读全文
posted @ 2012-02-08 16:35 .NET快速开发框架 阅读(24575) 评论(0) 推荐(1) 编辑
摘要: 从身份证号码中提取出生年月日 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1 =IF(A2"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,) 显示格式均为yyyy-m-d。(最简单的公式,把单元格设置为日期格式) =IF(LEN(A2)=15,"19"&MID(A2,7,2)&"-"&MID(A2,9,2)&"-"&MID(A2,11,2),MID(A2,7,4)&"-"&MID(A2,11,2)&"-"&MID(A2,13,2)) 显示格式为yyyy-mm-dd。(如果要求为“1995/03/29”格式的话,将”-” 换成”/”即可) =I 阅读全文
posted @ 2012-02-07 17:49 .NET快速开发框架 阅读(10315) 评论(0) 推荐(4) 编辑

点击右上角即可分享
微信分享提示