「全网首发」EXCEL结合记事本处理多条件求和大数据

原创 kaixinit  2018-07-26 12:10  阅读 1,291 次

关键还是工作思路,结合实际工作要求进行运用,举例说明:

需要三个表,日常录入数据的原表目录,记事本,EXCEL公式模板(必须设置为文本格式)

1、首先根据日常录入数据的表格目录需求写公式,要求是你EXCEL公式模板的列和日常录入数据的列必须保持一致如:

A B C D E F
日期 上年末欠款额 1月采购量 1月采购额 1月付款额 1月欠款额
2018年1月

2、根据上述目录需要在指定的日期进行取数求和,用到的函数为SUMPRODUCT,取工作表表名为001的,2018年1月份的采购量数据,A列为日期,C列为采购量明细,取数数据行为第二行,公式如下:

C采购量=SUMPRODUCT(('001'!A:A>=--"2018-1-1")*1,('001'!A:A<=--"2018-1-31")*1,('001'!C:C))

3、欠款额公式为上年末欠款额+1月采购额-1月付款额,日常写法是

1月欠款=B+D-E,这样写法不方便做记事本的数据替换,写法取用绝对值,这样方便文本替换,公式如下:

1月欠款=B*1+D*1-E*1

4、1月采购量至1月欠款额公式如下:

2018年1月采购量 2018年1月采购额 2018年1月付款额 2018年1月欠款额
=SUMPRODUCT(('001'!A:A>=--"2018-1-1")*1,('001'!A:A<=--"2018-1-31")*1,('001'!C:C)) =SUMPRODUCT(('001'!A:A>=--"2018-1-1")*1,('001'!A:A<=--"2018-1-31")*1,('001'!D:D)) =SUMPRODUCT(('001'!A:A>=--"2018-1-1")*1,('001'!A:A<=--"2018-1-31")*1,('001'!E:E)) =B2*1+D2*1-E2*1

5、记事本作用,做数据替换,把上述数据4复制到记事本内
=SUMPRODUCT(('001'!A:A>=--"2018-1-1")*1,('001'!A:A<=--"2018-1-31")*1,('001'!C:C))=SUMPRODUCT(('001'!A:A>=--"2018-1-1")*1,('001'!A:A<=--"2018-1-31")*1,('001'!D:D))=SUMPRODUCT(('001'!A:A>=--"2018-1-1")*1,('001'!A:A<=--"2018-1-31")*1,('001'!E:E))=B2*1+D2*1-E2*1

6、在把记事本5数据粘贴到录入数据目录指定列内,就可以正常取数据了!

7、总结:如写2018年2月的数据,把2018年1月的数据复制到记事本,然后做替换,把2018-1替换为2018年-2,2019年的公式,可以把2018年1月至12月的数据复制到记事本,做替换取绝对值如2018- 改为2019-,替换完成后在把欠款额的公式重新写下上月欠款额+本月采购额-本月欠款额

本文地址:http://www.kaixinit.com/info/office/713.html
版权声明:本文为原创文章,版权归 kaixinit 所有,欢迎分享本文,转载请保留出处!

发表评论


表情