【Excel公式应用】用公式实现可以自动计算结果的报销单模板

公众号回复2016   下载office2016

每一个好用的模板,背后都是一些基础的积累和技术的沉淀!

报销单是很常见的一类表单,有时候为了管理方便,会在Excel里制作一个电子版的报销单,格式大概是这样的:

(需要下载这个模板的可以在公众号后台回复:报销单)

看起来很方便,但是有一个问题,最下面的合计金额(标黄部分)如果能根据每一行的金额自动计算就完美了。

如何设置这里的公式,就是老菜鸟今天要和各位分享的内容了。

先看看完成后的效果吧:

随着金额的录入,下面的合计会自动更新。

要实现这个效果,需要解决三个问题:

1、对分列显示的数据求和;

2、将求和后的数字分列显示;

3、将分列显示的数字转为大写金额。

下面分别来看如何实现。

问题1:对分列显示的数据求和

要对分列显示的数据求和,公式并不是唯一的,例子中用的公式是:

=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函数求和就得到所需结果。

对于这部分如果不理解,有两个办法:

直接套用公式,或者从基础开始学一下公式。

问题2:将求和后的数字分列显示

求和结果有了,接下来解决第二个问题,如何把求和后的数字分列显示。

还是给出一个现成的公式吧:

=LEFT(RIGHT(" ¥"&$O$5*100,9-COLUMN(A1)))

这个公式同样不是唯一的方法,需要重点说明的是,在人民币符号前面是有个空格的,单就这个公式而言,涉及的函数都很基础,LEFT从左向右提取,RIGHT从右向左提取,COLUMN得到列号。

一些很简单的函数组合起来就能得到挺不错的效果,关于这个公式的原理,解释起来还比较费字,还是那句话,直接套用或者用F9自己去拆解,这是学习公式的一个方法。

问题3:数字转为大写金额

接下来是最后一步,得到大写金额。

这个公式的套路就更多了,百度一下就有很多,以前也分享过这方面的内容,有兴趣的可以看看链接:

【Excel公式教程】小写金额转换为大写金额的4个公式套路解析

本例使用的是程大管家提供的一个思路,比较简洁明了:

="合计人民币(大写):"&TEXT(INT(O5),"[DBNum2]")&"元"&TEXT(MOD(O5,1)*100,"[DBNum2]0角0分;;整")

← 左右滑动查看完整公式 →

公式分为三部分:前面的文字算是固定开头,第一个TEXT的作用是将整数部分变成大写数字,第二个TEXT的作用是将小数部分转为大写数字并以角和分显示。

具体含义的话需要对TEXT函数有比较全面的了解才行了。

完成以上三步之后,将O5单元格设置为白色字体,起到隐藏的效果就行。

本文旨在说明解决问题的思路和大致方法,公式都可以直接套用。

要想具备自己独立解决问题的能力,那就需要一段时间的学习才行了,推荐一本学习函数的教程《菜鸟的Excel函数修炼手册》:

(0)

相关推荐

  • Excel小写转大写金额的几种方法

    这也是一个很常见的问题了:如何将小写金额转为大写金额.在Excel中有几种实现方法,适合不同场景.今天为大家分别介绍一下: 01 单元格格式设置 第一种方法是使用单元格格式设置. 选中需要转换格式的单 ...

  • 就因日报表一个小失误,领导将我批了半小时,紧急求助

    就因日报表一个小失误,领导将我批了半小时,紧急求助

  • Excel | 这几个公式,你一定要会

    清晨,与您相约 只要你用到Excel数据处理,这几个公式就一定要会: IF判断等级 评定成绩:如果大于等于85为优秀,大于等于70小于85为良好,大于等于60小于70为合格,小于60为不合格. 公式: ...

  • Excel提取唯一值公式大全!

    今天我们来给大家分享一下,关于Excel中使用公式去重的各种写法,大家除了关注方法,更多的是去思考思路! 对!我们的需求就是这么简单,重复的提取一个! 我们看看有多少种写法. 公式1 ▼数组公式,记得 ...

  • Excel是如何控制公式计算的

    通常,Excel默认的计算模式为"自动"计算,这可以从下图1所示的功能区"公式"选项卡"计算"组中的计算选项看出来. 图1 也可以从&quo ...

  • 在Excel中编辑复杂公式

    引子:今天轻松一点,介绍Excel中编辑公式的功能.这个功能对于我来说,一直没有什么太大的用处,但是最近开始学习线性代数和统计学方面的知识,其中有一些非常复杂的公式,才突然发现原来Excel中也可以很 ...

  • Excel模糊查找的通用公式来了,可惜又长又臭。。。

    与 30万 读者一起学Excel 模糊查找,是指内容不一样的查找,主要是因为偷懒导致的,本来应该写完整的,结果简写. 模糊查找可以划分成三大类,跟着卢子一起看看. 1.内容少的,查找内容多的 在查找的 ...

  • excel求平均值的公式-常用函数可快速找到

    其实,我不该写这篇文章,因为在excel里面,像是求和.计数.最大值等这几个是常用的公式函数,excel做的也比较人性化,可以快速找到并使用这些常的公式. 既然写了,还是要认真的写一下,这对新手来说还 ...

  • 1123:Excel之如何实现公式的锁定保护

    Excel之如何实现公式的锁定保护

  • Excel常用函数、公式和技能

    Excel常用函数、公式和技能

  • 职场必备的10个Excel工作表函数公式,易学易懂,中文解读

    职场办公中,经常要对表格数据进行分析处理,在此过程中肯定少不了一些函数公式,为此,小编转么整理了10个职场必备的Excel工作表函数公式,供大家参考学习! 一.Excel工作表函数:Datedif. ...