Excel工作中的使用小技巧X.ppt

《Excel工作中的使用小技巧X.ppt》由会员分享，可在线阅读，更多相关《Excel工作中的使用小技巧X.ppt（36页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、ExcelExcel使用技巧使用技巧Smile Han2009/04/22Rev：X2 在工作當中用試算表來處理資料將會更加迅速、方便，而在各種試算表處理軟體中，Excel以其功能強大、操作方便著稱，贏得了廣大用戶的青睞。雖然Excel使用很簡單，不過真正能用好Excel的用戶並不多，很多人一直停留在錄入資料的水準，本文將向你介紹一些非常使用的技巧，掌握這些技巧將大大提高你的工作效率Excel-引言引言目錄一 讓Excel資料登錄不間斷二 快速定義工作簿格式三 快速複製公式四Shift“拖放”的妙用五 如何避免Excel中的錯誤資訊六 批量修改資料七 插入超連接及對象八Excel2007也能截

2、圖九Excel2007也玩重複標題行十為Excel2007添加一個“雙面列印”按鈕十一利用高級篩選功能巧妙刪除Excel重複記錄十二快速查找具有條件格式的Excel儲存格四函數-幫你成為Excel高手五換個口味把Excel儲存格變得凹凸有致十五在一個Excel檔中一次列印多個工作表一 讓Excel資料登錄不間斷我們在Excel2007中錄入資料時，通常會用右鍵頭或回車鍵進行儲存格切換，當輸入到行或列的末尾，右鍵頭和回車鍵就不起作用啦！這時候需要用滑鼠來定位到下一行或下一列的首儲存格。雖然不是很麻煩，但在資料量巨大的時候，時不時要去動下滑鼠，無法不間斷輸入資料，總歸覺得有點不方便。下面兩種方法可

3、有效地解決這個問題下面兩種方法可有效地解決這個問題：1.如果按行進行輸入，用Tab來向右切換儲存格，輸入到行末尾的儲存格時，直接按回車鍵，可立即切換到下一行的行首儲存格。2.加果按列進行輸入，先將資料錄入區域選中，再從第一個儲存格開始輸入。每一個儲存格完成後，按回車鍵，當輸入到列末尾儲存格時，依舊按回車鍵，游標將自動轉到第二列的列首。二 快速定義工作簿格式首先選定需要定義格式的工作簿範圍，按一下“格式”功能表的“樣式”命令，打開“樣式”對話方塊；然後從“樣式名”清單方塊中選擇合適的“樣式”種類，從“樣式包括”清單方塊中選擇是否使用該種樣式的數位、字體、對齊、邊框、圖案、保護等格式內容；按一下“

4、確定”按鈕，關閉“樣式”對話方塊，Excel工作簿的格式就會按照使用者指定的樣式發生變化，從而滿足了用戶快速、大批定義格式的要求三 快速複製公式複製是將公式應用於其它儲存格的操作，最常用的有以下幾種方法複製是將公式應用於其它儲存格的操作，最常用的有以下幾種方法：1是拖動制複製。操作方法是：選中存放公式的儲存格，移動空心十字游標至儲存格右下角。待游標變成小實心十字時，按住滑鼠左鍵沿列（對行計算時）或行（對列計算時）拖動，至資料結尾完成公式的複製和計算。公式複製的快慢可由小實心十字游標距虛框的遠近來調節：小實心十字游標距虛框越遠，複製越快；反之，複製越慢。2也可以輸入複製。此法是在公式輸入結束後立

5、即完成公式的複製。操作方法：選中需要使用該公式的所有儲存格，用上面介紹的方法輸入公式，完成後按住Ctrl鍵並按回車鍵，該公式就被複製到已選中的所有儲存格。3還可以選擇性粘貼。操作方法是：選中存放公式的儲存格，按一下Excel工具列中的“複製”按鈕。然後選中需要使用該公式的儲存格，在選中區域內按一下滑鼠右鍵，選擇快捷選單中的“選擇性粘貼”命令。打開“選擇性粘貼”對話方塊後選中“粘貼”命令，按一下“確定”，公式就被複製到已選中的儲存格。四 Shift“拖放”的妙用在拖放選定的一個或多個儲存格至新的位置時，同時按住Shift鍵可以快速修改儲存格內容的次序。具體方法為：選定儲存格，按下Shift鍵，移

6、動滑鼠指標至儲存格邊緣，直至出現拖放指針箭頭“?，然後進行拖放操作。上下拖拉時滑鼠在儲存格間邊界處會變為一個水準“工狀標誌，左右拖拉時會變為垂直“工狀標誌，釋放滑鼠按鈕完成操作後，儲存格間的次序即發生了變化。這種簡單的方法節省了幾個剪切和粘貼或拖放操作，非常方便。五 如何避免Excel中的錯誤資訊在Excel中輸入或編輯公式後，有可能不能正確計算出結果，Excel將顯示一個錯誤資訊，引起錯誤的原因並不都是由公式本身有錯誤產生的。下面我們將介紹五種在Excel中常出現的錯誤資訊，以及如何糾正這些錯誤。錯誤資訊錯誤資訊1輸入到儲存格中的資料太長或儲存格公式所產生的結果太大，在儲存格中顯示不下時，將

7、在儲存格中顯示。可以通過調整列標之間的邊界來修改列的寬度。如果對日期和時間做減法，請確認格式是否正確。Excel中的日期和時間必須為正值。如果日期或時間產生了負值，將在整個儲存格中顯示。如果仍要顯示這個數值，请按一下右鍵選擇“設置單元格格式”功能表中的“儲存格”命令，再按一下“數位”選項卡，然後選定一個不是日期或時間的格式。五 如何避免Excel中的錯誤資訊（2）錯誤資訊錯誤資訊2DIV/0!輸入的公式中包含明顯的除數0，例如120/0，則會產生錯誤資訊DIV/0!。或在公式中除數使用了空儲存格(當運算對象是空白儲存格，Excel將此空值解釋為零值)或包含零值儲存格的儲存格引用。解決辦法是修改

8、儲存格引用，或者在用作除數的儲存格中輸入不為零的值。錯誤資訊錯誤資訊3VALUE!當使用不正確的參數或運算子時，或者當執行自動校正公式功能時不能更正公式，都將產生錯誤資訊VALUE!。在需要數位或邏輯值時輸入了文本，Excel不能將文本轉換為正確的資料類型。這時應確認公式或函數所需的運算子或參數正確，並且公式引用的儲存格中包含有效的數值。例如，儲存格B3中有一個數字，而儲存格B4包含文本，則公式=B3B4將返回錯誤資訊VALUE!。五 如何避免Excel中的錯誤資訊（3）錯誤資訊錯誤資訊4NAME?在公式中使用了Excel所不能識別的文本時將產生錯誤資訊NAME?。可以從以下幾方面進行檢查糾正

9、錯誤：(1)如果是使用了不存在的名稱而產生這類錯誤，應確認使用的名稱確實存在。在“插入”功能表中指向“名稱”，再按一下“定義”命令，如果所需名稱沒有被列出，請使用“定義”命令添加相應的名稱。(2)如果是名稱，函數名拼寫錯誤應修改拼寫錯誤。(3)確認公式中使用的所有區域引用都使用了冒號(：)。例如：SUM(A1：C10)。注意將公式中的文本括在雙引號中。錯誤資訊錯誤資訊5NUM!當公式或函數中使用了不正確的數位時將產生錯誤資訊NUM!。要解決問題首先要確認函數中使用的參數類型正確。還有一種可能是由公式產生的數位太大或太小，Excel不能表示，如果是這種情況就要修改公式，使其結果在110307和1

10、10307之間。六 批量修改資料在EXCEL表格資料都已被填好的情況下，如何方便地對任一列（行）的資料進行修改呢？比如我們做好一個EXCEL表格，填好了資料，現在想修改其中的一列（行），例如：想在A列原來的資料的基礎上加8，有沒有這樣的公式？是不是非得手工的一個一個資料地住上加？對於這個問題我們自然想到了利用公式，當你利用工式輸入A1=A1+8時，你會得到EXCEL的一個警告：“MICROSOFTEXCEL不能計算該公式”只有我們自己想辦法了，這裡介紹一種簡單的方法：六 批量修改資料（2）第一步：在想要修改的列（假設為A列）的旁邊，插入一個臨時的新列（為B列），並在B列的第一個儲存格（B1）裡

11、輸入8。第二步：把滑鼠放在B1的或下角，待其變成十字形後住下拉直到所需的數據長度，此時B列所有的資料都為8。第三步：在B列上按一下滑鼠右鍵，“複製”B列。第四步：在A列按一下滑鼠的右鍵，在彈出的對話方塊中按一下“選擇性粘貼”，在彈出的對話方塊中選擇“運算”中的你所需要的運算子，在此我們選擇“加”，這是本方法的關鍵所在。第五步：將B列刪除。怎麼樣？A列中的每個資料是不是都加上了8呢？同樣的辦法可以實現對一（行）的乘，除，減等其它的運算操作。原表格的格式也沒有改變。此時整個工作結束，使用熟練後，將花費不到十秒鐘七 插入超連接及對象在功能欄點擊“插入”選擇“超級連接”，並根據位址選擇需要連接的文檔在

12、功能欄點擊“插入”選擇“對象”，然後點擊“由文件創建”，選定需創建的文件，勾選“連接”&“顯示為圖標”，爲了美觀，請記住修改連接檔的名稱，否則插入的對象將默認“Office軟件的名稱”。八 Excel2007也能截圖工作中我們常常需要將Excel2007文檔中一部分圖截出來供他人參考，如何實現呢？其實大可不必如此麻煩，因為在Excel2007中已經集成了截圖的功能，利用這一功能，我們就能把選定的儲存格區域方便地轉換為圖片。首先選中需要複製成圖片的儲存格區域，然後依次選擇“開始-剪貼板-粘貼”命令（如圖1所示）八 Excel2007中利用條件格式確保資料登錄在下拉式功能表中選擇“以圖片格式-複製

13、為圖片”，彈出“複製圖片”對話方塊（如圖2所示），選擇“圖片”後點擊“確定”按鈕。這時選定的表格區域就已經被覆製成圖片了。再將這張圖片粘貼到需要使用的地方即可。九 Excel2007也玩重複標題行在Word中，提供了表格標題行重複按鈕，只要選中標題行，按一下“重複標題行”按鈕即可讓每一頁都顯示設定的標題行，那麼EXCEL中能不能重複標題行呢？當然可以。你只要按照筆者如下步驟設置即可實現。以EXCEL2007為例，首先打開EXCEL2007，製作好工作表，然後在頂端行中或者左端列中輸入需要作為每頁頁首標題行的內容，然後按一下“頁面配置”選項，接下來按一下“頁面配置”選項右側的擴展按鈕，在彈出的對

14、話方塊中按一下“工作表”選項卡，介面如圖1。九 Excel2007也玩重複標題行（2）按一下頂端標題行或者左端標題列右側的圖示按鈕，這時候會彈出來一個設置對話方塊（如圖2），這時候，我們只要選擇需要重複的頂端標題行或者左端標題列中的內容，然後按一下圖2對話方塊右側的圖示按鈕，最後按一下工作表中的“確定”按鈕就好了。現在就趕緊到預覽列印中看看，是不是實現了標題行重複了呢？十為Excel2007添加一個“雙面列印”按鈕大家知道，在Word中可以將“奇數頁”和“偶數頁”分開列印，從而實現多頁文檔的“雙面列印”效果。但是，在Excel中卻沒有這一功能。下面，我們就以Excel2007為例，為其添加一個

15、具有“雙面列印”功能的按鈕。一、展開一、展開“開發工具開發工具”選項卡選項卡要在Excel中實現“雙面列印”，就要用到其內嵌的VBA功能，因此，需要先展開“開發工具”選項卡。啟動Excel2007，按一下左上角的軟體圖示，在隨後出現的下拉式功能表中(如圖1)，選擇“Excel選項”選項，打開“Excel選項”對話方塊(如圖2)圖1圖2十為Excel2007添加一個“雙面列印”按鈕（2）二、生成二、生成“個人宏工作簿個人宏工作簿”為了讓“雙面列印”按鈕對所有工作簿都有效，我們需要將其保存在“個人宏工作簿”中。第一次使用“巨集”這個功能時，需要先讓軟體生成一個隱藏的“個人宏工作簿”。切換到“開發工

16、具”選項卡中，按一下其中的“錄製巨集”按鈕，打開“錄製新宏”對話方塊(如圖3)。輸入一下宏名稱(如“smdy”)，按“保存在”右側的下拉按鈕，在隨後出現的下拉清單中，選擇“個人巨集工作簿”選項，確定進入“巨集”錄製狀態。圖3不需要進行任何操作，直接按一下“開發工具”選項卡中的“停止錄製”按鈕，系統會自動生成一個隱藏的“個人宏工作簿”。特別提醒:以後需要在“個人巨集工作簿”中編輯巨集時，就不需要再進入此步操作了。十為Excel2007添加一個“雙面列印”按鈕（3）三、編輯三、編輯“雙面列印雙面列印”代碼代碼按一下“開發工具”選項卡上的“VisualBasic”按鈕，進入VBA編輯狀態(如圖4)。

17、下拉按鈕，在隨後出現的下拉清單中，選擇“個人巨集工作簿”選項，確定進入“巨集”錄製狀態。圖4特別提醒特別提醒:按“Alt+F11”複合鍵，即可快速進入VBA編輯狀態。在左側“工程資源管理器”中，展開“VBAProject(PERSONAL.XLSB)”選項(這就是“個人宏工作簿”)，按兩下其中的“模組1”，然後用下述代碼替換右側編輯區中的原有代碼:Subsmdy()OnErrorResumeNextx=ExecuteExcel4Macro(get.document(50)Fori=1ToInt(x/2)+1ExecuteExcel4MacroPRINT(2,&2*i-1&,&2*i-1&,1,

18、2,TRUE,FALSE)NextiMsgBox請將打印紙反向裝入印表機中,vbOKOnly,列印另一面Forj=1ToInt(x/2)+1ExecuteExcel4MacroPRINT(2,&2*j&,&2*j&,1,2,TRUE,FALSE)NextjEndSub輸入完成後，關閉VBA編輯視窗返回到Excel編輯狀十一利用高級篩選功能巧妙刪除Excel重複記錄Excel有一個小小的缺陷，那就是無法自動識別重複的記錄。為了清除這些重複記錄，有的朋友是一個一個手工刪，既費時又費力。雖說Excel中並沒有提供給我們清除重複記錄這樣的功能，但我們還可以利用它的高級篩選功能來達到相同的目的。今天，筆

19、者就來向大家介紹一個如何利用Excel的“高級篩選”巧妙刪除重複記錄的小技巧。具體操作步驟如下具體操作步驟如下:1.打開一份帶有重複記錄的Excel文檔。如圖1所示(注:本圖已用Photoshop處理，其中彩色部分為重複的記錄)十一利用高級篩選功能巧妙刪除Excel重複記錄2.選中圖表中的所有記錄(注意，此時應將每列的標題行也選擇上，否则篩選完的資料表中將不再包含有該標題行)，執行“數據”菜單“篩選高級篩選”命令3.在彈出的“高級篩選”對話方塊中選擇“將篩選結果複製到其他位置”一項，並點擊“複製到”選擇框後面的範圍按鈕來選擇一塊區域以存放篩選後的數據(注意:此處千萬不要與原資料所處的儲存格相重

20、合，否則資料表將会混亂)，最後，勾選“選擇不重複的記錄”核取方塊後，點擊“確定”按鈕即可。如圖所示：十一利用高級篩選功能巧妙刪除Excel重複記錄4.此時，Excel便會將所有的重複記錄自動刪除掉，確認無誤後，您就可以把新生成的資料清單拷貝到新的工作表中繼續使用了。如圖所示十二快速查找具有條件格式的Excel儲存格如果Excel工作表的一個或多個儲存格具有條件格式(條件格式：當指定條件為真時，Excel自動應用於儲存格的格式，例如，儲存格網底或字體顏色。)，則可以快速找到它們以便複製、更改或刪除條件格式。可以使用“定位條件”命令只查找具有特定條件格式的儲存格，或查找所有具有條件格式的儲存格。查

21、找所有具有條件格式的儲存格查找所有具有條件格式的儲存格1、按一下任何沒有條件格式的儲存格。2、在“開始”選項卡上的“編輯”組中，按一下“查找和選擇”旁邊的箭頭，然後按一下“條件格式”。只查找具有相同條件格式的儲存格只查找具有相同條件格式的儲存格1、按一下有要查找的條件格式的儲存格。2、在“開始”選項卡上的“編輯”組中，按一下“查找和選擇”旁邊的箭頭，然後按一下“定位條件”。3、按一下“條件格式”。4、在“資料有效性”下面按一下“相同”。十三函數-幫你成為Excel高手1.Vlookup函數2.If函數3.IS函數4.組合函數十四換個口味把Excel儲存格變得凹凸有致厭煩了Excel儲存格那一成

22、不變的平面填充樣式了嗎？製作凹凸不平有點立體效果的試算表來換換口味吧。如圖1所示。先來個的簡單的。如圖2所示。相鄰行之間顯示凹凸效果。圖1圖2十四換個口味把Excel儲存格變得凹凸有致具體過程是：先選中圖示區域A1:H12儲存格，然後點擊功能區“開始”選項卡中“字體”功能組中“填充顏色”按鈕右側的小三角形，然後在“主題顏色”清單中選擇“白色，背景1，深色15%”，如圖3所示，將所選儲存格填充成淺灰色。圖3十四換個口味把Excel儲存格變得凹凸有致現在仍然選中這些儲存格區域，點擊功能區“樣式”選項卡中“條件格式”按鈕下的小三角形，在彈出的快顯功能表中點擊“新建規則”命令，如圖4所示。圖4十四換個

23、口味把Excel儲存格變得凹凸有致在打開的“新建格式規則”對話方塊中，點擊“選擇規則類型”清單中“使用公式確定要設置格式的儲存格”，然後在下方的“為符合此公式的值設置格式”輸入框中輸入公式“=mod(row(),2)=0”，如圖5所示。其用意是指定行號是偶數的儲存格為要設置格式的儲存格。圖5十四換個口味把Excel儲存格變得凹凸有致再點擊對話方塊右下角的“格式”按鈕，打開“設置單元格格式”對話框。點擊“邊框”選項卡，點擊左邊“顏色”下拉按鈕，然後在列表中選中左上角的“白色，背景1”顏色，並在右側“邊框”預覽窗口的四邊邊框線中點擊下邊線，然後再選擇顏色為“白色，背景1，深色50%”，點擊上邊線，

24、如圖6所示。這樣，就可以使儲存格上邊線為深灰色線，下邊線為白色線，而左右兩邊均無邊框。圖6十四換個口味把Excel儲存格變得凹凸有致一路確定，關閉全部的對話方塊後，就可以得到圖2所示的效果了至於圖1所示效果，其實可以由此類推得到。只要我們設置“行號為偶數且列數為奇數”或“行號為奇數且列數為偶數”的儲存格左邊框和上邊框為深灰色，則下邊框和右邊框為白色，那麼就可以得到圖示效果了。所以，用上面的方法就完全可以實現，只是在設置條件格式的公式時應該將公式寫成“=OR(AND(MOD(ROW(),2)=0,MOD(COLUMN(),2)=1),AND(MOD(ROW(),2)=1,MOD(COLUMN()

25、,2)=0)”，並注意邊框的格式如前所述就可以了。如果我們將上邊框和左邊框設置成白色，而下邊框和右邊框設置成深灰色，那麼可以得到恰好相反的凹凸效果。此外，我們在指定邊框線的同時，也可以指定儲存格的填充顏色，那麼就可以得到更為豐富的效果了。十五在一個Excel檔中一次列印多個工作表把滑鼠移到工作表的名稱處（若你沒有特別設置的話，Excel自動設置的名稱是“sheet1、sheet2、sheet3.”），然後點右鍵，在彈出的菜單中選擇“選擇全部工作表”的功能表項目，這時你的所有操作都是針對全部工作表了，不管是設置頁眉和頁腳還是列印你工作表。EXCEL中有序號一欄，由於對表格進行調整，序號全亂了,可

26、要是手動一個一個改序號實在太慢太麻煩，用什麼方法可以快速解決？如果序號是不應隨著表格其他內容的調整而發生變化的話，那麼在制作EXCEL表格時就應將序號這一欄位與其他欄位分開，如在“總分”與“排名”之間空開一列，為了不影響顯示美觀，可將這一空的列字段設為隱藏，這樣在調整表格（資料清單）的內容時就不會影響序號了。十六举手之劳实现Excel2007文档逆序打印在Word2007中，只要依次點擊左上角Office徽標列印，在打開的“列印”設置視窗中點擊“選項”按鈕，在接著出現的“Word選項”視窗中切換到“高級”標籤頁，然後勾選“列印”項中的“逆序打印頁面”選項(圖1)，這樣印表機列印時就會從最後一頁

27、開始逆序列印到第一頁，列印完畢後，最後一頁自然處於最底下，而第一頁處於最上面，如此則無須逆序重排文檔，直接就可以裝訂成冊，對於長篇打印文檔尤其方便。與Word相比，Excel的列印功能就相形見絀，Excel2007中也未見對列印功能有任何改善。但我們只要稍費心思，也能讓Excel2007輕鬆擁有“逆序列印”功能。十六举手之劳实现Excel2007文档逆序打印一一.打印机属性巧打印机属性巧设置置有些打印机只要安装了自带的驱动程序，就可以在打印属性中找到类似“逆序打印”的打印选项。以笔者的CanonPIXMAiP1000为例(事先请安装打印机附带光盘中的驱动程序)，打印时，只要打开Excel200

28、7工作簿，依次点击左上角Office徽标打印，打开“打印”设置窗口，在打印机名称中选中“CanonPIXMAiP1000”，然后点击“属性”按钮，调出“CanonPIXMAiP1000属性”窗口，切换到“页设置”标签页，勾选“反向”复选框再点击“确定”按钮即可(图2)。十六举手之劳实现Excel2007文档逆序打印打印机属性巧打印机属性巧设置置有些打印机只要安装了自带的驱动程序，就可以在打印属性中找到类似“逆序打印”的打印选项。以笔者的CanonPIXMAiP1000为例(事先请安装打印机附带光盘中的驱动程序)，打印时，只要打开Excel2007工作簿，依次点击左上角Office徽标打印，打开“打印”设置窗口，在打印机名称中选中“CanonPIXMAiP1000”，然后点击“属性”按钮，调出“CanonPIXMAiP1000属性”窗口，切换到“页设置”标签页，勾选“反向”复选框再点击“确定”按钮即可(图2)。Q&AQ&A