excel常用函数公式技巧搜集.docx
- 文档编号:12711381
- 上传时间:2023-04-21
- 格式:DOCX
- 页数:28
- 大小:32.43KB
excel常用函数公式技巧搜集.docx
《excel常用函数公式技巧搜集.docx》由会员分享,可在线阅读,更多相关《excel常用函数公式技巧搜集.docx(28页珍藏版)》请在冰豆网上搜索。
excel常用函数公式技巧搜集
excel常用函数公式技巧搜集(四)默认分类2010-10-0915:
06:
21阅读429评论0字号:
大中小订阅.
筛选后自动产生序列号并汇总
自动产生序列号:
在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()函数,多少空格都能去掉。
如何去掉字符和单元格里的空格
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
=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)
把单元格编号中的小写字母变成大写字母
lafayette148LAFAYETTE148
=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)
比较两个单元格内容是否一致
74P12514874P125148
比较单元格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)
姓名:
李六王武陈丰
结果:
322
把文本格式的数字转换成真正的数字
=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宏函数,OFFICE97及以前版专用,新版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、标题
文件-页面设置-工作表-打
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 常用 函数 公式 技巧 搜集