如何使用MicrosoftExcel对数据进行分类

Excel通常被认为是不能进行多种数据分析的工具。它无法扩展以处理大型数据集，并且缺少编程语言和机器学习库的某些关键功能。因此在本文您将看到许多公式无法适应Excel，我使用Excel的原因是使非程序员可以进行简单的数据分析，因为大多数人都对该工具有基本的了解。那么如何使用Microsoft Excel对数据进行分类呢？下文将给出介绍。

我们需要做出一个重要的决定：我们如何将这些数据集分为训练集和测试集。给定更大的数据集，可以使用一些优化技术来做出此决策。由于此数据集很小，适合初学者使用，因此按照惯例，我们将其拆分为70/30。换句话说，我们将使用70%的数据，即105个数据点作为训练集，而其余45个数据点作为测试集。

现在，我们将使用Excel随机采样70%的数据。首先，在工作表中添加一个名为“ Random Value”的列，并使用RAND()函数随机选择一个介于0和1之间的值。请记住，每次工作表时，RAND()函数都会重新选择一个新数字。重新计算。

为避免这种情况，生成数字后，我将复制它们（Ctrl + C），然后将它们特殊粘贴为值（Ctrl + Shift + V），以使其保持固定。我们将从单元格F2开始，然后向下拖动到最后一个数据点。

=RAND()

接下来，我将使用Excel的RANK()函数将它们排名为1到150，如下图所示从G2单元格开始，一直向下拖动到最后一个数据点。确保通过按F4或手动添加$符号来锁定参考框架，否则该公式将无法正常工作。

=RANK(F2, $F$2:$F$15)

现在，每个数据点都有一个介于1到150之间的唯一值。因为我们要为训练集使用105个值，所以我们将再增加一列，并使用快速IF()函数为训练集选择从1到105的值。

否则，我们会将值添加到测试集中。同样，我们将从H2开始，然后向下拖动到最后一个数据点。

=IF(G2<=105,”Training”, “Test”)

此时，您的数据集应该像屏幕截图一样进行设置。请记住，由于我们每个人都采了不同的随机样本，因此FH列中的特定值对您来说看起来会有所不同。您还应该花一点时间为下一步添加过滤器。

接下来，我们将把两组数据分成各自的工作表，以使事情井井有条。创建一个名为“ Training Set”的新工作表，并过滤原始工作表中的“ Training”数据。复制此数据以及标题，并将其粘贴到“培训集”中。您应该有106行。对工作表“测试集”执行相同的操作。您应该有46行（45个值+标题行）。

此时，由于我们已经隔离了数据，因此您可以摆脱“ Iris”工作表，并删除其余两个工作表中的FH列。最后，我将在每个工作表的开头添加一个“ ID”列，并通过简单地键入数字分别标记每个数据点1–105和1–45。

 建立模型

现在我们的数据已经准备就绪，我们可以继续建立模型了。提醒一下，此模型通过将我们希望分类的未知数据点与其最近或最相似的邻居进行比较来工作。为此，我们需要获取测试集中的每个点，并计算其与训练集中每个点的距离。

 距离的概念

距离是数学家确定n维空间中最相似点的方式。直觉是，点之间的距离越小，它们越相似。我们大多数人习惯于在二维空间中计算距离，例如x，y坐标系或使用经度和纬度。

有几种计算距离的方法，但为了简单起见，我们将使用欧几里得距离。下面是二维空间中欧几里德距离公式的可视化。如您所见，该公式的工作原理是在两个点之间创建一个直角三角形，并确定斜边的长度，即三角形的最长边，如箭头所示。

我们的数据集是4维的。对于我们来说，很难可视化超过3个维度的空间，但是无论您是否可以可视化它，我们仍然可以以相同的方式计算两个点之间的距离，而不考虑维度的数量。

用通俗易懂的语言来说，两点之间的欧几里得距离q&p可以通过为每个点取每个维度并反复平方它们之间的差值来确定，直到确定所有尺寸并将差异加在一起。

然后我们取该和的平方根，得到欧几里得距离。听起来很复杂，但是您会发现，一旦回到数据中，它的使用实际上非常简单。

 计算距离

在我们的工作簿中，创建一个名为“ Distance”的新工作表。我们针对此工作表的目标是创建一个45X105的矩阵，其中包含测试集中的每个数据点与训练集中的距离。在我们的情况下，每一行将对应于测试集中的一个数据点，而每一列将对应于训练集中的一个数据点。从A2开始，逐行向下处理，直到您达到A46，然后在每个单元格中填充数字1–45。

同样，填充手柄在这里很有用，因此您不必一一键入数字。现在，从B1开始工作，然后水平逐列进行直到找到DB1，然后在每一列中填充数字1–105。您的矩阵应类似于下面的屏幕快照，其中仅占一小部分。

在继续之前，您需要将矩阵转换为表格，以便我们使事情井井有条。选择整个矩阵，然后按Ctrl + T，然后将表命名为“ Distance_Table”，然后选择创建带有表头的表。接下来，您需要通过在单元格A1中键入名称来命名您的第一列“测试ID”。

现在我们的表已经设置好了，我们可以开始计算了。我们将从单元格B2开始，该单元格将计算训练集中的第一个点（ID#1）与测试集中的第一个点（ID#1）之间的距离。通过使用excel中的VLOOKUP函数，我们可以快速应用欧几里得距离公式，以找到每个尺寸的值，然后根据需要进行计算。

最好将此公式复制并粘贴到单元格B2中的公式栏中，因为它可以处理Excel中表格功能的一些特殊功能，但请确保您了解此公式正在执行的所有操作都是应用我们前面讨论的欧几里德距离公式。按照编写的内容，您可以拖动它来填满整个表格。

=SQRT(((VLOOKUP(NUMBERVALUE(Distance_Table[[#Headers],[1]]), ‘Training Set’!$A$1:$F$106, 2, FALSE)-VLOOKUP(Distance_Table[@[Test ID]:[Test ID]], ‘Test Set’!$A$1:$F$46, 2, FALSE)) ^ 2+(VLOOKUP(NUMBERVALUE(Distance_Table[[#Headers],[1]]), ‘Training Set’!$A$1:$F$106, 3, FALSE)-VLOOKUP(Distance_Table[@[Test ID]:[Test ID]], ‘Test Set’!$A$1:$F$46, 3, FALSE)) ^ 2+(VLOOKUP(NUMBERVALUE(Distance_Table[[#Headers],[1]]), ‘Training Set’!$A$1:$F$106, 4, FALSE)-VLOOKUP(Distance_Table[@[Test ID]:[Test ID]], ‘Test Set’!$A$1:$F$46, 4, FALSE)) ^ 2+(VLOOKUP(NUMBERVALUE(Distance_Table[[#Headers],[1]]), ‘Training Set’!$A$1:$F$106, 5, FALSE)-VLOOKUP(Distance_Table[@[Test ID]:[Test ID]], ‘Test Set’!$A$1:$F$46, 5, FALSE)) ^ 2))

 寻找最近的邻居

在此阶段，我们已经计算出测试集中的每个点与训练集中的每个点之间的距离。现在，我们需要确定测试集中每个点最近的邻居。创建一个名为“最近邻居”的新工作表，并从A2开始逐行工作，以1至45的数字填充单元格，以与测试集中的点相对应。我们的专栏将不会像以前的工作表那样代表培训集。相反，它们将代表6个最近的邻居，从第一个最近的邻居开始，然后从第二个最近的邻居开始，依此类推。第一个最近的邻居具有最小的距离，第二个最近的邻居具有第二个最小的距离，依此类推。您的工作表应如下所示：

如前所述，我们将在单元格B2中编写一个公式，可以拖动该公式以填充矩阵的其余部分。我们的方法是在距离表的相应行（2）中标识最小值，找到该值的列号，然后返回列名，因为这将为我们提供训练集中值的ID。

我们将结合使用Index和Match函数来实现此目的。请注意，我们能够简化此公式，因为我们具有远见卓识，可以将距离矩阵设置为Excel中的表格，因此我们可以轻松提取标题。

=INDEX(Distance_Table[#Headers], MATCH(SMALL(Distance!$B2:$DB2, 1), Distance!2:2, FALSE))

拖动此公式以填充最近的邻居矩阵的第一行。您将需要在SMALL()函数中手动调整粗体值以表示我们正在寻找的邻居。因此，例如，要找到第二近邻，公式将如下所示。

=INDEX(Distance_Table[#Headers], MATCH(SMALL(Distance!$B2:$DB2, 2), Distance!2:2, FALSE))

请记住，您的值将有所不同，因为用于形成测试集的随机样本与我的不同。

在此阶段，我通常花一分钟时间在可行的情况下手动仔细检查其中一行，以确保我的公式能够按预期工作。您需要大规模使用自动化测试，但现在我们将其保持简单。

我们有最后一步：我们需要确定每个最近邻居的分类。我们将回到B2中的公式，并对其进行修改以对Training Set中的ID进行VLOOKUP，然后返回分类。然后，我们将其拖动以填充矩阵。

=VLOOKUP(NUMBERVALUE(INDEX(Distance_Table[#Headers], MATCH(SMALL(Distance!$B2:$DB2, 1), Distance!2:2, FALSE))), ‘Training Set’!$A$1:$F$106, 6, FALSE)

 退后一步

让我们退后一步，看看我们已经完成了什么。现在，您已经为测试集中的每个点确定了6个最近邻居的分类。您可能会注意到，对于您的所有或几乎所有数据点，最近的6个邻居都将归为同一类别。这意味着我们的数据集将他高度聚集。在我们的案例中，我们的数据高度聚类有两个原因。

首先，正如我们在本教程开始时所讨论的那样，数据集设计为易于使用。其次，这是一个低维数据集，因为我们仅使用4维。当您处理现实世界的数据时，通常会发现它的聚集程度要低得多，尤其是随着维数的增加。数据的群集越少，构建有用模型所需的训练集就越大。

 通过机器学习进行优化

如果我们的数据始终与Iris数据集一样整齐地聚类，则无需进行机器学习。我们将使用公式简单地找到最近的邻居，然后使用该公式来确定每个未知数据点的分类。由于通常不是这种情况，因此机器学习可通过一次查看多个邻居来帮助我们更准确地预测未知数据点的分类。

但是，我们应该看几个邻居？这就是“ K最近邻”中的“ K”出现的地方。K描述了预测未知数据点分类时将考虑的邻居数。

 邻居太多或太多

凭直觉，了解为什么这个问题很棘手很重要。可能会看到太多的邻居，也可能会看到太多的邻居。特别是随着维数的增加，最近的邻居可能并不总是正确的分类。看着很少的邻居会限制您的模型可用于确定的信息量。

考虑到过多的邻居实际上会降低模型用作输入的信息的质量。这是因为随着引入更多邻居，您也将噪声引入数据中。试想一下在我们的示例中考虑所有104个邻居都没有道理。

因此，这成为经典的优化问题，我们试图找到给出最多信息而又不会太高或太低的K值。

 使用测试仪

在本教程中，我们将使用一个非常简单的反复试验过程来确定最佳K值。在继续之前，我建议您查看一下“最近邻居”工作表，并猜测一下最佳k值可能是什么，只是为了好玩。如果您是对的，我们会尽快找出答案！

 设置算法

算法只是计算机根据定义的一组规则反复重复的一组步骤。在这种情况下，我们将告诉计算机尝试不同的K值，使用我们的测试集计算每个错误的错误率，然后最终返回产生最低错误率的值。

为此，我们需要创建一个名为“ KNN模型”的新工作表。我们将如下设置，为每个测试数据点的A4至A48行标记1至45。

让我们从B列中的预测值开始。我们需要此公式根据K值进行调整。在K值为1的情况下，公式很简单，我们只取最近的邻居。

=’Nearest Neighbors’!B2

在K值大于1的情况下，我们将采用出现的最常见的邻居。如果邻居的出现是均匀分布的，例如，当K = 6时，如果3个邻居是Setosa，而3个邻居是Virginica，我们将以最近邻居的分类为准。

K = 2的公式如下。我们使用IFERROR，因为当给定的K值有两个邻居发生相同次数时，此公式将返回错误。

=IFERROR(INDEX(‘Nearest Neighbors’!B2:C2,MODE(MATCH(‘Nearest Neighbors’!B2:C2,’Nearest Neighbors’!B2:C2,0))), ‘Nearest Neighbors’!B2)

您需要在B4单元格中使用下面的扩展公式，该公式使您可以使用K值，包括K = 6。无需担心此公式的细节，只需复制并粘贴即可。顺便说一句，必须使用复杂，挑剔且难以理解的此类公式是我之前提到的Excel的局限之一。

这在Python中简直就是小菜一碟。请注意，如果K中没有值或1到6之间的值，则此公式将返回错误。您应该从单元格B4的B列复制此公式。

=IFS($B$1=1, ‘Nearest Neighbors’!B2, $B$1=2, IFERROR(INDEX(‘Nearest Neighbors’!$B$2:$C$2,MODE(MATCH(‘Nearest Neighbors’!$B$2:$C$2,’Nearest Neighbors’!$B$2:$C$2,0))), ‘Nearest Neighbors’!B2), $B$1=3, IFERROR(INDEX(‘Nearest Neighbors’!$B$2:$D$2,MODE(MATCH(‘Nearest Neighbors’!$B$2:$D$2,’Nearest Neighbors’!$B$2:$D$2,0))), ‘Nearest Neighbors’!B2), $B$1=4, IFERROR(INDEX(‘Nearest Neighbors’!$B$2:$E$2,MODE(MATCH(‘Nearest Neighbors’!$B$2:$E$2,’Nearest Neighbors’!$B$2:$E$2,0))), ‘Nearest Neighbors’!B2), $B$1=5, IFERROR(INDEX(‘Nearest Neighbors’!$B$2:$F$2,MODE(MATCH(‘Nearest Neighbors’!$B$2:$F$2,’Nearest Neighbors’!$B$2:$F$2,0))), ‘Nearest Neighbors’!B2),$B$1=6, IFERROR(INDEX(‘Nearest Neighbors’!$B$2:$G$2,MODE(MATCH(‘Nearest Neighbors’!$B$2:$G$2,’Nearest Neighbors’!$B$2:$G$2,0))), ‘Nearest Neighbors’!B2))

接下来，我们想获取每个测试点的实际已知分类，以便我们可以确定我们的模型是否正确。为此，我们在C列中使用快速VLOOKUP，从C4单元格开始向下拖动。

=VLOOKUP(A4, ‘Test Set’!$A$1:$F$46, 6, FALSE)

然后，我们将在D列中设置一个公式，以在预测不正确或错误时返回1，在预测正确时返回0。您将在单元格D4中开始并将公式向下拖动。

=IF(B4=C4, 0, 1)

最后，我们将使用单元格B2中的此公式，通过将错误数除以数据点的总数来计算错误率。作为惯例，我们将其格式化为百分比。

=SUM(D4:D48)/COUNT(D4:D48)

 运行算法

现在，我们准备针对不同的K值运行算法。因为我们只测试6个值，所以我们可以手工完成。但这不会很有趣，更重要的是无法扩展。您需要按照本文中的说明为Solver加载项启用Excel ，然后再继续。

现在，导航到“数据”功能区，然后单击“求解器”按钮。求解器按钮会根据我们的说明为我们自动进行反复试验。您将有一个对话框，其中包含要设置的参数或说明，如下所示。

我们对其进行设置，以便在测试1到6之间的值时寻求最小化错误率。

Excel将旋转一分钟，您可能会看到它在显示此对话框之前在屏幕上闪烁了一些值。您应该单击确定以保留求解器解决方案。

 解释错误率和解决方案

由于数据具有多个最小值或最大值，因此许多优化算法具有多种解决方案。以我为例。实际上，在我的特定情况下，所有整数值1到6都表示最小值，错误率约为2%。那么，我们现在该怎么办？

有几件事贯穿我的脑海。首先，这个测试集不是很好。该模型没有从测试集中获得任何优化优势，因此，我可能会重新做测试集，然后再次尝试查看是否得到不同的结果。我还会考虑使用更复杂的测试方法，例如交叉验证。

在我的测试集中如此低的错误率下，我也开始担心过度拟合。当模型过于适合特定训练或测试数据集的细微差别时，过度拟合是机器学习中出现的问题。当模型过度拟合时，在野外遇到新数据时，它就不会具有预测性或有效性。

当然，使用这样的学术数据集，我们希望我们的错误率相当低。

下一个考虑因素是，如果我确定了几个最小值，应选择哪个值。尽管该测试在此特定示例中无效，但通常我会选择最少的邻居，该邻居数量最少，以节省计算资源。如果必须考虑较少的邻居，我的模型将运行得更快。较小的数据集不会有任何影响，但是这样的决策可以节省大量的资源。

以上就是关于如何使用Microsoft Excel对数据进行分类的全部内容，想了解更多关于 Excel数据分析的信息，请继续关注。