基于Excel的模糊篩選系統設計與實現

王毅敏

（復旦大學 國家級實驗教學示范中心，上海 200433）

0 引言

人類社會自古以來就有處理數據的需求，文明程度越高，需要處理的數據越多、越復雜，所需的處理速度也越快。為此，人們不斷改善相關工具來滿足數據處理需求。計算機的出現，意味著信息時代來臨，人們需要更頻繁地與數據打交道。因此，Excel也應運而生。

Excel 為數據處理提供了一個強大的平臺，打開該平臺，展現給用戶的是簡潔、友好的操作界面。最上面是一排功能菜單，中間是一些功能組，其中包括一些常用的功能按鈕。下面是單元格的編輯區，界面的主要部分是由無數個單元格組成的表格。該界面看似簡單，卻具有強大的數據處理功能，用戶可進行計算、查詢、排序、篩選、分類、統計、匯總及圖表轉換等數據處理工作。Excel 目前已廣泛應用于各行各業的日常數據處理。

Excel 在管理信息系統應用方面，以考試和閱卷系統為例，實現了試題管理、自動組卷、自動閱卷與試卷分析功能，還特別實現了操作題的自動閱卷功能，使考試過程更加公平、公正，減少了教師工作量，提高了工作效率。系統采用VBA 等高級語言實現考試過程管理模塊、數據庫管理模塊及閱卷子系統模塊功能，但實現過程較為復雜，沒有充分利用Excel 現成的函數及功能，且系統升級維護較為困難。

本文介紹利用Excel 提供的工具及函數，無需高級語言編程，即可實現篩選功能，再進一步生成一個模糊篩選系統。在維護模塊中，用戶可隨意進行數據編輯、增加、刪除、排序、消除重復項等日常維護操作。

1 模糊選項功能制作

在Excel 的數據輸入過程中，為提高數據輸入效率，可利用數據驗證功能對單元格設置選項，用戶可方便地從中選擇所需選項。但如果選項數目很多，例如成百上千，則會很不方便。如果讓用戶輸入某個字符，系統通過模糊篩選列出相關選項，從而縮小范圍，將會大大方便用戶使用。Excel 提供了現成的數據選項功能，但未提供模糊選項功能。因此，本文介紹如何利用Excel 提供的工具及函數，無需編寫代碼實現上述功能。

要實現該功能，需要有一個輔助區域，以方便對選項數據的處理。該區域處理結果需要放入單元格驗證功能的序列選項中，用戶才能看到相應結果，以下介紹具體實現過程。

1.1 總體思路

將所有選項數據放在輔助區域中，該區域有數據選項列及模糊篩選列。數據選項列是用戶所有選項數據，模糊篩選列是用戶感興趣的選項數據，也是數據選項列的子集。

設計思路如下：最后的目標是從數據選項列中選出所有符合用戶要求的數據，放入模糊篩選列中。先判斷數據選項列的數據是否與用戶輸入信息相匹配，如果是，則返回所在單元格地址，否則返回一個很大的值，如65 536。因為與之匹配數據所在單元格的地址可能分散在數據選項列的各處，用排序函數對這些地址從小到大進行排序，使所有小于65 536 的數值都是相關單元格地址，且集中在一起。然后根據這些地址，利用查表函數在數據選項列中取出對應地址單元格的內容，放入模糊篩選列中。65 536這個地址不能在數據選項列中取出任何值，因此返回結果為空。至此，生成了模糊篩選列的結果。根據該結果列出的相應名稱，用查表函數取出所有名稱放入數據驗證功能的序列選項中，則完成了模糊選項設置。具體過程如下：

1.2 實施過程

利用Excel 有關函數FIND、SMALL、OFFSET 等的互相嵌套、數組公式計算及表對象等功能，可完成模糊選項設置的整個過程，以下分4步實現：

（1）確定數據選項列中匹配的數據。用戶需要的選項信息全部在數據選項列中，現存放的是大學名稱。如用戶在選項單元格中輸入“河”，在模糊篩選列中，用FIND 函數確定數據選項列中與用戶輸入信息相匹配的單元格，并將結果存入模糊篩選列中。輸入：=FIND（sheet！$a$2，$a$2：$a$63），其中sheet1！$a$2 為用戶輸入的選項單元格信息，$a$2：$a$63 為數據選項列區域，用數組公式計算結果，如圖1 所示。#VALUE！表示對應單元格沒有相關字符，如返回的是數字，表示該單元格第N 個字符與用戶輸入的選項信息匹配。

Fig.1 Determine the matching data in the data option column圖1 確定數據選項列中匹配的數據

（2）返回對應單元格行號。可用IF 函數對FIND 函數返回結果進行處理。如果為錯誤結果，即#VALUE！，表示沒有查詢到結果，則設置一個很大的值，如65 536 代替行號，否則返回該單元格行號。具體寫為：=IF（ISERR（FIND（Sheet1！$A$2，Sheet2！$A$2：$A$63）），65 535，ROW（A2：A63）），其中ISERR 函數返回結果為TRUE 或FALSE，ROW 函數返回數據選項列當前單元格的行號。在模糊篩選列中，65536 對應的是與用戶輸入信息未匹配的單元格，其它數值是與用戶輸入信息匹配的單元格行號，ROW（A2：A63）返回該結果。這些行號分散排列，結果如圖2 所示。為便于處理，后面需要將這些行號進行集中。

Fig.2 Return to the row numbers of corresponding cell圖2 返回對應單元格行號

（3）將行號遞增排列。至此，模糊篩選列中的結果是與用戶輸入信息匹配的對應單元格行號以及65 536 等數據。一般情況下，這些單元格行地址分散在模糊篩選列的各處，為便于處理，需要將相關行號集中在一起。可對行號進行升序排列，便于后面集中按該行號獲取名稱。

利用SMALL 函數實現排列，嵌套上面的結果，該函數可讓數據從小到大進行排列。可以寫成=SMALL（IF（ISERR（FIND（Sheet1！$A$2，Sheet2！$A$2：$A$63）），65535，ROW（A2：A63）），ROW（）-1），該函數返回的是第N小的數放在相應位置，如最小的放在第一個位置，第二小的放在第二個位置，依此類推。該函數有兩個參數，第一個為數據區域，第二個為序號，其中數據區域為前面運行的結果，序號為所在單元格行號，在這里表示第N 小的數。運行結果如圖3 所示，B2 單元格為最小的數，依此類推。

Fig.3 Row numbers in ascending order圖3 行號遞增排列

（4）取出對應行號內容。可以用OFFSET 函數，根據上面升序排列的行號查找工作表中數據獲得名稱。65 536是一個很大的數值，在這里沒有查詢結果，返回為空。函數參數說明為：OFFSET（reference，rows，cols，［height］，［width］），reference 為起始單元格或單元格區域；rows 為前面第一個單元格開始的相對行位置；cols 為前面第一個單元格開始的相對列位置，這里設為0；［height］、［width］為可選項，是所取單元格行或列的高度或寬度。根據本案例，具體函數可寫為：=OFFSET（$a2$，SMALL（IF（ISERR（FIND（sheet！$a$2，$a$2：$a$12）），65 536，ROW（$a$2：$a$12）），ROW（）-2），0，1，0）。也即是說，從$a2$單元格位置的第一行開始，根據上述SMALL 函數嵌套計算的行號結果，從數據選項列中查找到對應地址的單元格內容。所取單元格行方向高度為1，即一個單元格，放入模糊篩選列中，該模糊篩選列中的結果即為模糊篩選后的名稱集合。需要說明的是，該函數需要啟用數組公式進行計算。如用戶輸入“河”，在模糊篩選列中集中顯示所有包含“河”的名稱，如圖4所示。

Fig.4 Extract the content of corresponding row number圖4 取出對應行號內容

（5）將選項結果放入數據驗證功能的序列選項中。上述4 個步驟實現了根據用戶輸入信息在數據選項列單元格中匹配到相關信息，并通過FIND 函數、SMALL 函數及OFFSET 函數嵌套，將模糊篩選結果放入模糊篩選列中。最后需要將該結果放入數據驗證功能序列選項中，供用戶選擇。這里仍然用OFFSET 函數實現：=OFFSET（Sheet2！B1，1，，A3），Sheet2！B1 是模糊篩選列開始的單元格，A3存放的是模糊篩選列區域中可供選擇的選項數目，也即行的高度。如高度計算結果為5，則表示取出連續5 個單元格的區域內容，可用COUNT（FIND（Sheet1！$A$2，Sheet2！$A$2：$A$63））計算得到。COUNT 函數是計數函數，返回的是數值數目，FIND 函數運行過程在上文已有介紹，這里不再贅述。

至此，已完成了將輔助區域中的模糊篩選列數據放入數據驗證功能自定義選項的過程。當用戶在數據選項的數據單元格中輸入某個字符時，如“河”，則可以列出包含“河”字符的所有選項名稱，如圖5所示。

Fig.5 Fuzzy filtering result圖5 模糊篩選結果

2 模糊選項功能優化

上述功能完成后，仍有一個缺陷，即在輔助區域中，數據選項列的數據日常維護很不方便，無法進行增加或刪除，否則會報錯。原因是數據選項列及模糊篩選列中的單元格都是一個固定區域，在相關函數中也引用了這些固定區域。在用戶使用過程中，一些選項數據難免會產生變化，用戶需要對這些選項數據進行增加、刪除等操作。為了讓用戶可以自由增加或刪除這些選項數據，需要進一步完善相關功能。

Excel 提供了多種方法來解決該問題，在此，可利用表對象自動延展的特性實現功能優化。優化后，用戶可對輔助區域數據選項列中的所有名稱數據進行排序、編輯、增加及刪除等操作，從而十分方便地對這些數據進行日常維護。以下是優化具體步驟：

（1）將數據選項列中的工作表數據轉化為表對象。選定數據選項列區域，選中名稱為“插入”的選項卡，按下表格組中的表格按鈕，從而將選定區域轉換成表對象。

（2）確定數據選項列中的所有相關單元格，并返回對應單元格行號。在數據選項列右邊最上面的第一個單元格中輸入公式。根據用戶輸入信息，用FIND 函數確定數據選項列中與之相匹配的單元格，該函數公式為：=IF（ISERR（FIND（Sheet1！$B$2，表1［［#數據］，［數據選項列］］）），65 536，ROW（））。與上一節介紹的類似，Sheet1！$B$2 是用戶的輸入數據，表1［［#數據］，［數據選項列］］是表對象的結構化引用。該公式表示選中了一個名稱為“表1”的表對象，字段名為數據選項列中的數據部分（不包括字段名）。該數據為所有選項數據，即大學名稱。回車后自動生成結果，并增加一列，將列名稱自動命名為“列2”。至此，“列2”中的數據是根據數據選項列的數據，經過函數計算后的結果，如圖6所示。

Fig.6 Return to the row numbers of corresponding cell圖6 返回對應單元格行號

（3）將相關單元格行號遞增排列。“列2”中列出的是與查找結果相匹配的單元格行號，顯示的是對應數據選項列中單元格的行號，或者用一個很大的數值代替行號，這里采用65 536。現在要對“列2”中的數據進行遞增排列整理，即將行號從小到大升序排列。這樣做的目的是：可將找到的名稱放在最前面，便于集中管理。在“列2”數據列右邊第一個單元格中輸入公式：=SMALL（表1［［#數據］，［列2］］，ROW（）-2），則系統自動生成“列3”，所列數據是“列2”的升序排列。SMALL 函數可實現將列中數據按從小到大排列，其中表1［［#數據］，［列2］］為“列2”中的數據，ROW（）-2 為第N 小。如某單元格為第3 行，則ROW（）-2 計算結果為1，表示最小，后面依此類推，如圖7所示。

Fig.7 Row numbers in ascending order圖7 行號遞增排列

（4）取出單元格對應行號內容。“列3”中列出的行號是對應選項數據列中的數據名稱。因此，可用OFFSET 函數取出選項數據列中的名稱，在“列3”數據列右邊第一個單元格中輸入公式=OFFSET（表1［［#標題］，［列1］］，［探討列3］-2，，1，），系統自動生成“列4”。所列數據是“列3”行號對應“列1”的名稱，將“列4”名稱改為“模糊篩選列”，如圖8所示。

Fig.8 Extract to the content of corresponding line number圖8 取出對應單元格行號內容

（5）將模糊篩選列中的名稱加入選項中。模糊篩選列中的數據是模糊選項功能運行后的結果，該結果是模糊選項單元格中的選項數據。針對模糊選項單元格的數據驗證功能輸入公式=OFFSET（Sheet3！D：D，2，，B3，），其中Sheet3！D：D，2 表示從sheet3 的D 列第二個單元格開始查詢，B3 內容為模糊篩選列中的選項名稱數量，計算公式為=COUNT（FIND（$B$2，表1［［#數據］，［數據選項列］］））。

現輸入數據測試一下功能：在模糊選項單元格中輸入“東”，點擊單元格右邊按鈕，則列出與“東”相關的大學名稱，如“東北林業大學”“東北財經大學”“山東師范大學”等，如圖9所示。

Fig.9 Fuzzy filtering result圖9 模糊篩選結果

3 應用實例

某單位有40 多個部門，員工有1 萬名左右，需要做一個模糊篩選系統。相關數據包括：部門名稱、員工姓名、員工工號。部門名稱按拼音順序或按筆劃順序排列，可以進行選擇；員工姓名可進行模糊篩選；員工工號無須輸入，由系統自動確定。具體實現過程如下：

3.1 功能實現

以上介紹了面向用戶的功能，如何實現上述功能？這是設計者需要完成的任務，這里簡要介紹一下實現思路。

按照用戶習慣，首先確定部門，然后確定該部門中的員工，所以可用兩層篩選實現。部門名稱選項功能可利用Excel 中現成的數據驗證功能實現，姓名與部門名稱關聯，確定部門名稱后，再進行模糊篩選確定一組姓名。確定這組姓名中的某個人姓名后，用VLOOKUP 函數查詢到結果，由系統自動填入。

如何實現模糊篩選并優化功能，在前文已有介紹，這里介紹一下部門名稱與員工姓名的關系。兩者是“與”的關系，即兩者都存在，才出現最后的模糊篩選結果，并列出在選項列中供用戶選擇。

表中的“列8”與“列9”用來判斷部門名稱及員工姓名是否存在，運行結果為TRUE 或FALSE，“列8”“列9”的計算公式分別為：=NOT（ISERR（FIND（$N$3，表3［［#數據］，［部門］］）））以及=NOT（ISERR（FIND（$O$3，表3［［#數據］，［姓名］］）））。根據$N$3 單元格中的部門名稱及$O$3 單元格中的姓名查找相應的列，返回結果為TRUE 表示找到，FALSE 表示沒有找到。如為空則為TRUE，即為找到。現在$N$3 單元格中的部門名稱為空，$O$3 單元格中的姓名也為空，輔助區域結果如圖10 所示，即列出所有選項。

Fig.10 Results of auxiliary area圖10 輔助區域結果

“列4”中的公式為：=IF（［探討列8］*［探討列9］*1，ROW（），65536）。根據“列8”及“列9”的結果，進行邏輯“與”運算，如為TRUE，則返回一個結果所在的行號，否則返回一個很大的數值，如65 536（該數字表示無法在表中找到結果）。依據“列4”中的行號，在“列5”及“列6”中按升序排列行號，并查出相應姓名及部門。公式分別為=OFFSET（表3［［#標題］，［姓名］］，SMALL（表3［［#數據］，［列4］］，ROW（）-2）-2，，1，）及=OFFSET（表3［［#標題］，［部門］］，SMALL（表3［［#數據］，［列4］］，ROW（）-2）-2，，1，），這是最后選定部門名稱及模糊查找員工姓名后的結果，范圍已大幅縮小，用戶可從中選定一個姓名。一旦選定了姓名，系統利用公式=IFERROR（VLOOKUP（O3，表3［［#數據］，［列5］：［列7］］，3，FALSE），""），確定員工工號。

現在$N$3 單元格中的選定部門名稱為“通信與信息工程學院”，$O$3 單元格中的姓名為空，圖11 列出了輔助區域結果，即列出該部門的所有員工姓名。圖12 列出了$N$3 單元格部門名稱及$O$3 單元格中該部門的所有員工姓名可選名單。

Fig.11 Results of logical operation“and”圖11 邏輯“與”運算后的結果

Fig.12 Names of all employees in the selected department圖12 選定部門所有員工姓名

由于通信與信息工程學院有教職員工幾百名，在$O$3單元格中輸入“謝”，運行模糊篩選功能后，列出所有包含“謝”的名單，如圖13 所示。從中選定一個員工姓名，$P$3單元格自動確定該員工工號。

Fig.13 Fuzzy filtering result圖13 模糊篩選結果

在輔助區域中，工號、姓名、部門是用戶數據，后面幾列是輔助列，可利用Excel 功能將其隱去。整個數據區域是一個表對象，用戶可對這些數據進行日常維護，如增加、刪除等，不會對功能有任何影響，這些更新后的數據可在選項列中同步出現。

3.2 功能測試

（1）編輯及增刪數據。在實際使用中，選項中的數據不是固定不變的，有時需要進行修改、增加及刪除操作。輔助區域已轉換成表對象，利用表對象的擴展功能，只需輸入選項名稱，回車后系統會自動擴展將其加入表對象記錄。另外，也可很方便地對數據進行修改。如果用戶輸入重復的選項數據，可利用表對象刪除重復項的功能進行處理。所有修改、增加及刪除后的數據，將在用戶選項數據的單元格中自動同步更新。

（2）數據排序。輔助區域輸入的選項數據很多，可能有幾百甚至上千，如果能夠按一定的方式排序，將會極大地方便數據管理。由于輔助區域已轉換成表對象，可利用表對象排序功能很方便地按拼音或筆劃進行升序或降序排列，用戶選項數據的單元格中也將自動同步更新。

（3）模糊選項。該單位有上萬名員工，有可能會出現姓名相同的情況，但工號一定不會相同。現有兩員工姓名都為“韓向權”，一名在辦公室，一名在數理學院。選定單位名稱為辦公室，在姓名列表中為辦公室的韓向權。另外，辦公室有多位王姓員工，輸入“王”，則在姓名列表中列出所有王姓員工。

3.3 應用總結

在該實例中，該單位的員工有近萬人，如全部為選項數據，不方便用戶使用。這里采用分兩層選項的方法，很好地解決了此問題。第一個選項為部門，第二個選項為所屬部門員工，從而大幅減少了選項數目，操作上也更適應用戶習慣。系統已通過上述測試，運行正常。

4 結語

Excel 提供了強大的數據處理平臺，操作界面友好，用戶使用方便。除界面上的功能外，還有500 多個函數及大量現成的加載程序，可協作完成各種數據處理工作。為滿足用戶的個性化需求，Excel 提供了功能擴展接口及VBA 程序開發環境，前者可加載宏代碼或其它相關程序，后者可利用Excel 平臺開發用戶自己的程序。為充分利用其強大的數據處理能力，Excel 提供了數據導入接口，支持各類數據庫文件導入Excel 中進行數據處理，也支持SQL 語言對導入的數據進行預處理。

本文設計了一個模糊篩選系統，利用多個函數及其相應功能，包括表對象、數組公式等，還有各種函數的調用嵌套配合。可以這樣理解，Excel 提供了各種數據處理工具，這些工具有些存在于界面菜單中，有些存在于函數中，用戶可根據具體情況和具體問題對其進行靈活組合，針對性地解決如數據計算、排序、查詢、篩選、分類、匯總、統計，甚至數據轉圖表等數據處理問題。

本文詳細介紹了模糊篩選系統的設計思想及用Excel方法實現該設計的過程。該系統可分為前臺與后臺：前臺根據部門及姓名可方便、快捷地從一萬多名員工中選出某位員工；后臺可對基礎數據進行日常維護，以適應基礎數據的變更與增刪。系統不僅提高了信息輸入效率，而且減少了信息輸入工作量、降低了出錯率，還能很好地適應基礎數據的變化需要。如果用戶需要用其它數據進行模糊篩選，只需替換一下基礎數據即可，而不需要重新設計，因此具有較好的可維護性和復用性。