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

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

但是,僅僅同步輸入是遠遠不夠的。比如,我們需要將多張表格中相同位置的數據統一改變格式該怎麽辦呢?首先,我們得改變第一張表格的數據格式,再單擊“編輯”菜單的“填充”選項,然後在其子菜單中選擇“至同組工作表”。這時,Excel會彈出“填充成組工作表”的對話框,在這裏我們選擇“格式”一項,點“確定”後,同組中所有表格該位置的數據格式都改變了。

十九、改變文本的大小寫

在Excel中,爲表格處理和數據運算提供最強大支持的不是公式,也不是數據庫,而是函數。不要以爲Excel中的函數只是針對數字,其實只要是寫進表格中的內容,Excel都有對它編輯的特殊函數。例如改變文本的大小寫。

在Excel 2002中,至少提供了三種有關文本大小寫轉換的函數。它們分別是:“=UPPER(源數據格)”,將文本全部轉換爲大寫;“=LOWER(源數據格)”,將文本全部轉換成小寫;“=PROPER(源數據格)”,將文本轉換成“適當”的大小寫,如讓每個單詞的首字母爲大寫等。例如,我們在一張表格的A1單元格中輸入小寫的“excel”,然後在目標單元格中輸入“=UPPER(A1)”,回車後得到的結果將會是“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″))”。該公式盡管一長串,不過含義卻很明確:①如果數字是以“11”、“12”、“13”結尾的,則加上“th”後綴;②如果第1原則無效,則檢查最後一個數字,以“1”結尾使用“st”、以“2”結尾使用“nd”、以“3”結尾使用“rd”;③如果第1、2原則都無效,那麽就用“th”。因此,基數詞和序數詞的轉換實現得如此輕松和快捷。

二十二、用特殊符號補齊位數

和財務打過交道的人都知道,在賬面填充時有一種約定俗成的“安全填寫法”,那就是將金額中的空位補齊,或者在款項數據的前面加上“$”之類的符號。其實,在Excel中也有類似的輸入方法,那就是“REPT”函數。它的基本格式是“=REPT(“特殊符號”,填充位數)”詳見:http://www.knowsky.com/article.asp?typeid=118

比如,我們要在中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”單元格中輸入某個學生名,比如“趙耀”,回車之下我們會發現,其下方每一科目的單元格中就自動顯示出該生的入學成績了。

二十七、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監視的單元格及其公式了。

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

提示:當包含有指向其他工作簿的單元格被監視時,只有當所有被引用的工作簿都打開時,才能在“監視窗口”的列表中顯示出來。

會計應該學會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設計
 
 
回到王朝網路移動版首頁