В прошлых статьях мы создали шаблон меню на день с автоматическим подсчётом КБЖУ.
Прочитать начало можно здесь: Продвинутое меню на день в Google Таблицах: автоматический подсчёт КБЖУ
Теперь сделаем следующий шаг — автоматический список покупок на неделю, исходя из выбранных блюд и количества порций. Без ручных расчётов и копирования.
Шаг 1. Подготовка списка ингредиентов в «Продуктах»
На листе "Продукты" добавим к каждому блюду список ингредиентов с весом на одну порцию.
Формат:
- Все ингредиенты указываем в одной ячейке, через запятую.
- Вес указываем через слеш. Пример: овсянка/40, молоко/200, сахар/10
- Важно не создавать лишних пробелов. Пишите название составных ингредиентов через нижнее подчеркивание или другой разделитель. Например: филе_куриное
Это значит, что для одной порции нужно 40 г овсянки, 200 мл молока и 10 г сахара.
Важно: единицы измерения должны быть одинаковыми: либо всё в граммах, либо всё в миллилитрах — чтобы правильно суммировать.
Шаг 2. Создаём лист «Разделённые»
На этом листе мы разобьём список ингредиентов по одному в строку. Это подготовит данные для будущего расчёта.
Создайте новый лист и назовите его «Разделённые». Добавьте 4 заголовка в строки A1:D1:
- A — Блюдо
- B — Продукт+Вес (например, "молоко/200")
- C — Продукт (только название)
- D — Вес (только число)
Формула в колонке A:
Вставьте её в ячейку A2:
=ARRAYFORMULA(SPLIT(FLATTEN(FILTER('Продукты'!B2:B & "***" & SPLIT('Продукты'!G2:G; ", ")); 'Продукты'!G2:G <> "")); "***"))
Что делает эта формула:
- SPLIT('Продукты'!G2:G; ", ") — разбивает строку с ингредиентами по запятой.
- 'Продукты'!B2:B & "***" & ... — приклеивает к каждому ингредиенту название блюда. Разделитель *** нужен, чтобы потом разрезать строку обратно.
- FLATTEN(...) — собирает всё в одну длинную колонку.
- SPLIT(...; "***") — делит обратно на две колонки: блюдо и продукт/вес.
- ARRAYFORMULA(...) — делает всё это автоматически для всех строк.
Шаг 2.1. Извлекаем название продукта
В колонке C (ячейка C2):
=ARRAYFORMULA(ЕСЛИ(B2:B=""; ""; ЛЕВСИМВ(B2:B; ПОИСК("/"; B2:B)-1)))
Пояснение:
- ПОИСК("/"; B2:B) — находит позицию слэша.
- ЛЕВСИМВ(...; ... -1) — возвращает всё до него.
- ЕСЛИ(... = ""; ""; ...) — не работает с пустыми строками.
Пример: "молоко/200" → "молоко"
Шаг 2.2. Извлекаем вес ингредиента
В колонке D (ячейка D2):
=ARRAYFORMULA(ЕСЛИ(B2:B=""; ""; ЗНАЧЕН(ПСТР(B2:B; ПОИСК("/"; B2:B)+1; 10))))
Что делает формула:
- ПОИСК("/"; ...) + 1 — находит позицию после слэша.
- ПСТР(...; ...; 10) — извлекает строку длиной до 10 символов (достаточно для веса).
- ЗНАЧЕН(...) — превращает строку в число.
Пример: "молоко/200" → 200
Шаг 3. Формируем автоматический список покупок
Теперь переходим к самому интересному — создаём лист «Покупки», который будет собирать нужные продукты и рассчитывать общий вес с учётом количества порций.
Добавьте заголовки в строки A1:C1:
- A — Продукт
- B — Вес на одну порцию (суммарный по всем блюдам)
- C — Общий вес (с учётом количества порций)
В ячейке E2 (или любой свободной) напишите, сколько порций нужно (например, 3).
Шаг 3.1. Собираем список нужных продуктов
В ячейке A2:
=UNIQUE(FILTER(Разделенные!C2:C;(Разделенные!C2:C <> "") *
ISNUMBER(MATCH(Разделенные!A2:A; 'Меню на день'!B2:B; 0))))
Как работает:
- MATCH(...; 'Меню на день'!B2:B; 0) — проверяет, выбрано ли блюдо в меню.
- FILTER(...) — отбирает только нужные ингредиенты.
- UNIQUE(...) — убирает повторы, чтобы продукт был только один раз.
Шаг 3.2. Суммируем вес на одну порцию
В ячейке B2:
=ARRAYFORMULA(IF(A2:A = ""; "";BYROW(A2:A;LAMBDA(продукт;SUM(
FILTER(Разделенные!D2:D;(Разделенные!C2:C = продукт) * ISNUMBER(MATCH(Разделенные!A2:A; 'Меню на день'!B2:B; 0))))))))
Объяснение:
- Для каждого продукта ищем его вес в таблице «Разделённые».
- Смотрим только на блюда, которые выбраны в меню.
- Складываем вес, если один и тот же продукт встречается в нескольких блюдах.
Шаг 3.3. Умножаем на количество порций
В ячейке C2:
=ARRAYFORMULA(IF(B2:B = ""; ""; B2:B * $E$2))
- B2:B — вес на одну порцию
- $E$2 — количество порций (например, 3)
- ARRAYFORMULA — делает расчёт сразу для всех строк
Результат:
- Вы просто выбираете блюда в меню — список продуктов формируется сам.
- Указываете нужное количество порций — и автоматически видите итоговый вес.
- Всё готово для распечатки, экспорта или использования в магазине.
Если это первый раз, когда вы делаете такую таблицу — не бойтесь. Повторите шаги, и уже после первого раза вы будете уверенно ориентироваться в формулах. Это похоже на магию, но на самом деле — просто порядок и логика.