看看爱网中 - Excel 高手有哪些

JoyceAccSG · 发表于 2012-3-19 08:03

本帖最后由 JoyceAccSG 于 2012-3-19 08:06 编辑

80%人只用了20%的Excel

请问有人知道Pivot table(report) 和 Macro 吗，你们在工作中是怎么运用的？

blueyb · 发表于 2012-3-19 08:50

本帖最后由 blueyb 于 2012-3-19 09:56 编辑

项目管理才用到的功能
而如果真是做项目管理的，又会有自己专门的软件，用excel做Pivot table(report) 和 Macro 应该不多
爱网做项目管理的人应该不多，实际应用中项目管理的团队里也不是人人都做这个表
如果是学生，学项目管理的话，ua 有专门的软件，是微软package里的，免费给学生用

浪里黑条 · 发表于 2012-3-19 09:05

顶尖高手：

如花。

JoyceAccSG · 发表于 2012-3-19 10:36

blueyb 发表于 2012-3-19 08:50
2 E: g; }6 W, C1 {" L; p% d2 n项目管理才用到的功能7 f+ s a# Z( m% c; t' i: |, p- f
而如果真是做项目管理的，又会有自己专门的软件，用excel做Pivot table(report) 和 ...

不光是项目管理里才用得到，做Accounting 的话会用得到，尤其是对在和想在大公司工作的Accountant来讲，Pivot table(repot) is a very useful tool.

JoyceAccSG · 发表于 2012-3-19 10:37

浪里黑条发表于 2012-3-19 09:05 ) h& w% N5 `% J6 l/ H
顶尖高手：
: m% _" B1 u& p p2 i) \) [5 s$ w
/ E8 _5 s2 u( d6 \如花。

那我们看看如花来不来跟帖，

跟帖和提供的信息。

如花 · 发表于 2012-3-19 11:26

我用公式比较多，其次是用pivot table. 尽量不用宏，用宏的话，如果出错只有写的人才能解决。通用性差。

如花 · 发表于 2012-3-19 11:29

浪里黑条发表于 2012-3-19 10:05 $ z+ G- @7 ]5 H, |: g
顶尖高手：
( U2 h& b! N- [
; x. F9 D& p3 K3 K) l3 k- m8 D如花。

躺着也中枪了。

小熊之家 · 发表于 2012-3-19 11:34

用宏的话最好要懂vba；
pivot table做报表不错。

JoyceAccSG · 发表于 2012-3-19 11:34

如花发表于 2012-3-19 11:26
& @, r$ m/ z! [我用公式比较多，其次是用pivot table. 尽量不用宏，用宏的话，如果出错只有写的人才能解决。通用性差。

太好了，高人找到一个，有问题请教，等我回家以后。先谢谢了。

natson · 发表于 2012-3-19 11:39

本帖最后由 natson 于 2012-3-19 12:40 编辑

如花发表于 2012-3-19 12:26
/ y0 B4 b! N( q2 N, h我用公式比较多，其次是用pivot table. 尽量不用宏，用宏的话，如果出错只有写的人才能解决。通用性差。

I don't think so. All the software you are using is developed by programmer. You can't fix anything but you are still using it. If a software developed with C# has some problems, you will blame it to C#, won't you?通用性差 is just because programmer sucks, which has nothing to do with VBA

You can do almost anything with VBA. You can also develop software with it and sell it. That is why office is more expensive than other similar softwares.

如花 · 发表于 2012-3-19 11:46

natson 发表于 2012-3-19 12:39
$ M5 l7 r! h2 P4 a+ O1 gI don't think so. All the software you are using is developed by programmer. You can't fix anyth ...

wannahome · 发表于 2012-3-19 11:46

I do use pivot table. It is very useful.

JoyceAccSG · 发表于 2012-3-19 12:50

wannahome 发表于 2012-3-19 11:46 * o% m+ L0 @( O3 P/ q/ x
I do use pivot table. It is very useful.

Tell me a little bit more please, like when, how do you use it?

JoyceAccSG · 发表于 2012-3-19 12:51

natson 发表于 2012-3-19 11:39 - w4 Q: }+ A E7 H" h+ X. K5 R* n) u
I don't think so. All the software you are using is developed by programmer. You can't fix anyth ...

Please tell a little bit more about VBA, thanks!

浪里黑条 · 发表于 2012-3-19 12:52

小熊之家发表于 2012-3-19 12:34
0 ]- F3 m8 u3 z用宏的话最好要懂vba；
. `6 p; C" s4 C# R4 Fpivot table做报表不错。

浪里黑条 · 发表于 2012-3-19 12:56

JoyceAccSG 发表于 2012-3-19 13:50
) w/ C0 s0 w( c+ m8 vTell me a little bit more please, like when, how do you use it?

当你有很多变量，需要在特定时间内，考察这些变量的关系时候，就能用上。

例如，架鸡棚有十位女友，依据每位女友的容貌美丽程度，他每月总计花费大概是一万五千元。

架鸡棚有个Excel，详细记录了每位女友的每月花销情况。

当你看看谁最有可能变成正房的时候，就可以用这个功能了。

natson · 发表于 2012-3-19 13:40

http://en.wikipedia.org/wiki/Visual_Basic_for_Applications

Visual Basic for Applications (VBA) is an implementation of Microsoft's event-driven programming language Visual Basic 6 and its associated integrated development environment (IDE), which are built into most Microsoft Office applications. VBA enables building user defined functions, automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). It is also built into Office for Mac applications (apart from version 2008), other Microsoft applications such as Microsoft MapPoint and Microsoft Visio; as well as being at least partially implemented in some other applications such as AutoCAD, WordPerfect and ArcGIS. It supersedes and expands on the abilities of earlier application-specific macro programming languages such as Word's WordBasic. It can be used to control many aspects of the host application, including manipulating user interface features, such as menus and toolbars, and working with custom user forms or dialog boxes. VBA can also be used to create import and export filters for various file formats, such as OpenDocument (ODF).

As its name suggests, VBA is closely related to Visual Basic and uses the Visual Basic Runtime, but can normally only run code within a host application rather than as a standalone application. It can, however, be used to control one application from another via OLE Automation. For example, it is used automatically to create a Word report from Excel data, in turn automatically collected by Excel from polled observation sensors. The VBA IDE is reached from within an Office document by pressing the key sequence Alt+F11.

VBA is functionally rich and flexible but it does have some important limitations, such as restricted support for function pointers which are used as callback functions in the Windows API. It has the ability to use (but not create) (ActiveX/COM) DLLs, and later versions add support for class modules.

架鸡棚 · 发表于 2012-3-19 13:42

浪里黑条发表于 2012-3-19 10:05 - K8 g8 v6 m3 l& l7 C) U4 r q
顶尖高手：
; b9 k( x3 q, Z9 ^" d3 R: D
/ J$ o) m0 c N$ p6 u( p; q9 v: x如花。

顶尖副高手：

紫光。

架鸡棚 · 发表于 2012-3-19 13:46

本帖最后由架鸡棚于 2012-3-19 14:54 编辑

浪里黑条发表于 2012-3-19 13:56
5 M; t8 l: s" a2 K' B$ }当你有很多变量，需要在特定时间内，考察这些变量的关系时候，就能用上。
; O: z1 D1 u2 g& z# n( s0 G+ j) }3 L
例如，架鸡棚有十位女友，依 ...

如果偶有十位女友，白天早就累得站不起来了。每天只能坐轮椅上班，哪还有精力摆弄Excel?

如果偶真有十位女友，就雇你帮偶挖个地窖，建个培训基地。

natson · 发表于 2012-3-19 13:46

Simple example, I got a excel sheet with 20K rows of data from an equipment. I had to find out some data rows with different criteria. I can't pick out by hand one by one because it will take too much time. Writing a small macro with <100 rows of codes can do the job in seconds.

浪里黑条 · 发表于 2012-3-19 14:14

natson 发表于 2012-3-19 14:46 7 Y3 L: R3 |, b. I, I6 a
Simple example, I got a excel sheet with 20K rows of data from an equipment. I had to find out some ...

用Filter就可以啦。

natson · 发表于 2012-3-19 14:19

本帖最后由 natson 于 2012-3-19 15:43 编辑

浪里黑条发表于 2012-3-19 15:14 % [% r& A7 x' q4 r7 r0 N
用Filter就可以啦。

criteria are not simple number, data are not simple number too and criteria are multiple. It is a filter, with some added functions like copy to specific location in another excel file.

精打细算 · 发表于 2012-3-19 16:50

1991 年时用 quattro pro 的Macro 写了整套会计系统，那个累啊~~

现在常用Pivot table, 比较懒时直接用 lookup...if 写些简单的。

teng.ernest · 发表于 2012-3-19 16:55

vlookup is very useful formular for searching something you want to compare absolutely equal data.

JoyceAccSG · 发表于 2012-3-19 17:21

小熊之家发表于 2012-3-19 11:34 # o: f5 t6 i+ A: ~
用宏的话最好要懂vba；, m* g/ m/ q2 O7 F8 q1 e, C7 n) u
pivot table做报表不错。

现在的会计软件非常好用，报表应该不是Pivot table 的主要用途。

RobertKing · 发表于 2012-3-19 17:29

本帖最后由 RobertKing 于 2012-3-19 18:30 编辑

以前在IBM实习的时候见过大陆前辈牛人用VBA写的Application..真正的Application 前台后台都很强大。。。。。所以99% 的用户只用到了Excel 的功能的，不到10%。。。而且用excel 做data mapping 和数据分析。。。也很给力。。。。

teng.ernest · 发表于 2012-3-19 17:32

JoyceAccSG 发表于 2012-3-19 18:21 4 D* K4 k6 j# v8 Q j6 \' q
现在的会计软件非常好用，报表应该不是Pivot table 的主要用途。

The major function of Pivot Table is for data analysis.

JoyceAccSG · 发表于 2012-3-19 17:33

要带孩子去游泳了，晚上再聊，别不理我哟

teng.ernest · 发表于 2012-3-19 17:34

VBA is too complex for common people, it is develpment tool for someone has further purpose. 1% Excel user can use VBA to improve their job. It required a programmer.

watercress · 发表于 2012-3-19 19:02

vba我几乎天天在用。如果lz想学vba/excel macro可以考虑参加U of A的excel vba workshop，当然，如果你老板能给你报销的话

账号		自动登录	找回密码
密码			注册

看看爱网中 - Excel 高手有哪些

鲜花鸡蛋

鲜花鸡蛋

鲜花鸡蛋

鲜花鸡蛋

鲜花鸡蛋

鲜花鸡蛋

鲜花鸡蛋

鲜花鸡蛋

鲜花鸡蛋

鲜花鸡蛋

鲜花鸡蛋

鲜花鸡蛋

鲜花鸡蛋