库存管理的 7 个最有用的 Excel 公式

如果您要开始自己的零售业务,您必须有一种方法来跟踪您的库存。如今,仅用笔和纸将不再切割。然而,成熟的库存管理软件可能很昂贵。

但是您知道您可以使用 Excel 制作自己的作品吗?

这里有七个公式可帮助您创建库存管理电子表格。无需在特殊应用程序上花费额外费用即可更好地管理您的业务。

1. 总和

如果你一生都会使用一个公式,那么 SUM 就是它。此功能允许您添加值而无需单独选择每个单元格。

您可以通过使用此命令然后选择要添加的单元格范围来节省时间。

公式: =SUM(number1,[number2],…)

  • NUMBER1 :这是添加的第一个值。它可以是任何数字、一个单元格,甚至是一组单元格(称为范围)。
  • NUMBER2-255 (可选):这些是函数将添加的以下值。同样,它可以是任何数字、单元格或范围。您可以在此处放置多达 255 个值。

2. 苏米夫

该公式为 sum 函数增加了一层复杂性。如果要过滤掉要添加的单元格,可以使用 SUMIF 命令。

例如,如果您只想查找从特定供应商处销售的库存总和,您可以使用 SUMIF。如果您要进一步将过滤器范围缩小到特定产品,则可以使用 SUMIFS。

例如,如果您想过滤供应商和产品类型,您可以使用此功能。基本上,SUMIFS 使您能够使用两个或多个变量来过滤您的值。

公式: =SUMIF(range,criteria,[sum_range])

  • 范围:这些是 Excel 将检查以了解是否会添加相应值的单元格。
  • 条件:这是 Excel 将与 RANGE 下的单元格进行比较的值或公式。
  • SUM_RANGE (可选):这些是将被添加在一起的单元格。如果留空,则使用 RANGE 下的单元格。

在下图中,您将看到仅添加了支付给 Abacus Technologies 的金额。

3. SUMIFS

公式: =SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria20,…)

  • SUM_RANGE :这些是要添加的单元格。
  • CRITERIA_RANGE1 :这些是 Excel 将测试的单元格。如果此处的值基于 CRITERIA1 为真,则会添加相应的 SUM_RANGE 单元格。
  • CRITERIA1 :这是定义 CRITERIA_RANGE1 的内容。这可以是数字、函数或任何其他值。
  • CRITERIA_RANGE2 , CRITERIA2 ,…(可选):这些是额外的单元格范围和条件,用于进一步过滤您的 SUMIFS。您最多可以有 127 个额外的范围/标准对。

在下面的示例中,仅将支付给 Abacus Technologies 制造商下的 Jupiter 品牌的金额加在一起。

如果您发现这些公式有点复杂,这里有一些技巧可以帮助您快速学习 Excel

4. 查找

该函数有两个选项:VECTOR 和 ARRAY。 Vector 选项最适合在特定列中查找数据。

相反,数组选项在多行和多列中搜索值。它已被 VLOOKUP 和 HLOOKUP 取代,但仍保持与旧电子表格的兼容性。

公式: =LOOKUP(lookup_value,lookup_vector,[result_vector])

  • LOOKUP_VALUE :这是 Excel 将在您的 LOOKUP_VECTOR 中找到的值。
  • LOOKUP_VECTOR :这是 Excel 将找到您的 LOOKUP_VALUE 的范围。这只能是单行或单列,并且此处下的值应按升序排列。
  • RESULT_VECTOR (可选):这是 Excel 将找到要返回的相应值的范围。这是基于 LOOKUP_VECTOR 的结果。

在下面的示例中,用户知道商品名称但不熟悉 SKU 代码。为了获得 SKU 代码,他们使用了 LOOKUP 公式。

5. VLOOKUP

VLOOKUP 函数对按列排列的数据很有帮助。 Excel 在所选范围的第一列中查找您分配的值。然后它将根据您提供的数字跨列计数并返回相应的值。

公式: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

  • LOOKUP_VALUE :这是 Excel 将在表的第一列中查找的值。
  • TABLE_ARRAY :这是函数将运行的单元格范围。 Excel 将使用LOOKUP_VALUE并在TABLE_ARRAY的第一列中找到它。然后它将根据以下变量计算单元格的数量以返回结果。
  • COL_INDEX_NUMBER :这是 Excel 将计数以返回结果的列数。
  • RANGE_LOOKUP (可选):这只能是 TRUE 或 FALSE。如果您写为 TRUE,它将返回近似匹配(并且您的第一列应按升序排列)。如果您选择 FALSE,它将仅返回完全匹配。

在下面的示例中,用户搜索特定 SKU 的价格。由于价格在第 5 列下,因此COL_INDEX_NUMBER下的值设置为 5。

6.HLOOKUP

HLOOKUP 函数与 VLOOKUP 几乎相同,只是你这里的数据应该是按行排列的。

公式: =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

  • LOOKUP_VALUE :这是 Excel 将在表格的第一行中查找的值。
  • TABLE_ARRAY :这是函数将运行的单元格范围。 Excel 将使用LOOKUP_VALUE并在TABLE_ARRAY的第一列中找到它。然后它将根据以下变量计算单元格的数量以返回结果。
  • ROW_INDEX_NUMBER :这是 Excel 将计数以返回结果的行数。
  • RANGE_LOOKUP (可选):这只能是 TRUE 或 FALSE。如果您写为 TRUE,它将返回近似匹配(并且您的第一列应按升序排列)。如果您选择 FALSE,它将仅返回完全匹配。

如下图,HLOOKUP公式用于获取一个SKU的可用库存。由于可用库存的行号为 9,因此ROW_INDEX_NUMBER为 9。

7. XLOOKUP

XLOOKUP 函数是 VLOOKUP 和 HLOOKUP 公式的演变。它允许您返回多个结果,您可以使用它按列或按行搜索。但是,这仅适用于 Microsoft 365 版本的 Excel。

公式: =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

  • LOOKUP_VALUE :与上面的公式一样,这指的是 Excel 将找到的值。
  • LOOKUP_ARRAY :这是 Excel 将梳理以查找您分配的LOOKUP_VALUE的单元格范围。这可以位于表格中的任何位置,但只能是单行或单列。否则,您的公式将返回错误。
  • RETURN_ARRAY :这是 Excel 在您的 LOOKUP_ARRAY 中找到 LOOKUP_VALUE 后将显示的结果。这必须反映您的 LOOKUP_ARRAY 的形状。例如,如果 LOOKUP_ARRAY 是一列,那么 RETURN_ARRAY 也应该是一列。但是如果您选择多行或多列, RETURN_ARRAY 将返回大量结果。
  • IF_NOT_FOUND (可选):如果公式找不到您要查找的值,则该公式将显示您在此处写入的文本。如果留空,这将默认为#N/A。
  • MATCH_MODE (可选):只能是 -1、0、1 或 2。-1、0 和 1 将返回完全匹配。如果没有找到,-1 将显示下一个较小的值,0 将返回 #N/A,1 将显示下一个较大的值。如果留空,则默认为 0。
  • SEARCH_MODE (可选):只能是 1、 -1、2或 -2。 1 从 LOOKUP_ARRAY 中的第一项开始搜索,而 -1 从最后一项开始。 2 执行二分查找,这要求您的 LOOKUP_ARRAY 按升序排列。 -2 也一样,但您的列表必须按降序排列。 1 和 -1 都适用于短列表,但如果您有大量可以排序的数据,请考虑使用 2 或 -2。

在下面的示例中,您可以看到如何通过简单地键入项目名称来提取单个项目的完整信息。您也不仅限于选择表格的第一列或第一行——您可以选择任何数据集进行浏览。

Excel 的功能远超您的预期

这些是您可以用来帮助您管理库存的一些公式。如果您想在生活的其他方面使用 Excel,这些公式可以帮助您解决现实生活中的问题

了解如何使用这些功能和一些格式可以让您的库存管理系统走很长的路。