Excel复杂公式示例 [英] Excel complex formula example

问题描述

因此，非常感谢您的帮助，我很努力.下面列出了我目前需要的内容.首先，我有一个包含许多个人和信息的数据集.我感兴趣的是几列.

So I would greatly appreciate your help I'm having quite a struggle. What I need currently is listed below. To begin I have a data set with many individuals and information. There are a few columns that I'm interested in.

表1:

因此每个Individual都被标记为Free，Arc 1，Arc 2或Arc 3.每个人也都有许多与之相关的人，最后是成本.

So each Individual is either labeled as Free, Arc 1, Arc 2 or Arc 3. Each individual also has a number of people associated with it and lastly a cost.

Individual  | # of people  | Cost  | Type | Compliant with Costs?
A           |3             |45     |Free  |Yes/No?
B           |2             |57     |Arc 2 |

表2:

然后我在下面有一张分解表.免费可以拥有1个2或3个人，费用为1个30-40(2个)和40-50个(3个)在20-30美元之间.

I then have a table below that is broken out. Free can have 1 2 or 3 individuals and cost can be between 20 - 30 dollars for 1 30-40 for 2 and 40-50 for 3.

            |Free  |Arc 1 |Arc2  | Arc 3    
# of people | Cost | Cost | Cost | Cost
1           |20-30 |30-40 | 60-70| 90-100
2           |30-40 |40-50 | 70-80| 100-110
3           |40-50 |60-70 | 80-90| 110-120

---

因此，我想采用上述Individual列，并说个人A1是否免费且有3个人，其成本在40-50美元之间，如果是，则不是.

---

So i want to take the Individual column noted above and say if Individual A1 is in Free and has 3 individuals is their cost between 40-50 dollars, if so yes, if not no.

我知道这会使用某些公式，如果公式可能很多，我也尝试过Index(match())，但也无法弄清楚，如果您能帮忙，将不胜感激.

I know this will use some if formulas probably many, I tried Index(match()) and such too but couldn't figure it out if you could help that would be greatly appreciated.

下面是一个示例Excel文件的示例，看起来只有两个人很容易，但是有数百个，所以我希望有一个简单的公式.再次感谢您的帮助.

Below is an example of a sample Excel file, it looks easy with just two individuals but there are hundreds so I'm hoping there is an easy formula. Again any help is greatly appreciated.

Excel屏幕快照链接(与上面的示例相同) >

Excel Screenshot Link (Same as example above)

推荐答案

如果按如下所示更改参考表，则可以轻松地使用sumifs来提取最小值和最大值，并查看您的费用是否介于两者之间

If you change your reference table as follows, you can use a sumifs easily to pull the min and the max and see if your cost is in between

#   Type    Min Max
1   Free    20  30
1   Arc 1   60  70
2   Free    30  40

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