mfc 创建excel表格_如何在Microsoft Excel中创建费用和收入电子表格

mfc 创建excel表格

Creating an expense and income spreadsheet can help you manage your personal finances. This can be a simple spreadsheet that provides an insight into your accounts and tracks your main expenses. Here’s how in Microsoft Excel.

创建费用和收入电子表格可以帮助您管理个人财务。这可以是一个简单的电子表格，可以深入了解您的帐户并跟踪您的主要费用。这是在Microsoft Excel中的方法。

创建一个简单列表 (Create a Simple List)

In this example, we just want to store some key information about each expense and income. It doesn’t need to be too elaborate. Below is an example of a simple list with some sample data.

在此示例中，我们只想存储有关各项费用和收入的一些关键信息。不需要太复杂。下面是带有一些示例数据的简单列表的示例。

Enter column headers for the information you want to store about each expense and form of income along with several lines of data as shown above. Think about how you want to track this data and how you would refer to it.

输入要存储的有关每种费用和收入形式的信息的列标题，以及如上所示的几行数据。考虑一下您想如何跟踪这些数据以及如何引用它。

This sample data is a guide. Enter the information in a way that is meaningful to you.

此样本数据仅供参考。以对您有意义的方式输入信息。

将列表格式化为表格 (Format the List as a Table)

Formatting the range as a table will make it easier to perform calculations and control the formatting.

将范围格式化为表格将使执行计算和控制格式变得更加容易。

Click anywhere within your list of data and then select Insert > Table.

单击数据列表中的任意位置，然后选择“插入”>“表”。

Highlight the range of data in your list that you want to use. Ensure that the range is correct in the “Create Table” window and that the “My Table Has Headers” box is checked. Click the “OK” button to create your table.

在列表中突出显示要使用的数据范围。确保在“创建表”窗口中该范围正确，并且已选中“我的表具有标题”框。单击“确定”按钮创建表格。

The list is now formatted as a table. The default blue formatting style will also be applied.

现在，该列表已格式化为表格。默认的蓝色格式样式也将应用。

When more rows are added to the list, the table will automatically expand and apply formatting to the new rows.

当更多行添加到列表时，该表将自动展开并将格式应用于新行。

If you would like to change the table formatting style, select your table, click the “Table Design” button, and then the “More” button on the corner of the table styles gallery.

如果您想更改表格格式样式，请选择表格，单击“表格设计”按钮，然后单击表格样式库一角的“更多”按钮。

This will expand the gallery with a list of styles to choose from.

这将扩大画廊的风格列表供您选择。

You can also create your own style or clear the current style by clicking the “Clear” button.

您也可以通过单击“清除”按钮来创建自己的样式或清除当前样式。

命名表格 (Name the Table)

We will give the table a name to make it easier to refer to in formulas and other Excel features.

我们将为表格命名，以便在公式和其他Excel功能中更易于引用。

To do this, click in the table and then select the “Table Design” button. From there, enter a meaningful name such as “Accounts2020” into the Table Name box.

为此，请在表格中单击，然后选择“表格设计”按钮。在此处，在“表名”框中输入一个有意义的名称，例如“ Accounts2020”。

将收入和支出总计 (Add Totals for the Income and Expenses)

Having your data formatted as a table makes it simple to add total rows for your income and expenses.

将数据格式化为表格后，可以很容易地为您的收入和支出添加总行。

Click in the table, select “Table Design”, and then check the “Total Row” box.

单击表格，选择“表格设计”，然后选中“总行”框。

A total row is added to the bottom of the table. By default, it will perform a calculation on the last column.

总行将添加到表的底部。默认情况下，它将在最后一列执行计算。

In my table, the last column is the expense column, so those values are totaled.

在我的表格中，最后一列是费用列，因此这些值是合计的。

Click the cell that you want to use to calculate your total in the income column, select the list arrow, and then choose the Sum calculation.

在收入列中单击要用于计算总数的单元格，选择列表箭头，然后选择“总和”计算。

There are now totals for the income and the expenses.

现在有收入和支出总计。

When you have a new income or expense to add, click and drag the blue resize handle in the bottom-right corner of the table.

当您要添加新的收入或费用时，请单击并拖动表格右下角的蓝色调整大小手柄。

Drag it down the number of rows you want to add.

将其向下拖动到要添加的行数上。

Enter the new data in the blank rows above the total row. The totals will automatically update.

在总计行上方的空白行中输入新数据。总数将自动更新。

按月汇总收入和支出 (Summarize the Income and Expenses by Month)

It is important to keep totals of how much money is coming into your account and how much you are spending. However, it is more useful to see these totals grouped by month and to see how much you spend in different expense categories or on different types of expenses.

重要的是，要保持总计有多少钱流入您的帐户以及您有多少支出。但是，查看这些总计按月分组并查看您在不同支出类别或不同支出类型上花费的金额会更有用。

To find these answers, you can create a PivotTable.

若要找到这些答案，您可以创建数据透视表。

Click in the table, select the “Table Design” tab, and then choose “Summarise With PivotTable”.

在表格中单击，选择“表格设计”选项卡，然后选择“汇总数据透视表”。

The Create PivotTable window will show the table as the data to use and will place the PivotTable on a new worksheet. Click the “OK” button.

“创建数据透视表”窗口将表显示为要使用的数据，并将数据透视表放置在新的工作表上。点击“确定”按钮。

The PivotTable appears on the left, and a Field List appears on the right.

数据透视表显示在左侧，字段列表显示在右侧。

This is a quick demo to summarise your expense and income easily with a PivotTable. If you are new to PivotTables, check out this in-depth article.

这是一个快速演示，可使用数据透视表轻松汇总您的费用和收入。如果您不熟悉PivotTables，请阅读这篇深入的文章。

To view a breakdown of your expense and income by month, drag the “Date” column into the “Rows” area and the “In” and “Out” columns into the “Values” area.

要查看按月划分的支出和收入明细，请将“日期”列拖到“行”区域，然后将“输入”和“输出”列拖到“值”区域。

Be aware that your columns may be named differently.

请注意，您的列名称可能有所不同。

The “Date” field is automatically grouped into months. The “In” and “Out” fields are summed.

“日期”字段会自动分为几个月。汇总“输入”和“输出”字段。

In a second PivotTable, you can view a summary of your expenses by category.

在第二个数据透视表中，您可以按类别查看费用摘要。

Click and drag the “Category” field into “Rows” and the “Out” field into “Values”.

单击并将“类别”字段拖到“行”中，将“输出”字段拖到“值”中。

The following PivotTable is created summarising expenses by category.

将创建以下数据透视表，按类别汇总支出。

更新收入和支出数据透视表 (Update the Income and Expenses PivotTables)

When new rows are added to the income and expenses table, select the “Data” tab, click the “Refresh All” arrow, and then choose “Refresh All” to update both PivotTables.

将新行添加到收入和支出表后，选择“数据”选项卡，单击“全部刷新”箭头，然后选择“全部刷新”以更新两个数据透视表。

翻译自: https://www.howtogeek.com/462166/how-to-create-expense-and-income-spreadsheets/