Excel中的資料集通常在一列中多次包含相同的值。有時,瞭解一列中有多少個唯一值會很有用。例如,如果您執行一個商店並擁有一個包含所有事務的電子表格,那麼您可能需要確定您擁有多少獨特的客戶,而不是計算每個事務。
透過使用下面我們將向您介紹的方法計算Excel中的唯一值,可以做到這一點。
計算Excel中唯一值的一種快速而骯髒的方法是刪除重複項並檢視剩餘的條目數。這是一個很好的選擇,如果你需要一個答案很快,不需要跟蹤結果。
將資料複製到新的工作表中(這樣您就不會意外地刪除任何需要的資料)。選擇要從中刪除重複值的值或列。在“資料”選項卡的“資料工具”部分中,選擇“刪除重複項”。這將刪除所有重複的資料,只留下唯一的值。
如果在兩列之間拆分資訊,則相同的過程也會起作用。不同之處在於需要同時選擇這兩列。在我們的示例中,名字有一列,姓氏有第二列。
如果要跟蹤唯一值的數量,最好編寫一個公式。我們將在下面向您展示如何做到這一點。
相關:如何在Excel中過濾以顯示所需的資料
為了只計算唯一的值,我們必須組合幾個Excel函式。首先,我們需要檢查每個值是否重複,然後我們需要計算剩餘的條目。我們還需要使用陣列函式。
如果您只是在尋找答案,請使用以下公式,將A2:A13的每個例項替換為要使用的單元格:
{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}我們怎麼到那裡有點複雜。所以,如果你想理解為什麼這個公式是有效的,我們將在下面一次分解一部分。
我們先來解釋陣列是什麼。陣列是包含多個值的單個變數。這就像一次引用一堆Excel單元格,而不是單獨引用每個單元格。
從我們的角度來看,這是一個奇怪的區別。如果我們告訴一個公式以正常方式或陣列的形式檢視單元格A2:A13,那麼資料在我們看來是相同的。區別在於Excel如何在幕後處理資料。這是如此微妙的差別,以至於最新版本的Excel甚至不再區分它們,儘管舊版本確實如此。
就我們的目的而言,更重要的是知道如何使用陣列。如果您有最新版本的Excel,它會自動將資料儲存為一個數組,這樣做效率會更高。如果您使用的是舊版本,則在編寫完公式後,按Ctrl+Shift+Enter。一旦你這樣做了,公式將被大括號包圍以顯示它處於陣列模式。
FREQUENCY函式告訴我們一個數字在列表中出現的次數。這是偉大的,如果你與數字,但我們的名單是文字。要使用這個函式,我們必須先找到一種將文字轉換為數字的方法。
如果要計算數字列表中的唯一值,可以跳過下一步。
MATCH函式返回值第一次出現的位置。我們可以用它把名字列表轉換成數值。它需要知道三條資訊:
在我們的示例中,我們希望在Exel電子表格中查詢每個客戶的名字,看看他們的確切名字是否會再次出現在其他地方。
在上面的例子中,我們正在搜尋Tiah Gallagher(A2)的列表(A2:A13),我們想要一個精確的匹配。最後一個欄位中的0指定它必須完全匹配。我們的結果告訴我們名字最先出現在列表的什麼地方。在本例中,它是第一個名稱,因此結果是1。
問題是我們對所有的客戶都感興趣,而不僅僅是蒂亞。但是,如果我們試圖搜尋A2:A13而不是僅僅搜尋A2,我們會得到一個錯誤。這就是陣列函式很方便的地方。第一個引數只能接受一個變數,否則返回錯誤。但是,陣列被視為單個變數。
現在我們的函式告訴Excel檢查整個陣列是否匹配。但是等等,我們的結果沒有改變!上面還寫著1。這是怎麼回事?
我們的函式是返回一個數組。它遍歷陣列中的每個項並檢查匹配項。所有名稱的結果都儲存在一個數組中,並作為結果返回。因為一個單元格一次只顯示一個變數,所以它顯示的是陣列中的第一個值。
你可以自己檢查一下。如果將第一個範圍更改為A3:A13,結果將更改為2。這是因為Eiliyah的名字在列表中位於第二位,而這個值現在儲存在陣列中的第一位。如果將第一個範圍更改為A7:A13,則會再次得到1,因為Tiah的名字首先出現在我們要檢查的資料集的第一個位置。
相關:Excel公式,將幫助你解決現實生活中的問題
現在我們已經將名稱改為數值,我們可以使用FREQUENCY函式。與MATCH類似,它需要一個要查詢的目標和一個要檢查的資料集。同樣,為了匹配,我們不想只尋找一個值,我們希望函式檢查列表中的每一項。
我們希望FREQUENCY函式檢查的目標是MATCH函式返回的陣列中的每個項。我們要檢查MATCH函式返回的資料集。因此,我們傳送上面為這兩個引數建立的匹配函式。
如果您正在尋找唯一的數字,並且跳過了上一步,那麼您可以將數字的範圍作為兩個引數傳送。要搜尋列表中的所有數字,還需要使用陣列函式,因此如果使用的是舊版本的Excel,請記住在輸入公式後按Ctrl+Shift+Enter。
現在我們的結果是2。同樣,我們的函式返回一個數組。它將返回每個唯一值出現的次數陣列。單元格正在顯示陣列中的第一個值。在本例中,Tiah的名字出現了兩次,因此返回的頻率是2。
現在,我們的陣列具有與唯一值相同的值數。但我們還沒完成。我們需要一個方法把這些加起來。如果我們將陣列中的所有值都轉換為1,然後求和,那麼我們最終就會知道有多少個唯一的值。
我們可以建立一個IF函式,將所有大於0的值都更改為1。那麼所有的值都等於1。
為此,我們希望IF函式檢查頻率陣列中的值是否大於零。如果為true,則應返回值1。您將注意到,現在陣列中的第一個值返回為1。
我們已經進入最後階段了!最後一步是對陣列求和。
將上一個函式包裝為求和函式。完成了!所以我們最後的公式是:
{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}這是一個高階函式,需要大量的Excel知識。嘗試可能會讓人害怕。但是,一旦設定好了,它會非常有用,所以透過我們的解釋來確保您理解它可能是值得的。
如果您不需要經常計算唯一的條目,那麼快速而骯髒的刪除重複值的技巧將在緊要關頭起作用!
... 如果你知道如何使用Excel的話,它有很強的統計能力。下面我們來看看一些最基本的統計計算。我們開始吧! ...
如果你能在Excel中計算百分比,那就很方便了。例如,您可能需要計算銷售稅,或者上個月到本月銷售變化的百分比。你就是這麼做的! 計算增加百分比 首先,讓我們計算一個值相對於另一個值的增加百分比。 在這個例子中...
...考慮到每個值的重要性或權重的平均數。本文將向您展示如何單獨使用Excel的SUMPRODUCT和SUM函式,以及如何將兩者結合起來計算加權平均值。 什麼是加權平均數(a weighted average)? 加權平均數是一種考慮到每個值的重要性或權重的...
方差是對資料集中數字之間的分佈的一種度量。方差度量集合中每個數字離平均值的距離。 使用資料集圖表,我們可以觀察到各種資料點或數字之間的線性關係。我們透過繪製回歸線來實現這一點,回歸線試圖最小化任何單個...
我們很多人只需要一個計算器來計算簡單的興趣。你只需將日利率乘以本金,再乘以兩次付款之間的天數。 但是,當涉及到利息時,計算更為複雜,即本金和累計利息的累計利息。Excel電子錶格可以為您處理這項工作,前提是...
...回報率乘以資產貝塔繫數的差額。此公式可在Microsoft Excel中計算,如下所示。 瞭解capm 資本資產定價模型僅提供集中資產的預期回報。當投資者考慮投資時,這種預期回報可能是一個重要的價值。一般來說,預期收益與用來...
...項投資在一定時期內的回報率,以年百分比表示。下麵是如何手工計算和使用microsoftexcel計算的概述。 什麼是複合年增長率(cagr)? 但首先,讓我們來定義我們的術語。考慮複合年增長率最簡單的方法是認識到,經過若干年的...
...資回報。因此,目標不應該是最大化凈現值。 4:20 如何在Excel中計算內部收益率 什麼是凈現值(net present value)? 凈現值是現金流入現值和現金流出現值隨時間變化的差值。 專案的凈現值取決於所使用的折現率。所以在...