excel常用计算方法

❶ 鍦‥xcel涓濡备綍璁＄畻锷犲噺涔橀櫎

琛ㄦ牸閲屾庝箞璁＄畻锷犲噺涔橀櫎锛

1銆佸姞娉曪细

镓揿紑Excel琛ㄦ牸锛岄夋嫨鍗曞厓镙硷纴杈揿叆=銆傞夋嫨鏁板瓧锛岃緭鍏+锛屽啀阃夋嫨鏁板瓧锛屾寜涓嫔洖杞﹂敭镓ц屽姞娉曡＄畻銆

2銆佸噺娉曪细

阃夋嫨鍗曞厓镙硷纴杈揿叆=锛岄夋嫨鏁板瓧锛岃緭鍏-锛屽啀阃夋嫨鏁板瓧锛屾寜涓嫔洖杞﹂敭镓ц屽噺娉曡＄畻銆

3銆佷箻娉曪细

阃夋嫨鍗曞厓镙硷纴杈揿叆=锛岄夋嫨鏁板瓧锛岃緭鍏*锛屽啀阃夋嫨鏁板瓧锛屾寜涓嫔洖杞﹂敭镓ц屼箻娉曡＄畻銆

4銆侀櫎娉曪细

阃夋嫨鍗曞厓镙硷纴杈揿叆=锛岄夋嫨鏁板瓧锛岃緭鍏/锛屽啀阃夋嫨鏁板瓧锛屾寜涓嫔洖杞﹂敭镓ц岄櫎娉曡＄畻銆

鍑芥暟浠嬬粛锛

1銆丆OUNT琛ㄧず镄勬剰镐濇槸璁＄畻鏁板间釜鏁般

2銆丷OUND琛ㄧず镄勬剰镐濇槸锲涜垗浜斿叆銆

3銆丷AND琛ㄧず镄勬剰镐濇槸闅忔満鏁般

4銆丮IN琛ㄧず镄勬剰镐濇槸链灏忓笺

5銆丮AX琛ㄧず镄勬剰镐濇槸链澶у笺

6銆丼UM琛ㄧず镄勬剰镐濇槸姹傚拰銆

7銆丄VERAGE琛ㄧず镄勬剰镐濇槸骞冲潎鏁般

8銆両F琛ㄧず镄勬剰镐濇槸𨱒′欢銆

甯哥敤鍏寮忥细

鍙栫粷瀵瑰=ABS(鏁板瓧)銆

鍙栨暣=INT(鏁板瓧)銆

锲涜垗浜斿叆=ROUND(鏁板瓧锛屽皬鏁颁綅鏁)銆

鎶婂叕寮忎骇鐢熺殑阌栾鍊兼樉绀轰负绌猴纴鍏寮忥细C2=IFERROR(A2/B2,"")銆

IF澶氭浔浠跺垽鏂杩斿洖鍊煎叕寮忥细C2=IF(AND(A2<500,B2="链鍒版湡"),"琛ユ","")銆

缁熻′袱涓琛ㄦ牸閲嶅岖殑鍐呭瑰叕寮忥细B2=COUNTIF(Sheet15!A:A,A2)銆

缁熻′笉閲嶅岖殑镐讳汉鏁板叕寮忥细C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))銆

闅斿垪姹傚拰鍏寮忥细H3=SUMIF($A$2:$G$2,H$2,A3:G3)銆傛垨=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)銆

鍗曟浔浠舵眰鍜屽叕寮忥细F2=SUMIF(A:A,E2,C:C)銆

澶氭浔浠舵ā绯婃眰鍜屽叕寮忥细C11=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)銆

澶氲〃鐩稿悓浣岖疆姹傚拰鍏寮忥细b2=SUM(Sheet1:Sheet19!B2)銆

鎸夋棩链熷拰浜у搧姹傚拰鍏寮忥细F2=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)銆

鍗曟浔浠舵煡镓惧叕寮忓叕寮1锛欳11=VLOOKUP(B11,B3:F7,4,FALSE)銆

鍙屽悜镆ユ垒鍏寮忓叕寮=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))銆

澶氩崟鍏冩牸瀛楃︿覆钖埚苟鍏寮忥细c2=PHONETIC(A2:A7)銆

鎴鍙栭櫎钖3浣崭箣澶栫殑閮ㄥ垎鍏寮忥细=LEFT(D1,LEN(D1)-3)銆

鎴鍙栧墠镄勯儴鍒嗗叕寮忥细B2=Left(A1,FIND("-",A1)-1)銆

鎴鍙栧瓧绗︿覆涓浠讳竴娈电殑鍏寮忓叕寮忥细B1=TRIM(MID(SUBSTITUTE($A1,"",REPT("",20)),20,20))銆

❷ excel琛ㄦ牸甯哥敤鍏寮

涓銆佹暟瀛楀勭悊
1銆佸彇缁濆瑰 =ABS(鏁板瓧)
2銆佸彇鏁 =INT(鏁板瓧)
3銆佸洓鑸崭簲鍏 =ROUND(鏁板瓧,灏忔暟浣嶆暟)
浜屻佸垽鏂鍏寮
1銆佹妸鍏寮忎骇鐢熺殑阌栾鍊兼樉绀轰负绌
鍏寮忥细=IFERROR(A2/B2,"")
璇存槑锛氩傛灉鏄阌栾鍊煎垯鏄剧ず涓虹┖锛屽惁鍒欐ｅ父鏄剧ず銆
2銆両F澶氭浔浠跺垽鏂杩斿洖鍊
鍏寮忥细=IF(AND(A2<500,B2=" 链鍒版湡"),"琛ユ","")
璇存槑锛氢袱涓𨱒′欢钖屾椂鎴愮珛鐢ˋND,浠讳竴涓鎴愮珛鐢∣R鍑芥暟銆
涓夈佺粺璁″叕寮
1銆佺粺璁′袱涓琛ㄦ牸閲嶅岖殑鍐呭
鍏寮忥细=COUNTIF(Sheet15!A:A,A2)
璇存槑锛氩傛灉杩斿洖鍊煎ぇ浜0璇存槑鍦ㄥ彟涓涓琛ㄤ腑瀛桦湪锛0鍒欎笉瀛桦湪銆
2銆佺粺璁′笉閲嶅岖殑镐讳汉鏁
鍏寮忥细=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
璇存槑:鐢–OUNTIF缁熻″嚭姣忎汉镄勫嚭鐜版℃暟锛岀敤1闄ょ殑鏂瑰纺鎶婂嚭鐜版℃暟鍙樻垚鍒嗘瘝锛岀劧钖庣浉锷犮
锲涖佹眰鍜屽叕寮
1銆侀殧鍒楁眰鍜
鍏寮忥细=SUMIF($A$2:$G$2,H$2,A3:G3) 鎴 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
璇存槑锛氩傛灉镙囬樿屾病链夎勫垯鐢ㄧ2涓鍏寮忋
2銆佸崟𨱒′欢姹傚拰
鍏寮忥细=SUMIF(A:A,E2,C:C)
璇存槑锛歋UMIF鍑芥暟镄勫熀链鐢ㄦ硶
3銆佸氭浔浠舵ā绯婃眰鍜
鍏寮忥细=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)
璇存槑锛氩湪sumifs涓鍙浠ヤ娇鐢ㄩ氶厤绗*
5銆佸氲〃鐩稿悓浣岖疆姹傚拰
鍏寮忥细=SUM(Sheet1:Sheet19!B2)
璇存槑锛氩湪琛ㄤ腑闂村垹闄ゆ垨娣诲姞琛ㄥ悗锛屽叕寮忕粨鏋滀细镊锷ㄦ洿鏂般
6銆佹寜镞ユ湡鍜屼骇鍝佹眰鍜
鍏寮忥细=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)
璇存槑锛歋UMPRODUCT鍙浠ュ畬鎴愬氭浔浠舵眰鍜
浜斻佹煡镓句笌寮旷敤鍏寮
1銆佸崟𨱒′欢镆ユ垒鍏寮
鍏寮忥细=VLOOKUP(B11,B3:F7,4,FALSE)
璇存槑锛氭煡镓炬槸VLOOKUP链镎呴暱镄勶纴锘烘湰鐢ㄦ硶
2銆佸弻钖戞煡镓惧叕寮
鍏寮忥细=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))
璇存槑锛氩埄鐢∕ATCH鍑芥暟镆ユ垒浣岖疆锛岀敤INDEX鍑芥暟鍙栧
3銆佹煡镓炬渶钖庝竴𨱒＄﹀悎𨱒′欢镄勮板綍銆
鍏寮忥细
璇存槑锛0/(𨱒′欢)鍙浠ユ妸涓岖﹀悎𨱒′欢镄勫彉鎴愰敊璇鍊硷纴钥宭ookup鍙浠ュ拷鐣ラ敊璇鍊
4銆佹寜鏁板瓧鍖哄烟闂村彇瀵瑰簲镄勫
鍏寮忥细
鍏寮忚存槑锛歏LOOKUP 鍜孡OOKUP鍑芥暟閮藉彲浠ユ寜鍖洪棿鍙栧硷纴涓瀹氲佹敞镒忥纴阌鍞閲忓垪镄勬暟瀛椾竴瀹氲佸崌搴忔帓鍒椼
鍏銆佸瓧绗︿覆澶勭悊鍏寮
1銆佸氩崟鍏冩牸瀛楃︿覆钖埚苟
鍏寮忥细=PHONETIC(A2:A7)
璇存槑锛歅honetic鍑芥暟鍙鑳藉瑰瓧绗﹀瀷鍐呭瑰悎骞讹纴鏁板瓧涓嶅彲浠ャ
2銆佹埅鍙栧乏閮ㄥ垎
鍏寮忥细=LEFT(D1,LEN(D1)-3)
璇存槑锛歀EN璁＄畻鍑烘婚暱搴,LEFT浠庡乏杈规埅镐婚暱搴-3涓
3銆佹埅鍙-鍓岖殑閮ㄥ垎
鍏寮忥细=Left(A1,FIND("-",A1)-1)
璇存槑锛氱敤FIND鍑芥暟镆ユ垒浣岖疆锛岀敤LEFT鎴鍙栥
4銆佹埅鍙栧瓧绗︿覆涓浠讳竴娈电殑鍏寮
鍏寮忥细=TRIM(MID(SUBSTITUTE($A1,"",REPT("",20)),20,20))
璇存槑:鍏寮忔槸鍒╃敤寮烘彃N涓绌哄瓧绗︾殑鏂瑰纺杩涜屾埅鍙
5銆佸瓧绗︿覆镆ユ垒
鍏寮忥细=IF(COUNT(FIND("娌冲崡",A2))=0,"钖","鏄")
璇存槑锛氱敤𨱒ュ垽鏂镆ユ垒鏄钖︽垚锷熴
6銆佸瓧绗︿覆镆ユ垒涓瀵瑰
鍏寮忥细=IF(COUNT(FIND({"杈藉畞","榛戦緳姹","钖夋滠"},A2))=0,"鍏朵粬","涓滃寳")
璇存槑锛氲剧疆FIND绗涓涓鍙傛暟涓哄父閲忔暟缁勶纴鐢–OUNT鍑芥暟缁熻FIND镆ユ垒缁撴灉
涓冦佹棩链熻＄畻鍏寮
1銆佷袱镞ユ湡鐩搁殧镄勫勾銆佹湀銆佸ぉ鏁拌＄畻
A1鏄寮濮嬫棩链燂纸2011-12-1锛夛纴B1鏄缁撴潫镞ユ湡(2013-6-10)銆
璁＄畻锛
鐩搁殧澶氩皯澶╋纻=datedif(A1,B1," d")缁撴灉锛557
鐩搁殧澶氩皯链=datedif(A1,B1," m")缁撴灉锛18
鐩搁殧澶氩皯骞=datedif(A1,B1," Y")缁撴灉锛1
涓嶈冭槛骞寸浉闅斿氩皯链堬纻=datedif(A1,B1," Ym")缁撴灉锛6
涓嶈冭槛骞寸浉闅斿氩皯澶╋纻=datedif(A1,B1," YD")缁撴灉锛192
涓嶈冭槛骞存湀鐩搁殧澶氩皯澶╋纻=datedif(A1,B1," MD")缁撴灉锛9
datedif鍑芥暟绗3涓鍙傛暟璇存槑锛
"Y"镞堕棿娈典腑镄勬暣骞存暟銆
"M"镞堕棿娈典腑镄勬暣链堟暟銆
"D"镞堕棿娈典腑镄勫ぉ鏁般
"MD"澶╂暟镄勫樊銆傚拷鐣ユ棩链熶腑镄勬湀鍜屽勾銆
"YD"澶╂暟镄勫樊銆傚拷鐣ユ棩链熶腑镄勫勾銆
2銆佹墸闄ゅ懆链澶╂暟镄勫伐浣沧棩澶╂暟
鍏寮忥细=NETWORKDAYS.INTL(IF(B2<DATE(2015,1,1),DATE(2015,1,1),B2),DATE(2015,1,31),11)
璇存槑锛氲繑锲炰袱涓镞ユ湡涔嬮棿镄勬墍链夊伐浣沧棩鏁帮纴浣跨敤鍙傛暟鎸囩ず鍝浜涘ぉ鏄锻ㄦ汤锛屼互鍙婃湁澶氩皯澶╂槸锻ㄦ汤銆傚懆链鍜屼换浣曟寚瀹氢负锅囨湡镄勬棩链熶笉琚瑙嗕负宸ヤ綔镞
1銆佹煡镓鹃吨澶嶅唴瀹瑰叕寮忥细=IF(COUNTIF(A:A,A2)>1," 閲嶅","")銆
2銆佺敤鍑虹敓骞存湀𨱒ヨ＄畻骞撮缎鍏寮忥细=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0) 銆
3銆佷粠杈揿叆镄 18浣嶈韩浠借瘉鍙风殑鍑虹敓骞存湀璁＄畻鍏寮忥细=TENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2)) 銆
4銆佷粠杈揿叆镄勮韩浠借瘉鍙风爜鍐呰╃郴缁熻嚜锷ㄦ彁鍙栨у埆锛屽彲浠ヨ緭鍏ヤ互涓嫔叕寮忥细
=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1," 鐢","濂"),IF(MOD(MID(C2,17,1),2)=1," 鐢","濂"))
鍏寮忓唴镄勨淐2钬濅唬琛ㄧ殑鏄杈揿叆韬浠借瘉鍙风爜镄勫崟鍏冩牸銆
5銆佹眰鍜岋细=SUM(K2:K56) 钬斿筀2鍒癒56杩欎竴鍖哄烟杩涜屾眰鍜岋绂
6銆佸钩鍧囨暟锛=AVERAGE(K2:K56) 钬斿筀2K56 杩欎竴鍖哄烟姹傚钩鍧囨暟锛
7銆佹帓钖嶏细=RANK(K2 锛孠$2:K$56) 钬斿55钖嶅︾敓镄勬垚缁╄繘琛屾帓钖嶏绂
8銆佺瓑绾э细=IF(K2>=85," 浼",IF(K2>=74," 镩",IF(K2>=60," 鍙婃牸","涓嶅强镙")))
9銆佸︽湡镐昏瘎锛=K2*0.3+M2*0.3+N2*0.4 钬斿亣璁綤鍒椼丮鍒楀拰N鍒楀垎鍒瀛樻斁镌瀛︾敓镄勨滃钩镞舵昏瘎钬濄佲沧湡涓钬濄佲沧湡链钬濅笁椤规垚缁╋绂
10銆佹渶楂桦垎锛=MAX(K2:K56) 钬旀眰K2鍒癒56鍖哄烟锛55钖嶅︾敓锛夌殑链楂桦垎锛
11銆佹渶浣庡垎锛=MIN(K2:K56) 钬旀眰K2鍒癒56鍖哄烟锛55钖嶅︾敓锛夌殑链浣庡垎锛
12銆佸垎鏁版典汉鏁扮粺璁★细
锛1锛=COUNTIF(K2:K56,"100")钬旀眰K2鍒癒56鍖哄烟100鍒嗙殑浜烘暟锛涘亣璁炬妸缁撴灉瀛樻斁浜嶬57鍗曞厓镙硷绂
锛2锛=COUNTIF(K2:K56,">=95")锛岾57钬旀眰K2鍒癒56鍖哄烟95锝99.5鍒嗙殑浜烘暟锛涘亣璁炬妸缁撴灉瀛樻斁浜嶬58鍗曞厓镙硷绂
锛3锛=COUNTIF(K2:K56,">=90")锛峉UM(K57:K58) 钬旀眰K2鍒癒56鍖哄烟90锝94.5鍒嗙殑浜烘暟锛涘亣璁炬妸缁撴灉瀛樻斁浜嶬59鍗曞厓镙硷绂
锛4锛=COUNTIF(K2:K56,">=85")锛峉UM(K57:K59) 钬旀眰K2鍒癒56鍖哄烟85锝89.5鍒嗙殑浜烘暟锛涘亣璁炬妸缁撴灉瀛樻斁浜嶬60鍗曞厓镙硷绂
锛5锛=COUNTIF(K2:K56,">=70")锛峉UM(K57:K60) 钬旀眰K2鍒癒56鍖哄烟70锝84.5鍒嗙殑浜烘暟锛涘亣璁炬妸缁撴灉瀛樻斁浜嶬61鍗曞厓镙硷绂
锛6锛=COUNTIF(K2:K56,">=60")锛峉UM(K57:K61) 钬旀眰K2鍒癒56鍖哄烟60锝69.5鍒嗙殑浜烘暟锛涘亣璁炬妸缁撴灉瀛樻斁浜嶬62鍗曞厓镙硷绂
锛7锛=COUNTIF(K2:K56,"<60") 钬旀眰K2鍒癒56鍖哄烟60鍒嗕互涓嬬殑浜烘暟锛涘亣璁炬妸缁撴灉瀛樻斁浜嶬63鍗曞厓镙硷绂
璇存槑锛欳OUNTIF 鍑芥暟涔熷彲璁＄畻镆愪竴鍖哄烟鐢枫佸コ鐢熶汉鏁般
濡傦细=COUNTIF(C2:C351," 鐢")钬旀眰C2鍒癈351鍖哄烟锛埚叡 350浜猴级鐢锋т汉鏁帮绂
13銆佷紭绉鐜囷细=SUM(K57:K60)/55*100
14銆佸强镙肩巼锛=SUM(K57:K62)/55*100
15銆佹爣鍑嗗樊锛=STDEV(K2:K56) 钬旀眰K2鍒癒56鍖哄烟(55浜)镄勬垚缁╂尝锷ㄦ儏鍐碉纸鏁板艰秺灏忥纴璇存槑璇ョ彮瀛︾敓闂寸殑鎴愮哗宸寮傝缉灏忥纴鍙崭箣锛岃存槑璇ョ彮瀛桦湪涓ゆ瀬鍒嗗寲锛夛绂
16銆佹浔浠舵眰鍜岋细=SUMIF(B2:B56," 鐢"锛孠2:K56)钬斿亣璁綛鍒楀瓨鏀惧︾敓镄勬у埆锛 K鍒楀瓨鏀惧︾敓镄勫垎鏁帮纴鍒欐ゅ嚱鏁拌繑锲炵殑缁撴灉琛ㄧず姹傝ョ彮鐢风敓镄勬垚缁╀箣鍜岋绂
17銆佸氭浔浠舵眰鍜岋细锝=SUM(IF(C3:C322="鐢",IF(G3:G322=1,1,0))) 锝浓斿亣璁综鍒楋纸C3:C322
鍖哄烟锛夊瓨鏀惧︾敓镄勬у埆锛 G鍒楋纸G3:G322鍖哄烟锛夊瓨鏀惧︾敓镓鍦ㄧ彮绾т唬镰侊纸1銆2銆3銆4銆5锛夛纴鍒欐ゅ嚱鏁拌繑锲炵殑缁撴灉琛ㄧず姹备竴鐝镄勭敺鐢熶汉鏁帮绂杩欐槸涓涓鏁扮粍鍑芥暟锛岃緭瀹屽悗瑕佹寜 Ctrl锛婼hift锛婨nter缁勫悎阌(浜х敓钬滐经 锝浓濄)钬滐经锝浓濅笉鑳芥坠宸ヨ緭鍏ワ纴鍙鑳界敤缁勫悎阌浜х敓銆
18銆佹牴鎹鍑虹敓镞ユ湡镊锷ㄨ＄畻锻ㄥ瞾锛 =TRUNC((DAYS360(D3,NOW()))/360,0)钬斿亣璁綝鍒楀瓨鏀惧︾敓镄勫嚭鐢熸棩链燂纴 E鍒楄緭鍏ヨュ嚱鏁板悗鍒欎骇鐢熻ョ敓镄勫懆宀併
19銆佸湪Word涓涓変釜灏忕獚闂锛
杩炵画杈揿叆涓変釜钬渵钬濆彲寰椾竴𨱒℃尝娴绾裤
杩炵画杈揿叆涓変釜钬-钬濆彲寰椾竴𨱒＄洿绾裤
杩炵画杈揿叆涓変釜钬=钬濆彲寰椾竴𨱒″弻鐩寸嚎銆
1銆佺敤鏁版嵁链夋晥镐у畾涔夋暟鎹闀垮害銆
鐢ㄩ紶镙囬夊畾浣犺佽緭鍏ョ殑鏁版嵁锣冨洿锛岀偣"鏁版嵁"->"链夋晥镐"->"璁剧疆"锛"链夋晥镐ф浔浠"璁炬垚"鍏佽""鏂囨湰闀垮害""绛変簬""5"锛埚叿浣撴浔浠跺彲镙规嵁浣犵殑闇瑕佹敼鍙桡级銆傝缮鍙浠ュ畾涔変竴浜涙彁绀轰俊鎭銆佸嚭阌栾﹀憡淇℃伅鍜屾槸钖︽墦寮涓鏂囱緭鍏ユ硶绛夛纴瀹氢箟濂藉悗镣"纭瀹"銆
2銆佺敤𨱒′欢镙煎纺阆垮厤閲嶅嶃
阃夊畾A鍒楋纴镣"镙煎纺"->"𨱒′欢镙煎纺"锛屽皢𨱒′欢璁炬垚 钬滃叕寮=COUNTIF($A:$A,$A1)>1钬濓纴镣"镙煎纺"->"瀛椾綋"->"棰滆壊"锛岄夊畾绾㈣壊钖庣偣涓ゆ"纭瀹"銆傝繖镙疯惧畾濂藉悗浣犺緭鍏ユ暟鎹濡傛灉闀垮害涓嶅逛细链夋彁绀猴纴濡傛灉鏁版嵁閲嶅嶅瓧浣揿皢浼氩彉鎴愮孩镩层
涓夈佸湪EXCEL 涓濡备綍鎶夿鍒椾笌A鍒椾笉钖屼箣澶勬爣璇嗗嚭𨱒ワ纻
锛堜竴锛夈佸傛灉鏄瑕佹眰A銆丅涓ゅ垪镄勫悓涓琛屾暟鎹鐩告瘆杈冿细
锅囧畾绗涓琛屼负琛ㄥご锛屽崟鍑籄2鍗曞厓镙硷纴镣光沧牸寮忊->钬沧浔浠舵牸寮忊濓纴灏嗘浔浠惰句负:钬滃崟鍏冩牸鏁板尖濅笉钬灭瓑浜庘=B2
镣光沧牸寮忊->钬滃瓧浣撯->钬滈滆壊钬濓纴阃変腑绾㈣壊锛岀偣涓ゆ 钬灭‘瀹气濄傜敤镙煎纺鍒峰皢A2鍗曞厓镙肩殑𨱒′欢镙煎纺钖戜笅澶嶅埗銆
B鍒楀彲鍙傜収姝ゆ柟娉曡剧疆銆
锛堜簩锛夈佸傛灉鏄疉鍒椾笌B鍒楁暣浣撴瘆杈冿纸鍗崇浉钖屾暟鎹涓嶅湪钖屼竴琛岋级锛氩亣瀹氱涓琛屼负琛ㄥご锛屽崟鍑 A2鍗曞厓镙硷纴镣光沧牸寮忊->钬沧浔浠舵牸寮忊濓纴灏嗘浔浠惰句负:钬滃叕寮忊=COUNTIF($B:$B,$A2)=0
鎸変互涓婃柟娉曡剧疆钖庯纴 AB鍒楀潎链夌殑鏁版嵁涓岖潃镩诧纴 A鍒楁湁B鍒楁棤鎴栬 B鍒楁湁A鍒楁棤镄勬暟鎹镙囱颁负绾㈣壊瀛椾綋銆
锅囧畾链夊ぇ閲忕殑鏁版嵁 (鏁板)锛岄渶瑕佸皢姣忎竴琛屾寜浠庡ぇ鍒板皬鎺掑簭锛屽备綍镎崭綔锛
鐢变簬鎸夎屾帓搴忎笌鎸夊垪鎺掑簭閮芥槸鍙鑳芥湁涓涓涓诲叧阌瀛 ,涓诲叧阌瀛楃浉钖屾椂镓嶈兘鎸夋″叧阌瀛楁帓搴忋 镓浠ワ纴杩欎竴闂棰树笉鑳界敤鎺掑簭𨱒ヨВ鍐炽傝В鍐虫柟娉曞备笅锛
1銆佸亣瀹氢綘镄勬暟鎹鍦ˋ镊矱鍒楋纴璇峰湪F1鍗曞厓镙艰緭鍏ュ叕寮忥细=LARGE($A1:$E1,COLUMN(A1))
鐢ㄥ～鍏呮焺灏嗗叕寮忓悜鍙冲悜涓嫔嶅埗鍒扮浉搴旇寖锲淬
浣犲师链夋暟鎹灏嗘寜琛屼粠澶у埌灏忔帓搴忓嚭鐜板湪 F镊矹鍒椼傚傛湁闇瑕佸彲鐢 钬滈夋嫨镐х矘璐/鏁板尖濆嶅埗鍒板叾浠栧湴鏂广
娉锛氱1姝ョ殑鍏寮忓彲镙规嵁浣犵殑瀹为檯𨱍呭喌锛堟暟鎹锣冨洿锛変綔鐩稿簲镄勪慨鏀广傚傛灉瑕佷粠灏忓埌澶ф帓搴 ,鍏寮忔敼涓:=SMALL($A1:$E1,COLUMN(A1))
浜斻佸阀鐢ㄥ嚱鏁扮粍钖堣繘琛屽氭浔浠剁殑璁℃暟缁熻
渚嬶细绗涓琛屼负琛ㄥご锛孉鍒楁槸钬滃揿悕钬濓纴B鍒楁槸钬灭彮绾р濓纴C鍒楁槸钬滆鏂囨垚缁┾濓纴D鍒楁槸钬滃綍鍙栫粨鏋溾濓纴鐜板湪瑕佺粺璁♀灭彮绾р濅负钬滀簩钬濓纴钬滆鏂囨垚缁┾濆ぇ浜庣瓑浜 104锛屸滃綍鍙栫粨鏋溾濅负钬滈吨链钬濈殑浜烘暟銆傜粺璁＄粨鏋滃瓨鏀惧湪链宸ヤ綔琛ㄧ殑鍏朵粬鍒椼
鍏寮忓备笅锛=SUM(IF((B2:B9999=" 浜")*(C2:C9999>=104)*(D2:D9999=" 閲嶆湰"),1,0))
杈揿叆瀹屽叕寮忓悗鎸 Ctrl+Shift+Enter 阌,璁╁畠镊锷ㄥ姞涓婃暟缁勫叕寮忕﹀彿 "{}"銆
鍏銆佸备綍鍒ゆ柇鍗曞厓镙奸噷鏄钖﹀寘钖鎸囧畾鏂囨湰锛
锅囧畾瀵笰1鍗曞厓镙艰繘琛屽垽鏂链夋棤 "鎸囧畾鏂囨湰",浠ヤ笅浠讳竴鍏寮忓潎鍙 :
=IF(COUNTIF(A1,"*"&" 鎸囧畾鏂囨湰"&"*")=1,"链","镞")
=IF(ISERROR(FIND(" 鎸囧畾鏂囨湰",A1,1)),"镞","链")
渚嫔傛眰A1:A100 锣冨洿鍐呬笉閲嶅嶆暟鎹镄勪釜鏁帮纴镆愪釜鏁伴吨澶嶅氭″嚭鐜板彧绠椾竴涓銆傛湁涓ょ嶈＄畻鏂规硶锛氢竴鏄鍒╃敤鏁扮粍鍏寮忥细
=SUM(1/COUNTIF(A1:A100,A1:A100))
杈揿叆瀹屽叕寮忓悗鎸 Ctrl+Shift+Enter 阌,璁╁畠镊锷ㄥ姞涓婃暟缁勫叕寮忕﹀彿 "{}"銆备簩鏄鍒╃敤涔樼Н姹傚拰鍑芥暟锛
=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
涓冦佷竴涓宸ヤ綔钖勪腑链夎稿氩伐浣滆〃濡备綍蹇阃熸暣鐞嗗嚭涓涓鐩褰曞伐浣滆〃
1銆佺敤瀹3.0鍙栧嚭钖勫伐浣滆〃镄勫悕绉帮纴鏂规硶锛欳trl+F3 鍑虹幇镊瀹氢箟钖岖О瀵硅瘽妗嗭纴鍙栧悕涓篨锛屽湪钬滃紩鐢ㄤ綅缃钬濇嗕腑杈揿叆锛
=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,100) 纭瀹
2銆佺敤HYPERLINK鍑芥暟镓归噺鎻掑叆杩炴帴锛屾柟娉曪细鍦ㄧ洰褰曞伐浣滆〃锛堜竴鑸涓虹涓涓 sheet锛夌殑A2鍗曞厓镙艰緭鍏
鍏寮忥细=HYPERLINK("#'"&INDEX(X,ROW())&"'!A1",INDEX(X,ROW()))灏嗗叕寮忓悜涓嫔～鍏咃纴鐩村埌鍑洪敊涓烘锛岀洰褰曞氨鐢熸垚浜嗐
蹇鎹烽敭
涓婃爣锛欳trl+Shift+钬=钬
涓嬫爣锛欳trl+钬=钬
鎻掑叆鍟嗘爣绗﹀彿锛欳trl+Shift+钬漈钬
鎻掑叆娉ㄥ唽鍟嗘爣绗﹀彿锛欳trl+Shift+钬漅钬
鎻掑叆鐗堟潈绗﹀彿锛欳trl+Shift+钬滳钬