擔(dān)任人事行政、庫管、產(chǎn)品上貨管理等的小伙伴們在工作中常常需要動態(tài)查詢?nèi)藛T、產(chǎn)品信息。今天我們就來做一套包含照片的動態(tài)查詢系統(tǒng)，可以查到人員姓名、出生日期、職位、照片等等。

?

我們先用PS將下圖中的照片，批量裁剪成統(tǒng)一的大小，讓圖片尺寸保持為140x140像素。

?

裁剪后的效果如下：

?

第一步：制作數(shù)據(jù)源表并根據(jù)姓名批量插入圖片

數(shù)據(jù)源部分的基礎(chǔ)數(shù)據(jù)可以錄入，也可以直接引用其他表，操作簡單，不贅述。這里只說難點(diǎn)：根據(jù)姓名或者產(chǎn)品名稱批量插入圖片。

如圖，表格里已經(jīng)列好了姓名和相關(guān)信息，在桌面照片文件夾已經(jīng)配置好圖片，圖片的名稱跟姓名一一對應(yīng)。

?

?

現(xiàn)在要在照片這列根據(jù)姓名插入照片，如果手動一張張插入是非常麻煩的，那有什么方法可以快速完成呢?操作步驟如下：

（1）在G2輸入：

?

然后快速填充整個G列。此公式的目的就是獲取A列的姓名，生成對應(yīng)照片的鏈接地址，并與寬高屬性一起形成完整的表格中照片的代碼。

解析：

這里的公式實(shí)際是一句網(wǎng)頁代碼。Excel支持部分網(wǎng)頁代碼。

“table”是表格標(biāo)簽;“img”是圖片（image）標(biāo)簽，其后的“src”屬性用于指定圖片的位置。

“ ”是存放照片的文件夾路徑。實(shí)際操作中根據(jù)照片的實(shí)際路徑修改。

“A2”是姓名單元格，也就是照片名稱。

“width”和 “height”，圖片的寬高屬性，用于指定圖片在EXCEL中顯示的寬度和高度，具體操作大家可以根據(jù)實(shí)際情況進(jìn)行調(diào)整。

（2）把G列的公式復(fù)制粘貼到記事本，然后清空G列的公式。

?

（3）復(fù)制記事本中的所有內(nèi)容，然后在G2單元格選擇性粘貼為Unicode文本。

?

（4）點(diǎn)擊確定按鈕，照片就批量導(dǎo)入到表格里了。

?

注意：如果姓名有變動的話，照片不能隨之更新。

第二步：完成動態(tài)查詢系統(tǒng)

我們根據(jù)上一步驟完成帶有圖片的數(shù)據(jù)源，做一個動態(tài)查詢檔案，輸入姓名即可查詢到照片、性別、出生日期等。做好了之后是這樣的：

?

怎么操作呢？步驟如下：

（1）首先創(chuàng)建以下表格。

?

（2）在姓名對應(yīng)的B2單元格輸入“吳磊”。

?

（3）接下來“性別”“出生年月”等其他信息的獲取，我們根據(jù)姓名“吳磊”采用一個公式來完成。在性別對應(yīng)的B4單元格輸入

=IFERROR(OFFSET(數(shù)據(jù)源!$A$1,MATCH($B$2,數(shù)據(jù)源!$A:$A,0)-1,MATCH(查詢!A4,數(shù)據(jù)源!$1:$1,0)-1),"")

?

解析：

MATCH（查找內(nèi)容，查找區(qū)域，0）：表示查找第一個參數(shù)在第二個參數(shù)的位置，第三個參數(shù)為0代表精確匹配。這里分別返回的是B2單元格“吳磊”在數(shù)據(jù)源A列（姓名列）對應(yīng)的位置6和A4單元格“性別”在數(shù)據(jù)源第1行（標(biāo)題行）對應(yīng)的位置2。

OFFSET（參照位置，偏移的行位置，偏移的列位置）：表示以第一個參數(shù)為位置參照，偏移到第二參數(shù)定義的行數(shù)和第三參數(shù)定義的列數(shù)所在的單元格，返回其值。這里的含義是以“數(shù)據(jù)源”表里的A1單元格為準(zhǔn)，向下偏移6-1行向右偏移2-1列，獲取到B5單元格值“男”。

在上述OFFSET函數(shù)中，如果B2單元格為空，則返回錯誤信息“N/A”。我們利用IFERR0R函數(shù)，當(dāng)單元格返回錯誤“N/A”則輸出為空值。

因?yàn)楹罄m(xù)還要查詢“出生年月”“星座”等，所以公式中“查詢!A4”這個是相對引用，其他都采用了絕對引用。

然后把這個公式復(fù)制應(yīng)用到“出生年月”“星座”等對應(yīng)的單元格里。注意修改相對引用項(xiàng)。

?

（4）接下來我們要把圖片動態(tài)引用過來。

單擊【公式】選項(xiàng)卡下的名稱管理器旁邊的“定義名稱”。

?

在在彈出的對話菜單中，【名稱】處輸入“照片”，【引用位置】輸入公式：

=INDEX(數(shù)據(jù)源!$G:$G,MATCH(查詢!$B$2,數(shù)據(jù)源!$A:$A,0)

?

解析：

MATCH：表示查找第一個參數(shù)，也就是姓名“吳磊”單元格在第二個參數(shù)數(shù)據(jù)源姓名列的位置，返回6。

INDEX（數(shù)據(jù)區(qū)域，數(shù)據(jù)位置）：表示用第二個參數(shù)給出的位置在第一個參數(shù)中查找對應(yīng)的值。

上述公式的意思就是利用INDEX函數(shù)返回?cái)?shù)據(jù)源G列（圖片列）中對應(yīng)行號（由MATCH函數(shù)獲取）位置的圖片。

（5）復(fù)制數(shù)據(jù)源表任意一張照片，粘貼到“查詢”表的D2單元格。單擊該照片，在編輯欄中輸入公式：=照片，點(diǎn)擊Enter。

?

這樣當(dāng)B2單元格輸入姓名后點(diǎn)擊確定，對應(yīng)的照片和其他信息就會一起動態(tài)更新了。

注意：使用這種方法時，當(dāng)姓名為空的時候或者姓名錯誤的時候，仍然會顯示上一次操作之后的照片。