Obtenha um número de linha de 2 critérios (com 1 pesquisa fuzzy)

Question

Eu estou tentando criar alguns gráficos com referências de célula dinâmica como índices. Estes gráficos são baseados em carimbos de tempo gerados a partir de uma máquina de teste. Eu tenho uma coluna de valores booleanos (0 ou 1) que marcam "forçar logs" e uma planilha de vários campos de dados para máquina. Essa máquina também produz uma planilha em uma pasta de trabalho separada (essa planilha contém registros de data e hora reais que marcam horas de início e término) que estou copiando e colando manualmente em outra planilha da pasta de trabalho com os dados (tem aproximadamente 5000 linhas). Eu quero localizar meus índices de início e fim para o meu gráfico com base em carimbos de hora. Infelizmente, a pasta de trabalho do Excel produzida separadamente, com os carimbos de hora corretos para escolher, fica em torno de 1 segundo da data e hora na planilha de dados principal (esse atraso é variável, mas entre 0 e 2 segundos). Além disso, meus valores Booleanos "force log" devem ser 1 para registros de data e hora, mas muitas vezes eu tenho logs de força extra que não correspondem aos tempos corretos para iniciar o gráfico. Abaixo está uma amostra representativa conforme solicitado.

Worksheet containing data (Call this Sheet 2)
  A                     B           C          D
  Time Stamp            Force Log  Field 1    Field 2   
1 2/6/2015 3:21:22 PM   1          100        500      <- Extraneous boolean value
2 2/6/2015 3:21:23 PM   0          101.4      499.2
3 2/6/2015 3:21:24 PM   1          99.3       501.4    <- I want this index for graph start
4 2/6/2015 3:21:25 PM   0          100.3      498.9
5 2/6/2015 3:21:26 PM   0          102.4      500.7
6 2/6/2015 3:21:27 PM   1          101.8      499.3    <- I want this index for graph end

Worksheet containing machine generated data summary (call this Sheet 1)
Start Time    End Time
3:21:23 PM    3:21:26   <- The times here lead the other worksheet by less than 2 seconds
                        They are also off by 2/6/2015, but I can generate a helper column
After obtaining the indices 3 and 6, I would graph the values of Field 1 and Field 2.

Aqui está o que, do meu problema, eu juntei que preciso fazer: Fuzzy pesquisando por time stamps por teste truncado ou condicionalmente por equivalência através de um range (mais menos 2 segundos), e certifique-se de que a força log "A coluna de valores booleanos também é definida como 1.

Sou novo nas funções de referência e pesquisa do Excel, por isso não tenho a certeza se estou a perder alguma coisa. Até agora eu tentei LOOKUP, VLOOKUP (não funcionou porque minhas colunas de dados não estão formatadas corretamente para table_array), INDEX MATCH (não funcionou por causa do requisito difuso de uma das minhas pesquisas), e parece que não consigo encontrar qualquer fácil de entender soluções permitindo uma pesquisa com vários critérios que retorna vários resultados (eu tenho várias vezes que cada um precisa de um único resultado, não uma única vez que precisa de vários resultados; eu estava lutando para entender fórmulas de matriz no Excel).

Agradeço muito antecipadamente qualquer pessoa que tenha uma solução ou conselho.

EDIT: Eu entendo que a minha pergunta pode ter um fundo muito estranho. Por favor, deixe-me saber se você precisa de mim para esclarecer ou elaborar sobre o que exatamente eu preciso fazer. Obrigado!

    

Answer 1

Aqui está uma solução que fornecerá os números de índice para os pontos inicial e final. Você pode usar esses dois números para criar um intervalo dinâmico nomeado para os valores que deseja representar graficamente.

Primeiro, aqui estão as duas coisas que fiz com seus dados para facilitar as fórmulas. Você não precisa fazer isso também, mas precisará alterar algumas partes da fórmula para corresponder à sua configuração de dados.

1. Eu converti seus dados em uma tabela com o nome padrão Table1 . Quando uma fórmula faz referência a todos os valores de "Carimbo de data / hora", você a verá como Table1[Time Stamp] .
2. Eu nomeei as duas células com os horários inicial e final StartTime e EndTime para facilitar a seguir a fórmula. Caso contrário, ele apareceria como A10 e B10 e não seria tão fácil de seguir.

Esta é a fórmula para fornecer o número do índice para a hora de início:

{=MATCH(1,IF(Table1[Force Log]=1,IF(HOUR(Table1[Time Stamp])=HOUR(StartTime),IF(MINUTE(Table1[Time Stamp])=MINUTE(StartTime),IF(SECOND(Table1[Time Stamp])-SECOND(StartTime)<=2,IF(SECOND(Table1[Time Stamp])-SECOND(StartTime)>=0,1))))),0)}

... e aqui está a hora do fim:

{=MATCH(1,IF(Table1[Force Log]=1,IF(HOUR(Table1[Time Stamp])=HOUR(EndTime),IF(MINUTE(Table1[Time Stamp])=MINUTE(EndTime),IF(SECOND(Table1[Time Stamp])-SECOND(EndTime)<=2,IF(SECOND(Table1[Time Stamp])-SECOND(EndTime)>=0,1))))),0)}

O ponto chave é que ambas são fórmulas de array, então elas devem ser digitadas com Ctrl + Shift + Enter . Não tente colar nas chaves {} porque não funciona. Cole tudo, menos esses, use Ctrl + Deslocar + Enter , e as chaves {} aparecerão.

Veja como funciona:

É realmente apenas um monte de fórmulas IF aninhadas que resultam em uma matriz de 1 e 0 com uma fórmula MATCH que procura o primeiro 1 . As instruções IF configuram uma série de critérios e, se todos os critérios forem atendidos, o valor será 1 . Se algum deles não for atendido, o valor será 0 . (Bem, na verdade é FALSE , mas isso é avaliado em 0 .) Ele verifica que Force Log = 1 , a hora é a mesma, o minuto é o mesmo e que os segundos estão inclusivamente dentro de +0 / +2 segundos do tempo que você está procurando. Observe que eu não verifiquei se a data era a mesma que os horários de início e término não incluem valores de data.

Ponto-chave: Se houver vários registros que atendam a esse critério (ou seja, se o registro de data e hora 2/6/2015 3:21:23 PM em seu exemplo tiver um valor de Registro de Forças de 1), essa fórmula encontrará o primeiro registro que corresponde aos critérios.