請根據題目背景和下列要求完成操作。
行政部張橋負責本公司員工檔案的日常管理,以及員工每年各項基本社會保險費用的計算,請根據如下要求,幫助張橋完成相關數據的整理、計算、統計和分析工作:

(1)在考生文件夾下,將工作簿文檔“Excel素材.xlsx”另存為“Excel.xlsx”(“.xlsx”為文件擴展名),后續操作均基于此文件,否則不得分。操作過程中,不可以隨意改變原工作表素材數據的順序。
(2)按照如下要求,在工作表“身份證校對”中對員工的身份證號進行正誤校對:
①中國公民的身份證號由18位組成,最后一位即第18位為校驗碼,通過前17位計算得出。第18位校驗碼的計算方法是:將身份證的前17位數分別與對應系數相乘,將乘積之和除以11,所得余數與最后一位校驗碼一一對應。從第1位到第17位的對應系數以及余數與校驗碼對應關系參見工作表“校對參數”中所列。
②首先在工作表“身份證校對”中將身份號的18位數字自左向右分拆到對應列。
③通過前17位數字以及工作表“校對參數”中的校對系數計算出校驗碼,填入V列中。
④將原證號的第18位與計算出的校驗碼進行對比,比對結果填入W列,要求比對相符時輸入文本“正確”,不符時輸入“錯誤”。
⑤如果校對結果錯誤,則通過設置條件格式將錯誤證號所在的數據行以“紅色”文字、淺綠類型的顏色填充。
(3)按照如下要求,在工作表“員工檔案”中對員工檔案數據表進行完善:
①輸入每位員工的身份證號,員工編號與身份證號的對應關系見工作表“身份證校對”。如果已校對出錯誤,應將正確的身份證號填寫入工作表“員工檔案”中(假設所有錯誤號碼都是由于最后一位校驗碼輸錯導致的)。
②計算每位員工截止2016年12月31日的年齡,每滿一年才計算一歲,一年按365天計算。
③在“工作狀態”列的空白單元格中填入文本“在職”。
④計算每位員工在本公司工作的工齡,要求不足半年按半年計、超過半年按一年計,一年按365天計算,保留一位小數。其中,“在職”員工的工齡計算截止于2016年12月31日,離職和退休人員計算截止于各自離職或退休的時間。
⑤計算每位員工的工齡工資,公式:工齡工資=本公司工齡×50。
⑥計算員工的工資總額,公式:工資總額=工齡工資+簽約工資+上年月均獎金。
(4)按照如下要求,在工作表“社保計算”中計算每個員工本年度每月應繳社保金額:
①依據工作表“員工檔案”中的數據,篩選出所有“在職”員工的“員工編號”、“姓名”和“工資總額”三列數據,依次填入B、C、D中,并按員工編號由小到大排序。
②本市上年職工平均月工資為7086元,首先將其定義為常量“人均月工資”,然后依據下列規則計算出每位員工的“社?;鶖怠碧钊胂鄳狤列中,計算時需要在公式中調用新定義的常量“人均月工資”:社?;鶖底畹蜑槿司鹿べY7086元的60%,最高為人均月工資7086元的3倍:
menu.saveimg.savepath20190409133802.jpg
③每個人每個險種的應繳社保費=個人的社?;鶖怠料鄳碾U種費率,按照工作表“社保費率”中所列險種費率分別計算每位在職員工應繳的各險種費用,包括公司負擔和個人負擔部分。其中:醫療個人負擔=社?;鶖怠玲t療個人負擔比例+個人額外費用3元。
④為數據表設置恰當的數字格式,套用一個表格格式并取消自動篩選標記。
(5)按照如下要求,以工作表“社保計算”的結果為數據源,參照下列圖1所示樣例,自新工作表“透視分析”的A3單元格開始生成數據透視表:
①列標題應與示例圖相同。
②按圖中所示調整工資總額的數字格式。
③改變數據透視表樣式。

image.png




被同桌摸到高潮好爽H