数组公式基础而不是嵌套的 if-else [英] Array formula basics instead of nested if-else

问题描述

我已经在该网站上搜索了数组公式，但对我无法弄清楚的数组公式的一些基本问题感到困扰.

I have scoured the site for array formulas but am troubled by some basic issue of an array formula that i am not able to figure out.

下表列出了产品批量折扣的价格和各种截止点.海事组织一个公式，如，{=IF (A2'<'Cutoff,A2*PPVM)}

the following table lists prices and various cutoffs for volume discounts for a product. IMO a formula like, {=IF (A2'<'Cutoff,A2*PPVM)}

其中 cutoff 是指所有值，而price"是指应该起作用的价格点.我不想执行 IFS 或 IF-ELSE，因为我可能想稍后添加一行/删除一行.

in which cutoff refers to all the values and "price" refers to the price points should do the trick. I do not want to do IFS or IF-ELSE because I might want to add a row/delete a row later.

基本上，如果我有 5000 件产品，价格将是 5000*22.5，依此类推.任何人都可以建议可用于此的数组公式的基本结构吗?

Essentially, if i have 5000 products, the price would be 5000*22.5, and so on and so forth. Can anyone suggest the basic structure of an array formula that can be used for this?

推荐答案

类似于 PNUT 的回答但又有所不同.我假设截止点的值接收截止点的值.即 250 得到 30 的价格.我假设的另一件事是数量必须是整数.IE 你不能卖半个钉子.

Simmilar to PNUT's answer but yet different. I worked on the assumption that a value at the cut off received the value of the cutoff. ie 250 get 30 for a price. The other thing I assumed was that quantities had to be integers. IE you cannot sell half a nail.

我在 F3 中使用了以下公式

I used the following formula in F3

=INDEX(B:B,IFERROR(MATCH(D3-1,A:A,1),1)+1)*D3

我在 E3 中使用了以下公式来验证从表中提取的价格:

I used the folllowing formula in E3 just to verify what price was being pulled from the table:

=INDEX(B:B,IFERROR(MATCH(D3-1,A:A,1),1)+1)

更重要的是，如果您的数量超过 22000 或您的桌子的最后一个数字，它会给您 0 的价格和成本.

The kicker is if you have quantities greater than 22000 or the last number of your table, it will give you 0 for the price and therefore cost.

这篇关于数组公式基础而不是嵌套的 if-else的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！