【文章內(nèi)容簡(jiǎn)介】 ，必要時(shí)重新編輯公式，然后再次按下Ctrl＋Shift＋Enter鍵。） 此時(shí)你會(huì)看到單元格中的函數(shù)被一對(duì)大括弧‘{}’括起來(lái)了，而且單元格G5:G15中出現(xiàn)了頻率數(shù)組。在單元格H17中使用SUM函數(shù)檢查一下頻率數(shù)據(jù)之和是否等于62。 查看結(jié)果，我們可以看出，。（FREQUENCY返回?cái)?shù)組的最底下單元格G15中，包含了超過(guò)‘bins’。） 由于FREQUENCY函數(shù)的輸出是數(shù)組形式，所以不能對(duì)其中的某個(gè)單元格作修改。如果間隔數(shù)組改變了，則當(dāng)前的輸出數(shù)組必須先刪除，然后重新輸入函數(shù)。 可以將計(jì)算出來(lái)的頻率轉(zhuǎn)換為百分比頻率（分別除以數(shù)據(jù)集的大小62），并進(jìn)而計(jì)算出累積百分比頻率。百分比頻率和累積百分比頻率公式可以在Frequency表中看到。Return for months 162：1－62月份的收益數(shù)據(jù)；Summary Statistics：統(tǒng)計(jì)量；Returns：收益；Ln Returns：對(duì)數(shù)收益；Frequency Distribution：頻率分布；Mean：均值；St Dev：標(biāo)準(zhǔn)差；Max：最大值；Min：最小值；interval：間隔；freq：頻數(shù)；%freq：頻率百分比；%cum freq：累積頻率百分比；Month：月份；Total：共計(jì)【】 對(duì)數(shù)收益的百分比頻率和累積百分比頻率分布 顯示累積百分比頻率的最好方法是用XY圖，數(shù)據(jù)點(diǎn)用一條沒(méi)有標(biāo)記的平滑曲線連接。，可以選擇單元格區(qū)域G5:G14和J5:J14作為數(shù)據(jù)源。注意，如果要選中一些不連續(xù)的區(qū)域，可以先選擇第一個(gè)區(qū)域，然后按下Ctrl鍵不放松，再選擇第二個(gè)以及后繼的區(qū)域。Frequency Distribution：頻率分布；interval：間隔；freq：頻數(shù)；theory：理論值；Cumulative Frequency：累積頻率；actual：實(shí)際值；%freq：頻率百分比；%cum freq：累積頻率百分比；Month：月份；Total：共計(jì)【】 累積百分比頻率圖（實(shí)際數(shù)據(jù)和嚴(yán)格正態(tài)分布的數(shù)據(jù)） 對(duì)于正態(tài)分布的對(duì)數(shù)收益，累積分布呈一個(gè)S形（如圖中的虛線所示）。而實(shí)際的對(duì)數(shù)收益數(shù)據(jù)則可能是由于偏度的原因，與正態(tài)分布有一些偏離。 使用分位數(shù)函數(shù)Quartile QUARTILE(array,quart)函數(shù)返回?cái)?shù)據(jù)集四分位數(shù)。其中的第二個(gè)參數(shù)‘quart’是一個(gè)整數(shù)，表明返回哪一個(gè)四分位數(shù)：即，quart=0時(shí)，返回?cái)?shù)組的最小值；quart=1時(shí)，返回第1/4分位數(shù)（即排序后，處于數(shù)組25%處的值）；quart=2時(shí)，返回中位數(shù)（50%）；quart=時(shí)，返回第3/4分位數(shù)（75%）；如果是4，返回最大值。 QUARTILE函數(shù)提供了一個(gè)快捷并相對(duì)容易的方法來(lái)得到一個(gè)數(shù)據(jù)集的累積分布。： QUARTILE(E10:E71,G22)這里G22中的值為1，于是該函數(shù)返回第1/4四分位數(shù)。，也就是說(shuō)，在選定的數(shù)組中，%。第2/，是中位數(shù)，第3/，低于此數(shù)的對(duì)數(shù)收益占整個(gè)數(shù)據(jù)集的75%。:H25的XY圖，并對(duì)數(shù)據(jù)點(diǎn)作了標(biāo)記?？梢钥闯?。 Quartiles：四分位數(shù)值；Cumulative Frequency from quartiles：四分位點(diǎn)的累積頻率【】 使用QUARTILE計(jì)算Frequency表中的對(duì)數(shù)收益累積分布 。與QUARTILE相關(guān)的函數(shù)，PERCENTILE(array,k)，返回一個(gè)數(shù)據(jù)集的第k個(gè)百分位點(diǎn)的數(shù)值。 使用正態(tài)函數(shù)Norm 在統(tǒng)計(jì)類(lèi)函數(shù)中，與正態(tài)分布有關(guān)的函數(shù)名稱(chēng)都是以NORM開(kāi)頭的，后面跟著字母S時(shí)，則專(zhuān)指標(biāo)準(zhǔn)正態(tài)分布。 NORMSDIST(z)返回標(biāo)準(zhǔn)正態(tài)累積分布函數(shù)值。NORMSINV(probability)返回給定概率下標(biāo)準(zhǔn)正態(tài)分布的分位數(shù)。 函數(shù)NORMDIST(x,mean,standard_dev,cumulative)的功能則強(qiáng)大的多，它適用于任何正態(tài)分布。如果‘cumulative’的值為1（或TRUE），則返回累積分布函數(shù)值；如果‘cumulative’的值為0（或FALSE），則返回概率密度函數(shù)值。，在Norm表中，單元格C5和D5中分別輸入的是概率密度和左尾概率。這兩個(gè)單元格公式都使用了NORMDIST函數(shù)，且均值和方差均為0和1。在C5中，函數(shù)最后一個(gè)輸入項(xiàng)（‘cumulative’）的值為0，因此返回值為概率密度，而在D5中，其值為1，從而得到左尾概率。給定左尾概率時(shí)的分位數(shù)可以用NORMINV函數(shù)得到，如單元格F5所示。 拷貝這些公式，并檢測(cè)其結(jié)果，進(jìn)一步熟悉這些函數(shù)。 最后，我們將得到對(duì)數(shù)收益的累積百分比頻率分布。檢驗(yàn)正態(tài)性的方法之一是采用觀測(cè)數(shù)據(jù)的均值和方差，利用NORMDIST函數(shù)計(jì)算一組理論上的百分比頻率。結(jié)果見(jiàn)工作表Frequency中的K列（）。很明顯，兩者之間有一定的差距。Excel Normal Functions for N(0,1)：Excel正態(tài)分布函數(shù)【】 工作表Snorm中的一般正態(tài)分布函數(shù) Excel提供了大量的函數(shù)來(lái)進(jìn)行數(shù)據(jù)匯總和理論分布建模。本書(shū)的股票和期權(quán)部分將用到它們。 查找類(lèi)函數(shù) 在含有相關(guān)信息的表格中，查找函數(shù)可以根據(jù)不同的輸入信息，檢索出其相關(guān)的信息。例如，我們利用VLOOKUP函數(shù)從波動(dòng)率及對(duì)應(yīng)的期權(quán)價(jià)格表中檢索出給定波動(dòng)率下的布萊克舒爾斯期權(quán)價(jià)格。（布萊克舒爾斯公式的基本理論見(jiàn)第11章。） 一般來(lái)講，函數(shù)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)可以在給定表格（‘table_array’）中的最左一列中查找出匹配值，然后返回同一行中指定列（‘col_index_num’）中的數(shù)值。缺省情況下，表格中的第一列是按升序排列的（這就暗示range_lookup=1（或TRUE））。實(shí)際上，在本例中，最后一個(gè)參數(shù)可以忽略不寫(xiě)。 關(guān)于查找的例子可以在工作表LookUp中找到。為了檢驗(yàn)?zāi)闶欠褚呀?jīng)理解，可以用VLOOKUP函數(shù)查找出不同銷(xiāo)售額下應(yīng)付的傭金，傭金率放在單元格區(qū)域F5:G7中。然后滾動(dòng)到下面的布萊克舒爾斯期權(quán)價(jià)格查詢(xún)表。 所要查找的波動(dòng)率放在C17（20％）中，表格數(shù)組為F17:G27，波動(dòng)率按升序排列，期權(quán)價(jià)格在表格的第二列。因此，單元格D18中的公式為： VLOOKUP(C17, F17:G27,2)返回波動(dòng)率為20％。BlackScholes Call Value Lookup Table：布萊克舒爾斯看漲期權(quán)定價(jià)表；Volatility：波動(dòng)率；BS Call Value：布萊克舒爾斯看漲期權(quán)價(jià)值；【】 工作表LookUp中查找給定波動(dòng)率下的期權(quán)價(jià)格 要查找的值（‘lookup_value’）可以近似（或精確）地匹配表格中第一列的數(shù)據(jù)，在匹配的基礎(chǔ)上找到同一行中指定列的數(shù)據(jù)，并返回?？梢栽囍贑17中輸入不同的值，%、%等，看看這個(gè)查找函數(shù)是如何工作的。 ‘range_lookup’參數(shù)是一個(gè)邏輯值（TRUE或者FALSE），表明是進(jìn)行近似匹配還是精確匹配。如果是TRUE或忽略，則進(jìn)行近似匹配。這時(shí)如果沒(méi)有找到完全相同的值，就會(huì)用比‘lookup_value’小的最大值來(lái)匹配。如果是FALSE，VLOOKUP函數(shù)將進(jìn)行精確匹配，這時(shí)沒(méi)有找到完全相同的值就會(huì)返回錯(cuò)誤值NA。 與VLOOKUP類(lèi)似的函數(shù)HLOOKUP則對(duì)行數(shù)據(jù)進(jìn)行匹配，先查找表格中第一行中的匹配數(shù)據(jù)，然后返回同一列中指定行的數(shù)據(jù)。MATCH和INDEX也是查找類(lèi)函數(shù)。函數(shù)MATCH(lookup_value,lookup_array,match_type)返回單一列（或行）中在指定方式下（‘match_type’），與指定數(shù)值匹配的數(shù)組元素的相對(duì)位置。注意該函數(shù)返回的是匹配數(shù)值在數(shù)組中的位置，而不是數(shù)值本身。 如果match_type為0，函數(shù)則返回精確匹配的數(shù)值位置，不管數(shù)組如何排序。如果match_type為1，則返回近似匹配的數(shù)值位置，此時(shí)需要數(shù)組按升序排列。如果match_type為1，也返回近似匹配的數(shù)值位置，但此時(shí)需要數(shù)組按降序排列。 ，G列中的期權(quán)價(jià)格按升序排列。，可以在D22中輸入公式： =MATCH(C21,G17:G27,1)其返回值為6。 函數(shù)INDEX返回表或區(qū)域里特定行列中的數(shù)值。，特定的行和列為單元格C25和C26中的值，于是返回?cái)?shù)組F17:G27中第六列、第二行中的值。BlackScholes Call Value Lookup Table：布萊克舒爾斯看漲期權(quán)定價(jià)表；Volatility：波動(dòng)率；BS Call Value：布萊克舒爾斯看漲期權(quán)價(jià)值；【】 工作表LookUp中的函數(shù)公式和結(jié)果如果數(shù)組是單一列（或者單一行），參數(shù)‘col_num’（或‘row_num’）可以為空。你可以改變一下單元格D27中的值來(lái)練習(xí)函數(shù)INDEX在這種情況下的使用方法。 我們將在本書(shū)的股票部分應(yīng)用VLOOLUP、MATCH以及INDEX函數(shù)。 其他類(lèi)型函數(shù) 在介紹電子表格公式的同時(shí)，我們也想盡可能地介紹一些‘通用’的公式，它們可以處理一些相關(guān)但不完全相同的情況。例如，可以是0，可以是利息，也可以是本金加上利息。Bond Cashflows：債券現(xiàn)金流；Cash Flows for bonds：債券的現(xiàn)金流量【】 工作表Bonds中含有混合尋址以及嵌套IF函數(shù)的通用公式IF函數(shù)給出了兩種狀況下的不同結(jié)果，而含有一級(jí)嵌套的IF函數(shù)則可以給出三種不同的結(jié)果（嵌套越多，可給出的結(jié)果也越多）。單元格C11中的現(xiàn)金流公式含有一級(jí)嵌套： =IF($B11C$6,C$5,IF($B11=C$6,100+C$5,0))如果把C11中的公式拷貝到C11:H13中，就會(huì)得到每只債券每年的現(xiàn)金流。 對(duì)于類(lèi)型1的債券，現(xiàn)金流依賴(lài)于不同年份（單元格B11）和債券到期時(shí)間（C6）。如果年份在到期時(shí)間之前（B11C6），則現(xiàn)金流就是利息C5；如果剛好到期（B11=C6），則現(xiàn)金流為本金加上利息；其他情況（B11C6）則現(xiàn)金流為0。內(nèi)嵌的IF函數(shù)處理到期年份以及到期年份之后的現(xiàn)金流，而外圍的IF函數(shù)則處理第一種狀態(tài)下的利息收入。 在輸入公式時(shí)我們采用了‘混合尋址’方式，這是為了保證在拷貝單元格公式時(shí)，引用能夠作正確地變化。我們輸入C$6和C$5是為了保證在沿著列C向下拷貝時(shí)，始終將行5和行6中的數(shù)據(jù)作為到期年份和利息率。而$B11則隨著年份的不同變?yōu)?B12和$B13。我們輸入$B11，則當(dāng)公式拷貝到D列中時(shí)，B列中的數(shù)據(jù)仍然作為年份來(lái)計(jì)算，而C$5和C$6則變?yōu)镈$5和D$6。 在復(fù)制復(fù)雜模型的結(jié)果時(shí)，編寫(xiě)這種通用公式得到的收獲比節(jié)約的時(shí)間更有價(jià)值。 審核工具 對(duì)于復(fù)雜的單元格公式，使用審核按鈕組將會(huì)給你很大的幫助。從菜單欄中調(diào)出該按鈕組的方法之一，是點(diǎn)擊視圖菜單下的工具欄，然后選擇用戶(hù)定義…。，選中審核，就會(huì)出現(xiàn)審核按鈕組。 【】 訪問(wèn)審核工具欄及其主要按鈕 ，它們的名稱(chēng)從左到右分別為追蹤引用單元格、移去引用單元格追蹤箭頭、追蹤從屬單元格。 回到電子表格，選中單元格C11，然后點(diǎn)擊追蹤引用單元格按鈕，就可以顯示出單元格C11中引用到的單元格。（圖中也顯示了F13中引用到的單元格。）點(diǎn)擊移去引用單元格追蹤箭頭就可以清除這些箭頭。Bond Cashflows：債券現(xiàn)金流；Cash Flows for bonds：債券的現(xiàn)金流量【】 工作表Bonds中的追蹤引用單元格按鈕使用演示 用戶(hù)定義對(duì)話(huà)框可以按你的意愿來(lái)定制工具欄。如果你點(diǎn)擊命令頁(yè)，并選中某一類(lèi)別，你可以選擇并拖動(dòng)命令列表中的工具按鈕將按鈕拖到工具欄中。相反，你也可以選擇和拖動(dòng)按鈕將按鈕移出工具欄（使它們返回工具框）。 模擬運(yùn)算表（Data Tables） 模擬運(yùn)算表（Data Tables）可以執(zhí)行單元格公式一系列的重復(fù)計(jì)算，而不用拷貝或重新輸入公式。AMFEXCEL工作簿中有不少模擬運(yùn)算表的應(yīng)用實(shí)例。這里用CompoundDTab工作表中計(jì)算復(fù)利和折現(xiàn)因子的例子說(shuō)明單變量和雙變量輸入模擬運(yùn)算表的使用方法。在工作表DSBTab中也有其他一些關(guān)于模擬運(yùn)算表應(yīng)用的例子。 建立單變量模擬運(yùn)算表 %的年連續(xù)復(fù)利因子（單元格C10中）。利率為5%的年折現(xiàn)因子則放在單元格C11中。單元格中的公式也同時(shí)顯示出來(lái)。 可以得到包含不同時(shí)段復(fù)利因子和折現(xiàn)因子的表格，如t=一直到10年。用模擬運(yùn)算表來(lái)完成這項(xiàng)任務(wù)，首先規(guī)劃一下適當(dāng)?shù)陌婷妫鐖D中的第16行及其下方所示的那樣。 需要重復(fù)計(jì)算的公式放在模擬運(yùn)算表的第一行（行16）。因此在D16中，公式為‘=C10’，這使得它與單元格C10中的公式聯(lián)系起來(lái)。同樣的，在E16中，公式為‘=C11’。作為輸入變量，時(shí)間t的信息則放在C列，其起始行在公式行之下。注意，單元格C16是處在公式行中，并且它的值為空。:E26。 Continuous Compounding：連續(xù)復(fù)利；Enter formula(s) for output：輸入公式；Enter numbers for input variable t：為參數(shù)t輸入數(shù)字；【】 工作表CompoundDTab中單變量輸入模擬運(yùn)算表版面布局現(xiàn)在工作表就可以進(jìn)行模擬運(yùn)算表計(jì)算了，只需簡(jiǎn)單地做如下操作：l 選中表格區(qū)域C16:E26l 在主菜單中，點(diǎn)擊數(shù)據(jù)中的模擬運(yùn)算表在對(duì)話(huà)框中：列輸入單元格中輸入單元格C7，然后點(diǎn)擊確定按鈕，表中單元格格式的可讀性更強(qiáng)。單元格中顯示的是數(shù)字，但實(shí)際上包含的是數(shù)組公式。這些數(shù)值是動(dòng)態(tài)的，如果假定的數(shù)據(jù)如利率r，或變量t發(fā)生了改變，則它們將被重新計(jì)算一遍。可以試著將C5中的利率改成6%，然后觀察單元