怎麼用函數求出第三大的數 ?

metrostar

怎麼用函數求出第三大的數 ?

學妹又來麻煩大家了

請帥哥/美女版主 帥哥/美女學長跟學姐

幫忙學妹完成


答案 : 97


A 下有這些數據 (最大不超過100)


A1 = 100
A2 = 99
A3 = 100
A4 = 97
A5 = 96
A6 = 95
.
.
.
.
.

wqfzqgk

=large(范围,3)

metrostar

多謝 wqfzqgk 帥哥學長

您的公式雖然能用

但不是學妹要的,求出的數據是 99 耶 (不對不對)

是要 97 那個數據才對
不管前面數據是否有重複 100,99,97,96
要找出第三那個才算

PD961A

回復 3# metrostar


    找出第3大值.rar (2.12 KB)

下載次數: 22

2010-7-6 17:44

wqfzqgk

看错了，一自定义函数
Function 数值(rng As Range, k As Integer)
On Error Resume Next
Set d = CreateObject("scripting.dictionary")
Dim rngg As Range
For Each rngg In rng
d.Add rngg.Value, ""
Next
arr = d.keys
数值 = Application.WorksheetFunction.Large(arr, k)
End Function

wqfzqgk

Function 数值(rng As Range, k As Integer)
On Error Resume Next
Set d = CreateObject("scripting.dictionary")
Dim rngg As Range
For Each rngg In rng
d.Add rngg.Value, ""
Next
数值 = Application.WorksheetFunction.Large(d.keys
, k)
End Function

Hsieh

回復 6# wqfzqgk

1. Function maxk(rng As Range, k As Integer)
   
2. Set d = CreateObject("scripting.dictionary")
   
3. Dim rngg As Range
   
4. For Each rngg In rng
   
5. d(rngg.Value) = ""
   
6. Next
   
7. maxk = Application.WorksheetFunction.Large(d.keys, k)
   
8. End Function

複製代碼

這樣不需用ON ERROR RESUME NEXT來避免錯誤

wqfzqgk

回復 7# Hsieh


    以前也用过，时间长了忘记了

wqfzqgk

回復 8# wqfzqgk


    我没有看你的程序，我的那个不加不行，討論不重複清單中也可以用个来实现

Hsieh

dictionary物件要使用add方法可配合exists先檢查是否索引值已經存在
個人並不贊同使用on error陳述式來避開錯誤
只有在不確定錯誤產生狀況才使用on error resume next

1. Function maxk(rng As Range, k As Integer)
   
2. Set d = CreateObject("scripting.dictionary")
   
3. Dim rngg As Range
   
4. For Each rngg In rng
   
5. If d.exists(rngg.Value) = False Then d(rngg.Value) = ""
   
6. Next
   
7. maxk = Application.WorksheetFunction.Large(d.keys, k)
   
8. End Function

複製代碼