《玩轉(zhuǎn)Excel財(cái)務(wù)技巧》

Excel利用Excel函數(shù)實(shí)現(xiàn)過上千次的復(fù)雜運(yùn)算，也Excel，甚至了無新意。但我們平日里無數(shù)次重復(fù)的得心應(yīng)手的使用方法只無非是Excel一。本專題從Excel2002中的一些鮮為人知的技巧入手，領(lǐng)略一下關(guān)于Excel的別樣，為了保持名稱的一致性，利用數(shù)“據(jù)有效性”功能建了一L在Sheet2中，將企業(yè)名稱按類別(如工“業(yè)企業(yè)”、商“業(yè)企業(yè)”、個(gè)體企業(yè)等)分別輸入不同列3切換到Sheets,選中需要輸入企業(yè)類別”的列(如C列)，執(zhí)行數(shù)據(jù)一W效性端令打開數(shù)據(jù)有效性'允許右側(cè)的下拉按鈕，選中序例選項(xiàng)，在下面的來“源方框中，輸入工“業(yè)企業(yè)”，商“業(yè)企業(yè)”，個(gè)體企業(yè)”…序…列(各元素之間用英文逗號(hào)隔開)，確定退出。再選中需要輸入企業(yè)名稱的列(如D列)，再打開數(shù)據(jù)有效性”對話框，選中序“列”選項(xiàng)后，在來CC中該單元格對應(yīng)的D列單元格(如D4),單擊下拉按鈕，即可從相應(yīng)類別的企業(yè)名稱列表中選擇需要的企業(yè)檔”菜單，將常用的工作簿文檔添加到其中，方便隨時(shí)調(diào)A最右側(cè)的字符開始提取2個(gè)字符，輸入到此位置。當(dāng)然，如果你想提T字符。比如我們要想從A5單元格中提取武雙”兩個(gè)字時(shí)，就只須在目MIDA”可以了。意思是：在A5單元格中提取第4個(gè)字符后的兩個(gè)字符，也就是數(shù)字在變成序數(shù)詞都是使用的'痂'綴，但大凡是以“、”2、結(jié)尾的數(shù)字卻分別是以“sTK訊r延胡尾的。而且，力1、力2、力3這，3個(gè)數(shù)字又不一樣，它們卻仍然是以“th結(jié)”尾的。因此，實(shí)現(xiàn)起來似乎很復(fù)雜。其實(shí)，HOOSE尾使用“、'以3縉尾使用W；'③如果第1、2原則都無效，那末就用“th。咽此，基數(shù)詞和序數(shù)詞的轉(zhuǎn)換實(shí)和財(cái)務(wù)打過交道的人都知道，在賬面填充時(shí)有一種約定俗成的安“全填寫法”，那就是將金額中的rAREPTELEN(A2))和可；如果我們要將A3單元格中的數(shù)字從左側(cè)用*號(hào)填充至16位,就要改為qREPTlLENAh我們想用，㈱將A4中的數(shù)值從兩側(cè)填充，則需要改為qREPT〃",8-除了重復(fù)輸入之外，“REPT函數(shù)另一項(xiàng)衍生應(yīng)用就是可以直接在工作表中創(chuàng)建由純文本組成的直方圖。它的原理也很簡單，就是利用特殊符號(hào)的智能重復(fù)，按照指定單元格中的計(jì)算結(jié)果表現(xiàn)出長短不一的表中已有結(jié)果中列”的數(shù)EIFD<0咫P〃〃(,-ROUND(D3*100,0))〃,)e,然后選中它并拖動(dòng)填“充E應(yīng)D列中的結(jié)果；接著在G3單元格中寫入公式&IF(D3>0REP"〃ROUNDDT拖動(dòng)填充柄至G14。我們看到，一個(gè)沒有動(dòng)用Excel圖表功能的純文本直方圖SUBSTITUTE外，還要?jiǎng)佑谩癟RIM”函數(shù)來刪除空格。比如現(xiàn)在A1單元rds，MF(LEN(Al>O,(XLEN(TRIM&ALlE)NXSUBSTrnJTE(TRIM(Al)"/,T>H)”該式的含義是先用“SUBSTITUTE函數(shù)創(chuàng)建一個(gè)新字符串，并且利用叮RIM”函數(shù)刪除其中字符間的Excel在安裝Excel2002時(shí)選擇的是默認(rèn)方式，那末很可能不能在工"嗔菜單中的加載宏'，然后在彈出窗口中勾選歐元工具"選擊歐玩;轉(zhuǎn)換、一個(gè)獨(dú)立的專門用于歐元和歐盟成員國貨幣轉(zhuǎn)Excel窗口一樣，我們可以通過鼠標(biāo)設(shè)置貨幣轉(zhuǎn)換的源“區(qū)域”和目標(biāo)不同幣種即可。所示的就是“100歐元”分別轉(zhuǎn)換成歐盟成員國其他貨幣的顯示更顯專業(yè)，我們還可以點(diǎn)擊Excel工具欄上的歐“元”按鈕，這樣A。我們的設(shè)想是，當(dāng)我們在輸入學(xué)生姓名”右邊的C學(xué)的名字后，其下方的單元格中就會(huì)自動(dòng)顯示出該學(xué)生的各科成C然后單擊插入”之函“數(shù)選項(xiàng)。在如圖18彈出的窗口中，選擇4VLOOKUT函數(shù)，點(diǎn)確定:在隨即彈出的函數(shù)參數(shù)崎口中我們設(shè)置，Lookup_yalue指嚅(要在數(shù)據(jù)表首列中搜索的值)為P8”即搜(索我們在C8單元格中填入的人名)；叮數(shù)(據(jù)搜索的范圍)為“A2B:6即而所有學(xué)生的語文成績中搜索)；'a)Lvindex_nunT指要偎索的數(shù)值在表格中的序列號(hào))為‘2’即數(shù)(值在第2列)；’Range_look叩肝是(否需要精確匹配)為甲ALSE，表明(不UREC看到七區(qū)，域中顯示的命令行為q'JVLOOKUP(C8,A2C：6,3,FALSE),,;^VLOOKUP(C8,A2D：6,4,FALSE)”；JVLOOKUP(C8,A2E：6,5,FALSE)”；^VLOOKUP(C8,A2F：6,6,FALSE『其(參數(shù)意義同C9中一樣，再也不贅述)。接下來，我們就來檢驗(yàn)"LOOKUP”函數(shù)的功效。試著在“C8單”元格中輸入某個(gè)學(xué)生名，比如趙“耀l名目繁多，但數(shù)據(jù)類型卻又有一定的可比性，那末我們徹底可以先用鼠菜單的分“類匯總”選項(xiàng)。并在彈出菜單的選“定匯總項(xiàng)''區(qū)域選擇你9所示，現(xiàn)在的表格不是就小了許多嗎?固然，如果你還想查看明細(xì)的盡管有14大類50多種圖“表，樣式給Excel撐著腰，但對于紛繁復(fù)雜的數(shù)據(jù)關(guān)系，常規(guī)的圖表表示方二十九、熟悉Excel的從“文本到語音”力于文本的機(jī)器朗讀，但微軟的Office不論從語音的柔和度和語氣的抑揚(yáng)頓按照Office的默認(rèn)安裝，你可能還不能在Excel的菜單中找到它，在我們確認(rèn)安裝了從“文本到語音的選項(xiàng)后，就可以在Excel的工“具噪單中看到語，音項(xiàng)目了。如圖21所示，打開一個(gè)表格，隨便選擇其中的內(nèi)容，然后單擊從“文本到語音”工具欄上的朗骨賣單元格”按鈕，此時(shí)一個(gè)帶有磁性的聲音就開l卡片上調(diào)整即可計(jì)、排序等等正“統(tǒng)”功能格格不入，因?yàn)樗淖饔胷f命令欄中找到攝影按鈕，并將它拖^工具欄的任意位置。如果我們想要對表格中的某一后將鼠標(biāo)挪移到需要顯示照“片’的地方(固然，也可以是另一張表格)，再次按下攝影'按鈕，這樣剛才，通的照片!你可以試著改動(dòng)原來被我們拍“攝”下來的表格數(shù)據(jù)的鏈接和引用Excel了一個(gè)由3張空白表格組成的文件組呢?如果往解決不了所有的問題，多表格數(shù)據(jù)鏈接、多文件數(shù)據(jù)互動(dòng)才是以一當(dāng)StealStaQISeB想進(jìn)行一班和二班’各科成績的I：瞅以及年級平均分的計(jì)算。此時(shí)我們就可以將光標(biāo)定位到目標(biāo)位置，選擇好相關(guān)函數(shù)。然后在Excel彈出的函數(shù)對話框中，利用數(shù)據(jù)列表右側(cè)的“按”鈕點(diǎn)擊一下其他表格中想引用的Bcel口Excel2002極大地改進(jìn)了舊版本中比較薄弱的剪貼板功能，最大可以支持容納24個(gè)項(xiàng)目(而Excel2000只能容納12個(gè))。而且”這個(gè)剪貼板可以在任何Office應(yīng)用程序之間來回拷貝,所以對于時(shí)常需要拷筆者建議，如果你不希翼剪貼板總是出其不意地蹦出來，只須點(diǎn)擊剪貼板菜單底部的選“項(xiàng):清除自動(dòng)顯示Office剪貼板”復(fù)選框上的鉤。如果你不希望剪貼板的圖標(biāo)浮現(xiàn)在系統(tǒng)任務(wù)欄上或者隨時(shí)彈出來，貼板。從菜單中選取工“具”之咱定義”選項(xiàng)，點(diǎn)擊彈出對話框下部的鍵常按上方的能令冽表中選取“EditOffioeClipbo；ar將cf光標(biāo)置于請按新快捷鍵對話框中,并按下Alt+Shift強(qiáng)(者你喜歡的其他組合)，關(guān)“閉”對話框?，F(xiàn)在，我們只要一按下Alt+Shift+,CExcel2002的剪貼板就會(huì)數(shù)據(jù)出處Excel2002有一秘密武器一一公"式審核”工具，它可以將任一單元格中數(shù)據(jù)的來源和計(jì)算結(jié)果的去L審核工具欄我們?nèi)匀唤栌媒o“表格做個(gè)，用鼠標(biāo)選擇C12單元格。從圖25中我們可以看到，該單元格中的結(jié)果是通CACFALSEKCAC引用單元格按鈕后，Exce應(yīng)刻用箭頭和藍(lán)點(diǎn)指明了這3個(gè)單元格的所在(圖25)。固然，如果我們表格中某個(gè)數(shù)據(jù)式審核”工具欄上的圈“釋無效數(shù)據(jù)”按鈕來讓Excel自動(dòng)幫我三十四、巧用Excel2002的智“能鼠標(biāo)”我們知道，滾輪鼠標(biāo)現(xiàn)在幾乎成為了電腦的標(biāo)“配”，但當(dāng)我們滾動(dòng)鼠標(biāo)滾輪上下翻頁時(shí),你是否想復(fù)選框,點(diǎn)確“定”后，在智“能鼠標(biāo)”的默認(rèn)狀態(tài)下，上下滾動(dòng)鼠標(biāo)滾輪，工作區(qū)中的表格會(huì)以15%的比例放大或者縮小，的分“級顯示"，那末當(dāng)我們按住Shift和滾動(dòng)鼠標(biāo)滾輪時(shí)，又可以控制各級條目的顯示或者隱藏了。固然，。公式函數(shù)在改動(dòng)時(shí)是如何影響在工“具噪單中單擊公”式審核，子菜單，然后單擊顯，示監(jiān)視窗口，按鈕。右擊我們想跟蹤的單元格，并在快捷菜單中選擇添加監(jiān)視點(diǎn):這時(shí)，監(jiān)視窗口”的列表中就浮現(xiàn)了被Excel監(jiān)視的單元格及其公。L擊鼠標(biāo)，選自定義”選項(xiàng)，打開自定義對話框。在命“令，標(biāo)簽中，選中類別'按更改聽選內(nèi)容按鈕，在彈出菜單的命名'框中輸入一個(gè)名稱(如常用文檔'。)2.再在類“別嚇面任選一項(xiàng)(如插“入”選項(xiàng))，在右邊命“令”下面任選一項(xiàng)(如超“鏈接”選項(xiàng))，將它拖到新菜單(常用文檔)中，并仿照上面的操作對它進(jìn)行命名(如工“資表”等)，建立第3選中常用文檔”菜單中某個(gè)菜單項(xiàng)(如工“資表等)，右擊鼠標(biāo)，在彈出的快捷菜單中，選分配工“資.X1S等)文件夾，并選中該工作簿文檔。4.以后需要打開常“用文檔”菜單中的某個(gè)工作簿文檔時(shí)，只要展開常用文檔”菜單，單擊其中的提示：盡管我們將超鏈接選項(xiàng)拖^了常用文檔噪單中，但并不影響插入噪單中超降菜單項(xiàng)和常用'工顯示額以紅“色”顯示，大于等于1500元的工資總額以藍(lán)色顯示，低于1(H)元的工資總額以櫬色顯示，其它以黑色顯示，我們可以這樣設(shè)置。項(xiàng)，在后面的方框中輸入數(shù)值已000。憚?chuàng)粼诰庉媽I(yè)表格時(shí)，往往需要輸入一些特殊的專業(yè)符號(hào)，為了方便輸入，我們可以制作一個(gè)屬于自己LT制新宏飾令，打開錄制新宏對話框，輸入宏名如，仙301”并I各宏保存在個(gè)人宏。選中錄制宏'工具欄k的柑對引用“按鈕，然后將需要的特殊符號(hào)輸入到f，在工“具欄標(biāo)簽中，單擊新建按鈕，彈出新建工具欄對話框，輸入名稱——專切換到命令標(biāo)簽中，選中類別'下面的宏飛將命令下面的自定義按鈕頻拖^專業(yè)符號(hào)欄上(有多少個(gè)特殊符號(hào)就拖多少個(gè)按鈕)。4.右擊某個(gè)命名后的按鈕，在隨后彈出的快捷菜單中，選指“定宏”選項(xiàng)，打開指“定宏”對話框，選中相應(yīng)的宏(如ftihaol等)，確定退出。5.關(guān)閉自定義”對話框，以后可以像使用普通工具欄一樣，使用專“業(yè)符號(hào)”工具欄，向單元格中1.打開需要打印的工作表，用鼠標(biāo)在不需要打印的行(或者列)標(biāo)上拖拉，選中它們再右擊鼠標(biāo)，在隨后浮現(xiàn)的快捷菜單中，選隱“藏”選項(xiàng)，將不需要打印的行(或者列)隱藏起來。個(gè)名稱(如上報(bào)表后)，單擊確定按鈕。3.將隱藏的行(或者列)顯示出來，并重復(fù)上述操作，添“加，好其它的打印視面。些部門名稱既的有關(guān)信息不是按拼音順序，也不是按輸入部門排序的序列(如機(jī)關(guān)車隊(duì)一車間，二車間三車間，等)，單擊添“力『和確定按鈕退出。格，執(zhí)行數(shù)據(jù)一^序飾令，打開排序?qū)υ捒?，單擊選“項(xiàng)號(hào)安鈕，彈出按鈕，選中剛才自定義的序列，按兩次確“定*安鈕返回，所有數(shù)據(jù)就1選中需要隱藏內(nèi)容的單元格(區(qū)域)，執(zhí)行格“式一單元格”命令，打開單'元格格式對話框，在號(hào))。打開保護(hù)工作表對話框，設(shè)置好密碼后，確定返回。經(jīng)過這樣的設(shè)置以后，上述單元格中的內(nèi)容再也不顯示出來，就是使用Excel的透明功能也不能讓要輸入英文，有的單元格中要輸入中文，反復(fù)切換輸入法實(shí)在不方域，執(zhí)行數(shù)據(jù)-W效性”命令，打開數(shù)據(jù)有效性'對話框，切換到輸'入。以后當(dāng)選中需要輸入中文的單元格區(qū)域中任意一個(gè)單元格時(shí)，中文輸入法(輸入法列表中的第1個(gè)中文輸入法)自動(dòng)打開，當(dāng)選中其它單元格時(shí)，中文輸入法自動(dòng)關(guān)閉。方框中輸入，0也;可以是其他字符一0用小寫)，在替換為下面的方框中輸入要輸入，便姬字符此時(shí)可以不考慮，便W的大小寫，然后確認(rèn)一Excel不能滿足我們的所有需要。我們可以自定義一個(gè)函數(shù)，來完成一些特定的L執(zhí)行工“具一宏一VsualB^版輯器噪單命令(或者按“A1計(jì)F1快『舞鍵)，打開VisualBasic編輯窗FunctionV(a,b,h)V=h*(a+b)/2EndFunctionL執(zhí)行格“式T工作表一背景''命令，打開工'作表背景對話框，選中需要作為背景的圖片后，按下2.在按住Ctrl鍵的同時(shí)，用鼠標(biāo)在不需要襯圖片的單元格(區(qū)域)中拖拉，同時(shí)選中這些單元格(區(qū)域)。中，選中白，色”。經(jīng)過這樣的設(shè)置以后，留下的單元格下面襯上了圖片，而上述選中的單元格(區(qū)域)下面就沒有襯圖片了(其實(shí)，是圖片被白“色”遮蓋了)。如果我們想將多列的內(nèi)容合并到一列中，不需要利用函數(shù)，一個(gè)小小的連字符就能將它搞定(此處2.再次選中D1單元格，用填“充柄”將上述公式復(fù)制到D列下面的單元格中，B、C、D列的內(nèi)容EF選擇性粘貼命令，打開選“擇性粘貼對話框，選中其中的數(shù)值選項(xiàng)，按下確定按鈕，E列的內(nèi)容(不是公式)即被復(fù)制到F列中。此處假定學(xué)生成績保存在Sheetl工作表的A1至G64單元格區(qū)域中，其中第1行為標(biāo)題,第2行為學(xué)科名稱。DsheetlAINDEXsheetlAGINTROW)/3)+,1)COLUMN(冊州Al用填“充柄”將上述公式復(fù)制到Bl至G1單元格中；然后，再同時(shí)選中A1至G1單GA1至G2單元格區(qū)域(第1位學(xué)生的成績條區(qū)域)，按格式工具欄邊框的邊框列表中，選中所有框線”選項(xiàng)，為選中的區(qū)域添加邊框(如果不需要邊操作)。GAG在用函數(shù)處理數(shù)據(jù)時(shí)，往往不知道使用什么函數(shù)比較合適。Excel的搜“索函數(shù)”功能可以幫你縮小范執(zhí)行插入一函數(shù)命令，打開插入函數(shù)對話框，在搜索函數(shù)下面的方框中輸入要求如計(jì)數(shù)，,燃后單擊轉(zhuǎn)示在單元格內(nèi)的數(shù)據(jù)命令，打開監(jiān)視窗口，，，單擊其中的添加監(jiān)視技操作，在Excel2002以前的版本中，為單元格區(qū)域添加邊框的操作比較麻煩，Excel2002對此功能進(jìn)行了全新長度位數(shù)的單元格中卻填入了一個(gè)兩位數(shù)，或者在該輸入文字的單元格中你卻輸入了數(shù)字的時(shí)候，Excel就能自動(dòng)判斷、即時(shí)分析并彈出警告，那該多好啊!