Excel &VBA交流学习 (一楼更新）

darkjazz

david626 发表于 2017-4-10 11:47
我在工作中碰到过和这个题目类似的情况，但还多了一列。需要计算每个州在不同时期的销售总额，有什么好方 ...

数据格式放出来看一下 否则太抽象了 呵呵

ExcelMVP

如果您可以提供样品报告，也许我们可以提出一些解决方案

dwill

在sumproduct里面建立两个判定array。

一粒米

想借贴问一下word mail merge的东西。有5行资料，第三行有些是空白，有些有资料，怎么能让那些空白的自动删除（即第四行直接出现在第二行下边）。试了用If，then，else的程序，好像太繁琐了。有没有简单点的？

david626

darkjazz 发表于 2017-4-10 15:59
数据格式放出来看一下 否则太抽象了 呵呵

如下，还要对各州的数据进行加总。比如NSW从02/01/2017到08/01/2017的总合。有其他笨一点的方法，比如分两步算。就是想看看可不可以用一个公式解决的。


State        Date           Sales
   NSW      1/1/2017      123
   NSW      2/1/2017      248
   ACT       3/1/2017      354
   NSW      4/1/2017      189
   QLD       5/1/2017      177
   ACT       6/1/2017       285
   ACT       7/1/2017       184
   QLD       8/1/2017       321
   ACT       9/1/2017       119

baiii

david626 发表于 2017-4-11 10:55
如下，还要对各州的数据进行加总。比如NSW从02/01/2017到08/01/2017的总合。有其他笨一点的方法，比如分 ...

我会用pivot table,为什么执著于公式呢

david626

baiii 发表于 2017-4-11 11:24
我会用pivot table,为什么执著于公式呢

第一，任意两个时间点加总用pivot table 好像算不出来（也许可以，反正我不会）。 第二 如果是很大的表，这些数据还要被用在下面其他的公式里， hard code 用起来不方便

baiii

david626 发表于 2017-4-11 11:58
第一，任意两个时间点加总用pivot table 好像算不出来（也许可以，反正我不会）。 第二 如果是很大的表， ...

date作为filter，随便你怎么选啊~
如果是个大表，数值还要在公式间不停refer的吧，不建议这么做，excel很容易崩溃的。如果这是一个monthly report，建议写vba自动化，date，state或者其他变量可以在一个control sheet里改
数据量大到一定程度，excel就不是一个最佳工具了

bt123123

idlepoison 发表于 2017-4-7 18:01
因为你没有define name或者选择sales 和date

what's the benefit to define name?

david626

baiii 发表于 2017-4-11 13:07
date作为filter，随便你怎么选啊~
如果是个大表，数值还要在公式间不停refer的吧，不建议这么做，excel很 ...

感觉filter和sort之类对于单一任务和少量数据比较合适。 这个例子中以天为单位加总， 如果要是一年的数据，肯定没法用filter选日期的。
Excel处理几万行的数据还是绰绰有余的， 再多就不知用啥了。

baiii

david626 发表于 2017-4-11 14:02
感觉filter和sort之类对于单一任务和少量数据比较合适。 这个例子中以天为单位加总， 如果要是一年的数据 ...

最新的excel已经可以自动group date了，你也可以自己手动aggregate date to month, year etc
如果你要选择任意不连续的几天，是很不方便，用公式的话更不会简单
我觉得用公式解决这些问题不是最佳方案，尤其是表连表的情况，一旦出错，你得重头纠错；其他人也不容易接手，看着你一长串的公式... ...
处理大量数据的工具好多咧，简单上手的tableau，和excel比较相似

ExcelMVP

这是上面朋友问题的解决方案, 非常简单,请尝试一次.

=Sumproduct((State="NSW")*(Date>=Start Date)*(Date<=End Date)*Sales)


如果您有其他问题，请不要犹豫随时问, 欢迎更多的问题.

david626

ExcelMVP 发表于 2017-4-11 14:45
这是上面朋友问题的解决方案, 非常简单,请尝试一次.

=Sumproduct((State="NSW")*(Date>=Start Date)*(Date ...

多谢了

xingxingwang

好帖，顶ing。学习了很多。谢谢各位！

david626

请问楼主,这些公式都是自己通过实际工作琢磨出来的,还是有资料可以查询.

ExcelMVP

有些是自己琢磨出来的, 有些是和朋友和同事相互交流.
所以我在这里发这个帖子, 就是希望提供一个平台, 这样朋友门之间可以交流. 希望朋友们踊跃参加, 提供自己的宝贵意见.

vinsonz

加分支持楼主，感谢分享!

sunpower03

darkjazz 发表于 2017-4-7 19:10
试了一下，见笑

高阿

Ocean3mel

sumproduct 最近常常用到，很强大，很喜欢。

excel 用了很久，只是比较熟悉而已。普通的公式和pivot table （最近发现pivot chart 也很好）会用，可是一上升到需要设计report template，对 VBA 和 macro 有点望而却步。尤其是 record 不好使的时候，不知道VBA里怎么修正。

VBA 入门从哪里开始比较好呢？请楼主给深入简出地讲讲。谢谢！

doll-bear

Ocean3mel 发表于 2017-4-12 09:18
sumproduct 最近常常用到，很强大，很喜欢。

excel 用了很久，只是比较熟悉而已。普通的公式和pivot table ...

工作中也会用到一些macro，我一般都是google一个solution然后根据自己的情况慢慢改

darkjazz

doll-bear 发表于 2017-4-12 10:22
工作中也会用到一些macro，我一般都是google一个solution然后根据自己的情况慢慢改 ...

之前有试用过Macro但觉得运用范围小 适合变化很少的标准化报表，但是很多时候我们做的都是user defined report, Macro好像不太适用， VBA 我就是一直不得门径 好像需要编程的基础？很想学 另外，很多分析报表现在BI软件都能覆盖了，所以掌握一个BI系统很有帮助 我推荐Tableau

darkjazz

Ocean3mel 发表于 2017-4-12 09:18
sumproduct 最近常常用到，很强大，很喜欢。

excel 用了很久，只是比较熟悉而已。普通的公式和pivot table ...

本人也觉得sumproduct是很强大，新的sumifs虽然比它简单容易上手，但是牵涉到多个表文件的话就死翘翘(因为所有牵涉的表都要active,就是都要打开，否则公式出error结果), sumproduct可以在不打开其他表文件的基础上实习条件加总 好用

step2au

高人。

Ocean3mel

darkjazz 发表于 2017-4-12 11:15
之前有试用过Macro但觉得运用范围小 适合变化很少的标准化报表，但是很多时候我们做的都是user defined r ...

公司电脑装不了其他软件，还是在excel内解决问题比较实际。

doll-bear

darkjazz 发表于 2017-4-12 11:15
之前有试用过Macro但觉得运用范围小 适合变化很少的标准化报表，但是很多时候我们做的都是user defined r ...

对的，我用macro主要是一些copy paste，一次性lock/unlock所有tabs之类的重复性活动

jwycga

step2au

请教我目前只会lookup,sumif 等基本的功能，初涉povit table要达到以上各位水准要多久

voicar

1        =SUMPRODUCT((A2:A10>=D2)*(A2:A10<=E2)*B2:B10)
2        =SUM((A2:A10>=D2)*(A2:A10<=E2)*B2:B10)
3        =SUMIFS(B2:B10,A2:A10,">="&D2,A2:A10,"<="&E2)
4        Macro
方法二，公式输入后同时按Ctrl+Shift+Enter

1. Sub SumDate()
   
2.     Dim Arr, i&
   
3.    
   
4.     [e8] = 0
   
5.     Arr = Range("a1:z100")
   
6.     For i = 2 To UBound(Arr)
   
7.         If Arr(i, 1) >= [d2] And Arr(i, 1) <= [e2] Then
   
8.             [e8] = [e8] + Arr(i, 2)
   
9.         End If
   
10.     Next
    
11. End Sub

复制代码

ExcelMVP

我有另一个有趣的问题.  不能使用多个sumif, 您可以使用一个单一的sumif公式来计算NSW, QLD and WA的总和吗?

State        Date           Sales
    NSW      1/1/2017      123
    NSW      2/1/2017      248
    ACT       3/1/2017      354
    NSW      4/1/2017      189
    QLD       5/1/2017      177
    ACT       6/1/2017       285
    VIC       7/1/2017       184
    QLD       8/1/2017       321
    WA       9/1/2017       119

darkjazz

ExcelMVP 发表于 2017-4-12 16:17
我有另一个有趣的问题.  不能使用多个sumif, 您可以使用一个单一的sumif公式来计算NSW, QLD and WA的总和吗 ...

=SUMIF(A:A,{"NSW","QLD","WA"},C:C)
这样吗？还是漏了其他条件？