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

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

Excel-指定同房者的分房作業(MATCH,OFFSET)

 

【公式設計與解析】

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

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』,表示互相指定(同房),若傳回『X』,表示未互相指定(不能同房)。

若有錯誤訊息,則以 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相同者的『數量』。再根據這個數量來產生房間的編號。

arrow
arrow
    文章標籤
    Excel MATCH OFFSET
    全站熱搜

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