Excel通常被認(rèn)為是不能進(jìn)行多種數(shù)據(jù)分析的工具。它無法擴(kuò)展以處理大型數(shù)據(jù)集，并且缺少編程語言和機(jī)器學(xué)習(xí)庫的某些關(guān)鍵功能。因此在本文您將看到許多公式無法適應(yīng)Excel，我使用Excel的原因是使非程序員可以進(jìn)行簡單的數(shù)據(jù)分析，因為大多數(shù)人都對該工具有基本的了解。那么如何使用Microsoft Excel對數(shù)據(jù)進(jìn)行分類呢？下文將給出介紹。

我們需要做出一個重要的決定：我們?nèi)绾螌⑦@些數(shù)據(jù)集分為訓(xùn)練集和測試集。給定更大的數(shù)據(jù)集，可以使用一些優(yōu)化技術(shù)來做出此決策。由于此數(shù)據(jù)集很小，適合初學(xué)者使用，因此按照慣例，我們將其拆分為70/30。換句話說，我們將使用70%的數(shù)據(jù)，即105個數(shù)據(jù)點作為訓(xùn)練集，而其余45個數(shù)據(jù)點作為測試集。

現(xiàn)在，我們將使用Excel隨機(jī)采樣70%的數(shù)據(jù)。首先，在工作表中添加一個名為“ Random Value”的列，并使用RAND()函數(shù)隨機(jī)選擇一個介于0和1之間的值。請記住，每次工作表時，RAND()函數(shù)都會重新選擇一個新數(shù)字。重新計算。

為避免這種情況，生成數(shù)字后，我將復(fù)制它們（Ctrl + C），然后將它們特殊粘貼為值（Ctrl + Shift + V），以使其保持固定。我們將從單元格F2開始，然后向下拖動到最后一個數(shù)據(jù)點。

=RAND()

接下來，我將使用Excel的RANK()函數(shù)將它們排名為1到150，如下圖所示從G2單元格開始，一直向下拖動到最后一個數(shù)據(jù)點。確保通過按F4或手動添加$符號來鎖定參考框架，否則該公式將無法正常工作。

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

現(xiàn)在，每個數(shù)據(jù)點都有一個介于1到150之間的唯一值。因為我們要為訓(xùn)練集使用105個值，所以我們將再增加一列，并使用快速IF()函數(shù)為訓(xùn)練集選擇從1到105的值。

否則，我們會將值添加到測試集中。同樣，我們將從H2開始，然后向下拖動到最后一個數(shù)據(jù)點。

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

此時，您的數(shù)據(jù)集應(yīng)該像屏幕截圖一樣進(jìn)行設(shè)置。請記住，由于我們每個人都采了不同的隨機(jī)樣本，因此FH列中的特定值對您來說看起來會有所不同。您還應(yīng)該花一點時間為下一步添加過濾器。

接下來，我們將把兩組數(shù)據(jù)分成各自的工作表，以使事情井井有條。創(chuàng)建一個名為“ Training Set”的新工作表，并過濾原始工作表中的“ Training”數(shù)據(jù)。復(fù)制此數(shù)據(jù)以及標(biāo)題，并將其粘貼到“培訓(xùn)集”中。您應(yīng)該有106行。對工作表“測試集”執(zhí)行相同的操作。您應(yīng)該有46行（45個值+標(biāo)題行）。

此時，由于我們已經(jīng)隔離了數(shù)據(jù)，因此您可以擺脫“ Iris”工作表，并刪除其余兩個工作表中的FH列。最后，我將在每個工作表的開頭添加一個“ ID”列，并通過簡單地鍵入數(shù)字分別標(biāo)記每個數(shù)據(jù)點1–105和1–45。

　　建立模型

現(xiàn)在我們的數(shù)據(jù)已經(jīng)準(zhǔn)備就緒，我們可以繼續(xù)建立模型了。提醒一下，此模型通過將我們希望分類的未知數(shù)據(jù)點與其最近或最相似的鄰居進(jìn)行比較來工作。為此，我們需要獲取測試集中的每個點，并計算其與訓(xùn)練集中每個點的距離。

距離的概念

距離是數(shù)學(xué)家確定n維空間中最相似點的方式。直覺是，點之間的距離越小，它們越相似。我們大多數(shù)人習(xí)慣于在二維空間中計算距離，例如x，y坐標(biāo)系或使用經(jīng)度和緯度。

有幾種計算距離的方法，但為了簡單起見，我們將使用歐幾里得距離。下面是二維空間中歐幾里德距離公式的可視化。如您所見，該公式的工作原理是在兩個點之間創(chuàng)建一個直角三角形，并確定斜邊的長度，即三角形的最長邊，如箭頭所示。

我們的數(shù)據(jù)集是4維的。對于我們來說，很難可視化超過3個維度的空間，但是無論您是否可以可視化它，我們?nèi)匀豢梢砸韵嗤姆绞接嬎銉蓚€點之間的距離，而不考慮維度的數(shù)量。

用通俗易懂的語言來說，兩點之間的歐幾里得距離q&p可以通過為每個點取每個維度并反復(fù)平方它們之間的差值來確定，直到確定所有尺寸并將差異加在一起。

然后我們?nèi)≡摵偷钠椒礁玫綒W幾里得距離。聽起來很復(fù)雜，但是您會發(fā)現(xiàn)，一旦回到數(shù)據(jù)中，它的使用實際上非常簡單。

計算距離

在我們的工作簿中，創(chuàng)建一個名為“ Distance”的新工作表。我們針對此工作表的目標(biāo)是創(chuàng)建一個45X105的矩陣，其中包含測試集中的每個數(shù)據(jù)點與訓(xùn)練集中的距離。在我們的情況下，每一行將對應(yīng)于測試集中的一個數(shù)據(jù)點，而每一列將對應(yīng)于訓(xùn)練集中的一個數(shù)據(jù)點。從A2開始，逐行向下處理，直到您達(dá)到A46，然后在每個單元格中填充數(shù)字1–45。

同樣，填充手柄在這里很有用，因此您不必一一鍵入數(shù)字。現(xiàn)在，從B1開始工作，然后水平逐列進(jìn)行直到找到DB1，然后在每一列中填充數(shù)字1–105。您的矩陣應(yīng)類似于下面的屏幕快照，其中僅占一小部分。

在繼續(xù)之前，您需要將矩陣轉(zhuǎn)換為表格，以便我們使事情井井有條。選擇整個矩陣，然后按Ctrl + T，然后將表命名為“ Distance_Table”，然后選擇創(chuàng)建帶有表頭的表。接下來，您需要通過在單元格A1中鍵入名稱來命名您的第一列“測試ID”。

現(xiàn)在我們的表已經(jīng)設(shè)置好了，我們可以開始計算了。我們將從單元格B2開始，該單元格將計算訓(xùn)練集中的第一個點（ID#1）與測試集中的第一個點（ID#1）之間的距離。通過使用excel中的VLOOKUP函數(shù)，我們可以快速應(yīng)用歐幾里得距離公式，以找到每個尺寸的值，然后根據(jù)需要進(jìn)行計算。

最好將此公式復(fù)制并粘貼到單元格B2中的公式欄中，因為它可以處理Excel中表格功能的一些特殊功能，但請確保您了解此公式正在執(zhí)行的所有操作都是應(yīng)用我們前面討論的歐幾里德距離公式。按照編寫的內(nèi)容，您可以拖動它來填滿整個表格。

=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))

　　尋找最近的鄰居

在此階段，我們已經(jīng)計算出測試集中的每個點與訓(xùn)練集中的每個點之間的距離。現(xiàn)在，我們需要確定測試集中每個點最近的鄰居。創(chuàng)建一個名為“最近鄰居”的新工作表，并從A2開始逐行工作，以1至45的數(shù)字填充單元格，以與測試集中的點相對應(yīng)。我們的專欄將不會像以前的工作表那樣代表培訓(xùn)集。相反，它們將代表6個最近的鄰居，從第一個最近的鄰居開始，然后從第二個最近的鄰居開始，依此類推。第一個最近的鄰居具有最小的距離，第二個最近的鄰居具有第二個最小的距離，依此類推。您的工作表應(yīng)如下所示：

如前所述，我們將在單元格B2中編寫一個公式，可以拖動該公式以填充矩陣的其余部分。我們的方法是在距離表的相應(yīng)行（2）中標(biāo)識最小值，找到該值的列號，然后返回列名，因為這將為我們提供訓(xùn)練集中值的ID。

我們將結(jié)合使用Index和Match函數(shù)來實現(xiàn)此目的。請注意，我們能夠簡化此公式，因為我們具有遠(yuǎn)見卓識，可以將距離矩陣設(shè)置為Excel中的表格，因此我們可以輕松提取標(biāo)題。

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

拖動此公式以填充最近的鄰居矩陣的第一行。您將需要在SMALL()函數(shù)中手動調(diào)整粗體值以表示我們正在尋找的鄰居。因此，例如，要找到第二近鄰，公式將如下所示。

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

請記住，您的值將有所不同，因為用于形成測試集的隨機(jī)樣本與我的不同。

在此階段，我通常花一分鐘時間在可行的情況下手動仔細(xì)檢查其中一行，以確保我的公式能夠按預(yù)期工作。您需要大規(guī)模使用自動化測試，但現(xiàn)在我們將其保持簡單。

我們有最后一步：我們需要確定每個最近鄰居的分類。我們將回到B2中的公式，并對其進(jìn)行修改以對Training Set中的ID進(jìn)行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)

　　退后一步

讓我們退后一步，看看我們已經(jīng)完成了什么。現(xiàn)在，您已經(jīng)為測試集中的每個點確定了6個最近鄰居的分類。您可能會注意到，對于您的所有或幾乎所有數(shù)據(jù)點，最近的6個鄰居都將歸為同一類別。這意味著我們的數(shù)據(jù)集將他高度聚集。在我們的案例中，我們的數(shù)據(jù)高度聚類有兩個原因。

首先，正如我們在本教程開始時所討論的那樣，數(shù)據(jù)集設(shè)計為易于使用。其次，這是一個低維數(shù)據(jù)集，因為我們僅使用4維。當(dāng)您處理現(xiàn)實世界的數(shù)據(jù)時，通常會發(fā)現(xiàn)它的聚集程度要低得多，尤其是隨著維數(shù)的增加。數(shù)據(jù)的群集越少，構(gòu)建有用模型所需的訓(xùn)練集就越大。

通過機(jī)器學(xué)習(xí)進(jìn)行優(yōu)化

如果我們的數(shù)據(jù)始終與Iris數(shù)據(jù)集一樣整齊地聚類，則無需進(jìn)行機(jī)器學(xué)習(xí)。我們將使用公式簡單地找到最近的鄰居，然后使用該公式來確定每個未知數(shù)據(jù)點的分類。由于通常不是這種情況，因此機(jī)器學(xué)習(xí)可通過一次查看多個鄰居來幫助我們更準(zhǔn)確地預(yù)測未知數(shù)據(jù)點的分類。

但是，我們應(yīng)該看幾個鄰居？這就是“ K最近鄰”中的“ K”出現(xiàn)的地方。K描述了預(yù)測未知數(shù)據(jù)點分類時將考慮的鄰居數(shù)。

　　鄰居太多或太多

憑直覺，了解為什么這個問題很棘手很重要。可能會看到太多的鄰居，也可能會看到太多的鄰居。特別是隨著維數(shù)的增加，最近的鄰居可能并不總是正確的分類。看著很少的鄰居會限制您的模型可用于確定的信息量。

考慮到過多的鄰居實際上會降低模型用作輸入的信息的質(zhì)量。這是因為隨著引入更多鄰居，您也將噪聲引入數(shù)據(jù)中。試想一下在我們的示例中考慮所有104個鄰居都沒有道理。

因此，這成為經(jīng)典的優(yōu)化問題，我們試圖找到給出最多信息而又不會太高或太低的K值。

　　使用測試儀

在本教程中，我們將使用一個非常簡單的反復(fù)試驗過程來確定最佳K值。在繼續(xù)之前，我建議您查看一下“最近鄰居”工作表，并猜測一下最佳k值可能是什么，只是為了好玩。如果您是對的，我們會盡快找出答案！

　　設(shè)置算法

算法只是計算機(jī)根據(jù)定義的一組規(guī)則反復(fù)重復(fù)的一組步驟。在這種情況下，我們將告訴計算機(jī)嘗試不同的K值，使用我們的測試集計算每個錯誤的錯誤率，然后最終返回產(chǎn)生最低錯誤率的值。

為此，我們需要創(chuàng)建一個名為“ KNN模型”的新工作表。我們將如下設(shè)置，為每個測試數(shù)據(jù)點的A4至A48行標(biāo)記1至45。

讓我們從B列中的預(yù)測值開始。我們需要此公式根據(jù)K值進(jìn)行調(diào)整。在K值為1的情況下，公式很簡單，我們只取最近的鄰居。

=’Nearest Neighbors’!B2

在K值大于1的情況下，我們將采用出現(xiàn)的最常見的鄰居。如果鄰居的出現(xiàn)是均勻分布的，例如，當(dāng)K = 6時，如果3個鄰居是Setosa，而3個鄰居是Virginica，我們將以最近鄰居的分類為準(zhǔn)。

K = 2的公式如下。我們使用IFERROR，因為當(dāng)給定的K值有兩個鄰居發(fā)生相同次數(shù)時，此公式將返回錯誤。

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

您需要在B4單元格中使用下面的擴(kuò)展公式，該公式使您可以使用K值，包括K = 6。無需擔(dān)心此公式的細(xì)節(jié)，只需復(fù)制并粘貼即可。順便說一句，必須使用復(fù)雜，挑剔且難以理解的此類公式是我之前提到的Excel的局限之一。

這在Python中簡直就是小菜一碟。請注意，如果K中沒有值或1到6之間的值，則此公式將返回錯誤。您應(yīng)該從單元格B4的B列復(fù)制此公式。

=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列中設(shè)置一個公式，以在預(yù)測不正確或錯誤時返回1，在預(yù)測正確時返回0。您將在單元格D4中開始并將公式向下拖動。

=IF(B4=C4, 0, 1)

最后，我們將使用單元格B2中的此公式，通過將錯誤數(shù)除以數(shù)據(jù)點的總數(shù)來計算錯誤率。作為慣例，我們將其格式化為百分比。

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

　　運行算法

現(xiàn)在，我們準(zhǔn)備針對不同的K值運行算法。因為我們只測試6個值，所以我們可以手工完成。但這不會很有趣，更重要的是無法擴(kuò)展。您需要按照本文中的說明為Solver加載項啟用Excel ，然后再繼續(xù)。

現(xiàn)在，導(dǎo)航到“數(shù)據(jù)”功能區(qū)，然后單擊“求解器”按鈕。求解器按鈕會根據(jù)我們的說明為我們自動進(jìn)行反復(fù)試驗。您將有一個對話框，其中包含要設(shè)置的參數(shù)或說明，如下所示。

我們對其進(jìn)行設(shè)置，以便在測試1到6之間的值時尋求最小化錯誤率。

Excel將旋轉(zhuǎn)一分鐘，您可能會看到它在顯示此對話框之前在屏幕上閃爍了一些值。您應(yīng)該單擊確定以保留求解器解決方案。

　　解釋錯誤率和解決方案

由于數(shù)據(jù)具有多個最小值或最大值，因此許多優(yōu)化算法具有多種解決方案。以我為例。實際上，在我的特定情況下，所有整數(shù)值1到6都表示最小值，錯誤率約為2%。那么，我們現(xiàn)在該怎么辦？

有幾件事貫穿我的腦海。首先，這個測試集不是很好。該模型沒有從測試集中獲得任何優(yōu)化優(yōu)勢，因此，我可能會重新做測試集，然后再次嘗試查看是否得到不同的結(jié)果。我還會考慮使用更復(fù)雜的測試方法，例如交叉驗證。

在我的測試集中如此低的錯誤率下，我也開始擔(dān)心過度擬合。當(dāng)模型過于適合特定訓(xùn)練或測試數(shù)據(jù)集的細(xì)微差別時，過度擬合是機(jī)器學(xué)習(xí)中出現(xiàn)的問題。當(dāng)模型過度擬合時，在野外遇到新數(shù)據(jù)時，它就不會具有預(yù)測性或有效性。

當(dāng)然，使用這樣的學(xué)術(shù)數(shù)據(jù)集，我們希望我們的錯誤率相當(dāng)?shù)汀?/p>

下一個考慮因素是，如果我確定了幾個最小值，應(yīng)選擇哪個值。盡管該測試在此特定示例中無效，但通常我會選擇最少的鄰居，該鄰居數(shù)量最少，以節(jié)省計算資源。如果必須考慮較少的鄰居，我的模型將運行得更快。較小的數(shù)據(jù)集不會有任何影響，但是這樣的決策可以節(jié)省大量的資源。

以上就是關(guān)于如何使用Microsoft Excel對數(shù)據(jù)進(jìn)行分類的全部內(nèi)容，想了解更多關(guān)于 Excel數(shù)據(jù)分析的信息，請繼續(xù)關(guān)注中培偉業(yè)。