从一个报表学习excel.docx
《从一个报表学习excel.docx》由会员分享，可在线阅读，更多相关《从一个报表学习excel.docx（44页珍藏版）》请在冰豆网上搜索。
从一个报表学习excel
从一张报表入手学做Excel表格
（1）（连载）
2008-09-0801:
09
一、准备工作：
新建工作簿，设置常用工具栏
1. 新建工作簿
在电脑的某一目录下，新建一个Excel文件，取名为“材料购入耗用结存月报表.xls”：
双击打开该工作簿。
修改工作表标签名称：
双击表格区域下方的Sheet1标签，使其处于编辑状态，将其改名为“月报”：
2. 设置常用工具栏
在Excel的工具栏中，有很多快捷的工具图标，制作表格时，可以直接点击这些图标，而不用到菜单栏中查找。
但是，这些工具图标也有很多不常用的，致使工具栏分成了2排甚至3到4排，不但缩小了表格区域，而且不易快速找到想要的工具：
为此，我们可以自定义这些工具栏，保留常用的工具图标，删除不常用的，以扩大表格区域，方便更好的制作表格。
>>基础知识自定义工具栏
方法有二：
1）点击工具栏中第一个图
标，在下拉菜单中选择“添加或删除按钮”，分别选择“常用”和“格式”，按如下图所示勾选相应项目：
2）右键点击工具栏的任意空白处，在弹出菜单中选择“自定义”，之后用右键点击工具栏上相应的工具图标，在弹出菜单中点击“删除”，把不常用的图标逐个删除。
最终工具栏上保留的图标如下图所示：
另外还有几个常用的快捷键，方便对表格进行编辑：
>>基础知识快捷键
剪切：
Ctrl+X；复制：
Ctrl+C；粘贴：
Ctrl+V；
撤销：
Ctrl+Z；恢复：
Ctrl+Y；
全选：
Ctrl+A；保存：
Ctrl+S;
复制上方单元格：
Ctrl+D；复制左方单元格：
Ctrl+R；
设置单元格格式：
Ctrl+1；手动换行：
Alt+Enter；
查找：
Ctrl+F；替换：
Ctrl+H；
打印：
Ctrl+P；另存为：
F12。
等等。
从一张报表入手学做Excel表格
（2）（连载）
2008-09-0901:
04
二、制作表头
一般情况下，要设计一张表格或者根据表样制作一张表格，首先要对其主体结构进行分析，设计出表格的主体框架，然后再输入具体内容。
千万不可开始就把所有数据一股脑都输进去。
对于本报表，拿到表样后，可以先从表头入手。
具体制作步骤为：
1. 输入表头的关键内容
如下图所示：
在录入过程中，尽量不要对表格格式进行调整（不要调整字体，不要急着给表格加上边框，不要合并表格标题的单元格等），这样可以加快录入速度，不易打断思路，而且只有在输入完这些文字后，才能对表格有一个整体的把握，避免工作的重复性。
当然可以对几个小地方的单元格进行合并。
例如表1级列标题中的“本月购买”，包括2级标题“合计”、“甲供”，“自购”，每个2级标题又包含“数量”、“金额”两个3级标题，这时，可以把1级标题的几个单元格合并，以使其包含到3级标题所占的几个列。
>>小提示：
在编辑表格过程中，一定要每隔几分钟保存一次，以免发生意外将所编辑内容丢失。
2. 调整单元格格式
包括：
合并单元格，调整字体，给表格加上边框等。
达到如下图所示的效果：
1） >>基础知识给表格加上边框
选择要添加边框的的单元格区域，然后单击工具栏上的边框图标
。
注意：
表格中原有的暗灰色网格线是为了方便观看而设置的，这些网格线并不在打印中出现。
2） >>基础知识合并单元格
选择要合并的单元格区域，然后单击工具栏上的合并单元格图标
。
效果如图所示：
>>基础知识格式刷
表中若要制作若干相同的格式，可以先设置好一个后，用格式刷将
其他单元格刷成同样的格式。
例如，表格的2级标题“合计”、“甲供”、“自购”等，均为将两个单元格合并成1个，这时，可先把第一个“合计”所在的两个单元格合并，然后点击工具栏上的格式刷
，再选择其他要合并的单元格，此时，每两个单元格就合并到了一起，如图所示：
>>小技巧双击格式刷
如果要进行格式刷的多个单元格并不在一起，可以双击格式刷
，此时鼠标就一直处于格式刷状态，可以对不同位置的单元格进行格式刷，全部完成后，按Esc键退出格式刷状态。
3） >>基础知识调整字体并居中
快捷方法：
因为表格的大部分内容的格式均为：
字体大小：
10号，对齐方式：
水平居中、垂直居中等，只有表格标题字体较大。
所以，可先把整张表格设置成与大部分表格相同的格式，然后修改个别单元格的格式。
具体方法为：
全选工作表，将字体大小设置为10号，并点击工具栏的居中图标
，同时为了表格更加美观，可以设置垂直居中。
然后对个别单元格进行调整：
选中表格标题单元格，将字体设置为粗体，并设置大小为16号；选择“单位：
”单元格，将其设置为左对齐；选择报表日期所在单元格，将其设置为右对齐。
等等。
>>快捷键：
全选表格：
Ctrl+A；或者点工作表区域的左上方：
4） >>基础知识设置垂直居中
选择要设置的单元格区域，然后在选择区域内点击鼠标右键（这是一个常识问题，凡是对单元格或单元格区域进行右键操作时，均必须在所选择区域的范围之内点击右键），在弹出菜单中选择“设置单元格格式”：
切换到“对齐”选项卡，将“垂直对齐”设置为“居中”，如下图所示：
（在本文以后的描述中，该过程简化为：
选择要设置的单元格区域，右键/设置单元格格式/对齐，将“垂直对齐”设置为“居中”，确定。
）
>>快捷键：
调出“设置单元格格式”窗口：
Ctrl+1。
5） >>基础知识设置竖排文字
报表表头中，“单据号”为竖向排列的文字。
其具体做法有两种：
a、选择该单元格，右键/设置单元格格式/对齐，在“方向”中选择竖向文本的图标，确定。
如下图所示：
b、双击该单元格，使其处于编辑状态，然后按Alt+Enter键，即可实现在单元格内文本换行。
本单元格中，分别在“单”和“据”字后面按Alt+Enter键，就实现了竖向排列。
从一张报表入手学做Excel表格（3）（连载）
2008-09-0916:
05
三、输入基本数据
1. 输入基本数据
基本数据主要有：
日期、单据号、名称、规格型号、单位、单价、上月结存数量、本月购买数量（甲供/自购等）、本月结存数量。
为了以后方便分类汇总，在表格中多加了一列数据：
材料类别。
这些数据可以人工输入，也可以从材料软件中导出，然后复制过来。
当然，也可以在软件导出的数据基础上进行表头的调整。
这里假设数据已经获取，并输入到表格中，如下图所示：
其他空白的数据可以通过函数与公式获得。
2. 调整行高、列宽
在输入数据的过程中，会发现许多单元格不够宽，导致其中的数据跑到单元格外面，或者被右面的单元格数据所覆盖，或者直接变成一排#号：
这时，就要对列宽进行调整。
>>基础知识选中整列或多列整列
将鼠标放于要选择列的列标上，当鼠标指针变成
时，单击鼠标左键，即可选择整列。
将鼠标放于一列的列标上，当鼠标指针变成时
，按住鼠标左键不放，向左或向右拖动鼠标，即可选中自该列开始向左或向右的几整列。
>>基础知识调整行高、列宽
调整的方法有以下几种：
a、 鼠标拖动调整
单列：
将鼠标放于表格两列标之间的边界上，此时鼠标变成左右箭头，按住左键不放，左右拖动鼠标，即可调整列宽。
多列：
如果想把相邻的几列调整成相同的宽度，可以同时选中这几列，然后将鼠标放于选中的几列中的任意两列标之间的边界上，按住鼠标左键不放拖动之。
b、鼠标双击调整
单列：
将鼠标指向此列列标右边界线，待鼠标指针变成左右的双向箭头时双击，可得到最适合的列宽，即列宽刚好容纳此单元格内最长的内容。
多列：
同样，如果想把相邻的几列调整合适的宽度，可以同时选中这几列，然后将鼠标放于选中的几列中的任意两列标之间的边界上，待鼠标指针变成左右的双向箭头时双击。
c、 输入具体列宽值调整
单列：
鼠标右键点击某列列标，在弹出菜单中选择“列宽”，在弹出对话框中输入列宽值，确定。
多列：
选中多列，然后进行相同的操作，即可同时将多列调整成相同的列宽。
调整行高的方法与调整列宽的方法类似，不再累述。
实际操作中，可根据具体情况，选择一种或多种方法相搭配进行行高或列宽的调整。
在后面的制表过程中，如果发现列宽太窄或太宽，也可以随时调整列宽。
3. 自动换行
如果某一列中个别单元格数据太长，若单纯调整列宽，将会使得表格太大，打印时不能容于一张纸上，此时就要对其进行自动换行设置。
>>基础知识自动换行
在此单元格上点击右键/设置单元格格式/对齐，勾选自动换行，确定。
然后配合调整行高，使数据完全显示在单元格内。
另外，也可以将单元格格式设置为“缩小字体填充”，以显示完整单元格内容。
也可以用在前面讲解“设置竖排文字”时所用到的手动换行方式：
Alt+Enter键，来实现换行的目的。
4. 冻结窗格
当表中填记录很多时，向下滚动屏幕才能继续查看、编辑数据。
而列标题也随之滚动出屏幕之外，需要不断的上下翻动屏幕，才能正确填写对应项目的数据，操作十分麻烦。
这是就需要用到冻结窗格。
>>基础知识冻结窗格
选择冻结窗格后，被冻结的行和列将保持在表格得上方和左方不动。
在本报表中，我想让“单价”及其以左的列（A到H列）不动，所有列标题及其以上的行（第1到5行）不动，则选中H列和第5行交叉的右下方第一个单元格（即单元格I6），点击菜单栏上的窗口/冻结窗格。
选择冻结窗格后，被冻结的行和列将保持在表格得上方和左方不动。
从一张报表入手学做Excel表格（4）（连载）
2008-09-0916:
51
四、函数与公式
（一）
函数和公式是excel的灵魂，Excel表格之所以强大并如此受欢迎，函数与公式功不可没，没有了它们，Excel恐怕只剩下了一副空壳，所以，要想学会Excel，就必须掌握函数与公式，以及它们之间的相互搭配组合。
函数和公式可以大大提高工作效率，降低错误率，甚至能够做到人工不能做到的事情。
单纯的一个函数或公式只能实现Excel最基本的计算与统计功能，如加减乘除、SUM（求和）、PRODUCT（求积）、AVERAGE（求平均）、IF（条件）等。
而多个函数之间的灵活组合，才能发挥出Excel更强大的功能。
当然，如此繁多复杂的函数，绝不是简单的一两篇文章所能讲述清楚的。
而对于这份报表，简单的几个基本函数就可以胜任。
所以，本文仅仅介绍本报表中所能用到的函数。
1. 加减乘除
在本报表“本月购买”中，“合计”的数量（K6）是由甲供和自购的数量（M6与O6）相加得到的：
具体操作方法为：
在K6中输入=，然后用鼠标点击M6单元格，再输入+，再点击O6单元格，即可得到公式：
=M6+O6，这就是两者之和两者之和。
用同样的方法可得到多个单元格数据之和，如=M6+O6+P6+N8等等。
“本月耗用”中的合计数量（Q6）也可以如此得到：
=S6+U6。
如果是两数据相减，把+变成–即可。
例如，“本月耗用”中的工程数量（U6）是由上月结存数量（I6），加上本月购买数量（K6），减去本月临建耗用数量（S6），再减去本月结存数量（W6）得到的，则在U6中输入公式：
=I6+K6-S6-W6。
如果是两数相乘，则把+变成*（乘号）。
例如，“上月结存”中的金额（J6）等于单价（H6）乘以上月结存数量（I6），则在J6输入公式：
=H6*I6。
同样，其他的金额也可以如此得到。
两数相除，则用到除号“/”。
例如：
=B1/C1。
注意：
这些公式必须在英文状态或关闭输入法的状态下进行，否则将导致出错
>>公式技巧相对与绝对引用单元格
在本报表中，所有的金额公式均为数量（其左方相邻的单元格）乘以单价（单元格H6）。
每次的金额都这样一个一个的输入公式，也是十分麻烦的。
绝对引用符：
“$”。
加上了“$”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加上“$”的列标和行号为相对地址，在公式向旁边复制时会跟着发生变化。
混合引用时部分地址发生变化。
例如：
1） 相对引用，复制公式时地址跟着发生变化，如：
C1单元格有公式：
=A1+B1
当将公式复制到C2单元格时变为：
=A2+B2
当将公式复制到D1单元格时变为：
=B1+C1
2） 绝对引用，复制公式时地址不会跟着发生变化，如：
C1单元格有公式：
=$A$1+$B$1
当将公式复制到C2单元格时仍为：
=$A$1+$B$1
当将公式复制到D1单元格时仍为：
=$A$1+$B$1
3） 混合引用，复制公式时地址的部分内容跟着发生变化，如：
C1单元格有公式：
=$A1+B$1
当将公式复制到C2单元格时变为：
=$A2+B$1
当将公式复制到D1单元格时变为：
=$A1+C$1
在本报表中，所有同一行中的金额公式都是单价乘以数量，而单价一直固定在H列不变，所以，可以在单价的列号前加上绝对引用，以保证其所在的列保持不变。
在“上月结存”的“金额”（J6）中输入公式：
=$H6*I6。
然后将该单元格（注意是处于非编辑状态下）复制到其他金额所对应的单元格中，就会发现，所有的金额均为其左方相邻的单元格乘以单价。
从一张报表入手学做Excel表格（5）（连载）
四、函数与公式
（二）
2. 保留指定小数位数：
Round函数
一般情况下，金额的最小单位是“分”，也就是说，“金额”要保留2位小数。
如果通过设置单元格格式，将数据显示为两位小数，那么在金额合计中，其仍然按照实际小数位数进行计算，这样，查看报表的人将莫名其妙合计中那些多出的1分2分是从何处而来。
这时，就要用到Round函数将数据四舍五入保留两位小数。
>>基本公式保留指定小数位数：
Round函数
语法：
ROUND（number,num_digits）
ROUND函数语法具有以下参数：
number必需。
要四舍五入的数字。
num_digits必需。
位数，按此位数对number参数进行四舍五入。
例如：
=round（23.456,2），将数据23.456四舍五入保留两位小数，结果为23.46。
在本报表中，本月结存的金额单元格（J6）中，输入公式：
=ROUND（$H6*I6,2），然后复制该单元格到复制到其他金额所对应的单元格。
3. 批量复制公式
经过上一步骤，我们就得到了数据区域中第一行的所有数据：
下面，将把第一行用公式得到的所有数据复制到其下的行中。
>>基础知识批量复制公式
主要方法有3种：
1） 拖拽复制
以上月结存中的金额（J6）为例，选中J6单元格，鼠标放于该单元格的右下角，鼠标指针变成黑色十字，按住鼠标左键不放向下拖动之，其经过单元格就会自动填充由J6得到的公式：
如果相邻两单元格数据均需要批量向下复制，则可选中这两个单元格，用同样的方法拖拽复制。
2） 双击复制
该方法的前提条件是：
该列的相邻列（左或右列）已有数据或文字，且相邻列中没有空白单元格。
例如本报表中上月结存金额（J6），其左方已有数据（数量），则选中J6，鼠标放于该单元格的右下角，鼠标指针变成黑色十字，双击之，数据就会自动向下填充，直到遇到相邻列里有空白单元格为止，如图所示。
3） 选择区域复制
仍以上月结存金额（J6）为例。
复制J6单元格（快捷键：
复制：
Ctrl+C），然后选择其下的所有单元格（如果该列单元格太多，可以先选中J7，然后鼠标移动到最后一个单元格（如J80），按住Shift键，同时按下鼠标左键选择之，这样可以选择从J7到J80所经过的所有单元格），之后粘贴（快捷键：
粘贴：
Ctrl+V），这样，J6的公式就粘贴到所选择的单元格中了。
用同样的方法，可以把其它列也进行复制，最终结果如图：
从一张报表入手学做Excel表格（6）（连载）
2008-09-0916:
57
四、函数与公式（三）
>>知识引申拖拽复制
Excel里的拖拽复制情况比较复杂，这里仅简单介绍几种较为常用的情况。
根据单元格的多少，可分为1个单元格的拖拽复制，和多个单元格的拖拽复制。
1） 一个单元格的复制
根据单元格中的内容大体可以分为：
数字型、文本型、公式型等。
a、公式型的复制上文已经讲到，即公式中绝对引用的单元格不随复制而改变，而相对引用的，将随着所复制到目标单元格的改变而改变。
b、文本类型的数据，如果文本中不含数字，则直接复制原内容。
如果含有数字，则将文本中最后一个数字依次自动向下加1。
例如：
A1单元格中内容为：
a1b1.1c，则向下拖拽后变为：
如果不想让单元格内的数字有变化，则可按住Ctrl键的同时进行拖拽复制：
需要注意的是，如果单元格内容是单纯的数字，但是单元格格式是文本类型的，也符合此规则。
b、数字型的数据，简单的拖动复制将不会改变单元格内容：
但是如果按住Ctrl键后拖拽，则数值自动依次向下加1，如：
2） 多个单元格的复制
a、 等差序列
例如A1单元格内容为1，A2里为3，选择A1和A2后，按住选中区域右下角的句柄向下拖动复制后，其所经过的单元格将以A2-A1的差值（3-1=2）进行等差序列填充：
选中区域：
，拖拽复制：
b、等比序列
例如A1单元格内容为3，A2里为9，选择A1和A2后，用右键按住选中区域右下角的句柄向下拖动，松开右键后，会弹出一个菜单，选择“等比序列”，其所经过的单元格将以A2-A1的比值（9/3=3）进行等比序列填充：
==〉
==〉
c、不改变内容复制
按住Ctrl键同时拖拽，则可保持数据不变：
==〉
从一张报表入手学做Excel表格（7）（连载）
2008-09-0916:
59
四、函数与公式（四）
拖拽复制应用举例：
填充报表中的序号
如果报表中要求填写序号，如下图所示：
表中有上百条记录，如果把1、2、3、4……一个一个输上去，将浪费不少时间。
这里有几种方法可以快速填充序号：
保证D列单元格的格式为“常规”。
1） 在D6单元格内输入数字1，选择该单元格，按住Ctrl键，同时鼠标点住选择框右下角的句柄，向下拖动复制到报表的末尾，即可填充好序号。
2） 在D6单元格内输入数字1，D7单元格内输入2，选中D6和D7单元格，同时鼠标点住选择框右下角的句柄，向下拖动复制到报表的末尾，即可填充好序号。
3） 以上两种方法均有一个缺陷，填充完序号后，如果在表中再加入一行，或改变行的顺序，序号就会被打乱，需要重新填充：
这里介绍一种公式：
row（）。
>>基本公式返回引用的行号row（）
语法row（[reference]）
Reference：
为需要得到其行号的单元格或单元格区域。
如果省略reference，则假定是对函数ROW所在单元格的引用。
如果reference为一个单元格区域，并且函数ROW作为垂直数组输入，则函数ROW将以垂直数组的形式返回reference的行号。
在这里，公式中省略Reference参数。
在D6单元格输入公式：
=row（）-5，将返回D6单元格所在的行号6，并减去数字5，结果为1：
将该单元格向下拖拽复制，即可填充序列号。
而且即使行打乱顺序，该列序号也不会改变。
如果从中间插入1行，只需要将其他行的序号公式复制到该行对应的空白单元格，又得到了排好序的序号。
插入空白行：
打乱行次序：
>>知识引申选择性粘贴
在单元格复制中，如果单元格内容是公式，其复制的结果将会因公式中所引用的单元格区域内容不同而不同，例如，在A6中有求和公式：
=SUM（A1:
A5），其将对A1到A5中的数据求和。
如果只想在B6里得到A6的结果（即15），简单的把A6单元格复制到B6是不行的（公式变成了=SUM（B1:
B5））。
这时就要用到选择性粘贴。
复制A6单元格，选择B6单元格，右键/选择性粘贴，选择“数值”，确定，即可得到数字15。
另外，“选择性粘贴”窗口中，还有其他很多选项，大家可以参考Excel的帮助文件（快捷键F1）进行进一步的研究。
4. 单价、金额设置为2位小数，并右对齐
单价、金额这些货币性质的数字，如果设置为2位小数（精确到分），并右对齐到单元格，将会十分方便观看。
选中这些数据区域，点击工具栏上的右对齐图标。
仍旧保持这些区域被选中，右键/设置单元格格式/数字，在分类中选择“数值”，小数位数设置为2位，“负数：
”选择“-1234.10”，确定。
如图所示：
从一张报表入手学做Excel表格（8）（连载）
2008-09-1115:
37
五、排序
初始数据的数据，材料类别、购买日期、材料名称均是乱的，没有任何次序可言，不方便统计，也不方便查看。
为此，要对报表按照一定的关键字进行排序。
这里，按材料类别作为主关键字排序，在同一材料类别下，按材料名称排序；相同材料名称的，按规格型号排序；以上均相同的，按采购日期排序。
>>基础知识排序
如果用的是Excel2007，操作起来比较简单：
选择要排序的数据区域。
这里可以选择除表头以外的所有数据区域。
然后选择菜单栏的开始/排序和筛选/自定义排
升级会员 