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

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