版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進(jìn)行舉報或認(rèn)領(lǐng)
文檔簡介
1、Excel函數(shù)應(yīng)用之邏輯函數(shù)用來判斷真假值,或者進(jìn)行復(fù)合檢驗的Excel函數(shù),我們稱為邏輯函數(shù)。在Excel中提供了六種邏輯函數(shù)。即AND、OR、NOT、FALSE、IF、TRUE函數(shù)。一、AND、OR、NOT函數(shù)這三個函數(shù)都用來返回參數(shù)邏輯值。詳細(xì)介紹見下:(一)AND函數(shù)所有參數(shù)的邏輯值為真時返回 TRUE;只要一個參數(shù)的邏輯值為假即返回 FALSE。簡言之,就是當(dāng)AND的參數(shù)全部滿足某一條件時,返回結(jié)果為TRUE,否則為FALSE。語法為AND(logical1,logical2, .),其中Logical1, logical2, . 表示待檢測的 1 到 30 個條件值,各條件值可能為
2、TRUE,可能為 FALSE。 參數(shù)必須是邏輯值,或者包含邏輯值的數(shù)組或引用。舉例說明:1、 在B2單元格中輸入數(shù)字50,在C2中寫公式=AND(B230,B260)。由于B2等于50的確大于30、小于60。所以兩個條件值(logical)均為真,則返回結(jié)果為TRUE。 圖1 AND函數(shù)示例12、 如果 B1-B3 單元格中的值為 TRUE、FALSE、TRUE,顯然三個參數(shù)并不都為真,所以 在B4單元格中的公式=AND(B1:B3) 等于 FALSE 圖2 AND函數(shù)示例2(二)OR函數(shù)OR函數(shù)指在其參數(shù)組中,任何一個參數(shù)邏輯值為 TRUE,即返回 TRUE。它與AND函數(shù)的區(qū)別在于,AND
3、函數(shù)要求所有函數(shù)邏輯值均為真,結(jié)果方為真。而OR函數(shù)僅需其中任何一個為真即可為真。比如,上面的示例2,如果在B4單元格中的公式寫為=OR(B1:B3)則結(jié)果等于TRUE 圖3 OR函數(shù)示例(三)NOT函數(shù)NOT函數(shù)用于對參數(shù)值求反。當(dāng)要確保一個值不等于某一特定值時,可以使用 NOT 函數(shù)。簡言之,就是當(dāng)參數(shù)值為TRUE時,NOT函數(shù)返回的結(jié)果恰與之相反,結(jié)果為FALSE.比如NOT(2+2=4),由于2+2的結(jié)果的確為4,該參數(shù)結(jié)果為TRUE,由于是NOT函數(shù),因此返回函數(shù)結(jié)果與之相反,為FALSE。二、TRUE、FALSE函數(shù)TRUE、FALSE函數(shù)用來返回參數(shù)的邏輯值,由于可以直接在單元格
4、或公式中鍵入值TRUE或者FALSE。因此這兩個函數(shù)通??梢圆皇褂?。三、IF函數(shù)(一)IF函數(shù)說明IF函數(shù)用于執(zhí)行真假值判斷后,根據(jù)邏輯測試的真假值返回不同的結(jié)果,因此If函數(shù)也稱之為條件函數(shù)。它的應(yīng)用很廣泛,可以使用函數(shù) IF 對數(shù)值和公式進(jìn)行條件檢測。它的語法為IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示計算結(jié)果為 TRUE 或 FALSE 的任意值或表達(dá)式。本參數(shù)可使用任何比較運算符。Value_if_true顯示在logical_test 為 TRUE 時返回的值,Value_if_true 也可以是其他公
5、式。Value_if_false logical_test 為 FALSE 時返回的值。Value_if_false 也可以是其他公式。簡言之,如果第一個參數(shù)logical_test返回的結(jié)果為真的話,則執(zhí)行第二個參數(shù)Value_if_true的結(jié)果,否則執(zhí)行第三個參數(shù)Value_if_false的結(jié)果。IF函數(shù)可以嵌套七層,用 value_if_false 及 value_if_true 參數(shù)可以構(gòu)造復(fù)雜的檢測條件。Excel 還提供了可根據(jù)某一條件來分析數(shù)據(jù)的其他函數(shù)。例如,如果要計算單元格區(qū)域中某個文本串或數(shù)字出現(xiàn)的次數(shù),則可使用 COUNTIF 工作表函數(shù)。如果要根據(jù)單元格區(qū)域中的某一
6、文本串或數(shù)字求和,則可使用 SUMIF 工作表函數(shù)。(二)IF函數(shù)應(yīng)用1、 輸出帶有公式的空白表單 圖5 人事分析表1以圖中所示的人事狀況分析表為例,由于各部門關(guān)于人員的組成情況的數(shù)據(jù)尚未填寫,在總計欄(以單元格G5為例)公式為:=SUM(C5:F5)我們看到計算為0的結(jié)果。如果這樣的表格打印出來就頁面的美觀來看顯示是不令人滿意的。是否有辦法去掉總計欄中的0呢?你可能會說,不寫公式不就行了。當(dāng)然這是一個辦法,但是,如果我們利用了IF函數(shù)的話,也可以在寫公式的情況下,同樣不顯示這些0。如何實現(xiàn)呢?只需將總計欄中的公式(僅以單元格G5為例)改寫成:=IF(SUM(C5:F5),SUM(C5:F5)
7、,)通俗的解釋就是:如果SUM(C5:F5)不等于零,則在單元格中顯示SUM(C5:F5)的結(jié)果,否則顯示字符串。幾點說明:(1) SUM(C5:F5)不等于零的正規(guī)寫法是SUM(C5:F5)0,在EXCEL中可以省略0;(2) 表示字符串的內(nèi)容為空,因此執(zhí)行的結(jié)果是在單元格中不顯示任何字符。 圖42、 不同的條件返回不同的結(jié)果如果對上述例子有了很好的理解后,我們就很容易將IF函數(shù)應(yīng)用到更廣泛的領(lǐng)域。比如,在成績表中根據(jù)不同的成績區(qū)分合格與不合格?,F(xiàn)在我們就以某班級的英語成績?yōu)槔唧w說明用法。 圖6某班級的成績?nèi)鐖D6所示,為了做出最終的綜合評定,我們設(shè)定按照平均分判斷該學(xué)生成績是否合格的規(guī)則。
8、如果各科平均分超過60分則認(rèn)為是合格的,否則記作不合格。根據(jù)這一規(guī)則,我們在綜合評定中寫公式(以單元格B12為例):=IF(B1160,合格,不合格)語法解釋為,如果單元格B11的值大于60,則執(zhí)行第二個參數(shù)即在單元格B12中顯示合格字樣,否則執(zhí)行第三個參數(shù)即在單元格B12中顯示不合格字樣。在綜合評定欄中可以看到由于C列的同學(xué)各科平均分為54分,綜合評定為不合格。其余均為合格。3、 多層嵌套函數(shù)的應(yīng)用在上述的例子中,我們只是將成績簡單區(qū)分為合格與不合格,在實際應(yīng)用中,成績通常是有多個等級的,比如優(yōu)、良、中、及格、不及格等。有辦法一次性區(qū)分嗎?可以使用多層嵌套的辦法來實現(xiàn)。仍以上例為例,我們設(shè)定
9、綜合評定的規(guī)則為當(dāng)各科平均分超過90時,評定為優(yōu)秀。如圖7所示。 圖7說明:為了解釋起來比較方便,我們在這里僅做兩重嵌套的示例,您可以按照實際情況進(jìn)行更多重的嵌套,但請注意Excel的IF函數(shù)最多允許七重嵌套。根據(jù)這一規(guī)則,我們在綜合評定中寫公式(以單元格F12為例):=IF(F1160,IF(AND(F1190),優(yōu)秀,合格),不合格)語法解釋為,如果單元格F11的值大于60,則執(zhí)行第二個參數(shù),在這里為嵌套函數(shù),繼續(xù)判斷單元格F11的值是否大于90(為了讓大家體會一下AND函數(shù)的應(yīng)用,寫成AND(F1190),實際上可以僅寫F1190),如果滿足在單元格F12中顯示優(yōu)秀字樣,不滿足顯示合格字
10、樣,如果F11的值以上條件都不滿足,則執(zhí)行第三個參數(shù)即在單元格F12中顯示不合格字樣。在綜合評定欄中可以看到由于F列的同學(xué)各科平均分為92分,綜合評定為優(yōu)秀。(三)根據(jù)條件計算值在了解了IF函數(shù)的使用方法后,我們再來看看與之類似的Excel提供的可根據(jù)某一條件來分析數(shù)據(jù)的其他函數(shù)。例如,如果要計算單元格區(qū)域中某個文本串或數(shù)字出現(xiàn)的次數(shù),則可使用 COUNTIF 工作表函數(shù)。如果要根據(jù)單元格區(qū)域中的某一文本串或數(shù)字求和,則可使用 SUMIF 工作表函數(shù)。關(guān)于SUMIF函數(shù)在數(shù)學(xué)與三角函數(shù)中以做了較為詳細(xì)的介紹。這里重點介紹COUNTIF的應(yīng)用。COUNTIF可以用來計算給定區(qū)域內(nèi)滿足特定條件的單
11、元格的數(shù)目。比如在成績表中計算每位學(xué)生取得優(yōu)秀成績的課程數(shù)。在工資表中求出所有基本工資在2000元以上的員工數(shù)。語法形式為COUNTIF(range,criteria)。其中Range為需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria確定哪些單元格將被計算在內(nèi)的條件,其形式可以為數(shù)字、表達(dá)式或文本。例如,條件可以表示為 32、32、32、apples。1、成績表這里仍以上述成績表的例子說明一些應(yīng)用方法。我們需要計算的是:每位學(xué)生取得優(yōu)秀成績的課程數(shù)。規(guī)則為成績大于90分記做優(yōu)秀。如圖8所示 圖8根據(jù)這一規(guī)則,我們在優(yōu)秀門數(shù)中寫公式(以單元格B13為例):=COUNTIF(B4:B
12、10,90)語法解釋為,計算B4到B10這個范圍,即jarry的各科成績中有多少個數(shù)值大于90的單元格。在優(yōu)秀門數(shù)欄中可以看到j(luò)arry的優(yōu)秀門數(shù)為兩門。其他人也可以依次看到。2、 銷售業(yè)績表銷售業(yè)績表可能是綜合運用IF、SUMIF、COUNTIF非常典型的示例。比如,可能希望計算銷售人員的訂單數(shù),然后匯總每個銷售人員的銷售額,并且根據(jù)總發(fā)貨量決定每次銷售應(yīng)獲得的獎金。原始數(shù)據(jù)表如圖9所示(原始數(shù)據(jù)是以流水單形式列出的,即按訂單號排列) 圖9 原始數(shù)據(jù)表按銷售人員匯總表如圖10所示 圖10 銷售人員匯總表如圖10所示的表完全是利用函數(shù)計算的方法自動匯總的數(shù)據(jù)。首先建立一個按照銷售人員匯總的表單
13、樣式,如圖所示。然后分別計算訂單數(shù)、訂單總額、銷售獎金。(1) 訂單數(shù) -用COUNTIF計算銷售人員的訂單數(shù)。以銷售人員ANNIE的訂單數(shù)公式為例。公式:=COUNTIF($C$2:$C$13,A17)語法解釋為計算單元格A17(即銷售人員ANNIE)在銷售人員清單$C$2:$C$13的范圍內(nèi)(即圖9所示的原始數(shù)據(jù)表)出現(xiàn)的次數(shù)。這個出現(xiàn)的次數(shù)即可認(rèn)為是該銷售人員ANNIE的訂單數(shù)。(2) 訂單總額-用SUMIF匯總每個銷售人員的銷售額。以銷售人員ANNIE的訂單總額公式為例。公式:=SUMIF($C$2:$C$13,A17,$B$2:$B$13)此公式在銷售人員清單$C$2:$C$13中檢
14、查單元格A17 中的文本(即銷售人員ANNIE),然后計算訂單金額列($B$2:$B$13)中相應(yīng)量的和。這個相應(yīng)量的和就是銷售人員ANNIE的訂單總額。(3) 銷售獎金-用IF根據(jù)訂單總額決定每次銷售應(yīng)獲得的獎金。假定公司的銷售獎金規(guī)則為當(dāng)訂單總額超過5萬元時,獎勵幅度為百分之十五,否則為百分之十。根據(jù)這一規(guī)則仍以銷售人員ANNIE為例說明。公式為:=IF(C1750000,10%,15%)*C17如果訂單總額小于 50000則獎金為 10%;如果訂單總額大于等于 50000,則獎金為 15%。至此,我們已完全了解了EXCEL函數(shù)的邏輯函數(shù),相信大家在實際工作中會想出更多更有用的運用。例如在
15、A1單元格中有一個18位的身份證號,如果在A2單元格中輸入“=MID(A1,7,6)”就能把其中的出生年月提取出來。請以此為例講下這個函數(shù)的意思與用法。MID是從第幾位到第幾位取值的問題,我建議用right函數(shù),=IF(right(E3,1)=1,0,E3),而且取出來的是文本格式,因此,等號后面的數(shù)值需用。Excel用Max函數(shù)和IF函數(shù)實現(xiàn)求其他數(shù)據(jù)表的最大值 如下圖:上圖,為Sheet1表,僅有兩列,分別為“班級”和“最高分”,大家仔細(xì)看,分明就是求對應(yīng)班級號的最高分!如下圖,為Sheet2表!也僅有兩列,一為班級,二為成績總分,在此,為了方便學(xué)習(xí),為了更能簡單直觀的表述問題,僅使用測試
16、數(shù)據(jù)!我們要解決的問題就是,根據(jù)表一的班級號,通過表二求得最高分,填充到表一最高分列!要解決這個問題,可以使用簡單的辦法,那就是Max函數(shù)和IF函數(shù);下面我們詳細(xì)介紹如何使用這兩個函數(shù)以求最高分!如下圖,首先選擇表一中的B2單元格,之后在fx函數(shù)輸入框中這樣的代碼:“=MAX(IF(A2=Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100)”輸入代碼之后,切勿急躁,這不等同于以往的操作,輸入好之后,按Ctrl+Shift+Enter組合鍵進(jìn)行確認(rèn),以保證輸入的代碼為數(shù)組函數(shù),而非普通函數(shù)的使用!通過以上的組合鍵確認(rèn)之后,代碼的兩端自動加上大括號,注意哦,大括號是自動加入
17、的,而不是手工敲打鍵盤輸入的!B2單元格得出數(shù)據(jù)之后,直接使用填充功能進(jìn)行填充即可,其他班級的最高分自然會填充上!上圖,為最終結(jié)果!下面介紹下代碼:=MAX(IF(A2=Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100)A2=Sheet2!$A$2:$A$100代表檢測表一A2單元格的值,即1班的班級名稱,其比較范圍是表二的A2單元格到A100單元格,在此,其范圍可根據(jù)需要修改,一般,數(shù)據(jù)范圍大于預(yù)定范圍即可!Sheet2!$B$2:$B$100代表檢測表二成績總分的數(shù)據(jù)區(qū)域,即B2單元格到B100單元格的數(shù)據(jù),在此,其范圍可根據(jù)需要修改,一般,數(shù)據(jù)范圍大于預(yù)定范圍即
18、可!上述兩個代碼,綜合IF而言,指的就是根據(jù)表一特定單元格的班級名稱,在表二設(shè)定數(shù)據(jù)區(qū)域中的查找該班級的成績,而其他班級的成績則不作為參考對象!綜合外圍的MAX函數(shù),即是求該班級的最高分!倘若我們要查找的是最低分,可將MAX函數(shù)改為MIX函數(shù)即可!如:=MIX(IF(A2=Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100)倘若我們查找的不是某班級的最高分,而是所有班級的最高分,那么,只需將IF函數(shù)名稱及其對應(yīng)的大括號對去掉便可!如:=MAX(A2=Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100)注意哦,如果是求最小值的話,倘若表二中有個別數(shù)
19、據(jù)為空的話,其最小值當(dāng)0計算如果在公式中運算使用不等號,輸入.例如,如果單元格A1值不等于10,則B1單元格值等于20,否則等于0,在B1中輸入:=IF(A110,20,0)不超過“第七關(guān)”是可用if嵌套的,以三關(guān)為例: =IF(OR(B51=1,2),第一關(guān),IF(OR(B51=3,4),第二關(guān),IF(OR(B51=5,6),第三關(guān)) 如果關(guān)數(shù)超過7,可考慮用lookup函數(shù)。例:=lookup(b51,2,10,18,26,34,42,50,58,第一關(guān),第二關(guān),第三關(guān),第四關(guān),第五關(guān),第六關(guān),第七關(guān),第八關(guān))求助如何在一個IF中實現(xiàn)多個條件下求值?各位高手:在下想實現(xiàn)在一個IF中實現(xiàn)多個條件下求值?如:圖 我想在A2中實現(xiàn)=IF(A1=1或B1=2或C1=3或D1=4或E1=5或F1=6或G1=7或H1=8或I1=9或J1=10.,謝謝,非常感謝) =IF(A1=1和B1=2和C1=3和D1=4和E1=5和F1=6和G1=7和H1=8和I1=9和J1=10.,謝謝,非常感謝)一般用多個IF可實現(xiàn),A2=IF(A1=1,非常感謝,IF(B1=2,非常感謝,IF(C1=3,非常感
溫馨提示
- 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 人人文庫網(wǎng)僅提供信息存儲空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負(fù)責(zé)。
- 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請與我們聯(lián)系,我們立即糾正。
- 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時也不承擔(dān)用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。
最新文檔
- 公司采購紙杯合同范例
- 溝槽管件合同范例
- 口罩材料轉(zhuǎn)讓合同范例
- 車內(nèi)飾裝飾合同范例
- 欠款合同范例對公
- 花卉租合同范例
- 包裝課程設(shè)計引言總結(jié)
- 拉面烹調(diào)課程設(shè)計思路
- 總降壓變電所課程設(shè)計
- 托育早教思維課程設(shè)計
- 多金屬廢料高效綜合回收利用產(chǎn)業(yè)升級項目環(huán)評報告書
- 石方開挖的環(huán)保措施
- 商洛市商州區(qū)金礦煤礦礦山地質(zhì)環(huán)境保護(hù)與土地復(fù)墾方案
- 中國鐵塔股份有限公司代維交接指南(2017年)
- 常用藥物皮試配制法和藥物過敏反應(yīng)的急救措施
- 醫(yī)學(xué)微生物學(xué)知到章節(jié)答案智慧樹2023年山東第一醫(yī)科大學(xué)
- 印刷通用質(zhì)量檢驗標(biāo)準(zhǔn)
- 電子測量技術(shù)基礎(chǔ)課后答案
- 大興調(diào)查研究研討發(fā)言材料學(xué)習(xí)心得體會中心組3篇
- 培訓(xùn)學(xué)?;馂?zāi)應(yīng)急預(yù)案
- 面試評分表完整版
評論
0/150
提交評論