2015年9月28日 星期一

Excel-以合計不超過某一數值為一組來分組(OFFSET,MATCH,COLUMN)

網友在 Excel 中有一個資料表如下圖中的A欄和B欄,想要根據項次和數值的內容,將合計不超過某一數值者(本例為:300)分成一組,並且依項次由小至大分組。其次,再將各組的項次列出。以上兩個問題,該如何處理?
觀察下圖,項次 A01~A06 的合計為 285,若再加上 A07,會超過 300,所以將A01~A06 分在第1組。而 A07 為第 2 組的第 1 個,A07~A11 合計為 222,若再加上A12 的 96,會超過 300,所以將 A07~A11 分為第 2 組,依此類推。

【公式設計與解析】
本例以累不超過 300 者為一組。
1. 計算分組累計
儲存格C2:=B1
儲存格C3:=IF((C2+B3)>300,B3,C2+B3)
複製儲存格C3,往下各列貼上。
2. 找出組別
儲存格D2:=1
儲存格D3:=IF((C2+B3)>300,D2+1,D2)
複製儲存格D3,往下各列貼上。
3. 列出每組分項內容
先將D欄中有資料的範圍,定義名稱:組別。
儲存格G2:=IF(COLUMN(A:A)<=COUNTIF(組別,$F2),OFFSET($A$1,MATCH($F2,組別,0)+COLUMN(A:A)-1,,,),"")
(1) MATCH($F2,組別,0)
找出各組的第一個項目所在的列號。
(2) OFFSET($A$1,MATCH($F2,組別,0)+COLUMN(A:A)-1,,,)
根據各組第一個列號,依序列出各組的項目。其中,COLUMN(A:A)=1 在向右複製時,會產生 COLUMN(A:A)=1→COLUMN(B:BA)=2→COLUMN(C:CA)=3→...。
透過 OFFSET 函數將直式(由上而下)的資料轉換為橫式(由左而右)顯示。
(3) IF(COLUMN(A:A)<=COUNTIF(組別,$F2), 第(2)式 ,"")
如果項目所在欄號(A欄=1、B欄=2、C欄=3、...)大於該組的數量,則以空白顯示。
最後,複製儲存格G2,貼至儲存格G2:L16。

沒有留言:

張貼留言

檢視其他文章

好康東東