畜牧人
標(biāo)題:
Excel鮮為人知的35招秘技(二)
[打印本頁(yè)]
作者:
dwm8080
時(shí)間:
2009-5-28 19:36
標(biāo)題:
Excel鮮為人知的35招秘技(二)
十六、為單元格快速畫邊框
在
Excel 2002
以前的版本中,為單元格區(qū)域添加邊框的操作比較麻煩,
Excel 2002
對(duì)此功能進(jìn)行了全新的拓展。
單擊
“
格式
”
工具欄上
“
邊框
”
右側(cè)的下拉按鈕,在隨后彈出的下拉列表中,選
“
繪圖邊框
”
選項(xiàng),或者執(zhí)行
“
視圖
→
工具欄
→
邊框
”
命令,展開(kāi)
“
邊框
”
工具欄。
單擊工具欄最左側(cè)的下拉按鈕,選中一種邊框樣式,然后在需要添加邊框的單元格區(qū)域中拖拉,即可為相應(yīng)的單元格區(qū)域快速畫上邊框。
提示:
①
如果畫錯(cuò)了邊框,沒(méi)關(guān)系,選中工具欄上的
“
擦除邊框
”
按鈕,然后在錯(cuò)誤的邊框上拖拉一下,就可以清除掉錯(cuò)誤的邊框。
②
如果需要畫出不同顏色的邊框,可以先按工具欄右側(cè)的
“
線條顏色
”
按鈕,在隨后彈出的調(diào)色板中選中需要的顏色后,再畫邊框即可。
③
這一功能還可以在單元格中畫上對(duì)角的斜線。
十七、控制特定單元格輸入文本的長(zhǎng)度
你能想象當(dāng)你在該輸入四位數(shù)的單元格中卻填入了一個(gè)兩位數(shù),或者在該輸入文字的單元格中你卻輸入了數(shù)字的時(shí)候,
Excel
就能自動(dòng)判斷、即時(shí)分析并彈出警告,那該多好啊
!
要實(shí)現(xiàn)這一功能,對(duì)
Excel
來(lái)說(shuō),也并不難。
例如我們將光標(biāo)定位到一個(gè)登記
“
年份
”
的單元格中,為了輸入的統(tǒng)一和計(jì)算的方便,我們希望
“
年份
”
都用一個(gè)四位數(shù)來(lái)表示。所以,我們可以單擊
“
數(shù)據(jù)
”
菜單的
“
有效性
”
選項(xiàng)。在
“
設(shè)置
”
卡片
“
有效性條件
”
的
“
允許
”
下拉菜單中選擇
“
文本長(zhǎng)度
”
。然后在
“
數(shù)據(jù)
”
下拉菜單中選擇
“
等于
”
,且
“
長(zhǎng)度
”
為
“4”
。同時(shí),我們?cè)賮?lái)到
“
出錯(cuò)警告
”
卡片中,將
“
輸入無(wú)效數(shù)據(jù)時(shí)顯示的出錯(cuò)警告
”
設(shè)為
“
停止
”
,并在
“
標(biāo)題
”
和
“
錯(cuò)誤信息
”
欄中分別填入
“
輸入文本非法
!”
和
“
請(qǐng)輸入四位數(shù)年份。
”
字樣。
很顯然,當(dāng)如果有人在該單元格中輸入的不是一個(gè)四位數(shù)時(shí),
Excel
就會(huì)彈出示的警告對(duì)話框,告訴你出錯(cuò)原因,并直到你輸入了正確
“
樣式
”
的數(shù)值后方可繼續(xù)錄入。神奇吧
?
其實(shí),在
Excel
的
“
數(shù)據(jù)有效性
”
判斷中,還有許多特殊類型的數(shù)據(jù)格式可選,比如
“
文本類型
”
啊,
“
序列大小
”
啊,
“
時(shí)間遠(yuǎn)近
”
啊,如你有興趣,何不自作主張,自己設(shè)計(jì)一種檢測(cè)標(biāo)準(zhǔn),讓你的
Excel
展示出與眾不同的光彩呢。
十八、成組填充多張表格的固定單元格
我們知道每次打開(kāi)
Excel
,軟件總是默認(rèn)打開(kāi)多張工作表。由此就可看出
Excel
除了擁有強(qiáng)大的單張表格的處理能力,更適合在多張相互關(guān)聯(lián)的表格中協(xié)調(diào)工作。要協(xié)調(diào)關(guān)聯(lián),當(dāng)然首先就需要同步輸入。因此,在很多情況下,都會(huì)需要同時(shí)在多張表格的相同單元格中輸入同樣的內(nèi)容。
那么如何對(duì)表格進(jìn)行成組編輯呢
?
首先我們單擊第一個(gè)工作表的標(biāo)簽名
“Sheet1”
,然后按住
Shift
鍵,單擊最后一張表格的標(biāo)簽名
“Sheet3”
(如果我們想關(guān)聯(lián)的表格不在一起,可以按住
Ctrl
鍵進(jìn)行點(diǎn)選)。此時(shí),我們看到
Excel
的標(biāo)題欄上的名稱出現(xiàn)了
“
工作組
”
字樣,我們就可以進(jìn)行對(duì)工作組的編輯工作了。在需要一次輸入多張表格內(nèi)容的單元格中隨便寫點(diǎn)什么,我們發(fā)現(xiàn),
“
工作組
”
中所有表格的同一位置都顯示出相應(yīng)內(nèi)容了。
但是,僅僅同步輸入是遠(yuǎn)遠(yuǎn)不夠的。比如,我們需要將多張表格中相同位置的數(shù)據(jù)統(tǒng)一改變格式該怎么辦呢
?
首先,我們得改變第一張表格的數(shù)據(jù)格式,再單擊
“
編輯
”
菜單的
“
填充
”
選項(xiàng),然后在其子菜單中選擇
“
至同組工作表
”
。這時(shí),
Excel
會(huì)彈出
“
填充成組工作表
”
的對(duì)話框,在這里我們選擇
“
格式
”
一項(xiàng),點(diǎn)
“
確定
”
后,同組中所有表格該位置的數(shù)據(jù)格式都改變了。
十九、改變文本的大小寫
在
Excel
中,為表格處理和數(shù)據(jù)運(yùn)算提供最強(qiáng)大支持的不是公式,也不是數(shù)據(jù)庫(kù),而是函數(shù)。不要以為
Excel
中的函數(shù)只是針對(duì)數(shù)字,其實(shí)只要是寫進(jìn)表格中的內(nèi)容,
Excel
都有對(duì)它編輯的特殊函數(shù)。例如改變文本的大小寫。
在
Excel 2002
中,至少提供了三種有關(guān)文本大小寫轉(zhuǎn)換的函數(shù)。它們分別是:
“=UPPER
(源數(shù)據(jù)格)
”
,將文本全部轉(zhuǎn)換為大寫;
“=LOWER
(源數(shù)據(jù)格)
”
,將文本全部轉(zhuǎn)換成小寫;
“=PROPER
(源數(shù)據(jù)格)
”
,將文本轉(zhuǎn)換成
“
適當(dāng)
”
的大小寫,如讓每個(gè)單詞的首字母為大寫等。例如,我們?cè)谝粡埍砀竦?/font>
A1
單元格中輸入小寫的
“excel”
,然后在目標(biāo)單元格中輸入
“=UPPER
(
A1
)
”
,回車后得到的結(jié)果將會(huì)是
“EXCEL”
。同樣,如果我們?cè)?/font>
A3
單元格中輸入
“mr.weiwei”
,然后我們?cè)谀繕?biāo)單元格中輸入
“=PROPER
(
A3
)
”
,那么我們得到的結(jié)果就將是
“Mr.Weiwei”
了。
二十、提取字符串中的特定字符
除了直接輸入外,從已存在的單元格內(nèi)容中提取特定字符輸入,絕對(duì)是一種省時(shí)又省事的方法,特別是對(duì)一些樣式雷同的信息更是如此,比如員工名單、籍貫等信息。
如果我們想快速?gòu)?/font>
A4
單元格中提取稱謂的話,最好使用
“=RIGHT
(源數(shù)據(jù)格,提取的字符數(shù))
”
函數(shù),它表示
“
從
A4
單元格最右側(cè)的字符開(kāi)始提取
2
個(gè)字符
”
輸入到此位置。當(dāng)然,如果你想提取姓名的話,則要使用
“=LEFT
(源數(shù)據(jù)格,提取的字符數(shù))
”
函數(shù)了。還有一種情況,我們不從左右兩端開(kāi)始,而是直接從數(shù)據(jù)中間提取幾個(gè)字符。比如我們要想從
A5
單元格中提取
“
武漢
”
兩個(gè)字時(shí),就只須在目標(biāo)單元格中輸入
“=MID
(
A5,4,2
)
”
就可以了。意思是:在
A5
單元格中提取第
4
個(gè)字符后的兩個(gè)字符,也就是第
4
和第
5
兩個(gè)字。
二十一、把基數(shù)詞轉(zhuǎn)換成序數(shù)詞
將英文的基數(shù)詞轉(zhuǎn)換成序數(shù)詞是一個(gè)比較復(fù)雜的問(wèn)題。因?yàn)樗鼪](méi)有一個(gè)十分固定的模式:大多數(shù)的數(shù)字在變成序數(shù)詞都是使用的
“th”
后綴,但大凡是以
“1”
、
“2”
、
“3”
結(jié)尾的數(shù)字卻分別是以
“st”
、
“nd”
和
“rd”
結(jié)尾的。而且,
“11”
、
“12”
、
“13”
這
3
個(gè)數(shù)字又不一樣,它們卻仍然是以
“th”
結(jié)尾的。因此,實(shí)現(xiàn)起來(lái)似乎很復(fù)雜。其實(shí),只要我們理清思路,找準(zhǔn)函數(shù),只須編寫一個(gè)公式,就可輕松轉(zhuǎn)換了。不信,請(qǐng)看:
“=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″
))
”
。該公式盡管一長(zhǎng)串,不過(guò)含義卻很明確:
①
如果數(shù)字是以
“11”
、
“12”
、
“13”
結(jié)尾的,則加上
“th”
后綴;
②
如果第
1
原則無(wú)效,則檢查最后一個(gè)數(shù)字,以
“1”
結(jié)尾使用
“st”
、以
“2”
結(jié)尾使用
“nd”
、以
“3”
結(jié)尾使用
“rd”
;
③
如果第
1
、
2
原則都無(wú)效,那么就用
“th”
。因此,基數(shù)詞和序數(shù)詞的轉(zhuǎn)換實(shí)現(xiàn)得如此輕松和快捷。
二十二、用特殊符號(hào)補(bǔ)齊位數(shù)
和財(cái)務(wù)打過(guò)交道的人都知道,在賬面填充時(shí)有一種約定俗成的
“
安全填寫法
”
,那就是將金額中的空位補(bǔ)齊,或者在款項(xiàng)數(shù)據(jù)的前面加上
“$”
之類的符號(hào)。其實(shí),在
Excel
中也有類似的輸入方法,那就是
“REPT”
函數(shù)。它的基本格式是
“=REPT
(
“
特殊符號(hào)
”
,填充位數(shù))
”
。
比如,我們要在中
A2
單元格里的數(shù)字結(jié)尾處用
“#”
號(hào)填充至
16
位,就只須將公式改為
“=
(
A2&REPT
(
″#″,16-LEN
(
A2
)))
”
即可;如果我們要將
A3
單元格中的數(shù)字從左側(cè)用
“#”
號(hào)填充至
16
位,就要改為
“=REPT
(
″#″,16-LEN
(
A3
)))
&A3”
;另外,如果我們想用
“#”
號(hào)將
A4
中的數(shù)值從兩側(cè)填充,則需要改為
“=REPT
(
″#″,8-LEN
(
A4
)
/2
)
&A4&REPT
(
″#″
)
8-LEN
(
A4
)
/2
)
”
;如果你還嫌不夠?qū)I(yè),要在
A5
單元格數(shù)字的頂頭加上
“$”
符號(hào)的話,那就改為:
“=
(
TEXT
(
A5,″$#,##0.00″
(
&REPT
(
″#″,16-LEN
(
TEXT
(
A5,″$#,##0.00″
))))
”
,一定能滿足你的要求。
二十三、創(chuàng)建文本直方圖
除了重復(fù)輸入之外,
“REPT”
函數(shù)另一項(xiàng)衍生應(yīng)用就是可以直接在工作表中創(chuàng)建由純文本組成的直方圖。它的原理也很簡(jiǎn)單,就是利用特殊符號(hào)的智能重復(fù),按照指定單元格中的計(jì)算結(jié)果表現(xiàn)出長(zhǎng)短不一的比較效果。
比如我們首先制作一張年度收支平衡表,然后將
“E
列
”
作為直方圖中
“
預(yù)算內(nèi)
”
月份的顯示區(qū),將
“G
列
”
則作為直方圖中
“
超預(yù)算
”
的顯示區(qū)。然后根據(jù)表中已有結(jié)果
“D
列
”
的數(shù)值,用
“Wingdings”
字體的
“N”
字符表現(xiàn)出來(lái)。具體步驟如下:
在
E3
單元格中寫入公式
“=IF
(
D3<0,REPT
(
″n″,-ROUND
(
D3*100,0
))
,″″
)
”
,然后選中它并拖動(dòng)
“
填充柄
”
,使
E
列中所有行都能一一對(duì)應(yīng)
D
列中的結(jié)果;接著在
G3
單元格中寫入公式
“=IF
(
D3>0,REPT
(
″n″,ROUND
(
D3*100,0
))
,″″
)
”
,也拖動(dòng)填充柄至
G14
。我們看到,一個(gè)沒(méi)有動(dòng)用
Excel
圖表功能的純文本直方圖已展現(xiàn)眼前,方便直觀,簡(jiǎn)單明了。
二十四、計(jì)算單元格中的總字?jǐn)?shù)
有時(shí)候,我們可能對(duì)某個(gè)單元格中字符的數(shù)量感興趣,需要計(jì)算單元格中的總字?jǐn)?shù)。要解決這個(gè)問(wèn)題,除了利用到
“SUBSTITUTE”
函數(shù)的虛擬計(jì)算外,還要?jiǎng)佑?/font>
“TRIM”
函數(shù)來(lái)刪除空格。比如現(xiàn)在
A1
單元格中輸入有
“how many words?”
字樣,那么我們就可以用如下的表達(dá)式來(lái)幫忙:
“=IF
(
LEN
(
A1
)
=0,0,LEN
(
TRIM
(
A1
))
-LEN
(
SUBSTITUTE
(
TRIM
(
A1
)
,″,″,″″
))
+1
)
”
該式的含義是先用
“SUBSTITUTE”
函數(shù)創(chuàng)建一個(gè)新字符串,并且利用
“TRIM”
函數(shù)刪除其中字符間的空格,然后計(jì)算此字符串和原字符串的數(shù)位差,從而得出
“
空格
”
的數(shù)量,最后將空格數(shù)
+1
,就得出單元格中字符的數(shù)量了。
二十五、關(guān)于歐元的轉(zhuǎn)換
這是
Excel 2002
中的新工具。如果你在安裝
Excel 2002
時(shí)選擇的是默認(rèn)方式,那么很可能不能在
“
工具
”
菜單中找到它。不過(guò),我們可以先選擇
“
工具
”
菜單中的
“
加載宏
”
,然后在彈出窗口中勾選
“
歐元工具
”
選項(xiàng),
“
確定
”
后
Excel 2002
就會(huì)自行安裝了。
完成后我們?cè)俅未蜷_(kāi)
“
工具
”
菜單,單擊
“
歐元轉(zhuǎn)換
”
,一個(gè)獨(dú)立的專門用于歐元和歐盟成員國(guó)貨幣轉(zhuǎn)換的窗口就出現(xiàn)了。與
Excel
的其他函數(shù)窗口一樣,我們可以通過(guò)鼠標(biāo)設(shè)置貨幣轉(zhuǎn)換的
“
源區(qū)域
”
和
“
目標(biāo)區(qū)域
”
,然后再選擇轉(zhuǎn)換前后的不同幣種即可。所示的就是
“100
歐元
”
分別轉(zhuǎn)換成歐盟成員國(guó)其他貨幣的比價(jià)一覽表。當(dāng)然,為了使歐元的顯示更顯專業(yè),我們還可以點(diǎn)擊
Excel
工具欄上的
“
歐元
”
按鈕,這樣所有轉(zhuǎn)換后的貨幣數(shù)值都是歐元的樣式了。
作者:
jiakang
時(shí)間:
2010-2-3 11:10
heh確實(shí)很多都不知道..收藏了
作者:
zlnl
時(shí)間:
2010-2-3 12:46
都會(huì),不會(huì)的也用不到
作者:
萬(wàn)馬奔騰
時(shí)間:
2010-2-3 13:00
謝謝分享。
歡迎光臨 畜牧人 (http://ffers.com.cn/)
Powered by Discuz! X3.5