EXCEL 發出聲音[用(自定)函數]

是利用(自定)函數去發出聲音的~也蠻實用的~

在 Excel 中, 按 Alt F11 進入 Visiual Basic 編輯器畫面,
點選 [插入]/[模組], 畫面會跳出程式碼視窗, 在裡面複製貼上如下程式:
Private Declare Function sndPlaySound Lib "winmm.dll" Alias "sndPlaySoundA" (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long
Function SndPlay(Pathname As String) As Long
SndPlay = sndPlaySound(Pathname, SND_ASYNC)
End Function

然後切換回到 Excel 工作表, 在某個儲存格(不是A1或C5)輸入如下公式:
=IF(A1 < C5,sndplay("C:\Windows\Media\Windows XP 電話鈴聲.wav"),0)

註: 需確定 "C:\Windows\Media\ "裡有 "Windows XP 電話鈴聲.wav" 這個檔案
於是在 A1 和 C5 裡分別輸入數字, 當 A1 小於 C5 的時候, 
電腦就會播放電話鈴聲

注意: 如果大型的聲音 (.wav) 音效檔所指定，而這個呼叫會失敗，以播放整個檔案，您需要調整適當的音效驅動程式上的設定。


轉載於 http://tw.group.knowledge.yahoo.com/smt-knowldge/listitem/view?iid=15
參考 : http://support.microsoft.com/kb/86281/zh-tw

用 match 反方向 search 最後一個"同等值"

EXCEL DATE 


A: 4 5 4 3 6 7 5 4 5 0 2 6 3
B: 5




=MATCH(2,1/(A1:A13=B1))

confirmed with CTRL+SHIFT+ENTER

formula checks each cell against the target value with (A1:A13=B1) in the array formula, giving an array with TRUE (or 1) for the positions where the cells match, and with FALSE (or 0) for the rest. Dividing 1 by this results in 1s wherever that array was TRUE, and #DIV/0! wherever that array was false. So his formula is evaluated as

=MATCH(2,{#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,1,#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!})

Since no '2' is found in the array, and the last value (1) is found in the 9th position, the MATCH returns 9