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
• 日期:14年3月
• 部门:财务
• 建筑物:空中大厅
• 办公室:Sky 420
• 分配给:汤普森(Bell Thompson)
• 旧序列号:9XUUA56
• 旧型号:9030 AIO
• 部署:是
• 转移:否
• 状态:已完成
• 注意:已添加到库存"列表中

Result

• New Serial # (reference cell): CK9UZA2
• Date: Mar-14
• Department: Finance
• Building: Sky Hall
• Office: Sky 420
• Assign to: Bell Thompson
• Old Serial #: 9XUUA56
• Old Model: 9030 AIO
• Deploy: Yes
• Transfer: No
• Status: Completed
• Notes: Added to the Inventory list

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

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函数以及相对的单元格引用来返回列索引号(第3个参数).

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

在这种特定情况下，有可能利用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!

其他说明:

Further notes:

尽管这只是您如何使用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屋！