|
要說Excel中的神公式,，我今天介紹的3條公式,，基本能夠解決多數(shù)人的80%以上的大麻煩,。有人也許會想這些公式是不是很高級很難，其實不然,，這都是些常見的函數(shù),，只要我們理解了思路，是可以輕松寫出來的,。 一,、一條公式快速從身份證號碼里提取性別信息HR常常需要從身份證號碼中提取出每個人的性別信息，而我們都知道身份證號碼的長度往往有2種,，一種是18位的,，另一種則是15位。其中18位身份證號碼的倒數(shù)第二位數(shù)字代表一個人的性別（偶數(shù)代表女,，奇數(shù)代表男）,；而15位身份證號碼則是最后一位數(shù)字代表性別信息，同樣也是偶數(shù)代表女生,，奇數(shù)代表男生,。我們如何快速地將代表性別的數(shù)字提取出來并得出性別信息呢？ 思路： 1.提取代表性別的數(shù)字 =LEFT(RIGHT(B2,1+(LEN(B2)=18)*1)) 我們知道：要么是倒數(shù)第二位的數(shù)字（身份證號碼為18位）,，要么是倒數(shù)第一位（15位身份證號碼）的數(shù)字代表性別,，因此我該提取1位還是2位數(shù)字，是由身份證號碼的長度決定的,。因此我們可以得到公式（(LEN(B2)=18)*1是將邏輯值true或者false轉化為1或者0）： =RIGHT(B2,1+(LEN(B2)=18)*1) 然后我們再在上述公式得到的結果的基礎之上,，往左邊取一位即可得到代表性別的數(shù)字了。 2.根據(jù)得到的數(shù)字判斷其奇偶性從而返回性別信息 =MOD(LEFT(RIGHT(B2,1+(LEN(B2)=18)*1)),2) Mod返回的結果為0或者1,，偶數(shù)時返回為0,，奇數(shù)時返回1。接下來我們通過if函數(shù)返回性別信息即可,。 =IF(MOD(LEFT(RIGHT(B2,1+(LEN(B2)=18)*1)),2),'男','女') 動畫演示： 二,、簡單好用的一條公式批量返回查詢結果要我說，沒有比這個更好的Excel公式了,。如下圖所示,，我們需要將右側表格中的學生成績快速地返回到左側表中，該如何快速地完成這個任務呢,？ 沒錯,，很多朋友想到了vlookup函數(shù)，這的確是一個非常棒的函數(shù),，尤其是搭配match函數(shù)一起使用時?，F(xiàn)在我們就用經典實用的vlookup+match組合來搞定它。 思路：懂點vlookup函數(shù)的朋友都知道，完成這個公式并不難,，難的是用一條公式完成,。他們知道：vlookup函數(shù)共計4個參數(shù)，在這個例子第一個參數(shù)和第二個參數(shù)都固定不變,，注意引用即可,，第四個參數(shù)為0，無需變動,，唯一麻煩的是第三個參數(shù),，每寫一列都要更改一次。現(xiàn)在我們用match函數(shù)去自動返回A表中的每一列在B表中的位置,，公式如下： =match(B$1,$M$1:$S$1,0) 當公式向右復制時,，Excel會自動查找每一科成績在B表中的位置。然后我們在套上基礎的vlookup函數(shù)即可,。操作步驟：選中A2:G39,在編輯欄輸入公式： =VLOOKUP($A2,$M$2:$S$39,MATCH(B$1,$M$1:$S$1,),) 三,、一條公式快速搞定個人所得稅計算計算每個人的個人所得稅是咱們財務人員或者HR必不可少的工作技能。那么如何快速搞定個人所得稅呢,？個人所得稅的征收規(guī)定是這樣的：你的稅前工資交過扣除五險一金后,，工資額大于3500就需要交納個人所得稅了。具體的稅率表如下： 具體的計算方式如下： 應納稅所得額 = 稅前工資收入金額 － 五險一金(個人繳納部分) － 起征點(3500元) 如下圖所示： 這里的稅前工資是扣除了五險一金以后的,。這里我們用其減掉3500即可得到應繳納所得稅額,。然后根據(jù)所得稅額來計算咱們每一個人的個人所得稅。 =B2-3500 我們先根據(jù)規(guī)定制作如下圖的一個表格： 然后根據(jù)這個表格通過vlookup函數(shù)或者lookup函數(shù)返回其對應的稅率,，然后乘以應繳納所得稅額,，最后在通過vlookup函數(shù)或者lookup函數(shù)返回其對應的速算扣除數(shù)即可得到所得稅： 稅率：=LOOKUP(B2-3500,$F$2:$G$8)或者=VLOOKUP(B3-3500,$F$2:$G$8,2) 速算扣除數(shù)：=VLOOKUP(B3-3500,$F$2:$H$8,3)或者=LOOKUP(B2-3500,$F$2:$H$8) 個人所得稅公式為： =LOOKUP(B2-3500,$F$2:$G$8)*(B2-3500)-LOOKUP(B2-3500,$F$2:$H$8) 或者 =VLOOKUP(B3-3500,$F$2:$G$8,2)*(B3-3500)-VLOOKUP(B3-3500,$F$2:$H$8,3) 然而，收入不到稅點時,，Excel會返回錯誤值,，因此我們需要在外層嵌套一個iferror函數(shù)，如下： =IFERROR(LOOKUP(B2-3500,$F$2:$G$8)*(B2-3500)-LOOKUP(B2-3500,$F$2:$H$8),'') 或者 =IFERROR(VLOOKUP(B3-3500,$F$2:$G$8,2)*(B3-3500)-VLOOKUP(B3-3500,$F$2:$H$8,3),'')
|
|
|
