如何用excel做数据分析报表（9个一定用得到的Excel功能）

前言

很多人在用Excel做报表时，往往都花费很多时间，却因为不懂方法去验算每个数字的对错，所以常常会出现错误的资讯。这是很让人挫折的经验。

其实Excel是一个只要学习部分功能就能发挥强大效益的工具。尽管系统内置了许多功能和函数，但是只要掌握最基础的观念和做法，就足以应付职场里大部分的资料分析需求，让你的作业效率大幅提升，快速交出报表，还能确保报表数字的正确性。

在接下来的内容里，我们将按照输入资料、整理资料、资料运算、分析资料和制作图表这5个商务场景，介绍Excel里9个一定会用到的功能。

1.设定单元格格式

选对格式、准确输入，更改类别设定不用重复输入

1-文字格式/手机号码要这样输入，第一个0才会出现

不需要计算的资料，最好设定为文字格式，才不会被Excel自动判断为数值，改变呈现的样貌。 最常见的例子是，你想输入员工编号，明明输入的是088xx,最后显示出来却变成88xx,首位0莫名其妙的消失了。这是因为Excel误判为数值，而0在数值首位是没有意义的数字，会被直接舍弃。其他像办公电话、统一发票号码等，都是属于可以设定为文字的格式的类型。

方法一：加个单引号

要让数值变数字，可以在输入时先键入单引号['],例如['0193'],此时电话号码的数字就会显示最前面的0

方法二：统一改为文字格式

①先点选要改变格式的储存格 ②按右键选择[设置单元格格式] ③把[数值]改成[文本] ④按下确定即可

2-数值格式：想用来运算的资料，都要设置为数值

有时候运用函数计算却发现结果有误，可能就是储存格式格式设定错误导致的。比如说：常用来求和的SUM函数，碰到不是数组格式的数字会自动视为0，所以如果你输入的资料要用来运算，最好设置为数值格式。 除了方便计算，数值格式里还有许多呈现资料的格式，可以帮助你凸显报表的重点。

3-日期格式/年、月、日、星期，一个键就自由切换

excel内置的日期和时间格式有很多种，方便你切换资料呈现的样子，唯一的关键就是输入资料时必须按照标准打法[年/月/日 24小时制的时间]，例如2020/01/01 14:00:00，这样Excel就能辨别出这个资料是时间而非数值。之后就可以仰赖Excel储存格式的功能，变换需要的呈现形式，不需要重新输入资料。

2.储存单元格

搞懂相对引用与绝对引用，复制公式不出错

1、相对位置：复制函数到其他单元格，Excel会自动调整公式行列设定

一般来说，你可以适用SUM函数计算总销售，例如在C6单元格输入函数【=SUM(C3:C5)】,就能得到产品A的销售总和。 想接着计算计算产品B的销量，只要复制黏贴上A的销量和函数到D6就好了。Exceld的预设状态就会帮助你调整公公式，方便你在第一时间输入函数，可以直接复制和套用。不过，这份贴心的设计，有时候就是造成公式【跑掉】的原因。 比方说：你在同个工作表里设计了另一个表格【年度销售统计表】，打算把刚刚算出来的产品A第四季度的销量复制到年度统计里，一复制黏贴上却发现，明明要C13显示第四季度销量，缺出现了自动调整的函数【SUM(C10:C12)】!Excel的善意反而成为了困扰。

2、混合位置：无论怎么复制，都要计算制定的单元格

如果想要确保公式在复制黏贴上之后，会呈现你想要的样子，就要靠你的手工调整，告诉Excel不论把公式复制到哪里，都要计算你指定的哪一个单元格，别随便变动位置。 这个功能的【暗号】就死金钱符号【$】,表示[固定]。例如原本在输入公式时，单元格表示方式为A1,如果改成输入[$A$1],表示行不动、列不动。之后复制公式到其他单元格时，都是以A1来计算。假如你只希望固定行，但列依然要请Excel自动调整，就只要在行位旁边加上金钱符号，如【$A1】；若只想固定列，行位依然要请Excel自动调整，就只要在列位旁边加上金钱符号 ，如【A$1】。

想要确认自己是不是弄清楚【位置】的概念，不妨来玩一个小测试：

你可以再C3设定一个公式，再经过复制黏贴上，不用手工修改公式，就完成[九九乘法表]吗？没有位置观念的人会直接输入[=C2*B3],要是直接往下和往右复制公式，Excel的自动调整公司行业功能，会导致所有该相乘的数字都跑掉，无法得出九九乘法表应该有的结果。 那究竟怎么设定公式才会正确？其实只要掌握一个事就好；【不管公式估值到哪里，永远用B行的数字和第2列的数字相乘】，所有看到行号是B的就使用金钱符号，列位是2的也用金钱符号固定住就好了。

3.单一/多重行列排序

---

把资料分门别类排列整齐，让业绩一目了然

如果你的资料是随机输入的、没有特别整理过，通常很难从中看出数据的脉络，得出有助于商业判断的结果。Excel里最常被用到的资料整理功能，叫做【排序】，专门协助你将资料分门别类，将同类型的资料排列在一起，方便阅读。

1、单一列排序：依照业绩来排名

假如公司规定，只有当月业绩排名前3名才能平分奖金。那可以先按照每个业务员的销售金额高低进行排序，销售额越高的员工，名词越靠前，这种功能叫做【单一列排序】

2、多个列排序:在特定销售区域里，看出业务员的业绩排名

如果是因为第一个区的业绩增长最高，公司额外奖励一笔激励费用，那光按照销售金额排名还不够的。Excel里设有【多个列排序】，可以针对两个以上的分类来排序，也就是一次性将2个列都排出顺序来，让资料更细致的分类归纳。回到上述例子，主管必须将【销地区】作为第一层的排序分类，而同个销售地区的资料要以【销售金额】当做第二层的排序分类，由大到小的排序

4.自动筛选

从上万笔资料中，快速挑出需要的信息

面对上万笔资料，筛选绝对是你的好帮手。它可以快速挑出你想看的资料内容，而你不想要的资料则会被隐藏起来。 下面是一份人事资料表，里面包含公司里每个员工的编号、姓名、入职时间、工龄、生日等信息。身为人事主管，你要挑出几个人选，作为可能派往其他分公司的名单。

1、文字资料筛选：只显示户籍地为台中的员工资料

2、数字资料筛选：资历在两年以下的人选全部排除

5.SUMIF与COUNTIF

想指定项目计数和求和，用函数公式设计你的条件

学会输入和整理资料后，接下来就进入Excel的核心功能：数据运算。不过，有时候只想针对【特定条件】的项目做求和或者计数，这时候可以使用SUMIF和COUNTIF函数来实现。

SUMIF:只有符合条件的项目，才会被[加总]计算

SUMIF(数据范围，条件，要求和的区间)

COUNTIF:只有符合指定条件的项目，才会被计算【个数】

6.小计

---

不用函数也可以做运算，9步骤完成资料分类统计

还没学会许多函数，就要交报表，该怎么样先做简单的资料运算和整理？为了方便使用者建立资料的摘要，Excel内置【小计】的功能，可以帮你把资料分门别类、快速统计。完全不需要用到任何函数!

1、小计：一次算出各部门的申请总额

①将资料按照[申购部门]进行排序；

②点选[数据]功能选项卡中的[分类汇总]；

③设定分组：分类字段/小计设置为【申购部门】，这是告诉Excel请按照部门来分类资料，再进行统计；

④设定使用函数为【求和】；

⑤新增小计位置勾选【求和】；

⑥勾选【替代当前分类汇总】(取代当前小计)；

⑦视情况勾选【每组数据分页】；

⑧勾选【汇总结果显示在数据下方】;

⑨按下【确定】，完成分类汇总。

7.枢纽分析

精通枢纽功能，8成分析需求迎刃而解

要想再精进Excel技巧，别着急投入研究复杂的函数，而是要先弄清楚Excel内置的【枢纽分析】，就是excel表格中的数据透视表。这个项目操作起来简单、方便上手，但功能却十分强大，可以说是Excel最重要的精髓。几乎可以解决8成的Excel分析需求，帮你洞察资料内真正有意义的信息。

1、建立枢纽分析表:设定资料分析的范围，建立新的工作表

假定A公司需要请销售部分做一次市场调查，以决定是否进入新的行业。

4步完成透视表的新建；【快捷键为ALT-D-P】

①在【插入】中选择【数据透视表】； ②确认数据范围； ③选择放置分析表的位置； ④确定

2、设定枢纽分析表的组成：选择需要的字段，摆到对应的位置

枢纽分析表分为上下两个区块，上半部是勾选想出现在报表上的资料字段，下半部分则是你希望资料出现在报表的哪个位置。

3、枢纽分析表的资料分组：时间/金钱等数字资料，可以合并为一组

8.数据可视化

---

把枢纽分析表做成图，变身互动式图表

Excel和其他Office系列的软件一样，都有内置的绘制图表的功能，可以将数字表格化为可视化图表。

1、建立枢纽分析图：在一般工作表中，就能建立新图表

2、设定枢纽分析表的组成：将字段拖放到对应的位置，作出想要的图表

3、整理枢纽分析图：运用筛选功能，过滤不想看到的字段

9.地图功能

---

数据与地图对照，利于比较、掌握各地概括

如果你想利用Excel图表呈现某产品在全球的销售概括，但是又嫌直线图、饼图过于简单，那么你可以适应office365订阅版本中内置的Excel[地图]功能来绘制视觉化图表，用地理位置来对照数值或类别，更容易理解与沟通。

以上为本章介绍的9个Excel小技能，快快动手实际操作起来吧。

,