用Excel函数实现排序与筛选的方法_Office办公助手
分享高质量的原创Office教程、Word教程、Excel教程、PPT教程。
用Excel函数实现排序与筛选的方法
发布时间：　来源：Office办公助手(www.officezhushou.com)
Execl本身具有很方便的排序与筛选功能，下拉&数据&菜单即可选择排序或筛选对数据清单进行排序或筛选。但也有不足，首先无论排序或筛选都改变了原清单的原貌，特别是清单的数据从其它工作表链接来而源数据发生变化时，或清单录入新记录时必须从新进行排序或筛选。其次还有局限，例如排序只能最多对三个关键字(三列数据)排序，筛选对同一列数据可用&与&、或&或&条件筛选，但对不同列数据只能用&与&条件筛选。例如对某张职工花名册工作簿，要求筛选出年龄大于25岁且小于50岁或年龄大于50岁或小于25岁都是可行的，如同时要求性别是男的或女的也是可行的。但要求筛选出女的年龄在22岁到45岁，男的年龄在25岁到50岁时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便可实现上述三种筛选。
更多相关阅读
上一篇： 下一篇：
在Excel中利用系统时间和出生年月计算年龄是人事管理、工资统计中经常
本文以实例详解使用Excel中的MONTH函数、LOOKUP函数及TEXT函数计算日期所归
在制作财务报表时，有时需要将金额值中的数字分开放置到不同的单元
在职工档案表中，有时需要通过职工的出生年月自动计算该职工的退休
专题教程推荐
(window.slotbydup=window.slotbydup || []).push({
id: '3577897',
container: s,
size: '300,450',
display: 'inlay-fix'
热门资源下载下次自动登录
现在的位置:
& 综合 & 正文
函数：用选择法对10个数排序
#include "stdio.h"
int main() {
void sort (int array[],int n);
int a[10],i;
printf("enter array:\n");
for (i=0;i&10;i++)
scanf("%d",&a[i]);
sort(a,10);
printf("The sorted array:\n");
for (i=0;i&10;i++)
printf("%d ",a[i]);
printf("\n");
void sort (int array[], int n) {
int i,j,k,t;
for (i=0;i&n-1;i++) {
for (j=i+1;j&n;j++)
if (array[j]&array[k])
//将最小的数与a[i]对换
t=array[i];
array[i]=array[k];
array[k]=t;
【上篇】【下篇】C语言：一维数组之选择法排序_Linux系统开发_腾讯视频交换法：所谓交换，就是根据序列中两个记录键值的比较结果来这两个记录在序列中的位置，交换排序的特点是:将键值较大的记录向序列的尾部移动，键值较小的记录向序列的前部移动。
简单选择排序：的基本思想:第1趟，在待排序记录r[1]~r[n]中选出最小的记录，将它与r[1]交换;第2趟，在待排序记录r[2]~r[n]中选出最小的记录，将它与r[2]交换;以此类推，第i趟在待排序记录r[i]~r[n]中选出最小的记录，将它与r[i]交换，使有序序列不断增长直到全部排序完毕。
插入排序法：有一个已经有序的数据序列，要求在这个已经排好的数据序列中插入一个数，但要求插入后此数据序列仍然有序，这个时候就要用到一种新的排序方法--插入排序法插入排序的基本操作就是将一个数据插入到已经排好序的有序数据中，从而得到一个新的、个数加一的有序数据，算法适用于少量数据的排序，度为O(n^2)。是稳定的排序方法。插入算法把要排序的分成两部分:第一部分包含了这个数组的所有元素，但将最后一个元素除外(让数组多一个空间才有插入的位置)，而第二部分就只包含这一个元素(即待插入元素)。在第一部分排序完成后，再将这个最后元素插入到已排好序的第一部分中。插入排序的基本思想是:每步将一个待排序的纪录，按其关键码值的大小插入前面已经排序的文件中适当位置上，直到全部插入完为止。
冒泡排序：冒泡排序（Bubble Sort，台湾译为：泡沫排序或气泡排序）是一种简单的排序算法。它重复地走访过要排序的数列，一次比较两个元素，如果他们的顺序错误就把他们交换过来。走访数列的工作是重复地进行直到没有再需要交换，也就是说该数列已经排序完成。
以函数的形式举例
/*交换法对数组排序*/
void Sort(int arr[],int n){ int i, j, for (i = 0; i & n - 1; i++) {
for (j = i + 1; j & j++)
if (arr[j] & arr[i])/*此处降序*/
temp = arr[j];
arr[j] = arr[i];
/*选择法对数组排序*/
void Sort(int arr[],int n){ int i, j,k, for (i = 0; i & n - 1; i++) {
for (j = i + 1; j & j++)
if (arr[j] & arr[i])
k =　　　　/*记录最大值下标位置*/
if (k != i)　　　　/*若最大数所在的下标位置不在位置i则进行交换*/
temp = arr[k];
arr[k] = arr[i];
/*冒泡法对数组排序*/
void Sort(int arr[],int n){ int i, j, int k = 1; /*定义k若出现有序数组则不用再判断*/ for (i = 0; i & n - 1 && i++) {
for (j = 0; j & n - 1; j++)
if (arr[j] & arr[j + 1])
temp = arr[j];
arr[j] = arr[i+1];
arr[j+1] =
阅读(...) 评论()查看: 1323|回复: 0
c语言数组名做函数参数&选择法排序
本帖最后由 liuda 于
02:19 编辑
//形参数组和实参数组同时变
//用选择法对数组中的10个数由小到大排序
//我感觉这样反而使程序更加复杂了，它使用了函数，但是不用函数也可以
//这样就是把排序的结果调回去？不用调结果
//好像这道题不能一次性处理整个数组，得一个一个处理？就是让处理数组的
#include&stdio.h&
void main()
{
int a[10],i,j;
void cmp(int c[10]);//既然形参数组，和实参数组同时变，那就是无参函数，不需要带回函数值
printf(&please input 10 numbers\n&);
for(i=0;i&=9;i++)
{
scanf(&%d&,&a[i]);
}
cmp(a);//看来问题就出在这个方程这里了
for(j=0;j&=9;j++)
{
printf(&%d,&,a[j]);
}
}
void cmp( int c[10])
{int m,n,
& &for(m=0;m&=9;m++)
& &{
&&sta=c[m];
&&for(n=m+1;n&=9;n++)
&&{
& &&&if(c[n]&c[m])
{&&sta=c[m];
& && &&&c[m]=c[n];
& && &&&c[n]=
}
&&}
& &
& &}
Powered by}