MOS Excel 笔记

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

  1. 选中数据区域
  2. 添加条件格式,选择公式

炫酷版,需一单元格(例F4)加一块区域(例A1:C8)

  1. 设置F4单元格为下拉菜单 数据-数据验证-设置-验证条件-允许序列-选中某区域作为变动范围
  2. 选中A1:C8 新建格式规则=$A1=$F$4

效果:F4中选择任意数值*后,A*:C*整行变化

数据透视表

选中数据区域任意单元格 插入-数据透视表

基本布局:原表列名列出,可放入筛选、行、列、值模块

  • 筛选有两种

    • 常规筛选 拖动列名
    • 切片器筛选 插入-切片器
  • 统计方式修改

    • 选中数据-右键-值汇总依据
    • 列名拖入筛选后,下拉菜单-字段设置

图表

关键要素:分类,数值

选中数据-插入-图表

多数图表☆

选中标题,再按住ctrl依次选中分散数据 -插入-图表

复合图表

选中数据-插入-插入图表-右下小箭头-所有图表-组合 添加次图表

鼠标放在目标上-右键-设置格式

数据透视图

插入-数据透视图

数据透视表筛选
数据透视图筛选图例

快捷键

shift+f11 新建工作表

ctrl+enter 原位填充

f4 绝对引用,相对引用切换

单元格上方格线处点击:到达表格内容头部

单元格下方格线处点击:到达表格内容尾部

单元格右下角点击:自动扩充引用

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值