請教如何自定義函數

[gong]

請教如何將常用函數公式自定義成另一個函數

[leonchou]

這個教學不錯
http://www.officefans.net/cdb/viewthread.php?tid=725
自訂函數的"說明"
http://www.excelhelp.net/cgi-bin/forum/topic.cgi?forum=8&topic=22

[gong]

EXCELHELP好像上不去

[oobird]

與個例子吧，若要取a1:a20的不重複值，用陣列公式：'=IF(ROW()>SUM(1/COUNTIF($A$1:$A$20,$A$1:$A$20)),"",INDEX($A$1:$A$20,SMALL(IF(MATCH($A$1:$A$20,$A$1:$A$20,0)=ROW($A$1:$A$20),ROW($A$1:$A$20)),ROW(A1))))
寫成代碼：
Function ABCD(rng As Range, rng2 As Range)
a = rng.Address
b = "IF(ROW()>SUM(1/COUNTIF($A$1:$A$20,$A$1:$A$20)),"""",INDEX($A$1:$A$20,SMALL(IF(MATCH($A$1:$A$20,$A$1:$A$20,0)=ROW($A$1:$A$20),ROW($A$1:$A$20)),ROW(" & rng2.Address & "))))"
c = Replace(b, "$A$1:$A$20", a)
ABCD = Application.Evaluate(c)
End Function
放在模組內，在儲存格輸入=ABCD(A$1:A$20,A1)往下拉則達成上面陣列公式的效果
其實我也是初學，正在努力中。