EXCEL员工培训
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
空白行和空白列围起的数据区域） Ctrl+C:，右键＋T复制， Ctrl+Ｖ,右键＋P粘
贴 Ctrl+鼠标滚轮，屏幕放大放小 Ctrl+Shift+→ Ctrl+Shift+ ↑
二、高效录入数据
Ctrl+Shift+ ↓ Ctrl+Shift+ → Ctrl+Shift+End Ctrl+Shift+Home Ctrl+Shift+PgUp Ctrl+; 系统日期 Ctrl+Shift+: 系统时间（只能精确到分,如果精确秒
2、数据有效性
⑴下拉框的制作 ⑵文本长度高置（身份证件、手机号录入） ⑶不得重复录入相同数据（抽奖）
=countif(A:A,A1)=1
二、高效录入数据
⑷最多输入2位小数的单价
=B2=FLOOR(B2,0.01)
３、选择性粘贴。（应熟练使用“选择性粘贴”
中的数值、公式、格式、计算、转置、粘贴链接等所有 功能 ） 4、设置我的工具栏：将自己常用的工具放置在我的工 具栏中。工具→自定义→工具栏→新建→我的工具栏； 工具→自定义→命令→将常用字的如自动筛选、全部显 示、高级筛选、数值粘贴、数据透视表、有效性、条件 格式、合并单元格、撤消单位。
分列：数据→分列→分隔符号（固定）→分号、逗号、空格、其它） 将整列数字格式变为文本可运用分列,选中点文本. LEFT、RIGHT、MID函数 分别从左边、右边和中间取一个数值。
三、数据整理
2、选择性粘贴
（应熟练使用“选择性粘贴”中的数值、公式、格 式、计算、转置、粘贴链接等所有功能 ）
3、定位（F5）
用列表实现动态累计
二、高效录入数据
用好F4，可以省下许多时间。重复的就让F4替你做吧， 爽多多！
F5：显示“定位”对话框。其中“可见单元格”，“空 值”、“常量”、“当前区域”在做大量复制、粘贴过程 中很派用场。
工资表表头: 取消合并单元格后的快速填充： F5→空值→=A1→Ctr+Enter; 保护工作表: F5 →空白值→单元格→保护→去除保护→工具 →工作表保
四、统计、分析
㈡数据透视表 数据透视表列不得为空
⑵条件求和函数SUMIF、条件求和函数 COUNTIF
D2=SUMIF($A$2:$A$5,">160000",$B$2: $B$5) D2=SUMIF($A$2:$A$5，C2,$B$2:$B$5) C2=COUNTIF(A2:A5,“apples”)计算第一列 中苹果所在单元格的个数 C2=COUNTIF(B2:B5,">55")计算第二列中值 大于 55 的单元格个数
四、Байду номын сангаас计、分析
㈠常用函数
⑴求和函数
①SUM
=sum(A1:A6) =SUM('sheet1:sheet20'！B5) =SUM('*'！B5) =sum(A1，A2，A6)
四、统计、分析
②SUMPRODUCT
两行相乘(两列相乘)再累加和公式（计件工资时用） 将AB两列的数值对应相乘，再将相乘的数累加： A1*BA+A2*B2+A3*B3类推。 =SUM(A1:A10*B1:B10) 同时按“Ctrl+Shift+Enter” 或者=SUMPRODUCT(A1:A10,B1:B10) Enter
员工EXCEL培训
------准确、高效、美观、快乐
张伟珍 二○一○年二月
内容
一、表格设计 二、高效录入数据 三、数据整理 四、统计、分析 五、报表打印 六、邮件合并运用
一、表格设计
需要超过一个工作表吗?（内容相
似，归类）
哪些数据应该在行中，哪些应 该在列中?（长和宽）屏幕上查看、与打印
纸匹配。通常工作表应深而窄
MID(B2,7,4),YEAR(NOW())+1MID(B2,7,2)-1900) =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男 ","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))
②境外个人姓名合并
=IF(F2=0,B2,IF(G2=0,B2&C2,B2&C2&D2))
充分利用定位中的“可见单元格”，“当前区域”、 “空值”、“常量”在做大量复制、粘贴、删除过程 中很派用场。
4、查找、替换功能（Ctrl+H，包括整个工
作簿的查找、单元格格式中的字体大小、颜色等查找 替换）
三、数据整理
5、排序、筛选、高级筛选
⑴排序： ①升序、降序 快速插入行、快速删除行、工资表表头 ②按笔划、拼音字母 ③自定义序列排序（自定义序列在工具→选项→自定 义序列） ⑵筛选：学会使用“自动筛选”和“全部显示”按钮， 筛选中“自定义”功能，包括不等于、包含、不包含、 起于、止于等功能。
四、统计、分析
⑸取相关数据中某个字段的LEFT、 RIGHT、MID函数
分别取地址邮编 =RIGHT(A2，6)； =LEFT(A2，LEN(A2)－6)
⑹文本函数LEN、TEXT，TYPE，文本转数字的 VALUE函数
空单元格复制→文本区域→选择性粘贴→加
四、统计、分析
⑺日期函数DATE、YEAR、NOW等等。 =YEAR(NOW())
四、统计、分析
=IF(F2=0,B2,IF(G2=0,PHONETIC(B2:C2),PHONETI C(B2:D2)))
③查找数据并取万以下2位小数点
=round(vlookup$A1,$E$1:$G$100,2,false)/100 00,2) ④生日提醒 =IF(DATEDIF($A2-7,TODAY(),"YD")<=7,"提醒 ","") 企业多头开户，但只找其中一家开户银行 =if(exact(A1,A2), "",A2) =if(A1=A2, "",A2)
三、数据整理
⑶高级筛选： ①“选择不重复的记录”（删除多余记录最好使） ②多条件（包括三种以上的并列条件、或者条件、既 并又或的条件）筛选使用高级筛选再省力不过。
6、条件格式
给符合特定条件下的单元格加着色。 如日期中双休日加红色。公式 =weekday(A1,2)>5，格式根据自己意愿加粗、 加色都可以。
四、统计、分析
分级显示：时间段 =LOOKUP(A1,{1,2,8,15,32,62,92,123},{"1天","7天 ","14天","1月","2月","3月","4月","4月以上"}) 上述表达意思为： 1：1天 2-7：7天 8-14：14天 15-31：1月 32-61：2月 62-91：3月 92-122：4月 123以上：4月以上
四、统计、分析
分级显示：成绩段
=LOOKUP(D1,{0,60,70,85},{"差","中","良"," 优"})
上述表达意思为： 0-59.99分：差 60-69.99分：中 70-84.99分：良 85以上：优
四、统计、分析
=LOOKUP(LEFT(B2,6),{"320500","320581", "320582","320583","320584","320585"},{" 苏州","常熟","张家港","昆山","吴江","太仓"})
需要打印工作表吗? （长和宽）
一、表格设计
数据如何被使用?（录入规范性） 受众对象是谁?（汇总还是明细） 您不在时工作表还可以工作吗?
（批注）
工作表依赖导入的数据吗?（外
部数据源）
二、高效录入数据
1、常用快捷键
Shift:选中连片数据，相当于“：” Ctrl：选中间隔数据，相当于“，” Ctrl+*：选择环绕活动单元格的当前区域（由
⑸快速重命名。用鼠标双击工作表标签，可进行 重命名操作。
二、高效录入数据
Ctrl+Shift+PgDn 按Alt+Enter可在同一单元格中另起一个
新行。 按Ctrl+Enter可使用当前条目填充选定的
单元格区域。 按Shift+Enter可完成单元格输入并选择
上面的单元格。
二、高效录入数据
比较两个单元内容的内容是否相一致 C1=excat(A1,B1) C1=A1=B1
四、统计、分析
C1=if(A1=B1,1,0) 生日提醒 B2=IF(DATEDIF($A27,TODAY(),"YD")<=7,"提醒","")
四、统计、分析
⑽函数综合利用案例
①从身份证中算年纪 =IF(LEN(B2)=18,YEAR(NOW())+1-
护→输入密码。 全选→单元格→保护→去除保护→ F5→常量(公式)→单元格
→保护→点保护→工具 →工作表保护→输入密码。
二、高效录入数据
Excel中妙用双击功能 ⑴巧分窗口。 ⑵调整列宽 。 ⑶快速移动。 ⑷填充有规律数据和 快速复制公式内容。
在单元格右下角的小黑十字上用鼠标快速点击两下，同列单 元格中的所有的公式都复制好了，但有一个前提条件，其左 列不能有空格。
四、统计、分析
{“SUMPRODUCT”在插入，函数，全部里寻 找。} 固定行乘相对行(固定列乘相对列)再累加和 C1=SUM($A1:$A10*B1:B10) 同时按“Ctrl+Shift+Enter” 或者=SUMPRODUCT($A1:$A10,B1:B10) Enter
四、统计、分析
③最大值MAX、最小值MIN、平均值AVERAGE、 计数COUNT等
直接以”万”为单位显示数值:格式代码:0!.0, ( 0!.0,”万”) 直接以”亿”为单位显示数值:格式代码:0!.00,, ( 0!.00,,”
亿”)
二、高效录入数据
F2:编辑活动单元格并将插入点放置到线 条末尾。
F4：重复上一个命令或操作（如有插入、 删除、粘贴、编辑等），相对引用和绝对 引用键。按一次一个变化，可以按四次， 四种变化。例如选中“A1”引用，反复按 F4键时，就会在$A$1、A$1、$A1、A1 之间切换。日累计：C1=$B$1:$B1，利
三、数据整理
动态显示最大 、最小值 =A1=MAX(A$1:A$1000) =A1=MIN(A$1:A$1000)
四、统计、分析
比较熟练地运用常用函数、合 并计算和数据透视表。常用函数主 要有统计函数、文本函数、日期函 数、逻辑函数、查找与引用函数， 财务人员还需要充分运用财务函数。 能熟练、灵活运用20个以上的函数
找简称或地址
B2= LOOKUP (1,0/FIND($H$2:$H$18,A2), H$2:$H$18)
[$H$2:$H$18] 是地址简称
四、统计、分析
⑷取小数点位的
四舍五入ROUND，向上取整数ROUNDUP、往下 取整数ROUNDOWN 、INT B1=round(A1,0) B1=round(A1,2)
四、统计、分析
⑶查找函数VLOOKUP, INDEX、MATCH、
lookup 将所有的查找内容全部到另一个报表中
=VLOOKUP($A2,'2008'!$A$1:$D$18,COL UMN(B1),FALSE) =INDEX($A:$E,MATCH($O2,$A:$A,0),C OLUMN(B1)) 其中的COLUMN(B1)代表2，然后可以自由拖动， 若是第一个要查找的是3，则是COLUMN(C1),c 以此类推。
可以在根据自己爱好在单元格的日期中 选择自己喜欢的日期表达方式 从业务参号中提取日期
B2=--TEXT(MID(A2,13,8),"#-0000")
四、统计、分析
⑻排名函数RANK
B1=RANK($A1,$A$1:$A$15) 斩头去尾得分
=TRIMMEAN(A1:A10,2/10)
⑼逻辑函数IF
三、数据整理
1、合并、分列
合并符&（=A1&B1，=A1&“-”&B1） 合并函数： =CONCATENATE(A1,B1) =CONCATENATE(A1,"市",B1) =PHONETIC（A1：F1） =PHONETIC（A1：F20）
=PHONETIC(offset($A$1,match(E2,$C2$:$c$500,),,cou ntif($C2$:$c$500,E2),2))
需用VBA语言）
Ａlt+=键快速操作，对区、片值的加总
二、高效录入数据
Shift+F11快速插入工作表 按Alt+Enter可在同一单元格中另起一个新行。 按Ctrl+Enter可使用当前条目填充选定的单元格
区域。 按Shift+Enter可完成单元格输入并选择上面的
单元格。 Ctrl+1 单元格格式设置(自定义)
贴 Ctrl+鼠标滚轮，屏幕放大放小 Ctrl+Shift+→ Ctrl+Shift+ ↑
二、高效录入数据
Ctrl+Shift+ ↓ Ctrl+Shift+ → Ctrl+Shift+End Ctrl+Shift+Home Ctrl+Shift+PgUp Ctrl+; 系统日期 Ctrl+Shift+: 系统时间（只能精确到分,如果精确秒
2、数据有效性
⑴下拉框的制作 ⑵文本长度高置（身份证件、手机号录入） ⑶不得重复录入相同数据（抽奖）
=countif(A:A,A1)=1
二、高效录入数据
⑷最多输入2位小数的单价
=B2=FLOOR(B2,0.01)
３、选择性粘贴。（应熟练使用“选择性粘贴”
中的数值、公式、格式、计算、转置、粘贴链接等所有 功能 ） 4、设置我的工具栏：将自己常用的工具放置在我的工 具栏中。工具→自定义→工具栏→新建→我的工具栏； 工具→自定义→命令→将常用字的如自动筛选、全部显 示、高级筛选、数值粘贴、数据透视表、有效性、条件 格式、合并单元格、撤消单位。
分列：数据→分列→分隔符号（固定）→分号、逗号、空格、其它） 将整列数字格式变为文本可运用分列,选中点文本. LEFT、RIGHT、MID函数 分别从左边、右边和中间取一个数值。
三、数据整理
2、选择性粘贴
（应熟练使用“选择性粘贴”中的数值、公式、格 式、计算、转置、粘贴链接等所有功能 ）
3、定位（F5）
用列表实现动态累计
二、高效录入数据
用好F4，可以省下许多时间。重复的就让F4替你做吧， 爽多多！
F5：显示“定位”对话框。其中“可见单元格”，“空 值”、“常量”、“当前区域”在做大量复制、粘贴过程 中很派用场。
工资表表头: 取消合并单元格后的快速填充： F5→空值→=A1→Ctr+Enter; 保护工作表: F5 →空白值→单元格→保护→去除保护→工具 →工作表保
四、统计、分析
㈡数据透视表 数据透视表列不得为空
⑵条件求和函数SUMIF、条件求和函数 COUNTIF
D2=SUMIF($A$2:$A$5,">160000",$B$2: $B$5) D2=SUMIF($A$2:$A$5，C2,$B$2:$B$5) C2=COUNTIF(A2:A5,“apples”)计算第一列 中苹果所在单元格的个数 C2=COUNTIF(B2:B5,">55")计算第二列中值 大于 55 的单元格个数
四、Байду номын сангаас计、分析
㈠常用函数
⑴求和函数
①SUM
=sum(A1:A6) =SUM('sheet1:sheet20'！B5) =SUM('*'！B5) =sum(A1，A2，A6)
四、统计、分析
②SUMPRODUCT
两行相乘(两列相乘)再累加和公式（计件工资时用） 将AB两列的数值对应相乘，再将相乘的数累加： A1*BA+A2*B2+A3*B3类推。 =SUM(A1:A10*B1:B10) 同时按“Ctrl+Shift+Enter” 或者=SUMPRODUCT(A1:A10,B1:B10) Enter
员工EXCEL培训
------准确、高效、美观、快乐
张伟珍 二○一○年二月
内容
一、表格设计 二、高效录入数据 三、数据整理 四、统计、分析 五、报表打印 六、邮件合并运用
一、表格设计
需要超过一个工作表吗?（内容相
似，归类）
哪些数据应该在行中，哪些应 该在列中?（长和宽）屏幕上查看、与打印
纸匹配。通常工作表应深而窄
MID(B2,7,4),YEAR(NOW())+1MID(B2,7,2)-1900) =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男 ","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))
②境外个人姓名合并
=IF(F2=0,B2,IF(G2=0,B2&C2,B2&C2&D2))
充分利用定位中的“可见单元格”，“当前区域”、 “空值”、“常量”在做大量复制、粘贴、删除过程 中很派用场。
4、查找、替换功能（Ctrl+H，包括整个工
作簿的查找、单元格格式中的字体大小、颜色等查找 替换）
三、数据整理
5、排序、筛选、高级筛选
⑴排序： ①升序、降序 快速插入行、快速删除行、工资表表头 ②按笔划、拼音字母 ③自定义序列排序（自定义序列在工具→选项→自定 义序列） ⑵筛选：学会使用“自动筛选”和“全部显示”按钮， 筛选中“自定义”功能，包括不等于、包含、不包含、 起于、止于等功能。
四、统计、分析
⑸取相关数据中某个字段的LEFT、 RIGHT、MID函数
分别取地址邮编 =RIGHT(A2，6)； =LEFT(A2，LEN(A2)－6)
⑹文本函数LEN、TEXT，TYPE，文本转数字的 VALUE函数
空单元格复制→文本区域→选择性粘贴→加
四、统计、分析
⑺日期函数DATE、YEAR、NOW等等。 =YEAR(NOW())
四、统计、分析
=IF(F2=0,B2,IF(G2=0,PHONETIC(B2:C2),PHONETI C(B2:D2)))
③查找数据并取万以下2位小数点
=round(vlookup$A1,$E$1:$G$100,2,false)/100 00,2) ④生日提醒 =IF(DATEDIF($A2-7,TODAY(),"YD")<=7,"提醒 ","") 企业多头开户，但只找其中一家开户银行 =if(exact(A1,A2), "",A2) =if(A1=A2, "",A2)
三、数据整理
⑶高级筛选： ①“选择不重复的记录”（删除多余记录最好使） ②多条件（包括三种以上的并列条件、或者条件、既 并又或的条件）筛选使用高级筛选再省力不过。
6、条件格式
给符合特定条件下的单元格加着色。 如日期中双休日加红色。公式 =weekday(A1,2)>5，格式根据自己意愿加粗、 加色都可以。
四、统计、分析
分级显示：时间段 =LOOKUP(A1,{1,2,8,15,32,62,92,123},{"1天","7天 ","14天","1月","2月","3月","4月","4月以上"}) 上述表达意思为： 1：1天 2-7：7天 8-14：14天 15-31：1月 32-61：2月 62-91：3月 92-122：4月 123以上：4月以上
四、统计、分析
分级显示：成绩段
=LOOKUP(D1,{0,60,70,85},{"差","中","良"," 优"})
上述表达意思为： 0-59.99分：差 60-69.99分：中 70-84.99分：良 85以上：优
四、统计、分析
=LOOKUP(LEFT(B2,6),{"320500","320581", "320582","320583","320584","320585"},{" 苏州","常熟","张家港","昆山","吴江","太仓"})
需要打印工作表吗? （长和宽）
一、表格设计
数据如何被使用?（录入规范性） 受众对象是谁?（汇总还是明细） 您不在时工作表还可以工作吗?
（批注）
工作表依赖导入的数据吗?（外
部数据源）
二、高效录入数据
1、常用快捷键
Shift:选中连片数据，相当于“：” Ctrl：选中间隔数据，相当于“，” Ctrl+*：选择环绕活动单元格的当前区域（由
⑸快速重命名。用鼠标双击工作表标签，可进行 重命名操作。
二、高效录入数据
Ctrl+Shift+PgDn 按Alt+Enter可在同一单元格中另起一个
新行。 按Ctrl+Enter可使用当前条目填充选定的
单元格区域。 按Shift+Enter可完成单元格输入并选择
上面的单元格。
二、高效录入数据
比较两个单元内容的内容是否相一致 C1=excat(A1,B1) C1=A1=B1
四、统计、分析
C1=if(A1=B1,1,0) 生日提醒 B2=IF(DATEDIF($A27,TODAY(),"YD")<=7,"提醒","")
四、统计、分析
⑽函数综合利用案例
①从身份证中算年纪 =IF(LEN(B2)=18,YEAR(NOW())+1-
护→输入密码。 全选→单元格→保护→去除保护→ F5→常量(公式)→单元格
→保护→点保护→工具 →工作表保护→输入密码。
二、高效录入数据
Excel中妙用双击功能 ⑴巧分窗口。 ⑵调整列宽 。 ⑶快速移动。 ⑷填充有规律数据和 快速复制公式内容。
在单元格右下角的小黑十字上用鼠标快速点击两下，同列单 元格中的所有的公式都复制好了，但有一个前提条件，其左 列不能有空格。
四、统计、分析
{“SUMPRODUCT”在插入，函数，全部里寻 找。} 固定行乘相对行(固定列乘相对列)再累加和 C1=SUM($A1:$A10*B1:B10) 同时按“Ctrl+Shift+Enter” 或者=SUMPRODUCT($A1:$A10,B1:B10) Enter
四、统计、分析
③最大值MAX、最小值MIN、平均值AVERAGE、 计数COUNT等
直接以”万”为单位显示数值:格式代码:0!.0, ( 0!.0,”万”) 直接以”亿”为单位显示数值:格式代码:0!.00,, ( 0!.00,,”
亿”)
二、高效录入数据
F2:编辑活动单元格并将插入点放置到线 条末尾。
F4：重复上一个命令或操作（如有插入、 删除、粘贴、编辑等），相对引用和绝对 引用键。按一次一个变化，可以按四次， 四种变化。例如选中“A1”引用，反复按 F4键时，就会在$A$1、A$1、$A1、A1 之间切换。日累计：C1=$B$1:$B1，利
三、数据整理
动态显示最大 、最小值 =A1=MAX(A$1:A$1000) =A1=MIN(A$1:A$1000)
四、统计、分析
比较熟练地运用常用函数、合 并计算和数据透视表。常用函数主 要有统计函数、文本函数、日期函 数、逻辑函数、查找与引用函数， 财务人员还需要充分运用财务函数。 能熟练、灵活运用20个以上的函数
找简称或地址
B2= LOOKUP (1,0/FIND($H$2:$H$18,A2), H$2:$H$18)
[$H$2:$H$18] 是地址简称
四、统计、分析
⑷取小数点位的
四舍五入ROUND，向上取整数ROUNDUP、往下 取整数ROUNDOWN 、INT B1=round(A1,0) B1=round(A1,2)
四、统计、分析
⑶查找函数VLOOKUP, INDEX、MATCH、
lookup 将所有的查找内容全部到另一个报表中
=VLOOKUP($A2,'2008'!$A$1:$D$18,COL UMN(B1),FALSE) =INDEX($A:$E,MATCH($O2,$A:$A,0),C OLUMN(B1)) 其中的COLUMN(B1)代表2，然后可以自由拖动， 若是第一个要查找的是3，则是COLUMN(C1),c 以此类推。
可以在根据自己爱好在单元格的日期中 选择自己喜欢的日期表达方式 从业务参号中提取日期
B2=--TEXT(MID(A2,13,8),"#-0000")
四、统计、分析
⑻排名函数RANK
B1=RANK($A1,$A$1:$A$15) 斩头去尾得分
=TRIMMEAN(A1:A10,2/10)
⑼逻辑函数IF
三、数据整理
1、合并、分列
合并符&（=A1&B1，=A1&“-”&B1） 合并函数： =CONCATENATE(A1,B1) =CONCATENATE(A1,"市",B1) =PHONETIC（A1：F1） =PHONETIC（A1：F20）
=PHONETIC(offset($A$1,match(E2,$C2$:$c$500,),,cou ntif($C2$:$c$500,E2),2))
需用VBA语言）
Ａlt+=键快速操作，对区、片值的加总
二、高效录入数据
Shift+F11快速插入工作表 按Alt+Enter可在同一单元格中另起一个新行。 按Ctrl+Enter可使用当前条目填充选定的单元格
区域。 按Shift+Enter可完成单元格输入并选择上面的
单元格。 Ctrl+1 单元格格式设置(自定义)