教學 - 常用EXCEL技巧匯總

[psac]

常用EXCEL技巧匯總

利用"條件求和嚮導"對資料按條件進行匯總

在一個表格中經常需要將表中的資料按一定的條件進行求和計算。如一個人事職稱檔案表，需要統計"助理工程師"、"工程師"、"會計師"等職稱的人數各為多少。對類似問題，利用"條件求和嚮導"便可很好地解決。具體方法是選項"工具"*"增益集"，在開啟的視窗中選"條件求和嚮導"，然後按"確定"按鈕，使用增益集; 選項"工具"*"嚮導"*"條件求和"，進入求和嚮導; 在求和嚮導中首先按下"輸入"框右邊的小按鈕，然後在表格中直接選定包含資料的區域，再按下"下一步"，在"求和列"中選定求和資料所在列，在"條件"項中增加求和的條件，根據嚮導的步驟指定存放匯總結果的單元格，即將游標置於輸入項中，直接在表格中選相應單元格即可，最後按下"完成"按鈕。在匯總結果單元格中將出現預期的匯總結果，且一旦設定，匯總結果單元格會隨著資料變化自動匯總，一勞永逸。要取消匯總結果只需點擊匯總結果單元格，將公式去掉即可。當然，熟悉函數公式的用戶，可直接定義單元格的公式，效果是一樣的。

把Excel表格轉換為圖片

許多報紙和雜誌都介紹過在Excel中，同時按住Shift鍵點擊「文件」表單，原來的「關閉」表單項就會變成「全部關閉」。如果我們在按下Shift鍵的同時點擊「編輯」表單，原來的複製和貼上就會變成「複製圖片」和「貼上圖片」。利用這一功能，我們可以將一個資料表以圖片的形式進行複製，從而將其轉換為圖片。方法如下：

首先選需要複製成圖片的單元格區域，然後按住Shift鍵依次選項「編輯→複製圖片」指令，接著彈出「複製圖片」視窗，選項「圖片」單選項後點擊「確定」按鈕，這時就將選定的表格區域複製成圖片了。最後複製到目標只需直接選項「貼上」指令即可（或者按Shift鍵再選項「編輯→貼上圖片」指令）。我們還可以將其在Word中進行貼上。

另外，在複製圖片時如果選項了「如列印效果」單選項，在貼上的時候如果表格沒有邊框，複製後的圖片也不會出現邊框。


將Excel的文本數位轉換成數位



在Excel中，系統將前面帶有半形單引號的數位視為文本對待，而且為了某些需要，用戶可以通過「格式」表單中的「單元格」指令，將數位設定為文本格式。然而，現在我們卻需要把工作表中文本格式的數位轉換成數位，那麼以下方法可以一試。

1．一次轉換一個單元格

在「工具」表單上，按下「選項」，再按下「錯誤檢查」選擇項（一定要確保選了「允許後台錯誤檢查」和「數位以文本形式儲存於」複選框）。選任何在左上角有綠色錯誤指示符的單元格。在單元格旁邊，按下出現的按鈕，再按下「轉換為數位」。

2．一次轉換整個區域

在某空白單元格中，輸入數位「1」。選該單元格，並在「編輯」表單上按下「複製」指令。選取需轉換的儲存於為文本數位的單元格區域。在「編輯」表單上，按下「選項性貼上」。在「運算」下，按下「乘」。按下「確定」按鈕。

3．處理特殊財務數位

一些財務程序顯示負值時在該值右邊帶有負號「-」。要將此文本字串串轉換為數值，必須返回除最右邊字串（即負號）以外的所有文本字串串字串，然後乘以「-1」。例如，如果單元格A2中的值為「156-」，那麼公式「=LEFT(A2,LEN(A2)-1)*-1」將文本轉換為數值「-156」，然後用上面的方法操作。

快速隱藏單元格所在位置的行與列

1. 按ctrl+9 可隱藏選單元格或區域所在的行
2. 按ctrl+0 可隱藏選單元格或區域所在的列


恢復隱藏的行和列的快捷鍵是：
ctrl+shift+0
ctrl+shift+9

[psac]

用Excel 2003輕鬆套打報表

--------------------------------------------------------------------------------

想要找一個套打表格的軟體，先在搜尋一下，大多是用於財務的。
但是也有推薦像Form Pilot這樣的軟體，可惜是英文的。
繼續搜尋發現竟然可以用Excel2003來列印表格，想想可能會有和我一樣不知道的人。
故推薦給大家。

通常套打表格都用Word、WPS來實現，但由於軟體的計算功能有限，有時很難滿足資料極多、計算量也較大的財會、稅務和統計等行業報表的需求。對於此類報表，筆者平常都採用office/9.shtml' target='_blank' class='article'>Excel來套打，Excel強大的表格計算功能和自動填充功能可大大提高工作效率。在此介紹一下筆者用 Excel 2003套打表格的心得。

　　1.掃瞄表格
　　把原表格放入掃瞄儀，掃瞄解析度設定為100dpi、縮放比設為100、模式為黑白文本。在預覽圖形上調整掃瞄區域框使它盡量靠近表格邊框，你也可以在掃瞄後再用圖形處理軟體進行修剪，去除表格週圍的空白，再將掃瞄的圖像處理後儲存好。

　　如果在表格外也要填寫的話，那麼在掃瞄前最好先用鉛筆在原表格上畫一個邊框，作為掃瞄後修剪和量取頁邊界的依據。

　　2.設定背景
　　啟動Excel 2003，按下表單欄的「格式/工作表/背景…」，開啟工作表背景對話視窗，找到剛才儲存的表格圖像，設定為背景。按下工作列上顯示比例的下拉列表框，在項中直接輸入105後Enter鍵，把顯示比例設定為105%。

　　注意：這裡105%的比例是這樣得來的。在紙上畫一個邊長10厘米的正方形，掃瞄後設為電子錶格背景，此時比例為100%，用繪圖工作列中的 「矩形」工具畫一個與背景正方形完全重合的矩形，列印出來。量得列印出正方形邊長為10.5厘米，除以原正方形的邊長10厘米，可得比例為105%。另外，你也可以在Excel中畫一個邊長為10厘米的正方形，再試著慢慢調整比例，使它與背景正方形重合， 亦不難得到相應的比例。

3.定位填寫
　　通過移到行、列標題的邊界調整行高或列寬，再配合使用格式工作列中的「合併及居中」按鈕，調整表格線使它與背景表格的表格線重合(也不必很準確，只要能讓文字填寫在所需位置就可以)。對於大量等高(寬)的行(列)，全部選並從表單樣中的「格式/行/行高(列/列寬)」中直接輸入數值會更快些。然後在相應單元格裡輸入資料、文字或公式引用(如圖)，記住千萬不要設定單元格邊框，這樣一張表格就做好了。



4.頁面設定
　　按下表單欄中的「文件/頁面設定…/頁邊界」，用尺子量取原表格紙中表格邊框到紙張左邊和上邊的距離，把量取的尺寸直接輸入頁面設定對話視窗中相應的左、上頁邊界中，右、下邊距則要設小一點，讓整張表格都可以列印出來就行了。按下「選項」按鈕，進入列印機設定介面自訂紙張尺寸，直接輸入紙張 的尺寸，這當然也要直接用尺子測量。

　　部分列印機設定中可能沒有自訂紙張尺寸的功能，此時我們只要從「文件/頁面設定/頁面/紙張大小」中選一個比實際紙張大點的紙張尺寸，一般也可以正常列印。

　　5.列印輸出
　　現在可以把原表格紙放入列印機列印了，如果以上操作均無誤的話，一般無須調整即可一次成功。即使出現水準或垂直移位，也只要直接量取偏移值，然後增減到相應的上、左頁邊界中即可。在此我們設定為背景的表格雖然在螢幕上可以看到，但是並不會被列印出來， 所以無須考慮。

　　這種套打表格的方法特別適用於列印大量的相關表格。像財務、稅務和統計這三種報表中的資料都是密切相關的。如果把這些相關的報表都存放在同一活頁簿內，設定好不同工作表間的相互引用和計算公式。以後我們只要輸入幾個基本資料就可以自動計算套打出一套完 整的報表。
不完全,office有個毛病就是英文和中文的字體不一致,如果原來的地方填的是英文或數位或空的,現在改成中文那就會錯位

[psac]

妙用Microsoft Excel的VBA批量列印工作表

Microsoft Excel是很多辦公一族的常用工具，其強大的功能幾乎能滿足所有有資料處理方面的要求。而其原有的的巨集指令更是能簡化一些資料處理中重複性的工作。
一次筆者要把一批資料的紙張大小由B4紙調整為A4紙，並在列印時縮放成一頁（即一頁寬，一頁高）。由於資料都是由單獨工作簙構成的，對於每一個明細資料，都需要逐個開啟工作簙，設定每一張工作表的頁面設定，然後再列印，工作量很大。然而筆者利用巨集很快便完成了所有的工作。方法如下：
啟動Microsoft Excel，按快捷鍵「Alt+F11」進入Microsoft Visual Basic編輯器。在編輯器中按下表單「插入→模組」，在新增的模組中輸入以下程式碼：
Sub printer()
With Application.FileSearch
.LookIn = "E:\財務決算\變更報表\" '設定文件的搜尋路徑
.FileType = msoFileTypeExcelWorkbooks '設定要搜尋的檔案類型為活頁簿
If .Execute > 0 Then '如果找到一個或多個文件
For i = 1 To .FoundFiles.Count '設定開啟工作簙的循環
Workbooks.Open Filename:=.FoundFiles(i) '開啟找到的每一個工作簙
For j = 1 To Worksheets.Count '設定逐項選項工作表的循環
Worksheets(j).Select '選項工作表
With ActiveSheet.PageSetup '設定當前工作表的頁面設定
.PaperSize = xlPaperA4 '設定紙張大小為A4紙
.Zoom = False
.FitToPagesWide = 1 '設定為1頁寬
.FitToPagesTall = 1 '設定為1頁高
End With
Next j '選項下一張工作表
ActiveWorkbook.PrintOut '列印整個工作簙
ActiveWorkbook.Save '儲存當前工作簙
ActiveWorkbook.Close '關閉當前工作簙
Next i '開啟下一個工作簙
Else
MsgBox "沒有找到任何活頁簿文件" '提示沒有找到任何活頁簿文件
End If
End With
End Sub
輸入程式碼後，按快捷鍵「F5」執行巨集，這時系統就會開啟找到的第一個活頁簿，並依次設定每一張工作表的紙張大小為A4紙張，縮印成一頁寬和一頁高，然後再列印整個工作簙，儲存並結束，接著依次開啟當前目錄下的所有活頁簿，執行相同的工作。
注意，程序所搜尋到的文件僅是當前目錄下的活頁簿，不包括子目錄下的文件。

[psac]

隱藏指定Excel行 不讓它們被列印出來

在有些情況下，我們並不需要列印Excel工作表的某些行。例如，包含機密訊息的行，或者包含中間計算結果的行。

　　這時，我們可以將這樣的行隱藏起來，在列印完工作表之後再恢復其顯示。如果工作表中包含許多不需要列印的行，隱藏與恢復顯示的工作就會比較費時費力了。

　　下面將向大家介紹一種快速切換行的隱藏與顯示狀態的技巧，使用這一技巧，會大大提高隱藏與顯示非列印行的工作效率。該技巧主要用到Excel的「組與分級顯示」功能。

　　如圖1所顯示，我們將以該工作表為例來進行練習，其中第5、10、15行是不希望列印的行。點擊此處下載例子工作表。



圖1 工作表

　　具體操作步驟如下。

　　1.選第5行，選項表單指令「資料|組和分級顯示|組合」(或者按快捷鍵Alt+Shift+向右方向鍵)，如圖2所顯示。



圖2 表單指令

　　這時，在第5行左側出現如圖3所顯示的標記，並且在工作表的左上方出現1和2，這是分級顯示符號。按下某層次的顯示符號，可以隱藏或顯示下一級的明細資料。我們要利用的正是分級顯示符號的這一功能。



圖3 標記

　　2.選項第10行，按F4鍵。F4鍵的作用是重複最近的一次指令。這裡也可以按Alt+Shift+向右方向鍵，只不過不如F4來得快捷。

　　3.選項第15行，按F4鍵。現在的工作表如圖4所顯示。



圖4 工作表

　　4.按下左上角的分級顯示符號1，將第5、10、15行隱藏，現在的工作表應如圖5所顯示。



圖5 工作表

　　經過以上操作，我們可以快速地將不需要列印的行隱藏起來，等列印完了可以再按下分級顯示符號2將其恢復顯示。

　　小提示：Alt+Shift+向右方向鍵實際上是將某行降了一級，如果想讓某行升一個等級，可以按快捷鍵Alt+Shift+向左方向鍵，或者選項表單指令「資料|組和分級顯示|取消組合」。

[psac]

警惕Excel四捨五入 造成的資料誤差

很多朋友都在使用Excel編輯、處理各種資料報表，在使用程序中往往會發現Excel自動計算的結果與我們自己手動計算的結果會出現一個誤差。例如Excel工作表中有B2=16.18、C2=12.69，將B2與C2之和乘以0.11，將結果「四捨五入」，保留兩位小數，再將結果乘以3.12，再「四捨五入」保留兩位小數，Excel的計算結果是「9.91」，而我們手動式計算的結果是「9.92」。是什麼原因造成Excel計算錯誤呢？難道Excel也會犯迷糊？非也！Excel並不會犯迷糊，造成誤差的根本原因是「四捨五入」。
　　我們設定表格的時候，在「單元格格式」視窗中設定的「小數位數」只能將單元格中的數值「顯示內容」四捨五入，並不能對所存放的「數值」四捨五入。換句話說，顯示內容和實際存放內容（即參與運算的內容）並非完全一致。因此，造成Excel計算結果與實際需求出現誤差的元兇正，正是單元格資料的顯示內容與參與計算內容的不一致性。那麼我們該如何避免這種誤差呢？
　　方法很簡單，其一是利用Round函數對小數進行精確的四捨五入，其格式為：round（number，num_digits），其中「number」 為需要四捨五入的數位或運算公式，num_digits指定四捨五入的位數。針對本文所述問題，我們只需在D2單元格中輸入「=Round((B2+C2)*0.11,2)」，在「E2」單元格中輸入「=Round(D2*3.12,2)」即可。另外，我們還可以通過Excel進行一下簡單的設定來達到精確計算的目的，點擊Excel表單欄的「工具/選項」，在彈出的「選項」視窗中切換到「重新計算」選擇項，在「活頁簿選項」欄中將「以顯示值為準」複選框打上鉤，點「確定」按鈕即可（如圖）。

[psac]

讓Excel表頭自動填寫當前月份和日期

假設我們要做一個標題為「某公司6月份電費統計表」的Excel表格，其中「6」為進行電費統計的當月月份，第二行有製表日期。

　　電費每月公佈一次，那麼每月開啟此表修改編輯時，都要對標題中的月份和製表日期進行修改，萬一只改動了表內資料，而忘記以上兩個時間的改動，會出現表頭時間與表內資料的矛盾。

　　我們採取一個一勞永逸的措施來解決這個問題，任何時候開啟此工作表，都讓表頭自動填寫當前月份和日期。這要借助Excel三個函數來實現，在標題單元格輸入「="實驗中學"&& MONTH(TODAY())&&"月份電費統計表"」，函數「MONTH(TODAY())」表示當前月份，在第二行製表時間單元格輸入「=TODAY()」。函數「TODAY()」表示當前日期，就出現圖中結果。

　　完成上述設定，任何時候開啟該表，我們大可放心地去做表內資料的處理工作了，不必擔心表頭時間會出現什麼差錯，無需對表頭做任何改動。

[psac]

EXCEL大量資料快速錄入技巧

高考及其他許多類似工作有大量的資料需要錄入，為了便於匯總，各校必須統一格式，所以一般由上級主管部門設計好一個資料庫，再輔之以一個錄入的界面。這樣雖然操作起來非常直觀，但絲毫不能減輕資料錄入的工作量（除了自動編號之外）。仔細研究一下他們的資料庫和資料構成，我們就可以放棄那些漂亮的錄入界面，在EXCEL2000中輕鬆完成。
　　一、表頭的設計處理
　　首先要自己設計一張工作表。表頭上的表項設置，必須建立在對上級部門資料庫的認真剖析的基礎之上。如果是傳統的DBF資料庫，當然可以直接在EXCEL中進行相關操作；如果是ACCESS資料庫，經過適當處理，我們也可以在EXCEL2000中對它進行操作。實在不行，那就逐項記錄下所要錄入的資料項，自己設計一個表頭也是可以的。表頭最好用漢字註明，這樣可以避免錄入時出現張冠李戴的情形，需要時再適當調整一下就行了。
　　二、「查找、替換」和「自動更正」功能的巧用
　　再來分析一下這些資料：在「畢業學校」一欄，有好多個學生畢業於同一所學校，重複輸入效率肯定不高。對，有了，先自己定義幾個字元來臨時替代一下各個學校的名稱（如用「4Z」代表「棗陽市第四中學」，等全部完成後再按「Ctrl+H」把所有的「4Z」全部替換為「棗陽市第四中學」不就行了？
　　[提示：盡量用一些簡單好記、易錄入、不會與其他內容發生混淆的字元來執行替換，不過不要光顧了簡單好記，否則，你用「4」代替「棗陽市第四中學」試試，做了「全部替換」後可別罵人喲！]
　　如果你連Ctrl+H這一步也想省去，你可以試試EXCEL的「自動更正」功能。使用自動更正功能，可以簡化用戶對大量重複資料的輸入操作，例如可以將上面的「4z」定義更正為「棗陽市第四中學」，定義的方法是：
　　1.從工具表菜單中選擇「自動更正」，打開自動更正對話框。
　　　　2.在「替換」框中輸入資料， 如：「4z」，在「替換為」框中輸入資料，如：「棗陽市第四中學」，單擊「新增」，再單擊 「確定」。
　　　　之後，只需在單元格中輸入「4z」兩字，即會自動更正為「棗陽市第四中學」。
　　三、輸入法的自動切換
　　在輸入資料時，一張工作表中同時包含漢字、英文字母和數位，那麼對於不同的單元格，輸入時不斷地切換輸入方式也是人為增加的錄入工作量。只要作一下預處理，便可以使Excel對不同類型的單元格實現輸入法的自動切換。
　　啟動Excel 2000，新增一工作簿，取名為「2002.xls」，右鍵單擊工作表標籤「Sheet1」，選擇「重命名」，改名為「學籍登記表」。先將小張經常使用的「智慧式陳橋輸入法」設為預定漢字輸入法：單擊右下角的「En」圖示，選擇「內容」，在出現的對話框中選擇語言標籤下的「智慧式陳橋輸入平台」，單擊[設為預定值]並確認。按上述設計輸入表頭內容，然後選中「姓名」、「性別」等需要輸入漢字的那些列，在表菜單中依次選擇「資料→有效性→輸入法模式」選擇項，在「模式」下拉列表框中選擇「打開」，單擊[確定]按鈕（如圖1）。再選擇其他各列，同上操作步驟，調出「輸入法模式」選擇項，在「模式」下拉列表框中選擇「關閉（英文模式）」，單擊[確定]按鈕即可。
　　

　　經過這樣簡單的處理之後，在錄入過程中，當插入點處於不同的單元格時，Excel 2000能夠根據我們進行的設置自動在中英文輸入法間進行切換，省去了來回進行中英文切換的麻煩。
四、不定長資料的預處理
　　對於學生家庭住址這一欄，列寬該如何設置呢？太寬了會造成表格過寬左右滾動不方便，太窄又怕萬一哪個學生的地址比較長，豈不被它右邊的列給擋住了？不要緊，有兩招都是專門對付這種情況的：
　　第一招、在「家庭住址」列上方單擊，選中整列，依次選擇表菜單「格式→單元格→對齊」，在「文本控制」下選中「縮小字體填充」復選框（如圖2），單擊[確定]按鈕，調整該列到合適的列寬。這樣如果某個同學的地址超過了單元格的寬度，Excel能夠自動縮小字元的大小把資料調整到與所設列寬一致，以使資料全部顯示在單元格中。即使以後對這些單元格的列寬進行了更改，其中的字元也可乖乖地自動增大或縮小字號，以適應新的單元格列寬。
　　

　　第二招、選中「家庭住址」列後，勾選圖2中的「自動換行」復選框後，Excel能根據列的寬度和文本內容的長度自動換行，這樣就不必瞇起眼睛去忍受那些小字了！
　　五、讓EXCEL也「自動編號」
　　我們都知道，EXCEL的自動填充是它的一大特色功能。利用它來進行序號的「自動編製」，簡直就像是量體裁衣一樣方便。一般自動填充的方法都是用滑鼠左鍵指向填充柄，按住滑鼠向下拖動完成的（填充柄是位於選定區域角上的小黑塊，將滑鼠指向填充柄時，滑鼠的形狀變為黑十字。拖動填充柄可以將內容複製到相鄰單元格中，或填充日期系列）。如果表格有太多的行，這種方法不便掌握拖動的距離。這裡介紹一種本人摸索的更簡單的操作方法：用滑鼠左鍵雙擊填充柄。一切OK！注意：這樣做有時候不能達到預期的填充效果。還必須滿足下列條件：
　　1、只能縱向填充不能橫向填充。
　　2、欲填充的列的左(右)相鄰列非空，且中間無間斷，否則以上填充將在間斷處停止。
　　3、該方法對文本形式的序列也起作用。　　
六、自動定位小數位
　　因為有一大批諸如123.5之類的學產生績需要錄入，如果錄入前先進行下面的設置，將會使你的輸入速度成倍提高（可以不需要輸入小數點）。
　　單擊「工具」→「選項」→「編輯」選擇項，選中「自動設置小數點」復選框，在「位數」微調編輯框中鍵入需要顯示在小數點右面的位數。在此，我們鍵入「1」（如圖3）。單擊「確定」按鈕。
　　現在，在工作表的某單元格中鍵入「1235」，則在你按了Enter鍵鍵之後，該單元格的數位自動變為「123.5」。方便多了吧！此時如果你在單元格中鍵入的是「89」，則在你結束輸入之後，該單元格中的數位自動變為「89.0」。
　　

　　另外，如果你在「位數」框中輸入一個負數，比如「-1」，則Excel將在輸入的數位後自動添上兩個「0」。如果要暫時取消在「自動設置小數點」功能中設置的選項，可以在輸入的資料中自帶小數點。利用Excel的「自動設置小數點」的功能，對於大量帶有固定小數位的數位，或帶有固定位數的以「0」字元串結尾的數位的輸入，將大大提高編輯速度。
　　七、讓表格個性化
　　為了提高工作效率，越干越起勁，同時好為了避免錄入時間過長後出現差錯。選中整個工作表，單擊格式表菜單下的「自動套用格式……」，出現「自動套用格式」對話框，選擇一種比較你所喜歡的方案，單擊[選項]按鈕，展開對話框下部的「應用格式種類」選項框，為了保持前面的諸多設置，必須去掉「數位」、「對齊」、「邊框」等復選項前的選擇，只復選「字體」和「圖案」兩項，[確定]後完成設置。
　　[提示：在表格中，給單元格加上不同的顏色和底紋可以增強資料輸入時的直觀感覺，但在黑白印表時如果連顏色和底紋都打出來，表格的可視性就大打折扣了，因此用戶希望在資料處理時色彩繽紛，印表時仍舊是普通的白紙黑字。可以在「文件」表菜單中選擇「網頁面設置→工作表→印表→單元格單色印表」，這樣印表的表格就是普通的黑白表格了。]
　　一切準備就緒，下面就可以開始錄入了。

[psac]

如何用Excel服務器實現費用分攤和匯總

1.定義新模板
點擊「模板」-「新增模板」，








啟用該模板。
畫出該模板：




2.定義單一資料項
選中藍色單元格，滑鼠右鍵點擊藍色單元格，選擇「定義單一資料項」：


點擊「下一步」：

點擊「下一步」：



點擊「完成」。
3.定義「重複資料項」
選中藍色區域，滑鼠右鍵點擊，選擇「定義重複資料項」：





點擊「下一步」：

點擊「下一步」：

點擊「完成」。
在模板中定義：金額=明細*單價的公式。
4.填報
關閉模板，打開「我的工作台」：




儲存。
四、建立「各部門交費通知」模板錄像
1.定義模板
點擊「模板」，選擇「新增模板」：






啟用該版本。
畫出模板：

2.定義單一資料項
選中藍色單元格，滑鼠右鍵點擊，選擇「定義單一資料項」：


點擊「下一步」：






點擊「下一步」：



點擊「完成」。
3.定義「重複資料項」
選中藍色區域，滑鼠右鍵點擊，選擇「定義重複資料項」：





點擊「下一步」：

點擊「下一步」：

點擊「完成」。
4.定義「表間公式」
滑鼠右鍵點擊，選擇「定義表間公式」：






點擊「新增」：


點擊「來源資料」：






回到表間公式主界面，點擊「篩選條件」：

點擊「確定」，回到主界面，點擊「充填方式」：

表間公式「提取電費」：
提取中滿足下列條件的資料: 按以下方式填入: '電費' --->(按行(列)匹配) 收費專案
合計(電費分攤明細_明細.明細) --->(填入值) 明細內容
合計(電費分攤明細_明細.金額) --->(填入值) 金額
表間公式「提取水費」：
提取中滿足下列條件的資料: 按以下方式填入: '水費' --->(按行(列)匹配) 收費專案
合計(水費分攤明細_明細.明細) --->(填入值) 明細內容
合計(水費分攤明細_明細.金額) --->(填入值) 金額
5.填報
關閉模板，打開「我的工作台」：





點擊「應用」：

水費和電費就合計到一張單子上了。
選擇不同的「繳費單位」，就可以印表出各自的交費通知了。

[psac]

Q:


請教Excel高手：如何讓輸入的數值自動轉換成中文金額大寫！

如何讓輸入的數值自動轉換成中文金額大寫！
如：輸入123.45，轉換後：壹佰貳拾三元肆角伍分



A:

關於EXCEL中的中文大寫數位

描述：
譬如123.85
如何讓EXCEL將其自動在其他單元格裡顯示出 壹佰貳拾三圓捌角伍分

解決辦法：
如果只是轉成中文數位，從「分類」列表框中選擇「特殊」選項，指定「中文大寫數位」即可。

若是要轉換為中文大寫金額，則需使用自定義函數，方法如下：

1、執行「工具→巨集→Visual Basic編輯器」命令，進入「Visual Basic編輯」視窗。執行「插入→模塊」命令，插入一個模塊（如「模塊1」），雙擊「模塊1」在視窗右邊展開「模塊1（代碼）」編輯視窗，輸入如下代碼。

Function dxje(q)
　　　ybb = Round(q * 100) '將輸入的數值擴大100倍，進行四捨五入
　　　y = Int(ybb / 100) '截取出整數部分
　　　j = Int(ybb / 10) - y * 10 '截取出十分位
　　　f = ybb - y * 100 - j * 10 '截取出百分位
　　　zy = Application.WorksheetFunction.Text(y, "[dbnum2]") '將整數部分轉為中文大寫
　　　zj = Application.WorksheetFunction.Text(j, "[dbnum2]") '將十分位轉為中文大寫
　　　zf = Application.WorksheetFunction.Text(f, "[dbnum2]") '將百分位轉為中文大寫
　　　dxje = zy & "元" & "整"
　　　d1 = zy & "元"
　　　If f <> 0 And j <> 0 Then
　　　　dxje = d1 & zj & "角" & zf & "分"
　　　　If y = 0 Then
　　　　　dxje = zj & "角" & zf & "分"
　　　　End If
　　　End If
　　　If f = 0 And j <> 0 Then
　　　　dxje = d1 & zj & "角" & "整"
　　　　If y = 0 Then
　　　　　dxje = zj & "角" & "整"
　　　　End If
　　　End If
　　　If f <> 0 And j = 0 Then
　　　　dxje = d1 & zj & zf & "分"
　　　　If y = 0 Then
　　　　　dxje = zf & "分"
　　　　End If
　　　End If
　　　If q = "" Then
　　　　dxje = 0 '如沒有輸入任何數值為0
　　　End If
End Function

2、輸入完成後，關閉VBA編輯視窗返回工作表狀態。

3、如果要轉換B2單元格的數值，並將結果儲存的C2單元格中，只要在c2單元格中輸入公式：=dxje(B2)，確認即可。

通常情況下，自定義的函數只適應於定制的工作簿中，如果要在其它工作簿中使用，請將其製作為載入巨集，然後載入一下即可。
或許你說..
方法複雜了一點，另外可以用極點五筆或者智慧式陳橋五筆，兩種輸入法都相容拼音。其中都有輸入中文金額大寫的功能。

極點：
按；(分號)後輸入數位再按；極點會輸出大寫金額，如：輸入「；12,345.67；」（僅輸入引號內的內容）極點會輸出：壹萬貳仟三佰肆拾伍圓陸角柒分 。

智慧式陳橋：
分號鍵＋引號鍵＋數位鍵（＋小數點＋數位鍵）。
　　當你要輸入"壹萬貳仟三佰肆拾伍圓陸角柒分"時，可進行如下操作：
　　按一下分號鍵，再按一下引號鍵（分號鍵旁邊的鍵），再鍵入數位鍵12345.67，在智慧式五筆的提示行就會有大寫數位提示，按空格鍵即可

[psac]

Exce的工具軟體..:

Excel報表匯總助手 2.0 記憶體註冊機

軟件名稱: Excel報表匯總助手
軟件版本: 2.0
適用平台: Win2000, WinXP
軟件作者: TomorrowStudio
聯繫郵箱: TomorrowStudio@163.com
作者主頁: http://www38.websamba.com/tomorrowstudio
商品簡介
《Excel匯總助手》能快速匯總多個相同格式的excel文件，幫助您輕鬆匯總日報、月報、季報、年報或者下屬單位上報的報表，大大提高您的工作效率。它使用方便，5分鐘即可上手。歡迎使用!

Excel報表匯總助手 2.0 100.00 元/套 2006-03-22 註冊>>>

下載地址：http://www38.websamba.com/tomorrowst...lAssistant.rar



勤哲Excel服務器2006企業版

勤哲Excel服務器2006完整企業版 6.3
軟件語言： 簡體中文
界面預覽：
軟件類型： 大陸國產軟件 / 服務器類
執行環境： Win2003, WinXP, Win2000, WinME, Win9X
授權方式： 共享軟件

軟件簡介：1. 表間公式使得您能夠任意引用權限範圍內的企（事）業單位內的業務資料。一個Excel模版可以定義多個表間公式。表間公式可以大大提高您的工作效率，減少人為錯誤，降低您的勞動強度。
　　　　2. 工作流可以使Excel文件按照管理流程和權限角色進行流動，特別是，在Excel文件傳遞過程中可以隨時使用表間公式，獲得所需要的資料，因此，工作流充分和資料處理相結合是Excel服務器2005的一大特點。
　　　　3. ESWeb系統可以使用戶通過互連網進行填報 、修改、應用表間公式、圖表、工作流、交叉表、查詢資料和引用外部資料源。通過設計Excel服務器模版，就可自動實現Web化的管理訊息系統。
　　　　4. Excel服務器2006（企業版）中，公用資料和訊息（例如，組織機構、人員、角色、系統日誌、備份與恢復等） 統一管理; 資料類型、資料規範、用戶詞典、自動編號規則可以根據權限進行分散管理，業務表單、台帳和報表由用戶根據權限設計、管理。大大增強了系統的適應性和可用性。
　　　　5.外部資料源可以使用戶非常方便地將已有的ERP/SCM/CRM/OA等管理訊息系統的資料整合進入Excel服務器，實現資料整合。
　　　　6.回寫公式可以使系統按條件自動改變已有單據上的訊息，完全實現「前單驅動後單，後單核銷前單」的功能。
　　　　7. Excel服務器2006嵌入在MS Excel中，會用Excel,就很容易掌握Excel服務器，培訓成本和維護成本大大降低。
　　　　8.採用MS SQL Server2000作為系統資料庫，企業的Excel文件和業務資料具有安全保障。
　　　　9.適用於局域網和廣域網。
　　　 10. 支持群集系統。
　　　 11.支持圖片管理。
　　　 12.支持附件。
Excel服務器2006（企業版）分為服務端和客戶端。
Http://www.qinzhe.com
有錄像教程和應用實例。
客戶端需要與同版本的服務端共同使用。


下載地址
http://www.qinzhe.com/chinese/download.htm

試用期：累計30天，5個並發用戶。

[psac]

Excel實用操作技巧九則

本人使用Excel已經有一段時間，使用時學到過一些技巧，現介紹一部分如下：

　　1、快速輸入大寫中文數位

　　將游標移至需要輸入大寫數位的單元格中。利用數位小鍵盤在單元格中輸入相應的小寫數位(如12345)。右擊該單元格，點擊「設置單元格格式」，從彈出的「單元格格式」交談視窗中選擇「數位」選項；然後從「類型」列表框中選擇「中文大寫數位」選項。最後單擊「確定」按鈕，這時輸入的12345就自動變成「壹萬貳仟三佰肆拾伍」。

　　2、多個工作表的頁眉和頁腳同時設置

　　我們有時要把一個Excel文件中的多個工作表設置成同樣的頁眉和頁腳，分別對一張張工作表去設置感覺很煩瑣。如果用下面的方法就可以一次將多個工作表中的頁眉和頁腳同時設置好：把滑鼠移到工作表的名稱處(如果沒有給每張表取名的話，Excel自動設置的名稱就是Sheet1、Sheet2、Sheet3等等)，然後點右鍵，在彈出的表菜單中選擇「選擇全部工作表」的表菜單項，這時再進行頁眉和頁腳設置就是針對全部工作表了。

　　3、Excel單元格文字隨時換行

　　在Excel中，我們有時需要在一個單元格中分成幾行顯示文字等內容。那麼實現的方法一般是通過選中格式表菜單中的「單元格」下「對齊」的「自動換行」復選項，單擊「確定」即可，這種方法使用起來不是特別隨心所欲，需要一步步地操作。還有一種方法是：當你需要重起一行輸入內容的時候，只要按住Alt鍵的同時按下Enter鍵鍵就可以了，這種方法又快又方便。

　　4、Excel中插入空白行

　　如果想在某一行上面插入幾行空白行，可以用滑鼠拖動自此行開始選擇相應的行數，然後單擊右鍵，選擇插入。如果在每一行上面均插入一空白行，按住Ctrl鍵，依次單擊要插入新行的行標按鈕，單擊右鍵，選擇插入即可。

　　5、Excel中消除0值

　　有Excel中當單元格計算結果為0時，預定會顯示0，這看起來顯然有點礙眼。如果你想顯示0時，顯示為空白，可以試試下面的方法。打開「工具→選項→視圖」，取消「0值」復選項前的√，確定後，當前工作表中的值為0的單元格將全部顯示成空白。

　　6、批量轉換日期格式

　　以前在Excel中輸入職工出生時間時，為了簡單都輸入成「yymmdd」形式，但上級部門一律要求輸入成「yyyy-mm-dd」格式，那麼一千多名職工出生時間肯定不能每個手工轉化。最快速的方法是：先選定要轉化的區域。點擊「資料→分列」，出現「文本分列嚮導」交談視窗。勾選「固定寬度」，連續兩次點擊「下一步」按鈕，在步驟三交談視窗的「列資料格式」中，選擇「日期」，並選定「YMD」形式，按下「完成」按鈕，以前的文本即轉化成了需要的日期了。

　　7、快速選定「空白」和「資料」單元格

　　在Excel中，經常要選定空白單元格，逐個選定比較麻煩，如果使用下面的方法就方便多了：打開「編輯→定位」，在「定位」視窗中，按下「定位條件」按鈕；選擇「定位條件」中的「空值」，再按「確定」，空白單元格即被全部選定。如果要選定只含資料的單元格，在上面方法的「定位條件」視窗中，選擇「常量」，再點「確定」，含有資料的單元格全部選定。

　　8、防止誤改有新招

　　在包含多個工作表的工作薄中，為了防止誤修改，我們常常採取將行(列)隱藏或者設置編輯區域的方法，但是如果要防止整個工作表的誤修改怎麼辦呢?單擊「格式→工作表→隱藏」，將當前的工作表隱藏，這樣操作者連表格都看不到，誤操作就無從談起了。要重新顯示該表格，只須單擊「格式→工作表→取消隱藏」。要注意的是：如果設置了工作表保護，則不能進行隱藏操作。

　　9、快速隱藏

　　在印表工作表時，我們有時需要把某些行或者列隱藏起來，可是用表菜單命令或調整行號(列標)分界線的方法比較麻煩，這時筆者介紹一個簡單方法：在英文狀態下，按「Ctrl+9」或「Ctrl+0」組合鍵，就可以快速隱藏游標所在的行或列。

[psac]

微軟為球迷提供世界盃的Excel 模板

2006德國世界盃馬上要開幕了, 微軟為球迷提供了一個Excel 的模板文件.

該文件只有75K, EXCEL2000以上的版本可以使用. 不但方便的記錄比賽結果, 並自動計算哪些球隊晉級下一輪比賽以及相應的對陣情況. 目前是英文版本, 不過使用應該不受影響.


http://rapidshare.de/files/22951039/...Excel.rar.html

[psac]

國內玩家用EXCEL畫出《魂斗羅》等經典人物(組圖)

製作者：胡玉美豆瓣醬
　　作者看到老外用
魔方做的馬裡奧
的時候來了興趣,自己用EXCEL畫了個點陣的「馬裡奧」圖,之後應廣大玩家的要求,繼續畫出了「高橋名人」、「松鼠」、「林克」和「
魂斗羅
」。
　　



「魂斗羅」主角比爾和蘭斯

點擊此處檢視全部遊戲圖片

　　



「塞爾達傳說」的林克
　　



馬裡奧和蘑菇


松鼠大戰的小松鼠

[psac]

隱藏指定Excel行 不讓它們被印表出來

在有些情況下，我們並不需要印表Excel工作表的某些行。例如，包含機密訊息的行，或者包含中間計算結果的行。

　　這時，我們可以將這樣的行隱藏起來，在印表完工作表之後再恢復其顯示。如果工作表中包含許多不需要印表的行，隱藏與恢復顯示的工作就會比較費時費力了。

　　下面將向大家介紹一種快速切換行的隱藏與顯示狀態的技巧，使用這一技巧，會大大提高隱藏與顯示非印表行的工作效率。該技巧主要用到Excel的「組與分級顯示」功能。

　　如圖1所示，我們將以該工作表為例來進行練習，其中第5、10、15行是不希望印表的行。點擊此處下載例子工作表。



圖1 工作表

　　具體操作步驟如下。

　　1.選中第5行，選擇表菜單命令「資料|組和分級顯示|組合」(或者按快捷鍵Alt+Shift+向右方向鍵)，如圖2所示。



圖2 表菜單命令

　　這時，在第5行左側出現如圖3所示的標記，並且在工作表的左上方出現1和2，這是分級顯示符號。單擊某級別的顯示符號，可以隱藏或顯示下一級的明細資料。我們要利用的正是分級顯示符號的這一功能。



圖3 標記

　　2.選擇第10行，按F4鍵。F4鍵的作用是重複最近的一次命令。這裡也可以按Alt+Shift+向右方向鍵，只不過不如F4來得快捷。

　　3.選擇第15行，按F4鍵。現在的工作表如圖4所示。



圖4 工作表

　　4.單擊左上角的分級顯示符號1，將第5、10、15行隱藏，現在的工作表應如圖5所示。



圖5 工作表

　　經過以上操作，我們可以快速地將不需要印表的行隱藏起來，等印表完了可以再單擊分級顯示符號2將其恢復顯示。

　　小提示：Alt+Shift+向右方向鍵實際上是將某行降了一級，如果想讓某行升一個級別，可以按快捷鍵Alt+Shift+向左方向鍵，或者選擇表菜單命令「資料|組和分級顯示|取消組合」。

[psac]

海報印表　我用Office Excel就搞定

平常辦公中偶爾會需要印表一些標語、海報等A3、A2甚至更大幅面的文件，這靠我們辦公室常用的A4
印表機
顯然無法直接印表。該怎麼辦呢？對此筆者平常都是採用Excel進行印表的。使用Excel可以將A3、A2等大張的圖形分割成多頁印表，待後再進行拼接貼上去成圖，雖說多了一些接縫不過用來應應急也還可以，下面大家就和筆者一起動作操作吧。
　　首先啟動Excel選中A1單元格，選擇「插入/圖片/來自文件」，插入我們處理好的圖形文件。如果是用Word設計的海報，則應選擇「插入/對像…/從文件創建」（如圖1），點擊「瀏覽」按鈕打開儲存海報的Word文件，確定後將Word文件插入工作表中，右擊插入的Word文件選擇剪下，再點擊表菜單欄的「編輯/選擇性貼上去」，選定一種圖形格式，如：圖片（JPEG），即可把它轉換成JPG格式的圖形。



圖1
　　海報插入Excel文件後，我們可以利用Office提供的圖片編輯工具簡單修飾一下海報。右擊工具欄選中「圖形」和「繪圖」，以顯示這兩個工具欄。從「圖片」工具欄中我們可以選擇進行修剪、設定透明色、調節亮度對比度等（如圖2）。圖形處理好後點擊其中的「設置圖片格式」，在大小選擇項中按所需海報尺寸直接輸入圖形的高度和寬度。然後使用「繪圖」工具欄中的自選圖形、文字框、藝術字等工具對圖形做進一步裝飾。



圖2
　　然後我們該為海報印表做一些設置。選擇「文件/印表預覽」，點擊「頁邊距」按鈕，把
滑鼠
游標指向表示頁邊距位置的虛線，當游標變成雙箭頭時按住左鍵拖動，可直接調整頁邊距（如圖3），這也就是我們以後貼上去的邊距。點擊「設置」按鈕，在「網頁面」中選擇紙張大小為A4。對於一些特大的海報，印表出來的頁數太多，要貼上去時可能不太好找，可在「頁眉/頁腳」中選擇一種帶頁碼的頁腳格式（如圖4），以便印表後區分。　註：頁邊距不要設得太小，要考慮到印表機的實際可印表範圍，如果頁邊距超出印表機的可印表範圍將會使圖形超出部分丟失。同時要注意調整頁腳的位置，避免頁碼與圖形重疊。
一切搞定後，放入紙開始印表，把印表後的紙左側和上側多餘的紙邊裁掉，再用膠水貼上去拼接成一張大圖就行了。



圖3



圖4
　　小提示：如果印表的只是標語一類的特大字那就更簡單了，只要把插入圖片改成插入藝術字再從「設置藝術字格式」設定好所需的尺寸和格式就可以直接印表了。

[psac]

Q:
【求助】Excel如何統計不重複記錄數？

有一表格，共1萬多行，在以A列排序，如何算出A列不重複的記錄數？


A:
事先你排個序，
你再加一列，判斷列，判斷下一行資料和上一行資料，有重複的為true，無重複的為forse，
可以判斷一列，也可以判斷數列，
如果資料不是很嚴密，也可以發給我，我幫你判斷好，

=SUMPRODUCT(1/COUNTIF(A2:A14000,A2:A14000))
可以用公式，先將a列排序，在b列裡輸入「＝if(a1=a2,1,0)「,然後複製b列，貼上去為資料，排序，0的個數即為所求。


office 2007就有這個功能，不妨一試，雖然是英文版的



Q:


求助】如何清除excel文件單元格資料的鏈接？


，我在網上下載了一些資料（資料比較多），但它有些單元格資料帶有鏈接，如何刪除之，謝謝！

因為時間關係，一直未回復，今天回復時論壇提示說，該貼已超過40天了，不能回復！在此感謝lxbliu兄。我用的office2003是也可以。




A:


用「選擇性貼上去」，選中「數值」選項，應該可以去掉那些鏈接的。
你是怎麼複製的？用Ctrl+A全選後再複製試試看？


Q:
對的，我用的是Ctrl+A
A:


如果是在EXCEL 2000，取消超鏈接就比較麻煩，一個一個操作太累，這時候，可以利用複製的方法來達到批量取消超鏈接。

1.複製全部超鏈接，移動游標至其他空位，選擇性貼上去//值和數位格式，得到沒有超鏈接的文本，移回原區域即可。

2.複製全部超鏈接，移動游標至其他空位，選擇性貼上去//數值，得到原格式但沒有超鏈接的文本，移回原區域即可。

3.複製全部超鏈接，移動游標至其他空位，選擇性貼上去//公式，得到沒有超鏈接的文本，移回原區域即可。

4.複製全部超鏈接，移動游標至其他空位，選擇性貼上去//公式和數位格式，得到原格式但沒有超鏈接的文本，移回原區域即可。


Q:
於EXCEL自動計算

我想在A1單元格裡輸入公式56+1，單元格A2自動顯示結果57。

請問高手如何實現

求和公式，朋友自己能試驗一下嗎


A:
理解就是A2自動求各A1中的數值。不太明白這樣做的目的是什麼？詳細說明一下具體情況。



Q:
呵呵,我們搞工程量計算的,工程量計算書中要求這樣的格式
一列是公式內容,一列是計算結果.
用朋友的方法無法計算


A:

利用巨集表函數EVALUATE。
公式，顯示結果


　　假設C列為輸入的沒有等號公式（假設C1為「A1+B1」)，而相鄰的D列是你需要存放公式計算結果的地方（即D1顯示A1和B1單元格相加的結果）。

　　1. 選中D1，然後打開「插入」表菜單選擇「名稱」命令中的「定義」子命令，出現「定義名稱」交談視窗。

　　2. 在「在當前工作表中的名稱」輸入欄中輸入定義的名稱「ResultofFomula」，在下方的「引用位置」編輯欄中輸入「=EVALUATE(Sheet1!C1)」，單擊[確認]按鈕退出。

　　3. 在 D1中輸入「=ResultofFomula」，然後選中按住右下角的填充柄向下拉動填充即可。

　　提示：EVALUATE是Eexcel 4．0版的巨集表函數，Excel 2000和Excel 2002中還支持，但只可用於名稱定義中。

　　4. 填充後要按[F9]進行重算，如果C列的公式有改動，也需要及時按[F9]進行重算。

　　巧施妙計，就能讓公式和結果在Excel中和平共處了，你也試試吧。
單元格 A1中輸入=56+1，然後定義名稱 RESULT =EVALUATE(Sheet1!$A1)，最後你在B1中寫入=RESULT，B1就會顯示57了。