消费物价指数^{(Consumer Price Index)}是衡量通货膨胀的最重要指标之一。简而言之，您可以将其称为一篮子产品成本在两个时间范围（通常是几年）内的百分比增长。如果您想在 Excel 中计算 CPI^{(calculate CPI in Excel)}并创建图表，请通读本文。

注意^(Note)：在创建本文时，我们假设购买的产品数量是恒定的。

如何在 Excel 中计算 CPI

要在Excel中计算两年之间的消费者物价指数^{(Consumer Price Index)}，请计算这两年购买一揽子产品的所有金额的总和。然后使用以下公式计算CPI 比率^{(CPI ratio)}：

=[(Cumulative price of basket of commodities in later year) - (Cumulative price of basket of commodities in earlier year)] / (Cumulative price of basket of commodities in earlier year)

要查找CPI 百分比^{(CPI percentage)}，只需选择具有CPI 比率^{(CPI ratio)}的单元格并点击百分比^(percentage)符号。

同样的事情可以在几年内完成。

例如。我们创建了一张数据表，其中包含 2011 年至 2016 年一篮子产品的价格。产品价值的总和在各列的第 9 行中求和。2012 年以后每年的CPI在 K 列中提到，从单元格 K3 开始。^(CPI)

由于 2011 年的汇总价格在单元格 C9 中，而 2012 年的汇总价格在单元格 D9 中，因此 2012 年的CPI比率公式将变为：

=(D9-C9)/C9

让我们在单元格 K3 中输入这个公式。同样，2013 年CPI^(CPI)比率的公式将变为：

=(E9-D9)/D9

就像这样，我们将创建列表，直到我们得到 2016 年的CPI比率。但是，这个比率是十进制格式。要将值转换为百分比，请选择所有值，然后点击百分比^(Percentage)符号。

在Excel中为^(Excel)CPI值创建图表

CPI值的最佳图表类型是条形图。要创建它，请选择CPI百分比。然后转到Insert > Bar并选择Bar Graph的类型。

相应地更改条形图的大小和位置。

上面创建的图表本质上是静态的。如果您希望创建动态图^{(create a dynamic graph)}，则必须使用表格来存储数据。

希望能帮助到你！

Calculate Consumer Price Index or CPI in Excel and create its graph

Consumer Price Index is one of the most important indices for measuring inflation. In short, you could call it the percentage increase in the cost of a basket of products over two time-ranges (usually years). If you wish to calculate CPI in Excel and create its graph, please read through this article.

Note: While creating this article, we are assuming the purchased quantity of products to be constant.

How to calculate CPI in Excel

To calculate the Consumer Price Index between two years in Excel, take a sum of all the amounts spent on the basket of products over those two years. Then use the following formula to find the CPI ratio:

=[(Cumulative price of basket of commodities in later year) - (Cumulative price of basket of commodities in earlier year)] / (Cumulative price of basket of commodities in earlier year)

To find the CPI percentage, simply select the cell with the CPI ratio and hit the percentage symbol.

The same could be done over a range of years.

Eg. We have created a sheet of data with prices of a basket of products from 2011 to 2016. The total of the values of the products is summed in row 9 of the respective columns. The CPI for each year 2012 onward is mentioned in column K, starting from cell K3.

Since the summed up price for 2011 is in cell C9 and the summed up price for 2012 is in cell D9, the formula for the CPI ratio for 2012 would become:

=(D9-C9)/C9

Let us enter this formula in cell K3. Similarly, the formula for the CPI ratio for 2013 would become:

=(E9-D9)/D9

Just like this, we will create the list until we get the CPI ratio till 2016. However, this ratio is in decimal format. To convert the values to a percentage, select all of them, and hit the Percentage symbol.

Create a graph for CPI values in Excel

The best graph type for CPI values is the bar graph. To create it, select the CPI percentages. Then go to Insert > Bar and select the type of the Bar Graph.

Change the size and location of the bar graph accordingly.

The graph created above is static in nature. If you wish to create a dynamic graph, you would have to use tables for the data.

Hope it helps!

Related posts

• 如何在Excel中使用Percentile.Exc function

• 如何使用NETWORKDAYS function在Excel

• 如何计算在Excel的Yes or No entries数

• Excel，Word or PowerPoint上次无法启动

• 如何在Excel中使用Automatic Data Type feature

• Microsoft Excel在Windows 10运行时导致High CPU usage

• 如何在Excel使用EDATE and EOMONTH Functions

• 如何将一列拆分为Excel中的多列

• 如何在Excel中使用INT and LCM Functions

• 如何在Microsoft Excel中使用MID and MIDB function

• 如何在一页上打印Excel or Google Sheets中所选单元格

• 如何在Excel formula中锁定单元格以保护它们

• Excel中Mean的Calculate Standard Deviation and Standard Error

• 如何在Excel中使用Rept Function

• Microsoft Excel Tutorial，用于初学者

• 如何Merge and Unmerge cells在Excel

• 如何在Windows 11/10中创建Radar Chart

• 如何从Excel document删除Read？

• 如何在Excel中使用DISC function

• 如何在Microsoft Excel中使用HLOOKUP function