Excel编辑技巧.docx

Excel编辑技巧.docx

《Excel编辑技巧.docx》由会员分享，可在线阅读，更多相关《Excel编辑技巧.docx（16页珍藏版）》请在冰豆网上搜索。

Excel编辑技巧

巧用Excel编辑技巧

1编辑技巧

（1）分数的输入

　　如果直接输入“1/5”，系统会将其变为“1月5日”，解决办法是：

先输入“0”，然后输入空格，再输入分数“1/5”。

（2）序列“001”的输入

　　如果直接输入“001”，系统会自动判断001为数据1，解决办法是：

首先输入“'”（西文单引号），然后输入“001”。

　　（3）日期的输入　

　　如果要输入“4月5日”，直接输入“4/5”，再敲回车就行了。

如果要输入当前日期，按一下“Ctrl+；”键。

　　（4）填充条纹　

　　如果想在工作簿中加入漂亮的横条纹，可以利用对齐方式中的填充功能。

先在一单元格内填入“*”或“~”等符号，然后单击此单元格，向右拖动鼠标，选中横向若干单元格，单击“格式”菜单，选中“单元格”命令，在弹出的“单元格格式”菜单中，选择“对齐”选项卡，在水平对齐下拉列表中选择“填充”，单击“确定”按钮。

　　（5）多张工作表中输入相同的内容

　　几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2......来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。

输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据会接着出现在选中的其它工作表内。

　　（6）不连续单元格填充同一数据

　　选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。

在编辑区中输入数据，然后按住Ctrl键，同时敲一下回车，在所有选中的单元格中都出现了这一数据。

　　（7）在单元格中显示公式

　　如果工作表中的数据多数是由公式生成的，想要快速知道每个单元格中的公式形式，以便编辑修改，可以这样做：

用鼠标左键单击“工具”菜单，选取“选项”命令，出现“选项”对话框，单击“视图”选项卡，接着设置“窗口选项”栏下的“公式”项有效，单击“确定”按钮。

这时每个单元格中的分工就显示出来了。

如果想恢复公式计算结果的显示，就再设置“窗口选项”栏下的“公式”项失效即可。

　　（8）利用Ctrl＋*选取文本

　　如果一个工作表中有很多数据表格时，可以通过选定表格中某个单元格，然后按下Ctrl＋*键可选定整个表格。

Ctrl＋*选定的区域为：

根据选定单元格向四周辐射所涉及到的有数据单元格的最大区域。

这样我们可以方便准确地选取数据表格，并能有效避免使用拖动鼠标方法选取较大单元格区域时屏幕的乱滚现象。

　　（9）快速清除单元格的内容

　　如果要删除内容的单元格中的内容和它的格式和批注，就不能简单地应用选定该单元格，然后按Delete键的方法了。

要彻底清除单元格,可用以下方法：

选定想要清除的单元格或单元格范围；单击“编辑”菜单中“清除”项中的“全部”命令，这些单元格就恢复了本来面目。

2、单元格内容的合并

　　根据需要，有时想把B列与C列的内容进行合并，如果行数较少，可以直接用“剪切”和“粘贴”来完成操作，但如果有几万行，就不能这样办了。

　　解决办法是：

在C行后插入一个空列（如果D列没有内容，就直接在D列操作），在Ｄ1中输入“=B1&C1”，D1列的内容就是B、C两列的和了。

选中D1单元格，用鼠标指向单元格右下角的小方块“■”，当光标变成"＋"后，按住鼠标拖动光标向下拖到要合并的结尾行处，就完成了B列和C列的合并。

这时先不要忙着把B列和C列删除，先要把D列的结果复制一下，再用“选择性粘贴”命令，将数据粘贴到一个空列上。

这时再删掉B、C、D列的数据。

　　下面是一个“&”实际应用的例子。

用AutoCAD绘图时，有人喜欢在EXCEL中存储坐标点，在绘制曲线时调用这些参数。

存放数据格式为“x，y”的形式，首先在Excel中输入坐标值，将x坐标值放入A列，y坐标值放入到B列，然后利用“&”将A列和B列合并成C列，在C1中输入：

=A1&"，"&B1，此时C1中的数据形式就符合要求了，再用鼠标向下拖动C1单元格，完成对A列和B列的所有内容的合并。

　　合并不同单元格的内容，还有一种方法是利用CONCATENATE函数，此函数的作用是将若干文字串合并到一个字串中，具体操作为“=CONCATENATE（B1,C1）”。

比如，假设在某一河流生态调查工作表中，B2包含“物种”、B3包含“河鳟鱼”，B7包含总数45，那么：

输入“=CONCATENATE（"本次河流生态调查结果：

"，B2，""，B3，"为"，B7，"条/公里。

"）”计算结果为：

本次河流生态调查结果：

河鳟鱼物种为45条/公里。

3、条件显示

　　我们知道，利用If函数，可以实现按照条件显示。

一个常用的例子，就是教师在统计学生成绩时，希望输入60以下的分数时，能显示为“不及格”；输入60以上的分数时，显示为“及格"。

这样的效果，利用IF函数可以很方便地实现。

假设成绩在A2单元格中，判断结果在A3单元格中。

那么在A3单元格中输入公式：

=if（A2<60，“不及格”，“及格”）同时，在IF函数中还可以嵌套IF函数或其它函数。

　　例如，如果输入：

=if（A2<60，“不及格”，if（A2<=90，“及格”，“优秀"））就把成绩分成了三个等级。

　　如果输入　=if（A2<60，“差"，if（A2<=70，“中”，if（A2<90，“良”，“优”）））就把成绩分为了四个等级。

　　再比如，公式：

=if（SUM（A1：

A5>0，SUM（A1：

A5），0）此式就利用了嵌套函数，意思是，当A1至A5的和大于0时，返回这个值，如果小于0，那么就返回0。

还有一点要提醒你注意：

以上的符号均为半角，而且IF与括号之间也不能有空格。

4、自定义函数

　　虽然Excel中已有大量的内置函数，但有时可能还会碰到一些计算无函数可用的情况。

假如某公司采用一个特殊的数学公式计算产品购买者的折扣，如果有一个函数来计算岂不更方便？

下面就说一下如何创建这样的自定义函数。

　　自定义函数，也叫用户定义函数，是Excel最富有创意和吸引力的功能之一，下面我们在VisualBasic模块中创建一个函数。

在下面的例子中，我们要给每个人的金额乘一个系数，如果是上班时的工作餐，就打六折；如果是加班时的工作餐，就打五折；如果是休息日来就餐，就打九折。

首先打开“工具”菜单，单击“宏”命令中的“VisualBasic编辑器”，进入VisualBasic编辑环境，在“工程－VBAobject”栏中的当前表上单击鼠标右键，选择“插入”-“模块”，在右边栏创建下面的函数rrr，代码如下：

Functionrrr（tatol,rr）Ifrr="上班"Thenrrr=0.6*tatolElseIfrr="加班"Thenrrr=0.5*tatolElseIfrr="休息日"Thenrrr=0.9*tatolEndIfEndFunction。

　　这时关闭编辑器，只要我们在相应的列中输入rrr（F２，B２），那么打完折后的金额就算出来了。

5、矩阵计算

　　Excel的强大计算功能，不但能够进行简单的四则运算，也可以进行数组、矩阵的计算。

（1）数组和矩阵的定义　　

　　矩阵不是一个数，而是一个数组。

在Excel里，数组占用一片单元域，单元域用大括号表示，例如{A1：

C3}，以便和普通单元域A1：

C3相区别。

设置时先选定单元域，同时按Shift＋Ctrl＋Enter键，大括弧即自动产生，数组域得以确认。

　　一个单元格就是一个变量，一片单元域也可以视为一组变量。

为了计算上的方便，一组变量最好给一个数组名。

例如A={A1：

C3}、B={E1:

G3}等。

数组名的设置步骤是：

选定数组域，单击“插入”菜单，选择“名称”项中的“定义”命令，输入数组名，单击“确定”按钮即可。

更简单的命名办法为：

选择数组域，单击名称框，直接输入名称就行了。

　　矩阵函数是Excel进行矩阵计算的专用模块。

用“插入”－“函数”命令打开“粘贴函数”对话框，选中函数分类栏中的“数学与三角函数”，在右边栏常用的矩阵函数有：

MDETERM——计算一个矩阵的行列式；MINVERSE——计算一个矩阵的逆矩阵；MMULT——计算两个矩阵的乘积；SUMPRODUCT——计算所有矩阵对应元素乘积之和。

（2）矩阵的基本计算

数组计算和矩阵计算有很大的区别，比如下面这个例子中，A和B都是定义好的数组，因为这两个数组都是3×3的，输出结果也是3×3个单元格。

计算时先选定矩阵计算结果的输出域，为3×3的单元格区域，然后输入公式。

如果输入“=A＋B”或“=A-B”，计算结果是数组对应项相加或相减，输入“=A*B”表示数组A和B相乘，输入“=A/B”表示数组A除数组B。

如果要进行矩阵计算，就要用到相应的矩阵函数。

矩阵相加、相减与数组的加减表达形式是一样的，也是“=A＋B”和“=A-B”，表示矩阵相乘可以输入“=MMULT（A，B）”，而矩阵相除是矩阵A乘B的逆矩阵，所以计算公式是“=MMULT（A，MINVERSE（B））”。

公式输入后，同时按Shift＋Ctrl＋Enter键得到计算结果。

对于更复杂的矩阵计算，可以采用分步计算。

6、自动切换输入法

在一张工作表中，往往是既有数据，又有文字，这样在输入时就需要来回在中英文之间反复切换输入法，非常麻烦。

如果你要输入的东西很有规律性，比如这一列全是单词，下一列全是汉语解释，你可以用以下方法实现自动切换。

方法是：

（1）选中要输入英文的列，单击“数据”菜单，选择“有效性...”命令，在弹出的“数据有效性”对话框中，选中“输入法模式”选项卡，在“模式”框中选择“关闭（英文模式）”命令，单击“确定”按钮。

（2）选中要输入汉字的列，在“有效数据”对话框中，单击“IME模式”选项卡，在“模式”框中选择“打开”命令，单击“确定”按钮。

这样，当光标在前一列时，可以输入英文，在下一列时，直接可以输入中文，从而实现了中英文输入方式之间的自动切换。

7、批量删除空行

　　有时我们需要删除Excel工作薄中的空行，一般做法是将空行一一找出，然后删除。

如果工作表的行数很多，这样做就非常不方便。

我们可以利用“自动筛选”功能，把空行全部找到，然后一次性删除。

做法：

先在表中插入新的一个空行，然后按下Ctrl+A键，选择整个工作表，用鼠标单击“数据”菜单，选择“筛选”项中的“自动筛选”命令。

这时在每一列的顶部，都出现一个下拉列表框，在典型列的下拉列表框中选择“空白”，直到页面内已看不到数据为止。

　　在所有数据都被选中的情况下，单击“编辑”菜单，选择“删除行”命令，然后按“确定”按钮。

这时所有的空行都已被删去，再单击“数据”菜单，选取“筛选”项中的“自动筛选”命令，工作表中的数据就全恢复了。

插入一个空行是为了避免删除第一行数据。

　　如果想只删除某一列中的空白单元格，而其它列的数据和空白单元格都不受影响，可以先复制此列，把它粘贴到空白工作表上，按上面的方法将空行全部删掉，然后再将此列复制，粘贴到原工作表的相应位置上。

8、宏的应用

　　宏是一个指令集，用来告诉EXCEL来完成用户指定的动作。

宏类似于计算机程序，但是它是完全运行于EXCEL之中的，我们可以使用宏来完成枯燥的、频繁的重复性工作。

宏完成动作的速度比用户自己做要快得多。

例如，我们可以创建一个宏，用来在工作表的每一行上输入一组日期，并在每一单元格内居中对齐日期，然后对此行应用边框格式。

我们还可以创建一个宏，在“页面设置”对话框中指定打印设置并打印文档。

　　由于宏病毒的影响和对编程的畏惧心理，使很多人不敢用“宏”，或是不知道什么时候可以找宏来帮忙。

其实你尽管放心大胆地去用，如果只是用“录制宏”的方法，根本就没有什么难的，只是把一些操作象用录音机一样录下来，到用的时候，只要执行这个宏，系统就会把那操作再执行一遍。

　　下面给出了宏的应用场合，只要用“录制宏”就可以帮你完成任务，而不需要编程。

如果想对所录制的宏再进行编辑，就要有一定的VBA知识了。

　　*设定一个每个工作表中都需要的固定形式的表头；

　　*将单元格设置成一种有自己风格的形式；

　　*每次打印都固定的页面设置；

　　*频繁地或是重复地输入某些固定的内容，比如排好格式的公司地址、人员名单等；

　　*创建格式化表格；

　　*插入工作表或工作薄等。

需要指出的是，EXCEL中的宏与WORD中的宏有些不同之处，对于录制的操作，它会记住单元格的坐标（即所有的引用都是绝对的），所以在涉及到与位置有关的操作时，要格外注意。

如果相用相对引用，可以借助于Offset方法，比如下面的语句：

ActiveCell.Offset（1,0）.range（"A1"）.select宏的应用是很广的，上面提到的只是一点点，如果真的用起来，你会发现它有更丰富的内容和更灵活的应用方法。

9.函数的使用

这一章,我们来学习在Excel中使用公式和函数。

这是相当重要的一部分，在Excel中实现数据计算功能全靠它们了。

我们在Excel中计算一下公式y=（3x+1）/2在x从1变化到20时y的值：

先在第一列中输入好1到20这些数，然后在B1单元格中输入“=（3*A1+1）/2”，将其填充到下面的单元格中，就可以得到直观的结果了；可以很容易看出，这里“*”表示的是乘，“/”表示的就是除，括号的作用和平时是相同的。

创建公式是很容易的，关键在于你要想好怎么去创建这个公式，以及合理的使用单元格的引用。

最常用的函数功能就是求和，Excel中求和功能有很多的用法，最简单的就是自动求和功能了。

我们来看一个表格，现在我们需要把这个货物表的总值部分的全部款项汇总一下，看看到底有多少：

选中这些单元格和下面的“总计”单元格，然后单击工具栏上的“自动求和”按钮，在“总计”栏中就出现了上面单元格的数字的和。

这个自动求和功能可以自动在行或列中求和，对行中数据的求和同对列中数据的求和方法基本一致；但是象下面这个表，要加和的单元格并不是在同一个行或者列上，这时自动求和功能就没有办法了。

这时我们就要用到Excel提供的函数功能来实现了：

单击要填入分数和的单元格，单击工具栏上的“输入公式”按钮，现在左边的名称框变成了一个函数的选择列表框，单击这个列标框的下拉箭头，从中选择“SUM”项。

在编辑栏中就出现了函数SUM，单击“SUM”栏的Number1输入框的拾取按钮，从工作表中选择要将数值相加的单元格，单击输入框中的“返回”按钮回到刚才的对话框，单击“确定”按钮就可以了。

（如下图）如果要求和单元格不连续的话，你也可以用Ctrl键来配合鼠标进行选取。

Excel也有求平均数的函数：

选中要放置平均数的单元格，单击“输入公式”按钮，单击左边的函数选择下拉列表框的下拉箭头，选择平均数函数“AVERAGE”项，然后选择取值的单元格，单击“确定”按钮就可以了。

10、选择性粘贴

Excel为我们提供了一些自动功能。

先来看看选择性粘贴：

这里的选择性粘贴是指把剪贴板中的内容按照一定的规则粘贴到工作表中，而不是像前面那样简单地拷贝。

就拿这个表来说吧。

这里的“利润”一栏是我们使用公式计算得到的，选择这一栏，复制到Sheet2中，可以看到数值并没有跟着复制过来；这时就可以使用选择性粘贴了：

打开“编辑”菜单，单击“选择性粘贴”命令，打开“选择性粘贴”对话框，在“粘贴”一栏中选择“数值”，单击“确定”按钮，数值就可以粘贴过来了。

这种情况不仅是在几个工作表之间复制时会发生，在同一个工作表中进行复制时也会遇到，到时可要注意。

选择性粘贴还有一个很常用的功能就是转置功能。

简单地理解就是把一个横排的表变成竖排的或把一个竖排的表变成横排的：

选择这个表格，复制一下，切换到另一个工作表中，打开“选择性粘贴”对话框，选中“转置”前的复选框，单击“确定”按钮，可以看到行和列的位置相互转换了过来。

另外一些简单的计算也可以用选择性粘贴来完成：

选中这些单元格，复制一下，然后打开“选择性粘贴”对话框，在“运算”一栏选择“加”，单击“确定”按钮，单元格的数值就是原来的两倍了。

此外你还可以粘贴全部格式或部分格式，或只粘贴公式等等。

11、拼写检查

　　Excel还提供了拼写检查的功能，打开“工具”菜单，单击“拼写”命令，Excel就会自动对当前工作表中的内容进行拼写检查，在错误的地方会弹出“拼写检查”对话框，第一行中显示的是Excel认为有错误的单词，下面的“更改为”输入框中显示的是Excel默认的应该替换为的单词，再下面的“建议”列表框中显示的是在词典中与错误的单词比较接近的词。

单击右边的“更改”按钮可以把当前的词替换为“更改为”输入框中的词；然后Excel就会自动搜寻下一个错误的单词，从“建议”列表中选择要替换为的单词，单击“更改”按钮，就可以把单词替换为所选择的词；而选择“忽略”按钮可以忽略此处错误。

12、自动更正

　　现在我们在单元格中输入（c）然后按一下回车，可以看到输入的字符变了。

这是Excel的自动更正功能，我们再输入（r），回车；同样可以看到输入的字符又变了。

其实这些都是可以设置的：

打开“工具”菜单，单击“自动更正”命令，打开“自动更正”对话框，这里列出了所有自动更正的选择，在“键入时自动替换”列表中的头两个就是我们刚才看到的，输入（c）则替换为（c），输入（r）则替换为（r）。

我们还可以把自己容易犯错的词做一个自动更正的词条，以后再输入起来就很方便了：

在“替换”输入框中写入“制做”，在“替换为”输入框中填上“制作”，单击“添加”按钮将其添加到列表中，单击“确定”按钮；在单元格中输入“制做”，回车，可以看到“制做”就被替换成了“制作”。

如果你不想使用哪个自动更正的选项了，就在自动更正对话框中把这个选项去掉就行了。

13、宏的使用

另外还有一个最重要的自动功能，那就是“宏”。

我们每次听到“宏”总是和病毒在一块儿，一般自己做的宏是不会对自己有害的。

合理地使用宏可以大幅度地提高工作效率，而且有些操作不使用宏的话会很麻烦。

最简单的宏可以通过录制来做。

比如我们写一个宏，让它删除A3单元格的内容：

打开“工具”菜单，单击“宏”项，从子菜单中选择“录制新宏”命令，弹出“录制新宏”对话框，在“宏名”输入框中输入宏的名字，单击“确定”按钮，在界面中出现了“停止录制”工具栏，我们就可以进行宏的录制了，在状态栏的最左边可以看到目前的状态是“就绪录制”；单击A3单元格，按一下Delete键，这个宏的操作就算完成了，单击工具栏上的“停止录制”按钮，工具栏自动消失了。

现在在A3单元格中随便填上数值，选择其它的单元格，打开“工具”菜单中的“宏”子菜单，选择“宏”命令，打开“宏”对话框，选择我们刚才录制的宏，单击“执行”按钮，A3单元格的内容就没有了。

现在我们打开一个带有宏的工作簿，Excel会提醒我们打开的文件中带有宏，如果你不能确定宏是否带有恶意的成分就选择“禁用宏”，否则可以选择“启用宏”；在禁用之后即使宏中有恶意成份也不会对你的机器起作用了。

14.补充部分

记忆式键入

在Excel的单元格中键入文本时，Excel会将扫描同一列中的所有条目并将列中与所输入的文字相匹配的条目显示在单元格中，这就是记忆式键入功能；此时直接按回车键就可以接受所匹配的条目。

“记忆式键入”仅匹配完整的单元格条目，而不是单元格中单个的词。

当编辑公式时，“记忆式键入”不工作。

选择“工具”菜单的“选项”命令，打开“选项”对话框，单击“编辑”选项卡，清除“记忆式键入”复选框就可以禁用“记忆式键入”功能。

选择列表

在单元格上单击右键，可以看到“选择列表”命令，这个命令在“记忆式键入”功能打开时是可见的，单击它就可以显示同一列上邻近单元格中的所有唯一条目的列表。

选择列表中的某个条目就可以把它插入所选的单元格中。

默认文件夹

在使用打开或保存命令时会发现Excel自动把“我的文档”作为默认的保存和打开文件夹，如果我们平时的工作成果并不在这个文件夹中，这样是很不方便的，不过我们可以设置这个默认的文件夹：

打开“工具”菜单，选择“选项”命令，打开“选项”对话框；单击“常规”选项卡；在“默认工作目录”输入框中输入文件夹的路径名，然后单击“确定”按钮就可以了。

默认字体

在默认情况下，Excel工作表使用10磅的Arial字体；我们也可以将这个默认的设置改变：

选择“工具”菜单的“选项”命令，打开“选项”对话框，从“常规”选项卡的“标准字体”下拉列表框中选择一种字体，从“大小”下拉列表框中选择字体的大小，单击“确定”按钮；Excel会弹出对话框提示我们要重新启动Excel，重新启动Excel后Excel就会以设置的字体显示了。

单变量求解（数据分析）　

用Excel可以进行比较复杂的数值计算，比如算式z=3x+4y+1，我们要求当z=20、y=2时x的值，就可以使用单变量求解功能：

首先按一般的样子将公式建立起来，然后打开“工具”菜单，单击“单变量求解”命令，打开“单变量求解”对话框，拾取“目标”为公式所在的单元格，在“目标值”输入框中输入期望的值20，然后将“可变单元格”定位为x的数值所在单元格，单击“确定”按钮，在单元格中可以看到计算的结果；同时界面中出现了“单元格求解状态”对话框，此时单击“确定”可以接受通过计算导致单元格数值的改变，而单击“取消”按钮就可以撤消改变了。

模拟运算表（数据分析）

Excel作为一个电子表格其作用不仅仅是数据的电子化存储及排序和检索，它还有另外一项很重要的功能，那就是数据分析功能，这里用得最多的就是模拟运算表：

用一个简单的算式z=3x+4y+1来看：

要求当x等于从1到4间的所有整数，而y为1到7间所有整数时所有z的值，用模拟运算表做：

首先排好x与y的位置，然后在下面的单元格中建立一个公式，在公式所在行的右边和下面分别输入两个变量的变化值，这里我们在行上为x，列上为y，然后选中这个方形的区域，选择“数据”菜单中的“模拟运算表”命令，打开“模拟运算表”对话框，将“输入引用行的单元格”选择为公式中x的数值所在单元格，“输入引用列的单元格”选择为公式中y的数值所在的单元格。

单击“确定”按钮，就可以看到运算的结果了。

如果公式发生了改变，只要修改公式单元格中的公式就可以了。

补充部分

批注的使用

对于已经插入的批注，我们可以把它们都显示出来以方便查看和编辑：

打开“视图”菜单，单击“批注”命令，就可以将工作表中的所有批注都显示出来；将鼠标移动到批注所在的位置，鼠标变成了“I”形，在批注的区域内单击，可以看到批注区域内出现了闪动的光标，此时就可以对批注进行编辑了；单击工作表的任意其它位置，可以取消对批注的编辑；打开“视图”菜单，单击“批注”命令，在视图中就看不到批注了。

我们还可以使用“审阅”工具栏来进行对批注的操作：

用右键单击工具栏，单击快捷菜单中的“审阅”命令，界面中就出现了“审阅”工具栏，单击工具栏中的“显示所有批注”按钮，可以显示工作表中的所有批注，再单击这