Overview:
In this course, you will use Microsoft Office Excel 2010 enhance your spreadsheets with formulas, Charts, data analysis tools, templates.
Who Should Attend?
The target students for this course should be the users who need the skills to apply formulas, sort and filter data, datasheet security management, and import and export data, analyze data etc.
Agenda(Please bring your own computer to join 培训者请一定带电脑来参加)
8:45 - 9:00 Registration
9:00 - 12:00 Morning Session
12:00 - 13:00 Lunch
13:00 - 16:00 Afternoon Session
For bank transfers, please indicate use NJEVENT0424 as subject.
Part I: Explore the User Interface
1. Customize the Quick Access Toolbar
2. About Ribbon area
Part II: Time Saving Features
1. Tips for inputting data
How to input Massive Data by one keyboard stroke
How to Select the Data Range quickly
How to make a new line in a cell
How to input the current date and automatically update the date
How to copy the visible cells and ignore the hidden value
2. Fill command
a) Fill a number list
b) Fill a date list
c) Fill a text string list
d) Fill a Formula list
3. Use Paste Special Feature
a) How to keep the original column width when you make a copy
b) Use Transpose option:Switch rows with columns of your data table
c) Use Paste Link option
Part III: Formulas and functions
1. Cell reference: Absolute reference and relative reference
a) Use F4 Shortcuts key
2. Formula Auditing tools
a) Trace Precedents Cell
b) Trace Dependents Cell
c) Trace Error
d) Circle Invalid Data
3. Introduce Some Excel Basic Functions
e) General functions:SUM,MAX,MIN,AVERAGE,COUNT,COUNTA
f) Logic functions: IF,AND,OR,IFERROR
g) Statistical functions: COUNTIFS,SUMIFS,AVERAGEIFS ,SUBTOTAL
h) Date and time functions:YEAR,MONTH,DAY,DATE,DATEDIF
i) Text functions: MIDLEFT,RIGHT,LEN,VALUE
j) Lookup functions:VLOOKUP,HLOOKUP
4. Advanced functions:
k) VLOOKUP,IFERROR
Part IV: Manage workbook
1. Create professional report format
a) Use table feature: Set Worksheet header and background format
b) Print setting
c) Use page layout view
2. Use Validation rule to limit data inputting
d) Set input limits for numbers, text and dates
e) Create a dropdown list
f) Create a sub list
g) Set limit for imputing unique value in columns
h) Create a screen tips for Cells
i) Set a Warning message box for cells
3. Conditional formatting
j) Set conditional formatting formula
k) Highlight the top/bottom numbers
l) Highlight the duplicate value
m) Highlight the dates in current week/month/year
n) Use data bar and color Scales on you numbers
o) Show values with icons
p) Remove the format
4. Data security
q) Set editing restriction for a cell range
r) Hide formulas
s) Protect worksheet structure
t) Protect workbook files
Trainer
Miss. Liu
Eddic Office Gold Trainer
MCT (Microsoft Certificated Teacher)
Having more than 11years teaching experience with thousands of training hours for over 600 customers. Comfortable with working independently and with a high level of teamwork sensibility. Excellent communication skills with all trainees.
课程描述:
在 Microsoft Excel 2007/2010/2013 中,您可以使用更丰富和便捷的功能来分析数据。使用可视化方 式呈现以及共享数据,对您的数据进行智能分析。本课程主要内容包括:Excel 数据输入的 各种快捷方法,数据的特殊格式,使用公式与函数,使用条件格式功能进行数据智能分析。 数据的提取及使用汇总行等数据管理功能。使用强大灵活的数据透视表对数据进行多维度分 析,并且使用动态图表的方式将数据呈现出来等。初步了解宏功能的应用。
课程长度: 1天/7小时
培训对象:
行政、人事、财务、市场、管理、数据分析处理人员等
课程大纲:
Part 1: 新界面介绍(0.5 h )
n 快速访问工具栏
n 功能区
n 多种途径创建文档
Part 2:编修表格体验技巧、规范化的乐趣(1h)
n 在单元格内换行
n 快速输入今天日期
n 快速输入有规律的序列
n 选择性粘贴: 粘贴预览
n 复制为图片
n 快速选中数据表格
n 窗口冻结
n 全新的打印视图
Part 3: 公式及函数应用(2h)
n 公式应用
n 名称应用
n Excel基本函数
Part 4: 修饰表格(0.5h)
n 创建专业报表
u 增强的 Excel 表格格式
u “搜索”框搜索文本和数字
u 更卓越的表格命名选项
Part 5:体验数据有效预警管理技巧(1.5h)
u 数据有效性做下拉菜单
u 数据有效性限制输入范围
n 条件格式设置增强功能
u 定义条件格式
u 删除条件格式
Part 6:数据管理工具:(0.5h)
n 排序
n 筛选
n 分类汇总
n 数据保护
关于讲师
刘老师 易迪思Office金牌讲师 、微软MCT认证讲师
12年的授课经验,培训千余课时,已经服务600余公司客户,在专业知识和授课经验方面有着很强的结合,独立工作能力强同时又注重团队合作,授课效果受到学员和培训中心的高度评价.
服务客户
阿尔卡特Alcatel,飞利浦 PHILIPS,BP 石油,COMMINS康明斯发动机,上海大众,欧莱雅LOREAL, VOVOLE沃尔沃机械设备,UTSTARCOM,OITS西子奥迪斯,中石化,西门子 Siemens, 索尼 Sony, 富士施乐FUJI XERO,霍尼威尔Honeywell,BAYER拜耳制药,希尔顿Hilton Hotel,强生制药Johnson&Johnson,通用电器GE,开利空调Carrier,惠普HP,安万特 Aventis,朗讯Lucent,柯达Kodak,利乐包装Tetra Pak,阿斯利康AstraZeneca,Cocacola可口可乐,上海美国学校 Shanghai American School,和路雪等