01-Excel初阶操作-学习笔记

超链接专题

应用场景：一份excel表格中包含多个子表，如下图所示。让我们在目录所在的子表创建超链接，使得能够快速跳转到各个子表查看数据内容，并为每一个含有数据的表格添加返回到目录所在子表的超链接

手工创建超链接

具体操作：我们以制作跳转至“全部数据”所在子表为例

点到目录所在的子表——点击任意单元格（这里我点的B2）——右击——链接（此时弹出如下图左侧所示的界面）——按照如下图右侧所示填写即可，完成第一个超链接的制作。

类似的方法，我们创建出场景中所需要的其他超链接

超链接函数 - 批量创建

如何批量创建超链接呢？

具体操作：

我们在目录表格中的空白单元格按列分别输入：全部数据，东北，华东，华南，西北以及统计表——再在全部数据所在的单元格输入=HYPERLINK("#"&A4&"!A1",A4)其中A4单元格的内容是“全部数据”字样——回车，鼠标移动到输入HYPERLINK函数单元格的右下角，变成黑色十字后，下拉填充。

完成批量操作

以上的操作都是针对同一个Excel文件做的，那么如何针对外部文件制作超链接呢？

具体操作：首先要先确认要引用的外部文件确实存在！！！

使用函数HYPERLINK，在空白单元格输入=HYPERLINK("D:\HYPERLINK函数创建超链接.xlsx","打开文件")，第一个双引号是引用的外部文件，第二个双引号是空白单元格所显示的内容。

利用图片创建超链接

请看效果图展示

这样的图形化的超链接，请看操作演示

补充：在选择带有超链接的某一个图片时，按住Ctrl不放，在点击鼠标左键。

数据透视表专题

认识数据透视表

• 工具在哪

  打开要做数据透视表的数据——点击任意单元格——插入选项卡——数据透视表——点击确定

弹出如下界面，并针对行字段为“月”，列字段为“部门”，对发生额所数据透视。

• 一列为一个字段，一行为一条记录。

• 更改数据汇总方式

  在数据透视表中，双击表头的统计方式可以更换统计方式

注意！！！以上做数据透视表的拖拽方法是基于旧版本的，以.xls结尾的Excel文件可以采取。

在新版本的，以.xlsx结尾的Excel文件要想实现拖拽行为需要进行如下的设置

打开以.xlsx结尾的Excel文件——插入——数据透视表——鼠标在空白单元格右击——数据透视表选项——在弹出界面进行如下设置：点击“显示”——勾选“经典数据透视表布局”——点击确定

• 在数据透视表上的值，任意选择一个双击可以返回原数据的表，来展示数据的来源。但是有时为了做好保密工作，我们不希望通过双击可以来展示数据的来源，只想看到做好的数据透视表展示的内容。我们可以进行如下操作：

  在做好的数据透视表中，复制如下内容——在空白子表中粘贴——鼠标右击，选择性粘贴——勾选列宽——然后把原数据删除，只发给别人复制好的表格。

  

汇总结果显示为百分比

打开原始数据的Excel文件，然后建立数据透视表（即点击任意单元格——插入选项卡——数据透视表——按要求将“月”拖至行字段，将“部门”拖至列字段——汇总“发生额”），最后，双击透视表左上角的求和项：发生额——点击值显示方式——在值显示方式(A)的下来三角，选择显示百分比的方式。效果图如下

汇总结果显示为百分比，可以选择的显示百分比的方式有三种

• 列汇总的百分比
• 行汇总的百分比
• 总计的百分比

选择不同的显示百分比的方式，呈现出的效果如下图所示

父级及父级百分比

有时候公司要求我们制作的数据透视表的样式如下图所示（左右两种样式）

具体操作：

• 先制作数据透视表

  点击任意单元格——插入选项卡——数据透视表——按要求将“月”拖至行字段，将“部门”拖至列字段——汇总“发生额”

• 按要求调整汇总方式，接下来我们以上图左侧的样式为例

  在上面制作的数据透视表右侧数据透视表字段取消勾选“部门”——拖动“部门”到行字段，在拖到“月”区域时出现绿线，松开鼠标——双击透视表左上角的求和项：发生额——点击值显示方式——在值显示方式(A)的下来三角，选择父级汇总百分比——在基本字段，选择“月”

  

我们以上图右侧的样式

假设我们制作出了上图左侧的样式，在此基础上继续进行操作

拖动“科门划分”到行字段，在拖到“部门”区域时出现绿线，松开鼠标——双击透视表左上角的求和项：发生额——点击值显示方式——在值显示方式(A)的下来三角，选择父级汇总百分比——在基本字段，选择“部门”

同时显示多种统计维度

有时公司要求在汇总时既显示发生额的具体金额，有显示该金额在本年度所占的百分比，具体样式如下图所示

具体操作：

• 先制作数据透视表

  点击任意单元格——插入选项卡——数据透视表——按要求将“月”拖至行字段，将“部门”拖至列字段——汇总“发生额”

• 按要求调整汇总方式，接下来我们以上图样式为例

  我们再次拖拽“发生额”到汇总区域——双击求和项：发生额2——点击值显示方式——在值显示方式(A)的下来三角，选择列汇总的百分比

  好，现在我们按照上图样式更改名字：单击求和项：发生额，如下图所示

  

如果再显示一个维度，再次拖拽“发生额”到汇总区域，按照要求更改显示方式。

按日期统计数据

样例如下图

具体操作:

插入选项卡——数据透视表——拖拽订购日期到行字段，拖拽所属区域到列字段，拖拽金额到汇总区域——单击订购日期所在列的任意单元格——右击,组合——弹出界面，勾选“月”和“年”——点击确定

·有时要求我们对数据进行同比，即2007年1月和2008年1月比，又该如何操作呢？

首先，假设已经做好了上图样例所示的数据透视表，以下是后续的操作演示：

按数值组合数据

应用场景

• 业务员评分。老板想要看到每个评分区间有多少人

  

• 根据分析数据得到该公司是做零售行业还是高端产业制造

  

具体操作

• 点击空白单元格——插入选项卡——数据透视表——拖拽服务分到行字段，拖拽姓名到汇总区域——在服务分所在列，点击任意单元格——鼠标右击——组合（如下图勾选）——点击确定。

  

• 点击空白单元格——插入选项卡——数据透视表——拖拽金额到行字段，拖拽金额到汇总区域——在服务分所在列，点击任意单元格——鼠标右击——组合（如下图勾选）——点击确定。

  

针对情景二，汇总结果如下图所示

可以看到，价格在0-40000的产品销量最好，所以做零售比较合适。

按文本组和归类项目

制作样例

具体操作：

点击空白单元格——插入选项卡——数据透视表——拖拽所属区域到行字段，拖拽产品类别到行字段，拖拽订购日期到行字段——在订购日期所在列点击任意单元格——鼠标右击——组合（勾选月）——点击确定——再将月（订购日期）拖拽到列字段——拖拽金额到汇总区域——鼠标来到产品类别所在列的‘警告标’，待鼠标标为拖拽图标向下拖动三个单元格的位置——产品类别所在列选中“彩盒”，“宠物用品”，“服装”和‘暖靴“——鼠标右击组合——再选中产品类别所在列选中“睡袋”和”警告标“——鼠标右击组合——改名字单击数据组1，在表格区域上方fx标识右侧输入A类商品，同样的道理，按样例给出的要求更改名字

操作演示：

解决日期不能组合的问题

应用场景

• 如下图所示：可以看到月和日分别属于不同的列，并且是文本的格式，那么如何啊按季度进行汇总呢？

  

• 文本类型的日期如何让进行数据透视

具体操作

• 点击空白单元格——插入选项卡——数据透视表——拖拽月到行字段，拖拽部门到列字段，拖拽发生额到汇总区域——选中月所在列的1-3月，鼠标右击——组合（同理，按文本的组合方式划分四个季度）——改名字：更名为一季度，二季度，三季度和四季度——在数据透视表字段取消勾选月——设计选项卡——分类汇总下拉三角选择不显示分类汇总。

  以下是完成效果

  

• 因为订购日期是文本型数据，首先要将其转换为正常的日期格式：选中订购日期所在列——数据选项卡——分列——点击完成；然后就可以进行正常的数据透视工作

  注意！！！在进行正常的数据透视工作前，一定要把日期格式设置好