培训目标 Training Objective
许多人都在为了处理工作数据而在万家灯火的时候加班。面对海量的工作数据和繁复的重复工作,我们需要掌握什么样的方法和技巧才能轻松驾驭Excel制作报表?答案将在本次课程中得到揭晓,从数据的查找,整理到商务报表的快速制作,其中有很多很多的秘密和技巧可以让我们的工作更轻松,更智能。
If you’re often faced with the task of processing data and performing complicated, repetitive tasks in Excel you need to learn some skills that will help to lighten the load. In this course you will learn how to perform data searches, efficiently sort results and produce reports along with other skills that will help to make your work easier and smarter.
受众学员 Who Should Attend?
具备 Excel 先前使用经验,期望进一步优化汇总分析技能,快速提升制作报告水平的用户。
For experienced Excel users who need to get their Excel skills up to speed for reporting and analysis.
课程大纲 Advanced Topics Covered
本课程面向商业数据分析人士,旨在帮助用户为实现专业分析做好技术准备。
This programme will prepare you to take up a business analyst role.
单元格格式高级设置
· 条件格式中常用的函数
· COUNTIF, AND, OR
· 高亮显示重复数据
必备常用函数
· 时间、日期类函数
· 逻辑函数
· 查找函数
· 统计函数
· 其他常用函数
构建数字仪表盘报表
· 报表设计思路与理念
· 向仪表盘添加微型图表
· 向仪表盘添加数据有效性
· 添加常用函数(VLOOKUP,INDEX)
· 高级函数、公式(数组公式)
排序筛选
· 含计算公式的条件区域
· 查找不重复的数值记录
数据透视
· 添加计算字段、计算项
· 对数字、日期进行分组
· 多重数据区域汇总合并
EXCEL 高级图表
· 创建多类型组合图
· 创建叙述型瀑布图
· 创建双坐标轴图表
目标求解、方案分析及规划求解
EXCEL 宏
· 什么是宏,它能实现什么?
· 录制宏,实现按照条件查询
· 录制宏,实现同步多透视表
· 常用窗体控件(按钮、组合框等)
Advanced Cell Formatting
· Applying functions (COUNTIF, AND, OR) to conditional format
· Highlighting duplicates in columns
Must Know Functions
· Date and time functions, logical functions, lookup functions, statistical functions and more.
Digital Dashboard
· Knowing digital dashboard report design concept
· Advanced formulas and techniques
Sorting & Filtering
· Computed criteria examples, finding unique values
Pivot Table
· Calculated fields and items
· Grouping dates/numeric items
· Multiple consolidation ranges
More on Excel Charts
· Combination charts
· Waterfall charts explained
Go-seek, Scenario & Solver
Introduction to Excel Macros
· What is a macro?
· Recording a macro for querying data, synchronising pivot tables
· Forms (button/combo-box)
课程安排Agenda (培训者请一定带电脑来参加)
8:45 - 9:00 Registration
9:00 - 12:00 Morning session
12:00 - 13:00 Lunch
13:00 - 16:00 Afternoon session
About the Trainer
Xue Zheng,Eddic Training Centre
- QING HUA University special trainer for MBA
- 4 years Microsoft Most Valuable Professional (MVP)
- Microsoft Certified Professional (MCP)
As a long-term Microsoft corporate trainer, Mr Xue provides professional MS Office training to large state-owned businesses and foreign-invested enterprises.
He has earned the Microsoft Certified Trainer certification: MCSE, MCDBA, MCP and MCT.
For online payments please indicate BJ2015-01-14
请在银行转账单上附注 BJ2015-01-14