九色国产,午夜在线视频,新黄色网址,九九色综合,天天做夜夜做久久做狠狠,天天躁夜夜躁狠狠躁2021a,久久不卡一区二区三区

1.公式技巧

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

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

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

Function bookname()

bookname = ActiveSheet.Name

End Function

運(yùn)用時(shí)在單元格中輸入公式：=bookname()，即可返回當(dāng)前工作簿的標(biāo)簽名字。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1.6 DATEDIF函數(shù)的作用

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

語法：DATEDIF(start_date,end_date,unit)

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

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

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

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

說明：Microsoft Excel 按順序的系列數(shù)保存日期，這樣就可以對(duì)其執(zhí)行 計(jì)算。如果工作簿運(yùn)用 1900 日期系統(tǒng)，則 Excel 會(huì)將 1900 年 1 月 1 日保存為系列數(shù) 1。而如果工作簿運(yùn)用 1904 日期系統(tǒng)，則 Excel 會(huì)將 1904 年 1 月 1 日保存為系列數(shù) 0，（而將 1904 年 1 月 2 日保存為系列數(shù) 1）。例如，在 1900 日期系統(tǒng)中 Excel 將 1998 年 1 月 1 日保存為系列數(shù) 35796，因?yàn)樵撊掌诰嚯x 1900 年 1 月 1 日為 35795 天。請(qǐng)查閱 Microsoft Excel 如何 存儲(chǔ)日期和時(shí)間。

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

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

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 在一個(gè)單元格中指定字符出現(xiàn)的次數(shù)

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

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

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

我們?cè)谟行┣闆r下寫日期會(huì)用“20060404”表示，如何 轉(zhuǎn)換成“2006-04-04”的標(biāo)準(zhǔn)日期格式，用下面的兩個(gè)公式之一（假定在A1單元格中有原始日期）：

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

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

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

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

反之，如何 把“2006年4月4日”轉(zhuǎn)換成“20060404”？可以運(yùn)用 下面的公式之一（假定在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ù)的一個(gè)眾所周知的限定是嵌套不能超過7層。例如下面的公式是不正確的，因?yàn)榍短讓訑?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)會(huì)考慮用VBA代替。但是也可以可以通過對(duì)公式的一部分”定義名稱”來處理這種限定定義一個(gè)名叫”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))))))))

再定義另一個(gè)名叫”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 動(dòng)態(tài)求和

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

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

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

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

（2）返回包含負(fù)值的單元格數(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）返回包含三個(gè)字符內(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）返回包含當(dāng)前日期的單元格數(shù)量：=COUNTIF(DATA,TODAY()）

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

（14）返回平均值上面超過三個(gè)標(biāo)準(zhǔn)誤差的值的單元格數(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 計(jì)算一個(gè)日期是一年中的第幾天

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

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

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

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

=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中的絕對(duì)引用與相對(duì)引用之間切換

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

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

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

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

假定在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ù)行和偶數(shù)行單獨(dú)求和，例如要求A列第1行至1000行中奇數(shù)行之和，運(yùn)用 公式=SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000),2))，要求這些行中偶數(shù)行之和，運(yùn)用 公式=SUMPRODUCT((A1:A1000)*NOT(MOD(ROW(A1:A1000),2)))。

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

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

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

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

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

1.20 自動(dòng)記錄數(shù)據(jù)錄入時(shí)間

運(yùn)用 VBA實(shí)現(xiàn)，建立一個(gè)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 如果一個(gè)單元格中既有數(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ù)組就是單元的集合或是一組處理的值集合。可以寫一個(gè)數(shù)組公式，即輸入一個(gè)單個(gè)的公式，它執(zhí)行多個(gè)輸入的操作并產(chǎn)生多個(gè)結(jié)果——每個(gè)結(jié)果顯示在一個(gè)單元中。數(shù)組公式可以看成是有多重?cái)?shù)值的公式。與單值公式的不同之處在于它可以產(chǎn)生一個(gè)以上的結(jié)果。一個(gè)數(shù)組公式可以占用一個(gè)或多個(gè)單元。數(shù)組的元素可多達(dá)6500個(gè)。

（1）了解數(shù)組

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

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

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

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

下面我們?cè)僖赃\(yùn)用數(shù)組計(jì)算3種產(chǎn)品的銷售額為例，來說明如何 產(chǎn)生多個(gè)計(jì)算結(jié)果。其操作流程如下：

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

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

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

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

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

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

留心：不可以在數(shù)組公式中運(yùn)用列出常數(shù)的要領(lǐng)列出單元引用、名稱或公式。例如：{2*3,3*3,4*3}因?yàn)榱谐隽硕鄠€(gè)公式，是不能用的。{A1,B1,C1}因?yàn)榱谐龆鄠€(gè)引用，也是不能用的。不過可以運(yùn)用一個(gè)區(qū)域，例如{A1:C1}。

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

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

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

文字必須以雙引號(hào)括住。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1.23 數(shù)組的使用

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

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

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

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

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

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

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

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

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

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

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

而數(shù)組公式為：

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

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

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

a2b2C2

銷售點(diǎn) 銷售額 分段點(diǎn)

城北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é)束

"

類型 日期 單價(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格開始

計(jì)算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))}

計(jì)算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))}

計(jì)算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 求一個(gè)單元格數(shù)值中的最大數(shù)字和個(gè)數(shù)字之和

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

（1）求其中最大的數(shù)字，運(yùn)用 數(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ù)字之和，運(yùn)用 下面的公式：

=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()是一個(gè)非常有用的函數(shù)，現(xiàn)在舉個(gè)例子來說明。

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

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

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

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

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

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

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

1.27 返回最大值的行號(hào)和地址

返回最大值的行號(hào)：

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

（1）#####！

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

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

（2）#VALUE!

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

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

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

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

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

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

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

（3）#DIV/O!

當(dāng)公式被零除時(shí)，將會(huì)產(chǎn)生不正確值#DIV/O!。

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

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

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

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

（4）#NAME?

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

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

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

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

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

原由三：在公式中運(yùn)用標(biāo)志。

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

原由四：在公式中輸入文本時(shí)沒有運(yùn)用雙引號(hào)。

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

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

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

（5）#N/A

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

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

（6）#REF!

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

原由：刪除了由其他公式引用的單元格，或?qū)⒁苿?dòng)單元格粘貼到由其他公式引用的單元格中。

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

（7）#NUM！

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

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

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

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

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

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

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

（8）#NULL！

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

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

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

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

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

公式法一：

=IF(A1=0,"零元整",IF(A1<0,"負(fù)",)&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,"負(fù)",)&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 = "負(fù)"

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