今天我為大家講解下如何利用函數(shù)、數(shù)據(jù)有效性及條件格式實(shí)現(xiàn)數(shù)據(jù)高效輸入。

一、效果展示

如上圖，可以看到，當(dāng)我們?cè)谛彰休斎胄铡包S”或者“楊”的時(shí)候，會(huì)出現(xiàn)含有“黃”或“楊”的姓名下拉列表，選擇姓名后，序號(hào)列會(huì)自動(dòng)生成序號(hào)及單元格邊框，日期列也會(huì)自動(dòng)生成日期及單元格邊框，部門列根據(jù)下拉菜單確定后，崗位列會(huì)根據(jù)部門列生成二級(jí)下拉菜單，如當(dāng)我們選擇“工程部”時(shí)，崗位列會(huì)將工程部的所有崗位列表作為下拉菜單顯示。當(dāng)工資列輸入值超過50000時(shí)會(huì)出現(xiàn)警告。

二、功能講解

1、條件格式設(shè)置。

如上圖所示，設(shè)置區(qū)域內(nèi)單元格為非空值時(shí)，有外邊框。

2、函數(shù)公式。

如上圖，序號(hào)列公式“=IF(B2<>'',ROW(A1),'')”，假設(shè)姓名列B2不為空，則返回單元格A1對(duì)應(yīng)的行值。日期列公式“=IF(B2<>'',TODAY(),'')，”假設(shè)姓名列B2不為空，則返回日期TODAY。

3、姓名列數(shù)據(jù)有效性設(shè)置

如上圖，對(duì)表2中需要用到的姓名列表進(jìn)行升序或降序，這樣會(huì)讓相似內(nèi)容連續(xù)排列。

如上圖，對(duì)所選區(qū)域?qū)?shù)據(jù)有效性進(jìn)行設(shè)置，選擇序列，數(shù)據(jù)來源引用函數(shù)公式“=OFFSET(Sheet2!$A$1,MATCH(B2&'*',Sheet2!$A$2:$A$13,0),0,COUNTIF(Sheet2!$A$2:$A$13,B2&'*'),1)”，這里主要使用OFFSET函數(shù)返回包含關(guān)鍵字的數(shù)據(jù)。OFFSET函數(shù)的作用是根據(jù)指定的參照單元格，通過給定的偏移量返回新的引用數(shù)據(jù)。OFFSET（參照系，行偏移量，列偏移量，新引用區(qū)域的行數(shù)，新引用區(qū)域的列數(shù)）。

第一參數(shù)引用了表2中的A1單元格作為參照系。

第二參數(shù)用MATCH(B2&'*',Sheet2!$A$2:$A$13,0)確定行偏移量。MATCH為查找函數(shù)，根據(jù)查找值B2&”*”(*號(hào)是通配符，代表任意不確定字符)在Sheet2!$A$2:$A$13區(qū)域中查找，查找方式為0（精確查找）。當(dāng)B2單元格輸入關(guān)鍵字時(shí)，該函數(shù)將查找出包含關(guān)鍵字的數(shù)據(jù)在Sheet2!$A$2:$A$13區(qū)域中第一次出現(xiàn)的位置。

第三參數(shù)為0，因?yàn)槲覀兊臄?shù)據(jù)源只有A列一列，所以列偏移量為0，表示不偏移。簡(jiǎn)單來說，就是offset函數(shù)以Sheet2!$A$2單元格為參照，不橫向偏移，只向下偏移。

第四參數(shù)COUNTIF(Sheet2!$A$2:$A$13,B2&'*')統(tǒng)計(jì)A2-A13區(qū)域內(nèi)滿足條件B2&'*'，也就是包含B2單元格內(nèi)的關(guān)鍵詞的單元格出現(xiàn)的次數(shù)，也就是最終在數(shù)據(jù)驗(yàn)證下拉菜單中一共會(huì)出現(xiàn)幾行。

第五參數(shù)為新引用區(qū)域的列數(shù)，因?yàn)橹挥蠥列一列，所以為1。

如之前動(dòng)態(tài)圖所示：offset函數(shù)以A1為參照系向下查找，通過match函數(shù)在A2-A13中找到包含B2關(guān)鍵字“黃”的數(shù)據(jù)第一次出現(xiàn)的位置是從表2中單元格A2開始的第6行，再通過countif函數(shù)找到總共有2行，最終在下拉菜單中返回這2行1列的數(shù)據(jù)。

如上圖，在“出錯(cuò)警告”選卡中取消勾選，這樣輸入這樣在我們輸入時(shí)就不會(huì)跳出錯(cuò)誤提醒。

完成上述設(shè)置，姓名列的數(shù)據(jù)就會(huì)實(shí)現(xiàn)帶搜索功能的下拉菜單。

4、部門及崗位列數(shù)據(jù)有效性設(shè)置

如上圖對(duì)表2中C列數(shù)據(jù)組進(jìn)行命名，命名為“部門”，對(duì)E列數(shù)據(jù)組作為二級(jí)菜單進(jìn)行命名。

如上圖，將表1中部門列的數(shù)據(jù)有效性設(shè)置數(shù)據(jù)來源為我們之前建立的數(shù)據(jù)組“=部門”，崗位列的數(shù)據(jù)有效性設(shè)置為“=INDIRECT($C2)”，這樣崗位列的數(shù)據(jù)便成為部門列數(shù)據(jù)的二級(jí)下拉菜單。

5、工資列數(shù)據(jù)有效性設(shè)置

如上圖，將工資列數(shù)據(jù)有效性設(shè)置為“0-50000”，當(dāng)超出該范圍后，會(huì)出現(xiàn)警告錯(cuò)誤。

總結(jié)：利用函數(shù)公式及數(shù)據(jù)有效性不僅可以實(shí)現(xiàn)數(shù)據(jù)的快速錄入，還可以提高數(shù)據(jù)的準(zhǔn)確性，我今天教大家的只是一些簡(jiǎn)單的設(shè)置，希望可以起到拋磚引玉的作用，千里之行始于足下！今天就為大家講解到這里，希望能與大家一起學(xué)習(xí)成長(zhǎng)！