電子表格公式技巧1.公式技巧1.1在單元格中顯示工作表和工作簿的名稱(chēng)在單元格中顯示工作表的名稱(chēng)，有兩種要領(lǐng)：（1）建立如下自定義函數(shù)：Functionbookname()bookname=ActiveSheet.NameEndFunction運(yùn)用時(shí)在單元格中輸入公式：=bookname()，即可返回當(dāng)前工作簿的標(biāo)簽名字。（2）自定義名稱(chēng)的要領(lǐng)。定義如下名稱(chēng)：點(diǎn)擊[插入]à[名稱(chēng)]à[定義]，名稱(chēng)的定義為“T_B”，引用位置輸入：“=replace(get.document(1),1,find("]",get.document(1)),)&t(now())”，在單元格輸入“=T_B”就可以顯示當(dāng)前表名。值得留心的是，返回的工作表名稱(chēng)隨著工作表名稱(chē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()用來(lái)將now()產(chǎn)生的數(shù)值轉(zhuǎn)化為空文本。在單元格中顯示工作簿的名稱(chēng)，運(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，沒(méi)有則等于0，公式為：=IF(COUNTIF(A:A,"*$*")>0,1,0)。1.5計(jì)算某單元格所在的列數(shù)通常情況下，A列為第1列，AA列為27列?？梢栽贏1單元格中輸入列標(biāo)，通過(guò)下列公式計(jì)算出任何列標(biāo)的列數(shù)：=COLUMN(INDIRECT(A1&"1"))。例如：“FG”列為第163列。1.6DATEDIF函數(shù)的作用DATEDIF函數(shù)計(jì)算兩個(gè)日期之間的天數(shù)、月數(shù)或年數(shù)。提供此函數(shù)是為了與Lotus1-2-3兼容。語(yǔ)法：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為所需信息的返回類(lèi)型。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ù)的差。忽略日期中的年。說(shuō)明：MicrosoftExcel按順序的系列數(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)樵撊掌诰嚯x1900年1月1日為35795天。請(qǐng)查閱MicrosoftExcel如何存儲(chǔ)日期和時(shí)間。ExcelforWindows和ExcelforMacintosh運(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，即開(kāi)始日期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下寫(xiě)日期會(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用“定義名稱(chēng)”的要領(lǐng)突破IF函數(shù)的嵌套限定Excel中的IF()函數(shù)的一個(gè)眾所周知的限定是嵌套不能超過(guò)7層。例如下面的公式是不正確的，因?yàn)榍短讓訑?shù)超過(guò)了限定。=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代替。但是也可以可以通過(guò)對(duì)公式的一部分”定義名稱(chēng)”來(lái)處理這種限定定義一個(gè)名叫”O(jiān)neToSix”的名稱(chēng),里面包括公式：=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”的名稱(chēng)，里面包括公式：=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ù)值之和，更直接地說(shuō)，如果當(dāng)前單元格在B17，那么求A1:A16之和。運(yùn)用下面的公式：=SUM(INDIRECT("A1:A"&ROW()-1))。1.11COUNTIF函數(shù)的16種公式配置（設(shè)DATA為區(qū)域名稱(chēng)）（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"(不分大小寫(xiě))內(nèi)容的單元格數(shù)量：=COUNTIF(DATA,“GOOD”)（10）返回在文本中任何位置包含單詞"GOOD"字符內(nèi)容的單元格數(shù)量：=COUNTIF(DATA,“*GOOD*”)（11）返回包含以單詞"AB"(不分大小寫(xiě))開(kāi)頭內(nèi)容的單元格數(shù)量：=COUNTIF(DATA,“AB*”)（12）返回包含當(dāng)前日期的單元格數(shù)量：=COUNTIF(DATA,TODAY()）（13）返回大于平均值的單元格數(shù)量：=COUNTIF(DATA,">"&AVERAGE(DATA))（14）返回平均值上面超過(guò)三個(gè)標(biāo)準(zhǔn)誤差的值的單元格數(shù)量：=COUNTIF(DATA,“>"&AVERAGE(DATA)+STDEV(DATA)*3)（15）返回包含值為或-3的單元格數(shù)量：=COUNTIF(DATA,3）+COUNIF(DATA,-3)（16）返回包含值邏輯值為T(mén)RUE的單元格數(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)）”鍵來(lái)決定顯示或潛藏公式，可讓儲(chǔ)存格顯示計(jì)算的結(jié)果，還是公式本身。1.16如果某列中有大于0和小于0的數(shù)，將小于0數(shù)字所在的行自動(dòng)刪除假定在A1-A6中有大于0和小于0的數(shù)，可以用下面的VBA程序?qū)崿F(xiàn)：fori=6to1step-1ifcells(i,1)<0thenrows(i).Deletenexti1.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ù)來(lái)獲取單元格地址在復(fù)雜的計(jì)算中，往往要獲知單元格的地址，可以用函數(shù)=ADDRESS(ROW(),COLUMN())獲得當(dāng)前單元格的地址。1.19求一列中某個(gè)特定的值對(duì)應(yīng)的另外列的最大或最小值為了直觀起見(jiàn)，舉一個(gè)基本的例子：例如在A1:A10中有若干臺(tái)計(jì)算機(jī)、打印機(jī)、傳真機(jī)等物品的名稱(chēng)，在B1:B10中有上述設(shè)備對(duì)應(yīng)的價(jià)格，求“計(jì)算機(jī)”對(duì)應(yīng)的最低價(jià)格。可以用公式：=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代碼：PrivateSubWorksheet_Change(ByValTargetAsRange)IfTarget.Column<>1ThenExitSubElseTarget.Offset(0,1)=NowEndIfEndSub1.21如果一個(gè)單元格中既有數(shù)字又有字母，如何提取其中的數(shù)字呢Functiongetnumber(rngAsString)AsStringDimmylenAsIntegerDimmystrAsStringmylen=Len(rng)ForI=1Tomylenmystr=Mid(rng,I,1)IfAsc(mystr)>=48AndAsc(mystr)<=57Thengetnumber=getnumber&mystrEndIfNextIEndFunction1.22Excel數(shù)組的使用數(shù)組就是單元的集合或是一組處理的值集合?？梢詫?xiě)一個(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ù)組首先我們通過(guò)多個(gè)例子來(lái)說(shuō)明數(shù)組是如何工作的。我們可以從圖中看到，在“B”列中的數(shù)據(jù)為銷(xiāo)售量，在“C”列中的數(shù)據(jù)是銷(xiāo)售單價(jià)，要求計(jì)算出每種產(chǎn)品的銷(xiāo)售額和總的銷(xiāo)售金額，一般的做法是計(jì)算出每種產(chǎn)品的銷(xiāo)售額，然后再計(jì)算出總的銷(xiāo)售額。但是如果我們改用數(shù)組，就可以只鍵入一個(gè)公式來(lái)完成這些運(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ū)的銷(xiāo)售量和銷(xiāo)售單價(jià)相乘，相乘的結(jié)果共有3個(gè)數(shù)字，每個(gè)數(shù)字代表一個(gè)地區(qū)的銷(xiāo)售額，而“SUM”函數(shù)將這些銷(xiāo)售額相加，就得到了總的銷(xiāo)售額。下面我們?cè)僖赃\(yùn)用數(shù)組計(jì)算3種產(chǎn)品的銷(xiāo)售額為例，來(lái)說(shuō)明如何產(chǎn)生多個(gè)計(jì)算結(jié)果。其操作流程如下：(1)選擇“D2：D4”單元格區(qū)域，該區(qū)域中的每個(gè)單元格保存的銷(xiāo)售金額。如圖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è)單元格來(lái)處理，所以不能對(duì)“D2”到“D4”中的任一格作任何單獨(dú)處理，必須針對(duì)整個(gè)數(shù)組來(lái)處理。（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)分開(kāi)。下面是一個(gè)一維數(shù)組的例子。例如數(shù)組：{10,20,30,40,50}。在一維垂直數(shù)組中的元素用分號(hào)分開(kāi)。在下面的例子是一個(gè)6×1的數(shù)組，{100;200;300;400;500;600}。對(duì)于二維數(shù)組，用逗號(hào)將一行內(nèi)的元素分開(kāi)，用分號(hào)將各行分開(kāi)。下一個(gè)例子是“4×4”的數(shù)組（由4行4列組成）：{100,200,300,400;110,……;130,230,330,440}。留心：不可以在數(shù)組公式中運(yùn)用列出常數(shù)的要領(lǐng)列出單元引用、名稱(chēng)或公式。例如：{2*3,3*3,4*3}因?yàn)榱谐隽硕鄠€(gè)公式，是不能用的。{A1,B1,C1}因?yàn)榱谐龆鄠€(gè)引用，也是不能用的。不過(guò)可以運(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ù)組常量中可以含有不同類(lèi)型的值。數(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í)，MicrosoftExcel會(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。如果將公式寫(xiě)成=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ù)相同。這樣，MicrosoftExcel才能把計(jì)算所得的數(shù)組中的每一個(gè)數(shù)值放入數(shù)組區(qū)域的一個(gè)單元格內(nèi)。如果數(shù)組公式計(jì)算所得的數(shù)組比選定的數(shù)組區(qū)域還小，則MicrosoftExcel會(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”。如果MicrosoftExcel將一個(gè)數(shù)組擴(kuò)展到可以填入比該數(shù)組公式大的區(qū)域內(nèi)，而沒(mé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ū)域還要大，則超過(guò)的值不會(huì)出現(xiàn)在工作表上。1.23數(shù)組的使用（1）數(shù)組公式的實(shí)現(xiàn)要領(lǐng)：其實(shí)這些都是數(shù)組公式，數(shù)組公式的輸入要領(lǐng)是將公式輸入后，不要直接按回車(chē)鍵（Enter），而是要同時(shí)按Ctrl+Shift+Enter，這時(shí)計(jì)算機(jī)自動(dòng)會(huì)為你添加“{}”的。在論壇上，為了告訴大家這是數(shù)組公式，故在公式的頭尾都加上了“{}”。如果不注意按回車(chē)了，可以用鼠標(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ù)組常量括起來(lái)，并且用“，”和“；”分離元素。其中“，”分離不同列的值，“；”分離不同行的值。2、數(shù)組公式的原理：數(shù)組公式，說(shuō)白了就是同時(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）。或者添加ROUND輔助列（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)的銷(xiāo)售數(shù)量，須要統(tǒng)計(jì)總銷(xiāo)售額，常規(guī)做法須要添加輔助列C列，在C列中計(jì)算出C1：C10的每個(gè)單價(jià)的銷(xiāo)售額（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ì)如下圖所示銷(xiāo)量的頻率分布，即分別統(tǒng)計(jì)銷(xiāo)量在5000以下、5000到10000、10000到50000以及大于50000的銷(xiāo)售點(diǎn)數(shù)量a2b2C2銷(xiāo)售點(diǎn)銷(xiāo)售額分段點(diǎn)城北00141005000城北0021589010000城南001870050000城南00225900城南0035800城東00115300城東00238000城東0039800城西00156000城西00272050城中001130000城中00260400城中00348700步驟："1、打造如上圖所示的表格2、選中單元格G7：G10，直接輸入公式：=FREQUENCY（B4：B14，c4：c6）3、輸入公式后，按CTRL+SHIFT+ENTER鍵結(jié)束"類(lèi)型日期單價(jià)銷(xiāo)售數(shù)量A2005-6-15100010B2005-6-20100015B2005-7-1400010C2005-7-10400011B2005-8-15900013C2005-8-20900015A2005-9-30100014A2005-10-10100020B2005-10-15400025類(lèi)型從B1格開(kāi)始計(jì)算B產(chǎn)品8月份銷(xiāo)量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)品的銷(xiāo)量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)品銷(xiāo)量和8月后不包括C產(chǎn)品銷(xiāo)量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è)例子來(lái)說(shuō)明。（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，來(lái)返回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.28Excel多見(jiàn)不正確及處理辦法經(jīng)常用Excel可能都會(huì)遇到一些不正確值信息，如：#N/A！、#VALUE！、#DIV/O！等等，出現(xiàn)這些不正確的原由有很多種，如果公式不能計(jì)算正確結(jié)果，Excel將顯示一個(gè)不正確值，例如，在須要數(shù)字的公式中運(yùn)用文本、刪除了被公式引用的單元格，或者運(yùn)用了寬度不足以顯示結(jié)果的單元格。以下是幾種多見(jiàn)的不正確及其處理要領(lǐng)。（1）#####！原由：如果單元格所含的數(shù)字、日期或時(shí)間比單元格寬，或者單元格的日期時(shí)間公式產(chǎn)生了一個(gè)負(fù)值，就會(huì)產(chǎn)生#####！不正確。處理要領(lǐng)：如果單元格所含的數(shù)字、日期或時(shí)間比單元格寬，可以通過(guò)拖動(dòng)列表之間的寬度來(lái)修改列寬。如果運(yùn)用的是1900年的日期系統(tǒng)，那么Excel中的日期和時(shí)間必須為正值，用較早的日期或者時(shí)間值減去較晚的日期或者時(shí)間值就會(huì)導(dǎo)致#####！不正確。如果公式正確，也可以將單元格的格式改為非日期和時(shí)間型來(lái)顯示該值。（2）#VALUE!當(dāng)運(yùn)用不正確的參數(shù)或運(yùn)算對(duì)象類(lèi)型時(shí)，或者當(dāng)公式自動(dòng)更正功能不能更正公式時(shí)，將產(chǎn)生不正確值#VALUE!。原由一：在須要數(shù)字或邏輯值時(shí)輸入了文本，Excel不能將文本轉(zhuǎn)換為正確的數(shù)據(jù)類(lèi)型。處理要領(lǐng)：確認(rèn)公式或函數(shù)所需的運(yùn)算符或參數(shù)正確，并且公式引用的單元格中包含有效的數(shù)值。例如：如果單元格A1包含一個(gè)數(shù)字，單元格A2包含文本"學(xué)籍"，則公式"=A1+A2"將返回不正確值#VALUE!?？梢杂肧UM工作表函數(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)用的名稱(chēng)，或者運(yùn)用了不存在的名稱(chēng)。處理要領(lǐng)：確認(rèn)運(yùn)用的名稱(chēng)確實(shí)存在。選擇菜單"插入""名稱(chēng)""定義"命令，如果所需名稱(chēng)沒(méi)有被列出，請(qǐng)運(yùn)用"定義"命令添加相應(yīng)的名稱(chēng)。原由二：名稱(chēng)的拼寫(xiě)不正確。處理要領(lǐng)：修改拼寫(xiě)不正確的名稱(chēng)。原由三：在公式中運(yùn)用標(biāo)志。處理要領(lǐng)：選擇菜單中"工具""選項(xiàng)"命令，打開(kāi)"選項(xiàng)"對(duì)話框，然后單擊"重新計(jì)算"標(biāo)簽，在"工作薄選項(xiàng)"下，選中"接受公式標(biāo)志"復(fù)選框。原由四：在公式中輸入文本時(shí)沒(méi)有運(yùn)用雙引號(hào)。處理要領(lǐng)：Excel將其解釋為名稱(chēng)，而不理會(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ù)或公式中沒(méi)有可用數(shù)值時(shí)，將產(chǎn)生不正確值#N/A。處理要領(lǐng)：如果工作表中某些單元格暫時(shí)沒(méi)有數(shù)值，請(qǐng)?jiān)谶@些單元格中輸入"#N/A"，公式在引用這些單元格時(shí)，將不執(zhí)行數(shù)值計(jì)算，而是返回#N/A。（6）#REF!當(dāng)單元格引用無(wú)效時(shí)將產(chǎn)生不正確值#REF！。原由：刪除了由其他公式引用的單元格，或?qū)⒁苿?dòng)單元格粘貼到由其他公式引用的單元格中。處理要領(lǐng)：修改公式或者在刪除或粘貼單元格之后，立即單擊"撤消"按鈕，以恢復(fù)工作表中的單元格。（7）#NUM！當(dāng)公式或函數(shù)中某個(gè)數(shù)字有疑問(wèn)時(shí)將產(chǎn)生不正確值#NUM！。原由一：在須要數(shù)字參數(shù)的函數(shù)中運(yùn)用了不能接受的參數(shù)。處理要領(lǐng)：確認(rèn)函數(shù)中運(yùn)用的參數(shù)類(lèi)型正確無(wú)誤。原由二：運(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）。如果沒(méi)有運(yùn)用逗號(hào)，Excel將試圖對(duì)同時(shí)屬于兩個(gè)區(qū)域的單元格求和，但是由于A1:A13和D12:D23并不相交，所以他們沒(méi)有共同的單元格。1.29金額大寫(xiě)的轉(zhuǎn)換假設(shè)A1單元格為原始數(shù)據(jù)，即小寫(xiě)數(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)