Seleção avançada de células no Excel

Question

Eu sou novo nesse sabor do StackExchange, então se isso pertencer a outro lugar, por favor, mova-o; Eu imaginei que este seria o melhor lugar, no entanto.

Estou fazendo uma Planilha do Excel que simplesmente armazena dados financeiros básicos em 5 colunas ( Check Number , Date of Transaction , Description , Profit from Transaction e Balance After Transaction ) e linhas indefinidas. Cada planilha representa um mês e cada pasta de trabalho representa um ano. À medida que faço ou recebo um pagamento, eu o armazeno como uma nova linha, o que, inerentemente, torna o número de linhas por mês indefinido. A célula Balance de cada transação é a soma da célula Balance da linha acima e da célula Profit de sua linha. Eu quero cada mês para começar com uma linha especial (primeiro depois de cabeçalhos de coluna) que exibe um resumo das transações do mês último . Por exemplo, a célula Balance After Transaction exibiria o saldo da última linha e a célula Profit from Transaction exibiria os lucros totais do mês

Eu sei que se eu soubesse que todos os meses tinham exatamente 100 gastos, eu poderia conseguir isso para março com as seguintes fórmulas para lucro e equilíbrio, respectivamente:

=February!E2 - February!E102
=February!E102

No entanto, eu NÃO sei quantas linhas estarão na tabela de cada mês, e gostaria de automatizar isso o máximo possível (por exemplo, se eu encontrar uma despesa perdida ou duplicada em janeiro, não quero ter que atualizar todas as fórmulas que apontam para o saldo final de janeiro). Como posso fazer com que o Excel use automaticamente o último valor inserido em uma coluna, em qualquer planilha do Excel, em uma fórmula?

    

Answer 1

Minha primeira sugestão será converter esse intervalo em uma tabela (Insert - > Table), e então você pode adicionar uma linha de Totais, que você pode referenciar de uma planilha / pasta de trabalho diferente, pelo seu nome e não precisa se preocupar com o número de linhas.

Se não for uma opção, você pode fazer isso:

Para obter o resumo do lucro do mês anterior, use

=SUM(Month!$D:$D)

(isso pressupõe que a coluna de lucros não tem outros números além dos lucros).

E para o saldo do mês anterior, a fórmula do @Jesse deve funcionar, mas somente se a sua tabela começar na primeira linha e, se isso não acontecer, você pode tentar:

=INDEX(Month!$E:$E,MATCH(TRUE,ISBLANK(Month!$E:$E),0)-1)

ao digitá-lo, use CTRL+SHIFT+ENTER . Isto irá encontrar ISBLANK em toda a coluna para encontrar a primeira célula vazia, e então recuperar o valor logo acima dela (que seria a última linha).

Para tornar isso mais dinâmico e à prova do futuro, sugiro usar intervalos nomeados, portanto, se for necessário, você não precisará alterar todas as referências às colunas.

    

Answer 2

Outra maneira simples seria inserir nomes na pasta de trabalho. Jan, fev, etc. Utilize-os para determinar a sua última linha contando o montante de entradas de datas na sua coluna de datas.

Por exemplo, insira um nome "Jan" e RefersTo = COUNT (janeiro! A: A) +2 Isso fornecerá a contagem de linhas e você poderá usá-lo em sua fórmula indiretamente para criar a referência de intervalo.

A última célula na linha será = INDIRECT ("E" & Jan) e você poderá usar essas referências em uma planilha de resumo em outro lugar também, se desejar.

Isso pressupõe números (datas) na coluna A. Ele ignorará um cabeçalho se for texto, mas na linha 2, certifique-se de não ter um valor numérico ou ele distorcerá a contagem de linhas.

    

Answer 3

Não use indiretamente, com este nível de cálculo será doloroso. Indireto é volátil e fará com que cada fórmula na sua planilha seja recalculada toda vez que você mudar alguma coisa.

Você pode usar o índice. Ficaria como:

=INDEX(Month!A:E,COUNTA(Month!A:A),COLUMN())

Você só faz referência a colunas inteiras e o deslocamento da coluna é calculado automaticamente com a função COLUMN() .