简七理财工具包

理财软件推荐：简单易用的理财工具 #生活乐趣# #生活分享# #生活方式分享# #理财生活指南#

个人财务 (PERSONAL FINANCE)

According to CareerBuilder, 78% of Americans live paycheck to paycheck.

根据CareerBuilder的研究 ，有78％的美国人过着薪水支付的生活。

This number may be exacerbated by the recent recession, which has resulted in millions of people not receiving their paycheck.

最近的经济衰退可能加剧了这一数字，导致数百万人没有领取薪水。

What can you do about it? How about you track your expenses and start a budget?

你能为这个做什么？ 您如何跟踪费用并开始预算？

Tracking expenses is essential, it will help you gain awareness about your spending habits, and therefore, it will support you to optimize your spending. Expense tracking will answer the oldest question in finance — Where did the money go?

跟踪支出是必不可少的，它可以帮助您了解自己的支出习惯，因此，它可以帮助您优化支出。 费用跟踪将回答金融领域最古老的问题-钱去了哪里？

Budgeting allows you to allocate each dollar of your hard-earned money to a specific role. Budgeting empowers you to start an emergency fund, pay back debt, save for that downpayment, and many more.

通过预算，您可以将辛苦赚来的每一美元都分配给特定角色。 通过预算，您可以启动应急基金，偿还债务，节省首付款等。

There are many apps out there, which help in tracking expenses and budgeting. But, these apps cost money, are not tailored specifically for you, and can’t be updated easily if something changed in your financial situation.

那里有许多应用程序，可帮助跟踪费用和预算。 但是，这些应用程序需要花钱，不是专门为您量身定制的，并且如果您的财务状况有所变化，也无法轻松更新。

Building your own budgeting and tracking tool is simple. It can be stored in a cloud service (google docs, dropbox, or iCloud) and accessed remotely from anywhere.

构建自己的预算和跟踪工具非常简单。 它可以存储在云服务(Google文档，Dropbox或iCloud)中，并且可以从任何地方远程访问。

In the following, we will, step-by-step, build our very own budgeting and tracking tool in Excel. I chose Excel because:

接下来，我们将逐步在Excel中构建我们自己的预算和跟踪工具。 我选择Excel是因为：

The formulas in excel are easy and do not require excessive knowledge in programming and coding. excel中的公式很简单，不需要编程和编码方面的过多知识。 The formulas can easily be adapted for other software such as Numbers or Google Sheets. 这些公式可以轻松地应用于其他软件，例如Numbers或Google Sheets。 You can copy/paste cells to copy the formula for other cells. 您可以复制/粘贴单元格以复制其他单元格的公式。

NOTE THIS: The formulas I’ll be presenting in this post are for the English version of Excel. If you use Excel in a different language, please translate the equations to that language. Also, in my Excel version, I use ; as a separator in the formulas. Check which separator is used in your Excel version.

注意：我将在本文中介绍的公式适用于英文版的Excel。 如果您使用其他语言的Excel，请将方程式翻译成该语言。 另外，在我的Excel版本中，我使用; 作为公式中的分隔符。 检查您的Excel版本中使用了哪个分隔符。

Before we start with building this tool, note down your recurring expenses — monthly, quarterly, semiannually, and annually. Also, note down your fixed monthly income — salary, income from real estate, etc.

在开始构建此工具之前，请记下您的经常性费用-每月，每季度，每半年和每年。 另外，记下您的固定月收入-工资，房地产收入等。

To build the budgeting and expense tracking tool, we need to develop three sheets in excel:

要构建预算和支出跟踪工具，我们需要在excel中开发三张纸：

The lists sheet — The name of this sheet has to be Lists.

列表工作表-此工作表的名称必须为Lists 。

the months’ sheets — January, February, …, December. 月份的工作表-一月，二月，…，十二月。 The overview sheet 概述表

列表表 (The Lists Sheet)

In this sheet, we will write down three lists required to program this tool. List 1: the categories for the monthly variable expenses, List 2: The months, List 3: the recurring charges. The lists are necessary for the further development of the tool.

在此工作表中，我们将写下编写此工具所需的三个列表。 清单1：每月可变费用的类别，清单2：月份，清单3：经常性费用。 这些列表对于该工具的进一步开发是必需的。

Screenshot by the author 作者的屏幕截图

In cell J1 type the following formula =I1&"!”Then pull down from the right bottom corner of the cell. The result allows us to better reference in the upcoming steps.

在单元格J1键入以下公式=I1&"!” 然后从单元格的右下角下拉，结果使我们可以在后续步骤中更好地进行参考。

Screenshot by the author 作者的屏幕截图

The “Categories” list will be used as a dropdown menu later on. For this example, I’ve added four different savings categories (house, car, vacation, etc.). The “Cash” category tracks the withdrawn cash from the bank account. The list can be modified and extended as suitable.

稍后，“类别”列表将用作下拉菜单。 在此示例中，我添加了四个不同的储蓄类别(房屋，汽车，度假等)。 “现金”类别跟踪从银行帐户提取的现金。 该列表可以适当修改和扩展。

The “Recurring costs” list includes charges, which are not monthly but can occur in quarterly, semiannually, or annually fashion. Write down the amounts and the months in which these charges arise, as shown in the screenshots.

“经常性费用”列表包括费用，这些费用不是每月一次，而是可以每季度，每半年或每年一次发生。 记下这些费用的金额和产生的月份，如屏幕截图所示。

月表 (The Months’ Sheets)

This sheet will be duplicated eleven times after we are done with the January sheet. Therefore, it is essential to make sure that the first sheet is current and includes everything. I filled the sheet as fits this example. The colored cells include equations and formulas.

在完成一月份工作表后，此工作表将被重复十一次。 因此，必须确保第一张纸是最新的并且包含所有内容。 我按照这个例子填写了工作表。 有色单元格包括方程式和公式。

Screenshot by the author 作者的屏幕截图

The formula in cell B3 is the simplest. Its goal is to calculate the available funds for the month. It subtracts all the costs and savings from the monthly salary.

B3单元格中的公式最简单。 其目标是计算当月的可用资金。 它从月薪中减去所有成本和储蓄。

=B2-SUM(B6:B12;B15:B16;C20:C26;B29:B32)

Cell B15 is a little bit tricky. The formula here outputs the recurring expenses based on the month. Annual fees 1 equals 100,00€ only in January. The formula checks the values of the month in cell B1 and compares the value with the months documented under the lists sheet.

单元格B15有点棘手。 此处的公式输出基于月份的经常性费用。 年费1仅在1月份等于100,00€。 该公式检查单元格B1月份的值，并将该值与列表表下记录的月份进行比较。

=IF(OR($B$1=Lists!C18;$B$1=Lists!D18;$B$1=Lists!E18;$B$1=Lists!F18);Lists!B18;0)

This formula includes two functions: IF-function and OR-function. The OR function is true if only one of its variables is true. The If function gives a specific value if the function is true and another value if the function is false.

该公式包括两个函数：IF函数和OR函数。 OR功能是真实的 ，如果只是它的变量之一是真实的 。 IF函数给出了具体的值，如果功能是真实的 ，另一个值如果函数是假的 。

Cell B1 is referenced in this formula as $B$1 to prevent Excel from changing this reference if we copy/paste the cell B15 . Copy and Paste cell B15 to cell B16 and the equation shall look like

在此公式中，单元格B1被引用为$B$1以防止Excel在复制/粘贴单元格B15更改此引用。 将单元格B15复制并粘贴到单元格B16 ，等式应如下所示

=IF(OR($B$1=Lists!C19;$B$1=Lists!D19;$B$1=Lists!E19;$B$1=Lists!F19);Lists!B19;0)

Notice how Excel changed all the other values to +1 but not the cell B1 .

请注意，Excel如何将所有其他值更改为+1 ，但未将单元格B1更改为+1 。

Cell C20 gets an easy formula, which can be copied to all the cells from C20 to C26 . The used function is a SUMIF function, which only sums the values if a specific condition is true. The formula in cell C20 will sum all the variable expenses in the table on the right-hand side.

单元格C20得到一个简单的公式，可以将其复制到从C20到C26所有单元格中。 使用的函数是SUMIF函数，仅当特定条件为true时 ，才对值求和。 单元格C20的公式将汇总右侧表中的所有可变费用。

=SUMIF($E$6:$E$30;A20;$H$6:$H$30)

That is it, no more formulas or equations needed in the months’ sheets. Now, we only have to add a dropdown menu under the category column. to do this select cell E6 and select the Data tab, then Data validation, and then select the list as shown in the next figure.

就是这样，几个月的工作表中不再需要任何公式或方程式。 现在，我们只需要在类别列下添加一个下拉菜单。 为此，选择单元格E6并选择“数据”选项卡，然后选择“数据验证”，然后选择列表，如下图所示。

Screenshot by the author 作者的屏幕截图

That’s it! Easy isn’t it? There is one function that we can add, it is not a must, and it can function unproperly if saved on the cloud. The function is in cell D1 and cell B1 . This function saves one step of work. Instead of typing the month's number in cell B1 . With the help of this function, the value is written automatically based on the sheet's name.

而已！ 容易吗？ 我们可以添加一个功能，它不是必须的，如果保存在云中，它可能会不适当地运行。 该功能在单元格D1和单元格B1 。 此功能节省了一步的工作。 而不是在单元格B1中输入月份的数字。 借助此功能，将根据图纸名称自动写入值。

In cell D1 type in

在单元格D1输入

=MID(CELL(“filename”;A1);FIND(“]”;CELL(“filename”;A1))+1;256)

and in cell B1 type

并在单元格B1

=MONTH(DATEVALUE(D1&” 1"))

Now you only need to rename the sheet, and everything else is automated. Now every amount, which is written in the table on the right-hand side, is immediately considered in the budget and expense tracking tool.

现在，您只需要重命名工作表，其他所有操作都会自动执行。 现在，预算表和支出跟踪工具中会立即考虑写在右侧表格中的每个金额。

Screenshot by the author 作者的屏幕截图

These are the basics functions; you can add diagrams, conditional formatting, and even two different accounts if necessary. If you receive payments other than a fixed salary, you can add a category “Income.” Add the values in the table as negative values to consider them as income. Reformat the table and add as many functions as you wish, this is your budget and money tracking tool.

这些是基本功能； 您可以添加图表，条件格式，必要时甚至可以添加两个不同的帐户。 如果您收到固定工资以外的其他款项，则可以添加“收入”类别。 将表中的值添加为负值，以将其视为收入。 重新设置表格格式并添加所需的任意功能，这是您的预算和资金跟踪工具。

After you are done with January, just copy the sheet and rename it depending on the month.

一月完成后，只需复制工作表并根据月份将其重命名即可。

概述表 (The overview sheet)

The overview sheet is not necessary. It functions only as a summary of all expenses and incomes in a given year. This is helpful if you are planning to use this tool for many years. I’ve been using this tool since 2016 now, with one Excel file per year saved to my Dropbox account.

概述表不是必需的。 它仅充当给定年份中所有费用和收入的汇总。 如果您打算多年使用此工具，这将很有帮助。 自2016年以来，我一直在使用此工具，每年将一个Excel文件保存到我的Dropbox帐户中。

A simple overview is as shown in the next figure. The green highlighted cells are essential. They contain the cell number from the months’ lists, which includes the required information.

下图显示了一个简单的概述。 绿色突出显示的单元格至关重要。 它们包含月份列表中的单元号，其中包括必需的信息。

Screenshot by the author 作者的屏幕截图

The required formulas for this sheet only reference a specific cell in the months’ sheets. The fixed charges and the recurring expenses are a sum.

此工作表所需的公式仅引用月份工作表中的特定单元格。 固定费用和经常性费用为总和。

The formula in Excel, which will get the salary information from all other sheets is

Excel中从所有其他工作表获取薪水信息的公式是

=@INDIRECT(VLOOKUP(D$3;Lists!$I$1:$J$12;2;FALSE)&$A4;TRUE)

Now copy/paste this cell to all other cells except for D5 to D8 . In D5 type in the following

现在，将此单元格复制/粘贴到除D5至D8之外的所有其他单元格中。 在D5输入以下内容

=SUM(INDIRECT(VLOOKUP(D$3;Lists!$I$1:$J$12;2;FALSE)&$A5;TRUE):INDIRECT(VLOOKUP(D$3;Lists!$I$1:$J$12;2;FALSE)&$B5;TRUE))

Cell D5 can be copied to cell D6 , which now shall look like this

单元格D5可以复制到单元格D6 ，现在看起来像这样

=SUM(INDIRECT(VLOOKUP(D$3;Lists!$I$1:$J$12;2;FALSE)&$A6;TRUE):INDIRECT(VLOOKUP(D$3;Lists!$I$1:$J$12;2;FALSE)&$B6;TRUE))

Cells D7 and D8 are just a Sum-function for the variable expenses and savings.

D7和D8单元只是用于可变费用和节省的求和函数。

Select all the cells under January, including the month’s name, and drag it to the right for 11 further columns — Voilà! We are done.

选择一月下的所有单元格(包括月份的名称)，然后将其拖到右侧以再添加11列-Voilà ！ 我们完了。

Screenshot by the author 作者的屏幕截图

Expense tracking and budgeting are the first steps in financial literacy. Each dollar has a task and cannot be relocated to another category.

费用跟踪和预算编制是财务知识的第一步。 每块钱都有一个任务，不能重定位到另一个类别。

Many apps and programs offer budgeting and Expense tracking services, however, these apps cost money (some cost even a monthly subscription fees) and are not tailored to your specific needs and situation.

许多应用程序和程序都提供预算和费用跟踪服务，但是，这些应用程序要花钱(有些甚至需要每月订阅费)，并且并非针对您的特定需求和情况而量身定制。

Building your specific tool is simple and can be tailored to fit your requirements. You can update it later on with ease, and you don’t have to wait for a developer to release an update.

构建您的特定工具非常简单，可以根据您的要求进行定制。 您以后可以轻松地对其进行更新，而不必等待开发人员发布更新。

With my current Excel tool, I track my stocks, dividends, irregular income, home improvement costs, and all my investment portfolios across multiple platforms. No app can offer me this.

使用当前的Excel工具，我可以跨多个平台跟踪我的股票，股息，不定期收入，房屋装修成本以及所有投资组合。 没有任何应用程式可以提供这个功能。

Walid Al Otaibi -WAO- works at an engineering company in Germany as a Project Manager. He manages mainly sustainable energy projects.

Walid Al Otaibi -WAO-在德国的一家工程公司担任项目经理。 他主要管理可持续能源项目。

He comes from a multicultural background and is located in Germany since 2003. He is writing about Arab Culture, Multiculturalism, Finance, and Trending topics.

他来自多元文化背景，自2003年以来一直在德国居住。他撰写有关阿拉伯文化，多元文化，金融和趋势主题的文章。

网址：简七理财工具包 https://www.yuejiaxmz.com/news/view/316121

相关内容

理财工具(关于理财工具简述)
理财工具
13个超有用的理财投资工具包，只送不卖！
最佳个人理财工具
简七理财学习一（理财小常识）从毕业后开始有了理财的意识，这也是我第一个接触的理财课程，感觉讲得非常好，从中学会了许多理财
常见投资理财工具
5个简单有效的个人理财小工具，助你轻松管理财务
适合个人理财的投资工具介绍
盘点适合个人理财的投资工具
2018职场小白怎么理财 盘点7类常见的理财工具

随便看看