Usando uma função pré-existente para uma nova linha

Question

Eu tenho um documento do Excel que contém colunas X e um número N de linhas.

A última coluna de uma linha executa SUM das primeiras colunas X-1. O problema que tenho é que o usuário deste documento do Excel adiciona progressivamente linhas ao documento e, por causa disso, a função ainda não existe na última coluna para novas linhas.

Eu preciso de uma maneira de ter essa função em novas linhas dinamicamente (o usuário não é experiente no Excel e não tem a capacidade de simplesmente arrastar a função para baixo de uma linha).

    

Answer 1

Infelizmente, um usuário que não pode fazer coisas básicas é bastante comum. Você pode recorrer a algo que reescreve totalmente a fórmula (que é reconhecidamente um exagero) Mas veja se você pode adaptar essa ideia no evento Worksheet_SelectionChange. Ou, possivelmente, ativar as macros também irá derrubá-las.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 LastRow = ActiveSheet.UsedRange.Rows.Count
 Range("D2:D" & LastRow).Formula = "=SUM(INDIRECT(""A""&ROW()&"":C""&ROW()))"
End Sub

para subtotais

Range("D2:D" & LastRow).Formula = "=SUBTOTAL(9,(INDIRECT(""A""&ROW()&"":C""&ROW())))"

    

Answer 2

Você provavelmente poderia tornar isso realmente dinâmico com o VBA, mas acho que é um exagero simplesmente adicionar uma fórmula a uma célula. O que você poderia fazer é fazer parecer que a célula está vazia até que os dados sejam adicionados às células pertinentes e, em seguida, arraste essa célula para centenas ou milhares de linhas.

Por exemplo, =IF(LEN(A1)=0,"",A1+B1) mostrará uma célula vazia se A1 não contiver dados, mas executará a função A1 + B1 se isso acontecer. Se você arrastar abaixo da linha final, a coluna aparecerá em branco até que os dados sejam adicionados à próxima célula na coluna A.

Existem várias outras fórmulas que você pode usar para verificar células em uso, como =ISBLANK(A1) OR =ISTEXT(A1) com base em como você deseja verificar dados. Eu acho que a função LEN é mais fácil de se trabalhar. Você também pode fazer coisas como essa =IF(LEN(A1)+LEN(B1)=0,"",A1+B1) , que executará a fórmula se a célula tiver dados ou =IF(OR(LEN(A1)=0,LEN(B1)=0),"",A1+B1) , que só executará a fórmula se ambas as células tiverem dados.

Para a maioria das fórmulas, estender isso para vários milhares de linhas adiciona apenas alguns KB ao tamanho do seu arquivo. Se você tem fórmulas muito longas, ou muitas delas, que você está arrastando para baixo, isso pode aumentar significativamente o tamanho do arquivo.

    

Answer 3

Eu teria que ver sua planilha, mas parece que você poderia usar uma tabela dinâmica. O usuário simplesmente faz a entrada de dados e os relatórios são gerados e agregados dinamicamente. Confira o youtube para algumas instruções e demonstrações sobre como usar tabelas dinâmicas.