免费视频淫片aa毛片_日韩高清在线亚洲专区vr_日韩大片免费观看视频播放_亚洲欧美国产精品完整版

1.公式技巧

1.1 在單元格中顯示工作表和工作簿的名稱

在單元格中顯示工作表的名稱，有兩種要領(lǐng)：

（1）建立如下自定義函數(shù)：

Function bookname()

bookname = ActiveSheet.Name

End Function

運用時在單元格中輸入公式：=bookname()，即可返回當前工作簿的標簽名字。

（2）自定義名稱的要領(lǐng)。定義如下名稱：

點擊[插入]à[名稱]à[定義]，名稱的定義為“T_B”，引用位置輸入：“=replace(get.document(1),1, find("]",get.document(1)),)&t(now())”，在單元格輸入“=T_B”就可以顯示當前表名。值得留心的是，返回的工作表名稱隨著工作表名稱的變化而變化。在此引用中，GET.DOCUMENT()是宏表函數(shù)，當數(shù)據(jù)變動時不能自動計算，now()是易失性函數(shù)，任何變動都會強制計算，宏表函數(shù)所以加上now()就可以自動重算了，T()用來將now()產(chǎn)生的數(shù)值轉(zhuǎn)化為空文本。

在單元格中顯示工作簿的名稱，運用系統(tǒng)函數(shù)Cell()：

在單元格中輸入公式：=Cell("filename") ，就會返回該工作簿和工作表的名字（包括絕對路徑名），然后根據(jù)自己的須要運用一些文本處理函數(shù)執(zhí)行 處理即可。

留心：該函數(shù)必須在工作簿已經(jīng)保存的情況下才生效。

1.2 基本判斷單元格最后一位是數(shù)字還是字母

在有些情況下，須要判斷單元格的最后一位是數(shù)字還是字母，可以用下面三個公式之一：

（2）=IF(ISNUMBER(--RIGHT(A1,1)),"數(shù)字","字母")，直接返回數(shù)字或字母。其中“--”的意思是將文本型數(shù)字轉(zhuǎn)化為數(shù)值以便參與運算。

（3）=IF(ISERR(RIGHT(A1)*1),"字母","數(shù)字")，直接返回數(shù)字或字母。

1.3 如何 求出一個人到某指定日期的周歲？

=DATEDIF(起始日期,結(jié)束日期,"Y")

1.4 判斷單元格中存在特定字符

假如判斷A欄里能不能存在"$"字符，有則等于1，沒有則等于0，公式為：

=IF(COUNTIF(A:A,"*$*")>0,1,0)。

1.5 計算某單元格所在的列數(shù)

通常情況下，A列為第1列，AA列為27列?？梢栽贏1單元格中輸入列標，通過下列公式計算出任何列標的列數(shù)：

=COLUMN(INDIRECT(A1&"1"))。例如：“FG”列為第163列。

1.6 DATEDIF函數(shù)的作用

DATEDIF函數(shù)計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)。提供此函數(shù)是為了與 Lotus 1-2-3 兼容。

語法：DATEDIF(start_date,end_date,unit)

Start_date 為一個日期，它代表時間段內(nèi)的第一個日期或起始日期。日期有多種輸入要領(lǐng)：帶引號的文本串（例如 "2001/1/30"）、系列數(shù)（例如，如果運用 1900 日期系統(tǒng)則 36921 代表 2001 年 1 月 30 日）或其他公式或函數(shù)的結(jié)果（例如，DATEVALUE("2001/1/30")）。

End_date 為一個日期，它代表時間段內(nèi)的最后一個日期或結(jié)束日期。

Unit 為所需信息的返回類型。

Unit返回"Y"時間段中的整年數(shù)。"M"時間段中的整月數(shù)。"D"時間段中的天數(shù)。"MD"start_date 與 end_date 日期中天數(shù)的差。忽略日期中的月和年。"YM"start_date 與 end_date 日期中月數(shù)的差。忽略日期中的日和年。"YD"start_date 與 end_date 日期中天數(shù)的差。忽略日期中的年。

說明：Microsoft Excel 按順序的系列數(shù)保存日期，這樣就可以對其執(zhí)行 計算。如果工作簿運用 1900 日期系統(tǒng)，則 Excel 會將 1900 年 1 月 1 日保存為系列數(shù) 1。而如果工作簿運用 1904 日期系統(tǒng)，則 Excel 會將 1904 年 1 月 1 日保存為系列數(shù) 0，（而將 1904 年 1 月 2 日保存為系列數(shù) 1）。例如，在 1900 日期系統(tǒng)中 Excel 將 1998 年 1 月 1 日保存為系列數(shù) 35796，因為該日期距離 1900 年 1 月 1 日為 35795 天。請查閱 Microsoft Excel 如何 存儲日期和時間。

Excel for Windows 和 Excel for Macintosh 運用不同的默認日期系統(tǒng)。有關(guān)細致信息，請參閱 NOW。示例

DATEDIF("2001/1/1","2003/1/1","Y") 等于 2，即時間段中有兩個整年。

DATEDIF("2001/6/1","2002/8/15","D") 等于 440，即在 2001 年 6 月 1 日和 2002 年 8 月 15 日之間有 440 天。

DATEDIF("2001/6/1","2002/8/15","YD") 等于 75，即在 6 月 1 日與 8 月 15 日之間有 75 天，忽略日期中的年。

DATEDIF("2001/6/1","2002/8/15","MD") 等于 14，即開始日期 1 和結(jié)束日期 15 之間的差，忽略日期中的年和月。

1.7 在一個單元格中指定字符出現(xiàn)的次數(shù)

例如在A1單元格中有“abcabca”字符串，求“a”在單元格A1內(nèi)出現(xiàn)次數(shù)，用下列公式：

=LEN(A1)-LEN(SUBSTITUTE(A1, "a", ""))。

1.8 日期形式的轉(zhuǎn)換

我們在有些情況下寫日期會用“20060404”表示，如何 轉(zhuǎn)換成“2006-04-04”的標準日期格式，用下面的兩個公式之一（假定在A1單元格中有原始日期）：

=TEXT(A1,"0000-00-00")

=TEXT(A1,"????-??-??")。

也可以運用以下公式，轉(zhuǎn)換成“2006-4-4”的格式。

=LEFT(A1,4)&SUBSTITUTE(RIGHT(A1,4),0,"-")。

反之，如何 把“2006年4月4日”轉(zhuǎn)換成“20060404”？可以運用 下面的公式之一（假定在A1單元格中有原始日期）：

=YEAR(A1)&TEXT(MONTH(A1),"00")&TEXT(DAY(A1),"00" )

=YEAR(A1)&IF(MONTH(A1)<10,"0"&MONTH(A1),MONTH(A1))&IF(DAY(DAY(A1)<10),"0"&DAY(A1),DAY(A1))

=TEXT(A1,"yyyymmdd")。

也可以直接自定義格式：yyyymmdd。

1.9 用“定義名稱”的要領(lǐng)突破IF函數(shù)的嵌套限定

Excel中的IF()函數(shù)的一個眾所周知的限定是嵌套不能超過7層。例如下面的公式是不正確的，因為嵌套層數(shù)超過了限定。

=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33,IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,IF($A$4=7,77,FALSE))))))))

通常的要領(lǐng)會考慮用VBA代替。但是也可以可以通過對公式的一部分”定義名稱”來處理這種限定定義一個名叫”OneToSix”的名稱, 里面包括公式：

=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33,IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,FALSE))))))))

再定義另一個名叫”SevenToThirteen”的名稱，里面包括公式：

=IF(Sheet1!$A$4=7,77,IF(Sheet1!$A$4=8,88,IF(Sheet1!$A$4=9,99,IF(Sheet1!$A$4=10,100,IF(Sheet1!$A$4=11,110,IF(Sheet1!$A$4=12,120,IF(Sheet1!$A$4=13,130,"NotFound")))))))

最后單元格中輸入下面的公式：

=IF(OneToSix,OneToSix,SevenToThirteen)

1.10 動態(tài)求和

舉一個基本例子：例如對于A列，求出A1到當前單元格行標前面一行的單元格中的數(shù)值之和，更直接地說，如果當前單元格在B17，那么求A1:A16之和。運用 下面的公式：

=SUM(INDIRECT("A1:A"&ROW()-1))。

1.11 COUNTIF函數(shù)的16種公式配置（設(shè)DATA為區(qū)域名稱）

（1）返加包含值12的單元格數(shù)量：=COUNTIF(DATA,12)

（2）返回包含負值的單元格數(shù)量：=COUNTIF(DATA,"<0")

（3）返回不等于0的單元格數(shù)量：=COUNTIF(DATA,"<>0")

（4）返回大于5的單元格數(shù)量：=COUNTIF(DATA,">5")

（5）返回等于單元格A1中內(nèi)容的單元格數(shù)量：=COUNTIF(DATA,A1)

（6）返回大于單元格A1中內(nèi)容的單元格數(shù)量：=COUNTIF(DATA,“>”&A1)

（7）返回包含文本內(nèi)容的單元格數(shù)量：=COUNTIF(DATA,“*”)

（8）返回包含三個字符內(nèi)容的單元格數(shù)量：=COUNITF(DATA,“???”)

（9）返回包含單詞"GOOD"(不分大小寫)內(nèi)容的單元格數(shù)量：=COUNTIF(DATA,“GOOD”)

（10）返回在文本中任何位置包含單詞"GOOD"字符內(nèi)容的單元格數(shù)量：=COUNTIF(DATA,“*GOOD*”)

（11）返回包含以單詞"AB"(不分大小寫)開頭內(nèi)容的單元格數(shù)量：=COUNTIF(DATA,“AB*”)

（12）返回包含當前日期的單元格數(shù)量：=COUNTIF(DATA,TODAY()）

（13）返回大于平均值的單元格數(shù)量：=COUNTIF(DATA,">"&AVERAGE(DATA))

（14）返回平均值上面超過三個標準誤差的值的單元格數(shù)量：=COUNTIF(DATA,“>"&AVERAGE(DATA)+STDEV(DATA)*3)

（15）返回包含值為或-3的單元格數(shù)量：=COUNTIF(DATA,3）+COUNIF(DATA,-3)

（16）返回包含值邏輯值為TRUE的單元格數(shù)量：=COUNTIF(DATA,TRUE)

1.12 計算一個日期是一年中的第幾天

例如2006年7月29日是本年中的第幾天？在一年中，顯示是第幾天用什么函數(shù)呢？假定A1中是日期，運用 下列公式：

=A1-DATE(YEAR(A1),1,0)，將單元格格式配置為常規(guī)，返回210，即2006年7月29日是2006年的第210天。

1.13 如何 用公式求出最大值所在的行？

如A1：A10中有10個數(shù)，如何求出最大的數(shù)在哪個單元格？

=MATCH(LARGE(A1:A10,1),A1:A10,0)

=ADDRESS(MATCH(SMALL(A1:A10,COUNTA(A1:A10)),A1:A10,0),1)

=ADDRESS(MATCH(MAX(A1:A10,1),A1:A10,0),1)

1.14 在Excel中的絕對引用與相對引用之間切換

在Excel中建立公式時，該公式可以運用相對引用，即相對于公式所在的位置引用單元；也可以運用絕對引用，即引用特定位置上的單元。引用由所在單元格的“列的字母”和“行的數(shù)字”組成，絕對引用由在“列的字母”和“行的數(shù)字”前面加“$”表示，例如，$B$1是對第一行B列的絕對引用。公式中還可以混合運用相對引用和絕對引用?？梢赃\用 F4切換相對引用和絕對引用，選中包含公式的單元格，在公式欄中選擇想要改動的引用，按F4鍵可以執(zhí)行 切換。

1.15 在Excel公式和結(jié)果之間高速切換

在excel工作表中輸入計算公式時，可以運用 “Ctrl+`（中音號）”鍵來決定顯示或潛藏公式，可讓儲存格顯示計算的結(jié)果，還是公式本身。

1.16 如果某列中有大于0和小于0的數(shù)，將小于0數(shù)字所在的行自動刪除

假定在A1-A6中有大于0和小于0的數(shù)，可以用下面的VBA程序?qū)崿F(xiàn)：

for i=6 to 1 step -1

if cells(i,1)<0 then rows(i).Delete

next i

1.17 奇數(shù)行和偶數(shù)行求和

有時候須要奇數(shù)行和偶數(shù)行單獨求和，例如要求A列第1行至1000行中奇數(shù)行之和，運用 公式=SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000),2))，要求這些行中偶數(shù)行之和，運用 公式=SUMPRODUCT((A1:A1000)*NOT(MOD(ROW(A1:A1000),2)))。

1.18 用函數(shù)來獲取單元格地址

在復(fù)雜的計算中，往往要獲知單元格的地址，可以用函數(shù)=ADDRESS(ROW(),COLUMN())獲得當前單元格的地址。

1.19 求一列中某個特定的值對應(yīng)的另外列的最大或最小值

為了直觀起見，舉一個基本的例子：例如在A1:A10中有若干臺計算機、打印機、傳真機等物品的名稱，在B1:B10中有上述設(shè)備對應(yīng)的價格，求“計算機”對應(yīng)的最低價格?？梢杂霉剑?/p>

=min(if(a1:a10="計算機",b1:b10))，輸入該公式后按Ctrl+Shift+Enter完成。

1.20 自動記錄數(shù)據(jù)錄入時間

運用 VBA實現(xiàn)，建立一個Time.xls文檔，輸入以下VBA代碼：

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 1 Then

 Exit Sub

Else

 Target.Offset(0, 1) = Now

End If

End Sub

1.21 如果一個單元格中既有數(shù)字又有字母，如何提取其中的數(shù)字呢

Function getnumber(rng As String) As String

Dim mylen As Integer

Dim mystr As String

mylen = Len(rng)

For I = 1 To mylen

 mystr = Mid(rng, I, 1)

 If Asc(mystr) >= 48 And Asc(mystr) <= 57 Then

 getnumber = getnumber & mystr

 End If

Next I

End Function

1.22 Excel數(shù)組的使用

數(shù)組就是單元的集合或是一組處理的值集合?？梢詫懸粋€數(shù)組公式，即輸入一個單個的公式，它執(zhí)行多個輸入的操作并產(chǎn)生多個結(jié)果——每個結(jié)果顯示在一個單元中。數(shù)組公式可以看成是有多重數(shù)值的公式。與單值公式的不同之處在于它可以產(chǎn)生一個以上的結(jié)果。一個數(shù)組公式可以占用一個或多個單元。數(shù)組的元素可多達6500個。

（1）了解數(shù)組

首先我們通過多個例子來說明數(shù)組是如何 工作的。我們可以從圖中看到，在“B”列中的數(shù)據(jù)為銷售量，在“C”列中的數(shù)據(jù)是銷售單價，要求計算出每種產(chǎn)品的銷售額和總的銷售金額，一般的做法是計算出每種產(chǎn)品的銷售額，然后再計算出總的銷售額。但是如果我們改用數(shù)組，就可以只鍵入一個公式來完成這些運算。

輸入數(shù)組公式的步驟為：

選定要存入公式的單元格，在本例中我們選擇“D4”單元格。輸入公式=SUM(B2:B4*C2:C4)，但不要按下[Enter]鍵（輸入公式的要領(lǐng)和輸入普通的公式一樣），按下[Shift]+[Ctrl]+[Enter]鍵。我們就會看到在公式外面加上了一對大括號“{}”，如圖 7-36所示。

在單元格“D”中的公式“=SUM(B2:B4*C2:C4)”, 表示“B2: B4”范圍內(nèi)的每一個單元格和“C2：C4”內(nèi)相對應(yīng)的單元格相乘，也就是把每個地區(qū)的銷售量和銷售單價相乘，相乘的結(jié)果共有3個數(shù)字，每個數(shù)字代表一個地區(qū)的銷售額，而“SUM”函數(shù)將這些銷售額相加，就得到了總的銷售額。

下面我們再以運用數(shù)組計算3種產(chǎn)品的銷售額為例，來說明如何 產(chǎn)生多個計算結(jié)果。其操作流程如下：

 (1) 選擇“D2：D4”單元格區(qū)域，該區(qū)域中的每個單元格保存的銷售金額。如圖7-37所示。

 (2) 在“D2”單元格中輸入公式“=B2:B4*C2:C4”（不按[Enter]鍵）按下[Shift]+[Ctrl]+[Enter]”鍵，我們就可以從圖7-38中看到執(zhí)行后的結(jié)果。同時我們可以看到“D2”到“D4”的格中都會出現(xiàn)用大括弧“{ }”框住的函數(shù)式，這表示“D2” 到“D4”被當作一個單元格來處理，所以不能對“D2”到“D4”中的任一格作任何單獨處理，必須針對整個數(shù)組來處理。

 （2）運用數(shù)組常數(shù)

我們也可以在數(shù)組中運用常數(shù)值。這些值可以放在數(shù)組公式中運用區(qū)域引用的地點。要在數(shù)據(jù)公式中運用數(shù)組常數(shù)，直接將該值輸入到公式中并將它們放在括號里。例如，在圖7-39中，就運用了數(shù)組常數(shù)執(zhí)行 計算。

常數(shù)數(shù)組可以是一維的也可以是二維的。一維數(shù)組可以是垂直的也可以是水平的。在一維水平數(shù)組中的元素用逗號分開。下面是一個一維數(shù)組的例子。例如數(shù)組：{10,20,30,40,50}。在一維垂直數(shù)組中的元素用分號分開。在下面的例子是一個6×1的數(shù)組，{100;200;300;400;500;600}。

對于二維數(shù)組，用逗號將一行內(nèi)的元素分開，用分號將各行分開。下一個例子是“4 ×4”的數(shù)組（由4行4列組成）：{100,200,300,400;110, … … ;130,230,330,440}。

留心：不可以在數(shù)組公式中運用列出常數(shù)的要領(lǐng)列出單元引用、名稱或公式。例如：{2*3,3*3,4*3}因為列出了多個公式，是不能用的。{A1,B1,C1}因為列出多個引用，也是不能用的。不過可以運用一個區(qū)域，例如{A1:C1}。

對于數(shù)組常量的內(nèi)容，可由下列準則構(gòu)成：

數(shù)組常量可以是數(shù)字、文字、邏輯值或不正確值。

數(shù)組常量中的數(shù)字，也可以運用整數(shù)、小數(shù)或科學(xué)記數(shù)格式。

文字必須以雙引號括住。

同一個數(shù)組常量中可以含有不同類型的值。

數(shù)組常量中的值必須是常量，不可以是公式。

數(shù)組常量不能含有貨幣符號、括號或百分比符號。

所輸入的數(shù)組常量不得含有不同長度的行或列。

（3）數(shù)組的編輯

數(shù)組包含數(shù)個單元格，這些單元格形成一個整體，所以，數(shù)組里的某一單元格不能單獨編輯。在編輯數(shù)組前，必須先選取整個數(shù)組。

選取數(shù)組的步驟為：

(1) 選取數(shù)組中的任一單元格。

 (2) 在“編輯”菜單中選擇“定位”命令或者按下[F5]鍵，出現(xiàn)一個“定位”對話框。按下“定位條件”按鈕，出現(xiàn)一個定位條件對話框，如圖7-40所示。

選擇“當前數(shù)組”選項，最后按下“確定”按鈕，就可以看到數(shù)組被選定了。

編輯數(shù)組的步驟為:

選定要編輯的數(shù)組，移到數(shù)據(jù)編輯欄上按[F2]鍵或單擊左鍵，使代表數(shù)組的括號消散，之后就可以編輯公式了。編輯完成后，按下[Shift] +[Ctrl] + [Enter]鍵。

若要刪除數(shù)組，其步驟為：選定要刪除的數(shù)組，按[Ctrl]+[Delete]或選擇編輯菜單中的“清理 ”。

（4）數(shù)組的擴充

在公式或函數(shù)中運用數(shù)組常量時，其它運算對象或參數(shù)應(yīng)該和第一個數(shù)組具有相同的維數(shù)。必要時，Microsoft Excel 會將運算對象擴展，以符合操作須要的維數(shù)。每一個運算對象的行數(shù)必須和含有最多行的運算對象的行數(shù)一樣，而列數(shù)也必須和含有最多列數(shù)對象的列數(shù)一樣。

例如: = SUM({1,2,3}+{4,5,6}）內(nèi)的第一個數(shù)組為1×3,得到的結(jié)果為1+4、2+5和3+6的和,也就是21。如果將公式寫成 = SUM({1,2,3}+4}）,則第二個數(shù)據(jù)并不是數(shù)組，而是一個數(shù)值，為了要和第一個數(shù)組相加，Excel 會自動將數(shù)值擴充成1 ×3 的數(shù)組。運用 =SUM({1,2,3}+{4,4,4})做計算，得到的結(jié)果為1+4、2+4和3+4的和, 即18。

將數(shù)組公式輸入單元格區(qū)域中時，所運用的維數(shù)應(yīng)和這個公式計算所得數(shù)組維數(shù)相同。這樣，Microsoft Excel 才能把計算所得的數(shù)組中的每一個數(shù)值放入數(shù)組區(qū)域的一個單元格內(nèi)。

如果數(shù)組公式計算所得的數(shù)組比選定的數(shù)組區(qū)域還小，則 Microsoft Excel會將這個數(shù)組擴展，以便將它填入整個數(shù)組區(qū)域內(nèi)。例如：={1,2;3,4}*2擴充后的公式就會變?yōu)?{1,2;3,4}*{2,2;2,2}，則相應(yīng)的計算結(jié)果為“2,4,6,8”。再如：輸入公式={1,2;3,4}*{2,3}擴充后的公式就會變?yōu)?{1,2;3,4}*{2,3;2,3} ，則相應(yīng)的計算結(jié)果為“2,6,6,12”。

如果 Microsoft Excel 將一個數(shù)組擴展到可以填入比該數(shù)組公式大的區(qū)域內(nèi)，而沒有擴大值可用的單元格內(nèi)，這樣就會出現(xiàn)#N/A不正確值。例如：={1,2;3,4}={1,2,3} 擴充后的公式就會變?yōu)?{1,2,#N/A;3,4,#N/A}*{1,2,#/A;1.2.#N/A} ，而相應(yīng)的計算結(jié)果為“2,4,#N/A,4,6,#N/A”。

如果數(shù)組公式計算所得的數(shù)組比選定的數(shù)組區(qū)域還要大，則超過的值不會出現(xiàn)在工作表上。

1.23 數(shù)組的使用

（1）數(shù)組公式的實現(xiàn)要領(lǐng)：

其實這些都是數(shù)組公式，數(shù)組公式的輸入要領(lǐng)是將公式輸入后，不要直接按回車鍵（Enter），而是要同時按Ctrl+Shift+Enter，這時計算機自動會為你添加“{ }”的。

在論壇上，為了告訴大家這是數(shù)組公式，故在公式的頭尾都加上了“{ }”。如果不注意按回車了，可以用鼠標點一下編輯欄中的公式，再按Ctrl+Shift+Enter。

編輯或刪除數(shù)組公式編輯數(shù)組公式時，須選取數(shù)組區(qū)域并且激活編輯欄，公式兩邊的花括號將消散，然后編輯公式，最后按Ctrl＋Shift＋Enter鍵。選取數(shù)組公式所占有的全部區(qū)域后，按Delete鍵即可刪除數(shù)組公式。

數(shù)組常量的運用數(shù)組公式中還可運用數(shù)組常量，但必須自己鍵入花括號“{ }”將數(shù)組常量括起來，并且用“，”和“；”分離元素。其中“，”分離不同列的值，“；”分離不同行的值。

2、數(shù)組公式的原理：

數(shù)組公式，說白了就是同時對一組或幾組數(shù)同時處理，然后得到須要的答案。運用數(shù)組公式的最主要的原理是數(shù)于數(shù)之間一一對應(yīng)。

1、假設(shè)要將A1：A50區(qū)域中的所有數(shù)值舍入到2位小數(shù)位，然后對舍入的數(shù)值求和。很自然地就會想到運用公式：=ROUND（A1，2）＋ROUND（A2，2）＋…＋ROUND（A50，2）?；蛘咛砑覴OUND輔助列（A1=ROUND（A1，2）），然后對輔助用SUM函數(shù)合計（=SUM（A1：A50））。

如果用數(shù)組公式就不要這么麻煩，公式為：

{=SUM（ROUND（A1：A50，2））}，它的意思即為在數(shù)組A1：A50用ROUND函數(shù)執(zhí)行 二位小數(shù)的四舍五入，然后執(zhí)行 合計。

2、假設(shè)一題為A1：A10區(qū)域中為商品單價，B1：B10為對應(yīng)的銷售數(shù)量，須要統(tǒng)計總銷售額，常規(guī)做法須要添加輔助列C列，在C列中計算出C1：C10的每個單價的銷售額（C1=A1*B1），然后執(zhí)行 SUM合計（C11=SUM（C1：C10））。

而數(shù)組公式為：

{=SUM（A1：A10*B1：B10）}

3、留心：關(guān)于常數(shù)項的數(shù)組可以直接手工添加{ }，如此公式 = SUM({1,2,3}+{4,5,6}），這也是數(shù)組公式的一種形式。

須要統(tǒng)計如下圖所示銷量的頻率分布，即分別統(tǒng)計銷量在5000以下、5000到10000、10000到50000以及大于50000的銷售點數(shù)量

a2b2C2

銷售點 銷售額 分段點

城北001 4100 5000

城北002 15890 10000

城南001 8700 50000

城南002 25900

城南003 5800

城東001 15300

城東002 38000

城東003 9800

城西001 56000

城西002 72050

城中001 130000

城中002 60400

城中003 48700

步驟：

"1、打造如上圖所示的表格

2、選中單元格G7：G10，直接輸入公式：=FREQUENCY（B4：B14，c4：c6）

3、輸入公式后，按CTRL+SHIFT+ENTER鍵結(jié)束

"

類型 日期 單價 銷售數(shù)量

A 2005-6-15 1000 10

B 2005-6-20 1000 15

B 2005-7-1 4000 10

C 2005-7-10 4000 11

B 2005-8-15 9000 13

C 2005-8-20 9000 15

A 2005-9-30 1000 14

A 2005-10-10 1000 20

B 2005-10-15 4000 25

類型從B1格開始

計算B產(chǎn)品8月份銷量

13{=SUM(IF(($B$2:$B$10="B")*(MONTH($C$2:$C$10)=8),($E$2:$E$10),0))}

13 {=SUM(($B$2:$B$10="B")*(MONTH($C$2:$C$10)=8)*($E$2:$E$10))}

計算A產(chǎn)品和B產(chǎn)品的銷量

107 {=SUM(IF(($B$2:$B$10="A")+($B$2:$B$10="B"),($E$2:$E$10),0))}

107 {=SUM((($B$2:$B$10="A")+($B$2:$B$10="B"))*($E$2:$E$10))}

計算8月份前不包括B產(chǎn)品銷量和8月后不包括C產(chǎn)品銷量

49 {=SUM(IF(((MONTH($C$2:$C$10)<8)<>($B$2:$B$10="B"))*((MONTH($C$2:$C$10)>=8)<>($B$2:$B$10="C")),$E$2:$E$10))}

49 {=SUM(IF(((MONTH($C$2:$C$10)<8)-($B$2:$B$10="B"))*((MONTH($C$2:$C$10)>=8)-($B$2:$B$10="C")),$E$2:$E$10))}

49 {=SUM(((MONTH($C$2:$C$10)<8)<>($B$2:$B$10="B"))*((MONTH($C$2:$C$10)>=8)<>($B$2:$B$10="C"))*$E$2:$E$10)}

以上公式中*的意思為AND,+的意思為OR,-的意思為<> "不等于"

1.24 求一個單元格數(shù)值中的最大數(shù)字和個數(shù)字之和

我們平時都是對不同單元格之間的數(shù)字執(zhí)行 計算，但是在一個單元格內(nèi)部，各數(shù)字之間有什么聯(lián)系？這是一個很有創(chuàng)新意識的命題。例如A1中的數(shù)字為389732，求其中最大的數(shù)字9，求這和6個數(shù)字之和為32。

（1）求其中最大的數(shù)字，運用 數(shù)組公式：

{=MAX(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)}

先輸入=MAX(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)，再按Ctrl+Shift+Enter。

（2）求其中數(shù)字之和，運用 下面的公式：

=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)

1.25 邏輯函數(shù)的非邏輯表現(xiàn)

例如，求取范圍Data中小于0或大于5的數(shù)值之和:

 正確用法：

 {=SUM(IF((Data<0)+(Data>5),Data))}

不正確用法：

 {=SUM(IF(OR(Data<0,Data>5),Data))}

1.26 在EXCEL的數(shù)組公式中ROW函數(shù)的用法

在EXCEL的數(shù)組公式中，ROW()是一個非常有用的函數(shù)，現(xiàn)在舉個例子來說明。

（1）返回一列中最后一個數(shù)值

{=INDEX(A:A,MAX(ROW(A1:A100)*(A1:A100<>"")))}

在這個公式中用ROW函數(shù)返回A1:A100<>""即A1格到A100中不為空的單元格，它是一組數(shù)據(jù)，然后用MAX確定最大的一個行號，即最后一格不為空的單元格，然后用INDEX，來返回A1到A100中A列最大行號的那個數(shù)據(jù)。

（2）同理如果要返回一行中最后一個數(shù)值則為

{=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>"")))}

（3）下面出一個小題目，如果有興趣想學(xué)數(shù)組的可以試一下，返回A列100行中最后一個有數(shù)值的行號的公式是什么？

{=MAX(IF(A1:A100<>"",ROW(A1:A100),""))}

1.27 返回最大值的行號和地址

返回最大值的行號：

{=min(if(A1:A100=max(A1:A100),row(A1:A100),"")}

返回最大值的地址：

{=ADDRESS(MIN(IF(A1:A100=max(A1:A100),ROW(A1:A100),"")),COLUMN(A1:A100))}

{=MAX(IF((A1:A100<>"")*ISNUMBER(A1:A100),ROW(A1:A100),""))}

1.28 Excel多見不正確及處理辦法

經(jīng)常用Excel可能都會遇到一些不正確值信息，如：# N/A！、#VALUE！、#DIV/O！等等，出現(xiàn)這些不正確的原由有很多種，如果公式不能計算正確結(jié)果，Excel將顯示一個不正確值，例如，在須要數(shù)字的公式中運用文本、刪除了被公式引用的單元格，或者運用了寬度不足以顯示結(jié)果的單元格。以下是幾種多見的不正確及其處理要領(lǐng)。

（1）#####！

原由：如果單元格所含的數(shù)字、日期或時間比單元格寬，或者單元格的日期時間公式產(chǎn)生了一個負值，就會產(chǎn)生#####！不正確。

處理要領(lǐng)：如果單元格所含的數(shù)字、日期或時間比單元格寬，可以通過拖動列表之間的寬度來修改列寬。如果運用的是1900年的日期系統(tǒng)，那么Excel中的日期和時間必須為正值，用較早的日期或者時間值減去較晚的日期或者時間值就會導(dǎo)致#####！不正確。如果公式正確，也可以將單元格的格式改為非日期和時間型來顯示該值。

（2）#VALUE!

當運用不正確的參數(shù)或運算對象類型時，或者當公式自動更正功能不能更正公式時，將產(chǎn)生不正確值#VALUE!。

原由一：在須要數(shù)字或邏輯值時輸入了文本，Excel不能將文本轉(zhuǎn)換為正確的數(shù)據(jù)類型。

處理要領(lǐng)：確認公式或函數(shù)所需的運算符或參數(shù)正確，并且公式引用的單元格中包含有效的數(shù)值。例如：如果單元格A1包含一個數(shù)字，單元格A2包含文本"學(xué)籍"，則公式"=A1+A2"將返回不正確值#VALUE!?？梢杂肧UM工作表函數(shù)將這兩個值相加（SUM函數(shù)忽略文本）：=SUM（A1:A2）。

原由二：將單元格引用、公式或函數(shù)作為數(shù)組常量輸入。

處理要領(lǐng)：確認數(shù)組常量不是單元格引用、公式或函數(shù)。

原由三：賦予須要單一數(shù)值的運算符或函數(shù)一個數(shù)值區(qū)域。

處理要領(lǐng)：將數(shù)值區(qū)域改為單一數(shù)值。修改數(shù)值區(qū)域，使其包含公式所在的數(shù)據(jù)行或列。

（3）#DIV/O!

當公式被零除時，將會產(chǎn)生不正確值#DIV/O!。

原由一：在公式中，除數(shù)運用了指向空單元格或包含零值單元格的單元格引用（在Excel中如果運算對象是空白單元格，Excel將此空值當作零值）。

處理要領(lǐng)：修改單元格引用，或者在用作除數(shù)的單元格中輸入不為零的值。

原由二：輸入的公式中包含明顯的除數(shù)零，例如：=5/0。

處理要領(lǐng)：將零改為非零值。

（4）#NAME?

在公式中運用了Excel不能識別的文本時將產(chǎn)生不正確值#NAME?。

原由一：刪除了公式中運用的名稱，或者運用了不存在的名稱。

處理要領(lǐng)：確認運用的名稱確實存在。選擇菜單"插入""名稱""定義"命令，如果所需名稱沒有被列出，請運用 "定義"命令添加相應(yīng)的名稱。

原由二：名稱的拼寫不正確。

處理要領(lǐng)：修改拼寫不正確的名稱。

原由三：在公式中運用標志。

處理要領(lǐng)：選擇菜單中"工具""選項"命令，打開"選項"對話框，然后單擊"重新計算"標簽，在"工作薄選項"下，選中"接受公式標志"復(fù)選框。

原由四：在公式中輸入文本時沒有運用雙引號。

處理要領(lǐng)：Excel將其解釋為名稱，而不理會用戶準備將其用作文本的想法，將公式中的文本括在雙引號中。例如：下面的公式將一段文本"總計："和單元格B50中的數(shù)值合并在一起：="總計："&B50

原由五：在區(qū)域的引用中缺少冒號。

處理要領(lǐng)：確認公式中，運用的所有區(qū)域引用都運用冒號。例如：SUM（A2:B34）。

（5）#N/A

原由：當在函數(shù)或公式中沒有可用數(shù)值時，將產(chǎn)生不正確值#N/A。

處理要領(lǐng)：如果工作表中某些單元格暫時沒有數(shù)值，請在這些單元格中輸入"#N/A"，公式在引用這些單元格時，將不執(zhí)行 數(shù)值計算，而是返回#N/A。

（6）#REF!

當單元格引用無效時將產(chǎn)生不正確值#REF！。

原由：刪除了由其他公式引用的單元格，或?qū)⒁苿訂卧裾迟N到由其他公式引用的單元格中。

處理要領(lǐng)：修改公式或者在刪除或粘貼單元格之后，立即單擊"撤消"按鈕，以恢復(fù)工作表中的單元格。

（7）#NUM！

當公式或函數(shù)中某個數(shù)字有疑問時將產(chǎn)生不正確值#NUM！。

原由一：在須要數(shù)字參數(shù)的函數(shù)中運用了不能接受的參數(shù)。

處理要領(lǐng)：確認函數(shù)中運用的參數(shù)類型正確無誤。

原由二：運用了迭代計算的工作表函數(shù)，例如：IRR或RATE，并且函數(shù)不能產(chǎn)生有效的結(jié)果。

處理要領(lǐng)：為工作表函數(shù)運用不同的原始值。

原由三：由公式產(chǎn)生的數(shù)字太大或太小，Excel不能表示。

處理要領(lǐng)：修改公式，使其結(jié)果在有效數(shù)字范圍之間。

（8）#NULL！

當試圖為兩個并不相交的區(qū)域指定交叉點時將產(chǎn)生不正確值#NULL！。

原由：運用了不正確的區(qū)域運算符或不正確的單元格引用。

處理要領(lǐng)：如果要引用兩個不相交的區(qū)域，請運用聯(lián)合運算符逗號（，）。公式要對兩個區(qū)域求和，請確認在引用這兩個區(qū)域時，運用逗號。如：SUM（A1:A13，D12:D23）。如果沒有運用逗號，Excel將試圖對同時屬于兩個區(qū)域的單元格求和，但是由于A1:A13和D12:D23并不相交，所以他們沒有共同的單元格。

1.29 金額大寫的轉(zhuǎn)換

假設(shè)A1單元格為原始數(shù)據(jù)，即小寫數(shù)字。

公式法一：

=IF(A1=0,"零元整",IF(A1<0,"負",)&IF(INT(ABS(A1)),TEXT(INT(ABS(A1)),"[dbnum2]")&"元",)&IF(INT(ABS(A1)*10)-INT(ABS(A1))*10,TEXT(INT(ABS(A1)*10)-INT(ABS(A1))*10,"[dbnum2]")&"角",IF(INT(ABS(A1))=ABS(A1),,"零"))&IF(ROUND(ABS(A1)*100-INT(ABS(A1)*10)* 10,) , TEXT(ROUND(ABS(A1)*100-INT(ABS(A1)*10)*10,),"[dbnum2]")&"分","整"))

公式法二：

=IF(A1<0,"負",)&TEXT(TRUNC(ABS(A1)),"[DBNum2]G/通用格式")&"元"&IF(ROUND(A1,3) =ROUND(A1,),"整",TEXT(RIGHT(TRUNC(A1*10),1),"[DBNum2]G/通用格式")&"角"&IF (ROUND(A1,3)=ROUND(A1,1),"整",TEXT(RIGHT(ROUND((A1*100),),1),"[DBNum2]G/通用格式")&"分"))

公式法三：

=CONCATENATE(TEXT(INT(A1),"[DBNum2][$-804]G/通用格式")&"元"&IF((INT(A1*10) -INT(A1)*10)=0,"",TEXT(IF(AND(CEILING(A1*100,1)-INT(A1*10)*10=10,INT(A1*1000)-INT(A1*100)*10>=5),INT(A1*10)-INT(A1)*10+1,INT(A1*10)-INT(A1)*10),"[DBNum2][$-804]G/通用格式")&"角")&IF(OR(INT(A1*100)-INT(A1*10)*10=0,(IF(INT(A1*1000)-INT(A1*100)*10>=5,CEILING(A1* 100,1)-INT(A1*10)*10=10,FALSE))),"整",(IF(INT(A1*1000)-INT(A1*100)*10>=5,(IF(CEILING(A1 *100,1)-INT(A1*10)*10=10,"",(TEXT(CEILING(A1*100,1)-INT(A1*10)*10,"[DBNum2][$-804]G/通用格式")&"分"))),(TEXT(INT(A1*100)-INT(A1*10)*10,"[DBNum2][$-804]G/通用格式")&"分")))),"(￥",FIXED(A1,2,TRUE),"元)")

四、VBA代碼法（自定義函數(shù)）

Public Function BigNum(xiaoxie As Currency)

Application.Volatile

Dim fuhao As String

fuhao = ""

If xiaoxie < 0 Then

xiaoxie = -xiaoxie

fuhao = "負"

End If

If xiaoxie = 0 Then

BigNum = "零元整"

Else

Const cNum = "零壹貳叁肆伍陸柒捌玖-萬仟佰拾億仟佰拾萬仟佰拾元角分"

Const cCha = "零仟零佰零拾零零零零零億零萬零元億萬零角零分零整-零零零零零億萬元億零整整"

BigNum = ""

sNum = Trim(Str(Int(Round(xiaoxie, 2) * 100)))

For i = 1 To Len(sNum)

BigNum = BigNum + Mid(cNum, (Mid(sNum, i, 1)) + 1, 1) + Mid(cNum, 26 - Len(sNum) + i, 1)

Next i

For i = 0 To 11

BigNum = Replace(BigNum, Mid(cCha, i * 2 + 1, 2), Mid(cCha, i + 26, 1))

Next i

BigNum = fuhao + BigNum

 End If

End Function

上面四種辦法中，要領(lǐng)一和要領(lǐng)四等價；要領(lǐng)二、三和要領(lǐng)一、四的區(qū)別只是整數(shù)部分為零時，要領(lǐng)一、四不顯示“零元”，而直接顯示“角分”；要領(lǐng)三在要領(lǐng) 2的基礎(chǔ)上加上了數(shù)字顯示，下表是不同數(shù)據(jù)在四種要領(lǐng)中的顯示結(jié)果：