用Excel函数实现排序与筛选的方法
发布时间:2023-10-14 08:57:56
Execl本身具有很方便的排序与筛选功能,下拉“数据”菜单即可选择排序或筛选对数据清单进行排序或筛选。但也有不足,首先无论排序或筛选都改变了原清单的原貌,特别是清单的数据从其它工作表链接来而源数据发生变化时,或清单录入新记录时必须从新进行排序或筛选。其次还有局限,例如排序只能最多对三个关键字(三列数据)排序,筛选对同一列数据可用“与”、或“或”条件筛选,但对不同列数据只能用“与”条件筛选。例如对某张职工花名册工作簿,要求筛选出年龄大于25岁且小于50岁或年龄大于50岁或小于25岁都是可行的,如同时要求性别是男的或女的也是可行的。但要求筛选出女的年龄在22岁到45岁,男的年龄在25岁到50岁时Execl本身具有的筛选功能则无能为力了。再者排序与筛选不能结合使用,即不能在排序时根据条件筛选出来的记录进行排序。例如有一张职工资料清单,其中有的职工已经退休,对在职职工的年龄进行排序时无法剔除已退休职工的数据。
本文试图用Execl的函数来解决上述问题。
一、用函数实现排序
题目
如有一张工资表,A2:F501,共6列500行3000个单元格。表头A1为姓名代码(1至500)、B1为姓名、C1为津贴、D1为奖金、E1为工资、F1收入合计。现要求对职工收入从多到少排序,且在职工总收入相同时再按工资从多到少排序,在职工总收入和工资相同时再按奖金从多到少排序,在职工职工总收入和工资、奖金相同时再按津贴从多到少排序。
方法
G1单元格填入公式
“=if(F2=0,10^100,INT(CONCATENATE(999-f2,999-e2,999-d2,999-c2)))”,
CONCATENATE是一个拼合函数,可以把30个以下的单元的数据拼合成一个数据,这些被拼合的数据之间用逗号分开。用f2、e2等被拼合的数据用999来减,是为了使它们位数相同。(假定任何一个职工的总收入少于899元)。被拼合成的函数是文本函数,CONCATENATE与INT函数套用是为了使文本转换为数字。最外层的if函数是排序时用来剔除不进行排序的记录,在本例中指收入为零的记录。(在上文提到的职工年龄排序,则公式改为“if(f2="退休",10^100,…..)”,即剔除了退休职工。)
第二步把G1单元格的公式拖放到G500单元格(最简便的方法是点击G1单元格后向G1单元格右下方移动鼠标,见到黑十时双击鼠标就完成了G1到G500的填充)。
第三步在在H2单元填入公式“=MATCH(SMALL(G:G,ROW(A1)),G:G,0)”与第二步一样拖放到H501单元格。此公式实际上是把三列公式合成一列公式,ROW(A1)即为A1的行数是1,随着向下拖放依次为2、3、4…,SMALL(G:G,ROW(A1))为G列中最小的数随着向下拖放依次为第2、第3、..小的数,MATCH(SMALL(G:G,ROW(A1)),G:G,0)即为G列各行的数据中最小、第2、第3小等的数据在第几行。
第四步把A1至F1单元格的表头复制到I1至N1单元格,在I2单元格输入公式“=INDEX($A$2:$F$501,$H2,COLUMN(A$1))”INDEX函数是一个引用函数,即把$A$2:$F$501单元格列阵第$H2行第COLUMN(A$1)列的数据放入I2单元格。然后把I2单元格的公式拖放到N2单元格,点击N2单元格后向N2单元格右下方移动鼠标见到黑十时双击鼠标就完成了I2到N501单元格的填充到此全部完成。
以上叙述看似繁杂实际非常简单,只要把A1至F1的表头复制到I1至N1单元格,再分别在G1、H2、I2单元格输入公式然后向下拖放,即使对EXCEL应用不熟练的同志一分锺内便能完成。
对上述程序稍作变化还可得到更多用度。上面例子数据是从大到小排列的,如H列的函数中的SMALL改为LARGE,上面例子数据就从小到大排列了。如H2单元格的公式改为“=IF(O1=1,MATCH(SMALL(G:G,ROW(A1)),G:G,0),MATCH(LARGE(G:G,ROW(A1)),G:G,0))”
并把H2单元格的公式向下拖放。这样在O1单元格输入1上面例子数据是从大到小排列的,O1单元格输入1以外的数上面例子数据就从小到大排列了。
如在H列前插入若干列,如插入一列,则现在的H列输入类似G列的公式,例如
“=if(F2=0,10^100,d2)”,现在的I列的公式改为“=IF(P1=1,MATCH(SMALL(G:G,ROW(A1)),G:G,0),
MATCH(SMALL(H:H,ROW(A1)),H:H,0)))”
即在P单元格输入1以外的值就实现了按奖金大小排序.这样只要通过改变P1(原来的O1单元格)单元格内容的改变就能立即得到按不同要求的排序。
二、用函数实现筛选
题目
如有一张职工名册表,A2:F501,共6列500行3000个单元格。表头A1为姓名代码(1至500)、B1为姓名、C1为性别、D1为年龄、E1为学历、F1职称。现要求对职工的性别、年龄、学历、职称进行交错筛选,例如要求在同一张表上筛选出1、女的年龄在22岁到45岁,男的年龄在25岁到50岁,2、女博士,3、男博士后。
方法
第一步在G2单元格输入公式”=IF(OR(AND(C2="女",D2>=22,D2<=45),AND(C2="男",
D2>=25,D2<=50)),ROW(A1),0)“,在H2单元格输入公式”=IF(AND(C2="女",E2="博士"),
ROW(B1),0)“,在I2单元格输入公式”=IF(AND(C2="男",E2="博士后"),ROW(B1),0)“。在J2单元格输入公式“=IF(K$2=1,LARGE(G:G,ROW(A1)),IF(K$2=2,LARGE(H:H,ROW(A1)),
IF(K$2=3,LARGE(I:I,ROW(A1)),0)))”然后用上述提到的方法向下拖放。G、H、I列的公式的含义就是凡符合筛选条件的行记录下行号否则为零,J列的公式的含义根据K2的数值选择G、H、I中的一列进行排序并把不合条件的行除去。
第二步在K1单元格输文字”筛选选择”,A1到F1表头复制到L1到Q1,在L2单元格输入
公式“=IF($J2=0,0,INDEX($A$2:$F$501,$J2,COLUMN(A$1)))”,然后向右拖放到Q2,再向下拖放。INDEX函数的含义上文已说明。
第三步在P1单元格输入1或2或3便可实现上述三种筛选。
猜你喜欢
- Win10专业版用户在使用电脑过程中出现画面不清晰,一直很模糊,即使调节分辨率还是一样,看东西也不清楚,很多人都不知道怎么解决,下面就跟随小
- 如何自动保存Word 2003文档?使用Word 2003自动恢复功能可以确保始终保存文档的副本。自动恢复每隔几分钟左右便会秘密保存您的文档
- 以word2010为例,方法如下:1、光标定在目录页的最后,见图1。2、依次点击“页面布局”、“分隔符”、“下一页”。3、双击目录下一页的页
- 1、首先我们应该先打开需要进行设置目录的文件夹。2、在打开相关的文件夹后,我们找到菜单栏中的“引用”。3、我们选择需要设计一级目录的文字,点
- 在高频彩11选5中,前三直选的中奖金额最高,在这里,我们用excel生成前三直的字典,可以供彩民朋友在买彩票时查照和参考在高频彩11选5中,
- 在word中添加删除页码就设置过不少,那么在Excel 中也是一样的吗?如果你还不清楚那就快来了解一番吧 1、页面设置--页眉/页
- 在Excel2010工作表中,用户可以将被选中单元格中的小数设置分数表示,Excel2010的分数格式将小数四舍五入为值最接近的分数。在分数
- 在EXCEL里面,双击鼠标有很多妙用,比如说鼠标双击可以快速移动,多次使用格式刷等。以下是小编为您带来的关于EXCEL中双击鼠标的使用技巧,
- 在Word2013文档中,每种SmartArt图形布局都有固定数量的形状。用户可以根据实际工作需要删除或添加形状,操作步骤如下所述:第1步,
- VLOOKUP函数用于搜索指定区域内首列满足条件的元素,确定待检测单元格在区域中的行序号,再进一步返回选定单元格的值。那么在Excel表格中
- excel表格中冻结多行多列失败该怎么办?excel表格中一般我们会冻结表头,但是冻结多行的时候却失败了,该怎么办呢?下面我们来看看原因分析
- 在word文档中设置文字的格式,有时候需要使用word内部提供的设置好的格式,就比如设置文字的不同级别的标题,就需要使用各种标题的样式!一般
- 组合框的Textx性有两个用途一是用来返回或设里编辑域中的文本,另外一个用途就是返回列表框中被选择的选项。哪个选项。而对于列表框来说,Tex
- 在给长篇Word文章排版的时候,有时根据需要跳到某一页、某一行、某个标题、某一节或某种标注上进行处理,我们常看到不少人是通过翻动滚动条来定位
- 欣赏一下别人的成品: 1、尽量使用Tab、圆点的项目符号对齐,尽量不用空格找齐。用表格制作也可以,但是发送wor
- 由于公式属于嵌入式对象,因此它不能像图片或剪贴画一样设置文字环绕方式,从而能够在Word2010文档中自由移动。不过用户可以将公式放置在文本
- Word2016怎么取消语法错误时显示的红色波浪线?Word2016编辑文档的时候,总是出现红色的下划线,这是怎么回事?出现这个问题主要是开
- 我们有时会对两个Word文档中的内容对比,如上年报告中的数据和今年报告中的数据对比、上次奖励名单和这次奖励名单的对比、上下学期成绩的对比等等
- 在一张Excel表格的重复记录中,让你快速列出每种不同物品第2次或第n次出现的记录,你会怎么做?Vlookup函数就有这个本事。举例来说,产
- 当我们在书上忽然发现一篇好文章录入电脑,但是字数太多了,怎么办?别急,今天就由小编教大家解决这个问题!希望可以帮到大家!word2003怎么