1、EXCEL講師小組EXCEL技能培訓(xùn)1234小技巧&函數(shù)數(shù)據(jù)透視表簡單SQLVBA入門課程內(nèi)容小技巧&函數(shù)Q1:條件格式使用小技巧&函數(shù)這是已經(jīng)設(shè)置好的小技巧&函數(shù)根據(jù)自我需求，自己制作小技巧&函數(shù)Q2:凍結(jié)窗口遇到什么難題哦小技巧&函數(shù)Q3:如何粘貼數(shù)據(jù)不走樣小技巧&函數(shù)小技巧&函數(shù)Q4:計(jì)算空格時(shí)顯示#DIV/0！有什么辦法不顯示#DIV/0函數(shù)IFERROR0102定位小技巧&函數(shù)可根據(jù)數(shù)據(jù)變化可適用面積大小技巧&函數(shù)Q5:如何查找重復(fù)數(shù)據(jù)查找替換countif數(shù)據(jù)透視刪除重復(fù)項(xiàng)1234Ctrl+C復(fù)制Alt+D+P透視向?qū)trl的妙用其它快捷鍵Ctrl+V粘貼Ctrl+X剪切Ctrl

2、+A選擇全部Ctrl+F查找Ctrl+Z撤銷上一步Ctrl+G定位Ctrl+Shift+ 快速選擇范圍F1幫助F4 重復(fù)上一步操作 /引用切換鍵精選1-常用快捷鍵小技巧&函數(shù)為什么要使用數(shù)據(jù)有效性？1、數(shù)據(jù)錄入規(guī)范化；2、簡化錄入一、啟用數(shù)據(jù)有效性的方法1、依次單擊【數(shù)據(jù)】【數(shù)據(jù)有效性】，如圖所示122、【數(shù)據(jù)有效性】對話框界面精選2-數(shù)據(jù)有效性小技巧&函數(shù)二、制作【數(shù)據(jù)有效性】1234567數(shù)據(jù)有效性小技巧&函數(shù)數(shù)據(jù)有效性三、使用【數(shù)據(jù)有效性】小技巧&函數(shù) 18 一：選中整個(gè)表單，右擊設(shè)置單元格格式 取消“鎖定”二：選中需保護(hù)的區(qū)域，右擊設(shè)置單元格格式， 單擊“鎖定” 需保護(hù)區(qū)精選3-工作表

3、保護(hù)12小技巧&函數(shù) 19 數(shù)學(xué)計(jì)數(shù)類查找與引用邏輯函數(shù)文本函數(shù)Sum/ Sumif / SumifsCount/Counta/Countif/CountifsAverage/averageif/averagesMin/maxIf、Iferror、And、OrLen、Left、Right、Mid、Text、Value精選4-常用函數(shù)分類Today、Month、Year、DatedifVlookup、Column、Row、match、offset日期函數(shù)小技巧&函數(shù) 20 SUMIFS 函數(shù)功能對區(qū)域中滿足多個(gè)條件的單元格求和。語法SUMIFS(sum_range, criteria_range

4、1, criteria1, criteria_range2, criteria2, )參數(shù)sum_range 必需。對一個(gè)或多個(gè)單元格求和，包括數(shù)值或包含數(shù)值的名稱、區(qū)域或單元格引用，忽略空白和文本值。criteria_range1 必需。在其中計(jì)算關(guān)聯(lián)條件的第一個(gè)區(qū)域。criteria1 必需。條件的形式為數(shù)字、表達(dá)式、單元格引用或文本，可用來定義將對 criteria_range1 參數(shù)中的哪些單元格求和。criteria_range2, criteria2, 可選。附加的區(qū)域及其關(guān)聯(lián)條件。最多允許 127 個(gè)區(qū)域/條件對。示例 求貨品等級A品種芒果的銷售金額 =SUMIFS($D:$D,

5、B:B,”A”,A:A,”芒果”)=126 多條件求和函數(shù)小技巧&函數(shù) 21 查找引用函數(shù)VLOOKUP 函數(shù)功能在表格或數(shù)值數(shù)組 的首列查找指定的數(shù)值，并在表格或數(shù)組中指定列的同一行中返回一個(gè)數(shù)值。語法VLOOKUP(lookup_value,table_array,Col_index_num,range_lookup)參數(shù)Lookup_value 為需要在數(shù)據(jù)表第一行中進(jìn)行查找的數(shù)值。Lookup_value 可以為數(shù)值、引用或文本字符串。Table_array 為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表。使用對區(qū)域或區(qū)域名稱的引用Col_index_num 為 table_array 中待返回的匹配值

6、的列序號。Range_lookup 為一邏輯值，指明函數(shù) VLOOKUP 查找時(shí)是精確匹配，還是近似匹配。【備注：TRUE（可用任意“非0”值替代） 或省略：則返回小于 lookup_value 的最大數(shù)值。Table_array的首行必須按升序排列。FALSE（可用“0”替代）：函數(shù) VLOOKUP 將查找精確匹配值，如果找不到，則返回錯(cuò)誤值 #N/A。】示例 如果G2單元格變成了芒果那么值會(huì)變成什么？往右3位查找西瓜的銷售單價(jià)G2單元格=VLOOKUP(F2,$A:$D,3,0)結(jié)果是3Vlookup正確使用的前提之一是所找的列里沒有重復(fù)項(xiàng)小技巧&函數(shù) 22 字符串函數(shù)MID 函數(shù)功能MI

7、D 返回文本字符串中從指定位置開始的特定數(shù)目的字符語法MID(text,start_num,num_chars)參數(shù)Text 是包含要提取字符的文本字符串。Start_num 是文本中要提取的第一個(gè)字符的位置。文本中第一個(gè)字符的 start_num 為 1，以此類推。Num_chars 指定希望 MID 從文本中返回字符的個(gè)數(shù)。示例小技巧&函數(shù) 23 小技巧&函數(shù)邏輯函數(shù)IF 函數(shù)功能如果指定條件的計(jì)算結(jié)果為 TRUE，IF 函數(shù)將返回某個(gè)值；如果該條件的計(jì)算結(jié)果為 FALSE，則返回另一個(gè)值。語法IF(logical_test, value_if_true, value_if_false)參

8、數(shù)logical_test 必需。計(jì)算結(jié)果可能為 TRUE 或 FALSE 的任意值或表達(dá)式value_if_true 可選。logical_test 參數(shù)的計(jì)算結(jié)果為 TRUE 時(shí)所要返回的值value_if_false 可選。logical_test 參數(shù)的計(jì)算結(jié)果為 FALSE 時(shí)所要返回的值示例 把貨品等級為A定義為“好”，其它定義為“一般” 24 日期函數(shù)Datedif 函數(shù)功能可以計(jì)算兩個(gè)日期間的間隔，它能夠顯示天數(shù)、月數(shù)語法=DATEDIF(第一日期,第二日期,“差異)參數(shù)第一日期/兩個(gè)日期中較早的一個(gè)日期第二日期/兩日期中距今天最近的日期差異/d or m示例見excel小技巧

9、&函數(shù)1234小技巧&函數(shù)數(shù)據(jù)透視表簡單SQLVBA入門課程內(nèi)容 26 創(chuàng)建數(shù)據(jù)透視表的三步法數(shù)據(jù)透視表第一步：選中透視對象，單擊“插入數(shù)據(jù)透視表”。1234 27 創(chuàng)建數(shù)據(jù)透視表的三步法數(shù)據(jù)透視表第二步：創(chuàng)建數(shù)據(jù)透視表。注意點(diǎn)1、數(shù)據(jù)源的選擇：可以是區(qū)域，也可以是幾個(gè)整列（小貼士：源數(shù)據(jù)要更改刷新的，建議選擇幾列）。2、數(shù)據(jù)透視表的位置：可以和源數(shù)據(jù)放一起，也可以新建工作簿。123 28 創(chuàng)建數(shù)據(jù)透視表的三步法數(shù)據(jù)透視表第三步：創(chuàng)建數(shù)據(jù)透視表字段。按需求將字段移入的方框中 29 數(shù)據(jù)透視表功能簡介（值匯總選項(xiàng)）數(shù)據(jù)透視表 30 數(shù)據(jù)透視表功能簡介（值顯示方式）數(shù)據(jù)透視表 31 作用：一些簡單

10、運(yùn)算可以通過在數(shù)據(jù)透視表中進(jìn)行，使得一些重復(fù)制作的報(bào)表刷新更快。范例：將銷售元轉(zhuǎn)化為萬元。數(shù)據(jù)透視表功能簡介（計(jì)算字段）數(shù)據(jù)透視表123第一步：選中數(shù)據(jù)透視表中任何一單元格，單擊“開始插入插入計(jì)算字段 32 范例：將銷售元轉(zhuǎn)化為萬元。數(shù)據(jù)透視表功能簡介（計(jì)算字段）數(shù)據(jù)透視表第二步：填入需要計(jì)算的字段名稱與公式。12345注意點(diǎn)1、計(jì)算的字段名稱與原字段名稱不可重復(fù)；2、公式填寫不僅可以用加減乘除，還可使用函數(shù)。 33 范例：將銷售元轉(zhuǎn)化為萬元。數(shù)據(jù)透視表功能簡介（計(jì)算字段）數(shù)據(jù)透視表第三步：結(jié)果如圖所示： 34 數(shù)據(jù)透視表應(yīng)用小技巧（數(shù)據(jù)透視表函數(shù)）數(shù)據(jù)透視表數(shù)據(jù)透視表與GETPIVOTDAT

11、A函數(shù)的使用GETPIVOTDATA函數(shù)是配合數(shù)據(jù)透視表運(yùn)用的函數(shù)，編寫簡單，配合相對引用和絕對引用范例：根據(jù)提供的源數(shù)據(jù)配合使用GETPIVOTDATA函數(shù) 各水果的品種等級分布。數(shù)據(jù)源結(jié)果圖 35 數(shù)據(jù)透視表應(yīng)用小技巧（數(shù)據(jù)透視表函數(shù)）數(shù)據(jù)透視表用IFERROR函數(shù)去除引不到亂碼鎖行鎖列注意點(diǎn) GETPIVOTDATA函數(shù)不需要手工寫入，直接寫”=“點(diǎn)擊透視表中要引用的數(shù)據(jù)（如匯總）任何一個(gè)單元格+Enter,即可生成函數(shù)。1234小技巧&函數(shù)數(shù)據(jù)透視表簡單SQLVBA入門課程內(nèi)容37+=select 片區(qū),銷售公司名稱,經(jīng)銷商簡稱,經(jīng)銷商進(jìn)貨數(shù)量,經(jīng)銷商庫存數(shù)量,0 as 兩周銷售 fr

12、om C:UserssonyDesktopEXCEL中級-信息主管經(jīng)銷商進(jìn)銷存.xlsx.經(jīng)銷商進(jìn)銷存$ union all select 片區(qū)名稱 as 片區(qū),銷售公司名稱,經(jīng)銷商簡稱,0 as 經(jīng)銷商進(jìn)貨數(shù)量,0 as 經(jīng)銷商庫存數(shù)量,銷售數(shù)量 as 兩周銷售 from C:UserssonyDesktopEXCEL中級-信息主管經(jīng)銷商銷售明細(xì)表.xlsx.經(jīng)銷商銷售明細(xì)表$核心內(nèi)容1234小技巧&函數(shù)數(shù)據(jù)透視表簡單SQLVBA入門課程內(nèi)容貨 號B1201034B1201026B1201018銷售排名123產(chǎn)品圖片4041step 1:操作代碼42網(wǎng)絡(luò)（度娘、EXCEL HOME.)EXC

13、EL開發(fā)人員自己編寫（書籍）For Each oSlide In ActivePresentation.SlidesFor Each oShape In oSlide.ShapesSet oTxtRange = oShape.TextFrame.TextRange宏批量導(dǎo)入圖片/操作代碼43step 2:存放位置44宏批量導(dǎo)入圖片/保存位置145宏批量導(dǎo)入圖片/保存位置246step 3:如何調(diào)用47宏批量導(dǎo)入圖片/創(chuàng)建按鈕148宏批量導(dǎo)入圖片/創(chuàng)建按鈕249Sub 圖片() Dim i, c, r As Long, strImgPath As String strImgPath = C:UserssonyDesktopEXCEL中級-信息主管產(chǎn)品圖片庫 圖片路徑 c = ActiveCell.Column 貨號列 r = ActiveCell.Row 貨號行 i = r While Trim(Cells(i, c).Text) 貨號不為空繼續(xù) If Cells(i, c) 0 Then ActiveSheet.Pictures.Insert(strImgPath & Trim(Cells(i, c).