sql中计算总计金额怎么查_使用运行总计在Excel中比较年份-CSDN博客

sql中计算总计金额怎么查

If you're analyzing sales data from year to year, you can make a line chart that shows each month's sales. That lets you see if there were any months with big differences, and shows how sales went up and down over the year. Another option is to use a Running Total to compare years in Excel. It's quick and easy to set up with a pivot table and pivot chart.

如果您要逐年分析销售数据，则可以制作折线图以显示每个月的销售额。这样一来，您便可以查看是否存在相差较大的月份，并显示出一年中销售量的增长和下降情况。另一种选择是使用运行总计来比较Excel中的年份。使用数据透视表和数据透视表可以快速简便地进行设置。

显示每月总计 (Show Monthly Totals)

The quickest way to summarize the annual data is with a pivot table. In the example shown below, the sales date has been grouped by month and year. The year is in the Column area, and the Month (Date) is in the row area. The Sum of quantity is shown in the Values area.

汇总年度数据的最快方法是使用数据透视表。在下面的示例中，销售日期已按月份和年份分组。年在“列”区域中，而“月(日期)”在行区域中。数量总和显示在“值”区域中。

创建月度图表 (Create a Monthly Chart)

A line chart from this summary shows the total for each month, year over year. It shows that 2016 started out well, hit a slump in February, and picked up a bit in March.

此摘要中的折线图显示了每年逐年总计。这表明2016年开局良好，2月陷入低迷，3月有所回升。

It doesn't show the total per year though, and there isn't a way to include the pivot table Grand Total in a pivot chart.

它没有显示每年的总数，也没有办法在数据透视图中包含数据透视表“总计”。

显示运行总计 (Show a Running Total)

Another way to show the data is to create a Running Total. Instead of just seeing each month compared to that month in the previous year, see the overall total at each point in the year.

显示数据的另一种方法是创建“运行总计”。不仅要查看与上一年的每个月相比的每个月，还要查看一年中每个时间点的总数。

Make a copy of the first pivot table, and change its values to show a Running Total:

复制第一个数据透视表，并更改其值以显示“运行总额”：

Right-click on one of the amounts in the Value,s area
右键单击“ Value，s”区域中的金额之一
Click Show Values As
单击将值显示为
Click Running Total In
单击运行总计
Select Date as the Base Field.
选择日期作为基础字段。

There are instructions and a short step-by-step video on my website.

我的网站上有说明和简短的分步视频。

The new pivot table shows the Running Total over the months for each year.

新的数据透视表显示了每年几个月的运行总额。

You could remove the Grand Total, because it is empty when a Running Total is shown.

您可以删除总计，因为在显示“运行总计”时该总计为空。

制作枢轴图表以运行总计 (Make a Pivot Chart for Running Total)

Instead of creating and formatting a new pivot chart, re-use the old one, by making a copy and changing its source data. There's a video of these steps on my Contextures site.

无需创建新的数据透视图并设置其格式，而是通过复制并更改其源数据来重用旧的数据透视图。我的Contextures网站上有这些步骤的视频。

Copy the old pivot chart (for the monthly pivot table).
复制旧的数据透视图(用于每月数据透视表)。
Then, paste it into another workbook temporarily. Cut it from that workbook, and paste it onto the sheet with the new pivot chart
然后，将其临时粘贴到另一个工作簿中。从该工作簿中剪切它，并将其粘贴到具有新数据透视图的工作表上
Right-click the chart, and click Select Data (NOTE: if you right-click in the Plot area, you'll see a short pop-up menu)
右键单击图表，然后单击“选择数据”(注意：如果在“绘图”区域中单击鼠标右键，将会看到一个简短的弹出菜单)
Click in the Chart Data range box, and click on any cell in the Running Total pivot table
单击“图表数据”范围框，然后单击“运行总计”数据透视表中的任何单元格
Click OK, to close the dialog box.
单击确定，关闭对话框。

运行总计以比较年份 (Running Total to Compare Years)

In the new chart, you can see the Running Total, to compare years. It shows that the 2016 total is less than the previous year, even though it started out a hit higher in January.

在新图表中，您可以查看“运行总计”以比较年份。它表明，尽管2016年1月份开始大幅增长，但其总量却低于上一年。

显示两个数据透视图 (Show Both Pivot Charts)

Instead of keeping the pivot charts on separate sheets, you could put both charts on a dashboard, to show different views of the same data. Two pivot charts are better than one! Well, almost always.

您可以将两个图表放在仪表板上，以显示相同数据的不同视图，而不必将数据透视图保存在单独的工作表中。两个枢轴图表胜过一个图表！好吧，几乎总是如此。