EXCEL系列04

慧清書齋 2019-03-12

展開全文

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

一、效果展示

如上圖，可以看到，當我們在姓名列輸入姓“黃”或者“楊”的時候，會出現(xiàn)含有“黃”或“楊”的姓名下拉列表，選擇姓名后，序號列會自動生成序號及單元格邊框，日期列也會自動生成日期及單元格邊框，部門列根據(jù)下拉菜單確定后，崗位列會根據(jù)部門列生成二級下拉菜單，如當我們選擇“工程部”時，崗位列會將工程部的所有崗位列表作為下拉菜單顯示。當工資列輸入值超過50000時會出現(xiàn)警告。

二、功能講解

1、條件格式設置。

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

2、函數(shù)公式。

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

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

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

如上圖，對所選區(qū)域對數(shù)據(jù)有效性進行設置，選擇序列，數(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ù)返回包含關鍵字的數(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&”*”(*號是通配符，代表任意不確定字符)在Sheet2!$A$2:$A$13區(qū)域中查找，查找方式為0（精確查找）。當B2單元格輸入關鍵字時，該函數(shù)將查找出包含關鍵字的數(shù)據(jù)在Sheet2!$A$2:$A$13區(qū)域中第一次出現(xiàn)的位置。

第三參數(shù)為0，因為我們的數(shù)據(jù)源只有A列一列，所以列偏移量為0，表示不偏移。簡單來說，就是offset函數(shù)以Sheet2!$A$2單元格為參照，不橫向偏移，只向下偏移。

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

第五參數(shù)為新引用區(qū)域的列數(shù)，因為只有A列一列，所以為1。

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