不管是高校、企業(yè)還是政府機關(guān)，每年完成單位各種各樣的人事情況統(tǒng)計、制作人員名冊的工作都是必不可少的。SQL Server、Access等數(shù)據(jù)庫軟件入門門檻較高，不適合普通工作人員使用。筆者在不斷摸索中發(fā)現(xiàn)了用Excel數(shù)組公式進(jìn)行人員名冊管理、統(tǒng)計的巧妙方法，現(xiàn)介紹如下。
本文將以制作高校教師年底統(tǒng)計名冊、報表的實例來向大家介紹具體的操作。我們還要求當(dāng)增加或刪除員工記錄后，報表能自動更新。
一、準(zhǔn)備數(shù)據(jù)
1．將現(xiàn)有的教師名冊從數(shù)據(jù)庫中導(dǎo)出，存為Excel表格，根據(jù)名冊要求調(diào)整格式，制作成人員名冊；
2．在菜單中選擇“插入→名稱→定義”，分別定義各個列的含義，這樣我們才可以在公式中直接引用該列的集合。例如：“單位”引用位置是“=名單!$A$2:$A$344”表示在名單這個表單內(nèi)A列第2行到344行的這個范圍內(nèi)的單元格集合，我們稱為單位。定義好我們需要的名稱后保存工作表。
3．選擇“插入→工作表”選項，插入一個新工作表，并更名為“學(xué)院情況統(tǒng)計”。在表格內(nèi)建立表頭和需要的統(tǒng)計欄目。所有準(zhǔn)備工作到此結(jié)束，下面我們將為表格填充內(nèi)容。
1．首先進(jìn)行各學(xué)院總?cè)藬?shù)的統(tǒng)計。在學(xué)院1的總?cè)藬?shù)欄中填入公式“=COUN TIF(單位,“學(xué)院1”)”（不含引號，下同）。COUNTIF能計算區(qū)域中滿足給定條件的單元格的個數(shù)。標(biāo)準(zhǔn)格式為COUNTIF(Range,Criteria)，Range為滿足條件的單元格區(qū)域。Criteria為確定哪些單元格將被計算在內(nèi)的條件，其形式可以是數(shù)字、表達(dá)式、單元格引用或文本。COUNTIF (單位,“學(xué)院1”)含義為統(tǒng)計單位集合中內(nèi)容為“學(xué)院1”的單元格總數(shù)。
2． 在學(xué)院1的女欄目中輸入“=SUM((單位="學(xué)院1")*(性別="女"))”，輸入完畢后，按下“Ctrl+Shift+Enter”鍵，生成數(shù)組公式{=SUM((單位="學(xué)院1")*(性別="女"))}，計算結(jié)構(gòu)也自動顯示在單元格內(nèi)。公式的含義為計算單位為“學(xué)院1”且性別為“女”的單元格總數(shù)。“*”代表“與”關(guān)系。
依此類推，在學(xué)院1后面單元格依次填入下列公式：
博士：=SUM((單位="學(xué)院1")*(學(xué)歷="博士"))
碩士：=SUM((單位="學(xué)院1")*(學(xué)歷="碩士"))
研究生比例：不屬于統(tǒng)計內(nèi)容，只要利用公式計算（博士+碩士）/總?cè)藬?shù)即可。
填寫完學(xué)院1所有公式后，利用復(fù)制功能將學(xué)院1的所有公式復(fù)制到學(xué)院2的公式欄目。再利用替換功能，將學(xué)院2所有公式單元格的內(nèi)容中“學(xué)院1”替換為“學(xué)院2”，則可以得到學(xué)院2的公式?？芍貜?fù)使用，填寫完其他單元格，最后利用匯總功能完成合計欄。這樣，一個統(tǒng)計報表就生成了。
3． 數(shù)組公式與數(shù)組運算不同，數(shù)組公式通常以“Ctrl+Shift+Enter”結(jié)束，在不能使用工作表函數(shù)直接得到結(jié)果時，數(shù)組公式顯得特別重要，它可產(chǎn)生多值或?qū)σ唤M值進(jìn)行操作，也可以生成更復(fù)雜的公式。
如：求學(xué)院1中正高級職務(wù)人員中有博士學(xué)位且性別為女，年齡在35歲以下的人數(shù)，我們可以寫為：
=SUM((單位=“學(xué)院1”)*(學(xué)歷=“博士”)*(性別=“女”)*(年齡三、總結(jié)
利用Excel的數(shù)組公式制作名冊和統(tǒng)計報表大大降低了統(tǒng)計難度，也方便了數(shù)據(jù)的共享。利用數(shù)據(jù)保護(hù)功能將公式單元格保護(hù)起來形成模板，分發(fā)給其他人，使用者只要懂得簡單的Excel編輯知識就可以使用。今年的統(tǒng)計工作完成后，明年只要將新數(shù)據(jù)復(fù)制到名冊內(nèi)，就自動形成了新一年的統(tǒng)計名冊。大大簡化了年終統(tǒng)計帶來的煩惱。
本文將以制作高校教師年底統(tǒng)計名冊、報表的實例來向大家介紹具體的操作。我們還要求當(dāng)增加或刪除員工記錄后，報表能自動更新。
一、準(zhǔn)備數(shù)據(jù)
1．將現(xiàn)有的教師名冊從數(shù)據(jù)庫中導(dǎo)出，存為Excel表格，根據(jù)名冊要求調(diào)整格式，制作成人員名冊；
2．在菜單中選擇“插入→名稱→定義”，分別定義各個列的含義，這樣我們才可以在公式中直接引用該列的集合。例如：“單位”引用位置是“=名單!$A$2:$A$344”表示在名單這個表單內(nèi)A列第2行到344行的這個范圍內(nèi)的單元格集合，我們稱為單位。定義好我們需要的名稱后保存工作表。
3．選擇“插入→工作表”選項，插入一個新工作表，并更名為“學(xué)院情況統(tǒng)計”。在表格內(nèi)建立表頭和需要的統(tǒng)計欄目。所有準(zhǔn)備工作到此結(jié)束，下面我們將為表格填充內(nèi)容。
1．首先進(jìn)行各學(xué)院總?cè)藬?shù)的統(tǒng)計。在學(xué)院1的總?cè)藬?shù)欄中填入公式“=COUN TIF(單位,“學(xué)院1”)”（不含引號，下同）。COUNTIF能計算區(qū)域中滿足給定條件的單元格的個數(shù)。標(biāo)準(zhǔn)格式為COUNTIF(Range,Criteria)，Range為滿足條件的單元格區(qū)域。Criteria為確定哪些單元格將被計算在內(nèi)的條件，其形式可以是數(shù)字、表達(dá)式、單元格引用或文本。COUNTIF (單位,“學(xué)院1”)含義為統(tǒng)計單位集合中內(nèi)容為“學(xué)院1”的單元格總數(shù)。
2． 在學(xué)院1的女欄目中輸入“=SUM((單位="學(xué)院1")*(性別="女"))”，輸入完畢后，按下“Ctrl+Shift+Enter”鍵，生成數(shù)組公式{=SUM((單位="學(xué)院1")*(性別="女"))}，計算結(jié)構(gòu)也自動顯示在單元格內(nèi)。公式的含義為計算單位為“學(xué)院1”且性別為“女”的單元格總數(shù)。“*”代表“與”關(guān)系。
依此類推，在學(xué)院1后面單元格依次填入下列公式：
博士：=SUM((單位="學(xué)院1")*(學(xué)歷="博士"))
碩士：=SUM((單位="學(xué)院1")*(學(xué)歷="碩士"))
研究生比例：不屬于統(tǒng)計內(nèi)容，只要利用公式計算（博士+碩士）/總?cè)藬?shù)即可。
填寫完學(xué)院1所有公式后，利用復(fù)制功能將學(xué)院1的所有公式復(fù)制到學(xué)院2的公式欄目。再利用替換功能，將學(xué)院2所有公式單元格的內(nèi)容中“學(xué)院1”替換為“學(xué)院2”，則可以得到學(xué)院2的公式?？芍貜?fù)使用，填寫完其他單元格，最后利用匯總功能完成合計欄。這樣，一個統(tǒng)計報表就生成了。
3． 數(shù)組公式與數(shù)組運算不同，數(shù)組公式通常以“Ctrl+Shift+Enter”結(jié)束，在不能使用工作表函數(shù)直接得到結(jié)果時，數(shù)組公式顯得特別重要，它可產(chǎn)生多值或?qū)σ唤M值進(jìn)行操作，也可以生成更復(fù)雜的公式。
如：求學(xué)院1中正高級職務(wù)人員中有博士學(xué)位且性別為女，年齡在35歲以下的人數(shù)，我們可以寫為：
=SUM((單位=“學(xué)院1”)*(學(xué)歷=“博士”)*(性別=“女”)*(年齡三、總結(jié)
利用Excel的數(shù)組公式制作名冊和統(tǒng)計報表大大降低了統(tǒng)計難度，也方便了數(shù)據(jù)的共享。利用數(shù)據(jù)保護(hù)功能將公式單元格保護(hù)起來形成模板，分發(fā)給其他人，使用者只要懂得簡單的Excel編輯知識就可以使用。今年的統(tǒng)計工作完成后，明年只要將新數(shù)據(jù)復(fù)制到名冊內(nèi)，就自動形成了新一年的統(tǒng)計名冊。大大簡化了年終統(tǒng)計帶來的煩惱。