2014年5月21日 星期三

『Excel』使用函數取得不重複值並計算總計

有沒有使用 Excel 輸入資料後,想要把某一列的資料做不重複的篩選?若您剛好有這個疑問,來到這裡算是走對地方了~ 我們有很簡單的例子一步一步的說明

前提

不採取樞扭分析表,請利用函數實作  (迷之聲:樞紐拉一拉不是很快嘛!!)

說明

今天有一個資料表欄位有「類型」、「數量」、「名稱」,需要做一些統計,並且在一般資料區塊下方,當有輸入時,必須同時同步計算以下兩項區域

  1. 依照類型將名稱分類,表格呈現上方列為「類型」、左方為「名稱」
  2. 取得不重複的「名稱」,計算各類型的名稱的總數

先定義實作目標

看到這邊,我們先將把所需要的欄位先定義清楚,主要分為三個部分,分別是資料區(A)、分類區(B)、統計數量區(C)

「資料區」

首先是資料區,這裡是一般的資料,也就是會一直在輸入的地方,也預備要讓其他B、C區做自動計算,欄位這麼放

A:輸入「名稱」
B:輸入「數量」
C:輸入「類型」

就像這樣子。

「分類區」

接下來是分類區,欄位這麼放
E:「名稱」
特別注意的是名稱雖然都來自資料區,可是並不能利用一般複製貼上就貼過去,我們可以利用函數,讓他自動把值帶過來,請輸入以下函數:
=IF(A1>0,A1,"") 
F、G、H:「類型」,函數分別是

         F 欄  =IF(C1="A",B1,"")
         G 欄  =IF(C1="B",B1,"")
         H 欄  =IF(C1="C",B1,"")

「統計數量區」

然後,統計數量區,就是計算各種不同分類的不重複水果數量有多少,以下就是各欄位輸入的函數

J 欄
重點就是在這一段,我們要將資料區的資料,採取不重複的做法呈現,換句話說,當使用者輸入資料區的時候,有出現重複時也只能算是一筆
=INDEX(A:A,SMALL(IF(MATCH($A$1:$A$5000&"",$A$1:$A$5000&"",0)=ROW($A$1:$A$5000),ROW($A$1:$A$5000),4^8),ROW(A1)))&""
         
K 欄
這一欄就是要將A類的水果總計起來,我們還需要借用分類區的地方把數據提取出來
=IF($J1>0,SUMIF($E$1:$E$5000,$J1,$F$1:F$5000),"")

L 欄
這一欄就是要將B類的水果總計起來,我們還需要借用分類區的地方把數據提取出來
=IF($J1>0,SUMIF($E$1:$E$5000,$J1,$G$1:$H$5000),"")

M 欄
這一欄就是要將C類的水果總計起來,我們還需要借用分類區的地方把數據提取出來
=IF($J1>0,SUMIF($E$1:$E$5000,$J1,$H$1:$H$5000),"")

後記


使用者輸入的時候,主要輸入資料區就可以了,其他兩個區域都會立即做計算
請各位自行動手做吧,按照上面的步驟之後會看到神奇的效果咧

收工



沒有留言:

張貼留言