在可视化数据时,条形图(Bar)非常有用。他们可以显示一组数据或比较多个数据集。
在本文中,我们将介绍如何在Google 表格(Google Sheets)中制作各种类型的条形图。
如何在Google表格中创建条形图(Bar Graph)
我们将从一个简单的两列电子表格开始。在电子表格的第一列中,为系列中的每一行添加一个标签。如果您愿意,您可以在顶部单元格中添加一个类别,该类别将显示为图表水平 y 轴的标题。该类别名称下的标签将沿图表的水平轴显示。
添加(Add)至少一列数据。在第二列的第一个单元格中输入标签,然后在其下方的单元格中添加数据。
接下来,按照以下步骤插入条形图来表示您的数据。
- 选择(Select)所有包含数据的单元格。
- 从菜单中,选择插入(Insert )>图表(Chart)或选择插入图表(Insert Chart)图标。
无论您选择哪种方法,Google都会在您的工作表中插入一个条形图。(谷歌(Google)称之为柱形图(column chart)。这是一回事。)
在 Google 表格中制作包含多个数据的条形图(Making a Bar Graph with Multiple Data in Google Sheets)
要制作包含多组数据的条形图,只需添加更多数据列。
按照与上述相同的步骤插入数据的条形图表示。
- 选择(Select)所有包含数据的单元格。
- 从菜单中,选择插入(Insert )>图表(Chart)或选择插入图表(Insert Chart)图标。
这是一个使用上述电子表格中的多列数据的条形图。
在这种情况下,Google使用第一行数据中的类别作为图表标题。
在 Google 表格中制作堆积条形图(Making a Stacked Bar Graph in Google Sheets)
当您使用多个数据集时,您可以通过选择所谓的堆积条形图(stacked bar chart)来显示数据中的部分与整体关系。在上面的示例中,图表显示了每个人在特定月份阅读了多少本书。如果我们将条形图转换为堆积条形图,我们将看到每个人当月阅读的书数与每个人当月阅读的书总数的比较。
堆叠条形图有几种不同的风格。首先(First),我们将查看标准堆积条形图。(Standard stacked bar chart.)
插入条形图后,在其内部双击,图表编辑器(Chart Editor)面板将出现在右侧。
注意(Note):您始终可以在图表编辑器(Chart Editor)中更改图表的标题,或双击图表标题本身。
在堆叠(Stacking)下,选择标准(Standard)。
现在,您将看到每个类别的值堆叠成单个条形。
或者,您可以选择100%而不是Standard来制作堆叠条形图,以描述单个数据与整体的比率。当累计总数不重要时使用此选项。
因此,对于我们的示例,我们可能并不关心每个月总共阅读了多少本书——只关心每个人相对于其他人阅读了多少本书。
请注意(Notice),在上面的 100%堆叠条形图中(Stacked Bar Chart),沿 x 轴的标签现在是百分比。
如何交换图表中的列和行(How to Swap Columns & Rows in a Chart)
使用我们的示例,假设您想轻松地可视化每个人的阅读习惯如何逐月变化。Google 表格(Sheets)可让您轻松将列转换为行,反之亦然。
- 双击图表或图形。
- 选择右侧的设置。(Setup )
- Switch rows/columns旁边的框。
我们的常规条形图现在看起来像这样:
如果我们在堆积条形图上切换行和列,它将如下所示:
您可以看到,这些不同的选项中的每一个都非常适合讲述有关我们数据的特定故事。想想你(you)想讲什么故事,并确定哪种条形图最能说明你的观点。
在 Google 表格中自定义条形图(Customizing Bar Graphs in Google Sheets)
您可能已经注意到图表编辑器中的自定义选项卡。(Customize )
选择该选项卡以更改图表的外观。接下来,我们将浏览“自定义(Customize )”选项卡的每个部分。
图表样式(Chart style)允许您为图表选择背景颜色、边框颜色和字体。如果您不喜欢所做的更改,您可以随时选择“重置布局(Reset layout)”按钮重新开始。
选中最大化(Maximize)框将减少图表中的空白。试试看,看看你是否喜欢你所看到的。
选择3D框将使您的条形变为三维,如下所示:
(Compare mode)当您将鼠标悬停在图表的不同元素上时,比较模式将突出显示可比较的数据。在下面的图表中,请注意11 月(November)的数据(每个堆叠条的最顶部)是如何突出显示的。
图表和轴标题(Chart & axis titles)部分是另一个可以更改图表标题及其字体、字体大小、格式(斜体、粗体等)和文本颜色的地方。
在系列(Series)部分,您可以更改系列标签的外观。在我们的例子中,这是条形图的月份及其对应部分。例如,您可以将11 月(November)的数据从黄色更改为灰色。
您还可以格式化特定的数据点,例如,表示Lisa在(Lisa)10 月(October)阅读的书籍数量的数据。单击(Click)Format data point旁边的Add按钮,然后您可以从那里更改该单个数据点的颜色。
在图例(Legend)部分,您可以更改图例字体、字体大小、格式和文本颜色。
水平轴(Horizontal axis)和垂直轴(Vertical axis)部分提供了用于在图表的每个轴上格式化标签的类似选项。
最后,网格线和刻度线(Gridlines and ticks)是一个相对较新的功能(截至2020 年 6 月(June 2020)),它允许您通过插入刻度线、格式化它们以及设置它们之间的间距来强调轴的某些部分。
谷歌表格让条形图变得简单
现在您几乎了解了有关在Google 表格(Google Sheets)中制作条形图的所有知识。如果您想了解更多使用Google 表格(Google Sheets)的方法,请阅读您需要了解的 5 个 Google 表格脚本函数(5 Google Sheets Script Functions You Need to Know)(5 Google Sheets Script Functions You Need to Know)。
How to Make a Bar Graph in Google Sheets
Bar graphs can be extremely helpful when it сomеs to visυаlizing data. Thеy can diѕplay one set of data or compare multiple data sets.
In this article, we’ll go over how to make various types of bar graphs in Google Sheets.
How to Create a Bar Graph in Google Sheets
We’ll start with a simple, two-column spreadsheet. In the first column of your spreadsheet, add a label for each row in your series. If you like, you can add a category in the top cell and that category will appear as the title of your graph’s horizontal y-axis. The labels under that category name will appear along the horizontal axis of your chart.
Add at least one column of data. Enter a label in the first cell of the second column, and add the data in the cells beneath it.
Next, follow these steps to insert a bar graph to represent your data.
- Select all the cells that have data in them.
- From the menu, select Insert > Chart or select the Insert Chart icon.
Whichever method you choose, Google will insert a bar graph into your sheet. (Google calls it a column chart. This is the same thing.)
Making a Bar Graph with Multiple Data in Google Sheets
To make a bar graph that includes multiple sets of data, just add more columns of data.
Follow the same steps as above to insert a bar graph representation of your data.
- Select all the cells that have data in them.
- From the menu, select Insert > Chart or select the Insert Chart icon.
Here’s a bar graph that uses multiple columns of data from the spreadsheet above.
In this case, Google uses the categories in the first row of data as the chart title.
Making a Stacked Bar Graph in Google Sheets
When you use multiple data sets, you can show the part-to-whole relationships in your data by opting for what’s called a stacked bar chart. In our example above, the chart showed how many books each person read in a particular month. If we switch the bar graph to a stacked bar chart, we’ll see how many books each person read that month compared to the total number of books everyone read that month.
There are a couple different flavors of stacked bar charts. First we’ll look at the Standard stacked bar chart.
After you’ve inserted your bar chart, double-click inside it and the Chart Editor panel will appear to the right.
Note: You can always change the chart’s title inside the Chart Editor or by double-clicking on the chart title itself.
Under Stacking, choose Standard.
Now you’ll see the values of each category stacked into single bars.
Alternatively, instead of Standard, you can choose 100% to make a stacked bar chart that depicts the ratio of individual data to the whole. Use this when the cumulative total isn’t important.
So for our example, we might not care how many books were read in total each month—only how many books each person read relative to other people.
Notice that in the 100% Stacked Bar Chart above, the labels along the x axis are now percentages.
How to Swap Columns & Rows in a Chart
Using our example, let’s say you want to make it easy to visualize how each person’s reading habits changed from month to month. Google Sheets makes it easy to turn your columns into rows and vice versa.
- Double-click the chart or graph.
- Select Setup on the right.
- Check the box next to Switch rows/columns.
Our regular bar graph now looks like this:
If we switch rows and columns on our stacked bar chart, it will look like this:
You can see that each of these different options is ideal for telling a particular story about our data. Think about what story you want to tell, and determine which kind of bar graph most clearly makes your point.
Customizing Bar Graphs in Google Sheets
You may have noticed the Customize tab in the Chart Editor.
Select that tab to change the look and feel of your chart. Next we’ll go through each section of the Customize tab.
Chart style allows you to choose the background color, border color, and font for your chart. If you don’t like the changes you made, you can always select the Reset layout button to start over.
Checking the Maximize box will decrease the white space in your chart. Try it and see if you like what you see.
Selecting the 3D box will make your bars three-dimensional, like this:
Compare mode will highlight comparable data when you hover your mouse over different elements of your chart. In the chart below, notice how the November data (the top-most section of each stacked bar) is highlighted.
The Chart & axis titles section is another place you can change the chart title as well as its font, font size, format (italics, bold, etc.), and text color.
In the Series section, you can change the appearance of your series labels. In our case, that’s the months and their corresponding parts of the bar graph. For example, you could change the November data from yellow to gray.
You can also format a specific data point like, for example, the data representing the number of books Lisa read in October. Click the Add button next to Format data point, and from there you can change the color of that single data point.
In the Legend section, you can change the legend font, font size, format, and text color.
The Horizontal axis and Vertical axis sections offer similar options for formatting labels on each of your chart’s axes.
Finally, Gridlines and ticks is a relatively new feature (as of June 2020) allowing you to emphasize parts of your axes by inserting tick marks, formatting them, and setting the spacing between them.
Google Sheets Makes Bar Graphs Easy
Now you know almost all there is to know about making a bar graph in Google Sheets. If you’d like to learn more ways to use Google Sheets, read about the 5 Google Sheets Script Functions You Need to Know.