中數(shù)據(jù)的有效性的應(yīng)用

1、excel 中數(shù)據(jù)的有效性的應(yīng)用        3、 防止數(shù)據(jù)輸入錯(cuò)誤        典型應(yīng)用如下：        （1） 防止日期錯(cuò)誤：        只準(zhǔn)輸入日期或某個(gè)日期之后的特定日期：點(diǎn)擊EXCEL菜單“數(shù)據(jù)-有效性-”， 在“設(shè)置-允許”對(duì)話(huà)框中選擇“日期”、并在相應(yīng)位置輸入起始日期。  

2、              （2） 只準(zhǔn)輸入整數(shù)：        在“設(shè)置-允許”對(duì)話(huà)框中選擇“整數(shù)”        （3） 防止輸入重復(fù)值：        首先要選定整行/列或單元格區(qū)域（比如選中B列），然后再點(diǎn)擊EXCEL菜單“數(shù)據(jù)-有效性-”， 在“設(shè)置

3、”對(duì)話(huà)框中選擇“自定義”、在“公式”中輸入“=COUNTIF(B:B,B1)<2”                檢驗(yàn)一下：在B3單元格中輸入“A”，看看會(huì)出現(xiàn)什么結(jié)果？        （4） 只能輸入大于上一行的數(shù)值（或日期）：            

4、   注意引用區(qū)域的最后一行行標(biāo)為相對(duì)引用。A4的有效性公式為：=MAX($B$3:$B4)        4、 條件輸入        只準(zhǔn)輸入符合一定條件的數(shù)據(jù)：        （1） 只能輸入大于左側(cè)的數(shù)字             &

5、#160;  （2） 按條件輸入_根據(jù)左側(cè)條件來(lái)決定右側(cè)單元格如何輸入：        下圖中，如果單據(jù)類(lèi)型選擇了“入庫(kù)單”，則只能在“入庫(kù)數(shù)量”所在列即C列輸入數(shù)據(jù)，而不能在“出庫(kù)數(shù)量”所在列即D列輸入數(shù)據(jù)；反之亦然。               C列公式為：=IF(B5="入庫(kù)單",ISNUMBER(C5),FALSE)   

6、;     D列公式為：=IF(B5="出庫(kù)單",ISNUMBER(D5),FALSE)應(yīng)用一下拉菜單輸入的實(shí)現(xiàn)例1：直接自定義序列有時(shí)候我們?cè)诟髁懈餍兄卸驾斎胪瑯拥膸讉€(gè)值，比如說(shuō)，輸入學(xué)生的等級(jí)時(shí)我們只輸入四個(gè)值：優(yōu)秀，良好，合格，不合格。我們希望Excel2000單元格能夠象下拉框一樣，讓輸入者在下拉菜單中選擇就可以實(shí)現(xiàn)輸入。操作步驟：先選擇要實(shí)現(xiàn)效果的行或列；再點(diǎn)擊"數(shù)據(jù)有效性"，打開(kāi)"數(shù)據(jù)有效性"對(duì)話(huà)框；選擇"設(shè)置"選項(xiàng)卡，在"允許"下拉菜單中選擇

7、"序列"；在"數(shù)據(jù)來(lái)源"中輸入"優(yōu)秀，良好，合格，不合格"（注意要用英文輸入狀態(tài)下的逗號(hào)分隔！）；選上"忽略空值"和"提供下拉菜單"兩個(gè)復(fù)選框。點(diǎn)擊"輸入信息"選項(xiàng)卡，選上"選定單元格顯示輸入信息"，在"輸入信息"中輸入"請(qǐng)?jiān)谶@里選擇"。例2：利用表內(nèi)數(shù)據(jù)作為序列源。有時(shí)候序列值較多，直接在表內(nèi)打印區(qū)域外把序列定義好，然后引用。操作步驟：先在同一工作表內(nèi)的打印區(qū)域外要定義序列填好(假設(shè)在在Z1：Z8），如“單親家庭，

8、殘疾家庭，殘疾學(xué)生，特困，低收人，突發(fā)事件，孤兒，軍烈屬”等，然后選擇要實(shí)現(xiàn)效果的列（資助原因）；再點(diǎn)擊"數(shù)據(jù)有效性"，打開(kāi)"數(shù)據(jù)有效性"對(duì)話(huà)框；選擇"設(shè)置"選項(xiàng)卡，在"允許"下拉菜單中選擇"序列"；“來(lái)源”欄點(diǎn)擊右側(cè)的展開(kāi)按鈕（有一個(gè)紅箭頭），用鼠標(biāo)拖動(dòng)滾動(dòng)條，選中序列區(qū)域Z1：Z8（如果記得，可以直接輸入=$Z$1:$Z$8；選上"忽略空值"和"提供下拉菜單"兩個(gè)復(fù)選框。點(diǎn)擊"輸入信息"選項(xiàng)卡，選上"選定單元格顯示輸入信息

9、"，在"輸入信息"中輸入"請(qǐng)?jiān)谶@里選擇"。例3：橫跨兩個(gè)工作表來(lái)制作下拉菜單用INDIRECT函數(shù)實(shí)現(xiàn)跨工作表在例2中，選擇來(lái)源一步把輸入=$Z$1:$Z$8換成=INDIRECT("表二!$Z$1:$Z$8"),就可實(shí)現(xiàn)橫跨兩個(gè)工作表來(lái)制作下拉菜單。應(yīng)用二自動(dòng)實(shí)現(xiàn)輸入法中英文轉(zhuǎn)換有時(shí)，我們?cè)诓煌谢虿煌兄g要分別輸入中文和英文。我們希望Excel能自動(dòng)實(shí)現(xiàn)輸入法在中英文間轉(zhuǎn)換。操作步驟：假設(shè)我們?cè)贏列輸入學(xué)生的中文名，B列輸入學(xué)生的英文名。先選定B列，點(diǎn)擊進(jìn)入"數(shù)據(jù)有效性"，打開(kāi)"數(shù)據(jù)有效性

10、"對(duì)話(huà)框；選擇"輸入法"對(duì)話(huà)框，在"模式"下拉菜單中選擇"關(guān)閉（英文模式）"；然后再"確定"，看看怎么樣。應(yīng)用三數(shù)據(jù)唯一性檢驗(yàn)員工的身份證號(hào)碼應(yīng)該是唯一的，為了防止重復(fù)輸入，我們用“數(shù)據(jù)有效性”來(lái)提示大家。操作步驟：選中需要建立輸入身份證號(hào)碼的單元格區(qū)域（如B2至B14列），執(zhí)行“數(shù)據(jù)有效性”命令，打開(kāi)“數(shù)據(jù)有效性”對(duì)話(huà)框，在“設(shè)置”標(biāo)簽下，按“允許”右側(cè)的下拉按鈕，在隨后彈出的快捷菜單中，選擇“自定義”選項(xiàng)，然后在下面“公式”方框中輸入公式：=COUNTIF(B:B,B2)=1，確定返回。以后在上述單元

11、格中輸入了重復(fù)的身份證號(hào)碼時(shí)，系統(tǒng)會(huì)彈出提示對(duì)話(huà)框，并拒絕接受輸入的號(hào)碼。運(yùn)用Excel三大條件函數(shù)解決實(shí)際問(wèn)題！秋盡冬至，又到一年年終時(shí)。在工作中，數(shù)據(jù)的運(yùn)算量和匯總的操作一下比平時(shí)多了不少。眾所周知，在Excel中可以對(duì)數(shù)據(jù)進(jìn)行各種不同的運(yùn)算和匯總，今天我向大家介紹與條件相關(guān)的函數(shù)三兄弟，分別是“COUNTIF”、“SUMIF”和“IF”函數(shù)。他們有一個(gè)共同的特點(diǎn)都姓字符“IF”。大兄弟：COUNTIF函數(shù)（計(jì)數(shù)求和）COUNT函數(shù)，顧名思義是用來(lái)計(jì)數(shù)的，統(tǒng)計(jì)所選擇區(qū)域的數(shù)值型單元格個(gè)數(shù)。COUNTIF是COUNT函數(shù)的引伸與拓展，在計(jì)數(shù)時(shí)加上先前條件，只有符合計(jì)數(shù)的條件才進(jìn)行統(tǒng)計(jì)計(jì)算。比

12、如，從員工信息表中，計(jì)算出有多少人的年齡大于35歲。下面我們來(lái)看一個(gè)典型的分類(lèi)計(jì)數(shù)匯總的例子。這里有一張銷(xiāo)售流水記錄表，每名銷(xiāo)售人員累計(jì)做了多少“銷(xiāo)售訂單個(gè)數(shù)”呢？ 大兄弟COUNTIF正常工作需要兩個(gè)參數(shù)條件區(qū)域（本例為左側(cè)表中“銷(xiāo)售人員”一列）和計(jì)數(shù)條件（本例為右側(cè)表中的人員姓名）。要計(jì)算第一位銷(xiāo)售人員的“訂單數(shù)”，很簡(jiǎn)單，輸入函數(shù)公式 “=COUNTIF($C$2:$C$16,E2)”即可（見(jiàn)圖1）。二兄弟：SUMIF函數(shù)（條件求和）SUM函數(shù)的作用是對(duì)數(shù)據(jù)求和，而SUMIF對(duì)它進(jìn)行了引伸和拓展，比如計(jì)算“金額”在1元以上的數(shù)據(jù)總和、按照人員或產(chǎn)品分類(lèi)計(jì)算數(shù)據(jù)總和等等。它有3個(gè)參數(shù)，分別

13、是條件區(qū)域、判斷條件、實(shí)際的求和區(qū)域（如果它與“條件區(qū)域”是一個(gè)區(qū)域，就可省略）。在上例中，計(jì)算每位“銷(xiāo)售人員”的訂單總金額，就要使用SUMIF函數(shù)來(lái)協(xié)助了。如果要計(jì)算每個(gè)人的銷(xiāo)售訂單總金額，把左側(cè)表的“銷(xiāo)售人員”一列當(dāng)作“條件區(qū)域”，把右側(cè)表的每個(gè)名單當(dāng)作求和“條件”，把左側(cè)表的每筆“訂單金額”當(dāng)作“實(shí)際求和區(qū)域”，在G2單元格中輸入數(shù)據(jù)計(jì)算公式 “=SUMIF($C$2:$C$16,E2,$B$2:$B$16)”（見(jiàn)圖2），第1名銷(xiāo)售人員的“訂單總額”就瞬間產(chǎn)生了。小提示：在本例的COUNTIF函數(shù)和SUMIF函數(shù)中，由于“銷(xiāo)售人員”區(qū)域與“訂單總額”區(qū)域都是固定的，所以在函數(shù)中引用這兩列

14、地址時(shí)，要使用“絕對(duì)地址”，也就是在地址前添加“$”符號(hào)。三兄弟：IF函數(shù)邏輯高手IF函數(shù)是一個(gè)條件函數(shù)，它可以通過(guò)設(shè)置的條件進(jìn)行邏輯判斷。如果在剛才的數(shù)據(jù)匯總表中再添加一列“銷(xiāo)售獎(jiǎng)金”數(shù)據(jù)，“銷(xiāo)售獎(jiǎng)金”發(fā)放的方法是：如果某個(gè)人的訂單總額大于￥1500,000，那么“獎(jiǎng)金”數(shù)用總額×5%，否則“獎(jiǎng)金”就用總額×3%。所以，第1位銷(xiāo)售人員“銷(xiāo)售獎(jiǎng)金”的計(jì)算公式應(yīng)為“=IF(G2>1500000,G2*0.05,G2*0.03)”，如圖3所示。結(jié)合剛才的知識(shí)，推測(cè)一下這個(gè)公式中3個(gè)參數(shù)的涵義吧?！癎2>1500000”是IF函數(shù)的判斷條件，“G2*0.05”是條件成

15、立的操作，“G2*0.03”是條件不成立的操作。名師點(diǎn)睛：*數(shù)據(jù)的分類(lèi)匯總是Excel最常見(jiàn)的應(yīng)用。分類(lèi)匯總的方法很多，包括使用函數(shù)和公式。設(shè)置的條件可看作是分類(lèi)的依據(jù)，用“COUNTIF函數(shù)”進(jìn)行分類(lèi)計(jì)數(shù)匯總，用“SUMIF函數(shù)”進(jìn)行分類(lèi)求和匯總，非常方便。*IF函數(shù)的作用是根據(jù)判斷條件的真假，自動(dòng)進(jìn)行分支操作。比如根據(jù)身份證號(hào)碼的奇偶來(lái)判斷性別，根據(jù)績(jī)效數(shù)據(jù)來(lái)填寫(xiě)成績(jī)等等。在實(shí)際應(yīng)用時(shí)，一個(gè)非常實(shí)用的應(yīng)用是：把IF函數(shù)的“真”或“假”參數(shù)嵌套成另一個(gè)IF函數(shù)，實(shí)現(xiàn)多種分支操作。如2個(gè)IF函數(shù)的嵌套可寫(xiě)成“=IF（條件，真，IF（條件，真，假）”，這樣一來(lái)就可以實(shí)現(xiàn)3個(gè)分支判斷了（在Exce

16、l中，最多是7層函數(shù)嵌套）。*重復(fù)數(shù)據(jù)的篩選問(wèn)題常常讓我們感到非常棘手。COUNTIF函數(shù)除了可以實(shí)現(xiàn)分類(lèi)計(jì)數(shù)匯總外，它和IF函數(shù)配合，還可以實(shí)現(xiàn)對(duì)重復(fù)數(shù)據(jù)的標(biāo)識(shí)與篩選，從而將1列中的重復(fù)數(shù)據(jù)刪除。在如下圖4所示的案例中，使用了公式“=IF(COUNTIF($C$1:C1,C1)>1,”1”,”0”)”為重復(fù)數(shù)據(jù)的后面添加了標(biāo)識(shí)“1”，為不重復(fù)數(shù)的后面添加了標(biāo)識(shí)“0”。在公式中，COUNTIF是條件計(jì)數(shù)，可利用它統(tǒng)計(jì)出從這列的第1個(gè)數(shù)據(jù)起，某個(gè)數(shù)出現(xiàn)了幾次，再用IF函數(shù)判斷，若出現(xiàn)2次以上就添加“1”，若出現(xiàn)1次則就添加“0”。最后用Excel中的自動(dòng)篩選功能，將所有標(biāo)識(shí)為“1”的數(shù)據(jù)行

17、刪除，就能把重復(fù)數(shù)據(jù)統(tǒng)統(tǒng)清理掉了。與“IF”相關(guān)的函數(shù)三兄弟，不但為運(yùn)算添加了“條件”，而且為我們打通了很多解決問(wèn)題的道路，只要我們肯用心發(fā)掘，一定還能夠找到更多的問(wèn)題解決之道很多單位都組建了局域網(wǎng)，雖然有不少能在局域網(wǎng)上聊天的工具，但一來(lái)使用不方便，二來(lái)不安全(容易被老板發(fā)現(xiàn))。為此，筆者建議大家用Excel來(lái)聊天。 用Excel來(lái)聊天第一步:打開(kāi)Excel2003，新建一個(gè)工作簿文檔，取名保存(如“工作記錄.xls”)。第二步:執(zhí)行“工具共享工作簿”，打開(kāi)“共享工作簿”對(duì)話(huà)框(見(jiàn)圖1)，選中“允許多用戶(hù)同時(shí)編輯，同時(shí)允許工作簿合并”選項(xiàng)，確定返回。 第三步:將上述工作簿文檔保存在局域網(wǎng)上某臺(tái)電腦的一個(gè)共享文件夾中。 第四步:局域網(wǎng)內(nèi)用戶(hù)同時(shí)打開(kāi)上述工作簿，大家約定好輸入的單元格位置(如A用戶(hù)在A列輸入內(nèi)容、B列輸入時(shí)間；B用戶(hù)在C列輸入內(nèi)容、D列輸入時(shí)間)。 第五步:選定相應(yīng)的單元格(如A1)，將聊天內(nèi)容輸入到其中，再選中B1單元格，按下“Ctrl+Shift+;”組合鍵輸入系統(tǒng)當(dāng)前時(shí)間。第六步:單擊工具欄上的“保存”按鈕，將上述輸入內(nèi)容保存一下，對(duì)方只要再按一下“保存”按鈕，即可看到上述輸入的內(nèi)容。 第七步:如果老板來(lái)了，只要切換到其他工作表(如Sheet2