Excel 三种解决日常工作中金额分配的办法
- 秋叶Excel
2022-10-27 14:27
原文标题:《月底偷偷用了这 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之家所有文章均包含本声明。