Продвинутое меню на день в Google Таблицах: автоматический подсчёт КБЖУ

В прошлой статье мы сделали простой шаблон меню — он помог познакомиться с базовыми возможностями Google Таблиц.
Прочитать её можно здесь: Умные Google Таблицы: автоматическое меню

Теперь пора усложнить задачу. В этой статье соберём полноценный шаблон питания на день с пятью приёмами пищи и автоматическим подсчётом калорий, белков, жиров и углеводов (КБЖУ). Всё это — без ручного пересчёта и копирования формул.

Что будет в таблице:

• 5 приёмов пищи: завтрак, второй завтрак, обед, полдник, ужин
• Выбор блюда из выпадающего списка
• Автоматическая подстановка КБЖУ по каждому блюду
• Подсчёт общего итога за день

Шаг 1. Структура листов:

Лист "Продукты"

Создайте новый лист с названием Продукты. Здесь храним все данные о блюдах и КБЖУ:

Лист "Продукты" с заполненым списком продуктов

2. Лист "Меню на день"

Создайте новый лист с названием Меню на день. В нём сделаем таблицу вот такого вида:

Пустая таблица «Меню на день» с подписями строк и столбцов

Шаг 2. Добавляем выпадающий список

Во второй колонке на листе "Меню" (где “Блюдо”) для каждой строки добавим выпадающий список, чтобы можно было выбрать блюдо из базы.

Как это сделать:

1. Выделите ячейки в столбце «Блюдо» — например B2:B6
2. В меню: Данные → Проверка данных
3. Критерий: Диапазон

Продукты!B2:B21

1. Включите галочку «Показать список в ячейке»
2. Нажмите Готово

Выпадающий список с блюдами

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

Шаг 3. Автоматическая подстановка КБЖУ

Для этого используем функцию ФИЛЬТР. Например, в ячейке C2 (калории для завтрака):

=FILTER('Продукты'!C2:C21; 'Продукты'!B2:B21 = B2)

Что делает эта формула?

Она автоматически находит и подставляет калории (или другие значения) для выбранного блюда из базы данных, которая находится на листе Продукты.

FILTER(...)

Это функция, которая находит строки, соответствующие какому-то условию, и возвращает результат.

'Продукты'!C$2:C$21

Это диапазон, откуда берутся значения — в нашем случае это колонка с калориями.

• 'Продукты'! — значит, данные находятся на другом листе с именем Продукты.
• C$2:C$21 — это диапазон с калориями.
• $2 — означает, что при копировании формулы строка 2 будет зафиксирована (не поменяется).
• Без $ — при копировании вниз диапазон мог бы «съехать», что приведёт к ошибке.

'Продукты'!$B$2:$B$21

Это где искать совпадение, то есть колонка с названиями блюд.

• Полные абсолютные ссылки ($B$2:$B$21) означают, что и буква колонки, и номер строки зафиксированы.
• Это гарантирует, что при копировании формулы, она всегда будет смотреть только на колонку с названиями, и не "собьётся".

$B2

Это ячейка, в которой пользователь выбирает блюдо (например, Омлет).

• $B — колонка фиксирована (всегда "Блюдо"),
• 2 — строка меняется при копировании вниз (чтобы завтраку соответствовала строка 2, обеду — строка 3 и т.д.)

Что такое абсолютные и относительные ссылки?

В Google Таблицах (и Excel) можно "зафиксировать" часть формулы, чтобы она не менялась, когда вы копируете формулу вверх, вниз или вбок:

Типы ссылок

Как сделать абсолютную ссылку?

1. Кликните на нужную ячейку в формуле (например, B2)
2. Нажмите F4 на клавиатуре — ссылка станет $B$2
3. Нажимайте F4 ещё раз, чтобы переключаться между видами ссылок

Так как у нас есть ссылке в формуле протягивает направо и вниз на колонки Калории, Белки, Жиры, Углеводы.

Автоматическая подстановка КБЖУ после выбора блюда

Шаг 4. Подсчёт общего КБЖУ за день

После того как вы подставили калории, белки, жиры и углеводы на каждый приём пищи, самое время посчитать итог за день — чтобы видеть общую калорийность и состав.

Столбцы C, D, E, F содержат калории, белки, жиры и углеводы по каждому приёму пищи.

В строке 7 (там, где “Итого за день”) используем простую функцию СУММ для столбцов с C, D, E, F

=СУММ(C2:C6)

Советы:

• Можно сделать итог жирным шрифтом или выделить цветом
• Если вы захотите норму — добавьте рядом вторую таблицу: «Норма» и поставьте свои цели (например, 2000 ккал, 100 белков и т.п.)

Пример с нормами

• Или можно добавить таблицу с разницей в показателях.

Для этого в столбце Отклонение добавим формулу:

=ЕСЛИ(C7>J10; "превышение на "&C7-K10; "недобор на "&C7-J10)

Пример таблицы с отклонение.

Так же можно сделать условное форматирование и прочее в таблице.
Прочитать про условное форматирование можно здесь: Условное форматирование: подсветка важного

• Так же можно для наглядности построить диаграмму.

Как сделать диаграмму:

1. Выделите диапазон:

A1:F1,A7:F7

• В меню: Вставка → Диаграмма
• Нажимаем на три точки справа и выбираем изменить диаграмму
• Тип диаграммы: Круговая диаграмма

Добавление круговой диаграммы

Можно добавить во вкладке дополнительно подписи осей для наглядности, цвета — на вкус.

Изменения стиля диаграммы

Итог

Теперь у вас есть удобная таблица со списком блюд и автоматическим расчётом КБЖУ на день.

В следующей статье разберём, как на основе меню сформировать список покупок на неделю — тоже автоматически.
Прочитать можно здесь: Автоматический список покупок в Google Таблицах: продолжение меню с КБЖУ