用Excel做数据排序的常用方法与技巧Word文档下载推荐.docx
- 文档编号:22287290
- 上传时间:2023-02-03
- 格式:DOCX
- 页数:7
- 大小:21.03KB
用Excel做数据排序的常用方法与技巧Word文档下载推荐.docx
《用Excel做数据排序的常用方法与技巧Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《用Excel做数据排序的常用方法与技巧Word文档下载推荐.docx(7页珍藏版)》请在冰豆网上搜索。
对于这个问题,我们可以通过自定义序列来进行排序:
先把相应的职称序列按需要排序的顺序输入到相应的单元格区域(如N2至N18)中(图4);
执行“工具→选项”命令,打开“选项”对话框(图5),切换到“自定义序列”标签下,在“从单元格中导入序列”右侧的方框中输入“$N$2:
$N$18”(也可以用鼠标选择输入),然后单击“导入”按钮,将相应的序列导入到系统中,确定返回。
序列导入后,原来N2至N18区域中输入的数据可以删除,导入的序列在其他Excel文档中均可直接使用。
选中“职称”列任意一个单元格,执行“数据→排序”命令,打开“排序”对话框,单击其中的“选项”按钮,打开“排序选项”对话框(参见图3),按“自定义排序次序”选项右侧的下拉按钮,在随后弹出的下拉列表中,选中上述“导入”的序列,确定返回到“排序”对话框,再按下“确定”按钮即可。
[NEXT]
五、用函数进行排序
有时,我们对某些数值列(如“工龄、工资”等)进行排序时,不希望打乱表格原有数据的顺序,而只需要得到一个排列名次。
对于这个问题,我们可以用函数来实现(以“工龄”为例):
在“工龄”右侧插入一个空白列(J列),用于保存次序(图6),然后选中J2单元格,输入公式:
=RANK(I2,$I$2:
$I$101),然后再次选中J2单元格,将鼠标移至该单元格右下角成“细十字线状”时(这种状态,我们通常称之为“填充柄”状态),按住左键向下拖拉至最后一条数据为止,次序即刻显示出来(图6)。
若要升序排序,可在公式最后增加一个“非零”参数,如将上述公式改为:
$I$101,1)。
六、让序号不参与排序
当我们对数据表进行排序操作后,通常位于第一列的序号也被打乱了,如何不让这个“序号”列参与排序呢?
我们在“序号”列右侧插入一个空白列(B列),将“序号”列与数据表隔开。
用上述方法对右侧的数据区域进行排序时,“序号”列就不参与排序了。
插入的空列会影响表格的打印效果,我们可以将其隐藏起来:
选中B列(即插入的空列),右击鼠标,再选择“隐藏”选项即可。
高效办公Excel排序方法
排序是数据处理中的经常性工作,Excel排序有序数计算(类似成绩统计中的名次)和数据重排两类。
本文以几个车间的产值和名称为例,介绍Excel2000/XP的数据排序方法。
一、数值排序
1.RANK函数
RANK函数是Excel计算序数的主要工具,它的语法为:
RANK(number,ref,order),其中number为参与计算的数字或含有数字的单元格,ref是对参与计算的数字单元格区域的绝对引用,order是用来说明排序方式的数字(如果order为零或省略,则以降序方式给出结果,反之按升序方式)。
例如E2、E3、E4单元格存放一季度的总产值,计算各车间产值排名的方法是:
在F2单元格内输入公式“=RANK(E2,$E$2:
$E$4)”,敲回车即可计算出铸造车间的产值排名是2。
再将F2中的公式复制到剪贴板,选中F3、F4单元格按Ctrl+V,就能计算出其余两个车间的产值排名为3和1。
如果B1单元格中输入的公式为“=RANK(E2,$E$2:
$E$4,1)”,则计算出的序数按升序方式排列,即2、1和3。
需要注意的是:
相同数值用RANK函数计算得到的序数(名次)相同,但会导致后续数字的序数空缺。
假如上例中F2单元格存放的数值与F3相同,则按本法计算出的排名分别是3、3和1(降序时)。
2.COUNTIF函数
COUNTIF函数可以统计某一区域中符合条件的单元格数目,它的语法为COUNTIF(range,criteria)。
其中range为参与统计的单元格区域,criteria是以数字、表达式或文本形式定义的条件。
其中数字可以直接写入,表达式和文本必须加引号。
仍以上述为例,F2单元格内输入的公式为“=COUNTIF($E$2:
$E$4,"
&
gt;
"
amp;
E2)+1”。
计算各车间产值排名的方法同上,结果也完全相同,2、1和3。
此公式的计算过程是这样的:
首先根据E2单元格内的数值,在连接符&
的作用下产生一个逻辑表达式,即“&
176.7”、“&
167.3”等。
COUNTIF函数计算出引用区域内符合条件的单元格数量,该结果加一即可得到该数值的名次。
很显然,利用上述方法得到的是降序排列的名次,对重复数据计算得到的结果与RANK函数相同
3.IF函数
Excel自身带有排序功能,可使数据以降序或升序方式重新排列。
如果将它与IF函数结合,可以计算出没有空缺的排名。
上例中E2、E3、E4单元格的产值排序为例,具体做法是:
选中E2单元格,根据排序需要,单击Excel工具栏中的“降序排序”或“升序排序”按钮,即可使工作表中的所有数据按要求重新排列。
中004-5-3115:
43:
05
假如数据是按产值由大到小(降序)排列的,而您又想赋予每个车间从1到n(n为自然数)的排名。
可以在G2单元格中输入1,然后在G3单元格中输入公式“=IF(E3=E2,G3,G3+1)”,只要将公式复制到G4等单元格,就可以计算出其他车间的产值排名。
二、文本排序
选举等场合需要按姓氏笔划为文本排序,Excel提供了比较好的解决办法。
如果您要将数据表按车间名称的笔划排序,可以使用以下方法:
选中排序关键字所在列(或行)的首个单元格(如A1),单击Excel“数据”菜单下的“排序”命令,再单击其中的“选项”按钮。
选中“排序选项”对话框“方法”下的“笔画排序”,再根据数据排列方向选择“按行排序”或“按列排序”,“确定”后回到“排序”对话框。
如果您的数据带有标题行(如“单位”之类),则应选中“有标题行”(反之不选),然后打开“主要关键字”下拉列表,选择其中的“单位”,选中排序方式(“升序”或“降序”)后“确定”,表中的所有数据就会据此重新排列。
此法稍加变通即可用于“第一名”、“第二名”等文本排序,请读者自行摸索。
三、自定义排序
如果您要求Excel按照“金工车间”、“铸造车间”和“维修车间”的特定顺序重排工作表数据,前面介绍的几种方法就无能为力了。
这类问题可以用定义排序规则的方法解决:
首先单击Excel“工具”菜单下的“选项”命令,打开“选项”对话框中的“自定义序列”选项卡。
选中左边“自定义序列”下的“新序列”,光标就会在右边的“输入序列”框内闪动,您就可以输入“金工车间”、“铸造车间”等自定义序列了,输入的每个序列之
间要用英文逗号分隔,或者每输入一个序列就敲回车。
如果序列已经存在于工作表中,可以选中序列所在的单元格区域单击“导入”,这些序列就会被自动加入“输入序列”框。
无论采用以上哪种方法,单击“添加”按钮即可将序列放入“自定义序列”中备用。
使用排序规则排序的具体方法与笔划排序很相似,只是您要打开“排序选项”对话框中的“自定义排序次序”下拉列表,选中前面定义的排序规则,其他选项保持不动。
回到“排序”对话框后根据需要选择“升序”或“降序”,“确定”后即可完成数据的自定义排序。
需要说明的是:
显示在“自定义序列”选项卡中的序列(如一、二、三等),均可按以上方法参与排序,请读者注意Excel提供的自定义序列类型
例如数据当中有“局长”、“处长”、“科长”、“科员”并按当前顺序排序时,Excel的现有排序功能就无法直接实现了。
这时我们可以使用自定义排序来实现。
具体做法是:
在“工具”菜单选择“选项”,选择“自定义序列”,点“添加”按钮,在右边输入序列中按从小到大的顺序输入排序序列,或选中相应单元格后点击“导入”按钮。
这样会在左边的自定义序列加入你刚才输入的序列,再在排序对话框选项中选择自定义序列就能够按相应的顺序排序了。
Excel数据排序很简单四种方法
把成绩录入完后,使用“自动求和”功能计算出每个人的总分,并单击“数据”→排序,以“总分”为主“关键字”按“降序”排列。
接着在H1单元格输入“名次”二字(如图1)。
一、序列填充法
1.在H2单元格中输入1,然后把鼠指针指向H2单元格的四框上单击,让H2单元格为选中状态,或者单击一下其它任意单元格,再返回来单击H2单元格(不然接下来的填充→序列为灰色不可用)。
2.查出总共多少人。
3.单击“编辑”→填充→序列(如图2),在打开的“序列”对话框中,“序列产生在”项选“列”;
“类型”项选“等差序列”;
步长值1;
终止值输入总人数12;
最后单击“确定”按钮完成(如图3)。
二、托选填充法
1.左键拖选法:
在H2单元格中输入1,H3单元格中输入2,然后用鼠标把H2和H3单元格拖选上(如图4),接着把鼠标指针指向H3单元格右下角,当鼠标指针变成黑色实线加号时,按住左键向下拖动到H13单元格后放手。
2.右键拖选法:
在H2单元格中输入1,把鼠标指针指向H2单元格右下角,当鼠标指针变成黑色实线加号时,按住右键向下拖动到H13单元格后放手,这时屏幕上会弹出一个快捷菜单(如图5),左键单击“以序列方式填充”(也可以单击“序列”项,再按照序列填充法完成)。
三、函数判断法
1.排位函数RANK():
在H2单元格输入公式:
=RANK(G2,G:
G),接着把鼠标指针指向H2单元格右下角,当鼠标指针变成黑色实线加号时,按住左键向下拖动将公式向下复制到H13单格后放手(如图6)。
RANK函数是返回一个数值在一组数值中的排位。
数值的排位是与数据清单中其他数值的相对大小(如果数据清单已经排过序了,则数值的排位就是它当前的位置)。
其语法为:
RANK(number,ref,order)其中:
number为需要找到排位的数,ref为包含一组数的数组或引用(ref中的非数值型数据将被忽略),order为一指明排位方式的数,order为0或省略时按降序排位,order不为0时按升序排位。
这个公式中number是指G2单元格中的431分;
ref指的是G列这些分数;
order省略没有。
也就是说公式的最终意思是按照降序计算G2单元格中的431分在G列这组分数中所排的位置。
如果RANK函数中ref所引用的区域中有重复数,则函数返回相同的排位数,但重复数的存在将影响后续数值的排位。
也就是说如果分数相同名次相同,随后的名次将空缺。
如这里400出现两次,其排位为4,而395分的排位为6(没有排位为5的数值)。
2.条件函数IF():
在H2单元格直接输入1,在H3单元格输入公式:
=IF(G3=G2,H2,H2+1)接着把鼠标指针指向H3单元格右下角,当鼠标指针变成黑色实线加号时,按住左键向下拖动将公式向下复制到H13单格后放手(如图7)。
IF函数用于执行真假值判断后,根据逻辑测试的真假值返回不同的结果,因此`函数也称之为条件函数。
可以使用函数IF对数值和公式进行条件检测。
它的语法为IF(logical_test,value_if_true,value_if_false)。
其中Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。
此参数可使用任何比较运算符。
Value_if_true显示在logical_test为TRUE时返回的值,Value_if_true也可以是其他公式。
Value_if_false显示在logical_test为FALSE时返回的值。
Value_if_false也可以是其他公式。
也就是说如果第一个参数logical_test返回的结果为真的话,则执行第二个参数Value_if_true的结果,否则执行第三个参数Value_if_false的结果。
IF函数可以嵌套七层,用value_if_false及value_if_true参数可以构造复杂的检测条件。
这个公式中logical_test是G3=G2,value_if_true是H2,value_if_false是H2+1。
意思是G3单元格中的分数与G2单元格中的分数是否相同,如果相同,则显示H2单元格值,也就是我们填入的1,如果不同,则显示H2+1,也就是2。
用这种方法排的名次不会出现空缺,相同分数的排名仍然相同。
四、公式法
=H2+(G3
公式“=H2+(G3
公式中“=”为赋值命令,其作用是将计算结果填在这个单元格内,“H2”是我们先手工输入的1;
“+”为加法运算;
(G3
这种直接使用公式法同使用IF函数一样,名次不会出现空缺,相同分数的排名仍然相同。
Excel2003数据处理三则
一、让数据显示不同颜色
在学生成绩分析表中,如果想让总分大于等于500分的分数以蓝色显示,小于500分的分数以红色显示。
操作的步骤如下:
首先,选中总分所在列,执行“格式→条件格式”,在弹出的“条件格式”对话框中,将第一个框中设为“单元格数值”、第二个框中设为“大于或等于”,然后在第三个框中输入500,单击[格式]按钮,在“单元格格式”对话框中,将“字体”的颜色设置为蓝色,然后再单击[添加]按钮,并以同样方法设置小于500,字体设置为红色,最后单击[确定]按钮。
这时候,只要你的总分大于或等于500分,就会以蓝色数字显示,否则以红色显示。
二、将成绩合理排序
如果需要将学生成绩按着学生的总分进行从高到低排序,当遇到总分一样的则按姓氏排序。
操作步骤如下:
先选中所有的数据列,选择“数据→排序”,然后在弹出“排序”窗口的“主要关键字”下拉列表中选择“总分”,并选中“递减”单选框,在“次要关键字”下拉列表中选择“姓名”,最后单击[确定]按钮。
三、控制数据类型
在输入工作表的时候,需要在单元格中只输入整数而不能输入小数,或者只能输入日期型的数据。
幸好Excel2003具有自动判断、即时分析并弹出警告的功能。
先选择某些特定单元格,然后选择“数据→有效性”,在“数据有效性”对话框中,选择“设置”选项卡,然后在“允许”框中选择特定的数据类型,当然还要给这个类型加上一些特定的要求,如整数必须是介于某一数之间等等(如图3所示)。
另外你可以选择“出错警告”选项卡,设置输入类型出错后以什么方式出现警告提示信息(如图4所示)。
如果不设置就会以默认的方式打开警告窗口。
怎么样,现在处处有提示了吧,当你输入信息类型错误或者不符合某些要求时就会警告了。
排序
通过排序,可以根据某特定列的内容来重排数据清单中的行。
例如,假设一包含销售数据的数据清单有销售员、单位数量和订单日期等列。
我们可以用“排序”命令按销售员重排数据清单、从高到低组织单位数量或从早到晚排好订单日期。
当选择“排序”命令后,MicrosoftExcel会利用该列和我们指定的排序次序,或利用自定义排序次序来重新排列行、列或单个的单元格。
除非另有指定,否则MicrosoftExcel会根据我们选择的“主要关键字”列的内容以升序顺序(最低到最高)对行作排序。
当对数据排序时,MicrosoftExcel会遵循以下的原则:
如果我们由某一列来作排序,那么在该列上有完全相同项的行将保持它们的原始次序。
在排序列中有空白单元格的行会被放置在排序的数据清单的最后。
隐藏行不会被移动,除非它们是分级显示的一部分。
排序选项如选定的列、顺序(递增或递减)和方向(从上到下或从左到右)等,在最后一次排序后便会被保存下来,直到我们修改它们或修改选定区域或列标记为止。
如果我们按一列以上作排序,主要列中有完全相同项的行会根据我们指定的第二列作排序。
第二列中有完全相同项的行会根据我们指定的第三列作排序。
提示:
排序操作不仅仅适用于数据库,对于我们在工作表中的任意选定范围,我们都可以执行排序操作。
12.3.1按列排序
按照某一选定列排序的操作步骤如下:
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 数据 排序 常用 方法 技巧