曾经有过Excel中的数据工作表,并很快想查看数据的趋势吗?也许您在过去 5 年中有一些学生的考试成绩或贵公司的收入,而不是在Excel中创建一个图表,这需要时间并最终吃掉整个工作表,一个单元格中的一些小型迷你图表会会更好。
Excel 2010、2013(Excel 2010)和 2016 有一个很酷的功能,称为迷你图,基本上可以让您在单个Excel 单元格(Excel cell)中创建迷你图表。您可以将迷你图添加到任何单元格并将其保留在数据旁边。通过这种方式,您可以逐行快速地可视化数据。这只是在Excel中分析数据的另一种好方法。
在我们开始之前,让我们看一个简单的例子来说明我的意思。在下面的数据中,我在过去六个季度中有十家商店的收入。使用迷你图,我可以快速查看哪些商店增加了收入,哪些商店表现不佳。
显然,在使用迷你图查看数据时必须小心,因为根据您分析的数字,它可能会产生误导。例如,如果您查看Store 1,您会看到收入从 $56K 上升到大约 $98 并且趋势线(trend line)直线上升。
但是,如果您查看Store 8,趋势线(trend line)非常相似,但收入仅从 $38K 上升到 $44K。因此,迷你图不会让您以绝对值的形式查看数据。创建的图表仅与该行中的数据相关,理解这一点非常重要。
出于比较的目的,我继续使用相同的数据创建了一个普通的Excel 图表(Excel chart),在这里您可以清楚地看到每个商店相对于其他商店的表现。
在这个图表中,与 Store 1 相比,Store 8几乎是一条平线,Store 1 仍然是一条趋势线。因此,您可以看到如何根据您选择的显示方式以不同的方式解释相同的数据。常规图表可帮助您查看多行或数据之间的趋势,迷你图可帮助您查看一行数据中的趋势。
我应该注意,还有一种方法可以调整选项,以便迷你图也可以相互比较。我将在下面提到如何做到这一点。
创建迷你图
那么,我们如何去创建迷你图呢?在Excel 中(Excel),这真的很容易做到。首先(First),单击数据点旁边的单元格,然后单击插入,然后在(Insert)Sparklines下的Line、Column和Win/Loss之间进行选择。
(Pick)根据您希望数据的显示方式,从三个选项中进行选择。您以后可以随时更改样式,因此如果您不确定哪一种最适合您的数据,请不要担心。Win/Loss类型仅对具有正值和负值的数据才真正有意义。应该会弹出一个窗口,要求您选择数据范围(data range)。
单击(Click)右侧的小按钮,然后选择一行数据。选择范围后,继续并再次单击该按钮。
现在单击“确定”,您的迷你图或小图表应出现在该单元格中。要将迷你图应用到所有其他行,只需抓住右下边缘(bottom right edge)并将其向下拖动,就像在其中包含公式的单元格一样。
自定义迷你图
现在我们有了迷你图,让我们自定义它们!首先,您始终可以增加单元格的大小,以使图形更大。默认情况下,它们非常小,很难正确看到。现在继续并单击带有迷你图的任何单元格,然后单击迷你图工具下的(Sparkline Tools)设计(Design)选项卡。
从左侧开始,如果您想包含更多或更少的列,您可以编辑数据。在类型(Type)下,您可以更改所需的迷你图表类型。同样(Again),Win/Loss适用于具有正数和负数的数据。在Show下,您可以向图表添加标记,例如High Point、Low Point、Negative Points、First & Last Point和Markers(每个数据点的标记)。
在Style下,您可以更改图表的样式。基本上,这只是改变行或列(line or columns)的颜色,并让您选择标记的颜色。在其右侧,您可以单独调整迷你图和标记的颜色。
迷你图的唯一另一个重要方面是轴(Axis)选项。如果单击该按钮,您将看到一些名为“垂直轴最小值选项( Vertical Axis Minimum Value Options)”和“垂直轴最大值选项”的选项。( Vertical Axis Maximum Value Options.)
如果要使迷你图相对于所有其他行而不是仅相对于其自己的行,请在两个标题下为所有迷你图选择相同。( Same for All Sparklines)现在,当您查看数据时,您会发现可以根据绝对值比较图表。我还发现,在比较所有迷你图时,以列的形式(column form)查看图表可以更轻松地查看数据。
如您现在所见,Store 1的列比 Store 8的列高得多,Store 8的列略有上升,但收入值要小得多。浅蓝色的列是低点和高点,因为我检查了这些选项。
这就是关于迷你图的所有知识。如果你想为你的老板制作一个精美的Excel 电子表格(Excel spreadsheet),这就是这样做的方法。如果您有任何问题,请随时发表评论。享受!
How to use Sparklines in Excel
Ever had a workshееt of data in Excel and quіckly wanted to see the trend in the data? Maybe you have some teѕt scores for your students or revenue from your company over the last 5 years and іnstead of creating a chart in Excel, which takes time and ends up eating up an entire worksheet, some small mini-charts in a single cell would be better.
Excel 2010, 2013 and 2016 have a cool feature called sparklines that basically let you create mini-charts inside a single Excel cell. You can add sparklines to any cell and keep it right next to your data. In this way, you can quickly visualize data on a row by row basis. It’s just another great way to analyze data in Excel.
Before we get started, let’s take a look at a quick example of what I mean. In the data below, I have revenue from ten stores over the last six quarters. Using sparklines, I can quickly see which stores are increasing revenue and which stores are performing badly.
Obviously, you have to be careful when looking at data using sparklines because it can be misleading depending on what numbers you are analyzing. For example, if you look at Store 1, you see that revenue went from $56K to about $98 and the trend line is going straight up.
However, if you look at Store 8, the trend line is very similar, but the revenue only went from $38K to $44K. So sparklines don’t let you see the data in absolute terms. The graphs that are created are just relative to the data in that row, which is very important to understand.
For comparison purposes, I went ahead and created a normal Excel chart with the same data and here you can clearly see how each store performs in relation to the others.
In this chart, Store 8 is pretty much a flat line as compared to Store 1, which is still a trending up line. So you can see how the same data can be interpreted in different ways depending on how you choose to display it. Regular charts help you see trends between many rows or data and sparklines let you see trends within one row of data.
I should note that there is also a way to adjust the options so that the sparklines can be compared to each other also. I’ll mention how to do this down below.
Create a Sparkline
So, how do we go about creating a sparkline? In Excel, it’s really easy to do. First, click in the cell next to your data points, then click on Insert and then choose between Line, Column, and Win/Loss under Sparklines.
Pick from any of the three options depending on how you want the data displayed. You can always change the style later on, so don’t worry if you’re not sure which one will work best for your data. The Win/Loss type will only really make sense for data that has positive and negative values. A window should pop up asking you to choose the data range.
Click on the little button at the right and then select one row of data. Once you have selected the range, go ahead and click on the button again.
Now click OK and your sparkline or tiny chart should appear in that one cell. To apply the sparkline to all the other rows, just grab the bottom right edge and drag it down just like you would a cell with a formula in it.
Customizing Sparklines
Now that we have our sparklines, let’s customize them! Firstly, you can always increase the size of the cells so that the graphs are bigger. By default, they are pretty tiny and can be hard to see properly. Now go ahead and click in any cell with a sparkline and then click on the Design tab under Sparkline Tools.
Starting from the left, you can edit the data if you like to include more columns or less. Under Type, you can change the type of mini chart you want. Again, the Win/Loss is meant for data with positive and negative numbers. Under Show, you can add markers to the graphs like High Point, Low Point, Negative Points, First & Last Point and Markers (marker for every data point).
Under Style, you can change the styling for the graph. Basically, this just changes the colors of the line or columns and lets you choose the colors for the markers. To the right of that, you can adjust the colors for the sparkline and the markers individually.
The only other important aspect of sparklines is the Axis options. If you click on that button, you’ll see some options called Vertical Axis Minimum Value Options and Vertical Axis Maximum Value Options.
If you want to make the sparklines relative to all the other rows instead of just its own row, choose Same for All Sparklines under both headings. Now when you look at the data, you’ll see that you can compare the charts in terms of absolute values. I also found that viewing the charts in column form makes it easier to see the data when comparing all sparklines.
As you can see now, the columns in Store 1 are much higher than the columns for Store 8, which had a slight upward trend, but with a much smaller revenue value. The light blue columns are low and high points because I checked those options.
That’s about all there is to know about sparklines. If you want to make a fancy looking Excel spreadsheet for your boss, this is the way to do it. If you have any questions, feel free to post a comment. Enjoy!