Excel 2000进阶指南 张迎新 2000年 第53期 Excel 2000是一款得到广泛应用的优秀电子表格软件，其数字运算功能相当强大、接口类型十分齐备。也正因为如此，它的疑难和奥妙也一直困扰着无数用户，致使许多用户尽管使用了多年仍不能熟练掌握该软件的某些具体功能，为了迅速提高读者的Excel 2000应用水平。下面详细介绍Excel 2000的一些使用方法和技巧。 #1 一、Excel 2000编辑技巧 （一）单元格编辑技巧如^00100005a^ （二）提高数据录入准确性 1．有效性检查 Excel 2000可以对单元格内的数据类型进行限制，并能核对输入单元格的数据定义域，这一功能称之为有效性检查。以小数（其他数据相似）的有效性检查为例，其设置方法是： （1）选中需要输入小数并进行有效性检查的单元格或单元格区域，单击“数据”菜单中的“有效性”命令，打开“数据有效性”对话框。 （2）打开“设置”选项卡中的“允许”下拉列表，选中允许输入的数据类型，本例应选中“小数”。 （3）在“数据”下拉列表中选择所需的数据范围逻辑，如介于、小于、大于或等于等，然后指定数据的上下限。如果你选择的数据范围逻辑为“介于”，就可以在“最小值”和“最大值”输入具体数值，还可以为它指定单元格引用或公式。如果允许数据单元格为空，则应选中“设置”选项卡中的“忽略空值”项，反之则应将该项清除。 有效性检查设置结束后，一旦你输入了有效范围以外的数据，Excel 2000就会弹出“输入值非法”对话框，单击“重试”按钮可修改已输入的数据，单击“取消”可清除已输入的数据。 2．设置输入提示 用户对单元格的用途认识不清，是导致数据输入错误的常见原因。针对这种情况，Excel 2000可在用户选中单元格时给予提示，从而减少输入错误的发生。输入提示的设置方法是： （1）选中需要显示输入提示的单元格或单元格区域，单击“数据”菜单中的“有效性”命令，打开“数据有效性”对话框中的“输入信息”选项卡。 （2）选中“输入信息”选项卡中的“选定单元格时显示输入信息”项，在“标题”框内输入提示的标题，在“输入信息”框内输入提示的详细内容。完成后单击“确定”按钮。 此后只要你选中了具有输入提示的单元格或单元格区域，Excel就会自动弹出一个提示框，对用户的操作进行指导。 3．设置出错警告 数据录入错误几乎是不可避免的，有效性检查仅能查出输入的数据是否有问题，但不能给出纠正的方法。对此，你可以在进行有效性检查的同时设置出错警告，在数据录入错误发生时提示用户如何操作。出错警告的设置方法是： （1）选中需要显示录入出错警告的单元格或单元格区域，单击“数据”菜单中的“有效性”命令，打开“数据有效性”对话框中的“出错警告”选项卡。 （2）选中“输入信息”选项卡中的“输入无效数据时显示出错警告”项，在“样式”下拉列表中选择“中止”、“警告”和“信息”三种警告方式之一。它们的区别是：“中止”方式不允许用户输入数据定义域以外的数据，用户只有“重试”再次输入有效数据，或“取消”已输入的数据两种选择；“警告”方式在用户输入无效数据后会显示警告信息，同时询问“是否继续？”。选择“是”完成输入，选择“否”可修改已输入的数据，选择“取消”可删除已输入的数据；“信息”方式允许用户输入无效数据，用户若输入了无效数据会显示警告信息，若选择“确定”完成输入，选择“取消”则删除已输入的数据。 （3）在“标题”框内输入警告的标题，在“出错信息”框内输入发生错误的原因及如何纠正的内容。完成后单击“确定”按钮。 以上设置完成后，只要在具有出错警告的区域内输入了错误数据，Excel就会按设定方式对用户提出警告。取消出错警告的方法与上相同。 4．自动选择输入法 Excel数据录入时切换输入法会大大影响录入速度，为此Excel 2000可以根据你要输入的内容自动切换输入法。设置方法是： （1）选中需要使用某种输入法的单元格或单元格区域，再将该输入法激活。单击“数据”菜单中的“有效性”命令，打开“数据有效性”对话框中的“输入法模式”选项卡。 （2）在“输入法模式”选项卡的“样式”下拉列表中选择“打开”，最后单击“确定”按钮。 以后只要选中了已设置输入法的单元格，无论当前使用的是何种输入法，你需要的输入法都会自动激活，用起来非常方便。 （三）数据录入技巧 1．快速录入分数 通常在单元格内输入分数8/9会显示为8月9日，如果你想避免这种情况发生，除了将单元格格式设置为“分数”外（Excel 2000有分数格式）。输入8/9时在它前面添加一个0和一个空格（它在0与8/9之间）即可，但是用此法输入的分母最大不能超过99。 2．录入文本格式数字 如果你要在单元格中输入文本格式的数字（如“身份证号码”），除了事先将单元格设置为文本格式外，只须在数字前面加一个“＇”（单引号）即可。 3．行或列重复填充 如果你要在同一行或列内重复填充某些数据，可按以下方法操作：选中包含原始数据的单元格（或区域）；将鼠标移至所选区域右下角的填充柄，当光标变为小黑十字时，按下左键拖过所有需要填充的单元格再松开鼠标。 注意：如果被选中的是数字或日期等数据，可能会以序列方式填充，这时应按下Ctrl键再拖动。 4．周围数据填充 如果你要填充的数据与周围单元格（或区域）中的数据相同，可用以下方法快速重复填充： （1）按Ctrl+D键，将上方单元格中的数据填充进来，按Ctrl+R键，将左侧单元格中的数据填充进来。 （2）单击“编辑”、“填充”子菜单下的“向上填充”、“向左填充”命令，可将下方或右侧单元格中的数据填充进来。 注意：如果要填充的是一个区域，可先将其选中，再按上法操作，即可将对应区域内的数据填充进来。 5．简单序列填充 数字、日期等简单序列填充可按以下方法填充： （1）在起始单元格中输入序列的初值，然后在下方或右侧的单元格中输入序列的第二个值，这两个值的差就是序列的步长。 （2）选中已输入的所有单元格，将鼠标移至所选区域右下角的填充柄，当光标变为小黑十字时，按下左键沿行或列拖动。如果要按升序排列，应由上而下或由左而右拖动。如果要按降序排列，应由下而上或由右而左拖动。 6．自定义序列填充 如果你输入的序列比较特殊，可以先加以定义，再项内置序列那样使用。自定义序列的方法是： （1）单击“工具”菜单中的“选项”命令。打开“选项”对话框中的“自定义序列”选项卡。 （2）在“输入序列”框中输入自定义序列的全部内容，每输入一条按一次回车键，完成后单击“添加”按钮。整个序列输入完毕后，单击对话框中的“确定”按钮。 此后，你只要输入自定义序列中的前两三项，就可以按前面介绍的方法将其输入。 7．选择填充 若需要填充前一两个单元格面已输入过的数据（文本或文本与数字的混合，纯数字不行）。可以采用以下方法： （1）让光标停留在需要填充的单元格。按Alt+↓键打开本列已填充内容列表。选择快捷菜单中的“选择列表”命令，打开本列已填充内容列表。 （2）用上下方向键选中列表中你需要填充的内容回车，或直接用鼠标单击所需的内容，它就被填入内容列表上方的单元格。 8．双击序列填充 此法适用于填充列左侧有完整数据列的情况，用于排名次等场合非常方便。填充方法是： （1）若要填充数字序列，可在起始单元格中输入两三个数字，全部选中后双击填充柄。则产生的序列会自动向下填充，直到左侧数据列的空白单元格处为止。 （2）若要填充含数字的文本序列（如“第1名”之类），可在起始单元格中输入序列初始值（如“第1名”），将其选中后双击填充柄。则产生的序列就会自动向下填充，直到左侧数据列的空白单元格为止。 9．工作表重复填充 如果你想一次填充多张相同的工作表，省略以后的复制、粘贴等操作，可采用以下方法： （1）选中需要填充相同数据的工作表。方法是：若要选中多张相邻的工作表，可先单击第一张工作表标签，按住 SHIFT 键后单击最后一张工作表标签。若要选中多张不相邻的工作表，可先单击第一张工作表标签，按住 Ctrl键后单击要选中的其他工作表标签。 （2）在已选中的任意一张工作表内输入数据，则所有选定工作表的相应单元格会填入同一数据。 如果需要将某张工作表已有的数据快速填充到其他工作表，可采用以下方法：按下Ctrl键选中含有数据的工作表和待填充数据的工作表，再选中含有数据的单元格区域，最后单击“编辑”、“填充”子菜单下的“至同组工作表”命令，在对话框中选择要填充的内容（“全部”、“内容”或“格式”）后“确定”。 #1 二、快速操作Excel 2000 （一）快速设置格式 1．快速应用格式 （1）要想将某一单元格（或区域）的格式（字体、字号、行高、列宽等）应用于其他区域，可将上述单元格（或区域）选中。单击“格式”工具栏中的“格式刷”按钮，然后拖过要设置格式的单元格（或区域）。 （2）如果要将所选单元格（或区域）的格式应用到多个地方，可双击“格式刷”按钮，然后按上面介绍的方法操作，完成后再次单击“格式刷”退出操作。 2．快速设置“最合适的列（行）宽” （1）如果你要将工作表中的某列设置为“最合适的列（行）宽”，只须将光标放到该列列标右侧（行下面）的边线处，待光标变成双向光标后双击边线即可。 （2）如果你要将工作表中的所有列设置为“最合适的列（行）宽”，只须按设置“最合适的列宽”单击工作表左上角的全选按钮选中工作表。然后将光标放到任意一列列标右侧（行下面）的边线处，待光标变成双向光标后双击即可。 （二）快速操作技巧 1．快速删除行、列内容 如果你要删除行或列中的数据、格式等全部内容，可以将需要的行或列选中。然后按下SHIFT键，将鼠标移到“填充柄”处。待双向光标出现后，向上拖动一行（删除行内容时），或者向左拖动一列（删除列内容时）即可。 2．快速行列转换 （1）选中需要转换行列的单元格区域，单击工具栏中的“复制”按钮。然后打开一个新的工作表选中A1单元格，也可在当前工作表中选中一个空白单元格。单击“编辑”菜单下的“选择性粘贴”命令，选中对话框中的“转置”项，“确定”后行列的相互转换即告结束。 （2）选中需要转换行列的区域，单击Excel 2000工具栏中的“复制”按钮。选中转换后行列所在区域左上角的单元格，单击“编辑”菜单下的“选择性粘贴”命令，在“选择性粘贴”对话框内选中“转置”复选项，单击“确定”即可将行列快速转换。 3．快速自动保存 单击“工具”菜单中的“加载宏”命令，选中对话框中的“自动保存”命令，确定后“工具”菜单即可出现一条“自动保存”命令。单击该命令可打开“自动保存”对话框，对“自动保存时间间隔”、“保存选项”等进行设置。 #1 三、Excel 2000公式编辑技巧 1．使用公式选项板 公式选项板是输入和调试公式的一个有力工具，以下面的成绩等第计算公式“=IF（C2>=85，″A″，IF（C2>=70，″B″，IF（C2>=60，″C″，IF（C2<60，″D″）））”为例，使用“公式选项板”输入公式的步骤是： （1）选中用来存放计算结果的单元格，单击公式选项板中的“编辑公式”按钮（即“=”按钮） （2）单击“函数”下拉列表框右端的下拉箭头，从列表中选择“IF”。如果你需要的函数没有出现在列表中，可单击“其它函数”，打开“粘贴函数”对话框寻找。 （3）在打开的“IF”公式选项板中输入参数。对本例来说，第一个参数“Logical-test”是一个逻辑判断，须用键盘在其后的输入框内敲入“C2>=85”。第二个参数“Value-if-true”是“C2>=85”为“真”时计算（判断）结果，也需要键盘在其后的输入框内填入“A”。第三个参数“Value-if-false”是逻辑判断“C2>=85”为“假”时的计算（判断）结果，本例是继续执行下一个IF函数。所以应让光标停留在“Value-if-false”框内，然后单击“函数”框中的“IF”，则一个新的“IF”公式选项板打开，按上面介绍的方法继续输入直至结束。 （4）公式输入结束后，单击公式选项板中的“确定”按钮完成计算。 2．公式复制技巧 复制是将公式应用于其他单元格的操作，最常用的有以下几种方法： （1）拖动复制 选中存放公式的单元格，移动空心十字光标至单元格右下角。待光标变成小实心十字时，按住鼠标左键沿列（对行计算时）或行（对列计算时）拖动，至数据结尾完成公式的复制和计算。 还有另一种拖动复制方法：选中存放公式的单元格，移动空心十字光标至被选中单元格的边框。待光标变成指针形状时按下Ctrl键，待指针旁出现一小加号后，再按住鼠标左键沿列（对行计算时）或行（对列计算时）拖动，到达数据结尾完成公式的复制和计算。 （2）输入复制 此法是在公式输入结束后立即完成公式的复制，操作方法是：选中需要使用该公式的所有单元格，用上面介绍的方法输入公式，完成后按住Ctrl键敲回车，该公式就被复制到已选中的所有单元格。 （3）选择性粘贴 选中存放公式的单元格，单击Excel工具栏中的“复制”按钮。然后选中需要使用该公式的所有单元格，在选中区域内单击鼠标右键，选择快捷菜单中的“选择性粘贴”命令。打开“选择性粘贴”对话框后选中“粘贴”下的“公式”，单击“确定”公式就被复制到已选中的所有单元格。 3．公式与结果切换 单元格中的公式执行后显示的是计算结果，按Ctrl+`键（位于键盘左上角，与“~”为同一键），可使公式在显示公式内容与显示公式结果之间切换，方便了公式编辑和计算结果查看。 4．Excel 2000公式运算优先级 如果公式中同时使用了多个运算符，Excel 将按^00100005b^表所示优先级执行运算。 注意：若公式中包含相同优先级的运算符，Excel 将从左到右进行运算。如果要修改执行运算的顺序，应把公式需要首先计算的部分括在圆括号内。 #1 四、Excel计算错误解疑 （一）# # # # #错误 1．计算结果太长 假如A1=1266.62、B2=1668.96，则C1=A1+B1的结果为2935.58。若单元格C1的宽度小于3.88（依使用的字号不同略有差异），即会出现# # # # #错误。 解决方法：用拖动列标题边界的方法增加单元格宽度，直至显示结果正确。或选中出错的列，单击“格式”、“列”子菜单下的“最合适的列宽”命令，使列宽自动增加至合适的数值即可。 如果设定的小数位数过多，也可通过修改单元格数据格式的方法来消除# # # # #错误。方法是：选中出错的单元格，然后单击“格式”菜单中的“单元格”命令，打开“数字”选项卡，选中“分类”下的“数值”，选择合适的小数位数和小数格式后“确定”。 2．计算结果为负值 如果用上述方法不能消除错误，且进行了日期或时间计算，则有可能是计算结果为负值造成的。例如A1=1999-8-16、B1=1986-2-26，则C1=B1-A1就会出现# # # # #错误。 解决方法：修改计算公式，使计算出的日期或时间为正值。也可按上面介绍的方法将该单元格设为数值格式，也就是将计算出的日期或时间转化为序列数。方法是：用鼠标右键单击显示“# # # # #”的单元格，在快捷菜单中选“设置单元格格式”命令。打开“单元格式”对话框的“数字”选项卡，选中“分类”下的“数值”，最后在“负数”下选中你需要的格式后单击“确定”即可。 （二）#VALUE！错误 1．对含有文本的单元格进行了数值运算 如A1=3、B1=“6”（用引号引起来的6表示它为文本）或B1单元格内输入了“年月”二字。则C1=A1+B1的结果是#VALUE！。 解决方法：检查所有参与运算的单元格。若对含有文本的行或列求和，最好使用SUM函数，因为SUM函数在计算时可以忽略文本所在的单元格。 2．在需要单一数值的运算符或函数中输入了一个数值区域 例如计算“C1=INT（A1:B2）”（A1、B1、A2、B2均为小数），则C1的计算结果为#VALUE！。 解决方法：修改公式，将其中的数值区域改为单一数值，如将上式改为C1=INT（A1）。或者先对A1:B2区域求和，再进行取整运算，即C1=INT（SUM（A1:B2）） （三）#DIV/O!错误 原因：公式的除数引用了空白单元格或包含零的单元格。例如：F1=SUM（A1:D1）/E1，若E1为空白单元格或被其他公式赋与零值，则C1的计算结果为#DIV/O!。 解决方法：修改单元格引用，或者在用作除数的单元格中输入不为零的值。例如将F1=SUM（A1:D1）/E1中的E1改为其他单元格，或保证E1单元格赋予确定的非零数值。 判断是否属于上述错误有一个简单方法：即在用作除数的单元格中输入#N/A。如果F1由#DIV/0!变为#N/A，说明确属除数引用了空单元格或包含零的单元格导致了#DIV/O!。 （四）#NAME错误 1．函数名称输入错误 例如误在编辑栏中的公式“E1=SUM（A1:D1）”输成“E1=SUN（A1:D1）”，就会在E1单元格产生#NAME错误。 解决方法：不太熟练的用户最好用函数向导等方法输入函数。例如：先让光标停在要输入公式的单元格，然后单击Excel 2000“编辑栏”中的“编辑公式”按钮（即等号），将“=”插入编辑栏。再单击“名称框”右边的按钮打开下拉菜单，从中选择你需要的函数单击，打开“XXXX”（ XXXX为函数名）对话框，用鼠标拖动的方法选中需要计算的单元格或单元格区域，完成后“确定”。 2．在公式中引用的文本没有双引号 例如A1=68、A2=96，如要使B1、B2显示“卫生费68”、“卫生费96”等，可以用公式“=″卫生费″＆B50”，若其中的文本引用（″卫生费″）缺少双引号，就会出现#NAME错误。 解决方法：在公式中引用文本不要忘了加双引号。 （五）#N/A错误 1．函数或公式没有可用数值 例如公式“=LOOKUP（″里斯本″，D4:E4）”在D4:E4区域中查找文本“里斯本”，如果该区域恰恰为空或全为数值，就会出现#N/A错误。 解决方法：如果计算中某些单元格暂时没有数值，可在这些单元格中输入#N/A。公式在引用这些单元格时，将不进行计算，而是返回#N/A。 对LOOKUP、MATCH等函数的查找值（如″销售收入″）则应指定正确的值域，包括数据类型和单元格区域是否正确。 2．使用的函数中缺少一个或多个参数 例如公式“=LOOKUP（，A6:B7）”在查找A6:B7区域时缺少查找对象，尽管A6:B7区域均不为空，仍然会出现#N/A错误。 解决方法：仔细检查后在函数中输入全部参数。不熟练的用户最好用函数向导输入参数，凡可选的参数才能省略，否则必须输入。 （六）#REF！错误 原因：删除了由其它公式引用的单元格或将带有计算结果的单元格粘贴到其它公式引用的单元格中。例如公式“B1=IF（A1>5000，"好"，"差"）”在A1>5000时给B1赋值“好”，否则赋值“差”。如果你将公式“=LOOKUP（"a"，A8:B8）”的结果粘贴到A1就会出现#REF！错误。 解决方法：更改公式或错误粘贴了单元格之后立即单击工具栏中的“撤消键入”按钮，以修改错误的引用或粘贴。 （七）#NUM！错误 1．公式或函数输入的数值超出其可以接受的值域 例如“B6=ACOS（A9）”，而A9的值为-3就会出现#NUM！错误。 解决方法：应确保输入的数值在公式或函数的可接受范围内，并保证函数使用的参数类型正确。 2．计算产生的数值太大或太小，超出了Excel可以表示的范围 Excel 2000可以表示的数值在-1.79769313486231E+308与1.79769313486231E+308之间，若超出了上述范围就会出现#NUM！错误。 解决方法：修改计算公式，使产生的结果在满足Excel 2000的要求。 （八）#NULL！错误 原因：公式或函数中的区域运算符或单元格引用不正确。例如使用SUM函数对A1:A5和B1:B5两个区域求和，而在引用这两个没有交叉的区域时使用了交叉运算符（空格）。写成“SUM（A1:A5 B1:B5）”（注意：A5与B1之间有空格）就会出现#NULL！错误。 需要说明的是：交叉运算符（空格）是为了防止区域求和时发生重复计算而引入的。例如公式“=SUM（A1:A3，A1:C1）”将对A1单元格重复求和（加两次），而公式“=SUM（A1:A3 A1:C1）”只对A1单元格求和一次。 解决方法：对没有交叉的区域进行求和等运算时不要使用交叉运算符，而要使用逗号分隔求和区域。例如公式“=SUM（A1:A5 B1:B5）”应写成“=SUM（A1:A5，B1:B5）”。 #1 五、Excel 2000应用实例解析 1.利用身份证号码获取公民出生日期 使用Excel进行人事管理，常常需要输入身份证号码、出生年月日等数据。由于身份证号码内含有相关信息，可以使用Excel函数从身份证号码中提取出生年月日，从而避免重复劳动以及可能的输入错误。 现行的身份证号码左起第7至12个字符表示出生年月日（存在Y2K问题），我们可以使用MIDB函数从身份证号码的特定位置，分别提取出生年、月、日，再用CONCATENATE函数将提取出来的文字合并起来，就能得到对应的出生年月日。 具体公式为：=CONCATENATE（″19″，MIDB（A4，7，2），″年″，MIDB（A4，9，2），″月″，MIDB（A4，11，2），″日″）式中“19”是针对现行身份证号码中存在Y2K问题设置的，它可以在提取的出生年份前加上19。三个MIDB函数的操作对象存放在A4单元格，分别从左起第7、9、11个字符开始提取2个字符，即得到出生年、月和日，最后由CONCATENATE函数将所有字符合并起来。 需要注意的是：CONCATENATE函数和MIDB函数的操作对象均为文本，所以存放身份证号码的单元格必须事先设为文本格式，然后再输入身份证号。 2.利用身份证号码获取公民性别 这个公式可以从工作表的身份证号码中提取对应的性别。具体公式为：=IF（RIGHTB（a1，1）=″1″，″男″，IF（RIGHTB（a1，1）=″3″，″男″，IF（RIGHTB（a1，1）=″5″，″男″，IF（RIGHTB（a1，1）=″7″，″男″，IF（RIGHTB（a1，1）=″9″，″男″，″女″））））），公式中C列存放文本格式的身份证号码，函数RIGHTB提取字符串右端给定长度的字符。根据身份证号码编号规则，该字符为奇数时为男，否则为女。经过IF函数五次判断，凡RIGHTB（c1，1）值为“1”、“3”等奇数的均为男，否则为女。 需要注意的是：RIGHTB（c1，1）提取C1单元格存放的身份证号码右起第一个字符，由于它是由文本格式的单字节数字组成的，一个字符占用一个字节，故以1为单位计算要提取的字符数。如果是普通汉字或双字节数字，一个字符要占用两个字节，应以2为单位计算要提取的字符数。 3.成绩等第计算 学校采用等第评定考试成绩，一般的标准为：考试分数高于或等于85分为A等；考试分数低于85分高于或等于70分为B等；考试分数低于70分高于或等于60分为C等；考试分数低于60分为D等；没有参加考试的不划等第。用Excel计算等第的公式如下（其中分数存放在C列，计算结果存入D列）：=IF（C2>=85，″A″，IF（C2>=70，″B″，IF（C2>=60，″C″，IF（ISNUMBER（C2），″D″，IF（ISBLANK（C2），″ ″））））），这是一个典型的IF函数嵌套公式，式中第二个IF语句是第一个 IF 语句的参数，第三个 IF 语句则是第二个IF语句的参数，以此类推。如果第一个逻辑判断表达式C1>=85 为TRUE（真），则D1单元格被填入“A”；如果第一个逻辑判断表达式C1>=85不成立，则计算第二个IF语句“IF（C1>=70”；以此类推直至计算结束。其中ISNUMBER函数在C1为空时返回FALSE（假），接着执行最后一个IF语句，否则在C1单元格中填入“D”。ISBLANK函数C1为空时返回TRUE（真），则C1单元格被填入一个空格。使用ISNUMBER函数和ISBLANK函数，可防止某个学生没有参加考试（即考试成绩为空），但仍然给他评定为D等的情况发生。 如果成绩等第划分标准发生了变化，只须改变逻辑判断式中的值（85、70、60）即可，故上式在需要划分等第的场合具有一定的应用价值。IF函数常用的比较操作符如^00100005c^： 4.利用IF函数计算工资增加金额 IF函数是Excel中最有用的函数之一，它可以解决办公事务处理中的许多问题。请看以下实例： 某单位要给职工增加工资，其标准为：工龄少于等于5年为20元；工龄多于5年少于等于10年为40元；工龄多于10年少于等于15年为70元；工龄多于15年少于等于20年为100元；工龄多于20年少于等于25年为140元；工龄多于25年少于等于30年为180元；工龄多于30年为240元。 此问题的处理方法是：建立工作表，其中A列输入职工姓名，B列输入工龄，C列存放计算出来的增资金额，A1、B1、C1单元格中输入列标题。再让单元格指针停留在C2单元格，按笔者以前介绍过的方法输入公式：=IF（B2<=5，20，IF（B2<=10，40，IF（B2<=15，70，IF（B2<=20，100，IF（B2<=25，140，IF（B2<=30，180，IF（B2>30，240）））））））。 上式首先计算第一个逻辑判断“B2<=5”，若其为“真”，则C2单元格被填入数值“20”；若其为“假”，则计算第二个IF语句“IF（B2<=10，40”，依次执行直至计算结束。这就是IF函数嵌套的基本形式，加以改造后可应用于其他需要多重逻辑判断的场合。 需要注意的是：公式中的逻辑判断条件“B2<=10”、“B2<=15”等不能写成“530，C2+240））））））），执行后可以立即算出增加工资后的总工资。当然你也可以将公式中的“C2+20”、“C2+40”等修改为SUM（C2，20）、SUM（C2，40）等，这样可以执行一些更为复杂的求和运算。 5.SUM函数应用实例 SUM函数是Excel中使用最多的函数，利用它进行求和运算可以忽略存有文本、空格等数据的单元格，语法简单、使用方便。 （1）行或列求和 以最常见的工资表为例，它的特点是需要对行内的若干单元格求和，并要自动扣除“房电费”、“税金”等。根据习惯，这些项目并不输入负数。这时可在F2单元格输入如下公式：=SUM（A2:C2，-D2，-E2）。其中A2:C2引用是收入，而D2、E2为支出。由于Excel不允许在单元格引用前面加负号，所以应在表示支出单元格前加负号，这样即可计算出正确结果。若收入和支出所在的单元格不连续，可将公式写成“=SUM（A2:C2，-D2，E2:F2，-G2）”的形式。 （2）区域求和 区域求和常用于对一张工作表中的所有数据求总计。此时你可以让单元格指针停留在存放结果的单元格，然后在Excel编辑栏输入公式“=SUM（）”，用鼠标在括号中间单击，最后拖过需要求和的所有单元格。若这些单元格是不连续的，可以按住Ctrl键分别拖过它们。然后用手工在公式引用的单元格前加上负号。 （3）IF函数和SUM函数联合应用 IF函数和SUM函数联合起来，可以累计满足多个条件的值出现的次数。例如下面的公式就可以统计考试成绩及格的男生人数。每当 Excel 在 B1:B200 中找到“男”时，它就会检查同一行中 C 列（C1:=SUM（IF（B1:B200=″男″，IF（C1:C200>=60，1，0）））。 需要说明上式是一个数组公式，输入结束时必须按 下Ctrl+SHIFT键然后敲回车，Excel 2000自动在公式两侧加上大括号，成为“{=SUM（IF（B1:B200=″男″，IF（C1:C200>=60，1，0）））}”这是使用数组必不可少的步骤。 注意：SUM函数中的参数，即被求和的单元格或单元格区域不能超过30个。换句话说，SUM函数括号中出现的分隔符（逗号）不能多于29个，否则Excel就会提示参数太多。对需要参与求和的某个常数，可用“=SUM（单元格区域，常数）”的形式直接引用，一般不必绝对引用存放该常数的单元格。 6.COUNTIF函数应用实例 COUNTIF函数可累计满足某一条件的对象在单元格区域中出现的次数，该条件可以是数值、文本或表达式，在人事、工资和成绩统计中有广泛用途。请看以下实例： 某单位要统计工资报表中工资总额大于等于1000元的人数，男职工人数和女职工人数。假设工资总额存放在工作表的F列，职工性别存放在工作表B列，该单位有1000人。 具体计算公式为：统计工资总额大于等于1000元的人数可用公式：“=COUNTIF（F1:F1000，″>=1000″）”；统计男职工人数可用公式：“=COUNTIF（B1:B1000，″=男″）”，统计女职工人数可用公式：“=COUNTIF（B1:B1000，″=女″）” 7.SUMIF函数应用实例 SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。例如：某单位要统计工资报表中职称为中级的员工的工资总额。假设工资总额存放在工作表的F列，员工职称存放在工作表B列。 具体计算公式为：“=SUMIF（B1:B1000，″中级″，F1:F1000）”，其中“B1:B1000”为提供逻辑判断依据的单元格区域，“中级”为判断条件即只统计B1:B1000区域中职称为“中级”的单元格，F1:F1000为实际求和的单元格区域。 另外，COUNTIF 函数和SUMIF函数结合起来可用于统计考试及格学生的平均分。具体计算公式为：=SUMIF（A4:A46，″>=60″，A4:A11）/COUNTIF（A4:A46，″>=60″），式中两个函数的参数、逻辑判断条件的意义请参看上面的实例自行分析。 8.RANK函数应用实例 排名次是成绩统计等领域常用的一种分析手段，利用Excel 2000提供的RANK函数可以轻松完成。假设6个数据存放在C列的C2:C7单元格，那么只要选中D2单元格，然后在编辑栏中输入如下公式：=RANK（C2，$C$2:$C$6）。输入结束回车或单击“输入”按钮完成计算。RANK函数的最大优点是可以处理重名次，可使数值相同的单元格计算出的名次相同。如果你要排序的数据有40个，只须将以上两式中的6改为41即可。 9.利用FREQUENCY函数统计成绩分布 统计某次考试的成绩分布，是教育和其他统计的一项常规工作，Excel 2000中的FREQUENCY函数是完成这类工作的利器。 （1）打开一个空白EXCEL工作表，在A列输入待统计的考试成绩或其他数据，这里假设有A1:A50共50个成绩。如果B列的B1:B5单元格分别输入60、70、80、90、100，则表示统计小于60分、60—70、70—80、80—90和90—100分数段中的成绩个数。 （2）选中C1:C5单元格存放各分数段的成绩个数，在编辑栏内输入公式：=FREQUENCY（A1:A50，B1:B5） （3）让光标停留在公式的末尾，按下Shift+Ctrl键敲回车（这是生成数组公式的关键环节），C1:C5单元格立刻计算出各分数段中的成绩个数。 10.＆、MONTH（）和NOW（）函数的使用 许多报表的标题均含有月份，例如“XX中学X月教师课时奖金表”，利用＆、MONTH（）和NOW（）函数可以实现月份自动输入。具体做法如下： （1）将标题占用的所有单元格合并。 （2）在选中合并后的单元格，然后在编辑栏内输入公式：=“XX中学”＆MONTH（NOW（））＆“月教师课时奖金表”。 其中＆为连接符，它可以将其左右的字符连接成一个整体。NOW（）函数可以返回电脑内部的系统日期与时间，MONTH（）函数可以将序列数转换为对应的月份数。这样，每次打开这个工作表，标题中的月份均会自动更新，形如“XX中学12月教师课时奖金表”。如果你想在标题中显示年份，可以将公式修改为：=“XX中学”＆ YEAR（NOW（））＆“年”＆MONTH（NOW（））＆“月教师课时奖金表”。