Como transformar / mapear range / array com alguma função

Question

Sou um novato do excel, então é possível que já exista uma resposta para isso e eu simplesmente não sabia os termos certos. Estou tentando transformar um conjunto de dados e usá-lo em outra fórmula, sem usar uma coluna intermediária.

Neste momento, tenho uma planilha com 10 ativos na coluna A e alguma pontuação na coluna B. Desejo calcular o percentual de um portfólio que eu alocaria para cada ativo, com base em sua pontuação, mas limitado para que nenhum ativo pode ter mais de 30% da carteira.

Estou tendo problemas para descobrir como impor esse limite sem usar outra coluna para o escore principal.

Dados os dados da pontuação na coluna B, tenho a coluna D, "Porcentagem de portfólio não ajustada", em que cada linha tem a fórmula =B3/SUM($B$2:$B$11)

Eu então tenho a coluna E, com os valores da coluna B, mas com limite de 30%, =MIN(D2, 0.3) .

Por fim, tenho a coluna F "Percentual de portfólio ajustado", =E2/SUM($E$2:$E$11) .

Como faço isso sem a coluna E?

Meu primeiro instinto é que provavelmente há alguma sintaxe para aplicar uma transformação a uma coluna antes de usá-la, estilo lambda. Ou seja no pseudocódigo, a coluna Porcentagem de portfólio ajustada deve poder ser calculada

let values = min(score / sum(scores), .3) / sum(adjusted) for score in scores
             where adjusted = min(score / sum(scores), .3) for score in scores

Eu não tenho idéia de como fazer isso com o excel, embora eu ficasse feliz se pudesse escrever uma fórmula com algum tipo de sintaxe lambda. Por exemplo. com pontuações na coluna B, a segunda linha seria

=MIN(B2/SUM($B:$B), .3)/SUM(x -> MIN(x/SUM($B:$B), .3), $B:$B) .

Identificar a lógica repetida em uma função local seria legal, mas talvez seja pedir muito de algo que não seja uma linguagem de programação. Talvez essa transformação de dados seja pedir muito de algo que não é uma linguagem de programação?

    

Answer 1

Existe uma maneira de fazer isso.

Se, digamos, 1 ou 2 valores estão definidos para 30%, então os outros valores precisam ser escalados para que eles adicionem 70% ou 40%, etc. Essa fórmula faz isso:

 =IF(C2>=0.3,0.3,(C2/(SUMIF(C$2:C$11,"<0.3")))*(1-0.3*(COUNTIF(C$2:C$11,">=.3"))))

John, a sintaxe do Excel pode ser obtusa, então deixe-me guiá-lo por isso. A fórmula IF () preenche um dos dois valores dependendo se o% na coluna C é maior que ou igual a 30%. Se for, a fórmula retorna 0,3. Caso contrário, retorna o cálculo no restante da fórmula.

A primeira parte, C2 / SUMIF (), fornece a% que o valor na coluna C representa dos valores que são < 30%. Essas porcentagens somariam 100%, então elas são multiplicadas pela segunda parte, 1-0,3 * COUNTIF (). Começa com 1 e subtrai 0,3 para cada valor > = 30%. Portanto, os valores da primeira parte são escalonados por um fator de 0,7 ou 0,4, etc.

Se é possível ter mais de 3 ações > 30%, então alguma verificação de erro provavelmente está em ordem. Espero que isso ajude, e eu gostei de ver suas expressões lambda.

    

Answer 2

Talvez seja necessário repensar sua metodologia ligeiramente. Considere:

Em que coluna B são pontuações brutas, coluna C são versões normalizadas da coluna B , coluna D são versões recortadas da coluna C e a coluna E é a versão normalizada da coluna D .

O problema é um item como Ophelia. Seu valor normalizado excede 30%. Quando é limitado e depois voltado a normalizar, o valor ainda excede 30%.