excel组合汇总_Excel汇总20151116

最新推荐文章于 2024-09-15 22:12:08 发布

culiao2169

最新推荐文章于 2024-09-15 22:12:08 发布

阅读量194

点赞数

文章标签： excel java python 数据分析可视化

原文链接：https://contexturesblog.com/archives/2015/11/16/excel-roundup-20151116/

版权

excel组合汇总

In this week's roundup, create a rolling total, chart tricks, unmerge cells, clean data, and much more.

在本周的综述中，创建滚动总计，图表技巧，取消合并单元格，清除数据等等。

Excel Roundup http://blog.contextures.com/

1.累计 (1. Rolling Total)

Instead of an overall running total for a list, you could create a rolling total – use a formula to show the sum of the previous 12 months in each row. Download the sample file to see how it works.

您可以创建滚动总和，而不必使用列表的总体总和 -使用公式显示每行中前12个月的总和。下载示例文件以查看其工作方式。

2.有趣的图表 (2. Fun With Charts)

To make things easier for your, or the people who use your reports, Mike Alexander shows how to set up a check box to turn the chart labels on or off. The technique doesn't use macros, and you can download his sample file to see how it works.

为了使您或使用您的报告的人员更容易进行，Mike Alexander展示了如何设置一个复选框来打开或关闭图表标签。该技术不使用宏，您可以下载他的示例文件以查看其工作原理。

On his latest podcast, Chandoo talked about creating animated charts. To get some great ideas, listen to the podcast, and download the sample file.

在他最新的播客中，Chandoo谈到了如何制作动画图表。要获得一些好主意，请收听播客，然后下载示例文件。

3.解除单元格合并 (3. Unmerge Cells)

Merged cells can cause problems, especially if you're trying to sort or filter data. Sumit Bansal shows how you can find merged cells, and highlight them, using built-in Excel commands.

合并的单元格可能会导致问题，尤其是在您尝试对数据进行排序或过滤时。 Sumit Bansal显示了如何使用内置的Excel命令查找合并的单元格并突出显示它们。

If you just want to unmerge some cells in a range, select all the cells, then click the Merge & Centre command. Don't worry – it won't merge all the cells together – unless there's only one cell with data.

如果只想取消合并某个区域中的某些单元格，请选择所有单元格，然后单击“合并和居中”命令。不必担心–不会将所有单元合并在一起–除非只有一个单元包含数据。

4.显示成就差距 (4. Show the Achievement Gap)

In a report to her superintendent, the Science Goddess had to show the achievement gap -- the difference in achievement levels between populations of students. It took her a while to find the best way to represent the data in a chart, and you can see that the results were worth the effort.

在给她的学长的报告中，《科学女神》必须显示成就差距-学生群体之间的成就水平差异。她花了一段时间才找到在图表中表示数据的最佳方法，您可以看到结果值得付出努力。

5.数据清理 (5. Data Cleanup)

Faced with a messy list of telephone numbers, Dave Bruns used nested SUBSTITUTE functions to strip out all the spaces, hyphens, periods, parentheses, and commas. Then, he formatted the results with the Telephone Number format, to create a clean and easy-to-read phone list.

面对一堆乱七八糟的电话号码，Dave Bruns使用嵌套的SUBSTITUTE函数去除所有空格，连字符，句点，括号和逗号。然后，他使用“电话号码”格式对结果进行了格式化，以创建干净且易于阅读的电话列表。

There are more data cleanup techniques, along with other tips for data analysts, on the Investintech blog. One tip shows how to bring together data from several CSV files, using either Power Query, or Ron de Bruin's free RDB Merge add-in.

Investintech博客上提供了更多的数据清除技术以及针对数据分析师的其他技巧。一个技巧说明了如何使用Power Query或Ron de Bruin的免费RDB Merge加载项将多个CSV文件中的数据汇总在一起。

6.在结构化表中查找 (6. Lookup in a Structured Table)

On this Daily Dose of Excel blog, Dick Kusleika showed how to create a lookup formula that works with structured table references. He even made a video! Originally, he used VLOOKUP, but he has now switched to INDEX/MATCH instead, thanks to some tough love from the commenters. The Internet can be a rough place!

在Excel的Daily Dose博客上，Dick Kusleika展示了如何创建适用于结构化表引用的查找公式。他甚至拍了视频！最初，他使用VLOOKUP，但由于评论者的不懈努力，他现在改用了INDEX / MATCH。互联网可能是一个艰难的地方！

7. Excel幽默 (7. Excel Humour)

Finally, for a bit of spreadsheet humour, you can see what people are saying about Excel, in my weekly collection of tweets. Here's one of my favourite tweets from this week's collection.

最后，对于电子表格的一些幽默，您可以在我的每周推文集中看到人们对Excel 的评价。这是本周收藏中我最喜欢的推文之一。