☰

Preencher automaticamente linhas de outra planilha no Excel

tag-icon • tag-icon microsoft-excel worksheet-function
Preencher automaticamente linhas de outra planilha no Excel

Tenho uma pasta de trabalho que estou usando para controlar os produtos que encomendei. No momento, possui 2 planilhas - uma é uma lista de inventário de cada item organizada em linhas com todos os dados (ID do produto, quantidades mínimas/máximas, descrição, preço etc.) em cada coluna. Na segunda folha, acompanho quando os itens foram pedidos junto com os números do pedido de compra. Atualmente, copio a linha do item que estou pedindo da planilha de estoque e colo na segunda planilha com o número do pedido de compra, data e quantidade solicitada. Eu mantenho isso como uma lista contínua que continuo adicionando para poder ver tendências e histórico de pedidos. Existe uma maneira de, na segunda planilha, digitar o ID do produto, pressionar Enter e preencher a linha com as informações do mesmo ID do produto da planilha de inventário? Economizaria muito tempo eliminar o recurso de copiar e colar, já que existem milhares de IDs de produtos e suas informações.

Obrigado.

Responder1

Para esse tamanho de coleção, talvez você deva usar o Access ou alguma outra solução de banco de dados. Ainda assim, aqui vai.

Use a função VLOOKUP no Excel. Como um exemplo:

Sheet1 tem este layout

     |     A      |   B   |   C   |   D   |   E   |
1    | Product ID | Max Q | Min Q | Desc. | Price | 
2    |  ######### |   ### |    ## | xxxxx | 34.29 |
...
2359 |  ######### |   ### |    ## | xxxxx | 54.28 |

Sheet2 tem esse layout

     |     A     |     B     |    C    |     D     |     E      |   F   |   G   |
1    |Order date | Recv Date | Order Q | P.O. Num. | Product ID | Desc. | Price |
2    |           |           |         |           |            |       |       |

Na célula E2 da Folha2 você insere o ID do produto que corresponde a algum ID na coluna A da Folha1. Na célula F2 da Folha2 você insere a fórmula =VLOOKUP(E2,Sheet1!$A$2:$E$2359,4,TRUE)e em G2 você insere a fórmula =VLOOKUP(A1,Sheet1!$A$1:$H$2359,5,TRUE). As referências de intervalo são absolutas $para que não mudem à medida que a fórmula é copiada na página, e o terceiro parâmetro é a coluna "dentro" do intervalo que contém o valor desejado. Se, por exemplo, Sheet1 começasse na coluna H, o intervalo mudaria para Sheet1!$H$2:$L$2apenas 4 para Desc. e o 5 para Preço serianãomudar. Você pode evitar a necessidade de atualizar o intervalo nas fórmulas toda vez que adicionar um produto usando um intervalo nomeado, verifique a ajuda do Excel para isso. A coluna de ID do produto precisa ser classificada para obter o melhor efeito com base no tamanho do conjunto de dados relatado, embora não seja exigido pelo VLOOKUP, e você terá que copiar as fórmulas da linha 2 para baixo até onde achar necessário (não importa quantos milhares de linhas isso pode ser.) Se as planilhas tiverem nomes, coloque-as entre aspas simples, como =VLOOKUP(E2,'Inventory List'!$A$2:$E$2359,4,TRUE). Na fórmula, o primeiro argumento E2é a célula a ser correspondida, o segundo argumento Sheet1!$A$1:$H$2359é o intervalo a ser procurado - a pesquisa éapenasfeito noprimeirocoluna, o terceiro argumento é a coluna no intervalo para os dados retornarem e o quarto argumento TRUEforça uma correspondência exata.

Sério... considere migrar um conjunto de dados tão grande para um banco de dados. Isso significará mais trabalho agora, mas muito melhor no longo prazo, incluindo a capacidade de identificar tendências com mais facilidade e menos complicações.

informação relacionada