返回目录:excel表格制作
Excel也可以非常有趣,五个趣味案例让你重新认知数据之美
- 一个容易走火入魔的点:引用类型(相对引用、绝对引用、混合引用)!
- 九九乘法口诀表的制作方法!
- 三招走天下,三表概念(参数表、源数据表、汇总表)!
- Excel单元格中为什么只显示公式不显示计算结果?
- 函数不一定是解决问题的最佳办法!
一个容易走火入魔的点:引用类型(相对引用、绝对引用、混合引用)!
引用类型是公式与函数中一个容易走火入魔的点,如果没有理解引用类型,很容易写出错误的公式。
相对引用就是公式随着单元格的变化而变化,引用的地址不固定,绝对引用就是单元格固定不变。
绝对引用前面有个$,相对引用则没有,混合引用就是行与列一个是相对引用,一个是绝对引用。
利用F4键可以灵活切换相对引用和绝对引用。
对于初学者,可以这样去记忆,“有钱能使鬼推磨”,有$就是绝对引用,一心一意跟着你不跑,没有$就是相对引用,像墙头草随风倒。
例如,要计算各个业务类型收入占总收入的比重,C2输入公式=B2/B6,单击C2右下角+,往下拖动公式,返回#DIV/0!,如图3-8-6所示。
图3-8-6
单击公式菜单的显示公式,我们看到C列公式分子在变化,分母也在变化,如图3-8-7所示,而这里引用的地址是B6单元格合计数,需要固定不变。
图3-8-7
因此,我们C2单元格公式需要改为=B2/$B$6,单击C2右下角+,往下拖动公式,得到图3-8-8所示结果。
图3-8-8
这里公式分子就是相对引用,分母是绝对引用,再来看看混合引用,就是行或列中有一个是相对引用,另一个是绝对引用。
我们看看下面这个例子,要求不同类别每月占小计的比重,如图3-8-9所示。
图3-8-9
公式的分子用相对引用,分母用了混合引用,列固定行不固定,因为小计都在H列,不同类别的小计位于不同的行,如图3-8-10所示。
图3-8-10
九九乘法口诀表的制作方法!
上面介绍了引用类型,本招我们一起来制作九九乘法口诀表,步骤如下:
Step1 在B1:J1和A2:A10单元格区域输入数字1~9。
Step2 在B2单元格输入公式=B$1&"x"&$A2&"="&B$1*$A2,拖动B2单元格右下角黑色+往右再往下复制公式到B2:J10单元格区域,这样就可以得到一个简单的九九乘法口诀表,如图3-8-11所示。
图3-8-11
公式中B$1表示对行绝对引用对列相对引用,$A2表示对列绝对引用对行相对引用,用连接符&分别连接B$1、“x”、$A2、“=”,以及B$1*$A2的计算结果。
三招走天下,三表概念(参数表、源数据表、汇总表)!
实际工作中公式引用经常需要跨工作表或跨工作簿引用。
把Excel看作一个系统,这个系统由三表组成,何为三表?即参数表、源数据表、汇总表。
第一张表:
参数表配置参数,如图3-8-12所示,供源数据表和分类汇总表调用,属于基础数据。
设置参数表的好处是如果相关参数发生变化,不用修改汇总表的公式。
图3-8-12
第二张表:
源数据表可以编辑录入数据,我们日常工作最主要就是做好源数据表,如图3-8-13所示。
图3-8-13
第三张表:
Excel工作的最终目的是得到分类汇总结果,第三张表就是分类汇总表,如图3-8-14所示。
汇总表公式引用了参数表和源数据表的数据。
图3-8-14
Excel单元格中为什么只显示公式不显示计算结果?
Excel单元格中为什么只显示公式不显示计算结果?有两种可能:单元格是文本格式;选取了显示公式,取消方法是按组合键【Ctrl+~】。
我们来看看第一种情况,下表的B8单元格只显示公式不显示计算结果,双击单元格B8,发现公式前面有个单引号,’=SUM(B2:B7),如图3-8-15所示。
图3-8-15
这个单元格格式是文本格式,这种情况只要删掉单引号,或者右键单元格格式,把“文本”改为“常规”,如图3-8-16所示。
图3-8-16
我们再看看第二种情况,单元格格式是常规,但是还是只显示公式,这是因为选取了显示公式,要取消显示公式。
方法一:
组合键【Ctrl+~】。
方法二:
直接找到菜单(公式→公式审核→显示公式),取消“显示公式”,如图3-8-17所示。
图3-8-17
函数不一定是解决问题的最佳办法!
如图3-8-18所示,要求不同国家最低单价,通常情况我们用函数MIN解决,但是当国家数量有200多个,一个个写公式,非常慢,最简单的方法是用数据透视表。
图3-8-18
插入“数据透视表”,把A列国家名称拖到行标签,C列单价拖到数值,把默认的计数改为最小值,如图3-8-19所示。
图3-8-19
这个例子告诉我们遇到问题要学会打破常规思维,学会“偷懒”,用最简单方法解决。