PAGEPAGE27目錄一、函數(shù)1、SUBSTITUTE函數(shù) 32、如何不使顯示或打印出來的表格中包含有0值 33、將阿拉伯數(shù)字轉(zhuǎn)換為大寫金額 44、多條件數(shù)據(jù)統(tǒng)計 45、相同格式多工作表求和 56、如何判斷單元格里是否包含指定文本？ 57、求某一區(qū)域內(nèi)不重復的數(shù)據(jù)個數(shù) 58、分割文本 69、簡單判斷最后一位是數(shù)字或字母 610、多列數(shù)據(jù)合為一列 7、根據(jù)身份證號提取出生日期 712、多工作表中相同單元格反映在一張工作表中 813、將班次變?yōu)槲谋?814、隔行求和問題 815、在單元格中批量插入固定字符 816、用函數(shù)將輸入的月份轉(zhuǎn)換為本月所包含的天數(shù) 817、在EXCEL中如何統(tǒng)計字數(shù) 918、如何定義有效數(shù)字 919、根據(jù)錄入搜索對應的內(nèi)容 920、用SUMIF函數(shù)進行條件求和，不限于一個條件 1021、在excel中將已有的數(shù)值前加零變成六位 1022、求每個月有幾個星期日、一、二、三?六 1023、絕對值函數(shù)ABS 1024向下舍入取整函數(shù)INT 1125、分類匯總函數(shù)SUBTOTAL 1126、日期計算函數(shù)1127、星期函數(shù)1228、計算成績名次函數(shù) 1229WORKDAY函數(shù)(Excel2007適用) 1330HLOOKUP函數(shù)與VLOOKUP函數(shù) 1331超鏈接函數(shù)HYPERLINK 1332合并字符串函數(shù)1433、將數(shù)字轉(zhuǎn)換為美元貨幣的函數(shù)DOLLAR 1434將數(shù)字轉(zhuǎn)換為人民幣的函數(shù)RMB 1435、自動轉(zhuǎn)換大小寫函數(shù)PROPER 1536、文本替換函數(shù)REPLACE 1537、統(tǒng)計空白單元格的個數(shù)函數(shù)COUNTBLANK 1538利用函數(shù)查詢費率 16二、技巧1控制每列數(shù)據(jù)的長度并避免重復錄入 172把B列與A列同一行數(shù)據(jù)的不同之處標識出來 173快速在一個工作薄中建立多個工作表 174當前日期與時間的快速輸入 185對多張工作表進行操作 186數(shù)字批量加注單位 187文字批量加注單位 188保護我們的excel 199、在EXECL中建立下拉列表按鈕 2010快速填充數(shù)據(jù) 20在已有的單元格中批量加入一段固定字符 2012在同一對應單元格內(nèi)輸入相同內(nèi)容 2113EXCEL中行列互換 2114輸入平方立方 2115、用Excel為多頁表格 2116不打印單元格中的顏色與底紋 2117、在EXCEL中如何統(tǒng)計字數(shù) 22運用選擇性粘貼批量運算 2219、工作表的標簽的字體和大小可以更改嗎 2220、繪制有三條斜線的表頭 2221、自動調(diào)整小數(shù)點 2222、快速打開所需工作表2223、讓數(shù)值完全或部分替換公式 2324、在Excel中實現(xiàn)滾屏時固定顯示表格頭 2325、去除“最近使用過的工作簿”列表 2326、Excel工作表大綱的建立 2327插入“圖示” 2428“照相機”功能的妙用 2429、建立“常用文檔”新菜單 2530、為工作表添加的背景 2531、快速填入相同內(nèi)容 2632、快速復制向上單元格 2633、快速輸入系統(tǒng)日期 2634、輕松實現(xiàn)多行文本輸入 2635、使用Excel中的“宏” 2636、分區(qū)域鎖定EXCEL工作表 2737、另類錄入技巧 2738、宏代碼 2739、讓數(shù)據(jù)按需排序 2840、雙擊格式刷的妙用 2841、將Excel單元格區(qū)域轉(zhuǎn)換為圖片 2842、在工作表之間使用超級連接 29一、函數(shù)1、SUBSTITUTE函數(shù)（1、求A1"i"的次數(shù)：=LEN(A1)-LEN(SUBSTITUTE(A1,"i",""))如圖1：、求字符串長度=LEN(SUBSTITUTE(B3,"I",""))如圖2：、用“S”替換字符串中的“i”=SUBSTITUTE(A1,"i","S")如圖3：圖1圖2 圖32、如何不使顯示或打印出來的表格中包含有0值00=IF(B2*B3=0,"",B2*B3)4：意思為：若B2*B3=0,則單元格為空，否則顯示B2*B3。當然，B2*B3也可以換成其他公式，要以具體問題而定。圖43、將阿拉伯數(shù)字轉(zhuǎn)換為大寫金額A2佰數(shù)字,B2額(含元角分),在B2單元格5：圖5=IF((INT(A5*10)-INT(A5)*10)=0,TEXT(INT(A5),"[DBNum2]G/通用格式")&"元"&IF((INT(A5*100)-INT((A5)*10)*10)=0," 整 "," "&TEXT(INT(A5*100)-INT(A5*10)*10,"[DBNum2]G/通用格式")&"分"),TEXT(INT(A5),"[DBNum2]G/ 通用格式")&" "&IF((INT(A5*100)-INT((A5)*10)*10)=0,TEXT((INT(A5*10)-INT(A5)*10),"[DBNum2]G/用格式")&"角整",TEXT((INT(A5*10)-INT(A5)*10),"[DBNum2]G/通用格式")&"角"&TEXT(INT(A5*100)-INT(A5*10)*10,"[DBNum2]G/通用格式")&"分"))4、多條件數(shù)據(jù)統(tǒng)計假定第一行為表頭，ABCA需要顯示結(jié)果的單元格內(nèi)輸入如下公式：=SUM(IF((A2:A1000="京開高速")*(B2:B1000="")*(C2:C1000="A"),1,0))輸入完公式后按Ctrl+Shift+Enter動加上數(shù)組公式符號"{}"。如圖6： 圖65、相同格式多工作表求和SHEET101A1輸入公式：=SUM('SHEET1:SHEET100'!A1)然后按回車。如圖7：圖76、如何判斷單元格里是否包含指定文本？假定對A1有無"指定文本",以下任一公式均可:=IF(COUNTIF(A1,"*"&"指定文本"&"*")=1,"有","無")=IF(ISERROR(FIND("指定文本",A1,1)),"無","有")如圖8：圖87、求某一區(qū)域內(nèi)不重復的數(shù)據(jù)個數(shù)A1:A69：一是利用數(shù)組公式：=SUM(1/COUNTIF(A1:A6,A1:A6))Ctrl+Shift+Enter二是利用乘積求和函數(shù)：=SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6))注：公式中的A1:A6應根據(jù)實際情況而定。圖98、分割文本例：有一列數(shù)據(jù)，全部是郵箱的，現(xiàn)在想將@前面的賬號與@后面的域名分割開，分為兩列。“@”前部分：=LEFT(A1,FIND("@",A1)-1)“@”后部分：=RIGHT(A1,LEN(A1)-FIND("@",A1))如圖10：圖109、簡單判斷最后一位是數(shù)字或字母假設需判斷A1將公式=IF（ISNUMBER（--RIGHT（A1，1），"數(shù)字"，"字母"）或=I（ISER（RIGHA）*，字母數(shù)字）錄到其它任意單元格內(nèi)即可判斷10、多列數(shù)據(jù)合為一列A、B、CB1A2C1A3將公式圖11=INDIRECT("r"&INT((ROW()-1)/3)&"c"&MOD(ROW()-1,3)+1,0)如圖11：11、根據(jù)身份證號提取出生日期在單元格內(nèi)錄入以下公式15位、18位均適應：=I（LE（A=1DTMI（A4MI（A112MIA13，2I（LEA）=1DTMIA，MIA，，，MIA，1，2），"錯誤身份證號"）將單元格格式設為日期格式。如圖12圖1212、多工作表中相同單元格反映在一張工作表中=INDIRECT("Sheet"&ROW()-2&"!$B$4")如圖13：注：工作簿中的工作表名字應按一定順序排列indirect引用的函數(shù)row()是取當前行號。例如在a1，公式里的值變?yōu)閕ndirect("sheet2!a1"),＝sheet2!a1a2indirect("sheet3!a1"圖1313、將班次變?yōu)槲谋?IF(B:B="A","大夜","")&IF(B:B="B","早班","")&IF(B:B="C","中班","")&IF(B:B="D","小夜","")14、隔行求和問題假設a1至t1為數(shù)據(jù)（共有20列=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1))ctrl＋shift＋enter每隔三行之和。15、在單元格中批量插入固定字符24插入舊身份證號碼中，可以這樣做（號碼保存在A列中：在B1、C1、D1單元格中分別輸入公式：=LEFT(A1,6)、=RIGHT(A1,9)、=B1&"19"&C1，B、C、D19D16、用函數(shù)將輸入的月份轉(zhuǎn)換為本月所包含的天數(shù)假設A1單元格為月份：＝TEXT((DATE(YEAR(NOW()),A1+1,1)-1),"d")或：=DAY(DATE(YEAR(NOW()),A1+1,0))17、在EXCEL中如何統(tǒng)計字數(shù)A1A6公式：=SUM(LEN(A1:A6))錄完后按Ctrl+Shift+Enter鍵,讓它自動加上數(shù)組公式符號"{}"如圖14：18

圖140.00236660.0023。0.23660.23。用函數(shù)可如下:=FLOOR(A1,SIGN(A1)*10^(INT(LOG(ABS(A1)))-1)),+/-小數(shù)有效,0無效.用-1可調(diào)節(jié)取數(shù)位。19、根據(jù)錄入搜索對應的內(nèi)容B3A4A1：A3在B4單元格內(nèi)錄入公式圖15=IF(A4=0,"",VLOOKUP(A4,A1:C3,2,FALSE)；C21單元格公式“=IF(A4=0,"",VLOOKUP(A4,A1:C3,3,FALSE))”20、用SUMIF函數(shù)進行條件求和，不限于一個條件假設A列為名稱、B列為數(shù)量：=SUMIF(A:A,"京開",B:B)+SUMIF(A:A,"京石",B:B)+SUMIF(A:A,"六環(huán)",B:B)21、在excel中將已有的數(shù)值前加零變成六位＝RIGHT("00000"&A1,6)22、求每個月有幾個星期日、一、二、三?六將公式輸入到制定單元格，向右復制、向下復制。錄完后按ctrl+shift+Enter入數(shù)組。如圖16圖16=SUM(IF(WEEKDAY(DATE($A2,$B2,ROW(INDIRECT("$A$1:$A$"&DAY(DATE($A2,$B2+1,1)-1)))))=COLUMN()-2,1))}，23、絕對值函數(shù)ABSA1B1120、180，那么如果要求A1B1可以在C1如圖17圖1724、向下舍入取整函數(shù)INT例如要計算顯示器和硬盤的購買數(shù)量，可以在D3單元格中輸入以下公式：=INT(C3/B3)。如圖18圖1825、分類匯總函數(shù)SUBTOTAL分類匯總函數(shù)SUBTOTAL41、9月單日最高收費額：在元 格 E4 中 輸 =SUBTOTAL(4,B3:B32)2、9月單日最低收費額：在元 格 E5 中 輸 =SUBTOTAL(5,B3:B32)3、9月總收費額：在單元格E6中輸入=SUBTOTAL(9,B3:B32)如圖19圖1926、日期計算函數(shù)DATEIFDATEIF計算它們之間相差的年數(shù)、月數(shù)或者天數(shù)。參數(shù)可以是："y":計算周年"m":計算足月"d":計算天數(shù)"ym":剩余的足月"yd":剩余的天數(shù)"md":剩余的天數(shù)A2A3C2=DATEDIF(A2,A3,"Y")，計算相差的整月則輸入=DATEDIF(A2,A3,"m") 20如圖2027、星期函數(shù)WEEKDAY此函數(shù)可以返回某個日期為星期幾1或省略數(shù)字1（星期日）到數(shù)字7（星期六2數(shù)字1（星期一）到數(shù)字7（星期日。3數(shù)字0（星期一）到數(shù)字6（星期日例如可以輸入：=WEEKDAY(A1,2)28、計算成績名次：CHOOSE函數(shù)利用CHOOSE函數(shù)可以計算成績名次，在L4單元格中輸入以下公式：=CHOOSE(IF(K4>=95,1,IF(K4>=90,2,IF(K4>=85,3,IF(K4>=80,4,5)))),"","一星")”圖2129、WORKDAY函數(shù)(Excel2007適用)（起始日期期的日期值，工作日不包括周末和專門指定的日期。例如：給定一項工作，要求在50（其中不包括3WORKDAY“=WORKDAY(B1,B2,B3:B5)”。30、HLOOKUP函數(shù)與VLOOKUP函數(shù)HLOOKUPVLOOKUP數(shù)同為數(shù)據(jù)查找函數(shù)，只不過他們兩者的查找方式不但不相1122：=HLOOKUP("張三",A1:C4,2,FALSE)要查詢張三下一個是誰，公式為 =VLOOKUP(" 張 ",A1:C4,2,FALSE)圖2231、超鏈接函數(shù)HYPERLINK超鏈接函數(shù)HYPERLINK不常被用到，但功能卻很強大，如我們想間一個查詢界面，如圖23，可以輸入如下公式如圖23圖23=HYPERLINK("","歡迎進入百度")=HYPERLINK("/","歡迎登陸業(yè)務平臺（錄入專用）")=HYPERLINK(":8000/","歡迎登陸業(yè)務平臺（查詢專用）")32、合并字符串函數(shù)CONCATENATECONCATENATE并字符串，用法為=CONCATENATE（A2,B2）如圖24圖2433、將數(shù)字轉(zhuǎn)換為美元貨幣的函數(shù)DOLLARDOLLAR換為美元貨幣形式25A1=DOLLAR(A1,2)將A1 取整到百位公式為=DOLLAR(B1,-2)圖2534、將數(shù)字轉(zhuǎn)換為人民幣的函數(shù)RMBDOLLOR函數(shù)可以將數(shù)字轉(zhuǎn)換為人民幣格式。例如將A1位公式為，可以輸入“=RMB(A1,2)”35、自動轉(zhuǎn)換大小寫函數(shù)PROPER此函數(shù)可以自動轉(zhuǎn)換大小寫，用法很簡單。如圖26。在單元格內(nèi)錄入=PROPER(A1)圖2636、文本替換函數(shù)REPLACEREPLACE函數(shù)可以將特定位置的文本替換成任意文本，例如將京開高速公路中的“京吧B1單元格內(nèi)輸入=REPLACE(A1,1,2,"京石")，括“2”是指從第一個字開始向后兩個字。如圖27圖2737、統(tǒng)計空白單元格的個數(shù)函數(shù)COUNTBLANKCOUNTBLANK函數(shù)可以統(tǒng)計空白單元格個數(shù)，例如COUNTBLANK(A1:A10)38、利用函數(shù)查詢費率28知條件下我們可以利用函數(shù)做一個條件查詢查詢，在單元格B16圖28=INDEX(A3:G12,MATCH(B15,A3:A12),3)在單元格B15中輸入相應站名就可以顯示費率了39、批量加入超鏈接在EXCEL表內(nèi)工作簿sheet1，將其中的B2單元格做成超鏈接，鏈接到sheet2里面的A1，這樣sheet1!B3鏈接sheet2!A2，sheet1!B4鏈接sheet12A3……Sheet1的B2:=HYPERLINK("#SHEET2!A"&ROW()-1,"SHEET2!A"&ROW()-1)將公式向下復制.二、技巧1、控制每列數(shù)據(jù)的長度并避免重復錄入選定你要輸入的數(shù)據(jù)單元格范圍，點"數(shù)據(jù)"->"有效性"->"""文本長度""等于""5"（具體條件可根據(jù)你的需要改變。還可以定義一些提示信息、出錯警告信息，定義好后點"確定。圖292、把BA列同一行數(shù)據(jù)的不同之處標識出來假定第一行為表頭，單擊A2單元格，點“格式”->“條件格式”，將條件設為:“單元格數(shù)值用格式刷將A2單元格的條件格式向下復制。B列可參照此方法設置。圖303、快速在一個工作薄中建立多個工作表Alt+Enter4、當前日期與時間的快速輸入Ctrl”鍵與分號鍵“Shift”然后回車即可。5、對多張工作表進行操作Shift”鍵再用鼠標選定相鄰的多個工作表或鍵再用鼠標選定不相鄰的多個工作表來實現(xiàn)。選定之后，就可內(nèi)執(zhí)行查找和替換操作。6、數(shù)字批量加注單位Excel時，選中7、文字批量加注單位

圖31前面我們提到了為數(shù)@金額“@”是半角字符單擊剛才選中的單元格區(qū)車鍵后，你會驚喜地發(fā)現(xiàn)， 圖32這個單元格中出現(xiàn)的是“☆短款金額”。8、保護我們的excelEXCEL先選定所有單元格,點"格式"->"單元格"->"保護",取消"鎖定"前面的"√"。再選定你要保護的單元格,點"格式"->"單元格"->"保護",在"鎖定"前面打上"√"。點"工具"->"保護"->"保護工作表",輸入兩次密碼,點兩次"確定"即可。超越工作表保護的訣竅注：如果你想使用一個保護了的工作表，但又不知道其口令，有辦法嗎？有。（意：一定要新工作簿，即可超越工作表保護。、真正地隱藏工作表的。要隱藏工作表，你可以點擊工作表的選項卡，接著選擇“式>工作表>隱藏家都知道的做法，任何人都可以隱藏”一個工作表，請選擇工具>宏>VisualBasic”上方“工程”窗格中，點擊你想要“性”窗格中，選擇“Visible”屬性標簽，點擊右邊的下拉箭頭，選擇“2-xlSheetVeryHidden”。這樣一來，這個工作表就顯示不了 圖33“取消隱藏”xlSheetVisible”或“0-xlSheetHidden”即可。、隱藏單元格中的所有值有時候，我們需要將單元格中所有值隱藏起來，這時你可以選擇包含要隱藏值“格式”菜單中“單元格”“數(shù)字”“分類”列表中選擇“自定義”，然后將“類型”框中已有的代碼刪除，鍵入“;;;”（三個分號即可。、如何對工作簿進行安全保護如果你不想別人打開或修改你的工作簿，那么想法加個密碼吧。打開工作薄，選擇“文件”菜單中的“另存為”命令，選取“工常規(guī)選項”，根據(jù)用戶的需要分別輸入打開文件口令或“修改文件口令”“確定”9、在EXECL中建立下拉列表按鈕選定你要設置下拉列表的單元格，點“數(shù)據(jù)”->“有效性”入你的下拉列表內(nèi)容，各項之間用半角逗號隔開，如：A,B,C,D

圖圖3510、快速填充數(shù)據(jù)ExcelCtrl“Ctrl＋Enter”組合鍵，則剛才選中的所有單元格就被同時填入該數(shù)據(jù)了。11、在已有的單元格中批量加入一段固定字符在空白單元格內(nèi)寫入：="要添加的內(nèi)容"&原單元格（如A1）12、在同一對應單元格內(nèi)輸入相同內(nèi)容Ctrl（Sheet1SheetSheet4……，在其中任意一個工作表中的某一單元格（B8）中輸入Enter13、EXCEL中行列互換復制，選擇性粘貼，選中轉(zhuǎn)置，確定即可14、輸入平方、立方Word（x2)、立方(x3Excelx3”或“315、用Excel為多頁表格在EXCEL面設置－工作表－打印標題；可進行頂端或左端標題設置，通過按下折疊對話框按鈕后，用鼠標劃定范圍即可。這樣Excel就會自動在各頁上加上你劃固定的部分作為表頭。如36圖3616、不打印單元格中的顏色與底紋“文件”＼“頁面設置”＼“工作表“打印”欄內(nèi)選擇“單元格單色打印”選項。之后，打印出來的表格就面目如初了。17、在EXCEL中如何統(tǒng)計字數(shù)在單元格內(nèi)輸入“=SUM(LEN(范圍))”輸完后按ctrl+shift+Enter。加入數(shù)組。運用選擇性粘貼批量運算Excel5（元改的方法，即使用“選擇性粘貼”功能：“編輯”＼“復制”E2E150“編輯”＼“選擇性粘貼在“選擇性粘貼”對話框“運算”欄中選中“加”運算，按“確定”健即可。最后，要刪50。19、工作表的標簽的字體和大小可以更改嗎在桌面上點右鍵─內(nèi)容─外觀，相關(guān)的設定都在此更改。20、繪制有三條斜線的表頭用繪圖工具畫出斜線>>畫方框>>內(nèi)添加文字>>去邊框21、自動調(diào)整小數(shù)點1Excel行下面的設置，將會使你的輸入速度成倍提高。單擊“工具”菜單中的“選項數(shù)點”復選框，在“位數(shù)”微調(diào)編輯框中鍵入需要顯示在小數(shù)點右面的位數(shù)，在此，我們鍵入“2”單擊“確定”按鈕。22、快速打開所需工作表：我們一般把許多數(shù)據(jù)相關(guān)（37）圖3723、讓數(shù)值完全或部分替換公式就以1+1這個公式為例，我來介紹一下如何用數(shù)值來永久性替換公式的方法：先選中包含公式的單元格，然后點擊“復制”，然后再點擊“粘貼”，這時再點擊右側(cè)的箭頭，再點擊“只有值”，這樣一來，單元格中的公式就永久性轉(zhuǎn)為數(shù)值了。有時候我們并不想把整個公式都轉(zhuǎn)換為數(shù)值，而只是想把公式的一部分轉(zhuǎn)換為數(shù)值，如何做呢？先選中包含公式的單元格，然后在編輯欄中，選定要轉(zhuǎn)換為數(shù)值的部分，然后按下F9，24、在Excel選中要固定的表格頭的下一行的任意單元格，點擊菜單：窗口→即可實現(xiàn)滾屏時不滾動固定表格頭（選定單元格的左側(cè)和上面各行被凍結(jié)。同11、2列作為標題凍結(jié)起來，可以這樣操作：選中C2（選中單元格是關(guān)鍵，從執(zhí)行一下“窗口→凍結(jié)窗格”命令即可。注意：執(zhí)行“窗口→取消凍結(jié)窗格”命令，即可將凍結(jié)的窗口解除。25、去除“最近使用過的工作簿”列表Excel“文件”顯示出來，如何隱藏這些工作簿呢？點擊“工具”菜單中的“選項”，再彈出的對話框中點擊“常規(guī)”選項卡，然后去除“最近使用的文件列表”前面的對勾。26、Excel建立和Word的大綱視圖一樣，Excel這