Как извлечь даты из еженедельного трекера?

Question

Допустим, в каждый час дня недели я записываю, например, еду, которую я ем, или физическую активность, которую я делаю.

В конце недели у меня есть таблица, где строки - часы (с 12:00 до 12:00 - 23:00 - 11:59), столбцы - дни (с воскресенья по субботу), а значения - это продукты, которые я съел.

Как я могу получить список дат, когда я ел определенные продукты? Например, я ел банан с 10 до 11 утра по понедельникам и четвергам, кроме понедельника третьей недели месяца. Вместо этого я ел банан во вторник этой недели.

Как я могу получить даты?

Я пытался использовать countif, index, match, но я считаю, что мне не хватает очень простого решения, включающего сводные таблицы или поиск подходящего шаблона. Если худшее приходит к худшему, я готов (пере) изучить VBA / макросы, но я очень сомневаюсь, что для этого не существует шаблона или сводной таблицы.

---

Части моего трекера потребления пищи

---

Я хочу закончить с чем-то вроде (Таблица 3):

---

Answer 1

При способе форматирования ваших данных вам нужно будет выполнить поиск по листу для каждого экземпляра Banana, а затем использовать номера строк и столбцов, чтобы вернуться к дате и времени, когда они были съедены, это можно сделать в VBA , Одна из проблем вашего текущего формата заключается в том, что вы хотите искать банан, но иногда он смешивается в клетках с яйцами, иногда с морковью, а иногда и с самим собой.

Однако может показаться, что ваши данные могут быть упорядочены в более удобной для компьютера форме, но менее удобной для пользователя. У вас действительно есть ряд временных отметок, с одним фудом после каждого, это может быть серия столбцов, как показано ниже, и с дополнительными столбцами для расчетов.

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

Как только у вас есть такие данные, вы можете перечислить ваши типы продуктов питания на странице

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

Найти первое вхождение очень просто - просто используйте match(), но затем, когда вы в следующий раз посмотрите, вам нужно начать дальше вниз по списку, поэтому используйте offset(), чтобы изменить место начала поиска.

=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

Затем, чтобы получить список дат для каждого продукта, используйте 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