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

baiii

darkjazz 发表于 2017-4-12 17:00
=SUMIF(A:A,{"NSW","QLD","WA"},C:C)
这样吗？还是漏了其他条件？

=sum(SUMIF(A:A,{"NSW","QLD","WA"},C:C))
少了一层sum,你的公式只返回一个state总和

rebecca83

楼主愿意分享一下您的career path吗？你在Treasury负责的部门是budgeting还是treasury analysts？如果是前者，那么后来是怎么转去做分析师的呢？

darkjazz

baiii 发表于 2017-4-12 17:15
=sum(SUMIF(A:A,{"NSW","QLD","WA"},C:C))
少了一层sum,你的公式只返回一个state总和

对啊，忘记了加一层，谢谢指出，或者写成基本函数如下

{=SUM(IF((A:A)<>"ACT",IF((A:A)<>"VIC",C:C,0),0))}

kwikkopy

这贴太赞了！希望楼主多传授点分析技巧。Orz

ExcelMVP

上边朋友的答案非常完美 =sum(SUMIF(A:A,{"NSW","QLD","WA"},C:C)).

但因为这是Array Formula, 所以不要忘了要同时按 Ctrl + Shift + Enter. 还有另外一种办法, 如果Sumif条件特别多的话, 可以把条件放在Column D (D1 : D3) or more.

= Sum (SUMIF(A : A , D1 : D3 , C : C)). 然后 Ctrl + Shift + Enter.

cotta

现在的数据分析已经迈入tableau+big data时代了
虽然excel很强大，但是对于2mil以上的数据无力，
Cognos很IBM，但是维护成本太高，运行reports慢的要去砸电脑

ExcelMVP

Tableau 是更多关于如何组织和可视化数据&创建图表, 另一方面Excel具有更大的灵活性, 他们有自己不同的优势

kikizhu7

忽然觉得自己不会excel了 好恐慌......

ExcelMVP

上周末公司一个Planning Analyst问我一个问题, 因为他经常需要从后台下载数据然后做数据整理, 如果原始数据有 ”:” 就截取 ”:” 右边的数据, 如果没有”:”就保持原样. 他问我在不使用 "Text to Column "的前提下(因为样本有几万行而且很杂乱)能否用公式解决这个问题? 我觉得这是一个既常见又有趣的问题, 所以想在这里和大家讨论一下
Original Data                    Result
201745B                           201745B
12T:201712T                    201712T
201741A                           201741A
1235G:201745B               201745B

floodp

=IF(ISERROR(SEARCH(":",A2))=FALSE, RIGHT(A2,LEN(A2)-SEARCH(":",A2)),A2)

yingying.li001

一粒米 发表于 2017-4-10 18:48
想借贴问一下word mail merge的东西。有5行资料，第三行有些是空白，有些有资料，怎么能让那些空白的自动删 ...

F5, find special, -blank cell, delete

yingying.li001

ExcelMVP 发表于 2017-4-17 20:12
上周末公司一个Planning Analyst问我一个问题, 因为他经常需要从后台下载数据然后做数据整理, 如果原始数据 ...

這个right应该可以搞定吧

baiii

直接使用replace功能可以实现，“*：”替换成blank

syd1820

好难

ExcelMVP

yingying.li001 发表于 2017-4-18 09:12
這个right应该可以搞定吧

這个使用 Right 可能还不够, 因为你不知道 ":" 右边有多少Text, 所以你需要使用 Search 结合 Right 以及 Iferror

大熊

突然发现关注excel的也有这么多人。

学习

ExcelMVP

floodp 发表于 2017-4-17 20:42
=IF(ISERROR(SEARCH(":",A2))=FALSE, RIGHT(A2,LEN(A2)-SEARCH(":",A2)),A2)

floodp, 你太棒了, 这是一个几乎完美的答案.

但有一种极端的情况, 如果原始数据有超过1个 ":",  例如  2017:2145:201712T, 只提取 201712T 这就比较困难

baiii

ExcelMVP 发表于 2017-4-18 10:46
floodp, 你太棒了, 这是一个几乎完美的答案.

但有一种极端的情况, 如果原始数据有超过1个 ":",  例如  2 ...

使用replace "*:"，换成blank就可以了，搞定所有情况

ExcelMVP

baiii 发表于 2017-4-18 10:51
使用replace "*:"，换成blank就可以了，搞定所有情况

这个办法非常有创造力, 并且更易于使用.

还有另一种方法, 如果你想使用公式 =TRIM(RIGHT(SUBSTITUTE(TRIM(A2), ":", REPT(" ", 20)), 20))

floodp

baiii 发表于 2017-4-18 10:51
使用replace "*:"，换成blank就可以了，搞定所有情况

这个怎么写公式呢？刚狗了一下replace函数，还是没头绪。

floodp

ExcelMVP 发表于 2017-4-18 11:05
这个办法非常有创造力, 并且更易于使用.

还有另一种方法, 如果你想使用公式 =TRIM(RIGHT(SUBSTITUTE(TRI ...

这个牛。
现在有种做中学数学题的感觉，一道题可能有十种解法。

ExcelMVP

floodp 发表于 2017-4-18 11:21
这个怎么写公式呢？刚狗了一下replace函数，还是没头绪。

Replace不是一个公式, 它是一个Excel功能. Find + Replace.

在这个例子中, 选择原始数据, 然后使用 Find What "*:" then Replace with Blank

floodp

ExcelMVP 发表于 2017-4-18 11:42
Replace不是一个公式, 它是一个Excel功能. Find + Replace.

在这个例子中, 选择原始数据, 然后使用 Find ...

哦是这样，因为replace也是一个函数，所以想当然以为是在公式里。

20100

借这个贴也请教各位一个问题，请问是否可以用某个公式把左边的数据格式变成右边的数据格式？

        Jan                Feb                Mar                Apr       
        Value        Qty        Value        Qty        Value        Qty        Value        Qty
Apple        100        10        200        20        300        30        400        40
Banana        200        20        300        30        300        30        300        30
Grape        120        12        140        14        160        16        200        20

cathy2008

Mark

baiii

20100 发表于 2017-4-18 20:18
借这个贴也请教各位一个问题，请问是否可以用某个公式把左边的数据格式变成右边的数据格式？

        Jan                Feb         ...

如果你要用公式，公式只能帮你填入数据，表要你手动建出来。公式使用Index match组合，你可以把各个月份和各个水果合并重新创建一个辅助index，不然要套两层公式。
这实际是一个Unpivot的过程，如果你使用2016版本的excel,有unpivot table功能，很简单就能完成~如果你的excel没有Unpivot功能，你又想免除手动部分的话就得写vba

ExcelMVP

baiii 发表于 2017-4-18 22:51
如果你要用公式，公式只能帮你填入数据，表要你手动建出来。公式使用Index match组合，你可以把各个月份 ...

你是对的, 在这种情况下只能使用 Power Query 的 Unpivot Function. 或者使用 VBA

gateavalon

标记Bold， 然后写一个SDF， sumbold，打完收工

gateavalon

pusabaoyou 发表于 2017-4-10 13:45
这里有参加model off 的大霞吗

那是什么？

20100

baiii 发表于 2017-4-18 22:51
如果你要用公式，公式只能帮你填入数据，表要你手动建出来。公式使用Index match组合，你可以把各个月份 ...

我们的系统是2016的，用了你说的办法，不太熟练，半自动半手工地做完了，真的非常感谢 。
给你评分留言时，多打了一个错字，可是又不能编辑改正了，不好意思