Excel菜單功能及函數在表格處理中的應用探索

王維利

摘要：Excel在教學管理、賬務管理等工作的表格數據處理中應用廣泛，巧妙使用Excel的函數、功能，如VLOOKUP、RANK等函數及排序、分類匯總、數據透視表等功能，并根據處理要求使用適當的運算符及自定義表達式，會提高表格數據處理的效率和準確性。

關鍵詞：Excel;函數;公式;分類匯總;名次;VLOOKUP;RANK;拆分工作簿

中圖分類號：TP317? ? ? 文獻標識碼：A

文章編號：1009-3044（2020）23-0079-03

在日常辦公事務中，經常需要使用Excel對含有大量數據的表格進行處理，如果主要依靠肉眼判斷、對比和人工編輯，極易出現處理錯漏，并且效率極低。作者總結在近幾年工作中解決Excel數據處理方面部分問題的一些有效做法，并以實例說明，以期與廣大讀者共享。

1從相關表中提取匹配數據

在數據處理中，有時需從幾個相關表中提取某列值匹配的數據行中的內容，合并生成一個新表。此問題可通過合理使用VLOOKUP函數、文本連接運算符&、分列功能解決。

VLOOKUP函數的語法格式為：VLOOKUP （待匹配值，包含返回值和匹配值的關聯數據區域，返回值在關聯區域中的列序號，匹配方式 1/TRUE-近似或0/FALSE-精確）。

例如，學生成績表中只有學號、課程代碼、成績等數據，而公布成績時，需要將學生的姓名、班級、院系及課程名稱、學分等數據跟各科成績放在同一表格中，學生的詳細信息存在于學籍表中，而課程信息在課程信息表中，此時就需要從這兩個表中找到匹配行，提取出所需列的值放入學生成績表中。下面分兩種情況說明提取數據的操作方法。

1.1提取匹配的一列數據

這種情況可直接使用VLOOKUP函數提取所需數據填入指定位置。

例如，現需將學生姓名添加到學生成績表中，先在成績表中的學號、成績兩列之間插入新列——姓名，使表中A、B、C列分別為學號、姓名、成績，然后在單元格B2中輸入公式=VLOOKUP（A2，學籍表！$A$2：$B$8，2，0），即從學籍表（A、B列分別為學號、姓名）的$A$2：$B$8區域中精確查找學生成績表中A2單元格中的學號值，并從關聯區域中學號值匹配的數據行第2列中取值，數據放入公式所在單元格中。然后將此公式復制到學生成績表的姓名列中需要填入數據的其他單元格，即可達到要求。

操作中，請注意引用關聯數據區域時須使用絕對引用，以防止將含有關聯數據區域相對引用的公式復制到其他單元格，導致在錯誤的數據區域中進行匹配而取出不合理的數據值。

1.2提取多列數據

這種情況既可使用多次提取單列數據的方式完成，也可設法一次性提取多列數據，并進行必要的處理實現。第二種方法的操作過程是：先在要從中提取數據的源表中，將需要提取的多列數據使用特定分隔符（如*）進行連接并把結果放入一新列中，并按照提取單列數據的方法將所需數據放入目標表，再用數據分列功能將放入的數據拆分成所需的多列。

例如，現要將學生的姓名、班級、學院三列的信息加入成績表中，可按如下步驟操作：

1）在學籍表（B、C、E列依次為姓名、班級、學院）中將這三列數據連接后放在一個新列G（列標題為“姓名班級學院”）中。在單元格G2中輸入公式=B2&"*"&C2&"*"&E2，確定后用復制公式的方法把其他各行中的姓名、班級、學院信息也連接后放到此列中;

2）使用VLOOKUP函數提取學籍表中匹配行的G列數據放入學生成績表的相應列;

3）使用數據分列功能將學生成績表中“姓名班級學院”列拆分成三列。先將此列中的數據以“粘貼值”的方式復制到新列中，再用“數據-分列”功能拆分列，注意要使用*號作為分隔符號進行分列。

2以多列數據作為關鍵字進行分類匯總

這個任務可通過如下操作實現：使用字符串連接運算符&連接需要作為分類關鍵字的多列數據，作為新列放到數據表中，然后依據此列進行所需的數據分類匯總。

例如，在學生評教表中有每個學生對所有任課教師的評分，且存在同一教師給多個班級上同一門課的情況，若需分別統計各班學生對各教師所任教課程的評價平均分，則需以班級、課程、教師為關鍵字進行分類匯總。具體操作如下：

1）將各行中班級、課程代碼、教師工號的值連接后放于表格的新列——班級課程教師;

2）以“班級課程教師”列為分類字段，對“評價分”字段進行平均值匯總;

3）將匯總數據復制到新工作表中，以便進行其他處理。方法是：隱藏明細數據，只顯示匯總數據，選擇所需的匯總數據，將工作表中的定位條件設置為“可見單元格”，用復制、粘貼的方法將匯總數據復制到新工作表中。

如果在匯總數據表中需要對原通過連接操作生成的分類字段進行還原，則須在對分類依據的多個字段值進行連接時放入特定的分隔符號，那么此時只需按照指定分隔符進行數據分列即可。

3排名問題

排名問題，即按照表格中某一列或幾列數據的升序（或降序）給出各數據行的位次，如班級將所有學生按成績排定名次、生產企業按月產量對各個車間進行排名等，需要在名次列中填入對應的名次。這類問題可使用函數和公式兩種辦法來解決，下面分別進行說明。

3.1使用RANK函數實現

RANK函數的語法格式為：RANK（待排位的值，排位的數據區域，排位方式），其中排位方式指定要進行升序排位還是降序排位，0-按降序排位（默認），不等于0的值-按升序排位。

例如，要對如圖1所示的成績表按總成績進行排名。

操作方法：先在第一個數據行的“名次”列單元格中輸入公式：=RANK（B2，$B$2：$B$10，0），確定后再將公式復制到其他需要填入排名的單元格中。排名數據區域一般要用絕對引用方式，以免公式復制到其他單元格后得到錯誤排名，因為所有行的排名都是相對于同一組數據值而言的。

3.2使用自定義表達式實現

使用自定義表達式可按如下過程操作：

1）對要排名數據按排名規則進行排序，再在第1行數據的“名次”單元格中輸入1;

2）在第2行數據“名次”格中輸入公式：=IF（B3=B2，K2，K2+1），確定后即可填入對應的名次;

3）將上述公式復制到“名次”列中其他需填入名次值的單元格中。

使用這種方法與直接使用RANK函數得到的排名一般是相同的，但有一種情況例外：當參與排名的數據有相同值時，如[5，3，3，2]這組數據，數據值2用表達式進行排名的結果是3，而用RANK函數操作的結果是4。

3.3依據多列數據進行排名

在工作中，有時會遇到需要按照多列數據排名的問題，如成績表中的排名規則是：優先按總分排名，若總分相同，則按英語排名，若英語也相同再按電子電路進行排名。這種情況，難以直接使用RANK函數實現，而使用自定義表達式就比較方便。操作過程與上述使用表達式按單列數據排名相同，但有兩點要注意：

1）將數據表按排名規則排序時，需按多列進行排序，注意關鍵字列的先后順序;

2）在第2行的“名次”單元格中輸入的公式需修改如下：

操作完成后的結果如圖2所示。

4識別關鍵字列值相同的行

如需識別某列數據值相同的行，可先按關鍵字列排序，再合理使用表達式檢測。

例如，為各班級排完課表后，得到如下安排表，現需判斷哪些班級是合班上課。已知判斷不同行政班合班上課的規則是：周次、星期、節次、課程、教師值均相同。此時可按如下方法進行操作：

1）按上述關鍵字段所在列進行排序，使這些字段值相同的數據行相鄰;

2）在H3單元格輸入公式：=AND（C3=C2，D3=D2，E3=E2，F3=F2，G3=G2），確定后將此公式復制到H列后面的單元格，即可在上述關鍵字值相同的一組數據行中除第一行外顯示TRUE;

3）在I2單元格輸入公式：=H3，確定后將公式復制到I列其余單元格，即可將關鍵字值相等的多行中的第1行也標記為TRUE;

4）在J2單元格中輸入公式：=IF（OR（I2，J2），"合班"，"不合班"），確定后將公式復制到J列其他單元格，即可正確標識出各班課程安排是否合班的情況。結果如圖3所示。

上述方法也可用來檢測課表安排中班級時間沖突（按班級、周次、星期、節次排序后使用公式操作，但使用上述公式時需適當修改）、教師時間沖突（按教師、周次、星期、節次排序后使用公式操作）等情況。

5將數據表拆分成多個獨立工作簿

拆分工作表為獨立工作簿的步驟如下：1）按拆分依據列排序;2）將數據拆分成多個工作表;3）將工作簿中的各個工作表拆分成獨立的工作簿。

例如，有教師評價班級數據表，含有部門、班級、平均評價分、參評教師數4列，需將表中數據按部門拆分成獨立的工作簿，可按如下方法進行操作。

5.1將表中數據按部門列進行排序

使用排序功能操作。

5.2利用插入數據透視表實現按部門拆分成多個工作表

1）插入-數據透視表，將數據透視表設置為插入到當前工作表中。

2）在“數據透視表字段”框中合理分布各字段：以部門為篩選字段，班級為行字段，平均評價分、參評教師數為值字段，值字段的匯總方式為“平均值”（也可適當使用其他方式）。

3）在“數據透視表工具-設計”頁面中設置如下：分類匯總-不顯示;總計-對行和列禁用;報表布局-以表格形式顯示。

4）在“數據透視表工具-選項”頁面中設置如下：在“數據透視表”區域中，選擇“選項-顯示報表篩選頁”，并設置報表篩選頁字段為“部門”。

5.3將各個工作表拆分成獨立工作簿

1）右鍵單擊工作表標簽，選擇“查看代碼”，在出現的代碼框中將如下代碼復制進去。

Private Sub 分拆工作表（）

Dim sht As Worksheet

Dim MyBookAs Workbook

Set MyBook = ActiveWorkbook

For Each sht In MyBook.Sheets

sht.Copy

ActiveWorkbook.SaveAs Filename：=MyBook.Path& "＼" &sht.Name， FileFormat：=xlNormal? ? ?'將工作簿另存為EXCEL默認格式

ActiveWorkbook.Close

Next

MsgBox "文件已經被分拆完畢！"

End Sub

2）運行代碼，方法是使用“運行-運行子過程/用戶窗體”菜單項或按F5功能鍵，即可將工作表中的數據按部門拆分到獨立的工作簿中。

6結束語

本文針對Excel表格處理中的部分問題，結合實際工作經驗，對關聯數據提取、數據排名、分類匯總、工作表拆分成工作簿等問題的操作方法進行了探索、整理，以期對今后的數據處理工作有所啟發和幫助。

參考文獻：

[1] 江紅， 余青松. Excel數據處理與分析教程[M]. 北京：清華大學出版社， 2015.

[2] 李博.Excel 數據匹配研究[J].現代信息科技， 2010， 3（1）： 13-15.

[3] 潘皎. 淺析EXCEL函數在學生信息管理中的應用[J]. 黑龍江教育學院學報，2019，38（10）：148-150.

[4] 吳瑩. Excel中VLOOKUP和MATCH函數的應用[J]. 電腦知識與技術， 2019，15（2）：276-277.

【通聯編輯：王力】