Board logo

标题: Excel烧脑袋系列又来了 [打印本页]
作者: maximus    时间: 2016-9-22 10:21     标题: Excel烧脑袋系列又来了

钞票如纸张张薄，问题如棋次次新。

我又创造了一个新难题。[45]

如附件
不用数据透析表。
当ABC三列放进数据时候。

设计好的公式区域刷新统计：

统计按这样的规律：

提取A列相同，B列不为滞纳金 的名称：统计合计数
提取A列相同，B列为滞纳金 的合并名称：统计合计数

附件: 统计公式表.xls (2016-9-22 10:21, 74.5 KB) / 该附件被下载次数 84
http://hahabbs.w1.luyouxia.net/bbs/attachment.php?aid=148764
作者: 匿名    时间: 2016-9-22 10:36

提取A列相同，B列不为滞纳金 的名称：统计合计数
提取A列相同，B列为滞纳金 的合并名称：统计合计数

这两句话是什么意思？
作者: 匿名    时间: 2016-9-22 10:37

不是直接筛选就可以了吗？
作者: 匿名    时间: 2016-9-22 10:38

引用:
原帖由 Guest from 150.48.48.x 于 2016-9-22 10:36 发表
提取A列相同，B列不为滞纳金 的名称：统计合计数
提取A列相同，B列为滞纳金 的合并名称：统计合计数

这两句话是什么意思？
提取A列相同，B列不带滞纳金 的统计合计数
提取A列相同，B列带滞纳金 的统计合计数

是这样的意思吗？
作者: maximus    时间: 2016-9-22 10:53

引用:
原帖由 Guest from 150.48.48.x 于 2016-9-22 10:38 发表


提取A列相同，B列不带滞纳金 的统计合计数
提取A列相同，B列带滞纳金 的统计合计数

是这样的意思吗？
是的
作者: maximus    时间: 2016-9-22 10:54

我自己也在设计中。找到一点思路了。

弄完也上传给大家好提提建议。
作者: 匿名    时间: 2016-9-22 11:43

G3

=IF(RIGHT(F3,3)="滞纳金",SUMPRODUCT(($A$2:$A$977=MID(F3,1,LEN(F3)-4))*($B$2:$B$977="滞纳金")*($C$2:$C$977)),SUMPRODUCT(($A$2:$A$977=F3)*($C$2:$C$977)))

(数组公式，三键结束)

再把公式向下拖

统计公式表(1).xls (79 KB)

附件: 统计公式表(1).xls (2016-9-22 11:43, 79 KB) / 该附件被下载次数 42
http://hahabbs.w1.luyouxia.net/bbs/attachment.php?aid=148765
作者: 匿名    时间: 2016-9-22 11:48

=SUMIFS(C2:C977,A2:A977,"城镇土地使用税")
=SUMIFS(C2:C977,A2:A977,"城镇土地使用税")-SUMIFS(C2:C977,A2:A977,"城镇土地使用税",B2:B977,"滞纳金")

你自己测试下吧，其它同上，我没验证过其它，起码这个土地税的对了。
作者: rx782    时间: 2016-9-22 12:00

SUMPRODUCT+if都可以做到
作者: 夜鹰    时间: 2016-9-22 14:26

引用:
原帖由 Guest from 150.96.52.x 于 2016-9-22 11:43 发表
G3

=IF(RIGHT(F3,3)="滞纳金",SUMPRODUCT(($A$2:$A$977=MID(F3,1,LEN(F3)-4))*($B$2:$B$977="滞纳金")*($C$2:$C$977)),SUMPRODUCT(($A$2:$A$977=F3)*($C$2:$C$977)))

(数组公式，三键结束)

再把公式向下拖
...
上午无仔细看懂楼主要求，现更正7楼

G3

=IF(RIGHT(F3,3)="滞纳金",SUMPRODUCT(($A$2:$A$977=MID(F3,1,LEN(F3)-4))*($B$2:$B$977="滞纳金")*($C$2:$C$977)),SUMPRODUCT(($A$2:$A$977=F3)*($C$2:$C$977))-SUMPRODUCT(($A$2:$A$977=F3)*($B$2:$B$977="滞纳金")*($C$2:$C$977)))

把公式向下拖

统计公式表(1).xls (77 KB)

附件: 统计公式表(1).xls (2016-9-22 14:26, 77 KB) / 该附件被下载次数 37
http://hahabbs.w1.luyouxia.net/bbs/attachment.php?aid=148776
作者: 匿名    时间: 2016-9-22 15:33

我这个也是用公式“SUMPRODUCT”实现的，用楼主你发的附件改的。上下两个框，上面框是楼主你原来统计出来的，下面框是我用公式计算出来的，结果应该一致，你看看。

附件: 统计公式表.xls (2016-9-22 15:33, 75 KB) / 该附件被下载次数 38
http://hahabbs.w1.luyouxia.net/bbs/attachment.php?aid=148777
作者: 匿名    时间: 2016-9-22 15:49     标题: 回复 11楼帖子 的帖子

您这个方法有个缺陷，请看包含“滞纳金”的税种，里边求和有误。坛友“夜鹰”方法是正确的。
作者: 不能道尽    时间: 2016-9-22 16:15

SUMIF也可以

附件: 统计公式表.xls (2016-9-22 16:15, 84 KB) / 该附件被下载次数 39
http://hahabbs.w1.luyouxia.net/bbs/attachment.php?aid=148779
作者: 不能道尽    时间: 2016-9-22 16:38

貌似大家都是一列

附件: 22222.xls (2016-9-22 16:38, 83.5 KB) / 该附件被下载次数 31
http://hahabbs.w1.luyouxia.net/bbs/attachment.php?aid=148780
作者: maximus    时间: 2016-9-22 16:46

引用:
原帖由 夜鹰 于 2016-9-22 14:26 发表


上午无仔细看懂楼主要求，现更正7楼

G3

=IF(RIGHT(F3,3)="滞纳金",SUMPRODUCT(($A$2:$A$977=MID(F3,1,LEN(F3)-4))*($B$2:$B$977="滞纳金")*($C$2:$C$977)),SUMPRODUCT(($A$2:$A$977=F3)*($C$2:$C$977))-SU ...
这个公式不错。[24]

我刚好弄好前面那部分，后面就用你的。

请看附件。

1.征收项目是自动生成的。这样可以在A列放入任何数据，因为有时候征收项目可能会有变动（增加或减少）
2.增加了一个输入单元格E1：统计会根据输入的内容进行变化。（比如输入“滞纳金”或“个体户生产经营所得”等B列有的内容）

设计缺陷：
1。没把税和费的排列顺序规范
2。统计表中间有空白行
3。E1如果输入B列内容的一部分或者不输入内容，统计会出错，必须限制有效范围。
4。用wps打开使用飞快，用excel一旦E1变化，反应缓慢，这是什么回事？

附件: 统计公式表(2).xls (2016-9-22 16:46, 1.7 MB) / 该附件被下载次数 33
http://hahabbs.w1.luyouxia.net/bbs/attachment.php?aid=148781
作者: maximus    时间: 2016-9-22 16:57

引用:
原帖由 不能道尽 于 2016-9-22 16:15 发表
SUMIF也可以
厉害。公式简单准确。
作者: maximus    时间: 2016-9-22 16:58

引用:
原帖由 不能道尽 于 2016-9-22 16:38 发表
貌似大家都是一列
这个公式也很强大。正在学习里面的逻辑。
作者: maximus    时间: 2016-9-22 17:01

引用:
原帖由 不能道尽 于 2016-9-22 16:15 发表
SUMIF也可以
比较了几个公式。这个最简洁有力[12]
作者: maximus    时间: 2016-9-22 17:19

引用:
原帖由 夜鹰 于 2016-9-22 14:26 发表


上午无仔细看懂楼主要求，现更正7楼

G3

=IF(RIGHT(F3,3)="滞纳金",SUMPRODUCT(($A$2:$A$977=MID(F3,1,LEN(F3)-4))*($B$2:$B$977="滞纳金")*($C$2:$C$977)),SUMPRODUCT(($A$2:$A$977=F3)*($C$2:$C$977))-SU ...
这公式的扩展性最好！！！[12]
作者: maximus    时间: 2016-9-22 17:57

引用:
原帖由 不能道尽 于 2016-9-22 16:15 发表
SUMIF也可以
这个微微修改一下就很合适我原来要的效果
用来做平时的税费统计很好用。

E2可以录入其他条件

附件: 统计公式表 (2.2).xls (2016-9-22 17:57, 76.5 KB) / 该附件被下载次数 32
http://hahabbs.w1.luyouxia.net/bbs/attachment.php?aid=148806
作者: 不能道尽    时间: 2016-9-23 09:07     标题: 回复 20楼帖子 的帖子

G列应该
=SUMIFS(C:C,A:A,F3,B:B,"<>"&$E$2)
才能正确排除E2单元格的内容
"<>E2"只会排除文本为E2的项目
作者: maximus    时间: 2016-9-23 09:22

引用:
原帖由 不能道尽 于 2016-9-23 09:07 发表
G列应该
=SUMIFS(C:C,A:A,F3,B:B,""&$E$2)
才能正确排除E2单元格的内容
"E2"只会排除文本为E2的项目
谢谢指正。我就有奇怪为什么排除不了。所以用-F2来达到目的。
现在已经修正了。
作者: maximus    时间: 2016-9-23 09:48

引用:
原帖由 maximus 于 2016-9-22 16:46 发表



这个公式不错。[24]

我刚好弄好前面那部分，后面就用你的。

请看附件。

1.征收项目是自动生成的。这样可以在A列放入任何数据，因为有时候征收项目可能会有变动（增加或减少）
2.增加了一个输入单元 ...
提取列中不重复的内容。大家有什么更好的公式？
作者: 匿名    时间: 2016-9-23 09:57     标题: 回复 23楼帖子 的帖子

参考：=INDEX($A$2:$A$977,MATCH(0,COUNTIF(N$1:N1,$A$2:$A$977),0))，然后同时按Ctrl+Shift+Enter。
作者: maximus    时间: 2016-9-23 10:18

引用:
原帖由 Guest from 150.48.60.x 于 2016-9-23 09:57 发表
参考：=INDEX($A$2:$A$977,MATCH(0,COUNTIF(N$1:N1,$A$2:$A$977),0))，然后同时按Ctrl+Shift+Enter。
这个公式能否调整为：返回空值时显示为空？
作者: zhendeaini    时间: 2016-9-23 11:17     标题: 回复 25楼帖子 的帖子

参考：
=IF(ISERROR(INDEX($A$2:$A$977,MATCH(0,COUNTIF(N$1:N1,$A$2:$A$977),0))),"",INDEX($A$2:$A$977,MATCH(0,COUNTIF(N$1:N1,$A$2:$A$977),0)))，然后同时按Ctrl+Shift+Enter。[68]
作者: maximus    时间: 2016-9-23 12:04

引用:
原帖由 不能道尽 于 2016-9-22 16:38 发表
貌似大家都是一列
能解说下这个22222.xls公式的逻辑吗？
这公式能否把里面的“滞纳金”替换成指定单元格。如果征收“城镇土地使用税 滞纳金”里面去掉空格。需要怎么修改公式？
作者: maximus    时间: 2016-9-23 12:05

引用:
原帖由 zhendeaini 于 2016-9-23 11:17 发表
参考：
=IF(ISERROR(INDEX($A$2:$A$977,MATCH(0,COUNTIF(N$1:N1,$A$2:$A$977),0))),"",INDEX($A$2:$A$977,MATCH(0,COUNTIF(N$1:N1,$A$2:$A$977),0)))，然后同时按Ctrl+Shift+Enter。[68]
这个如果A列放入的数据不满977.会有一行显示为“0”。
作者: zhendeaini    时间: 2016-9-23 15:47     标题: 回复 28楼帖子 的帖子

参考：
=IF(ISERROR(INDEX(INDIRECT("$A$2:$A$"& COUNTA(A:A)),MATCH(0,COUNTIF(N$1:N1,INDIRECT("$A$2:$A$"& COUNTA(A:A))),0))),"",INDEX(INDIRECT("$A$2:$A$"& COUNTA(A:A)),MATCH(0,COUNTIF(N$1:N1,INDIRECT("$A$2:$A$"& COUNTA(A:A))),0)))
作者: maximus    时间: 2016-9-26 08:58

@不能道尽
@zhendeaini

能解说下这个22222.xls公式的逻辑吗？
这公式能否把里面的“滞纳金”替换成指定单元格。如果征收“城镇土地使用税 滞纳金”里面去掉空格。需要怎么修改公式？

附件: 22222.xls (2016-9-26 08:58, 76.5 KB) / 该附件被下载次数 24
http://hahabbs.w1.luyouxia.net/bbs/attachment.php?aid=148906
作者: zhendeaini    时间: 2016-10-8 09:34     标题: 回复 30楼帖子 的帖子

如果剔除空格，可以参考以下公式：
=IF(RIGHTB(I3,6)<>"滞纳金",SUMIFS(C:C,A:A,I3,B:B,"<>滞纳金"),SUMIFS(C:C,A:A,IF(ISERROR(LEFT(I3,FIND("滞",I3,1)-1)),I3,LEFT(I3,FIND("滞",I3,1)-1)),B:B,"滞纳金"))

话说，习惯使用Office 2003版本，很多新函数也在摸索中。[2]

附件: 22222.xls (2016-10-8 09:34, 77.5 KB) / 该附件被下载次数 28
http://hahabbs.w1.luyouxia.net/bbs/attachment.php?aid=149275
作者: maximus    时间: 2016-10-10 08:47

引用:
原帖由 zhendeaini 于 2016-10-8 09:34 发表
如果剔除空格，可以参考以下公式：
=IF(RIGHTB(I3,6)"滞纳金",SUMIFS(C:C,A:A,I3,B:B,"滞纳金"),SUMIFS(C:C,A:A,IF(ISERROR(LEFT(I3,FIND("滞",I3,1)-1)),I3,LEFT(I3,FIND("滞",I3,1)-1)),B:B,"滞纳金"))

话说， ...
谢谢版主。[12]
作者: zhendeaini    时间: 2016-10-10 14:45     标题: 回复 32楼帖子 的帖子

客气，或许你有更好的解决办法。多交流，多共享。[0]



欢迎光临 BBS (http://hahabbs.w1.luyouxia.net/bbs/) Powered by Discuz! 6.0.0