常用函数公式及技巧搜集
从身份证号码中提取出生年月日
=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)),"0000年00月00日"),IF(LEN(D4)=18,TEXT(MID(D4,7,8),"0000年00月00日"))))
显示格式为yyyy年mm月dd日。（如果将公式中“0000年00月00日”改成“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”),TODAY(),”Y”),)
=DATEDIF（A1，TODAY（），“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")
以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式
=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为出生年月日（可用身份证号码的公式生成）。
求工齡
=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月") '显示 40年09月
如果你找不到 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)&"个月","") '显示 40年09个月
自动算出工龄日期格式为(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)&"个月","")
【时间和日期应用】
自动显示当前日期公式
=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(),"aaaa hh:mm:ss")
求本月天数
设A1为2006-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(TODAY()),1)
用公式算出除去当月星期六、星期日以外的天数
=SUMPRODUCT(--(MOD(ROW(INDIRECT(DATE(YEAR(NOW()),MONTH(NOW()),1)&":"&DATE(YEAR(NOW()),MONTH(NOW())+1,0))),7)>1))
显示昨天的日期
每天需要单元格内显示昨天的日期，但双休日除外。
例如，今天是7月3号的话，就显示7月2号，如果是7月9号，就显示7月6号。
=IF(TEXT(TODAY(),"AAA")="一",TODAY()-3,IF(TEXT(TODAY(),"AAA")="日",TODAY()-2,TODAY()-1))
=IF(TEXT(TODAY(),"AAA")="一",TODAY()-3,TODAY()-1)
关于取日期
怎么设个公式使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]
将“2005年9月”转换成“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
一、查找—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转换为2005年8月6日格式
=TEXT(A1,"yyyy""年""m""月""d""日"";@")
象22怎样转换成22日？转成当年当月的日子
公式为：=date(year(now()),month(now()),22)
将“2006年5月”转换成“2006年05月”
公式为：=TEXT(A8,"yyyy""年""mm""月"";@")
也可以这样处理：选中单元格，设置单元格公式－数字－自定义，将yyyy“年”m“月”改为：yyyy“年”mm“月”，即可。但这方法打印出来显示为：2006/5/
将“1968年6月12日”转换为“1968/6/12”格式
=YEAR(A1)&"/"&MONTH(A1)&"/"&DAY(A1) 显示：1968/6/12
=TEXT(A1,"yyyy/mm/dd") 显示：1968/06/12
将“1968年6月12日”转换为“1968-6-12”格式
=YEAR(A1)&"-"&MONTH(A1)&"-"&DAY(A1) 显示：1968-6-12
=TEXT(A1,"yyyy-mm-dd") 显示：1968-06-12
将1993-12-28的日期格式转换成1993年12月
=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/dd"))
将“二○○三年十二月二十五日”转为“2003-12-25”格式，
1、可以用数组公式将中文日期转化为日期系列数｛=14610+MATCH(SUBSTITUTE(A3,"元","一"),TEXT(ROW($14611:$55153),"[DBNum1]yyyy年m月d日"),0)｝
该公式速度较慢。
2、改进后的公式，速度要快的多：
｛=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),"[DBNum1]0000"),0),MONTH(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]m月d日"),0)))｝
要设置为1900年的日期格式。
日期格式转换
如A列是月份数为8，B列是日期数为18，如何在C列显示“8月18日”
=A1&"月"&B1&"日"
反之，要将C列的“8月18日” 直接分别到D、E列，显示月份和日期，
月数份=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(),"yyyy年m月d日")
【排名及排序筛选】
一个具有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(A2<>A1,1,N(C1)+1)
按奇偶数排序
我想请教怎样按奇数顺序然后再按偶数顺序排序
=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并不能直接按字数排序。需要先计算出每首歌曲的字数，然后再进行排序。
如A、B列分别为“歌手”和“歌名”，在C1输入“字数”，在C2输入公式：
＝LEN(B2) 下拖，单击C2，单击工具栏上的“升序排列”即可，删除C列。
排序字母与数字的混合内容
日常使用中，表格经常会有包含字母和数字混合的数据，对此类数据排序时，通常是先比较字母的大小，再比较数字的大小，但EXCEL是按照对字符进行逐位比较来排序的，如下表：A7排在第5位，而不是第1位。排序结果无法令人满意。
|
|
A |
|
1 |
A122 |
|
2 |
A29 |
|
3 |
A317 |
|
4 |
A43 |
|
5 |
A7 |
|
6 |
B20 |
|
7 |
B3 |
|
8 |
C144 |
|
9 |
C5 |
|
10 |
C33 |
|
|
A |
B |
|
1 |
A7 |
A007 |
|
2 |
A29 |
A029 |
|
3 |
A43 |
A043 |
|
4 |
A122 |
A122 |
|
5 |
A317 |
A317 |
|
6 |
B3 |
B003 |
|
7 |
B20 |
B020 |
|
8 |
C5 |
C005 |
|
9 |
C33 |
C033 |
|
10 |
C144 |
C144 |
如果希望EXCEL改变排序的规则，需要将数据做一些改变。
在B1中输入公式：LEFT(A1,1)& RIGHT("000"& RIGHT(A1,LEN(A1)-1),3) 下拖
单击B2，单击工具栏上的“升序排列”即可。
随机排序
如A、B列分别为“歌手”和“歌名”，在C1输入“次序”，在C2输入公式：
＝RAND（），下拖，单击C2，单击工具栏上的“降序排列”即可对歌曲清单进行随机排序。
排序的问题
我想要这样的排序: 2001-2003
2004-2006
2007-2009
2010-2012;
其实不是数据排序，应该是数据填充。
输入公式=LEFT(E3,4)+3&"-"&RIGHT(E3,4)+3 即可。
怎样才能让数列自动加数
怎样做才能让数列自动加数
A A0001
B B0001
A A0002
C C0001
A A0003
B B0002
C C0002
公式为=A1&"000"&COUNTIF(A$1:A1,A1)向下拖
=TEXT(COUNTIF(A$1:A1,A1),"!"&A1&"0000")否则数字超过9就错误了。
一个排序问题
一个电子表格，格式是101、102... 999,10101、10102... 99901,1010101,1020201... 9990101,请问如何将它排列成101,10101,1010101,102,10201,1020101,... 999,99901,9990101 的形式。
我在数字前加了个字母,比如"d"&"数字",然后用排序就可以把它们按你的需求排列了.最后再把字母"d"去掉。
数字的自动排序,插入后不变?
1 赵一 总经理
2 赵二 副经理
3 赵三 副经理
4 赵四 技术员
5 赵五
6 赵六 员工
如上的一个表,如何实现当我把赵六这一整行(第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)
=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)))
依分数比高低名次成绩排名
=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/10000))) 不重复排名
=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)=INDIRECT("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)),"第[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-ROW(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+ROW(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+ROW(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-ROW(B16)/10000))+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
由小到大
=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(INDIRECT("1:"&COUNTIF($A$3:$A$21,A3)))),0)｝
｛=MATCH(C3,LARGE(IF($A$3:$A$21=A3,$C$3:$C$21),ROW(INDIRECT("1:"&COUNTIF($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(C3)/10000))+1｝
=19-FREQUENCY(($A$3:$A$21=A3)*($C$3:$C$21-ROW($C$3:$C$21)/1000),C3-ROW()/1000)+1
成绩排名
|
序号 |
姓名 |
语文 |
数学 |
英语 |
|
1 |
杨增海 |
135 |
136 |
146 |
|
2 |
郭爱玲 |
138 |
137 |
141 |
|
3 |
华志锋 |
134 |
138 |
141 |
|
4 |
袁文飞 |
134 |
143 |
135 |
能否用一个公式直接找出所用考生中语文成绩中第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、对英语进行排名，缺考计算在内。
|
英语 |
英语排名 |
|
42 |
9 |
|
62 |
3 |
|
72 |
1 |
|
48 |
5 |
|
48 |
5 |
|
72 |
1 |
|
54 |
4 |
|
42 |
9 |
|
缺考 |
|
|
缺考 |
|
|
45 |
8 |
|
46 |
7 |
缺考不计算在内
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))
如果隔几行排名，如下表，第五行、第九行和第十二行不参与排名。
|
单位 |
数据 |
排名 |
|
A |
1 |
8 |
|
A |
5 |
7 |
|
A |
6 |
6 |
|
小计 |
12 |
|
|
B |
8 |
4 |
|
B |
9 |
3 |
|
B |
7 |
5 |
|
小计 |
24 |
|
|
C |
18 |
1 |
|
C |
11 |
2 |
|
小计 |
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
函数筛选姓名
如何把两列中只要包含A和A+的人员筛选出来
=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),65536),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,"*","")
查找~*，替换为空。
将字符串中的星号“*”替换为其它字符
在查找栏输入~*
替换为“-”即可。
去空格函数
如何删去单元格中的空格，如姓名前，中，后的空格，即单元格中是两个字的人名中间有一个空格，想删去有何方法。如：中 国，改为：中国。
1、用公式：=SUBSTITUTE(A2," ","") 注：第一对双引号中有一空格。而第二个“”中是无空格的。
2、利用查找－替换，一次性全部解决。
“编辑”－“替换”（或Ctrl+H），在“查找”栏内输入一空格，“替换”什么也不输入（空白）。然后“全部替换”即可。
3、有一个专门删除空格的函数: TRIM()
在EXCEL编辑栏里，不管输中文还是英文只能输一个字节的空格，但如果字与字中间是两个字节的空格，那么TRIM（）就不起作用了，它就不认为是一个空格，而是一个汉字，怎么去“TRIM”也没用。如：单元格A1中有“中 心 是”，如果用TRIM则变成“中 心 是”， 想将空格全去掉，只能用SUBSTITUDE（）函数，多少空格都能去掉。
如何去掉字符和单元格里的空格
8900079501 8900079501~
1900078801 1900078802~
=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
=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+COLUMN(A1)-1,n))
=OFFSET($A$1,INT(((ROW(A1)-1)*7+COLUMN(A1)-1)/4),MOD((ROW(A1)-1)*7+COLUMN(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)
=TEXT(ROUNDUP(ROW()/4,),"00")
=TEXT(ROW(2:2)/4,"00")
多行数据排成一列
|
a1 |
b1 |
c1 |
d1 |
e1 |
f1 |
g1 |
h1 |
i1 |
|
a2 |
b2 |
c2 |
d2 |
e2 |
|
g2 |
h2 |
i2 |
|
a3 |
|
c3 |
d3 |
|
|
g3 |
h3 |
i3 |
|
a4 |
|
c4 |
|
|
|
g4 |
h4 |
i4 |
|
A5 |
|
c5 |
|
|
|
g5 |
h5 |
|
|
|
|
|
|
|
|
g6 |
|
|
|
a1 |
|
a2 |
|
a3 |
|
a4 |
|
A5 |
|
b1 |
｛=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)
把单元格编号中的小写字母变成大写字母
lafayette148 LAFAYETTE148
=UPPER(B1)
=LOWER(B1) （大写字母变成小写字母公式）
让姓名左右对齐
姓名用字，有的是三个汉字，有的是两个汉字，打印出来很不美观，要使姓名用字是两个字的与三个字的左右对齐也有两种方法:
方法一:格式设置法。选中我们已经删除完空格的姓名单元格，单击“格式→单元格”在打开的“单元格格式”对话框中的水平对齐方式中选择“分散对齐”选项，确定退出后即可使学生姓名用字左右对齐。
方法二:函数公式法。利用Excel中的“IF”、“LEN”、“MID”三种函数组合可使姓名用字左右对齐。具体示例为:在C3单元格中输入公式:“=IF(LEN(B2)>=3,B2,(MID(B2,1,1)&&" "&&MID(B2,2,1)))”，确定后利用填充柄将该公式进行复制即可。
数字居中而小数点又对齐
可在小数点的任一边替无效的零加入空间，以便当格式设定为固定宽字型，小数点可以对齐。
格式－单元格－数字－自定义－???.???-确定
请问：小数点后的“0”还有办法显示吗？比如：
2.0
12.001
格式－单元格－数字－自定义－???.0?-确定
计算指定单元格编号组中非空单元格的数量
计算B252到B262之间的非空单元格的数量。
=COUNTA(B252:B262)
比较两个单元格内容是否一致
74P125148 74P125148
比较单元格B53与C53中的内容是否一致。
假如内容一致，那么返回值为TRUE，不一致的话，返回值为FALSE。
=EXACT(B53,C53)
结果：TRUE
怎么样设置才能让这一列的每个单元格只能输入12位
怎么样设置才能让某一列或某一行的每个单元格只能输入12位,(阿拉伯数字和26个英文字母在内,没有中文。)
选中A列，设置数据有效性：自定义>公式：“=LEN(A1)=12”
如何让工作表奇数行背景是红色偶数行背景是蓝色
用条件格式
=ROW()/2=INT(ROW()/2) 设定颜色
条件格式: 公式为 =MOD(ROW(),2)=0
计算特定的一组单元格中，满足条件的单元格的个数
仍以上题为例，计算三个人在B307到B313中各自所占的单元格数。
李六的： =COUNTIF(B307:B313,B323)
王武的： =COUNTIF(B307:B313,C323)
陈丰的： =COUNTIF(B307:B313,D323)
姓名： 李六 王武 陈丰
结果： 3 2 2
把文本格式的数字转换成真正的数字
=VALUE(B1)
设置页码
如何设置“第×页，共×页”页码。
在页脚中设置：第&[页码]页，共&[总页码]页 即可
Excel表格里如何插入页码的?
我想把表格中的第1页的页码从第30页开始编,不知道该如何实现,哪位高手能帮忙?
在页面设置的页眉页脚中设置。
在插入页脚中输入＆[页码]+29即可。
如何设置页脚首页为第5页
Excel页脚设置页码是按顺序来的，首页为第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.DOCUMENT(65))+1)。再输入“总页”；引用位置处输入：=GET.DOCUMENT(50)+RAND()*0。最后再定义“无拘无束的页眉”；引用位置：="第"&IF(横向当前页=1,纵向当前页,横向当前页+纵向当前页)&"页/共"&总页&"页"。
现在你在工作表任何处输入=无拘无束的页眉即可。
本公式核心在于GET.DOCUMENT,这是4.0宏函数,OFFICE 97及以前版专用,新版OFFICE中仍兼容,但只限定义名称中使用。
在帮助中说(64和65为其参数):64 行数的数组,相应于手动或自动生成页中断下面的行。65 列数的数组。相应于手动或自动生成的页中断右边的列。"
本公式中取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()*0
GET.DOCUMENT(50)即求当前设置下欲打印的总页数，其中包括注释，如果文件为图表，值为1
RAND（）*0作用是当文件分页数改变时，本公式结果根随变化，起公式结果刷新作用。
获取“横向当前页”
横向当前页与纵向当前页原理相同，改ROW（）为COLUMN（），并将GET.DOCUMENT参数改为65即可
若你的工作表只有纵向分页或者横向分页，那么现在就可以使用前面的公式定义的名称获取当前页及总页了；但如果分页方式为横向多页纵向也多页呢？则在将以上“横向当前页”与“纵向当前页”无缝接合方可使用，否则将返回错误结果。
最后生成“无拘无束的页眉”（或者改称文件分页）
="第"&IF(横向当前页=1,纵向当前页,横向当前页+纵向当前页)&"页/共"&总页&"页"
公式解说完毕！各位可以用不同的文字定义名称在各自的工作表中试用了。
打印表头
在Excel中如何实现一个表头打印在多页上？
请选择文件-页面设置-工作表-打印标题-顶端标题行，然后选择你要打印的行。
打印表尾，通过Excel直接提供的功能应该是无法实现的，需要用vba编制才行。
Excel打印中如何不显示错误值符号
在“页面设置”－“工作表”－“错误单元格打印为”中，
将“显示值”改为“空白”即可。
对于一些不可打印的字符的处理
对于一些不可打印的字符（在Excel显示中类似空格），直接用替换方法不容易去掉。
可以这么做：
=SUBSTITUTE(CLEAN(A1)," ","")
用那个函数可将个位数前面的零值显示出来?
如果单元格A1的内容是5,在A2用那个函数可将A1的内容变为05?
(Text或value也可,总之个位数的零也显示,例:5变05,15则15)
可以用＝TEXT(A2,"00")
或将单元格格式自定义为00
如果你要在A3的前面插入100行
可以这样:在名称框输入 3:103-回车-ctrl+shift+"+"（大键盘）
请问如何每隔30行粘贴一新行
偶在班上负责统计企业进出口业务量，领导要求每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键，选择整个工作表，用鼠标单击“数据”菜单，选择“筛选”项中的“自动筛选”命令。这时在每一列的顶部，都出现一个下拉列表框，在典型列的下拉列表框中选择“空白”，直到页面内已看不到数据为止。
在所有数据都被选中的情况下，单击“编辑”菜单，选择“删除行”命令，然后按“确定”按钮。这时所有的空行都已被删去，再单击“数据”菜单，选取“筛选”项中的“自动筛选”命令，工作表中的数据就全恢复了。插入一个空行是为了避免删除第一行数据。
如果想只删除某一列中的空白单元格，而其它列的数据和空白单元格都不受影响，可以先复制此列，把它粘贴到空白工作表上，按上面的方法将空行全部删掉，然后再将此列复制，粘贴到原工作表的相应位置上。
快速删除空行
有时为了删除Excel工作簿中的空行，你可能会将空行一一找出然后删除，这样做非常不方便。你可以利用自动筛选功能来实现，方法是:先在表中插入新的一行(全空)，然后选择表中所有的行，单击“数据→筛选→自动筛选”命令，在每一列的顶部，从下拉列表中选择“空白”。在所有数据都被选中的情况下，单击“编辑→删除行”，然后按“确定”，所有的空行将被删去。注意:插入一个空行是为了避免删除第一行数据。
一次删完Excel里面多出很多的空白行
1、用分面预览看看
2、用自动筛选然后删除
3、用自动筛选，选择一列用非空白，空白行就看不到了，打印也不会打出来。但是实际上还是在的，不算删除。或者用自动筛选选择空白将空白行全显出来一次删完也可以。
4、先插入一列，在这一列中输入自然数序列，然后以任一列排序，排序完后删除数据后面的空行，再以刚才输入的一列排序，排序后删除刚才插入的一列。
每30行为一页并加上一个标题如何实现
每30行为一页，并加上一个标题，如何实现。
可以每30行加一个分页符，标题就用“打印标题”来设置。
1、 标题
文件-页面设置-工作表-打印标题-顶端标题行，设置一下就好了。
2、 每页30行
也是在页面设置中，设置上下页边距的调整可以实现，打印预览看一下就可以看到是不是30行了，不到30行你可以将行距加宽，进行调整，以我的经验，加标题的30行/页大概行距是20，这样连制表人的空间都留出来了。
每页30行－“插入》分页符”；然后每向下移动30行，点菜单“插入》分页符”。
如何实现隔行都加上标题项
在excel中,每条记录都要加上标题(隔行都加),如何才能快速实现?(只要打印出来能实现就成)。
在E列输入2 4,然后选中这两个单元格，拖住右下的点向下拉到底。
把第一行标题项复制，在有数据区域的下部选中与数据行数相同的空行，粘贴。
用同样的方法填上奇数（如上），按E列排序即可。
如何把标签页去掉的?
工具→选项→视图→点击“工作表标签”去掉（√）勾→确定。
恢复时也照此操作
工具→选项→视图→点击“工作表标签”显示（√）勾→确定。
去掉默认的表格线(网线)
单击“工具”菜单中的“选项”，再单击对话框中的“视图”，找到“网格线”，使之失效(将左边的“×”去掉)。
表格的框线
我们很喜欢为表格加上一道框线，不过这道框线又往往叫我们花掉很多时间来重画，例如在下方多加一列时，Excel并不会把新列加在下方框线之上。又例如将上方的数据拷到最后一列时，下方的框线就会给盖掉，变成穿了一个洞。
我的技巧就是在表格的最后一列留一列空列，并把它的列高定得很小，我就叫这一列「缓冲列」好了。把列高定小一点，除了美观之外，还可以用作提醒用户不要把数据打到缓冲列。你可以试试在缓冲列上加列或拷数据到缓冲列之上，框线并不会给弄乱。
列标的标识变了
通常EXCEL的列标都是用大写英文字母表示的，我的EXCEL的列标今天都变成了阿拉伯数字表示的了，请教这两种表示方法有什么不同，如果想恢复成字母表示的该怎么办。
这是EXCEL的R1C1样式。在这里改回来：工具/选项/常规：不选R1C1样式。
符号的意义
单元格自定义格式中"?" 和"#"代表的是什么意思。
“？” 一个字符，字符：可以是文本、也可以是数字；
“#” 一个数值字符，数值字符：只能是数字。
双击格式刷竟也能COPY文本(不是文本格式)
步骤：选中“单元格”→双击格式刷→按住Ctrl键选择需复制的不连续目标区域→按回车Enter键
格式刷的作用其实没变，复制文本其实只是按Enter的结果。
分解动作分为三步：
1.定位在原数据上，双击格式刷：复制所有内容
2.在目标区域按格式刷：选择性粘贴-格式
3.按回车：粘贴所有内容。
你会发现如果原单元格上有批注或其实Shape对象的话，一样也复制了，跟原数据按Ctrl+C，选择区域.再按Enter这个意思是一样的。
查找+格式刷的妙用
通常在数据校对时要用到查找，找到之后就用不同格式区分(如字体为红色、底纹为黄色等等)，如此重复。
例如：先找到第一个数据并将字体改为红色,然后双击格式刷,当查找到其它相同数据时,再按Ctrl+A，excel就会将新找到的数据自动改为红色与之区分。
楼主能不能做一个动画演示？
具体操作为：Ctrl+F→在查找栏输入要查找的值→查找下一个→设置格式（不要退出查找对话框）→双击格式刷→查找全部→Ctrl+A→关闭
另外，通常的方法是：Ctrl+F→在查找栏输入要查找的值→查找全部→Ctrl+A，再设置格式。
光标移动
在一个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+↓ 双击选取单元格之下框线
用SUN函数快速求和
如何用sum函数快速求和
操作：将光标移到欲要求和的列或行，直按"Alt+"="，最后按一下“enter"键就可以。这样我们不用输入参数就可以快速求和。
在Excel中快速查看所有工作表公式
只需一次简单的键盘点击，即可可以显示出工作表中的所有公式，包括Excel用来存放日期的序列值。
要想在显示单元格值或单元格公式之间来回切换，只需按下CTRL+`（位于TAB键上方）。
在Excel中设置行间距
想必大家都知道Excel中是没有行间距设置功能的吧。利用拼音指南却可以让我们在Excel中轻松设置单元格中文字的行间距。
在Excel 2003中选中需要设置行间距的单元格，单击“格式”菜单，依次选择“拼音指南/显示或隐藏”，马上可以看到单元格中文字行间距变大了。
如果想再进一步调整行间距，可再单击“格式”菜单，选择“拼音指南/设置”打开“拼音属性”窗口，切换到“字体”选项卡下，把字号设置大一点，确定后行间距就会相应增大，反之则减小。
怎样同时改变多行行高
我们知道，通过拖动行或列间的分界线可以改变行高或列宽，但怎样同时改变向行或几列的高度或宽度呢？
我们以改变行高为例，先选中要改变行高的列，按下Shift键再单击行标题头，可以选定连续的多行（如果要选中多个不连续行，可以按下Ctrl键）。选中多列后，拖动任意一个被选中的行标题间的分界线，到适当高度释放鼠标，所有被选中的行高都改变了。
我们也可以精确地改变行高：选中多行后，单击“格式”菜单，选择“行”中的“行高”命令，设置行高为20，单击“确定”，行高都被设置为20了。
快速换行
在Excel单元格中输入数值后，按下Alt键不松开，再按下Enter键，即可快速换行。
让文本换行
每次在Excel单元格中输入一个值，再按下Enter键，活动单元格均默认下移一个单元格，非常不方便。不过，这时，可以选择“工具”→“选项”→“编辑”，然后取消“按Enter键移动活动单元格标识框”复选框即可。
在Excel中行列快速转换
如果需要要将Excel按行（列）排列的数据，转换为按列（行）排列，可以通过“选择性粘贴”来实现。
选中需要转换的数据区域，执行一下“复制”操作；选中保存数据的第一个单元格，执行“编辑选择性粘贴”命令，打开“选择性粘贴”对话框，选中其中的“转置”选项，确定返回即可。
将原有列中的内容倒置过来
|
1 |
5 |
|
2 |
4 |
|
3 |
3 |
|
4 |
2 |
|
5 |
1 |
B1 =OFFSET(A$1,COUNTA(A:A)-ROW(A1),)
快速回到A1单元格
按下Ctrl+Home组合键，快速选中A1单元格。
复制粘贴中回车键的妙用
1、先选要复制的目标单元格，复制后，直接选要粘贴的单元格，回车OK；
2、先选要复制的目标单元格，复制后，选定要粘贴的区域，回车OK；
3、先选要复制的目标单元格，复制后，选定要粘贴的不连续单元格，回车OK。
一次选中批注单元格
按下Ctrl+Shift+O (字母 O)组合键，可以一次性选定所有带批注的单元格。
一次在所有单位格中插入批注
1选择你已经做批注的单元格
2复制
3选择你要做相同批注的所有单元格
4编辑〉选择性粘贴〉批注
在公式中插入批注
如果要在公式中插入批注信息，可以利用“N（Value）”返回“0”的特点，因为文字属于其它值。
1．假如A1～F1单元格中是个别统计数字，G1则是它们的总和，那么一般情况下其公式为“=SUM(A1:F1)”。
2．如果要在公式中插入批注信息，可以将公式更改为“=SUM(A1:F1)+N("A1～F1的总和")”，如图1所示。
不连续单元格填充同一数据
选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。在编辑区中输入数据，然后按住Ctrl键，同时敲一下回车，在所有选中的单元格中都出现了这一数据。
空白行的填充
各位，我一些同事喜欢在表格内使用合并单元格，而我的进行公式运算时需把单元格打散，但就出现了许多空白格，现在我想把空白格用临近的非空白填充。
EX：A1格为“张三”，A8为“李四”，A21为“王五”之类，现在我要把A2到A7填为张三，A9到A20为李四，A22之后为王五。
在B1输入=IF(A1<>"",A1,IF(ROW()>1,INDIRECT("b"&ROW()-1)))，向下复制到合适位置。然后用选择性粘贴功能替换到A列中去。
怎样用函数向下实现自动填充
各位高手，怎样用函数实现如下的功能：把左边的空格，用上面的A0001代码填充，实现右边的格式，谢谢解答！！！
A0001 白色 300 | | A0001 白色 300
红色 500 | ---> | A0001 红色 500
黄色 300 | | A0001 黄色 300
如果你的第一个“A0001”在[A1]，A2=if(b2>0,a$1,"") 向下拖曳。
最好用附件的形式来提问，这样可以减少相互间猜题的麻烦。
用绝对值是不行的，假如，我下面还有别的编号，这个功能就实现不了啊
怎么设置自动保存
在“工具”菜单上，单击“选项”，再单击“保存”选项卡。选中“自动保存时间间隔”复选框。在“分钟”框中，指定希望 Microsoft Office 程序保存文件的频率。
避免输入网址和电子邮件地址时的超链接
在单元格中输入的网址或电子邮件地址，Excel在默认情况下会将其自动设为超级链接。如果想取消网址或电子邮件地址的超级链接，可以在单元格上单击鼠标右键，选择“超级链接/取消超级链接”即可。
此外，还有两个有效办法可以有效避免输入内容成为超级链接形式：
1、在单元格内的录入内容前加入一个空格；
2、单元格内容录入完毕后按下“Ctrl＋z”组合键，撤消一次即可。
单元格前面自动加了等号
我的单元格怎么输入时间后前面自动加了等号,然后2005年就变成了1905年了呢?
工具-选项-1-2-3帮助-转换 lotus 123 公式
有无打勾？去掉
加盖公章
我们日常上送、下发的报表材料、通知等都要加盖公章，如果把这项工作交给Excel或Word来完成，我们的工作就轻松多了。
第一步：制作公章图案
首先我们要做出一个公章的图案，最简单的办法是把公章图案扫描到电脑中，然后处理成透明的GIF图像。我们也可以直接用Excel来制作：把绘图工具打开，选中“椭圆”工具，在按下“Shift”键的同时拖开鼠标，就可以得到一个正圆了。双击这个正圆打开“设置自选图形格式”对话框，在“颜色与线条”标签中，填充颜色选“无填充颜色”，线条颜色设为红色，选3磅粗的单线形（图）。公章的文字用艺术字来制作，填充颜色和线条颜色都用红色，并设成无阴影产。弧形文字和水平文字要分开来做，在做弧形文字时，把艺术字拖到圆形的上方，在艺术字工具中选“艺术字形状－细上弯弧”，按住黄色的四方块往下拉，再作适当的调整，就可以做出公章里的圆弧形的文字了。公章中间还有一个红五星，用“自选图形”的星形就可以做出来了，填充颜色和线条颜色用红色。最后，按住“Shift”键把组成公章的文字、图形全部选上，执行右键菜单中的“组合”命令，一个公章就做好了。
如何把做好的公章保存出来？这里有一方法：把工作表另存为Web页，然后到保存目录中找到*.files的文件夹，里面有一个GIF图片，这就是刚才做好的公章图案了，它的背景是透明的，我们把它改名为gongzhang.gif保存下来即可。
第二步：添加“盖章”按钮
接下来我们给Excel添加一个盖章按钮，当一个工作表做好后，点击这个盖章按钮，就可以为我们盖上公章了。
先把公章图形复制出来（用来粘贴作为按钮的图标），然后打开“工具－自定义”对话框，选中“命令”标签，在“类别”栏中找到“宏”，在右边的“命令”栏里就会出现一项“自定义按钮”。用鼠标把这个笑脸图标拖出到菜单栏或工具栏上放下，在笑脸图标上击右键，在弹出的菜单中把“命名”处的文字改为“加盖公章”。接下来点击“粘贴按钮图标”这个命令，就可以用刚和复制的公章图形来代替笑脸图标了。把鼠标移下来选中“分配超级链接－插入图片”，然后在“请键入文件名称或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、双击拆分窗格的分割条(上下滚动条的上方，左右滚动条的右边，没试过拆分窗口的朋友可以先从窗口/拆分里体验一下)，可以按当前单元格上下左右拆分；拆分后再双击分割条的任意部分可以恢复。
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步 然后再点击[Shockwave Flash Object]项目，表示要插入Flash物件。
第3步 接下来，鼠标会变成一个小十字，此時可以在Excel编辑区中画一个大小适中的方框，这个方框就是用来显示Flash时钟的內容的。
第4步 画好方框后，接着点击【属性】，准备设置属性。
第5步 出現「属性」对话框后，将DeviceFont设置成False；将Eebedmovie设置成True；将Enabled设置成True；将Locked设置成True；将Loop设置成True；将Menu设置成False；并在“Movie”右侧填入时钟的地址与名称(如：G：\22006.swf)。
第6步 如下图，退出设计模式，全部完成。
小写数字转换成人民币大写
方法1
=IF(TRUNC(H16)=H16,TEXT(H16,"[DBNum2]G/通用格式")&"元整",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"),"",TEXT(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]")&"元",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)),"[DBNum2]")&"分","整"),"-",)
小数点后两位四舍五入: =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)),"[DBNum2]"))&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中，经常会碰到要制作的复杂表头，其中包含有斜线和文字(见下图画红圈处)，许多初学者往往对此束手无策，还有的干脆胡乱调整，由于采取的方法不当，结果却是花了很长的时间却达不到理想的效果。
图1 图2
许多初学者经常采用以下两个操作来处理：
1、 用边框中的斜线来调整；
2、 在单元格中直接输入文字。
现笔者分析一下其弊端：
1、 用边框中的斜线来调整，只能画一条斜线，不能画多条斜线，同时，画出的斜线只能是单元格的对角线，不能随意更改。见图2：
2、在单元格直接输入文字，要分行，只能先让单元格自动换行，然后用空格键移动相应的文字到第二、三、四行，这种方法费时费力，文字一多往往就做不到理想的效果。
至此，不少初学者不尽望洋兴叹：做一个表头怎么那么难!!
其实，换一种思维，换一个方法，就“柳暗花明又一村”了。下面，笔者还是以图1为例进行详细说明。
首先，输入没有斜线的单元格的内容，调整行列(见图3)。
图3 图5
在这里说明一下，调整行列很重要，这样制作带斜线表头后表格的其它部分不用再调整了，否则调整表格的其它部分，斜线单元格的内容又挨重新调整。这点笔者千万要注意。
然后，单击绘图工具栏中的“直线”(图4中画红圈处)。
图4
将鼠标移到单元格中，明确直线的起点和终点，从起点按住鼠标拉到终点，即可画出第一条直线。
如果起点和终点有偏差，可将鼠标移到直线的起点处(或终点)，鼠标由空心十字形变成斜双箭头后，即可按住鼠标往任意一个方向调整直线到合适的位置，上、下、左、右均可。这就是这种方法的好处。
用这种方法做出第二条直线，效果见图6：
图6 图8
现在是输入文字了，怎么输入呢?用文本框工具。
单击绘图工具栏中的“文本框”，见后页图(画红圈处)：
图7
然后在单元格中按一下鼠标，输入第一个字“科”。效果见图8：
这里要注意的是“按一下鼠标”，不要拖动鼠标。如果拖动鼠标，文本框会出现黑边框(见图9)。
图9 图10
将图8文本框中的“科”选中，调整其字体、字号等格式成合适的效果。这一点也要注意，到下面的复制操作时就不必再来调整各个文字的格式了。
鼠标点一下“科”文本框，然后将鼠标移到该文本框边缘，点鼠标右键。见图10：
在弹出的菜单中选择“复制”，在表格的任一个地方点鼠标右键，选择“粘贴”，就会出现另一个“科”文本框。效果见图11：
图11 图12
将“科”字改为“目”字，将鼠标移到该文本框边缘，鼠标变成十字箭头形，按住鼠标移动该文本框到单元格合适的位置。效果见图12：
如果“科”字和“目”字相对位置不理想，还可以继续调，只要选择相应的文本框移动就行了。这就是为什么我们把两个字分成两个文本框，目的就是为了方便调整这两个字之间的相对位置。
通过采取相似的方法，把其余的文字做出来。效果见下图：
至此，读者一定会发现，再复杂的斜线表头，利用直线和文本框工具，也就迎刃而解了!
【字符截取与增减】
=RIGHT(A1,LEN(A1)-SEARCH(",",A1,1))
截取字符的公式
有一组数据101～103*,11～20*…我想截取~至*之間的數字。
=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)
取数函数
单元格中用函数单独取出 *号前 （后）的数
假如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(B2,"*","")))))
如何把单元格中的数字提取出来（字符串中不连续）
数组公式
=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 结果：33249
033k3mn249up 结果：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. Working Status 只取 Working Status
如果“.” 前的字數固定
=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
=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.2514 12512.25
=TRUNC(B23,2)
单元格数字提取问题
单元格里面填写的 CHIP(0601-2299),把0601-2299 提取出来应该怎么做。
=IF(B3="","",SUBSTITUTE(MID(B3,FIND("(",B3)+1,100),")",""))
我用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. Henry Jackey”，如何用函数将最后的一个单词取出来呢？当然，我们现在是知道最后的单词是6个字符，可以用Right(A1,6)来计算，但如果最后一个单词的字符数是不定的呢，如果做呢？请大家试下有几种方法。
方法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,ROW(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)+IF(ISNUMBER(RIGHT(TRIM(A2),2)+0)=TRUE,1,0))
如何在一列已经输入的数据前添加“p”
比如一列数据 添加后变
112234 p112234
123435 p123435
124355 p124355
123545 p123545
选中所有单元格，设置格式，自定义-〉在缺省的“G/通用格式”前面加上“"p"”（半角的双引号中间是p）即可
加一列全是“p”，使用&=a1&b1 Shift+7
="P"&A1
在自定义中输入"Q"#即可,很简单的。
什么函数可以插入字符
怎样用第一列的数据形成第二列的数据，即在特定位置加上几个相同字符串？
解答： b1="04"& a1
问：哪如果倒过来呢？
答：用公式A1=MID(B1,3,13)或A1=SUBSTITUTE(B1,"04","",1)
如何在数据前添加“*”号
数据如在B列,在A列整列加“*”，C列C1输入公式C1=A1&B1,
下拉。用“选择性粘贴”选“值”复制到D列，删去A、B、C列。
数字前面加上数字
123
0123
75223
比如说上述的数字，我想在它们前面加上38910104，而且位置短的数字，会自动补0
变成如下：
3891010400123
3891010400123
3891010475223）
=TEXT(A1,"3891010400000")
3、查找替换，查找栏输入：第*班，替换栏空置，全部替换
【数据拆分与合并】
数字如何拆分
我有一组数据，如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) 向右拖
也可以，选中区域后“数据”-----分列，“固定列”-----看到尺寸的时候分别在01234567890两数之间点击一下-----完成就行了！
单元格的拆分
一个单元格数据即包含了物品名又包含其规格,两者之间用"/"来隔开,现想把两者单独分开。
如：轴承/SKF 62122R，外六角螺丝/M10*30....."/"前后都无标准长度。
1、用“数据-分列”，不用公式的方法最簡單了。
2、用公式：
=LEFT(A1,FIND("/",A1)-1)
=RIGHT(A1,FIND("/",A1)-1)
如何拆分字组
如何将一个单元格里的字组拆开来啊，
1、你好啊→你 好 啊 要用什么函数啊?
=SUBSTITUTE(A1,"好"," 好 ")
2、”你— 好 — 啊“改成“你好啊” 把中间的横线去掉。要用什么函数或怎样在自定义里面设置？
①用查找替换功能，查找“—”，替换为空值
②=SUBSTITUTE(A1,"— 好 — ","好")
用连字符“&”来合并文本
将B、C、D列合并。
1.在E1单元格中输入公式：=B1&C1&D1 下拉
2.选中E列，执行“复制”操作，然后选中F列，
执行“编辑→选择性粘贴”命令，打开“选择性粘贴”对话框，选中其中的“数值”选项，按下“确定”按钮，E列的内容（不是公式）即被复制到F列中。
3.将B、C、D、E列删除，完成合并工作。
怎样把不同格式不同位置的内容合并到一个单元格内
如：一个是文本格式一个是日期格式，怎么合并呢？
今天是： 2007-06-25
＝A1&TEXT(B1,"yyyy-m-d h: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函数，此函数的作用是将若干文字串合并到一个字串中，具体操作为
=CONCATENATE（B1,C1）
比如，假设在某一河流生态调查工作表中，B2包含“物种”、B3包含“河鳟鱼”，B7包含总数45，那么： 输入“=CONCATENATE（"本次河流生态调查结果："，B2，""，B3，"为"，B7，"条/公里"）” 计算结果为：本次河流生态调查结果：河鳟鱼物种为45条/公里。
关于文字在表格中的组合
如：
|
计算 |
机 |
计算机 |
C1=A1&B1
求拆解合并公式
|
2/25 |
2/ |
25 |
|
4/25 |
4/ |
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))
把分散在各单元格的内容合在一个单元格中
75 P 128 66 75P12866
=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"用相对引用, 用"格式刷"刷下去...
为方便和不易出错起见, 先设置一行的条件格式, 再用格式刷将格式复制到需要的行。
隔行不同字体颜色怎么设置
每隔一行就用不一样的颜色，有什么快速的办法吗？
格式－条件格式(公式）：=MOD(ROW(A1),2)=0
让不同类型数据用不同颜色显示
在工资表中，如果想让大于等于2000元的工资总额以“红色”显示，大于等于1500元的工资总额以“蓝色”显示，低于1000元的工资总额以“棕色”显示，其它以“黑色”显示，我们可以这样设置。
1.打开“工资表”工作簿，选中“工资总额”所在列，执行“格式→条件格式”命令，打开“条件格式”对话框。单击第二个方框右侧的下拉按钮，选中“大于或等于”选项，在后面的方框中输入数值“2000”。单击“格式”按钮，打开“单元格格式”对话框，将“字体”的“颜色”设置为“红色”。
2.按“添加”按钮，并仿照上面的操作设置好其它条件（大于等于1500，字体设置为“蓝色”；小于1000，字体设置为“棕色”）。
3.设置完成后（图2），按下“确定”按钮。
看看工资表吧，工资总额的数据是不是按你的要求以不同颜色显示出来了。
有无办法让B2所在行都呈红色字体。
如何做这样的EXCEL表（颜色交叉）
省得看错行了。
设置二行不同的格式，同时选取这两行，按右键复制，选复制格式。
若只是要不同颜色间隔，应该这样就行了：
格式 > 自动格式设置 –
我的条件格式公式为: =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
在这里 Rw Rw用于格式化的范围内起始行号, N 是每一组颜色条中包含的工作表行数。在上方左图的示例中, Rw等于8, N 等于 3.
“偶数条纹 ”使用公式
=MOD(ROW()-Rw,N*2)+1>N
在这里 Rw用于格式化的范围内起始行号, N 是每一组颜色条中包含的工作表行数. 在上方右图的示例中, Rw等于8, N 等于 3..
如何在条件格式中使用这些公式的方法是：选择你想格式化的单元格范围. 然后在格式菜单中选择条件格式. 在弹出的对话框中将条件设置为公式，并在右边的框内输入上面的公式并设置好格式的图案颜色,确定后退出，看看自己的杰作吧!
在这两个公式中, 你可以直接输入任意Rw 及N的值，也可以使用自定义名称引用的值. 使用定义名称在改变第一组颜色条包含的工作表行数时非常容易。
如，想要使得工作表中的行隔行显示，可以简单地改变 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) >红色 则能用。是什么原因？
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
如何做到小于10显示二位小数，大于10显示一位小数
如何做到小于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
#!.#,
自定义单元格格式
[=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． @”文本” (在原有的文本上加上新文本或数字);
5． 0000000 （发票号码等号码输入）;
6. yyyy/mm
7. yyyy/m/d aaaa -->ex. 2003/12/20 星期六
8. m"月"d"日" (ddd) -->ex. 12月20日 (Sat)
9. "Subject (Total: "0")" -->单纯加上文字
10. "Balance"* #,##0_ -->对齐功能
11. [蓝色]+* #,##0_ ;-* #,##0_ -->正负数的颜色变化
12. **;**;**;** -->仿真密码保护 (搭配sheet保护)
13. [红色][<0];[绿色][>0] （小于0时显示红色，大于0时绿色，都以绝对值显示）
14 [>0]#,##0.00;[<0]#,##0.00;0.00 （会计格式，以绝对值形式显示）
自定义格式
Excel中预设了很多有用的数据格式，基本能够满足使用的要求，但对一些特殊的要求，如强调显示某些重要数据或信息、设置显示条件等，就要使用自定义格式功能来完成。 Excel的自定义格式使用下面的通用模型：正数格式，负数格式，零格式，文本格式，在这个通用模型中，包含三个数字段和一个文本段：大于零的数据使用正数格式；小于零的数据使用负数格式；等于零的数据使用零格式；输入单元格的正文使用文本格式。我们还可以通过使用条件测试，添加描述文本和使用颜色来扩展自定义格式通用模型的应用。
（1）使用颜色 要在自定义格式的某个段中设置颜色，只需在该段中增加用方括号括住的颜色名或颜色编号。Excel识别的颜色名为：[黑色]、[红色]、[白色]、[蓝色]、[绿色]、[青色]和[洋红]。Excel也识别按[颜色X]指定的颜色，其中X是1至56之间的数字，代表56种颜色(如图5）。
（2）添加描述文本 要在输入数字数据之后自动添加文本，使用自定义格式为：＂文本内容＂@；要在输入数字数据之前自动添加文本，使用自定义格式为：@＂文本内容＂。@符号的位置决定了Excel输入的数字数据相对于添加文本的位置。
（3）创建条件格式 可以使用六种逻辑符号来设计一个条件格式：>（大于）、>=（大于等于）、<（小于）、<=（小于等于）、=（等于）、<>（不等于），如果你觉得这些符号不好记，就干脆使用“>”或“>=”号来表示。
由于自定义格式中最多只有3个数字段，Excel规定最多只能在前两个数字段中包括2个条件测试，满足某个测试条件的数字使用相应段中指定的格式，其余数字使用第3段格式。如果仅包含一个条件测试，则要根据不同的情况来具体分析。
自定义格式的通用模型相当于下式：[>；0]正数格式；[<；0]负数格式；零格式；文本格式。
下面给出一个例子：选中一列，然后单击“格式”菜单中的“单元格”命令，在弹出的对话框中选择“数字”选项卡，在“分类”列表中选择“自定义”，然后在“类型”文本框中输入“"正数:"($#,##0.00);"负数:"($ #,##0.00);"零";"文本:"@”，单击“确定”按钮，完成格式设置。这时如果我们输入“12”，就会在单元格中显示“正数：（$12.00）”，如果输入“-0.3”，就会在单元格中显示“负数：（$0.30）”，如果输入“0”，就会在单元格中显示“零”，如果输入文本“this is a book”，就会在单元格中显示“文本：this is a book”。 如果改变自定义格式的内容，“[红色]"正数:"($#,##0.00);[蓝色]"负数:"($ #,##0.00);[黄色]"零";"文本:"@”，那么正数、负数、零将显示为不同的颜色。如果输入“[Blue];[Red];[Yellow];[Green]”，那么正数、负数、零和文本将分别显示上面的颜色。
再举一个例子，假设正在进行帐目的结算，想要用蓝色显示结余超过$50，000的帐目，负数值用红色显示在括号中，其余的值用缺省颜色显示，可以创建如下的格式： “[蓝色][>50000] $#,##0.00_);[红色][<0]( $#,##0.00); $#,##0.00_)” 使用条件运算符也可以作为缩放数值的强有力的辅助方式，例如，如果所在单位生产几种产品，每个产品中只要几克某化合物，而一天生产几千个此产品，那么在编制使用预算时，需要从克转为千克、吨，这时可以定义下面的格式： “[>999999]#,##0,,_m"吨"";[>999]##,_k_m"千克";#_k"克"” 可以看到，使用条件格式，千分符和均匀间隔指示符的组合，不用增加公式的数目就可以改进工作表的可读性和效率。
另外，我们还可以运用自定义格式来达到隐藏输入数据的目的，比如格式＂；＃＃；0＂只显示负数和零，输入的正数则不显示；格式“；；；”则隐藏所有的输入值。 自定义格式只改变数据的显示外观，并不改变数据的值，也就是说不影响数据的计算。灵活运用好自定义格式功能，将会给实际工作带来很大的方便。
怎样定义格式
怎样定义格式表示如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
解答：
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的显示为红色，则可以采用以下的方法来操作：选中需要进行彩色设置的单元格区域，选择“格式”→“单元格”，在弹出的对话框中单击“数字”选项卡。然后选择“分类”列表中的“自定义”选项，在“类型”框中输入“［绿色］［＜500；［红色］［＞＝500］”，最后单击“确定”按钮即可。
小提示
除了红色和绿色外，用户还可以使用六种颜色，它们分别是黑色、青色、蓝色、洋红、白色和黄色。另外，“［＞＝120］”是条件设置，用户可用的条件运算符有：“＞”、“＜”、“＞＝”、“＜＝”、“＝”、“＜＞”。当有多个条件设置时，各条件设置以分号“；”作为间隔。
