excel 公式修改E11=IF(AND(SUMPRODUCT(--(OFFSET(C$11,MAX(6*ROW(A1)-6),6)=OFFSET(C$17,MAX(6*ROW(A1)-6),6)))=6,BC110),"A","")其中C$17 是需要变化的 17 23 29F11=IF(AND(SUMPRODUCT(--(OFFSET(C$11,MAX(6*ROW(A1)-6),6)=OFFSET(C$23,MAX(6*ROW(A1)-6),6
来源:学生作业帮助网 编辑:作业帮 时间:2024/07/04 03:49:45
![excel 公式修改E11=IF(AND(SUMPRODUCT(--(OFFSET(C$11,MAX(6*ROW(A1)-6),6)=OFFSET(C$17,MAX(6*ROW(A1)-6),6)))=6,BC110),](/uploads/image/z/2111484-12-4.jpg?t=excel+%E5%85%AC%E5%BC%8F%E4%BF%AE%E6%94%B9E11%3DIF%28AND%28SUMPRODUCT%28--%28OFFSET%28C%2411%2CMAX%286%2AROW%28A1%29-6%29%2C6%29%3DOFFSET%28C%2417%2CMAX%286%2AROW%28A1%29-6%29%2C6%29%29%29%3D6%2CBC110%29%2C%22A%22%2C%22%22%29%E5%85%B6%E4%B8%ADC%2417+%E6%98%AF%E9%9C%80%E8%A6%81%E5%8F%98%E5%8C%96%E7%9A%84+17+23+29F11%3DIF%28AND%28SUMPRODUCT%28--%28OFFSET%28C%2411%2CMAX%286%2AROW%28A1%29-6%29%2C6%29%3DOFFSET%28C%2423%2CMAX%286%2AROW%28A1%29-6%29%2C6)
excel 公式修改E11=IF(AND(SUMPRODUCT(--(OFFSET(C$11,MAX(6*ROW(A1)-6),6)=OFFSET(C$17,MAX(6*ROW(A1)-6),6)))=6,BC110),"A","")其中C$17 是需要变化的 17 23 29F11=IF(AND(SUMPRODUCT(--(OFFSET(C$11,MAX(6*ROW(A1)-6),6)=OFFSET(C$23,MAX(6*ROW(A1)-6),6
excel 公式修改
E11=IF(AND(SUMPRODUCT(--(OFFSET(C$11,MAX(6*ROW(A1)-6),6)=OFFSET(C$17,MAX(6*ROW(A1)-6),6)))=6,BC110),"A","")
其中C$17 是需要变化的 17 23 29
F11=IF(AND(SUMPRODUCT(--(OFFSET(C$11,MAX(6*ROW(A1)-6),6)=OFFSET(C$23,MAX(6*ROW(A1)-6),6)))=6,BC110),"A","")
G11=IF(AND(SUMPRODUCT(--(OFFSET(C$11,MAX(6*ROW(A1)-6),6)=OFFSET(C$29,MAX(6*ROW(A1)-6),6)))=6,BC110),"A","")
现在需要求解的是,如何修改公式,横着拖时,可以自动生成,不用一个一个的输入
因为有E1-XX634 之多
excel 公式修改E11=IF(AND(SUMPRODUCT(--(OFFSET(C$11,MAX(6*ROW(A1)-6),6)=OFFSET(C$17,MAX(6*ROW(A1)-6),6)))=6,BC110),"A","")其中C$17 是需要变化的 17 23 29F11=IF(AND(SUMPRODUCT(--(OFFSET(C$11,MAX(6*ROW(A1)-6),6)=OFFSET(C$23,MAX(6*ROW(A1)-6),6
横着拖?
把公式中的C$17改成
indirect("C"&column(A:A)*6+11)
就行了
可是,你的公式,我怎么看,都适合于竖着拖
如果竖着拖,则把C$17改成
indirect("C"&row(1:1)*6+11)