在EXCEL中某单元格里用IF条件语句一次能用几个IF，我的好像只能用8个。 在EXCEL里面怎么运用IF逻辑函数公式？最多能只能运用8个...
\u5728EXCEL\u4e2d\u67d0\u5355\u5143\u683c\u91cc\u7528IF\u6761\u4ef6\u8bed\u53e5\u4e00\u6b21\u80fd\u7528\u51e0\u4e2aIF\uff0c\u6211\u7684\u597d\u50cf\u53ea\u80fd\u75288\u4e2a\u3002\u89e3\u51b3\u653e\u53d1\u592a\u591a\u4e86, \u770b\u4f60\u80fd\u4e0d\u80fd\u7406\u89e3
=char(L2+64)
=LOOKUP(L2,{1,2,3,4,5,6,7,8,9},{"A","B","C","D","E","F","G","H","I"})
Vlookup\u4e5f\u53ef\u4ee5, index+match \u516c\u5f0f\u4e5f\u53ef\u4ee5, \u770b\u4f60\u7684\u5b9e\u9645\u95ee\u9898\u662f\u4ec0\u4e48
\u56e0\u4e3a\u95ee\u9898\u6240\u6d89\u53ca\u7684\u6570\u636e\u6709\u89c4\u5f8b\uff0c\u56e0\u6b64\u53ef\u4ee5\u4e0d\u901a\u8fc7IF\u51fd\u6570\u6765\u6c42\u89e3\u3002
\u5728\u6b64\u63d0\u4f9b\u4ee5\u4e0b\u65b9\u6cd5\u4f9b\u9a8c\u8bc1\uff0c\u5e0c\u671b\u5bf9\u4f60\u6709\u6240\u5e2e\u52a9 \uff01
\u6570\u636e\u7cfb\u6570\u5728D\u5217\uff0c\u90a3\u4e48\u5728E1\u5355\u5143\u683c\u8f93\u5165\u4ee5\u4e0b\u516c\u5f0f\uff0c\u7136\u540e\u5411\u4e0b\u586b\u5145\u516c\u5f0f
=-CEILING(D1,0.05)*50/0.05+200
\u8868\u793a\uff1a\u7cfb\u6570\u4ee50.05\u4e3a\u57fa\u51c6\u683c\u5f0f\u5411\u4e0a\u4fdd\u7559\uff1b\u6bcf0.05\u76f8\u5dee50\u5143\uff1b\u5f53\u7cfb\u6570\u4e3a0\u65f6\uff0c200\u5143\u3002
\u8be6\u89c1\u9644\u56fe\uff1a
类似题目中，可以使用VLOOKUP达到类似的效果。
举例：=VLOOKUP(L2,{5,"A";10,"B";15,"C";20,"D";25,"E";30,"F";35,"G";40,"H"},2,FALSE)
如果L2不是上述的数时不显示数字，则可以
=IF(ISERROR(MATCH(L2,{5,10,15,20,25,30,35,40},0)),"",VLOOKUP(L2,{5,"A";10,"B";15,"C";20,"D";25,"E";30,"F";35,"G";40,"H"},2,FALSE))
if语句最多只能嵌套7层。
可以将公式改为：
=choose(L2/5,A,B,C,C,E,F,G,H)
直接用这个就行了，别的太复杂了吧
=if(mod(l2,5)=0,char(64+l2/5),"")
用VBA代码可以解决
绛旓細绗4姝 锛氬崟鍑婚変腑鍒氬垰鍐欏畬鍏紡鐨鍗曞厓鏍锛岄紶鏍囩Щ鍔ㄥ埌鍗曞厓鏍煎彸涓嬭锛屽綋榧犳爣鍙樻垚鈥+鈥濆彿鏃跺崟鍑婚紶鏍囧乏閿笉鏉惧紑锛屽苟鍚戜笅鎷栧姩锛岄渶瑕佸垽鏂灏戣灏辨嫋鍔ㄥ埌澶氬皯琛岋紝鐒跺悗鍐嶆澗寮榧犳爣宸﹂敭銆傜5姝 锛 浠ヤ笅涓浣跨敤Excel鐨処F鍜孋OUNTIF涓や釜鍑芥暟鍒ゆ柇鍗曞厓鏍煎寘鍚壒瀹氬瓧绗︼紝鏄剧ず鐗瑰畾鍊肩殑鏈缁堟晥鏋溿
绛旓細褰撳仛璁板綍涓嶆兂鎵嬪姩杈撳叆鏃ユ湡鏃讹紝鍙互浣跨敤杩欎釜鏂规硶銆鍦ㄩ渶瑕佽緭鍏ユ棩鏈熺殑鍗曞厓鏍艰緭鍏ュ叕寮忥細if(B2="","",now())锛屼笅鎷夊～鍏呴渶瑕佽嚜鍔ㄦ樉绀烘棩鏈熺殑鍖哄煙銆傚叕寮忕殑鎰忔濇槸褰揃2鍗曞厓鏍间负绌烘椂锛屽綋鍓嶅崟鍏冩牸涓虹┖锛屽惁鍒欐樉绀哄綋鍓嶆棩鏈熴傚涓嬪浘鎵绀猴細鎴戜滑鍙互鐪嬪埌鏃ユ湡鏍煎紡骞朵笉姝ｇ‘锛岃屾槸涓涓叉暟瀛椾唬鐮侊紝姝ゆ椂闇瑕侀夋嫨鏃ユ湡鎵鍦ㄥ尯鍩燂紝鎸...
绛旓細1銆佸湪Excel琛ㄦ牸涓夋嫨绛旀鏀剧疆鐨勫崟鍏冩牸銆2銆佸湪鎴戜滑瑕佹搷浣滅殑鍗曞厓鏍艰緭鍏モ=鈥濓紝涓婃柟鍑芥暟鏍忎篃鐩稿簲鍑虹幇鈥=鈥濄3銆佸崟鍑诲嚱鏁癴x锛屾壘鍒癷f鍑芥暟銆4銆佽緭鍏ュ垽鏂潯浠跺拰鍒ゆ柇缁撴灉锛岀涓琛孡ogical_test杈撳叆鍒ゆ柇鏉′欢锛屽C2>=60锛屽嵆璁〤2鏍忓ぇ浜庣瓑浜20鏃朵负鐪燂紝绗簩琛孷alue_if_true杈撳叆绗﹀悎鍒ゆ柇鏉′欢鐨勫垽鏂粨鏋滐紝濡傚悎鏍硷紝...
绛旓細鍦‥xcel涓紝鍙互浣跨敤鈥淚F鈥濆拰鈥淪EARCH鈥濆嚱鏁版潵鍒ゆ柇鍗曞厓鏍间腑鏄惁鍚湁鏌愪釜瀛楃銆1. 浣跨敤IF鍑芥暟锛欼F鍑芥暟鐢ㄤ簬鍦‥xcel涓繘琛屾潯浠跺垽鏂傚叾璇硶涓猴細IF(logical_test, [value_if_true], [value_if_false])銆傚叾涓璴ogical_test鏄綘瑕佹祴璇曠殑鏉′欢锛孾value_if_true]鏄綋鏉′欢涓虹湡鏃惰繑鍥炵殑鍊硷紝[value_if_false]...
绛旓細excel鍗曞厓鏍鍑芥暟if鐨浣跨敤鏁欑▼(涓)姝ラ1锛氭墦寮Excel锛屾墦寮浣犵殑鏁版嵁鏂囨。锛屽湪浣犳兂杈撳嚭鏁版嵁鐨勫崟鍏冩牸鐐瑰嚮宸ュ叿鏍忎笂鐨勨滄彃鍏モ濃斺斺滃嚱鏁扳濃斺斺滈昏緫鈥濃斺斺淚F鈥,鐒跺悗鐐瑰嚮纭畾銆傛楠2锛氱偣鍑荤‘瀹氬悗浼氬脊鍑涓涓妗嗘锛岀涓涓〃绀烘潯浠讹紝鍚庨潰涓や釜鏄垽鏂潯浠剁殑缁撴灉銆傜偣鍑诲皬缂栨爣娉ㄧ殑鍦版柟锛岄夋嫨鏁版嵁锛屼竴鑸垜浠笉鐢ㄩ夋嫨...
绛旓細1銆侀鍏堬紝鎵撳紑Excel锛屽鍥撅紝A鍒楀拰B鍒楁暟鎹繘琛屾瘮杈冦2銆侀変腑缁撴灉瑕佹斁缃殑C2鍗曞厓鏍锛岀偣鍑绘彃鍏ュ嚱鏁癴x鎸夐挳銆3銆佸脊鍑烘彃鍏ュ嚱鏁板璇濇锛岀偣鍑籭f鍑芥暟锛岀偣鍑荤‘瀹氥4銆佸湪绗涓涓鍙傛暟妗嗕腑鐐瑰嚮锛屽啀鐐瑰嚮A2鍗曞厓鏍硷紝A2灏卞嚭鐜板湪绗竴涓弬鏁版涓5銆佹帴涓嬫潵鍦ˋ2鍚庨潰杈撳叆澶т簬鍙凤紝鍐嶇偣鍑籅2锛孊2鍑虹幇鍦ㄥぇ浜庡彿鍚庨潰銆6銆佸啀鐐瑰嚮...
绛旓細1銆鍦╡xcel琛ㄦ牸涓鐨凙鍒鍗曞厓鏍煎唴杈撳叆涓缁勬暟鎹紝鐢ㄦ潵浣跨敤IF鍑芥暟杈撳叆浜斾釜鏉′欢杩涜鏁版嵁鎿嶄綔銆2銆佸湪B2鍗曞厓鏍煎唴杈撳叆IF鍏紡锛=IF(A2<60,"1",IF(A2<100,"2",IF(A2<200,"3",IF(A2<280,"4",IF(A2<350,"5")))銆傛剰鎬濇槸A2鍗曞厓鏍间腑濡傛灉鏁版嵁灏忎簬60锛屽垯鏄剧ず鏁板瓧1锛屽鏋滄暟鎹皬浜100锛屽垯鏄剧ず鏁板瓧2锛...
绛旓細=IF(1>2,"鍒ゆ柇鐪","鍒ゆ柇鍋")鍏朵腑鍒ゆ柇1澶т簬2缁撴灉鏄亣涔熷氨鏄疐ALSE锛屾墍浠鍗曞厓鏍杩斿洖绗笁鍙傛暟鏂囨湰鈥滃垽鏂亣鈥濄3銆佺涓鍙傛暟鍦ㄦ煇浜鎯呭喌涓嬩笉鏄敤姣旇緝鍒ゆ柇绗﹀彿鏉ュ垽鏂殑銆Excel涓皢0璁や负鏄疐ALSE锛岄潪0鍊肩瓑浠蜂簬TRUE銆傛墍浠ュ涓嬪浘锛孊1鍏紡涓猴細=IF(SUM(A1:A2),"鍒ゆ柇鐪","鍒ゆ柇鍋")锛屽洜涓篈1:A2鍚堣澶т簬0锛...
绛旓細=IF(1>2,"鍒ゆ柇鐪","鍒ゆ柇鍋")鍏朵腑鍒ゆ柇1澶т簬2缁撴灉鏄亣涔熷氨鏄疐ALSE锛屾墍浠鍗曞厓鏍杩斿洖绗笁鍙傛暟鏂囨湰鈥滃垽鏂亣鈥濄3绗竴鍙傛暟鍦ㄦ煇浜鎯呭喌涓嬩笉鏄敤姣旇緝鍒ゆ柇绗﹀彿鏉ュ垽鏂殑銆Excel涓皢0璁や负鏄疐ALSE锛岄潪0鍊肩瓑浠蜂簬TRUE銆傛墍浠ュ涓嬪浘锛孊1鍏紡涓猴細=IF(SUM(A1:A2),"鍒ゆ柇鐪","鍒ゆ柇鍋")鍥犱负A1:A2鍚堣澶т簬0锛屾墍浠...
绛旓細鍦ㄦ寚瀹鍗曞厓鏍煎唴鎻掑叆鍑芥暟濡備笅锛=IF(AND(A1>=80,A1<90),"鍖洪棿鍐","Wrong")鍏朵腑锛岃嫢婊¤冻鏉′欢锛屽垯a2鏄剧ず鈥滄弧瓒虫潯浠垛濓紝鍚﹀垯鏄剧ず鈥淲rong鈥,鍏蜂綋鎿嶄綔濡備笅锛1锛堿鍒楅殢鏃惰緭鍏ヤ竴浜涙暟鍊硷紝B鍒楄幏寰楁潯浠跺悗缁撴灉銆2锛夊湪B2鍒楄緭鍏F鏉′欢鐨勫叕鍙糕=IF(AND(A1>=80,A1<90),"鍖洪棿鍐","Wrong")鈥濓紝璁惧畾缁撴灉涓猴細...
