MOS Excel 笔记
哔哩哔哩视频地址
分享学习笔记,以方便日后查阅。
录入数据
推荐横向录入。
推荐tab: tab tab tab tab enter tab…
特殊数据录入
日期录入
必须以 - 或 / 分隔
百分比录入
推荐先修改数据类型为百分比格式
身份证号码录入
excel计算精度为15位,后续会省略
格式需要改为文本
原位填充
ctrl+enter,选中区域全部填充
批注☆
添加批注
审阅-添加批注
右键-添加批注
显示批注
审阅-上一条/下一条/显示所有……
单元格保护
审阅-工作表保护,全表保护,不可编辑
选中某区域,右键设置单元格格式
-保护-取消锁定
;工作表保护后该区域可编辑
单元格默认都是锁定状态,一旦执行保护,都会被保护起来。
两种形式
- 部分锁定、其余不锁定
- 部分不锁定、其余锁定
条件格式
条件格式命令:后面规则覆盖前面的
标记重复值/唯一值
开始-条件格式-突出显示单元格规则-重复值
内置条件
同理
管理规则
开始-条件格式-管理规则
可以上移、下移规则位置,修改执行顺序
数字格式☆
主要在数字模块 货币、小数点位置
单元格格式-数字 更详细
日期
本质是数字,从1900/01/01开始
1900/01/01==1
自定义数据格式
根据现有格式代码进行组合和修改,制作出新的效果
自定义数据格式-目标格式-自定义-开始自由发挥
公式-函数-引用
公式
以等号开头的式子作为单元格内容
函数
把复杂的工作包起来
例SUM(B2:F7)
引用
把另外一个单元格的值拿到另一个单元格中使用
- 相对引用:随着位置的不同产生递增的变化 例:D3
- 绝对引用:拖动时字母(列号)和数字(行号)都不变 例:$D$3
- 混合引用:字母锁定,数字不锁定;或反之 例:$D3, D$3
引用一列 E:E
可跨工作簿引用(链接) 例=’[新建 Microsoft Excel 工作表.xlsx]Sheet1’!$A$1
默认加绝对引用
函数
tab
补全函数名
点击fx可选择参数
ROUND
保留有效位数。
参数:Number, Num_digits
待处理数值,保留位数 (可以是引用)
ROUNDUP:向上取整
ROUNDDOWN:向下取整
LEFT
从左侧截取字符串的部分内容
参数:Text, Num_chars
COUNTIF
条件计数 -> 区域,条件
参数:Range,Criteria
COUNTIFS
多条件计数,取与
参数:Range1,Criteria1,Range2,Criteria2
SUMIF
条件求和
参数:Range,Criteria,Sum_range
条件区域,条件,加和区域(有时需要求A:A列满足某条件的条目在B:B列值的和)
SUMIFS
多条件求和
参数:Sum_range,Range1,Criteria1,Range2,Criteria2
IF
条件判断
参数:Logical_test,Value_if_true,Value_if_false
逻辑表达式,符合返回值,不符合返回值
可以函数嵌套,在函数栏点击函数名即可跳入该函数
VLOOKUP
查找引用类的函数
从一个表中根据某个字段拉取另一个表中的数据:找什么,去哪找,返回什么
参数:Lookup_value,Table_array,Col_index_num,Range_lookup
查找内容,查找范围(必须包含返回内容所在的列),返回内容(列标号,用数字表示)
Range_lookup:精确匹配或近似匹配:精确匹配用 0 或 False 表示;近似匹配用 1 或 True 表示;为“可选”项,即可填可不填;若不填,则默认值为近似匹配。一般需填‘0’
查找范围一定是完整的几列或锁定的封闭区域,不能是非锁定的某封闭区域,不然会出错
补充:
- 跨表查找,在Fx界面中实现
VLOOKUP进阶
部分匹配
&代表连接,*代表任意字符 “*”&B3,以B3结尾;B3&“*”,以B3开始;"*“B3”*",包含B3
近似匹配
匹配数值区间,查找范围需要升序排列,然后查找内容向下(较小值)找到返回值(即条件向后兼容)
LOOKUP☆
从单行或单列或数组中查找一个值,条件向后兼容:找谁,在哪找,返回什么
相当于模糊的VLOOKUP
参数:Lookup_value,Lookup_vector,Result_vector
日期
日期与数字对应 1900/01/01对应1
DAY,MONTH,YEAR
数据提取
WEEKDAY
返回当前日期是一周的第几天
参数:Serial_number,Return_type
(起止模式,1-周日到周六1~7,2-周一到周日1~7,3-周一到周日0~6)
WEEKNUM
当前周是一年的第几周
NETWORKDAY
返回两个日期之间的完整工作日数目
参数:Start_date,End_date,Holidays(自定义假期)
WORKDAY
多少天之后是哪天
参数:Start_date,Day,Holidays
DATEDIF
多功能隐含函数
参数:开始日期,结束日期,类型值
类型值 | 说明 |
---|---|
y | 计算结果是两个日期间隔的年数 |
m | 计算结果是两个日期间隔的月份数 |
d | 计算结果是两个日期间隔的天数 |
ym | 计算结果是两个日期间隔的月份数,不计算相差年数 |
yd | 计算结果是两个日期间隔的天数,不计算相差年数 |
md | 计算结果是两个日期间隔的天数,不计算相差年数和月份数 |
监视窗口
修改某个表格时想快速观察另一个表的修改情况
公式-监视窗口-添加监视
等价方法:视图-新建窗口
方案管理
数据-(预测)模拟分析-方案管理器
添加方案、显示
摘要-方案摘要(需选择结果单元格)
带公式的条件格式
开始-条件格式-新建规则-使用公式确定要设置的单元格,设置公式
例如 标记重复值:=COUNTIF($B$30:$B$35,$B30)>1
- 选中数据区域
- 添加条件格式,选择公式
炫酷版,需一单元格(例F4)加一块区域(例A1:C8)
- 设置F4单元格为下拉菜单 数据-数据验证-设置-验证条件-允许序列-选中某区域作为变动范围
- 选中A1:C8 新建格式规则
=$A1=$F$4
效果:F4中选择任意数值*后,A*:C*整行变化
数据透视表
选中数据区域任意单元格 插入-数据透视表
基本布局:原表列名列出,可放入筛选、行、列、值模块
-
筛选有两种
- 常规筛选 拖动列名
- 切片器筛选 插入-切片器
-
统计方式修改
- 选中数据-右键-值汇总依据
- 列名拖入
筛选
后,下拉菜单-字段设置
图表
关键要素:分类,数值
选中数据-插入-图表
多数图表☆
选中标题,再按住ctrl依次选中分散数据 -插入-图表
复合图表
选中数据-插入-插入图表-右下小箭头-所有图表-组合 添加次图表
鼠标放在目标上-右键-设置格式
数据透视图
插入-数据透视图
数据透视表 | 筛选 | 列 | 行 | 值 |
---|---|---|---|---|
数据透视图 | 筛选 | 图例 | 轴 | 值 |
快捷键
shift+f11 新建工作表
ctrl+enter 原位填充
f4 绝对引用,相对引用切换
单元格上方格线处点击:到达表格内容头部
单元格下方格线处点击:到达表格内容尾部
单元格右下角点击:自动扩充引用