3.3 精通库存管理
库存管理是生产、计划和控制的基础。通过对仓库、货位等账务管理及出入库单据进行管理，可以及时反映各种物资的仓储、流向情况，为生产管理和成本核算提供依据。通过库存分析，可以为管理者及决策人员提供库存资金占用情况、物资积压情况、短缺、超储情况等不同的统计分析信息。
3.3.1 制作商品分类表
商品分类表在库存管理中占有非常重要的地位，它集中记录了每种商品的编码、名称、供应商等信息，方便用户对商品进行分类管理。
【例3-13】创建商品分类表。
01 新建一个名为“商品分类表”的工作表，在其中输入标题和列标题，然后选中“A1：G1”单元格区域，如图3-71所示。
图3-71新建工作表
02 打开“开始”选项卡，单击“字体”命令组中的对话框启动器，如图3-72所示。
图3-72 单击对话框启动器
03 在“A1”单元格中输入“1”，选中“A1：A24”单元格区域，单击“编辑”命令组中的“填充”按钮，弹出下拉列表，从中选择“序列”选项，如图3-73所示。
图3-73 选择“序列”选项
04 弹出“序列”对话框，在“序列产生在”栏中选择“列”单选项，在“类型”栏中选择“等差序列”单选项，在“步长值”文本框中输入“1”，单击“确定”按钮，如图3-74所示。
图3-74 “序列”对话框
05 选中“B3：B24”单元格区域，按“Ctrl+1”组合键，弹出“设置单元格格式”对话框，打开“数字”选项卡，在“分类”列表框中选择“文本”选项，单击“确定”按钮，如图3-75所示。
图3-75 设置文本格式
06 选中“G3：G24”单元格区域，按“Ctrl+1”组合键，弹出“设置单元格格式”对话框，打开“数字”选项卡，在“分类”列表框中选择“会计专用”选项，将小数位数设置为“2”，单击“确定”按钮，如图3-76所示。
图3-76 设置会计专用格式
07 选中“A2：G24”单元格区域，按“Ctrl+1”组合键弹出“设置单元格格式”对话框，打开“边框”选项卡，从中设置外边框和内边框，如图3-77所示。
图3-77 设置边框
08 单击“确定”按钮，返回工作表编辑区，在表格中输入商品信息，最终效果如图3-78所示。
图3-78 最终效果
3.3.2 制作入库单
入库单是用来记录商品入库情况的单据，是重要的原始凭证，其中记录了商品的编码、商品名称、入库的数量等重要信息。
【例3-14】创建入库单。
01 新建一个名为“入库单”工作表，在其中输入表格的标题和列表题，如图3-79所示。
图3-79 新建工作表
02 设置单元格格式，合并单元格，为表格添加边框，调整列宽等，最终设置好的表格如图3-80所示。
图3-80 空白入库单
03 为了节省工作量，用户可以使用引用公式，导入商品分类表中的数据。选中“B6”单元格，在单元格中输入“=IF($A6=””,””,VLOOKUP($A6,商品分类表!$B$3:$G$13,2,0))”，如图3-81所示。
图3-81 输入引用公式
04 按“Enter”键确认输入，然后将鼠标放到“B6”单元格的右下角，当鼠标变成“+”时，按住鼠标左键，向右移动鼠标，如图3-82所示。向右填充公式。
图3-82 向右填充公式
05 由于“规格型号”列在商品分类表中的位置与“商品名称”列所在的位置不同，规格型号在所选区域的第3列，所以，需要修改“C6”单元格中的公式为“=IF($A6=””,””,VLOOKUP($A6,商品分类表!$B$3:$G$13,3,0))”，如图3-83所示。
图3-83 修改公式
06 同样的修改“D6”单元格、“E6”单元格和“F6”单元格中的公式，然后选中“B6：F6”单元格区域，将鼠标移动到选中区域的右下角，当鼠标变成“+”时，按住鼠标左键，向下拖动鼠标，如图3-84所示。向下填充公式。
图3-84 向下填充公式
07 选中“A6：A13”单元格区域，打开“开始”选项卡，单击“数字”命令组中的“数字格式”按钮，弹出下拉列表，从中选择“文本”选项，如图3-85所示。
图3-85 设置为文本格式
08 在“A6”单元格中输入“111001”，此时，会自动出现其对应的商品名称、规格型号、供应商和成本单价等信息，然后手动输入其他信息即可，最终效果如图3-86所示。
图3-86 最终效果
3.3.3 编制入库统计表
为了统计商品的入库情况，方便进行库存统计，用户需要编制入库统计表，将所有商品的入库情况进行登记。
【例3-15】创建入库统计表。
01 按住“Ctrl”键，将鼠标移动到“入库单”工作表的标签上，按住鼠标左键，移动鼠标，当鼠标变成“
”时，将其移动到工作表的最后位置，松开鼠标，如图3-87所示。
图3-87 复制入库单
02 这样就复制了一个“入库单”工作表，名为“入库单（2）”，打开“开始”选项卡，单击“单元格”命令组中的“格式”按钮，弹出下拉列表，从中选择“重命名工作表”选项，将名称修改为“入库统计表”，如图3-88所示。
图3-88 重命名工作表
03 选中入库统计表中所有包含内容的列，单击“编辑”命令组中的“清除”按钮，弹出下拉列表，从中选择“全部清除”选项，如图3-89所示。
图3-89 清除全部内容和格式
04 此时，被选定区域的内容和格式全部被清除了，如图3-90所示。这样，用户就可在工作表中重新输入内容，设置格式。
图3-90 清除后的效果
05 在工作表中输入标题和列标题，并设置其格式，然后为表格添加边框，如图3-91所示。
图3-91 创建空白的入库统计表
06 引用商品分类表中的数据，选中“D3”单元格，输入“=IF($C3=””,””,VLOOKUP($C3,商品分类表!$B$3:$G$13,2,0))”，按“Enter”键确认输入，如图3-92所示。
图3-92 输入引用公式
07 将鼠标移动到“D3”单元格右下角，当鼠标变成“+”时，按住鼠标左键，向右移动鼠标，填充公式，如图3-93所示。
图3-93 向右填充公式
08 选中“E3”单元格，将公式修改为“=IF($C3=””,””,VLOOKUP($C3,商品分类表!$B$3:$G$13,3,0))”，如图3-94所示。同样的也修改“F3：H3”单元格区域中的公式。
图3-94 修改公式
09 选中“D3：H3”单元格区域，将鼠标移动到“H3”单元格右下角，当鼠标变成“+”时，按住鼠标左键，向下移动鼠标，填充公式，如图3-95所示。
图3-95 向下填充公式
10 选中“C3：C20”单元格区域，单击“数字”命令组中的“数字格式”按钮，弹出下拉列表，从中选择“文本”选项，如图3-96所示。
图3-96 设置文本格式
11 选中“J3”单元格，在其中输入“=H3*I3”，按“Enter”键确认输入，如图3-97所示。
图3-97 输入公式
12 将鼠标移动到“J3”单元格右下角，当鼠标变成“+”时，按住鼠标左键，向下移动鼠标，填充公式，如图3-98所示。
图3-98 向下填充公式
13 在表格中输入商品编码，此时，在表格中自动显示该编码代表的商品的信息，如图3-99所示。接着按照入库单输入其他信息，最终效果，如图3-100所示。
图3-99 输入编号
图3-100 最终效果
3.3.4 编制出库统计表
销售人员将商品销售出去后，需要填写出库单，才能将商品从仓库中领走，而为了统计所有商品的出库情况，需要创建出库统计表，将审核无误的出库单上的数据登记到出库统计表中。
【例3-16】创建出库统计表。
01 将入库单复制到“出库单”工作表中，修改其中部分内容，形成出库单，填写出库单，如图3-101所示。
图3-101 出库单
02 新建一个名为“出库统计表”工作表，输入标题和列标题，并设置标题格式，形成空白的出库统计表，如图3-102所示。
图3-102 创建出库统计表
03 选中“A：N”列，打开“开始”选项卡，单击“样式”命令组中“条件格式”按钮，弹出下拉列表，从中选择“新建规则”选项，如图3-103所示。
图3-103 选择“新建规则”选项
04 弹出“新建格式规则”对话框，在“选择规则类型”列表框中选择“使用公式确定要设置格式的单元格”选项，在“为符合此公式的值设置格式”文本框中输入“=$B2<>”””，单击“格式”按钮，如图3-104所示。
图3-104 输入公式
05 弹出“设置单元格格式”对话框，打开“边框”选项卡，从中设置外边框，然后单击“确定”按钮，如图3-105所示。
图3-105 设置外边框
06 返回“新建格式规则”对话框，在“预览”组合框中可以看到设置后的效果，单击“确定”按钮，如图3-106所示。
图3-106 “新建格式规则”对话框
07 此时，有数据的行会自动添加边框，随着记录的增加，记录行会自动添加边框。选中“D2”单元格，输入引用商品分类表的公式，按“Enter”键确认输入，如图3-107所示。
图3-107 输入引用公式
08 在“E2”、“G2”和“H2”单元格中也同样输入引用的公式，接着选中“L2”单元格，输入“=I3*J3”，然后按“Enter”键确认输入，如图3-108所示。
图3-108 输入公式
09 选中“M2”单元格，输入“=L3-K3”，按“Enter”键确认输入，如图3-109所示。接着按照审核无误的出库单录入其他数据，最终效果如图3-110所示。
图3-109 输入公式
图3-110 最终效果
3.3.5 利用函数进行库存统计
用户创建入库统计表和出库统计表后，还需要创建库存统计表，通过引用出入库统计表中的数据、使用公式计算本期结存的数量和金额，用户能够随时掌握商品的库存情况。
【例3-17】创建库存统计表。
01 新建一个名为“库存统计表”工作表，在其中输入标题和列标题，并设置其格式，为表格添加边框，最终效果如图3-111所示。
图3-111 创建空白的库存统计表
02 选中“B4”单元格，在其中输入“=IF(ISNA(VLOOKUP($A4,商品分类表!$B:$G,COLUMN(),0)),"",VLOOKUP($A4,商品分类表!$B:$G,COLUMN(),0))”，按“Enter”键确认输入，如图3-112所示。引用商品分类表中的商品名称。
图3-112 输入引用公式
03 将鼠标移动到“B4”单元格的右下角，当鼠标变成“+”时，按住鼠标左键，向右移动鼠标，如图3-113所示。向右填充公式。
图3-113 向右填充公式
04 选中“D4”单元格，修改公式为“=IF(ISNA(VLOOKUP($A4,商品分类表!$B:$G,COLUMN(),0)),"",VLOOKUP($A4,商品分类表!$B:$G,COLUMN()+1,0))”，按“Enter”键确认输入，如图3-114所示。
图3-114 修改公式
05 选中“B4：D4”单元格区域，将鼠标移动到“D4”单元格的右下角，当鼠标变成“+”时，按住鼠标左键，向下移动鼠标，如图3-115所示。
图3-115 向下填充公式
06 选中“F4”单元格，在其中输入“=IF(ISNA(VLOOKUP($A4,商品分类表!$B:$G,6,0)),0,VLOOKUP($A4,商品分类表!$B:$G,6,0)*E4)”，按“Enter”键确认输入，如图3-116所示。计算上期结存的金额，然后将公式填充到下方的单元格中。
图3-116 计算上期结存金额
07 选中“G4”单元格，在其中输入“=SUMIF(入库统计表!C:C,库存统计表!A4,入库统计表!I:I)”，按“Enter”键确认输入，如图3-117所示。计算本期入库的数量。
图3-117 计算本期入库数量
08 选中“H4”单元格，在其中输入“=SUMIF(入库统计表!C:C,库存统计表!A4,入库统计表!J:J)”，按“Enter”键确认输入，如图3-118所示。计算本期入库的金额，然后将公式填充到下方的单元格中。
图3-118 计算本期入库金额
09 选中“I4”单元格，在其中输入“=SUMIF(出库统计表!C:C,库存统计表!A4,出库统计表!J:J)”，按“Enter”键确认输入，如图3-119所示。计算本期出库的数量。
图3-119 计算本期出库数量
10 选中“J4”单元格，在其中输入“=SUMIF(出库统计表!C:C,库存统计表!A4,出库统计表!K:K)”，按“Enter”键确认输入，如图3-120所示。计算本期出库的金额。
图3-120 计算本期出库金额
11 将公式复制到下方的单元格中，选中“K4”单元格，在其中输入“=E4+G4-I4”，按“Enter”键确认输入，如图3-121所示。计算本期结存的数量。
图3-121 计算本期结存数量
12 将公式复制到下方的单元格中，选中“L4”单元格，在其中输入“=F4+H4-J4”，按“Enter”键确认输入，如图3-122所示。计算本期结存的金额。
图3-122 计算本期结存金额
13 将公式复制到下方的单元格中，接着输入商品编码和上期结存的数量，其余的信息都会自动显示出来，最终的效果如图3-123所示。
图3-123 最终效果
3.3.6 库存情况分析
用户创建好库存统计表后，需要对表格中的数据进行统计和分析，使用户随时掌握库存的具体情况，并以此为依据，对库存量进行控制。
【例3-18】统计上期和本期的结存总数量和总金额，将本期结存数量超过4500的突出显示。
01 在库存统计表右方的“N1：O5”单元格区域中，新建一个“库存总成本统计”表格，然后选中“O2”单元格，在其中输入“=SUM(F4:F13)”，如图3-124所示。
图3-124 计算上期结存总成本
02 按“Enter”键确认输入后，选中“O3”单元格，在其中输入“=SUM(L4:L13)”，按“Enter”键确认输入，如图3-125所示。
图3-125 计算本期结存总成本
03 选中“O4”单元格，在其中输入“=SUM(E4:E13)”，按“Enter”键确认输入，如图3-126所示。
图3-126 计算上期结存总数量
04 选中“O5”单元格，在其中输入“=SUM(K4:K13)”，按“Enter”键确认输入，如图3-127所示。
图3-127 计算本期结存总数量
05 选中“K4：K13”单元格区域，为该区域添加条件格式，如图3-128所示。
图3-128 选中单元格区域
06 打开“开始”选项卡，单击“样式”命令组中的“条件格式”按钮，弹出下拉列表，从中选择“突出显示单元格规则—大于”选项，如图3-129所示。
图3-129 设置条件格式
07 弹出“大于”对话框，在“为大于以下值的单元格设置格式”文本框中输入“4500”，在“设置为”下拉列表中选择“浅红填充色深红色文本”选项，如图3-130所示。
图3-130 “大于”对话框
08 单击“确定”按钮后，返回工作表编辑区，可以看到符合条件的单元格被突出显示了，如图3-131所示。
图3-131 突出显示的效果