请大家帮我把此公式简化一下。谢谢。

xw8858 · 发表于 2010-8-12 10:30

d33=IF(IF(D$1="杀码",MOD(OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$9,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$10="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$10,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$11="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$11,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$12="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$12,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$13="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$13,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$14="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$14,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$15="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$15,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$16="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$16,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$17="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$17,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$18="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$18,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$19="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$19,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$20="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$20,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$21="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$21,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$22="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$22,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$23="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$23,INDIRECT(D$7&"!A1:cz1"),)-1)))))))))))))))+E$8,49),MOD(OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$9,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$10="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$10,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$11="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$11,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$12="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$12,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$13="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$13,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$14="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$14,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$15="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$15,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$16="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$16,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$17="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$17,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$18="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$18,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$19="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$19,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$20="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$20,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$21="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$21,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$22="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$22,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$23="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$23,INDIRECT(D$7&"!A1:cz1"),)-1)))))))))))))))+E$8,12))=0,"49",IF(D$1="杀码",MOD(OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$9,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$10="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$10,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$11="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$11,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$12="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$12,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$13="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$13,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$14="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$14,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$15="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$15,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$16="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$16,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$17="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$17,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$18="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$18,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$19="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$19,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$20="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$20,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$21="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$21,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$22="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$22,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$23="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$23,INDIRECT(D$7&"!A1:cz1"),)-1)))))))))))))))+E$8,49),MOD(OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$9,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$10="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$10,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$11="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$11,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$12="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$12,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$13="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$13,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$14="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$14,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$15="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$15,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$16="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$16,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$17="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$17,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$18="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$18,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$19="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$19,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$20="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$20,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$21="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$21,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$22="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$22,INDIRECT(D$7&"!A1:cz1"),)-1)+IF(D$23="",,OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$23,INDIRECT(D$7&"!A1:cz1"),)-1)))))))))))))))+E$8,12)))

[ 本帖最后由 xw8858 于 2010-8-12 10:40 编辑 ]

xw8858 · 发表于 2010-8-12 10:40

解释一下，免得大家看的头疼。（看附件）
d33 是自动把d8-d30里面所给出的条件对应c33的行号从前面的表格里面查询出来的结果相加，然后大于49的就除以49之后的余数。

amarantine · 发表于 2010-8-12 10:59

能满足要求就行,反正打开又不卡,长度达到7288字节的公式怎么优化也短不了

踏鞋寻觅 · 发表于 2010-8-12 11:04

解释有点绕，

piny · 发表于 2010-8-12 18:02

同意大家的說法，砍掉您的公式，詳細敘述您的實際需求，總是會有好方法可以實現的 ^^

xw8858 · 发表于 2010-8-12 13:58

原帖由 amarantine 于 2010-8-12 10:59 发表
能满足要求就行,反正打开又不卡,长度达到7288字节的公式怎么优化也短不了

不行啊。要简化因为我还有东西没加进去。

xw8858 · 发表于 2010-8-12 14:51

拜托拜托。不要沉鸟~~~

amarantine · 发表于 2010-8-12 16:22

定义名称可以短很多
比如 A=OFFSET(INDIRECT(D$7&"!A1"),MATCH($C33,INDIRECT(D$7&"!A1:A3000"),)-1,MATCH(D$10,INDIRECT(D$7&"!A1:cz1"),)-1)

zljkd007 · 发表于 2010-8-12 16:35

7000多字符

楼主要简化最好把原来的公式删了，然后明确的提出要求，由别人重新帮你写公式！7000多字符基本没人看的！

laowuwyx · 发表于 2010-8-12 16:39

类似的长公式我已收藏了3个，这个绝不放过。

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

[求助] 请大家帮我把此公式简化一下。谢谢。