在 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值。
留言列表