個人總結(jié)EXCEl實戰(zhàn)技巧

基本功能條件格式:快速比較不同區(qū)域的數(shù)值（一）單元格數(shù)值>>.>”不等于”>>>引用單元格標記不存在人員:“=COUNTIF($F$2:$F$7,$B4)=0”標記重復錄入=COUNTIF($A$1:$A2,$A2)>1快速比較不同區(qū)域的數(shù)值（二）公式>>>>”=OR(EXACT(G21,$H$21:H$30))=FALSE”在工作日歷中突顯周末日:=”WEEKDAY(G40,2)>5”,”=WEEKDAY(G40,2)=6,”=WEEKDAY(G40,2)=7”工作表中活動單元格所在行列高亮顯示“=(ROW()=CELL("row"))+(COLUMN()=CELL("col"))”CELL("ROW")的用途是返回工作表中活動單元格的行號CELL("COL")的用途是返回工作表中活動單元格的列號需要VBA代碼來實現(xiàn)讓工作表在活動單元格發(fā)生改變時執(zhí)行重新計算PrivateSubWorksheet_SelectionChange(ByValTargetAsRange)CalculateEndSub工作表中特定單元格所在行列高亮顯示=($J14="小計")+(J$14="小計")>0上述公式使用的是相對引用，目的是判斷J列與第一行中是否會出現(xiàn)“小計”字樣，如果出現(xiàn)，則在相同的行、列中應用醒目的格式。=($J14="小計")+(J$14="小計")=OR(($J14="小計"),(J$14="小計"))永恒間隔顏色:“=AND(LEN($A2)>0,MOD(ROW(),2)=1”“=MOD(SUBTOTAL(3,B$2:B2),2)=0”日期提醒7前后“=ABS(DATE(YEAR(TODAY()),MONTH($K30),DAY($K30))-TODAY())<=7”特殊數(shù)值提醒單元格數(shù)值>>.>”不等于”>>>引用單元格名稱:1.插入名稱2.使用名稱框3.指定名稱名稱命名的為與不為規(guī)則：1.名稱可以是任意字符與數(shù)字組合在一起，但不能以數(shù)字開頭，更不能以數(shù)字作為名稱，如1ecfo。同時名稱不能與單元格地址相同如,B3。如果要以數(shù)字開頭，可在前面加上下劃線，如_1ecfo。2.不能以字母R,C,r,c作為名稱，因為R,C,在R1C1引用樣式中表示工作表的行，列。3.名稱中不能包含空格，可以用下劃線或者點號代替。4.不能使用除下劃線，點號和反斜號(/)以外的其他符號，允許用問號(？)，但不能作為名稱的開頭，如yhy?就可以，而?yhy就不可以。5.被定義為Print_Titles的區(qū)域?qū)⒊蔀楫斍肮ぷ鞅淼拇蛴〉捻敹藰祟}行和左端標題行。6.被定義為Print_Area的區(qū)域?qū)⒈辉O置為工作表的打印區(qū)域。數(shù)據(jù)有效性:杜絕負數(shù)庫存”=R15<=VLOOKUP(Q15,$Q$3:$R$12,2,0)”此公式的含義是：用單元格數(shù)值與匹配相應產(chǎn)品的庫存數(shù)量比較，只有小于或等于時菜滿足允許輸入銷售數(shù)量的條件。此公式還有作用，如果A列中輸入了庫存表中不存在的產(chǎn)品，也將不允許輸入銷售數(shù)量強制序時錄入”=MAX($V$18:$V18)”輸入的日期必須大于當列中最大的.多級選擇錄入“=$Z$6:$AD$6”上級(標題)“=OFFSET($Y$7,,MATCH($AF7,$Z$6:$AD$6,),COUNTA(OFFSET($Y$7,,MATCH($AF7,$Z$6:$AD$6,),100)))”分級(內(nèi)容)COUNTA(OFFSET(A1,,,10,2))相當于計數(shù)A1:B10分解:A1移0行0列的10行2列數(shù)據(jù)在單元格中創(chuàng)建下拉列表快速切換有效性的來源:”=OFFSET(List.1,,A6-1)”E6為list編號,共三列LIST.1 List.1 List.2 List.3甲 甲 A 1 乙 B 2 丙 C 3 丁 D 4 戊 E 5 己 F 6 庚 G 7 辛 H 8 壬 I 9 癸 J 10動態(tài)源的數(shù)據(jù)有效性”=INDIRECT("B"&5)相當于B5的值.=INDIRECT("A10")始終對單元格A10進行引用1.圖表方面:A.定義名稱:date=”INDIRECT("AutoData!$B$2:$B$"&AutoData!$E$15)”Sale=”INDIRECT("AutoData!$C$2:$c$"&AutoData!$E$15)B.設置水平滾動條(窗體)>>>>設置控件格式>>>控制C.設置數(shù)據(jù)源:2.單元格方面:CODE與CHAR相互轉(zhuǎn)換A設置顯示單元格=”DEAN”B.定義名稱DEAN”=INDIRECT("Sheet1!$"&CHAR(Sheet1!$L$4)&"$4")”數(shù)據(jù)源在同行不同列或定義名稱GUANG=INDIRECT("Sheet1!$B"&"$"&Sheet1!$M$4)數(shù)據(jù)源在同列不同行C.設置水平滾動條(窗體)>>>>設置控件格式>>>控制限制重復值的錄入“=COUNTIF($A$2:$B$11,M9)=1”針對雙關鍵字限制重復值“=SUM(N($M27&"|"&$N27=$M$27:$M$36&"|"&$N$27:$N$36))=1”在合并兩個單元格的內(nèi)容時增加了字符"|"，目的是為了避免因兩個條件直接組合而出現(xiàn)本不應該出現(xiàn)的雷同。如業(yè)務員“李五”和客戶“光商場”的組合，與業(yè)務員“李五光”和客戶“商場”的組合。數(shù)據(jù)分析排序與篩選規(guī)則：要先排序優(yōu)先級較低的，或排序排序優(yōu)先級最高的。對于相同比劃數(shù)的漢字，EXCEL按照其內(nèi)碼順序排列，而不是按照比劃順序進行的排列。篩選，自動篩選；工具，保護，保護工作表，勾選“使用自動篩選”選項。排序的常見的故障1.沒有正確選擇數(shù)據(jù)區(qū)域，而自動選擇的區(qū)域中包含有空格。2.內(nèi)存不足：增加電腦的物理內(nèi)存；優(yōu)化電腦的性能：如關閉暫時不需要的其他程序，刪除臨時文件；減少排序的數(shù)據(jù)區(qū)域。3.數(shù)據(jù)區(qū)域中包含有格式化為文本的數(shù)字。4.排序區(qū)域包含合并單元格。分類匯總使用分類匯總功能以前，必須先對數(shù)據(jù)列表中需要進行分類的字段進行排序，本表是已經(jīng)排序后的狀態(tài)！F5定位條件選定可見單元格1.兩次分類匯總，第二次取消替除當前分類匯總說明：分類匯總的實質(zhì)是，EXCEL為數(shù)據(jù)列表創(chuàng)建匯總項，并使用分級顯示視圖，當匯總要求較簡單時，它不失為分析數(shù)據(jù)的得力工具。但如果數(shù)據(jù)列表較龐大，并且匯總要求較為復雜時，更好的分類匯總工具是數(shù)據(jù)透視表。多層次的數(shù)據(jù)瀏覽選中數(shù)據(jù)區(qū)域“數(shù)據(jù)”——“組與分級顯示”——“自動建立分級顯示”。取消：“數(shù)據(jù)”——“組與分級顯示”——“清除分級顯示”。在受保護的工作表中調(diào)整分級顯示視圖PrivateSubWorkbook_Open()Worksheets("分類匯總2").ProtectPassword:="pwd",userinterfaceonly:=TrueWorksheets("分類匯總2").EnableOutlining=TrueEndSub數(shù)據(jù)透視表用二維表創(chuàng)建數(shù)據(jù)透視表快速統(tǒng)計重復項目在數(shù)據(jù)透視表種添加計算項自動刷新數(shù)據(jù)透視表方法1.工作簿打開時刷新透視表：“數(shù)據(jù)透視表選項”。方法2.單個透視表的刷新：“查看代碼”VBA代碼privatesubworksheet_activate()activesheet.pivottables"數(shù)據(jù)透視表4".pivotcache.refreshendsub注意“"數(shù)據(jù)透視表4"”不是指的工作表，是透視表的名字（在“表選項”）函數(shù)統(tǒng)計求和統(tǒng)計選定區(qū)域數(shù)據(jù)的個數(shù)=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},$A2))find函數(shù)用于查找數(shù)字字符在單元格中是否存在；count函數(shù)用于統(tǒng)計引用區(qū)域或數(shù)組中數(shù)值的個數(shù)。=IF(COUNTA($A22:$D22)=4,"完畢","")=IF(COUNTBLANK($A23:$D23),"","完畢")認識COUNTIF函數(shù)公式 含義=COUNTIF(A41:A59,8) 數(shù)值8的單元格個數(shù)，文本008也算=COUNTIF(A41:A59,">8") 數(shù)值大于8的單元格個數(shù)=COUNTIF(A41:A59,">=8") 數(shù)值大于等于8的單元格個數(shù)=COUNTIF(A41:A59,">"&A5) 數(shù)值大于A5的值8的單元格個數(shù)=COUNTIF(A41:A59,"<>8") 不等于8(含文本008)的所有單元格個數(shù)=COUNTIF(A41:A59,"<>") 非真空單元格個數(shù)，相當于COUNTA=COUNTIF(A41:A59,"<>""") 區(qū)域內(nèi)所有單元格個數(shù)=COUNTIF(A41:A59,"=") 真空單元格個數(shù)=COUNTIF(A41:A59,"") 真空及空文本（假空）單元格個數(shù)=COUNTIF(A41:A59,"><") 非空文本單元格個數(shù)=COUNTIF(A41:A59,"*") 文本（含空文本）單元格個數(shù)=COUNTIF(A41:A59,"*8*") 包含字符8的文本單元格個數(shù)=COUNTIF(A41:A59,"a?") 以a開頭且只有兩個字符的單元格個數(shù)=COUNTIF(A41:A59,"?B*") 第2個字符為b的單元格個數(shù)=COUNTIF(A41:A59,A10&"*") 以A10單元格字符開頭的單元格個數(shù)=COUNTIF(A41:A59,"??") 字符長度為2的文本單元格個數(shù)=COUNTIF(A41:A59,TRUE) 內(nèi)容為邏輯值TRUE的單元格個數(shù)=COUNTIF(A41:A59,#DIV/0!) 被0除錯誤的單元格個數(shù)=COUNTIF(A41:A59,"#DIV/0!") 被0除錯誤的單元格個數(shù)認識SUMIF函數(shù)公式 含義 =SUMIF(A2:A10,"A001",C2:C10) 匯總工號為"A001"的銷售量 =SUMIF(A2:A10,"A001",C2) 簡寫方式，匯總工號為"A001"的銷售量 =SUMIF(A2:A10,A2,C2:C10) 匯總工號為A2單元格的銷售量 =SUMIF(A2:A10,"A001",C3) 簡寫方式，匯總工號為"A001"的銷售量，其中A2對應C3、A3對應C4……A10對應C11 =SUMIF(A2:A10,"A*",C2:C10) 匯總工號以"A"開頭的銷售量 =SUMIF(B2:B10,"?珠*",C2:C10) 匯總商品名稱第2個字為"珠"的銷售量 單字段多條件求和單日最高銷售量為：“=MAX(SUMIF(A190:A198,A190:A198,B190:B198))”單日銷售最高的日期：”=INDEX(A190:A198,MATCH(D190,SUMIF(A190:A198,A190:A198,B190),0))”數(shù)組 使用通配符模糊求和SUMIF(A217:A224,B228&"*"&C228&"*",B217:B224)B228和C228是包含的關鍵字乘法口決表(混合引用)=IF(B$1>$A2,"",B$1&"*"&$A2&"="&B$1*$A2)查找與引用VLOOKUPVLOOKUP的模糊查找；且range_lookup為TRUE(1)，則使用小于等于lookup_value的最大值VLOOKUP的數(shù)組查找?！?VLOOKUP(A29,{0,"差";60,"中";80,"良";90,"優(yōu)"},2,TRUE)”VLOOUPIF綜合應用:“=IF($B50<=$E$36,0,($B50-$E$36)*VLOOKUP(($B50-$E$36),$C$38:$E$46,2,1)-VLOOKUP(($B50-$E$36),$C$38:$E$46,3,1))”INDEX；MATCH；ADDRESS“=INDEX(E77:G85,MATCH(B78,D77:D85,0),MATCH(B79,E76:G76,0))”“=ADDRESS(MATCH($B78,$D$76:$D$85,0),MATCH($B79,$E$76:$G$76,0)+4)”=HYPERLINK("#LINK1","定位")LINK1為名稱,”定位”為標簽返回最后一條數(shù)據(jù)記錄”=LOOKUP(G197,IF(A196:A204=G196,B196:D204))”在數(shù)據(jù)清單中隔行（列）插入一個空行:“=IF(MOD(ROW(),2)=0,"",OFFSET(Sheet1!$A$1,INT(ROW()/2),COLUMN()-1))”在數(shù)據(jù)清單中隔行（列）插入兩個空行“=IF(MOD(ROW(),3)=0,"",(IF(MOD(ROW(),3)=2,"",OFFSET(Sheet1!$A$1,INT(ROW()/3),COLUMN()-1))))”特別函數(shù):=A2&REPT(“#”,16-LEN(A2))A2結(jié)尾處用”#”號填充至16位=REPT(“#”,16-LEN(A2))&A2A2左側(cè)用”#”號填充至16位=REPT("#",8-LEN(A2)/2)&A2&REPT("#",16-LEN(A2)/2)A2側(cè)用”#”號填充至16位TEXT(A1,”AAAA”)日期星期星期幾查找?或*前面要加~DATEDIF(A1,TODAY(),”Y”))計算過去某一天至今的時間DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1)當月天數(shù)30或31ATL+;復制當前選中的顯示區(qū)域ATL+ENTER批量文本格式至數(shù)值>>>>>批量加上0即可轉(zhuǎn)換自定義顯示格式變成值>>>CTRL+C兩次>>>全部粘貼>>>編輯/選擇性粘貼>>>文本簡單數(shù)組公式(CTRL+ATL+ENTER)計算數(shù)學大于50小于60的人數(shù)(C列是數(shù)學成績){=SUM((C2:C15>=50)*(C2:C15<=60))}{=FREQUENCY(C2:C15,{50,60,70,80,100})}排名函數(shù)分數(shù) 100 100 90 94 98 94 93排名 1 1 5 3 2 3 4{=SUM(IF($B$1:$H$1<=B1,"",1/(COUNTIF($B$1:$H$1,$B$1:$H$1)