Como faço para extrair datas de um rastreador semanal?

Question

Digamos que em cada hora do dia em uma semana eu registro, por exemplo, a comida que eu como ou a atividade física que faço.

No final da semana, eu tenho uma tabela onde as linhas são horas (12: 00-12: 59-11: 00: 00: 15: 59) e as colunas são dias (domingo a sábado) e os valores são os alimentos que eu comi.

Como faço para obter uma lista das datas em que comi certos alimentos? Por exemplo, comi banana das 10h às 11h de segunda e quinta-feira, exceto na segunda-feira da terceira semana do mês. Em vez disso, comi banana na terça-feira daquela semana.

Como faço para obter as datas?

Eu tentei usar countif, index, match, mas acredito que estou perdendo uma solução muito simples envolvendo tabelas dinâmicas ou procurando o modelo certo. Se o pior acontecer, estou disposto a (re) aprender VBAs / macros, mas duvido muito que não exista um modelo lá fora ou uma solução de tabela dinâmica para isso.

Partes do meu rastreador de consumo de alimentos

Euqueroacabarcomalgocomo(Tabela3):

    

Answer 1

Com a maneira como seus dados são formatados, você precisaria pesquisar a planilha para cada instância de Banana e, em seguida, usar os números de linha e coluna para consultar a data e a hora em que isso foi comido. feito em VBA. Um dos problemas com o seu formato atual, é que você quer procurar por banana, mas às vezes é misturado em células com ovos, às vezes cenoura, e às vezes por si mesmo.

No entanto, parece que os seus dados podem ser organizados de uma forma mais amigável, mas menos amigável. Você realmente tem uma série de carimbos de tempo, com um único alimento depois de cada um, isso poderia estar em uma série de colunas como abaixo, e com colunas adicionais para cálculos

Date    Food    Hour    Day Week
30/01/2017 10:00    sandwich    10  2   5
30/01/2017 10:00    apple   10  2   5
30/01/2017 10:00    pancake 10  2   5
30/01/2017 16:00    eggs    16  2   5
30/01/2017 16:00    hotdog  16  2   5
31/01/2017 16:00    carrot  16  3   5
31/01/2017 16:00    eggs    16  3   5
31/01/2017 16:00    chips   16  3   5
02/02/2017 16:00    eggs    16  5   5
02/02/2017 17:00    banana  17  5   5
03/02/2017 18:00    orange  18  6   5
04/02/2017 12:00    ham 12  7   5

Depois de ter os dados como este, você pode listar seus tipos de alimentos na página

sandwich    apple   pancake eggs    hotdog  carrot  banana  ham
   1       2         3       4      5      6        10      12
#N/A      #N/A     #N/A      7     #N/A    #N/A    #N/A    #N/A
#N/A      #N/A     #N/A      9     #N/A    #N/A    #N/A    #N/A
#N/A      #N/A     #N/A      #N/A   #N/A    #N/A    #N/A    #N/A

Encontrar a primeira ocorrência é fácil - basta usar match (), mas da próxima vez que procurar, você precisa começar mais abaixo na lista, então use offset () para mudar onde a pesquisa começa.

=MATCH(L$21,$B$23:$B$1000,0)
=MATCH(L$21,OFFSET($B$23:$B$100,L22,0),0)+L22
=MATCH(L$21,OFFSET($B$23:$B$100,L23,0),0)+L23
etc

Depois, para obter sua lista de datas para cada comida, use index ()

=INDEX($A$23:$A$34,L22)

sandwich            apple               pancake             eggs
30/01/2017 10:00    30/01/2017 10:00    30/01/2017 10:00    30/01/2017 16:00
#N/A                #N/A                 #N/A               31/01/2017 16:00
#N/A                #N/A                 #N/A               02/02/2017 16:00