Excel文本函数用法集萃.docx
《Excel文本函数用法集萃.docx》由会员分享，可在线阅读，更多相关《Excel文本函数用法集萃.docx（15页珍藏版）》请在冰豆网上搜索。
Excel文本函数用法集萃
Excel文本函数用法集萃
Excel是办公应用中的经常使用软件，它不但有统计功能，在进行查找、计算时，Excel也有诸多的函数能够简化咱们的操作。
需要注意的是对中英文的处置是不大相同的，中文的一个字是按两个字节计算的，稍不注意就可能忽略这一点，造成错误。
其实Excel函数中有专门针对双字节字符的函数。
让咱们先来看看函数FIND与函数FINDB的区别。
语法：
FIND（find_text,within_text,start_num）
FINDB（find_text,within_text,start_num）
两个函数的参数相同。
作用：
FIND函数用于查找文本（within_text）中的字符串（find_text），并从within_text的首字符开始返回find_text的起始位置编号。
也可利用SEARCH查找字符串，可是，FIND和SEARCH不同，FIND区分大小写而且不许诺利用通配符。
FINDB函数用于查找文本（within_text）中的字符串（find_text），并基于字节数从within_text的首字符开始返回find_text的起始位置编号。
此函数用于双字节字符。
例如：
在图1中，单元B2中的公式为“=FIND（"雨",A2）”
单元格B3中的公式为“=FINDB（"雨",A2）”
两个函数公式得出的结果不一样，这是因为在FIND函数中，“雨”位于“吴雨峰”串中的第二个位置，故返回“2”；而在FINDB函数中，每一个汉字按2个字节算，因此“雨”是从第三个字节开始的，返回“3”。
图1请注意两个函数的不同结果
同理：
LEFT与LEFTB、RIGHT与RIGHTB、LEN与LENB、MID与MIDB、REPLACE与REPLACEB、SEARCH与SEARCHB的关系也如是。
即不带字母B的函数是按字符操作的，而带字母B的函数是按字节进行操作的。
咱们在编辑、修改、计算工作簿数据时，常常会用到许多汉字字符，若是利用以上带字母B的函数对汉字字符进行操作，就会很方便。
经常使用函数一键完成
微软Excel电子表格提供了上百种函数命令，可是关于大多数一般人来讲常经常使用到的只是其中一些经常使用的函数，比如算术求和（SUM函数）、求最大值（MAX函数）、求最小值（MIN函数）、求平均值（AVERAGE函数）等，而且很多时候都是临时计算一下，并非需要在专门的单元格内设置这些函数来保留结果。
其实，Excel提供了一个超级有效的功能，能够方便地实现简单函数的运算，只是它被大多数人忽略了，让咱们来找找看。
具体操作超级简单，第一确保Excel视图菜单的状态栏被勾选，在选定需要进行运算的单元格后，用鼠标右键单击一下Excel状态栏右边的NUM区域会弹出一个小菜单（如图1），里面的“求和（S）、最小值（I）、最大值（M）、计数（C）、均值（A）”就别离对应Excel的SUM函数、MIN函数、MAX函数、COUNT函数和AVERAGE函数，要想进行其中一项运算只需用鼠标作相应选择、NUM区域的左侧就会显示出运算结果。
另外假设您的机械年代久远、怕那个自动运算的功能阻碍系统运行速度的话还可选择小菜单中的“无”项来取消这项功能。
Excel自概念函数的妙用
咱们在编辑Excel工作表时，除能够用其内置的函数处置表中的数据外，还能够依照自己的实际需要，自概念函数来处置表中的数据。
如图1所示是一个学生大体情形记录表，学号的编制原那么是：
前4位是年份，第5位是初、高中代码（1是初中，2是高中），第6位是年级代码（1别离代表初、高一等），第7、8位是班级代码，后面是序号。
下面，咱们以一个具体的实例，自概念一个函数，依照学号来自动填写班级名称（即图中的D列内容）。
1.启动Excel2003（其它版本请仿照操作），打开相应的工作表。
2.执行“工具→宏→VisualBasic编辑器”命令（或直接按“Alt+F11”组合键），进入VisualBasic编辑状态（如图1）。
图1编辑器
3.执行“插入→模块”命令，插入一个新模块。
再双击插入的模块，进入模块代码编辑状态。
4.将以下代码输入其中：
Functionbj（xh）
SelectCaseMid（xh,5,1）
Case1
bj="初"&&Mid（xh,6,3）&&"班"
Case2
bj="高"&&Mid（xh,6,3）&&"班"
EndSelect
EndFunction
[友谊提示]
①上述代码中，“bj”是函数命令，“xh”是变量名称，二者均能够随意修改。
②自概念函数结构是（若是有多个变量，请用英文状态下的逗号分隔）：
Function函数名称（变量名称）
代码
EndFunction
③利用下面的代码，能够达到一样的成效：
Functionbj（xh）
IfMid（xh,5,1）=1Then
bj="初"&&Mid（xh,6,3）&&"班"
Else
bj="高"&&Mid（xh,6,3）&&"班"
EndIf
EndFunction
5.代码输入完成后，关闭VisualBasic编辑窗口，返回Excel编辑状态。
6.选中D2单元格，输入公式：
=BJ（A2），再用“填充柄”将上述公式复制到D列下面的单元格区域中，班级名称即可自动填入相应的单元格中（参见图2）。
Excel中多张表格同时显示的方式
学校有一批表格需要调整打印，但每张表格中都有不同的地址需要对照修改，若是每次打开一个表格改完后最小化，再打开另一个进行修改，实在是比较麻烦。
其实，细心的你只要在Excel2003中略微设置一下，就能够够让所有的表格都显示出来。
方式如下：
单击“窗口”菜单上的“新建窗口”，切换至新的窗口。
然后单击需要显示的工作表。
对每张需要显示的工作表重复以上操作，打开需要在活动工作簿中同时显示的多张表格。
在“窗口”菜单上单击“重排窗口”。
在“排列方式”下单击所需的选项。
若是只是要同时显示活动工作簿中的表格，那么选中“当前活动工作簿的窗口”复选框。
假设要将工作簿窗口还原回整屏显示，可单击工作簿窗口右上角的“最大化”。
通过设置，几个任务同时出此刻工作簿的窗口中，给修改工作带来了专门大的方便。
这种方式在教学中的课表编排中常经常使用到。
巧妙处置Excel工作表表头
当新建或打开一个已有的Excel工作表时，都不可幸免地会碰到表格表头的创建或修改，而且在表格编辑进程中和表格输出时也涉及表头的处置问题，咱们能够利用一些技术来更好地处置Excel表头。
1．让表头自动填写当前月份和日期
比如表格题目为“某公司6月份电费统计表”（如图），其中“6”为进行电费统计的当月月份，第二行为制表日期。
假设该单位电费每一个月发布一次，那么每一个月打开此表修改编辑时，都要对题目中的月份和制表日期进行修改，万一只改动了表内数据，而忘记以上两个时刻的改动，会显现表头时刻与表内数据的矛盾。
咱们采取一个一劳永逸的方法，随时打开此工作表，让表头自动填写当前月份和日期。
这要借助Excel三个函数来实现，在题目单元格输入“="实验中学"&&MONTH（TODAY（））&&"月份电费统计表"”，函数“MONTH（TODAY（））”表示当前月份，在第二行制表时刻单元格输入“=TODAY（）”。
函数“TODAY（）”表示当前日期，就显现图中结果。
完成上述设置，任何时候打开该表，咱们大可安心地去做表内数据的处置工作了，没必要担忧表头时刻会显现什么过失，无需对表头做任何改动。
2．锁定表头
当咱们编辑太长或过宽的Excel工作表时，需要向下或向上转动屏幕，而表头也相应转动，不能在屏幕上显示，如此咱们弄不清要编辑的数据对应于表头的那一个信息。
按以下方式可将表头锁定，使表头始终位于屏幕可视区域。
第一选定要锁定的表头，若是咱们要将图中表头（1~3行）锁定，那么单击A4单元格，然后单击“窗口”菜单中的“冻结拆分窗口”命令，即可完成表头的冻结。
假设还需锁定表格左侧第一列，那么单击第一列和第三行交叉处的右下方单元格B4，以下操作步骤同上。
若是要取消表头锁定，那么单击“窗口”菜单中的“撤消窗口冻结”命令。
3．为表格多页输出自动加表头
若是咱们要输出一个工作表，而此表需要输出多页，如何让第一页以后的每一页都自动加上与第一页相同的表头呢？
能够这么做：
单击“文件”菜单下的“页面设置”命令，弹出“页面设置”对话框；单击“工作表”选项卡，在“打印题目”下“顶端题目行”右边文本框中单击鼠标左键，显现闪烁光标后，在Excel表用鼠标左键选择表头所在行，最后选择“确信”即可。
Excel中排名用函数会更简单
当班主任，总免不了要给学生排个名次什么的。
比如如图1所示成绩表，若是需要维持原表的姓名顺序不变并给所有学生排定名次的话，那么以往的一个可行做法是：
先利用工具栏中的［自动求和］按钮计算出每一个学生的总分，然后点击菜单命令“数据→排序”，打开“排序”对话框，“要紧关键字”选择“总分”，并选中右边的“降序”单项选择项。
点击［确信］按钮，就能够够实现将所有人的成绩按总分以降序排列了。
以后要做的确实是在名次列中手工依次输入每名学生的成绩。
若是数据量比较大时，能够先输入一、二、3后，选中这三个单元格，然后把鼠标移到单元格右下角的小方块上，待鼠标变成十字形后向下拖动，自动产生数字。
最后，点击菜单命令“数据→排序”，打开“排序”对话框。
这次以“编号”为要紧关键字，并选中右边的“升序”单项选择项。
现在才显现咱们想要的结果。
只是，那个方法仍是有些麻烦，要紧表此刻两个方面：
1.完成全数的排序工作要别离以“总分”和“编号”为关键字进行两次排序。
若是不幸在数据表中忘记设置“编号”列，那么第一次排序以后，想再回到原先的序列顺序麻烦就大了（呵呵，您也甭笑，当初我就多次碰到过这种事，可谓前车之鉴）。
2.最麻烦的是排名次。
以总分为关键字排好序后，需要人工填入名次，这固然会很麻烦。
尽管，您能够利用填充柄自动输入数列的功能完成快速输入，但这也会带来另外一个麻烦，那确实是若是总分相同，按通常的情形，他们的名次应该是相同的，只是，自动填充功能是可不能做到这一点的。
因此还需要手工修改有关的名次。
若是有成百上千的数据，岂可不能弄得头都大了？
此刻，咱们来看看如何更快、更好地排出那个无规律的数列。
1.在“总分”列的F2单元格输入“=SUM（C2:
E2）”，然后按下回车键，第一个学生的总分就会计算出来了。
图1原始成绩表
2.在“名次”列的“G2”单元格输入“=RANK（F2,$F$2:
$F$7）”，然后按下回车键，第一名学生的名次也就出来了。
怎么，名次不对？
没关系，往后看。
对了，还要说一句，那确实是上面公式中那个“$F$7”是“总分”栏中的最后一个数据的位置，实际中能够依照表格中的实际情形来填写。
3.选中F2和G2单元格，然后拖动填充柄向下至最后一个单元格，看到了吗？
所有人的总分和名次都在刹时完成了（如图2）。
而且更妙的是，依照这种方式，若是总分相同，名次也是相同的。
数据越多，这种方式就越能表现出它的优势。
碰运气吧！
图2排好序的表
最后要提示的是，在用于排名次的“=RANK（F2,$F$2:
$F$7）”公式中，可万万不要忽略了符号“$”呀！
名次到底能不能排得准确无误，它可是起到很重要的作用呢。
Excel排序技术两那么
1．按行排列数据
相信大伙儿对Excel的排序功能都不陌生，通常数据只是按列排列。
有时咱们需要按行排列数据，这时只需从“数据”菜单当选择“排序”，在排序对话框中左下角选中“选项”按钮，在弹出的对话框当选择“按行排序”，并设置相应关键字即可，而且还能够更改排序的方式是按拼音仍是按笔画排序或是选择自概念序列中的排序方式。
2．自概念排序
例如数据当中有“局长”、“处长”、“科长”、“科员”并按当前顺序排序时，Excel的现有排序功能就无法直接实现了。
这时咱们能够利用自概念排序来实现。
具体做法是：
在“工具”菜单项选择择“选项”，选择“自概念序列”，点“添加”按钮，在右边输入序列中按从小到大的顺序输入排序序列，或选中相应单元格后点击“导入”按钮。
如此会在左侧的自概念序列加入你适才输入的序列，再在排序对话框选项当选择自概念序列就能够够按相应的顺序排序了。
巧用excel为学生考试成绩排名
见图，表格中学号和姓名等是用运算机录入并打印出来，教师只是手写填上考试成绩和排名，然后让打字员录入表格中。
方式和步骤如下：
1．录入完分数后，对学号、姓名和分数进行多个字段排序。
方式是以分数为主排序，鼠标点击第一个分数按住向左下拖动选定前三列后，点击降序按钮，排出了高分到低分的分数排序。
2．在名次列中从上到下输入1……10，最简单的方式是输入1后右击鼠标按住向下拖动至10，选定序列方式填充即可。
3．以学号为主排序，即恢恢复先的学号排序。
鼠标点击学号列第一个（不是一号）按住向右下拖动至名次列最后选定前四列后，点击升序按钮，还原学号排序。
如此就完成了图表中的第一次考试成绩和名次的录入。
若是要完成以后几回考试成绩和名次的录入工作，可把前次考试成绩和名次两列隐藏起来后，按上述方式录入完后，取消隐藏即
将Excel表格内容直接转换成图片
通常情形下，要将Excel的表格内容复制成图片插在Word文档中，咱们除利用抓图软件确实是按键盘上的PrintScreen键来抓取，这两种方式中前者要借助第三方软件来实现，后者尽管简单，但抓取的图片也要通过裁剪、缩放等方式处置。
其实在Excel中还提供了更方便的将表格内容复制成图片的方式，操作超级简便，下面大伙儿就跟从我一路来看看吧。
1．将选区复制成图片
《中国电脑教育报》曾介绍过在Word窗口中按住Shift键再单击“文件”菜单，现在就多出了两个命令即“全数关闭”和“全数保留”，这两个命令超级有效。
在Excel中操作时“文件”菜单中只多出“全数关闭”命令，但当我按住Shift键再单击“编辑”菜单时，我发觉“编辑”菜单中原先的“复制”和“粘贴”命令别离变成了“复制图片”和“粘贴图片”。
两个命令却使我意外地发觉这种在Excel中将表格内容复制成图片的方式。
第一选定需要复制成图片的单元格区域，按住Shift键单击“编辑”菜单，选择“复制图片”命令，弹出“复制图片”对话框，按系统默许设置（即将“外观”和“格式”别离选定“如屏幕所示”和“图片”），单击“确信”按钮。
这时就将选定的表格区域复制成图片了。
假设需要将其粘贴出来，只需要按住Shift键再单击“编辑”菜单，选择“粘贴图片”命令即可（或不按Shift键，直接从“编辑”菜单当选择“选择性粘贴”命令，以“图片”方式粘贴），还能够将其粘贴到其他软件中去。
2．选区的摄影操作
除用上述方式能够将单元格区域复制成图片，另外在Excel中还提供了一个超级简单抓图工具，这确实是“摄影”工具。
该按钮能够通过下面的操作拖放到工具栏中：
右击工具栏，选择“自概念”命令，从弹出的“自概念”对话框中单击“命令”选项卡，选择左框“类别”中的“工具”，在右框的“命令”中找到“摄影”工具，并将其拖放到工具栏中备用。
操作时，第一选定要复制成图片的单元格区域，单击工具栏中的“摄影”按钮，现在选区周围显现虚框选中状态，鼠标指针变成“+”字交叉形，现在单击鼠标，选区就以图片的形式粘贴出来了。
用这种方式复制的图片，若是源数据表中的数据发生了转变，对应的图中相应数据也会随着发生转变。
但粘贴到其他软件中后，相应位置的数据那么固定下来，再也不随之发生转变。
Excel中常见问题及解决方式
笔者与Excel常常“打交道”的进程中，常碰到如此或那样的问题。
其中，以下几方面的问题尤其突出。
现将显现的问题及解决的方式告知同行，以此共勉。
一、在Excel中不能进行求和运算
由于在操作中更改了字段的数值后，求和字段的所有单元格中的数值没有随之转变，造成不能正常运算。
能够单击“工具→选项”命令，在打开的“选项”对话框中单击“从头计算”选项卡。
在“计算”选项区当选中“自动重算”单项选择按钮，单击“确信”按钮，就能够够进行自动计算并更新单元格的值了。
二、在Excel中打开多个工作表时提示“内存不足，不能执行显示”
这时第一关闭所有的应用程序，在桌面上单击鼠标右键，从弹出的快捷菜单当选择“属性”选项，在打开的“显示属性”对话框中单击“设置”选项卡，将“颜色”下拉列表框设置为256色，即可解决问题。
三、在Excel中显现启动慢且自动打开多个文件
进入Excel中后，单击“工具→选项”命令，在打开的“选项”对话框中单击“常规”选项卡，删除“替补启动目录”文本框中的内容，单击“确信”按钮即可。
四、在Excel中显现“＃DIV/0！
”错误信息
假设输入的公式中的除数为0，或在公式中除数利用了空白单元格（当运算对象是空白单元格，Excel将此空值说明为零值），或包括零值单元格的单无格引用，就会显现错误信息“#DIV/0！
”。
只要修改单元格引用，或在用作除数的单元格中输入不为零的值即可解决问题。
五、在Excel中显现“＃VALUE！
”错误信息
此情形可能有以下四个方面的缘故之一造成：
一是参数利用不正确；二是运算符利用不正确；三是执行“自动更正”命令时不能更正错误；四是当在需要输入数字或逻辑值时输入了文本，由于Excel不能将文本转换为正确的数据类型，也会显现该提示。
这时应确认公式或函数所需的运算符或参数是不是正确，而且在公式引用的单元格中包括有效的数值。
六、在Excel中显现“＃NAME？
”错误信息
显现此情形一样是在公式中利用了Excel所不能识别的文本，比如：
利用了不存在的名称。
解决的方式是：
单击“插入→名称→概念”命令，打开“概念名称”对话框。
若是所需名称没有被列出，在“在当前工作薄的名称”文本框中输入相应的名称，单击“添加”按钮将其添加，再单击“确信”按钮即可。
七、在Excel中显现“＃NUM！
”错误信息
当函数或公式中利用了不正确的数字时将显现错误信息“#NUM!
”。
这时应确认函数中利用的参数类型的正确性，然后修改公式，使其结果在-10307到+10307范围内即可。
Excel打印故障问答锦囊
问：
我想打印彩色的页眉，却不能为页眉应用彩色格式。
如何解决？
答：
页眉中的文字只能打印为黑色，即便利用彩色打印机，也不能对页眉和页脚中的文字应用彩色。
要打印彩色页眉，能够利用打印题目代替页眉。
例如，要在每页的顶端打印公司的徽标与地址，将徽标与地址信息放置在工作表的前几行；在“文件”菜单上，单击“页面设置”，单击“工作表”选项卡，然后在“顶端题目行”框中，输入包括徽标与地址的行。
问：
我利用“格式”菜单上的“背景”命令，为工作添加了背景图案，但打印时，却没有打印工作表背景。
什么缘故？
如何解决？
答：
用“格式”菜单上的“背景”命令添加到工作表中的背景图案可不能被打印出来。
但用“插入”菜单中的“图片”命令添加到工作表中的图案和用“格式”菜单上的“单元格”命令添加到工作表中的单元格背景图案或阴影，都会被打印出来。
问：
在我打印工作表时，Excel忽略了所设置的分页符。
什么缘故？
答：
若是已设置让Excel将工作簿打印到特定数量的页面中，那么，Excel将忽略已设置的手动分页符，并缩小打印工作表的尺寸。
假设要将工作表打印到指定的页数中并利用手动插入的分页符，请单击“文件”菜单上的“页面设置”，再单击“页面”选项卡，然后单击“缩放比例”。
若是“缩放比例”不能产生预期成效，你能够在设置打印区域时，将其中的每一页都选定为独立的区域，Excel会将打印区域中不相邻的区域打印到独立的页面上。
问：
我在工作表中设置了多个打印区域后，Excel却不能将多个打印区域打印到同一页上。
如何解决？
答：
若是打印区域中包括工作表中的不相邻区域，在默许情形下，Excel将把每一个区域打印到单独的页面上。
你能够利用以下方式指定工作表中的独立区域，并将它们打印到同一页面上。
1．将不相邻的列打印到相邻的位置上：
选定不想打印的列，点击“格式”菜单中的“列”，然后单击“隐藏”。
设置一个包括所有列的打印区域，其中也包括隐藏的列。
2．将不相邻的行打印到上下相邻的位置上：
选定不想打印的行，点击“格式”菜单中的“行”，然后单击“隐藏”。
设置一个包括所有行的打印区域，其中也包括隐藏的行。
Excel超有效打印技术
打印表格是很多朋友在工作中常常要面临的任务，那个地址咱们通过实例图解的方式向大伙儿提供一些Excel中有效的打印技术。
认真学习并利用这些技术，能够使咱们能加倍驾轻就熟地完成工作中的各类打印任务。
一、在每一页上都打印行题目或列题目
在Excel工作表中，第一行通常寄存这各个字段的名称，如“客户资料表”中的“客户姓名”、“效劳帐号”、“公司名称”等等，咱们把这行数据称为题目行（题目列依此类推）。
当工作表的数据过量超过一页时，打印出来只有第一页有行题目，如此阅读起来不太方便。
用下面方式能够让每一页都打印行题目：
进入要打印的工作表，选择菜单“文件→页面设置”命令，在弹出的对话框当选择“工作表”选项卡，然后单击“打印题目”区“顶端题目行”文本区右端的按钮，对话框缩小为一行，并返回Excel编辑界面，用鼠标单击一下题目行所在的位置（图1），再单击回车即可。
（图1图片较大，请拉动转动条观看）
这时对话框恢恢复状，能够看到“顶端题目行”文本框中显现了适才选择的题目行（图2），查对无误后单击“确信”完成设置。
以后打印出来的该工作表的每一页都会显现行题目了。
图2
说明：
列题目的设置可仿照操作。
假设需要要打印工作表的行号和列标（行号即为标识每行的数字，列标为标识每列的字母），勾选“打印”区的“行号列标”复选框即可。
二、只打印工作表的特定区域
在实际的工作中，咱们并非老是要打印整个工作表，而可能只是特定的区域，那么应该如何设置呢？
请随着下面的方式进行。
（一）、打印特定的一个区域
若是需要打印工作表中特定的一个区域有下面两种方式。
方式1：
先选择需要打印的工作表区域，然后选择菜单“文件→打印”命令，在弹出的“打印内容”对话框的“打印内容”区，勾选“选定区域”单项选择框（图3），再单击“确信”即可。
图3
方式2：
进入需要打印的工作表，选择菜单“视图→分页预览”命令，然后选中需要打印的工作表区域，单击鼠标右键，在弹出的菜单当选择“设置打印区域”命令即可（图4）。
图4
说明：
方式1适用于偶然打印的情形，打印完成后Excel可不能“记住”那个区域。
而方式2那么适用于老是要打印该工作表中该区域的情形，因为打印完成后Execl会记住那个区域，下次执行打印任务时还会打印那个区域。
除非你选择菜单“文件→打印区域→取消打印区域”命令，让Excel取消那个打印区域。
（二）、打印特定的几个区域
若是要打印特定的几个区域，和上面的方式对应，也有两种方式。
方式1：
开始时，按住Ctrl键，同时选中要打印的几个区域，后面的操作与上面的方式1相同。
方式2：
在分页预览视图下，用前面方式2介绍的方式设置好一个打印区域后，再选择要打印的第二个区域，单击鼠标右键，在弹出的菜单当选择“添加到打印区域”命令即可（图5）。
用一样的方式设置其它需要
升级会员 