Лічильник калорій Excel

У понеділок я описав жахливі побічні ефекти різдвяного печива та опублікував робочу книгу Excel для схуднення. Тепер, ось лічильник калорій Excel, який ви можете додати до свого набору інструментів.

Відстежувач схуднення

Як зазначав Дік Куслейка в коментарях до програми «Відстежувач схуднення», просто завантаження книги не впливає на вашу вагу. Очевидно, це вимагає трохи зусиль, включаючи зміни в харчуванні і, можливо, деякі вправи. Чи не може Microsoft вигадати щось, щоб полегшити схуднення? Мені навіть байдуже, чи є в ньому меню чи стрічка!

Лічильник калорій Excel Дані про їжу

Білл Гейтс не відповідає на мої телефонні дзвінки, тому я створив лічильник калорій Excel, яким тим часом можу користуватися. Як тільки Білл надішле мені щось краще, я повідомлю вас.

Для початку я скопіював деякі дані про їжу з веб-сайту Health Canada. Я створив список продуктів у книзі Excel із категоріями, продуктами харчування, вимірами та кількістю калорій.

Ви можете додати елементи до списку та відсортувати список за категоріями, коли закінчите додавати продукти харчування.

Метричні вимірювання

Вимірювання є метричними, тому я також додав приблизний список перетворень, якщо ви не говорите метрично. Існує також таблиця, де ви можете зробити власні перетворення, набравши число в зелених клітинках. Функція CONVERT обчислює кількість сусідньої білої клітини.

Додайте більше стовпців до таблиці пошуку продуктів

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

У програмі Excel 2007 перейдіть на вкладку Формули та виберіть Диспетчер імен (у програмі Excel 2003 натисніть Вставити | Ім'я | Визначити)
У списку імен натисніть FoodLookup
У полі З посиланнями внизу змініть кількість стовпців з 3, до кількості стовпців у вашій переглянутий таблиці.

= OFFSET (FoodList! $ B $ 1,1,0, COUNTA (FoodList! $ B: $ B) -1, 3)

Клацніть прапорець зліва, щоб підтвердити зміну, і натисніть кнопку Закрити

Крім того, див. Нижче, щоб додати більше стовпців до щоденного лічильника калорій

Щоденний лічильник калорій

У головному аркуші під назвою FoodEntry ви можете ввести дату, цільову кількість калорій та всі продукти, які ви їсте. Є лише 22 рядки для введення даних, тому це повинно допомогти обмежити споживання їжі!

У зелених клітинках для введення даних є випадаючі списки, створені за допомогою перевірки даних Excel. Списки джерел їжі/закусок та категорій зберігаються в названих діапазонах на аркуші Списки.

У стовпці Продукт харчування є випадаючий список перевірки залежних даних на основі вибраної категорії. Цей список походить із аркуша FoodList, тому важливо зберегти аркуш FoodList відсортованим за категоріями. В іншому випадку неправильний товар може з’явитися у випадаючому списку Продукти харчування.

Додайте кількість

Після вибору продукту харчування з’являється його вимірювання, і ви можете ввести кількість, яку ви з’їли, на основі цього вимірювання. Наприклад, якщо вимірювання становить 125 мл, а ви з’їли 250 мл, введіть 2 як кількість.
Після введення кількості розраховується загальна кількість калорій для цього продукту харчування.

ПРИМІТКА. Якщо ви не можете знайти конкретну їжу, просто введіть її в клітинку та введіть кількість калорій вручну. Або додайте продукт до таблиці пошуку на аркуші FoodList.

Додайте більше стовпців до щоденного лічильника калорій

Щоденний лічильник калорій у зразковому файлі містить лише калорії. Якщо ви додали до таблиці пошуку більше стовпців, таких як жир або вуглеводи, вам також потрібно буде додати ці стовпці на аркуш щоденного лічильника калорій.

На аркуші FoodEntry вставте додаткові стовпці праворуч від стовпця G
Виділіть комірки G4: G26 і перетягніть праворуч, створюючи додатковий стовпець для кожного з ваших нових стовпців
Клітини заголовка повинні заповнюватися автоматично, виходячи з заголовків у таблиці пошуку продуктів.
Виділіть клітинку G5 і змініть формулу, щоб вона включала клітинки заголовків для всіх стовпців у вашій таблиці пошуку продуктів. У файлі зразка заголовки містяться в $ B $ 1: $ D $ 1 . За допомогою одного додаткового стовпця ви б змінили це на $ B $ 1: $ E $ 1

= IF ($ D5 = ", ", $ E5 * VLOOKUP ($ D5, FoodLookup, MATCH (H $ 4, FoodList! $ B $ 1: $ D $ 1, 0), 0))

Скопіюйте оновлену формулу до останнього рядка та праворуч до останнього стовпця.

Також, див. Нижче, про додавання додаткових стовпців до даних лічильника калорій.

Зберігання даних лічильника калорій

Після того, як ви закінчите їсти протягом дня або на початку наступного дня, ви зможете перенести всі дані до бази даних лічильника калорій. Просто натисніть кнопку "Зберегти щоденні дані та очистити" у верхній частині аркуша FoodEntry.

Щоденні дані копіюються у вигляді значень на аркуш DailyRecord і проставляються з датою, яку ви ввели на аркуші FoodEntry.

Додайте більше стовпців до даних лічильника калорій

Дані лічильника калорій у зразковому файлі містять лише вказані калорії. Якщо ви додали до таблиці пошуку більше стовпців, таких як жир або вуглеводи, вам також потрібно буде додати ці стовпці до аркуша даних лічильника калорій.

На аркуші DailyRecord введіть ті самі нові заголовки стовпців у тому самому порядку, що і ви додали до таблиці пошуку продуктів,

Налаштуйте макрос збереження щоденних даних

Коли ви натискаєте кнопку "Зберегти щоденні дані" на аркуші Щоденного обліку калорій, усі дані копіюються на аркуш даних "Лічильник калорій". Ви налаштуєте макрос, який запускає кнопка, щоб він також копіював ваші нові стовпці.

Клацніть правою кнопкою миші кнопку "Зберегти щоденні дані" та натисніть Призначити макрос
Натисніть кнопку Редагувати
У наступному рядку коду збільште 2 щоб включити всі ваші нові стовпці. Наразі у макросі буде оновлено лише 2 стовпці, F: G.
- Встановити rCalc = rEntry.Offset (0, 4). Змінити розмір (rEntry.Rows.Count - 1, 2)
Знайдіть наступний рядок коду та змініть його посилання на заголовок, щоб відповідати оновленій формулі в комірці G4:
- rCalc.Columns (2) .Формула = _
  "= IF ($ D5 =" " "," " ", $ E5 * VLOOKUP ($ D5, FoodLookup, MATCH (G $ 4, FoodList! $ B $ 1: $ D $ 1, 0), 0))"
Скопіюйте рядок коду (вище), який ви щойно змінили, та вставте іншу копію коду для кожного нового стовпця. Наприклад, якщо ви додали один додатковий стовпець, скопіюйте та вставте цей код один раз.
Перегляньте кожен скопійований рядок коду, щоб він відображав номер наступного стовпця та посилання на клітинку для заголовка в цьому стовпці. Наприклад, якщо є один новий стовпець (H), а заголовки таблиці пошуку є B1: E1, ось переглянутий скопійований код:
- rCalc.Columns (3) .Формула = _
  "= IF ($ D5 =" " "," " ", $ E5 * VLOOKUP ($ D5, FoodLookup, MATCH (H $ 4, FoodList! $ B $ 1: $ E $ 1, 0), 0))"

Узагальнення даних про лічильник калорій

Зберігаються дані знаходяться в динамічному діапазоні з назвою FoodRecord, і на основі цього іменованого діапазону існує зведена таблиця. Коли ви натискаєте кнопку, щоб зберегти щоденні записи, зведена таблиця автоматично оновлюється.

Я додав кілька полів у зведену таблицю, щоб розпочати роботу зі звітом. Ви можете додати більше полів або переставити речі, щоб отримати потрібну структуру звіту.

Завантажте зразок лічильника калорій Excel

Ви можете завантажити останню версію лічильника калорій на моєму веб-сайті Contexture. Він знаходиться на сторінці зразків файлів Excel, у розділі Форми користувача: UF0016 - Лічильник калорій Excel із калькулятором рецептів. Інструкції щодо використання останньої версії див. У цій публікації в блозі: Калькулятор поживних речовин рецептів Excel

БУДЬ ЛАСКА, ЗАПИШИ: Як я вже згадував у понеділок, я не є медичним працівником, тому використовуйте цю книгу для лише для розважальних цілей. Кількість калорій - від Health Canada, тому інгредієнти та кількість калорій можуть відрізнятися там, де ви живете. Перевірте етикетки товару на наявність точних цифр.

І деякі показники калорій можуть бути неправильними - я майже впевнений, що фактичні калорії для вина та печива набагато нижчі, ніж оцінює Health Canada. 😉
_______

35 думок на тему “Лічильник калорій Excel”

Який цікавий спосіб допомогти людям дотримуватися своїх новорічних рішень! Я знаю, що люди з Excel хотіли б, щоб ви поділилися своїми знаннями та ідеями з громадою на сторінці Office у Facebook.

Кім
Команда MSFT Office Outreach

Дякую Кім, я рада, що вам сподобалась стаття.

Я зробив! Сподіваюся, ви зможете продовжувати ділитися своїми думками та досвідом з рештою спільноти Office. Підрахунок калорій та збереження здоров’я, безумовно, зараз у багатьох людей на увазі.

Кім
Команда MSFT Office Outreach

[. ] Лічильник калорій Excel [. ]

Щиро дякую за це. Мені дійсно потрібно було зібрати свої дії, і це дуже корисно, дякую, що поділилися своєю важкою працею!

Привіт,
Щойно відкрив ваші електронні таблиці - це саме те, що я шукав!

Єдина проблема, коли я натискаю кнопку "Зберегти щоденні дані та очистити", я отримую повідомлення про помилку:

'((# REF.xls не вдалося знайти. Перевірте написання імені файлу та перевірте правильність розташування файлу.

Чи можете ви порадити будь ласка?

Дуже хороша робота та відмінна демонстрація навичок роботи з Excel.
Дружина дуже хоче цим скористатися, але має ту ж проблему, що й Рей Кей; Єдина проблема, коли я натискаю кнопку "Зберегти щоденні дані та очистити", я отримую повідомлення про помилку:

'((# REF.xls не вдалося знайти. Перевірте написання імені файлу та перевірте правильність розташування файлу.'

Виправте це, і це було б непоганим інструментом.

Добре, я знайшов виправлення. Рей Кей зроби це.
Клацніть правою кнопкою миші кнопку "Зберегти та очистити".
Клацніть лівою кнопкою миші 'Призначити макрос'.
Клацніть лівою кнопкою миші "Додати дані"
Клацніть лівою кнопкою миші 'OK'

Тепер це має спрацювати.

Дякую Баркс і Рей Кей - я виправив версію лічильника калорій в Excel 2003, де була проблема.

Чи можете ви відстежувати більше калорій, я хотів би додати стовпець для відстеження жирових грамів

[. ] ми розглянемо функцію TRIM. У січні деякі люди намагаються ВТРИМАТИ кілька кілограмів, і мої книги з підрахунку калорій в Excel та книги з відстеження втрати ваги в Excel популярні. На жаль, функція TRIM не допоможе [. ]

Дякуємо за електронну таблицю. Цікаво, чи замислювались ви коли-небудь про те, щоб додати спосіб використовувати електронну таблицю для двох людей, тобто чоловіка та дружини, які підраховують калорії разом. Я міг би створити два окремі файли Excel, але тоді мені доведеться постійно додавати елементи до обох списків продуктів, а не лише до одного. Будь-які думки?

Я дуже новачок у excel та vba, тому все це здається дещо залякуючим. Я хотів створити власний планувальник їжі в Excel, і мені рекомендували перевірити ваш сайт. (Ось де я вперше опублікував Mr.Excel: http://www.mrexcel.com/forum/showthread.php?p=2631014&posted=1#post2631014) Ваша програма виглядає справді чудово!

Однак я хотів створити щось, що складає загальну кількість усіх поживних речовин, а не лише калорій. Я використовував веб-сайт, схожий на те, що ви використовували для копіювання та збереження даних про їжу в Excel, який включає деталі кальцію, вітаміну А тощо. Http://www.nal.usda.gov/fnic/foodcomp/search/

Я впорядкував свої дані трохи інакше: перший стовпець зліва - це список поживних речовин. Наступні стовпці мають назву їжі в першому рядку, а потім перелічують кількість поживних речовин у першій колонці на 100 грам порції. Останній стовпець відображає діапазони, рекомендовані для мого віку, ваги та рівня активності, взяті звідси: http://fnic.nal.usda.gov/interactiveDRI/

Я сподівався, що зможу навчитися створювати інтерфейс, який дозволить мені вибирати продукти та їх кількість, складати їх, і порівнювати загальну кількість поживних речовин із рекомендованими для мого віку, ваги, рівня активності, щоб я міг планувати харчування щодня.

Ну, схоже, ваша програма робить приблизно те саме, за винятком того, що вона лише додає калорії. Можливо, якщо я вивчу його і обдурю його досить довго, я зрозумію, як змінити та додати функції. Але якби ви надіслали мені електронне повідомлення, я міг би надіслати вам свої столи, і, можливо, ми могли б це разом розібратися.

Дякуємо за електронну таблицю. Це прекрасно . Щоб зробити це ще кращим, чи можете ви додати додаткові 3 стовпці для розрахунку білків, вуглеводів та жиру? Спасибі

Великий аркуш. Дуже корисний для мого клієнта, я ціную ваші зусилля.

Дякую! Саме те, що я шукав.

Я щойно натрапив на цю книгу днями, і, з одного боку, я її люблю, а з іншого боку, маю невелику проблему з нею.
Я намагаюся розширити інформацію, щоб включити не лише калорії, але жир, білки, вуглеводи тощо. Я успішно додав всю інформацію, яка мені потрібна, до списку продуктів, але мої графі в щоденному введенні не втягуватимуть інформацію.
Усі клітинки після стовпця Калорії, який перерахований першим, відображають помилку #REF, і я не можу протягом усього життя з’ясувати, як це виправити.
Мені цікаво, чи є в будь-якому випадку, як творець, ви готові змінити книгу, щоб вона працювала належним чином для мене, і надіслати її мені.
Це було б дуже вдячне, оскільки я майже відмовився від цієї справи.
Дякую!

@ Восени, я оновив публікацію в блозі, щоб показати інструкції щодо додавання нових стовпців до книги.

Дякую, Дебра! Це нарешті працює, і я не можу дочекатися, щоб почати користуватися ним.

@ Осінь, чудово - рада, що зараз це працює.

Я також хочу додати в книгу аркуш, який би працював подібно до аркуша щоденного входу, де я можу ввести рецепт для розрахунку поживної інформації на 1 порцію згаданого рецепта.
Чи повинні ті самі коди для щоденного входу працювати і для цього? Або мені потрібно робити щось інше?
Дякую.

@ Осінь, я зробив щось подібне, і отримаю зразок файлу якомога швидше. Ви можете використовувати подібний код, але ви просто хочете зберегти кінцевий результат із назвою рецепта та загальною інформацією про поживні речовини, а також кількістю порцій, замість того, щоб зберігати всі рядки інгредієнтів.

@Debra, Просто цікаво, чи змогли ви щось зробити, де я можу зберегти загальну кількість рецептів у своєму списку продуктів.
Копіювання підсумків працює, але я сподівався, що є щось, що буде працювати швидше і простіше.
Дякую.

@ Осінь, дякую за нагадування! Наступного тижня я опублікую калькулятор рецептів.

@ Дебра
Дякую!
Я не старанно ним користувався, тому забув про це.
Але я починаю новий план у понеділок, тому, якщо це можливо, це буде в понеділок, це було б чудово.
Дякую!

@ Осінь, це буде у вівторковому дописі в блозі, тож сподіваюсь, ви зможете зачекати до того часу!

Вівторок буде добре. Дякую!

Щиро дякую за чудову електронну таблицю. Такий простий у використанні та маніпулюванні - навіть для задоволення австралійських потреб. Любіть це! 🙂