使用Excel创建简单的进销存系统

大家好，今天小编来为大家解答使用Excel创建简单的进销存系统这个问题，很多人还不知道，现在让我们一起来看看吧！

朋友的小伙伴电脑操作和Excel数据管理能力较弱。他前期购买了专门的进销存软件，但经常把数据弄乱，所以他放弃了，希望用Excel万无一失地记录销售情况。

框架设计

01、进货记录表：这是整个进销存报表的基础。所有字段均从购买记录生成。

如图所示，购买记录表单采用智能表单。表单区域会随着数据的填写而自动扩展，并延续设定的格式。该报表中各个字段的内容需要手动输入，因为它们是后续销售记录表下拉菜单的数据源。

为了防止数据输入错误，对[购买价格]、[定价]、[数量]进行了“数据验证”设置。如图，要求只能输入数字，不能输入其他形式的数值。

同时设置错误警告，提示用户根据早期测试中常见的错误修改输入的错误内容。

对于J列中的日期，使用日期函数将A、B、C列中输入的年、月、日转换为标准日期。

公式为=DATE([@年],[@月],[@日])。请注意，由于数据区已转换为智能表，因此公式中的引用是结构化引用。

02、销售记录表：这是进销存报表的主要数据来源。

销售记录表的字段比较多，但是这个表的字段很多，填写起来也不是那么麻烦。有以下三种填写方式：

手动输入：A、B、C 栏的年、月、日需要手动输入。

下拉菜单选择：通过下拉菜单选择产品和型号。这些下拉菜单的数据来源是购买记录表中的数据。

自动生成公式：采购商品时已确定采购价格和定价字段。它们与产品有对应关系，因此通过公式自动匹配。=IFERROR(INDEX(产品列表区域，MATCH([@model],产品列表!B:B,0),4),\’\’)

03. 产品清单

该表不需要填写，是自动生成的，是一个过渡表。实际使用时可以将其隐藏。那么为什么我们需要这样一张表呢？

有以下几个原因：

使用Excel创建简单的进销存系统

为了使销售表中填写的产品信息与采购表中的产品信息保持一致，需要将采购表中的产品做成下拉菜单。但购买表是逐行记录，重复较多，不能直接使用；

还有其他信息，如采购价格、定价等，与采购表中的产品没有一一对应的关系。需要将它们排序为一对一的关系，以便使用匹配函数可以准确地匹配它们。

该表通过购买记录表创建数据透视表，实现去重和一对一关系。如图所示，是创建的产品列表之一。

那么，现在您知道购买记录表中刷新按钮的作用了吧？用于将新添加的购买记录刷新到数据透视表中，生成新的产品列表。因此，不刷新一次，数据透视表的源数据就会更新一次，产品列表也会发生变化。

那么如何将这个改变后的产品列表作为销售记录表中的下拉菜单呢？答案是使用动态区域函数Offset。

例如，我们为模型创建一个名称：

该名称指的是动态扩展的数据区域：

=OFFSET(产品列表!$J$1,MATCH(销售记录!$D2,产品列表!$J:$J,0)-1,1,COUNTIF(产品列表!$J:$J,销售记录!$D2), 1）

通过这个动态区域，总是可以获取数据透视表中的最新数据。因此，每次在购买记录表中输入购买信息后，点击刷新，菜单就会更新到最新。

04.库存报告

这是进销存报表的核心。在这张表中，序号显示了每日关键销售指标、每月关键销售指标以及详细的库存状况，并且这些数据可以按时间进行过滤。

该表中除时间数据外，其他数据均通过公式生成，无需填写。

我只给你举一个例子：

某产品上个月的余额数量。这是一个多条件求和公式。上月余额=上月购买量-上月销售额。

所以公式是这样的：

=SUMIFS(采购记录[数量],采购记录[产品],库存报告!B9,采购记录[年份],库存报告!$D$2,采购记录[月份],库存报告!$G$2-1) – SUMIFS （销售记录[数量]、销售记录[产品]、库存报告！B9、销售记录[年份]、库存报告！$D$2、销售记录[月]、库存报告！$G$2-1）