Office中国论坛/Access中国论坛

标题: 怎么才能设置公式把不是零的数据取出来 [打印本页]

---

作者: crmen    时间: 2006-10-2 01:11
标题: 怎么才能设置公式把不是零的数据取出来


<TABLE cellSpacing=0 cellPadding=0 width=1158 border=0 x:str>

<COLGROUP>

<COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1280" width=40>

<COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1184" span=8 width=37>

<COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1568" width=49>

<COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1184" span=2 width=37>

<COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1696" span=4 width=53>

<COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1440" width=45>

<COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1184" span=2 width=37>

<COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1440" span=3 width=45>

<COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1696" width=53>

<COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1440" span=4 width=45>





1



0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0



2



0

0

0

0

0

0

17

0

26

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

0

<

---

作者: czzqb    时间: 2006-10-2 05:01
数组公式：=SMALL(IF($A$1P$1,$A$1P$1),COLUMN(A1))

向右拉

---

作者: crmen    时间: 2006-10-2 05:20
不行呀,老大

---

作者: pureshadow    时间: 2006-10-2 05:42
条件格式啊，工具选项中的零值取消啊，套个IF公式啊，N多种方法呢，看你爱好了哈。

---

作者: 大少    时间: 2006-10-2 19:31
=IF(COUNTIF($A1AB1,"<>0")<COLUMN(A1),"",INDEX($A1AB1,SMALL(IF($A1AB1<>0,COLUMN($A$1:$AB$1),256),COLUMN(A1))))

数组公式,右拖下拖[attach]20693[/attach]

---

作者: crmen    时间: 2006-10-4 04:13
标题: 怎么我把AB改成IV就不行了呀，我需要取数到IV列
=IF(COUNTIF($A1AB1,"<>0")<COLUMN(B1),"",INDEX($A1AB1,SMALL(IF($A1AB1<>0,COLUMN($A$1:$AB$1),256),COLUMN(B1))))

改成

=IF(COUNTIF($A1:$IV1,"<>0")<COLUMN(A1),"",INDEX($A1:$IV1,SMALL(IF($A1:$IV1<>0,COLUMN($A$1:$IV$1),256),COLUMN(A1))))

就不能用了，郁闷

---

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)

Powered by Discuz! X3.3