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

技巧1在單元格創(chuàng)建下拉列表有許多新手在EXCEL中第一次見到下圖所示的下拉列表時，都以為是程序做的，當(dāng)他們知道圖中下拉列表只是一個普通的利用數(shù)據(jù)有效性完成的EXCEL技巧時，他們會覺得很驚奇。怎么利用數(shù)據(jù)有效性來做個下拉列表吧:怎么利用數(shù)據(jù)有效性來做個下拉列表吧:第一步在一個連續(xù)的單元格區(qū)域輸入列表中的項目，如圖中E7:E11有個商品名稱的表第二步選中A2單元格，單擊“菜單”——“數(shù)據(jù)”——“有效性”，在“數(shù)據(jù)有效性”對話框的”設(shè)置”選項卡中，在“允許”下拉列表中選擇“序列”項.第三步在”來源”框中輸入“=$E$8:$E$11”（或輸入“=”號后，用鼠標選中E8:E11）第四步勾選”忽略空值”與”提供下拉箭頭”復(fù)選框，如圖所示第五步單擊”確定”按鈕，關(guān)閉”數(shù)據(jù)有效性”對話框.這樣,就能實現(xiàn)第一張圖所示的效果了。如果列表的內(nèi)容較少，或者不方便在工作表中輸入列表項目，也可以省略上述的第一步，然后將第三步的操作改為:直接在”來源”框中輸入列表內(nèi)容，項目之間以半角的逗號分隔.如圖所示在一般情況下，數(shù)據(jù)的有效性中的序列來源，只能引用當(dāng)前工作表中的單元格區(qū)域。如果希望能夠引用其他工作表中的單元格區(qū)域，則必須先為單元格區(qū)域定義名稱，然后在”來源”框中輸入名稱.例如,將另一張工作表中的A2:A10區(qū)域，名稱定義為“SPMC”,然后在“數(shù)據(jù)有效性”的“來源”框中輸入“=SPMC”。當(dāng)我們需要對表格中的項目進行特別說明時，常常會使用EXCEL的批注功能。給單元格做批注的方法，這里不多浪費時間。而給大家介紹一下另類批注：使用批注多了，我們會發(fā)現(xiàn)EXCEL的批注也有不足之處：一、批注框的大小尺寸會受到單元格行高變化的影響；二、批注框的默認情況下，是只顯示標識符。必須把光標懸停在單元格的上方批注內(nèi)容才會顯示出來，否則即使當(dāng)單元格處于活動狀態(tài)時，它也不會顯示；三、是在上面2種情況的共同作用下，加上拆分（凍結(jié)）窗口下的插入、拖曳等工作表操作，會導(dǎo)致批注的位置遠離原來的單元格，而被主人遺忘，并隨著主人對單元格的復(fù)制或格式刷操作而被大量復(fù)制，這也是造成文件增肥的主要原因之一。我曾經(jīng)為一個會員給他的文件減肥時，從表里找出3500多個遠離母單元格的批注棄兒，最終我通過刪除這些個“批注棄兒”，幫那個會員給文件容量縮減了2/3之多。言歸正傳，說說數(shù)據(jù)有效性利用數(shù)據(jù)有效性功能，我們可以實現(xiàn)另類的批注效果，克服以上不足。第一步：選定單元格，如C1。第二步:單擊菜單〃數(shù)據(jù)〃-〃有效性〃，在〃數(shù)據(jù)有效性〃對話框的〃輸入信息〃選項卡中，勾選〃選定單元格時顯示

輸入信息〃復(fù)選框，并在〃標題〃和〃輸入信息〃文本框中輸入相應(yīng)內(nèi)容.如圖所示第三步:單擊〃確定〃按鈕，關(guān)閉〃數(shù)據(jù)有效性〃對話框.設(shè)置完畢后，當(dāng)此單元格處于活動狀態(tài)時，就會顯示剛才輸入的內(nèi)容,效果如圖所示用鼠標左鍵按住上圖中的提示框，可以將它移動到工作表中的任何位置.更重要的是數(shù)據(jù)有效性不管你對多少單元格設(shè)置，設(shè)置的內(nèi)容有多少變化，它都只會產(chǎn)生一個提示框。只是選中不同的設(shè)置的單元格，顯示不同的內(nèi)容，不會如批注一樣產(chǎn)生大量的對象增加文件容量。當(dāng)然數(shù)據(jù)有效性做的批注也有它的缺點，格式單調(diào)，輸入的字符有限，也欠美觀性等等。技巧三結(jié)合函數(shù)完成可切換的動態(tài)下拉列表在通常情況下，當(dāng)用戶使用數(shù)據(jù)有效性時，只能處理一組數(shù)據(jù)來源。而在某些場合中，數(shù)據(jù)來源不止一組，如果能有某種方法讓下拉列表根據(jù)給定的條件來展現(xiàn)就好了?，F(xiàn)在讓函數(shù)幫助數(shù)據(jù)有效性完成這可切換的動態(tài)下拉列表吧：如圖所示，E1:G7有3列分別為“英文”、“數(shù)字”、“中文”的3列字段DEFG1英文數(shù)字中文2-AAA101房門3BBB102凳子4CCC10凱電腦.5DDD1046.EEE7FFF8現(xiàn)在要根據(jù)A1單元格的值，讓A3單元格的下拉列表分別展現(xiàn)不同的下拉列表

第一步：選定A3單元格，單擊菜單“插入”——“名稱”——“定義”，在“定義名稱”對話框中如圖所示，寫入名稱“SPMC”，引用位置輸入公式“=CHOOSE(MATCH($A$1,$E$1:$G$1,),$E$2:$E$7,$F$2:$F$5,$G$2:$G$4)”，單擊“確定”。第二步：選定A3單元格，單擊菜單“數(shù)據(jù)”——“有效性”，在“數(shù)據(jù)有效性”對話框的“設(shè)置”選項卡中，在“允許”下拉列表框中選擇“序列”；第三步：在“來源”框中輸入:"=SPMC”；第四步：勾選“忽略空值”與“提供下拉箭頭”復(fù)選框，如圖所示，單擊“確定”按鈕?，F(xiàn)在當(dāng)用戶在A1單元格中分別輸入“英文”、“數(shù)字”、“中文”時,A3的下拉列表框中會出現(xiàn)不同的展現(xiàn)不同的下拉列表，分別對應(yīng)不同的數(shù)據(jù)從本實例可以看出,當(dāng)設(shè)置數(shù)據(jù)有效性時，序列來源不僅可以指定一個單元格區(qū)域.使用名稱來引用單元格區(qū)域，還可以使用公式來完成更復(fù)雜的任務(wù)，上述公式利用了CHOOSE函數(shù)和MATCH函數(shù)，根據(jù)A1單元格的值來動態(tài)引用數(shù)據(jù)源。此外還有許多函數(shù)可以和數(shù)據(jù)有效性結(jié)合使用，這里就不一一細述了。技巧四、動態(tài)的數(shù)據(jù)有效性在日常工作中，數(shù)據(jù)有效性的數(shù)據(jù)源往往是不斷增加的內(nèi)容的單元格區(qū)域。就希望在數(shù)據(jù)有效性中設(shè)置的來源能自動同步增加這些新補充的內(nèi)容，需要動態(tài)的數(shù)據(jù)有效性如圖，要在單元格A2建立動態(tài)的下拉列表，讓其下拉數(shù)據(jù)根據(jù)C列的增加而增加建立動態(tài)的數(shù)據(jù)有效性下拉列表有兩種方法：1、列表法第一步：選中C1單元格，“菜單”——“數(shù)據(jù)”——“列表”——“創(chuàng)建列表”(或選中C1單元格，CTRL+L)，進入創(chuàng)建列表窗，查看區(qū)域是否正確，勾選列表有標題后，按確定。列表創(chuàng)建完成。第二步，選中列表區(qū)域，在名稱框輸入“SPMC”,(或選中區(qū)域，菜單——插入——名稱——定義，在定義名稱框里輸入名稱名“SPMC”),完成給列表定義名稱的步驟。而后重復(fù)本貼技巧三的第二、三、四步。2、函數(shù)法方法如同本貼技巧三的操作，不同是函數(shù)的公式不同，名稱SMPC，引用位置輸入公式是“=OFFSET($C$1,,,COUNTA($C:$C))”事實上，動態(tài)引用數(shù)據(jù)源的訣竅就是在數(shù)據(jù)有效性的來源設(shè)置中使用動態(tài)名稱，有動態(tài)名稱完成自動適應(yīng)內(nèi)容增長的各種變化。北覺得列表法建立的動態(tài)數(shù)據(jù)源，簡便易學(xué)，建表者不需要有函數(shù)基礎(chǔ)。但只能增加數(shù)據(jù)源，如果數(shù)據(jù)減少了，就會在下拉列表里增加很多空項目。而函數(shù)法建立的動態(tài)數(shù)據(jù)源，基本隨內(nèi)容的增減而增減，不會因數(shù)據(jù)刪減了，就增加空項目。但建表者需要有一定的函數(shù)功力。技巧五、限定錄入數(shù)據(jù)必須符合特定條件在許多場合中，為規(guī)范用戶的錄入，保證數(shù)據(jù)的嚴謹和可讀性，需要限定錄入數(shù)據(jù)必須符合特定條件，此時就又需要用到數(shù)據(jù)有效性了。如果數(shù)據(jù)的限定比較簡單，比如：必須是某個區(qū)間的整數(shù)或小數(shù)；必須是某個時間段等，可以在”數(shù)據(jù)有效性”對話框中”設(shè)置”選項卡里面通過選擇不同的有效性允許條件，并配合相應(yīng)的參數(shù)來實現(xiàn)、但對于數(shù)據(jù)特征較為復(fù)雜的，就必須使用"自定義”條件，通過編寫公式來實現(xiàn)例如，我們經(jīng)常碰到人員錄入，就需要錄入身份證號，大家都知道我國的身份證號分老版15位，新版18位兩種，另兩個人的身份證號是不可能發(fā)生重復(fù)的。這就需要我們在設(shè)計表格時把這樣特定限定條件放進去，避免這樣明顯的錄入差錯。我們現(xiàn)在一起來做個限定A2：A100區(qū)域的身份證不重復(fù)錄入的數(shù)據(jù)有效性吧第一步：選中A2：A100區(qū)域。第二步：單擊菜單”數(shù)據(jù)"-"有效性”，在”數(shù)據(jù)有效性”對話框的”設(shè)置”選項卡中，在允許下拉列表框中選擇”自定義”項。第三步：在"公式"框中輸入：=(SUMPRODUCT(--(A$1:A2=A2))=1)*(OR(LEN(A2)=15,LEN(A2)=18))第四步：勾選”忽略空值”復(fù)選框，單擊”確定”按鈕，關(guān)閉”數(shù)據(jù)有效性”對話框。來現(xiàn)在，我們試試，輸入正常的不重復(fù)身份證號，是否能正常錄入？再分別:輸入重復(fù)身份證號；位數(shù)不是15或18位的身份證號，是否會象下圖所示一樣被EXCEL拒絕？注：在此要提醒大家，利用數(shù)據(jù)有效性來限定錄入數(shù)據(jù)必須符合特定條件，是一種事前控制，它只對將要錄入的數(shù)據(jù)起作用。如果單元格在設(shè)置數(shù)據(jù)有效性以前就錄入了數(shù)據(jù)，或是批量復(fù)制的數(shù)據(jù)，則不會受到數(shù)據(jù)有效性的檢查。此時，在設(shè)計表格時就需要配合條件格式功能，讓條件格式幫你做后期檢查提醒工作。技巧六、杜絕負數(shù)庫存在一般情況下，用戶在處理銷售出庫時需要依據(jù)庫存余額來確定最大出庫量，以避免產(chǎn)生出庫數(shù)量大于庫存數(shù)量，出現(xiàn)負數(shù)庫存的后果。因此，在大多數(shù)的企業(yè)管理系統(tǒng)中，開具出庫單時，填寫的數(shù)量都會被限制為必須小于等于庫存量。如果用戶用EXCEL來管理庫存與出入庫業(yè)務(wù)，也可以利用數(shù)據(jù)有效性的特性，輕松實現(xiàn)這樣的控制。以如圖所示的工作薄中，有兩張工作表，庫存表記錄著所有產(chǎn)品的庫存余額，出庫表用來填寫每次的實際出庫數(shù)量，(說明一下，這個表，只是我隨手打的表，并非實際表樣，僅為說下面我們針對銷售表的F3:F7單元格設(shè)置數(shù)據(jù)有效性，實現(xiàn)禁止輸入大于庫存數(shù)量的實際出庫數(shù)量的效果。第一步：將”庫存”工作表中的A3:B7定義名稱為”KC”。第二步:在”出庫"工作表中，選定區(qū)域F3:F7單元格.第三步：單擊菜單”數(shù)據(jù)"-"有效性"，在”數(shù)據(jù)有效性”對話框的”設(shè)置”選項卡中，在”允許”下拉列表框中選擇”自定義”項。第四步:在"公式"框中輸入：=F3<=VLOOKUP(D3,KC,2,0)第五步：勾選”忽略空值”復(fù)選框，單擊”確定”按鈕，關(guān)閉”數(shù)據(jù)有效性”對話框。如圖，我在實際出庫中輸入了超過庫存量的出庫量，給EXCEL拒絕。說明一下，以上方法只適用于進出發(fā)生少、品種少的企業(yè)。真正的企業(yè)用數(shù)據(jù)有效性是根本負擔(dān)不了大數(shù)據(jù)量的。常?？吹接腥讼Ｍ眉兒瘮?shù)加技巧，來處理庫存帳務(wù)工作。個人覺得這個不很現(xiàn)實，庫存帳還是交給VBA吧。這里用庫存帳舉例，只是說明一下，數(shù)據(jù)有效性可以這么用而已。技巧七、強制序時錄入我們在輸入數(shù)據(jù)時，經(jīng)常需要錄入流水單證，流水單證的特定是序時性，即新錄入數(shù)據(jù)的日期絕不能早于已有記錄的最大日期。利用EXCEL的數(shù)據(jù)有效性可以輕松地完成這樣的限

制。以在A2:A100單元格區(qū)域內(nèi)需要輸入流水單證的時間為例，有兩個方法可以實現(xiàn)。方法一確定。第一步：選定區(qū)域A2:A100.第二步：單擊菜單”數(shù)據(jù)”-”有效性”，在”數(shù)據(jù)有效性”對話框的”設(shè)置”選項卡中，在”允許”下拉列表框中選擇”自定義”項。第三步：在”公式”框中輸入:=N(A2)>=N(A1)第四步：勾選”忽略空值”復(fù)選框，單擊”確定"按鈕，關(guān)閉”數(shù)據(jù)有效性”對話框。方法二:第一步：選定區(qū)域A2:A100.第二步：單擊菜單”數(shù)據(jù)”-”有效性”，在”數(shù)據(jù)有效性”對話框的”設(shè)置”選項卡中，在”允許”下拉列表框中選擇”日期”項。第三步:在”數(shù)據(jù)”下拉列表框中選擇”大于或等于”，在”開始日期”框中輸入：=MAX($A$2:$A2)第四步：勾選”忽略空值”復(fù)選框，如圖所示。單擊”確定”按鈕，關(guān)閉”數(shù)據(jù)有效性”對話框。先輸入一輔助表列，一列為中文城市，一列為英文城市。選中中文城市單元格后一插入一名稱一定義，在上面輸入名稱“中文”，引用位置應(yīng)該就是中文城市單元格，不是的請修改。同樣處理英文城市區(qū)域取名為英文。B3單元格一數(shù)據(jù)一有效性一設(shè)置?！霸试S”選“序列”，“來源”輸入：=INDIRECT($A$3)，同時勾選上邊上的“忽略空值”和“提供下拉箭頭”。確定退出。EXCEL自動隱藏內(nèi)容為0的單元格方法一選中整個工作表-右鍵(設(shè)置單元格)-保護(取消鎖定、隱藏前面的J)-F5定位-定位條件-公式-確定-右鍵(設(shè)置單元格)-保護(把鎖定、隱藏選中-確定)-工具-保護-保護單元格。這樣就能隱藏公式，但是單元的大小就不能再編輯了。設(shè)定條件格式從而達到自動隱藏內(nèi)容為0的EXCEL單元格的目的。選擇目標列或目標區(qū)域，在EXCEL格式菜單下面選擇條件格式，在對話框中分別設(shè)置左中右的條件為：選擇單元格數(shù)值、等于、0，設(shè)置好EXCEL自動隱藏內(nèi)容為0的單元格條件后，點擊格式按鈕，在字體設(shè)置中把顏色選為白色，確定退出條件格式設(shè)置對話框。以后，當(dāng)目標列或目標區(qū)域中有單元格的內(nèi)容為0的時候，0的字體顏色會自動被設(shè)置為白色，這樣可以達到自動隱藏內(nèi)容為0的EXCEL單元格的目的。但是需要說明的是，這種自動隱藏內(nèi)容為0的EXCEL單元格的方法中，0并不是被清理了，而是被隱藏了，因