公众号回复2016 下载office2016 每一个好用的模板,背后都是一些基础的积累和技术的沉淀! 报销单是很常见的一类表单,有时候为了管理方便,会在Excel里制作一个电子版的报销单,格式大概是这样的: (需要下载这个模板的可以在公众号后台回复:报销单) 看起来很方便,但是有一个问题,最下面的合计金额(标黄部分)如果能根据每一行的金额自动计算就完美了。 如何设置这里的公式,就是老菜鸟今天要和各位分享的内容了。 先看看完成后的效果吧: 随着金额的录入,下面的合计会自动更新。 要实现这个效果,需要解决三个问题: 1、对分列显示的数据求和; 2、将求和后的数字分列显示; 3、将分列显示的数字转为大写金额。 下面分别来看如何实现。 要对分列显示的数据求和,公式并不是唯一的,例子中用的公式是: =SUM(IFERROR(F5:M14*10^{7,6,5,4,3,2,1,0},0))% 在这个公式中,用到了SUM函数(求和)和IFERROR函数(排除错误),还有一个常量数组{7,6,5,4,3,2,1,0}。 公式的原理很简单,因为表格中最多有8列数字,也就是数据区域F5:M14,每一列的数字乘以10的n次方,n随列数的增加对应减少,因为左边的是高位,右边是低位。 10^{7,6,5,4,3,2,1,0}就是这个作用 遇到错误值直接变成0,这样就会得到一组数字,后面来个%表示将数字缩小一百倍,因为角和分是小数部分。 最后用SUM函数求和就得到所需结果。 对于这部分如果不理解,有两个办法: 直接套用公式,或者从基础开始学一下公式。 求和结果有了,接下来解决第二个问题,如何把求和后的数字分列显示。 还是给出一个现成的公式吧: =LEFT(RIGHT(" ¥"&$O$5*100,9-COLUMN(A1))) 这个公式同样不是唯一的方法,需要重点说明的是,在人民币符号前面是有个空格的,单就这个公式而言,涉及的函数都很基础,LEFT从左向右提取,RIGHT从右向左提取,COLUMN得到列号。 一些很简单的函数组合起来就能得到挺不错的效果,关于这个公式的原理,解释起来还比较费字,还是那句话,直接套用或者用F9自己去拆解,这是学习公式的一个方法。 接下来是最后一步,得到大写金额。 这个公式的套路就更多了,百度一下就有很多,以前也分享过这方面的内容,有兴趣的可以看看链接: 【Excel公式教程】小写金额转换为大写金额的4个公式套路解析 本例使用的是程大管家提供的一个思路,比较简洁明了: 公式分为三部分:前面的文字算是固定开头,第一个TEXT的作用是将整数部分变成大写数字,第二个TEXT的作用是将小数部分转为大写数字并以角和分显示。 具体含义的话需要对TEXT函数有比较全面的了解才行了。 完成以上三步之后,将O5单元格设置为白色字体,起到隐藏的效果就行。 本文旨在说明解决问题的思路和大致方法,公式都可以直接套用。 要想具备自己独立解决问题的能力,那就需要一段时间的学习才行了,推荐一本学习函数的教程《菜鸟的Excel函数修炼手册》: |
|