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

Excel使用技巧,Smile Han 2009/04/22 Rev：X2,在工作當中用試算表來處理資料將會更加迅速、方便，而在各種試算表處理軟體中，Excel以其功能強大、操作方便著稱，贏得了廣大用戶的青睞。雖然Excel使用很簡單，不過真正能用好Excel的用戶並不多，很多人一直停留在錄入資料的水準，本文將向你介紹一些非常使用的技巧，掌握這些技巧將大大提高你的工作效率,Excel -引言,目錄,一 讓Excel資料登錄不間斷 二 快速定義工作簿格式 三 快速複製公式 Shift“拖放”的妙用 五 如何避免Excel中的錯誤資訊 六 批量修改資料 插入超連接及對象 Excel2007也能截圖 Excel2007也玩重複標題行 十 為Excel2007添加一個“雙面列印”按鈕 十一 利用高級篩選功能巧妙刪除Excel重複記錄 十二 快速查找具有條件格式的Excel儲存格 函數-幫你成為Excel高手 換個口味 把Excel儲存格變得凹凸有致 十五 在一個Excel檔中一次列印多個工作表,一 讓Excel資料登錄不間斷,我們在Excel2007中錄入資料時，通常會用右鍵頭或回車鍵進行儲存格切換，當輸入到行或列的末尾，右鍵頭和回車鍵就不起作用啦！ 這時候需要用滑鼠來定位到下一行或下一列的首儲存格。雖然不是很麻煩，但在資料量巨大的時候，時不時要去動下滑鼠，無法不間斷輸入資料，總歸覺得有點不方便。 下面兩種方法可有效地解決這個問題： 1.如果按行進行輸入，用Tab來向右切換儲存格，輸入到行末尾的儲存格時，直接按回車鍵，可立即切換到下一行的行首儲存格。 2.加果按列進行輸入，先將資料錄入區域選中，再從第一個儲存格開始輸入。每一個儲存格完成後，按回車鍵，當輸入到列末尾儲存格時，依舊按回車鍵，游標將自動轉到第二列的列首。,二 快速定義工作簿格式,首先選定需要定義格式的工作簿範圍，按一下“格式”功能表的“樣式”命令，打開“樣式”對話方塊；然後從“樣式名”清單方塊中選擇合適的“樣式”種類，從“樣式包括”清單方塊中選擇是否使用該種樣式的數位、字體、對齊、邊框、圖案、保護等格式內容；按一下“確定”按鈕，關閉“樣式”對話方塊，Excel工作簿的格式就會按照使用者指定的樣式發生變化，從而滿足了用戶快速、大批定義格式的要求,三 快速複製公式,複製是將公式應用於其它儲存格的操作，最常用的有以下幾種方法： 是拖動制複製。操作方法是：選中存放公式的儲存格，移動空心十字游標 至儲存格右下角。待游標變成小實心十字時，按住滑鼠左鍵沿列（對行計算時 ）或行（對列計算時）拖動，至資料結尾完成公式的複製和計算。公式複製的 快慢可由小實心十字游標距虛框的遠近來調節：小實心十字游標距虛框越遠， 複製越快；反之，複製越慢。 2也可以輸入複製。此法是在公式輸入結束後立即完成公式的複製。操作方法 ：選中需要使用該公式的所有儲存格，用上面介紹的方法輸入公式，完成後按 住Ctrl鍵並按回車鍵，該公式就被複製到已選中的所有儲存格。 3還可以選擇性粘貼。操作方法是：選中存放公式的儲存格，按一下Excel工具 列中的“複製”按鈕。然後選中需要使用該公式的儲存格，在選中區域內按一 下滑鼠右鍵，選擇快捷選單中的“選擇性粘貼”命令。打開“選擇性粘貼”對 話方塊後選中“粘貼”命令，按一下“確定”，公式就被複製到已選中的儲存 格。,四 Shift“拖放”的妙用,在拖放選定的一個或多個儲存格至新的位置時，同時按住Shift鍵可以快速修改儲存格內容的次序。 具體方法為：選定儲存格，按下Shift鍵，移動滑鼠指標至儲存格邊緣，直至出現拖放指針箭頭“?“，然後進行拖放操作。上下拖拉時滑鼠在儲存格間邊界處會變為一個水準“工“狀標誌，左右拖拉時會變為垂直“工“狀標誌，釋放滑鼠按鈕完成操作後，儲存格間的次序即發生了變化。這種簡單的方法節省了幾個剪切和粘貼或拖放操作，非常方便。,五 如何避免Excel中的錯誤資訊,在Excel中輸入或編輯公式後，有可能不能正確計算出結果，Excel將顯示一個錯誤資訊，引起錯誤的原因並不都是由公式本身有錯誤產生的。下面我們將介紹五種在Excel中常出現的錯誤資訊，以及如何糾正這些錯誤。 錯誤資訊1 輸入到儲存格中的資料太長或儲存格公式所產生的結果太大，在儲存格中顯示不下時，將在儲存格中顯示。可以通過調整列標之間的邊界來修改列的寬度。 如果對日期和時間做減法，請確認格式是否正確。Excel中的日期和時間必須為正值。如果日期或時間產生了負值，將在整個儲存格中顯示。如果仍要顯示這個數值，请按一下右鍵選擇“設置單元格格式”功能表中的“儲存格”命令，再按一下“數位”選項卡，然後選定一個不是日期或時間的格式。,五 如何避免Excel中的錯誤資訊（2）,錯誤資訊2DIV/0! 輸入的公式中包含明顯的除數0，例如120/0，則會產生錯誤資訊DIV/0!。 或在公式中除數使用了空儲存格(當運算對象是空白儲存格，Excel將此空值解釋為零值)或包含零值儲存格的儲存格引用。解決辦法是修改儲存格引用，或者在用作除數的儲存格中輸入不為零的值。 錯誤資訊3VALUE! 當使用不正確的參數或運算子時，或者當執行自動校正公式功能時不能更正公式，都將產生錯誤資訊VALUE!。 在需要數位或邏輯值時輸入了文本，Excel不能將文本轉換為正確的資料類型。這時應確認公式或函數所需的運算子或參數正確，並且公式引用的儲存格中包含有效的數值。例如，儲存格B3中有一個數字，而儲存格B4包含文本，則公式=B3B4將返回錯誤資訊VALUE!。,五 如何避免Excel中的錯誤資訊（3）,錯誤資訊4NAME? 在公式中使用了Excel所不能識別的文本時將產生錯誤資訊NAME?。可以從以下幾方面進行檢查糾正錯誤： (1)如果是使用了不存在的名稱而產生這類錯誤，應確認使用的名稱確實存在。在“插入”功能表中指向“名稱”，再按一下“定義”命令，如果所需名稱沒有被列出，請使用“定義”命令添加相應的名稱。 (2)如果是名稱，函數名拼寫錯誤應修改拼寫錯誤。 (3)確認公式中使用的所有區域引用都使用了冒號(：)。例如：SUM(A1：C10)。 注意將公式中的文本括在雙引號中。 錯誤資訊5 NUM! 當公式或函數中使用了不正確的數位時將產生錯誤資訊NUM!。 要解決問題首先要確認函數中使用的參數類型正確。還有一種可能是由公式產生的數位太大或太小，Excel不能表示，如果是這種情況就要修改公式，使其結果在110307和110307之間。,六 批量修改資料,在EXCEL表格資料都已被填好的情況下，如何方便地對任一列（行）的資料進行修改呢？ 比如我們做好一個EXCEL表格，填好了資料，現在想修改其中的一列（行），例如：想在A列原來的資料的基礎上加8，有沒有這樣的公式？是不是非得手工的一個一個資料地住上加？對於這個問題我們自然想到了利用公式，當你利用工式輸入A1=A1+8時，你會得到EXCEL的一個警告：“MICROSOFT EXCEL不能計算該公式”只有我們自己想辦法了，這裡介紹一種簡單的方法：,六 批量修改資料（2）,第一步：在想要修改的列（假設為A列）的旁邊，插入一個臨時的新列（為B列），並在B列的第一個儲存格（B1）裡輸入8。 第二步：把滑鼠放在B1的或下角，待其變成十字形後住下拉直到所需的數據長度，此時B列所有的資料都為8。 第三步：在B列上按一下滑鼠右鍵，“複製” B列。 第四步：在A列按一下滑鼠的右鍵，在彈出的對話方塊中按一下“選擇性粘貼”，在彈出的對話方塊中選擇“運算”中的你所需要的運算子，在此我們選擇“加”，這是本方法的關鍵所在。 第五步：將B列刪除。 怎麼樣？A列中的每個資料是不是都加上了8呢？同樣的辦法可以實現對一 （行）的乘，除，減等其它的運算操作。原表格的格式也沒有改變。 此時整個工作結束，使用熟練後，將花費不到十秒鐘,七 插入超連接及對象,在功能欄點擊“插入”選擇“超級連接”，並根據位址選擇需要連接的文檔 在功能欄點擊“插入”選擇“對象”，然後點擊“由文件創建”，選 定需創建的文件，勾選“連接”&“顯示為圖標”，爲了美觀，請記 住修改連接檔的名稱，否則插入的對象將默認“Office軟件的名稱”。,八 Excel2007也能截圖,工作中我們常常需要將Excel2007文檔中一部分圖截出來供他人參考，如何 實現呢？ 其實大可不必如此麻煩，因為在Excel2007中已經集成了截圖的功能，利用 這一功能，我們就能把選定的儲存格區域方便地轉換為圖片。 首先選中需要複製成圖片的儲存格區域，然後依次選擇“開始-剪貼板-粘貼” 命令（如圖1所示）,八 Excel2007中利用條件格式確保資料登錄,在下拉式功能表中選擇“以圖片格式-複製為圖片”，彈出“複製圖片”對話方塊（如圖2所示）， 選擇“圖片”後點擊“確定”按鈕。這時選定的表格區域就已經被覆製成圖片 了。 再將這張圖片粘貼到需要使用的地方即可。,九 Excel2007也玩重複標題行,在Word中，提供了表格標題行重複按鈕，只要選中標題行，按一下“重複標題行”按鈕即可讓每一頁都顯示設定的標題行，那麼EXCEL中能不能重複標題行呢？當然可以。你只要按照筆者如下步驟設置即可實現。 以EXCEL2007為例，首先打開EXCEL2007，製作好工作表，然後在頂端行中或者左端列中輸入需要作為每頁頁首標題行的內容，然後按一下“頁面配置”選項，接下來按一下“頁面配置”選項右側的擴展按鈕，在彈出的對話方塊中按一下“工作表”選項卡，介面如圖1。,九 Excel2007也玩重複標題行（2）,按一下頂端標題行或者左端標題列右側的圖示按鈕，這時候會彈出來一個設置對話方塊（如圖2），這時候，我們只要選擇需要重複的頂端標題行或者左端標題列中的內容，然後按一下圖2對話方塊右側的圖示按鈕，最後按一下工作表中的“確定”按鈕就好了。現在就趕緊到預覽列印中看看，是不是實現了標題行重複了呢？,十 為Excel2007添加一個“雙面列印”按鈕,大家知道，在Word中可以將“奇數頁”和“偶數頁”分開列印，從而 實現多頁文檔的“雙面列印”效果。但是，在Excel中卻沒有這一功能 。下面，我們就以Excel2007為例，為其添加一個具有“雙面列印”功 能的按鈕。 一、展開“開發工具”選項卡 要在Excel中實現“雙面列印”，就要用到其內嵌的VBA功能，因此， 需要先展開“開發工具”選項卡。 啟動Excel2007，按一下左上角的軟體圖示，在隨後出現的下拉式功能表中( 如圖1)，選擇“Excel選項”選項，打開“Excel選項”對話方塊(如圖2) 圖1 圖2,十 為Excel2007添加一個“雙面列印”按鈕（2）,二、生成“個人宏工作簿” 為了讓“雙面列印”按鈕對所有工作簿都有效，我們需要將其保存在“個 人宏工作簿”中。第一次使用“巨集”這個功能時，需要先讓軟體生成一 個隱藏的“個人宏工作簿”。 切換到“開發工具”選項卡中，按一下其中的“錄製巨集”按鈕，打開“ 錄製新宏”對話方塊(如圖3)。輸入一下宏名稱(如“smdy”)，按“保存在” 右側的下拉按鈕，在隨後出現的下拉清單中，選擇“個人巨集工作簿”選 項，確定進入“巨集”錄製狀態。 圖3,不需要進行任何操作，直接按一下“開發工具”選項卡中的“停止錄製”按鈕，系統會自動生成一個隱藏的“個人宏工作簿”。 特別提醒:以後需要在“個人巨集工作簿”中編輯巨集時，就不需要再進入此步操作了。,十 為Excel2007添加一個“雙面列印”按鈕（3）,三、編輯“雙面列印”代碼 按一下“開發工具”選項卡上的“Visual Basic”按鈕，進入VBA編輯狀態( 如圖4)。下拉按鈕，在隨後出現的下拉清單中，選擇“個人巨集工作簿” 選項，確定進入“巨集”錄製狀態。 圖4,特別提醒:按“Alt+F11”複合鍵，即可快速進入VBA編輯狀態。 在左側“工程資源管理器”中，展開“VBAProject(PERSONAL.XLSB)”選項(這就是“個人宏工作簿”)，按兩下其中的“模組1”，然後用下述代碼替換右側編輯區中的原有代碼: Sub smdy() On Error Resume Next x = ExecuteExcel4Macro(“get.document(50)“) For i = 1 To Int(x / 2) + 1 ExecuteExcel4Macro “PRINT(2,“ & 2 * i - 1 & “,“ & 2 * i - 1 & “,1,2,TRUE,FALSE)“ Next i MsgBox “請將打印紙反向裝入印表機中“,vbOKOnly, “列印另一面“ For j = 1 To Int(x / 2) + 1 ExecuteExcel4Macro “PRINT(2,“ & 2 * j & “,“ & 2 * j & “,1,2,TRUE,FALSE)“ Next j End Sub 輸入完成後，關閉VBA編輯視窗返回到Excel編輯狀,十一 利用高級篩選功能巧妙刪除Excel重複記錄,Excel有一個小小的缺陷，那就是無法自動識別重複的記錄。為了清除這些 重複記錄，有的朋友是一個一個手工刪，既費時又費力。 雖說Excel中並沒有提供給我們清除重複記錄這樣的功能，但我們還可以利 用它的高級篩選功能來達到相同的目的。今天，筆者就來向大家介紹一個 如何利用Excel的“高級篩選”巧妙刪除重複記錄的小技巧。 具體操作步驟如下: 打開一份帶有重複記錄的Excel文檔。如圖1所示 (注:本圖已用Photoshop 處理，其中彩色部分為重複的記錄),十一利用高級篩選功能巧妙刪除Excel重複記錄,2.選中圖表中的所有記錄(注意，此時應將每列的標題行也選擇上，否则篩 選完的資料表中將不再包含有該標題行)，執行“數據”菜單“篩選高級篩 選”命令 3.在彈出的“高級篩選”對話方塊中選擇“將篩選結果複製到其他位置”一項， 並點擊“複製到”選擇框後面的範圍按鈕來選擇一塊區域以存放篩選後的數 據(注意:此處千萬不要與原資料所處的儲存格相重合，否則資料表將会混亂 )，最後，勾選“選擇不重複的記錄”核取方塊後，點擊“確定”按鈕即可。 如圖所示：,十一利用高級篩選功能巧妙刪除Excel重複記錄,4.此時，Excel便會將所有的重複記錄自動刪除掉，確認無誤後，您就可以把 新生成的資料清單拷貝到新的工作表中繼續使用了。如圖所示,十二 快速查找具有條件格式的Excel儲存格,如果Excel工作表的一個或多個儲存格具有條件格式(條件格式：當指定條件為 真時，Excel自動應用於儲存格的格式，例如，儲存格網底或字體顏色。)，則 可以快速找到它們以便複製、更改或刪除條件格式。可以使用“定位條件”命 令只查找具有特定條件格式的儲存格，或查找所有具有條件格式的儲存格。 查找所有具有條件格式的儲存格 1、按一下任何沒有條件格式的儲存格。 2、在“開始”選項卡上的“編輯”組中，按一下“查找和選擇”旁邊的箭頭，然後按一下“條件格式”。 只查找具有相同條件格式的儲存格 1、按一下有要查找的條件格式的儲存格。 2、在“開始”選項卡上的“編輯”組中，按一下“查找和選擇”旁邊的箭頭，然後按一下“定位條件”。 3、按一下“條件格式”。 4、在“資料有效性”下面按一下“相同”。,十三函數-幫你成為Excel高手,1.Vlookup函數 2.If函數 3. IS函數 4.組合函數,十四 換個口味 把Excel儲存格變得凹凸有致,厭煩了Excel儲存格那一成不變的平面填充樣式了嗎？製作凹凸不平有 點立體效果的試算表來換換口味吧。如圖1所示。,先來個的簡單的。如圖2所示。相鄰行之間顯示凹凸效果。,圖1,圖2,十四 換個口味 把Excel儲存格變得凹凸有致,具體過程是：先選中圖示區域A1:H12儲存格，然後點擊功能區“開始 ”選項卡中“字體”功能組中“填充顏色”按鈕右側的小三角形，然 後在“主題顏色”清單中選擇“白色，背景1，深色15%”，如圖3所示 ，將所選儲存格填充成淺灰色。,圖3,十四 換個口味 把Excel儲存格變得凹凸有致,現在仍然選中這些儲存格區域，點擊功能區“樣式”選項卡中“條件 格式”按鈕下的小三角形，在彈出的快顯功能表中點擊“新建規則” 命令，如圖4所示。,圖4,十四 換個口味 把Excel儲存格變得凹凸有致,在打開的“新建格式規則”對話方塊中，點擊“選擇規則類型”清單 中“使用公式確定要設置格式的儲存格”，然後在下方的“為符合此 公式的值設置格式”輸入框中輸入公式“=mod(row(),2)=0”，如圖5所 示。其用意是指定行號是偶數的儲存格為要設置格式的儲存格。,圖5,十四 換個口味 把Excel儲存格變得凹凸有致,再點擊對話方塊右下角的“格式”按鈕，打開“設置單元格格式”對 話框。點擊“邊框”選項卡，點擊左邊“顏色”下拉按鈕，然後在列 表中選中左上角的“白色，背景1”顏色，並在右側“邊框”預覽窗口 的四邊邊框線中點擊下邊線，然後再選擇顏色為“白色，背景1，深 色50%”，點擊上邊線，如圖6所示。這樣，就可以使儲存格上邊線為 深灰色線，下邊線為白色線，而左右兩邊均無邊框。,圖6,十四 換個口味 把Excel儲存格變得凹凸有致,一路確定，關閉全部的對話方塊後，就可以得到圖2所示的效果了 至於圖1所示效果，其實可以由此類推得到。只要我們設置“行號 為偶數且列數為奇數”或“行號為奇數且列數為偶數”的儲存格左邊 框和上邊框為深灰色，則下邊框和右邊框為白色，那麼就可以得到圖 示效果了。所以，用上面的方法就完全可以實現，只是在設置條件格 式的公式時應該將公式寫成 “=OR(AND(MOD(ROW(),2)=0,MOD(COLUMN(),2)=1),AND(MOD(R OW(),2)=1,MOD(COLUMN(),2)=0)”，並注意邊框的格式如前所述就 可以了。 如果我們將上邊框和左邊框設置成白色，而下邊框和右邊框設置 成深灰色，那麼可以得到恰好相反的凹凸效果。此外，我們在指定邊 框線的同時，也可以指定儲存格的填充顏色，那麼就可以得到更為豐 富的效果了。,十五 在一個Excel檔中一次列印多個工作表,把滑鼠移到工作表的名稱處（若你沒有特別設置的話，Excel自動設置 的名稱是“sheet1、sheet2、sheet3.”），然後點右鍵，在彈出的菜 單中選擇“選擇全部工作表”的功能表項目，這時你的所有操作都是針對 全部工作表了，不管是設置頁眉和頁腳還是列印你工作表。 EXCEL中有序號一欄，由於對表格進行調整，序號全亂了,可要是手動 一個一個改序號實在太慢太麻煩，用什麼方法可以快速解決？ 如果序號是不應隨著表格其他內容的調整而發生變化的話，那麼在制 作EXCEL表格時就應將序號這一欄位與其他欄位分開，如在“總分” 與“排名”之間空開一列，為了不影響顯示美觀，可將這一空的列字 段設為隱藏，這樣在調整表格（資料清單）的內容時就不會影響序號 了。,十六 举手之劳 实现Excel 2007文档