Формула Excel для расчета рабочих дней на основе количества ресурсов

Question

У меня есть ячейка таблицы 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.

Я надеюсь, что это имеет смысл, и буду признателен за любую помощь, как этого добиться.

Answer 1

Как отметил Мате Юхасз, это очень трудно сделать с помощью функции листа, но не невозможно.

Если вы действительно хотите это сделать, то лучше использовать временную переменную (используйте несколько ячеек для хранения временного значения), а затем скрыть их. (например, установить ширину столбца 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

Если вы не хотите использовать эти временные переменные, вы можете заменить каждую ячейку ее значением, но формула будет очень длинной.

Однако, как указал Мате Юхасз, это очень сложно. Используйте макрос лучше.

Answer 2

Достижение вашей цели будет действительно сложным, как упоминали другие товарищи. Лучший способ достичь своей цели - думать численно. Я вижу, если вы измените способ работы клеток, будет более эффективным. Например, измените столбец 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 должен работать с числами, а также вводить числа быстрее, чем вводить строки, поскольку вы сэкономите время. Подумай об этом еще раз.

Answer 3

Эта макро функция будет работать:

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

Откройте VBA / Macros с помощью Alt+ F11, в ThisWorkbook вставьте новый модуль и вставьте код с правой стороны.

В ячейку D2 поместите формулу =resourceDays(D1,B2,C2) , где параметры

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