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數據分析的信息，請繼續關注中培偉業。