常用的excel函数公式
1、计算性别(F列)=IF(MOD(MID(E3,17,1),2),"男","女")
2、出生年月(G列)
=TEXT(MID(E3,7,8),"0-00-00")
3、年龄公式(H列)
=DATEDIF(G3,TODAY(),"y")
4、退休日期(I列)
=TEXT(EDATE(G3,12*(5*(F3="男")+55)),"yyyy/mm/dd aaaa")
5、籍贯(M列)
=VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,)
注：附带示例中有地址库代码表
6、社会工龄(T列)
=DATEDIF(S3,NOW(),"y")
7、公司工龄(W列)
=DATEDIF(V3,NOW(),"y")&"年"&DATEDIF(V3,NOW(),"ym")&"月"&DATEDIF(V3,NOW(),"md")&"天"
8、合同续签日期(Y列)
=DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1
9、合同到期日期(Z列)
=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY(),"[
10、工龄工资(AA列)
=MIN(700,DATEDIF($V3,NOW(),"y")*50)
11、生肖(AB列)
=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(E3,7,4),12)+1,1)
二、员工考勤表公式
1、本月工作日天数(AG列)
=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)
2、调休天数公式(AI列)
=COUNTIF(B9:AE9,"调")
3、扣钱公式(AO列)
婚丧扣10块，病假扣20元，事假扣30元，矿工扣50元
=SUM((B9:AE9={"事";"旷";"病";"丧";"婚"})*)
三、员工数据分析公式
1、本科学历人数
=COUNTIF(D:D,"本科")
2、办公室本科学历人数
=COUNTIFS(A:A,"办公室",D:D,"本科")
3、30~40岁总人数
=COUNTIFS(F:F,">=30",F:F,"
四、其他公式
1、提成比率计算
=VLOOKUP(B3,$C$12:$E$21,3)
2、个人所得税计算
假如A2中是应税工资，则计算个税公式为：
=5*MAX(A2*%-,)
3、工资条公式
=CHOOSE(MOD(ROW(A3),3)+1,工资数据源!A$1,OFFSET(工资数据源!A$1,INT(ROW(A3)/3),,),"")
注：
A3:标题行的行数+2,如果标题行在第3行，则A3改为A5
工资数据源!A$1:工资表的标题行的第一列位置
4、Countif函数统计身份证号码出错的解决方法
由于Excel中数字只能识别15位内的，在Countif统计时也只会统计前15位，所以很容易出错。不过只需要用 &"*" 转换为文本型即可正确统计。
=Countif(A:A,A2&"*")
绛旓細COUNT A鍑芥暟锛氳绠楀尯鍩熷唴闈炵┖鍗曞厓鏍兼暟閲 COUNT A(璁℃暟鍖哄煙)COUNT IF鍑芥暟锛氳绠楃鍚堝崟涓潯浠剁殑鏁伴噺 COUNT IF(鏉′欢鍖哄煙锛 鏉′欢)COUNT IFS鍑芥暟锛氳绠楃鍚堝涓潯浠剁殑鏁伴噺 COUNT IFS(鏉′欢鍖哄煙1锛 鏉′欢1锛 鏉′欢鍖哄煙2锛屾潯浠2...)3.鍖归厤鍑芥暟 LOOKUP鍑芥暟锛氳鏌愪釜鍊煎搴旂殑鍏朵粬鍖哄煙涓殑鍊 LOOKUP(鏌ヨ鍊硷紝 鏌ユ壘...
绛旓細1銆乻um姹傚拰鍑芥暟 鐢ㄦ硶=sum(姹傚拰鍖哄煙)锛屾瘮濡俿um(A1:B2,D1:E2)锛屽氨鏄眰A1:B2鍜孌1:E2涓や釜鍖哄煙鍐呮墍鏈夋暟鍊肩殑鍜岋紱2銆乴eft浠庡乏杈瑰彇鎸囧畾闀垮害瀛楃涓插嚱鏁 鐢ㄦ硶=left(瀛楃涓叉垨鎵鍦ㄥ崟鍏冩牸锛岄暱搴︼級锛屾瘮濡俵eft(A1,1)锛屽氨鏄粠A1鐨勫乏杈瑰彇涓浣嶆枃鏈紝渚嬪A1涓槸鈥淎BC鈥濓紝鍒欐鍏紡缁撴灉涓篈銆3銆text鏂囨湰鏍煎紡鍑...
绛旓細鍏紡锛欳2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))璇存槑:鐢–OUNTIF鍑芥暟缁熻鍑烘簮鏁版嵁涓瘡浜虹殑鍑虹幇娆℃暟锛屽苟鐢1闄ょ殑鏂瑰紡鎶婂彉鎴愬垎鏁帮紝鏈鍚庡啀鐩稿姞銆傚洓銆佹暟鎹眰鍜屽叕寮 1銆侀殧鍒楁眰鍜岀殑搴旂敤 鍏紡锛欻3=SUMIF($A$2:$G$2,H$2,A3:G3)鎴=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)璇存槑锛氬鏋滃湪鏍囬...
绛旓細excel甯哥敤鍏紡鍑芥暟鏈塈F鍑芥暟SUMIFS鍑芥暟COUNTIFVLOOKUP鍑芥暟锛孡OOKUP鍑芥暟 1IF鍑芥暟 IF鍑芥暟涓鑸槸鎸囩▼搴忚璁℃垨Excel绛夎蒋浠朵腑鐨勬潯浠跺嚱鏁帮紝鏍规嵁鎸囧畾鐨勬潯浠舵潵鍒ゆ柇鍏垛滅湡鈥漈RUE鈥滃亣鈥滷ALSE锛屾牴鎹昏緫璁＄畻鐨勭湡鍋囧硷紝浠庤岃繑鍥炵浉搴旂殑鍐呫備笅闈㈡垜椹笂缁欏ぇ瀹跺垎浜獷xcel甯哥敤鐢靛瓙琛ㄦ牸鍏紡锛屽笇鏈涘ぇ瀹堕兘鑳藉浼氬苟杩愮敤璧锋潵 Excel甯哥敤鐢靛瓙...
绛旓細excel甯哥敤鍑芥暟澶у叏1銆丼UM鍑芥暟锛歋UM鍑芥暟鐨勪綔鐢ㄦ槸姹傚拰銆傚嚱鏁板叕寮忎负=sum锛堬級渚嬪锛氱粺璁′竴涓崟鍏冩牸鍖哄煙锛=sum(A1:A10)缁熻澶氫釜鍗曞厓鏍煎尯鍩燂細=sum(A1:A10,C1:C10)2銆丄VERAGE鍑芥暟:Average鐨勪綔鐢ㄦ槸璁＄畻骞冲潎鏁般傚嚱鏁板叕寮忎负=AVERAGE()渚嬪锛氳绠楀涓崟鍏冩牸鐨勫钩鍧囧=AVERAGE(A1:A12锛3銆count鍑芥暟锛氫綔鐢ㄦ槸璁＄畻鍗曞厓...
绛旓細excel鍑芥暟鍏紡澶у叏锛1銆両F鍑芥暟锛欼F鍑芥暟鏄渶甯哥敤鐨鍒ゆ柇绫诲嚱鏁颁箣涓锛岀洰鐨勬槸鍒ゆ柇闈炴鍗冲郊銆傚叕寮忥細锛滻F(A2>4,鈥滄槸鈥濓紝鈥滃惁鈥濓級銆2銆佹潯浠舵眰鍜屽嚱鏁帮細鍏紡锛氾紳SUMIF(鏉′欢鍖哄煙锛屾寚瀹氱殑姹傚拰鏉′欢锛屾眰鍜岀殑鍖哄煙锛夛細鐢ㄩ氫織鐨勮瘽鎻忚堪灏辨槸锛氬鏋淏2:B9鍖哄煙鐨勫肩瓑浜庘滄槸鈥濓紝灏卞A2:A9鍗曞厓鏍煎搴旂殑鍖哄煙姹傚拰銆3銆佹潯浠...
绛旓細Excel鍑芥暟鍏紡澶у叏 宸ヤ綔涓渶甯哥敤Excel鍑芥暟鍏紡澶у叏涓銆佹暟瀛楀鐞1銆佸彇缁濆鍊=ABS(鏁板瓧)2銆佸彇鏁=INT(鏁板瓧)3銆佸洓鑸嶄簲鍏=ROUND(鏁板瓧,灏忔暟浣嶆暟)浜屻佸垽鏂叕寮1銆佹妸鍏紡浜х敓鐨勯敊璇兼樉绀轰负绌哄叕寮忥細C2=IFERROR(A2/B2,)璇存槑锛氬鏋滄槸閿欒鍊煎垯鏄剧ず涓虹┖锛屽惁鍒欐甯告樉绀恒2銆両F澶氭潯浠跺垽鏂繑鍥炲煎叕寮忥細C2=IF(AND...
绛旓細3銆佹渶甯哥敤鐨凟xcel鍑芥暟鍏紡闆躲侀殢鏈烘暟闅忔満鏁板嚱鏁帮細=RAND()棣栧厛浠嬬粛涓涓嬪浣曠敤RAND()鍑芥暟鏉ョ敓鎴愰殢鏈烘暟(鍚屾椂杩斿洖澶氫釜鍊兼椂鏄笉閲嶅鐨)銆俁AND()鍑芥暟杩斿洖鐨勯殢鏈烘暟瀛楃殑鑼冨洿鏄ぇ浜0灏忎簬1銆4銆佸叕寮忎竴锛欰VERAGE姹傚钩鍧囧煎嚱鏁拌绠椾竴缁勬暟鎹殑骞冲潎鍊兼暟鎹夋嫨涓涓崟鍏冿紝鐢ㄦ潵鏄剧ず骞冲潎鍊兼暟鎹湪fx鍏紡杈撳叆鏍忛噷杈硅緭鍏ワ細=...
绛旓細1銆鍑芥暟鍚嶇О锛欰BS 涓昏鍔熻兘锛氭眰鍑虹浉搴旀暟瀛楃殑缁濆鍊笺備娇鐢ㄦ牸寮忥細ABS(number)鍙傛暟璇存槑锛歯umber浠ｈ〃闇瑕佹眰缁濆鍊肩殑鏁板兼垨寮曠敤鐨勫崟鍏冩牸銆傚簲鐢ㄤ妇渚嬶細濡傛灉鍦˙2鍗曞厓鏍间腑杈撳叆鍏紡锛=ABS(A2)锛屽垯鍦ˋ2鍗曞厓鏍间腑鏃犺杈撳叆姝ｆ暟锛堝100锛夎繕鏄礋鏁帮紙濡-100锛夛紝B2涓潎鏄剧ず鍑烘鏁帮紙濡100锛夈傜壒鍒彁閱掞細濡傛灉number鍙傛暟涓嶆槸...
绛旓細鍏紡锛欶2=SUMIF(A:A,C:C)璇存槑锛氳繖鏄疭UMIF鍑芥暟鐨勬渶鍩虹鐨勭敤娉 浜斻佹煡鎵句笌寮曠敤鍏紡 1銆佸崟鏉′欢鏌ユ壘 璇存槑锛歏LOOKUP鏄excel涓鏈甯哥敤鐨鏌ユ壘鏂瑰紡 鍏佸瓧绗︿覆澶勭悊鍏紡 1銆佸鍗曞厓鏍煎瓧绗︿覆鐨勫悎骞 璇存槑锛歅honetic鍑芥暟鍙兘鍚堝苟瀛楃鍨嬫暟鎹紝涓嶈兘鍚堝苟鏁板 2銆佹埅鍙栫粨鏋3浣嶄箣澶栫殑閮ㄥ垎 璇存槑锛歀EN璁＄畻鎬婚暱搴,LEFT浠...
