學不完．教不停．用不盡

網友提問一個有趣的題目：在 Excel 的資料表中，如何根據互相指定同房人員來自動分配房間？

參考下圖，B欄是每個人的姓名，C欄是這個人指定的同房人員，根據這兩欄的資訊要自動分好房間，讓指定的兩人自動分在同一間。

【公式設計與解析】

為了讓讀者更清楚公式，所以使用了輔助欄位。

1. 找出A的同房者B所指定的同房人員C

儲存格D2：=OFFSET($B$1,MATCH(C2,$B$2:$B$23,0),1)

(1) MATCH(C2,$B$2:$B$23,0)

利用 MATCH 函數，找出儲存格C2在儲存格範圍B2:B23的位置，傳回一個數值。

(2) OFFSET($B$1,MATCH(C2,$B$2:$B$23,0),1)

利用 OFFSET 函數根據第(1)式傳回的位置找到對應的儲存格內容（人員C）。

2. 判斷人員C是否和人員A相同

判斷人員C是否和人員A相同，若相同，表示互相指定（同房），若不相同，表示未互相指定（不能同房）

儲存格E2：=IFERROR(IF(D2=B2,"V","X "&B2),"")

若傳回『V』，表示互相指定（同房），若傳回『Ｘ』，表示未互相指定（不能同房）。

若有錯誤訊息，則以 IFERROR 函數將其顯示為空白。

3. 找出人員A和人員B的位置，並取最小值

儲存格F2：

=IF(E2="V",MIN(MATCH(B2,$B$2:$B$23,0),MATCH(C2,$B$2:$B$23,0)),"")

(1) MATCH(B2,$B$2:$B$23,0)

找出儲存格B2內容在儲存格B2:B23中的位置，傳回一個數值。

(2) MATCH(C2,$B$2:$B$23,0)

找出儲存格C2內容在儲存格C2:CB23中的位置，傳回一個數值。

(3) MIN(MATCH(B2,$B$2:$B$23,0),MATCH(C2,$B$2:$B$23,0))

比較第(1)式和第(2)式的傳回值，並以 MIN 函數取其最小值。

4. 執行分房作業

儲存格G2：=IF(F2<>"","A0"&COUNTIF($F$2:$F$23,"<="&F2)/2,"")

假設房間號碼是A01~A20。

COUNTIF($F$2:$F$23,"<="&F2)/2：利用 COUNTIF 函數計算由第一個儲存格F2起始的儲存格範圍內和儲存格F2相同者的『數量』。再根據這個數量來產生房間的編號。