На волне всеобщего помешательства о здоровой пище, решил тоже обратить свое внимание на то, какое количество белков, жиров и углеводов попадает в мой организм. Засим, был создан Excel дашборд, куда я вводил все, что я съел за день. После месячного трекинга потребляемой пищи, нарисовалась, не внушающая оптимизма, картинка, которая заставила задуматься об отношении к тому, что я запихиваю в свой организм. Оказывается, количество белка, даже вполовину не соответствует рекомендуемой дневной норме потребления. Были сделаны соответствующие выводы и внесены корректировки и ежедневный рацион.
Скачать дашборд и прочитать о нюансах его работы можете под катом.
Как работать с дашбордом
Файл с дашбордом состоит из пяти листов, два из которых видимы для пользователя, остальные – скрытые.
Data (видимый) – сюда вноситься информация о приемах пищи. Пользователь вводит в новую строку таблицы (при этом таблица данных DataTable расширяется) дату, категорию, продукт и вес. При этом потреблённая энергетическая ценность рассчитывается от веса продукта.
Value (скрытый) – лист с информацией о пищевой ценности продуктов. Сюда вы заносите данные о продуктах, которые составляют ваш ежедневный рацион. Благо интернет изобилует такого рода информацией. Продукты, занесенные в эту таблицу, формируют списки на листе Data. Две таблицы на листах Data и Value связаны между собой формулой подстановки ВПР.
Обратите внимание, добавил для каждого продукта идентификационный номер (на будущее), вдруг, кто-нибудь захочет расширить функционал дашборда. На данный момент номер ID ни в одном из расчетов не используется.
Oper (скрытый) — один из двух операционных листов. Отвечает за формирование списка категорий и списка продуктов на листе Value.
Calculation (скрытый) – здесь производятся расчеты, которые в конечном итоге будут отображены в дашборде.
Dashboard (видимый) – самый красивый и основной лист, ради чего все это задумывалось. Лист с дашбордом.
В дашборде задается два параметра: период, за который вы хотите отобразить данные, и компонент, по которому строятся два верхних графика.
Как читать дашборд
На дашборде отображены четыре графика, визуализирующие информацию в определенном ключе:
Потребление компонента – график Парето, который показывает, какая категория продукта была основным вкладчиком в потребление того или иного компонента.
Тренд потребления компонента – отображает недельный тренд потребления в сравнении с рекомендуемой суточной нормой (тренд строиться за последнюю неделю от Даты ПО, указанной в периоде).
Потребление за период – здесь все понятно. Информация обо всех потребленных компонентах за период.
Факт vs Целевое значение – сравнивает все потребленные компоненты в процентном соотношении с рекомендуемой нормой. Идею взял у МакДональдса, уж больно она мне понравилась.
Excel интересности в дашборде
В дашборде используется принцип динамических диаграмм, которые меняют свое отображение в зависимости от заданных параметров. Как мы уже упоминали, в нашем случае присутствует два параметра.
В зависимости от указанного периода – первый параметр – меняются данные в таблицах листа Calculation, которые основаны на формулах СУММЕСЛИМН (диапазон C10:F15). Далее происходит сортировка выбранных данных (L10:L15) с помощью функции НАИБОЛЬШИЙ. Этот подход был описан статье Добавление сортировки в дашборд.
За формирование списков категорий и списков продуктов отвечает небольшой макрос, который запускается каждый раз, когда вы покидаете лист Value. Макрос помещает категории продуктов в столбец A листа Oper. Напротив каждой категории в строку помещается список всех продуктов, которые присутствуют в этой категории.
В зависимости от выбранной категории в таблице DataTable меняется список продуктов, который появляется при выборе продукта. За формирование списка отвечает именованная формула операц, которая использует в качестве параметра значение категории, находящееся в ячейке слева.
=СМЕЩ(Oper!$A$1;ПОИСКПОЗ(Data!$C51;спкатегорий;0)- 1;1;;СЧЁТЗ(СМЕЩ(ДВССЫЛ(АДРЕС(ПОИСКПОЗ(Data!$C51;спкатегорий;0);2;;;»Oper»));0;0;;2000)))
В итоге
Дашборд я строил исключительно под свои нужды, поэтому, вероятно, каждый из вас, скачав шаблон дашборда, сделает свой, более функциональный и красочный.
Скачать дашборд Excel бесплатно
Было бы замечательно сделать серию статей по практическому использованию LibreOffice/OpenOffice для создания дашбордов
Спасибо за комментарий, Александр. Ни разу не пользовался этими пакетами инструментов, но думаю, в будущем можно будет занятся. Идея отличная
Это продукты из ОП (OpenSourse). Приоритетный сейчас либре.
Продукт обсолютно бесплатный. Конкурент МС Офису.
Оригинальный сайт.
Понятное и лаконичное изложение материала, файлы с ценными примерами.
Так держать!
Здравствуйте Ренат,
хотел тоже выразить вам благодарность за довольно подробное описание.
Скачал ваш пример и хотел разобраться с кодом.
например этот кусочек:
li = li + 1: avArr(li, 1) = vItem.Value
Для чего стоит двоеточие после li = li + 1 «» : «» ?
спасибо
Двоеточие означает перенос строки
Правильно ли я понимаю, что «_» означает обратную операцию (т.е. код можно переносить на другую строку, а читаться он будет как часть этой)?
Спасибо
Все верно, Александр
Ренат, подскажите, пожалуйста, где прописано, при каком ивенте запускается Extract_Unique?
Спасибо
Александр, событие называется Worksheet_Deactivate(), находится в модуле листа Лист2(Value)