从图表中提取值，并按excel中的条件过滤 [英] Extract a value from the chart, filtered by criteria in excel

问题描述

我在excel中有两张这样的纸:

I've got two sheets like this in excel :

价格走势图:

                            **Post AB**    **Post  Tenn**   **Post DN**
                         Price 10.1-10.20  Price 10.1-10.20  Price 10.1-20.1
CityOrigin  Destination      20 kg  40 kg    20 kg  40 kg     20 kg  40 kg

New York     Madrid          45      40     40      50       45      40
Los Angeles  Madrid          65      70     70      70       56      60
Oregon       Paris           89      100    110     105      74      98
Washington   Paris           34      80     45      65       45      69

和工作表:

                                            Price Rate
Post Career  CityOrigin          Date            20KG      40KG
Post AB      New Tork          =Today()            ?        ?
Post Tenn    Los Angles            "               ?        ?
Post DN      Oregon                "               ?        ?

我想知道，是否可以将今天日期与邮政职业生涯和城市来历一起使用，以便从价格表中仅提取20kg和40kg包装中今天有效的费率?

I am wondering, is it possible to use today date with Post Career and City origin to extract only rates that are actually valid for today for 20kg and 40kg packages from price chart sheet?

我理想的结果应该是这样的:

My ideal result should be look like this :

                                                   Price Rate
Post Career  CityOrigin          Date            20KG      40KG
Post AB      New York             10/20           40       45
Post Tenn    Los Angles           10/20           70       70
Post DN      Oregon               10/20           74       98

我的问题是应该使用哪个函数根据价格表上的日期和职业生涯来调用价格?多重查询?

My question is which function I should use to call the price based on date and post career from price sheet? multiple Lookup??

推荐答案

这是到目前为止，我不知道数据结构中其他日期范围的情况，但这应该可以为您提供一些帮助.

So here is what I have so far without knowing how other date ranges will be like in your data structure, but this should give you something to work on.

我在cell D13中输入的公式是:

=INDEX($C$4:$H$7,MATCH($B13,$A$4:$A$7,0),MATCH($A13,$C$1:$H$1,0)+IF(D$12="20 kg",0,1))

基本上，我只是使用INDEX/MATCH查找行号和列号.一旦有其他数据可以播放，如果找不到解决方法，我可以再看一遍.

Basically I just use INDEX/MATCH to lookup the row and column numbers. Once you have other data come in to play, I can take another look if you can't find a way around it.

请注意，我已经删除了第1行上的*符号，因此在MATCH函数中更容易执行此操作，否则，您将需要使用数组公式来执行此操作，而这可能不是您所希望的方式想去.

Please note that I have removed the * sign on row 1 so it is easier to do in the MATCH function, otherwise, you will need to use array formula to do this and that is probably not the way you want to go.

这篇关于从图表中提取值，并按excel中的条件过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！