Como Kyle indica, as pessoas normalmente só usam endereçamento relativo, e sua resposta descreve como fazer isso. Você pode ver quão simples as fórmulas se tornam.
Explicação das fórmulas originais
As fórmulas de amostra que você incluiu na pergunta podem ter sido feitas dessa maneira para destacar o relacionamento entre os endereços de célula. Enquanto a estrutura não mudar, esse estilo de endereçamento enfoca a lógica das relações físicas das células. No entanto, observe que, se a estrutura da planilha for alterada, tudo será quebrado, pois as referências estão vinculadas à estrutura original.
Para entender o que eles estão fazendo, ADDRESS cria uma descrição de texto de uma referência de célula. Isso permite usar cálculos para descobrir onde uma referência deve apontar. Uma demonstração breve e de demonstração . INDIRECT converte uma descrição de texto de uma referência de célula em uma referência real.
Então, se você quiser uma alternativa mais limpa, o mais limpo seria usar apenas referências de células reais, como a resposta de Kyle. Se você está na situação em que realmente quer o foco na estrutura da planilha, ainda há algumas maneiras de otimizar isso.
endereçamento R1C1
Existe uma forma de endereçamento chamada R1C1, que expressa endereços como linhas e colunas absolutas ou relativas de uma maneira que pode ser calculada e usada diretamente, sem a necessidade das funções ADDRESS, ROW e COLUMN.
Para um endereço absoluto, use o número da linha e o número da coluna após os rótulos R e C. Então A3 seria R3C1 .
Para endereçamento relativo, deixe o número para se referir à linha ou coluna atual ou coloque o número relativo entre colchetes. Portanto, a célula na mesma linha e duas colunas à esquerda seriam referenciadas como RC[-2] .
No Excel e no Calc, há uma opção para alterar o estilo do endereço para R1C1, o que se aplica a tudo. No entanto, você pode usá-lo em fórmulas específicas sem fazer isso usando uma cadeia de texto e usando INDIRECT (que precisa do parâmetro de estilo de endereçamento opcional definido como zero para reconhecer o estilo R1C1). Então a primeira fórmula:
=INDIRECT(ADDRESS(ROW(),COLUMN()-2))-IF(ISNUMBER(INDIRECT(ADDRESS(ROW()-1,COLUMN()-2))),
INDIRECT(ADDRESS(ROW()-1,COLUMN()-2)),0)-INDIRECT(ADDRESS(ROW(),COLUMN()-1))
se tornaria:
=INDIRECT("RC[-2]",0)-IF(ISNUMBER(INDIRECT("R[-1]C[-2]",0)),
INDIRECT("R[-1]C[-2]",0),0)-INDIRECT("RC[-1]",0)
Você pode ver que isso é uma forma abreviada da construção ADDRESS / ROW / COLUMN.
OFFSET
A maneira típica de fazer isso, no entanto, quando você precisa tornar as relações relativas óbvias, é menor do que isso. Você pode usar a função OFFSET. A forma abreviada para se referir a uma única célula é OFFSET(reference,rows,columns) . Você especifica o ponto de partida e, em seguida, o número de linhas e colunas dele.
Nos seus exemplos, a fórmula determina qual é a célula atual e você pode fazer isso no OFFSET, mas geralmente não é necessário. A referência real da célula é normalmente usada. Como na resposta de Kyle, isso normalmente será traduzido corretamente se você copiar a fórmula para outra célula. OFFSET é uma forma de tornar explícito o relacionamento de localização. Então, se sua primeira fórmula estava na célula C5 , seria assim:
=OFFSET(C5,0,-2)-IF(ISNUMBER(OFFSET(C5,-1,-2)),
OFFSET(C5,-1,-2),0)-OFFSET(C5,0,-1)