Excel高级使用技巧

1、编辑技巧

1 编辑技巧

（1） 分数的输入

如果直接输入“1/5"，系统会将其变为“1月5日"，解决办法是：先输入“0"，然后输入空格，再输入分数“1/5"。

（2） 序列“001"的输入

如果直接输入“001"，系统会自动判断001为数据1，解决办法是：首先输入“'"（西文单引号），然后输入“001"。

（3） 日期的输入　

如果要输入“4月5日"，直接输入“4/5"，再敲回车就行了。如果要输入当前日期，按一下“Ctrl+；"键。

（4） 填充条纹如果想在工作簿中加入漂亮的横条纹，可以利用对齐方式中的填充功能。先在一单元格内填入“*"或“~"等符号，然后单击此单元格，向右拖动鼠标，选中横向若干单元格，单击“格式"菜单，选中“单元格"命令，在弹出的“单元格格式"菜单中，选择“对齐"选项卡，在水平对齐下拉列表中选择“填充"，单击“确定"按钮（如图1）。

图１

（5） 多张工作表中输入相同的内容

几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2......来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据会接着出现在选中的其它工作表内。

（6） 不连续单元格填充同一数据

（图和内容无关）

选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。在编辑区中输入数据，然后按住Ctrl键，同时敲一下回车，在所有选中的单元格中都出现了这一数据。

（7） 在单元格中显示公式

如果工作表中的数据多数是由公式生成的，想要快速知道每个单元格中的公式形式，以便编辑修改，可以这样做：用鼠标左键单击“工具"菜单，选取“选项"命令，出现“选项"对话框，单击“视图"选项卡，接着设置“窗口选项"栏下的“公式"项有效，单击“确定"按钮（如图2）。这时每个单元格中的分工就显示出来了。如果想恢复公式计算结果的显示，就再设置“窗口选项"栏下的“公式"项失效即可。

图２

（8） 利用Ctrl＋*选取文本

如果一个工作表中有很多数据表格时，可以通过选定表格中某个单元格，然后按下Ctrl＋*键可选定整个表格。Ctrl＋*选定的区域为：根据选定单元格向四周辐射所涉及到的有数据单元格的最大区域。这样我们可以方便准确地选取数据表格，并能有效避免使用拖动鼠标方法选取较大单元格区域时屏幕的乱滚现象。

（9） 快速清除单元格的内容

如果要删除内容的单元格中的内容和它的格式和批注，就不能简单地应用选定该单元格，然后按Delete键的方法了。要彻底清除单元格,可用以下方法：选定想要清除的单元格或单元格范围；单击“编辑"菜单中“清除"项中的“全部"命令，这些单元格就恢复了本来面目。

2、单元格内容的合并

根据需要，有时想把B列与C列的内容进行合并，如果行数较少，可以直接用“剪切"和“粘贴"来完成操作，但如果有几万行，就不能这样办了。

解决办法是：在C行后插入一个空列（如果D列没有内容，就直接在D列操作），在Ｄ1中输入“=B1&C1"，D1列的内容就是B、C两列的和了。选中D1单元格，用鼠标指向单元格右下角的小方块“■"，当光标变成"＋"后，按住鼠标拖动光标向下拖到要合并的结尾行处，就完成了B列和C列的合并。这时先不要忙着把B列和C列删除，先要把D列的结果复制一下，再用“选择性粘贴"命令，将数据粘贴到一个空列上。这时再删掉B、C、D列的数据。

下面是一个“&"实际应用的例子。用AutoCAD绘图时，有人喜欢在EXCEL中存储坐标点，在绘制曲线时调用这些参数。存放数据格式为“x，y"的形式，首先在Excel中输入坐标值，将x坐标值放入A列，y坐标值放入到B列，然后利用“&"将A列和B列合并成C列，在C1中输入：=A1&"，"&B1，此时C1中的数据形式就符合要求了，再用鼠标向下拖动C1单元格，完成对A列和B列的所有内容的合并（如图3-4）。

合并不同单元格的内容，还有一种方法是利用CONCATENATE函数，此函数的作用是将若干文字串合并到一个字串中，具体操作为“=CONCATENATE（B1,C1）"。比如，假设在某一河流生态调查工作表中，B2包含“物种"、B3包含“河鳟鱼"，B7包含总数45，那么： 输入“=CONCATENATE（"本次河流生态调查结果："，B2，""，B3，"为"，B7，"条/公里。"）" 计算结果为：本次河流生态调查结果：河鳟鱼物种为45条/公里。

3、条件显示

我们知道，利用If函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生成绩时，希望输入60以下的分数时，能显示为“不及格"；输入60以上的分数时，显示为“及格"。这样的效果，利用IF函数可以很方便地实现。 假设成绩在A2单元格中，判断结果在A3单元格中。那么在A3单元格中输入公式： =if（A2<60，“不及格"，“及格"） 同时，在IF函数中还可以嵌套IF函数或其它函数。

例如，如果输入： =if（A2<60，“不及格"，if（A2<=90，“及格"，“优秀"）） 就把成绩分成了三个等级。　

如果输入　=if（A2<60，“差"，if（A2<=70，“中"，if（A2<90，“良"，“优"））） 就把成绩分为了四个等级。 　　

再比如，公式： =if（SUM（A1：A5>0，SUM（A1：A5），0） 此式就利用了嵌套函数，意思是，当A1至A5的和大于0时，返回这个值，如果小于0，那么就返回0。 还有一点要提醒你注意：以上的符号均为半角，而且IF与括号之间也不能有空格。

4、自定义格式

Excel中预设了很多有用的数据格式，基本能够满足使用的要求，但对一些特殊的要求，如强调显示某些重要数据或信息、设置显示条件等，就要使用自定义格式功能来完成。 Excel的自定义格式使用下面的通用模型：正数格式，负数格式，零格式，文本格式，在这个通用模型中，包含三个数字段和一个文本段：大于零的数据使用正数格式；小于零的数据使用负数格式；等于零的数据使用零格式；输入单元格的正文使用文本格式。 我们还可以通过使用条件测试，添加描述文本和使用颜色来扩展自定义格式通用模型的应用。

（1）使用颜色 　要在自定义格式的某个段中设置颜色，只需在该段中增加用方括号括住的颜色名或颜色编号。Excel识别的颜色名为：[黑色]、[红色]、[白色]、[蓝色]、[绿色]、[青色]和[洋红]。Excel也识别按[颜色X]指定的颜色，其中X是1至56之间的数字，代表56种颜色.

（2）添加描述文本 　要在输入数字数据之后自动添加文本，使用自定义格式为：＂文本内容＂@；要在输入数字数据之前自动添加文本，使用自定义格式为：@＂文本内容＂。@符号的位置决定了Excel输入的数字数据相对于添加文本的位置。

（3）创建条件格式 　可以使用六种逻辑符号来设计一个条件格式：>（大于）、>=（大于等于）、<（小于）、<=（小于等于）、=（等于）、<>（不等于），如果你觉得这些符号不好记，就干脆使用“>"或“>="号来表示。

由于自定义格式中最多只有3个数字段，Excel规定最多只能在前两个数字段中包括2个条件测试，满足某个测试条件的数字使用相应段中指定的格式，其余数字使用第3段格式。如果仅包含一个条件测试，则要根据不同的情况来具体分析。

自定义格式的通用模型相当于下式：[>；0]正数格式；[<；0]负数格式；零格式；文本格式。

下面给出一个例子：选中一列，然后单击“格式"菜单中的“单元格"命令，在弹出的对话框中选择“数字"选项卡，在“分类"列表中选择“自定义"，然后在“类型"文本框中输入“"正数:"($#,##0.00);"负数:"($ #,##0.00);"零";"文本:"@"，单击“确定"按钮，完成格式设置。这时如果我们输入“12"，就会在单元格中显示“正数：（$12.00）"，如果输入“-0.3"，就会在单元格中显示“负数：（$0.30）"，如果输入“0"，就会在单元格中显示“零"，如果输入文本“this is a book"，就会在单元格中显示“文本：this is a book"。 如果改变自定义格式的内容，“[红色]"正数:"($#,##0.00);[蓝色]"负数:"($ #,##0.00);[黄色]"零";"文本:"@"，那么正数、负数、零将显示为不同的颜色。如果输入“[Blue];[Red];[Yellow];[Green]"，那么正数、负数、零和文本将分别显示上面的颜色。

再举一个例子，假设正在进行帐目的结算，想要用蓝色显示结余超过$50，000的帐目，负数值用红色显示在括号中，其余的值用缺省颜色显示，可以创建如下的格式： “[蓝色][>50000] $#,##0.00_);[红色][<0]( $#,##0.00); $#,##0.00_)" 使用条件运算符也可以作为缩放数值的强有力的辅助方式，例如，如果所在单位生产几种产品，每个产品中只要几克某化合物，而一天生产几千个此产品，那么在编制使用预算时，需要从克转为千克、吨，这时可以定义下面的格式： “[>999999]#,##0,,_m"吨"";[>999]##,_k_m"千克";#_k"克"" 可以看到，使用条件格式，千分符和均匀间隔指示符的组合，不用增加公式的数目就可以改进工作表的可读性和效率。

另外，我们还可以运用自定义格式来达到隐藏输入数据的目的，比如格式＂；＃＃；0＂只显示负数和零，输入的正数则不显示；格式“；；；"则隐藏所有的输入值。 自定义格式只改变数据的显示外观，并不改变数据的值，也就是说不影响数据的计算。灵活运用好自定义格式功能，将会给实际工作带来很大的方便。

5、绘制函数图象

做教学工作的朋友们一定会遇到画函数曲线的问题吧！如果想快速准确地绘制一条函数曲线，可以借助EXCEL的图表功能，它能使你画的曲线既标准又漂亮。你一定会问，是不是很难学呀？其实这一点儿也不难，可以说非常简便，不信你就跟我试一试。

以绘制y=|lg（6+x^3）|的曲线为例，其方法如下： 在某张空白的工作表中，先输入函数的自变量：在A列的A1格输入"X="，表明这是自变量，再在A列的A2及以后的格内逐次从小到大输入自变量的各个值；实际输入的时候，通常应用等差数列输入法，先输入前二个值，定出自变量中数与数之间的步长，然后选中A2和A3两个单元格，使这二项变成一个带黑色边框的矩形，再用鼠标指向这黑色矩形的右下角的小方块“■"，当光标变成"＋"后，按住鼠标拖动光标到适当的位置，就完成自变量的输入。

输入函数式：在B列的B1格输入函数式的一般书面表达形式，y=|lg（6+x^3）|；在B2格输入“=ABS（LOG10（6+A2^3））"，B2格内马上得出了计算的结果。这时，再选中B2格，让光标指向B2矩形右下角的“■"，当光标变成"＋"时按住光标沿B列拖动到适当的位置即完成函数值的计算。

绘制曲线：点击工具栏上的“图表向导"按钮，选择“X，Y散点图"（如图7），然后在出现的“X，Y散点图"类型中选择“无数据点平滑线散点图"；此时可察看即将绘制的函数图像，发现并不是我们所要的函数曲线，单击“下一步"按钮，选中“数据产生在列"项，给出数据区域，这时曲线就在我们面前了

需要注意：如何确定自变量的初始值，数据点之间的步长是多少，这是要根据函数的具体特点来判断，这也是对使用者能力的检验。如果想很快查到函数的极值或看出其发展趋势，给出的数据点也不一定非得是等差的，可以根据需要任意给定。

从简单的三角函数到复杂的对数、指数函数，都可以用EXCEL画出曲线。如果用得到，你还可以利用EXCEL来完成行列式、矩阵的各种计算，进行简单的积分运算，利用迭代求函数值（如x^2=x^7+4，可用迭代方法求x值），等等，凡是涉及计算方面的事，找EXCEL来帮忙，它一定会给你一个满意的答案。

6、自定义函数

虽然Excel中已有大量的内置函数，但有时可能还会碰到一些计算无函数可用的情况。假如某公司采用一个特殊的数学公式计算产品购买者的折扣，如果有一个函数来计算岂不更方便？下面就说一下如何创建这样的自定义函数。

自定义函数，也叫用户定义函数，是Excel最富有创意和吸引力的功能之一，下面我们在Visual Basic模块中创建一个函数。 在下面的例子中，我们要给每个人的金额乘一个系数，如果是上班时的工作餐，就打六折；如果是加班时的工作餐，就打五折；如果是休息日来就餐，就打九折。首先打开“工具"菜单，单击“宏"命令中的“Visual Basic编辑器"，进入Visual Basic编辑环境，在“工程－VBAobject"栏中的当前表上单击鼠标右键�...

第一步：整理数据

最原始的数据只有X轴和Y轴两列；X次轴和Y次轴3列数据是后加的（计算得出Y次轴两列分别是Y轴的各数据占比和累计占比）；

第二步：先以X轴和Y轴两列数据画出柱形图，再将Y次轴累计百分比添加进图表中，如下图所示，那个柱形图很矮的就是Y次轴的累计百分比，点中这个矮的柱形图，右击，选择设置系列格式，点击次坐标轴，接下来右击刚刚改的柱形图，选择更改图标类型，改成折线图。

第三步：将主坐标轴的柱形图间距调为0，右击柱形图，选择设置系列格式，分类间距调为0；

第四步：选择“布局”中的“坐标轴”选项，选择次要横坐标轴，如下图所示：

至此，图形初步完成。

第六步：主纵坐标要和次纵坐标对应，纵坐标是累计百分比，因此主纵坐标要用累计数量对应，算出Y轴数值总和250，因此主纵坐标最大值设为250，次纵坐标其实最大值只有100%，为了便于分网格线，数据5等分（只要主，纵等分份数一样，6份，7份，10份……，都可以）

第七步：调整次横坐标，选定次横坐标，右击，选择“设置坐标轴格式”，选择“在刻度线上”；

第八步：完善，添加标题，调整位置，相信大家都会，我就不再赘述了。最后，祝大家工作顺心。

图/文 | 安伟星

直方图是用于展示数据分布情况的图表，经常用于数据统计中。

它可清晰地展示出数据的分类情况和各类别之间的差异，为分析和判断数据提供依据。

01创建直方图

创建直方图，只用一列数据即可，直方图的分类标签是根据数值的大小进行自动分组。比如对一组测试数据，我们想要考察测试分数的分布情况。

①选择数据，选中上图所示的数据区域；

②单击“插入”>“插入统计信息图表”>“直方图”。

默认生成的直方图是个样子的。到这里，一定会有几个疑问：

①这些柱子的个数是怎么决定的？

②横坐标轴代表的含义是什么？

③这个图能够说明什么问题？

带着这些问题，我们继续学习。

02配置直方图箱

右键单击图表的水平坐标轴，单击“设置坐标轴格式”，然后单击“坐标轴选项”。

直方图的核心就在“横坐标轴”选项中，这里官方的说法是“箱”，关于“箱”的设置，有六个选项。

这六个选项的使用方法，总结如下表所示，Excel默认生成的直方图是自动模式，此时箱宽度通过使用 Scott 正态引用规则计算(1)。

我们通过自定义箱宽度的方式，设置每个箱子的宽度。其实这个宽度指的是数值的分组跨度，输入20，那么这组数据将按照20的间隔进行分组。

数据分组的间距越窄，图表拆分的箱型就越多，分析的区间就越精准。但是这个区间不是无限度的细分的，否则就失去了统计的意义了。

从图中可以明显看出，分数处于在（60，80]之间的最多，（80，100]之间的最少。通过成绩的分布，可以分析出整体水平、测试题目的难易度等等，从而为我们的决策做依据。这就是直方图的最典型的意义。

03帕累托图

帕累托图（Pareto chart），以意大利经济学家V.Pareto的名字而命名，在反映质量问题、展现质量改进项目等领域有广泛应用。

它是按照发生频率大小顺序绘制的直方图，表示有多少结果是由已确认类型或范畴的原因所造成。

从帕累托图的定义中，可以知道，将直方图进行排序，基本上就变成了帕累托图，同时帕累托图还有一条表示累计百分比的折线。因此可以说，帕累托图是直方图的增强版。

举例说明

▼

比如有六项因素能够影响测验成绩，选中此数据表。

单击“插入”>“插入统计信息图表”>然后选择“直方图”右侧的“排列图”。

然后，你看Excel 2016竟然可以一键生成帕累托图，而且还可以自动对项目按照值的大小进行排序，自动计算累计百分比。

①图中的柱形代表每个因素的频数（出现的次数）；

②折线图是累计百分比，最终值一定要落在100%上；

与帕累托图紧密相关的是二八法则，在这里体现在“影响结果80%的因素，集中在所有因素的20%里面”，所以集中力量解决这20%的因素，就能将成绩（或者品质）提升80%。

▌注：（1）用于在 Excel 2016 中创建直方图的公式，自动选项（Scott 正态引用规则）

·END·

在excel中可以利用FREQUENCY函数进行频数统计，利用“数据分析”中的“直方图”宏程序进行频数分析。

函数 FREQUENCY 可以计算数值在某个区域内的出现频率，然后返回一个垂直数组，

由于 函数FREQUENCY 返回一个数组，所以它必须以数组公式的形式输入。

通过频数分布函数可以对数据进行分组和归类，从而使数据的分布形态更加清楚地表现出来。

下图是某课程的学生成绩，我们对其进行统计分析。

我们把学生成绩进行分段0-60、60-70、70-80、80-90、90-100。

我们在C2：C5区域一次输入59、69、79、89作为频数的接收区域（输入的不是每组上限，上限不在本组内）,因为分5组，所以有4个分段点，最后一个不用分段点，但是在计算时要包含进去。

选中D2:D6区域，编辑栏中输入：=FREQUENCY（B2：B25，C2：C5），再按Ctrl+Shift+Enter组合键，得到频数分布结果。

重新整理得到频数分布表。

我们利用直方图也可以获得各组的频数，在菜单“数据”选项卡下“数据分析”选项。如果没有找到需要先在excel选项中的“自定义功能区”中把“开发工具”选中。

然后打开“开发工具”加载项，选中“分析工具库”和“分析工具库-VBA函数”复选框。

这时我们打开“数据”选项卡，单击“数据分析”，弹出“数据分析”对话框。我们选择“直方图”。

在弹出的“直方图”对话框中选工作表中B2:B25填入输入区域，C2:C5填入接收区域。在输出区域中选一个单元格作为输出图表的起始区域，我们这里选G11，同时选中“累计百分比”、“图表输出”。（选中相应单元格区域系统自动加上绝对引用符号）如下图所示：

单击确定按钮，得到频数分布表、直方图及累计频数图。

直接将两个数值相除即可，除号用反斜杠代替。例如求A1单元格数值占A2单元格数值百分比，则=A1/A2即可，然后需要Ctrl+1设置单元格格式为百分比格式。也可以用TEXT函数更改显示格式为百分比格式。

• 任何计算公式都需要以等号开始，例如=A1/A2

• 可以直接用TEXT函数更改格式，例如=TEXT（A1/A2，"#%")

TEXT函数详解：

1. 语法：TEXT(value,format_text)

2. Value 为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用。

3. Format_text 为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。

（本文内容由百度知道网友本本经销商贡献）

在 Excel 值显示方式中，比较难抽象难于理解的莫过于父行汇总的百分比、父列汇总的百分比和父级汇总的百分比，它们的计算方法是用某项的值比上行上或列上父项的值。抽象之处就是行上父项的值或列上父项的值，单从字面上无法看出是什么意思，只有放到具体的实例中才好理解，以下分别列举了父行汇总的百分比、父列汇总的百分比和父级汇总的百分比的具体操作实例，实例中操作所用版本均为 Excel 2016。

一、Excel父行汇总的百分比

1、在一个服装销量透视表中，勾选了“大类、产品名称和销量”三个字段，其中“大类和产品名称”在“行”列表框中，“大类”排在“产品名称”前面，“销量”在“∑ 值”列表框中。

2、把鼠标移到“销量”字段上，按住左键，拖到“∑ 值”下的列表框中，出现一条粗绿线时放开左键；选中 C3 单元格，输入“百分比”，右键 C5 单元格，在弹出的菜单中，依次选择“值显示方式”→ 父行汇总的百分比，则统计出各件与各类服装父行汇总的百分比，操作过程步骤，如图1所示：

3、“父行汇总的百分比”含意说明：

A、父行汇总的百分比 = 某项的值 / 行上父项的值。这个计算公式有些抽象，下面结合演示的实例说明。就拿 A5 单元格的“白色T恤”来说，它的“父行汇总的百分比”为 39.14%，这个结果是用它的销量 732 / 1870（男装总销量）求出来的；“白色T恤”是“男装”下的一件衣服，即“男装”就“白色T恤”的“行上父项”。

B、“男装”的“父行汇总的百分比”又是怎么求得的？也是一样的道理，用“男装”的总销量比上“所有服装（即男装和女装）”总销量，即 1870 / 6484 求得的，也就是“男装”的“行上父项”是所有服装销量的“总计”。

C、计算地区的“父行汇总的百分比”也是一样的方法。只需把某件产品的销量比上某个地区（如广州）的销量，就求得该件产品的“父行汇总的百分比”；再把某个地区的销量比上销量总计，就求得该地区的“父行汇总的百分比”。

二、Excel父列汇总的百分比

1、有一个1月至3月的服装销量透视表，统计出了每种服装每个月的销量、所有服装一个月的总销量和所有服装1月至3月的总销量，如图2所示：

2、现在要计算“父列汇总的百分比”。右键其中一个单元格（如 C5），在弹出的菜单中依次选择“值显示方式”→ 父列汇总的百分比，如图3所示：

3、则统计出每件衣服每个月和所有衣服一个月总销量的“父列汇总的百分比”，如图4所示：

4、“父列汇总的百分比”含意说明：

A、父列汇总的百分比 = 某项的值 / 列上父项的值。这个计算公式与“父行汇总的百分比”的计算方法一样，同样用上面的实例来解析。同样拿 A5 单元格的“白色T恤”来说，它“1月份”的“父列汇总的百分比”为 28.12%，这个结果是用它1月的销量 457 / 1625（3个月的总销量）求出来的，即“3个月的总销量 1625”是“白色T恤”的“列上父项”。“白色T恤”2、3 月的“父列汇总的百分比”同样分别用它们当月销量比上 1625 求得。

B、所有服装每个月的“父列汇总的百分比”是用所有服装当月的总销量比上所有服装三个月的总销量求得。就以所有服装1月份的“父列汇总的百分比”31.24%（C10 单元格）为例 ，它是用 2937 / 9400 求得，2937 是所有服装1月份的总销量（见图2），9400 是所有服装三个月的总销量。

三、Excel父级汇总的百分比

1、仍以服装销量透视表为例。再把一个“销量”字段拖到“∑ 值”列表框中，选中 D3 单元格，输入“父级汇总百分比”，右键 D5 单元格，在弹出的菜单中，依次选择“值显示方式”→ 父级汇总的百分比，弹出“值显示方式（父级汇总百分比）”，“基本字段”选择“大类”，单击“确定”，则统计出各种服装的“父级汇总的百分比”，操作过程步骤，如图5所示：

2、“父级汇总的百分比”含意说明：

A、父级汇总的百分比 = 某项的值 / 所选“基本字段”的值。它跟“父行汇总的百分比”和“父列汇总的百分比”的计算方法一样，只是它可以选择“基本字段”，而“基本字段”相当“父行或父列”。

B、演示中，“基本字段”选择了“大类”，统计结果几乎与“父行汇总百分比”一样，例如“白色T恤”的“父级汇总的百分比”为 39.14%，与“父行汇总百分比”完全一样，它也是用 732 / 1870 求得。所不同的是，“父级汇总的百分比”只求了每件服装销量的“父级汇总的百分比”，没有继续求“大类”（如“男装”）总销量的“父级汇总的百分比”，即 D17 为空；也就是“父级汇总的百分比”只会统计一级。

在 Excel 中，只要把两个数相除且将格式设置为百分比，就可以返回百分比。Excel百分比计算分为简单的、带条件的和分母为0三种情况。其中带条件的，可用 SumIf、CountIf 等函数；分母为0的情况可以用 If、IfError、IsError、IsErr 函数判断，如果分母为0，返回0或空，否返回百分比。以下就是Excel自动计算与带条件计算百分比及分母为0时的解决方法的具体操作实例，实例中操作所用版本均为 Excel 2016。

一、Excel自动计算百分比

1、假如要计算每件服装销量所占的百分比。选中 E2 单元格，输入公式 =D2/$D$11，按回车，则返回小数，单击“开始”选项下的“百分比样式” % 符号，把单元格格式设置为百分比，则小数转为百分比；把鼠标移到 E2 右下角的单元格填充柄上，按住左键，往下拖，则计算出其它服装的销量百分比；操作过程步骤，如图1所示：

2、说明：公式 =D2/$D$11 中的分子 D2 是对单元格的相对引用，分母 $D$11 是对单元格的绝对引用，往下拖时，D2 会自动变为 D3、D4、……，而 $D$11 会一直保持不变，以实现每件服装的销量与总销量相除。

二、Excel带条件计算百分比

1、假如要计算销量大于等于 600 的服装占总数的百分比。把公式 =COUNTIF(D2:D10,">=600")/COUNT(D2:D10) 复制到 E2 单元格，按回车，返回计算结果 67%，操作过程，如图2所示：

2、公式说明：

公式 =COUNTIF(D2:D10,">=600")/COUNT(D2:D10) 中，分子 =COUNTIF(D2:D10,">=600") 用于统计销量大于等于 600 的服装件数，分母 COUNT(D2:D10) 用于统计服装总数。

三、Excel计算百分比分母为0时的解决方法

计算百分比时，如果分母事先无法确定，可能出现分母为0的情况，此时将返回错误的值；因此，遇到这种情况时，应该加判断分母是否为0的条件，当分母为0时，返回0（或""），否则返回计算结果。

（一）方法一：用 if 判断

1、假如要计算1月销量占一季度总销量的百分比。把公式 =IF(G2<>0,D2/G2,0) 复制到 H2 单元格，按回车，则计算出“绿色t恤”1月销量占第一季度销量的百分比；把鼠标移到 H2 右下角的单元格填充柄上，按住左键并往下拖，则计算出其它服装1月销量占一季度销量的百分比；操作过程步骤，如图3所示：

2、公式说明：公式用if函数判断分母 G2 是否为0，如果为0，则返回0；否则返回 D2/G2。从计算结果可知，分母为0的，结果都返回0。除返回0，也可以返加空（""），用 "" 取代公式后面的 0 即可。

（二）方法二：用 IfError 判断

1、同样计算1月销量占第一季度总销量的百分比。把公式 =IFERROR(D2/G2,0) 复制到 H2 单元格，如图4所示：

2、按回车，则计算出“绿色t恤”1月销量占第一季度销量的百分比，与用If函数计算的结果一样，如图5所示：

3、同样用往下拖的方法计算出其它服装的百分比，销量全为0的百分比都为0，如图6所示：

（三）方法三：用 If 结合 IsError 或 IsErr 判断

1、同样计算1月销量占第一季度总销量的百分比。把公式 =IF(ISERROR(D2/G2),0,D2/G2) 复制到 H2 单元格，按回车，则计算出“绿色t恤”1月销量占第一季度销量的百分比；同样用往下的方法，计算出其它服装的百分比，结果与前两种方法完全一至；操作过程步骤，如图7所示：

2、公式说明：ISERROR(D2/G2) 用于判断 D2/G2 是否返回错误，如果是，则返回真，否则返回假；然后再用 if 根据 ISERROR(D2/G2) 的返回值决定是返回0还是返回 D2/G2。

3、用 IsErr 代替 IsError，则公式 =IF(ISERROR(D2/G2),0,D2/G2) 变为 =IF(ISErr(D2/G2),0,D2/G2)，计算结果与上面的三种方法一样。

以上的三种方法中，第二种方法比较简便，其次是第一种方法，第三种方法需要多计算一次百分比，数据量大时可能会影响效率，此时，可选择前两种方法。

复合增长率又称为年均复合增长率，英文为 Compound Annual Growth Rate，通常只用简写 CAGR 代替；它是用来描述一个投资在一定时期内的年度增长率。与算术平均增长率相比，复合增长率是几何平均增长率，也就是平常所说的利滚利，即本年的利息也计入下一年的本金获利。在 Excel 中，计算复合增长率分为百分比和数字两种形式，它们都可用同一个公式计算，下就是它们用 Excel 计算的具体操作方法，操作中所用版本均为 Excel 2016。

一、Excel计算百分比形式的年均复合增长率

1、添加辅助列，并在该列给每个百分比增长率加 1。双击 C2 单元格，输入公式 =b2+1，按回车，返回 113%；选中 C2，把鼠标移到 C2 右下角的单元格填充柄上，鼠标变为加号后双击左键，则 B 列剩余的增长率全部都加 1。

2、计算年均复合增长率。双击 C8 单元格，把公式 =(PRODUCT(C2:C7))^(1/(YEAR(A7)-YEAR(A2)))-1 复制到 C8，按回车，返回 0.056567391；确保当前选项卡为“开始”，单击“百分号（%）”（或按快捷键 Ctrl + Shift + %），则小数转为百分数且不保留小数和自动四舍五入；操作过程步骤，如图1所示：

提示：如果要保留一位小数，选中 C8 后，按 Ctrl + 1（需关闭汉语输入法），打开“设置单元格格式”窗口， 选择“数字”选项卡，再选择左边的“百分比”，在右边“小数位数”后输入 1，单击“确定”，则 6% 变为 5.7%，如图2所示：

3、增长率为什么要加 1？因为本年价值 = 上年价值 × （1 + 增长率），而复合增长率 =（现有价值/基础价值)^(1/年数) - 1，即求复合增长率需要把增长率转为“现有价值/基础价值”形式，则（本年价值/上年价值）= 1 + 增长率。

4、公式 =(PRODUCT(C2:C7))^(1/(YEAR(A7)-YEAR(A2)))-1 说明：

A、PRODUCT(C2:C7) 是把 C2 至 C7 的每个数值相乘，结果为 1.316697512。

B、YEAR(A7) 是取 A7 中日期的年份（即返回 2018），因为 A2 到 A7 的单元格格式为日期型；如果单元格格式为数值型或文本型不需用 Year 函数取年份；YEAR(A7)-YEAR(A2) 用于计算求复合增长率的年数，结果为 5。

C、则公式变为 (1.316697512)^(1/5)-1，接着对小数求指数为 1/5 的指数运算，然后再把计算结果减 1，最后返回 0.056567391。

提示：如果“年份”连续的，还可以用 Count(A2:A7) -1 来统计“年数”，则公式变为 =(PRODUCT(C2:C7))^(1/(Count(A2:A7)-1))-1。

二、Excel计算数字形式的年均复合增长率

1、假如有一个 2013 年到 2018 年的投资表，“资产”列出的是每年的总资产，现在要求年均复合增长率。双击 B8 单元格，把公式 =(B7/B2)^(1/(YEAR(A7)-YEAR(A2)))-1 复制到 B8，按回车，返回 0.108318466，按快捷键 Ctrl + Shift + % 把小数转为百分数；操作过程步骤，如图3所示：

2、公式说明：

A、公式 =(B7/B2)^(1/(YEAR(A7)-YEAR(A2)))-1 由求复合增长率公式代入数值变化而来，B7 为现在价值，B2 为基础价值，也就是用最后一年的价值比第一年的价值；公式中的其它部分与上例中的一样。

B、另外，公式还可以用指数计算函数 Power 计算，即 =POWER(B7/B2,1/(YEAR(A7)-YEAR(A2)))-1。

提示：一年增长下一年亏损且数值大于 0 也可以用上面的方法计算，例如：第一年资产为 12000，第二年为 16000，第三年为 8000，用这个公式 =(8000/12000)^(1/2)-1 计算能获得正确的结果（返回负增长），演示如图4所示：

另外，如果次产中有负数不能简单的用“现有价值/基础价值)^(1/年数) - 1”公式求复合增长率，这样会获得错误的结果。

转载自百家号作者：解晴新生

饼图、百分比堆积图都可以很好的表现出数据的百分比，不过使用时略有不同。制作步骤非常类似：选中数据，插入图表，选择合适的图表，图表就生成了，接下来就是修改图表的样式和外观了。

百分比堆积柱形图

如图，百分比堆积图上不仅显示了员工已完成的数量和待完成的数量，还可以很清楚地两者之间的百分比。

百分比堆积条形图

和百分比堆积柱形图几乎没有区别，一个横向，一个纵向。当类系列名称较长时，条形图就比较适合了，柱形图上的系列名称（横坐标数据）会倾斜，甚至显示不完整。

组合图（柱形图+堆积面积图）

如图，使用堆积面积图来表示百分比，整个图表最大值是100%，有颜色填充的部分是员工已经完成的数据，可以非常清楚地看到每个人的完成率和整体的完成情况。

排列图

排列图会自动对数据进行排序，一般用于表示质量问题，可以让人一眼就找出问题的严重性和问题最多的系列。

饼图

饼图也许是用得最多的百分比图表了。百分比堆积柱形图上显示的百分比一般属于同一事物或同一个人（如已完成和未完成的比例），而普通的饼图则表示的是一个团队中每个人的贡献比例。如果要同时表现出每个人的贡献和每个人的完成情况，可以使用饼图的变体——圆环图。

图表虽多，表达出来的意义有所不同，使用哪一个，还要看自己的需要。想学习图表的制作和修改的朋友，请阅读我的文章《WPS Excel入门：图表制作方法和图表元素》。

谢谢阅读，每天学一点，省下时间充实自己其他能力。欢迎点赞、评论、关注和点击头像。

在 Excel 中，数据透视表主要用于分析统计，它与普通表的区别在于可任意组合字段，从面方便分析统计各项数据。Excel数据透视表怎么做？它有通常有两种方法，一种是创建空数据透视表，另一种是创建统计好的数据透视表；其中前者需要自己统计，后者不用；一般来说，如果有符合要求的统计好的数据透视表，通常选择它，这样不必再统计。数据透视表创建好后，可以按要求对指定列排序，也可按要求进行各类分析统计，例如对某项求和、统计各类产品所占百分比等。以下就是Excel数据透视表怎么做与如何统计各项所占百分比和排序的具体操作方法，实例中操作所用版本均为 Excel 2016。

一、Excel数据透视表怎么做

（一）创建空数据透视表

1、把数据透视表创建到“新工作表”。选中要插入到数据透视表的单元格区域（可以是部分单元格，也可以是整个表格，这里框选整个表格），选择“插入”选项卡，单击屏幕左上角的“数据透视表”，打开“创建数据透视表”窗口，“表/区域”右边自动填好了刚才框选的区域 Sheet1!$A$1:$F$10，“选择放置数据透视表的位置”选择“新工作表”，单击“确定”，则在一个表工作表（Sheet2）创建了数据透视表；勾选“产品名称、价格和销量”三个字段，则它们各项列在左边，并自动求出“价格”和“销量”的总计；操作过程步骤，如图1所示：

2、把数据透视表创建到“现在工作表”，仅把三列创建到数据透视表

A、框选要创建到数据透视表中的区域，例如框选 B、C 和 D 列（方法为：框选 B 列后，按住 Ctrl，再框选 C 和 D 列）；选择“插入”选项卡，单击“数据透视表”，打开“创建数据透视表”窗口，如图2所示：

B、“选择放置数据透视表的位置”选择“现有工作表”，单击“位置”右边的输入框把光标定位到哪里，框选 H1:J10 这片区域，则 Sheet1!$H$1:$J$10 自动填到“位置”后面的输入框中，如图3所示：

C、单击“确定”，则在 H、I 和 J 三列创建数据透视表，勾选“产品名称、价格和销量”三个字段后，效果如图4所示：

提示：选择部分单元格或行列创建数据透视表时，选择区域必须连在一起，否则会提示选择区域无效而无法创建数据透视表，图5所示的选择区域就是无效的：

(二）创建统计好的数据透视表

1、框选整个表格，选择“插入”选项卡，单击屏幕左上角的“推荐的数据透视表”，打开“推荐的数据透视表”窗口，选择一项符合统计要求的项，例如选择“求和项：销量，按大类和小类”，单击“确定”，则在新工作簿创建所选数据透视表，操作过程步骤，如图6所示：

2、在“推荐的数据透视表”窗口中，列出了按各种要求统计的选项，如果有符合要求的直接选择，将节省很多操作步骤，也就不用再做统计，十分方便。

二、在数据透视表中排序

1、在数据透视表中，字符和数值都能排序，但勾选字符列时会自动按升序排序，勾选数值列则不会；选中要排序的项，然后右键这些项，在弹出的右键菜单中，“排序”展开的“升序”和“降序”都呈灰色不可选，如图7所示：

2、给数值列排序。假如给“价格”按“升序”排序。选中 B4:B12，选择“数据”选项卡，单击“升序”图标，则记录按“价格”升序排序，操作过程步骤，如图8所示：

在数据透视表中，不同于普通表对一列排序时需要选择“排序依据（即扩展选定区域和以当前选定区域排序）”，它默认按“扩展选定区域”排序，即对一个字段排序就是对整行排序。

三、按类统计各种服装所占的百分比

（一）统计服装大类（即男装与女装）的百分比

单击屏幕右边“数据透视表字段”下面的“大类”勾选它，把鼠标移到“产品名称”上并按住左键，把它拖到“∑ 值”下面的列表框中，再拖一个“产品名称”“∑ 值”下面；单击“计数项：产品名称2”，输入“百分比”；框选 C4:C5，在其上面单击右键，在弹出的菜单中选择“值显示方式”→ 总计的百分比，则算出男装与女装所占的百分比；操作过程步骤，如图9所示：

（二）统计服装小类的百分比

1、勾选“小类”，把“产品名称”拖到“∑ 值”下面的列表框中，单击“计数项：产品名称”，输入百分比把它替换掉；框选 B4:B6，右键它们，在弹出的菜单中选择“值显示方式”→ 总计的百分比，则统计出三个小类（T恤、衬衫和雪纺）所占的百分比，操作过程步骤，如图10所示：

2、这里统计出的三个小类（T恤、衬衫和雪纺），是不分男装与女装的，也就是它们同时包含男装和女装。例如 T恤 占 33.33%，包括男装和女装的T恤。

四、数据透视表中的字符字段自动折叠

1、在数据透视表中，如果同时勾选多个字符字段，它们将自动折叠，即后面勾选的字段折叠到先勾选字段下面，如图11所示：

2、图11中，先勾选“产品名称”，第二勾选“编号”，第三勾选“大类”，“编号”在“产品名称”下，“大类”在“编号”下；因为统计时，每次通常只统计一项字符列。而数值列会独立占一列，这样便于计算与统计。