Cálculo do número de usuários em uma laje

Question

Eu tenho uma planilha de excel na qual preciso calcular com base em números em uma laje, por exemplo:

1. 1-10 usuários @ $ 1
2. 11-20 usuários @ $ 0,8
3. 21 a 200 usuários a US $ 0,5
4. 201 usuários ou mais @ $ 0,3

Eu tenho a sensação de que isso tem algo a ver com a aritmética do módulo, mas não tenho a menor idéia por onde começar no Excel.

Até agora, tentei calcular os usuários por bloco, de acordo com a visualização da planilha:

Para o exemplo acima, com 60 usuários, haveria 10 usuários na primeira laje (1-10 usuários), 10 usuários na segunda laje (11-20 usuários) e 40 usuários na terceira laje (21 para 200 usuários).

O custo total se tornaria:

[$ 1 * slab 1 contagem de usuários (10 usuários)] + [$ 0.8 * slab 2 contagem de usuários (10 usuários)] + [$ 0.5 * slab 3 contagem de usuários (40 usuários)] = $ 38

Alguém pode, por favor, aconselhar sobre o melhor caminho a seguir.

    

Answer 1

Eu seguiria uma das duas abordagens. 1) programação macro / vba 2) VLookups e adicione alguns dados extras à sua planilha.

A macro seria:

1) Faça um loop pelas linhas na coluna "Slab End" para encontrar o número "slab" em que a Contagem de usuários se encaixa, localizando onde Slab Start > Contagem de usuários.

No seu exemplo, isso seria a laje 21 - 200.

2) Calcule a quantidade para os 'degraus da laje' (se isso fizer sentido), multiplicando a Largura da Laje por cada laje menor que a laje encontrada na etapa 1) e adicionando os resultados

No seu exemplo, isso seria (10 * 0,80) + (10 * 1,00)

3) Subtraia as larguras do User Count para encontrar o número que resta para a laje encontrada no passo 1).

No seu exemplo, isso seria 60 - 20 = 40 * 0,5.

No entanto, acho que adicionar alguns dados extras à sua planilha e usar o VLookup funcionaria sem recorrer a macros. Eu adicionaria colunas de 'CountLessThanSlab' e 'CostOfCount' a cada linha, resultando em uma folha que se parece com:

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

Em seguida, sua fórmula levaria a contagem de usuários (60), use VLookup para encontrar a linha correta (21, 200, 180, ...), subtraia 'CountLessThan' (20) da contagem de usuários para obter 40 e adicione a soma desse valor multiplicada pelo custo unitário da laje para o AmountLessThan na mesma linha. Algo como isso deve fazer isso:

=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))

Advertência: Eu não tenho excel na minha frente, atm.

Editar : sou um drongo

O conceito é válido, mas a fórmula está errada. (Lembrei-me incorretamente de que o valor pesquisado era menor que o valor relevante na tabela, mas é o contrário).

Esta fórmula funciona usando a configuração da tabela que mencionei acima:

=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))

    

Answer 2

Para ajudar na simplificação e minimizar entradas adicionais, adicione preços unitários em ColumnE e adicione os custos cumulativos em ColumnF e aplique a fórmula conforme abaixo:

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

Editar:

Entender os IFs aninhados pode ser um pouco mais fácil com B1 um intervalo nomeado (Usuários), o conteúdo de E subiu uma linha e o layout da fórmula foi alterado:

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