標題:
請問有數筆不同值的考試分數！如何設定函數找出最接近50分的人名呢！
[打印本頁]
作者:
阿宇哥
時間:
2010-9-27 23:27
標題:
請問有數筆不同值的考試分數！如何設定函數找出最接近50分的人名呢！
[attach]2896[/attach]請問有數筆不同值的考試分數！如何設定函數找出最接近50分的人名呢！請各位大大幫忙！謝謝
作者:
oobird
時間:
2010-9-28 00:12
回復
1#
阿宇哥
定義名稱：a=MAX(IF(Sheet1!$B$1:$B$11<=50,Sheet1!$B$1:$B$11))
b=MIN(IF(Sheet1!$B$1:$B$11>=50,Sheet1!$B$1:$B$11))
ab=IF(ABS(50-a)<ABS(50-b),a,b)
儲存格輸入公式：=INDIRECT("a"&MATCH(ab,$B$1:$B$11,0))
作者:
toomellowhaw
時間:
2010-9-28 08:22
=INDEX(A1:A11,MATCH(MIN((B1:B11-50)^2),(B1:B11-50)^2,))
陣列公式
作者:
Hsieh
時間:
2010-9-28 10:02
[attach]2899[/attach]
陣列公式=IF(ROW(A1)>y,"",INDEX($A$1:$A$11,SMALL(IF(($B$1:$B$11=x+50)+($B$1:$B$11=50-x),ROW($B$1:$B$11)),ROW(A1))))
[attach]2901[/attach]
作者:
joviwang
時間:
2010-9-28 16:42
{=INDEX(A1:A11,MATCH(SMALL(ABS(50-B1:B11),1),ABS(50-B1:B11),))}
作者:
阿宇哥
時間:
2010-9-28 21:09
感謝各位大大幫忙！謝謝！
作者:
ANGELA
時間:
2010-9-29 15:06
=LOOKUP(2,1/((MIN((50-B1:B11)^2))=(50-B1:B11)^2),A1:A11)
作者:
toomellowhaw
時間:
2010-9-29 16:57
回復
7#
ANGELA
=LOOKUP(2,1/((MIN((50-B1:B11)^2))=(50-B1:B11)^2),A1:A11)
對不起，可以麻煩您解說函數裡前兩項參數嗎？
作者:
ANGELA
時間:
2010-9-30 12:46
=LOOKUP(2,1/((MIN((50-B1:B11)^2))=(50-B1:B11)^2),A1:A11)
第一個參數2,可以是其他數字,用2 是習慣而已,第二個參數B1到B11=min((50-B1:B11)^2)會得到TRUE,否則得到FALSE,用1去除會得到1或#DIV/0!,LOOKUP的特性是比對不到時會返回最後的數字,也就是1的位置.
作者:
Hsieh
時間:
2010-9-30 14:27
回復
8#
toomellowhaw
以欄位分解公式(如圖)
[attach]2923[/attach]
作者:
toomellowhaw
時間:
2010-9-30 14:59
回復
9#
ANGELA
喔..原來如此..
智慧真是無價啊..
又學到一種算法了..
也對LOOKUP多一層了解了..
以前都不知道可以這樣用這個函數..
感恩..
作者:
toomellowhaw
時間:
2010-9-30 15:02
回復
10#
Hsieh
感謝版主的詳細分欄圖解
在解說與圖解兩相對照下
終於弄明白了
以後也可以採用這種方式去理解公式
感恩..
作者:
阿宇哥
時間:
2010-9-30 21:39
不同人有不同的方法！但都是好方法！感謝各位大大解決小弟的問題！謝謝
作者:
oobird
時間:
2010-10-1 21:56
早先對lookup的認識，只能作些單純的運算，功能甚至比不上vlookup。
大約5、6年前，新加坡的Bosco_yip掀起了一陣lookup的探討旋風，使lookup用在更高階的功能上。
可惜當時醉心於auto cad,對excel冷了下來，無心關注，現在大家會的我都不會了！
此例為何要^2？沒有^2就不能得到正確答案，有朋友撥冗為我說明一下嗎？
作者:
Hsieh
時間:
2010-10-1 22:48
正正得正
負負得正
作者:
oobird
時間:
2010-10-1 23:36
謝謝，用ABS郤不能得到相同效果，原因安在？
作者:
toomellowhaw
時間:
2010-10-2 00:05
回復
16#
oobird
ABS也行，只須輸入為陣列公式..
作者:
Hsieh
時間:
2010-10-2 00:15
ABS仍可達到相同效果啊!
=LOOKUP(2,1/((MIN(ABS(50-B1:B11)))=ABS(50-B1:B11)),A1:A11)
1/((MIN(ABS(50-B1:B11)))=ABS(50-B1:B11))
傳回是1/FALSE或1/TRUE的陣列
就傳回#DIV/0!或1的陣列
只要第1個參數大於1就能找到最後一個1的對應值
作者:
oobird
時間:
2010-10-2 07:37
本帖最後由 oobird 於 2010-10-2 07:38 編輯
但…正如樓上名字很難唸的仁兄所言，要用陣列輸入才能成功呀！
如果用陣列公式，這裡lookup就失去優勢了。
作者:
Andy2483
時間:
2023-5-11 15:32
謝謝論壇,謝謝各位前輩
後學藉此帖練習VBA陣列與字典,學習方案如下,請各位前輩指教
執行結果:
[attach]36341[/attach]
Option Explicit
Sub TEST()
Dim Brr, Y, i&, b#
'↑宣告變數
Set Y = CreateObject("Scripting.Dictionary")
'↑令Y變數是 字典
Brr = Range([B1], Cells(Rows.Count, 1).End(3))
'↑令Brr變數是 二維陣列,以儲存格值帶入
For i = 1 To UBound(Brr)
'↑設順迴圈!
b = Abs(50 - Brr(i, 2))
'↑令b變數是 50減 Brr第2欄陣列值後,Abs()轉為正值
Y(b) = Trim(Y(b) & " " & Brr(i, 1) & "(" & Brr(i, 2) & ")")
'↑令以b變數當key,item是自身連接Brr陣列第1.2欄陣列值
Next
MsgBox Y(Application.Min(Y.KEYS))
'↑令跳出提示窗 顯示: 以Y字典裡最小的key值回傳的item值
Set Y = Nothing: Erase Brr
'↑令釋放變數
End Sub
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
