Excel教程:你会库存台账结余汇总吗?

今天跟大家一起分享一个某学员遇到的关于库存台账结余汇总统计的案例。

简单介绍一下这位学员的问题和需求。

这位学员的工作主要是统计管理各种型号产品的库存台账。现在需要将大量型号产品库存信息表中的结余数量汇总到一个工作表中。

如下图:

如GHE、AA、600分别是三种不同型号产品,E列中的最后一行为该产品的最新结余情况。(注:各产品型号表中的格式一致)

现在需要在汇总表中以产品型号为行,统计每个产品最后结余情况。

如下图所示:

我们要达成这样的需求其实有2个问题点。

1、将各个产品结余明细工作表中的数据引用到汇总表中。

2、如何才能返回产品型号表中的最后结余情况。

下面我们就带着这两个问题一起来解决这位同学的需求。1、既然用到引用那么我们就必须要会用indirect函数,其主要作用为返回文本字符串所指定的引用。

举例:

A列为工作表的名称,通过将A2单元格中的文本字符与!B2合并构建一个引用。相信大家在平时工作中使用跨表匹配的时候一定很熟悉,目标单元格引用均由工作表名称+感叹号+单元格名称组成,比如:GHE!B2。

这里我们就可以通过INDIRECT(A2&"!B2")函数公式直接返回GHE工作表中B2单元格的内容。

我们再看看GHE工作表中B2单元格的内容是不是GHE。

我们看到GHE工作表中B2单元格的内容的确是GHE。

3、第二个问题是如何返回最后结余情况。

以GHE工作表为例我们最终目的是要返回表中的E7单元格内容,而且需要随着行数变化而变化。

看到这里相信很多人都会想到用offset函数来完成

Offset函数功能为以指定的引用为参照系,通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。Reference作为偏移量参照系的引用区域。Reference必须为对单元格或相连单元格区域的引用;否则,函数OFFSET返回错误值#VALUE!。

如下图:

函数公式:=OFFSET(B2,5,3,1,1)

含义:以GHE工作表所在的B2作为参照单元格,向右偏移3列,向下偏移5行即可返回E7单元格最终的结余数。函数公式中的5表示第五行,3表示第三列,最后两个参数为1表示只返回一个单元格内容。

下面我们我只要将函数公式与第一步中的indirect函数公式完成嵌套:

=OFFSET(INDIRECT(A2&"!B2"),5,3,1,1)

静态的数据返回做好了,那么如何做到随着行数的变化而随时变化呢?

因为表中A列的日期与E列结余是对应的,这里我们巧妙的将行数用count函数来代替,通过count函数统计A列数值单元格数量来作为OFFSET的第二个参数。这样我们就能做到随着行数变化随时统计对应的最终结余数据。

函数公式为:COUNT(INDIRECT(A2&"!A:A"))+1,加1的原因是由于GHE工作表A列中只有4个单元格为数值,而在上个案例中我们需要向下偏移5行,所以我们需要这基础上加1来补充到。

最终函数公式:

=OFFSET(INDIRECT(A2&"!B2"),COUNT(INDIRECT(A2&"!A:A"))+1,3,1,1)

我们再来简单总结梳理一下:

本案例中主要的难点在如何引用指定列的最后一行数值,这里我们使用了offset、indirect函数完成了指定数据的引用,同时通过count函数完成了对指定单元格动态更新查找。最终实现了更具产品名称快速统计返回对应库存台账的最终结余情况。

****部落窝教育-excel库存台账结余汇总****

原创:龚春光/部落窝教育(未经同意,请勿转载)

(0)

相关推荐

  • 你一定还不会跨工作表求和,快来这里看看吧!

    送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 有这样一个问题: 某品牌的店面在不同区域的销售情况如下表.这些店面分属于两个区域.每个门店表格的格式完全相同. ...

  • 值得收藏的Excel函数公式

    值得收藏的Excel函数公式

  • Excel教程:库存台账结余汇总

    今天跟大家一起分享一个某学员遇到的关于库存台账结余汇总统计的案例. 简单介绍一下这位学员的问题和需求. 这位学员的工作主要是统计管理各种型号产品的库存台账.现在需要将大量型号产品库存信息表中的结余数量 ...

  • 你会库存台账结余汇总吗?

    简单介绍一下这位学员的问题和需求. 这位学员的工作主要是统计管理各种型号产品的库存台账.现在需要将大量型号产品库存信息表中的结余数量汇总到一个工作表中. 如下图: 如GHE.AA.600分别是三种不同 ...

  • 【Excel教程】如何在Excel中按月汇总数据,公式和操作都告诉你!(文末赠书)

    公众号回复2016   下载office2016 对于使用Excel处理数据的伙伴们来说,各种统计汇总工作是日常必须面对的问题,关于汇总的函数,我们也学习了不少,求和用SUM,单条件求和用SUMIF, ...

  • Excel教程:产品核价单 合并单元格求和汇总

    这是一个伙伴的产品核价清单Excel文件,左边是源数据,右边是做好的效果.需要解决两个问题,如下: 1. D列总面积,根据A列单元格大小进行合并. 2. 在D列合并单元格计算总面积,也就是对应的C列面 ...

  • Excel教程:文本数字混杂的单元格提取数字并汇总

    公众号回复:入群,下载练习课件 从文本中提取数字汇总的方法很多,可以根据自己熟悉的方法来解决就好.本文分享两种方法,如下. 第一,快速填充法 借助辅助列,在C2.C3单元格手动输入数字,然后下拉快速填 ...

  • Excel教程:sumif函数条件求和公式案例:成绩表求和汇总

    SUMIF函数是Excel中使用频率很高的一个函数,主要用于对区域中符合指定的单个条件的值求和. sumif函数有3个参数,语法为:=SUMIF(条件区域,指定的条件,需要求和的区域) 如果第三参数省 ...

  • Excel教程:常用的Excel统计函数汇总

    Excel表格数据统计常常都会用到,有了统计函数,分析数据也轻松不少.那么有哪些统计函数呢?下面给大家分享Excel常见的统计函数汇总. 1.max和min函数 Max是求最大值函数,在单元格中输入函 ...

  • Excel教程:仅用2个函数搞定一张自动统计库存和利润的进销存表

    编按: 哈喽,大家好!同事小钱将开始下班摆摊赚第二份收入.他想每天都知道自己赚了多少.小本生意,用不着大会计,或者专门的记账软件,只需要做一张简单的Excel进销存表就能赚亏一目了然.表里会用SUMI ...

  • Excel教程:不会函数也能汇总数据

    合并计算这个功能在excel各个版本都有,能够帮助用户将单元格区域中的数据,按照项目的匹配,对同类数据进行汇总,汇总的方式包括求和.计数.平均值.最大值.最小值等等.不会函数的同学也能够轻松实现数据汇 ...