Como copiar fórmulas quando há linhas vazias entre elas sem perder a sequência da planilha vinculada?

Question

Eu tenho um arquivo Excel que contém duas planilhas.

• Resumo
• Dados não tratados

Minha planilha de resumo contém dados como este, onde há uma lacuna de algumas linhas para cada linha de dados seguinte

Agora o problema é que se eu tiver uma planilha com mais de 1000 linhas e quiser alterar alguma coisa nela, tenho que editar manualmente cada referência de célula, pois ela não continua a referência da planilha RawData, mas obtém o número da célula como referência de Folha de resumo.

Na imagem de exemplo anexada, se eu copiar e colar a fórmula, obtenho os números das células como 2, 5, 8, 11 e assim por diante, dependendo da lacuna da linha onde preciso copiar as fórmulas conforme mostrado na coluna de fórmula manual como sequência B2, B3, B4, B5, independentemente do número da linha da planilha atual. Tentei muitas coisas, mas não consegui encontrar nenhuma solução. Qualquer ajuda?

Para evitar confusão, criei um exemplo mais realista da minha planilha com uma captura de tela da guia RawData também.

Folha de dados brutos: - Folha de dados brutos

Aqui está a planilha vinculada onde estou vinculando os dados da planilha RawData Folha de produto resumida

Como você pode ver, ele está mostrando o Produto de Teste 1, o Produto de Teste 6 e o ​​Produto de Teste 11 se você copiar e colar a fórmula, enquanto este deve ser o Produto de Teste 1, 2 e 3.

Aqui está o exemplo do link do arquivo Excelhttps://we.tl/t-3F3mJra7pe

Answer 1

Deixe-me primeiro descrever a razão por trás do problema,

• SobreFolha de resumona célula F3a fórmula =Rawdata!A2retorna valor Test Product 1.
• Assim que você copiarFolha de resumodados de B3:F7em B8, Excel lê6 linhasentre F3:F8e retorna Test Product 7da Célula B8deDados não tratadosFolha.

O que estava estragando todo o exercício.

A solução:

• Você precisaColuna AuxiliaremDados não tratadosFolha.

• Reescrever a célula B3emResumoFolha com Produto Simples 1.
• Insira esta fórmula na célula F3emResumoFolha.

=VLOOKUP(B3,RawData!A$2:E$12,2,FALSE)

Você entende isso.

• Agora,cópia deAlcance B3:F7eColarno celular B8.

Você entende isso.

• Reescrever célula B8comProduto Simples 2, agora você obtém a sequência correta.

• Repita oCopiar colarcom correçãoProdutos simplessequência, você obtém os resultados desejados.

Você pode ajustar as referências de células na Fórmula conforme necessário.

Answer 2

Nesses tipos de cenários, acho que uma solução mais simples, porém robusta, seria usar a função OFFSET(). Offset retorna uma referência a uma única célula ou intervalo de células. Considerando o seu cenário, se a primeira fórmula Copiar e Colar se referir ao RawData Produto1, e a segunda Copiar e Colar se referir ao Produto2, não importa quantas linhas seriam ignoradas nesses Copiar e Colar, eu implementaria este OFFSET conforme abaixo:

A B               C      D        E       F
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B1);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B6);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B11);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B16);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large

Observe que ao copiar a fórmula "=OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B1);0)", a referência a "Summary!$B$1:B1" mudará automaticamente com o mesmo comportamento que você NÃO QUER; mas, uma vez que essa referência esteja sendo usada dentro de uma fórmula COUNTA(), ela funcionará e você não precisará alterar milhares de referências sozinho. Fazendo isso, COUNTA() contará quantas vezes um “Produto Simples” está aparecendo desde o início da coluna, e utilizará esse deslocamento interno para se referir ao respectivo produto em posição ordinal. Tudo isso sem a necessidade de criação de colunas auxiliares. O resultado será o seguinte:

A B               C      D        E       F
  Simple Product  FALSE  TRUE             TestProduct1
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct2
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct3
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct4
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large