Excel工作中的使用小技巧.pptx

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,Page:,#,Foxconn Confidential,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2019/12/5,#,在工作當中用試算表來處理資料將會更加迅速、方便，而在各種試算表處理軟體中，,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,）,錯誤資訊,2,DIV/0!,輸入的公式中包含明顯的除數,0,，例如,120/0,，則會產生錯誤資訊,DIV/0!,。,或在公式中除數使用了空儲存格,(,當運算對象是空白儲存格，,Excel,將此空值解釋為零值,),或包含零值儲存格的儲存格引用。解決辦法是修改儲存格引用，或者在用作除數的儲存格中輸入不為零的值,。,錯誤資訊,3,VALUE!,當使用不正確的參數或運算子時，或者當執行自動校正公式功能時不能更正公式，都將產生錯誤資訊,VALUE!,。,在需要數位或邏輯值時輸入了文本，,Excel,不能將文本轉換為正確的資料類型。這時應確認公式或函數所需的運算子或參數正確，並且公式引用的儲存格中包含有效的數值。例如，儲存格,B3,中有一個數字，而儲存格,B4,包含文本，則公式,=B3,B4,將返回錯誤資訊,VALUE!,。,五,如何避免,Excel,中的,錯誤資訊（,3,）,錯誤資訊,4,NAME?,在公式中使用了,Excel,所不能識別的文本時將產生錯誤資訊,NAME?,。可以從以下幾方面進行檢查糾正錯誤：,(1),如果是使用了不存在的名稱而產生這類錯誤，應確認使用的名稱確實存在。在“插入”功能表中指向“名稱”，再按一下“定義”命令，如果所需名稱沒有被列出，請使用“定義”命令添加相應的名稱。,(2),如果是名稱，函數名拼寫錯誤應修改拼寫錯誤。,(3),確認公式中使用的所有區域引用都使用了冒號,(,：,),。例如：,SUM(A1,：,C10),。,注意將公式中的文本括在雙引號中。,錯誤資訊,5,NUM!,當公式或函數中使用了不正確的數位時將產生錯誤資訊,NUM!,。,要解決問題首先要確認函數中使用的參數類型正確。還有一種可能是由公式產生的數位太大或太小，,Excel,不能表示，如果是這種情況就要修改公式，使其結果在,1,10307,和,1,10307,之間。,六,批量,修改資料,在,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 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,列印另一面,For 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,的,“,高級篩選,”,巧妙刪除重複記錄的小技巧,。,具體操作步驟如下,:,打開,一份帶有重複記錄的,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,文档逆序打印,在,Word 2007,中，只要依次點擊左上角,Office,徽標列印，在打開的“列印”,設置視窗中點擊“選項”按鈕，在接著出現的“,Word,選項,”視窗中切換到,“高級”標籤頁，然後勾選“列印”項中的“逆序打印頁面”選項,(,圖,1),，這樣,印表機列印時就會從最後一頁開始逆序列印到第一頁，列印完畢後，最後一頁,自然處於最底下，而第一頁處於最上面，如此則無須逆序重排文檔，直接就可,以裝訂成冊，對於長篇打印文檔尤其方便。,與,Word,相比，,Excel,的列印功能就相形見絀，,Excel 2007,中也未見對列印功能有任何改善。但我們只要稍費心思，也能讓,Excel 2007,輕鬆擁有“逆序列印”功能。,十六 举手之劳 实现,Excel 2007,文档逆序打印,一,.,打印机属性巧设置,有些打印机只要安装了自带的驱动程序，就可以在打印属性中找到类似“逆,序打印”的打印选项。以笔者的,Canon PIXMA iP1000,为例,(,事先请安装打印机,附带光盘中的驱动程序,),，打印时，只要打开,Excel 2007,工作簿，依,次点击左上角,Office,徽标打印，打开“打印”设置窗口，在打印机名称中选,中“,Canon PIXMA iP1000”,，然后点击“属性”按钮，调出“,Canon PIXMA,iP1000,属性”窗口，切换到“页设置”标签页，勾选“反向”复选框再点击,“确定”按钮即可,(,图,2),。,十六 举手之劳 实现,Excel 2007,文档逆序打印,打印机属性巧设置,有些打印机只要安装了自带的驱动程序，就可以在打印属性中找到类似“逆,序打印”的打印选项。以笔者的,Canon PIXMA iP1000,为例,(,事先请安装打印机,附带光盘中的驱动程序,),，打印时，只要打开,Excel 2007,工作簿，依,次点击左上角,Office,徽标打印，打开“打印”设置窗口，在打印机名称中选,中“,Canon PIXMA iP1000”,，然后点击“属性”按钮，调出“,Canon PIXMA,iP1000,属性”窗口，切换到“页设置”标签页，勾选“反向”复选框再点击,“确定”按钮即可,(,图,2),。,Q&A,