Excel学习笔记

快捷记忆

• Excel最大计算精度是15位,第一种是将格式设置成文本，第二种是在输入前先输入’号开头

• ctrl+Enter 快速填充

• ctrl+A 全部选中

• ctrl+方向键 快速向下或…

• 双击边框

• 自定义排序：选项->高级->编辑自定义列表->导入***

• *: 表示任意个字符
  ?: 表示任意单个字符

• 高级筛选：自定义筛选区域+条件进行筛选***

• 数据验证中设置提示信息，和错误提示等

数据管理

• 数据验证
  • 在数据验证中设置输入信息，和出错警告等
    
  • 在数据验证中设置输入数据的要求，不符合规则进行报错
    
  • 在数据验证中设置序列，把每个选项之间用逗号隔开，进行数据输入时可直接进行选择
    
• 条件格式
  • 突出显示单元格规则：选择重复值以及大于，等于，小于，包含某指定数据等的单元格数据
  • 项目选取规则：选择前后%多少的数据，以及低于/高于平均值等的数据
  • 图标集：选取图标集后在管理规则里面设置对应的规则
  • 数据条：在单元格中显示，编辑对应的规则使数据更加突出
  • 应用：可以应用在找出数据差异，或者查找出不重复值。差异：选定区域，在突出重复值里可换为唯一值
• 表格美化
  • 对齐方式：跨列居中
  • 中文字体的选择尽量用无衬线字体，类似微软雅黑、微软雅黑 Light、黑体、华文细黑等。
  • 网格线一般选择关闭，避免产生过多的视觉干扰。
  • 标题字体一般10至14磅，正文字体大小一般8至10磅。重点内容需要加粗字体或边框标注出来。
  • 边框根据需要可以自定义修改
  • 用取色器进行取色，自定义颜色，输入RGB值
  • 选择对应的数据格式,比如：会计专用

  数据清洗

• 数据验证
  • 序列 ：用逗号隔开，输入数据时可进行选择（=参数表）
  • 圈示无效数据 ：先通过设置规定有效数据，然后进行圈示就行
  • 其他类型，如：整数，文本长度等根据需要自己设置
• 数据库连接
  • 根据数据文件类型选择对应的导入方式
  • 文本文件：要规定分割方式，每行或者每列的数据类型（时间等）
  • 属性->刷新时间
• 数据拆分-合并-转换
  • 拆分数据：把相应的数据填写到对应单元格中，然后ctrl+e
    
  • 合并数据：把数据写入同一个单元格，用逗号隔开，然后ctrl+e
    
  • 转换数值：对于汉字的转换需要手动输入两次，然后再ctrl+e
    
• 数据转换(二维转一维)
  1. 新建查询-> 合并查询-> 启动 Power Query 编辑器
  2. 将数据提交到 Power Query 编辑器
  3. 选中需要转换的数据
  4. 转换选项卡 -> 逆透视列 -> 文件 ->关闭并上载（重新导入excel表格）
  5. 原数据改变，进行刷新，转换后的表格进行对应的转换
     

第三次直播

• 锁定单元格 ： F4

• F9：查看编辑栏里的数值

• SUMIFS: 多条件求和

• COUNTIFS:多条件计数

• RANDBETWEEN(bottom, top):返回bottom和top之间的随意整数

• VLOOKUP：四个值 (要查找的项、要查找位置、区域中包含要返回的值的列号、返回近似匹配或精确匹配 - 指示为 1/TRUE 或 0/FALSE)

• REPT(text, number_times):

  • text: 文本内容
  • number_times：文本重复次数

• OFFSET(reference, rows, cols, [height], [width])

  • reference：数据区域最左上角第一个单元格
  • rews: 偏移行数，即左上角向下的行数
  • cols：偏移列数，即左上角向右的列数
  • height：可选。需要返回的引用的行高。 Height 必须为正数。
  • width：可选。 需要返回的引用的列宽。 Width 必须为正数。

• MATCH(lookup_value, lookup_array, [match_type])

  • lookup_value：要匹配的值

  • lookup_array：匹配区域

  • match_type：

  • 1 或省略	MATCH 查找小于或等于 *lookup_value* 的最大值。 *lookup_array* 参数中的值必须以升序排序，例如：…-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE。
    0	MATCH 查找完全等于 *lookup_value* 的第一个值。 *lookup_array* 参数中的值可按任何顺序排列。
    -1	MATCH 查找大于或等于 *lookup_value* 的最小值。 *lookup_array* 参数中的值必须按降序排列，例如：TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, … 等等。

数据统计-高级函数套用技术应用

Excel 四则运算：

• 除了不等于表示为**<>**,其他跟c++一样

Excel 单元格行列混合引用

• 相对引用和绝对引用：按F4切换引用方式。
  相对引用:	A1	复制、粘贴公式时行、列都变化
  绝对引用:	$A$1	复制、粘贴公式时行、列都不变化
  行绝对引用:	A$1	复制、粘贴公式时行不变化，列变化
  列绝对引用:	$A1	复制、粘贴公式时行变化，列不变化

Tab:函数确认输入

数字类型是靠单元格右边的

常见函数应用

• 最大值 MAX()
  最小值 MIN()
  第N大值 LARGE(区域或数组，N)
  第N小值 SMALL(区域或数组，N)
  平均值 AVERAGE(数据范围)
  排名 RANK(数字，查找区域，0：降序|1：升序)

• 常见应用函数
  COUNT: 返回数字个数
  COUNTA: 返回非空单元格个数
  COUNTBLANK: 返回空单元格个数

空格可以表示区域交叉的单元格

• 求和函数
  SUM: 返回指定区域的和
  PRODUCT :返回指定区域相乘结果
  SUMPRODUCT : 返回选定区域相乘之后和的结果

• 条件计数函数
  COUNTIF: 区域 + 条件，返回个数
  COUNTIFS：可以输入多个区域 + 条件，返回满足所有条件得个数

• 统计不重复得个数
  {=SUM(1/COUNTIF(条件区域，条件区域))}
  数组公式：ctrl+shift+enter
  注：COUNTIF(条件区域，条件区域)相当于统计出条件区域中每个单元格中元素出现的个数

• SUMIF(range,criteria,sum_range)
  (1) Range 为用于条件判断的单元格区域。
  (2) Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、”32”、”>32” 或 “apples”。
  (3) Sum_range 是需要求和的实际单元格。
  SUMIFS(SUM_range,criteria1,sum_range1,criteria2,sum_range2,…)
  (1) SUM_Range 为用于条件判断的单元格区域。
  (2) Criteria1 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、”32”、”>32” 或 “apples”。
  (3) range1 是需要求和的实际单元格。