EXCEL公式技巧匯總

1.公式技巧1.1在單元格中顯示工作表和工作簿的名稱在單元格中顯示工作表的名稱，有兩種要領(lǐng)：（1）建立如下自定義函數(shù)：Functionbookname()bookname=ActiveSheet.NameEndFunction運(yùn)用時在單元格中輸入公式：=bookname()，即可返回當(dāng)前工作簿的標(biāo)簽名字。（2）自定義名稱的要領(lǐng)。定義如下名稱：點擊[插入]à[名稱]à[定義]，名稱的定義為“T_B”，引用位置輸入：“=replace(get.document(1),1,find("]",get.document(1)),)&t(now())”，在單元格輸入“=T_B”就可以顯示當(dāng)前表名。值得留心的是，返回的工作表名稱隨著工作表名稱的變化而變化。在此引用中，GET.DOCUMENT()是宏表函數(shù)，當(dāng)數(shù)據(jù)變動時不能自動計算，now()是易失性函數(shù)，任何變動都會強(qiáng)制計算，宏表函數(shù)所以加上now()就可以自動重算了，T()用來將now()產(chǎn)生的數(shù)值轉(zhuǎn)化為空文本。在單元格中顯示工作簿的名稱，運(yùn)用系統(tǒng)函數(shù)Cell()：在單元格中輸入公式：=Cell("filename")，就會返回該工作簿和工作表的名字（包括絕對路徑名），然后根據(jù)自己的須要運(yùn)用一些文本處理函數(shù)執(zhí)行處理即可。留心：該函數(shù)必須在工作簿已經(jīng)保存的情況下才生效。1.2基本判斷單元格最后一位是數(shù)字還是字母在有些情況下，須要判斷單元格的最后一位是數(shù)字還是字母，可以用下面三個公式之一：（2）=IF(ISNUMBER(--RIGHT(A1,1)),"數(shù)字","字母")，直接返回數(shù)字或字母。其中“--”的意思是將文本型數(shù)字轉(zhuǎn)化為數(shù)值以便參與運(yùn)算。（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列。可以在A1單元格中輸入列標(biāo)，通過下列公式計算出任何列標(biāo)的列數(shù)：=COLUMN(INDIRECT(A1&"1"))。例如：“FG”列為第163列。1.6DATEDIF函數(shù)的作用DATEDIF函數(shù)計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)。提供此函數(shù)是為了與Lotus1-2-3兼容。語法：DATEDIF(start_date,end_date,unit)Start_date為一個日期，它代表時間段內(nèi)的第一個日期或起始日期。日期有多種輸入要領(lǐng)：帶引號的文本串（例如"2001/1/30"）、系列數(shù)（例如，如果運(yùn)用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ù)的差。忽略日期中的年。說明：MicrosoftExcel按順序的系列數(shù)保存日期，這樣就可以對其執(zhí)行計算。如果工作簿運(yùn)用1900日期系統(tǒng)，則Excel會將1900年1月1日保存為系列數(shù)1。而如果工作簿運(yùn)用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天。請查閱MicrosoftExcel如何存儲日期和時間。ExcelforWindows和ExcelforMacintosh運(yùn)用不同的默認(rèn)日期系統(tǒng)。有關(guān)細(xì)致信息，請參閱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”的標(biāo)準(zhǔn)日期格式，用下面的兩個公式之一（假定在A1單元格中有原始日期）：=TEXT(A1,"0000-00-00")=TEXT(A1,"????-??-??")。也可以運(yùn)用以下公式，轉(zhuǎn)換成“2006-4-4”的格式。=LEFT(A1,4)&SUBSTITUTE(RIGHT(A1,4),0,"-")。反之，如何把“20XX年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ù)的一個眾所周知的限定是嵌套不能超過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代替。但是也可以可以通過對公式的一部分”定義名稱”來處理這種限定定義一個名叫”O(jiān)neToSix”的名稱,里面包括公式：=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到當(dāng)前單元格行標(biāo)前面一行的單元格中的數(shù)值之和，更直接地說，如果當(dāng)前單元格在B17，那么求A1:A16之和。運(yùn)用下面的公式：=SUM(INDIRECT("A1:A"&ROW()-1))。1.11COUNTIF函數(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）返回包含三個字符內(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）返回平均值上面超過三個標(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計算一個日期是一年中的第幾天例如20XX年7月29日是本年中的第幾天？在一年中，顯示是第幾天用什么函數(shù)呢？假定A1中是日期，運(yùn)用下列公式：=A1-DATE(YEAR(A1),1,0)，將單元格格式配置為常規(guī)，返回210，即20XX年7月29日是20XX年的第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中建立公式時，該公式可以運(yùn)用相對引用，即相對于公式所在的位置引用單元；也可以運(yùn)用絕對引用，即引用特定位置上的單元。引用由所在單元格的“列的字母”和“行的數(shù)字”組成，絕對引用由在“列的字母”和“行的數(shù)字”前面加“$”表示，例如，$B$1是對第一行B列的絕對引用。公式中還可以混合運(yùn)用相對引用和絕對引用?？梢赃\(yùn)用F4切換相對引用和絕對引用，選中包含公式的單元格，在公式欄中選擇想要改動的引用，按F4鍵可以執(zhí)行切換。1.15在Excel公式和結(jié)果之間高速切換在excel工作表中輸入計算公式時，可以運(yùn)用“Ctrl+`（中音號）”鍵來決定顯示或潛藏公式，可讓儲存格顯示計算的結(jié)果，還是公式本身。1.16如果某列中有大于0和小于0的數(shù)，將小于0數(shù)字所在的行自動刪除假定在A1-A6中有大于0和小于0的數(shù)，可以用下面的VBA程序?qū)崿F(xiàn)：fori=6to1step-1ifcells(i,1)<0thenrows(i).Deletenexti1.17奇數(shù)行和偶數(shù)行求和有時候須要奇數(shù)行和偶數(shù)行單獨求和，例如要求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ù)雜的計算中，往往要獲知單元格的地址，可以用函數(shù)=ADDRESS(ROW(),COLUMN())獲得當(dāng)前單元格的地址。1.19求一列中某個特定的值對應(yīng)的另外列的最大或最小值為了直觀起見，舉一個基本的例子：例如在A1:A10中有若干臺計算機(jī)、打印機(jī)、傳真機(jī)等物品的名稱，在B1:B10中有上述設(shè)備對應(yīng)的價格，求“計算機(jī)”對應(yīng)的最低價格?？梢杂霉剑?min(if(a1:a10="計算機(jī)",b1:b10))，輸入該公式后按Ctrl+Shift+Enter完成。1.20自動記錄數(shù)據(jù)錄入時間運(yùn)用VBA實現(xiàn)，建立一個Time.xls文檔，輸入以下VBA代碼：PrivateSubWorksheet_Change(ByValTargetAsRange)IfTarget.Column<>1Then

ExitSubElse

Target.Offset(0,1)=NowEndIfEndSub1.21如果一個單元格中既有數(shù)字又有字母，如何提取其中的數(shù)字呢Functiongetnumber(rngAsString)AsStringDimmylenAsIntegerDimmystrAsStringmylen=Len(rng)ForI=1Tomylen

mystr=Mid(rng,I,1)

IfAsc(mystr)>=48AndAsc(mystr)<=57Then

getnumber=getnumber&mystr

EndIfNextIEndFunction1.22Excel數(shù)組的使用數(shù)組就是單元的集合或是一組處理的值集合?？梢詫懸粋€數(shù)組公式，即輸入一個單個的公式，它執(zhí)行多個輸入的操作并產(chǎn)生多個結(jié)果——每個結(jié)果顯示在一個單元中。數(shù)組公式可以看成是有多重數(shù)值的公式。與單值公式的不同之處在于它可以產(chǎn)生一個以上的結(jié)果。一個數(shù)組公式可以占用一個或多個單元。數(shù)組的元素可多達(dá)6500個。（1）了解數(shù)組首先我們通過多個例子來說明數(shù)組是如何工作的。我們可以從圖中看到，在“B”列中的數(shù)據(jù)為銷售量，在“C”列中的數(shù)據(jù)是銷售單價，要求計算出每種產(chǎn)品的銷售額和總的銷售金額，一般的做法是計算出每種產(chǎn)品的銷售額，然后再計算出總的銷售額。但是如果我們改用數(shù)組，就可以只鍵入一個公式來完成這些運(yùn)算。輸入數(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ù)將這些銷售額相加，就得到了總的銷售額。下面我們再以運(yùn)用數(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”被當(dāng)作一個單元格來處理，所以不能對“D2”到“D4”中的任一格作任何單獨處理，必須針對整個數(shù)組來處理。

（2）運(yùn)用數(shù)組常數(shù)我們也可以在數(shù)組中運(yùn)用常數(shù)值。這些值可以放在數(shù)組公式中運(yùn)用區(qū)域引用的地點。要在數(shù)據(jù)公式中運(yùn)用數(shù)組常數(shù)，直接將該值輸入到公式中并將它們放在括號里。例如，在圖7-39中，就運(yùn)用了數(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ù)組公式中運(yùn)用列出常數(shù)的要領(lǐng)列出單元引用、名稱或公式。例如：{2*3,3*3,4*3}因為列出了多個公式，是不能用的。{A1,B1,C1}因為列出多個引用，也是不能用的。不過可以運(yùn)用一個區(qū)域，例如{A1:C1}。對于數(shù)組常量的內(nèi)容，可由下列準(zhǔn)則構(gòu)成：數(shù)組常量可以是數(shù)字、文字、邏輯值或不正確值。數(shù)組常量中的數(shù)字，也可以運(yùn)用整數(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所示。選擇“當(dāng)前數(shù)組”選項，最后按下“確定”按鈕，就可以看到數(shù)組被選定了。編輯數(shù)組的步驟為:選定要編輯的數(shù)組，移到數(shù)據(jù)編輯欄上按[F2]鍵或單擊左鍵，使代表數(shù)組的括號消散，之后就可以編輯公式了。編輯完成后，按下[Shift]+[Ctrl]+[Enter]鍵。若要刪除數(shù)組，其步驟為：選定要刪除的數(shù)組，按[Ctrl]+[Delete]或選擇編輯菜單中的“清理”。（4）數(shù)組的擴(kuò)充在公式或函數(shù)中運(yùn)用數(shù)組常量時，其它運(yùn)算對象或參數(shù)應(yīng)該和第一個數(shù)組具有相同的維數(shù)。必要時，MicrosoftExcel會將運(yùn)算對象擴(kuò)展，以符合操作須要的維數(shù)。每一個運(yùn)算對象的行數(shù)必須和含有最多行的運(yùn)算對象的行數(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ù)值擴(kuò)充成1×3的數(shù)組。運(yùn)用=SUM({1,2,3}+{4,4,4})做計算，得到的結(jié)果為1+4、2+4和3+4的和,即18。將數(shù)組公式輸入單元格區(qū)域中時，所運(yùn)用的維數(shù)應(yīng)和這個公式計算所得數(shù)組維數(shù)相同。這樣，MicrosoftExcel才能把計算所得的數(shù)組中的每一個數(shù)值放入數(shù)組區(qū)域的一個單元格內(nèi)。如果數(shù)組公式計算所得的數(shù)組比選定的數(shù)組區(qū)域還小，則MicrosoftExcel會將這個數(shù)組擴(kuò)展，以便將它填入整個數(shù)組區(qū)域內(nèi)。例如：={1,2;3,4}*2擴(kuò)充后的公式就會變?yōu)?{1,2;3,4}*{2,2;2,2}，則相應(yīng)的計算結(jié)果為“2,4,6,8”。再如：輸入公式={1,2;3,4}*{2,3}擴(kuò)充后的公式就會變?yōu)?{1,2;3,4}*{2,3;2,3}，則相應(yīng)的計算結(jié)果為“2,6,6,12”。如果MicrosoftExcel將一個數(shù)組擴(kuò)展到可以填入比該數(shù)組公式大的區(qū)域內(nèi)，而沒有擴(kuò)大值可用的單元格內(nèi)，這樣就會出現(xiàn)#N/A不正確值。例如：={1,2;3,4}={1,2,3}擴(kuò)充后的公式就會變?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，這時計算機(jī)自動會為你添加“{}”的。在論壇上，為了告訴大家這是數(shù)組公式，故在公式的頭尾都加上了“{}”。如果不注意按回車了，可以用鼠標(biāo)點一下編輯欄中的公式，再按Ctrl+Shift+Enter。編輯或刪除數(shù)組公式編輯數(shù)組公式時，須選取數(shù)組區(qū)域并且激活編輯欄，公式兩邊的花括號將消散，然后編輯公式，最后按Ctrl＋Shift＋Enter鍵。選取數(shù)組公式所占有的全部區(qū)域后，按Delete鍵即可刪除數(shù)組公式。數(shù)組常量的運(yùn)用數(shù)組公式中還可運(yùn)用數(shù)組常量，但必須自己鍵入花括號“{}”將數(shù)組常量括起來，并且用“，”和“；”分離元素。其中“，”分離不同列的值，“；”分離不同行的值。2、數(shù)組公式的原理：數(shù)組公式，說白了就是同時對一組或幾組數(shù)同時處理，然后得到須要的答案。運(yùn)用數(shù)組公式的最主要的原理是數(shù)于數(shù)之間一一對應(yīng)。1、假設(shè)要將A1：A50區(qū)域中的所有數(shù)值舍入到2位小數(shù)位，然后對舍入的數(shù)值求和。很自然地就會想到運(yùn)用公式：=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銷售點銷售額分段點城北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é)束"

類型日期單價銷售數(shù)量A2005-6-15100010B2005-6-20100015B2005-7-1400010C2005-7-10400011B2005-8-15900013C2005-8-20900015A2005-9-30100014A2005-10-10100020B2005-10-15400025類型從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ù)字，運(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()是一個非常有用的函數(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.28Excel多見不正確及處理辦法經(jīng)常用Excel可能都會遇到一些不正確值信息，如：#N/A！、#VALUE！、#DIV/O！等等，出現(xiàn)這些不正確的原由有很多種，如果公式不能計算正確結(jié)果，Excel將顯示一個不正確值，例如，在須要數(shù)字的公式中運(yùn)用文本、刪除了被公式引用的單元格，或者運(yùn)用了寬度不足以顯示結(jié)果的單元格。以下是幾種多見的不正確及其處理要領(lǐng)。（1）#####！原由：如果單元格所含的數(shù)字、日期或時間比單元格寬，或者單元格的日期時間公式產(chǎn)生了一個負(fù)值，就會產(chǎn)生#####！不正確。處理要領(lǐng)：如果單元格所含的數(shù)字、日期或時間比單元格寬，可以通過拖動列表之間的寬度來修改列寬。如果運(yùn)用的是1900年的日期系統(tǒng)，那么Excel中的日期和時間必須為正值，用較早的日期或者時間值減去較晚的日期或者時間值就會導(dǎo)致#####！不正確。如果公式正確，也可以將單元格的格式改為非日期和時間型來顯示該值。（2）#VALUE!當(dāng)運(yùn)用不正確的參數(shù)或運(yùn)算對象類型時，或者當(dāng)公式自動更正功能不能更正公式時，將產(chǎn)生不正確值#VALUE!。原由一：在須要數(shù)字或邏輯值時輸入了文本，Excel不能將文本轉(zhuǎn)換為正確的數(shù)據(jù)類型。處理要領(lǐng)：確認(rèn)公式或函數(shù)所需的運(yùn)算符或參數(shù)正確，并且公式引用的單元格中包含有效的數(shù)值。例如：如果單元格A1包含一個數(shù)字，單元格A2包含文本"學(xué)籍"，則公式"=A1+A2"將返回不正確值#VALUE!。可以用SUM工作表函數(shù)將這兩個值相加（SUM函數(shù)忽略文本）：=SUM（A1:A2）。原由二：將單元格引用、公式或函數(shù)作為數(shù)組常量輸入。處理要領(lǐng)：確認(rèn)數(shù)組常量不是單元格引用、公式或函數(shù)。原由三：賦予須要單一數(shù)值的運(yùn)算符或函數(shù)一個數(shù)值區(qū)域。處理要領(lǐng)：將數(shù)值區(qū)域改為單一數(shù)值。修改數(shù)值區(qū)域，使其包含公式所在的數(shù)據(jù)行或列。（3）#DIV/O!當(dāng)公式被零除時，將會產(chǎn)生不正確值#DIV/O!。原由一：在公式中，除數(shù)運(yùn)用了指向空單元格或包含零值單元格的單元格引用（在Excel中如果運(yùn)算對象是空白單元格，Excel將此空值當(dāng)作零值）。處理要領(lǐng)：修改單元格引用，或者在用作除數(shù)的單元格中輸入不為零的值。原由二：輸入的公式中包含明顯的除數(shù)零，例如：=5/0。處理要領(lǐng)：將零改為非零值。（4）#NAME?在公式中運(yùn)用了Excel不能識別的文本時將產(chǎn)生不正確值#NAME?。原由一：刪除了公式中運(yùn)用的名稱，或者運(yùn)用了不存在的名稱。處理要領(lǐng)：確認(rèn)運(yùn)用的名稱確實存在。選擇菜單"插入""名稱""定義"命令，如果所需名稱沒有被列出，請運(yùn)用"定義"命令添加相應(yīng)的名稱。原由二：名稱的拼寫不正確。處理要領(lǐng)：修改拼寫不正確的名稱。原由三：在公式中運(yùn)用標(biāo)志。處理要領(lǐng)：選擇菜單中"工具""選項"命令，打開"選項"對話框，然后單擊"重新計算"標(biāo)簽，在"工作薄選項"下，選中"接受公式標(biāo)志"復(fù)選框。原由四：在公式中輸入文本時沒有運(yùn)用雙引號。處理要領(lǐng)：Excel將其解釋為名稱，而不理會用戶準(zhǔn)備將其用作文本的想法，將公式中的文本括在雙引號中。例如：下面的公式將一段文本"總計："和單元格B50中的數(shù)值合并在一起：="總計："&B50原由五：在區(qū)域的引用中缺少冒號。處理要領(lǐng)：確認(rèn)公式中，運(yùn)用的所有區(qū)域引用都運(yùn)用冒號。例如：SUM（A2:B34）。（5）#N/A原由：當(dāng)在函數(shù)或公式中沒有可用數(shù)值時，將產(chǎn)生不正確值#N/A。處理要領(lǐng)：如果工作表中某些單元格暫時沒有數(shù)值，請在這些單元格中輸入"#N/A"，公式在引用這些單元格時，將不執(zhí)行數(shù)值計算，而是返回#N/A。（6）#REF!當(dāng)單元格引用無效時將產(chǎn)生不正確值#REF！。原由：刪除了由其他公式引用的單元格，或?qū)⒁苿訂卧裾迟N到由其他公式引用的單元格中。處理要領(lǐng)：修改公式或者在刪除或粘貼單元格之后，立即單擊"撤消"按鈕，以恢復(fù)工作表中的單元格。（7）#NUM！當(dāng)公式或函數(shù)中某個數(shù)字有疑問時將產(chǎn)生不正確值#NUM！。原由一：在須要數(shù)字參數(shù)的函數(shù)中運(yùn)用了不能接受的參數(shù)。處理要領(lǐng)：確認(rèn)函數(shù)中運(yùn)用的參數(shù)類型正確無誤。原由二：運(yùn)用了迭代計算的工作表函數(shù)，例如：IRR或RATE，并且函數(shù)不能產(chǎn)生有效的結(jié)果。處理要領(lǐng)：為工作表函數(shù)運(yùn)用不同的原始值。原由三：由公式產(chǎn)生的數(shù)字太大或太小，Excel不能表示。處理要領(lǐng)：修改公式，使其結(jié)果在有效數(shù)字范圍之間。（8）#NULL！當(dāng)試圖為兩個并不相交的區(qū)域指定交叉點時將產(chǎn)生不正確值#NULL！。原由：運(yùn)用了不正確的區(qū)域運(yùn)算符或不正確的單元格引用。處理要領(lǐng)：如果要引用兩個不相交的區(qū)域，請運(yùn)用聯(lián)合運(yùn)算符逗號（，）。公式要對兩個區(qū)域求和，請確認(rèn)在引用這兩個區(qū)域時，運(yùn)用逗號。如：SUM（A1:A13，D12:D23）。如果沒有運(yùn)用逗號，Excel將試圖對同時屬于兩個區(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(