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

《Excel工作中的使用小技巧X2.ppt》由会员分享，可在线阅读，更多相关《Excel工作中的使用小技巧X2.ppt（36页珍藏版）》请在三一办公上搜索。

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

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

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

4、塊，Excel工作簿的格式就會按照使用者指定的樣式發生變化，從而滿足了用戶快速、大批定義格式的要求,三 快速複製公式,複製是將公式應用於其它儲存格的操作，最常用的有以下幾種方法：是拖動制複製。操作方法是：選中存放公式的儲存格，移動空心十字游標至儲存格右下角。待游標變成小實心十字時，按住滑鼠左鍵沿列（對行計算時）或行（對列計算時）拖動，至資料結尾完成公式的複製和計算。公式複製的快慢可由小實心十字游標距虛框的遠近來調節：小實心十字游標距虛框越遠，複製越快；反之，複製越慢。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將此空值解釋為零值)或包含零值儲存格的儲存格引用。解決辦法是修改儲存格引用，或者在用作除數的儲存格中輸入不為零的值。錯誤資訊3VALUE!當使用不正確的參

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

9、名稱”，再按一下“定義”命令，如果所需名稱沒有被列出，請使用“定義”命令添加相應的名稱。(2)如果是名稱，函數名拼寫錯誤應修改拼寫錯誤。(3)確認公式中使用的所有區域引用都使用了冒號(：)。例如：SUM(A1：C10)。注意將公式中的文本括在雙引號中。錯誤資訊5NUM!當公式或函數中使用了不正確的數位時將產生錯誤資訊NUM!。要解決問題首先要確認函數中使用的參數類型正確。還有一種可能是由公式產生的數位太大或太小，Excel不能表示，如果是這種情況就要修改公式，使其結果在110307和110307之間。,六 批量修改資料,在EXCEL表格資料都已被填好的情況下，如何方便地對任一列（行）的資料進行

10、修改呢？比如我們做好一個EXCEL表格，填好了資料，現在想修改其中的一列（行），例如：想在A列原來的資料的基礎上加8，有沒有這樣的公式？是不是非得手工的一個一個資料地住上加？對於這個問題我們自然想到了利用公式，當你利用工式輸入A1=A1+8時，你會得到EXCEL的一個警告：“MICROSOFTEXCEL不能計算該公式”只有我們自己想辦法了，這裡介紹一種簡單的方法：,六 批量修改資料（2）,第一步：在想要修改的列（假設為A列）的旁邊，插入一個臨時的新列（為B列），並在B列的第一個儲存格（B1）裡輸入8。第二步：把滑鼠放在B1的或下角，待其變成十字形後住下拉直到所需的數據長度，此時B列所有的資料都

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

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

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

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

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

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

17、1”複合鍵，即可快速進入VBA編輯狀態。在左側“工程資源管理器”中，展開“VBAProject(PERSONAL.XLSB)”選項(這就是“個人宏工作簿”)，按兩下其中的“模組1”，然後用下述代碼替換右側編輯區中的原有代碼:Sub smdy()On Error Resume Nextx=ExecuteExcel4Macro(get.document(50)For i=1 To Int(x/2)+1ExecuteExcel4Macro PRINT(2,&2*i-1&,&2*i-1&,1,2,TRUE,FALSE)Next iMsgBox 請將打印紙反向裝入印表機中,vbOKOnly,列印另一面F

18、or j=1 To Int(x/2)+1ExecuteExcel4Macro PRINT(2,&2*j&,&2*j&,1,2,TRUE,FALSE)Next jEnd Sub輸入完成後，關閉VBA編輯視窗返回到Excel編輯狀,十一 利用高級篩選功能巧妙刪除Excel重複記錄,Excel有一個小小的缺陷，那就是無法自動識別重複的記錄。為了清除這些重複記錄，有的朋友是一個一個手工刪，既費時又費力。雖說Excel中並沒有提供給我們清除重複記錄這樣的功能，但我們還可以利用它的高級篩選功能來達到相同的目的。今天，筆者就來向大家介紹一個如何利用Excel的“高級篩選”巧妙刪除重複記錄的小技巧。具體操作步

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

20、一利用高級篩選功能巧妙刪除Excel重複記錄,4.此時，Excel便會將所有的重複記錄自動刪除掉，確認無誤後，您就可以把新生成的資料清單拷貝到新的工作表中繼續使用了。如圖所示,十二 快速查找具有條件格式的Excel儲存格,如果Excel工作表的一個或多個儲存格具有條件格式(條件格式：當指定條件為真時，Excel自動應用於儲存格的格式，例如，儲存格網底或字體顏色。)，則可以快速找到它們以便複製、更改或刪除條件格式。可以使用“定位條件”命令只查找具有特定條件格式的儲存格，或查找所有具有條件格式的儲存格。查找所有具有條件格式的儲存格1、按一下任何沒有條件格式的儲存格。2、在“開始”選項卡上的“編輯”

21、組中，按一下“查找和選擇”旁邊的箭頭，然後按一下“條件格式”。只查找具有相同條件格式的儲存格1、按一下有要查找的條件格式的儲存格。2、在“開始”選項卡上的“編輯”組中，按一下“查找和選擇”旁邊的箭頭，然後按一下“定位條件”。3、按一下“條件格式”。4、在“資料有效性”下面按一下“相同”。,十三函數-幫你成為Excel高手,1.Vlookup函數2.If函數3.IS函數4.組合函數,十四 換個口味 把Excel儲存格變得凹凸有致,厭煩了Excel儲存格那一成不變的平面填充樣式了嗎？製作凹凸不平有點立體效果的試算表來換換口味吧。如圖1所示。,先來個的簡單的。如圖2所示。相鄰行之間顯示凹凸效果。,圖

22、1,圖2,十四 換個口味 把Excel儲存格變得凹凸有致,具體過程是：先選中圖示區域A1:H12儲存格，然後點擊功能區“開始”選項卡中“字體”功能組中“填充顏色”按鈕右側的小三角形，然後在“主題顏色”清單中選擇“白色，背景1，深色15%”，如圖3所示，將所選儲存格填充成淺灰色。,圖3,十四 換個口味 把Excel儲存格變得凹凸有致,現在仍然選中這些儲存格區域，點擊功能區“樣式”選項卡中“條件格式”按鈕下的小三角形，在彈出的快顯功能表中點擊“新建規則”命令，如圖4所示。,圖4,十四 換個口味 把Excel儲存格變得凹凸有致,在打開的“新建格式規則”對話方塊中，點擊“選擇規則類型”清單中“使用公式

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

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

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

26、著表格其他內容的調整而發生變化的話，那麼在制作EXCEL表格時就應將序號這一欄位與其他欄位分開，如在“總分”與“排名”之間空開一列，為了不影響顯示美觀，可將這一空的列字段設為隱藏，這樣在調整表格（資料清單）的內容時就不會影響序號了。,十六 举手之劳 实现Excel 2007文档逆序打印,在Word 2007中，只要依次點擊左上角Office徽標列印，在打開的“列印”設置視窗中點擊“選項”按鈕，在接著出現的“Word選項”視窗中切換到“高級”標籤頁，然後勾選“列印”項中的“逆序打印頁面”選項(圖1)，這樣印表機列印時就會從最後一頁開始逆序列印到第一頁，列印完畢後，最後一頁自然處於最底下，而第一頁

27、處於最上面，如此則無須逆序重排文檔，直接就可以裝訂成冊，對於長篇打印文檔尤其方便。,與Word相比，Excel的列印功能就相形見絀，Excel 2007中也未見對列印功能有任何改善。但我們只要稍費心思，也能讓Excel 2007輕鬆擁有“逆序列印”功能。,十六 举手之劳 实现Excel 2007文档逆序打印,一.打印机属性巧设置有些打印机只要安装了自带的驱动程序，就可以在打印属性中找到类似“逆序打印”的打印选项。以笔者的Canon PIXMA iP1000为例(事先请安装打印机附带光盘中的驱动程序)，打印时，只要打开Excel 2007工作簿，依次点击左上角Office徽标打印，打开“打印”设

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