☰

根据资源数量计算工作日的 Excel 公式

tag-icon • tag-icon microsoft-excel worksheet-function
根据资源数量计算工作日的 Excel 公式

我有一个 Excel 电子表格单元格，如下所示：

   A             | B           | C                    | D              | E
1  TASK          | WORK (days) | RESOURCES            | PLUMBER (days) | ELECTRICIAN (days)
2  Fit bathroom  | 3           | Plumber, Electrician | 1.5            | 1.5

单元格 A2、B2 和 C2 是手动输入的。

单元格 D2 中的公式取“工作”天数（单元格 B2）的总数，然后将其除以单元格 C2 中指定的资源数量（每个资源用逗号分隔）：

=IF(ISNUMBER(SEARCH("Plumber",$C2)),$B2/(LEN($C2)-LEN(SUBSTITUTE($C2,",",""))+1),0)

单元格 E2 的公式类似：

 =IF(ISNUMBER(SEARCH("Electrician",$C2)),$B2/(LEN($C2)-LEN(SUBSTITUTE($C2,",",""))+1),0)

我想修改我的公式以便能够处理 C2 单元格中的以下类型的条目：

Plumber, Electrician [200%]

在这种情况下，单元格 D2 应设置为 1，单元格 E2 应设置为 2。

我希望这是有意义的，并希望得到有关如何实现这一点的任何帮助。

答案1

此宏函数将起作用：

Public Function resourceDays(resourceName As String, totalDays As Integer, totalResources As String)
    resourceDays = 0
    Dim eachResource() As String
    eachResource = Split(totalResources, ",")
    totalValues = 0
    For i = 0 To UBound(eachResource)
        thisresource = eachResource(i)
        startPct = InStr(thisresource, "[")
        If startPct = 0 Then
            resourceValue = 1
            totalValues = totalValues + resourceValue
            If UCase(Trim(thisresource)) = UCase(Trim(resourceName)) Then
                thisvalue = resourceValue
            End If
        Else
            endPct = InStr(thisresource, "%")
            PctValue = (Mid(thisresource, startPct + 1, endPct - startPct - 1)) / 100
            Tempresource = Mid(thisresource, 1, startPct - 1)
            resourceValue = PctValue
            totalValues = totalValues + resourceValue
            If UCase(Trim(Tempresource)) = UCase(Trim(resourceName)) Then
                thisvalue = resourceValue
            End If



        End If
    Next i
    resourceDays = totalDays * (thisvalue / totalValues)
 End Function

使用Alt+打开 VBA / 宏F11，在本工作簿插入一个新的模块并将代码粘贴在右侧。

在细胞上D2代入公式=resourceDays(D1,B2,C2)，其中参数为

resourceDays=(Name of Resource, Total Of Days, String Of Total Resources)。

答案2

正如 Mate Juhasz 指出的那样，使用工作表功能来做到这一点非常困难，但并非不可能。

如果你真的想这样做，那么你最好使用临时变量（使用一些单元格来存储临时值）然后隐藏它们。（例如，设置 G 列的宽度 = 0）

您可以这样做：

G1 =SEARCH(",", C2)
G2 =TRIM(MID(C2,G1+1,LEN(C2)))
G3 =TRIM(MID(C2,G1+1,LEN(C2)))
G4 =MID(G2, SEARCH("[",G2)+1, SEARCH("]", G2)-SEARCH("[",G2)-1)
G5 =MID(G3, SEARCH("[",G3)+1, SEARCH("]", G3)-SEARCH("[",G3)-1)
G6 =IF(ISNUMBER(G4+0),G4+0,1)
G7 =IF(ISNUMBER(G5+0),G5+0,1)
G8 =B2 / (G6+G7) * G6
G9 =B2 / (G6+G7) * G7

D3 =IF(ISNUMBER(SEARCH("Plumber",G3)), G9, G8)
E3 =B2-D2

如果您不想使用这些临时变量，那么您可以用它的值替换每个单元格，但公式会很长。

但是，正如 Mate Juhasz 指出的那样，这非常困难。最好使用宏。

答案3

正如其他朋友提到的那样，实现目标将非常复杂。实现目标的更好方法是用数字思考。我认为如果你改变单元格的工作方式会更有效。例如，手动修改 D 列和 E 列。而 C 列有 IF 语句，它将在 D 列和 E 列中搜索数字输入。这将使您更容易修改或添加工作表中的任何其他功能。这是我的意思的一个例子：

=IF(AND(ISNUMBER(D3),ISNUMBER(E3)),"Plumber, Electrician",IF(ISNUMBER(D3),"Plumber",IF(ISNUMBER(E3),"Electrician","")))

当您在 C 列中使用上述函数时，它将搜索 D 列和 E 列中输入的数字，并输入正确的资源。从同一功能，您还可以进一步扩展它。

我之所以改变输入方式，是因为Excel本来就是用来处理数字的，而且输入数字比输入字符串要快，这样可以节省时间。再想想吧。

相关内容