EXCEL技巧汇总.docx

1、EXCEL技巧汇总EXCEL技巧匯總利用條件求和嚮導對資料按條件進行匯總在一個表格中經常需要將表中的資料按一定的條件進行求和計算。如一個人事職稱檔案表，需要統計助理工程師、工程師、會計師等職稱的人數各為多少。對類似問題，利用條件求和嚮導便可很好地解決。具體方法是選項工具*增益集，在開啟的視窗中選條件求和嚮導，然後按確定按鈕，使用增益集; 選項工具*嚮導*條件求和，進入求和嚮導; 在求和嚮導中首先按下輸入框右邊的小按鈕，然後在表格中直接選定包含資料的區域，再按下下一步，在求和列中選定求和資料所在列，在條件項中增加求和的條件，根據嚮導的步驟指定存放匯總結果的單元格，即將游標置於輸入項中，直接在表格

2、中選相應單元格即可，最後按下完成按鈕。在匯總結果單元格中將出現預期的匯總結果，且一旦設定，匯總結果單元格會隨著資料變化自動匯總，一勞永逸。要取消匯總結果只需點擊匯總結果單元格，將公式去掉即可。當然，熟悉函數公式的用戶，可直接定義單元格的公式，效果是一樣的。把Excel表格轉換為圖片許多報紙和雜誌都介紹過在Excel中，同時按住Shift鍵點擊文件表單，原來的關閉表單項就會變成全部關閉。如果我們在按下Shift鍵的同時點擊編輯表單，原來的複製和貼上就會變成複製圖片和貼上圖片。利用這一功能，我們可以將一個資料表以圖片的形式進行複製，從而將其轉換為圖片。方法如下：首先選需要複製成圖片的單元格區域，然

3、後按住Shift鍵依次選項編輯複製圖片指令，接著彈出複製圖片視窗，選項圖片單選項後點擊確定按鈕，這時就將選定的表格區域複製成圖片了。最後複製到目標只需直接選項貼上指令即可（或者按Shift鍵再選項編輯貼上圖片指令）。我們還可以將其在Word中進行貼上。另外，在複製圖片時如果選項了如列印效果單選項，在貼上的時候如果表格沒有邊框，複製後的圖片也不會出現邊框。 將Excel的文本數位轉換成數位在Excel中，系統將前面帶有半形單引號的數位視為文本對待，而且為了某些需要，用戶可以通過格式表單中的單元格指令，將數位設定為文本格式。然而，現在我們卻需要把工作表中文本格式的數位轉換成數位，那麼以下方法可以一

4、試。1一次轉換一個單元格在工具表單上，按下選項，再按下錯誤檢查選擇項（一定要確保選了允許後台錯誤檢查和數位以文本形式儲存於複選框）。選任何在左上角有綠色錯誤指示符的單元格。在單元格旁邊，按下出現的按鈕，再按下轉換為數位。2一次轉換整個區域在某空白單元格中，輸入數位1。選該單元格，並在編輯表單上按下複製指令。選取需轉換的儲存於為文本數位的單元格區域。在編輯表單上，按下選項性貼上。在運算下，按下乘。按下確定按鈕。3處理特殊財務數位一些財務程序顯示負值時在該值右邊帶有負號-。要將此文本字串串轉換為數值，必須返回除最右邊字串（即負號）以外的所有文本字串串字串，然後乘以-1。例如，如果單元格A2中的值為

5、156-，那麼公式=LEFT(A2,LEN(A2)-1)*-1將文本轉換為數值-156，然後用上面的方法操作。快速隱藏單元格所在位置的行與列1. 按ctrl+9 可隱藏選單元格或區域所在的行2. 按ctrl+0 可隱藏選單元格或區域所在的列恢復隱藏的行和列的快捷鍵是：ctrl+shift+0ctrl+shift+9快速選項Excel工作表在Excel中，要在一個有很多個工作表的活頁簿中快速的選項某一個工作表，只需將滑鼠游標移到工作薄左下角的工作表滾動軸(Sheet Tabs)，然後點擊右鍵，在快捷表單中選項你想要的工作表 如何將多個工作表一次設定成同樣的頁首和頁尾？如何才能一次列印多個工作表？

6、如果在一個Excel文件中含有多個工作表，如何將多個工作表一次設定成同樣的頁首和頁尾？如何才能一次列印多個工作表？把滑鼠移到工作表的名稱處（若你沒有特別設定的話，Excel自動設定的名稱是sheet1、sheet2、sheet3.），然後點右鍵，在彈出的表單中選項選項全部工作表的表單項，這時你的所有操作都是針對全部工作表了，不管是設定頁首和頁尾還是列印你工作表。當然，如果只選項其中幾個工作表，你可用ctrl或shift與滑鼠結合選項 避開Excel的誤差微軟的Excel軟體在財務套用領域使用得非常廣泛、被許多服務機構用來處理財務資料。筆者就是用Excel來為服務機構生產資表的，不過筆者在使用程

7、序中發現，當用Excel進行複雜算術運算時，其計算出來的結果有時會與電子計算器的結果有些微小的差別，特別是在進行多項除法運算而留有餘數的情況下，算術誤差很容易產生，究其原因，就是Excel在除不盡的情況下，不會自動對數值進行四捨五入的操作。這在要求嚴格的財務報表上當然是不允許的，避開這種誤差的途徑有兩種：1 利用Round函數對中間值四捨五入Excel中Round函數的格式為：Round(數值，保留的小數位數），它的作用就是對指定數值進行四捨五入的操作並保留指定小數位數。比如Round(8.8888,2）的意思就是對8.8888的千分位進行四捨五入、結果就是8.89，當然為工作方便，此函數括號

8、內的數值也可以是單元格位址或別的算術公式，每個中間值經過這樣處理後得到的最終運算結果就和計算器算出來的一致了。2 正確設定Excel選項來避開誤差上述函數方法雖然方便，不過遇上大型Excel表格時，如果每個單元格都要設定這個函數，工作量未免太巨大，這時我們就可以進入Excel的工具表單選項重新計算，在活頁簿選項處勾選以顯示值為準，上述Excel誤差也可有效避免，而預設情況下這個小選項是沒有被勾選的。 如何快速輸入資料序列？如果你需要輸入諸如表格中的項目序號、日期序列等一些特殊的資料系列，千萬別逐條輸入，為何不讓Excel自動填充呢？在第一個單元格內輸入起始資料，在下一個單元格內輸入第二個資料，

9、選定這兩個單元格，將游標指向單元格右下方的填充柄，沿著要填充的方向移到填充柄，拖過的單元格中會自動按Excel內部規定的序列進行填充。如果能將自己經常要用到的某些有規律的資料（如辦公室人員名單），定義成序列，以備日後自動填充，豈不一勞永逸！選項工具表單中的選項指令，再選項自訂序列標籤， 在輸入項中輸入新序列，注意在新序列各項2間要輸入半形符號的逗號加以分隔（例如：張三，李四，王二），按下增加按鈕將輸入的序列儲存起來。只計算Excel公式的一部分在Excel中當我們偵錯一個複雜的公式時可能需要知道公式某一部分的值，可以用以下的辦法來獲得：雙按含有公式的單元格，選定公式中需要獲得值的那部分公式，按

10、F9鍵，Excel就會將被選定的部分取代成計算的結果，按Ctrl+Z可以恢復剛才的取代。如果選定的是整個公式的話，就可以看到最後的結果。例如，有這樣一條公式：(A1*B1+C1)/D1，選定公式中的A1*B1（假設A1和B1的值分別為12和8），按F9鍵，Excel就會將A1*B1這部分轉換為96。這時，公式就變成了(96+C1)/D1。如果繼續選定D1（假設D1的值為6），並按F9鍵，Excel會將公式轉換為(A1*B1+C1)/6。 當你完成了公式的計算部分，想恢復成原來的公式， 按Esc即可。在Excel中當我們偵錯一個複雜的公式時可能需要知道公式某一部分的值，可以用以下的辦法來獲得：雙

11、按含有公式的單元格，選定公式中需要獲得值的那部分公式，按F9鍵，Excel就會將被選定的部分取代成計算的結果，按Ctrl+Z可以恢復剛才的取代。如果選定的是整個公式的話，就可以看到最後的結果。如何不使顯示或列印出來的表格中包含有0值？通常情況下，我們不希望顯示或列印出來的表格中包含有0值，而是將其內容置為空。例如，圖1合計列中如果使用=b2+c2+d2公式，將有可能出現0值的情況，如何讓0值不顯示？方法一；使用加上If函數判斷值是否為0的公式，即： =if（b2c2d20， b2c2d2）方法二：選項工具選項視窗，在視窗選項中去掉零值選項。 方法三：使用自訂格式。 選 E2：E5區域，選項格式

12、單元格數位，從分類列表項中選項自訂，在格式項中輸入G/通用格式；-G/通用格式；，按確定按鈕即可。方法四：將0取代成空 (選項單元格匹配)方法五(根據5樓雨點)：定義目標EXCEL表，在條件格式中將單元格數值等於0，在格式中將顏色設為白色，兩次確定即可。定義表中的0值均為白色，列印時也不顯示 Excel中攝影的妙用在Excel中，如果需要在一個頁面中反映另外一個頁面的更改，我們一般用貼上連接等方式來實現。但是，如果需要反映的內容比較多，特別是目標位置的格式編排也必須反映出來的時候，再使用連接資料的方式就行不通了。好在天無絕人之路，Excel早為我們準備了照相機，你只要把希望反映出來的那部分內容

13、照下來，然後把照片貼上到其他的頁面即可。1、準備照相機1） 開啟Excel的工具表單，選項自訂對話視窗。2） 按下指令選擇項，在類別中選項工具，在右邊指令列表中找到攝影，並且將其拖到工作列的任意位置。2、給目標拍照假設我們要讓Sheet2中的部分內容自動出現在Sheet1中。1） 移到滑鼠並且選項Sheet2中需要拍照的內容。2） 用滑鼠按下工作列上準備好的照相機按鈕，於是這個選定的區域就被拍了下來。3、貼上照片1） 開啟Sheet1工作表。2） 在需要顯示照片的位置上按下滑鼠左鍵，被拍攝的照片就立即貼上過來了。在Sheet2中調整照片的各種格式，貼上到Sheet1中的內容同步發生變化，而且因

14、為插入的的確是一幅自動更新的圖像文件，所以，圖片工作列對這個照片也是有效的哦！你可以按幾個按鈕試試，這個照片還可以自由的旋轉呢！怎麼樣？這個數碼照相機還不錯吧！Excel 電子錶格運用技巧匯總目的在於 彙集電子錶格的各種使用技巧 共同提高 以後會不斷充實 並整理 希望大家也能都整理並回帖發表！Excel XP的八則快速輸入技巧 如果我們在用Excel XP處理龐大的資料訊息時，不注意講究技巧和方法的話，很可能會花費很大的精力。因此如何巧用Excel XP，來快速輸入訊息就成為各個Excel XP用戶非常關心的話題，筆者向大家介紹幾則這方面的小技巧。 1、快速輸入大量含小數點的數位 如果我們需要

15、在Excel XP工作表中輸入大量的帶有小數位的數位時，按照普通的輸入方法，我們可能按照數位原樣大小直接輸入，例如現在要在單元格中輸入0.05這個數位時，我們會把0.05原樣輸入到表格中。不過如果需要輸入若干個帶有小數點的數位時，我們再按照上面的方法輸入的話，每次輸入數位時都需要重複輸入小數點，這樣工作量會變大，輸入效率會降低。其實，我們可以使用Excel XP中的小數點自動定位功能，讓所有數位的小數點自動定位，從而快速提高輸入速度。在使用小數點自動定位功能時，我們可以先在Excel XP的編輯界面中，用滑鼠依次按下工具/選項/編輯標籤，在彈出的對話視窗中選自動設定小數點複選框，然後在位數微調

16、編輯項中按鍵輸入需要顯示在小數點右面的位數就可以了。以後我們再輸入帶有小數點的數位時，直接輸入數位，而小數點將在Enter鍵鍵後自動進行定位。例如，我們要在某單元格中按鍵輸入0.06的話，可以在上面的設定中，讓位數選項為2，然後直接在指定單元格中輸入6，Enter鍵以後，該單元格的數位自動變為0.06，怎麼樣簡單吧？ 2、快速錄入文本文件中的內容 現在您手邊假如有一些以純文本格式儲存的文件，如果此時您需要將這些資料製作成Excel XP的工作表，那該怎麼辦呢？重新輸入一遍，大概只有頭腦有毛病的人才會這樣做；將表單上的資料一個個複製/貼上到工作表中，也需花很多時間。沒關係！您只要在Excel X

17、P中巧妙使用其中的文本文件匯入功能，就可以大大減輕需要重新輸入或者需要不斷複製、貼上的巨大工作量了。使用該功能時，您只要在Excel XP編輯區中，依次用滑鼠按下表單欄中的資料/獲取外部資料/匯入文本文件指令，然後在匯入文本會話視窗選項要匯入的文本文件，再按下匯入鈕以後，程序會彈出一個文本匯入嚮導對話視窗，您只要按照嚮導的提示進行操作，就可以把以文本格式的資料轉換成工作表的格式了。 3、快速輸入大量相同資料 如果你希望在不同的單元格中輸入大量相同的資料訊息，那麼你不必逐個單元格一個一個地輸入，那樣需要花費好長時間，而且還比較容易出現錯誤。你可以通過下面的操作方法在多個相鄰或不相鄰的單元格中快速

18、填充同一個資料，具體方法為：首先同時選需要填充資料的單元格。若某些單元格不相鄰，可在按住Ctrl鍵的同時，點擊滑鼠左鍵，逐個選；其次輸入要填充的某個資料。按住Ctrl鍵的同時，按Enter鍵鍵，則剛才選的所有單元格同時填入該資料。 4、快速進行中英文輸入法切換 一張工作表常常會既包含有數位訊息，又包含有文字訊息，要錄入這樣一種工作表就需要我們不斷地在中英文之間反覆切換輸入法，非常麻煩，為了方便操作，我們可以用以下方法實現自動切換：首先用滑鼠選需要輸入中文的單元格區域，然後在輸入法表單中選項一個合適的中文輸入法；接著開啟有效資料對話視窗，選IME模式標籤，在模式項中選項開啟，按下確定按鈕；然後再

19、選輸入數位的單元格區域，在有效資料對話視窗中，按下IME模式選擇項，在模式項中選項關閉（英文模式）；最後按下確定按鈕，這樣用滑鼠分別在剛才設定的兩列中選單元格，五筆和英文輸入方式就可以相互切換了。 5、快速移除工作表中空行 移除Excel XP工作表中的空行，一般的方法是需要將空行都找出來，然後逐行移除，但這樣做操作量非常大，很不方便。那麼如何才能減輕移除工作表中空行的工作量呢？您可以使用下面的操作方法來進行移除：首先開啟要移除空行的工作表，在開啟的工作表中用滑鼠按下表單欄中的 插 入表單項，並從下拉表單中選項列，從而插入一新的列，在列中順序填入整數；然後根據其他任何一列將表中的行排序，使所有

20、空行都集中到表的底部。刪去所有空行中列的資料，以列重新排序，然後刪去列。按照這樣的移除方法，無論工作表中包含多少空行，您就可以很快地移除了。6、快速對不同單元格中字號進行調整 在使用Excel XP編輯文件時，常常需要將某一列的寬度固定，但由於該列各單元格中的字串數目不等，致使有的單元格中的內容不能完全顯示在螢幕上，為了讓這些單元格中的資料都顯示在螢幕上，就不得不對這些單元格重新定義較小的字號。如果依次對這些單元格中的字號調整的話，工作量將會變得很大。其實，您可以採用下面的方法來減輕字號調整的工作量：首先新增或開啟一個活頁簿，並選需要Excel XP根據單元格的寬度調整字號的單元格區域；其次按

21、下用滑鼠依次按下表單欄中的格式/單元格/對齊標籤，在文本控制下選縮小字體填充複選框，並按下確定按鈕；此後，當你在這些單元格中輸入資料時，如果輸入的資料長度超過了單元格的寬度，Excel XP能夠自動縮小字串的大小把資料調整到與列寬一致，以使資料全部顯示在單元格中。如果你對這些單元格的列寬進行了更改，則字串可自動增大或縮小字號，以適應新的單元格列寬，但是對這些單元格原設定的字體字號大小則保持不變。 7、快速輸入多個重複資料 在使用Excel XP工作表的程序中，我們經常要輸入大量重複的資料,如果依次輸入，無疑工作量是巨大的。現在我們可以借助Excel XP的巨集功能,來記錄首次輸入需要重複輸入的

22、資料的指令和程序,然後將這些指令和程序賦值到一個組合鍵或工作列的按鈕上,當按下組合鍵時,電腦就會重複所記錄的操作。使用巨集功能時，我們可以按照以下步驟進行操作:首先開啟工作表，在工作表中選要進行操作的單元格；接著再用滑鼠按下表單欄中的工具表單項，並從彈出的下拉表單中選項巨集子表單項，並從隨後彈出的下級表單中選項錄製新巨集指令；設定好巨集後，我們就可以對指定的單元格，進行各種操作，程序將自動對所進行的各方面操作記錄複製。 8、快速處理多個工作表 有時我們需要在Excel XP中開啟多個工作表來進行編輯，但無論開啟多少工作表，在某一時刻我們只能對一個工作表進行編輯，編輯好了以後再依次編輯下一個工作

23、表，如果真是這樣操作的話，我們倒沒有這個必要同時開啟多個工作表了，因為我們同時開啟多個工作表的目的就是要減輕處理多個工作表的工作量的，那麼我們該如何實現這樣的操作呢？您可採用以下方法：首先按住Shift鍵或Ctrl鍵並配以滑鼠操作，在活頁簿底部選項多個彼此相鄰或不相鄰的工作表標籤，然後就可以對其實行多方面的批量處理；接著在選的工作表標籤上按右鍵彈出快捷表單，進行插入和移除多個工作表的操作；然後在文件表單中選項頁面設定，將選的多個工作表設成相同的頁面模式；再通過編輯表單中的有關選項，在多個工作表範圍內進行尋找、取代、定位操作；通過格式表單中的有關選項，將選的多個工作表的行、列、單元格設成相同的樣

24、式以及進行一次性全部隱藏操作；接著在工具表單中選項選項，在彈出的表單中選項視窗和編輯按鈕，將選的工作表設成相同的視窗樣式和單元格編輯內容；最後選上述工作表集合中任何一個工作表，並在其上完成我們所需要的表格，則其它工作表在相同的位置也同時產生了格式完全相同的表格。高效辦公Excel排序方法集中營排序是資料處理中的經常性工作，Excel排序有序數計算（類似成績統計中的名次）和資料重排兩類。本文以幾個車間的產值和名稱為例，介紹Excel 2000/XP的資料排序方法。 一、數值排序 1.RANK函數 RANK函數是Excel計算序數的主要工具，它的語法為:RANK （number，ref，order

25、），其中number為參與計算的數位或含有數位的單元格，ref是對參與計算的數位單元格區域的絕對引用，order是用來說明排序方式的數位（如果order為零或省略，則以降序方式指出結果，反之按升序方式）。 例如圖1中E2、E3、E4單元格存放一季度的總產值，計算各車間產值排名的方法是:在F2單元格內輸入公式=RANK（E2，$E$2: $E$4），敲Enter鍵即可計算出鑄造車間的產值排名是2。再將F2中的公式複製到記事本，選F3、 F4單元格按Ctrl+V，就能計算出其餘兩個車間的產值排名為3和1。如果B1單元格中輸入的公式為=RANK（E2，$E$2:$E$4，1），則計算出的序數按升序方

26、式排列，即2、1和3。 需要注意的是:相同數值用RANK函數計算得到的序數（名次）相同，但會導致後續數位的序數空缺。假如上例中F2單元格存放的數值與 F3相同，則按本法計算出的排名分別是3、3和1（降序時）。 2.COUNTIF函數 COUNTIF函數可以統計某一區域中符合條件的單元格數目，它的語法為COUNTIF（range，criteria）。其中range為參與統計的單元格區域，criteria是以數位、陳述式或文本形式定義的條件。其中數位可以直接寫入，陳述式和文本必須加引號。 仍以圖1為例，F2單元格內輸入的公式為=COUNTIF（$E$2:$E$4， &E2）+1。計算各車間產值排名

27、的方法同上，結果也完全相同，2、 1和3。 此公式的計算程序是這樣的:首先根據E2單元格內的數值，在連接符&的作用下產生一個邏輯陳述式，即176.7、167.3等。 COUNTIF函數計算出引用區域內符合條件的單元格數量，該結果加一即可得到該數值的名次。很顯然，利用上述方法得到的是降序排列的名次，對重複資料計算得到的結果與RANK函數相同。 3.IF函數 Excel自身帶有排序功能，可使資料以降序或升序方式重新排列。如果將它與IF函數結合，可以計算出沒有空缺的排名。以圖1中E2、E3、 E4單元格的產值排序為例，具體做法是:選E2單元格，根據排序需要，按下Excel工作列中的降序排序或升序排序

28、按鈕，即可使工作表中的所有資料按要求重新排列。 假如資料是按產值由大到小（降序）排列的，而您又想賦予每個車間從1到n（n為自然數）的排名。可以在G2單元格中輸入1，然後在G3單元格中輸入公式=IF（E3=E2， G3,G3+1），只要將公式複製到G4等單元格，就可以計算出其他車間的產值排名。 二、文本排序 選舉等場合需要按姓氏筆劃為文本排序， Excel提供了比較好的解決辦法。如果您要將圖1資料表按車間名稱的筆劃排序，可以使用以下方法: 選排序關鍵字所在列（或行）的首個單元格（如圖1中的A1），按下Excel資料表單下的排序指令，再按下其中的選項按鈕。選排序選項對話視窗方法下的筆畫排序，再根據

29、資料排列方向選項按行排序或按列排序，確定後回到排序對話視窗（圖2）。如果您的資料帶有標題行（如圖1中的服務機構之類），則應選有標題行（反之不選），然後開啟主要關鍵字下拉列表，選項其中的服務機構，選排序方式（升序或降序）後確定，表中的所有資料就會據此重新排列。 此法稍加變通即可用於第一名、第二名等文本排序，請讀者自行摸索。 三、自訂排序 如果您要求Excel按照金工車間、鑄造車間和維修車間的特定順序重排工作表資料，前面介紹的幾種方法就無能為力了。這類問題可以用定義排序規則的方法解決:首先按下Excel工具表單下的選項指令，開啟選項對話視窗中的自訂序列選擇項（圖3）。選左邊自訂序列下的新序列，游標

30、就會在右邊的輸入序列框內閃動，您就可以輸入金工車間、 鑄造車間等自訂序列了，輸入的每個序列之 間要用英文逗號分隔，或者每輸入一個序列就敲Enter鍵。如果序列已經存在於工作表中，可以選序列所在的單元格區域按下匯入，這些序列就會被自動加入輸入序列框。無論採用以上哪種方法，按下增加按鈕即可將序列放入自訂序列中備用（圖3）。 使用排序規則排序的具體方法與筆劃排序很相似，只是您要開啟排序選項對話視窗中的自訂排序次序下拉列表，選前面定義的排序規則，其他選項保持不動。回到排序對話視窗後根據需要選項升序或降序，確定後即可完成資料的自訂排序。 需要說明的是: 顯示在自訂序列選擇項中的序列（如一、二、三等），均可按以上方法參與排序，請讀者注意Excel提供的自訂序列檔案類型。談談Excel輸入的技巧