學不完．教不停．用不盡

網友在 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。