Excel菜單功能及函數(shù)在表格處理中的應(yīng)用探索

王維利

摘要：Excel在教學(xué)管理、賬務(wù)管理等工作的表格數(shù)據(jù)處理中應(yīng)用廣泛，巧妙使用Excel的函數(shù)、功能，如VLOOKUP、RANK等函數(shù)及排序、分類匯總、數(shù)據(jù)透視表等功能，并根據(jù)處理要求使用適當(dāng)?shù)倪\(yùn)算符及自定義表達(dá)式，會(huì)提高表格數(shù)據(jù)處理的效率和準(zhǔn)確性。

關(guān)鍵詞：Excel;函數(shù);公式;分類匯總;名次;VLOOKUP;RANK;拆分工作簿

中圖分類號：TP317? ? ? 文獻(xiàn)標(biāo)識碼：A

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

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

1從相關(guān)表中提取匹配數(shù)據(jù)

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

VLOOKUP函數(shù)的語法格式為：VLOOKUP （待匹配值，包含返回值和匹配值的關(guān)聯(lián)數(shù)據(jù)區(qū)域，返回值在關(guān)聯(lián)區(qū)域中的列序號，匹配方式 1/TRUE-近似或0/FALSE-精確）。

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

1.1提取匹配的一列數(shù)據(jù)

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

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

操作中，請注意引用關(guān)聯(lián)數(shù)據(jù)區(qū)域時(shí)須使用絕對引用，以防止將含有關(guān)聯(lián)數(shù)據(jù)區(qū)域相對引用的公式復(fù)制到其他單元格，導(dǎo)致在錯(cuò)誤的數(shù)據(jù)區(qū)域中進(jìn)行匹配而取出不合理的數(shù)據(jù)值。

1.2提取多列數(shù)據(jù)

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

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

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

2）使用VLOOKUP函數(shù)提取學(xué)籍表中匹配行的G列數(shù)據(jù)放入學(xué)生成績表的相應(yīng)列;

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

2以多列數(shù)據(jù)作為關(guān)鍵字進(jìn)行分類匯總

這個(gè)任務(wù)可通過如下操作實(shí)現(xiàn)：使用字符串連接運(yùn)算符&連接需要作為分類關(guān)鍵字的多列數(shù)據(jù)，作為新列放到數(shù)據(jù)表中，然后依據(jù)此列進(jìn)行所需的數(shù)據(jù)分類匯總。

例如，在學(xué)生評教表中有每個(gè)學(xué)生對所有任課教師的評分，且存在同一教師給多個(gè)班級上同一門課的情況，若需分別統(tǒng)計(jì)各班學(xué)生對各教師所任教課程的評價(jià)平均分，則需以班級、課程、教師為關(guān)鍵字進(jìn)行分類匯總。具體操作如下：

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

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

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

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

3排名問題

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

3.1使用RANK函數(shù)實(shí)現(xiàn)

RANK函數(shù)的語法格式為：RANK（待排位的值，排位的數(shù)據(jù)區(qū)域，排位方式），其中排位方式指定要進(jìn)行升序排位還是降序排位，0-按降序排位（默認(rèn)），不等于0的值-按升序排位。

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

操作方法：先在第一個(gè)數(shù)據(jù)行的“名次”列單元格中輸入公式：=RANK（B2，$B$2：$B$10，0），確定后再將公式復(fù)制到其他需要填入排名的單元格中。排名數(shù)據(jù)區(qū)域一般要用絕對引用方式，以免公式復(fù)制到其他單元格后得到錯(cuò)誤排名，因?yàn)樗行械呐琶际窍鄬τ谕唤M數(shù)據(jù)值而言的。

3.2使用自定義表達(dá)式實(shí)現(xiàn)

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

1）對要排名數(shù)據(jù)按排名規(guī)則進(jìn)行排序，再在第1行數(shù)據(jù)的“名次”單元格中輸入1;

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

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

使用這種方法與直接使用RANK函數(shù)得到的排名一般是相同的，但有一種情況例外：當(dāng)參與排名的數(shù)據(jù)有相同值時(shí)，如[5，3，3，2]這組數(shù)據(jù)，數(shù)據(jù)值2用表達(dá)式進(jìn)行排名的結(jié)果是3，而用RANK函數(shù)操作的結(jié)果是4。

3.3依據(jù)多列數(shù)據(jù)進(jìn)行排名

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

1）將數(shù)據(jù)表按排名規(guī)則排序時(shí)，需按多列進(jìn)行排序，注意關(guān)鍵字列的先后順序;

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

操作完成后的結(jié)果如圖2所示。

4識別關(guān)鍵字列值相同的行

如需識別某列數(shù)據(jù)值相同的行，可先按關(guān)鍵字列排序，再合理使用表達(dá)式檢測。

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

1）按上述關(guān)鍵字段所在列進(jìn)行排序，使這些字段值相同的數(shù)據(jù)行相鄰;

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

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

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

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

5將數(shù)據(jù)表拆分成多個(gè)獨(dú)立工作簿

拆分工作表為獨(dú)立工作簿的步驟如下：1）按拆分依據(jù)列排序;2）將數(shù)據(jù)拆分成多個(gè)工作表;3）將工作簿中的各個(gè)工作表拆分成獨(dú)立的工作簿。

例如，有教師評價(jià)班級數(shù)據(jù)表，含有部門、班級、平均評價(jià)分、參評教師數(shù)4列，需將表中數(shù)據(jù)按部門拆分成獨(dú)立的工作簿，可按如下方法進(jìn)行操作。

5.1將表中數(shù)據(jù)按部門列進(jìn)行排序

使用排序功能操作。

5.2利用插入數(shù)據(jù)透視表實(shí)現(xiàn)按部門拆分成多個(gè)工作表

1）插入-數(shù)據(jù)透視表，將數(shù)據(jù)透視表設(shè)置為插入到當(dāng)前工作表中。

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

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

4）在“數(shù)據(jù)透視表工具-選項(xiàng)”頁面中設(shè)置如下：在“數(shù)據(jù)透視表”區(qū)域中，選擇“選項(xiàng)-顯示報(bào)表篩選頁”，并設(shè)置報(bào)表篩選頁字段為“部門”。

5.3將各個(gè)工作表拆分成獨(dú)立工作簿

1）右鍵單擊工作表標(biāo)簽，選擇“查看代碼”，在出現(xiàn)的代碼框中將如下代碼復(fù)制進(jìn)去。

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默認(rèn)格式

ActiveWorkbook.Close

Next

MsgBox "文件已經(jīng)被分拆完畢！"

End Sub

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

6結(jié)束語

本文針對Excel表格處理中的部分問題，結(jié)合實(shí)際工作經(jīng)驗(yàn)，對關(guān)聯(lián)數(shù)據(jù)提取、數(shù)據(jù)排名、分類匯總、工作表拆分成工作簿等問題的操作方法進(jìn)行了探索、整理，以期對今后的數(shù)據(jù)處理工作有所啟發(fā)和幫助。

參考文獻(xiàn)：

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

[2] 李博.Excel 數(shù)據(jù)匹配研究[J].現(xiàn)代信息科技， 2010， 3（1）： 13-15.

[3] 潘皎. 淺析EXCEL函數(shù)在學(xué)生信息管理中的應(yīng)用[J]. 黑龍江教育學(xué)院學(xué)報(bào)，2019，38（10）：148-150.

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

【通聯(lián)編輯：王力】