服务指南
您当前的位置:中心首页>服务指南

常用函数公式及技巧搜集

发布时间:2016-01-14      信息来源:      阅读次数:0

【身份证信息提取】
从身份证号码中提取出生年月日
=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”格式的话,将”-”换成”/”即可)
=IF(D4="","",IF(LEN(D4)=15,TEXT(("19"&MID(D4,7,6)),"00000000
"),IF(LEN(D4)=18,TEXT(MID(D4,7,8),"00000000"))))
显示格式为yyyymmdd日。(如果将公式中“00000000日”改成“0000-00-00,则显示格式
yyyy-mm-dd
=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),"19"&MID(A1:A2,7,6))
显示格式为yyyymmdd
=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0
=IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2))
=MID(A1,7,4)&""&MID(A1,11,2)&""&MID(A1,13,2)&""
=IF(A1<>"",TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))
从身份证号码中提取出性别
=IF(MOD(MID(A1,15,3),2),"","")(最简单公式)
=IF(MOD(RIGHT(LEFT(A1,17)),2),"","")
=IF(A2<>””,IF(MOD(RIGHT(LEFT(A2,17)),2),””,””),)
=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,"","")
从身份证号码中进行年龄判断
=IF(A3<>””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”),T
ODAY(),”Y”),)
=DATEDIFA1TODAY(),“Y”
(以上公式会判断是否已过生日而自动增减一岁)
=YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900
=YEAR(TODAY())-IF(LEN(A1)=15,"19"&MID(A1,7,2),MID(A1,7,4))
=YEAR(TODAY())-VALUE(MID(B1,7,4))&""
=YEAR(TODAY())-IF(MID(B1,18,1)="",CONCATENATE("19",MID(B1,7,2)),MID(B1,7,4))
按身份证号号码计算至今天年龄
=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"y")
20061031日为基准日,按按身份证计算年龄(周岁)的公式
2
=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1,"2006-10-31","y")
按身份证号分男女年龄段按身份证号分男女年龄段,身份证号在K列,年龄段在J列(身份证号为18位)男性16周岁以下为1男性16周岁(含16周岁)以上至50周岁为2男性50周岁(含50周岁)以上至60周岁为3男性60周岁(含60周岁)以上为4女性16周岁以下为1女性16周岁(含16周岁)以上至45周岁为2女性45周岁(含45周岁)以上至55周岁为3女性55周岁(含55周岁)以上为4
=MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,2)),TODAY(),"y"),{0,16
,50,60}-{0,0,5,5}*ISEVEN(MID(K1,17,1)))
=SUM(--(DATEDIF(MID(K1,7,4)&"/"&MID(K1,11,2)&"/"&MID(K1,13,2),TODAY(),"y")>
={0,16,45,55}+{0,0,5,5}*MOD(MID(K1,17,1),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
如何求出一个人到某指定日期的周岁?
=DATEDIF(起始日期,结束日期,"Y")
计算距离退休年龄的公式
=IF(E2="","",IF(E2>=V2,"已经退休","距离退休还有
"&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),"Y")&"
"&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),"YM")&"个月
"&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),"Md")&""))其中E2为年龄(可用身份证号码的公式生成);
V2为法定退休年龄(男60,女50)公式为:=IF(D2="","",IF(D2="",60,50))
D2为男或女(可用身份证号码的公式生成)U2为出生年月日(可用身份证号码的公式生成)。
3
求工齡
=DATEDIF(B2,TODAY(),"y")
=DATEDIF(B2,TODAY(),"ym")
=DATEDIF(B2,TODAY(),"md")
=DATEDIF(B2,TODAY(),"y")&""&DATEDIF(B2,TODAY(),"ym")&"
"&DATEDIF(B2,TODAY(),"md")&""
计算工龄
=DATEDIF(C6,C8,"y")求两日期间的年数
=DATEDIF(C6,C8,"ym")求两日期间除去整年数剩余的月数
=DATEDIF(C6,C8,"m")求两日期间的总月数如果只需要算出周年的话,可以用=datedif("1978-8","2006-5","Y")
年龄及工龄计算有出生年月如何求年龄?有工作时间如何求工龄?(求出的结果为多少年另几个月,如:0303的形式,即3年零
3个月)。
a1是出生年月或工作时间:
=datedif(a1,today(),"y")
=text(datedif(a1,today(),"y"),"00")&text(datedif(a1,today(),"m"),"00")[B2]=1964-9-1:
=TEXT(DATEDIF(B2,TODAY(),"y"),"00")&TEXT(MOD(DATEDIF(B2,TODAY(),"m"),12)
,"00")'显示4009
=TEXT(DATEDIF(B2,TODAY(),"y"),"00
")&TEXT(MOD(DATEDIF(B2,TODAY(),"m"),12),"00")'显示4009如果你找不到DATEDIF函数,也可以不用DATEDIF函数,[B2]=1964-9-1:
=TEXT(RIGHT(YEAR(NOW()-B2),2),"00")&TEXT(MOD(MONTH(NOW()-B2)-1,12),"00"
)'显示4009
=TEXT(RIGHT(YEAR(NOW()-B2),2)&""&MOD(MONTH(NOW()-B2)-1,12)&"个月
","")'显示4009个月
自动算出工龄日期格式为(yyyy.mm.dd)能否用:(yyyy.mm.dd)这种格式来计算出工龄有多长呢~以前用这样一段(=TEXT(RIGHT(YEAR(NOW()-A1),2)&"
"&MOD(MONTH(NOW()-A1)-1,12)&"个月",""))。但这种方法只能用:(yyyy-mm-dd)这样的日期格式才能实现!
你不妨把“.”替换成“-”,不就行了吗,再说后者是日期的一种标准格式,
=TEXT(RIGHT(YEAR(NOW()-SUBSTITUTE(A1,".","-")),2)&"
"&MOD(MONTH(NOW()-SUBSTITUTE(A1,".","-"))-1,12)&"个月","")
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
4
【时间和日期应用】
自动显示当前日期公式
=YEAR(NOW())当前年
=MONTH(NOW())当前月
=DAY((NOW()))当前日
如何在单元格中自动填入当前日期
Ctrl+;
如何判断某日是否星期天
=WEEKDAY(A2,2)
=TEXT(A1,"aaaa")
=MOD(A1,7)<2
某个日期是星期几比如2007年2月9日,在一单元格内显示星期几。
=TEXT(A1,"aaa")(五)
=TEXT(A1,"aaaa")(星期五)
=TEXT(A1,"ddd")Fri
=TEXT(A1,"dddd")Friday
什么函数可以显示当前星期如:星期二10:41:56
=TEXT(NOW(),"aaaahh:mm:ss")
求ᴀ月天数A12006-8-4求ᴀ月天数
A1=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))也有更簡便的公式:=DAY(EOMONTH(NOW(),0))需加載分析工具箱。当前月天数:
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-DATE(YEAR(TODAY()),MONTH(TOD
AY()),1)用公式算出除去当月星期六、星期日以外的天数
=SUMPRODUCT(--(MOD(ROW(INDIRECT(DATE(YEAR(NOW()),MONTH(NOW()),1)&"
:"&DATE(YEAR(NOW()),MONTH(NOW())+1,0))),7)>1))
显示昨天的日期每天需要单元格内显示昨天的日期,但双休日除外。例如,今天是73号的话,就显示72号,如果是79号,就显示76号。
=IF(TEXT(TODAY(),"AAA")="",TODAY()-3,IF(TEXT(TODAY(),"AAA")="
",TODAY()-2,TODAY()-1))
=IF(TEXT(TODAY(),"AAA")="",TODAY()-3,TODAY()-1)
5
关于取日期怎么设个公式使A1在年月日向后推5,变成2011-7-15
=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1))
=EDATE(A1,12*5)
如何对日期进行上、中、下旬区分
=LOOKUP(DAY(A1),{0,11,21,31},{"上旬","中旬","下旬","下旬"})
如何获取一个月的最大天数"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1为"2001-03-01
日期格式转换公式
“01/12/2005”转换成“20050112”格式
RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2)
YEAR($A2)&TEXT(MONTH($A2),"00")&TEXT(DAY($A2),"00")该公式不用设置数据有效性,但要设置储存格格式。也可以用下列两方法:
1、先转换成文ᴀ,然后再用字符处理函数。
2[数据]-[分列][日期]-[MDY]
将“20059月”转换成“200509”格式先用公式:=text(a1,"yyyymm")+0然后将单元格格式为常规。
将“2005-8-6”格式转换为“20050806”格式用公式:=TEXT(A1,"YYYYMMDD")反之,将20050806转为日期2005-8-6格式,可用公式:
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))另四种公式:
text(a1,"0000-00-00")显示:2005-08-06
--TEXT(A1,"#-00-00"),把单元格设置为日期格式显示:2005-8-6
TEXT(20050806,"0000-00-00")*1,单元格设置日期型显示:2005-8-6
VALUE(LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,2))显示:2005-8-6
将“20060501”转换为“2006-05-01”格式
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
将“199306”转换为“1993-6公式1=LEFT(A3,4)&"-"&RIGHT(A3,2)*1公式2=--TEXT(A3*100+1,"#-00-00")公式2需要设置单元格格式,自定义:e-m公式3=TEXT(TEXT(A3&"01","0000-00-00"),"e-m")
198405转换成1984.05
6一、查找—1984,替换—1984.二、如果全部是年月的话,我个人建议,1、采取辅助=mid(xxxxxx,1,4)&"."&right(xxxxxx,2)2、选中这列,用数据中的分列。然后……………
三、单元格格式/数字/自定义,类型下面输入:####"."##
将文ᴀ“2004.01.02”转换为日期格式:2004-1-2
=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))
2005-8-6转换为200586日格式
=TEXT(A1,"yyyy""""m""""d"""";@")
22怎样转换成22日?转成当年当月的日子公式为:=date(year(now()),month(now()),22)
将“20065月”转换成“200605月”公式为:=TEXT(A8,"yyyy""""mm"""";@")也可以这样处理:选中单元格,设置单元格公式-数字-自定义,将yyyy“年”m“月”改为:yyyy“年”mm“月”,即可。但这方法打印出来显示为:2006/5/
将“1968612日”转换为“1968/6/12”格式
=YEAR(A1)&"/"&MONTH(A1)&"/"&DAY(A1)显示:1968/6/12
=TEXT(A1,"yyyy/mm/dd")显示:1968/06/12
将“1968612日”转换为“1968-6-12”格式
=YEAR(A1)&"-"&MONTH(A1)&"-"&DAY(A1)显示:1968-6-12
=TEXT(A1,"yyyy-mm-dd")显示:1968-06-12
1993-12-28的日期格式转换成199312
=CONCATENATE(YEAR(A1),"",MONTH(A1),"")
=YEAR(A1)&""&MONTH(A1)&""也可以自定义格式[$-404]e""m""
将“1978-5-2”包含年月日的日期转换成“197805”只有年月的格式
year(A1)&text(month(A1),"00")
要将“99.08.15”格式转换成“1999.08.15”如何做选中列,数据菜单中选分列,分列过程中格式日期YMD”,结束。
要保持2005/8/6格式当输入2005/8/6后系统自动变成2005-8-6,要保持2005/8/6格式,可以使用强制文ᴀ(前面加'号)或使用公式=TEXT(A1,"YYYY/MM/DD")。也可以用另一种公式:
=IF(ISERROR(TEXT(A1,"yyyy/mm/dd")),TEXT(A1,"0000!/00!/00"),TEXT(A1,"yyyy/mm/d
d"))
7
将“二○○三年十二月二十五日”转为“2003-12-25”格式
1、可以用数组公式将中文日期转化为日期系列数=14610+MATCH(SUBSTITUTE(A3,"
",""),TEXT(ROW($14611:$55153),"[DBNum1]yyyymd"),0)该公式速度较慢。
2、改进后的公式,速度要快的多:
=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),"[DBNum1]0000"),0),MO
NTH(MATCH(SUBSTITUTE(MID(A7,6,7),"",""),TEXT(ROW($1:$366),"[DBNum1]m
d"),0)),DAY(MATCH(SUBSTITUTE(MID(A7,6,7),"","
"),TEXT(ROW($1:$366),"[DBNum1]md"),0)))要设置为1900年的日期格式。
日期格式转换A列是月份数为8B列是日期数为18,如何在C列显示“818日”
=A1&""&B1&""反之,要将C列的“818日”直接分别到DE列,显示月份和日期,月数份=LEFT(C5,FIND("",C5)-1)日期数=MID(C5,FIND("",C5)+1,FIND("",C5)-FIND("",C5)-1)也可分别用公式:
=month(--c5)
=day(--c5)
日期格式转换问题输入的日期是:04-07-26.与另一格的"001"合并,合并出来是:040726001.
=TEXT(A1,"YYMMDD")&"001"
要想自动取得“编制日期:XXXX年X月X日”可在该单元格输入="编制日期:"&TEXT(TODAY(),"yyyymd")
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【排名及排序筛选】
一个具有1111项汇总方式的函数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(A2<>A1,1,N(C1)+1)
按奇偶数排序
8我想请教怎样按奇数顺序然后再按偶数顺序排序
=IF(MOD(A1,2),0,1)
=IF(ROW()>50,(ROW()*2)-100,(ROW()*2)-1)
=ROW()*2-1-(ROW()>50)*99
自动生成序号比如在第二列中输入内容回车后第一列的下一行自动生成序列号。
=IF(B2<>"",A2+1,"")
如何自动标示A栏中的数字大小排序?
=RANK(A1,$A$1:$A$5)
=RANK(A1,A:A)
如何设置自动排序A列自动变成从小到大排列
B=SMALL(A$2:A$28,ROW(1:1))
A列自动变成从大到小排列
B=LARGE(A$2:A$28,ROW(1:1))
重复数据得到唯一的排位序列想得到数据的出现总数吗({1,2,2,3,4,4,5}数据的出现总数为5)?解答:不需要插列,不需要很多的函数就行了.
=RANK(B3,B$3:B$12)+COUNTIF(B$3:B3,B3)-1
按字符数量排序制作歌曲清单时,习惯按字符数量来排列分类,但是EXCEL并不能直接按字数排序。需要先计算出每首歌曲的字数,然后再进行排序。AB列分别为“歌手”和“歌名”,在C1输入“字数”,在C2输入公式:
LEN(B2)下拖,单击C2,单击工具栏上的“升序排列”即可,删除C列。
排序字母与数字的混合内容日常使用中,表格经常会有包含字母和数字混合的数据,对此类数据排序时,通常是先比较字母的大小,再比较数字的大小,但EXCEL是按照对字符进行逐位比较来排序的,如下表:A7排在第5位,而不是第1位。排序结果无法令人满意。A1A1222A293A3174A435A76B207B38C1449C5
910C33如果希望EXCEL改变排序的规则,需要将数据做一些改变。B1中输入公式:LEFT(A1,1)&RIGHT("000"&
RIGHT(A1,LEN(A1)-1),3)下拖单击B2,单击工具栏上的“升序排列”即可。
随机排序AB列分别为“歌手”和“歌名”,在C1输入“次序”,在C2输入公式:
RAND(),下拖,单击C2,单击工具栏上的“降序排列”即可对歌曲清单进行随机排序。
排序的问题我想要这样的排序:2001-2003
2004-2006
2007-2009
2010-2012;其实不是数据排序,应该是数据填充。
输入公式=LEFT(E3,4)+3&"-"&RIGHT(E3,4)+3即可。
怎样才能让数列自动加数怎样做才能让数列自动加数AA0001BB0001AA0002CC0001AA0003BB0002CC0002公式为=A1&"000"&COUNTIF(A$1:A1,A1)向下拖
=TEXT(COUNTIF(A$1:A1,A1),"!"&A1&"0000")否则数字超过9就错误了。
一个排序问题一个电子表格,格式是101102...999,1010110102...99901,1010101,1020201...9990101,问如何将它排列成101,10101,1010101,102,10201,1020101,...999,99901,9990101的形式。我在数字前加了个字母,比如"d"&"数字",然后用排序就可以把它们按你的需求排列了.后再把字母"d"去掉。
数字的自动排序,插入后不变?1赵一总经理2赵二副经理3赵三副经理4赵四技术员5赵五AB1A7A0072A29A0293A43A0434A122A1225A317A3176B3B0037B20B0208C5C0059C33C03310C144C144
106赵六员工如上的一个表,如何实现当我把赵六这一整行(第6行)插入到上面的表中时,A列的序列号不变?最后的效果如下:1赵一总经理2赵二副经理3赵六员工4赵三副经理5赵四技术员6赵五
A1单元格输入公式=row(),往下拉,然后再插入。
=SUBTOTAL(3,$B$2:$B2)在A1中输入公式:“=if(b1="","",counta($b$1:b1)”后下拉复制至A列各行即可(“”不必输入)
根据规律的重复的姓名列产生自动序号
姓名序号
张三1
张三1
李四2
李四2
赵五3
赵五3
赵五3
王六4
王六4
=(A1<>A2)+N(B1)
=IF(A3=A2,B2,B2+1)姓名已排序:
B2=SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2))姓名᳾排序:
B2=IF(COUNTIF(A$2:A2,A2)>1,VLOOKUP(A2,A:B,2,0),SUMPRODUCT(1/COUNTIF(A$2
:A2,A$2:A2)))~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
排名的函数用排名函数来对成绩进行排名,用起来非常地方便。
=IF(ISERR(RANK(M3,M:M)),"",RANK(M3,M:M))
A列是成绩,B列是排名
=SUMPRODUCT((A$1:A$9>A1)/COUNTIF(A$1:A$9,A$1:A$9))+1
自动排名公式
=RANK(C3,$C$3:$C$12)
11
=RANK(A2,$A$2:$A$11,0)
=RANK(C2,$C$2:$C$65)+COUNTIF($C$2:C2,C2)-1
百分比排名的公式写法为:
=PERCENTRANK($C$3:$C$12,C3)
平均分及总分排名
=AVERAGE(B2:E2)
=RANK(F2,$F$2:$F$65536)
求名次排名统计成绩时遇到一个分别求班级和年级总分名次排名的问题,不晓得应该运用什么公式来实现。班级名次:
=SUMPRODUCT((BJ=A2)*(ZF>E2))+1年级名次:
=RANK(E2,ZF)公式下拖。
排名次根据总分值大小,只将姓名排序后,降序结果
=INDEX(A$2:A$6,RANK(D2,D$2:D$6))根据总分值大小,只将姓名排序后,升序
=INDEX(A$2:A$6,RANK(D2,D$2:D$6,1))
根据分数进行普通排名
=RANK(A2,$A$2:$A$12)
=RANK(A2,A$2:A$12)+COUNTIF(A$2:A2,A2)-1
=SUMPRODUCT(1*($E$3:$E$12>=E3))
=RANK(K3,$K$3:$K$26)
=RANK(A2,A$2:A$12)
=SUM((A$2:A$12>=A2)/COUNTIF(A$2:A$12,A$2:A$12))
=COUNTIF($K$3:$K$26,">"&K3)+1
=INDEX($A$2:$A$7,MATCH(LARGE($C$2:$C$7,ROW(A1)),$C$2:$C$7,0),1)
=SUMPRODUCT(($A$2:$A$12>A2)/COUNTIF($A$2:$A$12,$A$2:$A$12&""))+1
=RANK(D2,OFFSET($A$1,MATCH($A2,$A:$A,0)-1,3,COUNTIF($A:$A,$A2),1))
对于普通排名分数相同时,按顺序进行不重复排名
=RANK(K32,$K$32:$K$55)+COUNTIF($K$32:$K32,K32)-1
=COUNTIF($K$32:K32,K32)-1+COUNTIF($K$3:$K$26,">"&K32)+1
=SUMPRODUCT(1*(($E$3:$E$12+ROW($E$3:$E$12)/100>=($E3+ROW(E3)/100))))
=RANK(E3,$E$3:$E$12)+COUNTIF($E$3:E3,E3)-1
=SUMPRODUCT(1*(($E$3:$E$12+$B$3:$B$12/100)>=(E3+B3/100)))
依分数比高低名次成绩排名
12
=RANK($E3,$E$3:$E$22)內建方式排名
=SUMPRODUCT(1*($E$3:$E$12>=E3))一般方式排名
=RANK(E3,$E$3:$E$22)+SUM(IF($E$3:$E$22>E3,1/COUNTIF($E$3:$E$22,$E$3:$E$22)
,0))-COUNTIF($E$3:$E$22,">"&E3)}一般方式排名
=RANK(E3,$E$3:$E$12)+COUNTIF($E$3:E3,E3)-1不重复排名
=SUMPRODUCT(1*(($E$3:$E$12+ROW($E$3:$E$12)/100>=($E3+ROW(E3)/100))))
=SUMPRODUCT(1*(($E$3:$E$12+$B$3:$B$12/100)>=(E3+B3/100)))不重复排名
=SUMPRODUCT(1*(($E$3:$E$12+$B$3:$B$12/100+$C$3:$C$12/10000)>=(E3+B3/100+C3/1
0000)))不重复排名
=RANK($E3,$E$3:$E$22,1)倒排序
美国式排名
=RANK(K247,$K$247:$K$270)
=RANK(B1,$B1:$H1)
中国式排名
=RANK(B2,$B$2:$B$21,0)
=RANK(B1,$B1:$H1)+COUNTIF($B$1:B1,B1)-1
=SUM(IF($A$1:$E$1>=A1,1/COUNTIF($A$1:$E$1,$A$1:$E$1),""))
=SUMPRODUCT(($B$2:$B$21>=B2)/COUNTIF($B$2:B$21,B$2:B$21))
=SUMPRODUCT((B$3:B$21>B3)*(1/COUNTIF($B$3:$B$21,$B$3:$B$21)))+1(升序)
=SUMPRODUCT((B$3:B$21<B3)*(1/COUNTIF($B$3:$B$21,$B$3:$B$21)))+1(降序)
=SUM(--(IF(FREQUENCY(B$2:B$21,B$2:B$21),B$2:B$21>B2)))+1
=SUM(IF($B$3:$B$21<=B3,"",1/(COUNTIF($B$3:B$21,B$3:B$21))))+1(升序)=SUM(IF($B$3:$B$21<=B3,1/(COUNTIF($B$3:B$21,B$3:B$21)),""))}(降序)=SUM(IF($B$2:$B$21>B2,1/COUNTIF($B$2:B$21,B$2:B$21)))+1=SUM(IF($A$1:$E$1>=A1,1/COUNTIF($A$1:$E$1,$A$1:$E$1),""))
=SUM(($B$2:$B$21>B2)*(MATCH($B$2:B$21,B$2:B$21,)=ROW($1:$20)))+1=SUM(IF($B$1:$H$1<=B1,"",1/(COUNTIF($B$1:$H$1,$B$1:$H$1))))+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)=IN
DIRECT("A"&RIGHT(LARGE(($E$2:$E$21*100+ROW($A$2:$A$21)),ROW(A1)),2))
=RANK(C2,OFFSET($C$1,MATCH(E2,$E$2:$E$768,),,COUNTIF($E$2:$E$768,E2)))数组公式=INDEX(A:A,MOD(LARGE(E$2:E$21*100+ROW($2:$21),ROW(1:1)),100))
=OFFSET($A$1,RIGHT(LARGE($E$2:$E$21*1000+ROW($E$2:$E$21),ROW()-25),3)-1,,)
=OFFSET($A$1,RIGHT(LARGE(($E$2:$E$21*100+ROW($A$1:$A$20)),ROW(A3)),2),)
=TEXT(SUMPRODUCT(($E$2:$E$21>=E2)/COUNTIF($E$2:$E$21,$E$2:$E$21)),"
13
[DBNUM1]G/通用格式名")
排序后排名
=SUM(IF($B$2:$B$15>=B2,1/COUNTIF($B$2:$B$15,$B$2:$B$15)))
=SUMPRODUCT((B$2:B$15>=B2)/COUNTIF(B$2:B$15,B$2:B$15))
位次排名
=IF($B2:$O2>=0,RANK($B2:$O2,$B2:$O2,0),)
根据双列成绩进行共同排名
=RANK(C345,($C$345:$C$356,$H$345:$H$356))
在双列间排名
=RANK(B2,($B$2:$B$26,$E$2:$E$16))
等次排名由大到小排名
=RANK(B3,$B$3:$B$12)
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25>B16))+1由小到大排名
=RANK(B3,$B$3:$B$12,1)
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25<B16))+1
不等次排名(行小排先)由大到小
=RANK(B3,$B$3:$B$12)+COUNTIF($B$3:B3,B3)-1
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000>B16-RO
W(B16)/10000))+1由小到大
=RANK(B3,$B$3:$B$12,1)+COUNTIF($B$3:B3,B3)-1
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25+ROW($B$16:$B$25)/10000<B16+RO
W(B16)/10000))+1
不等次排名(行大排先)由大到小
=COUNT($B$3:$B$12)-RANK(B3,$B$3:$B$12,1)-COUNTIF($B$3:B3,B3)+2
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25+ROW($B$16:$B$25)/10000>B16+RO
W(B16)/10000))+1由小到大
=COUNT($B$3:$B$12)-RANK(B3,$B$3:$B$12)-COUNTIF($B$3:B3,B3)+2
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000<B16-RO
W(B16)/10000))+1
顺次排名
14由大到小
=SUMPRODUCT((B$3:B$12>B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)))+1
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25>B16)/COUNTIF($K$16:$K$25,$K$16
:$K$25))+1由小到大
=SUMPRODUCT((B$3:B$12<B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)))+1
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25<B16)/COUNTIF($K$16:$K$25,$K$16
:$K$25))+1
有并列排名
=RANK(B2,$B$2:$B$20)
=SUMPRODUCT(1*($B$3:$B$21>B3))+1
=COUNTIF($B$3:$B$21,">"&B3)+1=SUM(IF($B$3:$B$21>B3,1,0))+1
=19-FREQUENCY($B$3:$B$21,B3)+1
=SUMPRODUCT(($B$2:$B$20>=B2)/COUNTIF($B$2:$B$20,$B$2:$B$20))
无并列排名
=RANK(B3,$B$3:$B$21)+COUNTIF($B$3:$B3,B3)-1
=SUMPRODUCT((B3-ROW()/1000<$B$3:$B$21-ROW($B$3:$B$21)/1000)*1)+1
=19-FREQUENCY($B$3:$B$21-ROW($B$3:$B$21)/1000,B3-ROW()/1000)+1=SUM(IF($B$3:$B$21-ROW($B$3:$B$21)/1000>B3-ROW()/1000,1,0))+1
有并列分段排名
=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21>C3))+1
=19-FREQUENCY(($A$3:$A$21=A3)*($C$3:$C$21),C3)+1
=MATCH(C3,LARGE(OFFSET($C$2,IF($A$3:$A$21=A3,ROW($A$3:$A$21)-2),),ROW(I
NDIRECT("1:"&COUNTIF($A$3:$A$21,A3)))),0)=MATCH(C3,LARGE(IF($A$3:$A$21=A3,$C$3:$C$21),ROW(INDIRECT("1:"&COUNT
IF($A$3:$A$21,A3)))),0)
=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21>C3)/COUNTIF($N$3:$N$21,$N$3:$N$
21))+1}(需辅助列)
无并列分段排名
=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21-ROW($C$3:$C$21)/10000>C3-ROW(C
3)/10000))+1
=19-FREQUENCY(($A$3:$A$21=A3)*($C$3:$C$21-ROW($C$3:$C$21)/1000),C3-ROW()/10
00)+1
成绩排名
序号姓名语文数学英语
1杨增海135136146
2郭爱玲138137141
15
3华志锋134138141
4袁文飞134143135能否用一个公式直接找出所用考生中语文成绩中第100名的成绩是多少?
=LARGE(C2:C417,100)
=PERCENTILE(C2:C417,(416-100)/416)
=PERCENTILE($C$2:$C$417,(COUNTA($C$2:$C$417)-100)/COUNTA($C$2:$C$417))能否用一个公式直接找出所用考生中语文成绩中按与考人数的35%切线中位于第35%成绩是多少?升冪
=SMALL(C2:C417,416*0.35)
=PERCENTILE($C$2:$C$417,0.35)降冪
=LARGE(C2:C417,416*0.35)
=PERCENTILE($C$2:$C$417,1-0.35)
如何排名
1、对英语进行排名,缺考不计算在内。
2、对英语进行排名,缺考计算在内。英语英语排名429623721485485721544429缺考缺考458467缺考不计算在内
b2=IF(A2="缺考","",RANK(A2,$A$2:$A$13))然后按照B列排序缺考计算在内
=IF(A2="缺考",COUNTIF($A$2:$A$13,">=0")+1,RANK(A2,$A$2:$A$13))
=IF(A2="缺考",COUNT($A$2:$A$13)+1,RANK(A2,$A$2:$A$13,0))
数据排名(隔几行排名)
=IF(A2="","",RANK(A2,$A$2:$A$11,0))如果隔几行排名,如下表,第五行、第九行和第十二行不参与排名。单位数据排名A18A57
16A66小计12B84B93B75小计24C181C112小计29
=IF(A2="小计","",RANK(B2,(B$2:B$4,B$6:B$8,B$10:B$11)))下拉
根据分数进行倒排名
=RANK($E3,$E$3:$E$22,1)
=RANK(K60,$K$60:$K$83,1)
=COUNTIF($K$60:$K$83,"<"&K60)+1
倒数排名函数是什么
1为正排序,0为逆排序。倒数排名=RANK(A2,$A$2:$A$5,0)正数排名=RANK(A2,$A$2:$A$5,1)
如何实现每日各车间产量的排名
=RANK(C2,OFFSET($C$2:$C$5,MATCH(A2,$A$2:$A$1000,0)-1,,,))
=RANK(C2,OFFSET($C$2:$C$5,MATCH(A2,$A$2:$A$33,0)-1,,,))
分数相同时按照一科的分数进行排名
=MATCH(K308*100+D308,LARGE($K$308:$K$331*100+$D$308:$D$331,ROW($K$308:
$K$331)-307),)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
筛选后自动产生序列号并汇总自动产生序列号:在A1输入以下公式,往下拖。
=SUBTOTAL(3,$B$2:B2)*1自动汇总,用以下公式:
=SUBTOTAL(9,$B$2:B2)说明:汇总时,不要在“全选”状态下进行,先“筛选”出某一单位,自动求和。然后再恢复到“全选”或者选择任何单位,就能自动汇总了(在“筛选”出某一单位进行求和时,一般表格会自动产生以上汇总公式)。其它:如同时要在其它单元格显示人数,在“全选”状态下,选定单元格,点“fx”(用sum”函数)再点击序列号最᳿尾数,即可。
如何筛选奇数行公式=MOD(A1,2)=1
17
函数筛选姓名如何把两列中只要包含AA+的人员筛选出来
=IF(ISNUMBER(FIND("A",C2))+ISNUMBER(FIND("A",B2))>0,"OK","")
名次筛选名次=RANK(K5,K$2:K$435)班名次=RANK(K6,OFFSET(K$2,MATCH(A6,A:A,)-2,,COUNTIF(A$1:A$500,A6)))
如何实现快速定位(筛选出不重复值)
=IF(COUNTIF($A$2:A2,A2)=1,A2,"")
=IF((COUNTIF($A$2:A2,A2)=1)=TRUE,A2,"")
=INDEX(A:A,SMALL(IF(MATCH(A$1:A$20,A$1:A$20,)=ROW($1:$20),ROW(A$1:A$20),6
5536),ROW()))&""(数组公式)
如何请在N列中列出A1:L9中每列都存在的数值
=IF(ROW()>SUM(--x),"",INDEX(A:A,SMALL(IF(x,ROW($A$1:$A$9)),ROW())))
自动为性别编号的问题有一个编码,5位,第1位,1为男,2为女,后面4位,代表他的编号,从0001-9999,如何达到下表:性别编码男10001男10002女20001男10003女20002男的也是从0001-9999女的也是从0001-9999如果你是已经输入了其它信息,仅仅为快速输入编码的话。用筛选可以实现吧。先以“男”为关键字进行排序,然后在第一个男的编码输入10001,下拉复制到最后一单即可。同理再以“女”排序。完成目标。用公式:
=IF(A2="",TEXT(COUNTIF(A$2:A2,A2),"10000"),TEXT(COUNTIF(A$2:A2,A2),"20000"))向下拖
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【文ᴀ与页面设置】
EXCEL中如何删除*号在录入帐号是录入了*号,如何删除。可以用函数SUBSTITUTE(a1,"*","")查找~*,替换为空。
18
将字符串中的星号“*”替换为其它字符
在查找栏输入~*~*
替换为“-”即可。
去空格函数如何删去单元格中的空格,如姓名前,中,后的空格,即单元格中是两个字的人名中间有一个空格,想删去有何方法。如:中国,改为:中国。1、用公式:=SUBSTITUTE(A2,"","")注:第一对双引号中有一空格。而第二个“”中是无空格的。2、利用查找-替换,一次性全部解决。“编辑”-“替换”(或Ctrl+H),在“查找”栏内输入一空格,“替换”什么也不输入(空白)。然后“全部替换”即可。3、有一个专门删除空格的函数:TRIM()在EXCEL编辑栏里,不管输中文还是英文只能输一个字节的空格,但如果字与字中间是两个字节的空格,那么TRIM()就不起作用了,它就不认为是一个空格,而是一个汉字,怎么去“TRIM”也没用。如:单元格A1中有中心是,如果用TRIM则变成中心,想将空格全去掉,只能用SUBSTITUDE()函数,多少空格都能去掉。
如何去掉字符和单元格里的空格89000795018900079501~19000788011900078802~
=SUBSTITUTE(B2,"~","")
怎样快速去除表中不同行和列的空格编辑-定位-定位条件-空值,可选中所有空单元格,再删除。
如何禁止输入空格Excel中如何通过编辑“有效数据”来禁止录入空格?烦请大侠们费心解答。解答:有效性公式。=COUNTIF(A1,"**")=0
(注:COUNTIF(A1,"**")在单元格有空格时结果为1,没有空格时结果为0如希望第一位不能输入空格:countif(a1,"*")=0如希望最后一位不能输入空格:countif(a1,"*")=0)
代替单元格中字符串单元格编号,开始位数,从开始位数算起第几位数,要用于代替的的字符串。
windows2000变成windows2K
=REPLACE(B2,8,3,"K")单元格编号,要代替掉的字符,要用作代替的字符,第几个。代替单元格B391中的全部TT,改为UU
EETTCCTTFF变成EEUUCCUUFF
=SUBSTITUTE(B394,"TT","UU")只代替单元格B391中的第一次出现的TT,改为UU
EETTCCTTFF变成EEUUCCTTFF
19
=SUBSTITUTE(B397,"TT","UU",1)
把单元格中的数字转变成为特定的字符格式函数中的第二个参数的双引号一定不能是中文格式的(不能用任意中文输入法输入的双引号。)实例:20000目的:变成带有美元符号的字符
10000变成带有人民币符号的字符
151581变成带有欧元符号的字符
1451451变成中文繁体的字符
15748415变成中文简体的字符操作步骤:=TEXT(B72,"$0.00")结果:$20000.00
=TEXT(B73,"¥0.00")10000.00
=TEXT(B74,"€0.00")€151581.00
=TEXT(B75,"[DBNum2]G/通用格式")壹佰肆拾伍万壹仟肆佰伍拾壹
=TEXT(B76,"[DBNum1]G/通用格式")一千五百七十四万八千四百一十五
把有六百多个单元格的一列,变成一页的多列有一张表,共有14页,但每页只有一列,如何把他们整合在一起,变成一页(按每页的顺序),如果使用剪切和粘贴的方式,那样太麻烦。
=INDIRECT("r"&(COLUMN()-3)*48+ROW()&"C1",0)复制到其他单元格
N列变M列公式归纳为
=OFFSET($A$1,INT(((ROW(A1)-12)*m+COLUMN(A1)-1)/n),MOD((ROW(A1)-1)*m+COL
UMN(A1)-1,n))
=OFFSET($A$1,INT(((ROW(A1)-1)*7+COLUMN(A1)-1)/4),MOD((ROW(A1)-1)*7+COLU
MN(A1)-1,4))四列变七列
=OFFSET($A$1,INT(((ROW()-20)*10+COLUMN()-1)/7),MOD((ROW()-20)*10+COLUMN()
-1,7))七列变十列
一列变四列
=OFFSET($A$1,ROW($A1)*4-COLUMNS(C:$F),)
=OFFSET($A$1,(ROW()-3)*4+MOD(COLUMN()-8,4),)
=OFFSET($A$1,ROW(A1)*4-4+MOD(COLUMN()-13,4),)
四列变一列
=OFFSET($F$1,INT(ROW(1:1)/4+3/4)-1,MOD(ROW()-1,4))
=OFFSET($F$1,INT((ROW(1:1)-1)/4),MOD(ROW()-1,4))
=OFFSET($F$1,ROUNDUP((ROW(1:1)/4),0)-1,MOD(ROW()-1,4))
=OFFSET($F$1,(ROW()-1)/4,MOD(ROW()-1,4))
重复四次填充
=TEXT(INT(ROW()/4+3/4),"00")
=IF(TRUNC((ROW()-1)/4,0)<9,"0"&TRUNC(ROW()/4-0.01,0)+1,TRUNC(ROW()/4-0.01,0)+
1)
20
=TEXT(ROUNDUP(ROW()/4,),"00")
=TEXT(ROW(2:2)/4,"00")
多行数据排成一列
a1b1c1d1e1f1g1h1i1
a2b2c2d2e2g2h2i2
a3c3d3g3h3i3
a4c4g4h4i4
A5c5g5h5
g6
=IF(ROW()>COUNTA($A$1:$I$10),"",INDEX($A$1:$I$10,MOD(SMALL(IF($A$1:$I$10
<>"",ROW($A$1:$I$10)+COLUMN($A$1:$I$10)*100000),ROW()),100000),INT(SMALL(IF(
$A$1:$I$10<>"",ROW($A$1:$I$10)+COLUMN($A$1:$I$10)*100000),ROW())/100000)))
将单元格一列分为多列如果有一列资料需要分为多列,只要先将此列选中,然后再选择“数据”→“分列”,此时会出现一个对话框,选“固定宽度”或“分隔符号”。如为前者则下一步后只要用鼠标轻点资料即可以按任意宽度进行分割了,如为后者则只要有明显的分隔符号即可,下一步后就可以自定义刚分的列的格式了,定好后就算完成了。步骤:
1、先确定1列的最适合的列宽,再将其宽度乘以分成列数,分列前的列宽=最适合的列宽×需分成的列数.
2、编辑—填充—内容重排。
3、数据—分列。
首写字母大写把单元格编号中的单词首写字母变成大写字母,其余字母变成小写。china-China
=PROPER(B160)
把单元格编号中的小写字母变成大写字母
lafayette148LAFAYETTE148
=UPPER(B1)
=LOWER(B1)(大写字母变成小写字母公式)
让姓名左右对齐姓名用字,有的是三个汉字,有的是两个汉字,打印出来很不美观,要使姓名用字是两个字的与三个字的左右对齐也有两种方法:方法一:格式设置法。选中我们已经删除完空格的姓名单元格,单击“格式→单元格”在打开的“单元格格式”对话框中的水平对齐方式中选择“分散对齐”选项,确定退出后即可使学生姓名用字左右对齐。方法二:函数公式法。利用Excel中的“IF”、“LEN”、“MID”三种函数组合可使姓名用字左右对齐。具体示例为:在C3单元格中输入公
a1
a2
a3
a4
A5
b1
21式:“=IF(LEN(B2)>=3,B2,(MID(B2,1,1)&&""&&MID(B2,2,1)))”,确定后利用填充柄将该公式进行复制即可。
数字居中而小数点又对齐可在小数点的任一边替无效的零加入空间,以便当格式设定为固定宽字型,小数点可以对齐。
格式-单元格-数字-自定义-???.???-确定请问:小数点后的“0”还有办法显示吗?比如:
2.0
12.001
格式-单元格-数字-自定义-???.0?-确定
计算指定单元格编号组中非空单元格的数量计算B252B262之间的非空单元格的数量。
=COUNTA(B252:B262)
比较两个单元格内容是否一致
74P12514874P125148比较单元格B53C53中的内容是否一致。假如内容一致,那么返回值为TRUE,不一致的话,返回值为FALSE
=EXACT(B53,C53)结果:TRUE
怎么样设置才能让这一列的每个单元格只能输入1212位怎么样设置才能让某一列或某一行的每个单元格只能输入12位,(阿拉伯数字和26个英文字母在内,没有中文。)选中A列,设置数据有效性:自定义>公式:“=LEN(A1)=12”
如何让工作表奇数行背景是红色偶数行背景是蓝色用条件格式
=ROW()/2=INT(ROW()/2)设定颜色条件格式:公式为=MOD(ROW(),2)=0
计算特定的一组单元格中,满足条件的单元格的个数仍以上题为例,计算三个人在B307B313中各自所占的单元格数。李六的:=COUNTIF(B307:B313,B323)王武的:=COUNTIF(B307:B313,C323)陈丰的:=COUNTIF(B307:B313,D323)姓名:李六王武陈丰结果:322
把文ᴀ格式的数字转换成真正的数字
=VALUE(B1)
22
设置页码如何设置“第×页,共×页”页码。在页脚中设置:&[页码]页,共&[总页码]页即可
Excel表格里如何插入页码的?我想把表格中的第1页的页码从第30页开始编,不知道该如何实现,哪位高手能帮忙?在页面设置的页眉页脚中设置。
在插入页脚中输入&[页码]+29即可。
如何设置页脚首页为第5Excel页脚设置页码是按顺序来的,首页为第1页。如何设置首页为第5页?在页脚输入“第&[页码]+4页”,结果ᴀ该显示“第1页”的就显示第5页了。(用于多个工作表全选)页面设置—页面—起始页码输入5(用于单个工作表)。
表格的页脚问题是这样的,我每个表格有4张,总共一个文件里面有6个表格,相当于总共24页,我希望它能够自动打,而且我想设置页脚为,共24页,第?页,怎么办?试一试选择所有的工作表(工作组)然后再设置页脚,打印的时候也是用工作组打印。把所有工作表选中就可以了然后你再点打印,或者你先浏览,再设置也行!shift依次点表单的标签。其实,就是在选择浏览或者打印前,先选中你想要的工作表,然后再一个个的浏览,就相当于你的操作对所有工作表都已经起了作用似的。请楼主试一试,按以下步骤办:
1.文件→页面设置→页眉/页脚→页脚(F,选自己需要的页脚格式
2.文件→打印→整个工作簿。
无拘无束的页眉页眉和页脚大家都用过吧?用得最多的莫过于当前第几页/总共第几页。但你是否想过将“第N页/总M页”无拘无束的放置,而不是只能置于页眉页脚中?,现教你一法,可以通用。到任何地方均可使用。首先:点CTRL+F3打开定义名称,再在上面输入“纵向当前页”,在下面引用位置处输入
=IF(ISNA(MATCH(ROW(),GET.DOCUMENT(64))),1,MATCH(ROW(),GET.DOCUMENT(
64))+1)。然后再继续添加第二个名称:“横向当前页”,在下面引用位置处输入
=IF(ISNA(MATCH(column(),GET.DOCUMENT(65))),1,MATCH(column(),GET.DOCUMEN
T(65))+1)。再输入“总页”;引用位置处输入:=GET.DOCUMENT(50)+RAND()*0。最后再定义“无拘无束的页眉”;引用位置:=""&IF(横向当前页=1,纵向当前页,横向当前页+纵向
当前页)&"/"&总页&""现在你在工作表任何处输入=无拘无束的页眉即可。ᴀ公式核心在于GET.DOCUMENT,这是4.0宏函数,OFFICE97及以前版专用,新版OFFICE中仍兼容,但只限定义名称中使用。在帮助中说(64和65为其参数):64行数的数组,相应于手动或自动生成页中断下面的行。65列数的数组。相应于手动或自动生成的页中断右边的列。"
23ᴀ公式中取64,用于计算当前行与分页符之前后关系.GET.DOCUMENT(64)即返回分页符所在行下一行之行号(亦即第二页第一行)。判断当前行是否大于分页符所在行
“=IF(ISNA(MATCH(ROW(),GET.DOCUMENT(64))),1”此句利用MATCH之模糊查找功能将当前行号与分页符下行(分页符下一行是一个单元N行的一维数组,文档有几页则有几行,ᴀ实例文档有三页,请看公式求值之计算图示)做比较,此处省略MATCH第三参数,即查找小于等于目标值,如果目标值大于当前行号,则MATCH返回错误值。那么此处再用IF(ISNA(),1)加以判断,即若找不到小于等于当前行号的值则显示1,表示当前行处于第一页。取得当前行所在页
MATCH(ROW(),GET.DOCUMENT(64))+1如果前一个MATCH返回FALSE,则取IF函数第三参数值即MATCH(ROW(),GET.DOCUMENT(64))+1此参数再用MATCH在GET.DOCUMENT(64)产生的数组中查找当小于等于前行号的数值,若数组中第N个值小于等于当前行号,则当前行在N+1页。取得总页
=GET.DOCUMENT(50)+RAND()*0GET.DOCUMENT(50)即求当前设置下欲打印的总页数,其中包括注释,如果文件为图表,值为1RAND()*0作用是当文件分页数改变时,ᴀ公式结果根随变化,起公式结果刷新作用。获取“横向当前页”横向当前页与纵向当前页原理相同,改ROW()为COLUMN(),并将GET.DOCUMENT参数改为65即可若你的工作表只有纵向分页或者横向分页,那么现在就可以使用前面的公式定义的名称获取当前页及总页了;但如果分页方式为横向多页纵向也多页呢?则在将以上“横向当前页”与“纵向当前页”无缝接合方可使用,否则将返回错误结果。最后生成“无拘无束的页眉”(或者改称文件分页)
="="第"&IF(横向当前页=1,纵向当前页,横向当前页+纵向当前页)&")&"页/共"&总页&"&"页"公式解说完毕!各位可以用不同的文字定义名称在各自的工作表中试用了。
打印表头Excel中如何实现一个表头打印在多页上?请选择文件-页面设置-工作表-打印标题-顶端标题行,然后选择你要打印的行。打印表尾,通过Excel直接提供的功能应该是无法实现的,需要用vba编制才行。
Excel打印中如何不显示错误值符号在“页面设置”-“工作表”-“错误单元格打印为”中,将“显示值”改为“空白”即可。
对于一些不可打印的字符的处理对于一些不可打印的字符(在Excel显示中类似空格),直接用替换方法不容易去掉。可以这么做:
=SUBSTITUTE(CLEAN(A1),"","")
24
用那个函数可将个位数前面的零值显示出来?如果单元格A1的内容是5,在A2用那个函数可将A1的内容变为05?(Text或value也可,总之个位数的零也显示,例:5变05,15则15)可以用TEXT(A2,"00")或将单元格格式自定义为00
如果你要在A3A3的前面插入100行可以这样:在名称框输入3:103-回车-ctrl+shift+"+"(大键盘)
请问如何每隔3030行粘贴一新行偶在班上负责统计企业进出口业务量,领导要求每30家做一合计数,偶只有每隔30行插入复制单元格的方法来添加的,很是麻烦,请教各位大虾有什么快捷的方法呀在最后加一辅助列,输入=INT((ROW()-1)/31)+1(假设一个标题行)然后以该行分类字段汇总.
在工作表里有连续10行数据,现在要每行间格2解答:1:如sheet1!$A$1:$D$10中有连续10行资料,在sheet2中把sheet1中的数据每行间隔2行,sheet2!A1中公式可用:
=IF(ROW()=1,Sheet1!A1,IF(MOD(ROW(),3)=1,INDEX(Sheet1!$A$1:$D$10,INT((ROW()-1)/
2)+1,COLUMN()),""))然后填充公式(注意公式在SHEET2中的填充范围,超过范围会出错!)2:小修改
=IF(MOD(ROW(),3)=1,INDEX(Sheet1!$A$1:$Z$500,INT(ROW()/3)+1,COLUMN()),"")
一个大表每一行下面需要加一行空行,怎么加最方便方法一:增加辅助列,填充数据排序完成方法二:增加辅助列,函数完成
=IF(MOD(ROW(),2),INDIRECT("a"&ROUNDUP(ROW()/2,0)),"")
Excel中插入空白行如果想在某一行上面插入几行空白行,可以用鼠标拖动自此行开始选择相应的行数,然后单击右键,选择插入。如果在每一行上面均插入一空白行,按住Ctrl键,依次单击要插入新行的行标按钮,单击右键,选择插入即可。
快速删除工作表中的空行如果用户想删除Excel工作表中的空行,一般的方法是需要将空行都找出来,然后逐行删除,但这样做操作量非常大,很不方便。下面提供二种快速删除工作表中的空行的方法:1、首先打开要删除空行的工作表,在打开的工作表中单击“插入→列”命令,从而插入一新的列X,在X列中顺序填入整数,然后根据其他任何一列将表中的行排序,使所有空行都集中到表的底部。删去所有空行中X列的数据,以X列重新排序,然后删去X列。2、如批量删除空行,我们可以利用“自动筛选”功能,把空行全部找到,然后一次性删除。做法:先在表中插入新的一个空行,然后按下Ctrl+A键,选择整个工作表,用鼠标单击“数据”菜单,选择“筛选”项中的“自动筛选”命令。这时在每一列的顶部,都出现一
25个下拉列表框,在典型列的下拉列表框中选择“空白”,直到页面内已看不到数据为止。在所有数据都被选中的情况下,单击“编辑”菜单,选择“删除行”命令,然后按“确定”按钮。这时所有的空行都已被删去,再单击“数据”菜单,选取“筛选”项中的“自动筛选”命令,工作表中的数据就全恢复了。插入一个空行是为了避免删除第一行数据。如果想只删除某一列中的空白单元格,而其它列的数据和空白单元格都不受影响,可以先复制此列,把它粘贴到空白工作表上,按上面的方法将空行全部删掉,然后再将此列复制,粘贴到原工作表的相应位置上。
快速删除空行有时为了删除Excel工作簿中的空行,你可能会将空行一一找出然后删除,这样做非常不方便。你可以利用自动筛选功能来实现,方法是:先在表中插入新的一行(全空),然后选择表中所有的行,单击“数据→筛选→自动筛选”命令,在每一列的顶部,从下拉列表中选择“空白”。在所有数据都被选中的情况下,单击“编辑→删除行”,然后按“确定”,所有的空行将被删去。注意:插入一个空行是为了避免删除第一行数据。
一次删完Excel里面多出很多的空白行1、用分面预览看看2、用自动筛选然后删除3、用自动筛选,选择一列用非空白,空白行就看不到了,打印也不会打出来。但是实际上还是在的,不算删除。或者用自动筛选选择空白将空白行全显出来一次删完也可以。4、先插入一列,在这一列中输入自然数序列,然后以任一列排序,排序完后删除数据后面的空行,再以刚才输入的一列排序,排序后删除刚才插入的一列。
每30行为一页并加上一个标题如何实现每3030行为一页并加上一个标题如何实现
30行为一页,并加上一个标题,如何实现。可以每30行加一个分页符,标题就用打印标题来设置。
1、标题文件-页面设置-工作表-打印标题-顶端标题行,设置一下就好了。
2、每页30也是在页面设置中,设置上下页边距的调整可以实现,打印预览看一下就可以看到是不30行了,不到30行你可以将行距加宽,进行调整,以我的经验,加标题的30/页大概行距是20,这样连制表人的空间都留出来了。每页30行-插入》分页符;然后每向下移动30行,点菜单插入》分页符
如何实现隔行都加上标题项在excel中,每条记录都要加上标题(隔行都加),如何才能快速实现?(只要打印出来能实现就成)。E列输入24,然后选中这两个单元格,拖住右下的点向下拉到底。把第一行标题项复制,在有数据区域的下部选中与数据行数相同的空行,粘贴。用同样的方法填上奇数(如上),按E列排序即可。
如何把标签页去掉的?工具→选项→视图→点击工作表标签去掉()勾→确定。恢复时也照此操作
26工具→选项→视图→点击工作表标签显示()勾→确定。
去掉默认的表格线(网线)单击“工具”菜单中的“选项”,再单击对话框中的“视图”,找到“网格线”,使之失效(将左边的“×”去掉)。
表格的框线我们很喜欢为表格加上一道框线,不过这道框线又往往叫我们花掉很多时间来重画,例如在下方多加一列时,Excel并不会把新列加在下方框线之上。又例如将上方的数据拷到最后一列时,下方的框线就会给盖掉,变成穿了一个洞。我的技巧就是在表格的最后一列留一列空列,并把它的列高定得很小,我就叫这一列「缓冲列」好了。把列高定小一点,除了美观之外,还可以用作提醒用户不要把数据打到缓冲列。你可以试试在缓冲列上加列或拷数据到缓冲列之上,框线并不会给弄乱。
列标的标识变了通常EXCEL的列标都是用大写英文字母表示的,我的EXCEL的列标今天都变成了阿拉伯数字表示的了,请教这两种表示方法有什么不同,如果想恢复成字母表示的该怎么办。这是EXCEL的R1C1样式。在这里改回来:工具/选项/常规:不选R1C1样式。
符号的意义单元格自定义格式中"?""#"代表的是什么意思。“?”一个字符,字符:可以是文ᴀ、也可以是数字;“#”一个数值字符,数值字符:只能是数字。
双击格式刷竟也能COPY文ᴀ(不是文ᴀ格式)
步骤:选中“单元格”→双击格式刷→按住Ctrl键选择需复制的不连续目标区域→按回
Enter格式刷的作用其实没变,复制文ᴀ其实只是按Enter的结果。分解动作分为三步:
1.定位在原数据上,双击格式刷:复制所有内容
2.在目标区域按格式刷:选择性粘贴-格式
3.按回车:粘贴所有内容。你会发现如果原单元格上有批注或其实Shape对象的话,一样也复制了,跟原数据按
Ctrl+C,选择区域.再按Enter这个意思是一样的。
查找+格式刷的妙用通常在数据校对时要用到查找,找到之后就用不同格式区分(如字体为红色、底纹为黄色等等),如此重复。例如:先找到第一个数据并将字体改为红色,然后双击格式刷,当查找到其它相同数据时,再按Ctrl+Aexcel就会将新找到的数据自动改为红色与之区分。楼主能不能做一个动画演示?具体操作为:Ctrl+F→在查找栏输入要查找的值→查找下一个→设置格式(不要退出查找对话框)→双击格式刷→查找全部→Ctrl+A→关闭另外,通常的方法是:Ctrl+F→在查找栏输入要查找的值→查找全部→Ctrl+A,再设置格
27式。
光标移动在一个Excel工作表中作业时,双击某单元格的四周(上、下、左、右),会迅速移动光标的位置,若是双击上方即刻回到单元格所在列的最顶端,双击下方则移动到最底端的编辑外,同样双击左右也是到相对应的地方,双击单元格中间则变为输入状态。大家可以试试,这样比移动工作表中的下拉图标快捷。
最后一行为文ᴀ
=offset($1,MATCH(CHAR(65535),b:b)-1,)
最后一行为数字
=offset($1,MATCH(9.9999E+307,b:b)-1,)或者:=lookup(2,1/(b1:b1000<>""),b1:b1000)
如何在EXCEL中快速定位最后一行数据如果定位就是选中的意思,可按CTRL+END键实现。
CTRL+双击选取单元格之下框线
SUM函数快速求和如何用sum函数快速求和操作:将光标移到欲要求和的列或行,直按"Alt+"=",最后按一下“enter"键就可以。这样我们不用输入参数就可以快速求和。
Excel中快速查看所有工作表公式只需一次简单的键盘点击,即可可以显示出工作表中的所有公式,包括Excel用来存放日期的序列值。要想在显示单元格值或单元格公式之间来回切换,只需按下CTRL+`(位于TAB键上方)。
Excel中设置行间距想必大家都知道Excel中是没有行间距设置功能的吧。利用拼音指南却可以让我们在Excel中轻松设置单元格中文字的行间距。在Excel2003中选中需要设置行间距的单元格,单击“格式”菜单,依次选择“拼音指南/显示或隐藏”,马上可以看到单元格中文字行间距变大了。如果想再进一步调整行间距,可再单击“格式”菜单,选择“拼音指南/设置”打开“拼音属性”窗口,切换到“字体”选项卡下,把字号设置大一点,确定后行间距就会相应增大,反之则减小。
怎样同时改变多行行高我们知道,通过拖动行或列间的分界线可以改变行高或列宽,但怎样同时改变向行或几列的高度或宽度呢?我们以改变行高为例,先选中要改变行高的列,按下Shift键再单击行标题头,可以选定连续的多行(如果要选中多个不连续行,可以按下Ctrl键)。选中多列后,
28拖动任意一个被选中的行标题间的分界线,到适当高度释放鼠标,所有被选中的行高都改变了。我们也可以精确地改变行高:选中多行后,单击“格式”菜单,选择“行”中的“行高”命令,设置行高为20,单击“确定”,行高都被设置为20了。
快速换行在Excel单元格中输入数值后,按下Alt键不松开,再按下Enter键,即可快速换行。
让文ᴀ换行每次在Excel单元格中输入一个值,再按下Enter键,活动单元格均默认下移一个单元格,非常不方便。不过,这时,可以选择“工具”→“选项”→“编辑”,然后取消“按Enter键移动活动单元格标识框”复选框即可。
Excel中行列快速转换如果需要要将Excel按行(列)排列的数据,转换为按列(行)排列,可以通过“选择性粘贴”来实现。选中需要转换的数据区域,执行一下“复制”操作;选中保存数据的第一个单元格,执行“编辑选择性粘贴”命令,打开“选择性粘贴”对话框,选中其中的“转置”选项,确定返回即可。
将原有列中的内容倒置过来1524334251
B1=OFFSET(A$1,COUNTA(A:A)-ROW(A1),)
快速回到A1单元格按下Ctrl+Home组合键,快速选中A1单元格。
29
复制粘贴中回车键的妙用1、先选要复制的目标单元格,复制后,直接选要粘贴的单元格,回车OK;2、先选要复制的目标单元格,复制后,选定要粘贴的区域,回车OK;3、先选要复制的目标单元格,复制后,选定要粘贴的不连续单元格,回车OK。
一次选中批注单元格按下Ctrl+Shift+O(字母O)组合键,可以一次性选定所有带批注的单元格。
一次在所有单位格中插入批注
1选择你已经做批注的单元格
2复制
3选择你要做相同批注的所有单元格
4编辑〉选择性粘贴〉批注
在公式中插入批注如果要在公式中插入批注信息,可以利用“NValue返回“0”的特点,因为文字属于其它值。
1.假如A1F1单元格中是个别统计数字,G1则是它们的总和,那么一般情况下其公式为“=SUM(A1:F1)”
2.如果要在公式中插入批注信息,可以将公式更改为“=SUM(A1:F1)+N("A1F1的总和
")”,如图1所示。
不连续单元格填充同一数据选中一个单元格,按住Ctrl键,用鼠标单击其他单元格,就将这些单元格全部都选中了。在编辑区中输入数据,然后按住Ctrl键,同时敲一下回车,在所有选中的单元格中都出现了这一数据。
空白行的填充各位,我一些同事喜欢在表格内使用合并单元格,而我的进行公式运算时需把单元格打散,但就出现了许多空白格,现在我想把空白格用临近的非空白填充。
EXA1格为张三A8李四A21王五之类,现在我要把A2A7填为张三,
A9A20为李四,A22之后为王五。B1输入=IF(A1<>"",A1,IF(ROW()>1,INDIRECT("b"&ROW()-1))),向下复制到合适位置。然后用选择性粘贴功能替换到A列中去。
怎样用函数向下实现自动填充各位高手,怎样用函数实现如下的功能:把左边的空格,用上面的A0001代码填充,实现右边的格式,谢谢解答!!!
30
A0001白色300||A0001白色300红色500|--->|A0001红色500黄色300||A0001黄色300如果你的第一个“A0001”[A1]A2=if(b2>0,a$1,"")向下拖曳。最好用附件的形式来提问,这样可以减少相互间猜题的麻烦。用绝对值是不行的,假如,我下面还有别的编号,这个功能就实现不了啊
怎么设置自动保存在“工具”菜单上,单击“选项”,再单击“保存”选项卡。选中“自动保存时间间隔”复选框。在“分钟”框中,指定希望MicrosoftOffice程序保存文件的频率。
避免输入网址和电子邮件地址时的超链接在单元格中输入的网址或电子邮件地址,Excel在默认情况下会将其自动设为超级链接。如果想取消网址或电子邮件地址的超级链接,可以在单元格上单击鼠标右键,选择“超级链接/取消超级链接”即可。此外,还有两个有效办法可以有效避免输入内容成为超级链接形式:1、在单元格内的录入内容前加入一个空格;2、单元格内容录入完毕后按下“Ctrl+z”组合键,撤消一次即可。
单元格前面自动加了等号我的单元格怎么输入时间后前面自动加了等号,然后2005年就变成了1905年了呢?工具-选项-1-2-3帮助-转换lotus123公式有无打勾?去掉
加盖公章我们日常上送、下发的报表材料、通知等都要加盖公章,如果把这项工作交给Excel或Word来完成,我们的工作就轻松多了。第一步:制作公章图案首先我们要做出一个公章的图案,最简单的办法是把公章图案扫描到电脑中,然后处理成透明的GIF图像。我们也可以直接用Excel来制作:把绘图工具打开,选中“椭圆”工具,在按下“Shift”键的同时拖开鼠标,就可以得到一个正圆了。双击这个正圆打开“设置自选图形格式”对话框,在“颜色与线条”标签中,填充颜色选“无填充颜色”,线条颜色设为红色,选3磅粗的单线形(图)。公章的文字用艺术字来制作,填充颜色和线条颜色都用红色,并设成无阴影产。弧形文字和水平文字要分开来做,在做弧形文字时,把艺术字拖到圆形的上方,在艺术字工具中选“艺术字形状-细上弯弧”,按住黄色的四方块往下拉,再作适当的调整,就可以做出公章里的圆弧形的文字了。公章中间还有一个红五星,用“自选图形”的星形就可以做出来了,填充颜色和线条颜色用红色。最后,按住“Shift”键把组成公章的文字、图形全部选上,执行右键菜单中的“组合”命令,一个公章就做好了。如何把做好的公章保存出来?这里有一方法:把工作表另存为Web页,然后到保存目录中找到*.files的文件夹,里面有一个GIF图片,这就是刚才做好的公章图案了,它的背景是透明的,我们把它改名为gongzhang.gif保存下来即可。第二步:添加“盖章”按钮接下来我们给Excel添加一个盖章按钮,当一个工作表做好后,点击这个盖章按钮,就可以为我们盖上公章了。
31先把公章图形复制出来(用来粘贴作为按钮的图标),然后打开“工具-自定义”对话框,选中“命令”标签,在“类别”栏中找到“宏”,在右边的“命令”栏里就会出现一项“自定义按钮”。用鼠标把这个笑脸图标拖出到菜单栏或工具栏上放下,在笑脸图标上击右键,在弹出的菜单中把“命名”处的文字改为“加盖公章”。接下来点击“粘贴按钮图标”这个命令,就可以用刚和复制的公章图形来代替笑脸图标了。把鼠标移下来选中“分配超级链接-插入图片”,然后在“请键入文件名称或Web页名称”栏里输入公章图片gongzhang.gif的文件名及路径,然后按“确定”返回。好了,看到“加盖公章”这个按钮了吧,点击一下看看,呵呵,页面上就盖上一个鲜红的公章了,用鼠标可以把它拖到任意的地方。在Word文档中加盖公章的方法与此大同小异,大家可以自己试一试。
查找+格式刷的妙用通常在数据校对时要用到查找,找到之后就用不同格式区分(如字体为红色、底纹为黄色等等),如此重复。例如:先找到第一个数据并将字体改为红色,然后双击格式刷,当查找到其它相同数据时,再按Ctrl+A,excel就会将新找到的数据自动改为红色与之区分。具体操作为:Ctrl+F→在查找栏输入要查找的值→查找下一个→设置格式(不要退出查找对话框)→双击格式刷→查找全部→Ctrl+A→关闭
另外,通常的方法是:Ctrl+F→在查找栏输入要查找的值→查找全部→Ctrl+A,再设置
格式。但再找下个值时,又要重新设置格式,如果要找的值很多的话就显得不是那么方便了。当然按照自己的习惯做最好的。如果用格式刷+查找功能呢,就可以找到A并把A设成红色,再双击格式刷,然后再找B、找C、找D…excel就会把找到的B、C、D自动的设为红色以之区分,而不用找一个设一次字体了。
Excel中鼠标双击妙用1、双击单元格,就可以编辑单元格的内容(对应用快捷键——F2)。2、在行/列边缘双击鼠标,则可以得到此列的最适合的行高/列宽。3、双击工作表标签,可以直接重命名工作表的名称。4、在填充的时候,选定单元格再移动到这个区域的右下角,这时鼠标会变成细十字。(1)当选择区域当下方的单元格有内容时,双击会自动填充下方有数据的区域。(2)当选择区域当下方的单元格为空时而左边有数据时,双击会自动填充到与左边有数据的区域齐。(3)当选择区域当下方与左边的单元格为空时而右边有数据时,双击会自动填充到与右边有数据的区域齐。5、将鼠标移动到选定单元格的边上,这时鼠标会变成带箭头的十字。(1)这时双击,可以移动到数据区域的边缘,相当于快捷键——Ctrl+方向键。(2)如果按住Shift再双击,可以快速选择数据,相当于快捷键——Shift+Ctrl+方向键。6、双击工具栏的空白处,就可以调出自定义工具栏的对话框。7、双击左上角工具栏中工作簿的图标,可以关闭当前工作簿;双击标题栏中的Excel图标,可以关闭Excel。8、使用格式刷时,用双击而不是单击就可以多次使用,再单击一次格式刷结束。在使用绘图工具栏时,如果双击线、矩形、圆等图形时也可以连续绘图。9、双击拆分窗格的分割条(上下滚动条的上方,左右滚动条的右边,没试过拆分窗口的
32朋友可以先从窗口/拆分里体验一下),可以按当前单元格上下左右拆分;拆分后再双击分割条的任意部分可以恢复。
10.在菜单上双击,可将菜单中所有的菜单项(包括不常用的菜单项)全部展开。
11.如果工具条浮动在工作表区,在工具条的标题栏双击,则该工具条返回工作表区上部或下部位置。在选中行或选中列(当然也可以是某一行、列)的边缘双击,自动设置成合适的行高或列宽(偶常常在做完表后调整列宽、行高的时候把整个表选中,双击两下就OK了)。12.双击还可以恢复被隐藏的行或列如要恢复被隐藏的第3行,先将鼠标指针移动到第2行与第4行之间的位置,当鼠标的指针变成横向为“=”号,纵向为黑色实心双箭头的十字形状时,再双击操作,被隐藏的行就会显示出来了。变为"=""||"时直接拖动就可以了。13.在标题栏上双击,由最大化窗口(原始状态)还原到原始状态(最大化)大小。14.双击EXCEL中的透视表中的数据,可在新的工作表中列出该数据的明细。
Excel中快速定位的技巧实例在Excel中,我们需要到达某一单元格,一般是使用鼠标拖动滚动条来进行,但如果数据范围超出一屏幕显示范围或数据行数非常多时,想快速定位到某一单元格可要有点麻烦了。其实我们可以使用“定位”功能迅速到达想要的单元格。例1:需要选中Y2008单元格(或快速移动到Y2008单元格),我们可以使用“编辑/定位”菜单,在引用位置里输入“Y2008”后按回车即可。例2:需要选中Y列的2004~2008行的单元格,我们按照相同的方法,在引用位置里输入“Y2004:Y2008”按回车即可。例3:需要选中2008行的单元格,我们可以在引用位置里输入“2008:2008”按回车即可。例4:需要选中2004~2008行的单元格,我们可以在引用位置里输入“2004:2008”按回车即可。
Excel中插入Flash时钟的步骤动态时钟不是用函数运算、自动化功能制作出來的,這只是簡单的插入Flash文挡的功能而已,而且只要你有Flash文件,任何人都可以轻松自行制作。制作方法:1步首先打开一个空白Excel文件,点击“视图”→然后点选【控件工具箱】,→点击“其他控件”。2步然后再点击[ShockwaveFlashObject]项目,表示要插入Flash物件。3步接下来,鼠标会变成一个小十字,此時可以在Excel编辑区中画一个大小适中的方框,这个方框就是用来显示Flash时钟的內容的。4步画好方框后,接着点击【属性】,准备设置属性。5步出現「属性」对话框后,将DeviceFont设置成False;将Eebedmovie设置成TrueEnabled设置成True;将Locked设置成True;将Loop设置成True;将Menu设置成False并在“Movie”右侧填入时钟的地址与名称(如:G\22006.swf)6步如下图,退出设计模式,全部完成。
小写数字转换成人民币大写方法1
=IF(TRUNC(H16)=H16,TEXT(H16,"[DBNum2]G/通用格式")&"元整
33
",TEXT(TRUNC(H16),"[DBNum2]G/通用格式"&"
"))&IF(AND(TRUNC(H16)<>H16,RIGHT(TRUNC(H16*10))<>"0"),TEXT(TRUNC(MOD(
H16*10,10)),"[DBNum2]G/通用格式")&"
","")&IF(AND(RIGHT(TRUNC(H16*10))="0",TRUNC(H16)<>H16),"
","")&IF(TRUNC(H16*10)<>H16,TRUNC(H16*10)=H16*10),"","")方法2
=IF(F10=0,"",CONCATENATE(IF(INT(F10)=0,"",TEXT(INT(F10),"[DBNum2]G/通用格式
")),IF(INT(MID(RIGHT(FIXED(F10,2,1),2),1,1))=0,IF(INT(MID(RIGHT(FIXED(F10,2,1),1),
1,1))=0,"",IF(INT(F10)=0,"","
")),TEXT(INT(MID(RIGHT(FIXED(F10,2,1),2),1,1)),"[DBNum2]G/通用格式角
")),IF(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1))=0,"
",TEXT(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1)),"[DBNum2]G/通用格式分"))))方法3人民币大写的函数公式,可正负,最多两位小数。
=IF(A1<0,"","")&IF(TRUNC(A1)=A1,TEXT(IF(A1<0,-A1,A1),"[DBNum2]")&"元整
",IF(TRUNC(A1*10)=A1*10,TEXT(TRUNC(IF(A1<0,-A1,A1)),"[DBNum2]")&"
"&TEXT(RIGHT(A1),"[DBNum2]")&"角整",TEXT(TRUNC(A1),"[DBNum2]")&"
"&IF(ISNUMBER(FIND(".0",A1)),"",TEXT(LEFT(RIGHT(A1,2)),"[DBNum2]")&"
")&TEXT(RIGHT(A1),"[DBNum2]")&""))方法4修改一下4:根据剑魔兄的测试,发现有一个问题,如-100.05,现修正如下:
=IF(A1<0,"","")&IF(TRUNC(A1)=A1,TEXT(IF(A1<0,-A1,A1),"[DBNum2]")&"元整
",IF(TRUNC(A1*10)=A1*10,TEXT(TRUNC(IF(A1<0,-A1,A1)),"[DBNum2]")&"
"&TEXT(RIGHT(A1),"[DBNum2]")&"角整
",TEXT(TRUNC(IF(A1<0,-A1,A1)),"[DBNum2]")&""&IF(ISNUMBER(FIND(".0",A1)),"",TEXT(LEFT(RIGHT(A1,2)),"[DBNum2]")&"
")&TEXT(RIGHT(A1),"[DBNum2]")&""))方法5
=IF(A1<0,"",)&TEXT(TRUNC(ABS(A1)),"[DBNum2]G/通用格式")&"
"&IF(ROUND(A1,3)=ROUND(A1,),"
",TEXT(RIGHT(TRUNC(A1*10),1),"[DBNum2]G/通用格式")&"
"&IF(ROUND(A1,3)=ROUND(A1,1),"
",TEXT(RIGHT(ROUND((A1*100),),1),"[DBNum2]G/通用格式")&""))方法6无条件舍去:=CONCATENATE(IF(A1<0,"",""),TEXT(IF(TRUNC(A1)=0,"
",TRUNC(ABS(A1))),"[DBNum2]")&"
",IF(OR(AND(ABS(A1)<0.1,TRUNC(A1)=A1),RIGHT(INT(ABS(A1)*100),2)="00"),"",TEX
T(RIGHT(TRUNC(A1*10),1),"[DBNum2]")),IF(RIGHT(TRUNC(A1*10),1)="0","","
"),IF(OR(TRUNC(A1*10)-(A1*10)=0,RIGHT(TRUNC(A1*100),1)="0"),"
",TEXT(RIGHT(TRUNC(A1*100),1),"[DBNum2]")&""))小数点后两位四舍五入:=CONCATENATE(IF(A1<0,"
",""),TEXT(IF(TRUNC(ROUND(A1,2))=0,"
",TRUNC(ABS(ROUND(A1,2)))),"[DBNum2]")&"
34
",IF(TRUNC(ROUND(A1,2))=ROUND(A1,2),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10),
1),"[DBNum2]")),IF(RIGHT(TRUNC(ROUND(A1,2)*10),1)="0","","
"),IF(OR(TRUNC(ROUND(A1,2)*10)-(ROUND(A1,2)*10)=0,RIGHT(ROUND(A1,2),1)="0",
TRUNC(ROUND(A1,2))=ROUND(A1,2)),"
",TEXT(RIGHT(ROUND(A1,2),1),"[DBNum2]")&""))方法7无条件舍去:=IF(A1<0,"","")&SUBSTITUTE(TEXT(TRUNC(A1),"[DBNum2]")&"

"&IF(ISNUMBER(FIND(".",TRUNC(A1,2))),TEXT(RIGHT(TRUNC(A1*10)),"[DBNum2]"
)&IF(ISNUMBER(FIND(".0",A1)),"","
"),"")&IF(LEFT(RIGHT(TRUNC(A1,2),3),1)=".",TEXT(RIGHT(TRUNC(A1,2)),"[DBNum
2]")&"",""),"-",)小数点后两位四舍五入:=IF(A1<0,"
","")&SUBSTITUTE(TEXT(TRUNC(ROUND(A1,2)),"[DBNum2]")&"
"&IF(ISNUMBER(FIND(".",ROUND(A1,2))),TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"
[DBNum2]")&IF(ISNUMBER(FIND(".0",ROUND(A1,2))),"","
"),"")&IF(LEFT(RIGHT(TRUNC(ROUND(A1,2),2),3),1)=".",TEXT(RIGHT(ROUND(A1,2)
),"[DBNum2]")&"",""),"-",)方法8再简化如后,请大家试试。无条件舍去:
=IF(A1<0,"","")&TEXT(TRUNC(ABS(A1)),"[DBNum2]")&"
"&IF(ISERR(FIND(".",TRUNC(A1,2))),"",TEXT(RIGHT(TRUNC(A1*10)),"[DBNum2]"))
&IF(RIGHT(TRUNC(A1*10))="0","","
")&IF(LEFT(RIGHT(TRUNC(A1,2),3))=".",TEXT(RIGHT(TRUNC(A1,2)),"[DBNum2]")&
"","")小数点后两位四舍五入:
=IF(A1<0,"","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"
"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[D
BNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"
","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]
")&"","")方法9
=IF(ISTEXT(C2),"","人民币:"&TEXT(INT(C2),"[dbnum2]")&"
"&IF(INT(C2*10)-INT(C2)*10=0,"",TEXT(INT(C2*10)-INT(C2)*10,"[dbnum2]")&"
")&IF(INT(C2*100)-INT(C2*10)*10=0,"
",TEXT(INT(C2*100)-INT(C2*10)*10,"[dbnum2]")&""))
轻轻松松制作超复杂Excel表头在Excel中,经常会碰到要制作的复杂表头,其中包含有斜线和文字(见下图画红圈处),许多初学者往往对此束手无策,还有的干脆胡乱调整,由于采取的方法不当,结果却是花了很长的时间却达不到理想的效果。
35图1图2许多初学者经常采用以下两个操作来处理:1、用边框中的斜线来调整;2、在单元格中直接输入文字。现笔者分析一下其弊端:1、用边框中的斜线来调整,只能画一条斜线,不能画多条斜线,同时,画出的斜线只能是单元格的对角线,不能随意更改。见图2:2、在单元格直接输入文字,要分行,只能先让单元格自动换行,然后用空格键移动相应的文字到第二、三、四行,这种方法费时费力,文字一多往往就做不到理想的效果。至此,不少初学者不尽望洋兴叹:做一个表头怎么那么难!!其实,换一种思维,换一个方法,就“柳暗花明又一村”了。下面,笔者还是以图1为例进行详细说明。首先,输入没有斜线的单元格的内容,调整行列(见图3)。图3图5在这里说明一下,调整行列很重要,这样制作带斜线表头后表格的其它部分不用再调整了,否则调整表格的其它部分,斜线单元格的内容又挨重新调整。这点笔者千万要注意。然后,单击绘图工具栏中的“直线”(图4中画红圈处)。图4将鼠标移到单元格中,明确直线的起点和终点,从起点按住鼠标拉到终点,即可画出第一条直线。如果起点和终点有偏差,可将鼠标移到直线的起点处(或终点),鼠标由空心十字形变成斜双箭头后,即可按住鼠标往任意一个方向调整直线到合适的位置,上、下、左、右均可。这就是这种方法的好处。用这种方法做出第二条直线,效果见图6:
36图6图8现在是输入文字了,怎么输入呢?用文ᴀ框工具。单击绘图工具栏中的“文ᴀ框”,见后页图(画红圈处):图7然后在单元格中按一下鼠标,输入第一个字“科”。效果见图8:这里要注意的是“按一下鼠标”,不要拖动鼠标。如果拖动鼠标,文ᴀ框会出现黑边框(见图9)。图9图10将图8文ᴀ框中的“科”选中,调整其字体、字号等格式成合适的效果。这一点也要注意,到下面的复制操作时就不必再来调整各个文字的格式了。鼠标点一下“科”文ᴀ框,然后将鼠标移到该文ᴀ框边缘,点鼠标右键。见图10:在弹出的菜单中选择“复制”,在表格的任一个地方点鼠标右键,选择“粘贴”,就会出现另一个“科”文ᴀ框。效果见图11:图11图12将“科”字改为“目”字,将鼠标移到该文ᴀ框边缘,鼠标变成十字箭头形,按住鼠标移动该文ᴀ框到单元格合适的位置。效果见图12:如果“科”字和“目”字相对位置不理想,还可以继续调,只要选择相应的文ᴀ框移动就行了。这就是为什么我们把两个字分成两个文ᴀ框,目的就是为了方便调整这两个字之间的相对位置。通过采取相似的方法,把其余的文字做出来。效果见下图:
37至此,读者一定会发现,再复杂的斜线表头,利用直线和文ᴀ框工具,也就迎刃而解了!
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【字符截取与增减】
截取单元格里某个字符后的字符
=RIGHT(A1,LEN(A1)-SEARCH(",",A1,1))
截取字符的公式有一组数据101103*,1120*…我想截取~*之間的數字。
=MID($A3,FIND("~",$A3)+1,FIND("*",$A3)-(FIND("~",$A3)+1))
如何确定*号后的数字
=RIGHT(A1,LEN(A1)-FIND("*",A1,1))
=REPLACE(A1,1,FIND("*",A1),)
=TRIM(RIGHT(SUBSTITUTE(A1,"*",REPT("",LEN(A1))),LEN(A1)))
=MID(A1,SEARCH("~*",A1)+1,100)
=SUBSTITUTE(A1,LEFT(A1,FIND("*",A1)),"")=--MID(A1,MATCH("~*",MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),)+1,100)
=RIGHT(A1,LEN($A$1)-FIND(CHAR(CODE("*")),$A$1,1))(取最后三位)
如何提取数字中间的数数据在A1单元格,则公式为:
=MID(A1,5,3)解释:“5”是从第5位开始提取,“3”是提取3个数。
三个数中,如何取出中间那个比如1,2,3三个数字,我想取出2,用什么方法?
=LARGE(A1:A3,2)
取数值后三位公式
=RIGHT(A1,3)
取数函数单元格中用函数单独取出*号前(后)的数
38假如2.01*750位于A1
=MID(A1,1,FIND("*",A1,1)-1)取得*号前的数据
=MID(A1,FIND("*",A1,1)+1,LEN(A1)-FIND("*",A1,1)+1)取得*号后的数据2.01*750*800的得数就是750*800,而不是800,怎么办呢
=MID(B2,FIND("*",B2,FIND("*",B2,1)+1)+1,LEN(B2))
=RIGHT(B2,LEN(B2)-FIND("/",SUBSTITUTE(B2,"*","/",LEN(B2)-LEN(SUBSTITUTE(B
2,"*","")))))
如何把单元格中的数字提取出来(字符串中不连续)数组公式
=SUM(MID(0&A4,LARGE(ISNUMBER(--MID(A4,ROW($1:$20),1))*ROW($1:$20),ROW($
1:$20))+1,1)*10^ROW($1:$20)/10)
数字在字符串中不连续如何提取数字如:3k3mn249up结果:33249033k3mn249up结果:333249用数组公式:
=SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW($1:$1024),1))*
ROW($1:$1024),ROW($1:$308))+1,1)*10^ROW($1:$308)/10)
用如何提取“-”前后的字符
5-0
4-2
0-6取左边=CHOOSE(TYPE($D4),MONTH($D4),LEFT($D4,FIND("-",$D4)-1))取右边=CHOOSE(TYPE($D4),DAY($D4),RIGHT($D4,LEN($D4)-FIND("-",D$4)))
怎样删去﹕后的文字格式都是XXXX:YYYYYY,如何只保留XXXX,而全部删下﹕后的文字呢﹖(XXXX的长度是不一样)这个用函数可轻易解决:=LEFT(A1,FIND(":",A1)-1)若没有要求一定要用函数解,那也可以试试用[数据]>(数据剖析)
怎样只取“.”之后的文字﹖如:Q24-S4.WorkingStatus只取WorkingStatus如果“.”前的字數固定
=RIGHT(A1,LEN(A1)-7)如果不固定
=RIGHT(A1,LEN(A1)-FIND(".",A1))
=TRIM(RIGHT(A1,LEN(A1)-FIND(".",A1,1))
获取单元格内容中字符串08:25,18:25
如:要取得单元格b5中的从左边算起五位的字符串。即是08:25
39
=LEFT(B5,5)
如:要取得单元格b5中的从右边算起五位的字符串。即是08:25
=RIGHT(B18,5)单元格编号,起始位数,从起始位算起的第几位数
MEP090296
=MID(B1,4,3)结果:090
如何提取一串数字中的几位数字(字符)如: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)
如何把一个单元格中的数字挑出来一个单元格中有数字、空格、汉字,如:“11210101银行存款/工行”,数字的位数不确定,但都从最左边开始,数字和汉字中间有一个空格。如何只把数字显示出来?
1、如果都是这样就简单"都从最左边开始,数字和汉字中间有一个空格"假定在A1,公式为:=LEFT(A1,FIND("",A1)-1)
2、数据分列不更简单么?分列符号选中空格前面那个框。
分割文ᴀ有一列数据,全部是邮箱的,现在想将@前面的账号与@后面的域名分割开,分为两列,如何做?采用函数分割:例如:A1:name@163.com
B1:=LEFT(A1,FIND("@",A1)-1)-->name
C1:=RIGHT(A1,LEN(A1)-FIND("@",A1))-->163.com或:数据-分列-分列-分隔符号-@就可以了
按照给定的位数,截断小数点后的数字对整数无效,且这个函数没有四舍五入的功能
12512.251412512.25
=TRUNC(B23,2)
单元格数字提取问题单元格里面填写的CHIP(0601-2299),0601-2299提取出来应该怎么做。
=IF(B3="","",SUBSTITUTE(MID(B3,FIND("(",B3)+1,100),")",""))
40我用IF函数是因为看到你给的表格当中,数据和数据之间都有一行空行,如果没有空行的话,数据是连续的时候公式可以简化为:
=SUBSTITUTE(MID(B3,FIND("(",B3)+1,100),")","")
以关键字提取名称求当输入球队时,自动生成联赛名称联赛球队当输入球队时函数自动生成英超车路士车路士英超英超阿仙奴爱华顿英超英超曼联祖云达斯意甲英超利物浦麦斯纳意甲英超爱华顿英超米杜士堡意甲祖云达斯
=INDEX(A$2:A$23,MATCH(D2,B$2:B$23,0))
=INDIRECT("A"&MATCH(D2,B:B,))
=VLOOKUP(D2,IF({1,0},$B$2:$B$23,$A$2:$A$23),2,0)
如何把文ᴀ中的前几个字符去除如将“第二班AAA”中的“第二班”三个字去除。1、用公式:
=RIGHT($A2,3)
=RIGHT($A2,LEN($A2)-FIND("",$A2))
=MID($A2,FIND("",$A2)+1,LEN($A2))
=RIGHT(B2,LENB(B2)-LEN(B2))
=REPLACE(A1,1,3,"")
=SUBSTITUTE(A1,"第二班",)如前几个字符或后英文字數,不相同或不固定,公式可用:
{=MID(A2,MATCH(0,--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<65),0),255)}
2、数据>分列>固定宽度,把上述数据分为两列后,删除第一班的列
对一列中的文字统一去掉最后一个字能否对一列中的文字统一去掉最后一个字?这些文字不统一,有些字数多,有些字数少。如何处理?
=REPLACE(A1,LEN(A1),1,"")(在过渡列进行)
讨如何去掉单元格中的第一个数字?
=MID(A1,2,LEN(A1)-1)或者=RIGHT(A1,LEN(A1)-1)
=REPLACE(A1,1,1,"")
论一下取最后一个单词的方法例如现在在A1中有一句“M.HenryJackey”,如何用函数将最后的一个单词取出来呢?当然,我们现在是知道最后的单词是6个字符,可以用Right(A1,6)来计算,但如果最后一个单词的字符数是不定的呢,如果做呢?请大家试下有几种方法。
41方法1、用一列公式填充
=IF(LEFT(RIGHT($A$1,ROW()),1)=CHAR(32),RIGHT($A$1,ROW()-1),“”)方法2=MID(A1,FIND("*",SUBSTITUTE(A1,"","
*",LEN(A1)-LEN(SUBSTITUTE(A1,"",""))))+1,LEN(A1)-FIND("",A1))方法3=IF(ISERROR(SEARCH("",TRIM(LEFT(B1)))),RIGHT($A$1,ROW()),"")拖出来的第一个字符就行。方法4
{=RIGHT(A1,LEN(A1)-MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="
")*ROW(INDIRECT("1:"&LEN(A1)))))}嫌长就(假定最长100字符)
{=RIGHT(A1,LEN(A1)-MAX((MID(A1,ROW(1:100),1)="")*ROW(1:100)))}
如何去掉单元格最后一位数字说明:单元格前面的数据不能改变,去掉最后一位数字。
=LEFT(A2,LEN(A2)-IF(ISNUMBER(--RIGHT(A2)),LEN(LOOKUP(9E+307,--RIGHT(A2,R
OW(INDIRECT("1:"&LEN(A2)))))),))如果后面代的数值在两位以内,也可以用以下方式实现,当然如果超过两位的话,可以加语句IF(ISNUMBER(RIGHT(TRIM(A2),X)+0)=TRUE,1,0)X代表后面数值的个数。
=LEFT(TRIM(A2),LEN(TRIM(A2))-IF(ISNUMBER(RIGHT(TRIM(A2),1)+0)=TRUE,1,0)+I
F(ISNUMBER(RIGHT(TRIM(A2),2)+0)=TRUE,1,0))
如何在一列已经输入的数据前添加“p”比如一列数据添加后变
112234p112234
123435p123435
124355p124355
123545p123545选中所有单元格,设置格式,自定义-〉在缺省的“G/通用格式”前面加上“"p"”(半角的双引号中间是p)即可加一列全是“p”,使用&=a1&b1Shift+7
="P"&A1在自定义中输入"Q"#即可,很简单的。
什么函数可以插入字符怎样用第一列的数据形成第二列的数据,即在特定位置加上几个相同字符串?解答:b1="04"&a1问:哪如果倒过来呢?答:用公式A1=MID(B1,3,13)A1=SUBSTITUTE(B1,"04","",1)
如何在数据前添加“*”号数据如在B列,在A列整列加“*”,C列C1输入公式C1C1=A1&B1,下拉。用“选择性粘贴”选“值”复制到D列,删去ABC列。
数字前面加上数字
42123012375223比如说上述的数字,我想在它们前面加上38910104,而且位置短的数字,会自动补0变成如下:389101040012338910104001233891010475223)
=TEXT(A1,"3891010400000")
3、查找替换,查找栏输入:第*班,替换栏空置,全部替换
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【数据拆分与合并】
数字如何拆分我有一组数据,如12359等,假如这些数据均在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变为:01234567890(一个单元格一个数字),文字也同样。用以下公式:
=MID($A1,COLUMN(A1),1)向右拖
=MID($A$2,COLUMN()-1,1)向右拖
=MID($A$2,COLUMN(),1)向右拖也可以,选中区域后数据”-----分列,固定列”-----看到尺寸的时候分别在01234567890两数之间点击一下-----完成就行了!
单元格的拆分一个单元格数据即包含了物品名又包含其规格,两者之间用"/"来隔开,现想把两者单独分开。如:轴承/SKF62122R,外六角螺丝/M10*30....."/"前后都无标准长度。1、用“数据-分列”,不用公式的方法最簡單了。2、用公式:
=LEFT(A1,FIND("/",A1)-1)
=RIGHT(A1,FIND("/",A1)-1)
如何拆分字组如何将一个单元格里的字组拆开来啊,
1、你好啊→你好啊要用什么函数啊?
=SUBSTITUTE(A1,"","")
43
2改成你好啊把中间的横线去掉。要用什么函数或怎样在自定义里面设置?①用查找替换功能,查找“—”,替换为空值=SUBSTITUTE(A1,"——","")
用连字符“&”来合并文ᴀBCD列合并。
1.E1单元格中输入公式:=B1&C1&D1下拉
2.选中E列,执行“复制”操作,然后选中F列,执行“编辑→选择性粘贴”命令,打开“选择性粘贴”对话框,选中其中的“数值”选项,按下“确定”按钮,E列的内容(不是公式)即被复制到F列中。
3.BCDE列删除,完成合并工作。
怎样把不同格式不同位置的内容合并到一个单元格内如:一个是文ᴀ格式一个是日期格式,怎么合并呢?今天是:2007-06-25
A1&TEXT(B1,"yyyy-m-dh:mm;@")
把不同单元格的内容合到一个单元格里比如:A1=好,A2=好,A3=学,A4=A5输入公式,使A5单元格内容为:好好学习
=TEXT(A1&A2&A3&A4,"")
=CONCATENATE(A1,B1,C1,D1)
合并与拆分两列合并成一列如何做
=OFFSET($A$2,TRUNC(ROW(A2)/2,0)-1,MOD(ROW(A2),2))向下拖如果是一列拆开分成两列又如何做
=OFFSET($C$2,ROW()*2-4+COLUMN(A:A)-1,)
=INDEX($C:$C,(ROW(1:1))*2+COLUMN(A:A)-1)
=INDEX($C:$C,(ROW(2:2)-1)*2+COLUMN(A:A)-1)向右拖一个再向下拖。
=IF(ROW()>COUNT(A:A),INDEX(B:B,ROW()-COUNT(A:A)),A1)公式下拖。
合并不同单元格的内容合并不同单元格的内容,可以利用CONCATENATE函数,此函数的作用是将若干文字串合并到一个字串中,具体操作为
=CONCATENATEB1,C1比如,假设在某一河流生态调查工作表中,B2包含“物种”、B3包含“河鳟鱼”,B7包含总数45,那么:输入“=CONCATENATE("ᴀ次河流生态调查结果:",B2,"",B3,"为",B7,"条/公里")”计算结果为:ᴀ次河流生态调查结果:河鳟鱼物种为45条/公里。
关于文字在表格中的组合如:计算机计算机
44
C1=A1&B1
求拆解合并公式
2/252/25
4/254/25若合并:A1=B1&"/"&C1若拆解:B1=left(A1,find("/",A1))C1=right(A1,find("/",A1))
如何把字母和数字分开?想把A列中如A8中"n.m.1."分解成两列"n.m."和"1.",有什么好办法吗?可以用什么公式把字母与数字和符号分开吗?
=IF(ISERR(FIND(1,A8)),"",RIGHT(A8,LEN(A8)-FIND(1,A8)+1))
把分散在各单元格的内容合在一个单元格中75P1286675P12866
=CONCATENATE(A1,B1,C1,D1)
多个工作表的单元格合并计算
=Sheet1!D4+Sheet2!D4+Sheet3!D4,更好的=SUM(Sheet1:Sheet3!D4)
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【条件自定义格式】
通过条件格式将小计和总计的行设为不同的颜色答:输入=RIGHT(RC,1)="";设定字体、边框、图案;确定。
如何实现这样的条件格式有一个excel表单,若当其中一栏数值超过某一值,使整个一行底色为某一颜色(比如红色),用条件格式不能实现注意公式为=$A1>100,而不是=A1>100先选定整行再设置条件格式...列标"A"用绝对引用,行标"1"用相对引用,"格式刷"刷下去...为方便和不易出错起见,先设置一行的条件格式,再用格式刷将格式复制到需要的行。
45
隔行不同字体颜色怎么设置每隔一行就用不一样的颜色,有什么快速的办法吗?格式-条件格式(公式):=MOD(ROW(A1),2)=0
让不同类型数据用不同颜色显示在工资表中,如果想让大于等于2000元的工资总额以“红色”显示,大于等于1500元的工资总额以“蓝色”显示,低于1000元的工资总额以“棕色”显示,其它以“黑色”显示,我们可以这样设置。1.打开“工资表”工作簿,选中“工资总额”所在列,执行“格式→条件格式”命令,打开“条件格式”对话框。单击第二个方框右侧的下拉按钮,选中“大于或等于”选项,在后面的方框中输入数值“2000”。单击“格式”按钮,打开“单元格格式”对话框,将“字体”的“颜色”设置为“红色”。2.按“添加”按钮,并仿照上面的操作设置好其它条件(大于等于1500,字体设置为“蓝色”;小于1000,字体设置为“棕色”)。3.设置完成后(图2),按下“确定”按钮。看看工资表吧,工资总额的数据是不是按你的要求以不同颜色显示出来了。有无办法让B2所在行都呈红色字体。
如何做这样的EXCEL表(颜色交叉)省得看错行了。设置二行不同的格式,同时选取这两行,按右键复制,选复制格式。
46若只是要不同颜色间隔,应该这样就行了:格式>自动格式设置我的条件格式公式为:=IF($B1="","",MOD(ROW(),2)).
条件格式如何用条件格式实现数据表格的阴影间隔效果是条件格式=MOD(INT((ROW()+0.5-$A$1)/$A$2),2)=0公式是:=MOD(COLUMN()-a,b*2)+1<=b
=MOD(ROW()-rw,n*2)+1<=n
=MOD(COLUMN()-a,b*2)+1<=b
a=行列间隔显示!$L$1
b=行列间隔显示!$L$2
n=行列间隔显示!$A$2
rw=行列间隔显示!$A$1
使用条件格式设置颜色条纹Excel97版ᴀ中,你可以使用条件格式将你工作表中的行设置成间隔显示的条纹,作出来的效果象会计的分类账.其原理和手工设置行背景色一样,如果你整理工作表时删除或移动行,它并不移动.更多关于条件格式的信息请点击这里获得.奇数和偶数行条纹左边图示的被称作"奇数条纹".方法是奇数行用底纹颜色显示.ᴀ例中1,3,5用淡蓝色显示,而偶数行2,4,6没有变化.同样,右边图示的称作"偶数条纹".方法是偶数行2,4,6用底纹颜色显示,奇数行
1,3,5没有变化.应该注意的是"奇数""偶数"是针对一个行组合而言,并非指彩色条纹中的行,也不是指工作表的行。颜色条效果公式“奇数条纹”和“偶数条纹”的公式非常相似,“奇数条纹”可使用下面公式:
=MOD(ROW()-Rw,N*2)+1<=N在这里RwRw用于格式化的范围内起始行号,N是每一组颜色条中包含的工作表行数。在上方左图的示例中,Rw等于8,N等于3.“偶数条纹”使用公式
=MOD(ROW()-Rw,N*2)+1>N在这里Rw用于格式化的范围内起始行号,N是每一组颜色条中包含的工作表行数.
47上方右图的示例中,Rw等于8,N等于3..如何在条件格式中使用这些公式的方法是:选择你想格式化的单元格范围.然后在格式菜单中选择条件格式.在弹出的对话框中将条件设置为公式,并在右边的框内输入上面的公式并设置好格式的图案颜色,确定后退出,看看自己的杰作吧!在这两个公式中,你可以直接输入任意RwN的值,也可以使用自定义名称引用的值.使用定义名称在改变第一组颜色条包含的工作表行数时非常容易。如,想要使得工作表中的行隔行显示,可以简单地改变N值为1.此时如果你将一个记录范围的或一个无用的单元格定义为N,只需改变这个单元格的值即可达到快速更改的效果.当然,你也可以以同样的方法定义一个名称Rw,这样,你可以将公式照搬过去,更改样式非常方便快捷。利用公式设置的这种效果不会因为插入和删除行而改变,这是手工效果所达不到的。同样,如果你因为某种特殊需要将列设置成这种格式,可以将公式改为:
=MOD(column()-Rw,N*2)+1<=N或:=MOD(column()-Rw,N*2)+1>N
相同数据的提示?
A列是项目名称,B列是金额。想在A列输入时,如有相同项能给出提示或是字体颜色变为红色。例如:A1是“联想品牌”,如果在A23中输入“联想品牌”时,能给出提示或是字体变为红色。不知要怎么做?条件格式->选中A列->公式:=IF(COUNTIF(A:A,A1)>1,TRUE,FALSE)->将格式改为红色。另外如果用条件格式设置公式为=if(A:A="联想品牌",True,False)>红色不起作用,而用
=if(A1:A30="联想品牌",True,False)>红色则能用。是什么原因?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
如何做到小于1010显示二位小数,大于1010显示一位小数如何做到小于10显示二位小数,大于10显示一位小数公式:=IF(C5>10,TEXT(C5,"0.0"),TEXT(C5,"0.00"))使用自定义单元格格式[>10]0.0;[<10]0.00;0;@
如何根据数值的正负加上“+”“-”符号选中单元格点击右键单元格格式自定义格式
[>0]"+"#;[<0]"-"#;0这百分数只能另外设置了:
[>0]"+"0.0%;[<0]"-"0.0%;0.0%
120,000显示为12.0自定义格式:#!.0,
121,999显示为12.2#!.#,
自定义单元格格式
48[=0]"男";[=1]"女";则可实现输入0显示为“男”。输入1显示为“女”。
将单元格中的数全部变成万元表示自定义单元格格式:0"."0,或:0!.0000
有何办法实现将一张表中的数据由元的单位转换为万元也就是说将表格中的所有数据同时变为原来的1/10000.请问有什么简便的方法吗?
1.在任一格中(B1)输入10000
2.游标停在B1,后按[复制]
3.选取资料范围
4.[编辑]>[选择性贴上]
5.[]
6.[确定]如果还要后面自动显示"万元"可以到格式→单元格→数字(卷标)→自订G/通用格式改成G/通用格式"万元"选择性粘贴还有这种用法,真神奇。我原来都是另选一列,用函数round(B1/10000,0),再用选择性粘贴>数值复盖原来数据,这样处理有一个好处,就是小数点后面没有那么多的数字。可以采用=ROUND(D14/10000,0)&"万元"直接得到所需格式。
常用的自定义格式单元格属性自定义中的“G/通用格式“和”@”作用有什么不同?设定成“G/通用格式“的储存格,你输入数字1..9它自动认定为数字,你输入文字a..z它自动认定为文字,你输入数字1/2它会自动转成日期。设定成“@“的储存格,不管你输入数字1..9、文字a..z、1/2,它一律认定为文字。文字与数字的不同在於数字会呈现在储存格的右边,文字会呈现在储存格的左边。常用的自定义格式拿出来大家分享我最常用的有:
1.0”文ᴀ”、0.0”文ᴀ”、0.00”文ᴀ”等(输入带单位符号的数值);
2.#”文ᴀ”、#.#”文ᴀ”、###,###.##”文ᴀ”等(同上);
3.[DBNum1][$-804]G/通用格式、[DBNum2][$-804]G/通用格式等(数值的大小写格)
4@”文ᴀ”(在原有的文ᴀ上加上新文ᴀ或数字);
50000000(发票号码等号码输入);
6.yyyy/mm
7.yyyy/m/daaaa-->ex.2003/12/20星期六
8.m""d""(ddd)-->ex.1220(Sat)
9."Subject(Total:"0")"-->单纯加上文字
10."Balance"*#,##0_-->对齐功能
11.[蓝色]+*#,##0_;-*#,##0_-->正负数的颜色变化
12.**;**;**;**-->仿真密码保护(搭配sheet保护)
49
13.[红色][<0];[绿色][>0](小于0时显示红色,大于0时绿色,都以绝对值显示)
14[>0]#,##0.00;[<0]#,##0.00;0.00(会计格式,以绝对值形式显示)
自定义格式
Excel中预设了很多有用的数据格式,基ᴀ能够满足使用的要求,但对一些特殊的要求,如强调显示某些重要数据或信息、设置显示条件等,就要使用自定义格式功能来完成。Excel的自定义格式使用下面的通用模型:正数格式,负数格式,零格式,文ᴀ格式,在这个通用模型中,包含三个数字段和一个文ᴀ段:大于零的数据使用正数格式;小于零的数据使用负数格式;等于零的数据使用零格式;输入单元格的正文使用文ᴀ格式。我们还可以通过使用条件测试,添加描述文ᴀ和使用颜色来扩展自定义格式通用模型的应用。1)使用颜色要在自定义格式的某个段中设置颜色,只需在该段中增加用方括号括住的颜色名或颜色编号。Excel识别的颜色名为:[黑色][红色][白色][蓝色][绿色][][洋红]Excel也识别按[颜色X]指定的颜色,其中X156之间的数字,代表56颜色(如图5)。2)添加描述文ᴀ要在输入数字数据之后自动添加文ᴀ,使用自定义格式为:"文ᴀ内容"@;要在输入数字数据之前自动添加文ᴀ,使用自定义格式为:@"文ᴀ内容"。
@符号的位置决定了Excel输入的数字数据相对于添加文ᴀ的位置。3)创建条件格式可以使用六种逻辑符号来设计一个条件格式:>(大于)、>=(大于等于)、<(小于)、<=(小于等于)、=(等于)、<>(不等于),如果你觉得这些符号不好记,就干脆使用“>”或“>=”号来表示。由于自定义格式中最多只有3个数字段,Excel规定最多只能在前两个数字段中包括2个条件测试,满足某个测试条件的数字使用相应段中指定的格式,其余数字使用第3段格式。如果仅包含一个条件测试,则要根据不同的情况来具体分析。自定义格式的通用模型相当于下式:[>0]正数格式;[<0]负数格式;零格式;文ᴀ格式。下面给出一个例子:选中一列,然后单击“格式”菜单中的“单元格”命令,在弹出的对话框中选择“数字”选项卡,在“分类”列表中选择“自定义”,然后在“类型”文ᴀ框中输入“"正数:"($#,##0.00);"负数:"($#,##0.00);"";"文ᴀ:"@”,单击“确定”按钮,完成格式设置。这时如果我们输入“12”,就会在单元格中显示“正数:($12.00)”,如果输入“-0.3”,就会在单元格中显示“负数:($0.30)”,如果输入“0”,就会在单元格中显示“零”,如果输入文ᴀ“thisisabook”,就会在单元格中显示“文ᴀ:thisisabook”。如果改变自定义格式的内容,“[红色]"正数:"($#,##0.00);[蓝色]"负数:"($#,##0.00);[黄色]"";"文ᴀ:"@”,那么正数、负数、零将显示为不同的颜色。如果输入“[Blue];[Red];[Yellow];[Green]”,那么正数、负数、零和文ᴀ将分别显示上面的颜色。再举一个例子,假设正在进行帐目的结算,想要用蓝色显示结余超过$50000的帐目,负数值用红色显示在括号中,其余的值用缺省颜色显示,可以创建如下的格式:“[][>50000]$#,##0.00_);[红色][<0]($#,##0.00);$#,##0.00_)”使用条件运算符也可以作为缩放数值的强有力的辅助方式,例如,如果所在单位生产几种产品,每个产品中只要几克某化合物,而一天生产几千个此产品,那么在编制使用预算时,需要从克转为千克、吨,这时可以定义下面的格式:“[>999999]#,##0,,_m""";[>999]##,_k_m"千克";#_k""”可以看到,使用条件格式,千分符和均匀间隔指示符的组合,不用增加公式的数目就可以改进工作表的可读性和效率。另外,我们还可以运用自定义格式来达到隐藏输入数据的目的,比如格式";##;0只显示负数和零,输入的正数则不显示;格式“;;;”则隐藏所有的输入值。自定义格式只
50改变数据的显示外观,并不改变数据的值,也就是说不影响数据的计算。灵活运用好自定义格式功能,将会给实际工作带来很大的方便。
怎样定义格式怎样定义格式表示如00062920020001、00062920020002只输入001、002答:格式-单元格-自定义-"00062920020"@-确定
在工具按钮之间设置分隔线工具栏中只有不同组的工具按钮才用分隔线来隔开,如果要在每一个工具按钮之间设置分隔线该怎么操作?答:先按住“Alt”键,然后单击并稍稍往右拖动该工具按钮,松开后在两个工具按钮之间就多了一根分隔线了。如果要取消分隔线,只要向左方向稍稍拖动工具按钮即可。
自定义区域为每一页的标题自定义区域为每一页的标题。方法:文件-页面设置-工作表-打印标题-顶端标题行与左顶标题列这样就可以每一页都加上自己想要的标题。
一个单元格内格式问题如果我做了一个表某一列是表示重量的,数值很多在1--------------1524745444444之间的数不等。这些表示重量的数。如果我想次给他们加上单位,但要求是单位是>999999吨,之>999是千克,其余的是克。如何办答:[>9999]###.00,"";*,*.00"千克"
定制单元格数字显示格式定制单元格数字显示格式,先选择要定制的单元格或区域,》单击鼠标右键》单元格格式》选择数字选项》选择自定义》在类型中输入自定义的数字格式。如何输入自定义的数字格式:需要先知道自定义格式中那些常用符号的含意,具体可以先不选择自定义,而选择其它已有分类观看示例,以便得知符号的意义。比如:先选择百分比然后马上选择自定义,会发现类型中出现‘0.00%’,这就是百分比的定义法,把它改成小数位3位的百分比显示法只要把‘0.00%’改成‘0.000%’就好了,把它改成红色的百分比显示法只要把‘0.00%’改成‘[红色]0.00%’就好了。
巧用定位选条件单元格Excel表格中经常会有一些字段被赋予条件格式。如果要对它们进行修改,那么首先得选中它们。可是,在工作中,它们经常还是处在连续位置。按”Ctrl”健逐列选取恐怕有点太麻烦。其实,我们可以使用定位功能来迅速查找它们。方法是点击“编辑—定位”单命令,在弹出的“定位”对话框中,点击“定位条件”按钮,在弹出的“定位条件”对话框中,选中“条件格式”单选项成为可选。选择“相同”则所有被赋予相同条件格式的单元格会被选中。
工作表的标签的字体和大小可以更改吗答:在桌面上点右键─内容─外观,相关的设定都在此更改。sheet1工作表的A1、A2、A3单元格分别链接到sheet2、sheet3、sheet4
51解答:1、=indirect("sheet"&row()+1&"!a1")《程香宙的解释:indirect是把文ᴀ变为单元格引用的函数row()是取当前行号。例如在a1输入该公式,则row()=1,公式里的值变为indirect("sheet2!a1"),跟=sheet2!a1同效,在a2输入该公式,则row()=2,公式里的值变为indirect("sheet3!a1")》2、使用插入-超级链接-书签-(选择)-确定经验技巧按“Ctrl+~”可以一次显示所有公式(而不是计算结果)。再按一次回到计算结果。
隔行用不同颜色显示,请问如何做我想将隔行用不同颜色显示,请问如何做?条件格式,自定义,公式,...格式-->自动套用格式,选择你想要的格式,确定。我现找到了一种方法,即在上下两单元格格中设计不同颜色,再选中两单元格,用格式刷刷即可。条件格式中用公式,
=mod(row()/2,color)依次类推即可,一次设置两种、三种、四种等颜色。
将单元格设置为有“凸出”的效果或“凹进去”的效果用条件格式=mod(row(),2)=mod(column(),2)方法是设定单元格的边框3楼的办法不错,但是要一个格一个格地设定,数据多了很麻烦2楼的格式里设公式能不能搞成隔一行ao隔一行tu的形式呢?格式—自动套用格式里就有。凑个热闹。边框用黑白的就可以了看来还是用条件格式更方便些!用黑白双线边框是最简单的办法
在Excel中设计彩色数字用户在使用Excel处理数据时,经常需要将某些数据以特殊的形式显示出来,这样可以起到醒目的作用,使浏览者一目了然。如在某用户的Excel单元格中有“月工资”一栏,需要小于500的显示为绿色,大于500的显示为红色,则可以采用以下的方法来操作:选中需要进行彩色设置的单元格区域,选择“格式”→“单元格”,在弹出的对话框中单击“数字”选项卡。然后选择“分类”列表中的“自定义”选项,在“类型”框中输入“[绿色][<
52500;[红色][>=500]”,最后单击“确定”按钮即可。小提示除了红色和绿色外,用户还可以使用六种颜色,它们分别是黑色、青色、蓝色、洋红、白色和黄色。另外,“[>=120]”是条件设置,用户可用的条件运算符有:“>”、“<”、“>=”、“<=”、“=”、“<>”。当有多个条件设置时,各条件设置以分号“;”作为间隔。
定义名称的妙处名称的定义是EXCEL的一基础的技能,可是,如果你掌握了,它将给你带来非常实惠的妙处!1.如何定义名称插入-名称-定义2.定义名称建议使用简单易记的名称,不可使用类似A1…的名称,因为它会和单元格的引用混淆。还有很多无效的名称,系统会自动提示你。引用位置:可以是工作表中的任意单元格,可以是公式,也可以是文ᴀ。在引用工作表单元格或者公式的时候,绝对引用和相对引用是有很大区别的,注意体会他们的区别–和在工作表中直接使用公式时的引用道理是一样的。3.定义名称的妙处1–减少输入的工作量如果你在一个文档中要输入很多相同的文ᴀ,建议使用名称。例如:定义DATA=“ILOVEYOU,EXCEL!”,你在任何单元格中输入“=DATA”,都会显示“ILOVEYOU,EXCEL!”4.定义名称的妙处2–在一个公式中出现多次相同的字段例如公式=IF(ISERROR(IF(A1>B1,A1/B1,A1)),””,IF(A1>B1,A1/B1,A1)),这里你就可以将IF(A1>B1,A1/B1,A1)定义成名称“A_B”,你的公式便简化为=IF(ISERROR(A_B),””,A_B)5.定义名称的妙处3–超出某些公式的嵌套例如IF函数的嵌套最多为七重,这时定义为多个名称就可以解决问题了。也许有人要说,使用辅助单元格也可以。当然可以,不过辅助单元格要防止被无意间被删除。6.定义名称的妙处4–字符数超过一个单元格允许的最大量名称的引用位置中的字符最大允许量也是有限制的,你可以分割为两个或多个名称。同上所述,辅助单元格也可以解决此问题,不过不如名称方便。7.定义名称的妙处5–某些EXCEL函数只能在名称中使用例如由公式计算结果的函数,在A1中输入’=1+2+3,然后定义名称RESULT=EVALUATE(Sheet1!$A1),最后你在B1中写入=RESULT,B1就会显示6了。还有GET.CELL函数也只能在名称中使用,请参考相关资料。8.定义名称的妙处6–图片的自动更新连接例如你想要在一周内每天有不同的图片出现在你的文档中,具体做法是:8.1找7张图片分别放在SHEET1A1至A7单元格中,调整单元格和图片大小,使之恰好合适8.2定义名称MYPIC=OFFSET(SHEET1!$A$1,WEEKDAY(TODAY(),1)-1,0,1,1)8.3控件工具箱–文字框,在编辑栏中将EMBED("Forms.TextBox.1","")改成MYPIC就大功告成了。这里如果不使用名称,应该是不行的。此外,名称和其他,例如数据有效性的联合使用,会有更多意想不到的结果。
53
零值问题在工作表中隐藏所有零值在Excel默认情况下,零值将显示为0,这个值是一个比较特殊的数值。如果工作表中包含了大量的零值,会使整个工作表显得十分凌乱。如果要隐藏工作表中所有的零值,可以这样操作:选择“工具”→“选项”,打开“选项”对话框,单击“视图”标签,在“窗口选项”里把“零值”复选框前面的对号去掉,单击“确定”按钮。此时,可以看到原来显示有0的单元格全部变成了空白单元格。小提示若要在单元格里重新显示0,用上述方法把“零值”复选框前面的打上对号即可。隐藏部分零值有些时候可能需要有选择地隐藏部分零值,使隐藏的零值只会出现在编辑栏或正在编辑的单元格中,而不会被打印,这时候就要通过设置自定义数字格式来实现:先按住Ctrl键用鼠标左键一一选定需要隐藏零值的单元格,然后选择“格式”→“单元格”,在“单元格格式”对话框选择“数字”选项卡,在“分类”列表框中选择“自定义”选项,然后在右边的“类型”文ᴀ框中输入“0;_0;;@”,单击“确定”按钮。要将隐藏的零值重新显示出来,可选定单元格,然后在“单元格格式”对话框的“数字”选项卡中,单击“分类”列表中的“常规”选项,这样就可以应用默认的格式,隐藏的零值就会显示出来。条件隐藏零值利用条件格式也可以实现有选择地隐藏部分零值:首先选中包含零值的单元格,选择“格式”→“条件格式”,在“条件1”的第一个框中选择“单元格数值”,第二个框中选择“等于”,在第三个框中输入0,然后单击“格式”按钮,设置“字体”的颜色为“白色”即可。如果要显示出隐藏的零值,请先选中隐藏零值的单元格,然后选择“格式”菜单中“条件格式”,单击“删除”按钮,在弹出的“选定要删除的条件”对话框中选择“条件1”即可。使用公式将零值显示为空白还可以使用IF函数来判断单元格是否为零值,如果是的话就返回空白单元格,例如公式“=IF(A2-A3=0,"",A2-A3)”,如果A2等于A3,那么它们相减的值为零,则返回一个空白单元格;如果A2不等于A3,则返回它们相减的差值。
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【汇总计算与统计】
个调税公式
=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)
54
算物价的函数物价的那个三七作五,二舍八入的尾数处理,做一个函数。就是小数点后面第二位如果12的就舍掉,如果是34567的都变为5,如果是89的小数点第一位加1第二位就变为0。比如价格是3.323.31,作尾数处理就是3.3;价格是3.333.343.36
3.37,做尾数处理就是3.35;价格是3.383.39,做尾数处理就是3.4
=CEILING(A1-0.02,0.05)都是二位小数B2=ROUND(2*A2,1)/2超过二位小数B2=ROUND(2*ROUNDDOWN(A2,2),1)/2
自动计算应收款滞纳金要求在给定的应收日期、滞纳金率、当前日期(自动取)的基础上自动计算出应收滞纳金。解答:=(DATEDIF(应收日期,NOW(),"d"))*滞纳金率(每天)*应收金额
淘汰率题目如下:这个工厂有1000人,今天抽出十人来做调查,这十人一天的产量分别为101102105106989596104110103(A3-A12)。1000人当中淘汰率为5%,以这十人为标准那么这1000人他们的生产应该为多少才不会被淘汰,看看函数的帮助就知道了呀,返回数组K百分比值点,你要1000人淘汰5人就是5/1000=0.5%=0.005,就是你以这10个抽样调查的数据为基准,只要产量达到这个数就不会被淘汰了。(95.45)
公式=PERCENTILE(A3:A12,G1)
应用公积金的一个函数我公司职工公积金比例为26%也就是个人和单位各13%,给公积金投缴人员制作了一个函数。直接用基数乘以比例基数*比例=投缴额,对于投缴额的要求是:取最接近“投缴额”的偶数。我制作的函数是“=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),CEILING(A1*B1,2))注:A1=基数B1=投缴比例也可以改成这样
=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),INT(A1*B1)+1)=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),EVEN(A1*B1))
如何利用公式将数值转为百分比格式如用公式将1.289675显示为128.97%,不是用格式来达到的。公式=ROUND(B1*100,1)&"%"
比高得分公式
=RANK(B4,$B$4:$B$26,1)
自动评定奖级
=VLOOKUP(L179,IF({1,0},$D$204:$D$207,$B$204:$B$207),2)
=LOOKUP(L179,{0,4,7,12,24},{"一等奖","二等奖","三等奖","纪念奖","纪念奖"})
55
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
对带有单位的数据如何进行求和在数据后必须加入单位,到最后还要统计总和,请问该如何自动求和?(例如:A12KG
A26KG.....,在最后一行自动计算出总KG数)。
=SUMPRODUCT(--LEFT(A1:A5,(LEN(A1:A5)-2)))&”KG”
a列动态求和可以随着a列数据的增加,在“b1”单元格=sum(x)a列动态求和。
=SUM(OFFSET(A1,0,0,COUNTA(A:A),1))
动态求和公式自A列A1单元格到当前行前面一行的单元格求和。
=SUM(INDIRECT("A1:A"&ROW()-1))
列的跳跃求和若有20列(只有一行),需没间隔3列求和,该公式如何做?假设a1t1为数据(共有20列),在任意单元格中输入公式:
=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1))ctrlshiftenter结束即可求出每隔三行之和。跳行设置:如有12行,需每隔3行求和
=SUM(IF(MOD((ROW(1:12)),3)=0,(A1:A12)))
有规律的隔行求和要求就是在计划、实际、差异三项中对后面的12个月求和。
=SUMPRODUCT(--(MOD(COLUMN(F3:AO3)-CELL("Col",F3)+0,3)=0),F3:AO3)
=SUMIF($F$2:$AO$2,C$2,$F3:$AO3)
=SUMPRODUCT((MOD(COLUMN($F3:$AO3),3)=MOD(COLUMN(F3),3))*$F3:$AO3)也可以拖动填充,插入行、列也不影响计算结果。
如何实现奇数行或偶数行求和假设数据在A1:A100
奇数行:=SUMPRODUCT(MOD(ROW($A$1:$A$100),2)*$A$1:$A$100)
偶数行:=SUMPRODUCT((MOD(ROW($A$1:$A$100),2)=0)*($A$1:$A$100))奇数行求和=SUMPRODUCT((A1:A100)*MOD(ROW(A1:A100),2))偶数行求和=SUMPRODUCT((A1:A100)*NOT(MOD(ROW(A1:A100),2)))
单数行求和隔行求和用什么函数,即:A1+A3+A5+A7+A9…公式如何用。
{=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))}
{=SUM(IF(MOD(ROW(A1:A100),2)=1,A1:A100,0))}
统计偶数单元格合计数值
56统计F4到F62的偶数单元格合计数值。=SUM(IF(MOD(ROW(F4:F62),2)=0,F4:F62))
隔行求和公式设置均为数组公式:
=SUM(IF(MOD(ROW(A1:A110),2),A1:A110,0))
=SUM(N(OFFSET($A$1,ROW(1:55)*2-2,,,)))
=SUM((MOD(ROW(A1:A100),2)=1)*(A1:A100))
=SUM((MOD(ROW(A1:A100),2)=0)*(A1:A100))
=SUMPRODUCT((MOD(ROW(A1:A100),2)=0)*A1:A100)
隔列将相同项目进行求和隔列将出勤日和工资分别进行求和数组公式=SUM(IF(($B$4:$B$25)=B26,($C$4:$C$25),0))或;
=SUMPRODUCT(--(MOD(ROW(C5:C25),2)<>0),C5:C25)
隔行或隔列加总隔2列加总
=SUM((MOD(ROW($A$1:$A$25),2)=0)*$A$1:$A$25)隔2栏加总
=SUM((MOD(COLUMN($B$1:$T$1),2)=0)*$B$1:$T$1)
请问如何在一百行内做隔行相加数组公式
A1+A3+……+A99
=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))
A2+A4+……+A100
=SUM(N(OFFSET(A1,ROW(1:50)*2-1,)))
如何将间隔一定的列的数据相加呢碰到100多列的数据将间隔一定的数据用手工相加太烦了,也容易出错。如果需要相加的数据均有相同的名称(字段),可以用Sumif()来求解,如果没有,就需要用数组公式来解决了。{=SUM((MOD(ROW(A1:A18),3)=1)*A1:A18)}1、4、7……行相加。
隔列求和(AB列)
=SUM(A:A,B:B)
=SUM(A:A,B:B,C:C)(统计ABC列)
隔列求和的公式品种及日期1月1日1月2日1月3日1月4日1月5日余额进出进出进出进出进出
A11253279813
=SUMIF($B$2:$K$2,"",B3:K3)-SUMIF($B$2:$K$2,"",B3:K3)
=SUM(SUMIF(B$2:K$2,{"",""},B3:K3)*{1,-1})
57
隔列求和
类别成品代
码单价
安贞北辰长安长春合计
库存
销售
库存
销售
库存
销售
库存
销售
库存


皮带V19201270.0012121212库存合计=SUMIF($D$3:$BS$3,"库存",$D$4:$BT$4),销售合计=SUMIF($D$3:$BS$3,"销售",$D$4:$BT$4)
=SUMIF($D$3:$BS$3,BT$3,$D4:$BS4)
=SUMPRODUCT((MOD(COLUMN($D4:$BS4),2)=0)*$D4:$BS4)
关于隔行、隔列求和的问题2列加总
=SUM((MOD(ROW($A$1:$A$25),2)=0)*$A$1:$A$25)2行加总
=SUM((MOD(COLUMN($B$1:$T$1),2)=0)*$B$1:$T$1)均为数组公式。
EXCEL中求两列的对应元素乘积之和:a1*b1+a2*b2+b3*b3...的和
=SUM(A1:A3*B1:B3)(数组公式)
=SUMPRODUCT(A1:A10,B1:B10)
计算900~1000之间的数值之和
sumif函数的计算格式为:=sumif($a$1:$a$20,">1000")。即返回$a$1:$a$20中大于
1000的数值的和,但如果想计算900~1000之间的数值之和,应该如何编写。请参考:{=SUM(IF((A1:A20>900)*(A1:A20<1000),A1:A20))}
{=SUM((900<A1:A20)*(A1:A20<1000)*A1:A20)}
2=SUMIF(A1:A20,">900")-SUMIF(A1:A20,">1000")
双条件求和
1、求一班女生的个数:
=SUMPRODUCT((A2:A9=1)*(B2:B9=""""))
2、求一班成绩的和:
=SUMIF(A2:A9,1,C2:C9)"
3、求一班男生成绩的和:
=SUMPRODUCT((A2:A9=1)*(B2:B9=""""),C2:C9)"
如何实现这样的条件求和求型号中含BC但不含ABC的量:ABC型号数量1CRVABC12100
582CVABC131023CVBC121044CNVBC13106
=SUMIF($A$2:$A$12,"*"&"bc"&"*",$B$2:$B$12)-SUMIF($A$2:$A$12,"*"&"abc"&"*",$
B$2:$B$12)
=SUMPRODUCT((ISNUMBER(FIND("BC",A2:A12))<>ISNUMBER(FIND("ABC",A2:A12
)))*B2:B12)
A1:A10数字显为文ᴀ格式时,如何求和
=SUMPRODUCT(A1:A10)
求和所有ᴀ范例所使用的数据都为引用以下绿色区域,并定义为对应的标题。
NameSexAgePositionSalary
张无忌男26主角10000
韦小宝男16主角13000
灭绝女55配角3000
周芷若女22主角8000
鳌拜男62普通演员2000
仪琳女18配角5000
岳灵珊女19配角4500
令狐冲男27主角15000
性空男88普通演员2200
东方不败不详45主角9000
A求所有演员工资总额
71700=SUM($G$7:$G$16)简单求和
B求男演员工资总额
42200=SUMIF($D$7:$D$16,"",$G$7:$G$16)单条件求和.1
C求年龄在20岁以下的演员工资
22500=SUMIF($E$7:$E$16,"<20",$G$7:$G$16)单条件求和.2
D求主角和配角的工资(不是普通演员)
67500=SUMIF($F$7:$F$16,"*",$G$7:$G$16)单条件求和.3
E20岁以下女演员工资
9500{=SUM(($D$7:$D$16="")*($E$7:$E$16<20)*$G$7:$G$16)}多条件求和-同时满足条件
F求男性或主角的工资
59200{=SUM(IF(($D$7:$D$16="")+($F$7:$F$16="主角"),$G$7:$G$16))}多条件求和-只须满足条件之一
G求男性非主角或主角非男性的工资(即除男主角外的男性和主角)
59
g.121200{=SUM(IF(($D$7:$D$16="")-($F$7:$F$16="主角"),$G$7:$G$16))}
g.221200{=SUM(IF(($F$7:$F$16="主角")-($D$7:$D$16=""),$G$7:$G$16))}
多条件求和-只满足条件之一而不能同时满足
H~~~你不知道什么是数组函数啊,可是你有时候也要用多条件求和?不要紧,教你用另外的方法:SUBTOTAL20岁以下女演员工资
71700=SUBTOTAL(9,$G$7:$G$16)现在你看到的还不是最后结果,请按如下操作
1、把数据区域设置成可筛选
2、把SEX筛选成"=",把年龄筛选成<20
3、你再看上面的公式结果…
去掉其中两个最大值和两个最小值,再求和请问如何去掉两个最高分,两个最低分,剩余人员的分数求和,例如A1-A7中的7个分,去掉两个最高分,两个最低分,剩余人员的分数求和。
=SUM(A1:A50)-MAX(A1:A50)-LARGE(A1:A50,2)-MIN(A1:A50)-SMALL(A1:A50,2)
=SUM(A1:A20)-SUM(LARGE(A1:A20,{1,2}))-SUM(SMALL(A1:A20,{1,2}))
=TRIMMEAN(A1:A7,4/7)*(7-4)
=SUMPRODUCT(LARGE(A1:A7,ROW(A1:A7))*(ROW(1:7)>2)*(ROW(1:7)<6))
=SUMPRODUCT((A$1:A$7<LARGE(A$1:A$7,2))*(A$1:A$7>SMALL(A$1:A$7,2))*A$1:A$7)
=SUM(A!:A7)-LARGE(A!:A7,1)-LARGE(A!:A7,2)-SMALL(A!:A7,1)-SMALL(A!:A7,2)将此函数横着使用(A1-G1
=TRIMMEAN(A1:G1,4/7)*(7-4)
=SUMPRODUCT(LARGE(A1:G1,COLUMN(A1:G1))*(COLUMN(A:G)>2)*(COLUMN(A:G)<6))
去掉两个最高分、最低分,显示出被去掉的分数被去掉的分数:最大两个:=large(data,{1;2})最小两个:=small(data,{1;2})
永恒的求和
1=SUM(OFFSET(A1,,,ROW()-ROW(A1)))可以对A列数值自动求和。
2=SUM(INDIRECT("R2C:R[-1]C",FALSE))
3、=SUM(INDIRECT("A2:A"&ROW()-1))
=SUM(INDIRECT(ADDRESS(1,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))
按字体颜色求和做法:
G3={SUM(IF(($A$2:$A$19=E3)*($B$2:$B$19=F3),$C$2:$C$19))}
G4:G11公式为G3公式下拖.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
如何分班统计男女人数
60=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1))
=SUMPRODUCT(($B$2:$B$446=E3)*($C$2:$C$446=F$1))
=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1))
=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$F$1))=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$G$1)*$D$2:$D$446)=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=G$1))合计=COUNTIF($B$2:$B$446,E2)
统计数值大于等于80的单元格数目C17单元格中输入公式:
=COUNTIF(B1:B13,">=80")确认后,即可统计出B1B13单元格区域中,数值大于等于80的单元格数目。
计算出A1里有几个abc
A1:abc-ded-abc-def-abc-ded-ded-abc,如何计算出A1里有几个abc公式=(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc")
有条件统计如何统计当A1<=15时,统计B列中<=8.5的累加值和个数,而>15时不进行统计?个数:
=IF(A1>15,"",COUNTIF(B2:B10,"<=8.5"))累加值(求和):
=IF(A1>15,"",SUMIF(B2:B10,"<=8.5"))
如何统计各年龄段的数量需分别统计20岁以下、21-30岁、31-40岁、41-50岁、50岁以上年龄段的数量。根据“出生日期”用以下公式,得到“自动显示年龄”。先将F列的出生日期设置为“19765月”格式,在G列公式为:
=DATEDIF(F2,TODAY(),"Y")(周岁,自动显示年龄)
=YEAR(TODAY())-YEAR(F2)再根据年龄段:20岁以下、21-30岁、31-40岁、41-50岁、50岁以上,用以下公式,求出不同年龄段人数。J2公式为:
=SUMPRODUCT(($G$2:$G$34>$H1)*($G$2:$G$34<=$H2)*($C$2:$C$34=J$1))=SUM(($G$2:$G$34<=VALUE(MID(I2,1,2)))*1)或数组公式:
=SUM(($G$2:$G$34<=VALUE(MID(I3,4,2)))*1)-SUM($J$2:J2)
如何计算2020-50岁的人数?
=COUNTIF(C3:C17,">=20")-COUNTIF(C3:C17,">50")
=SUMPRODUCT((C3:C17>=20)*(C3:C17<=50))
=FREQUENCY(C3:C17,50)-FREQUENCY(C3:C17,19)=SUM(COUNTIF(C3:C17,">="&{20,51})*{1,-1})
61
如何统计4040-50岁的人的个数countif(a:a,">40")-countif(a:a,">50")
SUM(COUNTIF(a:a,">"&{40,50})*{1,-1})数组公式{=sum((a1:a7>40)*(a1:a7<50))}
SUMPRODUCT((A1:A7>40)*(A1:A7<50))
要统计出7岁的女生人数
=COUNTIF(D2:D12,D2)
=SUMPRODUCT((B2:B12="")*(D2:D12=7))
统计人数
=COUNTA(A:A)
=COUNTIF(A:A,">")
如何统计A1:A10D1:D10中的人数?
=COUNTA(A1:A10,D1:D10)
如何让EXCEL自动从头统计到当前单元格情况如下:C列要根据A列的内容来统计B列的数据,范围从A1:An,即当A列中An有数据时,Cn自动根据An的值,统计B1:Bn的数据。
=SUM(INDIRECT("B1:B"&LARGE((A1:A65535<>"")*(ROW(A1:A65535)),1)))
统计人数建议提建议人员姓名提建议人数建议1王、李、赵、孙、钱、胡6建议2张、王、李、赵、孙、钱、胡7建议3张、王、李、孙、钱、胡6
=LEN(B2)-LEN(SUBSTITUTE(B2,"",""))+1
=LEN(SUBSTITUTE(B2,"",""))
统计人数见表:性别年龄男6女35男3男55男21男53.5女55女56男65女45
62女53男51如何计算20-50岁的人数?
=COUNTIF(C3:C17,">=20")-COUNTIF(C3:C17,">50")
=SUMPRODUCT((C3:C17>=20)*(C3:C17<=50))
=FREQUENCY(C3:C17,50)-FREQUENCY(C3:C17,19)=SUM(COUNTIF(C3:C17,">="&{20,51})*{1,-1})如何计算男20-50岁的人数?
=SUMPRODUCT((B3:B17="")*(C3:C17>=20)*(C3:C17<=50))
求各分数段人数
90—100=COUNTIF(B2:B43,">=90")
80—89=COUNTIF(B2:B43,">=80")-COUNTIF(B2:B43,">=90")
70—79=COUNTIF(B2:B43,">=70")-COUNTIF(B2:B43,">=80")
60—69=COUNTIF(B2:B43,">=60")-COUNTIF(B2:B43,">=70")
50—59=COUNTIF(B2:B43,">=50")-COUNTIF(B2:B43,">=60")
有什么方法统计偶数例如:A1到E1有5个数如何统计着五个数中有几个是偶数ABCDEF15015811153在F1中的3要用什么公式能统计出来统计偶数的个数
=COUNT(1/MOD(A1:E1-1,2))
=Sum(Mod(a1:e1+1,2))将偶数转化成奇数,再求奇数的个数。请在编辑栏中选择部分公式按F9观察每一步的计算过程。
{=SUM(--((A1:F1)/2=INT((A1:F1)/2)))}算是一法,长了点
=SUMPRODUCT((MOD(A1:E1,2)=0)*1)
=SUMPRODUCT(1-MOD(A1:E1,2))
如何显示如果D2>20E2就显示$200、如果D2>30E2就显示$300依此类推解答:=INT(D2/10)*100,当然,你的单元格格式设置成$格式就可以了。否则用,
="$"&INT(D2/10)*100则该单元格成字符型。当然,你也可以用IF函数,但它有7层的限制。=IF(D2>30,
"300",IF(D2>20,"200"))
工资统计中的问题问题:表一和表二中的职工姓名相同,但不在同一个位置上。怎样用公式求出表一中职工在表二中对应的工资、奖金和值班费的总额。要求,不能用表二中先加入一列,然后求和,再用公式导入表一的方法。我想知道能否在表一中用一个公式就可实现,而表二不动。
=SUMPRODUCT((表二!$B$3:$B$42=A3)*(表二!$C$3:$E$42)+(表二!$G$3:$G$42=A3)*(
!$H$3:$J$42))
63
=IF(COUNTIF(表二!$B$3:$B$42,A3),SUMPRODUCT(VLOOKUP(A3,!$B$3:$E$42,{2;3;4},)),SUMPRODUCT(VLOOKUP(A3,表二!$G$3:$J$42,{2;3;4},)))
=IF(ISERROR(MATCH(A3,表二!$B$3:$B$42,0)),SUM(OFFSET(表二!$G$2,MATCH(A3,
!$G$3:$G$42,0),1,,3)),SUM(OFFSET(表二!$B$2,MATCH(A3,表二!$B$3:$B$42,0),1,,3)))
=IF(ISERROR(VLOOKUP(A3,表二!$B$3:$F$42,4,0)),SUM(INDIRECT("!H"&MATCH(A3,表二!$G$3:$G$42,0)+2&":J"&MATCH(A3,!$G$3:$G$42,0)+2)),SUM(INDIRECT("表二!C"&MATCH(A3,!$B$3:$B$42,0)+2&":J"&MATCH(A3,表二!$B$3:$B$42,0)+2)))
=IF(ISERROR(VLOOKUP(A3,表二!$B$3:$F$42,4,0)),VLOOKUP(A3,
!$G$3:$J$42,4,0),VLOOKUP(A3,表二!$B$3:$F$42,4,0))
统计数据问题一例如果我想统计50个数据中大于某个值的数据个数,(这个值是在使用时才输入某个单元格的),请问用什么函数。如数据单元格为A1:E10,值的单元格为A11。1、使用下面的数组公式:{=SUM(IF($A$1:$E$10>$A$11,1))}2、输入以下函数:=COUNTIF(A1E10,">"&A11)
根据给定的条件,对数据进行合计实例:姓名件数(姓名在B307-B313中;件数在C307-C313中)李六12王武50李六18陈丰187李六49王武135陈丰1584目的:对上面三个人的件数分别进行统计分析步骤:李六的:=SUMIF(B307:B313,B323,C307:C313)王武的:=SUMIF(B307:B313,C323,C307:C313)陈丰的:=SUMIF(B307:B313,D323,C307:C313)姓名:李六王武陈丰(分别在B323C323D323单元格中)结果:791851771
十列数据合计成一列
=SUM(OFFSET($1,(ROW()-2)*10+1,,10,1))
统计汉字字符个数中国A1"中国",A2"人民",A3中是空白,A4中是"幸福",A5,A6中是空白人民258
258幸福
247大家好中国147函数结果说明
64
=SUMPRODUCT(LENB(ASC(A1:A6))-LEN(A1:A6))11仅统计汉字字符个数
=SUMPRODUCT(LEN(A1:A6))23如果还混杂有其它字符
关于取数购进日期付款期7月5日2007-8-256月5日2007-7-257月18日2007-9-157月26日2007-9-15注:我想在B列的付款期中得到这样的结果:付款期=(购进日期+45天),但我们的付款期只有每月15和25号,如果按购进日期加上45天后不正好是付款日,那就得再往后延到最近的一个付款日,也就是15或25号。
=MIN(IF(DAY(A2+ROW($45:$70))={15,25},A2+ROW($45:$70)))=MIN(IF(DAY(A2+ROW($45:$70))={15,25},A2+ROW($45:$70),999999))
=MIN(IF((DAY(A2+ROW($45:$67))=15)+(DAY(A2+ROW($45:$67))=25),A2+ROW($45:$6
7)))
=IF(DAY(A2+45)<15,TEXT(A2+60-DAY(A2+45),"mmdd
"),TEXT(A2+70-DAY(A2+45),"mmdd"))
=DATE(YEAR(A2+45),IF(DAY(A2+45)>25,MONTH(A2+45)+1,MONTH(A2+45)),IF(DAY(A
2+45)<=15,15,IF(DAY(A2+45)<=25,25,15)))
统计单元格内不为空的格数如下图,怎么自动统计单元格内的“√”,而空白的单元格则不计入内?
=counta(a2:a31),下拉
=countif(a2:a31,"")
=COUNTIF(a2:a31,"<>")
自动将销量前十名的产品代号及销量填入表中如:产品代号在“B”列,销量在“C”列
=INDIRECT("b"&MATCH(ROW(A1),$D$2:$D$20,0)+1)
=INDIRECT("c"&MATCH(ROW(A1),$D$2:$D$20,0)+1)
统计最大的连续次数
65如图,请问如何编写公式求出A1到A10单元格中数字4连在一起的次数,ᴀ例中答案应为3(A1到A3)和2(A9到A10)。[1]A1到A10单元格中,数字4连在一起,最大的连续次数,公式为:
{=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A10),""
)),1)}[2]次大的连续长次数,公式为:
{=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A10),""
)),2)}
3个“不重复”个数统计
=SUM(--IF(MATCH(B$2:B$21,B$2:B$21,0)=ROW(B$2:B$21)-1,B$2:B$21>B2))+1
=SUM(--(IF(FREQUENCY(B$2:B$21,B$2:B$21),B$2:B$21>B2)))+1
=SUM(--(FREQUENCY(IF(B$2:B$21>B2,B$2:B$21),B$2:B$21)>0))+1
在一列有重复的姓名中,如何统计出具体有几人如果第一个张三在A1单元格,在B1处输入:
=IF(COUNTIF($A$1:A1,A1)>1,"",A1)向下复制即可用数组公式也可以解决呀:假设你要统计A1到A100可以这样:
=sum(1/countif(a1:a100,a1:a100),然后按住crtl,shift,和回车就可以了。
计数的问题这个例子主要是计数的问题:共有三列数据,分别统计每列字母的个数、每列有几个不同的字母,最后把它们分别列出来。对每列字母个数统计,字符用COUNTA(),数字可以用
COUNT()COUNTA()。公式分别为:
=COUNT(A2:A12)
=COUNTA(B2:B12)
=COUNTA(C2:C12)每列不相同的字母,公式分别为:
=SUM(1/COUNTIF(A$2:A$12,A$2:A$12))=SUM(1/COUNTIF(B$2:B$12,B$2:B$12))
=SUM(1/COUNTIF(C$2:C$12,C$2:C$12))分别列出来,公式分别为:
=IF(SUM(1/COUNTIF(A$2:A$12,A$2:A$12))>=ROW(A1),INDEX(A$2:A$12,SMALL(IF(
ROW(A$2:A$12)-1=MATCH(A$2:A$12,A$2:A$12,0),ROW(A$2:A$12)-1,"0"),ROW(A1))),"
END")=IF(SUM(1/COUNTIF(B$2:B$12,B$2:B$12))>=ROW(B1),INDEX(B$2:B$12,SMALL(IF(R
66
OW(B$2:B$12)-1=MATCH(B$2:B$12,B$2:B$12,0),ROW(B$2:B$12)-1,"0"),ROW(B1))),"EN
D")
=IF(SUM(1/COUNTIF(C$2:C$12,C$2:C$12))>=ROW(C1),INDEX(C$2:C$12,SMALL(IF(
ROW(C$2:C$12)-1=MATCH(C$2:C$12,C$2:C$12,0),ROW(C$2:C$12)-1,"0"),ROW(C1))),"
END")
列1列2列3
1mB
2nB
3mC
1nD
1mA
2mB
3nC
2nD
1mA
2nA
1mB对每列字母个数统计:
11111111每列不相同的字母有:
324它们分别是:
1mB
2nC
3ENDD
ENDA
END
如何分班统计男女人数姓名班别性别高健丽1女蔡美燕2女张玉玫3女蔡文文4女陈娇娇5女吴振宇1男周婷婷6女肖欣6女梁丽宝5女邱晓雯4女李春梅3女龙玉桦2女阮梅英1女
67梁光昕2男………

=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1))
=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=G$1))男{=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$F$1))女{=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$G$1))
男{=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$G$1)*$D$2:$D$446)
女{=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$H$1)*$D$2:$D$446)增加d列,输入公式:=B2&C2,合并数据后再利用countif公式对D列统计。
=COUNTIF($B$2:$B$446,E2)
在几百几千个数据中发现重复项我的意思不是查找功能,那个我会用,比如有几百个人的名字输入单元格中,但我面对那么多名字真无法短时间内看出谁重复了,该如何办?假设判断区域为A1:D10,格式/条件格式,选公式(不是数值),输入:
=COUNTIF($A$1:$D$10,A1)>1然后在格式中设置一个字体或图案颜色,确定,这样重复数据就变成了有色单元格。
统计互不相同的数据个数例如,3*3的区域中统计互不相同的数据个数,
123
321
120结果应为4(4个互不相同的数据)数组公式=sum(1/countif(a1:c3,a1:c3))还可以公式:
=COUNT(IF(FREQUENCY(A1:C3,A1:C3),1))
多个工作表的单元格合并计算
=Sheet1!D4+Sheet2!D4+Sheet3!D4,更好的=SUM(Sheet1:Sheet3!D4)
单个单元格中字符统计假设A1单元格中有数据"sdfsfjksfhweofiefondsfljsdfisdofjei"如何用公式统计出A1单元格中有多个不重复的字符?
=SUMPRODUCT(--(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(97:122)),""))=1))数组公式=SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),A1)),,1))这个公式只适用单元中的字符为小写字母,给个通用点的
=SUM(--(MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),MID(A2,ROW(INDIREC
T("1:"&LEN(A2))),1),)=ROW(INDIRECT("1:"&LEN(A2)))))
=SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),LOWER(A1))),,1))
数据区包含某一字符的项的总和,该用什么公式
=sumif(a:a,"*"&"某一字符"&"*",数据区)班别男女总人数129457423044743304474431437453044746304575
68
函数如何实现分组编码对数值进行分组编码
=A2&TEXT(COUNTIF($A$2:A2,A2),"00")
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【数值取整及进位】
取整数函数
907.51034.21500要改变为90810351500公式为:
=CEILING(A1,1)
90710341500要改变为91010401500公式为:
=CEILING(A1,10)如果要保留到百位数,即改变为100011001500公式为:
=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)
四舍五入公式
=ROUND()
=ROUND($B$1*A1,2)
=ROUND(B1*A1,2)
=round(a1,0)
=round(a1,0)*0.95
对数字进行四舍五入对于数字进行四舍五入,可以使用INT(取整函数),但由于这个函数的定义是返回实数舍入后的整数值。因此,用INT函数进行四舍五入还是需要一些技巧的,也就是要加上0.5,才能达到取整的目的。公式应写成:
=INT(B2*100+0.5)/100
如何实现“见分进元”在我们的工资中,有一项“合同补贴”,只要计算结果出现“分”值就在整数“元”进一位,也就是说3.01元进到4.00元,3.00元不变,整数“元”不变。
=IF((A3-INT(A3))>=0.3,IF((A3-INT(A3))>=0.8,1,0.5),0)+INT(A3)
69IF(RIGHT(FIXED(A1,2),2)>B1,TRUNC(A2)+1,A2)说明一下:A1即是要转换的目标;B2输入00(文ᴀ格式,必须是00这两个数)。
IF(INT(A1)<>A1,INT(A1)+1,A1)
=ROUNDUP(A1,0)
=CEILING(A9,1)
=INT(A9+1)
四舍五入如何将Excel中的数据,希望把千位以下的数进行四舍五入,例如:3245希望变成3000
3690希望成为400
=ROUND(C6*D6,2)
=ROUND(A2*0.001,)*1000
=ROUND(A2,-3)
=--FIXED(A2,-3)
=ROUND(A2/1000,0)*1000
如何四舍五入取两位小数如何四舍五入取两位小数,2.1452.15,0.14490.14.
=ROUND(A1,2)
根据给定的位数,四舍五入指定的数值对整数无效。四舍五入B234的数值,变成小数点后一位。
12512.251412512.3
=ROUND(B23,1)
四舍六入
=IF(MOD(INT(A1),2)=0,IF(MOD(A1,1)=0.5,INT(A1),INT(A1+0.5)),INT(A1+0.5))
=IF(AND(RIGHT(A1*100,1)="0",RIGHT(A1*10,1)="5")=TRUE,IF(INT(A1)/2=INT(INT(A
1)/2),INT(A1),ROUND(A1,0)),ROUND(A1,0))
AND(RIGHT(A1*100,1)="0",RIGHT(A1*10,1)="5")=TRUE判断是否为一位小数,且是
0.5,如果不符合上术要条件,按普通四舍五入法则处理,否则判断整数部分的奇偶。
=IF(RIGHT(A1,1)*1<5,INT(A1),IF(RIGHT(A1,1)*1>5,INT(A1)+1,IF(MOD(ROUND(A1,),2)
=0,ROUND(A1,),ROUNDDOWN(A1,))))
=IF(ROUNDUP(A1*2,)=A1*2,IF(MOD(ROUND(A1,),2)=1,ROUNDDOWN(A1,),ROUNDUP(
A1,)),ROUND(A1,))
如何实现23做工资时,常遇到:3.2元要舍去0.2元变为3.00,3.3元要把0.3元入为0.5元变为
3.5.请教,该如何实现?
=ROUND(A1*2,0)/2
=CEILING(A1,0.5)
=IF((A1-INT(A1))<=0.2,INT(A1),IF((A1-INT(A1))<=0.5,INT(A1)+0.5,IF((A1-INT(A1))<=0.7,
INT(A1),INT(A1)+1)))
=CEILING(A1-0.2,0.5)
70
=FLOOR(A1+0.2,0.5)
怎么设置单元格以千元四舍五入比如输入123456,显示出来123000
=CEILING(ROUND(A1/1000,0),1)*1000
=round(a1,-3)
=mround(A1,1000)
ROUND函数的四舍五入不进位的解决方法?计算一:A2=1345.3B2=1232.4C3=A2-B2=112.9D=0.05
E=ROUND(B2*D2,2)=5.64(计算结果为5.645,此运算没有进位)。计算二:A2=1225.4B2=1112.5C3=A2-B2=112.9D=0.05
E=ROUND(B2*D2,2)=5.65(计算结果为5.645,此运算进位)。以上两式中C3结果都为112.9,而为什么应用ROUND函数后结果却不一样。请教高手有什么函数能保证四舍五入不会出错。可将C列先变成文ᴀ性数据,再进行后面的运算,以达到计算的目的。如:C列可改成C1=TRIM(A1-B1),以此类推,只要是更改成文ᴀ性数据就行。
保留一位小数我需要保留一位小数,不管后面是什么数字,超过5或不超过5,都向前进一位.例如:329.99-->330.00329.84----->329.90329.86----->329.90
=roundup(*,2)=round(a1+0.04,1)
如何三舍四入
=round(原数值+0.001,2)
另类四舍五入我用Excle给别人算帐,由于要对上百家收费,找零却是个问题。于是我提出四舍五入,收整元。但是领导不同意,要求收取0.5元。例如:某户为123.41元,就收123.50元;如果是58.72元,就收58.5元。这可难坏了我。经过研究,我发现,可以在设置单元格中,设成分数,以2为分母,可以解决问题。但是打印出来的却是分数不好看,而且求和也不对。请各位高手给予指点。是这样的,如果是57.01元,则省去,即收57.00元;如果是57.31元,则进为57.50元;如果是57.70元,也收57.50元;要是57.80元,则收58.00元。假设数据在A1
=INT(A1)+IF((A1-INT(A1)<=0.3),0,IF((A1-INT(A1)>0.7),1,0.5))简化一下:
=INT(A1)+0.5*((A1-INT(A1)>0.3)+(A1-INT(A1)>0.7))
int函数取整数部分,A1-int(A1)取小数部分,根据你的意思:<=0.30算,0.3~0.7()0.5算,0.7~0.99……按+1则:第一个公式不难理解了简化公式中:“*((A1-INT(A1)>0.3)+(A1-INT(A1)>0.7))”即(小数部分>0.3)+(小数部分>0.7)我们知道这是省略if的判断语句,条件为真返回true(也就是1)否在为false0),那么如
71果小数<=0.3,则两个条件都为0,即整数部分+0.5*0=整数部分,介于0.3~0.7,则为整数部+0.5*1+0),大于0.7肯定也大于0.3啦,则为整数部分+0.5*1+1)。请问,如果是由几个分表汇总的总表想如此处理,该如何做。例:e112位置=SUM(一库入库!G112,二库入库!G112,四库入库!G112,保健酒基地入库!G112,下陆仓库入库!G112)汇总的结果为100.24,而我要求如果小数为24的话自动视为1累加,否则不便。就是小数为0.24才加1,否则都舍掉?若是:=ifsum公式-intsum公式)=0.24intsum公式)+1sum公式)
想把小数点和后面的数字都去掉,不要四舍五入比如:
12.30变成12.00
45.3245.00
25.3825.00
6.546.00
13.0213.00
59.6859.00
23.6223.00
=Rounddown(A1,0)你要把A1换成你要转换的那个单元格啊,然后拖动就可以了!我那里用的那个A1只是告诉你一个例子而已,你要根据你的实际情况来修改一下才能用的。
=INT(A1)
=TRUNC(A1,0)
求真正的四舍五入后的数请教如何在Excel中,求“金额合计”(小数点后二位数)时,所取的数值应是所求单元格中写的数字(四舍五入后的数字),而不是(四舍五入前)的数字。因为只有这样行和列及关联的工作表才能对得上,例如:表上的数值分别是:(1.802/2=0.901)0.90(A1);(1.604/2=0.802)0.80(A2);(1.406/2=0.703)0.70(A3);(因取小数点后二位)。合计数(A4)表中自己计算和显示是:(0.901+0.802+0.703=2.406)2.41(四舍五入后的数值)。但照表中的数值人工计算却是:(0.9+0.8+0.7=)2.4,有矛盾,还有许多例子,故请教各高手,如何设置公式,使得人工计算结果同表中一致。请指教。十分感谢!工具》选项》重新计算》以显示精度为准前打钩也可以用函数ROUND()使结果四舍五入。如ROUND(算式,2)代表保留两位小数,ROUND(算式,1)代表保留一位小数。
小数点进位小数点进位如何把1.4进成21.3进成2
=Ceiling(A1,1)
=Roundup(A1,0)
=INT(A1+0.9)
=int(a1)+1如何把1.4进成2,而1.2不进位
72
=ROUND(A1+0.1,0)
个位数归0或者归5
A*B后想得到C的结果值,用什么函数比较好
ABC(想得到的数值)
3201.1355
11401.21370
501.365
161.425
=FLOOR(A1*B1+5*(MOD(A1*B1,5)<>0),5)
=CEILING(A1*B1,5)
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【大小值或中间值】
求平均值如在列中有一组数字:1079272
=AVERAGE(A2:A6)上面数字的平均值为11行公式=AVERAGE(B2:D2)
如何实现求平均值时只对不等于零的数求均值?
AVERAGE(IF(A1:A5>0,A1:A5))
平均分的问题假设一个班有60人,要统计出各个学科排名前50的学生的平均分,用公式应该如何写?如果用排序再来算的话很麻烦,能不能直接用公式找出前50名进行计算?
{=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:50"))))}
怎样求最大值(最小值或中间值)
=IF(A2="","",MAX(OFFSET(C2,,,MIN(IF(A3:$A$15<>"",ROW(3:$15),15))-MAX(($A$2:A
2<>"")*ROW($2:2)))))
=IF(A2="","",MAX((LOOKUP(ROW($A$2:$A$14),IF($A$2:$A$14<>"",ROW($A$2:$A$14
)),$A$2:$A$14)=A2)*$C$2:$C$14))
=IF(A2="","",LOOKUP(2,1/FIND(A2,$B$2:$B$1000),$C$2:$C$1000))
=IF(A2="","",MAX(IF(ISNUMBER(FIND(A2,$B$2:$B$1000)),$C$2:$C$1000)))
平均数怎么弄如在列中有一组数字:1079272公式为:
=AVERAGE(A2:A6)上面数字的平均值为11
=AVERAGE(A2:A6,5)上面数字与5的平均值为10
去掉其中两个最大值和两个最小值的公式
73我要将一行数据进行处理。要去掉其中两个最大值和两个最小值,不知道怎样运用公式,应该是:
=SUM(A1:A50)-MAX(A1:A50)-LARGE(A1:A50,2)-MIN(A1:A50)-SMALL(A1:A50,2)这个只能减去1个最大和1个最小值,不符合题意。可用下面的公式。
=SUM(A1:A20)-SUM(LARGE(A1:A20,{1,2}))-SUM(SMALL(A1:A20,{1,2}))
去一行最高分最低分求平均值去一行中一个最高分和一个最低分求平均值公式为:=(SUM(A5:E5)-MAX(A5:E5)-MIN(A5:E5))/(COUNTIF(A5:E5,">0")-2)但另用TRIMMEAN()函数较好。=TRIMMEAN($A$5:$E$5,2/COUNT($A$5:$E$5))为需要进行整理并求平均值的数组或数值区域。TRIMMEAN(array,percent)为计算时所要除去的数据点的比例,例如,如果percent=0.2,在20个数据点的集合中,就要除去4个数据点(20x0.2):头部除去2个,尾部除去2个。用活了TRIMMEAN函数,这个问题易如反掌。
9个数值中去掉最高与最低然后求平均值假设9个数值所在的区域为A1A9
=(SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9))/7
=TRIMMEAN(A1:A9,2/COUNTA(A1:A9))
=TRIMMEAN(A1:A9,2/9)=AVERAGE(SMALL(A1:A9,ROW(2:8)))
=ROUND((SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9))/(COUNT(A1:A9)-2),3)
=TRIMMEAN(A1:A9,0.286)
求最大值(n列)
{=MAX(($A$2:$A$16=$D$2)*($B$2:$B$16))}=LARGE(IF(FREQUENCY(N3:AT3,N3:AT3),TRANSPOSE(N3:AT3)),ROW(A1))
=LARGE(IF(FREQUENCY(TRANSPOSE(N3:AT3),TRANSPOSE(N3:AT3)),(N3:AT3)),R
OW(A1))
如何实现求平均值时只对不等于零的数求均值?
=TRIMMEAN(IF(A1:A5>0,A1:A5))
得到单元格编号组中最大的数或最小的数对字符格式的数字不起作用。
=MAX(B16:B25)
=MIN(B16:B25)(得到最小的数的公式)
标记出3个最大最小值
=RANK(B4,$B4:$Q4)+COUNTIF($B4:B4,B4)<=4
=RANK(B4,$B4:$Q4,2)+COUNTIF(B4:$Q4,B4)<=4
=(COUNTIF($B3:$Q3,">"&B3)+COUNTIF($B3:B3,B3))<=3
=(COUNTIF($B3:$Q3,">"&B3)+COUNTIF(B3:$B3,B3))>COUNT($B3:$Q3)-3
=SMALL(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4)/10000,{1,2,3})
74
=LARGE(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4)/10000,{1,2,3})
=RANK(B8,$B8:$Q8)+COUNTIF($B8:B8,B8)-1<=3
=RANK(B8,$B8:$Q8)+COUNTIF($B8:B8,B8)-1>COUNT($B8:$Q8)-3C4+COLUMN(C4)/10000>LARGE(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4)/10000,4)
取前五名,后五名的方法
{=LARGE(IF(ISERROR($D$2:$D$57),0,$D$2:$D$57),ROW())}
{=SMALL(IF(ISERROR($D$2:$D$57),0,$D$2:$D$57),ROW())}
{=LARGE(IF(ISERROR(D$2:D$57),"",D$2:D$57),ROW(1:5))}
{=SMALL(IF(ISERROR(D$2:D$57),"",D$2:D$57),ROW(1:5))}
=LARGE(B$2:B$57,ROW(A1))
=SMALL(B$2:B$57,ROW(A1)+COUNTIF(B$2:B$57,0))
=LARGE(D$2:D$57,ROW(A1))
=SMALL($D$2:$D$57,5-MOD(ROW(A5),5))
如何用公式求出最大值所在的行?如A1:A10中有10个数,怎么求出最大的数在哪个单元格?
=MATCH(LARGE(A1:A10,1),A1:A10,0)
=ADDRESS(MATCH(SMALL(A1:A10,COUNTA(A1:A10)),A1:A10,0),1)
=ADDRESS(MATCH(MAX(A1:A10,1),A1:A10,0),1)
{=ADDRESS(MATCH(MAX(LEN(A1:A10)),LEN(A1:A10),FALSE),1)}
{=ADDRESS(SUM(($A$1:$A$10=MAX($A$1:$A$10))*(ROW($A$1:$A$10))),SUM(($A$1:$
A$10=MAX($A$1:$A$10))*(COLUMN($A$1:$A$10))))}如有多个最大值的话呢?如何一一显示其所在的单元格?
{=IF(ROW(1:1)<=COUNTIF($A$1:$A$100,MAX($A$1:$A$100)),ADDRESS(LARGE(IF($A$1:
$A$100=MAX($A$1:$A$100),ROW($A$1:$A$100)),ROW(1:1)),1),"")}
求多个最高分语文成绩有多个最高分,如何用公式的方法把他们抽出来(动态)?
B15=INDEX(A:A,SMALL(IF(B$2:B$10=MAX(B$2:B$10),ROW($2:$10),65536),ROW(1:1)))
&""数组公式,按下Ctrl+Shift+Enter结束。如果增加一个条件,就是在姓名前加一个类别,例如前5个人是A类的,4个是B类的,请分类找出A类和B类的对应姓名的最高分
=INDEX(B:B,SMALL(IF(C$2:C$10=MAX(IF($A$2:$A$10="A",$C$2:C$10)),ROW($2:$10),
IF(C$2:C$10=MAX(IF($A$2:$A$10="B",$C$2:$C$10)),ROW($2:$10),65536)),ROW(1:1)))&
""
如何求多条件的平均值应如何求下表中1月份400g重量的平均值月份规格重量
1400g401
1400g403
2400g402
75
2400g404
1200g201
1200g203
2200g202試試這個行不行
=SUMPRODUCT(($A$4:$A$10=1)*($B$4:$B$10="400g"),($C$4:$C$10))/SUMPRODUCT((
$A$4:$A$10=1)*($B$4:$B$10="400g"))比较土的办法
{=SUM(IF(($A$1:$A$7=1)*($B$1:$B$7="400g"),C1:C7,0))/SUM(IF(($A$1:$A$7=1)*($B$1:$
B$7="400g"),1,0))}数组公式:{=AVERAGE(IF(B2:B8="400g")*(A2:A8=1),(C2:C8),""))}另一个数组公式试试:=Average(if((a1:a10=1)*(b1:b10="400g"),c1:c10))
=SUMIF(B1:B7,B1,C1:C7)/COUNTIF(B1:B7,B1)这个也可以
想求出第三大之数值A1A4分别为1,2,2,3.想求出第三大之数值"1",应如何设公式。
=large(if(frequency(a1:a4,a1:a4),a1:a4),3)数组公式的解法
=LARGE((MATCH(A1:A10,A1:A10,)=ROW(1:10))*A1:A10,3)
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【查询和查找引用】
查找顺序公式
=LOOKUP(2,1/(A1:A20<>0),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))用数组公式
76
=INDEX(E1:E7,MAX(IF((B4:B7=B11),ROW(B4:B7),0)))
查找及引用如何查找并引用B2单元格中所显示日期当日的相应代码的值。
B3=IF(COUNTIF($E$3:$E$20,A3),VLOOKUP($A3,$E$2:$M$20,MATCH(B$2,$F$2:$M$2,)
+1,),"")
查找函数的应用我想在A5输入表的名称,B5自动跳出该表中B列的最后一个有效数值,请问B5的公式该如何设定?
=LOOKUP(9E+307,INDIRECT(A5&"!"&"B:B"))
B2=IF(A2="","",LOOKUP(9E+307,INDIRECT(A2&"!B:B")))
怎么能方便的判断某个单元格中包含多少个指定的字符?例:A1中是“ASAFAG”,我希望计算出A1里面有多少个“A”......
=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))
如何用查找函数一、要求:利用公式从左表中查询相应的地区,结果放在H14单元格
=VLOOKUP(G14,IF({1,0},D14:D18,C14:C18),2,)
h14=OFFSET(C14,MATCH(G14,D14:D18,0)-1,,,)
H14=INDIRECT("c"&MATCH(G14,D:D,))二、要求:根据C25单元格的商品名称,查找该商品的最新单价,即该商品最后一条记录的单价(结果放在D25单元格)。用数组公式:
=INDIRECT("G"&MAX((D14:D22=C25)*ROW(D14:D22)))
D25=LOOKUP(2,1/(D14:D22=C25),G14:G22)
日期查找的问题我有一个日期比如:2007/02/12,我想知道它减去一个固定天数比如6,最接近它的一个星期四(只能提前)是多少号
2007/02/12的答案应该是2007/02/01而不是2007/02/08日期在A1处,B1处输入:
=MAX((WEEKDAY(A1-6-{1,2,3,4,5,6,7},2)=4)*(A1-6-{1,2,3,4,5,6,7}))
A1=2007/02/12
B1,输入公式:
=A1-6-MOD(WEEKDAY(A1-6,2)+3,7)
如何自动查找相同单元格内容
=SUMPRODUCT(($D$2:$D$15=A21)*($E$2:$E$15))
=IF(ISERROR(VLOOKUP(A6,$D$2:$E$15,2,0)),0,VLOOKUP(A6,$D$2:$E$15,2,0))
查找函数
D3=LOOKUP(2,1/(($G$3:$G$14=B3)*($H$3:$H$14=C3)),$I$3:$I$14)
=IF(ISERROR(VLOOKUP(A14,A:B:D:F,2,FALSE)),"",VLOOKUP(A14,A:B:D:F,2,FALSE)
77
)
=IF(ISERROR(VLOOKUP(C2,k!B2:Z2189,2,FALSE)),"",VLOOKUP(C2,k!B2:Z2189,2,FAL
SE))
怎样对号入座(查找)
=VLOOKUP(D2,$A$1:$B$5,2,FALSE)
=INDEX($B$2:$B$5,MATCH(D2,$A$2:$A$5,0))
=OFFSET($A$1,MATCH(D2,$A$2:$A$5,0),1)
=VLOOKUP(D2,$A$1:$B$16,2,)
=VLOOKUP(D2,IF({1,0},$A$1:$A$9,$B$1:$B$9),2,)
=LOOKUP(2,1/($A$1:$A$10=D2),$B$1:$B$10)
一个文ᴀ查找的问题如何在一个单元格中,统计某个字符出现的次数,例如:单元格A1中填有:张三/李四/王五",如何通过公式来计算此单元格中共填有几个人姓名,每个人姓名之间用"/"符号分开,烦请相告.
=LEN(A1)-LEN(SUBSTITUTE(A1,"/",))+1
查找一列中最后一个数值我想用公式知道,另一个表中"A"列最下面一个数是多少,就行了.用不定值的,因为还有数据有增加,
=LOOKUP(9E+307,Sheet2!A:A)——最后一个数值
=LOOKUP(REPT("",255),Sheet2!A:A)——最后一个文ᴀ
=INDEX(Sheet2!A:A,MATCH(9E+307,Sheet2!A:A))
=INDEX(Sheet2!A:A,MATCH("*",Sheet2!A:A,-1))
Match(rept("",255),sheet2!A:A)
查找重复字符两组数值
AB
12456890134578查找单元格A和B里重复及不重复的字符正确答案:重复字符-1458不重复字符-023679以下公式对数字有效:重复数字:
=IF(COUNT(FIND(0,A1:B1))=2,0,"")&SUBSTITUTE(SUM(IF(ISNUMBER(FIND(ROW($1
:$9),A1))+ISNUMBER(FIND(ROW($1:$9),B1))=2,ROW($1:$9)*10^(10-ROW($1:$9)))),0,)不重复数字:
=IF(COUNT(FIND(0,A1:B1))=1,0,"")&SUBSTITUTE(SUM(IF(ISNUMBER(FIND(ROW($1
:$9),A1))+ISNUMBER(FIND(ROW($1:$9),B1))=1,ROW($1:$9)*10^(10-ROW($1:$9)))),0,)都是数组公式,按Ctrl+shift+enter结束。重复数字:
78
=IF(COUNT(FIND(0,A1:B1))=2,0,"")&SUBSTITUTE(SUM(IF(MMULT(COUNTIF(OFFSE
T(A1,,{0,1},),"*"&ROW($1:$9)&"*"),{1;1})>1,ROW($1:$9)*10^(9-ROW($1:$9)))),0,)不重复数字:
=IF(COUNT(FIND(0,A1:B1))=1,0,"")&SUBSTITUTE(SUM(IF(MMULT(COUNTIF(OFFSE
T(A1,,{0,1},),"*"&ROW($1:$9)&"*"),{1;1})<2,ROW($1:$9)*10^(9-ROW($1:$9)))),0,)
请教查找替换问题把表1中字符在4个以上的字段(4)查找出来,替换成表2中的人名,最好在原位置修,或者在新的一列上生成也成,只要其他内容保持不变并按原来的顺序即可。
=IF(LEN(A2)<4,A2,OFFSET(2!$A$1,SUMPRODUCT(--(LEN($A$2:A2)>3))-1,))
=IF(LEN(A2)<4,A2,INDEX(2!A:A,COUNTIF($A$2:A2,"="&"????*")))
IF函数替换法总结条件说明:小于10返回500,小于20返回800,小于30返回1100,小于40返回1400大于40返回1700类似于以上要求,大家最先想到IF函数,这也ᴀ属IF专长。但用IF一般要长长的公式,且计算较慢。现总结一下IF之替换公式,望能抛砖引玉,在我的倡导下各位提供更完善的方案。其中部分公式通用,部分公式有局限性,请看说明。(18个条件公式,根据速度,排名如下)
1=SMALL({500;800;1100;1400;1700},COUNTIF($A$9:$A$13,"<="&A1))
2=INDEX({500;800;1100;1400;1700},COUNTIF($A$9:$A$13,"<="&A1))
3=CHOOSE(COUNTIF($A$9:$A$13,"<="&A1),500,800,1100,1400,1700)
4=LOOKUP(A1,{0,10,20,30,40},{500,800,1100,1400,1700})
5=MIN(4,INT(A1/10))*300+500
6=MATCH(A1,{0,10,20,30,40})*300+200
7=MIN(40,FLOOR(A1,10))*30+500
8=HLOOKUP(A1,{0,10,20,30,40;500,800,1100,1400,1700},2,1)
9=200+SUM((A1>={0;10;20;30;40})*300)
10=FREQUENCY({0,10,20,30,40},A1)*300+200
11=MAX((A1>={0,10,20,30,40})*{500,800,1100,1400,1700})
12=INDEX({500;800;1100;1400;1700},MATCH(A1,{0;10;20;30;40},1))
13=CHOOSE(MATCH(A1,{0;10;20;30;40},1),500,800,1100,1400,1700)
14=500+SUM(IF(A1>={10,20,30,40},{300,300,300,300}))
15=IF(A1<10,500,IF(A1<20,800,IF(A1<30,1100,IF(A1<40,1400,1700))))
16=CHOOSE(SUM((A1>={0;10;20;30;40})*1),500,800,1100,1400,1700)
17=MAX((INT(A1/({10;20;30;40}))>0)*(ROW($1:$4)*300))+500
18=CHOOSE(MIN(INT(A1/(ROW($1:$4)*10))+1,5),500,800,1100,1400,1700)新增公式:
19=CHOOSE(MIN(INT(A1/(ROW($1:$4)*10))+1,5),500,800,1100,1400,1700)
20{=MAX((INT(A1/(ROW($1:$4)*10))>0)*(ROW($1:$4)*300))+500}
21=500+MIN(4,MAX(0,INT(A1/10)))*300
22MAX((A1>={0,10,20,30,40})*{500,800,1100,1400,1700})
23=MATCH(A1,{0,10,20,30,40})*300+200
24=MIN(40,FLOOR(A1,10))*30+500
79
25=FREQUENCY(ROW($1:$5)*10-10,A1)*300+200
查找的函数(查找᳿位词组)(数组公式:)=REPLACE(A2,1,MAX(IF(MID(A2,ROW($1:$100),1)="",ROW($1:$100))),)
=REPLACE(A2,1,LOOKUP(1,0/(MID(""&A2,ROW($1:$100),1)=""),ROW($1:$100))-1,)(数组公式:)=RIGHT(A2,MATCH(1,FIND("",RIGHT(""&A2,ROW($1:$100))),)-1)
=TRIM(RIGHT(SUBSTITUTE(A2,"",REPT("",50)),50))(好)其实这个公式的思路,是可以变化的,改变REPT()中的数值,可以返回,指定空格位置後的数据,比如:
A1=一二三四五六七八九
10个普通公式,分别为:
1=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",100)),100))返回第0空格位置後的数据>一二三四五六七八九
2=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",50)),100))返回第8空格位置後的数据>
3=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",40)),100))返回第7空格位置後的数据>八九
4=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",30)),100))返回第6空格位置後的数据>七八九
5=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",23)),100))返回第5空格位置後的数据>六七八九
6=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",18)),100))返回第4空格位置後的数据>五六七八
7=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",14)),100))返回第3空格位置後的数据>四五六七八九
8=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",12)),100))返回第2空格位置後的数据>三四五六七八九
9=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",11)),100))返回第1空格位置後的数据>二三四五六七八九
10=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",9)),100))返回第0空格位置後的数据>一二三四五六七八九
怎样从原始数据中自动获取最后一个数据原始数据
a12
b1221
c12
d33
a33自动获取
a432a432
b33b33
c22c44
c44d23
d23
公式=LOOKUP(1,0/($A$1:$A$100=C2),$B$1:$B$100)
两列数据查找相同值对应的位置
=MATCH(B1,A:A0)
80
查找数据公式两个(基ᴀ查找函数为VLOOKUP,MATCH)(1)、根据符合行列两个条件查找对应结果
=VLOOKUP(H1A1E7MATCH(I1A1E10)FALSE)(2)、根据符合两列数据查找对应结果(为数组公式)
=INDEX(C1C7MATCH(H1&I1,A1:A7&B1:B7,0))
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【输入数据的技巧】