Fórmula para copiar várias células com base em dois critérios

Question

Por favor ajude. Estou perplexo! Eu não estou muito familiarizado com as fórmulas do Excel.

Meu caderno de trabalho é de duas páginas. A primeira página destina-se a ser uma página de referência incluída em um relatório da manhã. A segunda página é uma página de coleta de dados que queremos que nossos operadores preencham.

Existem dois critérios que devem trazer informações da página de dados para a página de referência.

1. O trabalho está concluído? Se a resposta for sim, o valor da célula será Y e se o valor for não, o valor da célula será N. Eu só quero ver as linhas que contêm um valor de célula N em uma coluna chamada Trabalho concluído.

2. Esse critério procura valores para identificar um equipamento. Gostaria que uma fórmula identificasse uma peça de equipamento, verificasse se o trabalho estava concluído e, se não, copie a linha inteira para a folha de referência.

Isso é possível com uma fórmula? Alguém pode ajudar?

    

Answer 1

Vou trabalhar em algumas suposições:

1. Existe uma célula, A1, na sua folha de relatório, onde alguém pode inserir o equipamento em que está interessado.
2. Existem duas colunas, A e B, na sua página de 'coleta de dados': O nome do equipamento e o s / n para a integridade
3. Por enquanto, você só deseja ver as linhas "concluídas" na página do relatório (isso pode ser expandido com alterações sutis nas fórmulas).
4. As linhas concluídas na página do relatório começarão na linha 2

Primeiro, você precisa identificar como o equipamento inserido na célula A1 da guia de relatório corresponde a cada linha na coluna A da coleção de dados e que o trabalho está completo na coluna B Você poderia fazer isso com uma fórmula "e" na coluna C da sua guia de coleta de dados.

=AND(A1=reference!$A$1, B1='Y')

Isso retornará TRUE se ambas as condições forem atendidas e FALSE se um ou ambos não forem atendidos.

Em seguida, temos que ter um nível um pouco maior sobre isso. Precisamos obter as linhas TRUE da folha de entrada de dados para a folha de relatório. Não queremos que a folha de relatório tenha um monte de linhas em branco entre as linhas TRUE que são trazidas. Caso contrário, poderíamos apenas fazer um pouco de vlookup ou index / match no TRUE e chamá-lo por dia. Em vez disso, podemos usar o RANK para estabelecer a ordem na qual as linhas são trazidas da página de coleta de dados para a página de referência.

Para fazer isso, na coluna D da folha de coleta de dados, precisamos RANK os TRUE's de 1 para qualquer que seja a contagem de TRUE linhas. Como não há como dizer que uma linha TRUE está acima de outra, usaremos ROW () para o número da linha para estabelecer a classificação. Na coluna D, digite:

=IF(C1=TRUE, ROW(), 0)

Tudo o que isso fará será escrever a linha na Coluna D quando os critérios estabelecidos na Coluna C forem atendidos. Agora, para adicionar o RANK, na Coluna E, digite:

=RANK(D1,D1:D14)*(D1>0)

Isso faz duas coisas. Primeiro, ele determina a classificação da linha começando em 1 e indo para qualquer que seja o número de linhas "VERDADEIRAS" existentes nas primeiras 14 linhas (aumente isso para cobrir todas as linhas de entrada de dados). Segundo, o *(D1>0) coloca um 0 na coluna E se o valor na coluna D for 0 porque não queremos considerar a classificação se for uma linha FALSE.

Agora, você deve ter valores classificados na Coluna E que estão em ordem decrescente com base no fato de o critério ser VERDADEIRO e o número da linha. Agora precisamos arrastar esses valores para a folha de relatório. Para fazer isso, na folha de relatório, começando na célula A2, comece a numeração 1,2,3,4, ... abaixo da coluna. Estes "corresponderão" às classificações da sua folha de coleta de dados na coluna E.

Agora, para obter a primeira coluna de linhas correspondentes, insira o seguinte na coluna B da sua folha de relatórios (começando em B1):

=INDEX(Sheet2!A:A, MATCH(Sheet1!A2, Sheet2!E:E,0), 1)

Isso está usando a função de índice que permite extrair o valor de uma linha e coluna específicas em um intervalo. Nosso alcance é Sheet2! A: A (a coluna A da folha de coleta de dados). Nossa linha é qualquer valor de rank (sheet2! E: E) que corresponda aos nossos números que inserimos em nossa coluna de planilha de relatórios A. Nossa coluna, é apenas 1. Procure "index match in excel" no google para entender isso mais é novo para você. Você pode puxar sua coluna de coleta de dados B da mesma maneira. Na coluna B da folha de relatório (Iniciando em B2), digite o seguinte:

=INDEX(Sheet2!B:B, MATCH(Sheet1!A2, Sheet2!E:E,0), 1)

É isso pelas minhas suposições simples. Essencialmente, você precisa de três colunas para determinar se seus critérios foram atendidos na guia de coleta de dados e, em seguida, classificar esses dados de 1 para n. E você precisa usar correspondência de índice (ou vlookup se reorganizar um pouco as coisas) para puxar as linhas classificadas para a sua planilha de relatórios. Você pode copiar essa mesma lógica para classificar os FALSE caso queira exibir linhas marcadas como incompletas na guia de coleta de dados.

Por fim, depois de pensar bem, considere mover as colunas C, D, E na guia de coleta de dados para outra guia para que as pessoas que fazem a coleta de dados não criem suas fórmulas. A função Index / Match ainda funcionará bem e seus peeps de coleta de dados não verão os detalhes sangrentos do funcionamento interno da sua pasta de trabalho.