从一个报表学习excel.docx
- 文档编号:28641966
- 上传时间:2023-07-19
- 格式:DOCX
- 页数:44
- 大小:1.52MB
从一个报表学习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,操作起来比较简单:
选择要排序的数据区域。
这里可以选择除表头以外的所有数据区域。
然后选择菜单栏的开始/排序和筛选/自定义排
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 一个 报表 学习 excel