2024年1月10日发(作者：)
从菜鸟到精通——Excel实用技巧图文教程
“选择性粘贴”的技巧2则
本次内容简介：“选择性粘贴”的技巧
―选择性粘贴‖指用户有目的地对已经复制信息进行粘贴，如粘贴格式、数值等操作。如果用户巧用―选择性粘贴‖功能可以解决工作中的很多难题。
下面将向用户介绍―选择性粘贴‖的两则常用技巧：
1.数值公式的复制。2.巧用转置功能粘贴数据。
数值公式的复制
在WPS表格中计算本月的预期利润，―销售额‖一栏由公式计算所得，但选择复制到Sheet2的单元格，数值并未随之复制。这是什么原因？计算结果不易复制的情况不仅是在几个工作表之间复制时会发生，在同一个工作表中进行复制时也会遇到，利用―选择性粘贴‖法便可解决。
步骤一：在WPS表格中首先选择需要复制的单元格，如汇总、合计、求和、平均值等单元格，然后复制该单元格。如图 1所示。
图1 源复制数据的选择
步骤二：然后打开需要粘贴的工作表，定位单元格，选择―编辑｜选择性粘贴‖菜单项，弹出―选择性粘贴‖对话框，在―粘贴‖栏目中选择―数值‖。如图2所示。
图2 选择性粘贴数值的选择
步骤三：单击―确定‖按钮，便完成不带公式或函数的数据转换，结果如图3所示。
图3 数值公式复制的结果
巧用转置功能粘贴数据
在使用WPS表格编辑一张表格后，由于某种原因，用户希望将该表格的行列互换，以方便地打印在A4纸上，就需要用到―选择性粘贴‖中的转置功能。下面以学生的成绩表为例来进行说明。
步骤一：先选中要进行转换的数据区A1:L6，按下组合键将其复制。如图 4所示。
图4 需转置的数据
步骤二：将光标定位在另一个工作表的A1单元格中。然后选择―编辑｜选择性粘贴‖菜单项，在―选择性粘贴‖对话框中，勾选―转置‖复选框。如图 5所示
图5 勾选―转置‖复选框
步骤三：单击―确定‖按钮，数据已经符合我们的要求了，如图 6所示。
图 6 转置结果
小结：
1.用户使用―选择性粘贴‖功能可以有目的地对所复制内容进行格式、数值或公式的粘贴。
2.用户使用―选择性粘贴｜转置‖功能，可以将数据表进行―行列‖互换。
表格新增的―选择性粘贴｜运算‖功能，能够实现对数值进行运算操作。
适用范围:
适用于WPS Office 2005（751）及以上版本 。
“选择性粘贴”的妙用
本次内容简介：“选择性粘贴”的妙用
众所周知，WPS表格的简单易用有助于用户提高工作效率。例如：用户需要将很多数据乘以或除以同一个数时，如果使用公式或者进行手工修改就会比较麻烦，而使用WPS表格的―选择性粘贴‖则比较方便，这是因为它的新版本中添加了数值的―运算‖功能。
以下以一个实例说明如何如果利用这一新功能，该例将报表中以元显示的数字转化为以万元显示。
步骤1：在工作表内任一空白单元格中输入数字10000，然后复制该单元格。如图 1所示。
图1 按常规方式显示的源数据
步骤2：选取要转化为万元显示的数字单元格区域（注意：不能包含公式单元格），选择―编辑｜选择性粘贴‖菜单项，在打开的―选择性粘贴‖对话框中选取―粘贴‖内容为―数值‖，―运算‖方式为―除‖（加、减、乘同理）。如图 2所示。
图2 选择性粘贴选项的选取
步骤3：点击―确定‖按钮，原表中所选择的数据全部除以10000，改成为万元显示的数值。如图 3所示。
图3 万元显示的结果
小结：
Office 2005中新增的―选择性粘贴｜运算‖功能，实现了对数值进行（加、减、乘和除）数学运算操
作。
2.利用―选择性粘贴｜运算‖的功能，用户可以对单元格区域中的数字进行批量运算。
3.利用该功能，用户可以将单元格中的文本型数字批量转换为真正的数值。
适用范围:
适用于WPS Office 2005（751）以上版本。
WPS表格函数在发票金额录入中的妙用
财务人员一定离不开各种金额的输入和转换。有时一个数据在同一表格中会反复的调用，通过函数可以使输入更简化。
例如下列发票，假设不通过函数计算，需有输入四次。不仅工作效率极差，出错的机率也会大大提高。
图一面发票
解决思路：数量和单价是变量，需要手动录入，其它三处利用函数直计算总价，再将总价转换成需要的格式。
步骤一：―金额‖公式
1.选中单元格G4输入公式，然后右拉至Q4； 选中G4：G4下拉填充至G9：Q9。
=LEFT(RIGHT(TEXT($E4*$F4/1%," ￥#;;"),COLUMNS(A1:$K1)))
2.公式解析：
首先将E4和F4单元格相乘。为了便于公式填充，将单元格地址设置为绝对列、相对行形式，使公式右拉下拉填充时可以适应引用单元的变化；
然后将之除了1%，即扩大100倍，作用是消除小数点。如―1.01/1%‖即变为101，方便的面的公式计算；
再用TEXT函数来剔除数量与单价为0之状况。我们知道，单元格格式定义分为四段：[正数、负数、0、文本]，本例中TEXT的第二参数为" ￥#;;"，直接用公号分割开留下空白即表示忽略负数和0。当单价为0或者负数时公式结果返回空白；
再后用right函数提取字符。right是提取字符串右边N个字符的函数,用在此处因第二参数COLUMNS(A1:$K1)是动态的，当公式向右拉动时COLUMNS(A1:$K1)的结果将从11递减至1，那么提取的字符串也将进行递减；
最后用left函数提取最终结果。Left函数与right函数的作用相反--从字符串左边开始提取字符，其第二参数为可选。本例中为减短公式长度，省略了第二参数，即表示提取长度为默认值1。在text函数的参数中第二参数为" ￥#;;"，重点在于前面的空格。此处用left函数提取字符时，若E4和F4乘积之字符长度小于当前单位的位数加1（指亿万百等等的位数）时，left只能提取到空格，若等于当前单位的位数加1时则返回人民币符号―￥‖，然后才逐位返回乘积结果中的单字符，这正是需要结果。
3.本公式三个重点：A）TEXT函数的第二参数前面的空格的妙用；B）第二参数中两个―；‖的妙用；C）将right与left套用使公式逐位提取数字。
步骤二：―合计‖公式
1.选中单元格M10输入数组公式―=TEXT(SUM(E4:E9*F4:F9),"0.00")‖，并同时按CTRL+SHIFT+ENTER三键结束，否则公式对果将出错。输入完后若公式前后系统自动加上花括号即表示输入正错，见图二所示。<
图二 数组公式
2公式解析：将E4:E9区域的值分别乘以F4:F9的值，然后再相加即得到总计，公式会自动忽略空白区。本例若不用数组公式则公式要长很多，效率更差。如：=SUM(E4*F4+E5*F5+E6*F6+E7*F7+E8*F8+E9*F9)。数组运算长处是缩短公式，提高效率，可以将常规公式中的多步运算合并得以简化。最后用text函数将数组结果格式化为精确到小数点两位。
3.选择单元格M10:Q10,单击菜单上的―跨列合并‖，使单元格的结果居于区域正中。
此处用―跨列居中‖而不用―合并居中‖是因数组公式不能存在于多单元格合并后的单元格中。故使用区域跨列合并，显示效果和―合并及居中‖一样，但因未对单元格进行合并，则与数组公式可以和平相处。
图三 跨列合并单元格
步骤二：―大写‖公式
1.选择单元格C10,C10单元格可以先合并居中，然后输入公式：
=TEXT(M10,"[DBNum2][$RMB]G/通用格式")
2.公式解析：本公式通过text函数改变引用源M10单元格数字的显示方式，使之以人民币大写方示。其中[DBNum2]修改为[DBNum1]以及―G/通用格式‖修改为―0‖有不同的效果，读者可以自己试试。
图四 人民币大写
总结：
函数是一个格式化函数，它可以改变引用源的显示方式。第一参数是引用源，第二参数即显示方式。这是一处运用很广的函数。本例中三个公式都已用到它。
2.工作表表要善用公式，简化数字的重复录入，同时也提升准确性。
WPS表格中如何进行表格合并计算
本次内容简介：WPS表格中如何进行表格合并计算
―合并计算‖是WPS表格提供给用户用于实现多表数据统计的汇总工具。用户利用―合并计算‖工具可以在多个表中根据指定的单列（或单行）数据条件实现数据汇总。用户在进行―合并计算‖时，必须指定汇总结果存放的目标区域，此目标区域可位于与源数据相同的工作表上，也可以在其他工作表或工作簿内。其次，需要选择合并计算的数据源可以来自单个工作表、多个工作表或不同的工作簿。
某企业有两个销售分公司，当总公司希望得到各产品销量总额时，可以使用―合并计算‖功能来汇总数据。下面以大发公司第一季度每月的销售情况为例进行介绍，在本例中将对上海、广州两个分公司的工作表进行汇总，其结果保存在总公司的工作表中。
打开工作薄
使用WPS表格打开如图1所示的工作簿文件。
图1 源工作薄文件
定位目标区域：
首先使用鼠标定位到―总公司‖工作表的A2单元格，选择―数据｜合并计算‖菜单项，在―合并计算‖对话框的―函数‖下拉框中选定用来合并计算数据的汇总函数，如本例使用求和函数进行汇总。如图2所示。
图2 设定汇总分类
添加明细区域
在―引用位置‖框中，输入希望进行合并计算的源区的定义。
步骤1 将光标定位到―引用位置‖文本框，鼠标选定―上海‖分公司数据表标签，选择A2:D5区域。
步骤2 点击右侧的―添加‖按钮，将广州数据区域添加到―所有引用位置‖的列表框中。
步骤3 重复步骤1、2方法，将―广州‖分公司数据表区域进行添加。
步骤4 在―标签位置‖下分别勾选―首行‖和―最左列‖的复选框，结果如图3所示。
图3 目的区域的选择
完成汇总
按下―确定‖按钮。就可以看到合并计算的结果，如图 4所示。
图4 分类合并计算的结果
小结：
1.用户可以使用―合并计算‖来对多表进行汇总。
2.如果用户希望按源区域的首行字段进行汇总，需要勾选在―标题位置‖下的―首行‖复选框。
3.如果用户希望按源区域左列分类标记进行汇总，需要勾选―标题位置‖下的―最左列‖复选框。
4.在WPS表格中，最多可以指定255个源区域来进行合并计算。在合并计算时，不需要打开包含源区域的工作簿。
适用范围:
适用于WPS Office 2005及以上版本。
动动注册表 享受WPS Office 新的风格界面
WPS Office 2005推出已经有2年多了。是不是对她那与MS Office完全―兼容‖的界面感到厌倦了呢？其实金山公司早就为您准备好了一套风格独特的界面（如图1），只是不没有公布出来而已。如果你想提前享受这漂亮的界面风格，请跟我来！
图1
第一步，备份注册表：
其实这一步可有可无，因为我们对注册表的操作并不复杂，也基本没有什么危险，但根据老鸟的常识，为了以防万一，还是备份一下吧。
第二步，修改注册表：
在―运行‖中键入―regedit‖并回车，进入注册表编辑器。找到HKEY_CURRENT_USERSoftwareKingsoftOffice6.0common 项，在右侧的空白处右击鼠标，选择―新建－项‖，新建一个名为―Theme‖的项，并在该项下新建两个字符串值——―IconStyle‖和―LayoutStyle‖，并将这两个字符串的值均设置为―kso‖（如图2）:
图2
完成以上修改后，退出注册表编辑器，重新运行WPS 2005组件，看看你的程序界面，是不是已经变成了全新的风格？
如果想改回以前的风格，只需将以上注册表键值删除即可。
多种方式实现单元格合并
WPS 表格在新版本中增加了―合并单元格‖系列按钮，同时配有下拉菜单和快捷键。新增的合并单元格下拉按钮为用户提供了四种选择：合并及居中（Ｊ）、合并（Ｍ）、跨列合并（Ｃ）、跨列居中（Ｅ）。括号中的ＪＭＣＥ分别是四种合并单元格的快捷键。如图1所示：
图1
合并及居中
合并
跨列合并
跨列居中
下面是某实验小学学生成绩单，我们为了让某一科的代课老师更直观的看到学生的成绩，可以用WPS合并单元格达到目的。
具体操作为：
1.选中左边表格中（A1：C1）三列。
2.单击工具栏上的―合并单元格‖按钮，合并居中单元格。
3.三个科目合并居中操作方法同上。
4.给合并好的单元格每个科目再添上颜色，表格就一目了然了。
活用WPS表格制作九九乘法表
聪明的外甥女学会了九九乘法表，见到我就背给我听，还要我给她打一份大的，因为文具盒里的乘法表太小了。怎么办呢？咱不能打消小孩子学习的积极性吧!
经过几天的调试，我终于用WPS表格做出了九九乘法表。我的做法是这样的：
1、建立一个空白表格，在A1输入1，拖动鼠标左鍵向右至I1、向下至A9，这样A1到I1、A1到A9内就分别输入1到9(如图1)：
图1
2、A10内输入公式―=IF(AND($A1>=A$1,COLUMN()<11,ROW()<19),A$1&"×"&$A1&"="&$A1*A$1,"")‖
3、鼠标移到A10右下方，出现一个―+‖后，拖动鼠标左鍵向右至I1，然后依此法向下至I18，这样就完成了A10到I18区域内的公式快速复制。一个九九乘法表就呈现在我们面前了(如图2)：
图2
4、为了美观，我们可以把A1到I9的区域隐藏起来，具体操作如下:选中A1到I9的区域，单击格式菜单，在下拉菜单中选中―行‖，在行中单击―隐藏‖(如图3)：
图3
公式详解：IF为判断，AND为逻辑与，$A1为绝对列和相对行，A$1为相对列和绝对行，COLUMN()表示列数，ROW()表示行数，―&‖是文本连接符。
加快WPS表格数据录入的技巧二则
一、减少对鼠标的依赖和无效击键次数
我们在WPS表格中录入数据时可能做了很多―无用功‖，但我们却没有注意到。录入数据时，一般选择横着录入或竖着录入。横着录入和竖着录入可以分别用键盘上的―Tab‖键和―Enter‖键切换到下一个单元格继续录入。
为什么说我们录入数据时做了很多―无用功‖呢？以图1为例，当我们横着录入，F1单元格录入后要切换到A2单元格时用方向键就会需要击键很多次，用鼠标，手就要离开键盘。竖着录入同样有这样的问题。
图1
要加快录入，就要减少对鼠标的依赖和无效击键次数。下面介绍的方法可以有效得减少录入数据时对鼠标的依赖和无效击键次数。用鼠标选中需要录入数据的单元格，显示为反白的单元格为活动单元格，见图2：
图2
然后开始录入数据，活动单元格录好后，如果选择横着录入数据就一直按键盘上的―Tab‖键切换到下一个单元格；如果选择竖着录入数据就一直按键盘上的回车键―Enter‖来切换到下一个单元格。如果前面有录入错误的单元格，可以按键盘上的―Shift+Tab‖键或―Shift+Enter‖键返回，选择录入错误的单元格。
二、批量录入相同的数据和公式
用鼠标选中需要录入相同数据或公式的单元格，在编辑栏中输入数据或公式，然后按键盘上的―Ctrl+Enter‖键即可。以录入相同公式为例，选中批量录入公式的单元格，然后在编辑栏中输入活动单元格所对应的公式，按下键盘上的―Ctrl+Enter‖键。见图3、图4：
图3
图4
教你设计随心所欲查看数据之图表
传统的图表设计方式往往在多数据系列时查看起来比较费力。
比如利用以下数据生成图表（见图1和图2）。
图1 学生成绩表
图2 成绩表图表
可以看到，当数据系列较多时图表显示比较拥挤，且数字会产生重叠。若班级更多则更不利于查看了。
本文教你设计一种动态改变数据显示系列之方法。即可以通过单元格下拉菜单控制图表显示内容，选择―一班‖则图表中只显示一班的成绩状况图，选择―三班‖则只显示三班的成绩状况图，若选择―全部显示‖则出现所有班级的数据图。是否心动了？那么跟着我操作吧！
步骤1.建立单元格下拉菜单
1.选择单元格G1，点击菜单―数据‖|―在效性‖|―设置‖；
2.在有效性条件之―允许‖处选择―序列‖；
3.在―来源‖处输入―全部显示,一班,二班,三班,四班‖，(见图3)
图3 数据有效性设置
步骤2.建立数据辅助区
1.将标题行复制到B8：F8
2.在A9输入公式：=IF(OR($G$1="全部显示",$G$1=$A2),A2,IF(COLUMN()=1,"",-100))
公式含义：OR是逻辑函数，此处表示只要有一个条件成立则返且TRUE；COLUMN（）可返回当前列号；
与条件函数IF套用后，本公式即可使G1单元格数据变化时辅助区数据相应变化。当G1为―全部显示‖时，辅助区将引用数据源所有数据，当G1为―一班‖时，辅助区只引用一班的数据源，其它区域则显示-100。-100的作用是让数据系列产生在Y座标数据范围之外，从而不显示在图表中影响视线，达到隐藏作用。(结果见图4)
图4 辅助区结果
步骤3.生成基本图表
1.选中辅助区A8：F12，点击菜单―插入‖|―图表‖；
2.在选择图表类型窗口选择―堆积柱状图‖（见图5），然后点―完成‖，结果见图6；
图5 堆积柱状图 图6 初步生成的图表
步骤4.修饰图表
1.删除图例；
2.对座标轴点右键，选择―座标格式‖打开座标轴格式对话框，进入―刻度‖选项卡，将最小值改为0，最大值改为100，但要确保―最大值‖前打勾，再将主要和次要刻度改为20（见图7）；
图7 修改座标刻度
3.在图表区点右键，选择图表选项，输入图表标题，去掉网格线显示，将―数据标志‖选项卡中的―系列名称‖和―值‖打上勾，并在―分隔符‖处选择新行（见图8）
图8 修改数据标志
4.在G1单元格处选择―全部显示‖，然后选择―一班‖之柱形图，点击右键，选择―数据标志格式‖打开数据标志格式修改页，见图9。再选择右方―填充效果‖打开填充效果选项，在―渐变‖页中选择―双色‖，选择好自己喜欢的颜色后，将―底纹样式‖选择―垂直‖，并在―变形‖处选择第四个样式（见图10），最后点确定完成颜色修改。
图9 进入数据标志格式修改页
图10 修改填充效果
5.继续对其余三个班级成绩之柱形图进行颜色调整；
6.右击图表，选择―图表区格式‖，以前例方式修改其渐变色；
7.右击绘图区，选择―绘图区格式‖，以前例方式修改其渐变色。至此完成整个动态图表之设计。最后的效果如图11所示，大家可以在G1单元格进行不同的选择试试图表的变化。
图11 最终效果图
结束语：
图表设计并非一定是单一图表功能的运用，我们可以配合函数公式、数据有效性等工具对数据源进行后期处理并建立辅助区，最终生成图表，可以完成许多常规图表设计方式难以完成之效果，同时也能使你的图表更生动，更专业。特别是在多数据系列时，此方法犹为显示出其优势。
除了以上提到的公式和数据有效性工具能在多功能图表中占居优势外，―定义名称‖也是一个常常用于图表生成之辅助工具，定义动态数据引用的名称再运用于图表，可以使图表适应工作表数据的新增或者删除，而不必手动更新数据..........
对于利用名称进行动态数据引用的图表以后将陆继为大家介绍。
教您利用WPS表格检测输入数据的正确性
报表录入人员每天面对大量数据录入，难保不出现一次疏忽。不管数据重要性如何，报表错误总会给工作带来负面影响。 那么，除了录入人员自身输入时目测外还有更简洁高效之法么？ 答案是肯定的。WPS表格的―数据有效性‖功能可以为您提供便利，有效阻止无效输入，从而提升数据的准确性和制表速度。 ―数据有效性‖功能众多，限于篇幅，专为您讲解以下内容，大家可以举一反三，解决更多类似问题。
1.控制成绩表只能输入数值
2.限制手机号只能输入阿拉拍数字
3.限制指定位数只能是数字
4.限制用户有输入字母
5.限制输入电话号码和手机
6.根据前单元格字符决定是否输入
7.控制输入1---10000之间的质数
注：为了方便您学习，请先下载本文中所需的ET文档。
一:控制成绩表只能输入数值
大家知道,学生成绩是用数字表示的,且一般在0-100之间。那么只要掌握这个规律进对之进行相应的限制则成绩录入时则可防范出错（例如输入小数点变成了逗号不利用汇总或者数据超过100分等等）。
步骤1.先看一个简易的成绩表（见图1），先选中成绩区B2：B11，打开菜单―数据‖|―有效性‖。
图1
步骤2.在设置―数据有效性‖|―条件‖|―允许‖处选择―自定义‖；在公式处输入=ISNUMBER(B2)*AND(B2<=100,B2>=0)（见图2）。
公式含义：ISNUMBER(B2)表示必须是数字，AND(B2<=100,B2>=0)表示数据必须在0-100之间；两个条件中用*号连接表示必须同时满足两个条件。
图2
步骤3.在出错警告选项卡之样式选择―停止‖，―错误信息‖处输入― 你输入了非数字或者不在0--100范围中，
请重新输入。‖，点确定。
测试：在B2：B11区域输入大于100或者小于0或者―ABC‖等等数据看，系统将弹出提示并阻止您的输入。从而确保成绩录入的范围正确性（见图3）。
图3
二:限制手机号只能输入阿拉拍数字
某单元格用于存放手机号码，为了防范输入错误，同样可以利用数据有效性进行相应的约束。手机号码的特点是:每一个字符都是阿拉伯数字，不包括小数点,这与成绩分数不同；位数为11位。对手机号每个字符都进行检测，需要用到数组运算，而WPS2005表格的数据有效性公式中不支持数组运算，所以不能像前例一样直接在有效性公式窗口输入公式。而是借助辅助单元格，同时打开迭代计算来达到目的。
步骤1.打开菜单―工具‖|―选项‖|―重新计算‖，按以下方式设置（见图4）。
图4
步骤2.本例手机号码存于C8单元格，则将D8做为辅助单元格格，在其中输入公式：
=AND(NOT(ISERROR(FIND(MID(C8,ROW(INDIRECT("1:"&LEN(C8))),1),""))),LEN(C8)=11)
公式含义： NOT(ISERROR(FIND(MID(C8,ROW(INDIRECT("1:"&LEN(C8))),1),"")))表示每一位字符必须是阿拉伯数字；LEN(C8)=11表示必须为11位。当然也可自己再加条件，例如字符―13‖开始之类。
步骤3.开启菜单―数据‖|―有效性‖|―有效性条件‖，在―允许‖处选择―自定义‖；在公式处输入―=D8=TRUE‖，并在出错警告选项卡输入信息―你输入的不是阿拉伯数字或者不是11位,请重新输入!‖
测试：在单元格中输入一个错误的号码―I3512345566‖，已被系统阻止（见图5）。
图5
三：限制指定位数只能是数字
与前两例不同，本例可以自定义从某位数开始某位数结束限制为数字。
先看实例（见图6），起始位和结束位单元格可以随意定义，只要结束位不小于起始位即可。目的是设置完后手机型号单元格的指定位数只能是阿拉伯数字，否则阻止输入。
图6
步骤1.仍然开启迭代计算
步骤2.手机型号下面单元格做为辅助单元格，输入公式：
=OR(ISERROR(FIND(MID(B6,ROW(INDIRECT(C6&":"&D6)),1),"")))=FALSE
公式含义：利用数组运算查找指定字符是否位于―‖，有一个在范围之外则返回逻辑值FALSE.
步骤3.开启菜单―数据‖|―有效性‖|―有效性条件‖，在―允许‖处选择―自定义‖；在公式处输入=B7=TRUE；出错警告处之样式选择停止，再输入提示信息：―你输入的数据指定位数不是数字,请重新输入‖。
测试：在单元格输入―诺基亚-831‖，系统立即阻止（见图7）。
图7
四：限制用户有输入字母
在单元格中输入英文单词时，也可以用数据有效性进行限制。
步骤1.仍然开启迭代计算
步骤2.本例限制目标单元格为D3，以D4单元格为辅助，输入公式：
=COUNT(MATCH(CODE(UPPER(MID(D3,ROW(INDIRECT("1:"&LEN(D3))),1))),ROW(INDIRECT("65:90")),))=LEN(D3),见图8。
公式含义：利用数组运算逐一对单元格字符转换成ANSII字符集之数字代码，并计算其个数，再与单元格字符长度进行比较，若相同则表示符合要求。
图8
步骤3.开启菜单―数据‖|―有效性‖|―条件‖，在―允许‖处选择―自定义‖，在公式窗口输入=D4=TRUE，关添加阻止信息。
测试：在D3输入―l0ve‖(次字符为数字0)，系统立即阻止输入。
五：限制输入电话号码和手机号
在一个电话簿中，可以存放电话号码和手机号码，格式分别为************和135****5678.利用数据有效性仍然可以有效性的进行检测，这两种格式以外的数据阻止输入。先看看工作表数据（见图9）。
图9
步骤1.选中B2：B11，将之单元格格式设为―文本‖。
步骤2.开启菜单―数据‖|―有效性‖|―有效性条件‖，在―允许‖处选择―自定义‖；在公式处输入：=OR((LEN(B2)=11)*ISNUMBER(--B2)*LEFT(B2,2)=13,(LEN(B2)=12)*(MID(B2,5,1)="-"))
公式含义：(LEN(B2)=11)*ISNUMBER(--B2)*LEFT(B2,2)=13用于限制手机号码：(LEN(B2)=11)表示必须是11位，ISNUMBER(--B2)表示必须是数字，LEFT(B2,2)表示以13开头（可以自行修改）；(LEN(B2)=12)*(MID(B2,5,1)="-")用于限制电话号码必须是12位，且第5位是―-‖。
步骤3.设置出错时之警告信息：―你输入的不是正确的手机或者电话号码,请重新输入!‖。
测试：在区域任意单元格输入10位数字1351234567试试，立即被系统阻止（见10）。
图10
六：根据前单元格字符决定是否输入
说明:为了表示对少数民族学生的优待,根据学校提供的学生学习期间操行分进行高考加分.范围在1到20分之间，汉族学生不能加分。用数据有效性对此类事件也可以进行有效性检测。
先看看单元格数据（见图11）。
图11
步骤1.选中D2：D11，打开菜单―数据‖|―有效性‖|―有交性条件‖，选择自定义。
步骤2.在公式处输入=(C2<>"汉族")*(D2>=1)*(D2<=20)。
公式含义：前单元格非汉族且大于等于1、小于等于20.
步骤3.在出错警告处输入信息―该生非少数民族或者加分不在1-20分以内.请重新输入。‖
测试：在汉族学生后面输入任意字符或者在其它民族学生之加分单元格格输入21，系统立即阻止输入（见图12）。
图12
七：控制输入1---10000之间的质数
学校常常需要计算质数（质数即只能被除1和自身整除之数字）。
本例则限制单元格只能输入1---10000之间之数字且必须是质数。
步骤1.本例中限制对象为A2，选中单元格A2（见图13）。
图13
步骤2.打开菜单―数据‖|―有效性‖|―有交性条件‖，选择自定义。在公式处输入：
=AND(B2<>1,B2<=10000,OR(B2<4,PRODUCT(MOD(B2,ROW(INDIRECT("2:"&INT(B2^0.5)))))))
步骤3.在出错警告处样在式选择停止，输入信息：―您输入的不是质数或者超过10000，请重新输入！‖
测试：在B2输入1、4、10001等等数据时，系统立即阻止输入。
结语：数据有效性有一个强大的工具，将它配合函数公式能产生很大的作用，除上述数据控制外，还具有以下功能：
限制指定数值大小的整数；
限制指定字符长度的整数；
限制指定大小的小数；
产生下拉菜单；
限制指定范围的日期和时间；
限制指定长度字符；
限制输入指定姓氏之人名；
限制输入指定省下所属市名；
达成选择时提示；等等等等。
其中最大功能在于自定义允许条件为自定义，它可以配合函数产生无穷的变化，达成您各种需求。数据有效性也有它自身限制，使用时需要注意。即它只自限制手动输入字符，无法防范粘贴数据。所以对需要限制输入字符之单元格只能手动输入，否则会删除有效性信息。
解读居民身份证号码
本次内容简介：解读居民身份证号码
居民身份证号码是从事人事管理方面的人员经常接触到的一种特殊数据，原为15位，在21世纪以后都统一升级为18位，其编码规则按排列顺序从左至右依次为：
15位：6位数字地址码，6位数字出生日期码，3位数字顺序码。
18位：6位数字地址码，8位数字出生日期码，3位数字顺序码和1位数字校验码。
下面将向用户介绍在WPS表格中如何进行身份证数据的录入和利用身份证号码进行相关操作，包括以下内容：
1.身份证数据的录入； 2.身份证号码位数的转换；
3.从身份证号码中提取生日；4.从身份证号码中提取性别；
1.身份证号码的输入
居民身份证号码分别存在15位和18位数字组合（除部分身份证号码以X结尾），那么身份证号码的录入也经常使用户产生困惑。因此提供以下两种方法供用户进行数据录入：
以文本方式录入：首先，设置单元格格式为―文本‖，然后进行身份证号码的录入。
前置单撇号进行录入：在输入身份证号码时，先输入单撇号―'‖，再输入身份证号即可，如：在单元格中输入：'2310123。
注意：以上两种方法同样可以用于如：帐号、产品编号等长数值的数据录入。
已经录入数据的修改：
如果用户直接输入身份证号码，可能用户看到的是以科学计数法来显示的数值，因为在WPS表格中，整数数值仅能保留15位有效数字，而且当用户输入超过11位数值时，系统自动以科学计数法来进行显示。
因此对于这种情况，分别有两种办法来解决：
1.已经输入的15位身份证：用户需要重新在单元格格式中进行自定义设置：0，身份证可以完整显示。
2.已经输入的18位身份证：由于整数数值仅能保留15位有效数字，系统除了显示成科学计数法以外，原来录入的数据系统只能保留前15位有效数字，其他3位数字以0显示，数据精度已经丢失。因此这种数据只能重新录入。
2.15位与18位身份证的相互转换：
进入21世纪后，公安局新颁发的居民身份证都是18位。某些时候用户如果需要将15位身份证和18位身份证位数进行转换（或进行校验），可以使用以下两种方法：
15位转换为18位：
如：A2单元格为某一15位身份证号码，B2的转换的数组公式为：
{=REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)}
利用这个公式的原理，用户还可以对18位身份证进行校验，只需要将公式中―REPLACE(A2,7,,19)‖修改为―LEFT(A2,17)‖即可。
18位转换为15位:
如：A2单元格为某一18位身份证号码，B2的转换公式为：
=REPLACE(LEFT(A2,17),7,2,"")
3.从身份证号码中提取信息
人事部门经常需要从身份证号码中提取相关的人员信息，如：出生日期、性别等信息。下面分别对这两种信息的提取进行介绍：
提取出生日期:
如：A2单元格为某一身份证号码（18位或15位），下面的两个公式将可以提取出文本型日期：
=TEXT(MID(A2,7,IF(LEN(A2)=15,6,8)),"0-00-00")
=TEXT(MID(A2,7,6+(LEN(A2)=15)*2),"0-00-00")
如果用户希望将提取出来的数据转换为真正的日期，则需要对数据进行转换，常用的方法是：
=--TEXT(MID(A2,7,6+(LEN(A2)=15)*2),"0-00-00")
该公式的最前面加上―两个负号‖，在WPS表格中通常称为―减负运算‖，目的是将文本型数据转换为数值（由于日期型数据为特殊的数值，用户只需要设置单元格格式为日期格式即可）。
提取人员性别:
在身份证号码中，身份证顺序码的最后一位数字的奇偶性可以识别用户的性别，下面的公式将通过MOD函数取得用户的性别。
如：A2单元格为某一身份证号码（18位或15位），提取性别的公式为：
=IF(MOD(MID(A1,IF(LEN(A1)=15,15,17),1),2),"男","女")
=IF(MOD(RIGHT(LEFT(A1,17)),2),"男","女")
小结：
1.身份证号码的输入技巧，该技巧对录入帐号、产品编号等长数值的数据同样适用。
方法1：先设置单元格格式为―文本‖格式，再进行输入；
方法2：在输入时，先输入单撇号，再输入身份证号码；
2.用户可以通过函数公式对15位和18位身份证号码进行转换和校验。
3.用户可以通过函数公式从身份证号码中提取人员出生日期、人员性别等信息。
适用范围:
适用于WPS Office 2005及以上版本。
利用条件函数，自动测定服装号型
要组织党政领导过军事日了，需要为每一名参加活动的领导购买一套迷彩服。由于迷彩服是按身高、胸围、腰围来判断号型的，但要为每一名领导测量准确有数据却有着很大的难度。由于想出个变通的办法，即让各位领导自报身高和体重，再依据身高、体重来推算服装号型。
迷彩服的―号‖直接与身高挂钩，身高182以上为五号，175-182为四号，168-175为三号，160-168为二号，160以下为一号。而―型‖则比较繁杂，如果简单地从直观上来判断，则可以认为标准身材为3型，偏瘦为2型，很瘦为1型；偏胖为4型，很胖为5型。
从网上查询得知，判断人的体形是否标准，可以采用BMI(Body Mass Index)参数来判断。BMI=体重（公斤）/（身高（米）*身高（米）），BMI值在18-25之间为标准体型，大于25为轻度肥胖，大于35为中度以上肥胖；BMI小于18为轻度瘦弱，小于13则为中度以上瘦弱。根据这个标准，我们套用迷彩服号型的1-5型。
建新一个WPS 表格文档，输入单位、姓名、身高、体重鞋码等数据（如图1）：
图1
在F1、G1、H1单元格分别输入―号‖、―BMI‖、―型‖字样，并在F2单元格输入―=IF(C2>182,"五号",IF(C2>175,"四号",IF(C2>168,"三号",IF(C2>160,"二号","一号"))))‖（不含最外部引号，内部所有标点符号均为半角符号，下同）；，在G2单元格输入―=D2/(C2/100)^2‖，在H2单元格输入―=IF(G2>35,"5型",IF(G2>25,"4型",IF(G2>18,"3型",IF(G2>13,"2型","1型"))))‖。
此时，F2、G2、H2在个单元格已经能显示服装的号、BMI、型等信息了，下而，再在I2单元格输入―=CONCATENATE(F2,H2)‖把―号‖和―型‖两列数列连接起来，则完整地显示为―×号×型‖了！连续选中F2-I2单元格，将鼠标指针置于I2单元格右下方，当鼠标指针变为十字型添充柄时，向下拖动鼠标到最末记录所在行，则所有的号型信息全部统计好了（图2）:
图2
如果你觉得F1到H1三列数据没有必要显示的话，可以选中这三类，右击鼠标，选择―隐藏‖即可隐藏此三类，而数据的准确性却不受影响。
利用邮件合并快速打印荣誉证书
―七·一‖到了。单位表彰模范（优秀）共产党员时，需要制作一批荣誉证书。证书上的姓名一般采用手工填写或打印。如果采用打印方法，通常是在每张证书上填写姓名后即按打印。若表彰人员众多，则如此打印比较繁琐。我们可以利用WPS文字的邮件合并功能，结合WPS表格（ET），一次性打印所有证书。
1、制作数据源文件。
在ET中输入字段：姓名，并输入所有表彰人员姓名。保存文件（假设文件名为―表彰.ET‖），数据源制作完毕。如图1
图1
2、在WPS文字中制作文件模板。如图2
图2
3、点击菜单：―工具→邮件合并工具栏‖，打开数据源（上面保存的文件―表彰.ET‖），与包含表彰人员的ET表格进行连接。如图3
图3
4、在文件模板―同志‖前插入合并域，选择域名称―姓名‖。点击―查看合并数据‖图标际姓名。如图4
，可切换域与实
图4
5、点击邮件合并工具栏上的―合并到打印机‖，选择全部合并记录，确定即可一次性打印所有证书。如图5、图6
图5
图6
妙用WPS表格2005完成新生分班
新学期开始前，学校都要对新生进行分班。去年8月底，我意外地接到校长的电话，抽调我参与初一新生的分班工作。尽管是首次参加此项工作，但凭着对办公软件的熟练使用，我还是利用WPS2005表格迅速而圆满地完成了任务，让学校几位领导刮目相看。怀瑾握瑜，不敢自专，现将具体操作过程介绍如下。
第一步：听取要求、领会精神
校长对初一新生的基本情况做了简单介绍：新生共371人，其中男生200人，女生171人。分班要求如下：1、新生共分8个班，其中1－6班为普通班，7、8班为重点班；2、要求各班人数尽可能相同，且每班的男女生比例相当；3、两个重点班每班46人，要将男女生中的前30名（共60名学生）平均分入两个重点班。4、两个重点班其余16名学生的成绩应与普通班前16名学生的成绩大体相当。5、两个重点班之间、6个普通班之间学生成绩及各个分数段人数基本持平。6、按要求对部分关系户子女进行照顾，关系户子女大致可分为以下三种情况：第一种，点名要求进入某教师所带的重点班；第二种，进入任何一个重点班均可；第三种，如果按成绩排进不了重点班，则进入某教师所带的普通班。
明确了分班要求以后，我打开工作薄，在―总分‖单元格右侧的―I2‖单元格内输入―备注‖，并对照收集到的信息，将关系户子女的情况输入到与该生信息所对应的单元格内（如图1）。
图1
第二步：计算总分、排出名次
要想按要求完成分班任务，需要先将每位新生的总分计算出来，并按照总分进行排名，操作如下：
1、用鼠标单击―H3‖单元格，再单击常用工具栏上的―自动求和‖命令，你会发现在编辑栏里已经自动出现了相应公式―=SUM(E3:G3)‖（如图2），敲回车键确定后，第一位学生的总分已经被计算出来了。
图2
2、将鼠标移到―H3‖单元格右下角的填充柄（该单元格右下方的黑色小方块）上（如图3），当鼠标指针变为十字形时按住鼠标左健并向下拖动到与最后一位新生总分对应的单元格，松开鼠标以后，你会发现所有新生的总分都已经被计算出来了。
图3
3、用鼠标单击―H2‖单元格，再单击常用工具栏上的―降序排序‖命令。瞧，全体新生的总分排序已经完成（如图4），不过是男女生混合大排名。
图4
4、接着用鼠标单击―D2‖单元格，再单击常用工具栏上的―降序排序‖命令。瞧，这一次是先按照总分高低对女生排序，再按照总分高低对男生排序（如图5）。
图5
第三步：合理轮回、初定班级
先在―J2‖单元格内输入―班级‖，然后再进行如下操作：
1、由于男女生的前30名要均衡分入两个重点班，所以我们先在―J2‖单元格下面依次输入―7班、8班、8班、7班……‖，将女生中的前30名分入两个重点班（如图6），再拖动右侧的滚动条，找到与男生前30名对应的单元格，依次输入―8班、7班、7班、8班……‖，将男生中的前30名也分入两个重点班。
图6
2、现在两个重点班都还差16名学生，按照校长所讲的分班要求，这16名学生与其他班级前16名学生的成
绩应大体相当，所以我们接下来执行以下操作：在―J33‖（女生第31名所对应的单元格）到―J96‖单元格中依次输入―8班、7班、6班、5班、4班、3班、2班、1班、1班、2班、3班、4班、5班、6班、7班、8班……‖将这64名女生均匀分入8个班级；再在―J204‖（男生第31名所对应的单元格）到―J267‖单元格中依次输入―1班、2班、3班、4班、5班、6班、7班、8班、8班、7班、6班、5班、4班、3班、2班、1班……‖将这64名男生均匀分入8个班级。
3、现在两个重点班学生已经初步分配完毕，但6个普通班均只分配了16名学生，尚需将其余学生均衡地分入这6个普通班中。我们继续执行如下操作：在―J97‖到―J173‖（最后一名女生所对应的单元格）单元格中依次输入―1班、2班、3班、4班、5班、6班、6班、5班、4班、3班、2班、1班……‖将剩余的77名女生均匀分入这6个班级；再在―J268‖到―J373‖（最后一名男生所对应的单元格）单元格中依次输入―6班、5班、4班、3班、2班、1班、1班、2班、3班、4班、5班、6班……‖将剩余的106名男生也均匀分入这6个班级。
4、拖动鼠标选中第2行的相应单元格，再依次单击―数据‖菜单里的―筛选／自动筛选‖命令（如图7），该行各单元格右侧均出现一个箭头（如图8）；单击―班级‖右侧的箭头，在弹出的下拉菜单中单击―1班‖，该班级所有学生的名单就显示出来了，我们只要将这些学生的名单复制下来，插入一个新的工作表并执行粘贴操作，再重复上述操作，便可初步完成分班工作。
图7
图8
但是，由于我们在这轮分班过程中没有考虑关系户子女的问题，所以接下来还需对个别学生的班级进行调整。
第四步：手工调整、确定分班
出于种种原因，学校在分班过程中，需要考虑部分关系户的要求。个别关系户的要求在刚才的分班过程中已经得到了满足（如女生4），就无需考虑了。对于其他关系户，只需根据实际情况进行调整即可。以男生31为例（该生要求进入重点班，却被分进了6班，需要进行适当调整），调整思路如下：
1、察看该生基本信息，为方便读者比较，现将该生信息列表如下（表一）：
姓名
男生31
80 25 86 191
2、了解重点班中与该生性别相同、且总分最为接近的学生情况，现将相关学生信息列表如下（表二）：
姓名 性别 语文 数学 英语 总分 备注
男生76
男生153
男生99
男
男
男
78 40 73 191
7班
7班
8班
85 36 67 188
88 59 43 190
3、如果不需考虑其他因素，只需将男生31与表二中任一学生进行对调即可。
第五步：分析结果、汇总上报
分班工作已经基本结束，究竟能否满足校领导的要求，我们可以通过数字来说明。
1、插入一个新的工作表，将1－8班学生的基本信息复制到该工作表中，拖动鼠标选中所有学生的基本信息（如图9）。
性别
男
语文 数学 英语 总分 备注
进重点班
图9
图10
2、单击―数据‖菜单里的―分类汇总‖命令，在弹出的―分类汇总‖对话框中，设置―分类字段‖为―班级‖，―汇总方式‖为―平均值‖，―选定汇总项‖为―语文、数学、英语、总分‖，保持其他选项不变（如图10），单击―确定‖按钮；如果细心的话，此时您会发现在工作表的左侧会出现分级显示的控制按钮（如图11），单击标有―2‖的符号按钮，使工作表中的数据处于第二显示级别，您可以看到8个班各学科及总分的平均值（如图12），加以比较后，你会发现1－6班的各类成绩之间、7－8班的各类成绩之间虽然存在差距，但这种差距是非常小的，分班结果基本达到了校领导的要求。
图11
图12
3、请校领导审阅后，如果没有异议，即可正式打印，张贴公示。
读者朋友，敬请一试。
明明白白还贷，用WPS表格轻松制作还款明细表
房子交接了！同事小张兴奋地告诉我，并请我吃饭。饭桌上小张告诉我，房子是贷了一部分款买的，以后每月都得上银行缴纳固定的还款。是呀，现在房价这么高，不贷款很难买得起。小张又问我每月还的钱具体是怎么算出来，包括哪些部分，能不能打印个详细的还款表，好让他心中有数，毕竟这不是小数目。朋友之托，当竭尽全力，当晚我查了查资料，并用WPS表格打印出一份详细的还款明细表。第二天一早交到小张手里，小张佩服地直翘大拇指说，专业会计就是行！
象小张这种贷款专业称为按揭贷款，即以房产作为抵押的贷款。每月还款又称为月供。目前银行多采用等额本息法，利息是递减的。计算公式复杂，每月还本付息金额 = [ 本金 × 月利率 ×（1+月利率）^贷款月数 ] / [（1+月利率）^还款月数 - 1], 其中：每月利息 = 剩余本金 * 贷款月利率 ,每月本金 = 每月月供额 –
每月利息。注意上面的―＾‖是幂符号，―/‖是除以。
由于其中涉及N次方运算，手工无法实现，一般计算器也难以计算。但是用WPS表格可以轻松实现，因为有专门的函数。下面介绍是如何实现的
1、打开WPS表格，新建一个空白表格，在A1、B1、C1、D1分别输入―期数‖、―本金‖、―利息‖、―月供‖。
2、计算月供：在D2中输入―=PMT(0.0051,120,100000)‖， 拖动填充柄向下复制到D121（图1）。
图1
3、计算本金：在A2输入―1‖，拖动填充柄向下复制到―120‖。在B2输入公式―=PPMT(0.0051,ROW()-1,120,100000)‖ ，拖动填充柄向下复制公式到 B121（图2）。
图2
4、计算利息：在C2输入公式―=B2-D2‖，拖动填充柄向下复制公式到C121（图3）。
图3
这样一个详细的还款表就做成了，不仅能制作还款表，也能计算年金等投资。
公式讲解：
1、函数PMT基于固定利率及等额分期付款方式，返回贷款的每期付款额。
PMT(0.0051,120,100000)，0.0051为月利率，120为贷款期限即120月（10年），100000为贷款额。
2、函数PPMT基于固定利率及等额分期付款方式，返回投资在某一给定期间内的本金偿还额。
PPMT(0.0051,ROW()-1,120,100000)，0.0051为月利率，ROW()-1为期数,ROW()为行数，借助行数算出期数，120为贷款期限即120月（10年），100000为贷款额。
奇思妙想，用WPS表格帮哈韩MM背单词
办公室的MM是狂热的哈韩族，不仅热播的韩剧每集必看，流行的韩国音乐更是视若珍宝。更令人叫奇的竟在网上交了几个韩国朋友。语言不通成了交流的重大障碍，哈韩MM便下定决心学习韩语。为了检验学习的效果，MM一有空便默写单词，但是手工对照很麻烦。便问我能否做个电子单词默写本，我经过研究后，终于用WPS表格打造出电子单词默写本，对错自动判断。具体操作是这样的：
一、打开WPS表格，建立一个新表格，在A1、B1、C1、D1依次输入―单词‖、―词义‖、―默写‖、―判断‖。
二、在A列中输入要背的韩语单词，B列中输入对应的中文词义。
三、在D2中输入公式―＝IF(EXACT(A2,C2),"正确","错误")‖，拖动填充柄向下复制公式。
四、选中A列，用格式菜单下的―列→隐藏‖命令将A列隐藏。
至此，单词默写本基本上做好了（图1）。但是还有两点不够完美：
图1
1、C列为空白时，即没有默写时，也进行判断。可以把D2中的公式修改为
―=IF(ISBLANK(C2),"请写",IF(EXACT(A2,C2),"正确","错误"))‖，这样，没有默写时会提示―请写‖。
2、判断不够醒目，习惯上错误都以红字显示。
可以使用格式菜单下的条件格式把―错误‖以红字显示出来，选中D列，单击 格式菜单下的―条件格式‖，在―条件格式‖对话框中作如下图操作（图2）。
图2
这样一个完美的电子单词默写本就打造成功了（图3），需要添加单词时，取消A列隐藏添加即可。
图2
公式讲解： EXACT 函数是一个字符串比较函数，测试两个字符串是否完全相同。如果它们完全相同，则返回 TRUE；否则，返回 FALSE。 ISBLANK 函数是判断单元格是否为空白。公式―＝IF(EXACT(A2,C2),"正确","错误")‖意思是如果 A2、C2 的内容完全相同则显示"正确",否则显示"错误"。 公式=IF(ISBLANK(C2),"请写",IF(EXACT(A2,C2),"正确","错误"))‖加上了是否为空白单元格的判断，如果C2为空白则显示"请写"。
巧借WPS表格实现自动双色排版
双色排版，就是在同一版面内使用两种颜色的字体。这样做改变了单色排版的单调性，增加了内容的可读性。因为双色更醒目，也能有效地防止视觉疲劳，从而吸引更多的读者。现在很多出版物已采用。
WPS文字我们都知道是用来排版文章的，能否使用它实现双色排版呢？手工设置是可以的，但是这样的工作量是相当大的，对于大文章也是不现实的。使用宏当然能够实现，但对于初级用户是有难度的，而且个人版是没有宏的。那么如何实现呢？他山之石，可以攻玉。我们可以借用WPS表格轻松实现。
双色排版一般都是有规律地隔几行换用另一种颜色，基于这个特点我们可以用WPS表格2005新增的条件格式实现这一要求，下面就把制作过程简单介绍给大家。
1、建立一个空白表格，把需要设置的文本复制到这表格内，并加以调整。另外也可以在这个表格内直接输入文本。
2、选中文字区域，单击格式菜单， 在下拉菜单中选中―条件格式‖（如图1）：
图1
3、在随之出现的条件格式对话框左侧选择公式，在右栏中输入―＝MOD（TRUNC（(ROW（）－1）/5),2)=1‖(如图2):
图2
然后单击右下侧的―格式‖，在接着出现 单元格格式对话框中选择字体，并在颜色中设置所需的颜色（如图3）:
图3
4、单击条件格式对话框中的―添加‖，在条件2中输入公式"＝MOD（TRUNC（(ROW（）－1）/5),2)=0"，并设置另一种字体颜色（如图4）：
图4
5、这样，自动双色排版的文章就展现在我们面前了(如图5)：
图5
公式详解：MOD是整除，TRUNC是去尾取整，ROW()是行数。本例中设为每隔5行，两种颜色交替。根据需要可自由更改所隔行数与颜色数，公式中斜线后的5是行数，逗号后的2是颜色数。
巧用WPS表格的自动筛选功能进行学生成绩统计
做为学校的老师，每次考试结束都会迎来复杂、烦琐的学生成绩统计工作，自从电脑上安装了WPS Office
2005成绩统计工作轻松了许多，WPS表格的筛选功能可以帮助我轻松完成学生成绩统计。
图1 学生成绩表
下面就来看看WPS表格中的自动筛选功能是如何帮我快速完成成绩统计的。
WPS表格的自动筛选操作非常简单，选中表格中任意单元格，执行‗数据‘菜单中的‗筛选‘－‗自动筛选‘命令。此时，每一个列标题右侧均出现一个下拉按钮。
要求A：列出英语成绩为98分的学生成绩
操作步骤：点击列标题‗英语‘右侧下拉按钮，选择下拉菜单中的‗98‘即可。如图2：
图2 英语成绩为98分的学生成绩
注意，这时英语成绩不是98分的学生成绩都被隐藏起来，并不是被删掉了。自动筛选后的结果是只显示满足指定条件的行、把不满足指定条件的行隐藏起来。
如果需要显示全部，那么请执行‗数据‘菜单中的‗筛选‘－‗显示全部‘命令。
要求B：列出政治成绩在60分以上的学生成绩
操作步骤：点击列标题‗政治‘右侧下拉按钮，选择下拉菜单中的‗自定义‘，弹出‗自定义自动筛选方式‘对话框，选择‗大于‘，输入‗60‘后单击‗确定‘。如图3：
图3 自定义自动筛选方式
要求C：列出语文成绩大于等于60分并且小于85分的男生成绩
操作步骤：点击列标题‗语文‘右侧下拉按钮，选择下拉菜单中的‗自定义‘，弹出‗自定义自动筛选方式‘对话框，选择‗大于或等于‘，输入‗60‘，选择‗与‘的关系后，选择‗小于‘，输入‗85‘，如图4 ：单击确定。
图4 自定义自动筛选方式
点击列标题‗性别‘右侧下拉按钮，选择‗男‘，这时，只是鼠标轻轻点击几下，符合要求C的结果已被筛选出来。如图5：
图5 语文成绩大于等于60分并且小于85分的男生成绩
巧用WPS表格快速编排录入学号
新的一学期开始了，随着大量新生的入学，繁杂的学号编排录入工作也开始了。按照中国人的习惯，传统的做法是这样的：让每个学生把自己的姓氏笔画数报上来，然后根据学生的姓氏笔画数由少到多来排号。然后在这些号前加上入学年份、年级(系)、班级，如06年入学一年级二班都以―060102‖开头。接着就是大量的录入工作。这些工作不仅需要大量的人力，而且容易出错。有没有简单快捷的方法完成以上工作？有!用WPS2005表格让你又快又好地完成这些工作，和烦琐易错的工作说bye bye。
一、编排学号
1、选中我们已输入的学生姓名数据区域，单击数据下拉菜单后，单击排序，在排序选项卡中单击―选项‖
见图1：
图1
2、在排序选项菜选项卡中，在―方式‖一栏中选中―笔画排序‖图2：
图2
3、单击―确定‖
这样我们输入的学生姓名按笔画数由少到多排好了,见图3：
图3
二、学号录入
1、选中要录入学号的区域，单击右鍵，选中―设置单元格格式(F)‖见图4：
图4
2、在单元格格式选项卡中选中―数字‖，―分类‖选中―自定义‖，在―类型‖一栏中输入―"060102"00‖，单击―确定‖。图5：
图5
3、在A3中输入―1‖后，会自动变为―06010201‖。下面的学号可以这样自动填充：鼠标移到A3右下方，这时会出现一个―+‖符号，单击左鍵向下拖放。这样繁杂的学号编排录入工作就这 样轻松完成了，图6：
图6
参照此法我们也可以快速输入有共同特征的数据。
巧用WPS表格提取身份证个人信息
最近，单位需要上报人事局一份职工信息，其中既有个人身份证号，又有出生日期、性别等信息。由于人员众多，输入确实麻烦，而且容易出错，弄不好还得返工。能不能有个一个一劳永逸的办法，又快又准地录入出生日期、性别等个人信息呢？能！因为身份证号中包含这些个人信息，我们使用WPS表格可以轻松地提取些个人信息，以实现自动录入，从而又快又准地完成工作。
目前，我国的身份证号分为两种，一种为15位， 一种为18位。在15位的老版身份证中，第7到12位为出生日期数，最后一位为性别代码，偶数为女，奇数为男。18位的新身份证中第7到14位为出生日期数，倒数第2位为性别代码，同样偶数为女，奇数为男。
基于这个特点，我们可以用函数加以判断。如图1是我们已输入完身份证号的职工信息表，在输入身份证号时需注意，要把单元格格式设为文本型，或在所输身份证号前加一个单引号―'‖。
图1
1、提取出生日期
在D2中输入公式―=IF(LEN(C2)=15,TEXT(MID(C2,7,6),"1900年00月00日"),TEXT(MID(C2,7,8),"00年00月00日"))‖，拖动填充柄向下复制公式，这样就完成了出生日期的提取（如图2）：
图2
2、提取性别信息
在E2中输入公式―=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")‖，拖动填充柄向下复制公式，这样就完成了性别信息的提取（如图3）:
图3
公式详解：LEN是长度函数，MID是提取字符函数，TEXT是转换格式函数，MOD是整除函数，IF是判断函数。提取出生日期公式的意思是如果身份证号为15位，把从第7位起的6个字符转换为"1900年00月00日"的格式，否则（即为18位身份 证），把从第7位起的8个字符转换为"00年00月00日"的格式。提取性别信息公式的意思是如果身份证号为15位，把第15位和2整除，如果整除，显示为女，不能整除，显示为男；身份证号为18位，把第15位和2整除，如果整除，显示为女，不能整除，显示为男。另外，如果想把出生日期转换为真正的日期类型，只需在公式中在TEXT前加两个减号，这是减负运算，然后根据需要设置单元格具体的日期类型。
以上只是在身份证录入正确的情况下设置的，如果身份证号录入错误，如位数不对，日期信息不对（如月分数超过了12，日数出现2月有31号的情况），怎么办呢？还是留给大家思考吧！要学会举一反三哟。
巧用WPS表格为已有资料打印页号
我有一个朋友，是做招投标工作的，有许多资料都是复印件，需要插到文件中，现在需要把这里复印资
料重新排序，并打印上统一的页号，如果只是几页的话，当然用WPS文字就可以解决，但由于资料比较多，想一想，在WPS文字中插入几百个―分页符‖确实也是一件比较另人头痛的事。
于是想到用WPS表格来解决这一棘手问题。
打开WPS表格2005，在―名称框‖中直接输入―A10000‖，直接定位到一个比较靠后的单元格，在其中输入一些其实任意字符或数字。然后全选整张工作表，点击―文件－打印区域－设置打印区域‖，将整张工作表设置为打印区域。设置好打印区域后，点击―文件－页面设置‖调出页面设置对话框，设置好与资料一致的纸张类型，然后点选―页眉/页脚‖选项卡，在页脚中设置好页号格式（图1），点击确定退出。
图1
完成上述设置后，点击―打印‖按钮，在打印对话框中设置好要打印的页号范围（图2），按―确定‖按钮即可按要求在原有资料上打印页号了。
发布评论