2015年12月25日 星期五

Excel-利用下拉式選單挑選名字後自動顯示照片(OFFSET,MATCH,使用名稱)

在 Excel 檔案中用來計算與分析資料大多以表格和統計圖表呈現,如果有要用到呈現圖片,都需要靠手動方式來設定。這次來練習一個有趣的例子,如何才能做到像下圖中,輸入一個姓名,即能查詢這個人的照片?
在下圖中的例子,希望能選取一個人的姓名後,能自動顯示這個人的照片和計算其BMI值。
Excel-利用下拉式選單挑選名字後自動顯示照片(OFFSET,MATCH,使用名稱)

【公式設計與解析】
假設資料放在儲存格A1:E20。
1. 選取儲存格B1:B20,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名。
2. 選取儲存格G1,選取[資料/資料驗證]功能表中的「資料驗證」。
3. 設定資料驗證準則,儲存格內允許:清單;來源:=姓名。(姓名為先前已定義的名稱)
Excel-利用下拉式選單挑選名字後自動顯示照片(OFFSET,MATCH,使用名稱)
4. 在[公式/已定義名稱]功能表中選取「名稱管理員」,新增「image」名稱,其參照:
=OFFSET(工作表1!$C$1,MATCH(工作表1!$G$1,姓名,0),)
利用儲存格G1,在姓名範圍內比對,傳回所在位置。再透過 OFFSET 函數,找到這個姓名在相片欄位的儲存格位置。
Excel-利用下拉式選單挑選名字後自動顯示照片(OFFSET,MATCH,使用名稱)
5. 將儲存格C2的照片複製到儲存格G2中。
6. 選取儲存格G2中的照片,在公式編輯列中輸入「=image」。(在步驟定義的名稱)
Excel-利用下拉式選單挑選名字後自動顯示照片(OFFSET,MATCH,使用名稱)
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值。

沒有留言:

張貼留言

好康東東