表格标题行是月份的数值,创建列表后公式认不到月份了,该怎么办?

别雨寒 · 发表于 2008-3-13 10:37

工作表"年度报表",格标题行是月份的数值,创建列表后公式认不到月份了,该怎么办?<table cellspacing="0" cellpadding="0" width="981" border="0" xstr="" style="WIDTH: 738pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="71" style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2272;"></col><col></col><col width="189" style="WIDTH: 142pt; mso-width-source: userset; mso-width-alt: 6048;"></col><col></col><col width="74" style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2368;"></col><col></col><col width="69" style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2208;"></col><col></col><col span="5" width="52" style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1664;"></col><col></col><col span="6" width="53" style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1696;"></col><col></col></colgroup><tbody><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl25" width="71" height="19" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">列1</td><td class="xl25" width="189" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 142pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">列2</td><td class="xl25" width="74" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">列3</td><td class="xl25" width="69" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">列4</td><td class="xl25" width="52" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 39pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">列5</td><td class="xl25" width="52" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 39pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">列6</td><td class="xl25" width="52" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 39pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">列7</td><td class="xl25" width="52" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 39pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">列8</td><td class="xl25" width="52" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 39pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">列9</td><td class="xl25" width="53" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 40pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">列10</td><td class="xl25" width="53" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 40pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">列11</td><td class="xl25" width="53" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 40pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">列12</td><td class="xl25" width="53" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 40pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">列13</td><td class="xl25" width="53" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 40pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">列14</td><td class="xl25" width="53" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 40pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">列15</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl25" height="19" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">单位编码</td><td class="xl25" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">单位名称</td><td class="xl25" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">期初应收</td><td class="xl25" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">1</td><td class="xl25" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">2</td><td class="xl25" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">3</td><td class="xl25" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">4</td><td class="xl25" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">5</td><td class="xl25" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">6</td><td class="xl25" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">7</td><td class="xl25" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">8</td><td class="xl25" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">9</td><td class="xl25" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">10</td><td class="xl25" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">11</td><td class="xl25" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">12</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl24" height="19" xnum="3301001" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">3301001</td><td class="xl24" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">杭州利伟来婴童用品生活馆</td><td class="xl24" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">　</td><td align="right" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">0</td><td align="right" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">501.2</td><td align="right" xnum="1002.4" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">1002</td><td align="right" xnum="1002.4" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">1002</td><td align="right" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">0</td><td align="right" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">0</td><td align="right" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">0</td><td align="right" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">0</td><td align="right" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">0</td><td align="right" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">0</td><td align="right" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">0</td><td align="right" xnum="" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">0</td></tr></tbody></table>所以只好说没标题,但列表汇总是把月份的值也加进去了.

GnqAgSLd.rar (17 KB, 下载次数: 10)

皮皮1998 · 发表于 2008-3-13 11:15

=SUMPRODUCT((销售!$C$2:$C$10=$A3)*(MONTH(销售!$B$2:$B$10)&"月份"=D$2)*销售!$K$2:$K$10)

皮皮1998 · 发表于 2008-3-13 12:59

也可以按照主任的办法:把后面截取出来,然后再变成数值=SUMPRODUCT((销售!$C$2:$C$10=$A3)*(MONTH(销售!$B$2:$B$10)=LEFT(E$2)*1)*销售!$K$2:$K$10)

皮皮1998 · 发表于 2008-3-13 13:05

再发一个,我的等级就是青铜二级了!D3=SUMPRODUCT((销售!$C$2:$C$10=$A3)*(MONTH(销售!$B$2:$B$10)=COLUMN()-3)*销售!$K$2:$K$10)

qyszmmj · 发表于 2008-3-13 15:28

支持楼上的

		自动登录	找回密码
密码			注册

[资料] 表格标题行是月份的数值,创建列表后公式认不到月份了,该怎么办?