EXCEL在日常工作中的應(yīng)用.ppt_第1頁(yè)
EXCEL在日常工作中的應(yīng)用.ppt_第2頁(yè)
EXCEL在日常工作中的應(yīng)用.ppt_第3頁(yè)
EXCEL在日常工作中的應(yīng)用.ppt_第4頁(yè)
EXCEL在日常工作中的應(yīng)用.ppt_第5頁(yè)
已閱讀5頁(yè),還剩119頁(yè)未讀, 繼續(xù)免費(fèi)閱讀

下載本文檔

版權(quán)說(shuō)明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請(qǐng)進(jìn)行舉報(bào)或認(rèn)領(lǐng)

文檔簡(jiǎn)介

,EXCEL 在日常工作中的應(yīng)用,第一章 EXCEL文件管理,新建文件 工作表的隱藏 文件的安全與保護(hù) 快速打印指定表格,第二章 單元格編輯,錄入相同的內(nèi)容 下拉列表錄入 錄入內(nèi)容的限制 限制數(shù)字格式或大小 文本長(zhǎng)度單多條件限定 限制重復(fù)輸入 錄入?yún)^(qū)域的限制,第二章 單元格編輯,選取 使用定位選取 選擇性粘貼 粘貼數(shù)值 粘貼運(yùn)算 隱藏 查找,第三章 條件格式,3.1條件格式的設(shè)立、添加 設(shè)立條件格式 添加條件 3.2定義條件 單元格數(shù)值條件 公式條件 3. 3條件格式實(shí)例應(yīng)用 3.4小結(jié),第三章 條件格式,3.1條件格式的設(shè)立、添加 設(shè)立條件格式 添加條件 3.2定義條件 單元格數(shù)值條件 公式條件 3. 3條件格式實(shí)例應(yīng)用 3.4小結(jié),第三章 條件格式,3.1條件格式的設(shè)立、添加 設(shè)立條件格式 操作步驟:選中區(qū)域格式 條件格式 輸入條件選擇格式 添加條件 在條件設(shè)置對(duì)話框中,單擊添加按鈕 注:條件格式最多可以設(shè)置三個(gè),第三章 條件格式,3.2定義條件 單元格數(shù)值:用于簡(jiǎn)單的數(shù)值對(duì)比 公式 :用于設(shè)置較為復(fù)雜的單元格內(nèi)容 3.2.1單元格數(shù)值條件 3.2.2公式條件,第三章 條件格式,3.2定義條件 單元格數(shù)值:用于簡(jiǎn)單的數(shù)值對(duì)比 公式 :用于設(shè)置較為復(fù)雜的單元格內(nèi)容 3.2.1單元格數(shù)值條件 3.2.2公式條件,第三章 條件格式,3.3條件格式實(shí)例應(yīng)用 3.3.1工齡分析的顏色提示 3.2.2應(yīng)收賬款催款提醒 3.2. 3合同到期提醒 監(jiān)視重復(fù)錄入 格式化賬簿 代碼錄入的錯(cuò)誤顯示 動(dòng)態(tài)顯示銷售額排行 隱藏公式中的錯(cuò)誤值,第三章 條件格式,3.3條件格式實(shí)例應(yīng)用 3.3.5代碼錄入的錯(cuò)誤顯示 條件:1.代碼位數(shù)不等于五位 2.代碼位數(shù)不等于八位 公式: =AND(LEN($B2)5,LEN($B2)8,$B20),第三章 條件格式,3.3條件格式實(shí)例應(yīng)用 3.3.6動(dòng)態(tài)顯示銷售額排行 條件:突出顯示前N名商品的銷售額 公式: =$D2=LARGE($D$2:$D$10,5) 最大值函數(shù),MAX求出一個(gè)最大值,LARGE可以求第N個(gè)最大值.,第三章 條件格式,3.3條件格式實(shí)例應(yīng)用 3.3.7隱藏公式中錯(cuò)誤值 條件:把所有錯(cuò)誤值隱藏 公式: =ISERROR(D2) 判斷值是否為任意錯(cuò)誤值(#N/A,VALUE?。?第三章 條件格式,3.4小結(jié) 本章對(duì)條件的創(chuàng)建、條件的設(shè)置作了詳細(xì)介紹,同時(shí)也列舉了大量應(yīng)用實(shí)例。讀者從實(shí)例中不難看出,如果想用好條件格式,掌握公式及函數(shù)的使用是非常重要的。 習(xí)題: 1、如何設(shè)置公式條件 2、如何突出顯示重復(fù)錄入內(nèi)容? 3、如何突出顯示一列數(shù)據(jù)中最大前三個(gè)數(shù)字? 4、如何添加和刪除條件格式?,第四章 數(shù)據(jù)表和圖表,4.1排序 4.1.1數(shù)據(jù)表排序 4.1.2隔行插入空行 4.2分列 4.2.1拆分整列為多列 4.2.2長(zhǎng)文本型數(shù)字的導(dǎo)入 4.2.3轉(zhuǎn)化字符為日期格式 4.3自動(dòng)篩選 4.3.1自動(dòng)篩選的實(shí)現(xiàn) 4.3.2一次刪除所有重復(fù)記錄,第四章 數(shù)據(jù)表和圖表,4.3自動(dòng)篩選 4.3.1自動(dòng)篩選的實(shí)現(xiàn) 4.3.2一次刪除所有重復(fù)記錄 添加一輔助列,輸入公式: =IF(COUNTIF($D2:D2,D2)1,1,2),這兒一定要注意理解絕對(duì)引用和相對(duì)引用的用法! 第一個(gè):=IF(COUNTIF($D$2:D2,D2)1,1,2) 第二個(gè):=IF(COUNTIF($D$2:D5,D5)1,1,2),第四章 數(shù)據(jù)表和圖表,4.4高級(jí)篩選 高級(jí)篩選功能靈活性強(qiáng),和自動(dòng)篩選相比有如下特點(diǎn): 可以把篩選結(jié)果復(fù)制到其他位置; 需要設(shè)置條件區(qū)域,而且可以使用更多條件; 可篩選不重復(fù)記錄; 4.4.1篩選符合條件的記錄 1.輸入條件區(qū)域 規(guī)則:(1)標(biāo)題行和源區(qū)域一樣 (2)同行不同列的條件是并列關(guān)系; (3)同列不同行的條件是或者關(guān)系,第四章 數(shù)據(jù)表和圖表,2.設(shè)置篩選項(xiàng)目. 復(fù)制標(biāo)題行到要顯示篩選結(jié)果的第一行. 復(fù)制和手工輸入有什么區(qū)別? 3.數(shù)據(jù)篩選高級(jí)篩選,數(shù)據(jù)源區(qū)域,設(shè)置條件的區(qū)域,第四章 數(shù)據(jù)表和圖表,4.4.2篩選本列不重復(fù)記錄 4.4.3篩選兩區(qū)域重復(fù)記錄 4.4.4篩選兩表中不重復(fù)記錄 =COUNTIF($D$16:$D$24,D3)=0,第四章 數(shù)據(jù)表和圖表,4.5數(shù)據(jù)透視表 數(shù)據(jù)透視表是一種對(duì)數(shù)據(jù)清單快速建立匯總的動(dòng)態(tài)總結(jié)報(bào)告,它可以隨時(shí)調(diào)換行列的位置而進(jìn)行不同形式的匯總,是Excel提供的一個(gè)極為有效的匯總工具。數(shù)據(jù)透視表在銷售數(shù)據(jù)匯總、出入庫(kù)匯總及明細(xì)賬匯總等方面有著廣泛應(yīng)用,一個(gè)普通的數(shù)據(jù)表,你的工作表含有大量數(shù)據(jù),但是你知道這些數(shù)字的含義嗎?這些數(shù)據(jù)能夠解答您的問(wèn)題嗎?,不普通的數(shù)據(jù)透視表,數(shù)據(jù)透視表提供了一種快速且強(qiáng)大的方式來(lái)分析數(shù)值數(shù)據(jù)、以不同的方式查看相同的數(shù)據(jù)以及回答有關(guān)這些數(shù)據(jù)的問(wèn)題。,第四章 數(shù)據(jù)表和圖表,4.5數(shù)據(jù)透視表 4.5.1創(chuàng)建數(shù)據(jù)透視表 三步曲之一:確定報(bào)表類型,第四章 數(shù)據(jù)表和圖表,4.5數(shù)據(jù)透視表 4.5.1創(chuàng)建數(shù)據(jù)透視表 三步曲之二:確定數(shù)據(jù)源,第四章 數(shù)據(jù)表和圖表,4.5數(shù)據(jù)透視表 4.5.1創(chuàng)建數(shù)據(jù)透視表 三步曲之三:布局,第四章 數(shù)據(jù)表和圖表,4.5數(shù)據(jù)透視表 4.5.1創(chuàng)建數(shù)據(jù)透視表 三步曲之三:確定顯示位置,第四章 數(shù)據(jù)表和圖表,4.5.5固定數(shù)據(jù)透視表格式 4.5.1創(chuàng)建數(shù)據(jù)透視表 4.5.2調(diào)整數(shù)據(jù)透視表格式 4.5.3在數(shù)據(jù)透視表中設(shè)置公式 4.5.4其他編輯 調(diào)整匯總方式 顯示或隱藏匯總行 數(shù)據(jù)透視表的更新和自動(dòng)更新,第四章 數(shù)據(jù)表和圖表,4.5.7多個(gè)數(shù)據(jù)透視表合并,第四章 數(shù)據(jù)表和圖表,4.5.7多個(gè)數(shù)據(jù)透視表合并,第四章 數(shù)據(jù)表和圖表,4.6 圖表 4.6.1創(chuàng)建圖表 插入圖表 四步曲 設(shè)置圖表類型 設(shè)置數(shù)據(jù)源 設(shè)置圖表選項(xiàng) 設(shè)置圖表位置,第四章 數(shù)據(jù)表和圖表-四步曲,第四章 數(shù)據(jù)表和圖表,4.6.2 雙坐標(biāo)圖表,單坐標(biāo)圖表,常規(guī)設(shè)置的雙坐標(biāo)圖表,雙坐標(biāo)圖表,第五章 公式與函數(shù),公式與函數(shù)是Excel的精華所在,它為分析和處理數(shù)據(jù)提供了方便,特別是在處理大批量數(shù)據(jù)和進(jìn)行復(fù)雜數(shù)據(jù)分析等方面更能發(fā)揮它的強(qiáng)大功能。本章在介紹函數(shù)的用法時(shí),著重介紹它們?cè)谪?cái)務(wù)工作中的應(yīng)用。 本章要點(diǎn): IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函數(shù)的用法 IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函數(shù)的實(shí)例應(yīng)用,第五章 公式與函數(shù),公式與函數(shù)是Excel的精華所在,它為分析和處理數(shù)據(jù)提供了方便,特別是在處理大批量數(shù)據(jù)和進(jìn)行復(fù)雜數(shù)據(jù)分析等方面更能發(fā)揮它的強(qiáng)大功能。本章在介紹函數(shù)的用法時(shí),著重介紹它們?cè)谪?cái)務(wù)工作中的應(yīng)用。 本章要點(diǎn): IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函數(shù)的用法 IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函數(shù)的實(shí)例應(yīng)用,第五章 公式與函數(shù),5.1 IF函數(shù) IF函數(shù)是工作中最常用函數(shù)之一,它可以根據(jù)設(shè)置的條件進(jìn)行運(yùn)算或返回值。 語(yǔ)法:=IF(邏輯表達(dá)式,TRUE,F(xiàn)ALSE),邏輯表達(dá)式不成立返回的值,邏輯表達(dá)式成立返回的值,返回值為TRUE或FALSE的邏輯表達(dá)式,例:=IF(53,“對(duì)“,“不對(duì)“),例:=IF(53,“不對(duì)“,“對(duì)“),第五章 公式與函數(shù),5.1 IF函數(shù),邏輯表達(dá)式不成立返回的值,邏輯表達(dá)式成立返回的值,返回值為TRUE或FALSE的 邏輯表達(dá)式,打開EXCEL,第五章 公式與函數(shù),5.1.1 單條件和多條件判斷 1.單條件返回文本 IF函數(shù)實(shí)例(P113),邏輯表達(dá)式不成立返回的值,邏輯表達(dá)式成立返回的值,邏輯表達(dá)條件:比較實(shí)際數(shù)和計(jì)劃數(shù)的大小,D2=IF(C2B2,“節(jié)約“,“超支“),第五章 公式與函數(shù),5.1.1 單條件和多條件判斷 2.單條件判斷并運(yùn)算 IF函數(shù)實(shí)例(P113),邏輯表達(dá)式不成立進(jìn)行運(yùn)算的表達(dá)式,邏輯表達(dá)式成立時(shí)進(jìn)行運(yùn)算的表達(dá)式,邏輯表達(dá)條件: 銷售額是否超過(guò)3萬(wàn)元,C2=IF(B230000,B2*0.015,B2*0.01),第五章 公式與函數(shù),5.1.1 單條件和多條件判斷 3.單條件判斷返回引用區(qū)域 IF函數(shù)實(shí)例(P113),邏輯表達(dá)式不成立返回的區(qū)域,邏輯表達(dá)式成立時(shí)返回的區(qū)域,邏輯表達(dá)條件: A2是否等于銷售一部,=SUM(IF(A2=“銷售一部“,B5:B9,E5:E9),第五章 公式與函數(shù),5.1.1 單條件和多條件判斷 4.多條件判斷 IF函數(shù)實(shí)例,邏輯表達(dá)式不成立返回的表達(dá)式,邏輯表達(dá)式成立時(shí)返回的值,邏輯表達(dá)條件: B2或C2任一為0是否成立,=IF(OR(B2=0,C2=0), “,(C2-B2)/C2),第五章 公式與函數(shù),5.1.1 單條件和多條件判斷 4.多條件判斷 IF函數(shù)實(shí)例,=IF(B220000,B2*1%,IF(B225000,B2*2%, IF(B235000,B2*3%,B2*4%),=IF(B220000,B2*1%,IF(20000=B225000,B2*2%,IF(25000=B235000,B2*3%,B2*4%),常見(jiàn)的 錯(cuò)誤,常見(jiàn)的 錯(cuò)誤,第五章 公式與函數(shù),5.1.1 單條件和多條件判斷 4.多條件判斷 IF函數(shù)實(shí)例(P113),=IF(B2=10000),B2*2%,0)+IF(AND(B2=20000),B2*3%,0)+IF(AND(B2=30000),B2*4%,0)+IF(AND(B2=40000),B2*5%,0)+IF(AND(B2=50000),B2*6%,0)+IF(AND(B2=60000),B2*7%,0)+IF(AND(B2=70000),B2*8%,0)+IF(B280000,B2*9%,0),第五章 公式與函數(shù),課堂練習(xí): 成績(jī)表 小結(jié):這節(jié)課講了IF函數(shù)的使用,有以下四種情況: 單條件返回文本 單條件進(jìn)行運(yùn)算 單條件返回區(qū)域 多條件判斷 IF函數(shù)在實(shí)際工作中應(yīng)用很廣,要注意不同函數(shù)中參數(shù)的含義。,第五章 公式與函數(shù),5.2 SUM函數(shù) SUM函數(shù)是工作中最常用函數(shù)之一,幾乎所有的表格中都有合并的運(yùn)算。 語(yǔ)法:=SUM(參數(shù)1,參數(shù)2,參數(shù)30),參數(shù)最多為30個(gè),參數(shù)可以為引用,數(shù)值,文本,表達(dá)式和數(shù)組,例:=SUM(5,3,2,1),例:=(a1:b1),第五章 公式與函數(shù),5.2.1 連續(xù)、不連續(xù)及交叉區(qū)域求和 例1:連續(xù)區(qū)域的求和 =SUM(A1:C5) 例1:不連續(xù)區(qū)域的求和 =SUM(A1,B3,D22) 例1:交叉區(qū)域的求和 =SUM(1:3 C:C),注意:這兒有空格,第五章 公式與函數(shù),5.2.2 多工作表自動(dòng)匯總 是SUM函數(shù)的三維應(yīng)用 例: =SUM(1日:空白!C5),單引號(hào)的作用是去掉工作表名的空格,工作表的名稱必須加感嘆號(hào)!,第五章 公式與函數(shù),5.1 SUMIF函數(shù) SUMIF函數(shù)是根據(jù)指定條件對(duì)若干單元格求和。 語(yǔ)法:=SUMIF(條件范圍,條件,求和范圍),需要求和的實(shí)際范圍,省略則對(duì)條件范圍求和,只能用單條件而不能用復(fù)合條件,可以使用通配符,用于條件判斷的單元格區(qū)域,例:=SUMIF(B2:B9,“副教授”,D2:D9),例:=SUMIF(D2:D9,“2000“),第五章 公式與函數(shù),5.1 SUMIF函數(shù),求和范圍,條件,條件范圍,打開EXCEL,第五章 公式與函數(shù),5.3.1 單條件求和 問(wèn)題1:根據(jù)B列銷售金額求和,要求對(duì)銷售金額大于2000的數(shù)值求和 SUMIF函數(shù)實(shí)例(P121),省略求和范圍,對(duì)條件范圍進(jìn)行求和,條件,條件范圍,=SUMIF(B2:B9,“2000“),第五章 公式與函數(shù),5.3.1 單條件求和 問(wèn)題2:根據(jù)商品名稱求和,要求對(duì)商品名稱為A1的銷售金額求和 SUMIF函數(shù)實(shí)例,求和范圍,條件,條件范圍,=SUMIF(A2:A9,“A1“,B2:B9),第五章 公式與函數(shù),5.3.1 單條件求和 問(wèn)題3:根據(jù)B列銷售金額求和,要求對(duì)銷售金額大于D2的數(shù)值求和 SUMIF函數(shù)實(shí)例(P121),省略求和范圍,對(duì)條件范圍進(jìn)行求和,條件,條件范圍,=SUMIF(B2:B9,“&D2),第五章 公式與函數(shù),5.3.1 單條件求和 問(wèn)題4:對(duì)B列中大于平均數(shù)的銷售金額求和 SUMIF函數(shù)實(shí)例(P121),省略求和范圍,對(duì)條件范圍進(jìn)行求和,條件,條件范圍,=SUMIF(B2:B9,“&AVERAGE (B2:B9),第五章 公式與函數(shù),5.3.1 單條件求和 問(wèn)題5:求商品名稱包含”A”的銷售金額之和 SUMIF函數(shù)實(shí)例(P121),求和范圍,條件,條件范圍,=SUMIF(A2:A9,“A*“,B2:B9),第五章 公式與函數(shù),5.3.1 單條件求和 問(wèn)題2:根據(jù)商品名稱求和,要求對(duì)商品名稱為A1的銷售金額求和 SUMIF函數(shù)實(shí)例(P113),求和范圍,條件,條件范圍,=SUMIF(A2:A9,“A1“,B2:B9),第五章 公式與函數(shù),5.3.1 單條件求和 問(wèn)題6:根據(jù)商品名稱求第四五個(gè)字符為”A2”,且字符總長(zhǎng)度為6個(gè)字符的銷售金額求和 SUMIF函數(shù)實(shí)例(P121),求和范圍,條件,條件范圍,=SUMIF(A2:A9,“?A2?“,B2:B9),第五章 公式與函數(shù),5.3.2 多條件及區(qū)間求和 問(wèn)題1:符合入庫(kù)數(shù)量大于4小于10的商品,對(duì)其入庫(kù)數(shù)量求和 SUMIF函數(shù)實(shí)例(P122),=SUMIF (C2:C9,“4“)-SUMIF (C2:C9,“=10“),4,10,第五章 公式與函數(shù),5.3.2 多條件及區(qū)間求和 問(wèn)題2:B列品名分別為”AA”,”BB”,”CC”的銷售數(shù)量之和 SUMIF函數(shù)實(shí)例,=SUM(SUMIF (B2:B9 ,”AA”,”BB”,”CC”,C2:C9),第五章 公式與函數(shù),5.3.2 多條件及區(qū)間求和 問(wèn)題3:對(duì)品名分別為”AA”的手機(jī)入庫(kù)數(shù)量進(jìn)行求和 SUMIF函數(shù)實(shí)例,=SUMIF (A2:A9 ,”AA手機(jī)”,D2:D9) 注意要先添加一輔助列,第五章 公式與函數(shù),5.3.3 不相鄰區(qū)域的求和 SUMIF函數(shù)實(shí)例,=SUMIF (A3:D11 ,”1”,B3:E11) 注意兩個(gè)區(qū)域的大小要一致,第五章 公式與函數(shù),5.4 COUNTIF函數(shù) COUNTIF函數(shù)是根據(jù)指計(jì)算給定區(qū)域內(nèi)滿足特定條件單元格數(shù)目。 語(yǔ)法:=COUNTIF(條件范圍,條件),可以為數(shù)字,表達(dá)式或文本,用于條件判斷的單元格區(qū)域,例:=COUNTIF(B2:B9,“副教授”),例:=COUNTIF(D2:D9,“2000“),第五章 公式與函數(shù),5.4 COUNTIF函數(shù),條件,條件范圍,打開EXCEL,第五章 公式與函數(shù),5.4.1 按條件計(jì)數(shù) 問(wèn)題1:統(tǒng)計(jì)實(shí)發(fā)工資大于2500的人數(shù) COUNTIF函數(shù)實(shí)例(P124),條件,條件范圍,=COUNTIF(E2:E7,“2500“),第五章 公式與函數(shù),5.4.1 按條件計(jì)數(shù) 問(wèn)題2:統(tǒng)計(jì)財(cái)務(wù)部的人數(shù) COUNTIF函數(shù)實(shí)例,條件,條件范圍,=COUNTIF(A2:A7,”財(cái)務(wù)部”),第五章 公式與函數(shù),5.4.2 COUNTIF計(jì)數(shù)常見(jiàn)的錯(cuò)誤 1.區(qū)域選取的影響 COUNTIF函數(shù)實(shí)例(P124),=COUNTIF(B3:B8,C3:C8,”6”),=COUNTIF(B3:C8,”6”),第五章 公式與函數(shù),5.4.2 COUNTIF計(jì)數(shù)常見(jiàn)的錯(cuò)誤 2.數(shù)字格式的影響 COUNTIF函數(shù)實(shí)例(P124),解決辦法:把文本數(shù)字轉(zhuǎn)換成數(shù)值型,第五章 公式與函數(shù),5.4.2 COUNTIF計(jì)數(shù)常見(jiàn)的錯(cuò)誤 3.長(zhǎng)數(shù)字的影響 COUNTIF函數(shù)實(shí)例,解決辦法:在長(zhǎng)數(shù)字中添加*號(hào),第五章 公式與函數(shù),5.5 SUMPRODUCT函數(shù) SUMPRODUCT函數(shù)是在給定的幾組數(shù)組中,將數(shù)組間對(duì)應(yīng)的元素相乘,并返回乘積之和。 語(yǔ)法:= SUMPRODUCT (數(shù)組1,數(shù)組2,數(shù)組3,),數(shù)組參數(shù)必須具有相同的維數(shù),否則函數(shù)SUMPRODUCT將返回錯(cuò)誤值:“#VALUE!”,例:= SUMPRODUCT(1,2,3,4,5)=?,=1*2*3*4*5=120,第五章 公式與函數(shù),5.5 SUMPRODUCT函數(shù),數(shù)組2,數(shù)組1,打開EXCEL,數(shù)組3,第五章 公式與函數(shù),5.5.1庫(kù)存金額的簡(jiǎn)便運(yùn)算 不用設(shè)置金額列,直接計(jì)算出總?cè)霂?kù)金額 SUMPRODUCT函數(shù)實(shí)例,數(shù)組2,數(shù)組1,= SUMPRODUCT (B2:B9,C2:C9),數(shù)組參數(shù)必須具有相同的維數(shù),第五章 公式與函數(shù),5.5.2 多條件計(jì)數(shù)和求和 1.多條件同時(shí)成立 計(jì)數(shù): SUMPRODUCT(條件1)*(條件2)*(條件3)*(條件n) 求和: SUMPRODUCT(條件1)*(條件2)*(條件3)*(條件n)*(要統(tǒng)計(jì)的數(shù)據(jù)區(qū)域) 2.任一條件成立 計(jì)數(shù): SUMPRODUCT(條件1)+(條件2)+(條件3)+(條件n) 求和: SUMPRODUCT(條件1)+(條件2)+(條件3)+(條件n)*(要統(tǒng)計(jì)的數(shù)據(jù)區(qū)域) SUMPRODUCT函數(shù)實(shí)例,第五章 公式與函數(shù),多條件計(jì)數(shù)和求和 例5-17 在入庫(kù)明細(xì)匯總表中,根據(jù)要求計(jì)算 問(wèn)題1:計(jì)算供應(yīng)商A1的冰箱入庫(kù)類型的品種數(shù). SUMPRODUCT函數(shù)實(shí)例,條件2:類別為冰箱,條件1:供應(yīng)商的名字為A1,= SUMPRODUCT (B3:B11=“A1”)*(C3:C11=“冰箱”),第五章 公式與函數(shù),多條件計(jì)數(shù)和求和 例5-17 在入庫(kù)明細(xì)匯總表中,根據(jù)要求計(jì)算 問(wèn)題2:計(jì)算供應(yīng)商A3的洗衣機(jī)入庫(kù)數(shù)量. SUMPRODUCT函數(shù)實(shí)例(P126),條件3:類別為洗衣機(jī),條件1:供應(yīng)商的名字為A3,= SUMPRODUCT (B3:B11=“A3”),(C3:C11=“洗衣機(jī)”)*E3:E11),統(tǒng)計(jì)數(shù)據(jù):入庫(kù)數(shù)量,第五章 公式與函數(shù),多條件計(jì)數(shù)和求和 例5-17 在入庫(kù)明細(xì)匯總表中,根據(jù)要求計(jì)算 舉一反三: 1.計(jì)算供應(yīng)商A1或A2的冰箱入庫(kù)數(shù)量。 2.計(jì)算供應(yīng)商A1的冰箱或彩電的品種數(shù)。 SUMPRODUCT函數(shù)實(shí)例,第五章 公式與函數(shù),5.6 VLOOKUP函數(shù) VLOOKUP函數(shù)是在表格或數(shù)值組的首列查找指定的數(shù)值,并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。它是最常用的函數(shù)之一 功能: 1.指定位置查找和引用數(shù)據(jù) 2.表與表的核對(duì) 3.利用模糊運(yùn)算進(jìn)行區(qū)間查詢,第五章 公式與函數(shù),5.6 VLOOKUP函數(shù) 語(yǔ)法: =VLOOKUP(查找目標(biāo),查找區(qū)域,相對(duì)列數(shù),TRUE或FALSE),要找的內(nèi)容在查找區(qū)域中的哪一列?,在哪兒查找?注意:查找內(nèi)容必須在查找區(qū)域的第一列!,要查找的內(nèi)容,例:=VLOOKUP(B2,$D$2:$H$9,3,0),TRUE:模糊查找,FALSE:精確查找,可以用其1和0代替,第五章 公式與函數(shù),5.6 VLOOKUP函數(shù),相對(duì)列數(shù),查找區(qū)域,查找目標(biāo),打開EXCEL,精確查找或模糊查找,第五章 公式與函數(shù),5.6.1 單個(gè)區(qū)域查找 問(wèn)題1:要求在C列,從員工信息表中根據(jù)姓名查找其級(jí)別. VLOOKUP函數(shù)實(shí)例,=VLOOKUP(B2,$G$9:$H$14,2,0),要找的內(nèi)容在查找區(qū)域中的第2列,在哪兒查找? 員工信息表,注意絕對(duì)引用的使用!,要查找的內(nèi)容:張三,精確查找,可以用0代替,第五章 公式與函數(shù),5.6.1 單個(gè)區(qū)域查找 問(wèn)題2:要求在D,E列,分別根據(jù)工資級(jí)別和姓名,從基本工資表和提成表查找相應(yīng)的數(shù)值. VLOOKUP函數(shù)實(shí)例(P130),=VLOOKUP(C2,$G$2:$H$7,2,0),要找的內(nèi)容在查找區(qū)域中的第2列,在哪兒查找? 基本工資表,注意絕對(duì)引用的使用!,要查找的內(nèi)容:工資級(jí)別,精確查找,可以用0代替,第五章 公式與函數(shù),5.6.2 多個(gè)區(qū)域查找 利用以前所學(xué)的函數(shù) VLOOKUP函數(shù)實(shí)例(P130),=VLOOKUP(A2,IF(C2=“公司1“,$F$3:$G$6,$F$10:$G$13),2,0),要找的內(nèi)容在查找區(qū)域中的第2列,在哪兒查找? 現(xiàn)在有兩個(gè)表,需要判斷的時(shí)候就要想到IF函數(shù),要查找的內(nèi)容:姓名,精確查找,可以用0代替,第五章 公式與函數(shù),5.6.3 模糊查找計(jì)算個(gè)人所得稅 以前學(xué)過(guò)IF函數(shù)條件判斷后再求值,但嵌套太多,容易出錯(cuò),這里運(yùn)用VLOOKUP函數(shù)來(lái)解決這個(gè)問(wèn)題 VLOOKUP函數(shù)實(shí)例(P131),=C2*,要找的內(nèi)容在查找區(qū)域中的第3列,在哪兒查找?,要查找的內(nèi)容:應(yīng)稅所得,模糊查找,可以省略,應(yīng)稅所得,VLOOKUP(C2,$G$2:$I$10,3)-VLOOKUP(C2,$G$2:$J$10,4),第五章 公式與函數(shù),5.6.4 處理查找出現(xiàn)的錯(cuò)誤 在利用VLOOKUP函數(shù)查找時(shí),常遇到下列幾種查詢錯(cuò)誤: 參數(shù)設(shè)置錯(cuò)誤 空格及不可見(jiàn)字符引起的錯(cuò)誤 格式不一致引起的錯(cuò)誤,第五章 公式與函數(shù),1.參數(shù)設(shè)置錯(cuò)誤 VLOOKUP函數(shù)實(shí)例(P131),錯(cuò)誤原因:選取查詢區(qū)域錯(cuò)誤,錯(cuò)誤原因:省略參數(shù)是模糊查找,公式1:=VLOOKUP(B10,A1:E5,3,0),公式2:=VLOOKUP(B11,B2:C5,3,0),錯(cuò)誤原因:選取查詢區(qū)域錯(cuò)誤,公式3:=VLOOKUP(B12,B2:E5,3),第五章 公式與函數(shù),2.空格及不可見(jiàn)字符引起的錯(cuò)誤 VLOOKUP函數(shù)實(shí)例(P132),解決方法:替換不可見(jiàn)字符,解決方法:轉(zhuǎn)換格式,(1)空格引起的錯(cuò)誤,解決方法:替換空格,(2)不可見(jiàn)字符引起的錯(cuò)誤,3.數(shù)字格式不一致引起的錯(cuò)誤,第五章 公式與函數(shù),5.7 INDIRECT函數(shù) INDIRECT 是一個(gè)非常重要的函數(shù),它可以把隨意組合或者插入變量的字符串轉(zhuǎn)換成可以使用的引用。 功能: 返回由文字串指定的引用,并對(duì)引用進(jìn)行計(jì)算,顯示其內(nèi)容。,第五章 公式與函數(shù),5.7 INDIRECT函數(shù) 語(yǔ)法: =INDIRECT(文本字符串,引用類型),TRUE:A1類型 FALSE:R1C1類型 省略為A1類型,對(duì)單元格的引用或字符串,此單元格可以包含A1樣式的引用,定義為引用的名稱或?qū)ξ淖执畣卧竦囊谩?例:=INDIRECT(“R4C4”,0),例:=INDIRECT(“A1”),第五章 公式與函數(shù),5.6 INDIRECT函數(shù),引用類型,文本字符串,打開EXCEL,第五章 公式與函數(shù),5.7.1 行列轉(zhuǎn)置 以前我們學(xué)過(guò)用選擇性粘貼,現(xiàn)在我們來(lái)學(xué)習(xí)用公式進(jìn)行行列的轉(zhuǎn)置. INDIRECT函數(shù)實(shí)例,C1=INDIRECT(“A“&COLUMN(A1),A結(jié)合后面的數(shù)字組合成一個(gè)新的引用,注意相對(duì)引用的使用!,利用相對(duì)絕對(duì)的原理,把列數(shù)取出和前面的”A”組合成一個(gè)新的引用,注意相對(duì)引用的使用!,第五章 公式與函數(shù),5.7.2 日?qǐng)?bào)表的自動(dòng)累計(jì) 日?qǐng)?bào)表是每天必做的工作,累計(jì)工作則是日?qǐng)?bào)表中重要的一項(xiàng)。如果是比較復(fù)雜的日?qǐng)?bào)表,手工輸入累計(jì)值或每張逐一設(shè)置公式,是一件很麻煩的事。這時(shí)就要用到INDIRECT函數(shù) INDIRECT函數(shù)實(shí)例,=INDIRECT(DAY(C2)-1&“日!D13“)+D12,利用取日期中的天數(shù),減去1再加上“日!D13”就得到了上前一天報(bào)表的本月累計(jì)的引用,前一天報(bào)表的D13是前一天的本月累計(jì),再加上今天的本日累計(jì)就得出今天的本月累計(jì),第五章 公式與函數(shù),5.7.3 二級(jí)下拉列表設(shè)置 二級(jí)下拉列表是指在選取一級(jí)下拉列表內(nèi)容后,在后面二級(jí)下拉列表中可以顯示相對(duì)應(yīng)的子列表。光用我們以前學(xué)到的數(shù)據(jù)有效性已經(jīng)不夠了,這兒要用到INDIRECT函數(shù) INDIRECT函數(shù)實(shí)例,=INDIRECT(A2),這里的A2的內(nèi)容是”河南省“,但用了INDIRECT函數(shù)后,返回是的“河南省“所對(duì)應(yīng)的名稱代表的區(qū)域,舉一反三: 能不能做 三級(jí)下拉列表?,第五章 公式與函數(shù),5.8 其他數(shù)學(xué)函數(shù) 本節(jié)介紹的ROUND和MOD函數(shù)用法簡(jiǎn)單,但用途卻極為廣泛. 5.8.1 用ROUND函數(shù)處理工資表的計(jì)算誤差 語(yǔ)法:=ROUND(數(shù)字,指定的位數(shù)),如果指定的位數(shù)大于0,則舍入到指定的小數(shù)位; 如果指定的位數(shù)等于0,則舍入到最接近的整數(shù); 如果指定的位數(shù)小于0,則在小數(shù)舍入;,例:=ROUND(25.265,2)=25.27,例:=ROUND(25.265,0)=25,例:=ROUND(25.265,-1)=30,第五章 公式與函數(shù),5.8.1 ROUND函數(shù),指定的位數(shù),數(shù)字,打開EXCEL,第五章 公式與函數(shù),5.8.1用ROUND函數(shù)處理工資表的計(jì)算誤差 ROUND函數(shù)實(shí)例(P134),=ROUND(G4,2) 通過(guò)設(shè)置小數(shù)點(diǎn)位數(shù),只是顯示上保留兩位小數(shù),實(shí)質(zhì)上單元內(nèi)部的小數(shù)位數(shù)并沒(méi)有改變,而用ROUND函數(shù)是實(shí)質(zhì)上把多余的位數(shù)舍掉了,而不僅僅是在顯示上。,第五章 公式與函數(shù),5.8.2 用MOD函數(shù)隔行填充顏色 功能:返回兩數(shù)相除的余數(shù),結(jié)果的正負(fù)號(hào)與被除數(shù)相同。 語(yǔ)法:=MOD(被除數(shù),除數(shù)),例:=MOD(4,2)=0 例:=MOD(5,2)=1 例:=MOD(-10,4)=-2 例:=MOD(-10,-4)=-2,第五章 公式與函數(shù),5.8.2 MOD函數(shù),除數(shù),被除數(shù),打開EXCEL,第五章 公式與函數(shù),5.8.2 用MOD隔行填充顏色 MOD函數(shù)實(shí)例(P138),=MOD(ROW(),2)=0,返回當(dāng)前行的行數(shù),第五章 公式與函數(shù),5.9 其他統(tǒng)計(jì)函數(shù) 統(tǒng)計(jì)函數(shù)是工作中常用的函數(shù),以前我們學(xué)過(guò)SUM,COUNT等函數(shù)。本節(jié)將介紹其他幾個(gè)統(tǒng)計(jì)函數(shù)。 5.9.1 用COUNTA函數(shù)自動(dòng)統(tǒng)計(jì)工資表人數(shù) 功能:返回參數(shù)組中非空值的數(shù)目。 語(yǔ)法:=COUNTA(參數(shù)1,參數(shù)2,參數(shù)3參數(shù)N),N最大值為30;可以進(jìn)行多工作表的三維引用 注意和COUNT函數(shù)的對(duì)比! COUNT函數(shù)只統(tǒng)計(jì)數(shù)值型數(shù)據(jù),例:=COUNTA(A1:B67),第五章 公式與函數(shù),5.9.1 COUNTA函數(shù),參數(shù)2,參數(shù)1,打開EXCEL,第五章 公式與函數(shù),5.9.1用COUNTA函數(shù)自動(dòng)統(tǒng)計(jì)工資表人數(shù) COUNTA函數(shù)實(shí)例,=COUNTA(B2:B5) 不便于插入行 =COUNTA(INDIRECT(“B2:B“&ROW()-1) 利用INDIRECT函數(shù)把字符串轉(zhuǎn)換為引用,利用ROW函數(shù)把當(dāng)前行數(shù)減去1,得出上一行的行數(shù)。,第五章 公式與函數(shù),5.9.2用MAX函數(shù)設(shè)置變動(dòng)序號(hào) MAX和MIN函數(shù)是分別求最大值和最小值的函數(shù),它們常在復(fù)雜的數(shù)組公式中出現(xiàn)。 功能:MAX求一組數(shù)中的最大值;MIN求一組數(shù)中的最小值 語(yǔ)法:=MAX(數(shù)值1,數(shù)值2,) =MIN(數(shù)值1,數(shù)值2,) MAX和MIN函數(shù)實(shí)例(P141),第五章 公式與函數(shù),MAX和MIN函數(shù)實(shí)例(P141) 設(shè)置序號(hào),要求: 序號(hào)隨行的刪除可自動(dòng)調(diào)整為新的連續(xù)序號(hào) 在小計(jì)行、合計(jì)行和空行前不加序號(hào),=IF(OR(B6=“,B6=“小計(jì)“,B6=“合計(jì)“),“,MAX($A$1:A5)+1),注意絕對(duì)引用和相對(duì)引用的使用,第五章 公式與函數(shù),5.9.3用LARGE和SMALL實(shí)現(xiàn)銷售數(shù)量自動(dòng)排名 功能:LARGE求一組數(shù)中的第N個(gè)最大值;SMALL求一組數(shù)中的第N個(gè)最小值 語(yǔ)法: =LARGE(一組數(shù)值或單元格區(qū)域,第N個(gè)最大值) =SMALL(一組數(shù)值或單元格區(qū)域,第N個(gè)最小值) LARGE和SMALL函數(shù)實(shí)例,=LARGE(B2:B17,1),=SMALL(B2:B17,1),當(dāng)有兩個(gè)第二大值相等的時(shí)候,一個(gè)會(huì)作為第二大,另一個(gè)作為第三大,第五章 公式與函數(shù),5.10 其他查找引用函數(shù) 查找引用函數(shù)在單元格查詢,數(shù)據(jù)表之間的取數(shù)、核對(duì)方面有著極其廣泛的用途,前面我們學(xué)過(guò)VLOOKUP函數(shù),以下介紹其他查找引用函數(shù) 5.10.1 ROW和COLUMN生成公式變動(dòng)函數(shù) 語(yǔ)法:=ROW(引用的單元格或單元格區(qū)域) =COLUMN (引用的單元格或單元格區(qū)域),如果引用的單元格或單元格區(qū)域省略,返回的為當(dāng)前行號(hào); 如果引用的是一個(gè)單元格,返回的是引用單元格所在的行號(hào)或列號(hào); 如果引用的是一個(gè)單元格區(qū)域,返回的是單元格區(qū)域左上角所在的行號(hào)或列號(hào)。,第五章 公式與函數(shù),5.10 其他查找引用函數(shù) 如果公式在A10單元格,例:=ROW()=?,例:=ROW(C25)=?,例:=ROW(D2:E10)=?,例:=COLUMN()=?,例:= COLUMN(C25)=?,例:= COLUMN(D2:E10)=?,10,25,2,1,3,4,第五章 公式與函數(shù),5.10 其他查找引用函數(shù) ROW和COLUMN函數(shù)實(shí)例,C14=VLOOKUP($B14,$B$2:$F$9,2,0),C15=VLOOKUP($B15,$B$2:$F$9, COLUMN(B1),0),這兩個(gè)公式的區(qū)別在于相對(duì)列數(shù)的不同。第一個(gè)公式直接用數(shù)字2,后面的公式必須要手工來(lái)改相對(duì)列數(shù),這樣不便于公式的復(fù)制;第二個(gè)公式用的是COLUMN(B1),這樣隨著公式向右的拖動(dòng),列發(fā)生改變,函數(shù)返回的值也隨之改變,就可以實(shí)現(xiàn)公式的輕松復(fù)制。,第五章 公式與函數(shù),5.10 其他查找引用函數(shù) ROW和COLUMN函數(shù)實(shí)例,I3=LARGE($F$2:$F$9,1),J3=LARGE($F$2:$F$9,ROW(A1),這兩個(gè)公式的區(qū)別在于第N大值的不同。第一個(gè)公式直接用數(shù)字1,后面的公式必須要手工來(lái)改第N大值,這樣不便于公式的復(fù)制;第二個(gè)公式用的是ROW(A1),這樣隨著公式向下的拖動(dòng),行發(fā)生改變,函數(shù)返回的值也隨之改變,就可以實(shí)現(xiàn)公式的輕松復(fù)制。,第五章 公式與函數(shù),5.10 其他查找引用函數(shù) 5.10.2 MATCH和INDEX實(shí)現(xiàn)雙向查找 MATCH是查詢函數(shù),INDEX是引用函數(shù),在實(shí)際查找并返回值過(guò)程中,MATCH和INDEX總是結(jié)對(duì)出現(xiàn)在公式中。 功能: MATCH返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置 INDEX返回表格、區(qū)域中的數(shù)值或數(shù)值的引用。,第五章 公式與函數(shù),5.10 其他查找引用函數(shù) 5.10.2 MATCH和INDEX實(shí)現(xiàn)雙向查找 語(yǔ)法:=MATCH(查找的值,查找區(qū)域,查找類型),查找的類型為三種:-1,0,1 如果為1,查找小于或等于查找值的最大數(shù)值; 如果為0,查找等于查找值的第一個(gè)數(shù)值; 如果為-1,查找大于或等于查找值的最小數(shù)值; 如果省略,則默認(rèn)為1,在哪兒查找?,要查找的內(nèi)容,第五章 公式與函數(shù),5.10 其他查找引用函數(shù) 5.10.2 MATCH和INDEX實(shí)現(xiàn)雙向查找 語(yǔ)法:=INDEX(區(qū)域,行數(shù),列數(shù)),行數(shù)和列數(shù):是指相對(duì)于該區(qū)域的行數(shù)和列數(shù),而并非相對(duì)整個(gè)工作表的行數(shù)和列數(shù)。,為單元格 區(qū)域或數(shù)組常數(shù),MATCH和INDEX函數(shù)實(shí)例(P144),=INDEX($A$1:$H$6,MATCH(A11,$A$1:$A$6,0),MATCH(B11,$A$1:$H$1,0),第五章 公式與函數(shù),行數(shù),為單元格 區(qū)域或數(shù)組常數(shù),=INDEX($A$1:$H$6,MATCH(A11,$A$1:$A$6,0),MATCH(B11,$A$1:$H$1,0),列數(shù),查找的類型為0,查找等于查找值的第一個(gè)數(shù)值,第一個(gè)結(jié)果是2,表示行數(shù)為2; 第二個(gè)結(jié)果為4,表示列數(shù)為4,在哪兒查找?,要查找的內(nèi)容: 部門、3月費(fèi)用計(jì)劃,第五章 公式與函數(shù),5.10 文本函數(shù) 5.10.1 字符串的查找和截取 功能: LEFT:取左邊的N個(gè)字符 RIGHT:取右邊的N個(gè)字符 MID:根據(jù)指定位置取指定位數(shù)字符 LEN:字符串的字符數(shù) FIND:在指定字符串中查找指定字符的位置 SEARCH:查找特定字符或文

溫馨提示

  • 1. 本站所有資源如無(wú)特殊說(shuō)明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽,若沒(méi)有圖紙預(yù)覽就沒(méi)有圖紙。
  • 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
  • 5. 人人文庫(kù)網(wǎng)僅提供信息存儲(chǔ)空間,僅對(duì)用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對(duì)用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
  • 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請(qǐng)與我們聯(lián)系,我們立即糾正。
  • 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。

最新文檔

評(píng)論

0/150

提交評(píng)論