1000卡路里计算器_Excel卡路里计数器-CSDN博客

1000卡路里计算器

On Monday I described the horrible side effects of Christmas cookies, and posted an Excel Weight Loss Tracker workbook. Now, here is an Excel Calorie Counter to add to your tool kit.

周一，我描述了圣诞节饼干的可怕副作用，并发布了Excel减肥追踪器工作簿。现在，这是一个Excel卡路里计数器，可以添加到您的工具箱中。

减肥追踪器 (Weight Loss Tracker)

As Dick Kusleika pointed out in the comments for the Weight Loss Tracker, simply downloading the workbook doesn't have any effect on your weight. Apparently it takes a bit of effort, including dietary changes and maybe some <gasp> exercise. Can't Microsoft invent something to make weight loss easier? I don't even care if it has menus or a Ribbon!

正如Dick Kusleika在“减肥追踪器”的评论中指出的那样，仅下载工作簿对您的体重没有任何影响。显然，这需要一些努力，包括饮食变化和一些<gasp>运动。微软不能发明使减肥更容易的东西吗？我什至不在乎它是否有菜单或功能区！

Excel卡路里计数器食品数据 (Excel Calorie Counter Food Data)

Bill Gates isn't answering my phone calls, so I created an Excel Calorie Counter that I can use in the meantime. As soon as Bill sends me something better, I'll let you know.

比尔·盖茨没有接听电话，因此我创建了一个可同时使用的Excel卡路里计数器。比尔给我发送更好的消息后，我会立即通知您。

To start, I copied some food data from the Health Canada website. I created a list of foods in an Excel workbook, with categories, food items, measurements and calorie counts.

首先，我从加拿大卫生部网站复制了一些食品数据。我在Excel工作簿中创建了食物列表，其中包括类别，食物项目，测量值和卡路里计数。

You can add items to the list, and sort the list by category when you're finished adding food items.

您可以将项目添加到列表中，并在完成添加食物项目后按类别对列表进行排序。

公制测量 (Metric Measurements)

The measurements are metric, so I added an approximate conversion list too, in case you don't speak metric. There's also a table where you can do your own conversions, by typing a number in the green cells. The CONVERT function calculates the amount in the adjacent white cell.

度量是公制的，所以我也添加了一个近似转换列表，以防您不使用公制。还有一个表格，您可以通过在绿色单元格中键入数字来进行自己的转换。转换功能计算相邻白电池中的数量。

在食物查询表中添加更多列 (Add More Columns to the Food Lookup Table)

The food lookup table in the sample file only has calories list. If you want to add more columns to the lookup table, such as fat or carbs, you'll also need to change the named range for the lookup table.

示例文件中的食物查找表仅包含卡路里列表。如果要向查找表中添加更多列，例如脂肪或碳水化合物，则还需要更改查找表的命名范围 。

In Excel 2007, click the Formulas tab, and click Name Manager (In Excel 2003, click Insert | Name | Define)
在Excel 2007中，单击“公式”选项卡，然后单击“名称管理器”(在Excel 2003中，单击“插入” |“名称” |“定义”)
In the list of names, click on FoodLookup
在名称列表中，单击“ FoodLookup”
In the Refers To box, at the bottom, change the number of columns, from 3, to the number of columns in your revised table.
在底部的“引用到”框中，将列数从3更改为修订表中的列数。

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

= OFFSET(FoodList $ B $ 1,1,0，COUNTA(FoodList $ B：！$ B)-1，3)

Click the check mark at the left, to confirm the change, and click Close
单击左侧的复选标记，以确认更改，然后单击“关闭”。

Also, see below, for adding more columns to the Daily Calorie Counter

另外，请参阅下文，为“每日卡路里计数器”添加更多列

每日卡路里计数器 (The Daily Calorie Counter)

In the main sheet, named FoodEntry, you can enter the date, your target calorie count, and all the foods that you eat. There are only 22 rows for data entry, so that should help limit your food intake!

在名为FoodEntry的主表中，您可以输入日期，目标卡路里计数以及所有食用的食物。数据输入只有22行，因此应该有助于限制食物摄入！

In the green data entry cells, there are drop down lists, created with Excel's data validation. The Meal/Snack and Category source lists are stored in named ranges on the Lists sheet.

在绿色数据输入单元格中，有使用Excel的数据验证创建的下拉列表。餐食/零食和类别源列表存储在“列表”表上的命名范围内。

There's a dependent data validation drop down list in the Food Item column, based on the Category that you select. This list comes from the FoodList sheet, so it's important to keep the FoodList sheet sorted by category. Otherwise, the wrong items might appear in the Food Item drop down list.

根据您选择的类别，“食品项目”列中有一个从属数据验证下拉列表。该列表来自FoodList工作表，因此，重要的是要按类别对FoodList工作表进行排序。否则，错误的项目可能会出现在“食品项目”下拉列表中。

添加数量 (Add the Quantity)

After you select a food item, its measurement appears, and you can enter the quantity that you ate, based on that measurement. For example, if the measurement is 125mL, and you ate 250mL, type 2 as the quantity. Once the quantity is entered, the total calories for that food item are calculated.

选择食品后，将显示其度量值，然后您可以根据该度量值输入所吃的食物数量。例如，如果测量值为125mL，而您吃了250mL，则键入2作为数量。输入数量后，将计算该食品的总卡路里。

NOTE: If you can't find a specific food, just type it in the cell, and enter the calorie count manually. Or, add the food item to the lookup table on the FoodList worksheet.

注意：如果找不到特定食物，只需在单元格中键入，然后手动输入卡路里计数即可。或者，将食品添加到FoodList工作表上的查找表中。

在每日卡路里计数器中添加更多列 (Add More Columns to the Daily Calorie Counter)

The Daily Calorie Counter in the sample file only has calories listed. If you added more columns to the lookup table, such as fat or carbs, you'll also need to add those columns to the Daily Calorie Counter sheet.

示例文件中的“每日卡路里计数器”仅列出卡路里。如果您在查询表中添加了更多列，例如脂肪或碳水化合物，则还需要将这些列添加到“每日卡路里计数器”表中。

On the FoodEntry sheet, insert extra columns to the right of column G
在FoodEntry工作表上，在G列的右侧插入多余的列
Select cells G4:G26, and drag to the right, creating an extra column for each of your new columns
选择单元格G4：G26，然后向右拖动，为每个新列创建一个额外的列
The heading cells should automatically fill in, based on the headings on the Food Lookup table.
标题单元格应根据“食物查找”表上的标题自动填写。
Select cell G5, and change the formula, so it includes the heading cells for all the columns in your food lookup table. In the sample file, the headings are in $B$1:$D$1. With one additional column, you would change that to $B$1:$E$1
选择单元格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))

= IF($ D5 =“”，“”，$ E5 * VLOOKUP($ D5，FoodLookup，MATCH(H $ 4，FoodList！ $ B $ 1：$ D $ 1,0)，0))

Copy the revised formula down to the last row, and right, to the last column.
将修改后的公式向下复制到最后一行，然后将其复制到最后一列。

Also, see below, for adding more columns to the Calorie Counter Data.

另外，请参阅下面的内容，将更多列添加到卡路里计数器数据中。

存储卡路里计数器数据 (Storing the Calorie Counter Data)

After you've finished eating for the day, or at the start of the next day, you can move all the data to the Calorie Counter database. Just click the "Save Daily Data and Clear" button at the top of the FoodEntry sheet.

当天或第二天开始吃完饭后，可以将所有数据移至卡路里计数器数据库中。只需单击FoodEntry表顶部的“保存每日数据并清除”按钮。

The daily data is copied, as values, to the DailyRecord worksheet, and stamped with the date that you entered on the FoodEntry sheet.

每日数据将作为值复制到DailyRecord工作表中，并在您在FoodEntry工作表上输入的日期上盖印。

将更多列添加到卡路里计数器数据 (Add More Columns to the Calorie Counter Data)

The Calorie Counter Data in the sample file only has calories listed. If you added more columns to the lookup table, such as fat or carbs, you'll also need to add those columns to the Calorie Counter Data sheet.

示例文件中的卡路里计数器数据仅列出卡路里。如果您在查询表中添加了更多列(例如脂肪或碳水化合物)，则还需要将这些列添加到“卡路里计数器数据”表中。

On the DailyRecord sheet, type the same new column headings, in the same order, that you added to the food lookup table,
在DailyRecord表上，以与添加到食物查找表中相同的顺序键入相同的新列标题，

调整“保存每日数据”宏 (Tweak the Save Daily Data Macro)

When you click the "Save Daily Data" button on the Daily Calorie Counter sheet, all the data is copied to the Calorie Counter Data sheet. You'll tweak the macro that button runs, so it copies your new columns too.

当您单击“每日卡路里计数器”工作表上的“保存每日数据”按钮时，所有数据都将复制到“卡路里计数器数据”工作表中。您将调整该按钮运行的宏，以便它也复制您的新列。

Right-click the "Save Daily Data" button, and click Assign Macro
右键单击“保存每日数据”按钮，然后单击“分配宏”
Click the Edit button
点击编辑按钮
- Set rCalc = rEntry.Offset(0, 4).Resize(rEntry.Rows.Count - 1, 2)
  设置rCalc = rEntry.Offset(0，4).Resize(rEntry.Rows.Count- 1，2 )
In the following line of code, increase the 2 to include all your new columns. Currently, only 2 columns, F:G, will be updated in the macro.
在下面的代码行中，将2增加为包括所有新列。当前，宏中仅更新两列F：G。
- rCalc.Columns(2).Formula = _
  rCalc.Columns(2).Formula = _
  
  "=IF($D5="""","""",$E5*VLOOKUP($D5,FoodLookup,MATCH(G$4,FoodList!$B$1:$D$1,0),0))"
  “ = IF($ D5 =”“”“，”“”，$ E5 * VLOOKUP($ D5，FoodLookup，MATCH(G $ 4，FoodList！ $ B $ 1：$ D $ 1,0)，0))“”
Copy the line of code (above) that you just modified, and paste in another copy of the code, for each new column. For example, if you added one additional column, you would copy and paste that code once.
复制刚修改的代码行(上面)，并为每个新列粘贴代码的另一个副本。例如，如果添加了另一列，则将复制并粘贴该代码一次。
- rCalc.Columns(3).Formula = _
  rCalc.Columns( 3 )。公式= _
  
  "=IF($D5="""","""",$E5*VLOOKUP($D5,FoodLookup,MATCH(H$4,FoodList!$B$1:$E$1,0),0))"
  “ = IF($ D5 =”“”“，”“”，$ E5 * VLOOKUP($ D5，FoodLookup，MATCH( H $ 4，FoodList！ $ B $ 1：$ E $ 1,0)，0))“”

汇总卡路里计数器数据 (Summarizing the Calorie Counter Data)

The stored data is in a dynamic range named FoodRecord, and there's a pivot table based on that named range. When you click the button to save your daily records, the pivot table is automatically updated.

存储的数据在一个名为FoodRecord的动态范围内，并且有一个基于该命名范围的数据透视表。单击按钮保存日常记录时，数据透视表将自动更新。

I've add a few fields to the pivot table, to get you started on a report. You can add more fields, or rearrange things, to get the report structure that you'd like.

我已在数据透视表中添加了一些字段，以帮助您开始创建报表。您可以添加更多字段或重新安排内容，以获得所需的报告结构。

下载示例Excel卡路里计数器 (Download the Sample Excel Calorie Counter)

You can download the latest version of the calorie counter on my Contextures website. It is in the Excel Sample Files page, in the User Forms section: UF0016 - Excel Calorie Counter With Recipe Calculator. For instructions on how to use the latest version, see this blog post: Excel Recipe Nutrients Calculator

您可以在我的Contextures网站上下载卡路里计数器的最新版本。它在Excel Sample Files页面的User Forms部分中： UF0016-带食谱计算器的Excel卡路里计数器。有关如何使用最新版本的说明，请参见此博客文章： Excel Recipe Nutrients Calculator

PLEASE NOTE: As I mentioned on Monday, I'm not a medical professional, so use this workbook for entertainment purposes only. The calorie counts are from Health Canada, so the ingredients and calorie counts might be different where you live. Check the product labels for accurate numbers.

请注意：正如我在周一提到的，我不是医学专家，因此只能将此工作簿用于娱乐目的 。卡路里计数来自加拿大卫生部，因此您居住的地点的成分和卡路里计数可能会有所不同。检查产品标签上的准确数字。

And some of the calorie counts might be incorrect – I'm pretty sure the actual calories for wine and cookies are much lower than the Health Canada estimates. 😉 _______

而且某些卡路里计数可能不正确–我很确定葡萄酒和饼干的实际卡路里含量远低于加拿大卫生部的估计。 😉_______