電子表格函數應用實例整理XXXX

《電子表格函數應用實例整理XXXX》由會員分享，可在線閱讀，更多相關《電子表格函數應用實例整理XXXX（58頁珍藏版）》請在裝配圖網上搜索。

1、電子表格函數實例教程整理 2011 1,求和函數SUM Excel電子表格函數實例教程 語法：SUM (numbed, number2,...)。 參數：numbed、number2...為1到30個數值(包括邏輯值和文本表達式) 、區(qū)域或引用, 各參數之間必須用逗號加以分隔。 注意：參數中的數字、邏輯值及數字的文本表達式可以參與計算，其中邏輯值被轉換為 1, 文本則被轉換為數字。 如果參數為數組或引用， 只有其中的數字參與計算， 數組或引用中的 空白單元格、邏輯值、文本或錯誤值則被忽略。 應用實例一：跨表求和 使用SUM函數在同一工作表中求和比較簡單， 如果需要對不同

2、工作表的多個區(qū)域進行求和， 可以采用以下方法：選中 Excel XP "插入函數”對話框中的函數， “確定”后打開“函數參 數”對話框。切換至第一個工作表，鼠標單擊“ numbed”框后選中需要求和的區(qū)域。如果 同一工作表中的其他區(qū)域需要參與計算，可以單擊“ number2”框，再次選中工作表中要計 算的其他區(qū)域。上述操作完成后切換至第二個工作表， 重復上述操作即可完成輸入。“確定” 后公式所在單元格將顯示計算結果。 應用實例二：SUM函數中的加減混合運算 財務統(tǒng)計需要進行加減混合運算， 例如扣除現金流量表中的若干支出項目。 按照規(guī)定，工作 表中的這些項目沒有輸入負號。這時可以

3、構造“ =SUM (B2:B6, C2:C9, -D2, -E2)”這樣 的公式。其中 B2:B6, C2:C9引用是收入，而 D2、E2為支出。由于 Excel不允許在單元格 引用前面加負號，所以應在表示支出的單元格前加負號， 這樣即可計算出正確結果。即使支 出數據所在的單元格連續(xù)，也必須用逗號將它們逐個隔開，寫成“ =SUM (B2:B6, C2:C9, -D2, -D3, D4)”這樣的形式。 應用實例三：及格人數統(tǒng)計 假如B1:B50區(qū)域存放學生性別，C1:C50單元格存放某班學生的考試成績， 要想統(tǒng)計考試成 由于它是一個數組公式，輸入結束后必須按住 Ctrl+Shift鍵回車

4、。公式兩邊會自動添加上大 括號，在編輯欄顯示為 “ {=SUM (IF (B1:B50="女"，IF (C1:C50>=60, 1 , 0))) }“，這 是使用數組公式必不可少的步驟。 語法：AVERAGE (numbed, number2,...)。 2.平均值函數AVERAGE 績及格的女生人數。 可以使用公式“=SUM(IF (B1:B50= 〃女〃，IF (C1:C50>=60 , 1, 0)))”, 參數：numberl > number2...是需要計算平均值的 1?30個參數。 注意：參數可以是數字、包含數字的名稱、數組或引用。數組或單元格引用中的文字、邏輯 值或

5、空白單元格將被忽略，但單元格中的零則參與計算。如果需要將參數中的零排除在外， 則要使用特殊設計的公式，下面的介紹。 應用實例一：跨表計算平均值 標簽名為“一班”、“二班”和“三班”的工作表存放各班學生的成績，則它們的總平均分計 算公式為“ =AVERAGE (一班！C1:C36,三班！C1:C32 ,三班！C1:C45)”。式中的引用輸入方 法與SUM跨表求和時相同。 應用實例二：忽略零的平均值計算公式 假設A1:A200隨機存放包括零在內的 48個數值，在 AVERAGE參數中去掉零引用很麻煩， 這種情況可以使用公式“ =AVERAGE (IF (A1:A200<>0 , A1:

6、A200 ,"") ”。公式輸入結 束后按住Ctrl+Shift回車，即可對 A1:A200中的數值(不包括零)計算平均值。 3 .邏輯函數IF 語法：IF (logical_test, value_if_true , value_if_false )。 參數：logical_test 是結果為 true(真)或 false (假)的數值或表達式；value_if_true 是 logical_test 為true時函數的返回值，如果 logical_test為ture并且省略了 value_if_true ,則返回true。而 且value_if_true可以是一個表達式； value

7、_if_false是logical_test為false時函數的返回值。 如果 logical_test 為 false 并且省略 value_if_false ,則返回 false。Value_if_false 也可以是一個 表達式。 應用實例一：個人收入調節(jié)稅計算 假設個人收入調節(jié)稅的稽征辦法是： 工資在1000元以下的免征調節(jié)稅， 工資1000元以上至 1500元的超過部分按 5%的稅率征收，1500元以上至2000元的超過部分按 8%的稅率征收， 高于2000元的超過部分按 30%的稅率征收。 假如工作表的 B列存放職工姓名， C列存放工資，選中 D列的空白單元格輸入公式“

8、=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工資應繳納的收入調節(jié) 稅。 應用實例二：消除無意義的零 公式中的IF語句是逐次計算的，如果第一個邏輯判斷 C2<=1000成立，則公式所在單元格 被填入空格；如果第一個邏輯判斷式不成立，則計算第二個 IF語句；直至計算結束。如果 稅率征收標準發(fā)生了變化，只須改變邏輯和計算式中的值，如 1000、1500和20

9、00等即可。 用SUM函數計算工資總額等問題時，若引用的全部參數均為空白單元格，公式仍然會計算 出一個“ 0”。這樣打印出來的報表不僅很不美觀。為此可將計算公式設計為“ =IF (SUM (A1:B1 , D2:E8) <>0, SUM (A1:B1 , D2:E8), 〃 〃)"，即是當 SUM (A1:B1 , D2:E8) 計算結果不等于零時，公式所在單元格顯示 SUM (A1:B1 , D2:E8)的計算結果，否則顯示 一個空格。 上面這個問題在財務計算中依然存在， 如公式“=A1-A6-A9” 有可能為零或顯

10、示負數。 為了避免打印零或負數， 可以設計公式“=IF (A2-A6-A9=0 ,"", IF (A2-A6-A9<0 , RMB (A2-A6-A9 ), A2-A6-A9 ))”。當 A2-A6-A9=0 時在單元格中填寫 零，否則進行下一個邏輯判斷。如果 A2-A6-A9<0則在結果前添加一個“”符號，否則進 行正常運算直至結束。 應用實例三：多條件求和 假設C1:C460區(qū)域內存放著職工的職稱， D1:D460區(qū)域存放著職工的性別。如果要統(tǒng)計具 有高級職稱的男性職工總數， 可以使用公式“=SUM (IF (C1:C460=〃高級〃，IF (D1:D460= 〃男〃，1,

11、0)))"。這是一個數組公式，輸入結束后按住 Ctrl+Shift回車即可計算出結果。 4 .快捷方式或鏈接創(chuàng)建函數 HYPERLINK 語法：HYPERLINK (link_location , friendly_name)。 參數：link_location是文件的路徑和文件名，它可以指向文檔中的某個具體位置。如 Excel 工作表中的特定單元格或區(qū)域，或某個 Word文檔中的書簽，也可以指向硬盤中的文件或是 Internet或Intranet的URL。Friendly_name為單元格中顯示的鏈接文字或數字，它用藍色顯 示并帶有下劃線。如果省略了 friendly_name ,

12、單元格就將link_location顯示為鏈接。 應用實例：管理文檔或網站地址 如果你擁有大量文檔或收集了許多網站地址， 管理起來一定有困難。如果能夠將其中的文檔 名、URL等與文檔等對象鏈接起來，不僅管理方便，還可以直接打開文檔或訪問站點。具 體操作方法是：首先根據文檔類型建立管理工作表， 其中的文件名或網站名必須使用以下方 法輸入：選中一個空白單元格，單擊 Excel XP工具欄中的“粘貼函數”按鈕，打開“粘貼 框中輸入文件的完整路徑和名稱 (包括擴展名)，如"C:\my documents\ IT網站集錦.doc”, 此后你就可以從工作表中打開文檔，方法是：打開工作表，在其中找

13、到需要打開的文件。 箭 頭光標指向文件名會變成手形，若停留片刻則會顯示該文件的完整路徑和名稱， 單擊則會調 用關聯程序將文件打開。 然后在"friendly_name ”框中輸入文件名(如“ 按鈕，所選單元格即出現帶下劃線的紫色文件名“ IT網站集錦”)。確認輸入無誤單擊“確定” IT網站集錦”。 函數”對話框，在“函數分類”框下選中“查找與引用”，然后在“函數名”框內找到HYPERLINK 函數。單擊對話框中的“確定”按鈕，彈出“ HYPERLINK ”函數向導。在“ link_location ” 如果你收集了許多網站的 URL,還可以用此法建立一個大型“收藏夾” 既可以

14、用 強大功能進行管理，又可以從工作表中直接訪問 Web站點。 5 .計數函數COUNT 語法：COUNT (valuel, value2,...)。 參數：valuel, value2...是包含或引用各類數據的 1?30個參數。 注意：COUNT函數計數時數字、日期或文本表示的數字會參與計數，錯誤值或其他無法轉 換成數字的文字被忽略。 如果參數是一個數組或引用， 那么只有數組或引用中的數字參與計 數；其中的空白單元格、邏輯值、文字或錯誤值均被忽略。 應用實例：及格率統(tǒng)計 假如C1:G42存放著42名學生的考試成績，在一個空白單元格內輸入公式" =COUNTIF (C1:C42

15、, 〃 >=60〃)/COUNTA (C1:C42)”回車，即可計算出該列成績的及格率(即分 數為60及以上的人數占總人數的百分比)。 6 .最大值函數MAX、最小值函數MIN 語法：MAX (numbed, number2MIN (numbed, number2 ,...)。 參數：numbed, number2...是需要找出最大值(最小值)的 1至30個數值、數組或引用。 注意：函數中的參數可以是數字、空白單元格、邏輯值或數字的文本形式，如果參數是不能 轉換為數字的內容將導致錯誤。 如果參數為數組或引用， 則只有數組或引用中的數字參與計 算，空白單元格、邏輯值或文本則被忽略

16、。 應用實例：查詢最高分(最低分) 假如C1: G42存放著42名學生的考試成績，則選中一個空白單元格，在編輯欄輸入公式 “=MAX (C1:C42)”，回車后即可計算出其中的最高分是多少。 7.條件求和函數 SUMIF 語法： SUMIF (range, criteria, sum_range)。 參數：range是用于條件判斷的單元格區(qū)域， criteria是由數字、邏輯表達式等組成的判定條 件，sum_range為需要求和的單元格、區(qū)域或引用。 應用實例：及格平均分統(tǒng)計 如果將上述公式中的函數名改為 MIN ,其他不變，就可以計算出 C1: G42區(qū)域中的最低分。 假

17、如A1:A36單元格存放某班學生的考試成績，若要計算及格學生的平均分，可以使用公式 “=SUMIF (A1:A36 ,〃 >=60” , A1:A36 )/COUNTIF (A1:A36 ,〃 >=60")。公式中的 “=SUMIF (A1:A36 , " >=60〃，A1:A36 )”計算及格學生的總分，式中的“ A1:A36 ”為提供邏輯判 斷依據的單元格引用，“>=60”為判斷條件，不符合條件的數據不參與求和， A1:A36則是 邏輯判斷和求和的對象。公式中的 COUNTIF (A1:A36, 〃 >=60〃)用來統(tǒng)計及格學生的人 8.貸款償還計算函數 PMT 語法：PMT

18、(rate, nper, pv, fv, type) 參數：如今貸款買房子或車子的人越來越多， 計算某一貸款的月償還金額是考慮貸款的重要 依據，Excel XP提供的PMT函數是完成這一任務的好工具。 語法中的rate是貸款利率；nper 為貸款償還期限；pv是貸款本金；fv為最后一次付款后剩余的貸款金額，如果省略 fv,則 認為它的值為零；type為0或1,用來指定付款時間是在月初還是月末。如果省略 type,則 假設其值為零。 應用實例：購房還款金額 假如你為購房貸款十萬元，如果年利率為 7%,每月末還款。采用十年還清方式時，月還款 額計算公式為“ =PMT (7%/12, 1

19、20,-100000)”。其結果為 -1, 161.08,就是你每月須償 還貸款1161.08元。 9.樣本的標準偏差函數 STDEV 語法：STDEV (numbed, number2,...)。 參數：numbed, number2,...為對應于總體樣本的 1到30個參數，它們可以是數值、引 用或數組。 注意：STDEV函數的參數是總體中的樣本，并忽略參數中的邏輯值( true或false)和文本。 如果需要用全部數據計算標準偏差，則應使用 STDEVP函數。如果參數中的邏輯值和文本 不能忽略，請使用 STDEVA函數。 假設某班共有36名學生參加考試，隨機抽取的五個分數為

20、 A1=78、A2=45、A3=90、A4=12 和A5=85。如果要估算本次考試成績相對平均分的離散程度， 即學生的考試成績偏離平均分 的多少，可以使用公式“ =STDEV (A1:A5)”。其計算結果為33.00757489,標準偏差的數值 越大成績越分散。 應用實例二：質量波動估計 應用實例一：成績離散度估計 質量控制等場合也能用到 出它們的抗拉強度。根據 果計算的標準偏差比較小， 量波動較大，抗拉強度不夠 10.排序函數RANK 語法：RANK （number, STDEV函數，如從一批鋼絲繩中隨機抽出若干進行試驗，分別測P宣 STDEV函數的計算結果即可判斷鋼

21、絲繩的抗拉強度是否分散，禺 說明抗拉強度的一致性好， 質量比較穩(wěn)定。反之說明鋼絲繩的質 ref, order）。 參數：number是需要計算其排位的一個數字； ref是包含一組數字的數組或引用（其中的非 數值型參數將被忽略）；order是用來說明排序方式的數字（如果 order為零或省略，則以降 序方式給出結果，反之按升序方式） 應用實例：產值排序 假如E2、E3、E4單元格存放一季度的總產值。 計算各車間產值排名的方法是：在 F2單元格內輸入公式" =RANK （E2, $E$2:$E$4）”，敲 回車即可計算出鑄造車間的產值排名是 2。再將F2中的公式復制到剪貼板，選中

22、F3、F4單 元格按Ctrl+V ,就能計算出其余兩個車間的產值排名 3和1。如果B1單元格中輸入的公式 為“=RANK （E2, $E$2:$E$4, 1）”，則計算出的序數按升序方式排列，即 2、1和3。 需要注意的是：相同數值用 RANK函數計算得到的序數（名次）相同，但會導致后續(xù)數字 的序數空缺。假如上例中F2單元格存放的數值與 F3相同，則按本法計算出的排名分別是 3、 3和1 （降序時）。即176.7出現兩次時，鑄造和維修車間的產值排名均為 3,后續(xù)金工車間 的排名就是1 （沒有2）。 11 .四舍五入函數 語法：ROUND （number, num_digits）。

23、 參數：number是需要四舍五入的數字； num_digits為指定的位數，number將按此位數進行 四舍五入。 應用實例：消除計算誤差 假設Excel工作表中有 D2=356.68、E2=128.12,需要將 D2與E2之和乘以0.1,將計算結果 四舍五入取整數，再將這個結果乘以 1.36 （取兩位小數）得到最終結果。 一般用戶的做法是選中某個單元格（如 F2）,使用“單元格”命令將它的小數位數設為零， 然后在其中輸入公式" F2= （D1+E1） *0.1"。再將G2單元格的小數位數設成兩位，最后把 注意：如果num_digits大于0,則四舍五入到指定的小數位；如果 n

24、um_digits等于0,則 四舍五入到最接近的整數； 如果num_digits小于0,則在小數點左側按指定位數四舍五入。 F2*1.36的結果存入其中就可以了。從表面上看，上述方法沒有什么問題。因為( D1+I *0.1在F2單元格顯示48 (注意：是顯示 48),如果F2單元格的小數位數為零， (D1+ER *0.1經四舍五入后的結果就是 48。接下去卻出了問題，因為 F2*1.36的計算結果是65.90, 根本不是48*1.36的正確結果65.28,其中65.90是(D2+E2 )*0.1未經四舍五入直接乘以 1.36 的結果。 以上計算結果說明：“單元格格式”、“數字”選

25、項卡設置的“小數位數”，只能將單元格數值 的顯示結果進行四舍五入， 并不能對所存放的數值進行四舍五入。 換句話說，單元格數值的 顯示結果與實際存放結果并不完全一致， 如果不注意這個問題，計算工資等敏感數據就會出 現錯誤。例如在上例中，F2單元格內的數值雖然顯示為 48,但實際存放的卻是 48.45,自然 得出了 48.45*1.36=65.90的結果(按計算要求應為 65.28)。 要解決這個問題并不難, 你只須在G2單元格內輸入公式 “二(ROUND ((D2+E2) *0.1 , 0)) *1.36"，就可以按要求計算出正確結果 65.28。式中的ROUND函數按指定位數對 “(

26、D2+E2) *0.1”進行四舍五入，函數中的參數 0將“(D2+E2) *0.1"四舍五入到最接近的整數。 12 .條件計數函數 COUNTIF 語法：COUNTIF (range, criteria) o 參數：range為需要統(tǒng)計的符合條件的單元格區(qū)域； criteria為參與計算的單元格條件，其形 式可以為數字、表達式或文本(如36、〃 >160〃和〃男〃等)。條件中的數字可以直接寫入， 表達式和文本必須加引號。 應用實例：男女職工人數統(tǒng)計 假設A1:A58區(qū)域內存放著員工的性別，則公式“ =COUNTIF (A1:A58 ,"女")”統(tǒng)計其 中的女職工數量，"=COUN

27、TIF (A1:A58 , 〃男〃)”統(tǒng)計其中的男職工數量。 COUNTIF函數還可以統(tǒng)計優(yōu)秀或及格成績的數量，假如 C1: G42存放著42名學生的考試 成績，則公式“ =COUNTIF (C2: G2, 〃 >=85〃)”可以計算出其中高于等于 85分的成績 數目。如將公式改為“ =COUNTIF (C2: G2, 〃 >=60 〃)”，則可以計算出及格分數的個數。 13 .百分排位預測函數 PERCENTILE 參數：array為定義相對位置的數值數組或數值區(qū)域, k為數組中需要得到其百分排位的值。 注意：如果array所在單元格為空白或數據個數超過 8191,則返回#NUM

28、 !錯誤。如果k<0 或k>1 ,則返回#NUM !錯誤。如果k不是1/ (n-1)的倍數，該函數使用插值法確定其百分 排位。 應用實例：利潤排行預測 語法：PERCENTILE (array, k)。 假設C1:C60區(qū)域存放著幾十個公司的利潤總額。如果你想知道某公司的利潤達到多少，才 能進入排名前10%的行列，可以使用公式“ =PERCENTILE (C1:C60, 0.9)”。如果計算結果 為9867萬元，說明利潤排名要想進入前 10%,則該公司的利潤至少應當達到 9867萬元。 14.數值探測函數ISNUMBER 語法：ISNUMBER (value)。 參數：如果va

29、lue為數值時函數返回ture (真),否則返回false (假)。 應用實例一：無錯誤除法公式 如果工作表為 C1設計了公式“ =A1/B1 "，一旦B1單元格沒 有輸入除數，就會在 C1中顯示錯誤信息“ # DIV/0!”。這不僅看起來很不美觀，一旦作為 報表打印還可能引起誤會。為避免上面提到的問題出現，可將C1單元格中的公式設計成“=IF (ISNUMBER (B1), A1/B1 ,""式中的ISNUMBER 函數對B1單元格進行探測，當 B1被填入數值時返回true (真)，反之返回false (假)。為真時IF函數執(zhí)行A1/B1的運算， 為假時在C1單元格中填入空格。 應用

30、實例二：消除無意義的零 使用SUM函數計算工資總額時，若引用的單元格區(qū)域沒有數據， Excel仍然會計算出一個 結果“ 0”。這樣打印出來的報表不符合財務規(guī)定，為此可將公式設計成“ =IF (ISNUMBER (A1:B1 ) , SUM (A1:B1 ),"") 式中ISNUMBER函數測試SUM函數所引用的單元格區(qū)域是否全部為空，當不為空時返回 true (真)，反之返回fales (假)。為真時IF函數執(zhí)行SUM (A1:B1 ),為假時在存放計算結 果的F1單元格中填入空格，這樣即可避免在 F1單元格中出現“ 0”。 15.零存整取收益函數 PV 語法： PV (rate

31、, nper, pmt, fv, type)。 參數：rate為存款利率；nper為總的存款時間，對于三年期零存整取存款來說共有 3*12=36 個月；pmt為每月存款金額，如果忽略pmt則公式必須包含參數 fv; fv為最后一次存款后希 望得到的現金總額，如果省略了 fv則公式中必須包含 pmt參數；type為數字0或1,它指 應用實例：零存整取收益函數 PV 假如你每月初向銀行存入現金 500元，如果年利2.15% (按月計息，即月息 2.15%/12)。如 果你想知道5年后的存款總額是多少，可以使用公式“ =FV (2.15%/12, 60,-500, 0, 1)” 計算，其結果為

32、 31, 698.67。 式中的2.15%/12為月息；60為總的付款時間，在按月儲蓄的情況下為儲蓄月份的總和； -500 定存款時間是月初還是月末。 percent為計算時需要除 4個，即頭部除去 2個尾 函數TRIMMEAN將在數據 為每月支出的儲蓄金額(-表示支出)；0表示儲蓄開始時賬戶上的金額，如果賬戶上沒有 分錢，則為0否則應該輸入基礎金額；1表示存款時間是月初還是月末， 1為月初0或忽 為月末。 16.內部平均值函數 TRIMMEAN 語法：TRIMMEAN (array, percent)。 參數：array為需要去掉若干數據然后求平均值的數組或數據區(qū)域； 去的

33、數據的比例，如果 percent = 0.2,說明在20個數據中除去 部除去2個。如果percent=0.1,則30個數據點的10%等于3, 首尾各去掉一個數據。 應用實例：評比打分統(tǒng)計 歌唱比賽采用打分的方法進行評價， 為了防止個別人的極端行為，一般計算平均分數要去掉 若干最高分和最低分。假如 B1:B10區(qū)域存放某位歌手的比賽得分，則去掉一個最高分和一 個最低分后的平均分計算公式為“ =TRIMMEAN ( B1:B10 , 0.2)”。公式中的0.2表示10個 數據中去掉2個(10X0.2),即一個最高分和一個最低分。 17 .日期年份函數 YEAR 語法：YEAR (se

34、rial_number)。 參數：serial_number為待計算年份的日期。 應用實例：“虛工齡”計算 所謂“虛工齡”就是從參加工作算起，每過一年就增加一年工齡，利用 YEAR函數計算工 齡的公式是“ =YEAR (A1 ) -YEAR (B1)”。公式中的A1和B1分別存放工齡的起止日期， YEAR (A1)和YEAR (B1)分別計算出兩個日期對應的年份，相減后得出虛工齡。 18 .起止天數函數 DAYS360 參數：start_date和end_date是用于計算期間天數的起止日期， 可以使用帶引號的文本串 (如 “1998/01/30")、系列數和嵌套函數的結果。如果

35、 start_date在end_date之后，貝U DAYS360 將返回一個負數。 語法：TRUNC (number, num_digits)。 19.取整函數 TRUNC 0 語法：DAYS360 (start_date, end_date, method)。 參數：number是需要截去小數部分的數字， num_digits則指定保留到幾位小數。 應用實例：“實工齡”計算 實際工作滿一年算一年的工齡稱為“實工齡” ，如1998年6月1日至2001年12月31日的 工齡為 3 年。計算“實工齡”的公式是 “=TRUNC ((DAYS360 (〃 1998/6/1 〃，〃

36、2001/12/31 “))/360, 0)”,公式中的 DAYS360 (" 1998/6/1 " , 〃 2001/12/31 ")計算兩個日期相差 的天數，除以360后算出日期相差的年份(小數)。最后TRUNC函數將(DAYS360 (A1 , B1 ) /360的計算結果截去小數部分， 從而得出“實工齡”。如果計算結果需要保留一位小數， 只須將公式修改為 “ =TRUNC ((DAYS360 (" 1998/6/1 " , " 2001/12/31 " )) /360, 1) 如果你要計算參加工作到系統(tǒng)當前時間的實工齡， ("1998/6/1 ” , NOW ())) /360,

37、0)”。其中 可以將公式修改為 “ =TRUNC (( DAYS360 NOW ()函數返回當前的系統(tǒng)日期和時間。 即可。 20.字符提取函數 MID 語法： MID (text, start_num, num_chars) 或 MIDB (text, start_num, num_bytes)。 參數：text是含有要待提取字符的文本， start_num是要提取的第一個字符的位置 (其中第一 個字符的start_num為1以此類推)，num_chars指定MID從文本中提取的字符個數， Num_bytes指定MIDB從文本中提取的字符個數(按字節(jié)計算) 。 應用實例

38、：姓氏提取和統(tǒng)計 假如工作表C列存放著職工的姓名，如果想知道某一姓氏的職工人數，可以采用以下方法。 首先使用公式“ =MID (C1, 1, 1)”或“=MIDB (C1, 1, 2),從C1中提取出職工的姓氏， 采用拖動或復制的方法即可提取其他單元格中的職工姓氏。然后使用公式“ =COUNTIF (D1:D58, 〃張〃)”統(tǒng)計出姓張的職工數量，隨后改變公式中的姓氏就可以統(tǒng)計出其他姓 氏職工的數量。 21、邏輯運算符 所謂邏輯運算符也就是與、或、是、非、真、假等等條件判斷符號。這里介紹六條。 (1) AND 語法：AND(logical1 , logical2 , ???)。

39、 參數：Logical1 ,logical2 ,…為待檢驗的1?30個邏輯表達式，它們的結論或為 TRUE(真) 用途：所有參數的邏輯值為真時返回 TRUE(真);只要有一個參數的邏輯值為假，則返回 FALSE(假)。  或為FALSE(假)。參數必須是邏輯值或者包含邏輯值的數組或引用, 文字或空白單元格，則忽略它的值。如果指定的單元格區(qū)域內包括非邏輯值, 錯誤值#value!。 AND將熨 實例：如果 A1=2、A=6,那么公式“ =AND(A1A2) ”返回FALSE。如果B4=104,那么 公式 “ =IF(AND(1 (2) FALSE 用途：返回邏輯值 FALSE。

40、 語法：FALSE() 參數：該函數不需要參數 實例：如果在 A1單元格內輸入公式" =FALSE()”，回車后即可返回 FALSE。若在單元 格或公式中^入文字 FALSE, Excel會自動將它解釋成邏輯值 FALSE。 (3) IF 用途：執(zhí)行邏輯判斷，它可以根據邏輯表達式的真假，返回不同的結果，從而執(zhí)行數值 或公式的條件檢測任務。 語法：IF(logical_test , value_if_true , value_if_false)。 參數：Logical_test計算結果為 TRUE或FALSE的任何數值或表達式 ；value_if_true是 Logical_te

41、st 為 TRUE 時函數的返回值，如果 logical_test 為 TRUE 并且省略了 value_if_true , 則返回 TRUE。而且 value_if_true 可以是一個表達式；value_if_false 是 Logical_test 為 FALSE 時函數的返回值。如果 logical_test為FALSE并且省略 value_if_false ,則返回 FALSE。 value_if_false也可以是一個表達式。 喊■國: 語法：NOT(logical) (4) NOT 用途：求出一個邏輯值或邏輯表達式的相反值。 如果您要確保一個邏輯值等于其相反值, 就應

42、該使用NOT函數。 實例:公式 “=IF(C2>=85 , "A" , IF(C2>=70 , "B" , IF(C2>=60 , "C" , IF(C2<60 , "D")))) ”, 其中第二個IF語句同時也是第一個IF語句的參數。同樣，第三個 IF語句是第二個IF語句 的參數，以此類推。例如，若第一個邏輯判斷表達式 C2>=85成立，則D2單元格被賦值“A”; 如果第一個邏輯判斷表達式 C2>=85不成立，則計算第二個IF語句“IF(C2>=70" ；以此類推 直至計算結束，該函數廣泛用于需要進行邏輯判斷的場合。 參數：Logical是一個可以得出TRUE或FALSE結論的邏輯值或

43、邏輯表達式。本如巢客 值或表達式的結果為 FALSE,則NOT函數返回TRUE;如果邏輯值或表達式的結果為 TrUE 那么NOT函數返回的結果為 FALSE。 實例：如果 A1=6、A2=8 ,那么公式“ =NOT(A1 (5) OR 用途：所有參數中的任意一個邏輯值為真時即返回 TRUE(真)。 語法：OR(logical1 , logical2，…) 參數：Logical1 , logical2 ,…是需要進行檢驗的 1至30個邏輯表達式，其結論分別為 TRUE或FALSE。如果數組或引用的參數包含文本、數字或空白單元格，它們將被忽略。 如果指定的區(qū)域中不包含邏輯值， OR函

44、數將返回錯誤#value!。 實例：如果 A1=6、A2=8 ,則公式 “ =OR(A1+A2>A2 , A1=A2) ” 返回 TRUE;而公式 “=OR(A1>A2 , A1=A2) ” 返回 FALSE。 (6) TRUE 用途：返回邏輯值 TRUE。 語法：TRUE() 參數：該函數不需要參數 實例：如果在 A1單元格內輸入公式" 二TRUE()”，回車后即可返回 TRUE。若在單元格 或公式中輸入文字 TRUE, Excel會自動將它解釋成邏輯值 TRUE。函數TRUE主要用于與 其它電子表格程序兼容。 22、文本與數據函數 (1) ASC 用途：將字符串中的全

45、角(雙字節(jié))英文字母更改為半角(單字節(jié))字符。 喊■國: 參數：Text為文本或包含文本的單元格引用。如果文本中不包含任何全角英文字母， 則 文本不會被更改。 實例：如果 A1=excel,則公式“=ASC(A1)“返回excel。 語法：ASC(text)  (2) CHAR 用途：返回對應于數字代碼的字符， 該函數可將其他類型的電腦文件中的代碼轉換為字 符(操作環(huán)境為MacintoshMacintosh字符集和WindowsANSI字符集)。 語法：CHAR(number)。 參數：Number是用于轉換的字符代碼，介于 1?255之間(使用當前計算機字符集中的

46、字符)。 實例：公式 “ =CHAR(56)” 返回 8, =CHAR(36)返回 $。 (3) CLEAN 用途：刪除文本中不能打印的字符。 對從其他應用程序中輸入的字符串使用 CLEAN函 數，將刪除其中含有的當前操作系統(tǒng)無法打印的字符。 語法：CLEAN(text)。 參數：Text為要從中刪除不能打印字符的任何字符串。 實例：由于CHAR(7)返回一個不能打印的字符，因此公式 “=CLEAN(CHAR⑺&"text"&CHAR⑺) ”返回 text。 (4) CODE 用途：返回文字串中第一個字符的數字代碼 (對應于計算機當前使用的字符集 )。 語法：CODE(te

47、xt) 參數：Text為需要得到其第一個字符代碼的文本。 實例：因為 CHAR(65)返回A,所以公式“ =CODE("Alphabet") ”返回65。 將若干文字串合并到一個文字串中，其功能與 "&"運算符相同。 用途: 語法: 文字串、數字或對單個單元格的引用。 Text1 , text2,…為1到30個將要合并成單個文本的文本項，這些文本項可以是 CONCATENATE(text1 , text2,…) (5) CONCATENATE 實例：如果 A1=98、A2=千米，則公式 “ =CONCATENATE(A1 , A2) ” 返回?：9 與公式“ =

48、A1&A2 ”等價。 ? (6) DOLLAR 或 RMB 用途：按照貨幣格式將小數四舍五入到指定的位數并轉換成文字。 語法：DOLLAR(number , decimals)或 RMB(number , decimals)。 參數：Number是數字、包含數字的單元格引用，或計算結果為數字的公式 ；Decimals是 十進制的小數，如果 Decimals為負數，則參數number從小數點往左按相應位數取整。如果 省略Decimals,則假設其值為 2。 實例:公式 “ =RMB(1586.567 , 2)” 返回 “ 1586.57", =RMB(99.888)返回 “ 99.

49、89”。 ⑺ EXACT 用途：測試兩個字符串是否完全相同。如果它們完全相同，則返回 TRUE;否則返回 FALSE。EXACT函數能區(qū)分大小寫，但忽略格式上的差異。 語法：EXACT(text1 , text2)。 參數：Text1是待比較的第一個字符串， Text2是待比較的第二個字符串。 實例：如果 A1 =物理、A2=化學A3=物理，貝U公式“ =EXACT(A1 , A2) ”返回FALSE , =EXACT(A1 , A3)返回 FALSE, =EXACT("Word" , "word")返回 TRUE。 (8) FIND 用途：FIND用于查找其他文本串 (wit

50、hin_text)內的文本串(find_text),并從 within_text 的首字符開始返回find_text的起始位置編號。此函數適用于雙字節(jié)字符，它區(qū)分大小寫但 不允許使用通配符。 語法：FIND(find_text , within_text , start_num), 參數：Find_text是待查找的目標文本；Within_text是包含待查找文本的源文本 ;Start_num 指定從其開始進行查找的字符，即 within_text中編號為1的字符。如果忽略 start_num ,則 喊■國: 假設其為1。 實例：如果A1 =軟件報，則公式“ =FIND("軟

51、件"，A1 ,1)”返回1。 (9) FINDB 用途：FINDB用于查找其他文本串(within_text)內的文本串(find_text),并根據每個字符  使用的字節(jié)數從 within_text的首字符開始返回 的區(qū)別在于：前者是以字符數為單位返回起始位置編 置編號。 find text的起始位置編號。 FIND；.與 后者是以字節(jié)數為單位返回起始位 語法：FINDB(find_text , within_text , start_num), 參數：Find_text是待查找的目標文本；Within_text是包含待查找文本的源文本 ；Start_num 指定從其開始進

52、行查找的字符，即 within_text中編號為1的字符。如果忽略 start_num ,則 假設其為1。 注意：此函數適用于雙字節(jié)字符，它能區(qū)分大小寫但不允許使用通配符。其它事項與 FIND函數相同。 實例：如果 A1=電腦愛好者，則公式“ =FINDB(愛好者"，A1, 1)”返回5。因為每個 字符均按字節(jié)進行計算， 而一個漢字為2個字節(jié)，所以第三個漢字“愛”從第五個字節(jié)開始。 (10) FIXED 用途：按指定的小數位數四舍五入一個數，以小數格式設置后以文字串形式返回結果。 語法： FIXED(number , decimals, no_commas)。 參數：Number是

53、要進行四舍五入并轉換成文字串的數 ；Decimals為一數值，用以指定小 數點右邊的小數位數；No_commas為一邏輯值。如果是TRUE,則函數FIXED返回的文字不 含逗號。如果no_commas是FALSE或省略，則返回的文字中包含逗號。 實例：如果 A1=2001.16845 ,則公式 “ =FIXED(A2 , 2 , TRUE)” 返回 2001.17, =FIXED(6834.567 , -1)返回 6830。 (11) JIS 用途：將字符串中的半角(單字節(jié))英文字母更改為全角(雙字節(jié))字符。 參數：Text為文本或對包含文本的單元格 (或引用)。如果文本中不包含任何半

54、角英文字 母，則文本不會更改。 喊■國: 實例：如果 A1=excel ,則公式“ =JIS(a1)”返回EXCEL 。 用途：根據指定的字符數返回文本串中的第一個或前幾個字符。 此函數用于雙字節(jié)字符。 (12) LEFT 或 LEFTB 語法：JIS(text) 語法: LEFT(text , num_chars)或 LEFTB(text , num_bytes)。 Text是包含要提取字符的文本串 ；Num_chars指定函數要提取的字符數，它必須 大于或等于0。Num_bytes按字節(jié)數指定由LEFTB提取的字符數。 實例：如果A1=電腦愛好者，則LEFT(A

55、1 , 2)返回“電腦”，LEFTB(A1 , 2)返回“電”。 (13) LEN 或 LENB 用途：LEN返回文本串的字符數。LENB返回文本串中所有字符的字節(jié)數。 語法：LEN(text)或 LENB(text)。 參數：Text待要查找其長度的文本。 注意：此函數用于雙字節(jié)字符，且空格也將作為字符進行統(tǒng)計。 實例：如果 A1=電腦愛好者，則公式“ =LEN(A1) ”返回5, =LENB(A1)返回10。 (14) LOWER 用途：將一個文字串中的所有大寫字母轉換為小寫字母。 語法：LOWER(text)。 語法：Text是包含待轉換字母的文字串。 注意：L

56、OWER函數不改變文字串中非字母的字符。 LOWER與PROPER和UPPER函 數非常相似。 實例：如果 A1=Excel ,則公式 “ =LOWER(A1) ” 返回 excel。 (15) MID 或 MIDB 用途：MID返回文本串中從指定位置開始的特定數目的字符， 該數目由用戶指定。MIDB 返回文本串中從指定位置開始的特定數目的字符， 該數目由用戶指定。MIDB函數可以用于 雙字節(jié)字符。 參數：Text是包含要提取字符的文本串。 Start_num是文本中要提取的第一個字符的位 置，文本中第一個字符的 start_num為1,以此類推；Num_chars指定希望MI

57、D從文本中返回 字符的個數；Num_bytes指定希望MIDB從文本中按字節(jié)返回字符的個數。 語法：MID(text , start_num, num_chars)或 MIDB(text , start_num, num_bytes)。  實例：如果a1=電子計算機，則公式“ =MID(A1 2)返回“子”。 (16) PHONETIC 用途：提取文本串中的拼音 (furigana)字符。 語法：PHONETIC(reference)。 3, 2) ,=MIDB( 果 參數：Reference為文本串或對單個單元格或包含文本串的單元格區(qū)域的引用。如 reference

58、為單元格區(qū)域，則返回區(qū)域左上角單元格中的 furigana文本串。如果reference為不 相鄰單元格的區(qū)域，將返回 #N/A錯誤值。 注意：該函數在中文 Excel中無效。 (17) PROPER 用途：將文字串的首字母及任何非字母字符之后的首字母轉換成大寫。 將其余的字母轉 換成小寫。 語法：PROPER(text) 參數：Text是需要進行轉換的字符串， 包括雙引號中的文字串、 返回文本值的公式或對 含有文本的單元格引用等。 實例：如果 A1 =學習excel,則公式“ =PROPER(A1) ”返回“學習 Excel”。 (18) REPLACE 或 REPLAC

59、EB 用途：REPLACE使用其他文本串并根據所指定的字符數替換另一文本串中的部分文 本。REPLACEB的用途與REPLACE相同，它是根據所指定的字節(jié)數替換另一文本串中的 部分文本。 參數：Old_text是要替換其部分字符的文本 ；Start_num是要用new_text替換的old_text 喊■國: 注意：以上兩函數均適用于雙字節(jié)的漢字。 實例：如果A1=學習的革命、A2=電腦，則公式“=REPLACE(A1 , 3, 3, A2)”返回“學 中字符 的位置；Num_chars是希望 REPLACE 使 用new_text替 換old_text中字 符的個 數;Num_

60、bytes是希望 REPLACE使用new_text替換old_text的字節(jié)數 換old_text中字符的文本。 ;New_text是要用于替 語法：REPLACE(old_text , start_num, num_chars, new_text), REPLACEB(old_text , start_num, num_bytes, new_text)。 習電腦"，=REPLACEB(A1 , 2, 3, A2)返回“電腦的革命” (19) REPT 用途：按照給定的次數重復顯示文本?？梢酝ㄟ^ REPT函數對單元格進行重復填充。 語法：REPT(text, number_ti

61、mes)。 參數：Text是需要重復顯示的文本， Number_times是重復顯示的次數(正數)。 注意：REPT函數的結果不能多于 255個字符。 實例：公式“ =REPT("軟件報"，2)”返回“軟件報軟件報” (20) RIGHT 或 RIGHTB 用途：RIGHT根據所指定的字符數返回文本串中最后一個或多個字符。 RIGHTB根據 所指定的字節(jié)數返回文本串中最后一個或多個字符。 語法：RIGHT(text , num_chars), RIGHTB(text , num_bytes)。 參數：Text是包含要提取字符的文本串；Num_chars指定希望RIGHT提取的字

62、符數，它 必須大于或等于 0。如果num_chars大于文本長度，則 RIGHT返回所有文本。如果忽略 num_chars,則假定其為1。Num_bytes指定欲提取字符的字節(jié)數。 實例：如果 A1 =學習的革命，則公式" =RIGHT(A1 , 2)”返回“革命"，=RIGHTB(A1 , 2)返回“命”。 (21) SEARCH 或 SEARCHB 用途：返回從start_num開始首次找到特定字符或文本串的位置編號。 其中SEARCH以 字符數為單位，SEARCHB以字節(jié)數為單位。 參數：Find_text是要查找的文本，可以使用通配符，包括問號“ ？”和星號 其中 3, =

63、SEARCHB(" A1)”返回 實例：如果 A1 =學習的革命，則公式" =SEARCH("的", 的"，A1)返回5。 問號可匹配任意的單個字符，星號可匹配任意的連續(xù)字符。如果要查找實際的問號或星號， 應當在該字符前鍵入波浪線" ~"。Within_text是要在其中查找 find_text的文本。Start_num 是within_text中開始查找的字符的編號。如果忽略 start_num,則假定其為 1。 語法：SEARCH(find_text , within_text , start_num), SEARCHB(find_text , within_text , s

64、tart_num)。 (22) SUBSTITUTE 用途：在文字串中用new_text替彳t old_text。如果需要在一個文字串中替換指定的文本, 可以使用函數SUBSTITUTE;如果需要在某一文字串中替換指定位置處的任意文本，就應當 使用函數REPLACE 。 語法：SUBSTITUTE(text , old_text, new_text, instance_num)。 參數：Text是需要替換其中字符的文本， 或是含有文本的單元格引用；Old_text是需要替 換的舊文本；New_text用于替換old_text的文本；Instance_num為一數值，用來指定以 new

65、_text 替換第幾次出現的 old_text;如果指定了 instance_num,則只有滿足要求的 old_text被替換；否 則將用new_text替換Text中出現的所有 old_text。 A2=電腦，則公式 “ =SUBSTITUTE(A1 ,"的革命"，A2 , 實例：如果A1 =學習的革命、 1)”返回“學習電腦”。 (23) T 用途：將數值轉換成文本。 語法：T(value)。 參數：value是需要進行測試的數據。如果 value本身就是文本，或是對文本單元格的 引用，T函數將返回value;如果沒有弓I用文本，則返回 ""(空文本)。 實例：如果A1

66、中含有文本“電腦”，則公式“ =T(A1)”返回“電腦” (24) TEXT 用途：將數值轉換為按指定數字格式表示的文本。 語法：TEXT(value , format_text)。 參數：value是數值、計算結果是數值的公式、或對數值單元格的引用 ；format_text是所 喊■國: (25) TRIM 注意：使用“單元格格式”對話框的“數字”選項卡設置單元格格式，只會改變單元格 的格式而不會影響其中的數值。使用函數TEXT可以將數值轉換為帶格式的文本， 而其結果 將不再作為數字參與計算。 實例：如果 A1=2986.638,則公式“=TEXT(A5 , "#, ##0.00")” 返回 2, 986.64。 要選用的文本型數字格式，即“單元格格式”對話框“數字”選項卡的“分類”列表框中顯 示的格式，它不能包含星號“ *”。 用途：除了單詞之間的單個空格外, 獲得了帶有不規(guī)則空格的文本，可以使用 (28) WIDECHAR 清除文本中的所有的空格。 如果從其他應用程序 TRIM函數清除這些空格。 語法：TRIM(text)。 參數：