Fórmula do Excel para calcular dias de trabalho com base no número de recursos

Question

Eu tenho uma célula de planilha do Excel com a seguinte aparência:

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

As células A2, B2 e C2 são inseridas manualmente.

A fórmula na célula D2 leva o número total de dias "Work" (célula B2) e os divide pelo número de recursos especificados na célula C2 (cada recurso é separado por uma vírgula):

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

A fórmula para a célula E2 é semelhante:

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

Eu gostaria de modificar meu forumla para poder lidar com o seguinte tipo de entrada na célula C2:

Plumber, Electrician [200%]

Nesse caso, a célula D2 deve ser definida como 1 e a célula E2 deve ser definida como 2.

Espero que isso faça sentido e agradeço qualquer ajuda sobre como conseguir isso.

    

Answer 1

Esta função macro funcionará:

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

Abra o VBA / Macros com Alt + F11 , sob ThisWorkbook insira um novo módulo e cole o código no lado direito.

Na célula D2 coloque a fórmula =resourceDays(D1,B2,C2) , onde os parâmetros são

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

    

Answer 2

Como Mate Juhasz apontou, é muito difícil fazê-lo com a função de planilha, mas não é impossível.

Se você realmente quer fazer isso, então é melhor usar a variável temporária (Use algumas células para armazenar o valor temporário) e depois ocultá-las. (por exemplo, definir largura da coluna G = 0)

Você pode fazer isso assim:

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

Se você não quiser usar essas variáveis temporárias, poderá substituir todas as células pelo seu valor, mas a fórmula será muito longa.

No entanto, assim como Mate Juhasz apontou, isso é muito difícil. Usar macro é melhor.

    

Answer 3

Atingir o seu objetivo será realmente complicado, tal como outros colegas mencionaram. A melhor maneira de alcançar seu objetivo é pensar numericamente. Eu vejo se você mudar o modo de trabalho das células será mais eficaz. Por exemplo, faça a coluna D & E modificado manualmente. Enquanto a coluna C possui uma instrução IF que procura nas colunas D e E pela entrada de números. Isso facilitará a modificação ou inclusão de outras funções na planilha. Este é um exemplo do que eu quis dizer:

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

Quando você usa a função acima na coluna C, ele procura por entrada de número na coluna D e E e coloca os recursos corretos. Da mesma função, você também pode estender ainda mais.

A razão pela qual eu mudei o modo de entrada, é porque o Excel queria trabalhar com números, e digitar números é mais rápido do que digitar strings, já que você economizará tempo. Pense nisso novamente.