使用Excel 的高級(jí)功能
條件格式、數(shù)據(jù)有效性、工作表分級(jí)顯示和合并計(jì)算、鏈接和超鏈接、使用語(yǔ)音引擎等，這些功能極大地加強(qiáng)了Excel處理電子表格數(shù)據(jù)的能力，使用戶能夠更輕松地應(yīng)對(duì)自己的工作。同時(shí)，這些功能非常易于使用，讀者無(wú)需花很多時(shí)間就能夠快速掌握它們。
３２．１ 條件格式的設(shè)置
要對(duì)某個(gè)單元格或單元格區(qū)域設(shè)置條件格式，可按照下面的步驟進(jìn)行操作：
步驟１．選定目標(biāo)單元格或單元格區(qū)域。
步驟２．單擊菜單“格式”——“條件格式”，彈出“條件格式”對(duì)話框。
步驟３．在“條件格式”對(duì)話框的“條件下（１）”下拉列表框中選擇一種條件類型（單元格數(shù)值或公式）。
步驟４．設(shè)置具體的條件。步驟３選擇不同，條件設(shè)置也會(huì)有所不同，條件可以是常量、單元格引用或公式。
步驟5.單擊“格式”按鈕，Excel會(huì)彈出應(yīng)用于條件格式的“單元格格式”對(duì)話框，在對(duì)話框可設(shè)置條件為真時(shí)要應(yīng)用的格式。
如果要添加條件格式，在“條件格式”對(duì)話框中，單擊“添加”按鈕，重復(fù)步驟３～步驟５。
步驟５．單擊“確定”按鈕。
當(dāng)用戶添加了多個(gè)條件格式時(shí)，Excel會(huì)按順序進(jìn)行判斷，如果所有條件都不滿足，則不應(yīng)用任何格式。
如果有一個(gè)以上的條件同時(shí)被滿足時(shí)，則只應(yīng)用順序在先的條件所對(duì)應(yīng)的格式。如一個(gè)單元格設(shè)置了３個(gè)條件格式，條件１為大于１００，條件２為大于８０，條件３為大于６０；則輸入數(shù)值大于１１０時(shí)，３個(gè)條件都被滿足，則只應(yīng)用條件１設(shè)置的格式。因此，設(shè)置多條件的條件格式時(shí)，要充分考慮各條件之間的設(shè)置順序。
３２．２ 設(shè)置單元格數(shù)值條件
當(dāng)條件格式的條件類型為單元格數(shù)值時(shí)，只能以單元格的數(shù)據(jù)為基準(zhǔn)與其他數(shù)據(jù)（可以是常量、單元格引用甚至是公式結(jié)果）對(duì)比。功能上有一定的局限性，但使用比較簡(jiǎn)單。設(shè)置單元格數(shù)值條件時(shí)有以下幾個(gè)判斷選項(xiàng)：
１．介于
２．未介于
３．等于
４．不等于
５．大于
６．小于
７．大于或等于
８．小于或等于
如果條件中需要在文字之間進(jìn)行對(duì)比，或在文字與數(shù)字之間對(duì)比，實(shí)際是按照它們的系統(tǒng)編碼值來(lái)進(jìn)行對(duì)比。其順序由小到大分別為數(shù)字、英文字母、漢字。當(dāng)漢字之間進(jìn)行對(duì)比時(shí)，系統(tǒng)默認(rèn)為按照它們的拼音字母大小來(lái)對(duì)比。
３２．２．１ 介于與未介于
介于與未介于用來(lái)判斷單元格的數(shù)值是否在某一區(qū)間內(nèi)，如果條件成立，即單元格的數(shù)值介于或未介于某一區(qū)間內(nèi)，則應(yīng)用條件格式中設(shè)置的格式。
示例３２．１ 標(biāo)識(shí)成績(jī)表格中所有６０～７９分的分?jǐn)?shù)
把學(xué)生成績(jī)?cè)冢叮啊罚狗值膯卧裼没疑玻担サ膯卧竦准y標(biāo)識(shí)出來(lái)。
步驟１．選定單元格區(qū)域B2：D11，單擊菜單“格式”——條件格式。
步驟２．在“條件格式”對(duì)話框的“條件１（１）”下拉列表框中選擇“單元格數(shù)值”，在其右側(cè)的下拉列表框中選擇“介于”，在兩個(gè)文本框中分別輸入數(shù)字６０和７９。
步驟３．單擊“格式”按鈕，以彈出的“單元格格式”對(duì)話框中選擇“圖案”選項(xiàng)卡，選擇顏色為灰色－２５％。
步驟４．單擊“確定”按鈕關(guān)閉“單元格格式”對(duì)話框，再單擊“條件格式”對(duì)話框的“確定”按鈕關(guān)閉“條件格式”對(duì)話框。
示例３２．２ 標(biāo)識(shí)ａ或ｂ開(kāi)頭的英語(yǔ)單詞
把英語(yǔ)單詞表中以 ａ或ｂ開(kāi)頭的單詞用粉紅色底紋標(biāo)識(shí)出來(lái)。
步驟１．選定單元格區(qū)域，單擊菜單欄的“格式”——條件格式。
步驟２．在“條件格式”對(duì)話框的“條件１（１）”下拉列表框中選擇“單元格數(shù)值”，在右側(cè)的下拉列表框中選擇“介于”，然后分別在兩個(gè)文本框中輸入“ａ”和“ｂ中”。
步驟３．單擊“格式”按鈕，在彈出的“單元格格式”對(duì)話框中選擇“圖案”選項(xiàng)卡，選擇顏色為粉紅色。
步驟４．單擊“確定”按鈕關(guān)閉“單元格格式”對(duì)話框，再單擊“條件格式”對(duì)話框的“確定”按鈕關(guān)閉“條件格式”對(duì)話框。
“未介于”與“介于”條件正好相反（除非單元格為錯(cuò)誤值），如例１的學(xué)生成績(jī)表，因開(kāi)家長(zhǎng)會(huì)的需要，要把成績(jī)小于60，與成績(jī)大于等于80的學(xué)生標(biāo)識(shí)出來(lái)，則可設(shè)置為未介于60到79，在此不再贅述。
注意：本例用介于“ａ”到“ｂ中”來(lái)判斷開(kāi)頭字母是否為ａ或ｂ，是因?yàn)槿魏我裕忾_(kāi)頭的單詞都小于字符串“ｂ中”。如“bzz”與“ｂ中”對(duì)比，第1個(gè)字符同為“b”，則對(duì)比第2個(gè)字符“z”和“中”，因?yàn)闈h字大于英文字母，所以“中”大于“z”，因此“ｂ中”大于“bzz”。
３２．２．２ 等于與不等于
等于與不等于用來(lái)判斷單元格的數(shù)值等于或不等于目標(biāo)值，如果條件為真，則應(yīng)用條件格式中設(shè)置的格式。
示例３２．３ 標(biāo)識(shí)指定的行政區(qū)單元格
Ａ列單元格是中國(guó)省份名稱表，在單元格E1輸入省份名稱，要求標(biāo)識(shí)出A列中與E1相同的單元格，并將單元格填充灰色-25%。
步驟１．選定Ａ列，單擊菜單欄的“格式”——條件格式。
步驟２．在“條件格式”對(duì)話框的“條件1（1）”下拉列表框中選擇“單元格數(shù)個(gè)”，在右邊的下拉列表框中選擇“等于”，單擊文本框，再用鼠標(biāo)單擊E1單元格，文本框中自動(dòng)填上“=$E$1“）。
步驟３．單擊“格式”按鈕，在彈出的“單元格格式”對(duì)話框中選擇“圖案”選項(xiàng)卡，選擇顏色為灰色－２５％。
步驟４．單擊“確定”按鈕關(guān)閉“單元格格式”對(duì)話框，再單擊“條件格式”對(duì)話框的“確定”按鈕關(guān)閉“條件格式”對(duì)話框。
“不等于”與“等于”條件相反，當(dāng)“等于”條件為真時(shí)，“不等于”條件就為假；“等于”條件為假時(shí)，“不等于”條件就為真，只有一種情況例外，就是單元格數(shù)據(jù)為錯(cuò)誤值，本書(shū)不再舉例說(shuō)明。
３２．２．３ 大于與小于
大于與小于用來(lái)判斷單元格的數(shù)據(jù)大于或小于某個(gè)數(shù)據(jù)，如果條件為真，則應(yīng)用條件格式中設(shè)置的格式。
示例３２．４ 標(biāo)識(shí)出銷售業(yè)績(jī)大于１００的記錄
步驟１．選定單元格區(qū)域，單擊菜單欄的“格式”——“條件格式”。
步驟２．在“條件格式”對(duì)話框的“條件1（1）”下拉列表框中選擇“單元格數(shù)值”，右側(cè)的下拉列表框中選擇“大于”，文本框中輸入“100”。
步驟３．單擊“格式”按鈕，在彈出的“單元格格式”對(duì)話框中選擇“圖案”選項(xiàng)卡，選擇顏色為灰色－２５％。
步驟４．單擊‘確定’按鈕關(guān)閉“單元格格式”對(duì)話框，再單擊“條件格式”對(duì)話框的“確定”按鈕關(guān)閉“條件格式”對(duì)話框。
大于與小于條件相反，當(dāng)大于條件為真時(shí)，小于條件就為假；大于條件為假時(shí)，小于條件就為真。只有一種情況例外，即單元格數(shù)據(jù)為錯(cuò)誤值。
３２．２．４ 大于或等于
大于或等于用來(lái)判斷單元格的數(shù)據(jù)大于或等于某個(gè)數(shù)據(jù)。
示例３２．５ 分別用兩種顏色標(biāo)識(shí)成績(jī)表格中60~79分和80分以上的分?jǐn)?shù)
步驟１．選定單元格區(qū)域，單擊菜單欄的格式——條件格式。
步驟２．單擊“條件格式”對(duì)話框中的“添加”按鈕，在“條件２（２）”下拉列表框中選擇“單元格數(shù)值”，在右側(cè)的下拉列表框中選擇“大于或等于”，文本框中輸入80。
步驟３．單擊“格式”按鈕，在彈出的“單元格格式”對(duì)話框中選擇“圖案”選項(xiàng)卡，選擇顏色為黃色。
步驟４．單擊“確定”按鈕關(guān)閉“單元格格式”對(duì)話框，再單擊“條件格式”對(duì)話框的“確定”按鈕關(guān)閉“條件格式”對(duì)話框。
３２．２．５ 小于或等于
小于或等于用來(lái)判斷單元格的數(shù)據(jù)小于或等于某個(gè)數(shù)據(jù)，如果條件為真，則應(yīng)用于該格式。該用法與大于或等于相同，本書(shū)不再贅述。
３２．３ 設(shè)置公式條件
當(dāng)條件格式的條件類型為公式時(shí)，可以處理比較復(fù)雜的條件，而且條件既可以與本單元格數(shù)值有關(guān)，也可以與本單元格數(shù)值無(wú)關(guān)，下面舉例說(shuō)明。
示例３２．６ 在成績(jī)青史標(biāo)識(shí)出總分低于８０的行
步驟１．選定單元格區(qū)域，單擊菜單欄的“格式”——“條件格式”。
步驟２．在“條件格式”對(duì)話框匠“條件１（１）”下拉列表框中選擇“公式”，在文本框中輸入“＝$G3<80"。
步驟３．單擊“格式”按鈕，在彈出的“單元格格式”對(duì)話框中選擇“圖案”選項(xiàng)卡，選擇顏色為茶色。
步驟４．單擊“確定”按鈕關(guān)閉“單元格格式”對(duì)話框，再單擊“條件格式”對(duì)話框的“確定”按鈕關(guān)閉“條件格式”對(duì)話框。
在這個(gè)例子中，除了Ｇ列單元格的條件與單元格本身數(shù)值有關(guān)，其他單元格都與本單元格的數(shù)值無(wú)關(guān)，如姓名列，無(wú)論姓甚名誰(shuí)，只要考核成績(jī)小于８０分，就要應(yīng)用條件格式中所設(shè)置的格式。
注意：公式“＝$G3<80"中使用了行相對(duì)列絕對(duì)的混合引用，當(dāng)公式復(fù)制到其他單元格時(shí)，列的位置不改變，都絕對(duì)引用Ｇ列單元格，行位置隨單元格位置的改變而相對(duì)改變，比如A3的條件格式公式“＝$G3<80"復(fù)制到E5，則公式變?yōu)?#8220;＝$G5<80"。
示例３２．７ 自動(dòng)為表格中新增的記錄添加邊框線
是一個(gè)還未設(shè)置邊框的人才信息采集表，內(nèi)容隨時(shí)會(huì)增加，為了打印出來(lái)的表格能夠清晰，必須為數(shù)據(jù)區(qū)域添加邊框，而非數(shù)據(jù)區(qū)域不要邊框。為了能在增加記錄的同時(shí)自動(dòng)為新記錄加上邊框，可以使用條件格式來(lái)解決，操作步驟如下。
步驟１．選定單元格區(qū)域A2：F100（范圍大小可根據(jù)用戶必須使用的行數(shù)多少而調(diào)整），保持活動(dòng)單元格A2，單擊菜單欄的“格式”——“條件格式”。
步驟２．在“條件格式”對(duì)話框中的“條件下（1）”下拉列表框中選擇“公式”，在右側(cè)的文本框中輸入“$A2<>”“”。
步驟３．單擊“格式”按鈕，在彈出的“單元格格式”對(duì)話框中選擇“邊框”選項(xiàng)卡，單擊“預(yù)置”下的“外邊框”。
步驟４．單擊“確定”按鈕關(guān)閉“單元格格式”對(duì)話框，再單擊“條件格式”對(duì)話框的“確定”按鈕關(guān)閉“條件格式”對(duì)話框。
現(xiàn)在，當(dāng)用戶在Ａ列輸入姓名后，Excel會(huì)自動(dòng)為該行表格添加邊框，如果清除A列某一單元格的姓名，Excel也會(huì)自動(dòng)把該行的邊框線清除。
人才信息采集表
姓名
性別
年齡
文化程度
畢業(yè)院校
畢業(yè)時(shí)間
陳國(guó)仲
男
23
本科
廈門大學(xué)
2005年7月
王若水
男
23
本科
西安科技大學(xué)
2005年7月
盧小慧
女
26
碩士
廈門大學(xué)
2006年7月
張山峰
男
24
本科
省金融管理干部學(xué)院
2004年7月
歐陽(yáng)山
男
23
碩士
北京大學(xué)
2005年7月
東門雄
男
23
本科
北京大學(xué)
2005年7月
西門雪
女
27
碩士
清華大學(xué)
2003年7月
何以東
男
25
本科
南開(kāi)大學(xué)
2005年7月
翁愛(ài)花
女
25
本科
南京大學(xué)
2005年7月
許秀如
女
24
本科
集美大學(xué)
2006年7月
施小虹
女
24
本科
江西財(cái)經(jīng)大學(xué)
2006年7月
示例３２．８ 標(biāo)識(shí)數(shù)據(jù)列中重復(fù)的數(shù)據(jù)
是一份倉(cāng)庫(kù)新產(chǎn)品進(jìn)出庫(kù)結(jié)存表，為了使產(chǎn)品編號(hào)不重復(fù)輸入，可以設(shè)置條件格式，使重復(fù)輸入的編號(hào)單元格以粉紅色底紋填充。
期初
本期進(jìn)
本期出
結(jié)存
日期
產(chǎn)品編號(hào)
產(chǎn)品名稱
數(shù)量
單價(jià)
金額
數(shù)量
單價(jià)
金額
數(shù)量
單價(jià)
金額
數(shù)量
單價(jià)
金額
13563
煉乳
10.00
56.00
560.00
0.00
56.00
0.00
10.00
56.00
560.00
14253
牛奶
10.00
11.00
110.00
0.00
11.00
0.00
10.00
11.00
110.00
18021
甜橙
20.00
79.00
1,580.00
0.00
79.00
0.00
20.00
79.00
1,580.00
151013
哈密瓜
20.00
80.00
1,600.00
20.00
80.00
1,600.00
0.00
80.00
0.00
103018
蘋(píng)果粉
20.00
65.00
1,300.00
20.00
65.00
1,300.00
0.00
65.00
0.00
103071
青蘋(píng)果
100.00
66.00
6,600.00
30.00
66.00
1,980.00
70.00
66.00
4,620.00
103551
青蘋(píng)果
20.00
127.00
2,540.00
20.00
127.00
2,540.00
0.00
127.00
0.00
106018
香蕉粉
40.00
128.00
5,120.00
40.00
128.00
5,120.00
0.00
128.00
0.00
123013
巧克力
20.00
70.00
1,400.00
0.00
70.00
0.00
20.00
70.00
1,400.00
107043
豆奶
116.00
75.55
8,764.00
116.00
75.55
8,764.00
0.00
75.55
0.00
108087
五香牛肉
2.00
130.00
260.00
2.00
130.00
260.00
0.00
130.00
0.00
110028
紅茶粉
20.00
68.00
1,360.00
0.00
68.00
0.00
20.00
68.00
1,360.00
121018
雞肉粉末
40.00
69.00
2,760.00
40.00
69.00
2,760.00
0.00
69.00
0.00
123013
巧克力
20.00
71.00
1,420.00
20.00
71.00
1,420.00
0.00
71.00
0.00
129028
椰子粉
20.00
72.00
1,440.00
0.00
72.00
0.00
20.00
72.00
1,440.00
129032
椰子油
40.00
73.00
2,920.00
0.00
73.00
0.00
40.00
73.00
2,920.00
133014
乳化煉奶
20.00
74.00
1,480.00
20.00
74.00
1,480.00
0.00
74.00
0.00
135552
稀奶油
6.00
75.00
450.00
0.00
75.00
0.00
6.00
75.00
450.00
142048
牛奶精粉末
200.00
76.00
15,200.00
0.00
76.00
0.00
200.00
76.00
15,200.00
146043
番石榴
98.00
77.00
7,546.00
98.00
77.00
7,546.00
0.00
77.00
0.00
147011
葡萄
20.00
78.00
1,560.00
20.00
78.00
1,560.00
0.00
78.00
0.00
147063
葡萄
10.00
79.00
790.00
10.00
79.00
790.00
0.00
79.00
0.00
151013
哈密瓜
20.00
131.00
2,620.00
20.00
131.00
2,620.00
0.00
131.00
0.00
151051
哈密瓜
140.00
81.00
11,340.00
40.00
81.00
3,240.00
100.00
81.00
8,100.00
160011
檸檬
40.00
82.00
3,280.00
40.00
82.00
3,280.00
0.00
82.00
0.00
160021
檸檬
118.00
132.00
15,576.00
118.00
132.00
15,576.00
0.00
132.00
0.00
步驟１．選定Ｂ列，單擊菜單欄的“格式”——“條件格式”。
步驟２．在“條件格式”對(duì)話框的“條件１（１）”下拉列表框中選擇“公式”，在文本框中輸入“＝COUNTIF
（B：B，B1）>1”。
步驟３．單擊“格式”按鈕，在彈出的“單元格格式”對(duì)話框中選擇“圖案”選項(xiàng)卡，選擇顏色為粉紅。
步驟４．單擊“確定”按鈕關(guān)閉“單元格格式”對(duì)話框，再單擊“條件格式”對(duì)話框的“確定”按鈕關(guān)閉“條件格式”對(duì)話框。
３２．４定位、復(fù)制和刪除條件格式
條件格式可以被復(fù)制并粘貼到別的單元格區(qū)域，也可以被用戶刪除。
３２．４．１復(fù)制條件格式
包含條件格式的單元格被復(fù)制時(shí)，條件格式會(huì)被一同復(fù)制。如果只需要復(fù)制單元格的條件格式以應(yīng)用到其他單元格區(qū)域而不需要單元格抽內(nèi)容，可利用選擇性粘貼的相關(guān)特性或格式刷來(lái)實(shí)現(xiàn)。
示例３２．９復(fù)制條件格式
如圖所示的產(chǎn)品銷售表，不同倉(cāng)庫(kù)的產(chǎn)品編號(hào)不能重復(fù)，成都百盛的產(chǎn)品編號(hào)已設(shè)置了條件格式來(lái)標(biāo)識(shí)重復(fù)的編號(hào)，現(xiàn)在要把Ｂ列的條件格式復(fù)制到北京當(dāng)代倉(cāng)庫(kù)的產(chǎn)品編號(hào)Ｆ列，有以下兩種方法可以實(shí)現(xiàn)。
倉(cāng)庫(kù)
產(chǎn)品編號(hào)
銷售數(shù)
倉(cāng)庫(kù)
產(chǎn)品編號(hào)
銷售數(shù)
成都百盛
4044.4.16.1
43
北京當(dāng)代
4044.4.16.1
25
成都百盛
8340.4.B8.1
37
北京當(dāng)代
8340.4.B8.1
34
成都百盛
8330.4.18.1
50
北京當(dāng)代
8330.4.18.1
44
成都百盛
3491.4.11.1
50
北京當(dāng)代
4044.4.16.1
21
成都百盛
2120.4.26.1
31
北京當(dāng)代
2120.4.26.1
40
成都百盛
8340.4.B8.1
23
北京當(dāng)代
8600.4.26.1
33
成都百盛
2120.9.26.1
46
北京當(dāng)代
2120.9.26.1
35
成都百盛
2120.3.12.1
37
北京當(dāng)代
2120.3.12.1
44
成都百盛
1120.3.26.4
46
北京當(dāng)代
1120.3.26.4
29
成都百盛
8330.4.11.1
38
北京當(dāng)代
8330.4.11.1
45
成都百盛
1120.9.26.1
46
北京當(dāng)代
1120.9.26.1
23
成都百盛
1120.3.26.1
32
北京當(dāng)代
1120.3.26.1
49
成都百盛
1120.4.13.1
30
北京當(dāng)代
1120.4.13.1
28
成都百盛
1120.4.26.1
40
北京當(dāng)代
1120.4.26.1
40
成都百盛
1120.4.26.4
22
北京當(dāng)代
1120.4.26.4
30
成都百盛
1120.9.12.1
31
北京當(dāng)代
1120.9.12.1
43
成都百盛
8340.4.B1.1
50
北京當(dāng)代
8340.4.B1.1
35
成都百盛
3895.4.11.1
32
北京當(dāng)代
3895.4.11.1
27
成都百盛
3895.4.11.4
50
北京當(dāng)代
3895.4.11.4
46
成都百盛
8360.4.C8.1
41
北京當(dāng)代
8360.4.C8.1
23
成都百盛
1120.4.26.1
48
北京當(dāng)代
1120.9.12.1
40
成都百盛
4554.4.38.1
46
北京當(dāng)代
4554.4.38.1
49
成都百盛
2120.3.26.4
24
北京當(dāng)代
2120.3.26.4
41
選擇性粘貼方法
步驟１．復(fù)制Ｂ列。
步驟２．選定Ｆ列，單擊菜單欄的“編輯”——“選擇性粘貼”，Excel會(huì)自動(dòng)彈出“選擇性粘貼”對(duì)話框。
步驟３．在“選擇性粘貼”對(duì)話框中的“粘貼”選項(xiàng)里選擇“格式”選項(xiàng)按鈕，再單擊“確定”按鈕。
格式刷方法
步驟１．選定Ｂ列。
步驟２．單擊“格式”工具欄上的“格式刷”按鈕，然后選擇要設(shè)置條件格式的單元格區(qū)域Ｆ列，結(jié)果與選擇性粘貼方法一樣。
注意：使用以上兩種方法，不但復(fù)制了條件格式，也復(fù)制了所有的單元格格式。
３２．４．２ 刪除條件格式
如果用戶不再需要使用單元格中的條件格式，可以對(duì)其進(jìn)行刪除。
示例３２．１０
仍以示例３２．９的圖所示為例，清除A1：B6的條件格式的步驟如下。
步驟１．選定Ａ1：B6。
步驟２．單擊菜單“格式”——“條件格式”。
步驟３．單擊“條件格式”對(duì)話框的“刪除”按鈕，彈出“刪除條件格式”對(duì)話框。
步驟４．在“刪除條件格式”對(duì)話框中勾選要?jiǎng)h除的條件復(fù)選框。被勾選的條件格式將疲取消，其余的條件如果已存在，將繼續(xù)保留。
步驟５．單擊“確定”按鈕關(guān)閉“刪除條件格式”對(duì)話框，再單擊“條件格式”對(duì)話框的“確定”按鈕，關(guān)閉“條件格式”對(duì)話框。
注意：按<Delete>鍵來(lái)清除單元格內(nèi)容時(shí)，與利用菜單欄執(zhí)行“編輯”——“清除”——“內(nèi)容”結(jié)果一樣，都只刪除了單元格的內(nèi)容，其格式仍被保留。如果要清除單元格的所有格式（包括邊框、字體、底紋等），可選定單元格，然后在菜單欄執(zhí)行“編輯”——‘清除’——“格式”命令。
３２．４．３ 定位到包含條件格式的單元格
若要選定工作表中含有條件格式的單元格，可以用定位的方法來(lái)選定。
定位包含條件格式的單元格有兩種結(jié)果，一是定位到與活動(dòng)單元格具有相同條件的條件格式單元格；二是定位包含任何條件的條件格式單元格。
若要定位包含任何條件的條件格式單元格，請(qǐng)按以下步驟執(zhí)行。
步驟１．單擊菜單欄的“編輯”——“定位”（也可以按<Ctrl+G>組合鍵或功能鍵<F5>），Excel會(huì)自動(dòng)彈出“定位”對(duì)話框。
步驟２．單擊“定位”對(duì)話框的“定位條件”按鈕，在彈出的“定位條件”對(duì)話框單擊“條件格式”單選按鈕。
步驟３．單擊“確定”按鈕。
若要定位到與活動(dòng)單元格具有相同條件的條件格式單元格，請(qǐng)?jiān)诓襟E２中的“定位條件”對(duì)話框中單擊“條件格式”和“相同’單選按鈕。
３２．５條件格式高級(jí)應(yīng)用示例
充分利用條件格式中的公式，可以設(shè)置各種通常情況下難以實(shí)現(xiàn)的效果，以滿足用戶的更多要求。本節(jié)將詳細(xì)說(shuō)明公式對(duì)本工作表的引用和公式對(duì)其他工作表的引用兩種情況。
３２．５．１公式對(duì)本工作表的引用
示例３２．１１標(biāo)識(shí)最近５天內(nèi)沒(méi)有完成的任務(wù)項(xiàng)目
如圖任務(wù)完成情況表，要求Ａ列日期在今天（假設(shè)今天為２００７年４月２日）之前的５天內(nèi)，并且Ｃ列為“Ｎ”的行，用灰色－２５％字體顯示出來(lái)。
日期 型號(hào) 是否完成
2007年3月15日 TL325PK1 N
2007年3月16日 TL325PK2 Y
2007年3月17日 TL325PK3 N
2007年3月18日 TL325PK4 N
2007年3月19日 TL325PK5 Y
2007年3月20日 TL325PK6 N
2007年3月21日 TL325PK7 Y
2007年3月22日 TL325PK8 N
2007年3月23日 TL325PK9 N
步驟１．選定單元格區(qū)域A2：C10，單擊菜單欄的“格式”——“條件格式”。
步驟２．在“條件格式”對(duì)話框的“條件１（１）”下拉列表框中選擇“公式”，在右側(cè)的文本框中輸入“＝AND（TODAY()$A2,TOD
AY()-$A2<=5,$C2="N")"。
步驟３．在彈出的“單元格格式”對(duì)話框中選擇“字體”選項(xiàng)卡，在顏色下拉框選擇灰色－２５％。
步驟４．單擊“確定”按鈕關(guān)閉“單元格格式”對(duì)話框，再單擊“條件格式”對(duì)話框的“確定”按鈕關(guān)閉“條件格式”對(duì)話框。
示例３２．１２使用不同填充顏色區(qū)別表格中不同部門的記錄
如圖所示的員工名單，部門列中，只要相鄰的部門不同，就給該行填充不同顏色的底紋。
分公司名稱 部門 姓名 性別 職務(wù) 出生日期 參加工作時(shí)間
南京分公司 銷售部 趙一 男 經(jīng)理 1953年12月 1978年12月
南京分公司 銷售部 錢二 女 業(yè)務(wù)員 1942年7月 1965年3月
南京分公司 銷售部 孫三 男 業(yè)務(wù)員 1951年3月 1972年4月
南京分公司 銷售部 李四 女 業(yè)務(wù)員 1956年11月 1978年12月
南京分公司 財(cái)務(wù)部 周五 男 經(jīng)理 1940年8月 1965年3月
南京分公司 財(cái)務(wù)部 吳六 女 會(huì)計(jì) 1948年7月 1972年4月
南京分公司 財(cái)務(wù)部 鄭七 男 會(huì)計(jì) 1955年9月 1978年12月
南京分公司 財(cái)務(wù)部 王大 女 會(huì)計(jì) 1945年5月 1965年3月
北京分公司 銷售部 馮九 男 經(jīng)理 1953年4月 1972年4月
北京分公司 銷售部 陳十 女 業(yè)務(wù)員 1958年4月 1978年12月
北京分公司 銷售部 禇十一 男 業(yè)務(wù)員 1945年2月 1965年3月
北京分公司 銷售部 衛(wèi)十二 女 業(yè)務(wù)員 1953年12月 1972年4月
北京分公司 銷售部 蔣十三 男 業(yè)務(wù)員 1957年4月 1978年12月
北京分公司 財(cái)務(wù)部 沈十四 女 經(jīng)理 1942年8月 1965年3月
北京分公司 財(cái)務(wù)部 韓十五 男 會(huì)計(jì) 1947年10月 1972年4月
北京分公司 財(cái)務(wù)部 楊十六 女 會(huì)計(jì) 1952年12月 1978年12月
北京分公司 財(cái)務(wù)部 朱十七 男 會(huì)計(jì) 1940年10月 1965年3月
北京分公司 財(cái)務(wù)部 秦十八 女 會(huì)計(jì) 1951年12月 1972年4月
北京分公司 財(cái)務(wù)部 尤十九 男 會(huì)計(jì) 1955年10月 1978年12月
步驟１．選定單元格區(qū)域A2：G20，單擊菜單欄的“格式”——“條件格式”。
步驟２．在“條件格式”對(duì)話框的“條件１（１）”下拉列表框中選擇“公式”，在右邊的文本框中輸入“＝MOD(SUM(N($B$1:$b1<>
$b$2:$b2)),2)"。
步驟３．單擊“格式”按鈕，在彈出的“單元格格式”對(duì)話框中選擇“圖案”選項(xiàng)卡，選擇顏色為茶色為，再單擊“確定”按鈕。
步驟４．單擊“添加”按鈕，重復(fù)步驟２和步驟３，文本框公式改為“＝MOD(SUM(N($B1:$B1<>$B$2:$B2)),2)=0”，顏色改用淺綠色。
步驟５．單擊“確定”按鈕。
３２．５．２ 公式對(duì)其他工作表的引用
設(shè)置條件格式，如果條件公式要引用其他工作表的數(shù)據(jù)，可以采用兩種方法來(lái)解決，即使用名稱引用和使用函數(shù)引用。
１．使用名稱引用
使用名稱引用是指把要引用的單元格區(qū)域定義為名稱后，在條件格式公式中，以名稱代替引用的單元格地址。
３２．１３ 標(biāo)識(shí)會(huì)計(jì)賬簿輸入錯(cuò)誤的會(huì)計(jì)科目名稱或編號(hào)
如圖是標(biāo)準(zhǔn)的會(huì)計(jì)科目名稱與編號(hào)表
會(huì)計(jì)科目名稱和編號(hào)
順序號(hào) 編號(hào) 名稱
一、資產(chǎn)類
1 1001 現(xiàn)金
2 1002 銀行存款
3 1009 其他貨幣資金
4 1101 短期投資
5 1102 短期投資跌價(jià)準(zhǔn)備
6 1111 應(yīng)收票據(jù)
7 1121 應(yīng)收股息
8 1131 應(yīng)收賬款
9 1133 其他應(yīng)收款
10 1141 壞賬準(zhǔn)備
11 1201 在途物資
12 1211 材料
13 1231 低值易耗品
14 1243 庫(kù)存商品
15 1244 商品進(jìn)銷差價(jià)
16 1251 委托加工物資
17 1261 委托代銷物資
18 1281 存貨跌價(jià)準(zhǔn)備
19 1301 待攤費(fèi)用
20 1401 長(zhǎng)期股權(quán)投資
21 1402 長(zhǎng)期債權(quán)投資
22 1501 固定資產(chǎn)
…… …… ……
是會(huì)計(jì)賬簿中的期末余額，為檢測(cè)賬簿的科目名稱與編號(hào)是否正確并且是一一對(duì)應(yīng)的，可以按以下步驟設(shè)置條件格式。
編號(hào) 名稱 期末余額
1234 應(yīng)收賬款 302,140.00
1402 長(zhǎng)期債權(quán)投資 257,796.00
1201 在途物資 208,243.00
1133 其他應(yīng)收款 98,677.00
1002 銀行存款 372,875.00
1141 壞賬準(zhǔn)備金 176,890.00
1111 應(yīng)收票據(jù) 109,852.00
1501 固定資產(chǎn) 175,075.00
1211 材料 389,223.00
1243 庫(kù)存商品 269,208.00
1009 其他貨幣資產(chǎn) 330,086.00
1121 應(yīng)收股息 417,527.00
1231 低值易耗品 182,246.00
1001 現(xiàn)金 387,590.00
步驟１．單擊菜單欄的“插入”——名稱——定義（或按Ctrl+F3組合鍵），顯示“定義名稱”對(duì)話框。
步驟２．在“定義名稱”對(duì)話框中“在當(dāng)前工作簿中的名稱”下的文本框中輸入一個(gè)名稱，如“科目”，單擊“引用位置”下的文本框，鼠標(biāo)單擊會(huì)計(jì)科目表的工作表標(biāo)簽，以激活會(huì)計(jì)科目表，選定B4：B26，輸入"&"，再選定C4：C26，Excel會(huì)自動(dòng)在文本框中輸入“=會(huì)計(jì)科目表！$B$4:$B$26&會(huì)計(jì)科目表！C$4:$C$26"，單擊“確定”按鈕，完成名稱的定義。
步驟３．選定會(huì)計(jì)賬簿所在工作表的單元格區(qū)域A2：C15，單擊菜單欄的“格式”——“條件格式”。
步驟４．在“條件格式”對(duì)話框的“條件１（１）”下拉列表框中選擇“公式”，在右邊的文本框中輸入“＝ISNA（MATCH（$A2&$B2,科目，0））“。
步驟５．單擊“格式”按鈕，在彈出的“單元格格式”對(duì)話框中選擇“圖案”選項(xiàng)卡，選擇顏色為茶色。］
步驟６．單擊“確定”按鈕關(guān)閉“單元格格式”對(duì)話框，再單擊“條件格式”對(duì)話框的“確定”按鈕關(guān)閉“條件格式”對(duì)話框。
從圖中可以看出Ａ２的編號(hào)錯(cuò)誤，會(huì)計(jì)科目表中并沒(méi)有編號(hào)為1234的科目；B7把編號(hào)為1141的“壞賬準(zhǔn)備”寫(xiě)成了“壞賬準(zhǔn)備”；B12把編號(hào)為1009的“其他貨幣資金”寫(xiě)成了“其他貨幣資產(chǎn)”，因此，這3行被填充了茶色底紋。
２．使用函數(shù)引用
使用函數(shù)引用，可以用INDRECT函數(shù)，把以文本串表示的單元格地址轉(zhuǎn)換為單元格引用。就是在工作表名后加上一個(gè)英文感嘆號(hào)，再加上單元格地址，如引用Sheet1表的A列，寫(xiě)成INDIRECT（"Sheet1!A:A");A引用Sheet2表的B3：E10，寫(xiě)成INDIRECT（“Sheet2！B3：E10”）；引用Sheet3表的第3行到第5行，寫(xiě)成INDIRECT（“Sheet3！3：5”）。
示例３２．１４ 標(biāo)識(shí)會(huì)計(jì)賬簿中輸入錯(cuò)誤的會(huì)計(jì)科目名稱或編號(hào)
示例３２．１３的圖的例子為例，來(lái)說(shuō)明相同條件下如何使用INDIRECT函數(shù)來(lái)達(dá)到與定義名稱相同的效果。
步驟１．選定會(huì)計(jì)賬簿所在工作表的單元格區(qū)域Ａ２：Ｃ１５，單擊菜單欄的“格式”——“條件格式”。
步驟２．在“條件格式”對(duì)話框的“條件１（１）”下拉列表框中選擇“公式”，在右邊的文本框中輸入“＝ISNA（MATCH（$A2&$B2
,INDIRECT("會(huì)計(jì)科目表！B4：B26)&INDIRECT("會(huì)計(jì)科目表！C4：C26"),0))"。
步驟３．單擊“格式”按鈕，在彈出的“單元格格式”對(duì)話框中選擇“圖案”選項(xiàng)卡，選擇顏色為茶色。
步驟４．單擊“確定”按鈕關(guān)閉“單元格格式”對(duì)話框，再單擊“條件格式”對(duì)話框的“確定”按鈕關(guān)閉“條件格式”對(duì)話框。
３２．６條件格式與單元格格式的優(yōu)先順序
假如一個(gè)單元格里既設(shè)置了條件格式，又設(shè)置了自定義數(shù)字格式，還設(shè)置了字體格式，則它們的優(yōu)先顯示順序依次為條件格式——自定義數(shù)字格式——單元格格式，下面舉例說(shuō)明。
示例３２．１５ 條件格式與自定義數(shù)字格式同時(shí)使用
在Ａ１：Ａ４分別輸入50、70、90、文本，把單元格字體設(shè)置為灰色-50%。]
選定Ａ１：Ａ４單元格，單擊菜單欄的“格式”——“單元格”（或按Ctrl+1組合鍵），在“單元格格式”對(duì)話框中單擊“數(shù)字”選項(xiàng)卡，在“分類”框中選擇“自定義”，“類型”框中輸入“[藍(lán)色][>60]0:G/通用格式；@”（不含外面雙引號(hào)，意思是大于60的數(shù)字字體顏色為藍(lán)色），單擊“確定”按鈕。
結(jié)果大于６０的數(shù)字７０、９０字體顏色為藍(lán)色，其他字體顏色為灰色－５０％，說(shuō)明自定義數(shù)字格式優(yōu)先于單元格格式。
再選定Ａ１：Ａ４單元格，單擊菜單欄的“格式”——“條件格式”。在“條件格式”對(duì)話框的“條件１（１）”下拉列表框中選擇“單元格數(shù)值”，在右邊的下拉列表框中選擇“大于”，在右邊文本框中輸入值８０，單擊“格式”按鈕。在彈出的“單元格格式”對(duì)話框中選擇“字體”選項(xiàng)卡，在“顏色”下拉框中選擇灰色－２５％，單擊“確定”按鈕關(guān)閉條件格式對(duì)話框。再單擊“確定”按鈕關(guān)閉“條件格式’對(duì)話框。
數(shù)字９０既符合自定義的數(shù)字格式，又符合條件格式的條件，但因條件格式優(yōu)先于自定義數(shù)字格式，所以顯示為灰色－２５％。另外，文本顯示為灰色－２５％是因?yàn)樗蠗l件格式的條件，文本大于任何數(shù)字。
３２．７條件格式的限制
條件格式與其他功能一樣，都有一定的限制，如果不了解具體有哪些限制，會(huì)使用戶陷入迷途。
３２．７．１ 對(duì)引用的限制
Excel條件格式中的引用，只能是對(duì)本工作表單元格區(qū)域的引用，不能直接引用其他工作簿或工作表單元格區(qū)域。如果一定要引用本工作表以外的區(qū)域，必須用定義名稱或使用INDIRECT函數(shù)來(lái)引用。
條件格式也不能使用聯(lián)合、交叉引用或數(shù)組常量，如Ｂ列是學(xué)生成績(jī)，要把成績(jī)?cè)冢叮啊福胺郑ú缓福胺郑┲g的成績(jī)用藍(lán)色字體顯示，條件格式公式不能寫(xiě)成“＝SUM(B1>={60;80})*{1;-1}0"，因?yàn)檫@里使用了數(shù)組常量（{60；80}、{1；-1}），可以用AND來(lái)判斷，公式為
“=AND(B1>=60;B1<80)”。
３２．７．２ 對(duì)條件個(gè)數(shù)的限制
Excel的條件格式中，可用的條件最多只能有3條。
如果要應(yīng)用超過(guò)了３條的條件格式，可以借助ＶＢＡ編寫(xiě)相應(yīng)的程序來(lái)解決。
３２．７．３ 對(duì)格式的限制
在條件格式中能夠設(shè)置的單元格格式，并非Excel單元格格式的全部。
在條件格式中的“單元格格式”對(duì)話框的“字體”選項(xiàng)卡中“字體”、“字號(hào)”、“上標(biāo)”、“下標(biāo)”為灰色，說(shuō)明這些選項(xiàng)是不適用于條件格式的。
如果讀者單擊“邊框”選項(xiàng)卡，還可以看到，在“邊框”選項(xiàng)卡里，可應(yīng)用的“線條樣式”也只有６種。
此外，條件格式中的“單元格格式”對(duì)話框中包含一個(gè)“清除”按鈕，可供用戶快速刪除現(xiàn)有的格式以便重新設(shè)置。
３２．７．４ 其他限制
使用條件格式功能所得到的單元格格式，無(wú)法利用Excel的查找與替換功能來(lái)定位，這個(gè)特性與普通單元格格式是完全不同的。
第三章 數(shù)據(jù)有效性
使用Excel的數(shù)據(jù)有效性功能，可以對(duì)輸入單元格的數(shù)據(jù)進(jìn)行必要的限制，并根據(jù)用戶的設(shè)置，禁止數(shù)據(jù)輸入或讓用戶選擇是否繼續(xù)輸入數(shù)據(jù)。
例如，可限制一份名單表格中的性別列中只能輸入“男”或“女”，而身份證號(hào)碼列中只能輸入１５位或１８位數(shù)字或字母等。
３３．１ 數(shù)據(jù)有效性的設(shè)置
要對(duì)某個(gè)單元格或單元格區(qū)域設(shè)置數(shù)據(jù)有效性，可按照下面的步驟進(jìn)行操作。
步驟１．選定目標(biāo)單元格或單元格區(qū)域。
步驟２．單擊菜單“數(shù)據(jù)”——“有效性”，Excel會(huì)自動(dòng)彈出“數(shù)據(jù)有效性”對(duì)話框。
步驟３．單擊“設(shè)置”選項(xiàng)卡，在“允許”下拉列表框中，選擇一個(gè)選項(xiàng)，如果要輸入公式，請(qǐng)選擇“自定義”選項(xiàng)。
步驟４．在“數(shù)據(jù)”下拉列表框選擇設(shè)定條件，根據(jù)步驟３的選擇不同，用戶可以訪問(wèn)的控件也會(huì)有所不同。
步驟５．單擊“確定”按鈕。
通過(guò)上述步驟后，選定的單元格區(qū)域就設(shè)置了用戶指定的有效性條件。
數(shù)據(jù)有效性其他選項(xiàng)的功能如下。
“輸入信息”選項(xiàng)卡
用戶選定單元格時(shí)出現(xiàn)信息框，類似批注的功能，前提是勾選“選定單元格時(shí)顯示輸入信息”單選按鈕（默認(rèn)為勾選），但如果“標(biāo)題”和“輸入信息”不輸入內(nèi)容，則不出現(xiàn)消息框（默認(rèn)為空），可作為另類批注使用。
例如，選定Ｂ２單元格，單擊菜單“數(shù)據(jù)”——“有效性”，單擊“輸入信息”選項(xiàng)卡，輸入信息。當(dāng)用戶選定Ｂ２單元格時(shí)，無(wú)論在“設(shè)置”選項(xiàng)卡里設(shè)置什么條件，Excel都會(huì)出現(xiàn)一個(gè)提示信息框。
“出錯(cuò)警告”選項(xiàng)卡
當(dāng)用戶輸入不符合數(shù)據(jù)有效性條件的數(shù)據(jù)（無(wú)效數(shù)據(jù)）時(shí)，Excel顯示的對(duì)話框內(nèi)容以及用戶是否可以繼續(xù)輸入數(shù)據(jù)的設(shè)置，前提是勾選“輸入無(wú)效數(shù)據(jù)時(shí)顯示出錯(cuò)警告”單選按鈕（默認(rèn)為勾選），樣式默認(rèn)為“停止”，“標(biāo)題”和“錯(cuò)誤信息”默認(rèn)為空。
用戶設(shè)置了“出錯(cuò)警告”樣式為“警告”，標(biāo)題為“警告”，錯(cuò)誤信息為“性別只能輸入“男”或“女”。
當(dāng)用戶輸入不符合數(shù)據(jù)有效性條件的數(shù)據(jù)時(shí)，Excel會(huì)自動(dòng)顯示對(duì)話框。
“輸入法模式”選項(xiàng)卡
該選項(xiàng)卡“模式”下拉列表框中有３個(gè)選項(xiàng)，分別為‘隨意’、‘打開(kāi)’和“關(guān)閉（英文模式）”。默認(rèn)為‘隨意’，當(dāng)用戶把輸入法模式設(shè)置為“打開(kāi)”時(shí)，選定該單元格，系統(tǒng)自動(dòng)打開(kāi)中文輸入法，任務(wù)欄中的“語(yǔ)言欄”將顯示“中文（中國(guó)）”圖標(biāo)；設(shè)置為“關(guān)閉（英文模式）”時(shí)，系統(tǒng)自動(dòng)關(guān)閉中文輸入法，任務(wù)欄中的“語(yǔ)言欄”將顯示“英語(yǔ)（美國(guó)）”圖標(biāo)。
選定Ａ列，單擊菜單“數(shù)據(jù)”——“有效性”，單擊“輸入法模式”選項(xiàng)卡，在“模式”下拉列表框選擇“打開(kāi)”，單擊“確定”按鈕，用同樣的方法，把Ｂ列的輸入法模式設(shè)置為“關(guān)閉”（英文模式）“。
如果用戶打開(kāi)了某種中文輸入法，則選定Ａ列的單元格將激活該輸入法；選定Ｂ列的單元格將屏蔽輸入法，顯示“英語(yǔ)（美國(guó)）”圖標(biāo)。當(dāng)用戶需要限制某單元格區(qū)域不能輸入中文時(shí)，可以使用此方法，避免重復(fù)切換中英文輸入法。
３３．２ 數(shù)據(jù)有效性允許的條件
數(shù)據(jù)有效性允許設(shè)置的條件有以下幾種：
１）任何值 ?。玻┱麛?shù) ?。常┬?shù) ?。矗┬蛄小 。担┤掌凇。叮r(shí)間?。罚┪谋鹃L(zhǎng)度 ８）自定義
３３．２．１ 任何值
選擇這個(gè)選項(xiàng)，用戶可以在單元格中輸入任何數(shù)據(jù)而不受影響，但其他選項(xiàng)卡的設(shè)置依舊保留，如果要把所有選項(xiàng)卡的設(shè)置都清除，請(qǐng)單擊“全部清除”按鈕。
３３．２．２ 整數(shù)
限制單元格只能輸入整數(shù)。
示例３３．１限制單元格只能輸入月份數(shù)
步驟１．選定要設(shè)置數(shù)據(jù)有效性的單元格區(qū)域，單擊菜單“數(shù)據(jù)”——“有效性”。
步驟２．單擊“設(shè)置”選項(xiàng)卡，在“允許”下拉列表框選擇“整數(shù)”，在“數(shù)據(jù)”下拉列表框選擇“介于”，在“最小值”折疊框中輸入１，在“最大值”折疊框中輸入１２。
步驟３．如果想選定單元格時(shí)出現(xiàn)提示框，單擊“輸入信息”選項(xiàng)卡，在“輸入信息”列表框中輸入想顯示的文字，如“請(qǐng)輸入月份數(shù)”，并確保“選定單元格時(shí)顯示輸入信息”已勾選。
步驟４．單擊“確定”按鈕。
通過(guò)設(shè)置后，選定的單元格區(qū)域就只能輸入１～１２的月份數(shù)。
３３．２．３ 小數(shù)
限制單元格只能輸入小數(shù)（包括整數(shù)）。
示例３３．２ 限制單元格只能輸入占比比例
如圖所示，是某市各金融機(jī)構(gòu)存款總額的市場(chǎng)份額占比，用戶可以限制占比列只能輸入一個(gè)大于或等于０且小于或等于１的小數(shù)（０％～１００％）。
××市各金融機(jī)構(gòu)存款總額市場(chǎng)份額占比
單位：億元
金融機(jī)構(gòu)名稱 存款總額 占比
中國(guó)工商銀行 126
中國(guó)農(nóng)業(yè)銀行 162
中國(guó)銀行 163
中國(guó)建設(shè)銀行 191
交通銀行 96
中信實(shí)業(yè)銀行 71
光大銀行 109
華廈銀行 68
中國(guó)民生銀行 145
招商銀行 92
中國(guó)農(nóng)業(yè)發(fā)展銀行 189
興業(yè)銀行 70
農(nóng)村信用合作聯(lián)社 121
渣打銀行 175
中國(guó)商業(yè)銀行 164
其他 175
步驟１．選定單元格區(qū)域Ｃ４：Ｃ１９，單擊菜單“數(shù)據(jù)”——“有效性”。
步驟２．單擊“設(shè)置”選項(xiàng)卡，在“允許”下拉列表框選擇“小數(shù)”，在“數(shù)據(jù)”下拉列表框選擇“介于”，在“最小值”折疊框中輸入０，在“最大值”折疊框中輸入１。
步驟３．單擊“確定”按鈕。
３３．２．４序列
用戶輸入或選擇的數(shù)據(jù)，必須是用戶在“來(lái)源”折疊框中輸入的引用或數(shù)據(jù)中已存在的數(shù)據(jù)。
當(dāng)用戶選擇“序列”條件，“來(lái)源”折疊框中可以直接輸入數(shù)據(jù)，也可以使用單元格引用或公式。
如果用戶選擇該條件，且勾選“提供下拉箭頭”時(shí)，當(dāng)用戶選定單元格時(shí)，在單元格右側(cè)會(huì)自動(dòng)出現(xiàn)一個(gè)下拉箭頭；單擊下拉箭頭，用戶可選擇其中的項(xiàng)填入單元格，而不必手動(dòng)輸入數(shù)據(jù)，非常方便，深得廣大用戶的喜愛(ài)。
１．直接輸入數(shù)據(jù)
直接在“來(lái)源”折疊框中輸入允許單元格輸入的所有數(shù)據(jù)。
示例３３．３ 限制單元格只能輸入性別
如圖員工花名冊(cè)所示，用戶可以在性別列，利用數(shù)據(jù)有效性來(lái)制作下拉箭頭，用以選擇“男”或“女”，具體操作如下：
姓名 單位 崗位職務(wù) 專業(yè)技術(shù)職稱 性別 民族 籍貫 出生年月 文化程度 政治面貌 參加工作時(shí)間 備注
王先 思明社 主任 助理會(huì)計(jì)師 漢 廈門 1965.05 中專 1982.12 福建銀行學(xué)校,農(nóng)金
劉遠(yuǎn) 思明社 副主任 助理經(jīng)濟(jì)師 漢 邵武 1974.04 本科 中共黨員 1996.07 2004.07集美大學(xué),會(huì)計(jì)
趙小 思明社 副主任 高級(jí)經(jīng)濟(jì)師 漢 廈門 1966.02 本科 中共黨員 1987.08
張自中 思明社 副主任 經(jīng)濟(jì)師 漢 四川 1972.10 本科 中共黨員 1995.07 1995.07于廈門大學(xué)計(jì)算機(jī)系（系統(tǒng)工程）
馬曉平 思明社綜合科 副科長(zhǎng) 經(jīng)濟(jì)師 漢 泉州 1973.03 大專 中共黨員 1992.09 2004.07集美大學(xué),經(jīng)濟(jì)貿(mào)易
李麗 思明社綜合科 文秘宣傳崗 助理政工師 漢 南平 1969.04 本科 1994.07 1994.07于廈門大學(xué)(音樂(lè)教育)
盧永輝 思明社綜合科 財(cái)務(wù)后勤崗 會(huì)計(jì)師 漢 廈門 1960.09 中專 1978.12 省農(nóng)行干校金融專業(yè)91.12
吳勇 思明社綜合科 會(huì)計(jì) 助理會(huì)計(jì)師 漢 廈門 1957.03 初中 中共黨員 1972.12
滿迪 思明社業(yè)務(wù)科 副科長(zhǎng) 經(jīng)濟(jì)員 漢 廈門 1975.12 大專 1997.11 2003.12廈大,法律
張國(guó)中 思明社業(yè)務(wù)科 信貸審查崗 經(jīng)濟(jì)師 漢 仙游 1974.11 本科 1996.08 2004.07廈大.金融
李小環(huán) 思明社業(yè)務(wù)科 信貸統(tǒng)計(jì)崗 會(huì)計(jì)師 漢 廈門 1979.05 本科 中共黨員 1998.08 2004.07于廈門大學(xué)成人教育學(xué)院(金融)
陳中 思明社業(yè)務(wù)科 信貸審查崗 經(jīng)濟(jì)師 漢 廈門 1976.01 本科 1994.09 2004.07于廈門大學(xué)網(wǎng)絡(luò)學(xué)院(金融學(xué))
王浩 思明社 票據(jù)交換 漢 莆田 1967.12 高中 1982.10
李婷婷 思明社 票據(jù)交換 漢 莆田 1971.04 高中 1987.11
孫小妹 思明社 票據(jù)交換 漢 永春 1949.11 初中 中共黨員 1969.03
趙四 營(yíng)業(yè)部 營(yíng)業(yè)部主任 經(jīng)濟(jì)師 漢 廈門 1961.07 大專 1981.07 1988.07于武漢農(nóng)行干部管理學(xué)校(經(jīng)濟(jì)信息)
張國(guó)鋒 營(yíng)業(yè)部 坐班主任 經(jīng)濟(jì)師 漢 廈門 1970.01 大專 中共黨員 1988.10 1998.07于中央黨校廈門函授學(xué)院(經(jīng)濟(jì)管理)
錢國(guó)良 營(yíng)業(yè)部 柜員 助理會(huì)計(jì)師 漢 廈門 1969.04 大專 1986.06 2004.09福州師范大學(xué),行政管理
林卿 營(yíng)業(yè)部 柜員 助理經(jīng)濟(jì)師 漢 閩清 1974.09 本科 1996.09 2003.12于中央黨校廈門函授學(xué)院
周健 營(yíng)業(yè)部 柜員 會(huì)計(jì)員 漢 長(zhǎng)樂(lè) 1974.02 中專 中共黨員 1992.08 福州財(cái)政金融學(xué)校國(guó)際金融專業(yè)
鄭振興 營(yíng)業(yè)部 信貸員 助理經(jīng)濟(jì)師 漢 廈門 1971.01 本科 1992.09 2005.07于湖南大學(xué)網(wǎng)絡(luò)學(xué)院（金融學(xué)）
高成立 營(yíng)業(yè)部 柜員 會(huì)計(jì)員 漢 寧德 1971.12 大專 1992.09 1999.07于廈門大學(xué)成人教育學(xué)院(財(cái)務(wù)會(huì)計(jì))
孫玉梅 營(yíng)業(yè)部 信貸員 經(jīng)濟(jì)師 漢 廈門 1968.11 大專 1991.01 1995.07于廈門大學(xué)成人教育學(xué)院（金融學(xué)）
鄒小川 營(yíng)業(yè)部 信貸員 經(jīng)濟(jì)員 漢 廈門 1968.11 中專 中共黨員 1985.01 2001.07省農(nóng)行干校,農(nóng)金
王國(guó)成 營(yíng)業(yè)部 信貸員 經(jīng)濟(jì)師 漢 江蘇 1971.02 本科 1991.07 2005.01廈大工商管理
李小慧 營(yíng)業(yè)部 柜員 經(jīng)濟(jì)員 漢 永安 1972.10 大專 1993.07 天津輕工學(xué)院
孫玉枝 營(yíng)業(yè)部 柜員 漢 廈門 1982.08 本科 2006.04
步驟１．選定單元格Ｅ２：Ｅ２８，單擊菜單“數(shù)據(jù)”——‘有效性’。
步驟２．單擊“設(shè)置”選項(xiàng)卡，在“允許”下拉列表框選擇“序列”，在“來(lái)源”折疊框中輸入“男，女”，并確保已勾選“提供下拉箭頭”復(fù)選框（默認(rèn)為勾選）。
步驟３．單擊“確定”按鈕。
當(dāng)用戶選定E2：E28中的單元格時(shí)，在單元格右側(cè)會(huì)出現(xiàn)一個(gè)下拉箭頭按鈕，單擊下拉箭頭按鈕（或按Alt+方向下組合鍵），出現(xiàn)下拉列表，選擇一個(gè)選項(xiàng)，完成數(shù)據(jù)輸入。
注意：“來(lái)源”中的每項(xiàng)內(nèi)容之間必須用英文半逗號(hào)隔開(kāi)，文本前后不要加引號(hào)。
深入了解 下拉箭頭不出現(xiàn)的解決方法
如果用戶在“設(shè)置”選項(xiàng)卡選擇條件為“序列”，并且勾選了“提供下拉箭頭”復(fù)選框，而選定單元格時(shí)，卻沒(méi)有出現(xiàn)下拉箭頭按鈕，請(qǐng)單擊菜單“工具”——“選項(xiàng)”，再單擊“視圖”選項(xiàng)卡，單擊“對(duì)象”中的“全部顯示”單選按鈕，再單擊“確定’按鈕。
２．使用單元格引用
在“來(lái)源”折疊框中輸入引用的單元格地址。
仍以員工花名冊(cè)為例，在“文化程度”列設(shè)置數(shù)據(jù)有效性。
步驟１．在一個(gè)空列（如Ｍ列）輸入各種文化程度。
崗位職務(wù) 專業(yè)技術(shù)職稱 性別 民族 籍貫 出生年月 文化程度 政治面貌 參加工作時(shí)間 備注 文化程度
主任 助理會(huì)計(jì)師 漢 廈門 1965.05 中專 1982.12 福建銀行學(xué)校,農(nóng)金
副主任 助理經(jīng)濟(jì)師 漢 邵武 1974.04 本科 中共黨員 1996.07 2004.07集美大學(xué),會(huì)計(jì) 高中
副主任 高級(jí)經(jīng)濟(jì)師 漢 廈門 1966.02 本科 中共黨員 1987.08 中專
副主任 經(jīng)濟(jì)師 漢 四川 1972.10 本科 中共黨員 1995.07 1995.07于廈門大學(xué)計(jì)算機(jī)系（系統(tǒng)工程） 大專
副科長(zhǎng) 經(jīng)濟(jì)師 漢 泉州 1973.03 大專 中共黨員 1992.09 2004.07集美大學(xué),經(jīng)濟(jì)貿(mào)易 本科
文秘宣傳崗 助理政工師 漢 南平 1969.04 本科 1994.07 1994.07于廈門大學(xué)(音樂(lè)教育) 碩士研究生
財(cái)務(wù)后勤崗 會(huì)計(jì)師 漢 廈門 1960.09 中專 1978.12 省農(nóng)行干校金融專業(yè)91.12 博士研究生
會(huì)計(jì) 助理會(huì)計(jì)師 漢 廈門 1957.03 初中 中共黨員 1972.12
副科長(zhǎng) 經(jīng)濟(jì)員 漢 廈門 1975.12 大專 1997.11 2003.12廈大,法律
信貸審查崗 經(jīng)濟(jì)師 漢 仙游 1974.11 本科 1996.08 2004.07廈大.金融
信貸統(tǒng)計(jì)崗 會(huì)計(jì)師 漢 廈門 1979.05 本科 中共黨員 1998.08 2004.07于廈門大學(xué)成人教育學(xué)院(金融)
信貸審查崗 經(jīng)濟(jì)師 漢 廈門 1976.01 本科 1994.09 2004.07于廈門大學(xué)網(wǎng)絡(luò)學(xué)院(金融學(xué))
票據(jù)交換 漢 莆田 1967.12 高中 1982.10
票據(jù)交換 漢 莆田 1971.04 高中 1987.11
票據(jù)交換 漢 永春 1949.11 初中 中共黨員 1969.03
營(yíng)業(yè)部主任 經(jīng)濟(jì)師 漢 廈門 1961.07 大專 1981.07 1988.07于武漢農(nóng)行干部管理學(xué)校(經(jīng)濟(jì)信息)
坐班主任 經(jīng)濟(jì)師 漢 廈門 1970.01 大專 中共黨員 1988.10 1998.07于中央黨校廈門函授學(xué)院(經(jīng)濟(jì)管理)
柜員 助理會(huì)計(jì)師 漢 廈門 1969.04 大專 1986.06 2004.09福州師范大學(xué),行政管理
柜員 助理經(jīng)濟(jì)師 漢 閩清 1974.09 本科 1996.09 2003.12于中央黨校廈門函授學(xué)院
柜員 會(huì)計(jì)員 漢 長(zhǎng)樂(lè) 1974.02 中專 中共黨員 1992.08 福州財(cái)政金融學(xué)校國(guó)際金融專業(yè)
信貸員 助理經(jīng)濟(jì)師 漢 廈門 1971.01 本科 1992.09 2005.07于湖南大學(xué)網(wǎng)絡(luò)學(xué)院（金融學(xué)）
柜員 會(huì)計(jì)員 漢 寧德 1971.12 大專 1992.09 1999.07于廈門大學(xué)成人教育學(xué)院(財(cái)務(wù)會(huì)計(jì))
信貸員 經(jīng)濟(jì)師 漢 廈門 1968.11 大專 1991.01 1995.07于廈門大學(xué)成人教育學(xué)院（金融學(xué)）
信貸員 經(jīng)濟(jì)員 漢 廈門 1968.11 中專 中共黨員 1985.01 2001.07省農(nóng)行干校,農(nóng)金
信貸員 經(jīng)濟(jì)師 漢 江蘇 1971.02 本科 1991.07 2005.01廈大工商管理
柜員 經(jīng)濟(jì)員 漢 永安 1972.10 大專 1993.07 天津輕工學(xué)院
柜員 漢 廈門 1982.08 本科 2006.04
步驟２．選定單元格區(qū)域Ｉ２：Ｉ２８，單擊菜單“數(shù)據(jù)”——“有效性”。
步驟３．單擊“設(shè)置”選項(xiàng)卡，“允許”下拉列表框選擇“序列”，“來(lái)源”折疊框中輸入“=$M$2:$M$8"),并確保已勾選“提供下拉箭頭”復(fù)選框。
步驟４．單擊“確定”按鈕。
３．使用公式
在“來(lái)源”折疊框中輸入公式。
再以員工花名冊(cè)為例，在“文化程度”列設(shè)置數(shù)據(jù)有效性。
如果用戶為了數(shù)據(jù)表的整潔，不在本工作表Ｍ列輸入各種文化程度，而把各種文化程度輸入到本工作簿另一個(gè)工作表（如Sheet2）的M2：M8，就不能直接使用單元格引用，必須使用公式來(lái)引用，方法與上例基本相同。在步驟3中，折疊框內(nèi)輸入公式"=INDIRECT("Sheet2!M2:M8")"。
３３．２．５日期
限制單元格只能輸入日期。
示例３３．４ 限制單元格中只能輸入當(dāng)年日期
在會(huì)計(jì)賬簿中，每個(gè)會(huì)計(jì)年度的日期列，可以限制只能輸入當(dāng)年日期，假設(shè)當(dāng)年為2007年。
步驟１．選定單元格區(qū)域，單擊菜單“數(shù)據(jù)”——“有效性”。
步驟２．單擊“設(shè)置”選項(xiàng)卡，在“允許”下拉列表框選擇“日期”，在“數(shù)據(jù)”下拉列表框選擇“介于”，在“開(kāi)始日期”折疊框中輸入“2007-1-1”，在“結(jié)束日期”折疊框中輸入“2007-12-31”。
步驟３．單擊“確定”按鈕。
通過(guò)設(shè)置后，單元格區(qū)域就只能接受當(dāng)年的日期，以防止日期輸入錯(cuò)誤。
３３．２．６ 時(shí)間
限制單元格只能輸入時(shí)間。
示例３３．５ 員工上、下班簽到簽退表
如圖所示，是某公司員工上、下班簽到簽退表，該公司上午上班時(shí)間為8：00~12：00，下午上班時(shí)間為2：00~5：00
公司規(guī)定員工必須在規(guī)定的時(shí)間前15分鐘內(nèi)簽到或簽退，如上午簽到時(shí)間在7：45~8：00這段時(shí)間。
2007年3月份員工簽到簽退表
日期
姓名 3月1日 3月2日 3月3日 3月4日 3月5日
上午 下午 上午 下午 上午 下午 上午 下午 上午 下午
張國(guó)中
李小環(huán)
陳中
王浩
李婷婷
孫小妹
趙四
張國(guó)鋒
錢國(guó)良
林卿
周健
鄭振興
高成立
孫玉梅
鄒小川
王國(guó)成
李小慧
孫玉枝
李國(guó)輝
盧梅
許傳國(guó)
黃玉葉
傅建設(shè)
步驟１．選定單元格區(qū)域Ｂ４：Ｂ２６，單擊菜單“數(shù)據(jù)”——“有效性”。
步驟２．單擊“設(shè)置”選項(xiàng)卡，在“允許”下拉列表框選擇“時(shí)間”，在“數(shù)據(jù)”下拉列表框選擇“介于”，在“開(kāi)始時(shí)間”折疊框中輸入“７：４５”，在“結(jié)束時(shí)間”折疊框中輸入“８：００”。
步驟３．單擊“確定”按鈕。
步驟４．用同樣的方法在Ｃ、Ｄ、Ｅ列的數(shù)據(jù)有效性中設(shè)置不同的時(shí)間。
步驟５．把３月１日的有效性復(fù)制到３月２日～３月３１日（關(guān)于數(shù)據(jù)有效性的復(fù)制，請(qǐng)參閱３３．３．２）
３３．２．７ 文本長(zhǎng)度
限制單元格可以輸入的數(shù)據(jù)字符串的長(zhǎng)度。
示例３３．６ 手機(jī)號(hào)碼限制
如圖所示的通訊錄中，為防止手機(jī)號(hào)碼輸入錯(cuò)誤，用戶可以限制單元格只能輸入長(zhǎng)度為１１位的字符串。
姓名 組別 手機(jī)號(hào)碼
張國(guó)中 親戚 13950028689
李小環(huán) 親戚 13906042353
陳中 親戚
王浩 親戚
李婷婷 親戚
孫小妹 同事
趙四 同事
張國(guó)鋒 同事
錢國(guó)良 同事
林卿 同事
周健 同事
鄭振興 朋友
高成立 朋友
孫玉梅 朋友
鄒小川 朋友
王國(guó)成 朋友
李小慧 同學(xué)
孫玉枝 同學(xué)
李國(guó)輝 同學(xué)
盧梅 同學(xué)
許傳國(guó) 同學(xué)
黃玉葉 同學(xué)
傅建設(shè) 同學(xué)
步驟１．選定單元格區(qū)域Ｃ２：Ｃ２４，單擊菜單“數(shù)據(jù)”——“有效性”。
步驟２．單擊“設(shè)置”選項(xiàng)卡，在“允許”下拉列表框選擇“文本長(zhǎng)度”，在“數(shù)據(jù)”下拉列表框中選擇“等于”
在“長(zhǎng)度”折疊框中輸入１１。
步驟３．單擊“確定”按鈕。
當(dāng)用戶輸入的數(shù)據(jù)不是１１位時(shí)，Excel會(huì)自動(dòng)彈出一個(gè)對(duì)話框，警告用戶輸入了錯(cuò)誤的數(shù)據(jù)。
３３．２．８ 自定義
數(shù)據(jù)有效性條件可以用公式來(lái)設(shè)置。以下的例子假設(shè)Ａ１為活動(dòng)單元格，“公式”折疊框中輸入不同的公式，有不同的效果。
１．只能輸入文本
＝ＩＳＴＥＳＴ（Ａ１）
２．只能輸入數(shù)字
＝ＩＳＮＵＭＢＥＲ（Ａ２）
３．輸入以“Ａ”開(kāi)頭的文本
公式１：＝ＬＥＦＴ（Ａ１）＝“Ａ”
公式２：＝ＦＩＮＤ（“Ａ”，Ａ１）＝１
公式１不區(qū)分大小寫(xiě)，如："Abc","abc"均符合條件。
公式２區(qū)分大小寫(xiě)，如：“Ａbc"符合條件， "abc"不符合條件。
４．最后兩個(gè)字為“公司”
＝RIGHT）A1，2）=“公司”
５．第３個(gè)字符為“中”
=COUNTIF（A1，“？？中*”）
或
＝MID（A1，3，1）=“中”
３３．３ 定位、復(fù)制和刪除數(shù)據(jù)有效性
數(shù)據(jù)有效性與條件格式一樣，可以定位到包含數(shù)據(jù)有效性的單元格，也可以把設(shè)置好的數(shù)據(jù)有效性復(fù)制應(yīng)用到其他單元格區(qū)域或刪除已經(jīng)設(shè)置好的數(shù)據(jù)有效性。
３３．３．１ 定位到包含數(shù)據(jù)有效性的單元格
若要選定工作表中含有數(shù)據(jù)有效性的單元格，可以用定位的方法來(lái)選定。
定位包含數(shù)據(jù)有效性的單元格有兩種結(jié)果，一是定位到與活動(dòng)單元格具有相同條件的數(shù)據(jù)有效性單元格；二是定位包含任何條件的數(shù)據(jù)有效性單元格。
若要定位包含任何條件的數(shù)據(jù)有效性單元格，請(qǐng)按以下步驟執(zhí)行。
步驟１．單擊菜單欄的“編輯”——“定位”（或按CTRL+G組合鍵或F5），Excel會(huì)自動(dòng)彈出“定位”對(duì)話框。
步驟２．單擊“定位”對(duì)話框的“定位條件”按鈕，在彈出的“定位條件”對(duì)話框中單擊“數(shù)據(jù)有效性”單選按鈕
步驟３．單擊“確定”按鈕。
若要定位到與活動(dòng)單元格具有相同條件的數(shù)據(jù)有效性單元格，請(qǐng)?jiān)诓襟E２中的“定位條件”對(duì)話框中單擊“數(shù)據(jù)有效性”和“相同”單選按鈕。
注意：相同條件是指數(shù)據(jù)有效性對(duì)話框中４個(gè)選項(xiàng)卡的設(shè)置完全一樣的條件，若有一個(gè)選項(xiàng)卡設(shè)置不一樣，則不視為相同條件的數(shù)據(jù)有效性。
３３．３．２復(fù)制數(shù)據(jù)有效性
包含數(shù)據(jù)有效性的單元格被復(fù)制時(shí)，數(shù)據(jù)有效性會(huì)被一同復(fù)制。如果只需要復(fù)制單元格的數(shù)據(jù)有效性以應(yīng)用到其他單元格區(qū)域而不需要單元格的內(nèi)容和其他格式，可利用選擇性粘貼的相關(guān)特性來(lái)實(shí)現(xiàn)。
仍以員工花名冊(cè)為例，假設(shè)用戶設(shè)置性別列的數(shù)據(jù)有效性時(shí)，只設(shè)置了Ｅ２的數(shù)據(jù)有效性，現(xiàn)在需要把Ｅ２的數(shù)據(jù)有效性應(yīng)用到Ｅ３：Ｅ２８，可使用以下方法。
步驟１．設(shè)置Ｅ２數(shù)據(jù)有效性（先不要選擇性別）。
步驟２．選定Ｅ２，把鼠標(biāo)放到單元格填充柄（單元格右下角的小點(diǎn)）處，當(dāng)光標(biāo)顯示為“＋”符號(hào)時(shí)，按住鼠標(biāo)右鍵下拉到Ｅ２８單元格，再放開(kāi)鼠標(biāo)左鍵。
通過(guò)這樣下拖后，Ｅ２的數(shù)據(jù)有效性就復(fù)制到Ｅ３：Ｅ２８。
如果含有數(shù)據(jù)有效性的單元格與目標(biāo)單元格不在相同的行或列時(shí)，可用選擇性粘貼的方法來(lái)實(shí)現(xiàn)，操作步驟如下：
步驟１．復(fù)制含有數(shù)據(jù)有效性的單元格。
步驟２．選定要應(yīng)用數(shù)據(jù)有效性的單元格區(qū)域，單擊菜單欄的‘編輯’——“選擇性粘貼”，Excel會(huì)自動(dòng)彈出“選擇性粘貼”對(duì)話框。
步驟３．在“選擇性粘貼”對(duì)話框中的‘粘貼’選項(xiàng)里選擇“有效性驗(yàn)證”選項(xiàng)按鈕，再單擊“確定”按鈕。
３３．３．３ 刪除數(shù)據(jù)有效性
如果用戶不再需要使用單元格中的數(shù)據(jù)有效性，可以對(duì)其進(jìn)行刪除，本書(shū)介紹了３種方法來(lái)刪除數(shù)據(jù)有效性，分別是選擇性粘貼、全部清除、重新設(shè)置。
示例３３．７ 刪除數(shù)據(jù)有效性
如圖所示，Ａ１：Ａ１０設(shè)置了數(shù)據(jù)有效性的序列條件，用來(lái)輸入“一月”～“十二月”。
１．選擇性粘貼
步驟１．復(fù)制一個(gè)沒(méi)有設(shè)置數(shù)據(jù)有效性的單元格。
步驟２．選定Ａ１：Ａ１０單元格，單擊菜單欄的“編輯”——‘選擇性粘貼’，Excel會(huì)自動(dòng)彈出‘選擇性粘貼’對(duì)話框。
步驟３．在“選擇性粘貼”對(duì)話框中的“粘貼”選項(xiàng)里選擇“有效性驗(yàn)證”選項(xiàng)按鈕，再單擊“確定”按鈕。
選擇性粘貼后，就刪除了數(shù)據(jù)有效性。
２．全部清除
步驟１．選定Ａ１：Ａ１０單元格，單擊菜單欄的“數(shù)據(jù)”——“有效性”。
步驟２．在“數(shù)據(jù)有效性”對(duì)話框中單擊“清除全部”按鈕。
步驟３．單擊“確定”按鈕。
３．重新設(shè)置
步驟１．選定Ａ１：Ａ１１單元格（把選定區(qū)域擴(kuò)展到至少包含一個(gè)未設(shè)置數(shù)據(jù)有效性的單元格），單擊菜單欄的“數(shù)據(jù)”——“有效性”，Excel會(huì)自動(dòng)彈出一個(gè)對(duì)話框。
步驟２．單擊對(duì)話框“否”按鈕關(guān)閉該對(duì)話框。
步驟３．單擊“數(shù)據(jù)有效性”對(duì)話框的“確定”按鈕。
通過(guò)這樣設(shè)置后，Ａ１：Ａ１０的數(shù)據(jù)有效性就被刪除了。
若選定的單元格含有不止一種的數(shù)據(jù)有效性，Excel將自動(dòng)彈出對(duì)話框，用戶只需單擊“確定”按鈕，再單擊“數(shù)據(jù)有效性”對(duì)話框的“確定”按鈕，就可以清除選定區(qū)域的數(shù)據(jù)有效性。
３３．４數(shù)據(jù)有效性的高級(jí)應(yīng)用示例
利用公式可以設(shè)置更為復(fù)雜的數(shù)據(jù)有效性，如防止用戶重復(fù)輸入數(shù)據(jù)、創(chuàng)建多級(jí)下拉菜單等。
３３．４．１手機(jī)號(hào)碼限制
示例３３．８限制手機(jī)號(hào)碼的輸入
以通訊錄為例，手機(jī)號(hào)碼有１１位數(shù)的手機(jī)號(hào)碼，也有８位數(shù)的小靈通號(hào)碼，限制輸入的數(shù)據(jù)只能是０～９的數(shù)字，不能輸入其他文本，只用“文本長(zhǎng)度”作為條件已不能滿足用戶的需求，必須使用“自定義”條件，用公式來(lái)限制。
步驟１．選定單元格區(qū)域Ｃ２：Ｃ２４，單擊菜單“數(shù)據(jù)”——“有效性”。
步驟２．單擊“設(shè)置”選項(xiàng)卡，在“允許”下拉列表框選擇“自定義”，在“公式”折疊框中輸入“＝AND（ISN
UMBER（C2），OR（LEN（C2）=8，LEN（C2）=11））”。
步驟３．單擊“確定”按鈕。
當(dāng)用戶輸入不符合規(guī)定的數(shù)據(jù)時(shí)，Excel會(huì)自動(dòng)出現(xiàn)對(duì)話框，警告用戶輸入了錯(cuò)誤的數(shù)據(jù)。
３３．４．２ 杜絕重復(fù)輸入數(shù)據(jù)
用戶在輸入某些數(shù)據(jù)時(shí)，如果希望輸入的數(shù)據(jù)不能重復(fù)，可以使用數(shù)據(jù)有效性來(lái)限制。
１．本工作表不能重復(fù)輸入數(shù)據(jù)
示例３３．９限制商品名稱不能重復(fù)輸入
如圖所示的庫(kù)存表，用戶如果不小心把某個(gè)商品名稱重復(fù)輸入，就會(huì)造成結(jié)算錯(cuò)誤。利用Excel的數(shù)據(jù)有效性功能，可以有效地解決這個(gè)問(wèn)題，具體操作如下。
序號(hào) 商品名稱 單位 商品注釋 倉(cāng)庫(kù)大概位置 進(jìn)貨單價(jià) 數(shù)量 金 額
1 凍目魚(yú) 斤 七箱 一一二 ￥221.61 260 ￥57,618.60
2 大黃魚(yú) 箱 一大袋 一一二 ￥14.45 9 ￥130.05
3 大蝦米 斤 一箱 一一二 ￥186.64 7 ￥1,306.48
4 魚(yú)干 箱 七箱 一一二 ￥14.45 6 ￥86.70
5 松板肉 袋 一箱 一一二 ￥186.64 4 ￥746.56
6 海香菇 袋 三箱 最底下 ￥221.61 3 ￥664.83
7 章魚(yú) 袋 -- 一一二 ￥226.21 3 ￥678.63
8 雞脆骨 袋 兩箱 一一二 ￥186.64 3 ￥559.92
9 咸黃魚(yú) 條 兩箱 一一二 ￥186.64 2 ￥373.28
10 魷魚(yú)干 箱 兩箱 一一二 ￥181.22 2 ￥362.44
11 雞冠 袋 一箱 最底下 ￥14.45 1 ￥14.45
12 情人果 袋 一箱 一一二 ￥221.61 1 ￥221.61
13 小蝦米 斤 共五箱 一一二 ￥181.22 1 ￥181.22
14 雪魚(yú) 斤 一箱 一一二 ￥337.92 1 ￥337.92
15 海欣小魚(yú)丸 袋 一箱 一一二 ￥14.45 1 ￥14.45
16 水餃 袋 一箱 一一二 ￥221.61 1 ￥221.61
17 目魚(yú)仔 箱 -- 一一二 ￥186.64 1 ￥186.64
18 凍扇貝 箱 -- 一一二 ￥186.64 1 ￥186.64
步驟１．選定單元格Ｂ２，按住鼠標(biāo)左鍵向下拖拉以選定區(qū)域Ｂ２：Ｂ１９，單擊菜單“數(shù)據(jù)”——“有效性”。
步驟２．單擊“設(shè)置”選項(xiàng)卡，“允許”下拉列表框選擇“自定義”，“公式”折疊框中輸入“＝COUNTIF（B：B，B2）=1”。
步驟３．單擊“確定”按鈕。
通過(guò)上述步驟設(shè)置數(shù)據(jù)有效性后，Ｂ列商品名稱已不能重復(fù)輸入，確保庫(kù)存表商品名稱的唯一性。
２．工作簿內(nèi)所有工作表不能重復(fù)輸入數(shù)據(jù)
示例３３．１０限制輸入在其他工作青史已存在的項(xiàng)目
如圖所示的工作簿，其中每個(gè)工作表的格式都相同，用來(lái)記錄當(dāng)月申請(qǐng)了公積金貸款的員工。公司規(guī)定，每個(gè)員工公積金貸款一年最多只能申請(qǐng)一次。為了執(zhí)行這樣的規(guī)定，每位員工的姓名只允許在整個(gè)工作簿中出現(xiàn)一次。要設(shè)置這樣的數(shù)據(jù)有效性，操作步驟如下。
姓名 申請(qǐng)日期
王先 2007-1-10
劉遠(yuǎn) 2007-1-10
趙小 2007-1-10
張自中 2007-1-10
馬曉平 2007-1-12
李麗 2007-1-12
盧永輝 2007-1-12
吳勇 2007-1-12
滿迪 2007-1-13
張國(guó)中 2007-1-14
李小環(huán) 2007-1-14
陳中 2007-1-14
王浩 2007-1-14
李婷婷 2007-1-14
孫小妹 2007-1-18
趙四 2007-1-18
張國(guó)鋒 2007-1-18
錢國(guó)良 2007-1-18
林卿 2007-1-18
周健 2007-1-19
鄭振興 2007-1-25
高成立 2007-1-25
孫玉梅 2007-1-25
鄒小川 2007-1-25
王國(guó)成 2007-1-25
李小慧 2007-1-25
孫玉枝 2007-1-25
步驟１．單擊菜單“插入”——“名稱”——“定義”（或按CTRL+F3組合鍵）。
步驟２．在“定義名稱”對(duì)話框中，定義３個(gè)名稱，分別為"shtmame","index"和 "shtlist",對(duì)應(yīng)的引用位置依次為"=
REPLACE（GET。WORKBOOK（1），1，F(xiàn)IND{"]",GET.WORKBOOK(1)),)&T(NOW())","GET.DOCUMENT(87)+NOW()*0" "LOOKUP(ROW(INDIRECT("1:"&index)),ROW(INDIRECT("1:"&COUNTA(shtname))),shtname)"
步驟３．這３個(gè)名稱依次取得工作簿中所有工作表名的水平數(shù)組、本工作表在工作簿中的位置和工作簿中第１個(gè)工作表到本工作表之間的所有工作表名的垂直數(shù)組。
步驟４．選定工作表１月的單元格區(qū)域Ａ２：Ａ６５５３６（選定單元格Ａ２，按CTRL+SHIFT+向下鍵組合鍵；或直接在名稱框中輸入A2：A65536，再按Enter鍵），單擊菜單“數(shù)據(jù)”——“有效性”。
注意：本示例定義名稱中，由于名稱公式中相對(duì)引用了行，所以定義名稱前，一定要把光標(biāo)放在第２行。
步驟５．單擊“設(shè)置”選項(xiàng)卡，在“允許”下拉列表框選擇“自定義”，在“公式”折疊框中輸入“＝SUM（CO
UNTIF（INDIRECT（shtlist&"!A:A"),A2))=1"，取消“忽略空值”復(fù)選框的勾選，勾選“提供下拉箭頭”復(fù)選框。
步驟６．單擊“確定”按鈕。
步驟７．重復(fù)步驟４～步驟６，把其他１１個(gè)月工作表的Ａ２：Ａ６５５３６也設(shè)置相同的數(shù)據(jù)有效性，用戶也可以用復(fù)制——選擇性粘貼的方法設(shè)置其他工作表的數(shù)據(jù)有效性。
若工作表尚未輸入其他內(nèi)容，可用填充的方法設(shè)置其他工作表的有效性。方法是：右擊工作表１月的標(biāo)簽，在右鍵快捷菜單中選擇“選擇全部工作表”，點(diǎn)擊菜單“編輯”——“填充”——“至同組工作表”，在彈出的“填充成組工作表”對(duì)話框選擇“全部”單選按鈕，再單擊“確定”按鈕。
通過(guò)上述操作后，已完成了用戶所需的數(shù)據(jù)有效性，當(dāng)用戶在２月工作表Ａ２的單元格輸入１月已有的姓名，如“李婷婷”，Excel會(huì)自動(dòng)彈出警告框，阻止用戶繼續(xù)輸入。
注意：本例中使用函數(shù)GET。WORKBOOK（1）和GET.DOCUMENT（87）是宏表4.0函數(shù)，有關(guān)此函數(shù)的更多內(nèi)容，請(qǐng)參閱第22章。
３３．４．３ 創(chuàng)建多級(jí)下拉列表
已經(jīng)介紹了利用數(shù)據(jù)有效性在工作表中創(chuàng)建下拉列表的功能，但事實(shí)上，用戶可以根據(jù)需要?jiǎng)?chuàng)建多級(jí)聯(lián)動(dòng)的下拉列表，而不僅僅單級(jí)的列表。
１．創(chuàng)建兩級(jí)下拉列表
示例３３．１１ 商品種類及名稱的兩級(jí)聯(lián)動(dòng)選擇
如圖所示，在Ａ～Ｃ列保存了一份商品的種類及商品的具體名稱資料，現(xiàn)在希望能夠?qū)崿F(xiàn)在Ｅ列選擇商品種類，在Ｆ列選擇只屬于相應(yīng)種類中的商品的效果。
海鮮 蔬菜 水果 選擇種類 選擇商品
凍目魚(yú) 香菜 情人果 蔬菜 冬瓜
大黃魚(yú) 芹菜 香蕉
龍蝦 菠菜 水蜜桃
章魚(yú) 苦瓜 無(wú)花果
咸黃魚(yú) 絲瓜 火龍果
紅蝦 角瓜 蘋(píng)果
鱈魚(yú) 南瓜 莢果
目魚(yú)仔 洋蔥 荔枝
扇貝 大蒜 葡萄
石斑魚(yú) 韭菜 櫻桃
烏賊 大白菜 香梨
鱟 空心菜 榴梿
小黃魚(yú) 芥菜
黃鱔 冬瓜
鯽魚(yú)
文昌魚(yú)
武昌魚(yú)
步驟１．選定單元格區(qū)域Ｅ２：Ｅ１８，單擊菜單“數(shù)據(jù)”——“有效性”。
步驟２．單擊“設(shè)置”選項(xiàng)卡，在“允許”下拉列表框選擇“序列”，在“來(lái)源”折疊框中輸入公式“＝$A$1:$C$A"。
步驟３．單擊“確定”按鈕。
步驟４．用同樣的方法設(shè)置Ｆ２：Ｆ１８的數(shù)據(jù)有效性，公式為“＝OFFSET($a$2,,MATCH($E2,$A$1:$C$1,)-1,COUNTA(OFFSET($A$2,,
MATCH($E2,$A$1:$C$1,0)-1,65535)))"。
通過(guò)設(shè)置后，選定Ｅ２：Ｅ１８中的單元格，將出現(xiàn)種類的下拉菜單，選定Ｆ２：Ｆ１８中的單元格，將出現(xiàn)該種類對(duì)應(yīng)商品的下拉菜單。如果Ｅ列沒(méi)有選擇種類，則Ｆ列也無(wú)法選擇。這從另一個(gè)角度上也避免了錯(cuò)誤的產(chǎn)生。
２．創(chuàng)建三級(jí)下拉列表
示例３３．１２ 分級(jí)選擇性錄入居住地
如圖所示，Ａ～Ｃ列是省份名、市（地）名、縣（區(qū)）名的資料，現(xiàn)在希望在F~H列分級(jí)選擇性錄入數(shù)據(jù)。在這個(gè)例子中，需要用輔助列來(lái)幫助設(shè)置數(shù)據(jù)有效性。
省份名 市（地）名 縣（區(qū)）名 姓名 省份名 市（地）名 縣（區(qū)）名
福建 福州 臺(tái)江 小王 福建 福州 臺(tái)江
福建 福州 馬尾 小張 福建 廈門 同安
福建 福州 連江 小趙 浙江 杭州 西湖
福建 廈門 同安
福建 廈門 集美
福建 廈門 杏林
福建 晉江 南安
福建 晉江 惠安
福建 晉江 永春
浙江 杭州 上城
浙江 杭州 下城
浙江 杭州 西湖
浙江 寧波 江東
浙江 寧波 江北
廣東 廣州 天河
廣東 廣州 花都
廣東 廣州 從化
廣東 深圳 南山
廣東 深圳 寶安
