excel来个高手！怎么设置当我在单元格上输数字的时候，输入的数大于20 excel表格中如何设置公式当一个单元格大于某个数字时
\u8bf7\u95eeexcel\u5355\u5143\u683c\u600e\u4e48\u8bbe\u7f6e\u5f53\u8f93\u5165\u6570\u5b57\u5927\u4e8e\u67d0\u503c\u65f6\uff0c\u5219\u81ea\u52a8\u53d8\u6210\u8be5\u503cALT+F11
\u53cc\u51fb\u5de6\u4fa7sheet1
\u590d\u5236\u4e0b\u5217\u4ee3\u7801
Private
Sub
Worksheet_Change(ByVal
Target
As
Range)
If
Target.Row
=
1
And
Target.Column
=
1
Then
If
[a1]
>
10
Then
[a1]
=
10
End
If
'\u6574\u4e2a\u4ee3\u7801\u8981\u653e\u5728sheet
\u6a21\u5757\u4e2d
,\u653e\u5728sheet1
\uff0csheet1\u751f\u6548
End
If
End
Sub
\u3000\u3000\u5728\u516c\u5f0f\u4e2d\u4f7f\u7528if\u51fd\u6570\u5373\u53ef\uff0c\u8fd9\u91cc\u4ee5excel2010\u4e3a\u4f8b\uff0c\u64cd\u4f5c\u6b65\u9aa4\u5982\u4e0b\uff1a
\u3000\u30001\u3001\u542f\u52a8excel2010\uff0c\u5728b2\u5355\u5143\u683c\u8f93\u516515\uff0cb3\u5355\u5143\u683c\u8f93\u51655
\u3000\u30003\u3001\u9009\u4e2db5\u5355\u5143\u683c\uff0c\u5728\u516c\u5f0f\u7f16\u8f91\u680f\u8f93\u5165\u516c\u5f0f\uff1a=if(b2>=10,b3*3,if(and(b2=8),b3*2,if(b2<8,b3*1)))\uff0c\u8f93\u5165\u5b8c\u6210\u540e\u70b9\u51fb\u786e\u8ba4\uff1b
\u3000\u30004\u3001\u64cd\u4f5c\u5b8c\u6210\uff0c\u8f93\u5165\u5176\u4ed6\u503c\u9a8c\u8bc1\u3002
\u3000\u3000\u5c0f\u4e8e8\u65f6\uff1a
\u3000\u3000\u5c0f\u4e8e10\uff0c\u5927\u4e8e8\u65f6\uff1a
设置单元格格式，自定义：
[<20]"正常";[>=20]"20";G/通用格式
注意，数字、符号都必须是英文小写状态的。设置及效果见下面3个贴图：
绛旓細1銆佸湪I1鍗曞厓鏍艰緭鍏ヤ互涓嬪叕寮忥紝鐒跺悗鍚戜笅濉厖鍏紡 =IF(C1=1,MAX(A1:B1)-1.45,IF(C1=2,-3,IF(C1=0,0,"")))濡傛灉瑕佽冭檻妯媺锛屽叕寮忔敼涓 =IF(C1=1,MAX($A1:$B1)-1.45,IF(C1=2,-3,IF(C1=0,0,"")))2銆佸叕寮2 锛氬湪C1鍗曞厓鏍艰緭鍏ヤ互涓嬪叕寮忥紝鐒跺悗涓嬫媺濉厖锛=COUNTIF(A1:B1,">1")...
绛旓細鐐 鈥滄牸寮忊-銆夆滄潯浠舵牸寮忊濇牴鎹嚜宸辩殑鏉′欢鏀瑰彉棰滆壊銆佸瓧浣撶瓑銆備絾濂藉儚鍙兘鏈変笁绉嶄笉鍚岀殑鏍煎紡銆
绛旓細浣跨敤if鍑芥暟灏卞彲浠ュ疄鐜般傚亣璁剧櫧缁嗚優鏁扮洰鍦ˋ2锛屽湪B2涓娇鐢ㄢ=if(a2<4.0,"鈫",if(a2>10,"鈫","")鈥
绛旓細鍦Excel涓锛屼綘鍙互浣跨敤鍑芥暟宓屽鎴栧紩鐢ㄥ叾浠栧崟鍏冩牸鐨勬柟寮鏉ヨ缃涓涓崟鍏冩牸鐨勫肩瓑浜庡彟涓涓嚱鏁扮殑缁撴灉銆備互涓嬫槸涓ょ甯哥敤鐨勬柟娉曪細鍑芥暟宓屽锛氶夋嫨瑕佽缃肩殑鍗曞厓鏍笺傚湪璇ュ崟鍏冩牸鐨勫叕寮忔爮涓緭鍏ョ瓑鍙凤紙=锛夛紝鐒跺悗杈撳叆鍑芥暟鍚嶇О锛屽苟鍦ㄦ嫭鍙峰唴濉啓鐩稿簲鐨勫弬鏁般備緥濡傦紝瑕佽绠椾袱涓崟鍏冩牸A1鍜孉2鐨勫拰锛屽彲浠ヤ娇鐢ㄥ嚱鏁=SUM(A1,...
绛旓細1.褰撯滃寘鐢ㄨ处鏈熲濆埌杈惧墠10澶╁紑濮--鍓5澶╋紝鈥滃鎴峰悕绉扳濇爮浜粍鐏紱2.褰撯滃寘鐢ㄨ处鏈熲濆埌杈惧墠5澶╁紑濮嬧滃鎴峰悕绉扳濇爮浜孩鐏紱3.褰撯滃寘鐢ㄨ处鏈熲濇湭鍒版垨鈥滃娉ㄢ濇爮鏈夊鐞嗚褰曟椂锛屸滃鎴峰悕绉扳濇爮涓嶄寒鐏垨浜伅鑷姩娑堝け銆傚湪A2鍗曞厓鏍璁剧疆鍏紡锛屾楠ゅ涓嬶細璁剧疆绗竴涓潯浠讹細1.鏍煎紡鈫掓潯浠舵牸寮忊啋鐐瑰嚮鈥滄潯浠讹紙1...
绛旓細鍗曞嚮B鍒楀垪鏍囬変腑璇ュ垪銆嬫牸寮忋嬫潯浠舵牸寮忋嬪叕寮=and(a1="open",b1-today()<=6)鍗曞嚮璇ュ璇濇涓婄殑鏍煎紡鎸夐挳锛璁剧疆鐩稿簲鐨勯鑹层
绛旓細鍗曞嚮杈撳叆妗嗗彸渚х殑灞曞紑鎸夐挳锛堟湁涓涓孩鑹茬殑寮澶达級锛屽啀鐢ㄩ紶鏍囧崟鍑绘爣棰樿宸︿晶鐨勮鏍囧織锛堟暟瀛1锛夛紝鐒跺悗鍗曞嚮灞曞紑鎸夐挳杩斿洖鈥滈〉闈璁剧疆鈥濆璇濇銆傛晥鏋滈瑙堬細鍗曞嚮鈥滃父鐢ㄢ濆伐鍏锋爮涓婄殑鈥滄墦鍗伴瑙堚濇寜閽紝鎷栧姩鍨傜洿婊氬姩鏉★紙鎴栬呭崟鍑婚《涓婂伐鍏锋爮宸︿晶鐨勨滀笅涓椤碘濇寜閽級杩涜閫愰〉棰勮锛屽綋瑙夊緱鏁堟灉婊℃剰鏃讹紝灏卞彲浠ヨ繘琛屾墦鍗颁簡...
绛旓細鍏舵柟娉曞涓嬪浘鎵绀猴細鈶犻夋嫨A1鍗曞厓鏍硷紝鐐瑰嚮銆愭潯浠舵牸寮忋戯紝銆愭柊寤鸿鍒欍戙傗憽閫夋嫨鈥滃叕寮忊濓紝涔嬪悗杈撳叆鍏紡锛=$A1="寰呯宸ラ挶鏈氦"锛夛紙涓嶅惈鎷彿锛夛紝鐒跺悗鐐瑰嚮銆愭牸寮忋戙傗憿鍦ㄣ愬瓧浣撱戦噷闈㈤夋嫨鈥滅孩鑹测濓紝涔嬪悗涓娆＄偣鍑汇愮‘瀹氥戙傗懀鍚岀悊锛屾寜鐓р憼鈶♀憿姝ユ柊寤轰竴涓摑鑹茬殑鏉′欢鏍煎紡銆傗懁鎶夾1鍗曞厓鏍煎悜鍙冲鍒跺～鍏咃紝閫夋嫨鈥...
绛旓細濡傛灉<=100鐨勪负鍘熸暟,鑰 >100鐨勬暟鍙樺仛100涓哄疄鏁(鍗冲彲褰100璁＄畻鐨勬暟,)灏辫鍦ㄥ彟涓涓崟鍏冩牸杈撳叆鍏紡 =IF(A1>100,100,A1)濡傛灉鍙樺仛100鐨勪负铏氭暟(鍗冲彧鏄樉绀100,鑰屽疄闄呰繕鏄寜鍘熷疄鏁拌绠楃殑),灏卞湪鍘熷崟鍏冩牸璁剧疆鍗曞厓鏍兼牸寮忚嚜瀹氫箟涓 [>100]"100";G/閫氱敤鏍煎紡 ...
绛旓細2銆佺粯鍒惰〃鏍硷細鐐瑰嚮寮濮-閫夋嫨瀛椾綋锛堝姞绮椾笌鍚︼級鈥斿瓧浣撳ぇ灏忊斿瓧浣撻鑹测旇竟妗嗏斿瓧浣撳榻愮殑鏂瑰紡鈥斿悎骞跺崟鍏冩牸鈥旇嚜鍔ㄦ崲琛岀瓑3銆 鍒朵綔鏂滅嚎琛ㄥご 锛氭寜ctrl+1璁剧疆鍗曞厓鏍兼牸寮忊旂偣鍑昏竟妗嗏旀牱寮忊旈夋嫨鏂滅嚎灏卞彲浠ヤ簡4銆佺獥鍙ｅ喕缁擄細鍐荤粨绐楀彛鍒嗛琛屻佺涓琛岀瓑锛屾垜浠夋嫨棣栬锛岄変腑棣栬鐐瑰嚮瑙嗗浘锛屽喕缁撻琛屽氨鍙互浜嗭紝杩欐牱鍙互鏂逛究...
