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+鈥滳鈥