文章分类 - Excel
Excel常用函数公式及技巧
摘要:
谈谈Excel输入的技巧
在Excel工作表的单元格中,可以使用两种最基本的数据格式:常数和公式。常数是指文字、数字、日期和时间等数据,还可以包括逻辑值和错误值,每种数据都有它特定的格式和输入方法,为了使用户对输入数据有一个明确的认识,有必要来介绍一下在Excel中输入各种类型数据的方法和技巧。
【1】输入文本
Excel单元格中的文本包括任何中西文文字或字母以及数字、空格和非数字字符的组合,每个单元格中最多可容纳32000个字符数。虽然在Excel中输入文本和在其它应用程序中没有什么本质区别,但是还是有一些差异,比如我们在Word、PowerPoint的表格中,当在单元格中输入文本后,按回车键表示一个段落的结束,光标会自动移到本单元格中下一段落的开头,在Excel的单元格中输入文本时,按一下回车键却表示结束当前单元格的输入,光标会自动移到当前单元格的下一个单元
阅读全文

摘要:
查找顺序公式
=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))
用数组公式
阅读全文

摘要:
求平均值
如在列中有一组数字:10、7、9、27、2
=AVERAGE(A2:A6) 上面数字的平均值为11
行公式=AVERAGE(B2:D2)
如何实现求平均值时只对不等于零的数求均值?
=AVERAGE (IF(A1:A50,A1:A5))
阅读全文

摘要:
取整数函数
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)
阅读全文

摘要:
统计数值大于等于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"))
阅读全文

摘要:
个调税公式
=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的小数点第一位
阅读全文

摘要:
常用的自定义格式
单元格属性自定义中的“G/通用格式“和”@”作用有什么不同?
设定成“G/通用格式“的储存格,你输入数字1..9它自动认定为数字,你输入文字a..z它自动认定为文字,你输入数字1/2它会自动转成日期。
设定成“@“的储存格,不管你输入数字1..9、文字a..z、1/2,它一律认定为文字。
文字与数字的不同在於数字会呈现在储存格的右边,文字会呈现在储存格的左边。
常用的自定义格式拿出来大家分享
阅读全文

摘要:
通过条件格式将小计和总计的行设为不同的颜色
答:输入=RIGHT(RC,1)="计";设定字体、边框、图案;确定。
如何实现这样的条件格式
有一个excel表单,若当其中一栏数值超过某一值,使整个一行底色为某一颜色(比如红色),用条件格式不能实现
注意公式为=$A1100,而不是=A1100
先选定整行再设置条件格式...
列标"A"用绝对引用, 行标"1"用相对引用, 用"格式刷"刷下去...
阅读全文

摘要:
数字如何拆分
我有一组数据,如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
阅读全文

摘要:
在Excel中,经常会碰到要制作的复杂表头,其中包含有斜线和文字(见下图画红圈处),许多初学者往往对此束手无策,还有的干脆胡乱调整,由于采取的方法不当,结果却是花了很长的时间却达不到理想的效果。
阅读全文

摘要:
如何提取一串数字中的几位数字(字符)
如: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)
阅读全文

摘要:
复制粘贴中回车键的妙用
1、先选要复制的目标单元格,复制后,直接选要粘贴的单元格,回车OK;
2、先选要复制的目标单元格,复制后,选定要粘贴的区域,回车OK;
3、先选要复制的目标单元格,复制后,选定要粘贴的不连续单元格,回车OK。
一次选中批注单元格
按下Ctrl+Shift+O (字母 O)组合键,可以一次性选定所有带批注的单元格。
一次在所有单位格中插入批注
1选择你已经做批注的单元格
2复制
3选择你要做相同批注的所有单元格
4编辑〉选择性粘贴〉批注
阅读全文

摘要:
Excel打印中如何不显示错误值符号
在“页面设置”-“工作表”-“错误单元格打印为”中,
将“显示值”改为“空白”即可。
对于一些不可打印的字符的处理
对于一些不可打印的字符(在Excel显示中类似空格),直接用替换方法不容易去掉。
可以这么做:
=SUBSTITUTE(CLEAN(A1)," ","")
用那个函数可将个位数前面的零值显示出来?
如果单元格A1的内容是5,在A2用那个函数可将A1的内容变为05?
(Text或value也可,总之个位数的零也显示,例:5变05,15则15)
可以用=TEXT(A2,"00")
或将单元格格式自定义为00
阅读全文

摘要:
EXCEL中如何删除*号
在录入帐号是录入了*号,如何删除。
可以用函数 SUBSTITUTE(a1,"*","")
查找~*,替换为空。
将字符串中的星号“*”替换为其它字符
在查找栏输入~*
替换为“-”即可。
阅读全文

摘要:
一个具有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)
阅读全文

摘要:
求最精简的自动排名公式
=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
阅读全文

摘要:
自动显示当前日期公式
=YEAR(NOW()) 当前年
=MONTH(NOW()) 当前月
=DAY((NOW())) 当前日
如何在单元格中自动填入当前日期
Ctrl+;
如何判断某日是否星期天
=WEEKDAY(A2,2)
=TEXT(A1,"aaaa")
=MOD(A1,7)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)
阅读全文

摘要:
从身份证号码中提取出生年月日
=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
阅读全文
