excel学习09（笔记自用）

1甘特图

(1)二维条形图

1. 光标放置空白格，插入二维条形图
2. 输入数据来源
3. 调整轴名称：“逆序类别”
4. 调整日期轴起始日：- 查询日期代码，输入坐标轴最小值（CTRL+1 设置单元格格式–常规）
5. 调整日期间隔：坐标轴选项–单位

(2)完成百分比

6. 完成百分比
   1. 误差线
   2. 添加表格数据：完成百分比*天数
   3. 注意：点选刚才隐藏的数列

(3)图标修改完善

7. 填充误差线，更换颜色表进度，加大宽度
8. 外观大小宽度与任务进度重合
9. 保持图表风格一致。图表框线设为无，填充为无填充
10. 选择横/纵坐标轴-右键-选择新增主要格线-边框改为虚线
11. 选择隐藏的柱状图，设置数据系列格式-分类间距改为0，填充满格子
12. 选中柱状，右键-增加数据标签-双击即可修改内容

2布尔逻辑

(1)基础知识

IF函数判断 =IF(E3=1000,TRUE,FALSE)
布尔逻辑 =E3=1000
等于 = 不等于
AND(条件1,条件2,条件3) 条件全真为TRUE，否则为FALSE
OR(条件1,条件2,条件3) 条件全假为FALSE，否则为TRUE
应对较为复杂的逻辑判断时AND和OR可搭配使用
也可用IF函数为判断结果输出文字信息
例如：=IF(OR(C3=有,AND(D3=5,E31000)),可升等,条件不符)

(2)数据上色

布尔逻辑也可用于条件格式，通过逻辑判断结果对数据进行上色
“框选表格——条件格式——新建规则——使用公式确定要设置格式的单元格——布尔逻辑判断公式——根据结果设置自己需要的色彩标注”【需要对第一个单元格进行锁定】

(3)好兄弟[布尔逻辑和SUMPRODUCT搭配]

SUMPRODUCT(乘数1,乘数2)
求商品总价***：SUMPRODUCT(数量,单价) 就能先用数量乘以单价再加总，算出所有商品总价***
计算加权总成绩：SUMPRODUCT(分数,权重) 各科分数与权重相乘再加总，算出加权总成绩
计算加权平均成绩只需要在公示后***除以SUM(权重总数)***即可

布尔逻辑和SUMPRODUCT搭配
单条件
=SUMPRODUCT((C3:C9=有)1)
=SUMPRODUCT(–(C3:C9=有))
两个公式效果一样，计算过程为判断条件“有”是否成立且加总，**【TRUE=1 FALSE=0】*，加总即为符合条件的数据数量

两条件A且B
=SUMPRODUCT((C3:C9=C11)(D3:D9=D11))
两条件相乘，全真为11=1，否则为0，加总即为符合两个条件的数据数量
还可以根据需要酌情添加乘数

3动态阵列（office365的功能）

(1)套用表格格式

将数据套用表格格式：新增或删除资料时，公式的范围也会同步调整，省了修改的麻烦

(2)UNIQUE函数

UNIQUE函数：从表格中去除重复的数据，返回一列清单，也叫spilling（溢出）
=UNIQUE(整列)
【溢出】的周围会显示蓝色的细框，方便辨识溢出所包含的范围；如想要修改溢出范围内的公式，必须回到开头的第一个单元格，其他单元格的公式会显示为浅灰色，无法进行编辑；如果公式在溢出时遇到了阻碍，比如在溢出范围内随意输入文字，公式就会显示错误讯息，知道将该单元格内容搬移到别处；当在表格中新增资料时，溢出的范围也会自动调整

3.SUMIF函数

=SUMIF(条件范围，筛选条件，加总范围)
SUMIF函数不具备自动溢出的功能
如希望SUMIF函数也能像UNIQUE函数一样自动溢出，可以加入动态阵列的语法，即在【筛选条件】的后方加上#字号，公式就能参照到整个溢出范围。

4.FILTER函数：

可以根据设定的条件来筛选表格中的资料
=FILTER(资料范围,筛选条件,错误讯息)
FILTER函数也可以用来进行双重栏位的筛选：
=FILTER(资料范围,(筛选条件1)*( 筛选条件2),错误讯息)

5.SORT函数：

将资料进行排序（简体中文依据拼音）
=SORT(排序范围，排序列位，排序方式) 1代表递增，-1代表递减

柏拉图

1计算百分比

1. ALT+=:计算这一列数值总和或用公式：=C5/SUM($C$5:$C$12)【注意锁定总数据范围】
2. 累加百分比：左加上
   

2排序【由高到低】与插图

将一图标格式更改为折线

3最简单的柏拉图建立方法【2016版以后有】

1. 将鼠标放置标题栏【如业务员】ctrl+shift+向下键【从表格框选一列表格】，按CTRL键不放，再将鼠标放置标题栏【如销售金额】ctrl+shift+向下键
2. 点击插入–图表–插入统计图表–直方图
   

XLOOKUP

七大技能

xlookup(查询条件，查询条件范围，查询结果范围)
1.向左查询xlookup(查询条件，查询条件范围f4，查询结果范围f4)
2.可直接输入错误提示
xlookup(查询条件，查询条件范围f4，查询结果范围f4，“查无此人”)
3.近似比对(分层级-发奖金/评优良)
xlookup(查询条件，查询条件范围-层级，查询结果范围f4,不写错误提示,-1)金额
4.横向查询
xlookup(查询条件，查询条件范围f4，查询结果范围f4,-1)
-1近似比对
5.模糊查询，通配符+输入2使用万用字元
xlookup(李*，查询条件范围f4，查询结果范围f4,2)
6.逆向查询xlookup(查询条件，查询条件范围f4，查询结果范围f4,-1)
-1逆向查询。变更列的查询方向-由下而上
7.双向筛选。两个xlookup叠加
xlookup(查找条件，查找范围，xlookup(查找条件，查找范围，结果范围))
加速查询。资料验证，下拉筛选