學校應用函數1

Excel 2000應用實例解析

2007-12-12 09:35:49|分類： 軟體應用 |標籤： |字型大小大中小訂閱

1.利用身份證號碼獲取公民出生日期 使用Excel進行人事管理，常常需要輸入身份證號碼、出生年月日等數據。由於身份證號碼內含有相關信息，可以使用Excel函數從身份證號碼中提取出生年月日，從而避免重複勞動以及可能的輸入錯誤。 現行的身份證號碼左起第7至12個字元表示出生年月日(存在Y2K問題)，我們可以使用MIDB函數從身份證號碼的特定位置，分別提取出生年、月、日，再用CONCATENATE函數將提取出來的文字合併起來，就能得到對應的出生年月日。 具體公式為：=CONCATENATE(″19″,MIDB(A4,7,2),″年″,MIDB(A4,9,2),″月″,MIDB(A4,11,2),″日″)式中「19」是針對現行身份證號碼中存在Y2K問題設置的，

Fe$d5F"fcHza+|$

它可以在提取的出生年份前加上19。三個MIDB函數的操作對象存放在A4單元格，分別從左起第7、9、11個字元開始提取2個字元，即得到出生年、月和日，最後由CONCATENATE函數將所有字元合併起來。 需要注意的是：CONCATENATE函數和MIDB函數的操作對象均為文本，所以存放身份證號碼的單元格必須事先設為文本格式，然後再輸入身份證號。 2.利用身份證號碼獲取公民性別 這個公式可以從工作表的身份證號碼中提取對應的性別。具體公式為：=IF(RIGHTB(a1,1)=″1″,″男″,IF(RIGHTB(a1,1)=″3″,″男″,IF(RIGHTB(a1,1)=″5″,″男″,IF(RIGHTB(a1,1)=″7″,″男″,IF(RIGHTB(a1,1)=″9″,″男″,″女″)))))，公式中C列存放文本格式的身份證號碼，函數RIGHTB提取字元串右端給定長度的字元。根據身份證號碼編號規則，該字元為奇數時為男，否則為女。經過IF函數五次判斷，凡RIGHTB(c1,1)值為「1」、「3」等奇數的均為男，否則為女。 需要注意的是：RIGHTB(c1,1)提取C1單元格存放的身份證號碼右起第一個字元，由於它是由文本格式的單位元組數字組成的，一個字元佔用一個位元組，故以1為單位計算要提取的字元數。如果是普通漢字或雙位元組數字，一個字元要佔用兩個位元組，應以2為單位計算要提取的字元數。 3.成績等第計算 學校採用等第評定考試成績，一般的標準為：考試分數高於或等於85分為A等；考試分數低於85分高於或等於70分為B等；考試分數低於70分高於或等於60分為C等；考試分數低於60分為D等；沒有參加考試的不劃等第。用Excel計算等第的公式如下(其中分數存放在C列，計算結果存入D列)：=IF(C2>=85,″A″,IF(C2>=70,″B″,IF(C2>=60,″C″,IF(ISNUMBER(C2),″D″,IF(ISBLANK(C2),″″)))))，這是一個典型的IF函數嵌套公式，式中第二個IF語句是第一個IF語句的參數，第三個IF語句則是第二個IF語句的參數，以此類推。如果第一個邏輯判斷表達式C1>=85為TRUE(真)，則D1單元格被填入「A」；如果第一個邏輯判斷表達式C1>=85不成立，則計算第二個IF語句「IF(C1>=70」；以此類推直至計算結束。其中ISNUMBER函數在C1為空時返回FALSE(假)，接著執行最後一個IF語句，否則在C1單元格中填入「D」。ISBLANK函數C1為空時返回TRUE(真)，則C1單元格被填入一個空格。使用ISNUMBER函數和ISBLANK函數，可防止某個學生沒有參加考試(即考試成績為空)，但仍然給他評定為D等的情況發生。 如果成績等第劃分標準發生了變化，只須改變邏輯判斷式中的值(85、70、60)即可，故上式在需要劃分等第的場合具有一定的應用價值。IF函數常用的比較操作符如^00100005c^： 4.利用IF函數計算工資增加金額 IF函數是Excel中最有用的函數之一，網v8N(網NH&6S:S0.z它可以解決辦公事務處理中的許多問題。請看以下實例： 某單位要給職工增加工資，

9URXGMS管`pa絡8網"供教

其標準為：工齡少於等於5年為20元；工齡多於5年少於等於10年為40元；工齡多於10年少於等於15年為70元；工齡多於15年少於等於20年為100元；工齡多於20年少於等於25年為140元；工齡多於25年少於等於30年為180元；工齡多於30年為240元。 此問題的處理方法是：建立工作表，其中A列輸入職工姓名，B列輸入工齡，C列存放計算出來的增資金額，A1、B1、C1單元格中輸入列標題。再讓單元格指針停留在C2單元格，按筆者以前介紹過的方法輸入公式：=IF(B2<=5,20,IF(B2<=10,40,IF(B2<=15,70,IF(B2<=20,100,IF(B2<=25,140,IF(B2<=30,180,IF(B2>30,240)))))))。 上式首先計算第一個邏輯判斷「B2<=5」，若其為「真」，則C2單元格被填入數值「20」；若其為「假」，則計算第二個IF語句「IF(B2<=10,40」，依次執行直至計算結束。這就是IF函數嵌套的基本形式，加以改造後可應用於其他需要多重邏輯判斷的場合。 需要注意的是：公式中的邏輯判斷條件「B2<=10」、「B2<=15」等不能寫成「5<B2<=10」、「10<B2<=15」，否則Excel雖不報告錯誤。但計算中該條件會被忽略，導致最終計算結果錯誤。另外，IF函數最多只能嵌套七層(如本例)，再多就會出錯。 另外，IF函數邏輯判斷返回的結果可以是數值或文本等數據，也可以是一個表達式(如嵌套的下一個IF函數)。根據這一點，我們可以在判斷返回結果時進行某些計算。仍用上面的例子，建立工作表，在D2單元格輸入以下公式：=IF(B2<=5,C2+20,IF(B2<=10,C2+40,IF(B2<=15,C2+70,IF(B2<=20,C2+100,IF(B2<=25,C2+140,IF(B2<=30,C2+180,IF(B2>30,C2+240)))))))，執行後可以立即算出增加工資後的總工資。當然你也可以將公式中的「C2+20」、「C2+40」等修改為SUM(C2，20)、SUM(C2，40)等，這樣可以執行一些更為複雜的求和運算。 5.SUM函數應用實例 SUM函數是Excel中使用最多的函數，利用它進行求和運算可以忽略存有文本、空格等數據的單元格，語法簡單、使用方便。 (1)行或列求和 以最常見的工資表為例，它的特點是需要對行內的若干單元格求和，並要自動扣除「房電費」、「稅金」等。根據習慣，這些項目並不輸入負數。這時可在F2單元格輸入如下公式：=SUM(A2:C2，-D2，-E2)。其中A2:C2引用是收入，而D2、E2為支出。由於Excel不允許在單元格引用前面加負號，所以應在表示支出單元格前加負號，這樣即可計算出正確結果。若收入和支出所在的單元格不連續，可將公式寫成「=SUM(A2:C2，-D2，E2:F2，-G2)」的形式。 (2)區域求和 區域求和常用於對一張工作表中的所有數據求總計。此時你可以讓單元格指針停留在存放結果的單元格，然後在Excel編輯欄輸入公式「=SUM()」，用滑鼠在括弧中間單擊，最後拖過需要求和的所有單元格。若這些單元格是不連續的，可以按住Ctrl鍵分別拖過它們。然後用手工在公式引用的單元格前加上負號。 (3)IF函數和SUM函數聯合應用 IF函數和SUM函數聯合起來，可以累計滿足多個條件的值出現的次數。例如下面的公式就可以統計考試成績及格的男生人數。每當Excel在B1:B200中找到「男」時，它就會檢查同一行中C列(C1:=SUM(IF(B1:B200=″男″,IF(C1:C200>=60,1,0)))。 需要說明上式是一個數組公式，輸入結束時必須按下Ctrl+SHIFT鍵然後敲回車，Excel2000自動在公式兩側加上大括弧，成為「{=SUM(IF(B1:B200=″男″,IF(C1:C200>=60,1,0)))}」這是使用數組必不可少的步驟。 注意：SUM函數中的參數，即被求和的單元格或單元格區域不能超過30個。換句話說，SUM函數括弧中出現的分隔符(逗號)不能多於29個，否則Excel就會提示參數太多。對需要參與求和的某個常數，可用「=SUM(單元格區域，常數)」的形式直接引用，一般不必絕對引用存放該常數的單元格。 6.COUNTIF函數應用實例 COUNTIF函數可累計滿足某一條件的對象在單元格區域中出現的次數，該條件可以是數值、文本或表達式，在人事、工資和成績統計中有廣泛用途。請看以下實例： 某單位要統計工資報表中工資總額大於等於1000元的人數，男職工人數和女職工人數。假設工資總額存放在工作表的F列，職工性別存放在工作表B列，該單位有1000人。 具體計算公式為：統計工資總額大於等於1000元的人數可用公式：「=COUNTIF(F1:F1000,″>=1000″)」；統計男職工人數可用公式：「=COUNTIF(B1:B1000,″=男″)」，統計女職工人數可用公式：「=COUNTIF(B1:B1000,″=女″)」 7.SUMIF函數應用實例 SUMIF函數可對滿足某一條件的單元格區域求和，該條件可以是數值、文本或表達式，可以應用在人事、工資和成績統計中。例如：某單位要統計工資報表中職稱為中級的員工的工資總額。假設工資總額存放在工作表的F列，員工職稱存放在工作表B列。 具體計算公式為：「=SUMIF(B1:B1000,″中級″,F1:F1000)」，其中「B1:B1000」為提供邏輯判斷依據的單元格區域，「中級」為判斷條件即只統計B1:B1000區域中職稱為「中級」的單元格，F1:F1000為實際求和的單元格區域。 另外，COUNTIF函數和SUMIF函數結合起來可用於統計考試及格學生的平均分。具體計算公式為：=SUMIF(A4:A46,″>=60″,A4:A11)/COUNTIF(A4:A46,″>=60″)，式中兩個函數的參數、邏輯判斷條件的意義請參看上面的實例自行分析。 8.RANK函數應用實例 排名次是成績統計等領域常用的一種分析手段，利用Excel2000提供的RANK函數可以輕鬆完成。假設6個數據存放在C列的C2:C7單元格，

vcdYN育育`BkZh

那麼只要選中D2單元格，然後在編輯欄中輸入如下公式：=RANK(C2,$C$2:$C$6)。輸入結束回車或單擊「輸入」按鈕完成計算。RANK函數的最大優點是可以處理重名次，

中V`=#$i網垠-網xW

可使數值相同的單元格計算出的名次相同。如果你要排序的數據有40個，只須將以上兩式中的6改為41即可。 9.利用FREQUENCY函數統計成績分布 統計某次考試的成績分布，是教育和其他統計的一項常規工作，Excel2000中的FREQUENCY函數是完成這類工作的利器。 (1)打開一個空白EXCEL工作表，在A列輸入待統計的考試成績或其他數據，這裡假設有A1:A50共50個成績。如果B列的B1:B5單元格分別輸入60、70、80、90、100，則表示統計小於60分、60—70、70—80、80—90和90—100分數段中的成績個數。 (2)選中C1:C5單元格存放各分數段的成績個數，在編輯欄內輸入公式：=FREQUENCY(A1:A50,B1:B5) (3)讓游標停留在公式的末尾，按下Shift+Ctrl鍵敲回車(這是生成數組公式的關鍵環節)，C1:C5單元格立刻計算出各分數段中的成績個數。 10.＆、MONTH()和NOW()函數的使用 許多報表的標題均含有月份，例如「XX中學X月教師課時獎金錶」，利用＆、MONTH()和NOW()函數可以實現月份自動輸入。具體做法如下： (1)將標題佔用的所有單元格合併。 (2)在選中合併後的單元格，然後在編輯欄內輸入公式：=「XX中學」＆MONTH(NOW())＆「月教師課時獎金錶」。 其中＆為連接符，它可以將其左右的字元連接成一個整體。NOW()函數可以返回電腦內部的系統日期與時間，MONTH()函數可以將序列數轉換為對應的月份數。這樣，每次打開這個工作表，標題中的月份均會自動更新，形如「XX中學12月教師課時獎金錶」。如果你想在標題中顯示年份，可以將公式修改為：=「XX中學」＆YEAR(NOW())＆「年」＆MONTH(NOW())＆「月教師課時獎金錶」。
推薦閱讀：