Excel（3）

一、条件格式与公式

1. 条件格式

   条件格式： 如果数据怎么样，就给单元格设置什么格式，动态改变单元格格式，标注出特殊数据。

   数据范围：选中区域–开始选项卡–条件格式–突出显示单元格规则—规则–设置格式

   模糊匹配：选中区域–开始选项卡–条件格式–突出显示单元格规则–文本包含–设置格式

   清除条件格式：开始选项卡–条件格式–清除规则–清除所选单元格的规则

   查找重复值：选中区域–开始选项卡–条件格式–突出显示单元格规则—重复值 （空列中也可做相应设置，使得若输入重复值会有格式变化）

   项目规则选取：选中区域–开始选项卡–条件格式–项目选取规则

   色阶和数据条：色阶通过颜色过渡显示数据大小（温度），数据条通过条的长短显示数据大小。

   图标集：通过不同形状描述不同大小的数值。

   新建规则：选中区域–开始选项卡—条件格式—新建规则—样式：经典

   为错误值设置格式：选中区域–开始选项卡—条件格式—新建规则—样式：经典–只为包含以下内容的单元格设置格式–错误

2. 切片器（其实就是筛选器）

   为数据透视表中的数据制作数据条：

   a. 点中表中任一数据单元格–插入选项卡–数据透视表—确定—右键–数据透视表选项—显示–经典的数据透视表布局–字段按行、列放到行字段、列字段和值字段–随便选择行字段中的一个日期—右键—组合–步长月份

   b. 选中要统计的数据区域—开始选项卡–条件格式–数据条

   c. 点中数据透视表中任一单元格—插入选项卡–切片器（筛选分类按钮）【右上角清除筛选器或者选中切片器按delete键删掉】

   注意：切片器必须工作于.xlsx格式的EXCEL文件！

3. 定义多重条件的条件格式

   选中区域–开始选项卡—条件格式，每个条件做一次，条件覆盖区域不相互重合时。

   条件相互覆盖时，先做区域大的条件，后做的会覆盖先做的。

4. 使用公式定义条件格式

   条件格式不针对单元格本身内容而是根据其他单元格内容做判断时，需要用公式。

   将数量大于100的项目日期标记为红色背景：选中日期区域–开始选项卡—条件格式–新建规则–样式：经典–使用公式确定要设置格式的单元—公式为 =D2>100

   将数量大于100的项目整行标记为红色背景：选中整个数据区域–开始选项卡—条件格式–新建规则–样式：经典–使用公式确定要设置格式的单元—公式为 =$D2>100

   注意：写公式时注意单元格引用！

5. 修改条件格式

   选中已做的条件格式–开始选项卡—条件格式–管理规则–编辑规则

二、文本函数

文本函数：关心一个单元格中的字符串怎么样。

1. 截取文本

   =LEFT(截取哪个单元格,截取几位)

提取前三位： =LEFT(A3,3)

​ =RIGHT(截取哪个单元格,截取几位)

提取后四位： =RIGHT(E3,4)

​ =MID(截取哪个单元格,从第几位开始,截取几位)

提取中间三位： =MID(A3,4,3)

提取身份证性别位数字（身份证有15位也有18位）： =RIGHT(LEFT(B13,17),1)

1. 获取文本中的信息

   FIND函数：查找一个字符在字符串中是第几位 。当该字符有多个时，会返回第一个的位数。

   =FIND(找谁,在哪找,(从哪一位开始找))

提取邮箱@前的姓名： =LEFT(F2,FIND(“@”,F2)-1)

要想找到第二个@的位置： =FIND(”@”,H11,find(“@”,H11)+1) 从第一个@后面一位开始找

提取邮箱@后的域名： =MID(F2,FIND(“@”,F2)+1,FIND(“.”,F2)-FIND(“@”,F2)-1)

1. LEN和LENB

   求字符串的长度（即有几个字符）： =LEN(text)

=LEN(“张三”) 结果为2

​ 求字符串的字节长度（即有几个字节）： =LENB(text)

=LENB(“张三”) 结果为4

​ 汉字一个字符等于两个字节，汉字是双字节字符。字母数字特殊符号等都是单字节字符。

提取283元或458千克的单位（利用一个汉字字节长度比字符长度多1）： =RIGHT(A2,LENB(A2)-LEN(A2))

1. 关于身份证

   身份证地区判断：（提取身份证号前六位，在对应地区码表中找到所属地区）

=VLOOKUP(LEFT(B2,6)*1,地区码!$A:$B,2,0)

​ 【注意：文本和数字格式！通过文本（如身份证号）处理得到的结果肯定是文本，而地区码是数值（判断是不是数值可以求和验证一下，能相加就是数值），直接找会出错，要文本*1或—文本】

​ 身份证提取生日：

=DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2))

=TEXT(MID(B2,7,8),"0000-00-00")          TEXT得到的是文本格式，不是日期格式，所以要*1转换

​ 身份证判断性别：

=IF(MOD(RIGHT(LEFT(B2,17),1),2)=1,“男”,“女”)

​ 身份证验证真伪：

=IF(LOOKUP((LEFT(A1,1)*7+MID(A1,2,1)*9+MID(A1,3,1)*10+MID(A1,4,1)*5+MID(A1,5,1)*8+MID(A1,6,1)*4+MID(A1,7,1)*2+MID(A1,8,1)+MID(A1,9,1)*6+MID(A1,10,1)*3+MID(A1,11,1)*7+MID(A1,12,1)*9+MID(A1,13,1)*10+MID(A1,14,1)*5+MID(A1,15,1)*8+MID(A1,16,1)*4+MID(A1,17,1)*2)-ROUNDDOWN((LEFT(A1,1)*7+MID(A1,2,1)*9+MID(A1,3,1)*10+MID(A1,4,1)*5+MID(A1,5,1)*8+MID(A1,6,1)*4+MID(A1,7,1)*2+MID(A1,8,1)+MID(A1,9,1)*6+MID(A1,10,1)*3+MID(A1,11,1)*7+MID(A1,12,1)*9+MID(A1,13,1)*10+MID(A1,14,1)*5+MID(A1,15,1)*8+MID(A1,16,1)*4+MID(A1,17,1)*2)/11,0)*11,{0,1,2,3,4,5,6,7,8,9,10},{"1","0","x","9","8","7","6","5","4","3","2"})=RIGHT(A1,1),"OK","Wrong")

三、数学函数

一、认识函数

1. ROUND函数

   ROUND函数：四舍五入。

   =ROUND(待处理数字,要四舍五入的位数)

=ROUND(E2,2)

​ ROUNDUP函数：直接向上进位（计算工作量需要1.1个人工，直接进位算为2个）

​ =ROUNDUP(待处理数字,要保留的位数)

=ROUNDUP(H3,0)

​ ROUNDDOWN函数：直接舍掉（计算员工假期能休3.7天，直接舍掉算为3天）

​ =ROUNDDOWN(待处理数字,要保留的位数)

=ROUNDDOWN(H3,0)

​ INT函数：直接取整（处理负值时与ROUNDDOWN有区别：-2.5用INT为-3，用ROUNDDOWN为-2）

​ =INT(待处理数字)

=INT(H3)

1. MOD函数

   MOD函数：求余数。

   =MOD(除数,被除数)

   判断奇偶： =MOD(除数,2)

   得到小数部分： =MOD(除数,1)

   特殊的舍入：员工假期计算，舍为整数或几.5（ 节假日最小单位为半天）

=IF(MOD(C2,1)>=0.5,INT(C2)+0.5,INT(C2)) 或 =INT(C2*2)/2

1. ROW函数与COLUMN函数

   求单元格的行数： =ROW(单元格)

=ROW(A8)

​ 求单元格的列数： =COLUMN(单元格)

=COLUMN(C13)

​ 返回公式所在单元格自己的行数： =ROW()

​ 返回公式所在单元格自己的列数： =COLUMN()

​ ROW()和COLUMN()常用于判断当前单元格的位置。

1. 基于位置规律的引用

   INDEX函数：给其一个区域，找出一个东西。（INDEX类似于一个机械手抓东西，但是需要规则指导）

   列转为行：复制一列—选择性粘贴—转置。

列转为行（引用位置与行列有关）： =INDEX($A:$A,COLUMN()-2)

跳跃取值（间隔5放大5倍）： =INDEX($E:$E,ROW()*5-17)

一列分多列： =INDEX($A:$A,ROW()*3+COLUMN()-10)

​ 分析思路：先写需求，再找规律，最后调试。

四、LOOKUP函数和数组

1. 回顾统计函数SUMIF和SUMIFS

   =SUMIF(在哪里找,找什么,把什么求和)

=SUMIF($B:$B,I8,$E:$E)

​ =SUMIFS(把什么求和,第一个条件区域,第一个条件,第二个条件区域,第二个条件…)

=SUMIFS($E:$E,$B:$B,I15,$C:$C,J15)

1. 数组

   数组（矩阵）：一组值和一个值运算。数组公式需要{}包括，但是不是手敲上的。

   注意：数组公式写完后不能直接敲回车键，要按住control+shift+回车键会出现花括号！

   注意：选择数组的时候不能直接选A:A 这样的整列，应选择每个单元格即 A1：A10，同时区域要绝对引用！

   利用TRUE的值为1，FALSE的值为0

=sum（条件1*条件2*条件…*求和项）

{=SUM(($A$2:$A$22=K15)\ast ($B$2:$B$22=L15)\ast$E$2:$E$22)}

​ 条件判断要切换成显示出判断结果：按F9，退出按ESC。

​ SUMPRODUCT函数：相当于带了花括号{}结尾的SUM，可以代替control + shift + enter 的麻烦。

1. 使用LOOKUP

   LOOKUP和VLOOKUP的区别：LOOKUP无法精确匹配，只能模糊匹配，但这个缺点可以克服。

   利用LOOKUP函数虽然不能精确匹配，但会跳过错误值。所以利用0除以一个只有0和1的数组，生成结果中有错误值和非错误值0。要用1去找。

   =LOOKUP(找什么,在哪列找,返回哪列)

   注意：区域的绝对引用且不能选整列！

单条件： =LOOKUP(1,0/($A$2:$A$92=G4),$B$2:$B$92)

多条件： =LOOKUP(1,0/(($A$2:$A$13=I6)\ast ($B$2:$B$13=J6)),$D$2:$D$13)

1. 计算个税

相当于用MAX函数将括号里的东西算七次最后取最大。

=IF(F5<=3500,0,MAX((F5-3500)*$C$4:$C$10-$D$4:$D$10)） control＋shift＋enter 同时按

五、INDIRECT函数

1. 认识INDIRECT单元格引用

（1）INDIRECT函数的意义及语法（间接引用）

​ INDIRECT是先得到单元格，再进行翻译得到。

=INDIRECT(F13) F13中内容为a1，INDIRECT函数间接把单元格类似地址A1的内容翻译出来。

（2）INDIRECT函数与INDEX函数对比

例：取每组中的第二个数值

=INDEX($E:$E,ROW()*5-25) 取E列第5个、第10、第15…

=INDIRECT(“e”&(ROW()*5-25)) 取E5、E10、E15…单元格

（3）处理跨表

表示引用名称叫1月的表中的G2单元格： =‘1月’!G2

跨表引用每个表的G2内容（顺序位置一样）： =INDIRECT(A4&“!g2”)

跨表引用每个表的张三的业绩（顺序位置不同）： =VLOOKUP(“张三”,INDIRECT(A4&“!A:G”),7,0)

横纵拖拽单元格（注意混合引用）： =VLOOKUP(B$2,INDIRECT($A3&“!$A:$G”),7,0)

（4）跨表引用时的单引号问题

​ 表名不规范（有特殊符号）时，不用单引号（英文单引号）将表名括起，INDIRECT可能会引用出错。

=INDIRECT(“'”&A4&“'!g2”)

1. INDIRECT名称引用

（1）为区域定义名称

​ 为区域定义名称：选中B2：B13的区域–公式选项卡–定义名称—张三

=SUM(张三) 可以求得张三区域的和

=SUM(INDIRECT(G3)) 可以求得张三区域的和（ G3单元格内容为张三）

（2）制作二级下拉列表

​ a. 分别选中各省下的城市区域，定义名称吉林省、江苏省、广东省…

​ b. 选中F列（省份列）—数据选项卡–数据有验证—允许：序列—来源： A3:C3（设置好省份列记得先在表格上写一个省份，不要空着）

​ c. 选中G列（二级城市列）–数据选项卡–数据有验证—允许：序列—来源： =INDIRECT(F1)

​ （选F1是因为要看选中G列时显示为白色的第一个单元格，白色单元格就是当前单元格）

六、 图表基础

1. 认识图表中的元素

（1）Excel中插入的图片、图表后设置成跟着单元格一起隐藏

​ 选中图片右键–设置图片格式—属性–随单元格改变位置和大小

（2）认识图表

​ 插入图表：选中数据区域—插入选项卡—柱形图

​ 删除图表：DELETE删除图表

​ 布局：选中图表–图标设计–添加图表元素

​ a. 图表标题：选中图表标题—右键–设置图表标题格式。

​ 选中图表标题，在编辑栏输入=B1可使标题自动随B1单元格内容变动而变。

​ b. 坐标轴标题：主要、次要。

​ c. 图例：常放在上方。

​ d. 数据标签：描述柱形图每柱的准确数值/类别名称/系列名称。

​ e. 模拟运算表：即数据。

​ f. 坐标轴：主要、次要。（核心）

​ 选中横坐标轴—右键–设置坐标轴格式–坐标轴标签（横坐标文字）–主要刻度线类型（坐标轴上的小线段）

		   选中纵坐标轴—右键--设置坐标轴格式—边界（最小值最大值）--单位（大）--设定坐标轴标签排列顺序（逆序类别）

​ g. 网格线：主要网格线是根据坐标轴主要刻度画的。

1. 创建并美化柱形图

（1）主次坐标轴

​ 双坐标轴：将很大的一组数据和很小的一组数据同时呈现在一个图中时，需要设置双坐标轴。

​ a. 点击次坐标轴柱形—右键–设置数据系列格式–选择次坐标轴

​ b. 点击次坐标轴柱形—右键–更改图表类型–折线图

​ c. 点击次坐标轴折线—右键–设置数据系列格式—填充—标记–数据标记选项–自动（数据标记选项是折线折点的那个小方块）

​ d. 把柱形图和折线图上下分开：点击纵坐标轴—右键–设置坐标轴格式–更改坐标值（最大值、最小值）来调节柱形高度

​ e. 调整网格线：点击纵坐标轴—右键–设置坐标轴格式–更改单位(大、小）)来调整网格线条数

​ f. 隐藏纵坐标轴：：点击纵坐标轴—右键–设置坐标轴格式—主刻度线类型：无、次刻度线类型：无、标签位置：无、线条：无线条

​ g. 网格线设为虚线：点击网格线—右键–设置主要网格线格式—短划线类型：虚线

​ h. 将折线改为绿色：点击折线—右键–设置数据系列格式—线条颜色：橄榄色—标记填充：纯色填充、颜色：橄榄色、边框：实线、颜色：橄榄色

​ i. 在图上显示数值：选中折线/柱形–图表设计–添加图表元素—数据标签—选中数据标签—右键–设置数据标签格式

（2）制作计划与实际对比图

​ 次坐标轴会挡在主坐标轴前面。

​ a. 计划完成放在次坐标轴上：注意更改次坐标轴刻度和主坐标轴刻度相同。

​ b. 次坐标轴柱形：设置为无填充，边框颜色实线红色加粗。

​ c. 修改纵坐标轴单位：设置坐标轴格式–显示单位

1. 图表通用设置

（1）利用复制粘贴更改数据系列显示样式

​ 将柱形图图长方形改成三角形：先画一个三角形（插入选项卡—形状—三角形—右键设置形状格式）–选中三角形—复制（Command+C）–选中图表中柱形—粘贴（Command+V）

​ 将柱形图长方形改成多个爱心：先画一个心形（插入选项卡—形状—心形—右键设置形状格式）–选中心形—复制（Command+C）–选中图表中柱形—粘贴（Command+V）—右键–设置数据系列格式—填充–层叠

​ 使爱心分布空一点（调节层叠图形间距）：插入选项卡—形状–矩形（矩形覆盖心形，比心形大一点且让心形右侧留有空白）–右键矩形形状格式–无填充色、无线条—选中心形和矩形（若选不中：查找与选择—选择对象–直接框选对象–右键组合）—复制（Command+C）–选中图表中柱形—粘贴（Command+V）

（2）巧用别人的图表模板

​ 保存图表模板：选中图表—右键–另存为模板

​ 使用模板：选中数据–左上角插入—图表–模板

七、经典动态图表实现原理

1. 动态图表实现原理

（1）理解图表中的数据系列

​ 点击图标—右键—选择数据源

​ 一个系列是一行或者一列数据，手工创建系列要一列一列的选取数据。

（2）制作复选框（勾上复选框时数据图表出现，不勾选时消失）

​ a. 点击上方Excel—偏好设置—视图—勾选“开发工具”选项卡

​ b. 开发工具选项卡—复选框—创建出复选框—右键点击可以选中复选框拖动

​ c. 在复选框上右键–双击–替换文字

​ d. 复选框控件和EXCEL关联起来：.在复选框上右键—设置控件格式—控制–单元格连接

​ e. 写公式 =IF(TRUE单元格,勾选时应显示的一列单元格数据区域,任一空白列)

=IF($I$3,$B$2:$B$13,$F$2:$F$13)

​ 注意：公式里所有单元格必须都是绝对引用！

​ f. 复制写好的公式—公式选项卡–定义名称—名称：彩盒、在引用位置处粘贴公式

​ 注意：定义名称前不能点中表格，应该点表格外的任意单元格！

​ g. 点击一个空白单元格—插入选项卡—折线图—右键–选择数据—新建系列–名称：彩盒、Y值：=sheet1!彩盒

​ 注意：写Y值时前面一定要写表名（sheet1!）

​ h. 固定纵坐标：两个表都显示时–点击纵坐标轴—右键–设置坐标轴格式—输入最大值、最小值

1. OFFSET函数

   透视表中的OFFSET：在数据透视表中，原数据有更新增加行数时，做好的数据透视表不会自动进行相应的数据更新，此时可以用OFFSET函数来取一个动态的数据区域。

   =OFFSET(以某个单元格为基准,下移n行,右移n列,取n行,取n列)

   计算A列有多少行： =COUNTA($A:$A)

=OFFSET($A$1,0,0,COUNTA($A:$A),11)

​ 复制此公式—公式选项卡—定义名称—名称：数据区域、将公式粘贴到选择单元格区域

​ 插入选项卡–数据透视表—选择表或区域：数据区域、选择要放置数据透视表的位置：新工作表

1. 利用OFFSET制作动态图表

（1）不管原来数据有多少，图表只反映最后十行的数据

​ a. 写好公式 =OFFSET($B$1,COUNTA($B:$B)-10,0,10,1)

​ b. 复制此公式—公式选项卡—定义名称—名称：成交、将公式粘贴到选择单元格区域

​ c. 点击空白的单元格–插入选项卡—折线图（创建一个空白折线图）–右键—选择数据—添加系列–名称：成交量、Y值：=sheet1!成交量

​ d. 也要改X轴，方法与上类似，公式为 =OFFSET($A$1,COUNTA($A:$A)-10,0,10,1) —点击折线图—右键—选择数据—水平（分类）轴标签： =sheet1!日期

（2）做有两个滚动条的动态表，一个控制数据往下取，一个控制取多少行

​ a. 开发工具—滚动条（建两个滚动控件）—右键–设置控件格式—控制—最小值：1、单元格链接分别为：D2、G2这两个空白单元格

​ b. 利用OFFSET函数确定区域： =OFFSET($B$1,$D$2,0,$G$2,1)

​ c. 复制写好的公式–公式选项卡—定义名称—名称：成交量、选择单元格区域：把公式复制进去

​ d. 点击空单元格—插入空白柱形图—右键—选择数据—添加系列–系列名称：成交量、系列值：=sheet1!成交量

​ e. 也要改X轴，方法与上类似，公式为 =OFFSET($A$1,$D$2,0,$G$2,1) —点击柱形图—右键—选择数据—水平（分类）轴标签： =sheet1!日期

八、制作甘特图和旋风图

1. 制作旋风图（双向条形图）

例：历年出口与内销对比图

（1）设置主次坐标轴

​ a. 选中整个数据区域—插入选项卡–条形图–簇状条形图

​ b. 点击一组条形–设置数据系列格式–次坐标

（2）设置条形图方向，使条形图往两边长，中间为0

​ a. 选中次要坐标轴右键–设置坐标轴格式–逆序刻度值

​ b. 主要和次要坐标轴分别右键–设置坐标轴格式–最小值-1，最大值1

​ c. 点中次要坐标轴—DELETE–删除次坐标轴

（3）美化图表

​ a. 把纵坐标标签移出图外：点击纵坐标轴右键–设置坐标轴格式—标签–标签位置：高或低

​ b. 网格线：点击主要坐标轴右键–设置坐标轴格式—单位—大：0.25–点中网格线，按DELETE键删除

​ c. 美化条形图：分别点中红色条形和蓝色条形–右键–设置数据系列格式—间隙宽度：100%、填充：纯色填充、阴影：预设

​ d. 在条形图上加相应数值：点中柱形–图标设计–添加图表元素—数据标签–居中

​ e. 主坐标轴左右都显示为100%：选中主坐标轴–右键–设置坐标轴格式—数字–自定义：0%;0%（表示正值和负值都显示0%）

​ f. 图片背景：选中图片—格式–设置对象格式–艺术效果–虚化—再选中图片–复制–选中图表区（表外框内的空白部分）—右键–设置图表区格式—填充：图片或纹理填充–插入图片来自：剪贴板–选中图表数据区域—右键–设置绘图区格式—无填充

​ PPT中图片不适合做背景时：点中背景图片—格式–艺术效果–（不要直接点模糊，其实模糊程度还不够）艺术效果选项–模糊辐射100%

​ 屏幕锁死：文件—选项–显示：调整禁用硬件图像加速

1. 制作甘特图

条形并不是真的悬空放，而是利用开始日期把条推出去，条的长度表示天数。

（1）制作普通甘特图

​ a. 选中数据区域—插入选项卡–条形图–堆积条形图

​ b. 美化条形图：选中大图表—图标设计–样式模板，选中不要的数据部分—右键–设置数据系列格式—填充：无填充、边框：无线条、阴影：无阴影

​ c. 选中横坐标轴—右键–设置坐标轴格式—最小值：41760（5月1日），最大值：41790（5月31日）【右键–设置单元格格式–得出两个日期所代表的实际数字】

​ d. 删去年份：日期右键–设置坐标轴格式—数字–找到只保留月、日的格式

​ e. 选中条形—右键–设置数据系列格式—间隙宽度：10%（留有阴影位置）

​ f. 点中纵坐标轴—右键–设置坐标轴格式–逆序类别

​ g. 刻度线改为虚线：选中刻度线—右键–设置网格线格式—短划线类型：虚线

（2）制作动态甘特图（用滚动条、函数控制甘特图日期变化）

​ a. 拆分数据：未完成（F列）、已完成（E列）

​ B11为今天的日期 =TODAY(）

E列 已完成天数      =IF($B$11<B2,0,IF($B$11>B2+C2,C2,$B$11-B2))

F列 未完成天数      =C2-E2

​ b. 同时选中数据区A1:B9和E1:F9（按住command键选）–插入堆积条形图

​ c. 把日期柱形隐藏，无填充无边框无阴影；调节最小值、最大值使剩余柱形充满坐标轴；更改日期刻度无年份；调节条形间距；竖直轴改为逆序

​ d. 添加滚动条：开发工具—滚动条—右键–设置对象格式—（最小值：41760、最大值：41790、单元格链接到B11（日期），会出现错误：滚动条最大只能设置30000）最小值：0、最大值：30、单元格链接到C11，B11=41760+C11

​ e. 跟随单元格而变的文本框：插入选项卡—文本–文本框–点中文本框边框，在上方编辑栏中写=$B$11，从而使文本框链接到单元格，显示相应日期

九、饼图美化与PPT图表

1. 双坐标柱形图补充

（1）主次坐标设置

​ 选中要设置的柱形图—右键–设置数据系列格式–次坐标轴

（2）主次坐标柱形避让

​ 方法一：次坐标柱形可以改成折线图

​ 方法二：把前面的次坐标柱形图改成边框无填充

​ 方法三（左右分开两个柱形）：选中任意柱形–右键–选择数据–添加系列，系列名称任意，系列值为0，重复再添加这样的系列—选中柱形—右键–设置数据系列格式–调整系列重叠和间隙宽度

1. 饼图美化

（1）饼图美化常见设置

​ 设置厚薄：选中饼图—右键–三维旋转–取消勾选自动缩放—调整高度（通常为30或40）

​ 增加质感：选中饼图—右键–设置数据系列格式—三维格式—顶部棱台（选择一种效果）—阴影—预设：居中偏移

​ 数据标签：选中饼图—图表设计–添加图表元素—数据标签–居中

（2）制作双层饼图

​ 想把谁放在前面，就先做谁

​ a. 选中要放在前面的数据区（B2:C10,没有包括前面的科目那列）—插入选项卡—平面饼图–选中饼图—右键–选择数据—添加系列—Y值：先删去默认出现的={1}，再选择第二部分数据（只选数字，不选其他的科目什么的）

​ b. 分层：选中上面的饼图—右键–设置数据系列格式–设为次坐标轴

​ c. 分大小：选中上面的饼图–向外拉小上面整个饼图–再一个一个的把里面的小饼图拼到圆心

​ d. 数据标签（要分类轴的名字和百分比）：选中前面小饼图—图表设计–添加图表元素—数据标签–其他数据标签选项—勾选类别名称、百分比—标签位置：居中–选中图表右键—选择数据—系列2—修改水平（分类）轴标签–选中后面大饼图—图表设计–添加图表元素—数据标签–其他数据标签选项—勾选类别名称、百分比—标签位置：居中

​ 注意：两层以上用环形图，最里面那层用饼图，其他用环形图一层一层套！

1. EXCEL与PPT

（1）PPT默认配色方案会影响复制过来的图表颜色

​ 更改配色方案：设计选项卡–颜色

​ 解决：图表粘贴到PPT中后—点击右下角粘贴选项–保留原格式

（2）图表更新

​ 手动刷新：开始选项卡–左上角粘贴–保留原格式和链接数据–图表设计–刷新数据

​ 弹出窗口询问是否刷新：开始选项卡–左上角粘贴–选择性粘贴–粘贴为Excel图表对象

（3）动画设置

​ 可以对图表的各个元素进行动画设置。

​ 柱形图长出来的动画：动画标签–进入：擦除—动画窗格—图表动画—组合图形：按系列/按分类—取消勾选通过绘制图表背景启动动画效果

​ 按系列中的元素：按系列，一个系列一个系列来动画

​ 按分类中的元素：按类别，一个类别一个类别来动画

十、宏表函数

宏表函数：利用宏表函数可以获取信息，不能直接在excel单元格中直接写宏表函数。

1. GET.CELL函数

   GET.CELL函数：获取单元格信息。

   =GET.CELL(获取单元格什么信息,获取哪个单元格信息)

   获取单元格的文字颜色： =GET.CELL(24,C2)

   获取单元格的背景颜色： =GET.CELL(63,A2)

获取单元格的背景颜色步骤：点击单元格B2–公式选项卡–定义名称—名称：计算颜色、选择单元格区域：=GET.CELL(63,A2) — 在B2单元格中写 =计算颜色

​ 获取单元格中的公式： =GET.CELL(6,D2)

​ 获取单元格中的公式： =FORMULATEXT(D4)

1. GET.WORKBOOK函数

   GET.WORKBOOK函数：获取当前工作簿信息。

   获取工作簿中所有表的名字：=GET.WORKBOOK(1)

获取工作簿中所有表的名字步骤：点击单元格A1–公式选项卡–定义名称—名称：工作表名、选择单元格区域：=GET.WORKBOOK(1) — （若在A1单元格中写=工作表名，此时不可下拉，下拉会重复同一张表的信息）在A1单元格中写=INDEX(工作表名,1)，取第一张表的名字–在A1单元格中写=INDEX(工作表名,row())，下拉得到所有工作表名。

​ 做超链接： =HYPERLINK(地址,(显示的名字))

=HYPERLINK(“http://www.baidu.com”,“百度”)

=HYPERLINK(INDEX(工作表名,ROW())&“!a1”)

​ 注意：超链接必须指向某个单元格，文档名!a1

1. EVALUATE函数

   SUBSTITUTE函数：替换函数。

   =SUBSTITUTE(单元格,“要被替换的符号”,“替换它符号”,(替换第几个符号))

=SUBSTITUTE(A16," “,”@",3)

=SUBSTITUTE(A9,“,”,“+”)

​ EVALUATE函数：运算，不能直接在excel单元格中直接写。

​ =EVALUATE(formula_text)

=EVALUATE(SUBSTITUTE(EVALUATE!A9,“,”,“+”))

例：一个单元格中数据为90,88,95求和。

方法一：点中B9单元格—公式选项卡—定义名称—名称：计算1、选择单元格区域：=EVALUATE(SUBSTITUTE(EVALUATE!A9,“,”,“+”)) --点中B9单元格—填写 =计算1

方法二：点中B9单元格—公式选项卡—定义名称—名称：计算2、选择单元格区域：=EVALUATE(“{”&A9&“}”) —点中B9单元格—填写 =SUM(计算2)

例：一个单元格中数据为11 12 113 154 15 16，提取第四段数据。

公式选项卡–定义名称—名称：数据3、选择单元格区域 =EVALUATE(”{“&SUBSTITUTE(A16," “,”;"）&”}“) —点中B16单元格—填写 =INDEX(数据3,4)

1. REFTEXT(ACTIVE.CELL())

   ACTIVE.CELL() ：得到活动单元格。

   REFTEXT() ：得到当前活动单元格的地址。

   按住F9键点击数据查看效果。