数组公式基础，而不是嵌套的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.

下表列出了产品的数量折扣的价格和各种临界值. IMO公式，例如{= 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)}

其中临界值是指所有值，而价格"是指应该完成此操作的价格点.我不想执行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屋！