1 小黑：30；未來(lái)教育：29

人事部專員小金負(fù)責(zé)本公司員工檔案的日常管理，以及員工每年各項(xiàng)基本社會(huì)保險(xiǎn)費(fèi)用的計(jì)算。按照下列要求幫助小金完成相關(guān)數(shù)據(jù)的整理、計(jì)算、統(tǒng)計(jì)和分析工作：

1.將考生文件夾下的工作簿文檔“Excel素材.xlsx”另存為“Excel.xlsx”（".xlsx"為文件擴(kuò)展名），之后所有的操作均基于此文件，否則不得分。操作過(guò)程中，不可以隨意改變?cè)ぷ鞅硭夭臄?shù)據(jù)的順序。

2.在工作表“身份證校對(duì)”中按照下列規(guī)則及要求對(duì)員工的身份證號(hào)進(jìn)行正誤校對(duì)：

①中國(guó)公民的身份證號(hào)由18位組成，最后一位即第18位為校驗(yàn)碼，通過(guò)前17位計(jì)算得出。第18位校驗(yàn)碼的計(jì)算方法是：

將身份證的前17位數(shù)分別與對(duì)應(yīng)系數(shù)相乘，將乘積之和除以11，所得余數(shù)與最后一位校驗(yàn)碼一一對(duì)應(yīng)。從第1位到第17位的對(duì)應(yīng)系數(shù)以及余數(shù)與校驗(yàn)碼對(duì)應(yīng)關(guān)系參見(jiàn)工作表“校對(duì)參數(shù)”中所列。

②首先在工作表“身份證校對(duì)”中將身份號(hào)的18位數(shù)字自左向右分拆到對(duì)應(yīng)列。

可以vlookup函數(shù)：以不同顏色突出顯示文本>字符底紋>底紋

也可以分列來(lái)做：

數(shù)據(jù)-分列-一個(gè)數(shù)字后面分一下-點(diǎn)完成

③通過(guò)前17位數(shù)字以及工作表“校對(duì)參數(shù)”中的校對(duì)系數(shù)計(jì)算出校驗(yàn)碼，填入V列中。

sumproduct函數(shù)：

返回相應(yīng)數(shù)組或區(qū)域乘積的和。

=SUMPRODUCT(array1, [array2], [array3], ...)。

=SUMPRODUCT(D3:T3*校對(duì)參數(shù)!$E$5:$U$5)。

mod函數(shù)：

返回兩數(shù)相除的余數(shù)。=mod(被除數(shù)，除數(shù))，取余數(shù)。

=mod(nExp1,nExp2)。

=MOD(SUMPRODUCT(D3:T3*校對(duì)參數(shù)!$E$5:$U$5),11)。

vlookup函數(shù)：

搜索表區(qū)域首列滿足條件的元素，確定待檢索單元格在區(qū)域中的行序號(hào)，再進(jìn)一步返回選定單元格的值。默認(rèn)情況下，表是以升序排序的。

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])。

=vlookup(mod(sumproduct(d3:t3*校對(duì)參數(shù)!$e$5:$u$5),11),校對(duì)參數(shù)!$b$4:$c$15,2,false)

④將原證號(hào)的第18位與計(jì)算出的校驗(yàn)碼進(jìn)行對(duì)比，比對(duì)結(jié)果填入W列，要求比對(duì)相符時(shí)輸入文本“正確”，不符時(shí)輸入“錯(cuò)誤”。

if函數(shù)：

判斷是否滿足某個(gè)條件，如果滿足返回一個(gè)值，如果不滿足則返回另一個(gè)值。

=IF(logical_test,value_if_true,value_if_false)。

=IF(U3=V3,"正確","錯(cuò)誤")

⑤如果校對(duì)結(jié)果錯(cuò)誤，則通過(guò)設(shè)置條件格式將錯(cuò)誤證號(hào)所在的數(shù)據(jù)行以“紅色”文字、淺綠類型的顏色填充。

設(shè)置條件格式：

先選中整個(gè)區(qū)域-開(kāi)始-條件格式-新建規(guī)則-使用公式確定要設(shè)置格式的單元格- =$W3="錯(cuò)誤"-設(shè)置格式

3.在工作表“員工檔案”中，按照進(jìn)行下列要求對(duì)員工檔案數(shù)據(jù)表進(jìn)行完善：

①輸入每位員工的身份證號(hào)，員工編碼與身份證號(hào)的對(duì)應(yīng)關(guān)系見(jiàn)工作表“身份證校對(duì)”。如果已校對(duì)出錯(cuò)誤，應(yīng)將正確的身份證號(hào)填寫入“員工檔案”中（假設(shè)所有錯(cuò)誤號(hào)碼都是由于最后一位校驗(yàn)碼輸錯(cuò)導(dǎo)致的）。

不用判斷正確or錯(cuò)誤，只需要取前18位+最后1位計(jì)算得到的校驗(yàn)碼。

vlookup函數(shù)：（見(jiàn)30-2）

=VLOOKUP(A3,身份證校對(duì)!B:C,2,0)。

left函數(shù)：

從文本字符串的第一個(gè)字符開(kāi)始返回指定個(gè)數(shù)的字符。

=LEFT(VLOOKUP(A3,身份證校對(duì)!B:C,2,0),17)。

&連接前后的數(shù)字

文本變成常規(guī)----公式本身變成結(jié)果。

②計(jì)算每位員工截止2016年12月31日的年齡，每滿一年才計(jì)算一歲，一年按365天計(jì)算。

yearfrac函數(shù)：

返回一個(gè)年分?jǐn)?shù)，表示兩個(gè)日期之間的整天天數(shù)。

=YEARFRAC(start_date,end_date,basis)。

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

直接減（因?yàn)橐荒臧凑?65天來(lái)算的）：

("日期"-日期)/365求年份，再用int函數(shù)取整。

int函數(shù)：

將數(shù)值向下取整為最接近的整數(shù)。

=int(number)。

=INT(("2016-12-31"-E3)/365)。

③在“工作狀態(tài)”列的空白單元格中填入文本“在職"。

替換or定位條件都用不了（被保護(hù)了……）。

篩選空白的-填上在職。

④計(jì)算每位員工在本公司工作的工齡，要求不足半年按半年計(jì)、超過(guò)半年按一年計(jì)，一年按365天計(jì)算，保留一位小數(shù)。其中，“在職“員工的工齡計(jì)算截止于2016年12月31日，離職和退休人員計(jì)算截止于各自離職或退休的時(shí)間。

ceiling函數(shù)：

將參數(shù)向上舍入為最接近的指定基數(shù)的倍數(shù)。

CEILING(number, significance)。

Number：必需參數(shù)，表示要舍入的值。

Significance：必需參數(shù)，表示要舍入到的倍數(shù)。

CEILING(("2016-12-31"-J3)/365,0.5)。

CEILING((K3-J3)/365,0.5)。

if函數(shù)：判斷是否在職。

=IF(L3="在職",CEILING(("2016-12-31"-J3)/365,0.5),CEILING((K3-J3)/365,0.5))。

注：要設(shè)置單元格格式為數(shù)字型，因?yàn)樘岬搅吮Ａ粢晃恍?shù)嗎？

⑤計(jì)算每位員工的工齡工資，公式：工齡工資=本公司工齡×50。

⑥計(jì)算員工的工資總額，公式：工資總額=工齡工資+簽約工資+上年月均獎(jiǎng)金。

4.在工作表“社保計(jì)算”中，按照下列要求計(jì)算每個(gè)員工本年度每月應(yīng)繳社保金額：

①依據(jù)工作表“員工檔案”中的數(shù)據(jù)，篩選出所有“在職”員工的“員工編號(hào)”、“姓名”和“工資總額”三列數(shù)據(jù)，依次填入B、C、D中，并按員工編號(hào)由小到大排序。

由小到大：升序；由大到?。航敌?。

②本市上年職工平均月工資為7086元，首先將其定義為常量“人均月工資”，然后依據(jù)下列規(guī)則計(jì)算出每位員工的“社?；鶖?shù)”填入相應(yīng)E列中，計(jì)算時(shí)需要在公式中調(diào)用新定義的常量“人均月工資”：社保基數(shù)最低為人均月工資7086元的60%，最高為人均月工資7086元的3倍：

公式-定義名稱-填名稱和引用位置。定義常量之后可以直接在公式里面寫名字。

if函數(shù)：

=IF(D4<人均月工資*0.6,人均月工資*0.6,IF(D4>人均月工資*3,人均月工資*3,D4))

③每個(gè)人每個(gè)險(xiǎn)種的應(yīng)繳社保費(fèi)=個(gè)人的社保基數(shù)×相應(yīng)的險(xiǎn)種費(fèi)率，按照工作表“社保費(fèi)率”中所列險(xiǎn)種費(fèi)率分別計(jì)算每位在職員工應(yīng)繳的各險(xiǎn)種費(fèi)用，包括公積負(fù)擔(dān)和個(gè)人負(fù)擔(dān)部分。其中：醫(yī)療個(gè)人負(fù)擔(dān)=社保基數(shù)×醫(yī)療個(gè)人負(fù)擔(dān)比例+個(gè)人額外費(fèi)用3元

不能直接點(diǎn)養(yǎng)老公司負(fù)責(zé)比例，要先點(diǎn)一個(gè)空格，再改成那個(gè)。

可以只改行數(shù)，先公司后個(gè)人。

④為數(shù)據(jù)表設(shè)置恰當(dāng)?shù)臄?shù)字格式，套用一個(gè)表格格式并取消自動(dòng)篩選標(biāo)記。

ctrl+1：調(diào)整單元格格式。

套用表格格式要注意：表包含標(biāo)題要選上、可以在表設(shè)計(jì)里面取消篩選。

5.以工作表“社保計(jì)算”的結(jié)果為數(shù)據(jù)源，參照下列圖1所示樣例，自新工作表“透視分析”的A3單元格開(kāi)始生成數(shù)據(jù)透視表，要求如下：

①列標(biāo)題應(yīng)與示例圖相同。

怎么拖入。

分組：4200-22200（跨度是3000）。單擊右鍵-組合。

百分比：?jiǎn)螕粲益I-總數(shù)的百分比。

計(jì)數(shù)項(xiàng)、求和項(xiàng)。

②按圖中所示調(diào)整工資總額的數(shù)字格式。

③改變數(shù)據(jù)透視表樣式。