贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

如果你想要運用 Excel 來練習資料處理,而找不到可利用的範例,可以進到政府資料開放平臺來取資料。政府資料開放平臺(https://data.gov.tw)提供了各類型的資料供民眾可以下載使用。

Excel-利用政府資料開放平臺練習資料處理

這個平台的各種資料使用了各式各樣的格式,例如:CSV、PDF、TXT等。

Excel-利用政府資料開放平臺練習資料處理

隨意取一個資料,例如:學生體重平均值(6歲-15歲),其提供了「CSV」格式的檔案。下載這個CSV檔。

Excel-利用政府資料開放平臺練習資料處理

開啟這個 CSV 檔,其內容如下圖:

Excel-利用政府資料開放平臺練習資料處理

先刪除不要的資料:

Excel-利用政府資料開放平臺練習資料處理

利用其提供的資料,轉換為下圖右:

儲存格G2:=SUMPRODUCT((學年度=$F3)*(年齡=$G$1)*INDIRECT(G$2))

Excel-利用政府資料開放平臺練習資料處理

另一種轉換:

儲存格G2:=SUMPRODUCT((年齡=$F3)*(學年度=$G$1)*INDIRECT(G$2))

Excel-利用政府資料開放平臺練習資料處理

再轉換為另一種格式:

儲存格G2:=SUMPRODUCT((年齡=$F3)*(學年度=G$2)*INDIRECT($G$1))

Excel-利用政府資料開放平臺練習資料處理

這些數據應該都是真實的,但不一定是最新的。不過,隨意取來練習資料處理,說不定可以讓你分析出令人意外的結果啊!

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

在 Excel 的工作表裡有一個資料表,如何判斷同一列中是否有重覆的內容?

參考下圖,資料的第一列中的「AAAA」有重覆出現,則判定為「O」;若沒有重覆出現,則判定為「X」。

Excel-找出儲存格範圍內是否有儲存格重覆(SUMPRODUCT,COUNTA,COUNT)

 

【公式設設與解析】

儲存格F2:

=IF(SUMPRODUCT(COUNTIF(A2:D2,A2:D2))>COUNTA(A2:D2),"O","X")

複製儲存格F2,貼至儲存格F2:F9。

(1) COUNTIF(A2:D2,A2:D2)

SUMPRODUCT 函數可以執行陣列運算,此公式可以找出儲存格A2:D2裡,每個儲存格內容的數量。傳回陣列:{3, 3, 1, 3}。

(2) SUMPRODUCT(COUNTIF(A2:D2,A2:D2))

SUMPRODUCT 函數裡將第(1)式的內容予以加總,本例為:3+3+1+3=10。

(3) IF(第(2)式>COUNTA(A2:D2),"O","X")

COUNTA(A2:D2)為計算儲存格的數量。若第(2)式的傳回值大於儲存格的數量,則必有至少2個儲存格內容重覆。

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

參考下圖,在 Excel 中有一個報名日期和報名人員的資料表(下圖左),由於每個人員有多次報名,如何找出每一個人員的最後一次報名日期(下圖右)?

Excel-找出日期清單中每個人員最後報名日期(OFFSET,MAX,SUBSTITUTE)

 

【公式設計與解析】

儲存格E2:{=OFFSET($A$1,MAX((SUBSTITUTE($B$2:$B$11,D2,"")<>
$B$2:$B$11)*ROW($B$2:$B$11))-1,0)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格E2,貼至儲存格E2:E11。

(1) SUBSTITUTE($B$2:$B$11,D2,"")

陣列公式中,利用 SUBSTITUTE 函數將每一個儲存格內容,含有儲存格D2內容者,全部置換成空字串。

(2) SUBSTITUTE($B$2:$B$11,D2,"")<>$B$2:$B$11)

判斷第(1)式的傳回結果和原來儲存格陣列內容是否相符,傳回 TRUE/FALSE 陣列。

本例儲存格D2的內容為「甲」,所以傳回 FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE

(3) 第(2)式*ROW($B$2:$B$11)

將第(2)式乘上儲存格陣列中每一個儲存格的列號(例如:ROW(B2)=2、ROW(B3)=3、...、ROW(B11)=11),在運算過程中 TRUE/FALSE 陣列會轉換為 1/0 陣列。

所以,傳回的結果即為含有儲存格D2內容的列號。本例結果傳回 0, 3, 0, 0, 0, 0, 0, 0, 0, 11。

(4) MAX(第(3)式)

利用 MAX 函數將第(3)式傳回的列號取其中的最大值。

(5) OFFSET($A$1,第(4)式-1,0)

將第(4)式傳回的列號最大值,代入 OFFSET 函數求得在第A欄中對應的日期。

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼