excel排行榜函数

根据业务指标生成排行榜，是兰色工作中必做的工作之一，为了能动态生成，兰色利用excel函数编写了复杂的数组公...



根据业务指标生成排行榜，是兰色工作中必做的工作之一，为了能动态生成，兰色利用excel函数编写了复杂的数组公式。排行榜生成了，但公式修改起来很费力，一不小心就出错。于兰色决定着手编写新的排名函数:

一、用法介绍

=PaiMing(数据区域,对应排名指标)

语法说明：

• 数据区域：要排名的数据区域，可以是一列区域，也可以是一行区域。
• 对应排名指标：和数据一一对应的指标。

二、功能演示

【例】如下图所示为各公司销售统计表，要求根据该表动态生成“销量排行榜”和利润排行榜。



生成的动态排行榜



三、公式设置方法

完成排行榜效果，如果用一般的方法，需要分别设置复杂的函数公式，但用PaiMing函数，只需要一键即可完成。

选取要显示的3列区域。在编辑栏中输入下面的公式，最后按Ctrl+shift+Enter完成输入。

（注：排行榜显示前N名，就选取N行）

=PaiMing(B2:B13,A2:A13)



输入完成后，公式两边会自动添加大括号 {}。





四、加载函数。

PaiMing不是内置函数，是兰色编写的。所以要想使用它，需要按下面的方法加载一下。

1、按alt+f11打开visual Basic窗口。插入菜单 - 模块。把下面的代码粘贴到右侧的窗口中。关闭VB窗口。



'示例用代码

Function PaiMing(rg As Range, rg1 As Range)

Dim iOuter As Long

Dim iInner As Long

Dim iLBound As Long

Dim iUBound As Long

Dim iTemp As Double

Dim x As Long, k As Long

Dim arr1, arr2, arr3(1 To 10000, 1 To 3)

arr1 = rg

arr2 = rg1

If UBound(arr1, 2) > 1 Then

arr1 = Application.Transpose(arr1)

arr2 = Application.Transpose(arr2)

End If

iLBound = LBound(arr1)

iUBound = UBound(arr1)

'冒泡排序

For iOuter = iLBound To iUBound

For iInner = iLBound To iUBound - iOuter

'比较相邻项

If arr1(iInner, 1) < arr1(iInner + 1, 1) Then

&#39;交换值

iTemp = arr1(iInner, 1)

iTemp1 = arr2(iInner, 1)

arr1(iInner, 1) = arr1(iInner + 1, 1)

arr1(iInner + 1, 1) = iTemp

arr2(iInner, 1) = arr2(iInner + 1, 1)

arr2(iInner + 1, 1) = iTemp1

End If

Next iInner

Next iOuter

For x = 1 To UBound(arr1)

arr3(x, 1) = arr2(x, 1)

arr3(x, 2) = arr1(x, 1)

k = k + 1

If x > 1 Then

If arr1(x, 1) = arr1(x - 1, 1) Then k = k - 1

End If

arr3(x, 3) = k

Next x
PaiMing = arr3
End Function

2、当前文件另存为“Excel 启用宏的工作簿”



完成上述操作，在当前的excel文件中，就可以直接使用PaiMing函数了。

删除公式方法：全选输入公式的区域，按delete删除。

兰色说：今天我们再一次见识了自定义函数，如果大家感觉有用，兰色会陆续推出更多更实用的excel函数。

示例文件下载地址：(粘贴到电脑浏览器地址栏中，按回车下载)

http://www.excelpx.com/home/upload/2015_02/temp_15021221206145.zip

    关注 excel第一教室