excel增减因素比较图表_比较Excel图表中的字数

It's week two in the free online

信息图表和数据可视化课程的第二周，我正在研究本周的作业。

The discussion this week is about a New York Times graphic that shows the number of times that words were used at national conventions.

本周的讨论是关于《纽约时报》的图片，该图片显示了在全国代表大会上使用该单词的次数。

The words are shown in blue and red (pink?) bubbles, representing the party colours, and you can add other words to the graphic, to see how they fared.

单词以蓝色和红色(粉红色？)气泡显示，代表聚会的颜色，您可以在图形中添加其他单词，以查看其效果。

If you click on a circle, the selected word is shown in context, below the graphic, in various speeches.

如果单击一个圆圈，则选定的单词会在上下文中以各种语言显示在图形下方。

信息图的改进 (Improvements to the Infographic)

There were things that I liked about this infographic, and a few things that could be improved.

关于此信息图表，我有些喜欢的地方，还有一些可以改进的地方。

The positives:

优点：

• The circles gave a quick overview of the word usage, and showed the party split on each word.
  圈子提供了单词用法的快速概览，并显示了在每个单词上的用法说明。
• Words that were used more often my Democrats fell to the left, and Republican favourites fell to the right.
  我的民主党人经常使用的词落在左边，而共和党的最爱则落在右边。
• It was easy to enter a new word, and see where it fell
  输入一个新词很容易，看看它落在哪里
• It helped to see the words in context, below the graphic, to understand how they were used
  它有助于在图形下方查看上下文中的单词，以了解其用法

The negatives:

底片：

• It was hard to see some of the words, in the smaller circles
  在较小的圆圈中很难看到一些单词
• The words were hard to find, because they were in random order, on either side of the graphic
  在图形的两侧很难找到这些单词，因为它们是随机顺序的
• It wasn't clear how or why the words in the infographic were selected. There must have been thousands of words used in the speeches, and only a few were shown.
  目前尚不清楚如何或为什么选择图表中的单词。 演讲中肯定有成千上万个单词，只显示了几个。

在Excel中显示单词用法 (Show the Word Usage in Excel)

It would take a huge database to store all those speeches, and analyze them. Excel probably couldn't handle that, but it can do a smaller version of the word usage graphic.

需要一个巨大的数据库来存储所有这些语音，并进行分析。 Excel可能无法解决这个问题，但是它可以处理较小的单词用法图形版本。

In an Excel chart, I could arrange the words alphabetically, so they're easier to find, or sort by total times used, or show highest use by each party.

在Excel图表中，我可以按字母顺序排列单词，因此更容易查找它们，也可以按使用的总时间进行排序，或者显示各方的最高使用率。

To start, I listed a few of the words, and the number of times they were used by each party.

首先，我列出了一些单词，以及每一方使用它们的次数。

I formatted that list as an Excel table, named tblWordsAll, and added a total in each row.

我将该列表格式化为一个名为tblWordsAll的Excel表 ，并在每行中添加了总计。

=SUM(tblWordsAll[@[Democrat]:[Republican]])

= SUM(tblWordsAll [@ [Democrat]：[Republican]])

To finish the table, I named the data in the first column – WordList.

为了完成表，我在第一列– WordList中命名了数据。

创建单词简短列表 (Create a Short List of Words)

Instead of showing all the words in the a chart, I'd like to select a few from the main list. So, I created another table, named tblWordsSel, with a drop down list in the first column.

我不想在图表中显示所有单词，而是从主列表中选择一些单词。 因此，我创建了另一个表tblWordsSel，并在第一列中有一个下拉列表。

The drop down is a data validation list, using the WordList range as its source.

下拉列表是一个数据验证列表 ，使用WordList范围作为其来源。

I selected three words, to use in the chart, and then sorted the words in ascending order (A-Z).

我选择了三个单词以在图表中使用，然后按升序(AZ)对单词进行排序。

跨表格填充公式很费劲 (Filling Table Formulas Across Is a Drag)

To pull the related numbers from the main table, I added INDEX / MATCH formulas in the next two columns.

为了从主表中提取相关数字，我在接下来的两列中添加了INDEX / MATCH公式。

=IFERROR(INDEX(tblWordsAll[Democrat], MATCH([@Word],tblWordsAll[Word],0)),0)

= IFERROR(INDEX(tblWordsAll [Democrat]，MATCH([@ Word]，tblWordsAll [Word]，0))，0)

Usually I would drag a formula across, to fill in the next column, but I can't find a way to create an absolute reference to a table column. Am I missing something obvious?

通常，我会拖动一个公式来填充下一列，但找不到找到对表列的绝对引用的方法。 我是否缺少明显的东西？

If I drag the formula over to the Republican column, it shifts from looking in the Word column, to looking in the Democrat column.

如果将公式拖到“共和党”列中，则该公式将从在“单词”列中查找到在“民主党”列中查找。

So, I copied the formula, and pasted it into the formula bar with cell C2 selected. Then, I changed the reference for the INDEX to Republican instead of Democrat. There must be a better way!

因此，我复制了公式，并将其粘贴到选择了单元格C2的公式栏中。 然后，我将INDEX的参考改为共和党而不是民主党。 肯定有更好的办法！

=IFERROR(INDEX(tblWordsAll[Republican], MATCH([@Word],tblWordsAll[Word],0)),0)

= IFERROR(INDEX(tblWordsAll [Republican]，MATCH([@ Word]，tblWordsAll [Word]，0))，0)

绘制结果图 (Chart the Results)

To show the results in a chart, I selected a cell in the small table, and on the Excel's Ribbon, clicked the Insert tab.

为了在图表中显示结果，我在小表中选择了一个单元格，然后在Excel的功能区上单击“插入”选项卡。

From the Bar Chart drop down, I clicked on Stacked Bar, to create this chart. How convenient – it even defaulted to the correct colours!

在“条形图”下拉列表中，单击“堆积条形图”以创建此图。 多么方便–它甚至默认为正确的颜色！

There are a few changes to make, so the chart looks better:

需要进行一些更改，因此图表看起来更好：

1. Right-click the Category Axis, and click Format Axis
   右键单击“类别”轴，然后单击“设置轴格式”
2. in the Axis Options, check 'Categories in Reverse Order' and for 'Horizontal Axis Crosses' select 'At Maximum Category'
   在“轴选项”中，选中“反转类别”，对于“水平轴交叉”，选择“最大类别”
3. On the Ribbon's Layout tab (under Chart Tools), click Legend, and move it to the bottom
   在功能区的“布局”选项卡上(在“图表工具”下)，单击“图例”，然后将其移至底部
4. Also on the Layout tab, click Data Labels, and choose Center.
   同样在“布局”选项卡上，单击“数据标签”，然后选择“中心”。
5. Change the Data Labels font to white, for better contrast.
   将数据标签字体更改为白色，以获得更好的对比度。

And here is the completed chart.

这是完整的图表。

添加更多单词 (Add More Words)

Just like the original infographic, you can add more words to this Excel Chart.

就像原始信息图一样，您可以在此Excel图表中添加更多单词。

1. Click in the last cell of the small table, and press the tab key, to go the first cell in a new row.
   单击小表的最后一个单元格，然后按Tab键，以进入新行中的第一个单元格。
2. Select a new word from the drop down list, and it is automatically added to the chart too.
   从下拉列表中选择一个新单词，该单词也会自动添加到图表中。

After adding or changing the word selections, you can sort the Word column alphabetically again, so it's easier to find the words.

添加或更改单词选择之后，您可以再次按字母顺序对“单词”列进行排序，因此更容易找到单词。

图表增强 (Chart Enhancements)

This simple chart lets you compare the word usage between parties, and see which words were used most overall.

这个简单的图表使您可以比较各方之间的单词使用情况，并查看总体上使用最多的单词。

There is room for improvement though, and here are a couple of features that you could add:

不过，还有改进的余地，您可以添加以下几个功能：

• The word list for the chart has to be sorted manually in this version. Add a macro to sort the list automatically if the words change.
  在此版本中，图表的单词列表必须手动排序。 如果单词更改，请添加宏以自动对列表进行排序。
• Add a Total column to the short word list, and an option to sort by that column, A-Z or Z-A.
  将“总计”列添加到短单词列表，并添加一个选项以按该列AZ或ZA进行排序。

What else would you add as an enhancement?

您还将添加什么作为增强功能？

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

To download the sample file, please visit my Contextures website: Excel Sample Files – Charts and Graphics Section.

要下载示例文件，请访问我的Contextures网站： Excel示例文件–图表和图形部分 。

Look for CH0006 – Word Usage Chart

寻找CH0006 –单词用法表

Excel仪表板课程今天关闭 (Excel Dashboard Course Closes Today)

Remember, today is your last chance to register for Mynda Treacy's Excel Dashboard Course.

请记住，今天是您最后一次注册Mynda Treacy的Excel仪表板课程的机会 。

The course is video based, delivered online and is available 24/7. You'll receive comprehensive workbooks and sample dashboards to keep, and there’s even an option to download the videos.

该课程以视频为基础，在线提供，并且全天24/7可用。 您会收到综合的工作簿和示例仪表板，以备保留，甚至还可以选择下载视频。

Click here to find out details of the course, read the student comments, and watch the 'behind the scenes' video that shows you what you'll receive as a member.

单击此处查找课程详细信息，阅读学生的评论，并观看“幕后花絮”视频，向您展示您将获得的会员资格。