大家好,我是爱踢汪。今天又给大家送上一波福利。担任人事行政、库管、产品上货管理等的小伙伴们在工作中常常需要动态查询人员、产品信息。今天我们就来做一套包含照片的动态查询系统,可以查到人员姓名、出生日期、职位、照片等等。
![cc3dfb0ccffaa891e5fa7632226b91a7.gif](https://i-blog.csdnimg.cn/blog_migrate/dbe212c4f5728887b55608eccf55eedc.gif)
我们先用PS将下图中的照片,批量裁剪成统一的大小,让图片尺寸保持为140x140像素。
![793cff7185428a63abfecb90f6f9cfb1.png](https://i-blog.csdnimg.cn/blog_migrate/60642d4bbea7605c4fbc0e9d46e74c3e.jpeg)
裁剪后的效果如下:
![9a3f07e9e7dad112562a7d2512b963de.png](https://i-blog.csdnimg.cn/blog_migrate/31d0af3d66016f78248ff73b0ee81a14.jpeg)
第一步:制作数据源表并根据姓名批量插入图片
数据源部分的基础数据可以录入,也可以直接引用其他表,操作简单,不赘述。这里只说难点:根据姓名或者产品名称批量插入图片。
如图,表格里已经列好了姓名和相关信息,在桌面照片文件夹已经配置好图片,图片的名称跟姓名一一对应。
![68998bcc16a46a1ad7a81b165a66edc4.png](https://i-blog.csdnimg.cn/blog_migrate/1a95c6021232977519c185581b1b95aa.jpeg)
![fdffc55168171dce5049c00731d9f4bb.png](https://i-blog.csdnimg.cn/blog_migrate/ba4329c4536344e1e0e9cb1c3149fb4f.jpeg)
现在要在照片这列根据姓名插入照片,如果手动一张张插入是非常麻烦的,那有什么方法可以快速完成呢?操作步骤如下:
(1)在G2输入:
![c5f9e4644aaa6e229d092ab5c16c8981.png](https://i-blog.csdnimg.cn/blog_migrate/f60d393f3e19f53c1352a52c159a7510.jpeg)
然后快速填充整个G列。此公式的目的就是获取A列的姓名,生成对应照片的链接地址,并与宽高属性一起形成完整的表格中照片的代码。
解析:
这里的公式实际是一句网页代码。Excel支持部分网页代码。
“table”是表格标签;“img”是图片(image)标签,其后的“src”属性用于指定图片的位置。
“ ”是存放照片的文件夹路径。实际操作中根据照片的实际路径修改。
“A2”是姓名单元格,也就是照片名称。
“width”和 “height”,图片的宽高属性,用于指定图片在EXCEL中显示的宽度和高度,具体操作大家可以根据实际情况进行调整。
(2)把G列的公式复制粘贴到记事本,然后清空G列的公式。
![346e5c337f1edf6f46a8675cdce74f7d.png](https://i-blog.csdnimg.cn/blog_migrate/8e784945222e20a0e22b86e662ba1e91.jpeg)
(3)复制记事本中的所有内容,然后在G2单元格选择性粘贴为Unicode文本。
![9cf7b28961eb8326db6fd196566fc4af.png](https://i-blog.csdnimg.cn/blog_migrate/c5f05026944da86de7c2beb58d8f8fdf.jpeg)
(4)点击确定按钮,照片就批量导入到表格里了。
![4f702494888e1ceee660f53665d6dc26.png](https://i-blog.csdnimg.cn/blog_migrate/16c58d2d27b4a6170805fda4794aa2e5.jpeg)
注意:如果姓名有变动的话,照片不能随之更新。
第二步:完成动态查询系统
我们根据上一步骤完成带有图片的数据源,做一个动态查询档案,输入姓名即可查询到照片、性别、出生日期等。做好了之后是这样的:
![b8abb29ea98a01c9f4cc3832a66c17bd.png](https://i-blog.csdnimg.cn/blog_migrate/7cbe8933b57ec8b62d357feef994c830.jpeg)
怎么操作呢?步骤如下:
(1)首先创建以下表格。
![eec99099565bf9ed83923d6d12d8e771.png](https://i-blog.csdnimg.cn/blog_migrate/243c1b6ddb947689d23cfd8371e775f1.jpeg)
(2)在姓名对应的B2单元格输入“吴磊”。
![a257ffd4dbf8379311d25c4577cf7c13.png](https://i-blog.csdnimg.cn/blog_migrate/165783da0db2d72e6d4b0e32e77c73f1.jpeg)
(3)接下来“性别”“出生年月”等其他信息的获取,我们根据姓名“吴磊”采用一个公式来完成。在性别对应的B4单元格输入
=IFERROR(OFFSET(数据源!$A$1,MATCH($B$2,数据源!$A:$A,0)-1,MATCH(查询!A4,数据源!$1:$1,0)-1),"")
![1b5003e4ade555e6dcc677f9ae13fcde.png](https://i-blog.csdnimg.cn/blog_migrate/d6149bd8674be0567c3e635f3418886d.jpeg)
解析:
MATCH(查找内容,查找区域,0):表示查找第一个参数在第二个参数的位置,第三个参数为0代表精确匹配。这里分别返回的是B2单元格“吴磊”在数据源A列(姓名列)对应的位置6和A4单元格“性别”在数据源第1行(标题行)对应的位置2。
OFFSET(参照位置,偏移的行位置,偏移的列位置):表示以第一个参数为位置参照,偏移到第二参数定义的行数和第三参数定义的列数所在的单元格,返回其值。这里的含义是以“数据源”表里的A1单元格为准,向下偏移6-1行向右偏移2-1列,获取到B5单元格值“男”。
在上述OFFSET函数中,如果B2单元格为空,则返回错误信息“N/A”。我们利用IFERR0R函数,当单元格返回错误“N/A”则输出为空值。
因为后续还要查询“出生年月”“星座”等,所以公式中“查询!A4”这个是相对引用,其他都采用了绝对引用。
然后把这个公式复制应用到“出生年月”“星座”等对应的单元格里。注意修改相对引用项。
![f0d2dad3d1e1c40c1e7e01d0b8d0495d.png](https://i-blog.csdnimg.cn/blog_migrate/46cc025d6e85a8af93a71cb57cf1fd7a.jpeg)
(4)接下来我们要把图片动态引用过来。
单击【公式】选项卡下的名称管理器旁边的“定义名称”。
![92ced725ae77214000ef63aaa319a4a5.png](https://i-blog.csdnimg.cn/blog_migrate/83e79be2b8caa7cc0b4345293552512e.jpeg)
在在弹出的对话菜单中,【名称】处输入“照片”,【引用位置】输入公式:
=INDEX(数据源!$G:$G,MATCH(查询!$B$2,数据源!$A:$A,0)
![82212d26619e81cdf40d215f89ffe5c2.png](https://i-blog.csdnimg.cn/blog_migrate/c5d8a8f963a111a18f02d09fe87c09a6.jpeg)
解析:
MATCH:表示查找第一个参数,也就是姓名“吴磊”单元格在第二个参数数据源姓名列的位置,返回6。
INDEX(数据区域,数据位置):表示用第二个参数给出的位置在第一个参数中查找对应的值。
上述公式的意思就是利用INDEX函数返回数据源G列(图片列)中对应行号(由MATCH函数获取)位置的图片。
(5)复制数据源表任意一张照片,粘贴到“查询”表的D2单元格。单击该照片,在编辑栏中输入公式:=照片,点击Enter。
![716276a8f91218ff300315d850d602cd.png](https://i-blog.csdnimg.cn/blog_migrate/cdaeb8f0918c362688cb054fa0ed2c3b.jpeg)
这样当B2单元格输入姓名后点击确定,对应的照片和其他信息就会一起动态更新了。
注意:使用这种方法时,当姓名为空的时候或者姓名错误的时候,仍然会显示上一次操作之后的照片。
Ok,整个查询系统就建立好了。今天的分享就到这里了。我是爱踢汪,您的关注是我坚持到现在的唯一动力,有了您的支持与鼓励,我才有信心一直坚持下去,继续奉上更多内容。衷心期待您能点一下上面红色关注按钮,关注我一下。万分感谢!