精湛的Excel & VBA技能让你告别职场“穷忙一族“,增效、加薪、提职、拿Offer样样称心如意


用Excel办公,你是不是常年陷入”穷忙,疲于奔命”的状态?工作低效使得职位升迁没有你份,增加薪水你不沾边,好挫败呀。如果你正是这个状态,拜托啦找找原因吧:你的Excel太差吧,你不懂excel VBA ,嘿果真如此,不低效才奇怪呀。如果你肯下功夫,学学Advance  Excel  & Excel VBA ,接下来会发生什么?精湛的Excel&VBA技能让你:在15 分钟内做出一个别人要用3个小时才能做出一个报表;
在两小时内分
析出一个别人要用一天时间才能找出问题根源;
在最短的时间做出一
个最优化的计划,而别人只能优化到 70%。强化你的Exce & VBA l技能吧,多好都不为之过,必须的!

这里的Case & Project 在网上学校里学不到
"Excel & VBA 项目培训"两个目标
目标一:让你在现有岗位工作效率提高70%!
用Excel办公你是不是常年陷入"穷忙,疲于奔命"的状态?工作低效使得职位升迁没有你份,增加薪水你不沾边,好挫败呀。如果你正是这个状态,只要你肯下功夫学精Advance Excel & Excel VBA ,你就可以做到:
1.15 分钟内做出一个别人用3个小时才能做出一个报表; 
2.两小时内分析出一个别人要用一天时间才能找出问题根源;
3.在最短的时间做出一个最优化的计划,而别人只能优化到 70%。
*我们的目标目标:在办公室里游刃有余;在老板面前举足轻重;加薪升职
目标二:帮你成功拿下FA、BA 等不同领域的 Analysis工作Offer
第一份FA/BA必须掌握硬技能通常是指:系统技能、分析技能和Excel&VBA技能
 * 很多人觉得找个网上的软件自学或者找一门网上的课程,就能学好Excel 和VBA,但是你学到的只是一些基础知识,不跳进水里,怎么能学会游泳呢?不亲临实战,会再多得Excel functions,又怎么能搞掂面试和找到工作呢?

 

* 我们的Excel 和VBA培训,在任何课本里也找不到,在任何网站里也学不到,我们教你的软件技能是在工作流程里的应用, 我们还教给你怎么用它写报告和上下级沟通,怎么用它交流,更重要的是交给你在工作中怎么把握你的角色,成功完成你的工作。
*我们的目标目标:
Financial Manager 亲自带你,70小时内,掌握别人5年都不能学会的技能,从零开始,一步一步,亲身体验一个Analyst从找工面试开始到实际工作的真实情形。 让您在面试时,镇定从容一举拿下Offer;工作职位包含FA、BA等各个领域的Analysis职位。

 

 

 

"Advance Excel for Business Professional"班让你成为办公室的精英一族



免费公开课:每周日:10:00 am

24小时精华训练,风暴般地提升你的 Excel技能,让您转眼成为办公室精英

 

 

"Excel VBA for Business Professional"班让你成为办公室的精英一族

VBA 秒杀Excel中的一切难题,瞬间变商务分析超人

免费公开课:每周日 :2:00 pm
24小时精华训练+20小时项目项目培训,绝对的头脑风暴,一个月成Senior Analyst中的佼佼者!!!

真实案例

真实案例,模拟工作环境,20小时突击训练,5场头脑风暴,让您在一个月成为Senior Analyst中的佼佼者!!!

案例一: (Financial Data Process & Reporting I)

You are a Co-op under the Finance team of Albertech Inc. Your colleague, Robert is a Senior Analyst who has been with the department for ten years. Robert is very knowledgeable about financial reporting. However, he is not willing to invest extra time in learning Excel coding to help improve productivity. Each month, Robert has to complete the Financial Statements report for Albertech’s three branches and send the report to the Senior Manager of the Finance team for further review. To complete the report, Robert has to collect three pieces of Excel files from the Operation Team and enter the data into another Excel template designed by Senior Manager. Robert does not know any Macros and he “complains” that the process is so annoying that each month, he has to spend four hours to manually enter the data and to double check the results. Your manager asks you to take a look at the process and to try to improve the process.

 

案例特色

 

 本案例让您运用VBA熟练处理多个Excel文档之间的数据流,并实现“一键”将原始数据变成高大上的金融报表。设计好的工具不但能自动的产生过去任何一个月的报表,还能够和定时器挂钩,于将来某一特定时间自动生成所需报表。

真实故事

01

David 凭借高超的Excel逆袭职场

好朋友David他的老板过去每半年才得到一张财务部门的公司市场营销盈亏报表,由于David超级精通Excel,使得现在每周可以得到一次报表,盈亏一目了然,于是让一个小公司生意做的风生水起。朋友自然大获老板赏识,在经济危机时这个朋友虽然在公司里最没资历非但没被裁员,反而高升

02

Cindy 凭借着高超的Excel VBA 技能加薪

Cindy毕业于多伦多大学金融专业,现就职于某大型保险公司,从事金融数据分析工作。刚开始,感觉自己EXCEL 好就够了。但是,很快地发现仅仅熟练掌握EXCEL FORMULA, 甚至是复杂的NESTED FORMULA也不够用了。尤其是同样面对大量的金融数据,其他TEAM 里有个别有计算机背景的同事,通过编写一些EXCEL VBA 程序,使得原来要花好几个小时做的分析或报告,只要轻轻一摁按钮,几秒钟就搞定了。于是,cindy 下决心要学EXCEL VBA。可是,自学了一段时间,又是买书,又是看YOUTUBE,收效甚微。究其原因,是因为她毕竟是学BUSINESS 的,没有一点编程背景,真是各行如隔山哪!

今年初,她选上了EXCEL VBA FOR BUSINESS PROFESSIONAL的课程,老师设计的课程是专门为我这样没有任何编程背景的BUSINESS PROFESSIONAL准备的。 

Cindy说要掌握好一门VBA,最好的办法就是在学完之后,立刻找机会在实际工作或学习中使用。因为本人从事数据分析工作,在平时的工作中要大量使 用EXCEL,通过观察发现自己TEAM里的同事,有很多时候时间花在了实际简单,但是要多次大量重复的工作上。于是自己就想,可以尝试利用老师在课堂上 教授的VBA编写程序,以达到工作自动化的目的。刚开始编写的时候也是遇到各种各样困难,但是因为有了课堂上打下的基础,再结合从网上看到的编程小技巧, 用了不多久,完全独立遍写的VBA程序就出炉了。从那以后,自己对编程也有了信心,平时也会花时间浏览VBA编程网站。过了一个多月,工作中又遇到了可以 用VBA实现的事情。因为这件事情太占用时间,所以在我们TEAM里,大家在几年的时间中一直想找到方法来自动化这个过程,无奈一直没有成功。这次同样还 是结合正在学的课程,我用了几周的时间写出几段程序,最终将要好几天完成的工作压缩到一分钟以内。 这样不仅节约了大量时间,提高了TEAM的工作效率, 本人还被评为季度优秀员工,年底老板为双倍加薪。我做到了,你也一定行的

Cindy的分享,让许多学员想清楚了,一味地自己死扣VBA的书本是走不通的,只有那些有着强大的工作经验背景的老师的指导可以起到”点石成金“作用,让你事半功倍地掌握ExcelVBA。

Advanced Excel for Business Professionals(course outline)

On Your Way to Becoming an Analyst

 Module One – Dealing with Data (6 hours)

·         Data Cleaning

o   Convert download data to flat data file

o   Keyboard shortcuts and tips

o   Relative / absolute / mixed references

o   Practical project – Getting started to analyse

·         Data Mining

o   Extract information from text strings

o   15 String function and formula

o   Practical project – Sales tax analysis 

·         Dada Mapping

o   Look up data with multiple criteria

o   Name ranges to make formula more readable

o   Highlighting analysis results with conditional format

o   Practical project – Vendor expense reconciliation 

·         Data Analysis

o   Analyze data with control logics

o   10 mostly used reporting functions and 8 database functions

o   Practical project – Financial statements preparation 

Module Two – Business Analysis and Reporting (6 hours)

·         Business Reporting

o   PivotTable / Calculated Fields / Data Table / Dynamic PivotTable

o   Extract data from PivotTable with GETPIVOTDATA function

o   Practical project – Sales report / top ten customers report / seasonality report 

·         Business Analysis

o   Data analysis working together with PivotTable

o   Practical project – Sales gross margin analysis / order lead-time report

o   Practical project – Excel test in job interviews 

·         Consolidation and Reporting

o   Consolidate report from multiple worksheets and/or workbooks

o   Edit Link function and INDIRECT formula

o   Array formula – Single cell array vs. multiple cell array formula

o   Practical project – Operating expenses Analysis 

Module Three – Visual Analytics and Dashboard (6 hours)

·         Analysis with Visual Aid

o   13 mostly used charts in reporting

o   Practical project – KPI dashboard

o   Practical project – Sales dashboard

o   Practical project – Marketing dashboard

o   Practical project – HR dashboard 

·         Powerful Decision Making Chart

o   Create dropdown button with data validation or combo box

o   Comparison of different lookup methods

o   MATCH / OFFSET / INDEX / working with data that changes size

o   Scroll bar and check box / 13 month sales trend chart / smoothing data and forecasting trend

o   Practical project – Create financial dashboard from profit and loss statements 

Module Four – Business Modelling (6 hours)

·         Business Modeling Essentials

o   Sensitivity Analysis and Scenario Analysis

o   Audit and security / Protect workbook and worksheet with password

o   Automate model navigation with Hyperlinks

o   Application of Macro / Macro recorder / VBA in business modeling

o   Business model layout, format, formula, chart and automation

o   Practical project – Net present value analysis model 

·         Modeling for Decision Making

o   Business model and decision making

o   Practical project – Breakeven analysis model

o   Practical project – Master budget / assumptions / sales & collections budget / COGS and payables budget / OPEX budget / headcount budget / CAPEX budget / cash budget / budget financial statements

 

Excel VBA for Business Professionals(course outline)

On Your Way to Becoming an Analyst 

1.      Excel VBA Essentials (3 hours)

·         Introduction to VBA

·         Visual Basic Editor

·         Getting Started

o Creating VBA Procedures

o Declaring Variables and Option Explicit

o Writing Comments

o Message Box and Input Box

o Writing Your First VBA Program

·         Recording Macros

o Using Macro Recorder

o Macro with Relative References

o Run Macro with a Button

·         Cleaning Codes Generated by Macro Recorder

o Reviewing Codes Created with Macro Recorder

o With Constructions

o Cleaning Codes in VBE 

2.      Working with Range Objects (3 hours)

·         Excel Object Model

o Excel Object Model Hierarchy

o Objects, Methods, Properties and Arguments

·         Referencing Ranges with VBA

o Referencing Single Cell Ranges

o Referencing Multiple Cell Ranges

o Current Region, Used Range and Union

o Using OFFSET to Reference Ranges

o Dealing with Ranges Changing Sizes 

3.      Range Properties and Methods (3 hours)

·         Range Properties

o Various Range Properties

o Range Properties Exercises

·         Range Methods

o Various Range Methods

o Range Methods Exercises

·         VBA Project – Sensitivity Analysis 

4.      Control Logic and For Each Loops (3 hours)

·         IF Constructions

o Introduction to IF Constructions

o Nested IF Constructions

o Compound IF Constructions

·         Case Constructions

·         For Each Loops

o Introduction to Loops

o For Each Loops

o For Each Loops with Control Logics

o Controlling PivotTables with For Each Loops 

5.      For Loops and Do Loops (3 hours)

·         For Loops

                                                  i.      Nested For Loops

                                                ii.      Using Counter in For Loops

                                              iii.      Counting Backward

                                              iv.      Exit For Loops Prematurely

                                                v.      Exercises of For Loops

·         Do Loops

                                                  i.      Do While Loop

                                                ii.      Do Until Loop

                                              iii.      Do Loop While

                                              iv.      Do Loop Until

                                                v.      Exercises of Do Loops

·         VBA Project – Loan Amortization Model

6.      Other Excel Objects (3 hours)

·         Workbooks

                                                  i.      Find Path of Workbook in Immediate Window

                                                ii.      Workbook Methods and Properties

·         Worksheets

                                                  i.      Worksheet Methods and Properties

·         Excel Charts

                                                  i.      Excel Chart Object Model

                                                ii.      Controlling Charts with VBA

·         VBA Arrays

·         VBA Project – Financial Reports Consolidation 

7.      User Forms (3 hours)

·         Creating User Form Templates

·         Working with Form Controls

                                                  i.      Types of Form Controls

                                                ii.      Creating Form Controls

                                              iii.      Changing Form Controls Properties

·         Event Handling Codes

·         VBA Project – Car Load Model 

8.      Other Topics (3 hours)

·         Scope of Variables and Passing Variables

·         Function Subs and Exercises

·         Using Excel Formula in VBA

·         Debugging

                                                  i.      Debug Syntax Errors

                                                ii.      Debug Run Time Errors

                                              iii.      Debug Logic Errors

·         Error Handling

·         Displaying Alerts and Screen Updating

·         Summary of Good Programming Practices

 

 

 


Excel VBA for Business Professionals,分析和报告图表


(更多资料和具体参加方法)


本页最后更新: | -- | 网站设计和虚拟主机服务 WECAN.ca CMS