excel使用vb统计分数_使用Excel选项按钮计算调查分数

excel使用vb统计分数

For easier data entry, you can add Option Buttons on a worksheet. Instead of having to type an answer to a question, just click on one of the buttons, to make a choice. Here's how to calculate survey scores with Excel Option Buttons.

为了简化数据输入，您可以在工作表上添加选项按钮。 无需键入问题的答案，只需单击按钮之一进行选择。 这是使用Excel选项按钮计算调查分数的方法。

添加选项按钮 (Add the Option Buttons)

When you set up the buttons, you can create a simple option group, with only two buttons, like the English or French example shown below.

设置按钮时，可以创建一个只有两个按钮的简单选项组，如下面显示的英语或法语示例。

There are set up instructions in this blog post, and in the video shown at the end of this article.

本文的博客文章和本文末尾的视频中都有设置说明。

使用选项按钮创建调查 (Create a Survey with Option Buttons)

If two options aren’t enough, you can set up something fancier, like Dave Peterson's Excel Survey Template, that he shares on my Contextures website.

如果两个选项还不够，您可以设置一个更奇特的东西，例如Dave Peterson的Excel Survey Template ，他可以在我的Contextures网站上共享它。

It uses programming to create Group Boxes, with six Option buttons in each group. The buttons in each group are linked to the Response column in that row.

它使用编程来创建“组框”，每个组中有六个“选项”按钮。 每个组中的按钮都链接到该行中的“响应”列。

In the screen shot below, the selected option button, and all the others in that group, are linked to cell C2, as you can see in the formula bar.

在下面的屏幕快照中，所选的选项按钮以及该组中的所有其他按钮都链接到单元格C2，如您在编辑栏中所看到的。

In row 2, the first button is selected, so the Response cell shows 1. In row 5, the 6th button is clicked, and 6 shows in the Response cell.

在第2行中，选择了第一个按钮，因此Response单元显示为1。在第5行中，单击了第6个按钮，在Response单元中显示了6。

计算分数 (Calculate the Score)

In Dave’s survey file, there is a Weight column (B), and the Score is calculated in column A, using this formula (cell A2):

在Dave的调查文件中，有一个权重列(B)，并且使用以下公式(单元格A2)在列A中计算了得分：

=IF(C2="","",IF(C2=6,"N/A",B2*(C2-1)))

= IF(C2 =“”，“”，IF(C2 = 6，“ N / A”，B2 *(C2-1)))

• If no response has been selected, the Score shows an empty string
  如果未选择任何响应，则“分数”显示为空字符串
• If the response is 6, the Score shows as “N/A”
  如果响应为6，则分数显示为“ N / A”
• For any other response, 1 is subtracted from the number in the Response cell, and that amount is multiplied by the number in the Weight column.
  对于任何其他响应，从“响应”单元格中的数字中减去1，然后将该值乘以“权重”列中的数字。

创建分数查询表 (Create a Score Lookup Table)

In some surveys, you might want a different type of scoring calculation. Perhaps responses 1, 3 and 5 are worth 1 point, 2 and 4 are worth 2 points, and response 6 means “N/A”.

在某些调查中，您可能需要其他类型的计分计算。 也许答案1、3和5得1分，而2和4得2分，而答案6表示“不适用”。

You can set up a table that shows those scores, as in the screen shot below. The lists are named ranges:

您可以设置一个显示这些得分的表格，如下图所示。 列表被命名为范围：

• RespList – cells M2:M7
  RespList –单元格M2：M7
• ScoreList – cells N2:N7
  ScoreList –单元格N2：N7

更改配方 (Change the Formula)

Next, you can change the formula in column A, so it finds the score for the selected response. The formula refers to the named ranges in the Score Lookup table.

接下来，您可以更改A列中的公式，以便为所选响应找到分数。 该公式引用“分数查找”表中的命名范围。

=IF(C2="","",IF(C2=6,"N/A",B2 * INDEX(ScoreList, MATCH(C2, RespList,0))))

= IF(C2 =“”，“”，IF(C2 = 6，“ N / A”，B2 * INDEX(ScoreList，MATCH(C2，RespList，0)))))

• If no response has been selected, the Score shows an empty string
  如果未选择任何响应，则“分数”显示为空字符串
• If the response is 6, the Score shows as “N/A”
  如果响应为6，则分数显示为“ N / A”
• For any other response, INDEX and MATCH return the score for the selected response, and that amount is multiplied by the number in the Weight column.
  对于任何其他响应，INDEX和MATCH返回所选响应的分数，该数量乘以“权重”列中的数字。

选项按钮设置视频 (Option Button Setup Video)

To see the steps for manually creating a Group Box with Option Boxes, watch this short video.

要查看手动创建带有选项框的组框的步骤，请观看此简短视频。

演示地址

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

To see Dave’s setup code, and the score calculation formulas, you can download the sample files from my Contextures website. The zipped file is in xls format, and contains macros.

若要查看Dave的设置代码和分数计算公式，可以从我的Contextures网站下载示例文件 。 压缩文件为xls格式，包含宏。

翻译自: https://contexturesblog.com/archives/2014/01/23/calculate-survey-scores-with-excel-option-buttons/

excel使用vb统计分数