查看單個文章
舊 2006-06-14, 01:40 AM   #35 (permalink)
psac
榮譽會員
 
psac 的頭像
榮譽勳章
UID - 3662
在線等級: 級別:30 | 在線時長:1048小時 | 升級還需:37小時級別:30 | 在線時長:1048小時 | 升級還需:37小時級別:30 | 在線時長:1048小時 | 升級還需:37小時級別:30 | 在線時長:1048小時 | 升級還需:37小時級別:30 | 在線時長:1048小時 | 升級還需:37小時
註冊日期: 2002-12-07
住址: 木柵市立動物園
文章: 17381
現金: 5253 金幣
資產: 33853 金幣
預設

Excel應用技巧

也許你已經在Excel中完成過上百張財務報表,也許你已利用Excel函數實現過上千次的複雜運算,也許你認為Excel也不過如此,甚至了無新意。但我們平日裡無數次重複的得心應手的使用方法只不過是Excel全部技巧的百分之一。本專題從Excel的最高版本2002中的一些鮮為人知的技巧入手,領略一下關於Excel的別樣風情。

一、建立分類下拉列表填充項

  1.在Sheet2中,將企業名稱按類別(如「工業企業」、「商業企業」、「個體企業」等)分別輸入不同列中,建立一個企業名稱資料庫。

  2.選中A列(「工業企業」名稱所在列),在「名稱」欄內,輸入「工業企業」字元後,按「Enter鍵」鍵進行確認。

  仿照上面的操作,將B、C……列分別命名為「商業企業」、「個體企業」……

  3.切換到Sheet1中,選中需要輸入「企業類別」的列(如C列),執行「資料→有效性」命令,打開「資料有效性」交談視窗。在「設置」標籤中,單擊「允許」右側的下拉按鈕,選中「序列」選項,在下面的「來源」方框中,輸入「工業企業」,「商業企業」,「個體企業」……序列(各元素之間用英文逗號隔開),確定退出。

  再選中需要輸入企業名稱的列(如D列),再打開「資料有效性」交談視窗,選中「序列」選項後,在「來源」方框中輸入公式:=INDIRECT(C1),確定退出。

  4.選中C列任意單元格(如C4),單擊右側下拉按鈕,選擇相應的「企業類別」填入單元格中。然後選中該單元格對應的D列單元格(如D4),單擊下拉按鈕,即可從相應類別的企業名稱列表中選擇需要的企業名稱填入該單元格中。

  提示:在以後印表報表時,如果不需要印表「企業類別」列,可以選中該列,右擊滑鼠,選「隱藏」選項,將該列隱藏起來即可



二、建立「常用文檔」新表菜單
 在表菜單欄上新增一個「常用文檔」表菜單,將常用的工作簿文檔新增到其中,方便隨時調用。

  1.在工具欄空白處右擊滑鼠,選「自定義」選項,打開「自定義」交談視窗。在「命令」標籤中,選中「類別」下的「新表菜單」項,再將「命令」下面的「新表菜單」拖到表菜單欄。

  按「更改所選內容」按鈕,在彈出表菜單的「命名」框中輸入一個名稱(如「常用文檔」)。

  2.再在「類別」下面任選一項(如「插入」選項),在右邊「命令」下面任選一項(如「超鏈接」選項),將它拖到新表菜單(常用文檔)中,並仿照上面的操作對它進行命名(如「工資表」等),建立第一個工作簿文檔列表名稱。

  重複上面的操作,多新增幾個文檔列表名稱。

  3.選中「常用文檔」表菜單中某個表菜單項(如「工資表」等),右擊滑鼠,在彈出的快捷表菜單中,選「分配超鏈接→打開」選項,打開「分配超鏈接」交談視窗。通過按「查找範圍」右側的下拉按鈕,定位到相應的工作簿(如「工資.xls」等)資料夾,並選中該工作簿文檔。

  重複上面的操作,將表菜單項和與它對應的工作簿文檔超鏈接起來。

  4.以後需要打開「常用文檔」表菜單中的某個工作簿文檔時,只要展開「常用文檔」表菜單,單擊其中的相應選項即可。

  提示:儘管我們將「超鏈接」選項拖到了「常用文檔」表菜單中,但並不影響「插入」表菜單中「超鏈接」表菜單項和「常用」工具欄上的「插入超鏈接」按鈕的功能。

三、讓不同類型資料用不同顏色顯示
在工資表中,如果想讓大於等於2000元的工資總額以「紅色」顯示,大於等於1500元的工資總額以「藍色」顯示,低於1000元的工資總額以「棕色」顯示,其它以「黑色」顯示,我們可以這樣設置。

  1.打開「工資表」工作簿,選中「工資總額」所在列,執行「格式→條件格式」命令,打開「條件格式」交談視窗。單擊第二個方框右側的下拉按鈕,選中「大於或等於」選項,在後面的方框中輸入數值「2000」。單擊「格式」按鈕,打開「單元格格式」交談視窗,將「字體」的「顏色」設置為「紅色」。

  2.按「新增」按鈕,並仿照上面的操作設置好其它條件(大於等於1500,字體設置為「藍色」;小於1000,字體設置為「棕色」)。

  3.設置完成後,按下「確定」按鈕。

  看看工資表吧,工資總額的資料是不是按你的要求以不同顏色顯示出來了。


四、製作「專業符號」工具欄
在編輯專業表格時,常常需要輸入一些特殊的專業符號,為了方便輸入,我們可以製作一個屬於自己的「專業符號」工具欄。

  1.執行「工具→巨集→錄製新巨集」命令,打開「錄製新巨集」交談視窗,輸入巨集名如「fuhao1」並將巨集儲存在「個人巨集工作簿」中,然後「確定」開始錄製。選中「錄製巨集」工具欄上的「相對引用」按鈕,然後將需要的特殊符號輸入到某個單元格中,再單擊「錄製巨集」工具欄上的「停止」按鈕,完成巨集的錄製。

  仿照上面的操作,一一錄製好其它特殊符號的輸入「巨集」。

  2.打開「自定義」交談視窗,在「工具欄」標籤中,單擊「新增」按鈕,彈出「新增工具欄」交談視窗,輸入名稱——「專業符號」,確定後,即在工作區中出現一個工具條。

  切換到「命令」標籤中,選中「類別」下面的「巨集」,將「命令」下面的「自定義按鈕」項拖到「專業符號」欄上(有多少個特殊符號就拖多少個按鈕)。

  3.選中其中一個「自定義按鈕」,仿照第2個秘技的第1點對它們進行命名。

  4.右擊某個命名後的按鈕,在隨後彈出的快捷表菜單中,選「指定巨集」選項,打開「指定巨集」交談視窗,選中相應的巨集(如fuhao1等),確定退出。

  重複此步操作,將按鈕與相應的巨集鏈接起來。

  5.關閉「自定義」交談視窗,以後可以像使用普通工具欄一樣,使用「專業符號」工具欄,向單元格中快速輸入專業符號了。


五、用「視面管理器」儲存多個印表網頁面
有的工作表,經常需要印表其中不同的區域,用「視面管理器」吧。

  1.打開需要印表的工作表,用滑鼠在不需要印表的行(或列)標上拖拉,選中它們再右擊滑鼠,在隨後出現的快捷表菜單中,選「隱藏」選項,將不需要印表的行(或列)隱藏起來。

  2.執行「視圖→視面管理器」命令,打開「視面管理器」交談視窗,單擊「新增」按鈕,彈出「新增視面」交談視窗,輸入一個名稱(如「上報表」)後,單擊「確定」按鈕。

  3.將隱藏的行(或列)顯示出來,並重複上述操作,「新增」好其它的印表視面。

  4.以後需要印表某種表格時,打開「視面管理器」,選中需要印表的表格名稱,單擊「顯示」按鈕,工作表即刻按事先設定好的界面顯示出來,簡單設置、排版一下,按下工具欄上的「印表」按鈕,一切就OK了。



六、讓資料按需排序
如果你要將員工按其所在的部門進行排序,這些部門名稱既的有關訊息不是按拼音順序,也不是按筆畫順序,怎麼辦?可採用自定義序列來排序。

  1.執行「格式→選項」命令,打開「選項」交談視窗,進入「自定義序列」標籤中,在「輸入序列」下面的方框中輸入部門排序的序列(如「機關,車隊,一車間,二車間,三車間」等),單擊「新增」和「確定」按鈕退出。

  2.選中「部門」列中任意一個單元格,執行「資料→排序」命令,打開「排序」交談視窗,單擊「選項」按鈕,彈出「排序選項」交談視窗,按其中的下拉按鈕,選中剛才自定義的序列,按兩次「確定」按鈕返回,所有資料就按要求進行了排序。
七、把資料徹底隱藏起來
工作表部分單元格中的內容不想讓瀏覽者查閱,只好將它隱藏起來了。

  1.選中需要隱藏內容的單元格(區域),執行「格式→單元格」命令,打開「單元格格式」交談視窗,在「數位」標籤的「分類」下面選中「自定義」選項,然後在右邊「類型」下面的方框中輸入「;;;」(三個英文狀態下的分號)。

  2.再切換到「保護」標籤下,選中其中的「隱藏」選項,按「確定」按鈕退出。

  3.執行「工具→保護→保護工作表」命令,打開「保護工作表」交談視窗,設置好密碼後,「確定」返回。

  經過這樣的設置以後,上述單元格中的內容不再顯示出來,就是使用Excel的透明功能也不能讓其現形。

  提示:在「保護」標籤下,請不要清除「鎖定」前面復選框中的「ˇ」號,這樣可以防止別人刪除你隱藏起來的資料。
八、讓中、英文輸入法智慧式化地出現
 在編輯表格時,有的單元格中要輸入英文,有的單元格中要輸入中文,反覆切換輸入法實在不方便,何不設置一下,讓輸入法智慧式化地調整呢?

  選中需要輸入中文的單元格區域,執行「資料→有效性」命令,打開「資料有效性」交談視窗,切換到「輸入法模式」標籤下,按「模式」右側的下拉按鈕,選中「打開」選項後,「確定」退出。

  以後當選中需要輸入中文的單元格區域中任意一個單元格時,中文輸入法(輸入法列表中的第1個中文輸入法)自動打開,當選中其它單元格時,中文輸入法自動關閉。
九、讓「自動更正」輸入統一的文本
你是不是經常為輸入某些固定的文本,如《電腦報》而煩惱呢?那就往下看吧。

  1.執行「工具→自動更正」命令,打開「自動更正」交談視窗。

  2.在「替換」下面的方框中輸入「pcw」(也可以是其他字元,「pcw」用小寫),在「替換為」下面的方框中輸入「《電腦報》」,再單擊「新增」和「確定」按鈕。

  3.以後如果需要輸入上述文本時,只要輸入「pcw」字元此時可以不考慮「pcw」的大小寫,然後確認一下就成了
十、在Excel中自定義函數
Excel函數雖然豐富,但並不能滿足我們的所有需要。我們可以自定義一個函數,來完成一些特定的運算。下面,我們就來自定義一個計算梯形面積的函數:

  1.執行「工具→巨集→Visual Basic編輯器」表菜單命令(或按「Alt+F11」快捷鍵),打開Visual Basic編輯視窗。

  2.在視窗中,執行「插入→模塊」表菜單命令,插入一個新的模塊——模塊1。

  3.在右邊的「代碼視窗」中輸入以下代碼:

Function V(a,b,h)
V = h*(a+b)/2
End Function

  4.關閉視窗,自定義函數完成。

  以後可以像使用內置函數一樣使用自定義函數。

  提示:用上面方法自定義的函數通常只能在相應的工作簿中使用。


十一、表頭下面襯張圖片
為工作表新增的背景,是襯在整個工作表下面的,能不能只襯在表頭下面呢?

  1.執行「格式→工作表→背景」命令,打開「工作表背景」交談視窗,選中需要作為背景的圖片後,按下「插入」按鈕,將圖片襯於整個工作表下面。

  2.在按住Ctrl鍵的同時,用滑鼠在不需要襯圖片的單元格(區域)中拖拉,同時選中這些單元格(區域)。

  3.按「格式」工具欄上的「填充顏色」右側的下拉按鈕,在隨後出現的「調色板」中,選中「白色」。
經過這樣的設置以後,留下的單元格下面襯上了圖片,而上述選中的單元格(區域)下面就沒有襯圖片了(其實,是圖片被「白色」遮蓋了)。

  提示襯在單元格下面的圖片是不支持印表的。
十二、用連字元「&」來合併文本
如果我們想將多列的內容合併到一列中,不需要利用函數,一個小小的連字元「&」就能將它搞定(此處假定將B、C、D列合併到一列中)。

  1.在D列後面插入兩個空列(E、F列),然後在D1單元格中輸入公式:=B1&C1&D1。

  2.再次選中D1單元格,用「填充柄」將上述公式複製到D列下面的單元格中,B、C、D列的內容即被合併到E列對應的單元格中。

  3.選中E列,執行「複製」操作,然後選中F列,執行「編輯→選擇性貼上去」命令,打開「選擇性貼上去」交談視窗,選中其中的「數值」選項,按下「確定」按鈕,E列的內容(不是公式)即被複製到F列中。

  4.將B、C、D、E列刪除,完成合併工作。

  提示:完成第1、2步的操作,合併效果已經實現,但此時如果刪除B、C、D列,公式會出現錯誤。故須進行第3步操作,將公式轉換為不變的「值」。
十三、快速印表學產生績條
 常有朋友問「如何印表成績條」這樣的問題,有不少人採取錄製巨集或VBA的方法來實現,這對於初學者來說有一定難度。出於此種考慮,我在這裡給出一種用函數實現的簡便方法。

  此處假定學產生績儲存在Sheet1工作表的A1至G64單元格區域中,其中第1行為標題,第2行為學科名稱。

  1.切換到Sheet2工作表中,選中A1單元格,輸入公式:=IF(MOD(ROW(),3)=0,〞〞,IF(0MODROW(),3(=1,sheet1!A$2,INDEX(sheet1!$A:$G,INT(((ROW()+4)/3)+1),COLUMN())))。

  2.再次選中A1單元格,用「填充柄」將上述公式複製到B1至G1單元格中;然後,再同時選中A1至G1單元格區域,用「填充柄」將上述公式複製到A2至G185單元格中。

  至此,成績條基本成型,下面簡單修飾一下。

  3.調整好行高和列寬後,同時選中A1至G2單元格區域(第1位學生的成績條區域),按「格式」工具欄「邊框」右側的下拉按鈕,在隨後出現的邊框列表中,選中「所有框線」選項,為選中的區域新增邊框(如果不需要邊框,可以不進行此步及下面的操作)。

  4.同時選中A1至G3單元格區域,點擊「常用」工具欄上的「格式刷」按鈕,然後按住滑鼠左鍵,自A4拖拉至G186單元格區域,為所有的成績條新增邊框。

  按「印表」按鈕,即可將成績條印表出來。
十四、Excel幫你選函數
在用函數處理資料時,常常不知道使用什麼函數比較合適。Excel的「搜索函數」功能可以幫你縮小範圍,挑選出合適的函數。

  執行「插入→函數」命令,打開「插入函數」交談視窗,在「搜索函數」下面的方框中輸入要求(如「計數」),然後單擊「轉到」按鈕,系統即刻將與「計數」有關的函數挑選出來,並顯示在「選擇函數」下面的列表框中。再結合檢視相關的幫助文件,即可快速確定所需要的函數。
十五、同時檢視不同工作表中多個單元格內的資料
有時,我們編輯某個工作表(Sheet1)時,需要檢視其它工作表中(Sheet2、Sheet3……)某個單元格的內容,可以利用Excel的「監視視窗」功能來實現。

  執行「視圖→工具欄→監視視窗」命令,打開「監視視窗」,單擊其中的「新增監視」按鈕,展開「新增監視點」交談視窗,用滑鼠選中需要檢視的單元格後,再單擊「新增」按鈕。重複前述操作,新增其它「監視點」。

  以後,無論在哪個工作表中,只要打開「監視視窗」,即可檢視所有被監視點單元格內的資料和相關訊息。



十六、為單元格快速畫邊框
 在Excel 2002以前的版本中,為單元格區域新增邊框的操作比較麻煩,Excel 2002對此功能進行了全新的拓展。

  單擊「格式」工具欄上「邊框」右側的下拉按鈕,在隨後彈出的下拉列表中,選「繪圖邊框」選項,或者執行「視圖→工具欄→邊框」命令,展開「邊框」工具欄。

  單擊工具欄最左側的下拉按鈕,選中一種邊框樣式模板,然後在需要新增邊框的單元格區域中拖拉,即可為相應的單元格區域快速畫上邊框。

  提示:1如果畫錯了邊框,沒關係,選中工具欄上的「擦除邊框」按鈕,然後在錯誤的邊框上拖拉一下,就可以清除掉錯誤的邊框。2如果需要畫出不同顏色的邊框,可以先按工具欄右側的「線條顏色」按鈕,在隨後彈出的調色板中選中需要的顏色後,再畫邊框即可。3這一功能還可以在單元格中畫上對角的斜線。
十七、控制特定單元格輸入文本的長度
你能想像當你在該輸入四位數的單元格中卻填入了一個兩位數,或者在該輸入文字的單元格中你卻輸入了數位的時候,Excel就能自動判斷、即時分析並彈出警告,那該多好啊!要實現這一功能,對Excel來說,也並不難。

  例如我們將游標定位到一個登記「年份」的單元格中,為了輸入的統一和計算的方便,我們希望「年份」都用一個四位數來表示。所以,我們可以單擊「資料」表菜單的「有效性」選項。在「設置」卡片「有效性條件」的「允許」下拉表菜單中選擇「文本長度」。然後在「資料」下拉表菜單中選擇「等於」,且「長度」為「4」。同時,我們再來到「出錯警告」卡片中,將「輸入無效資料時顯示的出錯警告」設為「停止」,並在「標題」和「錯誤訊息」欄中分別填入「輸入文本非法!」和「請輸入四位數年份。」字樣。

  很顯然,當如果有人在該單元格中輸入的不是一個四位數時,Excel就會彈出示的警告交談視窗,告訴你出錯原因,並直到你輸入了正確「樣式模板」的數值後方可繼續錄入。神奇吧?其實,在Excel的「資料有效性」判斷中,還有許多特殊類型的資料格式可選,比如「文本類型」啊,「序列大小」啊,「時間遠近」啊,如你有興趣,何不自作主張,自己設計一種檢測標準,讓你的Excel展示出與眾不同的光彩呢。
十八、成組填充多張表格的固定單元格
我們知道每次打開Excel,軟件總是預定打開多張工作表。由此就可看出Excel除了擁有強大的單張表格的處理能力,更適合在多張相互關聯的表格中協調工作。要協調關聯,當然首先就需要同步輸入。因此,在很多情況下,都會需要同時在多張表格的相同單元格中輸入同樣的內容。

  那麼如何對表格進行成組編輯呢?首先我們單擊第一個工作表的標籤名「Sheet1」,然後按住Shift鍵,單擊最後一張表格的標籤名「Sheet3」(如果我們想關聯的表格不在一起,可以按住Ctrl鍵進行點選)。此時,我們看到Excel的標題欄上的名稱出現了「工作組」字樣,我們就可以進行對工作組的編輯工作了。在需要一次輸入多張表格內容的單元格中隨便寫點什麼,我們發現,「工作組」中所有表格的同一位置都顯示出相應內容了。

  但是,僅僅同步輸入是遠遠不夠的。比如,我們需要將多張表格中相同位置的資料統一改變格式該怎麼辦呢?首先,我們得改變第一張表格的資料格式,再單擊「編輯」表菜單的「填充」選項,然後在其子表菜單中選擇「至同組工作表」。這時,Excel會彈出「填充成組工作表」的交談視窗,在這裡我們選擇「格式」一項,點「確定」後,同組中所有表格該位置的資料格式都改變了。
十九、改變文本的大小寫
在Excel中,為表格處理和資料運算提供最強大支持的不是公式,也不是資料庫,而是函數。不要以為Excel中的函數只是針對數位,其實只要是寫進表格中的內容,Excel都有對它編輯的特殊函數。例如改變文本的大小寫。

  在Excel 2002中,至少提供了三種有關文本大小寫轉換的函數。它們分別是:「=UPPER(源資料格)」,將文本全部轉換為大寫;「=LOWER(源資料格)」,將文本全部轉換成小寫;「=PROPER(源資料格)」,將文本轉換成「適當」的大小寫,如讓每個單詞的首字母為大寫等。例如,我們在一張表格的A1單元格中輸入小寫的「excel」,然後在目標單元格中輸入「=UPPER(A1)」,Enter鍵後得到的結果將會是「EXCEL」。同樣,如果我們在A3單元格中輸入「mr.weiwei」,然後我們在目標單元格中輸入「=PROPER(A3)」,那麼我們得到的結果就將是「Mr.Weiwei」了。
二十、提取字元串中的特定字元
除了直接輸入外,從已存在的單元格內容中提取特定字元輸入,絕對是一種省時又省事的方法,特別是對一些樣式模板雷同的訊息更是如此,比如員工名單、籍貫等訊息。

  如果我們想快速從A4單元格中提取稱謂的話,最好使用「=RIGHT(源資料格,提取的字元數)」函數,它表示「從A4單元格最右側的字元開始提取2個字元」輸入到此位置。當然,如果你想提取姓名的話,則要使用「=LEFT(源資料格,提取的字元數)」函數了。還有一種情況,我們不從左右兩端開始,而是直接從資料中間提取幾個字元。比如我們要想從A5單元格中提取「武漢」兩個字時,就只須在目標單元格中輸入「=MID(A5,4,2)」就可以了。意思是:在A5單元格中提取第4個字元後的兩個字元,也就是第4和第5兩個字。



二十一、把基數詞轉換成序數詞
將英文的基數詞轉換成序數詞是一個比較複雜的問題。因為它沒有一個十分固定的模式:大多數的數位在變成序數詞都是使用的「th」後綴,但大凡是以「1」、「2」、「3」結尾的數位卻分別是以「st」、「nd」和「rd」結尾的。而且,「11」、「12」、「13」這3個數位又不一樣,它們卻仍然是以「th」結尾的。因此,實現起來似乎很複雜。其實,只要我們理清思路,找準函數,只須編寫一個公式,就可輕鬆轉換了。不信,請看:「=A2&IF(OR(VALUE(RIGHT(A2,2))={11,12,13}),〞th〞,IF(OR(VALUE(RIGHT(A2))={1,2,3,},CHOOSE(RIGHT(A2),〞st〞,〞nd〞,〞rd〞),〞th〞))」。該公式儘管一長串,不過含義卻很明確:1如果數位是以「11」、「12」、「13」結尾的,則加上「th」後綴;2如果第1原則無效,則檢查最後一個數位,以「1」結尾使用「st」、以「2」結尾使用「nd」、以「3」結尾使用「rd」;3如果第1、2原則都無效,那麼就用「th」。因此,基數詞和序數詞的轉換實現得如此輕鬆和快捷。
和財務打過交道的人都知道,在賬面填充時有一種約定俗成的「安全填寫法」,那就是將金額中的空位補齊,或者在款項資料的前面加上「$」之類的符號。其實,在Excel中也有類似的輸入方法,那就是「REPT」函數。它的基本格式是「=REPT(「特殊符號」,填充位數)」。

  比如,我們要在中A2單元格裡的數位結尾處用「#」號填充至16位,就只須將公式改為「=(A2&REPT(〞#〞,16-LEN(A2)))」即可;如果我們要將A3單元格中的數位從左側用「#」號填充至16位,就要改為「=REPT(〞#〞,16-LEN(A3)))&A3」;另外,如果我們想用「#」號將A4中的數值從兩側填充,則需要改為「=REPT(〞#〞,8-LEN(A4)/2)&A4&REPT(〞#〞)8-LEN(A4)/2)」;如果你還嫌不夠專業,要在A5單元格數位的頂頭加上「$」符號的話,那就改為:「=(TEXT(A5,〞$#,##0.00〞(&REPT(〞#〞,16-LEN(TEXT(A5,〞$#,##0.00〞))))」,一定能滿足你的要求。
二十三、創建文本直方圖
 除了重複輸入之外,「REPT」函數另一項衍生應用就是可以直接在工作表中創建由純文本組成的直方圖。它的原理也很簡單,就是利用特殊符號的智慧式重複,按照指定單元格中的計算結果表現出長短不一的比較效果。

  比如我們首先製作一張年度收支平衡表,然後將「E列」作為直方圖中「預算內」月份的顯示區,將「G列」則作為直方圖中「超預算」的顯示區。然後根據表中已有結果「D列」的數值,用「Wingdings」字體的「N」字元表現出來。具體步驟如下:

  在E3單元格中寫入公式「=IF(D3<0,REPT(〞n〞,-ROUND(D3*100,0)),〞〞)」,然後選中它並拖動「填充柄」,使E列中所有行都能一一對應D列中的結果;接著在G3單元格中寫入公式「=IF(D3>0,REPT(〞n〞,ROUND(D3*100,0)),〞〞)」,也拖動填充柄至G14。我們看到,一個沒有動用Excel圖表功能的純文本直方圖已展現眼前,方便直觀,簡單明瞭
二十四、計算單元格中的總字數
有時候,我們可能對某個單元格中字元的數量感興趣,需要計算單元格中的總字數。要解決這個問題,除了利用到「SUBSTITUTE」函數的虛擬計算外,還要動用「TRIM」函數來刪除空格。比如現在A1單元格中輸入有「how many words?」字樣,那麼我們就可以用如下的表達式來幫忙:

  「=IF(LEN(A1)=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),〞,〞,〞〞))+1)」

  該式的含義是先用「SUBSTITUTE」函數創建一個新字元串,並且利用「TRIM」函數刪除其中字元間的空格,然後計算此字元串和原字元串的數位差,從而得出「空格」的數量,最後將空格數+1,就得出單元格中字元的數量了。
二十五、關於歐元的轉換
 這是Excel 2002中的新工具。如果你在安裝Excel 2002時選擇的是預定方式,那麼很可能不能在「工具」表菜單中找到它。不過,我們可以先選擇「工具」表菜單中的「載入巨集」,然後在彈出視窗中勾選「歐元工具」選項,「確定」後Excel 2002就會自行安裝了。

  完成後我們再次打開「工具」表菜單,單擊「歐元轉換」,一個獨立的專門用於歐元和歐盟成員國貨幣轉換的視窗就出現了。與Excel的其他函數視窗一樣,我們可以通過滑鼠設置貨幣轉換的「源區域」和「目標區域」,然後再選擇轉換前後的不同幣種即可。所示的就是「100歐元」分別轉換成歐盟成員國其他貨幣的比價一覽表。當然,為了使歐元的顯示更顯專業,我們還可以點擊Excel工具欄上的「歐元」按鈕,這樣所有轉換後的貨幣數值都是歐元的樣式模板了。
二十六、給表格做個超級搜索引擎  我們知道,Excel表格和Word中的表格最大的不同就是Excel是將填入表格中的所有內容(包括靜態文本)都納入了資料庫的範疇之內。我們可以利用「函數查詢」,對目標資料進行精確定位,就像網頁中的搜索引擎一樣。

  比如在所示的表格中,從A1到F7的單元格中輸入了多名同學的各科成績。而在A8到A13的單元格中我們則建立了一個「函數查詢」區域。我們的設想是,當我們在「輸入學生姓名」右邊的單元格,也就是C8格中輸入任何一個同學的名字後,其下方的單元格中就會自動顯示出該學生的各科成績。具體實現的方法如下:

  將游標定位到C9單元格中,然後單擊「插入」之「函數」選項。在如圖18彈出的視窗中,選擇 「VLOOKUP」 函數,點「確定」。在隨即彈出的「函數參數」視窗中我們設置「Lookup_value」(指需要在資料表首列中搜索的值)為「C8」(即搜索我們在C8單元格中填入的人名);「Table_array」(指資料搜索的範圍)為「A2︰B6」(即在所有學生的「語文」成績中搜索);「Col_vindex_num」(指要搜索的數值在表格中的序列號)為「2」(即數值在第2列);「Range_lookup」(指是否需要精確匹配)為「FALSE」(表明不是。如果是,就為「TURE」)。設定完畢按「確定」。

  此時回到表格,單擊C9單元格,我們看到「fx」區域中顯示的命令行為「=VLOOKUP(C8,A2︰B6,2,FALSE)」。複製該命令行,在C10、C11、C12、C13單元格中分別輸入:「=VLOOKUP(C8,A2︰C6,3,FALSE)」;「=VLOOKUP(C8,A2︰D6,4,FALSE)」;「=VLOOKUP(C8,A2︰E6,5,FALSE)」;「=VLOOKUP(C8,A2︰F6,6,FALSE)」(其參數意義同C9中一樣,不再贅述)。

  接下來,我們就來檢驗「VLOOKUP」函數的功效。試著在「C8」單元格中輸入某個學生名,比如「趙耀」,Enter鍵之下我們會發現,其下方每一科目的單元格中就自動顯示出該生的入學成績了。
二十七、Excel工作表大綱的建立
和Word的大綱視圖一樣,Excel這個功能主要用於處理特別大的工作表時,難以將關鍵條目顯示在同一屏上的問題。如果在一張表格上名目繁多,但資料類型卻又有一定的可比性,那麼我們完全可以先用滑鼠選擇資料區域,然後點擊「資料」表菜單的「分類匯總」選項。並在彈出表菜單的「選定匯總項」區域選擇你要匯總資料的類別。最後,如圖19所示,現在的表格不是就小了許多嗎?當然,如果你還想檢視明細的話,單擊表格左側的「+」按鈕即可。
二十八、插入「圖示」
 儘管有14大類50多種「圖表」樣式模板給Excel撐著腰,但對於紛繁複雜的資料關係,一般的圖表表示方法仍顯得枯燥和缺乏想像力。因此在最新版本Excel 2002中加入了「圖示」的功能。雖然在「插入」表菜單的「圖示」視窗中只有區區6種樣式模板,但對於說明資料之間的結構卻起到了「四兩撥千斤」的效果。比如要顯示資料的層次關係可以選擇「組織結構圖」;而要表達資金的流通過程則可以選擇「循環圖」;當然,要說明各種資料的交叉重疊性可以選擇「維恩圖」。你看,如圖20所示的維恩圖多麼漂亮。而且你還可以右擊該圖示,調出「圖示」工具欄。隨心所欲地設置「圖示樣式模板庫」甚至還可以多新增幾個圓環。


二十九、熟悉Excel的「從文本到語音」
 這是Excel 2002中的一個搶眼的新功能。它就是讓Office軟件不僅能寫、能算,還要能看、能說。儘管目前已經有許多軟件都致力於文本的機器朗讀,但微軟的Office不論從語音的柔和度和語氣的抑揚頓挫都是其它同類軟件不可比擬的。

  按照Office的預定安裝,你可能還不能在Excel的表菜單中找到它,在我們確認安裝了「從文本到語音」的選項後,就可以在Excel的「工具」表菜單中看到「語音」專案了。如圖21所示,打開一個表格,隨便選擇其中的內容,然後單擊「從文本到語音」工具欄上的「朗讀單元格」按鈕,此時一個帶有磁性的聲音就開始一字一句地朗讀了。值得一提的是,當碰到標點符號時,Excel的朗讀會自動停頓一會兒,然後再接著朗讀,這一點和其他軟件完全不同,筆者認為這樣的處理更加自然。

  還有一點需要說明的是,如果你想調整Excel 2002中語音朗讀的速度,可以在「控制台」中雙擊「語音」圖示,然後在「文字到語音」卡片上調整即可
三十、Excel中「攝影」功能的妙用
這個功能比較另類,似乎和計算、統計、排序等等「正統」功能格格不入,因為它的作用竟然是——「抓屏」!而且還不是像「PrintScreen」按鈕那樣「一把亂抓」,而是允許讓你通過滑鼠進行選擇,「指哪抓哪」。

  要找到這個功能還不太容易,我們點擊Excel「工具」表菜單的「自定義」選項。在「命令」卡片的「類別」中點「工具」,再在命令欄中找到「攝影」按鈕,並將它拖到工具欄的任意位置。如果我們想要對表格中的某一部分「照相」,只須先選擇它們(圖23),然後按下「攝影」按鈕,這時該選定區域就被「拍」了下來。然後將滑鼠移動到需要顯示「照片」的地方(當然,也可以是另一張表格),再次按下「攝影」按鈕,這樣剛才被「拍攝」的「照片」就立即貼上去過來了。當然,和「照片」一同出現的還有「圖片」工具欄。很顯然,Excel是將它作為一幅圖片來對待了,我們可隨意將它進行旋轉、縮放處理。

  不過,請各位一定要注意:這可不是一般的照片!你可以試著改動原來被我們「拍攝」下來的表格資料看看——重新整理一下「照片」,結果「照片」中的資料竟然也被同步更新了!
三十一、在多張表格間實現公用資料的鏈接和引用
也許我們會奇怪,為什麼每次打開Excel,軟件總是打開了一個由3張空白表格組成的文件組呢?如果你是專業的會計師、統計師或者諳熟於此的表格高手,就一定會明白,由於計算專案的名目繁多、資料訊息的頭緒複雜,單靠一張表格往往解決不了所有的問題,多表格資料鏈接、多文件資料互動才是以一當十的制勝法寶。

  比如我們首先製作「Sheet1」為「一班學產生績表」,「Sheet2」為「二班學產生績表」,然後來到「Sheet3」,想進行「一班」和「二班」各科成績的比較以及「年級平均分」的計算。此時我們就可以將游標定位到目標位置,選擇好相關函數。然後在Excel彈出的函數交談視窗中,利用資料列表右側的「」按鈕點擊一下其他表格中想引用的單元格就行了。你看,如圖24所示,這時函數視窗中就會出現「×班學產生績表!××單元格」的字樣了。此後,不管我們在源單元格中進行怎樣的修改,在「Sheet3」的目標位置都會適時進行重新計算
三十二、「馴服」Excel 2002的剪貼板視窗
Excel 2002極大地改進了舊版本中比較薄弱的剪貼板功能,最大可以支持容納24個專案(而Excel 2000只能容納12個)。而且,這個剪貼板可以在任何Office應用程式之間來回拷貝,所以對於經常需要拷貝和貼上去的用戶來說,確實更加方便。但每次當你連續使用兩次「複製」或「剪下」命令時,剪貼板就會彈出來,和你爭奪有限的文檔顯示空間,讓人討厭。好在,「馴服」剪貼板的方法非常簡單。

  筆者建議,如果你不希望剪貼板總是出其不意地蹦出來,只須點擊剪貼板表菜單底部的「選項」,清除「自動顯示Office剪貼板」復選框上的鉤。如果你不希望剪貼板的圖示出現在系統任務欄上或隨時彈出來,只須清除掉「在任務欄上顯示Office剪貼板的圖示」和「複製時在任務欄附近顯示狀態」兩個復選框上的選擇。只保留「收集而不顯示Office剪貼板」復選框前面的鉤即可。

  當然,在取消剪貼板自動顯示功能之後,你還可以隨時恢復這個功能。比如我們可以自定義一組快捷鍵,以便隨時迅速調出剪貼板。從表菜單中選取「工具」之「自定義」選項,點擊彈出交談視窗下部的 「鍵盤」按鈕,在彈出交談視窗的 「類別」列表中選取「編輯」,然後,在交談視窗右上方的「命令」列表中選取「EditOfficeClipboard」; 將游標置於「請按新快捷鍵」交談視窗中,並按下Alt+Shift+C(或者你喜歡的其他組合),「關閉」交談視窗。現在,我們只要一按下Alt+Shift+C,Excel 2002的剪貼板就會立燒錄出現在你面前了。

  提示:此秘技也適用於Word 2002、PowerPint 2002。
三十三、利用公式審核工具檢視資料出處
 Excel 2002有一秘密武器——「公式審核」工具,它可以將任一單元格中資料的來源和計算結果的去處顯示得清清楚楚、明明白白。

  讓我們單擊「工具」表菜單的「公式審核」選項,並點擊「顯示公式審核工具欄」。我們仍然借用「給表格做個超級搜索引擎」一招中的例子,用滑鼠選擇C12單元格。從圖25中我們可以看到,該單元格中的結果是通過函數「=VLOOKUP(C8,A2︰C6,3,FALSE)」得出的。因此,資料來源有3個:C8、A2和C6。所以,當我們單擊「公式審核」工具欄上的「追蹤引用單元格」按鈕後,Excel立刻用箭頭和藍點指明了這3個單元格的所在(圖25)。當然,如果我們表格中某個資料無效或語法不當的話,也可以點擊「公式審核」工具欄上的「圈釋無效資料」按鈕來讓Excel自動幫我們檢查紕漏。
三十四、巧用Excel 2002的「智慧式滑鼠」
 我們知道,滾輪滑鼠現在幾乎成為了電腦的「標配」,但當我們滾動滑鼠滾輪上下翻頁時,你是否想過我們還可以利用它完成一些其他的功能呢?

  點擊Excel 2002「工具」表菜單中的「選項」命令,然後在「一般」選擇項中選中「用智慧式滑鼠縮放」復選框,點「確定」後,我們再來看看現在的滑鼠滾輪都有些怎樣的功能:

  在「智慧式滑鼠」的預定狀態下,上下滾動滑鼠滾輪,工作區中的表格會以15%的比例放大或縮小,而只有當我們按住Ctrl鍵,再滾動滑鼠滾輪時,工作表才會像往常一樣上下翻頁。另外,如果我們使用了Excel的「分級顯示」,那麼當我們按住Shift和滾動滑鼠滾輪時,又可以控制各級條目的顯示或隱藏了。當然,還有更多的特殊功用需要各位在實踐中慢慢摸索。
三十五、Excel 2002「監視」視窗的應用
 如果你創建了一個較大的電子錶格,並且該表格具有鏈接到其他工作簿的資料時, Excel 中的「監視視窗」可以為你提供很大的幫助。通過它你可以輕鬆看到工作表、單元格和公式函數在改動時是如何影響當前資料的。

  在「工具」表菜單中單擊「公式審核」子表菜單,然後單擊「顯示監視視窗」按鈕。右擊我們想跟蹤的單元格,並在快捷表菜單中選擇「新增監視點」。這時,「監視視窗」的列表中就出現了被Excel監視的單元格及其公式了。

  以後,只要我們雙擊「監視視窗」中的該條目,被監視的單元格就會不請自來了。

  提示:當包含有指向其他工作簿的單元格被監視時,只有當所有被引用的工作簿都打開時,才能在「監視窗 口」的列表中顯示出來。
__________________
http://bbsimg.qianlong.com/upload/01/08/29/68/1082968_1136014649812.gif
psac 目前離線  
送花文章: 3, 收花文章: 1625 篇, 收花: 3196 次