.hd-box .hd-fr

Excel 三种解决日常工作中金额分配的办法

2022-10-27 14:27秋叶Excel(明镜在心)2评

原文标题:《月底偷偷用了这 3 个小技巧,我再没为 Excel 加过班……》

在众多使用 Excel 的职业当中,会计可谓是天天都要和表格打交道。

尤其是到了月底和月初的时候,更是表也多,头也大。

天天加班那是日常便饭。

其中,有一项工作就是对金额的分配

比如,下面这个小例子,需要将第一列中的金额按第二列的月份分配在后面的月份当中:

例如:第二行 3500,需要按 3 个月分配在后面的三个月当中,2022 年 1 月份分配 3500,2022 年 2 月份分配 3500,2022 年 3 月份分配 3500,超过 3 个月的,就不分配金额了。

按照这个思路来分配金额,该怎样来操作呢?

下面就来看看有哪些小妙招可以解决这个问题吧!

IF 函数 + MONTH 函数

解决这类问题,主要是利用条件判断函数作为主体思路。

如果条件成立,就返回某一个值,否则返回另一个值。

用公式表达就是:

IF(条件,条件成立时返回的值,条件不成立时返回的值)

完整的公式如下:

=IF(MONTH(C$1)<=$B2,$A2,"")

公式解析:

IF 条件判断函数有三个参数,

第一参数:MONTH (C$1)<=$B2

先用 MONTH 函数取出第一行中的【C1】单元格中的月份数字,然后再与前面的【B2】单元格中的数字进行比较。

如果取出的月份数字小于等于【B2】单元格中的值,就返回第二参数:【A2】单元格中的金额;否则就返回第三参数:空文本("")。

MONTH (C$1):返回【C1】单元格中的月份数字 1。

公式在向右拖动的时候,会变成 MONTH (D$1),返回【D1】单元格中的月份数字 2,以此类推!

使用该方法,需要注意以下三点:

❶ 此方法在用 MONTH 取月份的时候,【C1】单元格中必须为真日期或者假日期

比如,此题即使用的是文本型日期(即:假日期形式)。

❷ 注意其中混合引用的使用。

比如:C$1 中的行号要固定。$B2 和 $A2 中的列号要固定,

❸ 如果日期是下面这样:

看上去是日期,其实单元格中是文本:1 月,那么此时就不能使用 MONTH 函数了。

如何解决这个问题呢?可以用下面的方法。

IF 函数 + COLUMN 函数

此种方法利用 COLUMN 函数返回单元格的列号进行判断,可以完美解决方法 1 中的不足。

而且无论第一行是否是日期,还是非日期,都可以使用。

完整公式如下:

=IF(COLUMN(A1)<=$B2,$A2,"")

公式解析:

此方法使用 COLUMN 函数取出【A1】单元格中的列号,然后与【B2】单元格中的数字进行比较,如果条件成立(小于或等于),就返回【A2】单元格中的金额,否则返回空文本("")。

此题巧妙地利用了 COLUMN 函数进行列偏移,相应返回判断所需要的数字。

比如:

在公式向右拖动的时候,COLUMN (A1) 返回 1,COLUMN (B1) 返回 2,以此类推。正好与 B 列中的数字进行比较。

与 COLUMN 用法类似的还有它的兄弟 ROW,是获取单元格的行号。

它们的应用范围非常广泛,小伙伴可以好好体会下!

IF 函数 + COUNT 函数

COUNT 函数用于计算数字的个数,此处也可以利用它来解决此问题!

完整公式如下:

=IF(COUNT($B2:B2)<=$B2,$A2,"")

公式解析:

此处使用 COUNT 计数函数来统计在【$B2:B2】单元格区域中的数字的个数,如果小于【B2】单元格中的数字,就返回【A2】单元格,否则返回空文本("")。

在公式向右拖动的时候,会变成:

COUNT($B2:C2)

此时统计的是【$B2:C2】这个单元格区域中的数字,返回数字 2,此处的区域在不断地扩大,类似循环引用。即前面公式生成的结果作为后面公式的参数使用。

这种区域由小不断变大的用法也很广泛。

比如,在日常工作中碰到需要累计求和的时候,我们就可以尝试使用这个思路哦!

小伙伴们可以多研究研究!

写在最后

今天分享了三种方法来解决日常工作中金额分配的问题,

❶ IF 函数 + MONTH 函数;

❷ IF 函数 + COLUMN 函数;

❸ IF 函数 + COUNT 函数。

每种用法都很巧妙。

你是否有其他的解法呢?

今天给大家留的思考题是:

如果此题用【IF 函数 + SUM 函数】来解决,思路又是什么呢?

好了,欢迎大家留言写出你的答案哦!

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:明镜在心 编辑:小音、竺兰

广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。

下载IT之家APP,分享赚金币换豪礼
相关文章
大家都在买广告
热门评论
查看更多评论