用于库存的 Excel 公式 [英] Excel Formula for Inventory

问题描述

我是excel公式的新手，所以我不知道所有的功能.我正在尝试使用 Vlookup 或类似的东西为我的库存 Excel 工作表创建一个公式.

I'm new to the excel formula, so I don't know all the functions. I'm trying to create a formula using Vlookup or something akin to it for my inventory excel sheet.

我应该能够输入一个序列号(可能只有新版本)，它会在表格中搜索.

I should be able to enter a serial number(possibly only the new version), where it will search up in the table.

Date | Serial # (new) | Model (new) | Serial # (old) | Model (old) | Department | Assign | Building | Office | Deploy | Transfer | Status | Note |

示例

Mar-14 | CK9UZA2 | 7060 | 9XUUA56 | 9030 AIO | Finance | Bell Thompson | Sky Hall | Sky 420 | Yes | No | Completed | Added to the Inventory list

结果

• 新序列号(参考单元):CK9UZA2
• 日期:3 月 14 日
• 部门:财务
• 建筑:天空大厅
• 办公室:Sky 420
• 分配给:Bell Thompson
• 旧序列号:9XUUA56
• 旧型号:9030 AIO
• 部署:是
• 转移:否
• 状态:已完成
• 注意:已添加到库存列表

任何帮助都会有所帮助，特别是如果有人可以为我分解以供将来参考

Any assistance will be helpful, especially if someone can breakdown for me for future reference

推荐答案

为了将来参考，可以通过多种方式查找值(例如:VLOOKUP 和 HLOOKUP)，但是可以使用 INDEX 和 MATCH 进行非常强大的组合，至少与其他函数一样快，最多快得多.

For future reference, looking up values can be done in several ways (e.g.: VLOOKUPand HLOOKUP), however a very powerful combination can be made using INDEX and MATCH, being at least as fast as other functions and at best much faster.

INDEX

INDEX 函数函数返回一个值或对表或范围内的值的引用，并具有以下参数:

The INDEX function function returns a value or the reference to a value from within a table or range and has got the following parameters:

• 需要一组单元格(矩阵)
• 我们想要返回值的行的索引号
• 我们想要返回一个可选值的列的索引号

所以语法看起来像:=INDEX(RangeOfCells,RowIndex,[ColumnIndex])

MATCH

MATCH 函数在单元格范围内搜索指定项目，然后返回该项目在该范围内的相对位置.它有以下参数:

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. It has got the following parameters:

• 需要的查找值
• 需要的单元格(矩阵)查找数组
• 可选的匹配类型 (-1,0,1)(如果省略则自动输入 1)

所以语法看起来像:=MATCH(LookupValue,LookupArray,[MatchType])

INDEX + MATCH

让我们在 Sheet1 中想象一个简单的数据集，如下所示:

Let's imagine a simple dataset in Sheet1 like the following:

| Header1 | Header2 | Header3 | Header4 |
|---------|---------|---------|---------|
| ID1     | A       | Val1    | Month1  |
| ID2     | B       | Val2    | Month2  |
| ID3     | C       | Val3    | Month3  |
| ID4     | D       | Val4    | Month4  |
| ID5     | E       | Val5    | Month5  |

以及在 Sheet2 中搜索值的以下设置:

And the following setup to search for a value in Sheet2:

| Header1 | Header2 | Header3 | Header4 |
|---------|---------|---------|---------|
| ID3     |         |         |         |

现在，如果您对 Header2 到 Header4 的值感兴趣，通过在 Header1 下查找特定值，您可以应用以下内容B2 中的技术并向右拖动.

Now if you are interested in the value of Header2 up to Header4 by looking up a specific value under Header1 you can apply the following technique in B2 and drag right.

=INDEX(Sheet1!$B:$D,MATCH($A2,Sheet1!$A:$A,0),COLUMN(A1))

结果如下:

| Header1 | Header2 | Header3 | Header4 |
|---------|---------|---------|---------|
| ID3     | C       | Val3    | Month3  |

如果你考虑以上关于 INDEX 和 MATCH 你可以看到我给了 INDEX 它是必需的单元格数组(第一个参数)，使用 MATCH 返回行索引号(第二个参数)，并使用 COLUMN 函数以及相关单元格引用返回列索引号(第三个参数).

If you take into consideration the above about INDEX and MATCH you can see that I gave INDEX it's required array of cells (1st parameter), used MATCH to return a row index number (2nd parameter) and have utilized the COLUMN function along with a relative cell reference to return the column index number (3rd parameter).

MATCH 函数被赋予了一个查找值(第一个参数)、一个查找数组(第二个参数)，我使用了匹配类型 0，它告诉函数查找精确匹配.

The MATCH function has been given a lookup value (1st parameter), a lookup array (2nd parameter) and I have used match type 0, which tells the function to look for an exact match.

VLOOKUP

在这种特定情况下，可以使用 VLOOKUP 函数.但我会尝试说明为什么我更喜欢 INDEX + MATCH 组合:

In this specific case it would have been possible to utilize the VLOOKUP function. But I'll try to set out why I would prefer the INDEX + MATCH combination:

• VLOOKUP 几乎没有灵活性，因为查找值必须位于查找矩阵的最左列，而 INDEX 使您可以选择通过第三个参数.
• 如前所述，虽然 VLOOKUP 是更受欢迎的选项，但 INDEX + MATCH 是更快的选项.如果您追求速度，请使用它！
• VLOOKUP gives you little flexibility as the lookup value must sit in the left-most column of your lookup matrix, whereas INDEX gives you the option to return any column through the third parameter.
• As previously mentioned, while VLOOKUP is the more popular option, INDEX + MATCH is the faster option. If you go for speed, then use this!

进一步说明:

虽然这只是关于如何使用 INDEX + MATCH 的组合的简单细分，但还有一些事情需要考虑:

While this was just a simple breakdown of how you could use the combination of INDEX + MATCH, there are a few more things to consider:

• 在上面的示例中，我使用了绝对、半绝对和相对单元格引用(注意与 $ 符号的区别).利用此技术可以将公式拖动到右侧、左侧、底部或顶部.
• 设置表格而不是矩阵.您可以引用该表中的列而不是完整的列.与列中所有可能的行相比，仅搜索几行总是 (AFAIK) 更快！
• 当你变得更高级时，你会注意到 INDEX 和 MATCH 会在很多方面更频繁地显示为生命安全的东西:)
• 在查找完全匹配时，MATCH 可能会在根本找不到查找值时返回错误.当您看到任何 #N/A 错误时，需要考虑这一点.解决方法是在公式中使用 ISNA 或 IFERROR 函数.
• In the example above I used absolute, semi-absolute and relative cell references (note the differences with the $ sign). Utilize this technique to be able to drag a formula to the right, left, bottom or top.
• Set up a table instead of a matrix. You can refer to columns within that table instead of full columns. Searching only a few rows compared to all possible rows in a column is always (AFAIK) faster!
• When you get more advanced you'll notice INDEX and MATCH is something that will appear as a lifesafer much more often in many ways :)
• While looking for an exact match, MATCH might return an error when the lookup value simply isn't found. This is something to consider when you see any #N/A error. A workaround is a ISNA or IFERROR function within the formula.

我希望这能让你开始！查看我提供的链接以获取更深入的信息.

I hope that gets you started! Check the links I included for some more in-depth information.

这篇关于用于库存的 Excel 公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！