制作非常漂亮EXCEL的多種方法

關(guān)于制作非常漂亮EXCEL的多種方法第一頁，共五十一頁，編輯于2023年，星期日

網(wǎng)上有很多關(guān)于如何制作漂亮的幻燈片的教程，而對Excel的美化則寥寥無幾。實際上Excel不僅僅是一個電子表格軟件，它的單元格容易控制樣式，操作方便，因此使用Excel制作僅供閱讀的電子文檔也十分合適。這就要求文檔制作者必須懂得一些基本技巧，以制作出賞心悅目的電子文檔來。下面是我制作Excel格式電子文檔時的一些經(jīng)驗，分享給大家。希望大家都能夠制作出漂亮的文檔。前言第二頁，共五十一頁，編輯于2023年，星期日1.選擇合適的顯示比例。中文字體通常使用宋體，而宋體在默認(rèn)的100%狀態(tài)下并不好看（如下圖）。相比而言，85%的比例就舒服很多。75%的比例略嫌小，不過當(dāng)你有大量的數(shù)據(jù)時，或者制作文字較多的文檔時，75%的顯示比例就比較合適。

85%的比例很容易調(diào)節(jié)，只需在默認(rèn)的100%狀態(tài)下按住Ctrl鍵并向上滾動滾輪一次即可。75%需要手動輸入比例。比75%再小的顯示比例會使文字變形，不宜使用。另外Excel工作表保存后顯示比例也會一起被保存，可以保證閱讀者與你看到同樣的效果制作漂亮ＥＸＣEＬ的七種方法第三頁，共五十一頁，編輯于2023年，星期日2.背景網(wǎng)格線的取舍。

Excel默認(rèn)情況下會顯示灰色的網(wǎng)格線，而這個網(wǎng)格線會對顯示效果產(chǎn)生很大的影響。如下圖，同一張表格在有無網(wǎng)格線的情況下給人的感覺就完全不同——

有網(wǎng)格線時給人一種“這是一張以數(shù)據(jù)為主的表格”的心理暗示，而去掉網(wǎng)格線則會使重點落到工作表的內(nèi)容上，削弱表格的作用。去掉網(wǎng)格線可以在工具->選項中視圖選項卡中去掉網(wǎng)格線，但這種方法依賴于閱讀者的設(shè)置，所以最好選擇另外一條路：全選所有單元格(Ctrl-A)并設(shè)置白色背景。因此，以表格為主的工作表可以保留網(wǎng)格線，而以文字說明為主的工作表則最好去掉網(wǎng)格線。第四頁，共五十一頁，編輯于2023年，星期日3.設(shè)置適當(dāng)?shù)木€寬。

表格線寬度也會極大地影響閱讀者的心理。要搭配使用粗細(xì)線。最簡便易行的方法就是細(xì)內(nèi)線+粗邊框（只需使用格式工具欄中的邊框按鈕兩次即可做到）。全用粗線仿佛你在大喊大叫一樣，感覺很粗魯；而完全用細(xì)線又無法體現(xiàn)出表格的概念；而不用邊線會讓人覺得你的數(shù)據(jù)未經(jīng)過精雕細(xì)琢。

第五頁，共五十一頁，編輯于2023年，星期日

另外，大片的小單元格聚集時表格線會干擾閱讀者的視線，這時可嘗試將小單元格的邊線換成虛線。

第六頁，共五十一頁，編輯于2023年，星期日4.適當(dāng)使用粗體，但別太過分

毋庸置疑，表頭當(dāng)然要用粗體。對于表格內(nèi)的數(shù)據(jù)，原則來說不應(yīng)當(dāng)使用粗體，以免喧賓奪主。但也有特例，當(dāng)數(shù)據(jù)稀疏時，可以將其設(shè)置為黑體，起到強(qiáng)調(diào)的作用。粗體的使用也不宜泛濫，當(dāng)一屏文字大部分都是粗體時，你就應(yīng)當(dāng)考慮考慮閱讀者的感受了。第七頁，共五十一頁，編輯于2023年，星期日5.給標(biāo)題單元格加上背景色。毋庸置疑，單元格背景色按鈕的最下一排顏色天生就是用作單元格背景的（下圖中的紅框部分）。我通常的配色習(xí)慣是：橫向表頭-淡藍(lán)色，縱向表頭-淺綠色，普通單元格-白色，特殊單元格(如自動計算的單元格)-淺黃色，如下圖所示。這樣就能將各個單元格的功能區(qū)分開。第八頁，共五十一頁，編輯于2023年，星期日6.盡量少用單元格結(jié)合

單元格結(jié)合的兩個缺點：第一，在結(jié)合過的單元格前后插入新行時，新行的單元格不會自動結(jié)合，導(dǎo)致每一行的結(jié)合方式不一致；第二，有些情況下結(jié)合過的單元格中無法粘貼數(shù)據(jù)，帶來不必要的麻煩。因此，當(dāng)你制作收集數(shù)據(jù)的表格時，盡量不要使用單元格結(jié)合功能。

那么想要一個寬點的格子怎么辦？實際應(yīng)用中我發(fā)現(xiàn)，好多人使用單元格結(jié)合，其實只是想要一個大一點的格子而已。其實拖動單元格標(biāo)題可以改變單元格大小的，這個功能好多人都不知道么？第九頁，共五十一頁，編輯于2023年，星期日7.標(biāo)注的方法

給單元格做標(biāo)注有兩種方法。一種是右鍵單擊單元格插入批注，編輯完成后再次右擊顯示批注即可。優(yōu)點是使用方便，缺點是無法自定義樣式。另一種方法就是自己畫圖形，我喜歡使用圓角矩形，設(shè)置背景色為淺色，并用同種深顏色作為邊框色。第十頁，共五十一頁，編輯于2023年，星期日excl表格怎么加密

文件——另存為，彈出窗口后點右上角的常規(guī)選項。輸入并確認(rèn)密碼后點保存，當(dāng)問是否覆蓋時點是即可。第十一頁，共五十一頁，編輯于2023年，星期日EXCEL圖表處理

Excel具有完整的圖表功能，它不僅可以生成諸如條形圖、折線圖、餅圖等標(biāo)準(zhǔn)圖表，還可以生成較為復(fù)雜的三維立體圖表。對各種財務(wù)數(shù)據(jù)進(jìn)行圖表處理，可以更直觀地進(jìn)行財務(wù)分析，找出工作表格不容易發(fā)現(xiàn)的問題，使得財務(wù)管理工作更為有效。第十二頁，共五十一頁，編輯于2023年，星期日2.2.1圖表類型Excel提供了約14種標(biāo)準(zhǔn)圖表類型，如面積圖、柱形圖、條形圖、折線圖、餅圖、圓環(huán)圖、氣泡圖、雷達(dá)圖、股價圖、曲面圖、散點圖、錐形圖、圓柱圖、棱錐圖等，每種圖表類型又都有幾種不同的子類型。此外，Excel還提供了約20種自定義圖表類型，用戶可根據(jù)不同的需要選用適當(dāng)?shù)膱D表類型。關(guān)于各種圖表類型的具體情況，可單擊工具欄上的【圖表向?qū)А堪粹o，或單擊【插入】菜單，選擇【圖表】項，即可查看各種圖表。第十三頁，共五十一頁，編輯于2023年，星期日2.2.2圖表的建立

建立圖表的過程非常簡單，只要按照【圖表向?qū)А康挠嘘P(guān)說明，一步一步地進(jìn)行操作，即可完成圖表的制作。下面結(jié)合實例進(jìn)行說明。【例2-8】某企業(yè)2002年12個月的銷售量與銷售費用的有關(guān)數(shù)據(jù)如圖2-19所示，繪制各月銷售額與銷售費用之間關(guān)系的圖表的步驟如下：（1）選取單元格區(qū)域A2：M3，單擊工具欄上的【圖表向?qū)А堪粹o，或單擊【插入】菜單，選擇【圖表】項，出現(xiàn)【圖表向?qū)В?步驟之1－圖表類型】對話框，如圖2-20所示，在【圖表類型】中選【折線圖】，在【子圖表類型】中選【數(shù)據(jù)點折線圖】，單擊【下一步】按鈕，出現(xiàn)【圖表向?qū)В?步驟之2－圖表數(shù)據(jù)源】對話框，如圖2-21所示。（2）在【圖表向?qū)В?步驟之2－圖表源數(shù)據(jù)】對話框中，單擊【系列】，在【分類（X）軸標(biāo)志】欄中填入“=Sheet1!$B$1:$M$1”，單擊【下一步】按鈕，出現(xiàn)【圖表向?qū)В?步驟之3－圖表選項】對話框，（3）在【圖表向?qū)В?步驟之3－圖表選項】對話框中，對【標(biāo)題】的各項進(jìn)行輸入，即在【圖表標(biāo)題】欄中填入“銷售額和銷售費用的變化圖”，在【數(shù)值X軸】欄中填入“月份”，在【數(shù)值Y軸】欄中填入“金額（千元）”，單擊【下一步】按鈕，出現(xiàn)【圖表向?qū)В?步驟之4－圖表位置】如圖2-23所示。（4）在【圖表向?qū)В?步驟之4－圖表位置】對話框中，若要建立嵌入式圖表，即圖表嵌入在本工作表中，則選擇“作為其中的對象插入”；若要建立工作表圖表，則選擇“作為新工作表插入”。這里選擇“作為其中的對象插入”；然后單擊【完成】按鈕，即得到需要的圖表，如圖2-24所示。第十四頁，共五十一頁，編輯于2023年，星期日2.2.3圖表的編輯、修改及格式化通過圖表向?qū)Ы⒌膱D表可能不盡人意，如標(biāo)題太大或太小、坐標(biāo)系列太多、圖表尺寸太小、漏掉了數(shù)據(jù)系列、需要添加數(shù)據(jù)標(biāo)志等等，這時就需要對圖表進(jìn)行修改和格式化。1．設(shè)置坐標(biāo)、標(biāo)題、圖例等的格式設(shè)置坐標(biāo)、標(biāo)題、圖例等的格式的方法非常簡單，可將鼠標(biāo)移到坐標(biāo)、標(biāo)題、圖例等上，單擊右鍵，在快捷菜單上選擇相應(yīng)的項目即可。例如要改變X坐標(biāo)大小，單擊右鍵，出現(xiàn)快捷菜單，選擇【坐標(biāo)軸格式】項，就會彈出【坐標(biāo)軸格式】對話框，如圖2-25所示，選擇需要修改的項目，進(jìn)行設(shè)置即可。2．改變圖表大小單擊圖表區(qū)域，將它激活，圖表邊框出現(xiàn)8個操作柄，用鼠標(biāo)指向某個操作柄，當(dāng)鼠標(biāo)指針呈現(xiàn)雙箭頭時，按住左鍵不放，拖動操作柄到需要的位置上，然后放開鼠標(biāo)左鍵，即可完成。3．移動或復(fù)制圖表移動：單擊圖表區(qū)域，將它激活，圖表邊框出現(xiàn)8個操作柄，在圖表區(qū)域按住鼠標(biāo)左鍵不放，拖動鼠標(biāo)將圖表移到需要的地方。復(fù)制：單擊圖表區(qū)域，將它激活，圖表邊框出現(xiàn)8個操作柄，在圖表區(qū)域按住鼠標(biāo)左鍵不放，拖動鼠標(biāo)將圖表移到需要的地方，按Ctrl鍵，然后放開鼠標(biāo)。若需要將圖表復(fù)制到其他工作表或其他文件中，可選中圖表，按“Ctrl+C”鍵，再在需要安置圖表的工作表或其他文件的適當(dāng)位置，按“Ctrl+V”鍵4．添加數(shù)據(jù)標(biāo)志在很多情況下，在圖表上添加數(shù)據(jù)標(biāo)志，可以更直觀地表示因素的變化情況。添加數(shù)據(jù)標(biāo)志的步驟如下：單擊需要顯示數(shù)據(jù)標(biāo)志的數(shù)據(jù)點，然后按鼠標(biāo)右鍵，出現(xiàn)快捷菜單，如圖2-26所示，選擇【數(shù)據(jù)系列格式】對話框，單擊【數(shù)據(jù)標(biāo)志】，選擇“顯示值”。ExcelXP函數(shù)實用教程精華集iTbulo.COM2005-12-16未知(102189)第十五頁，共五十一頁，編輯于2023年，星期日ExcelXP函數(shù)快速上手第十六頁，共五十一頁，編輯于2023年，星期日1．求和函數(shù)SUM

語法：SUM（number1，number2，．．．）。參數(shù)：number1、number2．．．為1到30個數(shù)值（包括邏輯值和文本表達(dá)式）、區(qū)域或引用，各參數(shù)之間必須用逗號加以分隔。注意：參數(shù)中的數(shù)字、邏輯值及數(shù)字的文本表達(dá)式可以參與計算，其中邏輯值被轉(zhuǎn)換為1，文本則被轉(zhuǎn)換為數(shù)字。如果參數(shù)為數(shù)組或引用，只有其中的數(shù)字參與計算，數(shù)組或引用中的空白單元格、邏輯值、文本或錯誤值則被忽略。應(yīng)用實例一：跨表求和使用SUM函數(shù)在同一工作表中求和比較簡單，如果需要對不同工作表的多個區(qū)域進(jìn)行求和，可以采用以下方法：選中ExcelXP“插入函數(shù)”對話框中的函數(shù)，“確定”后打開“函數(shù)參數(shù)”對話框。切換至第一個工作表，鼠標(biāo)單擊“number1”框后選中需要求和的區(qū)域。如果同一工作表中的其他區(qū)域需要參與計算，可以單擊“number2”框，再次選中工作表中要計算的其他區(qū)域。上述操作完成后切換至第二個工作表，重復(fù)上述操作即可完成輸入?！按_定”后公式所在單元格將顯示計算結(jié)果。應(yīng)用實例二：SUM函數(shù)中的加減混合運算財務(wù)統(tǒng)計需要進(jìn)行加減混合運算，例如扣除現(xiàn)金流量表中的若干支出項目。按照規(guī)定，工作表中的這些項目沒有輸入負(fù)號。這時可以構(gòu)造“=SUM（B2:B6，C2:C9，-D2，-E2）”這樣的公式。其中B2:B6，C2:C9引用是收入，而D2、E2為支出。由于Excel不允許在單元格引用前面加負(fù)號，所以應(yīng)在表示支出的單元格前加負(fù)號，這樣即可計算出正確結(jié)果。即使支出數(shù)據(jù)所在的單元格連續(xù)，也必須用逗號將它們逐個隔開，寫成“=SUM（B2:B6，C2:C9，-D2，-D3，D4）”這樣的形式。應(yīng)用實例三：及格人數(shù)統(tǒng)計假如B1:B50區(qū)域存放學(xué)生性別，C1:C50單元格存放某班學(xué)生的考試成績，要想統(tǒng)計考試成績及格的女生人數(shù)。可以使用公式“=SUM（IF（B1:B50=″女″，IF（C1:C50>=60，1，0）））”，由于它是一個數(shù)組公式，輸入結(jié)束后必須按住Ctrl+Shift鍵回車。公式兩邊會自動添加上大括號，在編輯欄顯示為“{=SUM（IF（B1:B50=″女″，IF（C1:C50>=60，1，0）））}”，這是使用數(shù)組公式必不可少的步驟。第十七頁，共五十一頁，編輯于2023年，星期日2.平均值函數(shù)AVERAGE

語法：AVERAGE（number1，number2，．．．）。參數(shù)：number1、number2．．．是需要計算平均值的1～30個參數(shù)。注意：參數(shù)可以是數(shù)字、包含數(shù)字的名稱、數(shù)組或引用。數(shù)組或單元格引用中的文字、邏輯值或空白單元格將被忽略，但單元格中的零則參與計算。如果需要將參數(shù)中的零排除在外，則要使用特殊設(shè)計的公式，下面的介紹。應(yīng)用實例一：跨表計算平均值標(biāo)簽名為“一班”、“二班”和“三班”的工作表存放各班學(xué)生的成績，則它們的總平均分計算公式為“=AVERAGE（一班!C1:C36，三班!C1:C32，三班!C1:C45）”。式中的引用輸入方法與SUM跨表求和時相同。應(yīng)用實例二：忽略零的平均值計算公式假設(shè)A1:A200隨機(jī)存放包括零在內(nèi)的48個數(shù)值，在AVERAGE參數(shù)中去掉零引用很麻煩，這種情況可以使用公式“=AVERAGE（IF（A1:A200<>0，A1:A200，″″）”。公式輸入結(jié)束后按住Ctrl+Shift回車，即可對A1:A200中的數(shù)值（不包括零）計算平均值。第十八頁，共五十一頁，編輯于2023年，星期日3．邏輯函數(shù)IF語法：IF（logical_test，value_if_true，value_if_false）。參數(shù)：logical_test是結(jié)果為true（真）或false（假）的數(shù)值或表達(dá)式；value_if_true是logical_test為true時函數(shù)的返回值，如果logical_test為ture并且省略了value_if_true，則返回true。而且value_if_true可以是一個表達(dá)式；value_if_false是logical_test為false時函數(shù)的返回值。如果logical_test為false并且省略value_if_false，則返回false。Value_if_false也可以是一個表達(dá)式。應(yīng)用實例一：個人收入調(diào)節(jié)稅計算假設(shè)個人收入調(diào)節(jié)稅的稽征辦法是：工資在1000元以下的免征調(diào)節(jié)稅，工資1000元以上至1500元的超過部分按5%的稅率征收，1500元以上至2000元的超過部分按8%的稅率征收，高于2000元的超過部分按30%的稅率征收。假如工作表的B列存放職工姓名，C列存放工資，選中D列的空白單元格輸入公式“=IF（C2<=1000，″″，IF（（C2-1000）<=1500，（C2-1000）*0.05，IF（C2-E2<=1500，（C2-1500）*0.08，IF（C2>2000，（C2-2000）*0.3））））”，回車后即可計算出C2工資應(yīng)繳納的收入調(diào)節(jié)稅。公式中的IF語句是逐次計算的，如果第一個邏輯判斷C2<=1000成立，則公式所在單元格被填入空格；如果第一個邏輯判斷式不成立，則計算第二個IF語句；直至計算結(jié)束。如果稅率征收標(biāo)準(zhǔn)發(fā)生了變化，只須改變邏輯和計算式中的值，如1000、1500和2000等即可。應(yīng)用實例二：消除無意義的零用SUM函數(shù)計算工資總額等問題時，若引用的全部參數(shù)均為空白單元格，公式仍然會計算出一個“0”。這樣打印出來的報表不僅很不美觀。為此可將計算公式設(shè)計為“=IF（SUM（A1:B1，D2:E8）<>0，SUM（A1:B1，D2:E8），″″）”，即是當(dāng)SUM（A1:B1，D2:E8）計算結(jié)果不等于零時，公式所在單元格顯示SUM（A1:B1，D2:E8）的計算結(jié)果，否則顯示一個空格。上面這個問題在財務(wù)計算中依然存在，如公式“=A1-A6-A9”有可能為零或顯示負(fù)數(shù)。為了避免打印零或負(fù)數(shù)，可以設(shè)計公式“=IF（A2-A6-A9=0，″″，IF（A2-A6-A9<0，RMB（A2-A6-A9），A2-A6-A9））”。當(dāng)A2-A6-A9=0時在單元格中填寫零，否則進(jìn)行下一個邏輯判斷。如果A2-A6-A9<0則在結(jié)果前添加一個“￥”符號，否則進(jìn)行正常運算直至結(jié)束。應(yīng)用實例三：多條件求和假設(shè)C1:C460區(qū)域內(nèi)存放著職工的職稱，D1:D460區(qū)域存放著職工的性別。如果要統(tǒng)計具有高級職稱的男性職工總數(shù)，可以使用公式“=SUM（IF（C1:C460=″高級″，IF（D1:D460=″男″，1，0）））”。這是一個數(shù)組公式，輸入結(jié)束后按住Ctrl第十九頁，共五十一頁，編輯于2023年，星期日4．快捷方式或鏈接創(chuàng)建函數(shù)HYPERLINK

語法：HYPERLINK（link_location，friendly_name）。參數(shù)：link_location是文件的路徑和文件名，它可以指向文檔中的某個具體位置。如Excel工作表中的特定單元格或區(qū)域，或某個Word文檔中的書簽，也可以指向硬盤中的文件或是Internet或Intranet的URL。Friendly_name為單元格中顯示的鏈接文字或數(shù)字，它用藍(lán)色顯示并帶有下劃線。如果省略了friendly_name，單元格就將link_location顯示為鏈接。應(yīng)用實例：管理文檔或網(wǎng)站地址如果你擁有大量文檔或收集了許多網(wǎng)站地址，管理起來一定有困難。如果能夠?qū)⑵渲械奈臋n名、URL等與文檔等對象鏈接起來，不僅管理方便，還可以直接打開文檔或訪問站點。具體操作方法是：首先根據(jù)文檔類型建立管理工作表，其中的文件名或網(wǎng)站名必須使用以下方法輸入：選中一個空白單元格，單擊ExcelXP工具欄中的“粘貼函數(shù)”按鈕，打開“粘貼函數(shù)”對話框，在“函數(shù)分類”框下選中“查找與引用”，然后在“函數(shù)名”框內(nèi)找到HYPERLINK函數(shù)。單擊對話框中的“確定”按鈕，彈出“HYPERLINK”函數(shù)向?qū)?。在“l(fā)ink_location”框中輸入文件的完整路徑和名稱（包括擴(kuò)展名），如“C:＼mydocuments＼IT網(wǎng)站集錦.doc”，然后在“friendly_name”框中輸入文件名（如“IT網(wǎng)站集錦”）。確認(rèn)輸入無誤單擊“確定”按鈕，所選單元格即出現(xiàn)帶下劃線的紫色文件名“IT網(wǎng)站集錦”。此后你就可以從工作表中打開文檔，方法是：打開工作表，在其中找到需要打開的文件。箭頭光標(biāo)指向文件名會變成手形，若停留片刻則會顯示該文件的完整路徑和名稱，單擊則會調(diào)用關(guān)聯(lián)程序?qū)⑽募蜷_。如果你收集了許多網(wǎng)站的URL，還可以用此法建立一個大型“收藏夾”。既可以用Excel的強(qiáng)大功能進(jìn)行管理，又可以從工作表中直接訪問Web站點。第二十頁，共五十一頁，編輯于2023年，星期日5．計數(shù)函數(shù)COUNT

語法：COUNT（value1，value2，．．．）。參數(shù)：value1，value2．．．是包含或引用各類數(shù)據(jù)的1～30個參數(shù)。注意：COUNT函數(shù)計數(shù)時數(shù)字、日期或文本表示的數(shù)字會參與計數(shù)，錯誤值或其他無法轉(zhuǎn)換成數(shù)字的文字被忽略。如果參數(shù)是一個數(shù)組或引用，那么只有數(shù)組或引用中的數(shù)字參與計數(shù)；其中的空白單元格、邏輯值、文字或錯誤值均被忽略。應(yīng)用實例：及格率統(tǒng)計假如C1:G42存放著42名學(xué)生的考試成績，在一個空白單元格內(nèi)輸入公式“=COUNTIF（C1:C42，″>=60″）/COUNTA（C1:C42）”回車，即可計算出該列成績的及格率（即分?jǐn)?shù)為60及以上的人數(shù)占總?cè)藬?shù)的百分比）。第二十一頁，共五十一頁，編輯于2023年，星期日6．最大值函數(shù)MAX、最小值函數(shù)MIN

語法：MAX（number1，number2，．．．），MIN（number1，number2，．．．）。參數(shù)：number1，number2．．．是需要找出最大值（最小值）的1至30個數(shù)值、數(shù)組或引用。注意：函數(shù)中的參數(shù)可以是數(shù)字、空白單元格、邏輯值或數(shù)字的文本形式，如果參數(shù)是不能轉(zhuǎn)換為數(shù)字的內(nèi)容將導(dǎo)致錯誤。如果參數(shù)為數(shù)組或引用，則只有數(shù)組或引用中的數(shù)字參與計算，空白單元格、邏輯值或文本則被忽略。應(yīng)用實例：查詢最高分（最低分）假如C1：G42存放著42名學(xué)生的考試成績，則選中一個空白單元格，在編輯欄輸入公式“=MAX（C1:C42）”，回車后即可計算出其中的最高分是多少。

第二十二頁，共五十一頁，編輯于2023年，星期日7．條件求和函數(shù)SUMIF

語法：SUMIF（range，criteria，sum_range）。參數(shù)：range是用于條件判斷的單元格區(qū)域，criteria是由數(shù)字、邏輯表達(dá)式等組成的判定條件，sum_range為需要求和的單元格、區(qū)域或引用。應(yīng)用實例：及格平均分統(tǒng)計假如A1:A36單元格存放某班學(xué)生的考試成績，若要計算及格學(xué)生的平均分，可以使用公式“=SUMIF（A1:A36，″>=60″，A1:A36）/COUNTIF（A1:A36，″>=60″）。公式中的“=SUMIF（A1:A36，″>=60″，A1:A36）”計算及格學(xué)生的總分，式中的“A1:A36”為提供邏輯判斷依據(jù)的單元格引用，“>=60”為判斷條件，不符合條件的數(shù)據(jù)不參與求和，A1:A36則是邏輯判斷和求和的對象。公式中的COUNTIF（A1:A36，″>=60″）用來統(tǒng)計及格學(xué)生的人數(shù)。第二十三頁，共五十一頁，編輯于2023年，星期日8．貸款償還計算函數(shù)PMT

語法：PMT（rate，nper，pv，fv，type）.參數(shù)：如今貸款買房子或車子的人越來越多，計算某一貸款的月償還金額是考慮貸款的重要依據(jù)，ExcelXP提供的PMT函數(shù)是完成這一任務(wù)的好工具。語法中的rate是貸款利率；nper為貸款償還期限；pv是貸款本金；fv為最后一次付款后剩余的貸款金額，如果省略fv，則認(rèn)為它的值為零；type為0或1，用來指定付款時間是在月初還是月末。如果省略type，則假設(shè)其值為零。應(yīng)用實例：購房還款金額假如你為購房貸款十萬元，如果年利率為7%，每月末還款。采用十年還清方式時，月還款額計算公式為“=PMT（7%/12，120，-100000）”。其結(jié)果為￥-1，161.08，就是你每月須償還貸款1161.08元。第二十四頁，共五十一頁，編輯于2023年，星期日9．樣本的標(biāo)準(zhǔn)偏差函數(shù)STDEV

語法：STDEV（number1，number2，．．．）。參數(shù)：number1，number2，．．．為對應(yīng)于總體樣本的1到30個參數(shù)，它們可以是數(shù)值、引用或數(shù)組。注意：STDEV函數(shù)的參數(shù)是總體中的樣本，并忽略參數(shù)中的邏輯值（true或false）和文本。如果需要用全部數(shù)據(jù)計算標(biāo)準(zhǔn)偏差，則應(yīng)使用STDEVP函數(shù)。如果參數(shù)中的邏輯值和文本不能忽略，請使用STDEVA函數(shù)。應(yīng)用實例一：成績離散度估計假設(shè)某班共有36名學(xué)生參加考試，隨機(jī)抽取的五個分?jǐn)?shù)為A1=78、A2=45、A3=90、A4=12和A5=85。如果要估算本次考試成績相對平均分的離散程度，即學(xué)生的考試成績偏離平均分的多少，可以使用公式“=STDEV（A1:A5）”。其計算結(jié)果為33.00757489，標(biāo)準(zhǔn)偏差的數(shù)值越大成績越分散。應(yīng)用實例二：質(zhì)量波動估計質(zhì)量控制等場合也能用到STDEV函數(shù)，如從一批鋼絲繩中隨機(jī)抽出若干進(jìn)行試驗，分別測出它們的抗拉強(qiáng)度。根據(jù)STDEV函數(shù)的計算結(jié)果即可判斷鋼絲繩的抗拉強(qiáng)度是否分散，如果計算的標(biāo)準(zhǔn)偏差比較小，說明抗拉強(qiáng)度的一致性好，質(zhì)量比較穩(wěn)定。反之說明鋼絲繩的質(zhì)量波動較大，抗拉強(qiáng)度不夠一致。第二十五頁，共五十一頁，編輯于2023年，星期日10．排序函數(shù)RANK語法：RANK（number，ref，order）。參數(shù)：number是需要計算其排位的一個數(shù)字；ref是包含一組數(shù)字的數(shù)組或引用（其中的非數(shù)值型參數(shù)將被忽略）；order是用來說明排序方式的數(shù)字（如果order為零或省略，則以降序方式給出結(jié)果，反之按升序方式）。應(yīng)用實例：產(chǎn)值排序假如圖1中的E2、E3、E4單元格存放一季度的總產(chǎn)值。(圖1)計算各車間產(chǎn)值排名的方法是：在F2單元格內(nèi)輸入公式“=RANK（E2，$E$2:$E$4）”，敲回車即可計算出鑄造車間的產(chǎn)值排名是2。再將F2中的公式復(fù)制到剪貼板，選中F3、F4單元格按Ctrl+V，就能計算出其余兩個車間的產(chǎn)值排名3和1。如果B1單元格中輸入的公式為“=RANK（E2，$E$2:$E$4，1）”，則計算出的序數(shù)按升序方式排列，即2、1和3。需要注意的是：相同數(shù)值用RANK函數(shù)計算得到的序數(shù)（名次）相同，但會導(dǎo)致后續(xù)數(shù)字的序數(shù)空缺。假如上例中F2單元格存放的數(shù)值與F3相同，則按本法計算出的排名分別是3、3和1（降序時）。即176.7出現(xiàn)兩次時，鑄造和維修車間的產(chǎn)值排名均為3，后續(xù)金工車間的排名就是1（沒有2）。第二十六頁，共五十一頁，編輯于2023年，星期日11．四舍五入函數(shù)語法：ROUND（number，num_digits）。參數(shù)：number是需要四舍五入的數(shù)字；num_digits為指定的位數(shù)，number將按此位數(shù)進(jìn)行四舍五入。注意：如果num_digits大于0，則四舍五入到指定的小數(shù)位；如果num_digits等于0，則四舍五入到最接近的整數(shù)；如果num_digits小于0，則在小數(shù)點左側(cè)按指定位數(shù)四舍五入。應(yīng)用實例：消除計算誤差假設(shè)Excel工作表中有D2=356.68、E2=128.12，需要將D2與E2之和乘以0.1，將計算結(jié)果四舍五入取整數(shù)，再將這個結(jié)果乘以1.36（取兩位小數(shù)）得到最終結(jié)果。一般用戶的做法是選中某個單元格（如F2），使用“單元格”命令將它的小數(shù)位數(shù)設(shè)為零，然后在其中輸入公式“F2=（D1+E1）*0.1”。再將G2單元格的小數(shù)位數(shù)設(shè)成兩位，最后把F2*1.36的結(jié)果存入其中就可以了。從表面上看，上述方法沒有什么問題。因為（D1+E1）*0.1在F2單元格顯示48（注意：是顯示48），如果F2單元格的小數(shù)位數(shù)為零，（D1+E1）*0.1經(jīng)四舍五入后的結(jié)果就是48。接下去卻出了問題，因為F2*1.36的計算結(jié)果是65.90，根本不是48*1.36的正確結(jié)果65.28，其中65.90是（D2+E2）*0.1未經(jīng)四舍五入直接乘以1.36的結(jié)果。以上計算結(jié)果說明：“單元格格式”、“數(shù)字”選項卡設(shè)置的“小數(shù)位數(shù)”，只能將單元格數(shù)值的顯示結(jié)果進(jìn)行四舍五入，并不能對所存放的數(shù)值進(jìn)行四舍五入。換句話說，單元格數(shù)值的顯示結(jié)果與實際存放結(jié)果并不完全一致，如果不注意這個問題，計算工資等敏感數(shù)據(jù)就會出現(xiàn)錯誤。例如在上例中，F(xiàn)2單元格內(nèi)的數(shù)值雖然顯示為48，但實際存放的卻是48.45，自然得出了48.45*1.36=65.90的結(jié)果（按計算要求應(yīng)為65.28）。要解決這個問題并不難，你只須在G2單元格內(nèi)輸入公式“=（ROUND（（D2+E2）*0.1，0））*1.36”，就可以按要求計算出正確結(jié)果65.28。式中的ROUND函數(shù)按指定位數(shù)對“（D2+E2）*0.1”進(jìn)行四舍五入，函數(shù)中的參數(shù)0將“（D2+E2）*0.1”四舍五入到最接近的整數(shù)。第二十七頁，共五十一頁，編輯于2023年，星期日12．條件計數(shù)函數(shù)COUNTIF

語法：COUNTIF（range，criteria）。參數(shù)：range為需要統(tǒng)計的符合條件的單元格區(qū)域；criteria為參與計算的單元格條件，其形式可以為數(shù)字、表達(dá)式或文本（如36、″>160″和″男″等）。條件中的數(shù)字可以直接寫入，表達(dá)式和文本必須加引號。應(yīng)用實例：男女職工人數(shù)統(tǒng)計假設(shè)A1:A58區(qū)域內(nèi)存放著員工的性別，則公式“=COUNTIF（A1:A58，″女″）”統(tǒng)計其中的女職工數(shù)量，“=COUNTIF（A1:A58，″男″）”統(tǒng)計其中的男職工數(shù)量。COUNTIF函數(shù)還可以統(tǒng)計優(yōu)秀或及格成績的數(shù)量，假如C1：G42存放著42名學(xué)生的考試成績，則公式“=COUNTIF（C2：G2，″>=85″）”可以計算出其中高于等于85分的成績數(shù)目。如將公式改為“=COUNTIF（C2：G2，″>=60″）”，則可以計算出及格分?jǐn)?shù)的個數(shù)。第二十八頁，共五十一頁，編輯于2023年，星期日13．百分排位預(yù)測函數(shù)PERCENTILE

語法：PERCENTILE（array，k）。參數(shù)：array為定義相對位置的數(shù)值數(shù)組或數(shù)值區(qū)域，k為數(shù)組中需要得到其百分排位的值。注意：如果array所在單元格為空白或數(shù)據(jù)個數(shù)超過8191，則返回#NUM！錯誤。如果k<0或k>1，則返回#NUM！錯誤。如果k不是1/（n-1）的倍數(shù)，該函數(shù)使用插值法確定其百分排位。應(yīng)用實例：利潤排行預(yù)測假設(shè)C1:C60區(qū)域存放著幾十個公司的利潤總額。如果你想知道某公司的利潤達(dá)到多少，才能進(jìn)入排名前10%的行列，可以使用公式“=PERCENTILE（C1:C60，0.9）”。如果計算結(jié)果為9867萬元，說明利潤排名要想進(jìn)入前10%，則該公司的利潤至少應(yīng)當(dāng)達(dá)到9867萬元第二十九頁，共五十一頁，編輯于2023年，星期日14．?dāng)?shù)值探測函數(shù)ISNUMBER

語法：ISNUMBER（value）。參數(shù)：如果value為數(shù)值時函數(shù)返回ture（真），否則返回false（假）。應(yīng)用實例一：無錯誤除法公式如果工作表為C1設(shè)計了公式“=A1/B1”，一旦B1單元格沒有輸入除數(shù)，就會在C1中顯示錯誤信息“＃DIV／0！”。這不僅看起來很不美觀，一旦作為報表打印還可能引起誤會。為避免上面提到的問題出現(xiàn)，可將C1單元格中的公式設(shè)計成“=IF（ISNUMBER（B1），A1/B1，″″）”。式中的ISNUMBER函數(shù)對B1單元格進(jìn)行探測，當(dāng)B1被填入數(shù)值時返回true（真），反之返回false（假）。為真時IF函數(shù)執(zhí)行A1/B1的運算，為假時在C1單元格中填入空格。應(yīng)用實例二：消除無意義的零使用SUM函數(shù)計算工資總額時，若引用的單元格區(qū)域沒有數(shù)據(jù)，Excel仍然會計算出一個結(jié)果“0”。這樣打印出來的報表不符合財務(wù)規(guī)定，為此可將公式設(shè)計成“=IF（ISNUMBER（A1:B1），SUM（A1:B1），″″）”。式中ISNUMBER函數(shù)測試SUM函數(shù)所引用的單元格區(qū)域是否全部為空，當(dāng)不為空時返回true（真），反之返回fales（假）。為真時IF函數(shù)執(zhí)行SUM（A1:B1），為假時在存放計算結(jié)果的F1單元格中填入空格，這樣即可避免在F1單元格中出現(xiàn)“0”。第三十頁，共五十一頁，編輯于2023年，星期日15．零存整取收益函數(shù)PV

語法：PV（rate，nper，pmt，fv，type）。參數(shù)：rate為存款利率；nper為總的存款時間，對于三年期零存整取存款來說共有3*12=36個月；pmt為每月存款金額，如果忽略pmt則公式必須包含參數(shù)fv；fv為最后一次存款后希望得到的現(xiàn)金總額，如果省略了fv則公式中必須包含pmt參數(shù)；type為數(shù)字0或1，它指定存款時間是月初還是月末。應(yīng)用實例：零存整取收益函數(shù)PV假如你每月初向銀行存入現(xiàn)金500元，如果年利2.15%（按月計息，即月息2.15%/12）。如果你想知道5年后的存款總額是多少，可以使用公式“=FV（2.15%/12，60，-500，0，1）”計算，其結(jié)果為￥31，698.67。式中的2.15%/12為月息；60為總的付款時間，在按月儲蓄的情況下為儲蓄月份的總和；-500為每月支出的儲蓄金額（-表示支出）；0表示儲蓄開始時賬戶上的金額，如果賬戶上沒有一分錢，則為0否則應(yīng)該輸入基礎(chǔ)金額；1表示存款時間是月初還是月末，1為月初0或忽略為月末。第三十一頁，共五十一頁，編輯于2023年，星期日16．內(nèi)部平均值函數(shù)TRIMMEAN

語法：TRIMMEAN（array，percent）。參數(shù)：array為需要去掉若干數(shù)據(jù)然后求平均值的數(shù)組或數(shù)據(jù)區(qū)域；percent為計算時需要除去的數(shù)據(jù)的比例，如果percent=0.2，說明在20個數(shù)據(jù)中除去4個，即頭部除去2個尾部除去2個。如果percent=0.1，則30個數(shù)據(jù)點的10%等于3，函數(shù)TRIMMEAN將在數(shù)據(jù)首尾各去掉一個數(shù)據(jù)。應(yīng)用實例：評比打分統(tǒng)計歌唱比賽采用打分的方法進(jìn)行評價，為了防止個別人的極端行為，一般計算平均分?jǐn)?shù)要去掉若干最高分和最低分。假如B1:B10區(qū)域存放某位歌手的比賽得分，則去掉一個最高分和一個最低分后的平均分計算公式為“=TRIMMEAN（B1:B10，0.2）”。公式中的0.2表示10個數(shù)據(jù)中去掉2個（10×0.2），即一個最高分和一個最低分。第三十二頁，共五十一頁，編輯于2023年，星期日17．日期年份函數(shù)YEAR

語法：YEAR（serial_number）。參數(shù)：serial_number為待計算年份的日期。應(yīng)用實例：“虛工齡”計算所謂“虛工齡”就是從參加工作算起，每過一年就增加一年工齡，利用YEAR函數(shù)計算工齡的公式是“=YEAR（A1）-YEAR（B1）”。公式中的A1和B1分別存放工齡的起止日期，YEAR（A1）和YEAR（B1）分別計算出兩個日期對應(yīng)的年份，相減后得出虛工齡第三十三頁，共五十一頁，編輯于2023年，星期日18．起止天數(shù)函數(shù)DAYS360

語法：DAYS360（start_date，end_date，method）。參數(shù)：start_date和end_date是用于計算期間天數(shù)的起止日期，可以使用帶引號的文本串（如"1998/01/30"）、系列數(shù)和嵌套函數(shù)的結(jié)果。如果start_date在end_date之后，則DAYS360將返回一個負(fù)數(shù)。第三十四頁，共五十一頁，編輯于2023年，星期日19．取整函數(shù)TRUNC

語法：TRUNC（number，num_digits）。參數(shù)：number是需要截去小數(shù)部分的數(shù)字，num_digits則指定保留到幾位小數(shù)。應(yīng)用實例：“實工齡”計算實際工作滿一年算一年的工齡稱為“實工齡”，如1998年6月1日至2001年12月31日的工齡為3年。計算“實工齡”的公式是“=TRUNC（（DAYS360（″1998/6/1″，″2001/12/31″））/360，0）”，公式中的DAYS360（″1998/6/1″，″2001/12/31″）計算兩個日期相差的天數(shù)，除以360后算出日期相差的年份（小數(shù)）。最后TRUNC函數(shù)將（DAYS360（A1，B1）/360的計算結(jié)果截去小數(shù)部分，從而得出“實工齡”。如果計算結(jié)果需要保留一位小數(shù)，只須將公式修改為“=TRUNC（（DAYS360（″1998/6/1″，″2001/12/31″））/360，1）”即可。如果你要計算參加工作到系統(tǒng)當(dāng)前時間的實工齡，可以將公式修改為“=TRUNC（（DAYS360（″1998/6/1″，NOW（）））/360，0）”。其中NOW（）函數(shù)返回當(dāng)前的系統(tǒng)日期和時間。第三十五頁，共五十一頁，編輯于2023年，星期日20．字符提取函數(shù)MID語法：MID（text，start_num，num_chars）或MIDB（text，start_num，num_bytes）。參數(shù)：text是含有要待提取字符的文本，start_num是要提取的第一個字符的位置（其中第一個字符的start_num為1以此類推），num_chars指定MID從文本中提取的字符個數(shù)，Num_bytes指定MIDB從文本中提取的字符個數(shù)（按字節(jié)計算）。應(yīng)用實例：姓氏提取和統(tǒng)計假如工作表C列存放著職工的姓名，如果想知道某一姓氏的職工人數(shù)，可以采用以下方法。首先使用公式“=MID（C1，1，1）”或“=MIDB（C1，1，2），從C1中提取出職工的姓氏，采用拖動或復(fù)制的方法即可提取其他單元格中的職工姓氏。然后使用公式“=COUNTIF（D1:D58，″張″）”統(tǒng)計出姓張的職工數(shù)量，隨后改變公式中的姓氏就可以統(tǒng)計出其他姓氏職工的數(shù)量。第三十六頁，共五十一頁，編輯于2023年，星期日ExcelXP數(shù)據(jù)庫速成

ExcelXP具有強(qiáng)大的數(shù)據(jù)庫功能，對包含大量數(shù)據(jù)的表格進(jìn)行排序、篩選等處理易如反掌，下面介紹常用的數(shù)據(jù)庫操作。第三十七頁，共五十一頁，編輯于2023年，星期日1．?dāng)?shù)據(jù)排序ExcelXP第一行的單元格具有特殊作用，其中的文字相當(dāng)于數(shù)據(jù)庫字段名，可作為關(guān)鍵字參與排序、記錄單等各種操作。（1）文本排序選舉等場合需要按姓氏筆劃為文本排序，Excel提供了比較好的解決辦法。如果你要將圖1數(shù)據(jù)表按車間名稱的筆劃排序，可以使用以下方法：選中排序關(guān)鍵字所在列（或行）的首個單元格，單擊Excel“數(shù)據(jù)”菜單下的“排序”命令打開對話框，再單擊其中的“選項”按鈕。選中“排序選項”對話框“方法”下的“筆畫排序”，再根據(jù)數(shù)據(jù)排列方向選擇“按行排序”或“按列排序”，“確定”后回到“排序”對話框。如果你的數(shù)據(jù)帶有標(biāo)題行（如“單位”之類），則應(yīng)選中“有標(biāo)題行”（反之不選），然后打開“主要關(guān)鍵字”下拉列表，選擇其中的“單位”，選中排序方式（“升序”或“降序”）后“確定”，表中的所有數(shù)據(jù)就會據(jù)此重新排列。此法稍加變通即可用于“第一名”、“第二名”等文本的排序，請大家自行摸索。（2）自定義排序第三十八頁，共五十一頁，編輯于2023年，星期日1．?dāng)?shù)據(jù)排序（續(xù)）如果你要求Excel按照“金工車間”、“鑄造車間”和“維修車間”的特定順序重排工作表數(shù)據(jù)，前面介紹的幾種方法就無能為力了。這類問題可以用定義排序規(guī)則的方法解決：首先單擊Excel“工具”菜單下的“選項”命令，打開“選項”對話框中的“自定義序列”選項卡。選中左邊“自定義序列”下的“新序列”，光標(biāo)就會在右邊的“輸入序列”框內(nèi)閃動，你就可以輸入“金工車間”、“鑄造車間”等自定義序列了，輸入的每個序列之間要用英文逗號分隔，或者每輸入一個序列就敲回車。如果序列已經(jīng)存在于工作表，可以選中序列所在的單元格區(qū)域單擊“導(dǎo)入”，這些序列就會被自動進(jìn)入“輸入序列”框。無論采用以上那種方法，單擊“添加”按鈕即可將序列放入“自定義序列”中備用。使用排序規(guī)則排序的具體方法與筆劃排序很相似，只是你要打開“排序選項”對話框中的“自定義排序次序”下拉列表，選中前面定義的排序規(guī)則，其他選項保持不動?；氐健芭判颉睂υ捒蚝蟾鶕?jù)需要選擇“升序”或“降序”，“確定”后即可完成數(shù)據(jù)的自定義排序。第三十九頁，共五十一頁，編輯于2023年，星期日2．?dāng)?shù)據(jù)檢索如果ExcelXP工作表中的數(shù)據(jù)字段很多時，查看一條或幾條記錄需要左右滾動屏幕，此時可以采用以下方法檢索并查看：單擊“數(shù)據(jù)”菜單下的“記錄單”命令打開對話框（圖2）。再次單擊其中的“條件”按鈕。對話框中的字段就會變成空白等待輸入，并且“條件”按鈕變?yōu)椤氨韱巍卑粹o。在對話框的相應(yīng)字段位置輸入條件，如果在“序號”字段框內(nèi)輸入12回車，則序號為12的數(shù)據(jù)就會顯示在對話框中。當(dāng)然，你輸入的檢索條件可以使用>、<、>=、<=、<>邏輯符號。例如單擊“條件”按鈕后在“總分”框內(nèi)輸入“>170”，則表示檢索總分大于170的記錄，回車后第一條大于170的數(shù)據(jù)就會顯示在對話框中。繼續(xù)單擊“上一條”或“下一條”按鈕，可以查看檢索出來的其他數(shù)據(jù)。第四十頁，共五十一頁，編輯于2023年，星期日3．?dāng)?shù)據(jù)篩選

上面介紹的方法可以檢索滿足某一條件的記錄，但是檢索條件比較復(fù)雜時（如檢索總分大于170和小于120的紀(jì)錄），這種檢索方法就無能為力了。此時可以使用ExcelXP提供的自定義篩選功能，操作方法是：單擊“數(shù)據(jù)”、“篩選”子菜單下的“自動篩選”命令，每個字段名的右側(cè)會出現(xiàn)一個下拉按鈕。根據(jù)你要篩選的字段單擊下拉按鈕，在下拉菜單中選擇“自定義”命令打開對話框（圖3）。單擊第一行左側(cè)的下拉按鈕，選擇其中的“大于”，然后在其右邊輸入170；按相同方法在第二行中選擇“小于”，并在右邊輸入120，選中兩行中間的“或”后單擊“確定”按鈕，工作表會自動顯示所有符合篩選條件的記錄。篩選出來的記錄可以執(zhí)行復(fù)制等操作，如果你要清除篩選結(jié)果，只要將“數(shù)據(jù)”、“篩選”子菜單下的“自動篩選”前的選中標(biāo)記去掉即可。第四十一頁，共五十一頁，編輯于2023年，星期日4．?dāng)?shù)據(jù)匯總

小計、合計是應(yīng)用非常廣泛的數(shù)據(jù)統(tǒng)計方式，ExcelXP分類匯總是此類操作的高手。你只要將光標(biāo)放在工作表的任一單元格中，單擊“數(shù)據(jù)”菜單下的“分類匯總”命令打開對話框。如果要對某個班級的數(shù)學(xué)、語文成績進(jìn)行匯總，則應(yīng)在“分類字段”中選中“班級”，在“匯總方式”中選擇“求和”，在“選擇匯總項”中選中“數(shù)學(xué)”和“語文”。如果選中“匯總結(jié)果顯示在數(shù)據(jù)下放”，單擊“確定”按鈕后匯總結(jié)果會顯示在工作表數(shù)據(jù)的下面，反之則會顯示在工作表數(shù)據(jù)上方。如果你想清除工作表中的分類匯總結(jié)果，只要打開“分類匯總”對話框，單擊其中的“全部刪除”按鈕就可以了。第四十二頁，共五十一頁，編輯于2023年，星期日5．?dāng)?shù)據(jù)透視表

數(shù)據(jù)透視表是ExcelXP數(shù)據(jù)庫功能的重要組成部分，它能夠非常靈活的匯總數(shù)據(jù)。如果你要對一個學(xué)生成績工作表進(jìn)行統(tǒng)計，計算所有學(xué)生的數(shù)學(xué)成績總分，采用數(shù)據(jù)透視表可以快速完成任務(wù)。單擊“數(shù)據(jù)”菜單下的“數(shù)據(jù)透視表和數(shù)據(jù)透視圖