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 最终效果图

Excel 出库信息设置

为了方便产品销售出库,可以对出库信息进行设置,具体步骤如下所示。

步骤01:打开“进销存管理.xlsx”工作簿,复制“入库明细单”工作表,将复制后的工作表重命名为“出库明细单”,并将其移到工作表的最后位置。然后将该工作表中的“入库”更改为“出库”,将“供应商”更改为“客户”,并删除“客户”列中的公式。接着将表中的两条入库记录删除,只删除不含有公式的单元格的内容,其余内容和单元格中的公式保持不变,效果如图5-88所示。

步骤02:根据“出库单”添加出库记录,随着记录的增加,记录行会自动添加边框,如图5-89所示。

步骤03:在“成本单价”后面插入一列,在“成本金额”列后面插入两列,然后分别输入列标题“销售单价”“销售金额”和“毛利”,如图5-90所示。

图5-88 复制“入库明细单”并修改相应的数据

图5-89 添加出库记录

图5-90 插入列标题

步骤04:在单元格J3中输入出库商品的销售单价,然后在单元格M3中输入公式“=IF(B3=””,””,J3*K3)”,在N3单元格中输入公式“=IF(B3=””,””,M3-L3)”,按Enter键完成公式输入,最终效果如图5-91所示。

图5-91 完成出库明细单

Excel 入库信息设置

为了方便处理商品入库和出库等商品行为,可以对入库信息进行设置,具体操作步骤如下所示。

步骤01:打开“进销存管理.xlsx”工作簿,复制“入库单”工作表,将复制后的工作表重命名为“入库明细单”,并将其移到工作表的最后位置。然后选中整个工作表,按Delete键清除工作表中所有的单元格内容,如图5-76所示。

图5-76 复制入库单并删除所有内容

步骤02:清除单元格格式。选中单元格区域“B1:I10”,功能区切换到“开始”选项卡,单击“编辑”组中的“清除”下拉按钮,在展开的下拉列表中选择“清除格式”选项,如图5-77所示。

图5-77 清除格式

步骤03:随即系统会自动清除单元格区域“B1:I10”中的所有单元格格式,如图5-78所示。

图5-78 清除单元格格式后的效果

步骤04:在该工作表的相应位置输入表格标题和相应的列标题,然后进行适当的单元格格式设置,并适当地调整各列的列宽,如图5-79所示。

图5-79 输入有关信息

步骤05:导入入库商品信息。在单元格E3中输入公式“=IF(OR($B3=””,$D3=””),””,VLOOKUP($D3,商品代码表!$B:$G,COLUMN()-3,0))”,按Enter键完成输入,随即返回引用结果。然后将该单元格中的公式分别向右、向下填充,将“E3:I8”单元格区域进行填充,如图5-80所示。

步骤06:设置“成本金额”列公式。在单元格K3中输入公式“=IF(B3=””,””,I3*J3)”,按Enter键完成公式输入,随即返回计算结果。然后将该单元格中的公式填充到该列的其他单元格中,如图5-81所示。

步骤07:将B列和D列单元格的数字格式设置为文本格式,将I列和K列单元格的数字格式设置为保留两位小数的货币格式,然后根据“入库单”输入商品入库信息,如图5-82所示。

图5-80 设置“供应商”公式并进行填充

图5-81 设置“成本金额”列公式

图5-82 进行格式设置并输入相关信息

步骤08:使用条件格式自动添加边框。选中B列到L列,切换到“开始”选项卡下,单击“样式”组中的“条件格式”下拉按钮,在展开的下拉列表中选择“新建规则”选项,如图5-83所示。

图5-83 选择新建规则选项

步骤09:打开“新建格式规则”对话框,在“选择规则类型”选项下选择“使用公式确定要设置格式的单元格”类型,并在“为符合此公式的值设置格式”文本框中输入公式“=$B2<>”””,然后单击“格式”按钮,如图5-84所示。

步骤10:弹出“设置单元格格式”对话框,切换到“边框”选项卡下,在“线条”组合框中的“样式”列表框中选择合适的线条样式,在颜色下拉列表中选择颜色为“黑色”,然后在“预置”组合框中单击“外边框”按钮,如图5-85所示。然后单击“确定”按钮返回“新建格式规则”对话框。

图5-84 单击“格式”按钮

图5-85 设置单元格格式

步骤11:单击“确定”按钮返回工作表,此时有数据的行会自动添加边框,如图5-86所示。

图5-86 添加边框效果

步骤12:添加“0002”号入库单的入库记录,随着记录的增加,记录行会自动添加边框,最终效果如图5-87所示。

图5-87 添加入库记录完成入库明细单

Excel 制作出入库单据

出入库单据是常用的记录存货收发的原始凭证,制作出入库单据的具体步骤如下。

步骤01:打开“进销存管理.xlsx”工作簿,插入一个新的工作表,将其重命名为“入库单”,并将其移动到最后位置,如图5-69所示。

图5-69 新建“入库单”工作表

步骤02:在该工作表的相应位置输入“入库单”的相关项目,然后对其进行单元格格式设置,并适当地调整各列的列宽,效果如图5-70所示。

图5-70 输入相关数据

步骤03:导入入库商品信息。在单元格C5中输入公式“=IF($B5=””,””,VLOOKUP($B5,商品代码表!$B:$G,COLUMN()-1,0))”,按Enter键完成输入,随即返回引用结果。然后将该单元格中的公式不带格式地分别向右和向下填充,填充到单元格区域“C5:G8”中,如图5-71所示。

步骤04:设置“成本金额”列公式。在单元格I5中输入公式“=IF(B5=””,””,G5*H5)”,按Enter键完成输入,随即返回计算结果。然后将该单元格中的公式不带格式地填充到该列其他单元格中,如图5-72所示。

图5-71 设置计算公式并填充

图5-72 设置“成本金额”列公式

步骤05:复制“入库单”工作表,将复制的工作表重命名为“出库单”,并将其移到工作表的最后位置,然后将该工作表中的“入库”更改为“出库”,将“供应商”更改为“客户”,并删除“客户”列中的公式,其余内容和单元格中的公式保持不变,如图5-73所示。

步骤06:填制入库单。例如,企业在2015年8月7日收到采购部门采购的联想开天电脑和联想启天电脑各20台,验收入库时需填写“入库单”,只需填写“入库单号”“入库日期”“部门”“业务员”“商品代码”和“数量”等信息,其他内容将由单元格中的公式自动引用和计算。填制入库单的最终效果如图5-74所示。

步骤07:填制出库单。例如,企业在2015年8月10日销售30台联想启天电脑,商品出库时需填写“出库单”,只需要填写“出库单号”“出库日期”“部门”“业务员”“商品代码”“客户”和“数量”等信息,其他内容将由单元格中的公式自动引用和计算。填制出库单的最终效果如图5-75所示。

图5-73 复制“入库单”并进行更改

图5-74 填制入库单

图5-75 填制出库单

Excel 制作商品代码表

商品代码化在库存的系统化管理中起着关键性作用,既便于查找商品代码对应的商品名称、规格型号、供应商等信息,又可以实现个别计价,对发生价格变化的商品重新编码,以新代码识别新的商品入库价格。制作商品代码表的具体步骤如下。

步骤01:打开“进销存管理.xlsx”工作簿,将工作表Sheet3重命名为“商品代码表”,如图5-56所示。

图5-56 为工作表重命名

步骤02:在“商品代码表”中输入表格标题和相应的列标题,并进行简单的格式设置,如图5-57所示。

步骤03:设置文本型数字。选中B列,然后单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”选项,如图5-58所示。

步骤04:随即打开“设置单元格格式”对话框,切换到“数字”选项卡,在“分类”列表框中选择“文本”选项,然后单击“确定”按钮,如图5-59所示。

步骤05:在表格中输入商品代码和相应的商品信息,然后对其进行简单的格式设置,并适当地调整各列的列宽,效果如图5-60所示。

步骤06:使用条件格式自动添加边框。选中单元格区域“A3:H10”,功能区切换到“开始”选项卡下,单击“样式”组中的“条件格式”下拉按钮,在展开的下拉列表中选择“新建规则”选项,如图5-61所示。

图5-57 输入列标题

图5-58 选择“设置单元格格式”选项

图5-59 设置单元格格式

图5-60 输入商品代码及相应的信息

图5-61 选择“新建规则”选项

步骤07:随即弹出“新建格式规则”对话框,在“选择规则类型”中选择“使用公式确定要设置格式的单元格”,在“为符合此公式的值设置格式”文本框中输入公式“=$C4<>$C3”,然后单击“格式”按钮,如图5-62所示。

图5-62 设置新建格式规则

步骤08:弹出“设置单元格格式”对话框,切换到“边框”选项卡,在“线条”组合框中的“样式”列表框中选择一种线条样式,在颜色下拉列表中选择一种线条颜色,这里选择“黑色”。在“边框”组合框中单击“下边框”按钮,然后单击“确定”按钮,返回“新建格式规则”对话框,如图5-63所示。

步骤09:单击“确定”按钮返回工作表,如图5-64所示。

图5-63 设置边框

图5-64 单击“确定”按钮

步骤10:此时在不同的供应商之间会出现一条底部框线,区分开不同的供应商,如图5-65所示。

图5-65 添加底部框线效果图

步骤11:功能区切换到“视图”选项卡,取消勾选“显示”组中的“网格线”复选框,此时工作表中的网格线已经被隐藏起来,用户可以更加明显地看到不同供应商之间的间隔线,如图5-66所示。

步骤12:添加一条商品代码及其相应的商品信息记录,如图5-67所示。

步骤13:选中“商品代码”列中的任意一个单元格,然后单击“升序”排序按钮,对商品代码进行升序排序。此时不同供应商之间的分隔线会自动调整位置,商品代码表就制作完成了,如图5-68所示。

图5-66 取消勾选“网格线”复选框

图5-67 添加商品代码及其信息

图5-68 进行升序排序