EXCEL中如果A1输人为“O”,则要求同一行每隔7个单元格自动输出同样的“O”,怎么办? excel中如果单元格为B则输出1.1如果是A则输出0.7,...
EXCEL\u4e2d\u5982\u679c\u4efb\u610f\u4e00\u4e2a\u5355\u5143\u8f93\u5165\u4e3a\u201cO\u201d\uff0c\u4e14\u8981\u6c42\u540c\u4e00\u884c\u6bcf\u96947\u4e2a\u5355\u5143\u683c\u81ea\u52a8\u8f93\u51fa\u540c\u6837\u7684\u201cO\u201d\uff0c\u600e\u4e48\u529e\uff1f\u5728\u4f60\u8981\u8f93\u5165\u7684\u90a3\u4e2a\u5355\u5143\u683c\u6309\uff1d\uff0c\u7136\u5f8c\u9009\u62e9\u4eba\u4e00\u4e2a\u5355\u5143\u683c\u7684O\u3002\u7136\u5f8c\u5728\u5176\u4ed6\u4f60\u60f3\u8981\u7684\u5355\u5143\u683c\u590d\u5236\uff1d\u7684\u90a3\u4e2a\u5355\u5143\u683c\uff01
=IF(A1="A",1.1,IF(A1="B",0.7,""))
\u5982\u679cA1\u662fA\uff0c\u5219\u8f93\u51fa1.1\uff0c\u5982\u679c\u4e3aB\u5219\u8f93\u51fa0.7\uff0c\u5426\u5219\u4ec0\u4e48\u4e5f\u4e0d\u8f93\u51fa\u3002
选择第一行,在A1输入以下公式,按Ctrl+Enter组合键结束
=IF(MOD(COLUMN(),8)=1,"O","")
公式表示:列号被8整除,余数为1的,显示O,其他留空。
详见附图
用vba吧:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [a1] = "0" Then
For i = 1 To 5
ActiveSheet.Cells(1, i * 7 + 1) = [a1]
Next
End If
End Sub
--------------
不知道你要几个0,目前为5个
修改“5”这个数字就可以了
---------------------------------------
--------------------------------------
楼上的方法要把人给累坏的。况且整个行都每个单元格都有函数在里面,是在给文件增肥。严重影响运行速度。最好的办法是将上面的宏代码写入一个宏,需要时执行就可以了,不会留下任何的后遗症。
绛旓細閫夋嫨绗竴琛岋紝鍦A1杈撳叆浠ヤ笅鍏紡锛屾寜Ctrl+Enter缁勫悎閿粨鏉 =IF(MOD(COLUMN(),8)=1,"O","")鍏紡琛ㄧず锛氬垪鍙疯8鏁撮櫎锛屼綑鏁颁负1鐨勶紝鏄剧ずO锛屽叾浠栫暀绌恒傝瑙侀檮鍥
绛旓細=IF(A1="","鏈畬鎴",IF(A1>0,"瓒呴瀹屾垚",IF(A1=0,"瀹屾垚璁″垝","鏈畬鎴")))
绛旓細浣犵殑鎰忔濇槸涓嶆槸璇碅1涓緭鍏ョ殑鏄叕寮忥紝褰撳叕寮忕殑缁撴灉绛変簬0鏃舵樉绀衡滃凡缁撴竻鈥濓紝鍚﹀垯鏄剧ず鍏紡鐨勫硷紵濡傛灉鏄繖鏍风殑璇濓紝A1涓鐨勫叕寮忓簲鏀逛负=if(鍏紡=0锛"宸茬粨娓",鍏紡锛夋妸浣犵殑鍏紡濂楃敤杩囨潵灏卞彲浠ヤ簡銆
绛旓細1.a1涓虹┖鏃讹紝b1涓虹┖銆俠1=c1鏃讹紝鍦╟1浠绘剰杈撳叆:=IF(A1="","",IF(A1,C1,5))2.a1涓虹┖鏃讹紝b1=c1锛宑1鍙换鎰忚緭鍏ユ暟鎹 =IF(A1="",C1,IF(A1,C1,5))
绛旓細=if(A1=0,"A",if(A1=2,"B","C"))
绛旓細B1鏁扮粍鍏紡锛岃緭鍏ュ畬鎴愬悗涓嶈鐩存帴鍥炶溅锛岃鎸変笁閿 CTRL+SHIFT+鍥炶溅 涓嬫媺銆=IF(A1="","",--OR(A1=CHAR(ROW($65:$90)))鎴栬呯敤浠ヤ笅鏁扮粍鍏紡涔熻锛=IF(A1="","",COUNT(FIND(A1,CHAR(ROW($65:$90)))
绛旓細鍦˙1杈撳叆=IF(A1="","",IF(A1=0,1,IF(A1=1,0,"")))
绛旓細=IF(A1/B1>0.8,"O","X")鏄笉鏄杩欑缁撴灉锛
绛旓細a5杈撳叆 =if(countif(a1:a3,"O")=3,"OK","NG")鐒跺悗锛岄変腑A5鍗曞厓鏍艰繘琛屾潯浠舵牸寮忚缃紝鍏紡 ="NG"鏍煎紡涓畾涔変负绾㈣壊锛岀‘瀹氬嵆鍙
绛旓細鍊间负0杩樻槸鍖呭惈0锛濡傛灉鍊间负0锛屽湪D1閲岄潰鍐欎笂杩欎釜鍏紡锛屽彲浠ヨ揪鍒颁綘瑕佺殑鏁堟灉 =IF(AND(A1=0,B1=0,C1=0),0,"")