如何将Excel值转换为桶？ [英] How to convert Excel values into buckets?

问题描述

我在Excel中有一组数据，其中一列是估计值（周数）。

我想要一个Excel公式将其存入

• 小


• 中


• 大

如果值为0 - 10，则将其设置为小。如果数值是10 - 20把它放在中，等等。 。 。

如果除了嵌套if语句之外，还有其他优化方法吗？

解决方案

可能不是你想要的，但是如何使用Excel的条件格式化功能

编辑：
作为备用你可以创建一个vba函数，它可以作为一个公式来完成你的计算。
类似于

 函数getBucket（rng作为范围）作为字符串
 Dim strReturn As String 
 
选择案例rng.Value 
案例0至10 
 strReturn =小
案例11至20 
 strReturn =中等
案例21至30 
 strReturn =大
案例31至40 
 strReturn =巨大
案例否
 strReturn =OMG !!! 
 End Select 
 getBucket = strReturn 
 End Function 
  

I have a set of data in Excel and in one column is a estimate (number of weeks)

I want an Excel formula to bucket it into

• Small
• Medium
• Large

where if the value is 0 - 10 then put it Small. If the value is 10 - 20 put it in Medium, etc . . .

if there any elegant way of doing it besides having nested if statements all put together?

解决方案

May be not quite what you were looking for but how about using conditional formatting functionality of Excel

EDIT: As an alternate you could create a vba function that acts as a formula that will do the calulation for you. something like

Function getBucket(rng As Range) As String
    Dim strReturn As String

    Select Case rng.Value
        Case 0 to 10
            strReturn = "Small"
        Case 11 To 20
             strReturn = "Medium"
        Case 21 To 30
             strReturn = "Large"
        Case 31 To 40
             strReturn = "Huge"
        Case Else
             strReturn = "OMG!!!"
    End Select
    getBucket = strReturn
End Function

这篇关于如何将Excel值转换为桶？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！