Excel使用技巧

第15章 excel使用技巧Excel中最基本的操作就是向工作表中输入数据，本章介绍典型的数据输入方式及技巧。通过本章的学习，可以为进一步应用Excel 2003的强大功能打下良好的基础。15.1 单元格数据的输入15.1.1 单元格的表示方法单元格的行以字母表示，列以数字表示，A1单元格就是第一行第一列的单元格。当选择一个单元格时，单元格区域左上角是输入栏中会显示单元格的名称，并且被选择的单元格行和列名称栏都显示为黄色，如图15-1所示。图15-1 单元格15.1.2 输入和设置数据单击单元格就可以直接在单元格中输入文字、数字和特殊符号等，按Enter键即可完成输入并且光标移动到下面的单元格中。在单元格中输入 “2008/10/1”，按Enter键后数据自动会变成时间形式。右击单元格，在弹出的快捷菜单中执行【设置单元格格式】命令，弹出如图15-2所示的窗口。在【分类】列表框中选择分类，在【类型】列表框中选择具体的样式，这样就完成了数据的格式设置，图中为设置日期的不同格式。“数值”：可以设置小数点的位数和正负号的使用规则。“特殊”：用于设置数字不同形式的中文大写方式。其他选项比较简单，这里就不详细介绍了。图15-2 【单元格格式】对话框15.1.3 如何快速输入有规律的数据一些有规律的序号或者其他数据，可以采用填充的方法获得。如图15-3所示，在相邻的单元格输入并选择两个数字，此时出现+号标志，单击并拖动鼠标，即可得到顺次增加的数据组，并出现图标，如图15-4所示。单击图标，弹出如图15-5所示的快捷菜单，选择【复制单元格】单选按钮，即可得到如图15-6所示的结果。一般文字等没有数字变化规律的，都使用复制填充方式。 图15-3 选择数据 图15-4 生成数据组 图15-5 复制单元格 图15-6 生成的表格效果图在菜单栏中执行【编辑】|【填充】|【序列】命令，在弹出如图15-7所示的对话框中可以设置不同类别的序列方式。图15-7 【序列】对话框15.1.4 怎样输入身份证号码在Excel中，输入超过12位的数值，系统自动将其转换为科学记数格式，如果输入超过15位的数值，系统自动将15位以后的数值转换为“0”。（1）选中需要输入身份证号码的单元格。选择【格式】|【单元格】命令。在弹出的对话框中切换到【数字】选项卡，在【分类】列表框中选择【文本】选项。单击【确定】按钮。然后再输入身份证号码就可以了。15.1.5 设置对齐方式与自动换行在单元格中输入数据时，如果超出了单元格，除了调整单元格的列宽以外，还可以将单元格设置为自动换行，这样数据会依据单元格的大小来分配数据的行数。具体操作过程如图15-8所示。在单元格中输入数字，选择【格式】|【单元格】命令。在弹出的对话框中切换到【对齐】选项卡，即可以设置文字在单元格中的对齐方式和自动换行等，通过调整右面的指针可以调整文本在单元格中的对齐方向，如图15-9调整为-45时，得到如图15-10所示的结果。【缩小字体填充】用于不调整单元格尺寸的情况下将文字自动缩小到单元格中。 图15-8 设置自动换行 图15-9 设置对齐方式 图15-10 文字对齐效果15.1.6 设置单元格的数据范围、错误警报和输入提示如果要输入的数据有一定的范围和规律，可以对输入数字的类型、长度和范围等进行限制，以免输入错误。选中要输入数据的单元格，选择【数据】|【有效性】命令。在弹出的对话框中切换到【设置】选项卡，在【允许】下拉列表中选择数据类型。在【数据】下拉列表中选择设置数据范围的方式。设置具体范围后。单击【确定】按钮，如图15-11所示。在经过设置的单元格中输入的数据不符合要求时，Excel将发出错误提示。如图15-12所示。 图15-11 【数据有效性】对话框 图15-12 报错信息切换到【输入信息】选项卡，选择【选定单元格时显示输入信息】复选框。在【标题】文本框和【输入信息】文本框中输入需要显示的信息。单击【确定】按钮，如图15-13所示。单元格被选中后将会显示提示信息，如图15-14所示。 图15-13 设置【输入信息】选项卡 图15-14 提示信息15.2 单元格的修改与设置15.2.1 插入单元格、行、列这是非常简单的操作，用法和Word表格类似，选择单元格，然后在【插入】菜单中执行对应的命令就可以在该位置上插入单元格、行或者列，在插入单元格时会弹出如图所示的对话框，可以设置其他单元格的移动方向或者是插入一行或者一列。如图15-15所示。图15-15 【插入】对话框15.2.2 怎样设定单元格的底色单元格的底色默认为白色，也可以将其设定为其他颜色，这样容易分辨不同的单元格，选择想要设定底色的单元格，单击【格式】工具栏的【填充颜色】的下三角按钮。15.2.3 设置单元格的行高与列宽行高和列宽的调整方法相同，下面以列宽为例，讲述通常的几种方法。（1）在列名称栏，光标编程双箭头方式，如图15-16所示，即可左右调整列的宽度。（2）在双箭头方式下双击，即可根据单元格的内容调整列宽，列中多个单元格中有数据，则以最宽的作为标准调整。（3）在菜单栏中执行【格式】|【列】|【列宽】命令，弹出如图15-17所示的对话框，可以直接设置列的宽度。 图15-16 调整单元格列宽 图15-17 设置列宽15.2.4 怎样隐藏和恢复列与行利用Excel的隐藏功能可以将工作表中的行或者列隐藏起来，二者设置基本相同，下面以介绍一下隐藏和恢复隐藏列的操作。右击列名称选择该列并弹出如图15-18所示的快捷菜单，执行【隐藏】命令即可。隐藏效果如图15-19所示。单击并拖动鼠标，选择被隐藏列两边的列，右击，在弹出的快捷菜单中执行【取消隐藏】命令，即可显示被隐藏的列。 图15-18 选择【隐藏】命令 图15-19 隐藏效果15.2.5 怎样绘制边框在默认情况下打印Excel表格是不会打印边框的，如果打印时希望有边框，就要在打印前绘制边框。设置边框有如下两种方法。（1）单击工具栏上的【边框】按钮的下三角按钮，如图15-20所示，可以直接设置单元格边框的形式，单击【绘图边框】命令，可以直接手绘边框。（2）右击要设置边框的单元格，在弹出的快捷菜单中执行【设置单元格格式】命令。在弹出如图15-21所示的对话框中选择【边框】选项卡，可以设置边框的和线条的样式。 图15-20 选择边框或绘图边框 图15-21 设置【边框】选项卡15.2.6 工作表的基本操作默认一个工作簿有3个工作表，如图15-22所示，单击标签即可在不同工作表之间进行切换。右击标签弹出如图15-23所示快捷菜单，可以进行插入、删除、重命名和设置标签颜色等操作。在【格式】菜单中选择【工作表】子菜单，可以进行隐藏和取消隐藏等操作。 图15-22 默认工作表 图15-23 修改工作表标签菜单15.3 数据处理技巧15.3.1 排序的问题如果希望将工作表中的一行或者一列数据按照一定的规律重新排序，可以单击工具栏中的【降序排序】按钮（或【升序排序】按钮），直接进行排序。当多列进行排序时，要选择要排列的全部数据，执行【数据】|【排序】命令，会弹出如图15-24所示的对话框，最多可以按照3列来排列数据。首先按照“主要关键字”进行排列，当主要关键字相同时，再按照“次要关键字”排列。单击【选项】按钮，会弹出如图15-25所示的对话框，可以设置排序的标准和形式。 图15-24【排序】对话框 图15-25 【排序选项】对话框需要说明的是，如果要多列不连续的数据进行排序，如图15-26所示，如果不全部选择数据后进行排序，则另外一列数据不改变原来的位置，容易出现问题。图15-26 不连续数据排序15.3.2 导入外部数据的方法有些数据是通过其他软件或者方式获得的文本或者其他格式，可以直接导入。方法是在菜单栏中执行【数据】|【导入外部数据】|【导入数据】命令，在弹出的对话框中选择要导入的文件，得到如图15-27所示的结果。根据数据情况，选择区分数据的方式，单击【下一步】按钮，得到如图15-28所示的结果，选择对应的分割符号将数据按照行列的形式分离，得到如图15-29所示的结果，单击【下一步】按钮，直至操作结束，即可完成数据的导入工作。图15-27 选择要导入的文件 图15-28 选择数据区分方式 图15-29 选择对应的分割符号15.3.3 筛选数据利用excel可以在工作表中以一定的条件显示数据，这种功能叫做筛选。（1）如图15-30所示选择图表第一行中的标题。（2）执行【数据】|【筛选】|【自动筛选】命令，得到如图15-31所示的结果。（3）单击下拉按钮，如图15-32所示，可以选择数据的排列方式和显示数量，单击【自定义】选项，弹出如图15-33所示的对话框，通过改对话框可以设置显示数据的条件。 图15-30 选择标题 图15-31 自动筛选后的效果图 图15-32 下拉菜单图15-33 【自定义自动筛选方式】对话框15.3.4 怎样创建图表创建图表要以工作表中的数据为基础。（1）选择【插入】|【图表】命令，或者单击工具栏中的图标向导按钮。在弹出的对话框中单击【标准类型】标签，在【图表类型】列表中选择图表类型，在【子图表类型】列表中进一步选择具体的图表类型。单击【下一步】按钮。如图15-34所示。（2）单击【数据区域】标签，单击【数据区域】文本框右侧按钮。如图15-35所示。 图15-34 选择图表类型 图15-35选择图表源数据（3）在工作表中选择制作图表的数据范围，单击图表向导对话框右侧按钮返回源数据对话框，如图15-36所示。得到如图15-37所示的结果。 图15-36 选择生成图表数据区域 图15-37 【图表源数据】对话框（4）单击【系列】标签，得到如图15-38所示的结果，通过这个对话框可以设置产生图表的x轴和y轴数据。在【系列】选型表中单击添加可以在图表中增加新的数据图例。（5）单击【下一步】按钮，进入如图15-39所示的对话框中。【标题】选项卡可以设置图表坐标轴和标题文字。【坐标轴】选项卡可以设置是否使用坐标轴。【网格线】选项卡可以设置显示坐标刻度线的情况。【图例】选项卡用于设置图例的显示位置。【数据标志】选项卡用于设置在图表中显示数值的形式。【数据表】选项卡用于设置在图表中显示绘制图表的原始数据。 图15-38 【系列】选项卡 图15-39【图表选项】对话框（6）单击【完成】按钮，完成图表的绘制，如图15-40所示。（7）右击图表，弹出如图15-41所示的对话框，选择对应的选项可以更改绘制好的图表。 图15-40 生成的图表 图15-41 更改图表菜单（8）右击坐标轴，在弹出如图15-42所示的快捷菜单中执行【坐标轴格式】命令，得到如图15-43所示的对话框，可以设置坐标轴格式的刻度、文字字体、文字方向等。 图15-42 选择【坐标轴格式】命令 图15-43 【坐标轴格式】对话框15.4 使用公式与函数进行计算15.4.1 使用公式与函数进行计算公式和函数是Excel用于计算的重要功能，下面介绍一下函数和公式的简单用法。（1）选择单元格，在输入栏中单击【插入函数】按钮，如图15-44所示。（2）在弹出如图15-45所示的对话框中可以选择函数，如果不知道函数的具体写法，可以在【输入函数】文本框中输入关键字，然后单击【转到】按钮，会将函数说明中含有该关键字的函数在【选择函数】列表框中显示，列表框的下方显示函数的简单用法，单击【有关函数的帮助】可以进入帮助界面中显示函数的具体用法。（3）选择平均函数AVERAGE，单击【确定】按钮，在弹出如图15-46所示的对话框中单击按钮选择数据，单击【确定】按钮完成操作，得到函数运算结果。单击该单元格，在输入栏中显示使用的函数，如图15-47所示。 图15-44 选择【插入函数】命令 图15-45 【插入函数】对话框 图15-46 选择待计算的数据 15-47 单击结果显示公式（4）公式是根据需要自己编写的算法，如在工具栏中输入“=b2+c2”，则该单元格将b2和c2单元格中的数字进行相加。（5）函数和公式是可以作为序列的算法填充的，使用方法和填充方法一致。如图15-48所示，选择一个利用公式计算得到的结果，选择并向下拖动单元格，得到序列填充数据，选择数据，得到如图15-49所示的结果，从结果上看，这种填充方式是按照单元格的需要变化规律得到的。 图15-48 选择一计算结果 图15-49 拖动单元格的效果图（6）在输入栏中选择输入的公式或者函数，则所有引用的数据将突出显示，且突出框的颜色与输入栏中文字的颜色相同，如图15-50所示。图15-50 与公式或函数相关的数据显示15.4.2 引用数据的技巧上一节就是利用引用单元格的方法完成了一定的计算，这种在一个单元格中调用其他单元格的方法就称为引用。（1）如图15-51所示，选择D1单元格，显示该单元格引用的数据。（2）选择并剪切引用的数据，然后粘贴到其他位置，得到如图15-52所示的结果。从结果上看D1单元格并没有因为应用单元格位置的改变而改变，单击该单元格，得到如图15-53所示的结果，显示该值自动改变为新的位置。（3）如果复制D1单元格，粘贴到D6位置，则效果与序列填充是一样的，因为没有数据，所以显示错误提示，如图15-54所示。 图15-51 显示引用的数据 图15-52 剪切、粘贴显示的数据 图15-53 该值自动更新为新的位置 图15-54 提示错误信息从上面简单的例子可以看出，不同情况下引用单元格会得到不同的效果。而本例中使用的效果都被称为相对引用。将D1单元格中使用的函数改为“=AVERAGE($A$2:$A$5)”，则就变为绝对引用，这样无论复制到什么位置都不会改变，如图15-55所示。这里所谓的相对引用和绝对引用，引用单元格位置改变算法是否改变，而不是指引用源是否改变，如果再次将A1:A5单元格剪切并粘贴到其他位置，则引用数据的单元格还是不会发生改变。图15-55 绝对引用效果图当然也可以单独将行或者列变为绝对引用，写法如“$A1”、“A$1”所示。在工作表中引起其他工作表或工作簿中的数据也比较普遍，可以直接输入“Sheet3!C8”（表示引用Sheet3工作表中的C8单元格）或“Book4Sheet1!$A$1”（表示引用工作簿名称为Book4工作表名称为Sheet1的A1单元格）的形式，如图15-56所示。还有一种方法引用单元格，在输入栏中输入运算符以后，如图15-57所示。单击工作表（可以是其他工作表或者其他工作簿中的工作表）中的单元格，按Enter键完成引用输入工作。 图15-56 直接输入引用的数据 图15-57 输入运算符+单击工作表引用数据15.5 密码保护文档Excel中设置密码一般分为3个层次，即文件、工作簿和工作表。（1）执行【工具】|【选项】命令，在弹出如图15-58所示的对话框中可以设置打开和修改文件的密码，如果要取消密码保护，重新进入后删除即可。（2）执行【工具】|【保护】|【保护工作簿】命令，弹出如图15-59所示的对话框，可以设置保护密码保护在工作簿中的一些操作。设置保护功能后，【保护工作簿】命令变为【撤销保护工作簿】命令，执行该命令，输入密码即可取消密码保护。 图15-58 设置文件密码 图15-59 设置工作簿密码（3）执行【工具】|【保护】|【保护工作表】命令，弹出如图15-60所示的对话框，可以设置保护密码保护在工作簿中的一些操作。设置保护功能后，【保护工作表】命令变为【撤销保护工作表】命令，执行该命令，输入密码即可取消密码保护。 图15-60 设置工作表密码15.6 打印的技巧Excel与Word不同，因为Word是每个页面就是一个打印的单元，看到的和打印出来的效果是一致的。E