excel短日期长日期_Excel价格查询日期产品名称

最新推荐文章于 2024-10-01 09:29:45 发布

culiao2169

最新推荐文章于 2024-10-01 09:29:45 发布

阅读量499

点赞数

文章标签： java python excel 大数据人工智能

原文链接：https://contexturesblog.com/archives/2017/04/27/excel-price-lookup-date-product-name/

版权

excel短日期长日期

If there's just one price per product in an Excel lookup table, you could use the INDEX and MATCH functions to to get that price. But what if the price changes occasionally, and your pricing list has multiple dates and prices for each product? How can you do a product price lookup based on invoice date and product name?

如果在Excel查找表中每种产品只有一个价格，则可以使用INDEX和MATCH函数来获取该价格。但是，如果价格偶尔变化，并且您的定价清单中每个产品都有多个日期和价格，该怎么办？如何根据发票日期和产品名称查找产品价格？

()

查找最新价格 (Find the Latest Price)

Someone asked that question on my Contextures blog last week, on my post that shows how to find the latest price for a specific product in Excel.

上周，有人在我的Contextures博客上的帖子中问了这个问题，该帖子显示了如何在Excel中找到特定产品的最新价格。

Before we tackle the new problem, here is the example from that old post, where we wanted the LATEST price. The screen shot below shows a lookup table, with product prices and dates.

在我们解决新问题之前，这是旧帖子中的示例，我们希望获得最新价格。下面的屏幕快照显示了一个查找表，其中包含产品价格和日期。

In that example, I used two formulas:

在该示例中，我使用了两个公式：

MAX and IF (array-entered with Ctrl+Shift+Enter) to find the latest date for a product =MAX(IF($A$2:$A$9=A12,$B$2:$B$9))
MAX和IF(使用Ctrl + Shift + Enter输入数组)以查找产品的最新日期= MAX(IF($ A $ 2：$ A $ 9 = A12，$ B $ 2：$ B $ 9))
Then, SUMIFS to find the product's price on that date.
然后，SUMIFS查找该日期的产品价格。

=SUMIFS($C$2:$C$9,$A$2:$A$9,A12,$B$2:$B$9,B12)
= SUMIFS($ C $ 2：$ C $ 9，$ A $ 2：$ A $ 9，A12，$ B $ 2：$ B $ 9，B12)

Here's the result of a lookup for Pens, in that example. The formula ignores all the earlier prices for Pens, and returns the latest one.

在该示例中，这是查找Pens的结果。该公式将忽略所有Pens的早期价格，并返回最新的价格。

查找发票的匹配日期 (Find the Matching Date for an Invoice)

The new question had a different twist – there was an on-going list of invoices, and it needed a lookup formula to find the product price based on the invoice date.

新问题有一个不同的转折–发票清单不断，并且它需要一个查找公式来根据发票日期查找产品价格。

In the screen shot below, the invoice list is at the left, and the product pricing lookup is on the right.

在下面的屏幕快照中，发票清单在左侧，产品价格查询在右侧。

For the "BBB" sale on Jan. 10th, we need the Jan 8th price from the lookup table.
对于1月10日的“ BBB”促销，我们需要查询表中1月8日的价格。
The Mar 15h sale of BBB needs the Mar 15th price.
BBB在3月15日的销售需要3月15日的价格。

您将如何解决？ (How Would You Solve It?)

In my Excel newsletter this week, I posted my solution, and asked if anyone had another way to get the correct prices.

在本周的Excel新闻中，我发布了我的解决方案，并询问是否有人有其他方法来获取正确的价格。

My formula was a long, complicated, array-entered monstrosity. I figured someone could find a simpler solution. Brace yourself – here it is:

我的公式是一个漫长而复杂的数组输入怪兽。我认为有人可以找到更简单的解决方案。振作起来–这是：

=INDEX(Products[Price], SMALL(IF(Products[Item]=[@Item], IF(Products[Pdate]<=[@Date], ROW(Products[Pdate]) - ROW(Products[[#Headers],[Pdate]]))), COUNTIFS(Products[Item],[@Item],Products[Pdate], "<="&[@Date])))

= INDEX(产品[价格]，小(IF(产品[项目] = [@项目]，IF(产品[日期] <= [@日期]]，ROW(产品[日期]])-ROW(产品[[#Headers ]，[Pdate]]))))，COUNTIFS(产品[Item]，[@ Item]，产品[Pdate]，“ <=”＆[@ Date])))))

It works, but it's very difficult to read and understand. Also, array formulas break easily, if someone presses Enter, instead of Ctrl+Shift+Enter

它可以工作，但是很难阅读和理解。另外，如果有人按Enter键而不是Ctrl + Shift + Enter键，则数组公式很容易破坏

备选方案1 – VLOOKUP (Alternative 1 – VLOOKUP)

Fortunately, some smart and creative people read my newsletter, and they sent me their solutions.

幸运的是，一些聪明又有创造力的人阅读了我的时事通讯，并将解决方案发送给我。

Van V and Tim O both suggested using VLOOKUP. You'll need to add a column at the left of the pricing table, with a formula to combine the product name and date.

Van V和Tim O都建议使用VLOOKUP 。您需要在定价表的左侧添加一列，并使用公式来组合产品名称和日期。

It's not necessary, but I added an underscore between the name and date, as a separator.

没必要，但是我在名称和日期之间添加了下划线作为分隔符。

= [@Item] & "_" & [@Pdate]

= [@Item]＆“ _”＆[@Pdate]

Next, sort the price list by product (item) and date – this is crucial to making this method work.

接下来，按产品(项目)和日期对价目表进行排序-这对于使此方法有效至关重要。

Then, in the Invoice list, use a VLOOKUP formula with an approximate match, to get the correct price.

然后，在“发票”列表中，使用具有近似匹配项的VLOOKUP公式来获取正确的价格。

=VLOOKUP([@Item] & "_" & [@Date], Products,4,TRUE)
= VLOOKUP([@ Item]＆“ _”＆[@Date]，Products，4，TRUE)

I left my original formula in column E, for comparison, and add the VLOOKUP in column F.

我将原始公式留在E列中进行比较，并在F列中添加VLOOKUP。

The VLOOKUP worked perfectly, so it's a good option, if:

VLOOKUP完美运行，因此，如果出现以下情况，这是一个不错的选择：

you can add a column to the pricing table,
您可以在定价表中添加一列，
and remember to sort it by Item and Date.
并记得按项目和日期对其进行排序。

Thanks to Van and Tim for sending their VLOOKUP solutions.

感谢Van和Tim发送了他们的VLOOKUP解决方案。

备选方案2-索引/匹配 (Alternative 2 – INDEX/MATCH)

Paul B and Tim O sent their solutions too, and they both used INDEX and MATCH. The setup is similar to the VLOOKUP solution:

Paul B和Tim O也发送了他们的解决方案，他们都使用了INDEX和MATCH 。设置类似于VLOOKUP解决方案：

add a column to the pricing table, to combine the item and date. However, it doesn't need to be on the left – it can be anywhere in the table.
在定价表中添加一列，以合并商品和日期。但是，它不必在左侧-可以在表格中的任何位置。
pricing table must be sorted by item name and date (Tim suggested a macro to do that, so it's easier).
定价表必须按商品名称和日期排序(Tim建议使用宏来完成此操作，这样会更容易)。

Here is the formula for the invoice table, to pull the correct price from the pricing table:

这是发票表的公式，用于从定价表中提取正确的价格：

=INDEX(ProductsLU[Price], MATCH([@Item] &[@Date], Products[ItemDate],1))
= INDEX(ProductsLU [Price]，MATCH([@ Item]＆[@ Date]，Products [ItemDate]，1))

多合一配方 (All-In-One Formulas)

Thanks to David P and Leonid K, who also sent formulas, and these didn't require any changes to the pricing table. All three of their formulas are better than the one that I created – shorter and easier to read.

感谢David P和Leonid K，他们也发送了公式，这些公式不需要对定价表进行任何更改 。他们的所有三个公式都比我创建的公式更好-简短且易于阅读。

1) David's first formula uses LOOKUP:

1)David的第一个公式使用LOOKUP：

=LOOKUP(1,1/FREQUENCY(0,1/(1+(Products[Item]=[@Item])*(Products[Pdate]<=[@Date])*Products[Pdate])),Products[Price])
= LOOKUP(1,1 / FREQUENCY(0,1 /(1+(Products [Item] = [@ Item]] **(Products [Pdate] <= [@ Date]] * Products [Pdate]]))，Products [价钱])

2) David's second formula uses ROUND, and is array-entered (Ctrl++Shift+Enter):

2)David的第二个公式使用ROUND ，并且输入了数组 (Ctrl ++ Shift + Enter)：

=ROUND(MOD(MAX( IF( (Products[Item]=[@Item])* (Products[Pdate]<=[@Date]), Products[Pdate] + Products[Price]/1000000)), 1)* 1000000,5)
= ROUND(MOD(MAX(IF((Products [Item] = [@ Item])*(Products [Pdate] <= [@ Date])，Products [Pdate] + Products [Price] / 1000000))，1) * 1000000,5)

3) Leonid's formula is an INDEX/MATCH formula:

3)Leonid的公式是INDEX / MATCH公式：

=INDEX(Products[Price],MATCH(1,1/((Invoice[@Item]=Products[Item])*(Products[Pdate]<=Invoice[@Date])*Products[Pdate])))
= INDEX(产品[价格]，MATCH(1,1 /((发票[@Item] =产品[Item]] **产品[Pdate] <=发票[@Date]] *产品[Pdate]))))

您会选择哪种解决方案？ (Which Solution Would You Choose?)

With so many formula options, which one would you choose for your workbook?

有这么多公式选项，您会为工作簿选择哪一个？

The VLOOKUP and first INDEX/MATCH formulas are easiest to understand. However, they require changes and maintenance to the pricing table.
VLOOKUP和第一个INDEX / MATCH公式最容易理解。但是，它们需要更改和维护定价表。
The All-In-One formulas are a bit more complicated, but don't require any changes to the pricing table
多合一公式有点复杂，但是不需要对定价表进行任何更改
My original formula works, but it's the longest, and most complicated. I'm fond of it though, after all the deep thought that went into it!
我原来的公式有效，但是它是最长，最复杂的。毕竟，我很喜欢它！

原始配方 (The Original Formula)

A couple of people asked how my original formula works, so here is a description of the key pieces.

几个人问我原始公式的工作原理，所以这里是关键部分的说明。

The INDEX function will return a specific item in an array (range of cells), and the SMALL function tells it which item to return (row number in the range).
INDEX函数将返回数组(单元格范围)中的特定项目，SMALL函数告诉它要返回的项目(范围内的行号)。
The SMALL function returns the nth smallest number in an array , and COUNTIFS calculates that “n”.
SMALL函数返回数组中的第n个最小数字，并且COUNTIFS计算该数字“ n”。
The ROW function returns the worksheet row number for each matching item. To get the row within the pricing list, we subtract the row number for the pricing list header.
ROW函数返回每个匹配项的工作表行号。要获得定价列表中的行，我们减去定价列表标题的行号。
- In cell E6, select the “array” part of the SMALL function in the formula bar, and press F9
  在单元格E6中，在编辑栏中选择SMALL函数的“ array”部分，然后按F9
- You will see this result: SMALL({FALSE;2;3;FALSE;FALSE;6;FALSE;FALSE}
  您将看到以下结果：SMALL({ FALSE; 2; 3; FALSE; FALSE; 6; FALSE; FALSE }
- For items that match the criteria, the numbers show their positions in the price lookup table. Items that don’t match show as FALSE.
  对于符合条件的商品，数字会在价格查询表中显示其位置。不匹配的项目显示为FALSE。
The COUNTIFS calculates how many prices in the table have the same item, and a price date on or before the invoice date. NOTE: For this to work, the pricing table must be sorted by date
COUNTIFS计算表中有多少个具有相同项目的价格，以及发票日期或发票日期之前的价格日期。注意：为使此功能起作用，定价表必须按日期排序
- With cell E6 still selected, in the formula bar, select the COUNTIFS part of the formula, and press F9
  在仍选择单元格E6的情况下，在公式栏中选择公式的COUNTIFS部分，然后按F9
- The result is 3
  结果是3
- =INDEX(Products[Price],SMALL({FALSE;2;3;FALSE;FALSE;6;FALSE;FALSE},3))
  = INDEX(产品[价格]，小({FALSE; 2; 3; FALSE; FALSE; 6; FALSE; FALSE}， 3 ))
- The 3rd smallest number in the array is 6, so the price from the 6^th row is returned by the INDEX function.
  阵列中的第三最小数是6，所以从6价格^第i行由索引函数返回。