如何在excel中使用多重if函数 EXCEL 多重嵌套IF函数
EXCEL\u7528IF\u600e\u6837\u7b97\u591a\u6761\u4ef6\u554a1\u3001\u6253\u5f00excel\uff0c\u6267\u884c\u6587\u4ef6-\u6253\u5f00\u547d\u4ee4\uff0c\u6253\u5f00\u4e00\u526f\u9700\u8981\u5904\u7406\u7684\u6570\u636e\u8868\u683c\u3002
2\u3001\u9009\u62e9\u5355\u5143\u683cd2\uff0c\u8f93\u5165\u516c\u5f0f\u6846\u67b6=if\uff08\uff0c\u201c\u201d\uff0c\u201c\u201d\uff09\u8fd9\u91cc\u6211\u4eec\u67e5\u627e\u8bed\u6587=80\uff0c\u6570\u5b66=80\u7684\u540c\u5b66\u3002
3\u3001\u63a5\u7740\u5728if\u516c\u5f0f\u4e2d\u8f93\u5165\u51fd\u6570\u53c2\u6570\uff0c\u8bbe\u7f6e\u7b2c\u4e00\u4e2a\u53c2\u6570\u4e3aand\uff08b2="80",c2="80"\uff09\u3002
4\u3001\u63a5\u7740\u8bbe\u7f6e\u7b2c\u4e8c\u4e2a\u53c2\u6570\u4e3a\u201c\u662f\u201d\uff0c\u7b2c\u4e09\u4e2a\u53c2\u6570\u4e3a\u201c\u5426\u201d\uff0c\u6309\u56de\u8f66\u952e\u786e\u8ba4\u3002
5\u3001\u8fd9\u6837\u5c31\u4f1a\u51fa\u73b0\u5224\u65ad\u7ed3\u679c\uff0c\u4f7f\u7528\u53cc\u51fb\u586b\u5145\u7684\u65b9\u6cd5\u586b\u5145\u5176\u4ed6\u5355\u5143\u683c\u3002\u5f53\u7136\u4e5f\u53ef\u4ee5\u6839\u636e\u81ea\u5df1\u7684\u5b9e\u9645\u60c5\u51b5\u8fdb\u884c\u5176\u4ed6\u6761\u4ef6\u67e5\u8be2\uff0c\u53ea\u4e0d\u8fc7\u662f\u4fee\u6539\u51fd\u6570\u53c2\u6570\u5373\u53ef\u3002
\u6269\u5c55\u8d44\u6599\uff1a
EXCEL\u8bbe\u7f6e\u7eb5\u5750\u6807\u8f74\u6570\u5b57\u683c\u5f0f\u5c0f\u6280\u5de7
\u5e38\u89c1\u7684\u51e0\u4e2a\u5efa\u8bae\u7684\u8bbe\u7f6e\u6709\uff1a
1\u3001\u8d85\u8fc7999\u7684\u6570\u5b57\u53ef\u4ee5\u4f7f\u7528\u5343\u5206\u4f4d\u5206\u9694\u7b26\uff1b
2\u3001\u8fc7\u5927\u7684\u5750\u6807\u8f74\u523b\u5ea6\u6570\u5b57\u8003\u8651\u4f7f\u7528k\uff08thousand \u5343\uff09\u6216m\uff08million \u767e\u4e07\uff09\u4e3a\u5355\u4f4d\uff0c\u751a\u81f3\u53ef\u4ee5\u4f7f\u7528billion\uff08\u5341\u4ebf\uff09\u4e3a\u5355\u4f4d\uff0c\u8fd9\u4e2a\u53ef\u4ee5\u6839\u636e\u5b9e\u9645\u6570\u5b57\u5927\u5c0f\u7075\u6d3b\u8c03\u6574\uff1b
3\u3001\u53bb\u6389\u591a\u4f59\u7684\u5c0f\u6570\u70b9\u53ca\u5176\u540e\u9762\u76840\uff1b
4\u3001\u5bf9\u4e8e\u767e\u5206\u6bd4\u6570\u503c\uff0c\u7eb5\u5750\u6807\u8f74\u4e5f\u4f7f\u7528\u767e\u5206\u6bd4\u800c\u4e0d\u662f\u5c0f\u6570\uff1b
5\u3001\u5982\u679c\u6709\u5fc5\u8981\u53ef\u4ee5\u5728\u5750\u6807\u8f74\u4e0a\u6570\u5b57\u540e\u9762\u6dfb\u52a0\u5355\u4f4d\u3002
\u53c2\u8003\u8d44\u6599\uff1aExcel\u4f7f\u7528\u4e2d\u7684\u6280\u5de7-Excel\u5b98\u65b9\u7f51\u7ad9
=A2-(INT(A2/100)+1)*10
\u8fd9\u6837\u5c31\u53ef\u4ee5\u4e86
=IF(B2<=1500,"3%",IF(B2<=4500,"10%",IF(B2<=9000,"20%",IF(B2<=35000,"25%",IF(B2<=55000,"30%",IF(B2<=80000,"35%","45%"))))))
=LOOKUP(B2,{0,3,9,18,70,110,160}*500,{3,10,20,25,30,35,45}%)
绛旓細excel涓璱f鏄粈涔堝嚱鏁 IF鍑芥暟鏄Excel涓鐨勪竴涓昏緫鍑芥暟锛屽畠鍦ㄦ垜浠殑瀹為檯宸ヤ綔涓簲鐢ㄩ潪甯稿箍娉涳紝浣跨敤IF鍑芥暟鍙互寰堟柟渚跨殑甯姪鎴戜滑瀹屾垚涓浜涙潯浠跺垽鏂侷F鍑芥暟姒傚康鏄垽鏂槸鍚︽弧瓒虫煇涓潯浠讹紝濡傛灉婊¤冻鍒欒繑鍥炰竴涓硷紝濡傛灉涓嶆弧瓒冲垯杩斿洖鍙︿竴涓笺備娇鐢ㄦ柟娉曪細鎵撳紑Excel锛屾墦寮浣犵殑鏁版嵁鏂囨。锛屽湪浣犳兂杈撳嚭鏁版嵁鐨勫崟鍏冩牸鐐瑰嚮宸ュ叿鏍...
绛旓細1锛夐鍏堝湪鑿滃崟鏍忕偣鍑烩滆浆鎹⑩->鈥滆绠楀彉閲忊濓紝鎵撳紑璁＄畻鍙橀噺瀵硅瘽妗嗐2锛夊～鍐欑洰鏍囧彉閲忓悕绉帮紙杩欓噷鏄渋ncome鈥濓級锛岀劧鍚庡垎鍒～鍐IF鏉′欢锛堣繖閲屾槸宸ヨ祫<40000锛夊拰杈撳嚭琛ㄨ揪寮忥紙杩欓噷鏄伐璧剎1.1锛夛紝鏈鍚庣偣鍑荤‘瀹氾紝杩欐牱绗竴绉嶆儏鍐靛氨瀹屾垚浜嗐3锛夌劧鍚庢坊鍔犵浜岀鎯呭喌锛屾楠ゅ拰涓婇潰杩欐鏄竴鏍风殑锛屽～鍐欑洰鏍囧彉閲忓悕绉帮紙杩樻槸...
绛旓細褰撴垜浠湪浣跨敤鐢佃剳鐨勬椂鍊欙紝鍙互閫氳繃excel杞欢鏉ュ鐞嗕竴浜涙暟鎹枃妗ｏ紝鍚屾椂涔熷彲浠ヤ娇鐢ㄤ竴浜涘嚱鏁版潵澶勭悊杩欎簺鏂囨。锛岄偅涔堝鏋滄兂瑕浣跨敤澶涓潯浠剁殑IF鍑芥暟鐨勮瘽锛屽簲濡備綍鎿嶄綔鍛紵鎺ヤ笅鏉ュ氨鐢卞皬缂栨潵鍛婅瘔澶у銆傚叿浣撳涓嬶細1. 绗竴姝ワ紝鎵撳紑鐢佃剳涓殑涓涓猠xcel琛ㄦ牸鏂囨。锛屽涓嬪浘鎵绀猴紝闇瑕佹牴鎹笟缁╂潵姹傚緱瀵瑰簲鐨勫閲戙2. 绗簩姝ワ紝...
绛旓細绗竴姝ワ紝鍐欑涓涓潯浠讹紝骞朵笖瀹屾暣鐨勯楀彿鍜屾嫭鍙凤細=IF(A1<50000,400,)绗簩姝ワ紝澶嶅埗杩欎釜鍑芥暟锛岀矘璐村埌涓婇潰缂哄皯鐨勩愬惁鍒欍戜綅缃紝鏀逛负绗簩涓潯浠讹細=IF(A1<50000,400,IF(A1<70000,500,))濡傛寰幆锛屾渶鍚庡叕寮忎负锛=IF(A1<50000,400,IF(A1<70000,500,IF(A1<100000,600,800)))...
绛旓細"浣犻渶瑕佽ˉ鑰冪殑绉戠洰鏄細鏀挎不","鎭枩浣狅紝浣犱笉鐢ㄨˉ")))涓婇潰灏辨槸鑰冭檻鍥涚鎵鏈夌殑鎯呭喌涓嬶紝鍗曠函鐢↖F鍐欑殑璇彞锛屾垜鑷繁娴嬭瘯杩囷紝鍦‥XCEL涓鍙敤鐨勩傛槸涓嶆槸瑙夊緱寰堥暱锛熸甯哥殑锛屽鏋滀綘瀛﹁繃鎺掑垪缁勫悎锛屼綘灏辨竻妤氾紝涓绉戝彧鏈変袱绉嶆儏鍐碉紝浣嗘槸涓ょ灏辨槸鍥涚锛屼笁绉戝氨鏄叓绉嶏紝鍥涚灏辨槸鍗佸叚绉嶏紝瑕佹妸鍗佸叚绉嶆儏鍐甸兘鍐欒繘鍘伙紝...
绛旓細=IF(U5<=27锛(28-U5)*10锛(28-U5)*5) 銆傚鏋淯5灏忎簬绛変簬27锛屽氨鎵ц涔樹互10鐨勭畻寮忥紝鍚﹀垯灏辨墽琛屼箻浠5鐨勭畻寮忋傚叕寮忎笉鑳芥湁鈥(27<U5<28锛夆濊繖鏍风殑鍙屾潯浠躲備妇渚嬪瓙100--90鍒嗕负浼樼锛89---70涓鸿壇濂斤紝69--60涓哄強鏍硷紝59涓涓嬩负涓嶅強鏍笺傚叕寮=IF(B1<=59锛"涓嶅強鏍尖,IF(B1<=69锛"鍙婃牸鈥,IF(B1...
绛旓細婊¤冻澶氫釜鏉′欢涔熷彲浠ュ垎涓ょ鎯呭喌锛1锛夊悓鏃舵弧瓒冲涓潯浠讹紱2锛夋弧瓒冲涓潯浠朵腑鐨勪竴涓垨鑻ュ共涓嵆鍙傛垜浠互涓嬪浘鐨勬暟鎹潵涓句緥璇存槑銆傞鍏堬紝鍒╃敤AND()鍑芥暟鏉ヨ鏄庡悓鏃舵弧瓒冲涓潯浠躲備妇渚嬶細濡傛灉A鍒楃殑鏂囨湰鏄淎鈥濆苟涓擝鍒楃殑鏁版嵁澶т簬210锛屽垯鍦–鍒楁爣娉ㄢ淵鈥濄傚湪C2杈撳叆鍏紡锛=IF(AND(A2="A",B2>210),"Y","")鐭...
绛旓細excelif鍑芥暟澶氬眰宓屽鎬庝箞浣跨敤锛熸帴涓嬫潵灏辨潵灏辨潵涓哄ぇ瀹朵粙缁嶄竴涓媏xcelif鍑芥暟澶氬眰宓屽鐨勪娇鐢ㄦ柟娉曘1銆佹墦寮excel鏂囨。銆2銆佽緭鍏ョ浉鍏虫暟鎹3銆佸弻鍑昏緭鍏ョ粨鏋滅殑鍗曞厓鏍硷紝杈撳叆=IF(鏉′欢1,杩斿洖缁撴灉1,IF(鏉′欢2,杩斿洖缁撴灉2,IF(鏉′欢3,杩斿洖缁撴灉3锛...)))4銆佹寜鍥炶溅閿嵆鍙緱鍒扮瓟妗堛備互涓婂氨鏄负澶у浠嬬粛浜唀xcelif鍑芥暟澶氬眰...
绛旓細,B2>150),"Y","")锛孫R()鍑芥暟浼氭鏌ユ瘡涓潯浠讹紝涓鏃︽弧瓒充换浣曚竴涓潯浠讹紝灏变細杩斿洖"Y"銆備互涓婂叕寮忕ず渚嬮傜敤浜庡崟涓崟鍏冩牸鐨勫垽鏂傜劧鑰岋紝浣犱篃鍙互閫氳繃鏁扮粍鍏紡鐨勬柟寮忓湪鍗曚釜鍗曞厓鏍间腑涓娆℃у鐞嗗涓緟鍔╁崟鍏冩牸鐨勫垽鏂紝鎻愰珮鏁堢巼銆傝繖绉嶇伒娲杩愮敤IF鍑芥暟鐨勫鏉′欢閫昏緫锛岃兘澶熷府鍔╀綘鏇撮珮鏁堝湴杩涜Excel鐨勬暟鎹鐞嗗拰绛涢夈
绛旓細1銆=NOT(D12="鍏氬憳")2銆=AND(F12>=60,G12>=60)3銆=OR(F12<60,G12<60)4銆=IF(H12<AVERAGEA(H$12:H$31),"鏈揪鏍","杈炬爣")5銆=IF(H12>=80,"A",IF(H12>=60,"B","C"))6銆=IF(AND(F12<60,G12<60),"鏁板鑻辫",IF(F12<60,"鏁板",IF(G12<60,"鑻辫","")))璇...
