(Excel)常用函数公式及操作技巧之四：文本与页面设置(一)

 

(Excel)常用函数公式及操作技巧之四：

文本与页面设置(一)

——通过知识共享树立个人品牌。

 

 

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编制才行。