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

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

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

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

=RAND()

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

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

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

否則，我們會(huì)將值添加到測試集中。同樣，我們將從H2開始，然后向下拖動(dòng)到最后一個(gè)數(shù)據(jù)點(diǎn)。

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

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

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

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

　　建立模型

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

距離的概念

距離是數(shù)學(xué)家確定n維空間中最相似點(diǎn)的方式。直覺是，點(diǎn)之間的距離越小，它們?cè)较嗨啤Ｎ覀兇蠖鄶?shù)人習(xí)慣于在二維空間中計(jì)算距離，例如x，y坐標(biāo)系或使用經(jīng)度和緯度。

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

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

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

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

計(jì)算距離

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

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

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

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

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

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

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

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

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

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

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

請(qǐng)記住，您的值將有所不同，因?yàn)橛糜谛纬蓽y試集的隨機(jī)樣本與我的不同。

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

我們有最后一步：我們需要確定每個(gè)最近鄰居的分類。我們將回到B2中的公式，并對(duì)其進(jìn)行修改以對(duì)Training Set中的ID進(jìn)行VLOOKUP，然后返回分類。然后，我們將其拖動(dòng)以填充矩陣。

=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)為測試集中的每個(gè)點(diǎn)確定了6個(gè)最近鄰居的分類。您可能會(huì)注意到，對(duì)于您的所有或幾乎所有數(shù)據(jù)點(diǎn)，最近的6個(gè)鄰居都將歸為同一類別。這意味著我們的數(shù)據(jù)集將他高度聚集。在我們的案例中，我們的數(shù)據(jù)高度聚類有兩個(gè)原因。

首先，正如我們?cè)诒窘坛涕_始時(shí)所討論的那樣，數(shù)據(jù)集設(shè)計(jì)為易于使用。其次，這是一個(gè)低維數(shù)據(jù)集，因?yàn)槲覀儍H使用4維。當(dāng)您處理現(xiàn)實(shí)世界的數(shù)據(jù)時(shí)，通常會(huì)發(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í)。我們將使用公式簡單地找到最近的鄰居，然后使用該公式來確定每個(gè)未知數(shù)據(jù)點(diǎn)的分類。由于通常不是這種情況，因此機(jī)器學(xué)習(xí)可通過一次查看多個(gè)鄰居來幫助我們更準(zhǔn)確地預(yù)測未知數(shù)據(jù)點(diǎn)的分類。

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

　　鄰居太多或太多

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

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

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

　　使用測試儀

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

　　設(shè)置算法

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

為此，我們需要?jiǎng)?chuàng)建一個(gè)名為“ KNN模型”的新工作表。我們將如下設(shè)置，為每個(gè)測試數(shù)據(jù)點(diǎn)的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時(shí)，如果3個(gè)鄰居是Setosa，而3個(gè)鄰居是Virginica，我們將以最近鄰居的分類為準(zhǔn)。

K = 2的公式如下。我們使用IFERROR，因?yàn)楫?dāng)給定的K值有兩個(gè)鄰居發(fā)生相同次數(shù)時(shí)，此公式將返回錯(cuò)誤。

=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中簡直就是小菜一碟。請(qǐng)注意，如果K中沒有值或1到6之間的值，則此公式將返回錯(cuò)誤。您應(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))

接下來，我們想獲取每個(gè)測試點(diǎn)的實(shí)際已知分類，以便我們可以確定我們的模型是否正確。為此，我們?cè)贑列中使用快速VLOOKUP，從C4單元格開始向下拖動(dòng)。

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

然后，我們將在D列中設(shè)置一個(gè)公式，以在預(yù)測不正確或錯(cuò)誤時(shí)返回1，在預(yù)測正確時(shí)返回0。您將在單元格D4中開始并將公式向下拖動(dòng)。

=IF(B4=C4, 0, 1)

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

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

　　運(yùn)行算法

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

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

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

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

　　解釋錯(cuò)誤率和解決方案

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

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

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

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

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

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