☰

计算一个层级中的用户数量

tag-icon • tag-icon microsoft-excel
计算一个层级中的用户数量

我有一张 Excel 表，需要根据板中的数字进行计算，例如：

  1. 1-10 位用户 @ 1 美元
  2. 11-20 位用户 @ 0.8 美元
  3. 21-200 位用户 @ 0.5 美元
  4. 201 个用户或更多 @ 0.3 美元

我感觉这与模数运算有关，但不知道在 Excel 中从哪里开始。

到目前为止，我已经尝试根据电子表格视图计算每个块的用户数：

板坯

对于上述示例，如果有 60 个用户，则第一个等级（1-10 个用户）中有 10 个用户，第二个等级（11-20 个用户）中有 10 个用户，第三个等级（21 到 200 个用户）中有 40 个用户。

总成本将变为：

[ $1 * slab 1 用户数 (10 位用户) ] + [ $0.8 * slab 2 用户数 (10 位用户) ] + [ $0.5 * slab 3 用户数 (40 位用户) ] = $38

有人能就最好的前进方向提出建议吗？

答案1

我会遵循以下两种方法之一。1）宏/vba 编程 2）VLookups 并向您的工作表添加一些额外的数据。

该宏将：

1) 循环遍历“Slab End”列中的行，通过定位“Slab Start”>“User count”来找到适合用户数的“slab”数量。

在您的示例中，这将是板 21 - 200。

2）计算出“楼板台阶”的数量（如果有意义的话），通过将每个低于步骤 1）中找到的楼板的楼板宽度乘以楼板宽度，然后将结果相加

在您的示例中，这将是 (10 * 0.80) + (10 * 1.00)

3) 从用户计数中减去宽度，得到步骤 1) 中找到的板的剩余数量。

在您的示例中，这将是 60 - 20 = 40 * 0.5。

但是，我认为在工作表中添加一些额外数据并使用 VLookup 可以工作，而无需使用宏。我会在每一行中添加“CountLessThanSlab”和“CostOfCount”列，从而得到如下所示的工作表：

Start    End     Width     CountLessThan    CostLessThan    SlabUnit
    1     10        10                                          1.00
   11     20        10                10           10.00        0.80
   21    200       180                20           18.00        0.50
  201    500       300               200          108.00        0.30

然后，您的公式将采用用户计数 (60)，使用 VLookup 查找正确的行 (21、200、180、...)，从用户计数中减去“CountLessThan”(20) 得到 40，然后将该数字乘以 Slab Unit 成本的总和添加到同一行中的 AmountLessThan。类似这样的操作应该可以做到：

=vlookup(B1, $B$3:$E$7, 4, true) + ((B1 - vlookup(B1, $B$3:$E$7, 3, true)) * vlookup(B1, $B$3:$E$7, 5, true))

警告：目前我面前没有 Excel。

编辑：我是一只卷尾

概念正确，但公式错误。（我记错了，查找的值小于表中的相关值，但事实恰恰相反。）

使用我上面提到的表格设置，此公式有效：

=VLOOKUP(B1, $A$3:$F$7, 5, TRUE) + ((B1 - VLOOKUP(B1, $A$3:$F$7, 4, TRUE)) * VLOOKUP(B1, $A$3:$F$7, 6, TRUE))

答案2

为了简化并减少进一步的输入，在 ColumnE 中添加单价，在 ColumnF 中添加累计成本，并应用以下公式：

=IF(B1>B6,F6+E7*(B1-B6),IF(B1>B5,F5+E6*(B1-B5),IF(B1>B4,F4+E5*(B1-B4),B1*E4)))

SU586492 示例

编辑：

使用命名范围（用户）、分流一行 B1的内容和更改公式布局，理解嵌套的 IF 可能会更容易一些：E

=IF(用户>B6,F6+E6*(用户-B6),
 如果(用户>B5,F5+E5*(用户-B5),
 如果(用户>B4,F4+E4*(用户-B4),
                E3*(用户))))

相关内容