Excel使用备注

Excel使用备注

学习B站 【全套】excel零基础教程，从小白进阶到高手

案例: 互动式专案管理仪表板 (美观 动态)
案例: 员工排班表 (非常强大方便, 制作一次就不用每次输入, 而且美观)
搭配Parse Hub分析网络资料 (无需代码知识, 只用鼠标点击)

Excel中每行为一组数据, 每列为一个项目属性, 除非为了美观可以在制作后进行转置(复制粘贴后选择转置), 否则不要违背这个原理    
元数据为每一单元格, 每一行为一条数据, 所有数据为一个表  
(也就是说要主动为数据套用格式,或者声明为表,来确定数据)  
 多个表的有机组合(关联)为一个Excel文件, 而没有关联的数据理应放在不同文件中     
 Excel中使用相对参照,每一列序列加一,可以选中数据范围后按下F4(A1:B2 -> $A$1:$B$2)

大部分内容通过查找或者在菜单栏找一下就可以找到上手去用，所以记下名字就足够了

数据验证: 资料->数据验证 验证某单元格输入正确,如果错误输出提示信息,数据验证选择数据类型为序列可以制作下拉三角

定义名称: 框选,选择公式->定义名称 或者框选后点击输入栏左侧输入框输入名称,可以用名称指代数据范围,公式->名称管理员中可以方便管理

选项中可以设置用R1C1引用方式或者不用, 默认是用数字代表行, 字母代表列, 此时列在前, 否则R1C1 行在前

Excel中的数据类型：

数据项	引用方法
单元格	相对：R[0]C[0] 后一格 RC[1] 绝对R1C1 或者 A1 F8直接索引
行	20:20(第20行整行)
列	A:A(第1列整列)
表	=表1[[#全部],[日期]:[是否发货]] 或者 表1[#全部] (需要先设定为表, 可以重命名)
元组（一条记录）	A14:J14 (因为一个确定的表的列是不会变的)
数据（一列属性）	=表1[对方单位] 索引本行的某一列: =[@未回款金额]
工作页面（sheet）	=DataSheet!G9
函数（一个方法）	=VLOOKUP([@对方单位],表7,3,TRUE)
工作簿（一个 *.xlsx）	=[工作簿.xlsx]Sheet1!$D$4

函数

只要知道有这么一个函数就可以入门使用, 在使用时会有详细的注释甚至视频解说
函数支持一些运算符, 按照直觉使用就行, &连接, $绝对位置, > < = , “” 字符串

简单函数

选中某单元格,点击插入函数标签,选取适当函数,在选取页面可以查看函数说明.
函数中Array指数据范围 起始行:终止行

• SUM(num1, [num2],) 和
• AVERAGE() 均值
• MIN() MAX() 最值
• LARGE(Array,Key) SMALL() Array中第Key位大\小的数
• IF(Logical_test, Value_True, Value_False) IFS()多重判断 条件格式,可以嵌套
• MOUNTH(Date) 从日期格式中截取月份

复杂函数(部分函数只有Microsoft 365版本支持)

• VLOOKUP(要查找的关键字, 资料范围, 要返回资料范围的第几栏, 是否使用模糊匹配) XLOOKUP() L形查询,数据最左栏需要和关键字相同,要关键字递增排列(适用于等差表)
• IFERROR(f(),Error_msg) 如果查找为空,执行 Error_msg
• COUNT() COUNTA() COUNTIF() COUNTIFS()计算数字栏数目\计算非空格数目\计算满足条件栏数目, 可以将条件和单元格关联,通过修改条件进行多种计数 COUNTIF(data, “>”&1000) &用来串联文字和单元格,计数data中大于1000的单元格数
• SUMIFS() 按条件查找后求和
• INDIRECT() 以线索中文字查找目的单元格
• CONCATENATE() CONCAT() &(Ampersand)连接符号 连接文本 使用CHAR(num)插入符号
• LAMBDA() 新建函数可以递归 递归应用(文字替换)
• OFFSET() MATCH() 定位抓取某位置的表

冻结窗口 拆分视窗

排序: 选择所有数据列再按行排序

筛选: 筛选后可以右键选择从下拉菜单中选择, 以填充单元格

• 插入切片器可以通过面板中的按纽完成筛选
  • 切片器只能通过菜单栏切片器栏进行样式设置
  • 切片器需要以图为基础插入,之后右键连接多个图
• 开始->条件格式 可以筛选使得某些单元格(前10位)按照一定格式(颜色或者标签)显示

使用主题颜色可以方便的更换整体颜色风格

数据->合并计算: 合并不同季度的数据, 并建立多张表的连接

图表

选中数据, 插入,选择图表类型,图标分为两类: 原数据直接生成:图表 数据处理后生成:数据透视图(首先生成数据透视表,据此导出图表)
可以先 插入->数据透视表 建立数据透视表后,进入图表右侧数据透视表字段:

• 通过拖动属性名(表头),实现筛选\建立行列二维表\计算求和值,均值等数值
• 点击区域字段选中字段设置可以指定值的计算方式

图表样式

选中图表在菜单栏可以设置整体样式,行列配置
选中图表元素,右键->设置XX格式,可以在右侧折叠框中配置图表组件样式
点击图表,点击右上角加号可以快捷添加图表组件
选中某一空白单元格,可以选择 插入->迷你图栏 插入折线\柱形等迷你图

图表制作

• 散点图+趋势线: 展示未来走势
• 组合图: 比较差异较大的多个属性

快捷键

• Ctrl+Alt+F5 全部刷新 (选中数据透视表,选择菜单栏数据透视表分析->刷新\全部刷新)
• Alt+F5 需要选中数据透视表,刷新
• Ctrl+MouseLeft 复制 复选

技巧

• 按住Ctrl选中不同表可以同时编辑,只适用完全相同的表
• 数据->移除重复项
• 数据->分列 将一列按偏移量或分隔符分成多列
• 视图->冻结窗格

打印

首先进行打印预览:选中表格后选择页面布局设置纸张\边距,点击打印区域,选择要打印的表格内容,并且显示打印边线.

1. 分页布局:
   点击菜单栏视图选项,选择分页预览,拖动分页线来设定每一页的内容范围
2. 页眉页脚:
   点击视图下的页面布局设置页眉页脚等信息
3. 页码:
   点击审阅->视图 选中页脚区域右侧部分,点击菜单栏页眉和页脚工具下页码项\页数\当前日期等项设置页码
4. 水印:
   通过在页眉页脚插入图片间接设置

宏

视图->宏
如果常用可以在左上角下拉按钮中选择其他命令->自定义功能区,选择常用命令,勾选右侧的开发工具选项,此时菜单栏会增加开发工具按钮
通过录制宏即可开始,录制每一个执行的步骤,适用于多个格式相同相同的表进行相同的操作
启用宏后需要在保存时选择适当的保存选项,在文件打开时自动使用已录制的宏
如果要忽略每次宏执行的提示窗口,可以对其进行编辑,修改VBA程式码在程序块开头(通常在Sub Name() 之后)新增:Application.DisplayAlerts = False

多表格分析

数据->获取数据,使用Power Query (首先需要将数据转换为表)
建立空白查询, 输入 = Excel.CurrentWorkbook() 打开当前工作表,选择需要的信息进行合并,同时建立表之间的联系

目标搜寻 规则求解

数据->模拟分析->单变量求解

组件

多选框 单选框 滚动条等

常见的问题和解决方法

1. 单元格中的公式以文本格式显示

设置单元格样式为函数

2. 图表需要把一些项目归类到其他栏

在透视表的基础上利用查找函数将前X行和新建的表绑定，最后一行计算其他量

3. 仪表盘显示进度百分比

制作组合图，直线加饼状图，将饼状图下部分设为透明，直线图的坐标轴起点设置在中心

4. 使柱形图显示进度

制作组合图,数据整理为基础量和增量

5. 函数无法使用

查看是否需要购买 Microsoft 365, 有些函数要高级版才能用

6. 新增的一条数据没有继承表格中的函数 下拉选项

要将表格选中点击插入-> 插入表格, 定义为表格
或者将整列插入函数公式, 使用IF处理空项

7. 如何美化图表

风格要确定统一, 图和表之间的关联要做好, 只能有一张总表, 依据总表设置多个数据分析表(透视表), 再依据透视表设置图
删除不需要的图元素, 以及重复的元素, 保持页面简介, 适量添加阴影显现出立体感
切片器使图保持动态
保持多个表的信息的统一
画笔绘制表格 四周空白列做边距 背景图片

8. 数据透视表无法正常刷新，尤其是新增数据时

可能是用wps excel不同软件打开，但检查了透视表的数据源没问题，索引的是整个表，在数据项上设置了数据验证->列表，生成了下拉三角，检查了列表的数据源也没问题，索引的是某列但设定为表后可以根据列内容自动扩充。新增的项满足数据验证要求，也在透视表的数据源里，甚至在透视表字段的筛选项里可以看到新增项，但不管怎么勾选都没法显示出来。
最后就只能粗暴的选择该表，删除，再重新选择字段。
好在图是建立在透视表的关联表上的，不用重新设计图格式

图表效果