Excel进销存这么设计，一个公式简单求库存！

在公司中，进销存管理是一件经常要做的事，举一个简单的例子，下面是公司的物品出入库领取情况，如下所示：

第1列是发生的时间，第2列是发生的业务类型，是入库，还是出库，第3列是哪些物品，第4列是发生的数量。

每天的进出都这么登记，现在我们设计一个公式，可以直接汇总出库存。

?首先我们用一个公式，计算不重复的物品

在G2单元格输入公式：

=INDEX(C:C,SMALL(IF(MATCH($C$2:$C$10000&"",$C$2:$C$10000&"",0)=ROW($2:$10000)-1,ROW($2:$10000),4^8),ROW(1:1)))&""

因为是数据公式，所以按CTRL+SHIFT+ENTER进行计算，然后向下拖动

这个公式很复杂，很难理解，可以不用记，它的功能是提取不重复值的公式，碰到需要的时候，直接拿出来套用，这样的好处就是，当C列有新增一个物品时，新增的物品在G列也会直接出来。

?使用条件求和函数，分别进行入库和出库的数量

在H2单元格中输入公式：

=IF($G2="","",SUMIFS($D:$D,$B:$B,H$1,$C:$C,$G2))

在I2单元格中输入公式：

=IF($G2="","",SUMIFS($D:$D,$B:$B,I$1,$C:$C,$G2))

在J2单元格中输入公式：

=IFERROR(H2-I2,"")

向下填充公式，可以多填充几行，得到的结果如下所示：

现在问题来了，如果我们只想知道本月的入出库情况，以前的就期末盘存，或者说期初库存，这种形式，该如何设计公式？

我们在E列设置一个辅助列，判断时间是否是本月

输入的公式是：

=IF(YEAR(A2)&MONTH(A2)=YEAR(TODAY())&MONTH(TODAY()),"是","否")

然后在H2输入公式：

=IF(G2="","",SUMIFS(D:D,B:B,"入库",C:C,G2,E:E,"否")-SUMIFS(D:D,B:B,"出库",C:C,G2,E:E,"否"))

I2输入公式：

=IF($G2="","",SUMIFS($D:$D,$B:$B,I$1,$C:$C,$G2,$E:$E,"是"))

J2输入公式：

=IF($G2="","",SUMIFS($D:$D,$B:$B,J$1,$C:$C,$G2,$E:$E,"是"))

K2输入公式：

=IFERROR(H2+I2-J2,"")

这样这个表格里面统计的入库和出库，就只是本月产生的数量了，月初库存，也就是上个月的盘存情况了。

通过前面几篇 SQL语言教程 中，我们已说到这一篇要介绍的是Insert Into的另一种用法，也就是把检索出来的数据，批量添加到数据库中，其语法是：

INSERT INTO 表格名1 (列名11,列名12, ...) SELECT 列名21, 列名22, ... FROM 表格名2

以上的语法是最基本的语法。在Select后，还可能含有 WHERE、 GROUP BY、 及 HAVING 等子句，以及表格连接及别名等等。与Select Into对比，Select Into中目标表名应该在数据库中是不存在的，而 Insert Into Select正好相反，其目标表名在数据库中一定要存在的。

下面的这个例子，是把出仓表中的所有的钢笔数据，添加到上一篇文章http://www.excelba.com/Art/Html/310.html 生成的临时表的数据库中，如果你没有看到上一篇文章，请运行上一篇文章的程序生成临时表后，再运行下面这段程序。

Sub 进销存表数据库中出仓表里检索的数据添加到已有的表()

'*******************************************

'时间：2008-10-21

'作者：bengdeng

'功能：在程序文件同一目录下进销存表数据库中出仓表里检索的数据生成新表

'注意：要在工具/引用中引用microsoft activex date objects x.x

' 其中x.x为版本号，可能会因为你安装的office的版本不同而不同，本例引用了2.5版

'发布：http://www.excelba.com

'*******************************************

Dim conn As ADODB.Connection

Dim WN As String

Dim OldTableName As String

Dim NewTableName As String

Dim sSql As String

Dim tStr As String

WN = "进销存表.mdb"

OldTableName = "出仓表"

NewTableName = "临时表"

tStr = "钢笔"

Set conn = New ADODB.Connection

conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0" &_

"Data Source=" &ThisWorkbook.Path &"\" &WN

conn.Open

If conn.State = adStateOpen Then

sSql = "Insert Into " &NewTableName &" Select * From " &OldTableName &" Where 物品名称='" &tStr &"'"

conn.Execute sSql

MsgBox "成功把“" &OldTableName &"”中的所有的“" &tStr &"”记录汇总到“" &NewTableName &"”中！", , "http://excelba.com"

conn.Close

End If

Set conn = Nothing

End Sub

因为上一篇文章生成的临时表与出仓表的结构是完全相同的，因此正如上面的例子，我们不需要列出NewTableName中列明细资料，而OldTableName的列的资料，我们也可以用*来表示！

那么，如果列名不一样，能不能导入呢？当列的数据类型一致时，还是可以导入的，下面的这段 *** 作Excel文件的程序，功能是把进仓表的毛笔数据，导入到临时表中，其中进仓表的进仓日期对应着临时表的出仓日期，进仓数量对应着出仓数量，代码如下：

Sub 进销存表文件中进仓表里检索的数据添加到已有的表()

'*******************************************

'时间：2008-10-21

'作者：bengdeng

'功能：在程序文件同一目录下进销存表文件中进仓表里检索的数据生成新表

'注意：要在工具/引用中引用microsoft activex date objects x.x

' 其中x.x为版本号，可能会因为你安装的office的版本不同而不同，本例引用了2.5版

'发布：http://www.excelba.com

'*******************************************

Dim conn As ADODB.Connection

Dim WN As String

Dim OldTableName As String

Dim NewTableName As String

Dim sSql As String

Dim tStr As String

WN = "进销存表.xls"

OldTableName = "进仓表"

NewTableName = "临时表"

tStr = "毛笔"

Set conn = New ADODB.Connection

conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0" &_

"Extended Properties=Excel 8.0" &_

"Data Source=" &ThisWorkbook.Path &"\" &WN

conn.Open

If conn.State = adStateOpen Then

sSql = "Insert Into [" &NewTableName &"$] (出仓日期,物品名称,出仓数量) " &_

"Select 进仓日期 As 出仓日期,物品名称,进仓数量 As 出仓数量 From [" &_

OldTableName &"$] Where 物品名称='" &tStr &"'"

conn.Execute sSql

MsgBox "成功把“" &OldTableName &"”中的所有的“" &tStr &"”记录汇总到“" &NewTableName &"”中！", , "http://excelba.com"

conn.Close

End If

Set conn = Nothing

End Sub

Insert Into Select就介绍完了，现在可以动手复制一下上面的代码，运行一下看一下效果吧*^_^*。

如果你不是从第一篇 SQL语言教程 看起，你需要到http://www.excelba.com/Soft/Html/119.html 下载文件，压缩文件包含三个文件，数据库文件——进销存表.mdb与Excel文件——进销存表.xls就是保存数据的文件，而主文件.xls就是主程序文件，以后大家的代码可以添加在这个文件的新模块里，然后再运行程序代码。

转载请注明:本文来自:Excel吧 (www.excelba.com) 详细出处参考：http://www.excelba.com/Art/Html/311.html

---