Estamos trabalhando com uma tabela de centenas de projetos, com um valor total bem acima do nosso orçamento. Para priorizar os projetos, decidimos selecionar os projetos mais antigos primeiro, selecionando todos até que o limite de orçamento seja atendido.
Como exemplo, a tabela abaixo mostra o projeto (Coluna A), data de início (Coluna B) e quantidade (Coluna C). O valor do orçamento é definido em US $ 3.500, mas pode ser alterado, mas o valor total dos projetos é de US $ 5.000, portanto, nem todos podem ser aceitos. Eu gostaria de uma fórmula para a coluna D (preenchida aqui para ilustração), que pode selecionar os projetos até $ 3.500 por ordem da data de início.
Eu brinquei com isso usando a fórmula Small ou outros cálculos longos do SUMIF, mas parece que não consigo capturar o valor do Budget, que pode variar a qualquer momento, e construir a relação entre a data de início e a data de início. despesas.
Uma observação é que a tabela real tem 100s de linhas com um orçamento acima de $ 10mil - no exemplo abaixo a lista exclui o projeto 4, pois adicioná-lo nos levaria ao orçamento, mas na prática uma fórmula que exclui mais, mas inclui o projeto 4 por causa da questão do arredondamento.
D4=IF($C$1>=SUMIFS($C$4:$C$9,$B$4:$B$9,"<=" & B4),C4,"-")
PS.
using the Small formula or other lengthy SUMIF calculations
Eu recomendaria usar apenas SUMIFS (). Ele pode ser facilmente estendido com condições adicionais, enquanto a extensão de SUMIF () para SUMIFS () precisa reordenar os parâmetros da função.
Akina e Rajesh H forneceram soluções elegantes que funcionam para o exemplo da questão. No entanto, esta abordagem tem uma lacuna em que eles param muito cedo. Se você tiver um projeto grande que exceda o orçamento, seja sozinho ou com o total cumulativo, ele para com o projeto anterior em vez de ignorar o projeto não qualificado e continuar procurando outros projetos qualificados. Por exemplo:
Nocasotop,substituíumgrandeprojetopelosegundocronologicamente.Issoteriaempurradooacumuladosobreoorçamento,porissoparoucomumúnicoprojetoemvezdeadicionarosposterioresqueestariamdentrodoorçamento.
Nosegundocaso,torneioprimeiroprojetomaiorqueoorçamento.Emvezdepulareprocurarporoutrosquesequalificaram,elepáraenãoretornanada.
Minhasuposiçãoéqueoobjetivoépreencheroorçamento,dandoprioridadeaosprojetosmaisantigosprimeiro.Seumprojetoantigonãoseencaixanoorçamento,presumoquenãodevemosparardeolharparaesseponto.
Aquiestáumasoluçãosimplesquenãotemesseproblema.
Eufizváriassubstituiçõesdegrandevalorapenasparailustrarcomoafórmulafunciona.
Comoprimeiropasso,classifiqueiosdadoscronologicamente.Aprimeirafórmula(emD4):
=IF(C4>$C$1,"",C4)
Se o primeiro valor cronologicamente estiver abaixo do valor do orçamento, ele o utiliza, caso contrário, deixa um valor nulo. O restante das fórmulas é copiado do segundo valor (em D5):
=IF(SUM($D4:D$4)+C5>$C$1,"",C5)
Isso soma os valores de qualificação anteriores acima e adiciona o valor para esse projeto. Se o projeto colocar o total acima do orçamento, ele postará um valor nulo, caso contrário, adicionará o valor para o projeto. Ele faz isso para cada projeto subsequente, então você sempre obtém todos os projetos qualificados.
Observe que você pode melhorar sua estratégia. Por exemplo, suas regras param em US $ 2400 para os valores no meu exemplo. Você poderia se aproximar do orçamento adicionando outras prioridades que permitiriam selecionar projetos posteriores maiores do que os pequenos. Se a sua lista de projetos é o tempo que você descreve, provavelmente haverá muitas combinações próximas no tempo que poderiam preencher o orçamento. Ao diminuir a primeira restrição mais antiga, você pode se aproximar do orçamento. Você também pode combinar idade com outros objetivos, como tamanho do projeto; por exemplo, conclua alguns projetos grandes em comparação com muitos projetos pequenos.
Esse problema pode ser resolvido usando a seguinte fórmula também:
=IF(SUMPRODUCT(($C$4:$C$9)*($B$4:$B$9<=B4))<=$C$1,C4,"-")