甯哥敤鐢靛瓙琛ㄦ牸鍏紡
1
銆佹煡鎵鹃噸澶嶅唴瀹瑰叕寮忥細
=IF(COUNTIF(A:A,A2)>1,"
閲嶅
","")
銆?/p>
2
銆佺敤鍑虹敓骞存湀鏉ヨ绠楀勾榫勫叕寮忥細
=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)
銆?/p>
3
銆佷粠杈撳叆鐨?/p>
18
浣?/p>
韬唤璇佸彿
鐨勫嚭鐢熷勾鏈堣绠楀叕寮忥細
=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))
銆?/p>
4
銆佷粠杈撳叆鐨?/p>
韬唤璇佸彿鐮?/p>
鍐呰绯荤粺鑷姩鎻愬彇鎬у埆锛屽彲浠ヨ緭鍏ヤ互涓嬪叕寮忥細
=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"
鐢?/p>
","
濂?
"),IF(MOD(MID(C2,17,1),2)=1,"
鐢?/p>
","
濂?/p>
"))
鍏紡鍐呯殑鈥淐2鈥濅唬琛ㄧ殑鏄緭鍏ヨ韩浠?/p>
璇佸彿鐮佺殑鍗曞厓鏍笺�?/p>
1
銆佹眰鍜岋細
=SUM(K2:K56)
鈥斺�斿
K2
鍒?/p>
K56
杩欎竴鍖哄煙杩涜姹傚拰锛?/p>
2
銆?/p>
骞冲潎鏁?/p>
锛?/p>
=AVERAGE(K2:K56)
鈥斺�斿
K2 K56
杩欎竴鍖哄煙姹傚钩鍧囨暟锛?/p>
3
銆佹帓鍚嶏細
=RANK(K2
锛?/p>
K$2:K$56)
鈥斺�斿
55
鍚嶅鐢熺殑鎴愮哗杩涜鎺掑悕锛?/p>
4
銆佺瓑绾э細
=IF(K2>=85,"
浼?/p>
",IF(K2>=74,"
鑹?/p>
",IF(K2>=60,"
鍙婃牸
","
涓嶅強鏍?/p>
")))
5
銆佸鏈熸�昏瘎锛?/p>
=K2*0.3+M2*0.3+N2*0.4
鈥斺�斿亣璁?/p>
K
鍒椼�?/p>
M
鍒楀拰
N
鍒楀垎鍒瓨鏀剧潃
瀛︾敓鐨勨�滃钩鏃舵�昏瘎鈥濄�佲�滄湡涓�濄�佲�滄湡鏈�濅笁椤规垚缁╋紱
6
銆佹渶楂樺垎锛?/p>
=MAX(K2:K56)
鈥斺�旀眰
K2
鍒?/p>
K56
鍖哄煙锛?/p>
55
鍚嶅鐢燂級鐨勬渶楂樺垎锛?/p>
7
銆佹渶浣庡垎锛?/p>
=MIN(K2:K56)
鈥斺�旀眰
K2
鍒?/p>
K56
鍖哄煙锛?/p>
55
鍚嶅鐢燂級鐨勬渶浣庡垎锛?/p>
8
銆佸垎鏁版浜烘暟缁熻锛?/p>
锛?/p>
1
锛?/p>
=COUNTIF(K2:K56,"100")
鈥斺�旀眰
K2
鍒?/p>
K56
鍖哄煙
100
鍒嗙殑浜烘暟锛涘亣璁炬妸缁?/p>
鏋滃瓨鏀句簬
K57
鍗曞厓鏍硷紱
锛?/p>
2
锛?/p>
=COUNTIF(K2:K56,">=95")
锛?/p>
K57
鈥斺�旀眰
K2
鍒?/p>
K56
鍖哄煙
95
锝?/p>
99.5
鍒嗙殑
浜烘暟锛涘亣璁炬妸缁撴灉瀛樻斁浜?/p>
K58
鍗曞厓鏍硷紱
锛?/p>
3
锛?/p>
=COUNTIF(K2:K56,">=90")
锛?/p>
SUM(K57:K58)
鈥斺�旀眰
K2
鍒?/p>
K56
鍖哄煙
90
锝?/p>
94.5
鍒嗙殑浜烘暟锛涘亣璁炬妸缁撴灉瀛樻斁浜?/p>
K59
鍗曞厓鏍硷紱