Excel22

为了不影响企业的经营，同时又不会造成库存的负担，企业需要对库存量进行控制。在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　设置条件格式效果

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

步骤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　完成出库明细单

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

步骤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　添加入库记录完成入库明细单

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

步骤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　填制出库单

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

步骤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　进行升序排序