EXCEL在日常工作中的應(yīng)用.pptVIP

,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á)式,打開(kāi)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ù),求和范圍,條件,條件范圍,打開(kāi)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ù),條件,條件范圍,打開(kāi)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,打開(kāi)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),打開(kāi)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ù),引用類型,文本字符串,打開(kāi)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ù)字,打開(kāi)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ù),打開(kāi)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,打開(kāi)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:查找特定字符或文