办公软件-Excel基本操作-笔记

00-打印设置

打印时带顶端标题行

• 多面打印时设置每页都带顶端标题行
  1. 页面布局
  2. 打印标题
  3. 在顶端标题行处选择相应的标题区域
  4. 确定
  5. 文件-打印-右侧的打印预览处拖动鼠标，可看到打印效果，每页均有标题
     

00-单元格设置

简单的条件格式

1. 突出显示单元格规则：限定的是数据的范围+文本+日期
2. 最前最后规则：不指定范围，在求出集合里的百分比
3. 数据条：通过填充色来显示数据大小趋势
4. 色阶：通过不同色阶，显示数据大小的过渡色

• 利用条件格式中的数据条：显示数据的大小趋势
  

复杂的多重条件格式：利用公式设置条件格式

00-筛选

只复制筛选的内容，而非全部内容

• 怎样给单元格筛选
  1. 方法一：选择单元格-开始-排序和筛选-筛选
  2. 方法二：选中单元格，ctrl+shift+L
• 怎样只将筛选后的单元格复制至其他位置，而不是复制全部
  1. 选中区域后筛选，然后复制，即使筛选后，复制粘贴后仍是未筛选前的全部内容
  2. 解决：筛选-复制区域-ctrl+g即定位条件-选择可单元格-复制粘贴
     
     

筛选条件为数值的

筛选条件为文本

高级筛选-筛选出所有科目

高级筛选中的且条件与或条件

高级筛选-条件区域包含公式

00-排序

简单值排序

• 方法一

  1. 可添加多个条件，分别作为主关键字、次关键字
  2. 排序次序选择为升序即可。
  3. 还有降序、自定义序列可选择
     

• 方法二

  1. 将所有待排序的列从后向前依次排序，逐列排序
  2. 此方法可可突破自定义排序中可添加的关键字个数（即条件个数）少于实际需排序的个数的限制

单元格颜色排序

• 操作步骤
  1. 选中任一列待排序列中的单元格
  2. 自定排序、有多少种颜色就添加多少个条件，关键字为该列列头
  3. 排序的依据是单元格种颜色
  4. 排序次序选择相应的颜色，放在顶端而底部
     
     

文本排序

• 题目：表格中销售部门这列按照一部、二部、三部、四部、五部的顺序排序
  1. 选中该列或该列中的某个单元格，点击自定义序列
  2. 关键字为该列，排序依据为单元格值，次序选择自定义序列
  3. 自定义序列中在左侧选择新序列，右侧输入希望的次序，每个项用回车隔开
  4. 将新输入的序列添加到自定义序列库中即可
     
     

应用：修改工资表

• 题目一：将包含有N个人的工资表修改为N个人工资条
  1. 题目意思是每个人的工资条格式为：一行工资表头+一行实际工资数据
  2. 此操作在原表上操作
  3. 在原表末尾添加N-1行表头内容
  4. 在右侧另起一列，假设称为Y列
  5. 在Y列中，将原有行依次填充序列，从0至N
  6. 在Y列中，将新增行依次填充序列，从1.5，2.5，。。。至(N-1).5
  7. 将Y列按单元格值升序排序即可
     
     
     

00-分类汇总

简单的分类汇总：对一个字段分类汇总

应用：对多列分类汇总

• 题目：将不同地区的不同产品的成本数量金额分类汇总
  
  
  
  
  

应用：合并相同内容单元格

• 步骤
  1. 先对目标单元格排序
  2. 对目标单元格分类汇总，按该列分类，按该列汇总，方式为计数
  3. 将汇总出来的列选中空单元格：先选中-利用定位条件为空单元格-即可
  4. 合并单元格后，删除分类汇总，即可将A列中的汇总信息删除掉
  5. 复制合并后的单元格-选择性粘贴-只粘贴格式
  6. 删除A列，B列调整剧中格式，即可
     
     
     

00-数据有效性

指定单元格数据格式

• 基本操作步骤
  1. 选择要设置数据有效性的单元格
  2. 数据-数据工具-数据验证（旧版的是数据有效性）
  3. 设置值：可以将该单元格的值设为
     • 任何值
     • 整数
     • 小数
     • 日期
     • 时间
     • 序列
     • 文本长度
     • 自定义序列
       
• 将单元格设置下拉框
  • 在设置值处选择序列
  • 将要输入的具体文本依次输入，中间用英语的逗号隔开
  • 结果显示：类似于下拉框，在此单元格只能输入这值
    
    

保护单元格数据

半保护单元格数据

00-透视表

最基本的透视表使用

• 创建透视表：插入-数据透视表-默认为在新表创建
• 改为经典模式：在新表透视表区域处，右键-透视表选项-显示-勾选经典
  
  

组合透视表的基本使用

• 注意情况
  • 日期有空格：将其合并单元格，消除空格
  • 日期中有文本格式：统一格式
    

透视表应用：统计不同值区域内值的个数

对一列数据进行不同计算，汇总为多列

将已知列计算结果作为新增列字段数据

透视表应用：利用透视表筛选页生成多个工作表

切片器

• 切片器理解：其实就是用来做筛选的
  

00-公式与函数

常见的公式与函数技巧

01、利用定位条件(ctrl+g)将公式填充在不连续的空格。连续的空格可以直接拖拽。
	- 选择中所有的数据区域
	- ctrl+g，定位条件为空值，确定后即可选中不连续的空格
	- 将公式写在第1个空格，按ctrl+enter，即可将公式填充在所有空格

常见函数的语法

rank函数

语法：= rank(对谁排序，在哪个区域排序，0表示降序1表示升序)
注意：
- 第3项为空时，默认为按降序来排
- 第2项，区域一般要固定

iserror函数

用法：=if(iserror(A1/B1),0,A1/B1)
解释：用来判断公式A1/B1是否会发生错误，若发生错误，填写0，否则填充运算结果

and与or函数

用法：= and(条件1,条件2,条件3,...)
用法：= or(条件1,条件2,条件3,...)

count函数

用法：=count(区域)

countif与countifs函数语法

01、countif函数语法
用法：=countif(在哪个区域数,"条件")
举例：=countif(区域,">=60")	// 计算及格的科目数

02、countifs函数语法
语法：=countifs(条件区域1,条件1,条件区域1,条件1,。。。) 	// 成对出现

countif应用：查找某项是否在某区域中

countif应用：将不在某区域中的某项标为绿色

countif应用：利用数据有效性禁止输入重复数据

sumif与sumifs函数语法

01、sumif函数语法
语法：=sumif(查找区域,条件,求和区域)
解析：在查找区域里，看该区域内的数据是否满足指定的条件，对满足条件的项在求和区域里求和
注意：查找区域与求和区域可以相同，可以不同
返回：sumif和sumifs函数只能对数值进行求和，不可对文本等类型求和，返回的是数值

sumif应用：对多列数值求和

sumif与sumifs应用：进行多条件求和

• 利用连字符&，用sumif进行多条件求和
  
• 用sumifs，直接进行多条件求和
  

sumif应用：设置数据有效性

vlookup函数的语法

语法：= lookup(找谁即目标，查找区域，找到后返回查找区域的第几列数据，0或1)
解析：0表示精确匹配，1表示模糊匹配。模糊匹配只会找小于等于自己的最大值
	 vlookup只能通过查找左侧数据，返回查找列及其右侧的数据，不可返回其左侧数据
	 要获取左侧的数据，则要配合match,index函数，match查找功能，index引用功能
注意：目标的名字，必须在查找区域第1列
注意：目标的名字，只占查找区域第1列名字的部分，须加上连接符与通配符
	 = look(E1&"*",$B$1:$D$1,2,0)
	 如：目标为：电信，查找区域和1列有个记录名叫：电信公司，只能用通配符
1. 查找的目标为数字格式，而查找区域第1列格式为文本格式
	=vlookup(F1&"",查找区域,返回列,0)	// 将数字格式的目标改成文本格式
2. 查找的目标为文本格式，而查找区域第1列格式为数字格式
	=vlookup(F1+0,查找区域,返回列,0)	// 将文本格式的目标数字改为数字格式
3. 查找的目标、查找区域第1列的格式均为2种格式混杂 
   若用数值的方式查找会发生错误，则使用文本的方式查找，否则就用数值的方式查找
	=IF(ISERROR(VLOOKUP(F20+0,$A$18:$C$22,3,0)),
	VLOOKUP(F20&"",$A$18:$C$22,3,0),
	VLOOKUP(F20+0,$A$18:$C$22,3,0))
	

match、index应用：组合完成vlookup功能

vlookup,match应用：返回多列结果

常用日期函数

1. =year(日期)：可计算出年份，同理有month,day函数
2. =date(2000,10,11)可得到一个日期：2000/10/11
3. =date(year(日期),month(日期)+1,0)	// 计算指定日期当月最后一天
4. =datedif(开始日期，结束日期，"y") 
	- "y","m","d"分别表示年，月，日
	- "ym","md","yd"分别表示刨除年份算月份，刨除月份算天数，刨除年份算天数
5. =weeknum(日期,2)：默认以星期一当做一周的第一天，求出日期所属第几周
6. =weekday(日期,1)：求出该日期是星期几，1表示把星期天当做一周的第一天