Excel操作小技巧課件

1、Excel操作小技巧操作小技巧侯慶芳2015年11月13日 單元格邊線小技巧單元格邊線小技巧 單元格查找技巧單元格查找技巧 巧用巧用“=”號，效率翻倍號，效率翻倍 橫向篩選橫向篩選 合并單元格合并單元格 自動調(diào)整打印區(qū)域自動調(diào)整打印區(qū)域 視圖調(diào)整技巧視圖調(diào)整技巧 巧用復(fù)制粘貼巧用復(fù)制粘貼 快速跳轉(zhuǎn)列快速跳轉(zhuǎn)列 短公式的設(shè)置短公式的設(shè)置 Excel隱藏小功能隱藏小功能 常用快捷鍵常用快捷鍵一.單元格邊線小技巧1.最適合列寬 選取表格所有列，雙擊任一列的邊線就會自動調(diào)整為最適合的列寬3一.單元格邊線小技巧2.調(diào)整為固定列寬選取表格所有列，調(diào)整任一列，所有列的列寬也會調(diào)整為同樣大小。4一.單元格邊線

2、小技巧3.快速跳轉(zhuǎn) 雙擊單元格的邊線，就會快速跳轉(zhuǎn)到表格的邊緣位置5一.單元格邊線小技巧4.兩列互換。 按shift不松，拖動表格邊線，可以快速讓兩列互換。6一.單元格邊線小技巧5.快速插入空行/列 A、按shift不松，拖動行號右下角，可以快速插入空行。 按shift不松，拖動列號右下角，可以快速插入空列。 B、從第6行向下選取10行，點插入即可插入10空行。 從第6行向下選取10行，點插入即可插入10空行。7一.單元格邊線小技巧6.設(shè)置立體界面 左上粗深邊線右下淺或白邊線，可以讓界面向外突出，反之而可以產(chǎn)生凹陷效果。8一.單元格邊線小技巧7.設(shè)置細(xì)邊線 表格設(shè)置成虛線，打印出來的是超細(xì)表格

3、線。9一.單元格邊線小技巧8.快速添加外邊框 一個excel表格，如果加上粗外框就會立即美觀好多10二二.單元格查找技巧單元格查找技巧1.選取表中所有公式 選取工作表中所有的公式，別以為只有定位功能可以實現(xiàn)。在查找窗口中輸入 = 號，點擊“查找全部”，然后再按ctrl+a即可選取表中所有公式。112.批量刪除或隱藏帶顏色的行 打開格式下拉 - 點擊“從單元格選擇格式”并刷向黃色單元格 - 查找全部 - 按 ctrl+a 全選 - 按ctrl+9隱藏或右鍵菜單中點刪除。12二二.單元格查找技巧單元格查找技巧3.多列按區(qū)間篩選【例】如下圖所示表中，要求選取16月各產(chǎn)品銷量數(shù)量80的單元格，并填充綠

4、色背景。13二二.單元格查找技巧單元格查找技巧3.多列按區(qū)間篩選14操作步驟：A.選取數(shù)據(jù)區(qū)域，CTRL+F打開查找窗口；B.查找框中輸入*，點“查找全部”；C.點標(biāo)題“值”排序；D.選取第一個大于80的行，按shift選中最后一行；E.關(guān)閉“查找”窗口，填充綠色。二二.單元格查找技巧單元格查找技巧4.查找所有合并單元格 如需要查找工作表中的合并單元格，只需要先任選2個空單元格合并，然后從查找窗口中選取該合并單元格，再點查找即可。15二二.單元格查找技巧單元格查找技巧5.刪除括號中的內(nèi)容 如果要刪除一列字符串中括號內(nèi)的內(nèi)容。只需要查找 (*) 然后替換為空即可。16二二.單元格查找技巧單元格查

5、找技巧1.隔行填充 ctrl+g定位條件空值 - 在編輯欄中輸入=A2，按ctrl+enter完成填充17三.巧用“=”，效率翻兩倍2.多列轉(zhuǎn)一列 =第2列的第一個單元格，向下向右復(fù)制，最后公式轉(zhuǎn)換成數(shù)值。18三.巧用“=”，效率翻兩倍3.一列轉(zhuǎn)多列 如果轉(zhuǎn)換成每5行一列，在第2列輸入=A6，然后復(fù)制19三.巧用“=”，效率翻兩倍4.圖表上添加動態(tài)說明 選取圖表的標(biāo)題，然后在編輯欄中輸入=D1。20三.巧用“=”，效率翻兩倍四四.excel里橫向篩選的解決方案里橫向篩選的解決方案【例】下圖所示。要求篩遠(yuǎn)產(chǎn)品C數(shù)量大于50的月份。21【解】excel里既然提供了豎向篩選，那么我們想辦法把表格倒置

6、一下，原來的行變成列，列變成行。完成轉(zhuǎn)置有兩種方法。 A.選擇粘貼轉(zhuǎn)置。這種方法一次性的，數(shù)據(jù)源數(shù)據(jù)變化后再篩 選還需要重復(fù)操作。不可取。 B.函數(shù)轉(zhuǎn)置。transpose函數(shù)可以把一個區(qū)域進(jìn)行行列轉(zhuǎn)換。即 然是公式，原表變化，公式結(jié)果當(dāng)然也會跟著變化。這種方法 是我們所要的。操作步驟： 步驟1: 新建一個工作表，選取一個和原表行列相同大小的區(qū)域 如本例原表是7列6行的區(qū)域，那么在新工作表中選取 7行6列的區(qū)域，當(dāng)然選更多的行列數(shù)也行。 步驟2：選取區(qū)域后，在編輯欄中輸入=TRANSPOSE(Sheet2!A1:G6),光標(biāo)放在公式結(jié)尾處，按ctrl+shift同時再按 回車鍵，再同時松開三鍵

7、結(jié)束公式輸入。結(jié)果如下圖所示。22四四.excel里橫向篩選的解決方案里橫向篩選的解決方案步驟3：我們用公式備份了一個倒置的表，在這個備份表里你就可以利用自動篩選進(jìn)行篩選，等同于在原表中橫向篩選。篩選效果如下圖所示。23四四.excel里橫向篩選的解決方案里橫向篩選的解決方案五.合并單元格1.跨列合并 單行合并：工具欄-【 合并后居中 】/【 合并單元格 】 快捷鍵-【 】/【 】 多行合并，你還在一行一行的合并嗎？如果是看看下面的方法：24Alt+H+M+CAlt+H+M+MAlt+H+M+A2.合并單元格字符 兩個單元格的字符強(qiáng)烈要求合并到一起，公式=B2&C2。25五.合并單元格3.合并

8、多個單元格字符 選取區(qū)域 - 開始 - 填充 - 兩端對齊 合并的結(jié)果為：中國china臺灣taiwan26五.合并單元格4.合并單元格，且內(nèi)容都要保留 合并2個空單元格D2:D3，然后用格式刷，b列2個單元格會合并而且下面的單元格會保留內(nèi)容。27五.合并單元格六六.自動調(diào)整打印區(qū)域自動調(diào)整打印區(qū)域1.選取區(qū)域 - 頁面布局 - 設(shè)置打印區(qū)域。28u數(shù)據(jù)區(qū)域第一行避免空行數(shù)據(jù)區(qū)域第一行避免空行u數(shù)據(jù)區(qū)域第一列避免空列數(shù)據(jù)區(qū)域第一列避免空列u數(shù)據(jù)區(qū)域第一行數(shù)據(jù)區(qū)域第一行/列無合并單元格列無合并單元格2.公式 - 名稱管理器：打印選取print_area，然后在下面的文本框輸入以下公式：=OFFS

9、ET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)29六六.自動調(diào)整打印區(qū)域自動調(diào)整打印區(qū)域COUNTA(Sheet1!$A:$A)： 統(tǒng)計有多少統(tǒng)計有多少行行COUNTA(Sheet1!$1:$1)： 統(tǒng)計有多少列統(tǒng)計有多少列OFFSET（A1,行數(shù)行數(shù),列數(shù)列數(shù))： 以以A1為頂點根據(jù)行和為頂點根據(jù)行和 列數(shù)生成一個動態(tài)區(qū)域列數(shù)生成一個動態(tài)區(qū)域效果如下面動態(tài)圖所示，添加新的列或行后，打印區(qū)域也會自動調(diào)整大?。ㄗ⒁馓摼€變化）30六六.自動調(diào)整打印區(qū)域自動調(diào)整打印區(qū)域PS:所添加內(nèi)容第一行所添加內(nèi)容第一行/第一列必須不為空值第一列必

10、須不為空值1.對比兩個excel文件2.對比兩個工作表3.工作表翻看4.表格翻看最后一行5.標(biāo)題行顯示6.打印頂端標(biāo)題行31視圖 - 全部重排 - 水平/垂直重排視圖 - 新建窗口 - 全部重排 - 水平/垂直重排按ctrl+pgup（pgdn） 鍵可以快速翻看工作表雙擊表格中任一個單元格的下邊線，即可跳到最后一行。視圖 - 凍結(jié)窗格 - 凍結(jié)首行插入-頁眉頁腳-工作表-頂端標(biāo)題行/左端標(biāo)題列七.視圖調(diào)整技巧7.報表日期修改 【例】一個文件有上百張月報表，到下一個月時需要把所有報表的日期更改為本月。32七.視圖調(diào)整技巧【解】把第2N張表的日期所在單元格設(shè)置公式，用=號引用第1個月報表的日期，這

11、樣以后只需要修改第1個報表的日期，其他的都會跟著自動變化了。8.整列添加空格 開始 - 點“向右縮進(jìn)”。33七.視圖調(diào)整技巧Ctrl+Alt+Tab八八.巧用復(fù)制粘貼巧用復(fù)制粘貼1.把公式轉(zhuǎn)換為數(shù)值 342.復(fù)制格式 選以A1:C2向下拖動復(fù)制 - 僅填充格式，可以隔行填充顏色。Alt+E+S:調(diào)用選擇性粘貼對話框調(diào)用選擇性粘貼對話框3.把列寬調(diào)整一致4.行列轉(zhuǎn)置5.批注選擇性復(fù)制35八八.巧用復(fù)制粘貼巧用復(fù)制粘貼Alt+E+S:調(diào)用選擇性粘貼對話框調(diào)用選擇性粘貼對話框6.基本運(yùn)算復(fù)制1000所在單元格，然后選取A列粘貼 - 選擇性粘貼 - 運(yùn)算：加。即可讓A列值全加上1000。5.添加圖表系

12、列 在圖表上添加新系列，只需要復(fù)制 - 粘貼即可。36八八.巧用復(fù)制粘貼巧用復(fù)制粘貼6.自定義柱子圖形復(fù)制插入到excel中的圖形，再選中圖表中的柱子，ctrl+v后可以把圖形顯示到柱子上。37八八.巧用復(fù)制粘貼巧用復(fù)制粘貼九.快速跳轉(zhuǎn)列1.設(shè)置方法l 選取“大長表的包括標(biāo)題行的前兩行，公式 - 根據(jù)所選內(nèi)容創(chuàng)建。（excel2003版，插入 - 名稱 - 指定）38l 在彈出的窗口中，選中“首行2.使用方法 打開左上的地址框，選取列標(biāo)題，即可跳轉(zhuǎn)到指定的列。39九.快速跳轉(zhuǎn)列3.刪除名稱方法 如果需要刪除定義的名稱，可以打開定義名稱窗口，按shift鍵選取連續(xù)的名稱（按ctrl鍵選不連續(xù)的）

13、，點擊刪除按鈕即可批量刪除。40九.快速跳轉(zhuǎn)列41excel短公式看起來清爽，只是對新手來說有點難懂。什么樣的公式最好的，主要從以下幾點來判斷：l 是否容易理解。l 是否易修改，易維護(hù)，公式能否表格的變化而自動調(diào)整。l 計算量盡可能的少，不會拖慢表格。十十.短公式讓你隨心所欲短公式讓你隨心所欲十十.短公式讓你隨心所欲短公式讓你隨心所欲1.隔列求和如下圖所示，需要對112月份的計劃和實際數(shù)進(jìn)行求和(為方便截圖)長公式：=B3+D3+F3+H3+J3+L3+N3+P3+R3+T3+V3+X3短公式：=SUMIF(B$2:Y$2,Z$2,B3:Y3)422.總計行如下圖所示，需要對下表設(shè)置總計行。長

14、公式：C27 =C5+C8+C11+C14+C17+C20+C23+C26短公式：C27 =SUM(C2:C26)/243十十.短公式讓你隨心所欲短公式讓你隨心所欲3.多表求和如下圖所示，需要對131日的報表進(jìn)行匯總l 長公式：=1!B4+2!B4+3!B4+4!B4+5!B4+6!B4+7!B4+8!B4+9!B4+10!B4+11!B4+12!B4+13!B4+14!B4+15!B4+16!B4+17!B4+18!B4+19!B4+20!B4+21!B4+22!B4+23!B4+24!B4+25!B4+26!B4+27!B4+28!B4+29!B4+30!B4+31!B4l 短公式：=SU

15、M(1:31!B4)44十十.短公式讓你隨心所欲短公式讓你隨心所欲4.計算完成率如下表在計算完成率時，如果實際和計劃數(shù)有一方為空或0，則公式返回空。l 長公式：D2 =IF(OR(B2=0,C2=0),B2/C2)l 短公式：D2 =IF(B2*C2,B2/C2,)45十十.短公式讓你隨心所欲短公式讓你隨心所欲5.獎勵封頂如下圖所示的“實際獎勵”計算表中，如果“應(yīng)獎勵”數(shù)大于“最高獎勵”，則按“最高獎勵”金額，如果小于則按“應(yīng)獎勵”金額。l長公式：D2 =IF(B2=C2,D2,B2)l短公式：D2 =MIN(B2,C2)46十十.短公式讓你隨心所欲短公式讓你隨心所欲十一.Excel隱藏很深的

16、小功能2.隱藏0值。隱藏0值在實際工作中會經(jīng)常用到，但我們必須 文件 - 選項 - 高級 - 使勁向下翻才能找到“在具有零值的單元格顯示零”。471.數(shù)字設(shè)置為整數(shù)。 ctrl+1打開設(shè)置單元格格式窗口 - 數(shù)字 - 小數(shù)位置0。 （替換方法：按ctrl+shift+1組合鍵設(shè)置為貨幣型整數(shù)）3.給excel文件添加打開密碼excel升級到10版后，添加保護(hù)密碼依舊隱藏的很嚴(yán)實。【文件】 - 【信息】 - 【保護(hù)工作簿 】- 【用密碼進(jìn)行加密】48十一.Excel隱藏很深的小功能4.一次性刪除所有工作表批注 選取單元格區(qū)域后可以批量刪除批注，但如果表格范圍大或需要刪除批注的工作表很多，就需要使

17、用文檔檢查功能。49十一.Excel隱藏很深的小功能5.為批注或圖形添加圖片背景 批注也可以添加圖片作為背景，但excel把這個功能隱藏的真夠深的。50十一.Excel隱藏很深的小功能6.輸入網(wǎng)址后禁止自動添加超鏈接如果在excel中輸入http:/，會自動添加超鏈接。想禁止添加，你需要執(zhí)行 文件 - 選項 - 校對 - 自動更正選項 - 鍵入時自動套用格式 - Internet及網(wǎng)絡(luò)路徑替換為超鏈接。51十一.Excel隱藏很深的小功能7.調(diào)整行間距 使用文檔拼音功能52十一.Excel隱藏很深的小功能53Ctrl+1 0Ctrl+A ZCtrl+其他鍵F1 F12 十二十二.常用快捷鍵常用

18、快捷鍵54（1）F1:顯示幫助。 Ctrl+F1：顯示或隱藏功能區(qū)。 Alt+F1：創(chuàng)建當(dāng)前范圍內(nèi)數(shù)據(jù)圖表。 Alt+Shift+F1:插入新工作表。（2）F2：輸入。 Shift+F2：添加單元格批注。 Ctrl+F2：打印預(yù)覽。（3）F3：顯示粘貼名稱。 Shift+F3：插入函數(shù)。（4）F4：重復(fù)上一步。 Ctrl+F4=Ctrl+W 關(guān)閉當(dāng)前表格。（5）F5：定位功能。可聯(lián)合Ctrl+Enter使用快速填充數(shù)據(jù)。（6）F6=F10：調(diào)出快捷功能。 Ctrl+F6：切換下一工作簿。十二十二.常用快捷鍵常用快捷鍵55（7）F7：拼寫檢查。（8）F8：擴(kuò)展選定區(qū)域。（9）F9：計算所有打開的

19、工作簿。 Ctrl+F9：將工作簿最小化為圖標(biāo)。（10）F10=F6：調(diào)出快捷功能，可與Alt+H聯(lián)合使用。 Shift+F10=單擊右鍵。（11）F11：創(chuàng)建數(shù)據(jù)圖表。 Shift+F11：插入新表。（12）F12：另存為。十二十二.常用快捷鍵常用快捷鍵56（1）ctrl+1 顯示“單元格格式”對話框 （2）ctrl+2 應(yīng)用或取消加粗格式設(shè)置應(yīng)用或取消加粗格式設(shè)置 （3）ctrl+3 應(yīng)用或取消傾斜格式設(shè)置 （4）ctrl+4 應(yīng)用或取消下劃線 （5）ctrl+5 應(yīng)用或取消刪除線 （6）ctrl+6 在隱藏對象和顯示對象之間切換 （7）ctrl+7 顯示或隱藏“常用”工具欄 （8）ctrl+8 顯示或隱藏大綱符號 （9）ctrl+9 隱藏選定的行 （10）ctrl+0 隱藏選定的列十二十二.常用快捷鍵常用快捷鍵573.Ctrl+AZCtrl+A 選擇整個工作表 C