j pocket_Excel中的Pocket价格瀑布图

j pocket

I'm working on some pricing reports for a client, and one of the requests was for a Pocket Price Waterfall chart. I hadn't made one of those before, and fortunately the client sent me a sketch of the chart that they wanted. It looked something like this.

我正在为客户制作一些价格报告，其中一项要求是提供袖珍价格瀑布图。 我以前没有做过其中的一个，幸运的是，客户给了我一张他们想要的图表的草图。 它看起来像这样。

The good news was that it looked similar to other waterfall charts that I've made. However, instead of two highlighted columns, to show start and finish, it had several highlighted columns.

好消息是它看起来与我制作的其他瀑布图相似。 但是，为了显示开始和结束，它没有两个突出显示的列，而是具有几个突出显示的列。

袖珍价 (The Pocket Price)

The term "pocket price" was new to me, and some Googling led me to an article published by McKinsey & Company in 2003 – The Power of Pricing. They coined the term "Pocket Price" to describe the amount that you actually get from a customer, after the hidden costs and off-invoice discounts that might exist.

“零用钱”这个词对我来说是个新词，有些谷歌搜索使我想到了麦肯锡公司在2003年发表的一篇文章–定价的力量。 他们创造了术语“价格”来描述您在可能存在的隐藏成本和发票折扣之后实际从客户那里获得的金额。

The article shows a waterfall chart, similar to the one that my client wanted. Maybe that's where they got the idea!

这篇文章显示了一个瀑布图，类似于我的客户想要的。 也许这就是他们的主意！

添加额外的列 (Add the Extra Columns)

To experiment with adding more highlighted columns, I started with the cash flow waterfall chart from a previous blog post, and added rows for the Quarterly totals. In the previous version, there was only one number in the End column, and I needed a number in each Quarter row, where I wanted a highlighted column.

为了尝试添加更多突出显示的列，我从上一篇博客文章的现金流量瀑布图开始，并为“季度”总计添加了行。 在以前的版本中，“结束”列中只有一个数字，并且每个“四分之一”行都需要一个数字，我希望在该行中突出显示一列。

I created a new formula in the End column, cells C4:C14, to check the Net Cash Flow column (G). If that cell is empty, show the sum of the previous amounts.

我在“结束”列中的单元格C4：C14中创建了一个新公式，以检查“净现金流量”列(G)。 如果该单元格为空，请显示先前金额的总和。

=IF(LEN(G4)=0,SUM(B3,E3:F3)-D4,"")

= IF(LEN(G4)= 0，SUM(B3，E3：F3)-D4，“”)

The Base column is the reverse, showing the sum if the cell in column G is NOT empty.

“基础”列是相反的，如果G列中的单元格不为空，则显示总和。

=IF(LEN(G4)=0,"",SUM(B3:C3,E3:F3)-D4)

= IF(LEN(G4)= 0，“”，SUM(B3：C3，E3：F3)-D4)

修改后的瀑布图 (The Revised Waterfall Chart)

Here is the revised waterfall chart, with a highlighted column for each quarterly total, ending in September.

这是修改后的瀑布图，每个季度总计有一个突出显示的列，该列在9月结束。

Then, I used the same formulas with some faked Pocket Price data, to create the Pocket Price waterfall chart. There aren't any green columns, because all of the values are negative amounts, bringing down the price.

然后，我将相同的公式与一些虚假的Pocket Price数据一起使用，以创建Pocket Price瀑布图。 没有任何绿色列，因为所有值均为负值，从而降低了价格。

And now I'm ready to create the real Pocket Price waterfall chart, whenever we get to that stage in the project.

现在，无论何时到达项目的那个阶段，我都准备创建真实的Pocket Price瀑布图。

下载样本文件 (Download the Sample File)

To see the waterfall charts and the worksheet formulas, you can download the Pocket Price Waterfall Chart sample file.

要查看瀑布图和工作表公式，可以下载Pocket Price Waterfall Chart示例文件 。

The file is in Excel 2007 format, and zipped. There are no macros in the file.

该文件为Excel 2007格式，并已压缩。 该文件中没有宏。

瀑布图实用程序和视频 (Waterfall Chart Utility and Video)

If you need to make waterfall charts, take a look at Jon Peltier's time-saving Excel Chart Utility. While working on this chart, I discovered that Jon's add-in has this multi-highlighted column feature built in.

如果需要制作瀑布图，请查看Jon Peltier的省时Excel图表实用程序 。 在处理此图表时，我发现Jon的加载项内置了此多突出显示的列功能。

It also handles data that crosses the X Axis, with totals in the negative amounts.

它还处理与X轴交叉的数据，且总计为负数。

To see how I used Jon's add-in to quickly create a sample Pocket Price waterfall chart, you can watch this Excel video tutorial.

若要查看如何使用Jon的外接程序快速创建示例Pocket Price瀑布图，请观看此Excel视频教程。

演示地址

翻译自: https://contexturesblog.com/archives/2011/07/06/pocket-price-waterfall-chart-in-excel/

j pocket