Sydney BI

平平淡淡

我是做Business Intelligence的,工作中涉及到的工具有Excel BI，Power BI，MS BI，Tableau，Alteryx等等。如果大家对BI有兴趣或者有问题，可以用onedrive 或者dropbox 或者我email （uniquebob0709@gmail.com) 发个sample data，我会尽量快解答。 本贴长期有效。也希望通过此帖结交志同道合的朋友。

注意，有童鞋发给我sample workbook， 建议如果有任何敏感信息请适当处理后再发给我，我会贴图方便大家一起学习。如果不慎偷漏了你的信息，本人概不负责，所以请务必伪装你的数据后再发给我

平平淡淡

http://www.oursteps.com.au/bbs/f ... read&tid=621229
以前发的一个帖子

slau1

我覺得自己在辦公室就是excel hotline
不過一般就是vlookup幫別人解決問題
期待牛人更多tips

平平淡淡

给MS打打广告

date

不错，以后有问题找你

小馋豆

正好公司更新电脑，新的excel 2013不适应，可以来请教

gzrain

感谢分享

想请教一下lz， BI日常工作具体是什么呢，分析数据然后整理给manager？

平平淡淡

BI 就是采集，加工，处理公司的数据，然后给其他business user（通常是business analyst) ，再经过他们的分析交给他们的经理。这是传统意义上的BI。随着BI技术的发展，特别是self-service BI的发展，business analyst已经可以基本不依靠BI自己建模了。我是介于两者之间吧。当然每个公司的情况都不一样，和公司规模大小有关。传统BI 前期投入资金大，建模时间长，小公司通常发现很难justify cost，反而self serviceBI 更适合，投入小，产出快。两者用的tool 也不一样。手机上打点字真不易，希望回答了楼上的问题

macro_micro_eco

哇 好人

晓暄

小白的excel问题可以问吗，比如怎么用这个功能如何设置那个....

uknowoppa82

thanks

Gladwin

flat26a

楼主好人！

正好有个问题，我们现在用的是Excel 2010，大部分机器只能支持32位的power pivot add-in（虽然我的可以支持64位的）。现在我做的Pivot table很简单，就是发票按供应商金额汇总排序 （因为发现32位power pivot add-in 居然不能做Distinct count ），在Slicer 里面，能不能做Dynamic 的Top X？比如Top 5， Top 10。。。我知道自己可以在Table里选，但是希望能做在Slicer里面这样其他不懂Excel的人只要在Slicer里面按一下就好了。不知道32位的power pivot add-in能不能做？还是只有64位或者Excel 2013才可以？

因为Copyright的问题，现在公司里只能用Excel 2010。

谢谢！

平平淡淡

晓暄 发表于 2014-9-19 09:52
小白的excel问题可以问吗，比如怎么用这个功能如何设置那个....

有时间我会尽量回复

平平淡淡

flat26a 发表于 2014-9-19 10:01
楼主好人！

正好有个问题，我们现在用的是Excel 2010，大部分机器只能支持32位的power pivot add-in（虽然 ...

可以做的，没问题。distinct count 和32 bit还是64bit 没关系，两者都有。建议发个sample workbook给我，toy data 就可以。联系方式看首帖

decei

flat26a

平平淡淡 发表于 2014-9-19 11:09
可以做的，没问题。distinct count 和32 bit还是64bit 没关系，两者都有。建议发个sample workbook给我， ...

谢谢楼主！我发给你Gmail邮箱了，请查收

平平淡淡

flat26a 发表于 2014-9-19 14:45
谢谢楼主！我发给你Gmail邮箱了，请查收

I have sent the completed file back to your email. You can  also download the sample workbook from below link.
https://onedrive.live.com/?cid=7 ... 5E31C189063EC%21124

I know you probably do not know Power Pivot much. The key to address your issue is having a disconnected table ( do not set up relationship with your main table), in your case it's the TOP X table with one column. You can google Disconnected Table and find lots of interesting post on this top for Power Pivot.

I start off by building the base measure Spend = SUM(DATA[AMOUNT])
                                                               Number of Supplier = DISTINCTCOUNT(DATA[VENDOR_NAME])
then I rank your category based on Spend  CategoryRank = RANKX(ALL(DATA[Category]),DATA[Spend])
Finally the driver for Dynamic TOP N count is hidden measure(hide it as your users don't need to see it)
IF(HASONEVALUE('TOP'[TOP X]),IF(DATA[CategoryRank]<=VALUES('TOP'[TOP X]),1,0))
This formula checks if only 1 value selected in the TOP N slicer, if yes, all category with rank <= selected slicer value will get 1, else will get 0. if your user selected more than 1 value in slicer, then nothing will be shown as it doesn't make any sense to see top X and Y at the same time.
The Last thing is turn on the value filter to show 1 for the hidden measure



All the formula are exclusive to Power Pivot which is DAX formula and they are not part of normal Excel functions.

There you have it. Dynamic TOP N (what ever you like to call it)

cocobai

请问你在学校是读什么专业的

rm.lh

这个不错，顶

william.wangzy

好帖子，顶！

我也发了个Pivot Table顺序排列的问题到你的邮箱，先谢谢了。

平平淡淡

cocobai 发表于 2014-9-20 06:45
请问你在学校是读什么专业的

化工。一定让你感觉意外吧。

平平淡淡

william.wangzy 发表于 2014-9-20 11:15
好帖子，顶！

我也发了个Pivot Table顺序排列的问题到你的邮箱，先谢谢了。 ...

method 1 custom sort

Create a Custom List in Excel >Excel options>Edit Custom Lists

Right clcik on field in PT>Sort>More Sort options>Manual>More options>untick Sort automatically>Fisrt Key Sort order>choose your Custom List

This should work. I have used this a lot. for some reason I can not get it work in your case. Have no idea why.

method 2 PowerPivot

You need to install PowerPivot if you do not have it. Add the sort order to your main table( you can use vlookup to grab the sort order from your Order sheets. then load the table into PowerPivot (Clicking Create Linked Table in Power Pivot Tab in Excel ribbon will link your table into Power Pivot)

1. Create Linked Table to bring your table into Power Pivot

2.Click Power Pivot icon to go to Power Pivot Window

2 Click sort by
then select the code column in PowerPivot window and click Sort by Column, select the order column.

. Within PowerPivot window click PivotTable to create a Pivot Table, when you drag code column to Row you will see it's automatically sorted by desired order.


4. Pivot table with Desired sort
Sample workbook can be found below
https://onedrive.live.com/?cid=7 ... 5E31C189063EC%21124

jingjingying

楼主太强大了，我manager也是excel很厉害，看的我一愣一愣的

maodoubao

分儿发完了才看到lz这么热心的帖子。分儿回来一定补上。

请教一个问题。如何合并一堆结构一样的excel文件？（excel2007）
例如：
每个文件workbook都有一个sheet纪录如下数据：
第一行是：房间名称，面积，周长，高度。
从第二行起每行就是按第一行指定的房间数据。

如何把几个上述的workbook合成一个文件？
第一行还一样，第二行起就把所有workbook里的记录合在一起。

糯米老虎

我提个问题 关于保护工作表的问题。比如，一个excel有10个sheet，每个都不同，每个sheet上都有公式，并且设置成locked，我能否同时保护10个sheet上的所有公式而不用一页一页去设置密码？ 试过保护整个文件，但是就算是locked的cell公式还是可以被删除

提前谢过

leyan

maodoubao 发表于 2014-9-21 02:38
分儿发完了才看到lz这么热心的帖子。分儿回来一定补上。

请教一个问题。如何合并一堆结构一样的excel文件 ...

这个问题我可以回答 LZ 可以指点一下如果有更好的方法

DATA- Consolidate-选择你需要合并的内容 （SHEET1, SHEET2 etc），第一行就不需要选了。

不知道我是否理解正确你的要求。

平平淡淡

maodoubao 发表于 2014-9-21 02:38
分儿发完了才看到lz这么热心的帖子。分儿回来一定补上。

请教一个问题。如何合并一堆结构一样的excel文件 ...

楼上的提到了一种方法，见下图，但是我从来没有用过它，参考以下链接获取更多信息。
http://office.microsoft.com/en-a ... et-HP010342300.aspx


碰到这样的问题，我会选择VBA。你可以参考以下连接。你的情况不一样的地方是，每个结构一样的sheet在不同的workbook里，只要多加一个循环就可以了。稍微修改一下下面的code，你的问题就很容易解决了。

http://www.vbaexpress.com/kb/getarticle.php?kb_id=151

平平淡淡

糯米老虎 发表于 2014-9-21 10:37
我提个问题 关于保护工作表的问题。比如，一个excel有10个sheet，每个都不同，每个sheet上都有公式，并且设 ...

This one is a bit tricky. By default, every cell in a worksheet is locked. If all of your 10 sheets have exactly the same structure, you  can select the first one and hold SHIFT to select last sheet to put 10 worksheets in group mode. then you select all cells to unlock them first and then select the cells with formula and lock them.Lastly protect your worksheet and uncheck "Select locked cells", add password, then you are good to go.

But if the 10 worksheets do not have the same structure and the cells with formula are not in the same range in each sheet, things will get complex here. You can use VBA to do this of course, but it will be cumbersome if you have multiple ranges in each sheet which you want to protect the formula.  I would prefer to have a look at your workbook before i can give you a solid solution.

william.wangzy

平平淡淡 发表于 2014-9-20 21:13
method 1 custom sort

Create a Custom List in Excel >Excel options>Edit Custom Lists

楼主太强了，在源数据里面加一栏看来也是不错的解决办法。还没有用过PowerPivot,我要安装下，再尝试下method 2，谢谢！