Excel制表.docx

Excel制表.docx

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

Excel制表

Excel制表技巧（21）单元格B

      1.对单元格进行同增同减操作   

      例如：

想让选定区域中的每个单元格都同乘以2，可以这样操作，首先在选定区域外的某个单元格中输入2，选择这个单元格并复制，如图1所示。

                                                            图1 选定并复制单元格

       然后选择要进行同乘操作的单元格区域，选择好后，单击“编辑”菜单，选择“选择性粘贴”命令，打开“选择性粘贴”对话框，在“运算”栏中选择“乘”单选项，单击“确定”按钮，对话框中的设置如图2所示。

图2 “选择性粘贴”对话框

       选择区域中的每个单元格就都进行了乘以2的操作，而且已经将结果显示在了每个单元格中，其余操作类似。

    小编提示：

对于这个操作一定要注意操作顺序：

选定并复制要乘的数所在的单元格，接下来选中要进行运算的单元格区域，然后再执行“编辑”—“选择性粘贴”命令。

       在Excel中，如果要对某一单元格或某一区域中的每个单元格中的数值进行同加、同减、同乘或同除操作时，可以使用“选择性粘贴”功能轻松实现。

       使用这个功能还可以对单元格进行自身操作，例如可以进行单元格的自身相加、相减、相乘以及相除操作。

操作步骤如下：

选择要进行自身操作的单元格复制，如图3所示。

                                                                        图3 选择并复制数据区域

        然后单击“编辑”—“选择性粘贴”命令，打开“选择性粘贴”对话框，在“运算”栏中选择相应的运算操作，单击“确定”按钮，就可以实现单元格自身的操作。

       2.在已有数据的单元格中批量加入固定字符

       如果数据已经录入，需要在某些数据前面或后面再加入固定字符该怎么操作呢？

       例如：

要将B列中每个单元格中的数据后面都加上固定数字“0000”，可以进行操作：

选中C列，单击鼠标右键，从快捷菜单中选择“插入”命令，在B列后插入一空白列，如图4所示。

图4 插入空白列

        选中C3单元格，在公式栏中输入“＝B3&"0000"”，单击确认按钮，这个单元格中的数字就改变了，如图5所示。

（注意：

在编辑栏中输入的引号一定要是英文半角引号）

图5 添加固定内容

        然后使用自动填充功能，拖动自动填充柄就可以将一列中的数据都改变了。

       3.解决在单元格中不能输入小数点的问题

      不知你是否遇到过这种问题，在单元格中输入小数点时，它总是会自动变为逗号，这是怎么回事呢？

打开“控制面板”，选择其中的“区域和语言选项”项，打开“区域和语言选项”对话框，在“区域选项”选项卡中单击“定义”按钮，打开“自定义区域选项”对话框，看一下“小数点”项中的内容，如果这里显示的是“，”，那么问题就出在这里了，将它改为“.”，问题就解决了。

“自定义区域选项”对话框如图6所示。

图6 “自定义区域选项”对话框

       4.快速选中包含数据的所有单元格

       大家都知道选中整个工作表有很多种方法，如按“Ctrl+A”组合键、单击全选按钮等，可有时需要选中所有包含数据内容的单元格区域，该怎么操作呢？

可以这样操作：

首先选中一个包含数据的单元格，然后按下“Ctrl+Shift+*”组合键，就可以把所有包含数据的单元格选中。

效果如图7所示。

图7 选中包含数据的所有单元格

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

    小编提示：

本技巧仅适合于工作表中的数据是连续的情况。

        Excel改变单元格批注的形状添加图片

        默认情况下，在Excel中加入的批注通常（如图1）所示，就是一个矩形的文本框。

想不想改变一下批注的外观呢？

比如：

可以改为像（如图2）所示的效果。

图1 Excel中的默认批注样式

图2 更改批注格式

        操作步骤很简单哦，方法如下：

首先，选择“插入”—“批注”命令，给单元格添加批注。

然后，右击单元格，从快捷菜单中选择“显示/隐藏批注”命令，将批注显示出来。

确认“绘图”工具栏出现在屏幕中。

按住Ctrl键，单击选中批注。

从“绘图”工具栏中选择“绘图”—“改变自选图形”命令（参见图3），然后再从其子菜单中选择想要设置的形状即可。

图3 选择自选图形

        上面一个技巧我们讲解了如何更改批注的形状，接着我们为大家介绍如何在批注中添加图片。

首先按照上面讲解的方法将批注显示出来，，然后双击批注的边框，可以打开“设置批注格式”对话框，选择“颜色和线条”选项卡，从“填充”栏的“颜色”项的下拉列表中选择“填充效果”项，（如图4）所示。

                                                             图4 选择“填充效果”项

       随后可以打开“填充效果”对话框，选择“图片”选项卡，单击其中的“选择图片”按钮，选择要放在批注中的图片，然后连续单击“确定”按钮，回到Excel编辑状态，就可以看到选择的图片已经添加到批注中了，效果（如图5）所示。

图5 在批注中添加图片

       改变Excel工作簿默认单元格批注格式

　　如果您的Excel工作簿中运用了一些公式，又怕别人看不懂，可以在存在公式的单元格中插入批注，对公式进行必要的解释。

批注是一种十分有用的提醒方式，不仅可以用来解释复杂的公式，也可以将对其它用户工作簿的反馈意见写在批注当中以方便互相交流。

　　在Excel中，我们可以先选中要插入批注的单元格，然后选择“插入>批注”命令，这时就会在该单元格一侧出现批注框，可以直接在框中输入批注的内容，如图1所示。

　　图1 插入批注

　　创建了批注之后，可以随时改变批注的格式，方法是在包含批注的单元格（右上方有红色小三角的单元格）上单击右键，从弹出菜单中选择“编辑批注”，然后在批注框中选中要改变格式的内容，改变其格式，或者单击批注框的边缘选中整个批注，再改变其格式，例如改变字体、字号、文本颜色等。

还有一种办法是在批注上单击右键，从弹出菜单中选择“设置批注格式”，打开如图2所示的对话框然后进行设置。

　　图2 设置批注格式

　　然而如果经常会用到单元格批注，又不希望使用默认的单元格批注格式，总不能一次次地手动更改吧。

在Excel中没有提供改变默认单元格批注格式的途径，但有一种办法可以解决。

其实，Excel中的单元格批注的格式与Windows系统的工具提示格式是完全相同的，了解了这一点，就好办了。

　　什么是“Windows系统的工具提示”呢？

例如在Excel中，当我们将鼠标指针指向工具栏上的“保存”按钮上方时，就会出现如图3所示的黄底黑字的小提示框，告诉我们这是一个“保存”按钮，这就是工具提示。

　　图3 黄底黑字的小提示框

　　要改变工具提示的默认格式，需要打开“显示属性”对话框。

可以双击“控制面板”中的“显示”将其打开，如图4所示。

也可以在桌面上单击右键，然后从弹出菜单中选择“属性”将其打开。

　　图4 控制面板

　　以Windows XP系统为例，当打开“显示属性”对话框后，单击上方的“外观”选项卡，然后单击右下方的“高级”按钮，如图5所示。

　　图5 显示属性

　　在“高级外观”对话框中，在“项目”下方选择“工具提示”，然后就可以设置提示内容的字体、字号、文本颜色、粗体斜体以及提示框的背景颜色等属性了。

　　图6 外观

　　注意：

这一改变将会影响所有没有设置格式的批注，而对已经设置了格式的批注则没有影响。

如果您已经为某些批注应用了其它格式，例如，改变了文本的颜色，上述改变将不会改变这些批注的格式。

      Excel XP“单元格”合并与拆分的技巧

　　所谓合并数据是指将两列中的数据合并到一列中，拆分数据是指将一列中的数据拆分到两列中，图1为“毕业院校”和“专业”两列的拆分及合并的效果图。

　　合并数据

　　要将“毕业院校”和“专业”两列中数据合并为“毕业院校及专业”，有“真”、“假”两种合并方法，所谓真合并就是将两列数据真正合并到一列中，而假合并表面上看是合并为一列了，实际数据还是存在于两列中，具体操作跟我一起来看看吧。

　　1、真合并

　　如果将一列中的数据通过再输入合并到另一列中，那不是我们希望看到的解决办法，快捷而准确的操作方法是：

（1）将需要合并的“毕业院校”和“专业”两列数据通过“剪切”、“粘贴”移动到一个新工作表中，如命名为“合并”工作表。

（2）在“合并”工作表中将XLS文件另存为TXT文件，单击“文件→另存为”，保存类型选择“文本文件（制表符分隔）”，单击[保存]按钮后根据提示连续单击两次[确定]按钮即可。

　　（3）在Execl中单击[打开]按钮，重新打开TXT文件，在“文本导入向导→步骤之1”中采用默认设置，单击[下一步]按钮，在“步骤之2”分隔符号中，去掉“Tab键”前复选框的选择，单击[完成]按钮，这时两列中数据合并到一列中。

图1

　　（4）将合并后的数据再通过“剪切”、“粘贴”操作移动到原工作表的原位置处，合并操作完成。

　　提示：

合并后的数据在原来两列数据（如毕业院校和专业）之间会有一个空格，而有时根据实际需要去掉或加宽空格，可采取如下措施：

　　方法一：

对TXT文件进行编辑

　　XLS文件中列与列之间数据转换为TXT文件后是以制表符分隔的，此时合并后的数据在原来两列数据之间会有一个空格，改变TXT文件中两列之间距离（即改变空格的大小），对应的XLS文件中原两列数据之间的距离也会随之发生改变，若TXT文件中两列数据之间距离增大或减小为零，则合并的XLS文件中两列数据之间距离也会随之增大或减小为零。

如果你要将两列数据无缝合并，建议应用下面两种方法。

　　方法二：

利用函数ConcateName

中-5-31 15:

43:

04

图2

　　ConcateName函数可以实现将几个文本字符串合并为一个文本字符串，语法为：

ConcateName （text1,text2,……），text1, text2,……为1到30个将要合并成单个文本项的文本项，这些文本项可以为文本字符串、数字或对单个单元格的引用，如图2所示。

　　方法三：

利用“&”运算符

　　利用“&”运算符也可将几个文本字符串合并为一个文本字符串，“&”运算方式同“+”、“-”等运算方式一样，如图2所示。

　　2、假合并

　　我们也来一个“瞒天过海”吧，呵呵，在对单元格设置边框时，利用工具栏中的“外部框线”将两个单元格框在一起，这样两个单元格中数据就在一个表格中了，打印出来后保证不留一点“作假”痕迹。

此招对付较少单元格效果不错，要应付大量的单元格，奉劝你还是来点“真”的吧！

否则够你忙的了。

　　拆分数据

　　合并方法掌握了，拆分也不难，拆分的方法与前面介绍的合并方法相似，先将XLS文件另存为TXT文件，再对TXT文件进行编辑，将需要拆分的数据用“Tab”键或空格分开，然后再用Excel重新打开编辑后的TXT文件，这样原来在一起的数据就被拆分开了。

        在Excel单元格中设置“斜线”效果

　　表格中出现斜线是经常的事（如图1），以前遇到这种情况，要么把表格导入到WPS Office或Word中处理，要么就是打印好了之后再用笔加上去。

经过一番摸索，终于发现了在Excel中设置斜线的方法。

图1

图2

　　1、实现在单元格中分行的效果：

我们首先在单元格中输入“性别”，这时候按回车键的话，光标就会转到其他单元格中去了，所以必须按住“Alt”键的同时按回车键，光标转到下一行，接着输入“姓名”，然后再在“性别”前加入若干空格键，即可达到如图2的效果。

图3

　　2、在单元格中设置斜线：

选择单元格，在右键菜单中选择“设置单元格格式”，在出现的对话框中选择“边框”标签，进行斜线的设置（如图3）。

         让数据与EXCEL的单元格相匹配 

　　从所周知，Excel单元格的宽度并非固定不变的，它可根据用户的需要任意进行调整，如某个单元格中的内容较多以至于无法完全显示出来时，我们通常就会拓宽该单元格的宽度，以便使其能完整地显示单元格中的所有内容。

当然，有时也会出现一些相反的情况，如用户有时可能因为某些特殊原因而希望将单元格的宽度保持不变，此时为了使单元格中所有的内容都能显示出来，就必须缩小单元格内文字的字号，这通常是一件十分令人头痛的事情��为达到即可显示出单元格内所有的内容，又能尽可能地利用单元格的宽度的目的，我们必须对各种字号逐一进行测试，以选择最合适的字号大小，此举麻烦不说，还很难达到尽善尽美的地步。

不过Excel 97用户大可不必这么麻烦，Excel 97具备有“缩小字体填充”功能，它可自动让单元格内的数据与单元格的宽度相匹配，我们只需利用这一功能即可实现由系统自动调节单元格内的字号大小、并实现与单元格的宽度相适应的目的，十分方便。

为利用Excel 97的这一功能，我们可执行如下步骤：

     1 将希望具备有该功能的单元格定义为块。

     2 执行Excel“格式”菜单的“单元格”命令，系统弹出“单元格”对话框。

     3 在“单元格”对话框中选择“对齐”选项卡。

     4 复选“文体控制”框中的“缩小字体填充”选项。

    5 单击“确定”按钮。

Excel就会自动缩小它们的字号，使它们能恰好在该单元格内显示下为止。

这样就实现了即可显示所有数据，又可充分利用单元格已有宽度的目的，达到了两全其美，效果非常好。

另外，我们在使用此功能时还应注意以下几点：

Excel工作簿中各个单元格内字号的大小可能就不完全一样了，有些内容较少的单元格中的字号可能要大一些，而另外一些内容较多的单元格中的字号可能要小一些，这正是Excel自动对字号大小进行了调节的表现。

广大用户若不喜欢这种字号不统一的形式，则必须禁止Excel的“缩小字体填充”功能，当然，此后拓宽调节单元格宽度也就成了我们解决此类问题的唯一办法了。

（Excel自动将字号缩小，此后又自动恢复到原有大小除外），这点务必引起广大用户的注意.

　　这样，被选定单元格即具备了自动调节字号大小的能力，当这些单元格内的数据较多、采用原有字号无法完整地进行显示时，

       巧用快捷键轻松设置Eexel单元格格式

       Ctrl + Shift + ~：

应用“常规”数字格式

　　“常规”格式不包含特定的数字格式，相当于在“单元格格式”对话框中的“数字”选项卡中选择“常规”。

比如原来的数字格式为“货币”或“百分比”形式，使用该快捷键可以将这些格式清除掉，恢复到常规的格式。

　　Ctrl + Shift + $：

应用带两个小数位的“货币”数字格式，（负数在括号中）

　　Ctrl + Shift + %：

应用不带小数位的“百分比”格式

　　Ctrl + Shift + ^：

应用带两位小数位的“科学记数”数字格式

　　Ctrl + Shift + !

：

应用带两位小数位、使用千位分隔符且负数用负号 （-） 表示的“数字”格式

　　Ctrl + Shift + &：

对选定单元格应用外边框

　　Ctrl + Shift + _：

取消选定单元格的外边框

　　Ctrl + B：

应用或取消加粗格式

　　Ctrl + I：

应用或取消字体倾斜格式

　　Ctrl + U：

应用或取消下划线

　　Ctrl + 5：

应用或取消删除线

　　Ctrl + 1：

显示“单元格格式”对话框

　　Ctrl + 0：

隐藏单元格所在列

　　Ctrl + 9：

隐藏单元格所在行

　　Ctrl + -：

删除选定的单元格，会弹出一个对话框供选择

　　 巧妙切换固定区域内的单元格 

      学生们放假了，可是作为教师却不能马上放假，我们还需要用Excel录入学生成绩并进行处理，但在Excel表格中输入完一个数据后按回车键，光标会自动跳转到下一行同列的单元格中，若要输入其它列的数据，只能手动将光标移至其它列的单元格中。

如果想在同一行中连续录入数据，当光标到了表格中一行末尾的单元格时，按回车键能自动跳转到下一行需要输入数据的起始单元格中，这样不就能大大地提高数据录入的效率了吗？

很多朋友可能想这需要通过加载宏或用VBA编程来实现吧，其实通过简单的设置就可以实现这一操作了。

下面来看看吧。

       通过设置，我们就可以实现在一行中连续录入数据了。

接下来来看看如何才能使按回车键，光标自动跳转到下一行需要输入数据的起始单元格中。

我们以录入某班学生成绩为例，

（如图2）

选中成绩统计表中需要录入数据的单元格区域，其中“总分”和“均分”列用公式计算，不需要手工录入，因此不需要选中，此时就可以向成绩统计表中录入数据了，当光标到了J6单元格时按回车键，此时光标就自动跳转到C7单元格中了。

在录入过程中，按“Shift+Tab”组合键或向左的方向键“←”可以实现光标反向移动，便于修改录入错误的数据。

通过上面简单的设置，我们在用Excel录入大量数据时就不需要使用鼠标或方向键了，从而能大大地提高我们数据录入的效率，怎么样，非常简单吧，还不赶快去试一试。

首先，设置如何才能使按回车键时光标在同一行中移动。

方法是单击“工具”菜单中的“选项”命令，从弹出的“选项”对话框中单击“编辑”选项卡，在“按Enter键后移动”右侧的“方向”下拉列表中选择“向右”即可。

（如图1）

       如何在一个单元格中输入多行数据

　　有时，我们经常需要在一个Excel单元格中输入多行数据。

您可不要以为这个问题很简单，其实，当我们准备在单元格中换行时，只要按下回车键Excel便肯定转到下一个单元格了。

那么如何做才能在同一个单元格中输入多行数据呢？

笔者这里就给大家介绍两个小技巧。

　　【注】 本文所述技巧已于Excel 2003下测试通过 

　　方法一. 临时设置法 

　　如果我们只是临时需要在一个单元格中输入多行数据，那么就可以在输入完单元格内容的第一行后，按住Alt键再打回车，这时，继续输入的字符便自动出现在了当前单元格的下方了。

　　方法二. 批量设置法 

　　如果我们需要在大批单元格中都输入多行数据时，上例这样的方法就显得比较麻烦了。

其实，只要我们事先对这些单元格做一下统一设置，那么在这些单元格中就可以直接输出多行数据了。

具体设置方法如下：

　　1. 选中需要设置自动换行的单元格。

　　2. 右击这些单元格，执行“设置单元格格式”命令，并在“对齐”标签下勾选“自动换行”复选框。

　　3. 这时当我们再输入一些超长字符时，超长部分便被Excel自动分配到下一行了。

       格式的应用

    快速套用格式

    如果想将Excel XP工作表中的格式套用到其他位置，除使用“格式刷”外，“选择性粘贴”在特定场合下更加快捷。

操作方法是：

选中作为样本的单元格或区域，按Ctrl+C键将其放入剪贴板。

然后按下Ctrl键选中要套用格式的全部单元格，打开“选择性粘贴”对话框，选中“粘贴”下的“格式”并“确定”，则所菜单元格就会使用你复制的格式。

    样式复制技巧

    Excel允许用户将某个工作簿的样式复制到其他工作簿使用，从而扩大样式应用范围，打开包含样式的源工作簿和目标工作簿，单击“格式”→“样式”打开对话框。

单击其中的“合并”按钮打开“合并样式”对话框，在“合并样式来源”框中选择包含待复制样式的工作簿并“确定”，源工作簿中包含的样式就会复制到目标工作簿中。

    快速设置字型号、字体

    按下Ctrl+Shift+P组合键，则可快速定位到字型号设置窗口。

按下Ctrl+Shift+F组合键，则可快速定位到字体设置窗口。

    重复使用格式刷

    Excel中单击“格式刷”按钮时，可以将选定单元格的格式应用到其余的单元格中，但每一次只能应用一次“格式刷”，如需要重复使用，只要双击“格式刷”按钮，然后单击要改变其格式的多个单元格。

完成操作后，按Esc键或再次单击“格式刷”按钮，就可以关闭“格式刷”。

    样式功能快速定义工作簿格式

    选定需要定义格式的工作簿范围。

执行“格式”→“样式”，打开“样式”对话框，用户可以从“样式名”列表框中选择合适的“样式”种类，也可以从“样式包括”列表框中选择是否使用该种样式的数字、字体、对齐、边框、图案、保护等格式内容。

最后按下“确定”按钮，工作簿的格式就会按照用户指定的样式发生变化了。

    增加新的样式或修改已有样式的内容

    选择“格式”→“样式”，在“样式名”列表框输入新建样式的名称（或者选中已有的样式名）， 单击“更改”按钮，就会打开“单元格格式”对话框，在对该种样式的各项具体格式进行适当调整后，连续单击两次“确定”按钮就可以关闭所有对话框。

这样就完成了新增（或修改）指定样式具体内容的任务。

    自定义数据格式

    选中要设置自定义格式的单元格或区域，然后单击“分类”下的“自定义”，即可在下面的列表框内选择现有的数据格式，或者在“类型”框中输入你定义的一个数据格式（原有的自定义格式不会丢失）即可。

在后一种情况下，只要自行定义的数据格式被使用，它就会自动进入列表框像内置格式那样调用。

    使用格式表达式

    Excel的自定义格式很像一种数学公式，提供了正数、负数、零和文本四种基本的格式代码，这些格式代码（称为“节”）是以分号来分隔的。

其中正数的格式代码为“#，##0.00”，其中“#”表示只显示有意义的零（其他数字原样显示），逗号为千分位分隔符，“0”表示按照输入结果显示零，其中“0.00”小数点后的零的个数表示小数位数；负数的格式代码为“[Red]-#，##0.00”。

其中“-”表示负数，可选项[Red]定义负数的颜色，可以输入“黑色”、“蓝色”、“青色”、“绿色”、“洋红”、“红色”、“白色”或“黄色”，其他字符的意义和正数相同；零的格式代码为“0.00”，其中小数点后面的“0”的个数表示小数位数；文本的格式代码为“"TEXT"@”（或“@"TEXT"”），TEXT是要在输入文本的前面或后面显示的字符串，@代表输入单元格的文本，它的位置决定TEXT在输入文本前面还是后面显示。

    上述四种格式代码可以单独使用，也可以联合起来使用。

例如格式为“###0.0”的单元格内输入“12345.987”会显示为“12346.0”；而“"盈""利"###0.00"元";"亏""损"-###0.00"元"”则会输入“3689.28”会显示“盈利3689.28元”，而输入“-3689.28”则显示“亏损-3689.28元”。

    小提示

    代码前后添加的文本可以在类型框中直接输入，需要的双引号Excel能够自动添加。

四种格式代码联合使用时，代码应按正数、