Excel 库存量控制

为了不影响企业的经营,同时又不会造成库存的负担,企业需要对库存量进行控制。在Excel中,可以使用条件格式进行库存量控制,当库存量低于或者高于某个数值时,则以特殊格式警告,具体操作步骤如下所示。

步骤01:打开“进销存管理.xlsx”工作簿,切换到“库存统计”工作表,选中单元格区域“L4:L10”,功能区切换到“开始”选项卡,单击“样式”组中的“条件格式”下三角按钮,在展开的下拉列表中选择“新建规则”选项,如图5-107所示。

步骤02:弹出“新建格式规则”对话框,在“选择规则类型”选项卡下选择“只为包含以下内容的单元格设置格式”规则类型,在“只为满足以下条件的单元格设置格式”条件下,依次选择的条件为“单元格值”“大于或等于”,在右侧的文本框中输入30,然后单击“格式”按钮,如图5-108所示。

步骤03:打开“设置单元格格式”对话框,切换到“字体”选项卡,将“字形”设置为“加粗”,并且选择添加“双下画线”,选择“颜色”为“深蓝色”,如图5-109所示。

图5-107 选择新建规则

图5-108 编辑规则

图5-109 设置字体

步骤04:切换到“填充”选项卡,选择背景色为“黄色”,单击“确定”按钮,如图5-110所示。

图5-110 设置填充颜色

步骤05:返回“新建格式对话框”,单击“确定”按钮返回工作表,效果如图5-111所示。

图5-111 设置条件格式效果

Excel 库存统计相关函数及制作库存统计表

库存统计是对商品的出入库情况进行的综合统计,它包括期初库存、本期入库、本期出库和期末库存等信息。

相关函数介绍

在进行库存统计时会涉及ISNA函数和SUMIF函数,下面我们分别介绍这两个函数的语法和功能。

ISNA函数的语法和功能

在Excel中,ISNA函数的功能为,如果数值为对错误值#N/A单元格的引用,函数ISNA返回逻辑值TRUE,否则返回FALSE。

函数语法:ISNA(value)

Value为需要进行检验的数值。分别为:空白(空白单元格)、错误值、逻辑值、文本、数字、引用值或对于以上任意参数的名称引用。

SUMIF函数的语法和功能

Excel中的SUMIF函数的主要作用是按给定条件对指定单元格求和,即根据指定条件对若干单元格、区域或引用求和。

函数语法:SUMIF(range,criteria,[sum_range])

其中range是必需项,表示条件范围;criteria是必需项,表示条件;sum_range是可选项,表示求和范围。

制作库存统计表

步骤01:打开“进销存管理.xlsx”工作簿,复制“出库单”工作表,将复制后的工作表重命名为“库存统计”,并将其移到工作表的最后位置。选中单元格区域“B1:I11”,然后单击鼠标右键,在弹出的快捷菜单中选择“删除”选项,如图5-92所示。

图5-92 选择“删除”选项

步骤02:弹出“删除”对话框,选中“下方单元格上移”单选按钮,然后单击“确定”按钮,如图5-93所示。

图5-93 设置删除选项

步骤03:此时即可删除单元格区域“B1:I11”中的所有单元格,下方单元格依次上移,如图5-94所示。

步骤04:在“库存统计”工作表中输入表格标题和相应的列标题,然后进行单元格格式设置,并适当地调整各列的列宽,如图5-95所示。

步骤05:在“商品代码”列输入文本型数值代码,然后在C4单元格中输入公式“=IF(ISNA(VLOOKUP($B4,商品代码表!$B:$G,COLUMN(),0)),””,VLOOKUP($B4,商品代码表!$B:$G,COLUMN(),0))”,按Enter键完成输入,随即返回引用结果。然后将该单元格的公式向右和向下填充(不带格式),如图5-96所示。

图5-94 删除区域中所有单元格

图5-95 输入相关数据

图5-96 设置商品代码公式并填充

步骤06:在F4:F10单元格中输入期初库存数量,然后在单元格G4中输入公式“=IF(ISNA(VLOOKUP($B4,商品代码表!$B:$G,6,0)),0,VLOOKUP($B4,商品代码表!$B:$G,6,0)*F4)”,按Enter键完成输入,随即返回计算结果。然后将该单元格的公式填充到该列的其他单元格中,如图5-97所示。

步骤07:导入本期商品入库的数量。在单元格H4中输入公式“=SUMIF(入库明细单!D:D,库存统计!B4,入库明细单!J:J)”,按Enter键完成输入,随即返回计算结果。然后将该单元格的公式填充到该列其他单元格中,如图5-98所示。

步骤08:导入本期商品入库的成本金额。在I4单元格中输入公式“=SUMIF(入库明细单!D:D,库存统计!B4,入库明细单!K:K)”,按Enter键完成输入,随即返回计算结果。然后将该单元格的公式填充到该列其他单元格中,如图5-99所示。

图5-97 计算期初库存成本金额

图5-98 导入本期商品入库的数量

图5-99 导入本期商品入库的成本金额

步骤09:导入本期商品出库的数量。在J4单元格中输入公式“=SUMIF(出库明细单!D:D,库存统计!B4,出库明细单!K:K)”,按Enter键完成输入,随即返回计算结果。然后将该单元格的公式填充到该列其他单元格中,如图5-100所示。

图5-100 导入本期商品出库的数量

步骤10:导入本期商品出库的成本金额。在K4单元格中输入公式“=SUMIF(出库明细单!D:D,库存统计!B4,出库明细单!L:L)”,按Enter键完成输入,随即返回计算结果,然后将该单元格的公式填充到该列其他单元格中,如图5-101所示。

图5-101 导入本期商品出库的成本金额

步骤11:计算期末库存的数量。在L4单元格中输入公式“=F4+H4-J4”,按Enter键完成输入,随即返回计算结果。然后将该单元格的公式填充到该列其他单元格中,如图5-102所示。

步骤12:计算期末库存的成本金额。在M4单元格中输入公式“=G4+I4-K4”,按Enter键完成输入,随即返回计算结果。然后将该单元格的公式填充到该列其他单元格中,如图5-103所示。

步骤13:计算期初库存总成本。将单元格区域D11:F11合并为1个单元格,输入“期初库存总成本”,然后在G11单元格中输入公式“=SUM(G4:G10)”,按Enter键完成输入,随即返回计算结果,如图5-104所示。

图5-102 计算期末库存的数量

图5-103 计算期末库存的成本金额

图5-104 计算期初库存总成本

步骤14:计算期末库存总成本。将单元格区域K11:L11合并为一个单元格,输入“期末库存总成本”。然后在M11单元格中输入公式“=SUM(M4:M10)”,按Enter键完成输入,随即返回计算结果,如图5-105所示。

图5-105 计算期末库存总成本

步骤15:根据前面我们所学过的知识,为库存统计表添加合适的边框并去除网格线,最终效果如图5-106所示。

图5-106 最终效果图