Excel应该这么玩——3、命名表格:彻底改造公式

上一篇介绍了通过命名列将公式中的地址引用去掉,让公式更容易理解。但是之前的命名列只是针对单个表格的,如何引用其他表格的列是本篇的主要内容。
1、创建命名表格
命名表格的前提的创建表格,这个操作上一篇已经介绍过了,选中数据区中的一个单元格,按Ctrl+T即可。本篇以产品销售为例,先创建产品表,如下:
然后定位到菜单的公式->名称管理器:
在弹出的名称管理器对话框中选中刚创建的表格对应的项,点击上方的编辑按钮:
修改表格名称:
编辑名称对话框中可以看到表格的引用位置是从A2到C5的区域。如果插入新的数据行或数据列,引用位置会自动扩大。当然,删除行或列的时候引用位置也会缩小。如下图,在表格的右下角会有一个蓝色标记,用来标识表格的有效范围。
如果因为某些原因表格没有覆盖全部数据(例如空行,最好避免出现空行),可以用鼠标拖动蓝色标记来扩大表格范围,如下图:
2、创建销售表
创建销售表的过程省略(可以和产品表在不同的Sheet),要求输入货号的时候带出名称和价格,输入数量后得出小计。表格如下:
3、编写公式
用最常见的方式实现通过货号查找名称的方式是:
其中VLOOKUP第一个参数是要查找的单元格;第二个参数是查找区域,通过Sheet名称和列引用;第三个参数是要取值的列;第四个参数指示是否模糊匹配。
采用命名列和命名表格的方法修改公式,将VLOOKUP第一个参数改成命名列,第二个参数改成命名表格,公式马上变得更容易理解:
4、优化公式
上面的公式中存在一个数字2,表示查找产品表中的第2列。看到这个公式之后,先要看一下产品表的第2列是什么才能明白含义。如果在第2列之前插入一列,这个公式就会发生错误。如何让公式更容易理解并且避免列调整之后发生错误,解决方法还是采用命名列。
这里将数字2换成了COLUMN(产品[名称]),这个公式表示查找”产品“表的”名称“列的列号。由于采用名称引用,即使列发生调整也不会发生错误,也更容易理解。
5、小结
前面三篇文章要表达的意思是一致的,就是要将不容易理解的数字、地址引用改成通过名称来引用,提高公式的可读性。好比约文艺范的朋友逛广州方所,你可以给他经纬度,一般人打死也找不到地方。但是告诉他在地址是广州市天河路383号太古汇MU楼35号铺,这样就能够找到了。要记住公式是给人看的,只要语法没有错误电脑都可以理解,但公式的逻辑是否正确需要人来判断。
 
小技巧
输入公式时,Excel会列出几个候选项,当还没输入完整个公式,候选项只剩一个时,按“Tab”键,会自动补齐剩下的字母。
输入完公式名称时,按Ctrl+A,会弹出函数参数对话框,如下图:
关于VLOOKUP最后一个参数,这里给的注释是错误的。为FALSE时精确匹配,为TRUE时模糊匹配,使用时需要注意。
 

 

 

posted @ 2014-10-13 21:38 Alex Leo 阅读(...) 评论(...) 编辑 收藏