电子表格 关于数据筛选.docx
《电子表格 关于数据筛选.docx》由会员分享，可在线阅读，更多相关《电子表格 关于数据筛选.docx（14页珍藏版）》请在冰点文库上搜索。
电子表格关于数据筛选
⒈快速定义工作簿格式
首先选定需要定义格式的工作簿范围，单击“格式”菜单的“样式”命令，打开“样式”对话框；然后从“样式名”列表框中选择合适的“样式”种类，从“样式包括”列表框中选择是否使用该种样式的数字、字体、对齐、边框、图案、保护等格式内容；单击“确定”按钮，关闭“样式”对话框，Excel工作簿的格式就会按照用户指定的样式发生变化，从而满足了用户快速、大批定义格式的要求。
不管是编程还是使用公式，都得将个人所得税的方法转化为数学公式，并且最好将这个公式化简，为以后工作减少困难。
以X代表你的应缴税（减去免税基数）的工薪收入（这里的个人所得税仅以工薪为例），Tax代表应缴所得税，那么：
当500＜X≤2000则TAX=（X-500）*10+500*5=>TAX=X*10-25 当2000＜X≤5000则TAX=（X-2000）*15+2000*10=>TAX=X*15-125 ．．．．．．
依此类推，通用公式为：
个人所得税=应缴税工薪收入*该范围税率-扣除数 在此,扣除数=应缴税工薪收入上一范围上限*该范围税率-上一范围扣除数 其实只有四个公式，即绿色背景处。
黄色背景处则为计算时输入数据的地方。
各处公式设置即说明如下：
E3：
=C3*D3-C3*D2+E2
E4-E10：
根据E3填充得到，或者拷贝E3粘贴得到
C15：
=IF（B15>$B$12,B15-$B$12,0）如果所得工薪大于不扣税基数，则应纳税工薪为工薪减去为零不扣税基数，否则，应纳税工薪零。
D15：
=VLOOKUP（C15,$C$2:
$C$10,1）查阅应纳税工薪属于哪个扣税范围。
E15：
=C15*VLOOKUP（D15,$C$2:
$E$10,2）-VLOOKUP（D15,$C$2:
$E$10,3）查阅该扣税范围扣税税率和应减的扣除数。
这里主要用到VLOOKUP函数，可查阅帮助获取更多信息。
C15，D15的公式可以合并到E15中，那样可读性会差很多，但表格会清晰一些。
合并后公式：
=IF（B15>$B$12,B15-$B$12,0）*VLOOKUP（VLOOKUP（IF（B15>$B$12,B15-$B$12,0）,$C$2:
$C$10,1）,$C$2:
$E$10,2）-VLOOKUP（VLOOKUP（IF（B15>$B$12,B15-$B$12,0）,$C$2:
$C$10,1）,$C$2:
$E$10,3）实际上是将公式中出现的C15，D15用其公式替代即可。
18.用EXCEL轻松处理学生成绩
期末考试结束后，主任要求班主任自已统计本班成绩，尽快上报教导处。
流程包括录入各科成绩→计算总分、平均分并排定名次→统计各科分数段人数、及格率、优秀率及综合指数→打印各种统计报表→制作各科统计分析图表等。
有了EXCEL，我们可用不着躬着身、驼着背、拿着计算器一个一个算着学生的成绩了！
我迅速地打开电脑，启动EXCEL2000，录入学生的考试成绩，如图1所示。
然后在J2单元格处输入公式"=sum（c2:
i2）",然后拖动填充柄向下填充，便得到了每人的总分。
接着在k2单元格处输入公式"=average（c2:
i2）"，然后拖动填充柄向下填充，便得到了每人的平均分。
平均分只需保留一位小数，多了没用。
所以选中第k列，用鼠标右键单击，从弹出的快捷菜单中选"设置单元格格式（F）…",如图2所示，在数字标签中选中"数值"，小数位数设置为1位。
下面按总分给学生排出名次。
在L2单元格处输入公式"RANK（J2,J$2:
J$77,0）",然后拖动填充柄向下填充，即可得到每人在班中的名次 说明：
此处排名次用到了RANK函数，它的语法为：
RANK（number,ref,order） 其中number为需要找到排位的数字。
Ref为包含一组数字的数组或引用。
Ref中的非数值型参数将被忽略。
Order为一数字，指明排位的方式。
·如果order为0或省略，MicrosoftExcel将ref当作按降序排列的数据清单进行排位。
·如果order不为零，MicrosoftExcel将ref当作按升序排列的数据清单进行排位。
最后，单击L1单元格，然后在“工具”菜单中选“排序”－＞“升序”，即可按照名次顺序显示各学生成绩。
另外，我们还希望把不及格的学科突出显示，最好用红色显示。
于是拖拉选择C2:
E78（即所有学生语、数、外三科成绩），然后执行"格式"菜单下"条件格式"命令，弹出"条件格式对话框"。
我们把条件设为小于72分的用红色显示（因为这三科每科总分为120分），点击"格式"按钮，把颜色设为红色。
再按"确定"按钮。
然后用同样的方法把理、化、政、历四科小于60分的也用红色显示（因为这四科每科总分为100分）。
下面我们来统计各科的分数段以及及格率、优生率、综合指数等。
下面我们来统计各科的分数段以及及格率、优生率、综合指数等。
（1）60分以下人数：
在C78单元格处输入公式"=COUNTIF（C2:
C77,"<60"）"，拖动填充柄向右填充至I78单元格处；
（2）60分~69分人数：
在C79单元格处输入公式"=COUNTIF（C2:
C77,">=60"）-COUNTIF（C2:
C77,">=70"）"，拖动填充柄向右填充； （3）70分~79分人数：
在C80单元格处输入公式"=COUNTIF（C2:
C77,">=70"）-COUNTIF（C2:
C77,">=80"）"，拖动填充柄向右填充；（4）80分~89分人数：
在C81单元格处输入公式"=COUNTIF（C2:
C77,">=80"）-COUNTIF（C2:
C77,">=90"）"，拖动填充柄向右填充； （5）90分以上人数：
在C82单元格处输入公式"=COUNTIF（C2:
C77,">=90"）"，拖动填充柄向右填充； （6）平均分：
在C83单元格处输入公式"=AVERAGE（C2:
C77）",拖动填充柄向右填充至I83； （7）最高分：
在C84单元格处输入公式"=MAX（C2:
C77）",拖动填充柄向右填充至I84； （8）低分率：
是指各科40分以下人数与总人数的比值。
在C85单元格处输入公式"=COUNTIF（C2:
C77,"<=40"）/COUNT（C2:
C77）*100"，拖动填充柄向右填充至I85； （9）及格率：
语、数、外三科及格分为72分，所以在C86单元格处输入公式"=（COUNTIF（C2:
C77,">=72"）/COUNT（C2:
C77））*100"，并拖动填充柄向右填充至E86；而理、化、政、历等四科及格分60分，所以在F86单元格处输入公式"=（COUNTIF（F2:
F77,">=60"）/COUNT（F2:
F77））*100"，并拖动填充柄向右填充至I86；（10）优生率：
语、数、外三科96分以上为优生，所以在C87单元格处输入公式"=（COUNTIF（C2:
C77,">=96"）/COUNT（C2:
C77））*100"，拖动填充柄向右填充至E87；理、化、政、历等四科80分以上为优生，所以在F87单元格处输入公式"=（COUNTIF（F2:
F77,">=80"）/COUNT（F2:
F77））*100"，拖动填充柄向右填充至I87处；（11）综合指数：
我们学校的综合指数的计算公式为z=[（1+优生率－低分率）/2+及格率+平均分/该科总分]/3。
所以在C88单元格处输入公式"=（（1+C87/100-C85/100）/2+C86/100+C83/120）/3"，拖动填充柄向右填充至E88；在F88单元格处输入公式"=（（1+F87/100-F85/100）/2+F86/100+F83/100）/3"，拖动填充柄向右填充至I88。
对了，为了让别人对各科的分数段有一个较直观的认识，可以考虑采用图表。
单击“插入”菜单中“图表”命令，弹出“图表向导”对话框，在“图表类型”列表框中选择一种图型，如“饼图”，单击“下一步”，单击“数据区域”文本框右边的压缩列表框，拖拉选择B78：
C82，再次点击该压缩列表框；单击“下一步”，输入图表标题，如“高一
（1）班语文成绩分析图”；单击“下一步”，再单击“完成”。
如图4所示。
其它各科同样处理，但在拖拉选择数据区域时，因为是不连续的区域，所以要按住“Ctrl”键。
好！
一切OK！
且慢！
为了以后的考试中不再重复上述繁琐的工作，最好把上述工作表另存为一个模板。
于是我把上述工作表复制一份到另一工作簿中，然后删掉所有学生的单科成绩（即表中C2:
I77部分），执行"文件"菜单中的"另存为"命令，在"保存类型"下拉列表框中选"模板（*.xlt）"，把它保存为一个模板文件，这下可以一劳永逸了。
19.用EXCEL轻松准备考前工作
大考在即，主任要求“考务工作必须电子化”，为了万无一失，还特意提供给班主任一份考务工作流程图：
考前：
考场编排→打印单科成绩册→打印考场记录单→打印准考证号 考后：
录入各科成绩→计算总分、平均分并排定名次→统计各科分数段人数、及格率、优秀率及综合指数→打印各种统计报表→制作各科统计分析图表 既然任务已经明确，先把考前的准备工作做好吧！
具体工作包括：
考场编排→打印单科成绩册→打印考场记录单→打印准考证号
1、单科成绩册的编制和准考证号的自动编制
（1）我负责的班级这次考试共7门课程，为了区分科目，只需在EXCEL表头处设置“科目”、“班级”和“任课教师姓名”等。
考生的姓名可以从学生学籍表中提取，考号如何编制和处理呢？
为了以后登分和查找方便，考号基本按学籍号顺序把整个年级的考号放在一起编码。
编码由两部分组成：
①考场编号（三位数字），②班内顺序号（三位数字）。
为了加快录入速度，分别把它们放在两列中，这样可以分别进行填充。
（2）新建工作表“考场编号”（如图1），从学生学籍表中复制“姓名”列置于该表A列，首先设置B（准考证号）、C（班内编号）、D（考场编号）列的数据格式为文本方式。
（3）在B2单元格输入公式“=D2&C2”（其中的“&”为连接运算，将“考场编号”和“班内编号”连接成一个字符串），用鼠标选中B2单元格，将鼠标指向该区域右下角的填充柄，双击鼠标（这可是本人总结出来的最快的填充方法，下称“双击填充法”）即可将该公式自动填充至最后一个考生（因为此时还没有在C、D列输入数据，暂时B列中数据也没有出现）。
在C2单元格中输入“001”，C3单元格输入“002”，用鼠标拖动区域C2：
C3，将鼠标指向该区域右下角的填充柄，用上述“双击填充大法”即可对全班学生顺序编号。
然后每隔30人（一个标准考场）插入一空行，在D2和D3单元格中全部输入“001”，同样用“双击填充大法”迅速将第一考场的编号全部填充。
此时B2：
B31单元格已经全部自动填上了考生的准考证号。
对第二考场，只需在D33和D34格中输入“002”，并向下填充即可。
2、打印考场记录单
考场记录单是供监考教师监考时核对考生数目和身份时用的。
在上述“考场编号”工作表中，利用Excel的“分类汇总”功能，可以快速方便的达到这一目的。
操作步骤如下：
（1）在表中单击任一数据单元格，在“数据”菜单中，单击“分类汇总”命令。
（2）在“分类字段”下拉列表框中，单击需要用来分类汇总的数据列（“考场编号”列）。
（3）在“汇总方式”下拉列表框中，选择“计数”。
（4）在“选定汇总项（可有多个）”框中，复选“考场编号”框。
（5）复选“每组数据分页”选项，以便每个考场单打印一页。
其他两项“替换当前分类汇总”和“汇兑结果显示在数据下方”可根据情况选定。
（6）单击[确定]，考场记录单就已经做好了
（7）为了使每一页都打印同样的表头：
“2001年期末考试第X考场考场单”，单击菜单“文件→页面设置→页眉/页脚→自定义页眉（C）……”，进行相关设置。
为了更加美观，在“页面设置”的“页边距”窗口复选“水平居中”和“垂直居中”，并定义好纸张的大小。
说明：
（1）“分类汇总”前，必须按“考场编号”列排序，本例中已经是排好顺序的。
（2）对“分类汇总结”的结果不满意时，可以清除“分类汇总”，Excel将同时清除分级显示和插入“分类汇总”时产生的所有自动分页符。
方法是：
单击任一单元格，在“数据”菜单中，选择“分类汇总”命令，单击[全部删除]按钮。
3、打印准考证号码条
准考证号码条一般粘贴在课桌左上角，供考生寻找自己的座位用，过去主要靠手工抄写的方法完成。
这里我用Word的“邮件合并”功能巧妙地完成了这一任务。
方法如下：
启动Word2000，执行“工具”菜单下的“邮件合并”命令，弹出“邮件合并帮助器”，首先在当前窗口创建一个邮件标签。
依次点击“创建→邮件标签→活动窗口”，然后点击[获取数据]按钮，在下拉列表中选“打开数据源”，在文件类型列表框中把文件类型改为“MSExcel工作簿”，打开刚才“考场编号”工作表，按[确定]按钮。
接着设置主文档，点击[新建标签]按钮，弹出如图3所示“新建自定义标签”对话框。
在“标签名称”文本框中填上“座位号”，在“页面尺寸”下拉列表框中选“A4横向”。
按[确定]按钮，出现“创建标签”对话框。
点击“插入合并域”，插入“准考证号”、“姓名”两个域，按[确定]按钮，最后按[合并]按钮。
好，现在的考前准备工作基本完成，只等考试成绩出来了！
20.Excel的图表功能
Excel的图表转换功能具有更大的吸引力。
Excel能够根据工作表中的数据创建图表（即将行、列数据转换成有意义的图象）。
图表能帮助辨认数据变化的趋势，而在工作表中就很难辨别。
我们在Excel下先简单地制作一个记录正弦函数y=sin（x-a）数据的工作表：
x（度）
y1（a=0度）
y2（a=30度）
y3（a=60度）
0
0
-0.5
-o.866
30
-0.5
0
-0,5
60
0.866
0.5
0
90
1
0.866
0.5
120
0.866
1
0.866
150
0.5
0.866
1
180
0
0.5
0.866
210
-0.5
0
0.5
240
-0.866
-0.5
0
270
-1
-0.866
-0.5
300
-0.866
-1
-0.866
330
-0.5
-0.866
-1
360
0
-0.5
-0.866
然后根据工作表中的部分数据制作正弦曲线y2。
其步骤如下：
1．通过拖动鼠标选中x栏的数据。
按住Ctrl键不放，拖动鼠标再选中y2栏的数据。
注意，栏目标题不要选，因为它们不是数据。
2．选择插入|图表菜单项，或者直接点击工具栏?
quot;图表向导"按钮，调出图表类型窗口。
在该窗口的标准类型页面，列出了柱形图、条形图、折线图等图表类型可供选择。
这些类型大多适用于一维数据，对于二维数据表，如果想转换成折线图，不能直接选折线图，而应先选xy散点图为主类型，然后在子图表类型中选折线散点图或平滑线散点图。
3．按"下一步"按钮，进入图表源数据窗口。
此时，Excel已根据你所选的数据将正弦曲线y2显示在窗口中。
4．按"下一步"按钮，进入图表选项窗口。
在该窗口标题页，你可以给图表标题框输入：
正弦函数y=sin（x-a），给数值（x）轴框输入：
x（度），给数值（y）轴框输入：
y。
在图例页，你还可以选择是否显示图例，等等。
5．按"下一步"按钮，进入图表位置窗口。
我们选择选项：
⊙作为新工作表插入，这样，Excel会为你新建一个图表页。
如果选择选项：
⊙作为其中的对象插入，则Excel会将新建的图表插入在原工作表页面。
6．按"完成"按钮，Excel就会按照你的设置将所选数据转换成图表。
我们看到，一个新建的正弦曲线y2显示在整个屏幕上，同时，在下方工作表标签栏，新增加了图表1标签。
通过鼠标点击这些标签，可以与Sheet1、Sheet2、Sheet3等工作表进行页面切换。
假如，你还想把y1、y2、y3三条正弦曲线都建在一个图表上，则可以点击Sheet1标签，回到原始的工作表页面，从工作表中选择全部的数据单元格，再重复以上步骤，即可又创建一个新图表，同时工作表标签栏新增图表2标签。
这时点击文件|保存，则工作表及其图表将作为一个Excel文档存盘。
图表也是工作表，一个Excel文档最多可包含255个工作表。
图表建好后，如对选择的设置不满意，还可以通过图表菜单的子菜单回到以上的任一步骤进行修改。
通过格式菜单的子菜单，则可以设置图表区、绘图区、坐标轴的图案、字体、刻度。
或者直接用鼠标右键单击图表的图表区、绘图区或坐标轴，调出快捷菜单来设置修改它们。
我们将x轴刻度最大值由400改为360，将刻度单位值由50改为30，这样设置更为合适。
如果不显示图例，则应当为三条正弦曲线加注标识y1、y2、y3（通过添加文本框）。
现在，设置好的图表2如下所示：
人们在科学实验中经常需要对大量的实验数据进行处理，Excel的图表功能可以帮助我们观察和分析客观世界变量的内在规律和函数关系，特别是通过Excel的图表|添加趋势线功能菜单还可以帮助趋势预测和回归分析，为科学工作者的工作提供了极大的便利。
21.批量修改数据
在EXCEL表格数据都已被填好的情况下，如何方便地对任一列（行）的数据进行修改呢？
比如我们做好一个EXCEL表格，填好了数据，现在想修改其中的一列（行），例如：
想在A列原来的数据的基础上加8，有没有这样的公式？
是不是非得手工的一个一个数据地住上加？
对于这个问题我们自然想到了利用公式，当你利用工式输入A1=A1+8时，你会得到EXCEL的一个警告：
“MICROSOFT EXCEL不能计算该公式……”只有我们自己想办法了，这里介绍一种简单的方法：
第一步：
在想要修改的列（假设为A列）的旁边，插入一个临时的新列（为B列），并在B列的第一个单元格（B1）里输入8。
第二步：
把鼠标放在B1的或下角，待其变成十字形后住下拉直到所需的数据长度，此时B列所有的数据都为8。
第三步：
在B列上单击鼠标右键，“复制”B列。
第四步：
在A列单击鼠标的右键，在弹出的对话框中单击“选择性粘贴”，在弹出的对话框中选择“运算”中的你所需要的运算符，在此我们选择“加”，这是本方法的关键所在。
第五步：
将B列删除。
怎么样？
A列中的每个数据是不是都加上了8呢？
同样的办法可以实现对一列（行）的乘，除，减等其它的运算操作。
原表格的格式也没有改变。
此时整个工作结束，使用熟练后，将花费不到十秒钟
22.将Excel数据导入Access
如果想将Excel中的数据转换到Access中，可以采取下面的直接导入法和建立链接法来完成。
一、直接导入法
1.启动Access，新建一数据库文件。
2.在“表”选项中，执行“文件→获取外部数据→导入”命令，打开“导入”对话框。
3.按“文件类型”右侧的下拉按钮，选中“MicrosoftExcel（.xls）”选项，再定位到需要转换的工作簿文件所在的文件夹，选中相应的工作簿，按下“导入”按钮，进入“导入数据表向导”对话框
4.选中需要导入的工作表（如“工程数据”），多次按“下一步”按钮作进一步的设置后，按“完成”按钮。
注意：
如果没有特别要求，在上一步的操作中直接按“完成”按钮就行了。
5.此时系统会弹出一个导入完成的对话框按“确定”按钮。
至此，数据就从Excel中导入到Access中。
二、建立链接法
1.启动Access，新建一数据库文件。
2.在“表”选项中，执行“文件→获取外部数据→链接表”命令，打开“链接”对话框。
3.以下操作基本与上述“直接导入法”相似，在此不再赘述，请大家自行操练。
注意：
“直接导入法”和“建立链接法”均可以将Excel数据转换到Access中，两者除了在Access中显示的图标不同（图2）外，最大的不同是：
前者转换过来的数据与数据源脱离了联系，而后者转换过来的数据会随数据源的变化而自动随时更新。
23.办公技巧：
Excel定时提醒不误事
如果您从事设备管理工作，有近千台机械设备需要定期进行精度检测，那么，就得每天翻阅“设备鉴定台账”来寻找“到期”的设备——实在是太麻烦了！
用Excel建立一本“设备鉴定台账”是不是方便得多？
方法是：
用Excel的IF函数嵌套TODAY函数来实现设备“到期”自动提醒。
首先，运行Excel，将“工作簿”的名称命名为“设备鉴定台账”，输入各设备的详细信息、上次鉴定日期及到期日期（日期的输入格式应为“年-月-日”，如：
2003-10-21， 然后，选中图1所示“提示栏”下的F2单元格，点击插入菜单下的函数命令，在“插入函数”对话框中选择“逻辑”函数类中的IF函数，点击[确定]按钮，就会弹出“函数参数”对话框，分别在Logical_test行中输入E2=TODAY（）、value_if_true行中输入“到期”、Value_if_false行中输入“""”（如图2），并点击[确定]按钮。
这里需要说明的是：
输入的""是英文输入状态下的双引号，是Excel定义显示值为字符串时的标识符号，即IF函数在执行完真假判断后显示此双引号中的内容。
为了醒目，可在“单元格属性”中将F2单元格的字体颜色设置为红色。
最后，拖动“填充柄”，填充F列以下单元格即可。
我们知道Excel的IF函数是一个“条件函数”，它的语法是“IF（logical_test,value_if_true,value_if_false）”，具体地说就是：
如果第一个参数logical_test返回的结果为真，则执行第二个参数Value_if_true的结果，否则执行第三个参数Value_if_false的结果；Excel的TODAY函数［语法是TODAY（）］是返回当前系统日期的函数。
实际上，本文所应用的IF函数语句为IF（E2=TODAY（）,"到期",""），解释为：
如果E2单元格中的日期正好是TODAY函数返回的日期，则在F2单元格中显示“到期”，否则就不显示，TODAY函数返回的日期则正好是系统当天的日期。
Excel的到期提醒功能就是这样实现的。
24.办公小绝招构造Excel动态图表
（1）
Excel中的窗体控件功能非常强大，但有关它们的资料却很少见，甚至Excel帮助文件也是语焉不详。
本文通过一个实例说明怎样用窗体控件快速构造出动态图表。
假设有一家公司要统计两种产品（产品X，产品Y）的销售情况，这两种产品的销售区域相同，不同的只是它们的销售量。
按照常规的思路，我们可以为两种产品分别设计一个图表，但更专业的办法是只用一个图表，由用户选择要显示哪一批数据——即，通过单元按钮来选择图表要显示的数据。
为便于说明，我们需要一些示例数据。
首先在A列输入地理区域，如图一，在B2和C2分别输入“产品X”和“产品Y”，在B3:
C8区域输入销售数据。
一、提取数据
接下来的步骤是把
升级会员 