-
HR必會(huì)的26個(gè)Excel工作小技巧
2019-12-27 01:09:04HR在工作中常常要與 Excel 打交道，了解其中的使用技巧并能靈活運(yùn)用一些函數(shù)公式對(duì)于提高工作效率大有助益，小編今天就為各位HR總結(jié)了一些使用技巧，快來了解一下吧。 1 從身份證號(hào)碼提取生日 案例場(chǎng)景截圖如下（黃色區(qū)域輸入公式）
=TEXT(MID(A2,7,8),"0-00-00") 2 根據(jù)身份證號(hào)碼計(jì)算退休日期 案例場(chǎng)景截圖如下（黃色區(qū)域輸入公式）
=DATE(MID(A2,7,4)+55+MOD(MID(A2,17,1),2)*5,MID(A2,11,2),MID(A2,13,2)) 3 檢查身份證號(hào)碼是否重復(fù) 案例場(chǎng)景截圖如下（黃色區(qū)域輸入公式）
=IF(COUNTIF($B$2:$B$11,B2&"*")>1,"重復(fù)","") 4 根據(jù)身份證號(hào)碼判斷性別 案例場(chǎng)景截圖如下（黃色區(qū)域輸入公式）
=IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女") 5 根據(jù)身份證號(hào)碼實(shí)現(xiàn)生日提醒 案例場(chǎng)景截圖如下（黃色區(qū)域輸入公式）
=TEXT(7-DATEDIF(--TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2.2),8),"0-00-00"),NOW()+7,"yd"),"0天后生日;;今天生日") 還有其他提取方式： 提取屬相
=CHOOSE(MOD(MID(A1,7,4)-1900,12)+1,"鼠","牛","虎","兔","龍","蛇","馬","羊","猴","雞","狗","豬")
提取星座
=VLOOKUP(--TEXT(MID(A1,11,4),"2015-00-00"),{0,"摩羯";42024,"水瓶";42054,"雙魚";42084,"白羊";42114,"金牛";42145,"雙子";42177,"巨蟹";42208,"獅子";42239,"處女";42270,"天秤";42301,"天蝎";42330,"射手";42360,"摩羯"},2)
6 根據(jù)分項(xiàng)得分和所占權(quán)重計(jì)算KPI績(jī)效最后得分 案例場(chǎng)景截圖如下（黃色區(qū)域輸入公式）
=SUMPRODUCT(B$2:E$2*B3:E3) 7 去掉一個(gè)最高分、去掉一個(gè)最低分后再計(jì)算平均分 案例場(chǎng)景截圖如下（黃色區(qū)域輸入公式）
=TRIMMEAN(B2:H2,2/COUNT(B2:H2)) 8 個(gè)人所得稅計(jì)算 假如A2中是應(yīng)稅工資，則計(jì)算個(gè)稅公式為 =5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},) 9 統(tǒng)計(jì)兩個(gè)日期之間的工作日天數(shù) 一提到工作日計(jì)算，很多HR都皺緊了眉頭，因?yàn)楦鞣N崗位員工的排班不同，休息日不同，再加上員工的請(qǐng)假和各種節(jié)假日的調(diào)休......99%的HR都是在手動(dòng)計(jì)算工作日中苦苦煎熬。 看完下面這個(gè)示例的解決方案，以上所有問題一掃而光！
上圖所示為按照周六周日雙休的企業(yè)中，加上自定義節(jié)假日的工作日算法 首先定義名稱，按
然后在C2輸入公式： =NETWORKDAYS.INTL(A2,B2,1,holidays) 這樣就輕松計(jì)算出起始日期和終止日期之間的工作日天數(shù)了 為了大家能夠舉一反三，我再多啰嗦幾句，解析一下原理。 NETWORKDAYS.INTL函數(shù)的語法結(jié)構(gòu)如下： =NETWORKDAYS.INTL(起始日期,終止日期,周末日weekend,自定義節(jié)假日holidays) 關(guān)鍵點(diǎn)在于第三參數(shù)和第四參數(shù)。
關(guān)于第3參數(shù)weekend第二種表達(dá)方式的幾點(diǎn)說明： 1、weekend 的表達(dá)方式2是一個(gè)長(zhǎng)度為7的字符串
2、該字符串中的每個(gè)字符代表一周中的一天，從星期一開始
3、1 代表休息日，0 代表工作日
4、該字符串中只允許使用字符 1 和 0
關(guān)于第4參數(shù)holidays的幾點(diǎn)說明： 1、是一個(gè)包含一個(gè)或多個(gè)日期的可選集合，這些日期將作為節(jié)假日不參與工作日個(gè)數(shù)統(tǒng)計(jì)
2、可以是包含日期的單元格區(qū)域，也可以是區(qū)域的引用，還可以是代表日期序列值的數(shù)組常量
3、其中的日期或序列值順序可以任意打亂，不影響統(tǒng)計(jì)。
希望能夠幫到大家輕松應(yīng)對(duì)各種情況的工作日計(jì)算！ 10 根據(jù)工齡計(jì)算年假天數(shù)的4種方法 【職工帶薪年休假條例】 根據(jù)職工累計(jì)工作年限而定： 1、已滿1年不滿10年的，年休假5天;
2、已滿10年不滿20年的，年休假10天;
3、已滿20年的，年休假15天。
案例場(chǎng)景截圖如下（黃色區(qū)域輸入公式）
方法1=IF(B2<1,0,IF(B2<10,5,IF(B2<20,10,15)))
方法2=LOOKUP(B2,{0,1,10,20},{0,5,10,15})
方法3=5*MIN(3,ROUNDUP(B2/9.9,0))
方法4=5*MIN(3,CEILING(B2/9.9,1))
11 日期和星期同時(shí)顯示 設(shè)置單元格格式-數(shù)字-自定義: yyyy-m-d aaaa
12 快速復(fù)制表格 按Ctrl不松開往下拖動(dòng)表格，即可快速復(fù)制表格。
13 多個(gè)單元格批量輸入同一內(nèi)容 選中要輸入內(nèi)容的單元格，輸入內(nèi)容，最后按【Ctrl+Enter】結(jié)束輸入。 小提示：不連續(xù)的單元格，可以按【Ctrl】選中。學(xué)會(huì)用這16個(gè)Excel神技巧，零基礎(chǔ)都不用怕！
14 跨表格復(fù)制粘貼 按Shift鍵選取多個(gè)工作表，選中需要復(fù)制的表格區(qū)域，開始-填充-成組工作表-填充“全部”。
15 快速將多行內(nèi)容匯總到同一個(gè)單元格 利用【填充】功能下的“兩端對(duì)齊”，可以實(shí)現(xiàn)內(nèi)容合并的功能：拉寬列-選中需要復(fù)制的單元格內(nèi)容-開始-填充-兩端對(duì)齊。
16 多個(gè)單元格內(nèi)容合并為一個(gè)單元格且分行顯示 主要利用剪貼板功能：復(fù)制多個(gè)單元格-雙擊要粘貼的單元格-點(diǎn)擊剪貼板中已復(fù)制的內(nèi)容。
17 不復(fù)制隱藏的行 按【Alt+; 】鍵，選取顯示的行，然后復(fù)制、粘貼，即可避免復(fù)制隱藏掉的行。
18 自動(dòng)設(shè)置小數(shù)點(diǎn) 輸入數(shù)字之前先在單元格內(nèi)點(diǎn)擊百分比樣式%，輸入數(shù)字后再點(diǎn)擊千位分隔符樣式。
19 利用名稱框快速填充內(nèi)容 這里我們可以利用Excel的名稱框?qū)崿F(xiàn)快速填充200行的內(nèi)容：在名稱框中輸入A1:A200，按回車鍵，系統(tǒng)自動(dòng)選中A1:A200的單元格。 然后輸入內(nèi)容，自動(dòng)填充第一個(gè)空白單元格。最后按【Ctrl+Enter】組合鍵，完成批量錄入。
20 快速顯示表格中的公式 我們可以按【Ctrl+~】組合鍵進(jìn)行公式的顯示或隱藏，不過這個(gè)快捷鍵經(jīng)常被輸入法占用。 所以也可以直接點(diǎn)擊【公式】選項(xiàng)卡中的“顯示公式”進(jìn)行切換。
21 快速批量新建文件夾 在A列中把需要新建文件夾的文件名錄進(jìn)去，然后在B1中輸入公式="MD "&A1 （注意：MD后面一定要有個(gè)空格）。 按Enter鍵后，光標(biāo)移到B1單元格右下角，顯示“+”時(shí)下拉填充，然后把B列的數(shù)據(jù)復(fù)制到記事本中，單擊另存為，把.txt文檔后綴改為.bat，雙擊運(yùn)行該文件即可。
22 快速關(guān)閉多個(gè)Excel工作簿 按Shift鍵不松，單擊右上角關(guān)閉按鈕，即可關(guān)鍵所有已經(jīng)打開工作簿。
23 快速核對(duì)同行數(shù)據(jù) 有些人比較習(xí)慣的做法是使用查詢函數(shù)比如Vlookup進(jìn)行查詢匹配。 其實(shí)我們只需要一個(gè)快捷鍵就可完成數(shù)據(jù)的核對(duì)。 選取表格兩列，按【Ctrl + 】即可，還可以填充背景顏色突出顯示。
24 快速求和 只需按住快捷鍵組合【Alt+=】即可實(shí)現(xiàn)快速求和。
25 并排查看兩個(gè)工作簿 如果要同時(shí)查看兩個(gè)工作簿，可以先打開兩個(gè)工作簿，然后點(diǎn)擊 “視圖→并排窗口”。 26 快速刪除值保留公式 一般情況下我們可以通過【Ctrl+G】定位條件設(shè)置來完成，其實(shí)還可以直接選取非公式值：開始 - 查找和選擇 - 常量 - 刪除。
上面這26個(gè)小技巧，都是我們?cè)贓xcel使用過程中經(jīng)常要用到的，操作很簡(jiǎn)單，但非常實(shí)用，如果你能熟練掌握這些技巧，能解決工作中絕大部分的問題。
