Бесплатный шаблон дашборда в Excel — потребленная энергетическая ценность продуктов

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

Скачать дашборд и прочитать о нюансах  его работы можете под катом.

Как работать с дашбордом

Файл с дашбордом состоит из пяти листов, два из которых видимы для пользователя, остальные – скрытые.

Data (видимый) – сюда вноситься информация о приемах пищи. Пользователь вводит в новую строку таблицы (при этом таблица данных DataTable расширяется) дату, категорию, продукт и вес. При этом потреблённая энергетическая ценность рассчитывается от веса продукта.

Ввод данных в дашборд

Value (скрытый) – лист с информацией о пищевой ценности продуктов. Сюда вы заносите данные о продуктах, которые составляют ваш ежедневный рацион. Благо интернет изобилует такого рода информацией. Продукты, занесенные в эту таблицу, формируют списки на листе Data. Две таблицы на листах Data и Value связаны между собой формулой подстановки ВПР.

Обратите внимание, добавил для каждого продукта идентификационный номер (на будущее), вдруг, кто-нибудь захочет расширить функционал дашборда. На данный момент номер ID ни в одном из расчетов не используется.

Oper (скрытый) — один из двух операционных листов. Отвечает за формирование списка категорий и списка продуктов на листе Value.

Calculation (скрытый) – здесь производятся расчеты, которые в конечном итоге будут отображены в дашборде.

Dashboard (видимый) – самый красивый и основной лист, ради чего все это задумывалось. Лист с дашбордом.

лист с дашбордом excel

В дашборде задается два параметра: период, за который вы хотите отобразить данные, и компонент, по которому строятся два верхних графика.

Как читать дашборд

На дашборде отображены четыре графика, визуализирующие информацию в определенном ключе:

Потребление компонента – график Парето, который показывает, какая категория продукта была основным вкладчиком в потребление того или иного компонента.

Тренд потребления компонента – отображает недельный тренд потребления в сравнении с рекомендуемой суточной нормой (тренд строиться за последнюю неделю от Даты ПО, указанной в периоде).

Потребление за период – здесь все понятно. Информация обо всех потребленных компонентах за период.

Факт vs Целевое значение – сравнивает все потребленные компоненты в процентном соотношении с рекомендуемой нормой. Идею взял у МакДональдса, уж больно она мне понравилась.

макдональдс в excel

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

В итоге

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

Скачать дашборд Excel бесплатно


10 комментариев

  1. Здравствуйте Ренат,
    хотел тоже выразить вам благодарность за довольно подробное описание.

    Скачал ваш пример и хотел разобраться с кодом.

    например этот кусочек:

    li = li + 1: avArr(li, 1) = vItem.Value

    Для чего стоит двоеточие после li = li + 1 «» : «» ?

    спасибо

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *