O EASY EXCEL

4.excel文件的保存

• 保存格式

  Xlsx xl:excel s: 复数 x: 2007版

  宏：macro

  保存为模板：另存为-excel工作簿 – 保存类型选带模板的 位置要在templates下面

  新建的时候在我的模板中找

• 自动保存

  开始- excel选项（右下角） - 保存 – …

• 保存工具选项

  另存为 – （右下角）工具 - 常规选项 设置打开密码（只读密码）和修改密码（能改吗）

• 打开方式

  开始 – 打开 – 可以选打开的方式（只读/副本…）

• 文件属性

  开始 – 准备 – 属性…权限…

Excel本质是一个rar文件/zip

5.worksheet管理工作表

操作都是 在sheet 1 …处右键操作

• 给sheet1（工作表）重命名 – 在左下方右键选中 重命名（双击sheet1也可以改）
  

• 工作表的插入，删除
  

• 工作表的复制，移动 
  

• 工作表的颜色设置
  

• 工作表的选择
  

• 隐藏和取消隐藏
  

• 工作表保护 
  

6.单元格操作

Book工作簿 由 sheet 工作表组成 sheet由行列组成

• 选择

• pagedn 整页翻 Ctrl+pagedn 回到最上面 ctrl+下 到最下一行 ctrl+上回到最上 ctrl+ 右 到

  最右一列XFD

  某一个单元格的名字 C8：第8行C列

  4R×3C选中4行3列

  输入A1：C4选择区域

  A1, B2选中两个单元格

  A:A 选择整列 1:1选择整行 点击某行/列的名字（ABCD…）也可以选中整行/列

  选择一个区域可以定义一个名字，在下拉列表里可以载入选择

• 设置行列高宽

  在两个行/列的名字之间拖动可以设置行高列宽

• 插入，删除

右键选中某一个单元格 删除某行/列 

整行、整列、右侧左移、下方上移

右键插入 整行 活动单元格右/上移

• 隐藏和取消隐藏

• 移动与复制行列，单元格

复制一个区域，黏贴后在左下角有一个智能标记

开始（菜单右边）里面有一个粘贴

7.单元格类型 为学习公式做铺垫

• 数据类型
• 文本型
• 数字型
• 小数、分数、科学计数法
• 日期和时间
• 自定义格式

’009527 文本型单元格

输入身份证号 ’51070…… （否则是科学计数法）

输入2008/08/08 --- 显示2008/8/8

右键 – 设置单元格格式可以查看单元格类型 （还能改日期的显示方法）（同样东西的不同表达格式）

里面有个“自定义”在里面可以查看数字代码 

遇到全是###说明被挤住了，就拖动行宽，或者在两列中间双击一下

在开始里面可以设置数字单位，
1m2 --- 在字体里(右下角的小箭头)可以设置“上标” ---  1m2

8. 单元格格式

• 现成格式
• 表格样式与单元格格式
• 自己设定
• 对齐、字体、边框、填充、保护
• 条件格式

选中一个区域，右键 设置单元格格式  对齐、字体、填充、边框…

格式刷刷格式

开始 – 样式 – 单元格样式 里面可以设置配色和其他等
          单元格样式里下面新建样式可以自己设置样式 

页面布局 – 主题 – 设置颜色 字体 效果 之后可以保存主题

设计表格样式 - 样式 – 表格样式 样式由主题组成，根据主题的颜色生成

新建表格样式可以自定义表格样式

手动换行 alt + enter 

文字方向 设置单元格格式 

保护：审阅 – 更改 – 保护工作表

锁定 之后 就不能改了 

可以设置密码 

9.条件格式

先选中 开始 – 样式 – 条件格式 – 管理规则（新建规则，删除规则）--- 每条都可以管理 – 双击之后来编辑

如果条件有互斥，默认排在最上面的条件来设置

满足某条件，则显示某格式（边框，填充…）

10.编辑

• 查找
• 替换
• 定位

查找 CTRL + F 

里面有个选项可以做一些设置（范围，顺序按行还是列，）

比如，要查找“子”，勾选“单元格匹配”，那么找出来的就只有子，否则可以找出老子，孔子…

通配符：

1. “*”任意个字符

2. “?(英文的)”代表一个任意字符

   替换 CTRL + H

   找到一个东西然后替换成XXX

   还可以设置替换后的各式

   定位

   开始 – 望远镜 – 定位条件

11. 填充序列

• 自动填充功能
• 自定义序列

点击一个单元格，在这个单元格右下角有个黑点，将鼠标放在上面变成一个黑色十字架，拖动后再右下角选择填充序列

在开始 – 编辑 – 填充 可以自定义设置系列

在office 按钮 – excel选项 – 编辑自定义列表

可以自己设计序列 输入一个点一下enter 然后 点击 添加

12．插入对象 （PPT的插入比较详细）

插入

表

特殊图像

符号

插入表之后 – 点击表格里的一个地方会出现

上下文菜单“设计”

调整文本框中

CTRL + 按住一个图像 可以复制一个

Ctrl + >/< 符号 可以调整文本框中字体大小

插入 对象 公式

13.打印

主题

页面设置

调整为合适大小

打印预览

页面视图

选中一个区域后 – 在 页面布局 可以设置为打印区域

页面布局 右下角箭头 打印预览

分隔符 可以移动

打印标题 ： 在顶端标题行那里点一下 然后在excel中选中标题行

          左端标题行………………………………………………….左边的标题行

在excel左上方A 和 1 之间的三角 那是全选

14.分类汇总

先选中一个区域 - 数据 – 分级显示中的组合

………………………………………………………………………组合 – 自动建立分级显示

数据 – 分类汇总

15.数据

获取外部数据

排序和筛选

数据工具

分类汇总

排序筛选

要先变成一个数据清单或者data table

插入 表 选中区域

数据清单 = 数据表

这时出现 上下文菜单

然后就可以排序了 在标题栏旁边有个三角 可以选择如何排序

选中区域 数据 排序

在 次序里有个自定义序列 可以自己设置排序方法

排序后会在小箭头旁边出现一个向上或者向下的箭头

筛选
在小箭头里面有 筛选 这是 小箭头旁边出现沙漏

在数字筛选 中 可以自定义筛选的数字的范围

没有被筛选出的部分 被隐藏了 选不中

文本筛选 自定义筛选 需要搭配通配符 （*/？）

转意符号~假如想在筛选中有问号 需要写成~?

高级筛选

数据 高级 列表区域点一下然后在表中选中区域 条件区域 点一下然后 在表中选中写了条件的区域
比如说

年龄 年龄 年龄 武力

<=60 >=50 >=98

                   <=40

同一行之间是与 不同行是或

16.数据工具与数据有效性

数据 分列 有两类 1.分隔符号2.固定宽度

选 分隔符号 下一步

勾选 空格 完成

选中分隔好的 ctrl + c 然后 在 开始 粘贴下面的箭头点 选择性粘贴 转置

或者 选固定宽度 然后 自己设置多宽

然后 隐藏原始数据

然后 对转置好的 再来分列

分隔符号 设置为- OK

数据有效性

数据 - 数据有效性 输错了 停止/警告/提示

选择

数据有效性 允许- 序列 然后输入想要选的信息

17. 引用外部数据

数据 - 获取外部数据

比如 自网站 就把一个URL复制过去

数据来自网络 只要 数据刷新 就会更新网络上的数据

视图 窗口 冻结窗格

18. 公式

“=” 表示是一个公式

组合公式 按 F9就可以换回数字

^指数符号

1 + 2&3 = 33 &是字符串运算符

1 + （2&3）= 24

跨表引用 多维引用

跨工作簿引用

19. 公式 --- 绝对引用 相对引用

比如说 选中公式中的一部分（eg C5）然后按F4可以改成 “$C$5” 这就是 绝对引用 （后面的计算都不会随着填充的改变而改变）

制作九九乘法表

要定住哪个部分就在哪个部分前面加上绝对引用符号 $

=B$1*$A2

=B$1&""&$A2&"="&B$1$A2 显示出 1*1=1 双引号代表字符串

20.函数基本 MAX SUM AVERAGE MIN

SUM(D6：G6) 函数名字（求和） 参数列表 D6到G6

一个含有公式的单元格可以粘贴到另外一个单元格 会自动计算

定义一个区域：选中一个区域后，在左上角可以设名字【也可以在公式，定义的名称 中设置】

区域的计算

区域分交集和并集

逗号是并集 空格是交集

21.计数

COUNT 纯数数 COUNTA count文本

公式 - 自动求和 - 其他函数 countblank计算空白 countif（范围，条件） 计算满足条件的单元格数

去掉一个最高分 去掉一个最低分求平均 trimmmean(范围，去掉的比例)

选中函数的名字按F1 可以查看 该函数的帮助

22. 逻辑函数

逻辑关系 TRUE FALSE

公式 - 函数库中的插入函数 类别选择逻辑

and(2>1,3>2) flase

or(1<2,3>2) TURE

not(not（2>1）) true

IF 基本逻辑

if(logical test,value if true, value if false)

if(2>1,"Jige","bujige") --- jige

嵌套判断 =IF(A2>=60,IF(A2>=85,"优秀","及格"),"不及格")

IF配合计算

=IF("某单元格满足某条件",...,...)

IF配合公式

IF条件格式

开始 - 样式 - 条件格式 - 新建 使用公式确定...在编辑规则里写公式 下面格式设置格式

23.文本函数

公式 - 文本函数 - LEN（返回字符串字符个数）

LEFT 从一个文本字符串的第一个字符开始返回指定个数的字符

LEFT(A1，1)

=LEFT(A1,1)&"老板" 字符串相加要用"&"

=IF(LEN(A1)=4,LEFT(A1,2)&"老板",LEFT(A1,1)&"老板")

截取身份证中的年龄

• =RIGHT(LEFT(身份证号,10),4) 从左取10个再从右边取4个，截取出出身年龄

  = YEAR(NOW()) 然后单元格格式设置为常规 截取现在的年

  然后两者相减

• 或者：=MID(身份证,从第几个开始取,取几个)

姓鲁，名肃，字子敬 / 复姓诸葛名瑾字子瑜

鲁 =IF(LEFT(A1,1)="复",MID(A1,3,2),MID(A1,2,1))

肃 =IF(LEFT(SUBSTITUTE(A16,"，",""),1)="复",MID(SUBSTITUTE(A16,"，",""),6,1),MID(SUBSTITUTE(A16,"，",""),4,1))

子敬 =RIGHT(A16,2)

小写数字换成大写数字

123 设置单元格格式 特殊 中文大写数字

把左边的复制到右边 - 然后填充 - 复制单元格格式

24. 日期和时间

=TEXT(DATE(MID(B5,7,4),MID(B5,11,2),MID(B5,13,2)),"[DBNum1][$-804]yyyy年m月d日;@") --- 一九九四年十月十四日

TEXT函数的格式（文本，格式） 格式可以在设置单元格格式中自定义格式里复制

WEEKDAY 函数 returntype 为 2

取余函数 MOD(被除数,除数)

根据身份证后倒数第二个数 判断 男女 ：如果是奇数则男，否则女

=IF(MOD(MID(B2,17,1),2)=1,"男","女")

根据身份证号判断是否闰年

=IF(OR(MOD(D2,400)=0,AND(MOD(D2,4)=0,MOD(D2,100)<>0)),"闰年","平年")

25.VLOOKUP函数 查找

公式 查找和引用

VLOOKUP 列查找 HLOOKUP行查找

VLOOKUP 待查找的值所在的单元格---在什么区域查找---在所选中的区域中第几列，列代码---是否需要精确匹配（TRUE或不填近似匹配，FLASE精确匹配

出现问题到区域里面去查找 ，注意区域是否要绝对引用

=VLOOKUP(E18,F5:G10,2，FLASE)

• 根据身份证号判断男女

  =IF(MOD(LEFT(RIGHT(身份证,2),1),2)=0,"女","男")

• 根据身份证算年龄

  =IF(LEN(D19)=18,YEAR(NOW())-MID(D19,7,4),YEAR(NOW())-MID(D19,7,2)-1900)

模糊查找

26.引用函数

index 在矩阵里找

INDEX(区域，行数，列数） --- =INDEX(A1:C7,1,1)

offset 函数 移位

=OFFSET(某单元格移动起点做为，移动几行，移动几列，（height），（width）) 默认是没有后面两个参数的

eg. =SUM(OFFSET(B13,1,3,2,2) 向下移动1行，3列，然后是一个2*2的矩阵

数据有效性 - 序列 可以设置下拉式列表

MATCH函数

=MATCH(查找的某单元格，列区域，matchtype(0精确匹配，1小于，-1大于))

27.数组函数

新技巧

选中一个区域，然后输入一个数字 ， 然后按住ctrl 再按回车，这个区域就全部都是这个数了

类似的，先把某列第一个单元格的公式复制了，然后选中这列其余的单元格，在上面讲公式复制后，按住CTRL+ENTER，那么其余的就填充好了

FREQUENCY函数在公式 - 其他函数 - 统计 -FREQUENCY

=FREQUENCY(用来计算频率的数组，区间即分段点)【ctrl+shift+enter获得结果】

注意绝对引用

返回值是数组

28. rank函数

公式 - 其他函数 - 统计 - rank

=rank（哪一格，在哪一个区域，0或不填降序/1升序）

注意绝对引用

29.RANK函数

=RANK(LOOKUP(B2,$D$11:$E$12),$C$2:$C$7,1) 【注意绝对】引用

30. 图表 柱形图

选中区域 插入 图表

点一下 是选中所有同一颜色的 再点一下就是选中某一个 柱子

在图标上 右键 选择数据可以改行名称/列名称 、删除行列、 增加行列、 编辑行列

右键 移动图标 、 三维旋转、 更改图表类型 设置图表区格式

选择网格线 右键 可以设置网格线的格式

还可以设置 绘图区 背景墙 地板 数据点 图例 坐标轴 的格式

布局

31. 饼图和折线图和雷达图

选择数据区域 - 可以设置饼图的相关格式 学会使用切换行列

雷达图

=VLOOKUP($A$7,$A$2:$G$4,COLUMN(B7),FALSE)

设置为下拉列表可以 显示每个人的雷达图

32. 动态图表

序列 和 图表结合在一起

=OFFSET($A$2,MATCH(G2,$A$2:$A$6,0)-1,MATCH($H$1,$B$1:$C$1,0))

33.数据透视图

选中区域 插入 数据透视表/数据透视图

透过一些数据找出规律

34. 窗体

审阅 - 批注 拼写检查 繁体简体转换

**窗体 **

开发工具 - 插入 - 按钮

按住ctrl+shift 再按每个按钮可以选中所有

然后右键 - 设置控件格式 - 控制 - 已选择 - 选一个单元格

开发工具 插入 复选框checkbox

35. VBA入门 - 自定义函数

VB 一种编程语言（ = C# / C sharp）

VBA visual basic for application

VBS

扩展名必须是XLSM （另存为 - 启用宏的工作簿）

开发工具 visual basic - 插入 - 模块

Function AddOne(x As Range) 【As Double 参数列表 函数名字 返回值 】

'把单元格里面的内容取出来加一然后返回

AddOne = Val(x) + 1  

End Function

在 VBA 的编辑页面 双击 一个函数 右键 - 定义 - 可以 看到函数的属性和相关介绍

Function getDelta(a As Range, b As Range, c As Range) As Double

'通过一元二次方程的一般式得到delta

getDelta = Val(b) * Val(b) - 4 * Val(a) * Val(c)

End Function

设置断点 和 添加监视

设置断点 - 在 VBA编辑页面 在代码的左边的竖线那里点一下 就出现一个红点

添加监视 双击想要监视的东西 右键 - 添加监视 / 删除监视

在下面的监视窗口 还可以 修改 表达式

调试 逐语句

36.VBA条件结构

在VBA编辑页面 插入 模块

Function GetLevel(scroe As Double) As String

If score < 60 Then

    GetLevel = "不及格"

Else

    GetLevel = "及格"

End If

End Function

---

Function GetLevel(scroe As Double) As String

If score < 60 Then

    GetLevel = "不及格"

Else

    If score < 85 Then

        GetLevel = "合格"

    Else

        GetLevel = "优秀"

End If

End Function

---

37. 循环结构

Function Getsum() As Double

For i = 1 To 10

    Getsum = Getsum + i

Next

End Function

---

38. VBA 相关属性

开发工具 - 插入 - 按钮 - 新建 - 然后就有了一个新的模块

---

让EXCEL飞

#N/A表示没有找到

在VLOOKUP中，第四个参数是否精确查找不要忽略

开启手动计算： 公式 - 计算 - 计算选项 - 手动 。使用完后调回自动

使用真正的日期和时间来进行计算

• 数据 - 数据工具 - 分列 - 分隔符号 - 取消勾选所有的分隔符号复选框 - 在列数据格式处选择“日期”

DATEIF函数

• 用于计算两个日期之间间隔的年、月、天

• =DATEIF(开始日期，结束日期，日期间隔单位)

  • 日期间隔单位：Y：年 M：月 D：日

选择不连续的单元格

按住ctrl，选择不连续的单元格

给文本或者数值命名

公式 - 定义的名称 - 定义名称 - 确定

Inferror函数

=IFERROR(C2/B2," ")

---如果公式计算出现错误，则返回空字符

VLOOKUP函数

=VLOOKUP(要查的内容, 表格区域，第几列，精确查找还是近似查找)

注意：VLOOKUP函数的第二个参数所对应的表格必须按照要查找的内容所对应的列升序排列，否则会返回不正确的结果

INDEX & MATCH函数

INDEX函数就是 - 确定 ： 某行某列交叉点所在的单元格中是什么内容，可以用INDEX取出这个内容

= INDEX(单元格区域的引用地址，第几行，第几列)

MATCH函数就是 - 如果在表中有一行或者一列数值，那么给定一个数值，就可以使用MATCH函数得到该数值在这行或这列中的位置是什么，返回值是个数字

=MATCH(要找的值，区域，匹配类型)

总结： 简单来说，INDEX就是确定某个位置上是什么值，MATCH函数是确定一个值在区域中的位置

CHOOSE函数

=CHOOSE(2,"A","B","C") 得到的值是第二个，B

RANDBETWEEN函数用来得到介于指定两个数之间的随机整数

OFFSET函数

=OFFSET(参照点，返回区域相对于参照点偏移的行数，返回区域相对于参照点偏移的列数，返回区域行高，返回区域列宽)

INDERECT函数

有点像C语言中的指针，作用是把字符串形式的单元格地址引用转化为正真的单元格地址引用