Excel 五天快速入门

Excel 四天快速入门

Day 1- Excel 软件操作，快速上手

A.表格排版基础

1) 批量调整行高/列宽

∙ 选中整个表格然后在5-6之间变成十字形光标下拉。

∙

2) 合并单元格

∙

∙ 格式刷- 单击只能刷一次，双击可以刷多次

∙

3) 为图例选择边框

∙

∙ 虚线表示不能添加,实线表示可以添加

4) 去除网格线

∙

5) 隐藏/取消隐藏

∙ 右击-hide

∙ 选中被隐藏的前后两行-unhide

6) 知识点总结

B. Excel 单元格设置基础

7) 查看单元格格式的三种方法

∙ 右击-单元格格式(format cell)

∙

∙ Control + 1

8) 0-格式改为010.120的格式（数字占位符）

∙ 单元格内容>数字占位符，实际显示单元格内容<占位符，则用0补充

∙ "000"。1234显示为1234，12显示为012，“00.000"。100.14显示为100.140，1.1显示为01.100

∙

9) #-将结果保留三位小数（数字占位符）

∙ 只显示有数学意义的0，小数点后数字如大于#的数量，按#的位数四舍五入

∙ "###.##"，12.1显示为12.10，12.1263显示为12.13

∙

10) ?- 以小数点对齐，左右各3位（数字占位符）

∙ 在小数点两边为无意义的零添加空格,以便按固定宽度时,另外还用于对不等到长数字的分数。(小数点位置固定,两边延伸)

∙ "??.??"和"???.???"对齐结果以小数点对齐,"# ??/??"，则1.25显示成 1 ¼

∙ 将数值转换成分母的形式

∙ 6.86= 6 43/50

11) ,- 将数据缩小1000倍 （千分位分隔符）

∙ 如果逗号放在最后，1个逗号缩小1000倍

∙ "#,###"，10000显示为10,000

∙ "#,"，10000显示为10

∙ "#,,”，1000000显示为1

∙ 输入0, 则6857变成7

12) ,与# - 将数据缩小1000倍，保留2位小数（千分位分隔符）

∙

∙ 先保留两位小数再缩小一千倍

∙ 6857变成6.86

∙ 将数字使用千分位表示

13) @-引用原始文本，使用多个@可以重复文本 （文本占位符）

∙ 原始数据为“财务”集团“@”部“，显示为”集团财务部“

∙ @@@，显示为”财务财务财务“

∙ 在城市前加上广东-- 广东@

14) #- 重复下一个字符，直到填充满列宽(重复标志)

∙ 重复下一个字符，直到填充满列宽

∙ "@*-"，"ABC"显示为"ABC-----------"随着单元格长度而改变

∙ 将数据右边使用+补齐 @*+ 广州+++++++++

15) 条件- 快速复制格式，但是最多只能三个条件

∙ 小于50的显示“低”，50至100间显示“中等”，大于100的显示“高”

∙

∙ =等于 >大于

∙ <小于 >=大于等于

∙ <=小于等于 <>不等于

∙ 小于等于60显示差，小于等于80显示良好，大于80显示优秀

[<=60]"差";[<80]"良好";优秀

16) 其他格式

∙ 转换成人民币书写格式 ￥0元

∙ 数字转换成电话号码 000-0000-0000

C. Excel 数据分列基础

17) 数据分列-按照分割符号分列

∙

∙

18) 数据分列-按照固定宽带分列

∙

∙ 多选/错选 双击箭头or 向上拖动到空白处可以取消

∙ 忽略和选择输入的单元格

19) 分列修正数据

∙ 使用分列功能将文本数字改为数字- 选择General

∙ 使用分列功能将违日期改为日期- 选择Date

∙ 一次只能处理一列数据

20) 知识点总结

D. Excel 数据填充

21) 拉动填充

∙ 鼠标点击单元格，然后下拉或者双击

∙ 输入前两列然后右下角双击

22) 等差/等比数列填充

∙ 输入前两列例如1,3; 然后右下角双击

∙ 开始home-series

∙ 需要输入步长(间隔值)以及终值

∙ 等差linear, 等比Growth

∙ 勾选Trend则自动发现规律

23) 日期填充

∙ 拉动填充

∙ Home-series

24) 格式填充

∙

25) 快速填充

∙ Control + E

∙ 给Excel一个示例，然后快速填充

∙ 可以实习 拆分、合并、格式化等

E.查找与替换高级内容

26) 内容查找替换 -- Ctrl+F/H

∙ 可以一处一处查找/替换 也可以 replace all

27) 格式查找替换

∙ 查找-格式-选项-选择颜色

∙ 单元格匹配(Values) 查找的内容要和单元格完全匹配

28) 强制查找符号~和空格-- Shift+~

∙ 如果想查找*号的话，需要输入~*

∙

29) 通配符

∙ ? 代表单一字符

∙ * 代表N个字符

30) 知识点

F. Excel工作表保护/打印基础

31) 工作表保护- Reviews-Protect Sheet

∙ 仍然可以插入新的行/列,但是无法更改表格内容

∙ 试图修改回会出现Alter 报错

32) 工作簿保护- Review- Protect Workbook

∙ 可以在当前工作表修改删除数据,但是无法新建/删除新的表

33) 打印基础 – Control + P

∙ 分页预览- view- page break preview

∙ 将蓝线向右拖动即可调整打印页面

∙ 更改页面布局为1页- Page Layout – Width- 1 Page

34) 打印标题

∙ Page Layout- Page Setup

Day 2- Excel 函数进阶I

A. Excel常用数学函数

1. 引用

∙ 最基础引用=A1

∙ 跨Sheet表引用=Sheet名!A1，如：=案例1!A1 (引用是英文状态下)

∙ 跨文件引用=[文件名]Sheet名!$A1，如：=[工作簿1]Sheet1!$D$12

2. 求和Sum 函数

∙ 方法一：手动输入=sum(A1:A7)

∙ 方法二：

∙ 参数可以多个，可以为单元格引用，也可以为具体数值

3. 条件求和-- SUMIF

∙ SUMIF(条件区域，求和条件，求和区域）-- 英文状态下逗号

∙ 注意条件区域和求和区域要一一对应

∙ 加入$变成绝对定位，使条件区域不会改变

∙

∙ 广东* 是指广东开头的区域内容

4. 条件求和--SUMIFS

∙ (求和区域，条件1区域，条件1，条件2区域，条件2，…)

∙

∙

5. 四舍五入--ROUND

∙ Round(数据，小数位数)

∙

∙ 参数必须是单一的,不能是区域的

6. 取余函数-- MOD

∙ MOD(被除数,除数)

∙

7. 将数值进行取整（向下）-- INT

∙ INT(代切割的数字)

∙

B. Excel常用逻辑函数

8. 6个逻辑符号

∙ >：大于，例如10>6

∙ <：小于，例如6<10

∙ =：等于，例如10=10

∙ >=：大于等于，例如10>=6

∙ <=：小于等于，例如10<=10

∙ <>：不等于，例如10<>6

∙ Excel回返回 True/False 表示是否成立

9. If 函数

∙ IF(逻辑判断，判断结果为真的值，判断结果为假的值)

∙

∙ ALT+ENTER 为excel换行建

∙ 函数嵌套=IF(R4<60,"不及格",IF(R4<90,"良好","优秀"))

10. IFS函数

∙ IF(条件1，值1，条件2，值2，条件3，值3，…，TRUE，默认值)

∙ True表示前面的值都不符合，如果不写True会报错N/A

∙

11. AND/OR/NOT 函数

∙ AND函数：全票通过才为真（TRUE），即所有参数都为真,

∙ AND(条件1，条件2，条件3，...)

∙ 示例: =IF(AND(W4>=60,X4>60),"及格","不及格")

∙ OR函数：一票通过即为真（TRUE），即参数中任何一个为真，就为真

OR(条件1，条件2，条件3，…)

∙ 示例: =IF(OR(W19>=60,X19>60),"及格","不及格")

∙ NOT函数：用于求反值，即不满足条件才返回真(TRUE),

NOT(条件)

C. Excel常用日期和时间函数

12. DATE(年，月，日)

∙ 年、月、日可以为文本、数值、单元格地址或者公式等，月和日可以为负数，当出现负数时为指定年份1月/1日开始递减该（月份/天数+1）

∙ =DATE(B4,C4,D4)

13. YEAR/MONTH/DAY

∙ 提取出日期中的年/月/日

∙ 日期可以为文本、单元格地址、日期型公式等

∙ =YEAR()/MONTH()/DAY(),错误日期会报错

14. TODAY/NOW

∙ TODAY显示今天的日期，NOW显示当前时间

∙ = TODAY()/NOW ()

∙ 数据会实时更新

15. DATEDIF

∙ DATEDIF(开始时间，结束时间，时间类型)

∙ 注意如果开始时间大于结束时间会报错

∙ 日期差，时间类型有d/m/y可选

∙ DATEDIF("2019/01/01","2019/10/10","d")

16. EOMONTH

∙ 返回任意日期前后任意月份的最后一天

∙ EOMONTH(日期，调整月份数)

∙ EOMONTH("2019/01/01",5)

∙

∙ 0表示当前月份的最后一天

17. 日期的计算

∙ 日期+数字N，该日期往后N天

∙ 日期-数字N，该日期往前N天

∙ 日期-日期，两个日期的相关天数

∙ 算距离今天的长度 ：E-TODAY()

D. Excel 查找与引用函数上

18. MATCH

∙ 参数：MATCH(待查询信息，数据所在区域，查询模式)

∙ 反馈某个数据在用户指定区域内对应的顺序号,可返回-1,0,1

∙

∙ 示例： MATCH(5,A1:A100,0)

∙ 反馈某个数据在用户指定区域内对应的顺序号

∙ 1：查询大于等于待查询数据的最小值（降序排列）

∙ 0：精确查询待查询数据（任意）

∙ -1：查询小于等于待查询数据的最大值（神故乡排列）

19. INDEX (索引)

∙ 参数：根据指定的表格区域及二维坐标，返回单元格相关信息

∙ INDEX(表格区域，行坐标，列坐标)

∙

∙ 示例：INDEX("A1:B10",3,1) 第三行，第一列

∙ 注意行坐标和列坐标与单元格的行号,列号无关,只针对用户指定的表格区域

∙ Index与Match 合体动态刷新：

=INDEX(B5:F10,MATCH(C4,B5:B10,0),MATCH(E4,B5:F5,0))

20. ROW/COLUMN

∙ ROW函数查询单元格的行号, COLUMN函数查询单元格的列号

∙ ROW(待查询单元格/区域), COLUMN(待查询单元格/区域)

∙ 示例：ROW(A1)/COLUMN(A1)/ROW()

∙ 该序号为Excel的原始序号，与相关查询范围无关,查询列号的时候，返回的信息并非坐标的对应的字母, 而是字母对应的顺序；若不输入参数，则返回当前单元格的序列号

∙ 当参数为区域时，仅以左上角第一个单元格的信息为准

∙ 对于经常变动的表格使用row函数比序列好,因为编号会实时更新。

Day 3- Excel 函数进阶II

E. Excel 查找与引用函数下

21. VLOOKUP-- 关联查找函数

∙ VLOOKUP(检索关键字，查找范围，返回列数，查找选项)

∙ 示例：VLOOKUP("小明",A1:F100,2,0)

∙ 检索区域建议不要选全部,0代表精确查找

∙ 注意检索关键字在查找范围中必须是第一列

22. 反向查找

∙ VLOOKUP(检索关键字，IF({1,0},检索值范围,值范围)，返回列数，查找选项)

∙ 示例：VLOOKUP("小明",IF({1,0},B1:B100,A1:A100),2,0)

∙ 注意数组公式(含有大括号)必须使用Ctrl+Shift+Enter

23. vlookup多条件查找

∙ VLOOKUP(关键字1&关键字2，IF({1,0},值1&值2,值3)，返回列数，查找选项)

∙ 示例：VLOOKUP(D1&F1,IF({1,0},A1:A10&B1:C10,D1:D10),2,0)

∙ 注意IF的第3个参数只能为1列数据

24. vlookup一次性返回多列

∙ VLOOKUP(检索关键字，查找范围，COLUMN(A1)，查找选项)

∙ 检索关键字要锁定

∙

∙ 示例：VLOOKUP("小明",A1:F100,COLUMN(B2),0)

∙ 多条件和多列集合：

F. Excel 常用的文本类函数

25. LEFT/RIGHT—左右截取

∙ LEFT(待截取的字符串，截取的位数)

∙ RIGHT(待截取的字符串，截取的位数）

∙ 示例：LEFT("EXCEL2016",5) => EXCEL

RIGHT("EXCEL2016",5) => L2016

∙ 注意空格为1个字符

26. MID-途中截取器

∙ 从左边起的任意位置截取任意字符串

∙ MID(待截取的字符串，起点的位数，截取的位数)

∙

∙ 示例：MID("EXCEL2016",2,5) => XCEL2

∙ 注意起点的位数指需要截取的第一个字符串的位置

27. LEN/LENB =>byte--字符串长度计算

∙ LEN能反馈一个文本字符串的长度

∙ LENB能返回一个文本字符串的字节长度

∙ LEN(文本字符串)/LENB(文本字符串)

∙ 示例：LEN("EXCEL") => 5

LEN("芒种学院") => 4

LENB("EXCEL") => 5

LENB("芒种学院") => 8

∙ 注意：空格、数字、字符、英文标点，1字符=1字节；

∙ 中文、中文标点，1字符=2字节

28. FIND--字符位置探查器

∙ 查找某个字符串在另外一个字符串中第一次出现的位置

∙ FIND(查找内容，查找区域，[查找起点])

∙ 示例：FIND("芒种","网易云芒种") => 4

FIND("芒种","网易云芒种",2) => 4

FIND("小雪","网易云芒种") => #VALUE!

∙ 在第一个查找到的位置上加一

∙ 注意区分大小写 且不支持通配符查找（?&*）

∙ FIND & MID 函数集合：A10001-建筑-办公楼，将两个-中间的内容提取出来

∙ =MID(N4,FIND("-",N4)+1,2)

29. SEARCH--字符位置探查器

∙ SEARCH(查找内容，查找区域，[查找起点])

∙

∙ 示例：SEARCH("芒种","网易云芒种") => 4

SEARCH("芒?","网易云芒种") => 4

SEARCH("小雪","网易云芒种") => #VALUE!

∙ 注意不区分大小写, 支持通配符查找（?&*), 强制查找符号~

∙ LEFT& FIND函数集合：A10001-建筑-办公楼

∙

G.EXCEL函数综合汇总

30. F9--刷新页面函数/显示计算结果

31. 数组基本形式

∙ 可以存储多个值的变量

∙ 示例：={1,2,3} / ={1,2,3;4,5;6}

∙ 注意使用数组一定要使用Ctrl+Shift+Enter

∙ 注意使用;表示换行，使用,不换行

32. 多单元格数组公式

∙ 选中多个单元格之后，统一进行计算

∙ =A1:A100*B1*B100

∙ 示例：=A1:A100*B1*B100

∙ COUNTIF函数:

∙

33. 单个单元格数组公式

∙ 单个单元格的数组公式，要有一个函数对数组进行操作

∙ 示例：=SUM(A1:A100*B1:B100)

∙ If{1,0}表示函数会执行两次，当第一个条件为真，就会执行第二个。

H.EXCEL常见错误汇总

34. Excel查错功能

35. #DIV/0!-- 除数为0出现的错误

∙ E.g. = 6/0

∙ 发生原因：除数为0

36. #NAME?-- 函数名字拼写错误导致

∙ E.g. = SUMS(A1:A100)

∙ 发生原因：函数拼写错误

低版本调用了高版本函数

调用了不存在的函数

37. #VALUE! -- 运算规则出现出错

∙ E.g. =1+"我"=SUM(A1:A100*B1:B100)

∙ 发生原因：数组公式未正确使用Ctrl+Shift+Enter

文本与数值进行数学运算产生的错误

38. #REF! -- 引用的单元格被删除

∙ E.g. =SUM(#REF!)

∙ 发生原因：唯一性，比较少见，文件相互引用时且丢失了一般会发生

39. #N/A -- 找不对数据错误

∙ 发生原因：一般出现#N/A意味着语法没有错误；

出现在VLOOKUP中，有时候不是错误，有时候是参数书写不当导致

40. 循环应用-- 函数自己调用了自己

∙ 解决方法：通过【公式】-【循环引用】进行检查

41. IFERROR -- 错误处理函数

∙ IFERROR(公式，错误时显示的值)

∙ E.g. =IFERROR(A1/A2, "")

∙ 解决方法：输出空要使用""，不能不写第二个参数

∙

Day 4- Excel 图表基础+数据透视表

A. 制作动态图标

∙ Preference- view- developer tool

∙

B. 数据透视表

本文来源：https://www.2haoxitong.net/k/doc/db882219c0c708a1284ac850ad02de80d5d806ed.html