學不完．教不停．用不盡

在 Excel 檔案中用來計算與分析資料大多以表格和統計圖表呈現，如果有要用到呈現圖片，都需要靠手動方式來設定。這次來練習一個有趣的例子，如何才能做到像下圖中，輸入一個姓名，即能查詢這個人的照片？

在下圖中的例子，希望能選取一個人的姓名後，能自動顯示這個人的照片和計算其BMI值。

 

【公式設計與解析】

假設資料放在儲存格A1:E20。

1. 選取儲存格B1:B20，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：姓名。

2. 選取儲存格G1，選取[資料/資料驗證]功能表中的「資料驗證」。

3. 設定資料驗證準則，儲存格內允許：清單；來源：=姓名。(姓名為先前已定義的名稱)

4. 在[公式/已定義名稱]功能表中選取「名稱管理員」，新增「image」名稱，其參照：

=OFFSET(工作表1!$C$1,MATCH(工作表1!$G$1,姓名,0),)

利用儲存格G1，在姓名範圍內比對，傳回所在位置。再透過 OFFSET 函數，找到這個姓名在相片欄位的儲存格位置。

5. 將儲存格C2的照片複製到儲存格G2中。

6. 選取儲存格G2中的照片，在公式編輯列中輸入「=image」。(在步驟定義的名稱)

7. 在儲存格H2中輸入求BMI的公式：

儲存格H2：=ROUND(OFFSET(E1,MATCH(G1,姓名,0),)/(OFFSET(D1,
MATCH(G1,姓名,0),)/100)^2,1)

其中，

MATCH(G1,姓名,0)：由儲存格G1求得在姓名欄位中的位置。

(1) OFFSET(E1,MATCH(G1,姓名,0),)：透過 OFFSET 函數以相對位置求得體重。

(2) (OFFSET(D1,MATCH(G1,姓名,0),)/100)^2：透過 OFFSET 函數以相對位置求得身高。再將身高除以 100，再取平方數。

(3) BMI = (1)式／(2)式

最後使用 ROUND 函數將 BMI 設定顯示 2 位的小數點位數。

透過下拉式選單，你可以依姓名查詢其照片和BMI值。