Excel技巧大全

Excel单元格内文本换行三法

用过Excel的朋友都知道，在它的单元格里录入文本不像在Word中那样能很方便地换行，经过长期使用，笔者发现有三种办法可以解决在Excel单元格内输入文本时的换行难问题。

一、快捷键法

在单元格里输入文本后想换行，只要按住Alt键不放，然后敲一下回车键即可实现换行。如果你已经输完文本，发现内容太长了，则可双击该单元格，然后将光标移到要换行的位置，按下“Alt+Enter”即可。

二、格式设置法

先选定要使文本换行的单元格，执行“格式→单元格”命令，在弹出的“单元格格式”对话框中选择“对齐”，选中“文本控制”下的“自动换行”即可实现该单元格文本自动换行，再用格式刷去刷一下要自动换行的单元格就可以实现批量自动换行了。

三、文本框法

Word里的文本框大家比较熟悉，在它里面录入文本时会自动换行，而且可以只敲回车实现换行。在Excel中也可以进行同样的操作。

点击“绘图工具栏里”的“文本框”按钮，绘制一个文本框，其宽度和单元格宽度相同(可以按住Alt键同时拖动鼠标进行微调)，在里面录入文本(能够自动换行)，然后移动文本框的位置到单元格(可以用“Ctrl+方向键”微调)，再将文本框设置为“无填充色”和“无线条色”，最后别忘了将单元格的高度调整得与文本一样。

Excel名称框使用技巧

Excel中的名称框位于编辑栏左端的下拉列表框中，它主要用于指示当前选定的单元格、图表项或绘图对象。灵活运用名称框，对我们提高Excel的使用效率有很大帮助。

一、快速命名单元格或单元格区域

选定需要命名的单元格或单元格区域，然后直接在“名称框”中键入名称，再按回车键即可快速命名选定的单元格或单元格区域。

二、快速移动至指定单元格

如果要将活动单元格移动到指定的单元格，可直接在“名称框”中键入需要移动至相应位置的单元格标志。例如用户要将活动单元格从A1单元格移动到X1000单元格时，如果使用移动鼠标指针的方法将是一件非常麻烦的操作，但是你只要在“名称框”中键入“X1000”，按下回车键后活动单元格就会立即移动到X1000单元格中。

三、快速选定单元格区域

在“名称框”中直接键入需要选定的单元格区域标志，例如“B2:H200”，然后按下回车键，这时B2:H200单元格区域就被选中了。

如果需要选定工作表中不相邻的单元格或单元格区域，只要在“名称框”中使用逗号将各个单元格分隔开即可。例如在“名称框”中键入“A1，C2:D10，H:H”，按下回车键后，即可将A1单元格、C2:D10单元格区域以及H行同时选中。

巧用替换命令让单元格变色

在Excel XP中，查找和替换功能有了很大的增强，用户不仅可以对工作表中的字符进行查找和替换，而且还能够对单元格的格式进行查找和替换。现在我们就利用Excel

XP这一功能，快速将Excel工作表中包含公式的单元格设置为与普通单元格不同的颜色，以引起使用者的注意，避免用户误删包含有公式的单元格。

1.启动Excel XP，打开需要进行设置的工作簿文件。

2.选择“编辑”菜单下的“替换”命令，打开“查找和替换”对话框，在此对话框中单击“选项”按钮，切换“查找和替换”高级选项窗口(图1)。

3.由于在Excel中，包含公式的单元格都是以“=”号开头的，因此我们在“查找内容”框中键入“=”(引号不必输入)，以查找所有包含公式的单元格；由于我们只是设置包含公式单元格的格式，并不改变单元格公式内容，因此在“替换为”框中也要键入“=”(引号不必输入)；单击“替换为”输入框右侧的“格式”按钮，打开“替换格式”对话框(图2)。单击“图案”选项卡，在“单元格底纹颜色”选项区中为包含公式的单元格指定一种醒目的底纹颜色，单击“确定”按钮返回“查找和替换”对话框。

4.默认方式下，Excel

XP只对活动工作表中的内容进行查找和替换，如果需要对整个工作簿进行查找和替换操作，单击“范围”下拉箭头，选择“工作簿”选项，最后单击“全部替换”按钮，Excel

XP就会立即为工作表或工作簿中所有包含公式的单元格设置指定的单元格底纹颜色。

Office中Enter键的妙用

一、在Excel中的妙用

1.在单元格内换行

使单元格处于编辑状态，按“Alt+Enter”组合键后键入相应的数据。

2.同时在多个单元格中输入相同数据

选定需要输入数据的单元格，选定的单元格可以是相邻的，也可以是不相邻的。键入相应数据，然后按“Ctrl+Enter”组合键。

3.编辑数组公式

在数组区域中单击任一单元格。单击编辑栏，当编辑栏被激活时，大括号({

})在数组公式中消失。编辑数组公式内容，然后按“Ctrl+Shift+Enter”组合键。

二、在Word中的妙用

1.插入分页符

单击要出现在下一页上的行，按Ctrl+Enter”组合键。

2.分割表格

单击需要分割的位置，按“Ctrl+Shift+Enter”组合键。

Excel XP函数运用实例四则

实例一：身份证中信息的提取

1.持证人性别的判断：我们知道，旧身份证(15位)号码的最后1位数值，表示持证人的性别，若为奇数则为男，若为偶数则为女。我们利用Excel函数，对身份证号码进行适当处理，即可自动判断持证人的性别，并将结果填入相应的单元格中(此处假定身份证号码保存在B2单元格中，结果填入C2单元格中)。

（1）利用函数的嵌套来判断。在C2单元格中输入公式：=IF(RIGHT(B2，1)="1"，"男"， IF(RIGHT(B2，1)="3"，"男"，

IF(RIGHT(B2，1)="5"，"男"， IF(RIGHT(B2，1)="7"，"男"，

IF(RIGHT(B2，1)="9"，"男"，"女")))))。

（2）综合运用函数来判断。在C2单元格中输入公式：=IF(OR(RIGHT(B2，1)="1"，RIGHT(B2，1)="3"，RIGHT(B2，1)="5"，RIGHT(B2，1)="7"，RIGHT(B2，1)="9")，"男"，"女")。

在上面两个公式中，前一个是利用IF函数的嵌套来实现的，由于函数最多只能嵌套7层，因而有一定的局限性，且比较麻烦；后一个公式由于采用了OR函数，既没有层数的限制，也简单一些。

（3）运用其他函数来判断。我们在C2单元格中输入公式“=IF(RIGHT(B2，1)/2=INT(RIGHT(B2，1)/2)，"女"，"男")”或“=IF(MOD(RIGHT(B2，1)，2)=0，"女"，"男")”即可实现。

2.持证人出生时间的提取：同样，旧身份证号码中，第7—12位数字代表的是持证人的出生年月日，我们只要在D2(假定将出生时间存放在D2单元格中)单元格中输入公式：=19&MID(B2，7，2)&"年"&MID(B2，9，2)&"月"&MID(B2，11，2)&"日"，即可从B2单元格的身份证号码中将出生日期自动提取出来，并以中文习惯形式显示出来(如“1963年03月04日”等)。

小技巧：执行“视图→工具栏→公式审核”命令，打开“公式审核”工具条(图1)，按最右边的“公式求值”按钮，在随后弹出的“公式求值”对话框中，反复按“求值”按钮，即可逐项核查运算的结果。

注释：上述公式中所用的函数：①IF──逻辑函数：判断一个条件是否满足，如果满足返回一个值，如果不满足返回另一个值。②OR──逻辑函数：如果任一参数值为TRUE，则返回TRUE；只有当所有参数值均为FALSE，才返回FALSE。③RIGHT──文本函数：从一个字符串的最后一个字符开始返回指定个数的字符(也可以使用RIGHTB函数)。④MID──文本函数：从文本字符串中指定的起始位置起返回指定长度的字符。⑤MOD──数学函数：返回两数相除的余数。⑥INT──数学函数：将数值向下取整为最接近的整数。

实例二：批量插入固定字符

大家知道新的身份证号码(18位)将旧身份证号码的年份由2位改为4位。现在，我们要将年份的前两位(19)插入旧身份证号码中，如果一个一个地去插入，显然既麻烦又容易出错，如果利用Excel的函数来做，则既方便又准确。

此处假定旧身份号码保存在B列中，插入“19”后的号码暂时保存在C列中。我们在C2单元格中输入公式：=LEFT(B2，6)&19&RIGHT(B2，9)。再次选中C2，将鼠标移到右下角成“细十字”状(我们称之为“填充柄”)，按住左键向下拖拉，即可将上述公式复制到C列的以下单元格中(在复制过程中，系统会智能化地改变相应的单元格)。

小技巧：选中C列，按“复制”按钮，再选中B列，执行“编辑→选择性粘贴”命令，打开“选择性粘贴”对话框，选中“粘贴”下面的“数值”选项，然后按“确定”按钮，再将C列删除，就可以将C列的值正确地复制到B列中，从而不影响原有表格的结构。

注释：上述公式中用到一个新函数：LEFT──文本函数：从一个字符串的第一个字符开始返回指定个数的字符(也可以使用LEFTB函数)。

实例三：学生成绩的统计

图2是一张教师(特别是班主任)非常熟悉的“学生成绩统计表”，以前在统计各项数据时，大家通常采用的是笔算或按计算器的办法来进行的。现在可以用Excel来帮我们快速、准确地完成这些繁杂的统计工作。

1.总分的统计：选中H3单元格(用于存放学生丁1总分的单元格)，输入公式：=SUM(C3:G3)，按下Enter键后，丁1的总分即计算出来，并填入H3单元格中。用填充柄将该公式复制到H4—H47单元格中(假定该班级有45名同学)，将其他同学的总分统计出来。

用类似的方法，可以将某一学科的总分统计出来，并填入第48行相应的单元格中。

2.平均分的计算：选中C49单元格，输入公式：=AVERAGE(C3:C47)，按下Enter键后，语文学科的平均分即计算出来。

3.最高(低)分的统计：选中C50单元格，输入公式=MAX(C3:C47)，挑出语文学科的最高分；选中C51单元格，输入公式：=MIN(C3:C47)，挑出语文学科最低分。

4.各分数段学生人数的统计：分别选中C52和C57单元格，输入公式：=COUNTIF(C3:C47，">=90")和=COUNTIF(C3:C47，"<50")，就统计出了语文学科大于等于90分和低于50分的学生人数；分别选中C53、C54、C55、56单元格，依次输入公式=COUNTIF(C3:C47，">=80")-COUNTIF(C3:C47，">=90")、=COUNTIF(C3:C47，">=70")-COUNTIF(C3:C47，">=80")、=COUNTIF(C3:C47，">=60")-COUNTIF(C3:C47，">=70")、=COUNTIF(C3:C47，">=50")-COUNTIF(C3:C47，">=60")，即可统计出语文学科其他各分数段的学生人数。

5.名次的排定：选中I3单元格，输入公式：=RANK(H3，$H$3:$H$47)，按下Enter键后，丁1同学总分的名次即排定，并填入I3单元格中。用填充柄就可以将I3单元格中的公式复制到I4—I47单元格中，用于排定其他学生的名次。对公式中引用的参数做适当的修改，还可以排出每位同学各个学科成绩的名次。

小技巧：①图2的表格制作好了以后，将“学号、姓名、各科成绩”等内容清空，再执行“文件→另存为”命令，打开“另存为”对话框，将“文件类型”选定为“模板”，并给定一个文件名，将该工作簿(成绩统计表)保存为模板，方便以后的使用。②如果要增加学生或增加学科，只要插入空行(列)就行了，完全不必更改公式(系统会智能化地对公式进行调整)。③对于没有成绩的学生，其对应的单元格一定要为空，千万不要填入数字“0”，以免造成统计出错。

注释：上述公式中用到几个新的函数：SUM──数学函数：返回单元格区域中所有数值的和。AVERAGE──统计函数：计算参数的算术平均数。MAX(MIN)──统计函数：返回一组数值中的最大(小)值，忽略逻辑值和文本字符。COUNTIF──统计函数：计算某个区域中满足给定条件单元格的数目。

实例四：多条件统计──数组公式的使用

图3是一张普通的工资表，现在我们要统计职称为“高工”、性别为“男”的职工基础工资之和(数据位于A1F102区域中)。用一般的函数公式难以达到这一目的，我们可用数组公式来实现。

选中E103单元格(用于保存统计结果的单元格)，先输入公式：=SUM(IF(C3:C102="男"，IF(D3:D102="高工"，E3:E102)))，然后在按住“Ctrl+Shift”组合键(非常关键！！！)的同时按下Enter键即可。

小技巧：如果你不想让别人改动你所输入的公式，可以这样操作：选中所有无公式的单元格(按住Ctrl键，可以用鼠标选中不连续的单元格)，执行“格式→单元格”命令，打开“单元格格式”设置对话框，点击“保护”标签，将“锁定”选项前面的“∨”去掉，确定返回。再执行“工具→保护→保护工作表”命令，打开“保护工作表”对话框，两次输入密码后，确定返回即可。

特别提醒：实际上，上面我们输入的是一个数组公式，两端各有一个数组公式标志“{} ”，公式变成了：{=SUM(IF(C2:C20="男"，IF(D2:D20="高工"，E2:E20)))}，这个数组公式标志“{}”是不能直接用键盘输入的！

用Excel自动计息

在银行上班的同志肯定都会碰到计息的问题，而繁琐的计算有时的确很让人心烦。本人在实际工作中总结出了一些经验，用Excel成功简化了这一繁琐的工作，现在介绍给大家。

第一步：新建一Excel表格。格式如图所示。

需要注意的是，我将D3、E3、F3单元格合并成D3。并将D3单元格设成日期格式，起息日栏已全部设置为日期格式。

第二步：输入户名、本金、利率及起息日。

第三步：在天数栏录入公式。由于单位的特殊要求，我们对单位的计息，是按每月30天计算的，所以在天数栏录入的公式比较大。在E5栏输入如下内容：=YEAR($D$3)360+MONTH($D$3)30+DAY($D$3-(YEAR(D5)360+MONTH(D5)30+DAY(D5)))。我的思路是用D3的日期减去D5单元格的日期，由于这里是按每个月30天来计算，所以用年乘360，用月份乘30加上天数来减。如果你要按实际天数计算输入的公式会更简单。只需在E5栏输入=$D$3-D5

便可。之所以要用$D$3而不用D3是便于我们在复制公式时不产生错误。现在让我们把光标移动到E5单元格右下角，当鼠标变成黑心加号时，拖动鼠标，复制公式至D7。

第四步：让天数栏正确显示天数。在天数栏内录入公式后，其显示的结果并不是一个数值，而是一个日期，这时我们可以选中天数栏，右键单击鼠标选择设置单元格格式，在数字分类栏选择数值，小数点后位数为0。此时天数栏将显示为整型数值。

第五步：在利息栏输入公式。在E5单元格输入=B5*C5*E5/360，并参照第三步复制公式至E7。在合计项中输入公式=SUM(E5:E7)。

至此这个表格就算完成了，以后每月用户要计算利息的时候只要在D3单元格内输入要计算的日期，系统便会自动算出利息。此外，用户还可以通过工具栏菜单→保护→保护工作表，以防他人修改自己的公式。

用Excel巧编英语学习趣味练习

最近，我利用Excel XP的函数及“条件格式”功能，为儿子编制了一套记忆英语单词的练习，大大提高了儿子的学习兴趣。具体编制方法如下：

1.启动Excel XP，新建工作簿，并取名保存(如yylx1.xls)。

2.分别双击下面的工作簿名称(Sheet1、Sheet2)，将它命名为“练习、答案”(如图1)。

3.选中“答案”工作表的相应单元格，输入英语单词及相关内容。

4.选中“练习”工作表的相应单元格，与“答案”工作表相对应，输入英语单词的中文及其他内容(如图1)。

5.选中“批改”列下面的一个单元格(如D2)，输入公式：=IF（EXACT（C2答案 B2

=TRUE，"OK"，"NO"。再选中E2单元格，输入公式：=IF（D2="OK"，"很好"，"再想想"）“很好”和“再想想”文本你可以随意设置。

提示:①上述D2单元格输入公式的含义是：如果(IF)“练习”表中C2单元格的文本与“答案”表中B2单元格的文本完全相同(即EXACT函数返回“TRUE”)，则在D2单元格中显示“OK”，反之显示“NO”。②公式中的括号、逗号、引号均应在英文状态下输入。③如果E2单元格中的公式不输入，并不影响练习的正常使用。

6.同时选中D2和E2单元格，将鼠标移到E2单元格右下角成细十字状时(我们通常称之为“填充柄”)，按住左键向下拖拉，将上述公式复制到其他题目相应的单元格中。

提示:在公式的复制过程中，系统会智能化地更改公式中相应的单元格，不需要我们去一一修改了，非常方便。

7.选中D2单元格，执行“格式→条件格式”命令，打开“条件格式”对话框(如图2)，按“条件1”下面第二个方框旁的下拉按钮，选“等于”选项，在后面的方框中输入“OK”(不含引号，下同)，再按“格式”按钮，在随后弹出的“单元格格式”对话框中，将字体颜色设置为蓝色(表示正确)，按“确定”后返回“条件格式”对话框。

8.按“添加”按钮，仿照第7步的操作，在“等于”后面的方框中输入“NO”，将字体设置为红色(表示错误，如图2)。按“确定”后退出“条件格式”对话框。

提示：上述第7、8步操作只是为了增加练习的趣味性而已，如果不进行，并不影响练习的正常使用。

9.选中“答案”工作表中的第1至第10行(视具体练习量而定)，右击鼠标，选“隐藏”选项，将选中的行隐藏起来。再执行“工具→保护→保护工作表”命令，打开“保护工作表”对话框(如图3)，两次输入密码后，按“确定”退出。

经过这样的设置后，练习者不能查看答案。

10.至此，英语练习编制完成。

使用时，启动Excel

XP，打开上述文件(yylx1.xls)，选中“练习”工作表，对照B列中的中文，在C列中相应的单元格中填入英语单词，如果输入正确(包括大小写)，则D列相应单元格中显示出蓝色的“OK”字样，且E列相应单元格中显示出“很好”字样；如果输入错误，则D列相应单元格中显示出红色的“NO”字样，且E列相应单元格中显示出“再想想”字样。

提示:①通过更改“练习”和“答案”工作表中“题目”列和“答案”列的内容，即可快速制作出一套新的练习。②当然在需要更改“答案”工作表中的内容之前，先要执行“工具→保护→撤销保护工作表”命令，输入密码后，取消对工作表的保护，再将隐藏的行显示出来，才可以修改。③对于答案明确的标准化练习题，都可以用上述方法来编制。④以上制作过程在Excel

2000中同样可以实现。

如果你感兴趣，不妨动动手，既可辅导了孩子的学习，又可提高自己和孩子的电脑水平，何乐而不为。

在Excel中相同数据的统计

在Excel中，Countif(range，criteria)函数是用来统计某个区域中满足给定条件单元格的数目的。Range是要统计的区域，criteria是以数字、表达式、字符串形式给出的计数单元格必须符合的条件。

下面以某公司市场营销部人员表(图1)为例，进行某一姓名出现的次数统计和所有姓名出现的次数统计。

1.统计“姓名”这一列中有几个“姜丽”，在D7单元格中输入“=COUNTIF(A3:A7,A5)”或“=COUNTIF(A3:A7,"姜丽")”，按Enter键，结果如图2所示。

2.统计“姓名”列中每一姓名出现的次数。首先，在D3单元格中输入“=COUNTIF(A3:A7,A3)”，然后将此公式利用填充柄复制到D4、D5、D6、D7单元格中，修改单元格的值，结果很快就统计出来。

3.如果一张工作表中数据很多，要求查出某一列中重复的数据，当然你不想逐个单元格的输入或复制公式。下面介绍如何快速查出重复数据(多列中不行)。

仍以上表为例，步骤如下：

①在D3单元格中输入“=COUNTIF(A3:A7,A3)”。

②从上到下同时选中D3、D4、D5、D6、D7。

③单击“编辑”菜单中“填充”子菜单中的“向下填充”命令，结果就出来了。

④将光标置于D2单元格中，单击“数据”菜单中“筛选”子菜单中的“自动筛选”命令，单击“次数”旁边的列表框，选中2，这样，重复次数为2的记录就显示出来了。

以上的操作也适用于同一行中的数据。

Excel中填充柄的几种用法

大家知道在使用Excel输入数据的时候，经常要输入许多连续或不连续的数据，如学生的学号，货物的批号等等，如果采用手工输入的方法，将是一件非常麻烦而且容易出错的事情，如果我们使用Excel中“填充柄”的方法即可轻松快速输入许多连续或不连续的数据。

所谓“填充柄”就是在Excel中位于某区域上的一个小黑块(图1)，如果将鼠标指向它的时候，鼠标的形状变为黑十字。拖动“填充柄”可以将内容复制到相邻单元格中。下面以输入不连续学号和连续学号为例分别介绍使用“填充柄”拖拉填充的几种方法：

一、创建自定义填充序列后，输入第一个学号，再使用“填充柄”拖动(适用于大量、连续或不连续的数据)：如果经常输入大量的且不连续的数字及编号，如多个学年编号不同的学号等等，可以创建自定义序列，具体方法如下：

首先，请选择“工具/选项”菜单，在弹出的“选项”对话框中单击“自定义序列”列表框中的“新序列”选项，然后在“输入序列”编辑列表框中，从第一个序列(此处可以输入98001~98010、99001~99010等学号)元素开始输入新的序列。在键入每个元素后，按Enter键。整个序列输入完毕后，请单击“添加”按钮。

自定义序列中可以包含文字或带数字的文字。如果要创建只包含数字的自定义序列，如本文的“学号”等，则自定义序列后，在输入“学号”前还需要先选定足够的空单元格(一般选定某列即可)，然后在“格式”菜单上，单击“单元格”命令，再单击“数字”选项卡，对选定的空单元格应用文本格式，最后在设置了格式的单元格中输入序列项。本例须在A2单元格中输入98001后，将鼠标移动到A2单元格的右下角(填充柄位置)，可以看到光标变为一个黑色的十字，这时可按住鼠标左键向下拖动到指定位置松开即可(见图2)。

使用该方法需注意的是：①必须首先自定义序列号；②必须将该列格式设置为文本格式(如序号中包含文字则不行，因为当输入含有文字的数据时Excel自动默认为文本格式)。

小技巧：先输入西文单引号“'”再输入学号：98001(适合于输入超过15位的数字，如输入新版本身份证号码等“数字”不参与计算的场合)，这样输入的数字格式自动变为文本格式，可以直接使用“填充柄”拖动，这是一个非常简便的方法。

二、手工输入第一、第二两个相邻的“学号”再使用“填充柄”拖动(适用于连续数字)：如：首先在A2单元格中输入98001，在A3单元格中输入98002，然后用鼠标选中A1、A2后直接将鼠标移动到A3单元格的右下角(填充柄位置)，当鼠标也变为一个黑十字（见图3），即可按住鼠标左键向下拖动到指定位置松开即可。

三、手工输入首位学号，直接使用“Ctrl+填充柄”组合拖动(适用于连续数字，推荐用此方法)：从上面的图2和图3中可以发现两图的98001在单元格中的数字位置是不一样的，图3中98001在单元格右侧，是常规数字格式(数字的默认格式状态是在右侧，但可以改变)，图2中的98001在单元格的左侧，是文本格式(文本默认状态是在左侧，也可以改变)。由于Excel在输入数字时自动默认为数字格式，因此上述两种方法，如果不经常输入如此多的“学号”，笔者认为还是第二种方法较为实用，第一种方法较为麻烦，但适用于经常输入重复、不连续的数字(多个学年学号)等，大家可以根据需要或习惯选取。

下面再介绍一种更为简便的方法：首先在A2输入首位学号98001，将鼠标移动到A2单元格的右下角(填充柄位置)，当鼠标变为一个黑色的十字时，这时在按住Ctrl键(可以看到黑色的十字光标右上角还有一个小的黑色十字，见(图4)的同时，再按住鼠标左键向下拖动到指定位置松开即可。