搜索
从一张报表入手学做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+C12)绝对引用，复制公式时地址不会跟着发生变化，如：C1单元格有公式：=$A$1+$B$1当将公式复制到C2单元格时仍为：=$A$1+$B$1当将公式复制到D1单元格时仍为：=$A$1+$B$13)混合引用，复制公式时地址的部分内容跟着发生变化，如：C1单元格有公式：=$A1+B$1当将公式复制到C2单元格时变为：=$A2+B$1当将公式复制到D1单元格时变为：=$A1+C$1在本报表中，所有同一行中的金额公式都是单价乘以数量，而单价一直固定在H列不变，所以，可以在单价的列号前加上绝对引用，以保证其所在的列保持不变。在