Estou tentando distribuir vários custos anuais recorrentes de negócios ao longo de 12 meses, de modo a tornar as despesas totais de cada mês o mais próximas possível. Fazer isso manualmente consome muito tempo e estou tentando descobrir uma maneira de classificar automaticamente esses valores nas colunas relevantes. Em anexo está uma planilha simplificada com alguns valores e outra imagem de uma tabela distribuída manualmente para exemplificar o resultado que procuro. Tentei usar o Solver no Excel para esse problema, mas não cheguei a lugar nenhum - estou aberto a tentar qualquer outro software de planilha para resolver isso (Numbers [onde estão os dados reais atualmente], Planilhas Google, SmartSheet etc.).
Imagem dos valores base:
Imagem de valores distribuídos:
Um colega sugeriu usar uma biblioteca python que tenha algo como scipy.optimize? Mas sou um iniciante em Python, então isso parece um pouco fora de questão.
Tentarei contar como resolvi esse problema no LibreOffice Calc passo a passo. Como as funções utilizadas na solução são as mesmas para todos os tipos de planilhas, você pode aplicar essa abordagem a qualquer ferramenta disponível.
Em primeiro lugar, vamos transformar um pouco os dados de origem - o que você mostrou na primeira captura de tela não é adequado. Vamos fazer uma tabela assim: três colunas conterão o nome do Item, seu custo e o número do mês ao qual esse custo deverá ser atribuído.
Um pouco à direita, começando pela coluna E da primeira linha, escreveremos tantos zeros quanto o número de grupos que precisamos obter. Neste caso estamos falando de 12 meses – isso significa que haverá 12 zeros. Se você precisasse carregar 20 caixas com aproximadamente o mesmo peso, haveria 20 zeros. Espero que você tenha a idéia.
Agora vamos começar os cálculos. Na célula C2 colocamos a fórmula
=MATCH(MIN(E1:P1);E1:P1;0). Isso procurará o menor valor na linha anterior e retornará seu número - essencialmente o número do mês em que o pagamento é devido, o pagamento da coluna B da segunda linha. Na célula E2 colocamos a fórmula=IF(COLUMN()-4=$C2;E1+$B2;E1). Esta fórmula adicionará o próximo pagamento à coluna onde o menor valor foi acumulado atualmente:Agora estique a célula E2 para a direita até o final da linha de zeros (no nosso caso, até a coluna P).
Agora estenda todas as fórmulas até o final dos dados disponíveis. Deve ser algo assim:
Selecione a tabela da esquerda e crie uma tabela dinâmica com base nela:
Você obterá um resultado como este:
Sim, em vez de janeiro, fevereiro, março você verá 1,2,3... Mas isso é um grande problema?
Se o resultado obtido for inferior ao ideal, tente alterar a ordem das linhas na tabela original - por exemplo, classifique-as - e atualize a tabela dinâmica: (Para seus dados de exemplo classificados em ordem decrescente, obtive 131, 136 , 131, 126, 121, 132. 122, 128, 127, 123, 125, 122 )