Excel: como alterar um valor e, em seguida, atualizar com base nesse novo valor sem ter uma referência circular

1

Uma questão fundamental de gerenciamento de estoque (acredito)

Em termos simples:

IF(A<=0,A=20,)

Claro, não funciona porque, no momento em que você adiciona 20, 'A' se torna positivo. Excel recalcula e 20 é removido. Excel recalcula e 20 é adicionado, etc ........

Precisamos de uma maneira de fazer A = + 20 .... depois, olhar para B, C, D para a próxima condição negativa.

Quase todos os modelos de negócios devem lidar com o reabastecimento de estoque. ... mas como isso foi alcançado?

Aqui está o link para as planilhas do Google: link

Abaixo, simplesmente lista a planilha acima no texto. Haverá outras variáveis a serem consideradas, mas o problema fundamental primeiro precisa ser respondido

Aqui está o problema fundamental do estoque explicado:

Temos vendas, saldo de estoque e a necessidade de entrega (para coincidir com a falta de estoque).

  • O estoque inicial é inserido.
  • As vendas projetadas por semana são inseridas.
  • Em uma determinada semana, todas as ações são vendidas.
  • Nessa semana, uma entrega de estoque deve ocorrer.

Manualmente, é muito fácil lidar com isso ... procuramos um saldo de estoque negativo (ou zero) projetado e tocamos no teclado e inserimos a entrega de estoque necessária. Percorremos o nível de estoque - localizamos a próxima semana em que o estoque é necessário e a necessidade de entrega é inserida.

Bem.

Apenas se as vendas projetadas forem alteradas, todas as datas de entrega deverão ser alteradas.

Isso significaria, mais uma vez, alterar manualmente o primeiro nível de estoque negativo e, novamente, procurar o próximo nível de estoque negativo, etc.

No entanto ... Os modelos financeiros precisam funcionar, simplesmente alterando as vendas projetadas.

Carregue a projeção de vendas, encontre a semana sem estoque, adicione uma entrega ... e encontre a próxima semana sem estoque.

A única intervenção humana é mudar os números de vendas.

Aqui está uma versão muito simples:

         A               B             C          D          E          F          G          H          I
  +---------------+---------------+----------+----------+----------+----------+----------+----------+----------+
1 |  01/03/2017   |     Date      | 05/03/17 | 12/03/17 | 19/03/17 | 26/03/17 | 02/04/17 | 09/04/17 | 16/04/17 |
  +---------------+---------------+----------+----------+----------+----------+----------+----------+----------+
2 | Stock         | Delivery      |        1 |          |          |          |          |          |          |
3 |               | Consignment   |       37 |        0 |        0 |        0 |        0 |        0 |        0 |
4 |               | Stock Balance |       22 |       11 |       -1 |       -1 |       -1 |       -1 |       -1 |
5 |               | Sales         |       15 |       11 |       12 |          |          |          |          |
6 | Order Qty Ref | Item qty      |       37 |       37 |       37 |       37 |       37 |       37 |       37 |
  +---------------+---------------+----------+----------+----------+----------+----------+----------+----------+

C2:I2 = um número de 1 a 7

C3:I3 = Monitora C2:I2 para números de 1 a 7, em seguida, pesquisa a faixa 'Ordenar QtF Ref' (na linha 6) e publica o 'item qty', por exemplo, 37         ou seja, se entrega = 1 ..... 37 é a remessa.

SE (C2 = 1, $ C $ 6, SE (C2 = 2, $ D $ 6, SE (C2 = 3, $ E $ 6, SE (C2 = 4, $ F $ 6, SE (C2 = 5, $ G $ 6, SE (C2 = 6, $ H $ 6, SE (C2 = 7, $ I $ 6, )))))))

C4 = C3-C5 ..... Isso simplesmente abre o saldo do estoque - Vendas em consignação.

D4:I4 = C4+D3-D5 ..... Cada célula adiciona o saldo do estoque anterior a qualquer nova remessa e subtrai qualquer venda.

C5:I5 = Um número que representa o número de unidades vendidas (no modelo, esse intervalo de números de vendas é escolhido por uma lista suspensa).

C6:I6 = Um intervalo separado de 7 entregas potenciais de estoque.

A posição inicial do modelo tem as projeções de vendas carregadas e a primeira remessa (1).

Manualmente, procuramos o primeiro saldo de estoque negativo ou 0 (Linha 4) - encontramos o primeiro na coluna E e adicionamos 1 (ou um número entre 1 e 7) na linha 2 (ou seja, , na célula E2 ).

+---------------+---------------+----------+----------+----------+----------+----------+----------+----------+
|  01/03/2017   |     Date      | 05/03/17 | 12/03/17 | 19/03/17 | 26/03/17 | 02/04/17 | 09/04/17 | 16/04/17 |
+---------------+---------------+----------+----------+----------+----------+----------+----------+----------+
| Stock         | Delivery      |        1 |          |    →   1 |          |          |          |          |
|               | Consignment   |       37 |        0 |       37 |        0 |        0 |        0 |        0 |
|               | Stock Balance |       22 |       11 |       35 |       20 |        9 |       -4 |      -19 |
|               | Sales         |       15 |       11 |       13 |       15 |       11 |       13 |       15 |
| Order Qty Ref | Item qty      |       37 |       74 |       37 |       37 |       37 |       37 |       37 |
+---------------+---------------+----------+----------+----------+----------+----------+----------+----------+

Em seguida, procuramos manualmente pelo próximo saldo de estoque zero ou negativo (encontrá-lo na célula H4 ) e adicionamos outro 1 (na célula H2 ).

+---------------+---------------+----------+----------+----------+----------+----------+----------+----------+
|  01/03/2017   |     Date      | 05/03/17 | 12/03/17 | 19/03/17 | 26/03/17 | 02/04/17 | 09/04/17 | 16/04/17 |
+---------------+---------------+----------+----------+----------+----------+----------+----------+----------+
| Stock         | Delivery      |        1 |          |        1 |          |          |    →   1 |          |
|               | Consignment   |       37 |        0 |       37 |        0 |        0 |       37 |        0 |
|               | Stock Balance |       22 |       11 |       35 |       20 |        9 |       33 |       18 |
|               | Sales         |       15 |       11 |       13 |       15 |       11 |       13 |       15 |
| Order Qty Ref | Item qty      |       37 |       37 |       37 |       37 |       37 |       37 |       37 |
+---------------+---------------+----------+----------+----------+----------+----------+----------+----------+  

Ok, fácil - o cálculo simples é: IF (estoque_balanceamento < = 0, entrega = 1,)

O problema é que, no momento em que o saldo do estoque é ajustado para positivo ... o Excel remove o 1 ... deixando o saldo do estoque negativo - ad infinitum (erro de referência circular).

Evidentemente, precisamos que o Excel calcule 'da esquerda para a direita' de cada célula, até que uma condição zero ou negativa seja atendida. Insira a entrega de estoque ... e então reinicie os cálculos da primeira célula, até que uma condição zero ou negativa seja atendida.

Na verdade, precisamos que o Excel imite a intervenção humana.

Por exemplo,

IF(Stock_balance<=0,Delivery=1[stop_recalculate_until_no_negatives_are_found],)

Tudo já foi feito antes, mas não consigo encontrar a solução por meio de pesquisa. Por isso, acredito que esta questão é ideal para o superusuário.

    
por Marco-UandL 16.02.2017 / 21:05

1 resposta

0

Primeiro de tudo, Eu posso oferecer uma forma muito mais concisa da fórmula para a linha 3 e um formulário alternativo para a linha 4 (um que não precisa tornar C4 um caso especial):

  • Linha 3 → =IF(C$2=0, 0, INDEX($C$6, 1, C$2))
    Isso é simples - se C2 for 0, C3 será zero. (Lembre-se de que os testes em branco são iguais a zero.) Caso contrário, use o número de $C$6 , $D$6 , $E$6 ,…, indexado por C2 .
  • Linha 4 → =SUM($C3:C3)-SUM($C5:C5)
    Em célula C4 , isso reduz para C3-C5 . Ao arrastar / preencher para a direita, a primeira célula em cada intervalo permanece ancorada na coluna C enquanto a segunda célula aumenta automaticamente.
    Então D4 é =SUM(C3:D3)-SUM(C5:D5) , E4 é =SUM(C3:E3)-SUM(C5:E5) , etc.

O problema é que você deseja escolher o valor na linha 2 com base no valor da linha 4 antes de aplicar os valores nas linhas 2 e 3 para o valor na linha 4 para a coluna atual - e você está recebendo uma referência circular. A solução é estabelecer uma "fila auxiliar" que calcula precisamente esse valor fantasma (o valor na linha 4 antes de aplicar os valores nas linhas 2 e 3). Por exemplo, eu coloquei na linha 9 :

  • C9=-C5
  • D9=SUM($C3:C3)-SUM($C5:D5) (e arraste / preencha isso para a direita).

Você pode ver que isso é igual ao valor da Linha 4 antes que o valor na Linha 3 tenha sido adicionado. É então uma questão simples de definir a linha 2 como =IF(C9<=0, 1, 0) (que é uma fórmula que você já tem) e formate as células para não exibir zeros. (Ou você pode fazer a fórmula =IF(C9<=0, 1, "") se preferir) Agora você pode alterar as vendas projetadas (na Linha 5) e a programação de entrega nas linhas 2 e 3 será atualizada automaticamente.

Alterei o formato das datas porque escrevo datas como mm / dd / yy (sim, eu sei que é irracional, mas é uma coisa cultural), e olhando datas formatadas como dd / mm / yy dói meus olhos.

por 18.02.2017 / 07:34