Excel函数学习笔记

Excel函数学习笔记，职场人士专用、数据分析师必不可少的学习工具。

Excel

Excel动态图

函数--indirect

①制作供选择用的下拉列表

选中要制作下拉列表的单元格，点击数据---数据验证---允许---序列---选择来源区域---确定

②批量插入名称

选择除第一行标题外的所有行，点击公式---定义的名称---根据所选内容创建---最左列

查看上一步的效果，在左上角的名称框中，选择任意的一个姓名，表格中这个姓名后面所有单元格都被选择

③新建名称

公式---定义的名称---名称管理器---新建---在名称位置输入销量---引用位置---输入=indirect()---把鼠标放在括号中，选择下拉的那个框---确定---关闭

注释：indirect函数作用将括号里的文字，变成真正的单元格引用 ，当C59单元格中的是某一个姓名时 =indirect(1!$C$5) =indirect(李四) =D53:F55 所以“销量”这个名称代表的内容，当C59为某一个姓名时，就是D53:F55单元的引用，这样，“销量”就代表了一个根据C59单元内容随时变化的区域

④作图

插入一个没有数据的图：插入---图表---柱状图

在图形上单击鼠标右键---选择数据---系列名称---选择下拉框所在单元格---系列值---如后图所示

子主题 1

函数--offset+match

①制作供选择用的下拉列表

同上

②公式选项卡，新建名称

公式---名称管理器---新建---名称---销量2---在引用位置输入---=OFFSET('1'!$D$52:$F$52,MATCH('1'!$C$58,'1'!$C$53:$C$55,0),0)

offset(,match(,,),) 第一个参数是标题行除了第一个单元格的单元格引用

match(,,)第一个参数是需要匹配的单元格引用，第二个参数是第一个内容的所有区域，第三个是匹配类型

③作图

同上

函数--vlookup

①构造“辅助”行

在区域下方空白单元格A61中，输入=c58

在B61中输入=VLOOKUP($A$61,$C$53:$F$55,COLUMN(),0)，向右填充

②插入图表

选中61行即可，插入柱状图

在图中右键---选择数据---水平轴标签--编辑

Excel图表

图表概述

图表的类型

柱形图

应用范围

常显示一段时间内数据的变化或说明各项之间的比较情况

类别

二维柱形图

簇状柱形图

跨若干类别比较值，类别的顺序不重复

堆积柱形图

比较整体的各个部分，显示整体的各个部分如何随时间而变化

百分比堆积柱形图

比较各个值占总计的百分比，显示每个值的百分比如何随时间而变化

三维柱形图

三维簇状柱形图

三维堆积柱形图

三维百分比堆积柱形图

三维柱形图

折线图

应用范围

使用折线图来表示随时间变化的连续数据，故折线图用来反应在相等时间间隔下的数据趋势。一般沿横坐标轴均匀分布类别数据，沿纵坐标轴均匀分布数值数据

类别

二维折线图

折线图

按时间（年、月和日）或类别显示趋势。类别顺序很重要，存在许多数据点

堆积折线图

显示整体的各个部分如何随时间而变化

百分比堆积折线图

按时间或类别显示占整体的百分比，显示每个值所占百分比如何随时间而变化

带数据标记的折线图

按时间（年、月和日）或类别显示趋势。类别顺序很重要，存在很少的数据点

带标记的堆积折线图

显示整体的各个部分如何随时间而变化

带数据标记的百分比堆积折线图

按时间或类别显示占整体的百分比，显示每个值所占百分比如何随时间而变化

三维折线图

三维折线图

按时间（年、月和日）或类别显示趋势，显示第三个坐标轴上的数据，次坐标轴显示一些在其他线前面的线

饼图

应用范围

用于显示一系列数据中各项的比例大小，能直观地表达部分与整体之间的关系，各项比例值的总和始终等于100%，饼图中的数据点显示为整个饼图的百分比

类别

二维饼图

饼图

显示整体的比例。数字等于100%，图表仅包含几个饼图切片（切片太多，会造成难以估计角度）

复合饼图

显示整体的比例。从第一个饼图中提取一些值，将其合并在第二个饼图中，使较小百分比更具可读性或突出强调第二个饼图中的值

复合条饼图

显示整体的比例。从第一个饼图中提取一些值，将其合并在堆积条形图中，使较小百分比更具可读性或突出强调堆积条形图中的值

三维饼图

显示整体的比例。数字等于100%，图表仅包含几个饼图切片（太多切片造成难以估计角度）

圆环图

显示整体的比例。当存在与较大总和相关的多个系列时，请使用它，而不是使用饼图

条形图

应用范围

用于跨若干类别比较数值，与柱形图非常相似，也用于显示一段时间内数据的变化或说明各项之间的比较情况。但与柱形图不同的是条形图一般沿横坐标轴均匀分布数值数据，沿纵坐标轴均匀分布类别数据，所以可以在类别文本较长的时候使用

类别

二维条形图

簇状条形图

跨若干类型比较值。图表显示持续时间，类别文本很长

堆积条形图

比较相交于类别轴的整体的各个部分，显示整体的各个部分如何随时间而变化。类别文本很长

百分比堆积条形图

比较各个值占总计的百分比，显示每个值的百分比如何随时间而变化。类别文本较长

三维条形图

三维簇状条形图

跨若干类别比较值。图表显示持续时间，类别文本很长

三维堆积条形图

比较相交于类别轴的整体的各个部分，显示整体的各个部分如何随时间而变化。类别文本很长。

三维百分比堆积条形图

比较各个值占总计的百分比，显示每个值的百分比如何随时间而变化。类别文本较长。

面积图

应用范围

用于表现一系列数值随时间变化的程度，可引起人们对总值趋势的关注，常用来显示所绘的值的总和，或是显示整体与部分间的关系

类别

二维

面积图

按时间（年、月和日）或类型显示趋势。类别顺序很重要，强调随时间的变化幅度

堆积面积图

按时间或类别显示各部分与整体的关系，强调随时间的变化幅度，跨趋势强度总值

百分比堆积面积图

按时间或类别显示占整体的百分比，强调每个值所占百分比随时间的变化幅度

三维

三维面积图

按时间或类别显示趋势，显示第三个坐标轴上的数据，此坐标轴显示一些杂其他面积前面的面积。类别顺序很重要，强调随时间的变化幅度

三维堆积面积图

按时间或类别显示部分与整体的关系，强调随时间的变化幅度，跨趋势强调总值

三维百分比堆积面积图

按时间或类别显示整体的占比，强调每个值所占百分比随时间的变化幅度

X Y散点图

应用范围

X Y散点图可用于若干数据系列中各个数值之间的关系，通常用于显示和比较数值。此外散点图还可以用来绘制函数曲线，从简单的三角函数、指数函数、对数函数到更复杂的混合型函数，都可以利用散点图快速准确地绘制出曲线。所以在教学、科学计算中会常用到此图

类别

散点图

散点图

比较至少两组值或两对数据，显示值集之间的关系。数据代表单独的测量

带平滑线和数据标记的散点图

比较至少两组值或两对数据。存在很少的数据点，数据代表一组数据公式计算的x、y数对

带平滑线的散点图

比较至少两组值或两对数据。存在许多数据点，数据代表一组数据公式计算的x、y数对

带直线和数据标记的散点图

比较至少两组值或两对数据。存在很少的数据点，数据代表单独的测量

带直线的散点图

比较至少两组值或两对数据。存在许多数据点，数据代表单独的测量

气泡图

比较至少三组值或三对数据，显示值集之间的关系。在第三个值可以用来确定气泡的相对大小。

三维气泡图

比较至少三组值或三对数据，显示值集之间的关系。有第三个值可以用来确定气泡的相对大小。

曲面图

应用范围

曲面图可以用曲面来表现数据的变化情况，其颜色和图案用于表示在相同数值范围内的区域，并将数据之间的最佳组合显示出来。

类别

三维曲面图

在连续曲面上跨二维显示数值的趋势线

三维曲面图（框架图）

在连续曲面上跨两维显示数值的趋势线。类别和系列均为数字，直线显示数据曲线

曲面图

显示三维曲面图的二维顶视图，使用颜色显示值范围。类别和系列均为数字

曲面图（俯视框架图）

显示三维曲面图的二维顶视图。类别和系列均为数字。因为颜色可为此图表类型添加详细信息，所以可考虑改用曲面图。

股价图

应用范围

用于描述股票的走势，是一种专用图表，用户若要创建股价图，需要按照一定的顺序安排工作表中的数据

根据数据的排序顺序分

盘高-盘低-收盘图

显示股票随时间的表现趋势。拥有三个系列的价格值，盘高、盘低和收盘

开盘-盘高-盘低-收盘图

显示股票随时间的表现趋势。拥有四个系列的价格值：开盘-盘高-盘低和收盘

成交量-盘高-盘低-收盘图

显示股票随时间的表现趋势。拥有四个系列的值：成交量、盘高、盘低和收盘

成交量-开盘-盘高-盘低-收盘图

显示股票随时间的表现趋势。拥有五个系列的值：成交量、开盘、盘高、盘低和收盘

雷达图

应用范围

用于表现各数值相对于中心点的变化情况。在雷达图的创建中，由一个数据系列覆盖的区域用一种颜色来填充

类别

雷达图

显示相对于中心点的值。不能直接比较类别

带数据标记的雷达图

显示相对于中心点的值。不能直接比较类别

填充雷达图

显示相对于中心点的值。不能直接比较类别

树状图

树状图是一种数据的分层视图，按颜色和距离显示类别，可轻松显示其他图表类型很难显示的大量数据。一般用于展示数据之间的层级和占比关闭，矩形的面积代表数据的大小。

旭日图

用于展示多层数据之间的占比及对比关系，图形中每一个圆环代表同一级别的比例数据，离远点越近的圆环级别越高，最内层的圆表示层次结构的顶级。

直方图

用于展示数据分布情况的图表，常用于分析数据在各个区段的分布比例。它可清晰地展示出数据的分类情况和个级别之间的差异，是分析数据分布比重和分布频率的利器

箱型图

是Excel中新增的一个数据分析图表，其好处就是可以方便地观察在一个区间内一批数据的四分值、平均值以及离散值。例如可以利用箱型图分析多个班级在一些学科上的学生成绩，了解各班级成绩的分布情况

瀑布图

瀑布图是由麦肯锡顾问公司独创的一种图表类型。该图表采用绝对值与相对值想结合的方式，来展示初始值、以及该初始值的增减数值，非常适用于表达数个特定数值之间的数量变化关系或进行财务数据分析。

组合图表

应用范围

需要在图表中体现多个数据维度。将柱形图、折线图等不同类型的图表在同一个图表中呈现出来，这时就需要使用组合表

类别

粗状柱形图-折线图

突出显示不同类型的信息。具有混合类型的数据。

堆积面积图-簇状柱形图

突出显示不同类型的信息。具有混合类型的数据

簇状柱形图-次坐标轴上的折线图

突出显示不同的信息。图表中的值的范围变化很大，具有混合类型的数据。

图表的创建

图表的编辑

图表的布局设置

图表标题的编辑

图例的编辑

数据标签和数据表的添加

坐标轴的编辑

快速布局

图表类型的更改

查找与引用

单元格的引用

相对引用

在复制公式时，函数参数的位置也发生变化

绝对引用

公式不论复制到哪里，参数的绝对地址不变，使用方法就是在地址上添加“$”,具体的操作就是将光标放置在参数上，按下键盘的f4键

混合引用

在单元格地址中行或列前面添加“$”,比方说“$a1”,就是一个混合引用，表示列的绝对引用，行的相对引用，相反，如“a$1”表示列的相对引用，行的绝对引用

查找与引用函数

address

根据指定的行号和列标创建文本类型的单元格地址

语法格式：address(row_num,column_num,abs_num,a1,sheet_text)

参数：row_num表示单元格引用中使用的行号，column_num表示单元格引用中使用的列标,abs_num表示指定返回的引用类型，a1用以指定A1或R1C1引用样式的逻辑值。如果A1为True或省略，函数address返回A1样式的引用，如果A1为False，函数address返回R1C1样式的引用

areas

表示返回引用中包含的区域个数，区域可以为连续的单元格区域或某个单元格

areas(reference)

reference表示对某个单元格或单元格区域的引用，也可以引用多个区域，如果需要将几个引用指定为一个参数，则必须用括号括起来，以免系统将逗号作为参数间的分隔符

choose

表示在数值参数列表中返回指定的数值参数

choose(index_num,value1,[value2],...)

参数：index_num为必要参数，数值表达式或字段，它的运算结果是一个界于1-254之间的数值，或者为公式或对包含1-254之间某个数字的单元格引用，value1，value2,为数值参数，参数的个数介于1-254之间，函数choose会基于index_num从这些值参数中选择一个数值或一项要执行的操作。参数可以是数字、单元格引用、已定义名称、公式、函数或文本

column

表示返回指定引用的列标

column(reference)

参数：reference表示需要返回列标的单元格或单元格区域，如果省略了该参数，则返回该公式所在的单元格的列标

row

表示返回指定引用的行号

row(reference)

columns

表示返回数值或引用的列数

columns(array)

rows

表示返回数组或引用的行数

rows(array)

formulatext

表示返回单元格内的公式并以文本形状显示

formulatext(reference)

hlookup

在查找范围的首行查找指定的数值，最终返回区域中指定行的所在列的单元格中的数值

hlookup(lookup_value,table_array,row_index_num,range_lookup)

vlookup

在单元格区域的首列查找指定的数值，最终返回该区域的相同行中任意指定的单元格中的数值

vlookup(lookup_value,table_array,row_index_num,range_lookup)

lookup

向量形式

表示在单行或单列中查找指定的数值，然后返回第2个单行或单列中相同位置的单元格

lookup(lookup_value,lookup_vector,result_vector)

数组形式

表示在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值

lookup(lookup_value,array)

参数：lookup_value表示在数组中查找的值，array表示要与查找值进行比较的文本、数字或逻辑值的单元格区域

index

引用形式

表示返回指定的行与列交叉处的单元格引用

index(reference,row_num,column_num,area_num)

数组形式

返回指定的数值或数值数组

index(array,row_num,column_num)

indirect

对引用进行计算，并显示其内容

indirect(ref_text,a1)

match

返回指定数值在指定数组区域中的位置

match(lookup_value,lookup_array,match_type)

offset

返回单元格或单元格区域中指定行数和列数区域的引用

offset(reference,rows,cols,height,width)

transpose

将表格中的行列进行转置，例如将行单元格区域转置成列单元格区域，或将列单元格区域转置为行单元格区域

transpose(array)

array表示需要进行转置的数组或工作表上的单元格区域

先选中区域，然后输出函数，按ctrl+shift+enter

数据管理与分析

数据排序和筛选

排序

基本排序

按单个字段进行排序

按多个字段进行排序

先选择一列，然后菜单栏选择“数据”，单击”排序“，弹出对话框，选择“扩展选定区域”，点击“排序”

设置“主要关键字”为“实发工资”---排序依据为“数值”---次序为“升序”---单击“添加条件”

“次要关键字”---选择“部门”---设置排序依据和排序次序---确定

特殊排序

排序时第一列序号不变

把除了第一列后面的列选中，数据---排序----添加主关键字“实发工资”---设置排序依据和次序---确定

按笔画排序

和基本排序一样，不同的是添加“姓名”为主关键字，其他不变，点击“选项”按钮，选择“笔划排序”，确定

按行排序

选中任意单元格---数据---排序---选项---方向---按行排序---确定

排序---主要关键字---行1---升序---确定

按单元格底纹排序

选中单元格---数据---排序---主要关键字---实发工资---排序依据---单元格颜色---次序---选择颜色---添加条件--按照前面的步骤

自定义排序

其他步骤和之前一样，选中部门以后，次序选择“自定义序列”，然后把“财务部，技术部，销售部，人事部”添加到自定义序列中，每输入一个 单击一次添加，最后确定，在排序对话框中会显示刚才添加的序列，点击确定

筛选

快速筛选

选中“部门”---数据---筛选--在表格的“部门“中有个复选框，根据个人的需求进行筛选

自定义筛选

数字筛选

数据---筛选---进入筛选模式---单击 实发工资---数字筛选---自定义自动筛选方式---大于---输入4000---确定

前10项筛选

选中任意单元格，Ctrl+Shift+L进入筛选模式---点击”实发工资“筛选按钮---数字筛选---前10项---自动筛选前10个---最大---输入3----确定

文本筛选（模糊筛选）

数据---筛选--姓名---文本筛选--等于---自定义自动筛选方式---等于--输入”张*“---确定 查找出所有姓张的员工

如果在自定义自动筛选方式---等于---输入”张?“---确定 查找出项”张夏“这样的张姓员工 两个字的

在Excel中，通配符包括英文半角问号”?“和星号"*"两种，在进行筛选的时候，问号代替任意的单个字符；星号代替任意数目的字符，可以是单个字符、多个字符或无字符

日期筛选

选中单元格---Ctrl+Shift+L---出生日期---日期筛选---期间所有日期---1月

选中单元格---Ctrl+Shift+L---出生日期---日期筛选---介于---自定义自动筛选方式---输入日期（例如：1985/1/1 2000/1/1）---确定

高级筛选

高级筛选的”与“关系

销售总分大于320的信息

在A14：A15单元格区域输入筛选条件，将所有的条件输入在一行，表示与关系

数据---排序---高级---列表区域---选中上面所有数据，包含第一行---条件区域---选择条件区域---确定

或 关系

选出“销售部”或成绩大于320分的信息

在A14：A15单元格区域输入筛选条件，将所有的条件输入不同的行，表示或关系

数据---排序---高级---列表区域---选中上面所有数据，包含第一行---条件区域---选择条件区域---确定

分类汇总

简单分类汇总

选中部门任意单元格---数据---升序---分类汇总---分类字段---部门---汇总方式---求和---选定汇总项---总分---确定

多重分类汇总

选中表中任意单元格---数据---排序---主要关键字---部门---添加条件---次要关键字---职务---确定

分类汇总---分类字段---部门---汇总方式---求和---选定汇总项---实发工资---确定

再次分类汇总---分类字段---职务---汇总方式---平均值---选定汇总项---基本工资---取消勾选替换当前分类汇总---确定

可点击数字3按钮，只显示部门和职务总的结果

复制分类汇总的结果

打开多重分类汇总的结果表---单击3---显示所有汇总结果---选中---开始---编辑---查找和选择---定位条件---可见单元格---确定---返回工作表---Ctrl+C复制---切换到其他空表---Ctrl+V粘贴

隐藏分级显示

通过”Excel选项“对话框隐藏

打开需要隐藏分级的工作表---文件---选项---Excel选项---高级---取消勾选如果应用了分级显示，则显示分级显示符号---确定

功能区域隐藏

打开创建分类汇总的工作表---数据---分级显示---取消组合---清除分级显示

显示

根据什么隐藏 就反向操作

分页显示分类汇总

打开创建分类汇的工作表---数据---分类汇总---勾选每组数据分页---确定

页面布局---页面设置---在工作表选项卡中单击”顶端标题行“右侧按钮---返回工作表中选中标题行---确定

文件---打印

合并计算和条件格式

合并计算

按位置合并计算

打开工作表，“上半年”和“下半年”表结果和字段位置都相同，，切换到“合并计算”----选中B2单元格---数据---数据工具---合并计算---单击引用位置的折叠按钮---切换到“上半年”表----选中B2:C6单元格区域---单击折叠按钮----返回“合并计算”---在”引用位置“看到引用的单元格区域----添加

按照上面的方法添加“下半年”表引用区域----确定

使用按位置合并计算的时候，所有表格的结构、首行和首列都必须相同，否则计算结果是错误的

复杂结构多表合并计算

打开工作表，观察发现连个首列关键字排序不同

切换到“多表合并计算”---选择A1单元格---数据---数据工具---合并计算---单击“引用位置”按钮---切换“家乐福店”表---选择A1：C6单元格区域，单击按钮---返回“多表合并计算”---添加---勾选“标签位置”---最左列---确定

如果不勾选“最左列”，则计算出的结果是错误的，若勾选该复选框，系统会自动根据最左列相同关键字计算相对应的数据

设置合并计算的函数

打开工作表---切换“合并计算函数”---选择A1单元格---数据---数据工具---合并计算---函数---选择“平均值”---添加需要合并的区域---勾选首行---勾选最左列---确定

“首行”复选框的作用是根据首行关键字进行合并计算

编辑引用区域

修改引用区域

明确要修改的引用位置

删除引用区域

条件格式

创建条件格式

突出显示单元格

突出显示“专业知识”成绩在90分以上的

选择D2:D10单元格区域---开始---样式---条件格式----突出显示单元格规则---大于---为大于以下值的单元格设置格式---90---设置为---浅红色填充---确定

突出显示文本

突出显示“张”性员工

选中“员工姓名”---样式---条件格式---突出显示单元格规则---文本包含---为包含以下文本的单元格设置格式---张---设置为---自定义格式---根据自己喜好设置字体格式、边框样式、填充颜色---确定

使用“突出显示单元格规则”可以在复杂的表格中标记出重复的数值或文本。步骤：选中单元格区域---条件格式---突出显示单元格规则---重复值---根据需求设置格式---确定（检查重复的身份证号、联系方式、出生日期等）

最前/最后规则

标记出前5名

选中H2:H10单元格区域---开始---样式---条件格式---项目选取规则---前10项---为值最大的那些单元格设置格式---5---设置为---所需格式---确定

数据条

使用数据条可以直观地展现数据的大小，数据条越长表示数值越大，反之亦然

选中E2:E10单元格区域---开始---样式---条件格式---数据条---自己选一个喜欢的数据条---确定

色阶

根据单元格填充颜色的深浅展示数据的大小

选中应用色阶的单元格区域---条件格式---色阶--随便选一种即可---确定

也可以自定义 色阶---其他规则--新建格式规则---格式样式---双色刻度----设置最大值 最小值---确定

图标集

使用“图标集”对数据进行等级分类

选中F2：F10单元格区域---开始---样式---条件格式---图标集---三向箭头（彩色）

数据透视、筛选

数据透视表

创建数据透视表

介绍

数据透视表是一种交互式的Excel表格，可以动态地改变报表的版面布置。

创建方法

表格

快速创建

选中单元格，插入---表格---推荐的数据透视表

推荐的数据透视表---左侧选择合适的透视表样式--确定

打开新建的数据透视表--同时弹出“数据透视表字段”导航--功能区出现“数据透视表工具-分析和设计”

创建空白的数据透视表

选中单元格，插入---表格---推荐的数据透视表

推荐的数据透视表---空白的数据透视表--确定

根据需要选择字段

在透视表中添加内容

打开空白的数据透视表---数据透视工具-分析---显示---字段列表

数据透视表字段---工具---字段节和区域节并排

选择要添加到报表的字段--部门---拖拽到行区域

姓名--拖拽行区域

职务--拖拽到列区域--基本工资拖拽到值区域--实发工资拖拽到值区域

在上一步中，如果将“职务”拖拽到“筛选”区域，则在数据透视表的左上角出现“职务”筛选按钮，单击该按钮，在列表中勾选“选择多项”复选框，然后勾响应的复选框，单击“确定”

在数据透视表中只显示上一步勾选的部分

设置数据透视表字段

自定义字段名称

打开工作表，选中需要命名的字段名称，“求和项：基本工资”，在编辑栏中把“求和项”删掉

删掉之后，在后面输入“汇总”

选中要修改的字段，数据透视表工具-设计---活动字段---字段设置---值字段设置---自定义名称--确定

活动字段的展开和折叠

选中A5单元格--活动字段---折叠字段

使用该方法可以为选中的字段统一设置展开或折叠，如果用户想展开或折叠某部分，可以直接点击字段名称前面的"+"-"

隐藏字段标题

选择数据透视表中任意单元格，然后切换到“数据透视表工具-设计”，单击“显示”中的“字段标题”按钮就可以隐藏或显示字段标题

删除字段

导航窗格删除

数据透视表字段---单击需要删除的字段---例如单击“值”区域“求和项：奖金”字段，在列表中选择“删除字段”---或者在“选择要添加到报表的字段”区域取消勾选“奖金“复选框

右键菜单删除

选中要删除的字段--鼠标右键---删除”求和项:奖金“

设置字段的汇总方式

设置”值“区域的字段

选择需要汇总的任意单元格，数据透视表工具-分析---活动字段---字段设置

值字段设置---值汇总方式---计算类型---平均值

返回工作表，可见”基本工资“汇总方式为平均值，且字段名称自动修改为”平均值项：基本工资“

选中D5单元格，单击鼠标右键，在快捷菜单中选择”值字段设置“，打开”值字段设置“---值汇总方式---计算类型---最大值

返回工作表，可见”奖金“汇总方式为最大值，且字段名称自动修改为”最大值项：奖金“

设置”行“区域的字段

选中A4单元格，为”财务部“字段---活动字段---字段设置

字段设置---分类汇总---无

确定，各个部门下方的汇总行被隐藏

选中A4单元格，字段设置---分类汇总---自定义---选择一个或多个函数中任选一个---求和和平均值---确定

查看

设置数据的显示部分

选中B5单元格，设置”基本工资“---活动字段---字段设置

值字段设置---值显示方式---总计的百分比

确定

选中数据，鼠标右键，值显示方式----列汇总的百分比

编辑数据透视表

移动数据透视表

第一种方法

选中透视表中任意单元格，数据透视表工具-分析----操作---移动数据透视表

移动数据透视表---现有工作表---位置

返回工作薄中，切换“员工工资表”---选择需要移动的位置K1单元格---单击折叠按钮

返回“移动数据透视表”---确定---返回工作薄中，可见在选中的K1位置显示数据透视表，原位置的透视表已经不存在

第二种方式

在“移动数据透视表”---选中“新工作表”---确定---就可以在同一工作薄中新建工作表并将数据透视表移至该工作表中

第三种方式

使用复制功能将数据透视表复制到指定的位置，且保留原数据透视表。选中数据透视表中任意单元格，按Ctrl+A全选数据透视表，然后按Ctrl+C，复制数据透视表，然后选中需要粘贴的位置，单击“剪贴板”选项中“粘贴”，在列表中选择“粘贴”，就可以复制数据透视表

刷新数据透视表

手动刷新

选中透视表内任意单元格，数据透视表工具-分析---数据---刷新

打开数据透视表---选中任意单元格--单击鼠标右键---刷新

自动刷新

打开数据透视表，选中表格内任意单元格---数据透视表工具-分析---数据透视表---选项

数据透视表选项---数据---勾选打开文件时刷新新数据复选框---确定

更改源数据

选中数据透视表中任意单元格---数据透视表工具-分析---数据---更改数据源

更改数据透视表数据源---表/区域右侧的折叠

返回工作表，重新选择数据透视表的数据源

返回“移动数据透视表”---在“表/区域”文本框中显示更改后的数据源---确定，就可以更改数据源

数据透视表的排序

对数值进行排序

选中“基本工资”B4单元格，数据---排序和筛选---升序

返回查看，每个部门内部按基本工资升序排序，各部门汇总的数据没有变化

选中“财务部汇总数据”---鼠标右键---排序---降序

完成 进行查看

对行区域字段进行排序

选中任意单元格---行标签---选择字段---部门---升序

用相同的方法对“员工姓名”排序

行标签---其他排序选项---排序---升序排序或降序排序---确定

数据透视表的筛选

使用字段下拉列表筛选

打开数据透视表---单击“行标签”按钮---选择字段---部门---勾选筛选字段的复选框---确定---观察结果

单击“行标签”按钮---选择字段---员工姓名---勾选筛选字段的复选框---确定

使用标签筛选

打开数据透视表---单击“行标签”---选择字段---员工姓名---标签筛选---开头是----张*---确定

使用值筛选

打开数据透视表---行标签---值筛选---前10项---最大---5---依据---求和项：实发工资

应用条件格式

打开数据透视表---选中B4:B8单元格区域---开始---样式---条件格式---数据条---橙色数据条

格式选项---所有显示---求和项：基本工资 值的单元格

分页显示

打开数据透视表---数据透视表字段---行 区域的 部门 字段拖拽至 筛选区域中

返回数据透视表，切换至“数据透视表工具-分析”---单击“数据透视表”选项组中“选项”下三角按钮---在列表中选择“显示报表筛选页”

打开“显示报表筛选页”---选定要显示的报表筛选页字段---部门---确定

按部门分页打印

打开数据透视表---选中任意单元格 如：A5 ---切换至“数据透视表工具-分析”---活动字段---字段设置---布局和打印---勾选 每项后面插入分页符---确定

返回工作表---页面布局---页面设置---打印标题

页面设置---切换至“工作表”---顶端标题行---在工作表选择标题行---返回该对话框---确定

文件---打印

数据透视表的布局

数据透视表的布局调整

打开数据透视表---单击“部门”字段---在菜单中选择“删除字段”---职务拖拽到行区域，放在员工姓名字段上方

再次打开“数据透视表字段”---职务---移动到列标签---分别删除“值”区域中“奖金”和“保险”

数据透视表展示了基本工资和实发工资中不同职务下员工的工资分配情况

数据透视表的布局形式

打开新创建的数据透视表，系统默认情况下是“以压缩形式显示”

选中任意单元格---数据透视表工具-设计---布局---报表布局---以大纲形式显示

按照同样的方法---设置“以表格形式显示”

如果需要在透视表中重复标签---布局---报表布局---重复所有项目标签

数据透视表的经典布局

功能区

选中透视表中任意单元格---数据透视表工具-分析---数据透视表---选项---显示---勾选“经典数据透视表布局”---确定---光标移至“职务”字段上时光标会出现4个方向箭头---按住鼠标左键拖拽至“部门”字段前---在行区域中调整顺序

右键单击法

选中任意单元格---鼠标右键---数据透视表选项---勾选启用经典数据透视表布局

数据透视表的美化

套用数据透视表的样式

选中透视表内任意单元格---数据透视表工具-设计---选择合适的样式

自定义数据透视表样式

打开数据透视表---选中任意单元格---数据透视表工具-设计---数据透视表样式---新建数据透视表样式---表元素---第一列---格式---填充---设置填充颜色---字体---设置字体颜色---确定

设置单元格格式---填充---设置填充颜色---字体---设置字体颜色---确定

返回 新建数据透视表样式---总计行---格式---设置单元格格式---在对话框中设置填充颜色为浅褐色和字体为加粗---确定

返回数据透视表---数据透视表样式---应用这个自定义样式

数据筛选

切片器

切片器是以一种直观的交互式的方式来实现数据透视表中数据的快速筛选。在工作表中插入切片器，可使用按钮对数据进行快速分析和筛选

插入切片器

选中透视表任意单元格---数据透视表工具-分析---插入切片器---勾选相关的复选框---确定

筛选字段

隐藏切片器

切片器工具-选项---排列---选择窗格---选择全部隐藏

日程表

当需要对日期格式的字段进行筛选时，使用日程表功能可以准确快速地筛选出数据

插入日程表

打开数据透视表---选中任意表格---数据透视表工具-分析---筛选---插入日程表---打开日程表---勾选出生日期---确定

利用日程表进行筛选

打开透视表，单击“出生日期”切片器右上角的年----选择年----在日程表中---按住Shift选中1988和1999---在数据透视表中筛选出这个时间段的人的信息

数据透视图

创建数据透视图

打开数据透视表---选中数据透视表中任意单元格---数据透视表工具-分析---工具---数据透视图---插入图表---柱形图---簇状柱形图---确定

编辑数据透视图

更改数据透视图的类型

打开数据透视表---选中数据透视图---数据透视图工具-设计---类型---更改图表类型---折线图---选择合适的折线图---确定

设置数据透视图的布局

打开透视表---选中透视图---数据透视图工具-设计---图表布局---快速布局---选择合适的布局---返回数据透视图中，给标题框输入图表的标题

用户可以根据需要，添加相应的元素。选中透视图---数据透视图工具-设计---图表布局---添加图表元素---数据标签---上方

删除数据透视图

delete

美化数据透视图

选中数据透视图---数据透视图工具-设计---图表样式---选择合适的样式---图表样式----更改颜色---选择合适的颜色

数据透视表工具-格式---形状样式---选择合适的形状样式---形状样式---形状填充---选择图片---插入图片---来自文件---插入图片---选择合适的图片---确定

形状样式---形状轮廓---在列表中设置轮廓的宽度和颜色

形状样式---形状效果---设置发光效果

选中数据透视图标题---艺术字样式---选择合适的样式---设置填充颜色，文字效果等