會計應該學會Excel的35招秘技(1)

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

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

我們常常要將企業的名稱輸入到表格中,爲了保持名稱的一致性,利用“數據有效性”功能建了一個分類下拉列表填充項。

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

2.選中A列(“工業企業”名稱所在列),在“名稱”欄內,輸入“工業企業”字符後,按“回車”鍵進行確認。

仿照上面的操作,將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)/2End 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對此功能進行了全新的拓展。

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

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

提示:①如果畫錯了邊框,沒關系,選中工具欄上的“擦除邊框”按鈕,然後在錯誤的邊框上拖拉一下,就可以清除掉錯誤的邊框。②如果需要畫出不同顔色的邊框,可以先按工具欄右側的“線條顔色”按鈕,在隨後彈出的調色板中選中需要的顔色後,再畫邊框即可。③這一功能還可以在單元格中畫上對角的斜線。

十七、控制特定單元格輸入文本的長度

你能想象當你在該輸入四位數的單元格中卻填入了一個兩位數,或者在該輸入文字的單元格中你卻輸入了數字的時候,Excel就能自動判斷、即時分析並彈出警告,那該多好啊!要實現這一功能,對Excel來說,也並不難。

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

很顯然,當如果有人在該單元格中輸入的不是一個四位數時,Excel就會彈出示的警告對話框,告訴你出錯原因,並直到你輸入了正確“樣式”的數值後方可繼續錄入。神奇吧?其實,在Excel的“數據有效性”判斷中,還有許多特殊類型的數據格式可選,比如“文本類型”啊,“序列大小”啊,“時間遠近”啊,如你有興趣,何不自作主張,自己設計一種檢測標准,讓你的Excel展示出與衆不同的光彩呢。

十八、成組填充多張表格的固定單元格

我們知道每次打開Excel,軟件總是默認打開多張工作表。由此就可看出Excel除了擁有強大的單張表格的處理能力,更適合在多張相互關聯的表格中協調工作。要協調關聯,當然首先就需要同步輸入。因此,在很多情況下,都會需要同時在多張表格的相同單元格中輸入同樣的內容。

會計應該學會Excel的35招秘技(1)
  也許你已經在Excel中完成過上百張財務報表,也許你已利用Excel函數實現過上千次的複雜運算,也許你認爲Excel也不過如此,甚至了無新意。但我們平日裏無數次重複的得心應手的使用方法只不過是Excel全部技巧的百分...查看完整版>>會計應該學會Excel的35招秘技(1)
 
鮮爲人知 Excel會計必學35招秘技下-MSOFFICE
  十八、成組填充多張表格的固定單元格  我們知道每次打開Excel,軟件總是默認打開多張工作表。由此就可看出Excel除了擁有強大的單張表格的處理能力,更適合在多張相互關聯的表格中協調工作。要協調關聯,當然首...查看完整版>>鮮爲人知 Excel會計必學35招秘技下-MSOFFICE
 
鮮爲人知 Excel會計應學35招秘技上-MSOFFICE
  也許你已經在Excel中完成過上百張財務報表,也許你已利用Excel函數實現過上千次的複雜運算,也許你認爲Excel也不過如此,甚至了無新意。但我們平日裏無數次重複的得心應手的使用方法只不過是Excel全部技巧的百分...查看完整版>>鮮爲人知 Excel會計應學35招秘技上-MSOFFICE
 
Excel經典實用技巧35招
一、建立分類下拉列表填充項  我們常常要將企業的名稱輸入到表格中,爲了保持名稱的一致性,利用“數據有效性”功能建了一個分類下拉列表填充項。   1.在Sheet2中,將企業名稱按類別(如“工業企業”、“商業企業...查看完整版>>Excel經典實用技巧35招
 
美國注冊助理管理會計師培訓教材(高才財經系列叢書)|報價¥35.70|圖書,考試,資格考試/職稱考試,其他考試,陳越
目錄:圖書,考試,資格考試/職稱考試,其他考試,品牌:陳越基本信息·出版社:上海交通大學出版社·頁碼:282 頁·出版日期:2009年·ISBN:9787313055019·條形碼:9787313055020·包裝版本:1版·裝幀:平裝·開本:...查看完整版>>美國注冊助理管理會計師培訓教材(高才財經系列叢書)|報價¥35.70|圖書,考試,資格考試/職稱考試,其他考試,陳越
 
統計學--理論實務案例(高等院校統計學系列教材)|報價¥35.30|圖書,經濟與管理,會計、審計,審計,綜合,趙振倫
目錄:圖書,經濟與管理,會計、審計,審計,綜合,品牌:趙振倫基本信息·出版社:立信會計出版社·頁碼:425 頁碼·出版日:2005年·ISBN:7542915835·條碼:9787542915832·版次:1·裝幀:平裝·開本:16開 16開內容...查看完整版>>統計學--理論實務案例(高等院校統計學系列教材)|報價¥35.30|圖書,經濟與管理,會計、審計,審計,綜合,趙振倫
 
不開心的時候,應該學會調節自己的情緒。
  人總有情緒低落的時候--------也許由于一個人,也許由于一件事,讓人久久不能釋懷。情緒的低落,既會影響生活,也會影響日常的工作學習。當你感覺你正在被一些題目所困擾時,不妨試著照下面的方法往做,也許對你...查看完整版>>不開心的時候,應該學會調節自己的情緒。
 
人生應該學會的東西
  學會忘記   人的大腦像一個倉庫,它的容量是有限的。   你看,電腦隨時可以刪除無用的文件,而且還聰明地設一個“垃圾站”,把廢棄的東西永遠忘掉。人應該從電腦那裏得到一些啓示,也爲自己設一個...查看完整版>>人生應該學會的東西
 
教你學會應該怎樣把HTML結構化-css設計
  DIV+css的結構  你正在學習CSS布局嗎?是不是還不能完全掌握純CSS布局?通常有兩種情況阻礙你的學習:  第一種可能是你還沒有理解CSS處理頁面的原理。在你考慮你的頁面整體表現效果前,你應當先考慮內容的語...查看完整版>>教你學會應該怎樣把HTML結構化-css設計
 
 
回到王朝網路首頁