我们都知道当数据过多的时候，我们制作Excel图表就会显得非常的复杂，图表上面的内容就会特别多。Excel老玩家就会想到用切片器制作动态可变化的图表来显示。今天我们就来学习一下一个比......
2023-01-08
如何利用 excel 搞定一套高逼格的动态查询系统
Excel函数
2023-01-08
担任人事行政、库管、产品上货管理等的小伙伴们在工作中常常需要动态查询人员、产品信息。今天我们就来做一套包含照片的动态查询系统，可以查到人员姓名、出生日期、职位、照片等等。
我们先用PS将下图中的照片，批量裁剪成统一的大小，让图片尺寸保持为140x140像素。
裁剪后的效果如下：
第一步：制作数据源表并根据姓名批量插入图片
数据源部分的基础数据可以录入，也可以直接引用其他表，操作简单，不赘述。这里只说难点：根据姓名或者产品名称批量插入图片。
如图，表格里已经列好了姓名和相关信息，在桌面照片文件夹已经配置好图片，图片的名称跟姓名一一对应。
现在要在照片这列根据姓名插入照片，如果手动一张张插入是非常麻烦的，那有什么方法可以快速完成呢?操作步骤如下：
（1）在G2输入：
然后快速填充整个G列。此公式的目的就是获取A列的姓名，生成对应照片的链接地址，并与宽高属性一起形成完整的表格中照片的代码。
解析：
这里的公式实际是一句网页代码。Excel支持部分网页代码。
“table”是表格标签;“img”是图片（image）标签，其后的“src”属性用于指定图片的位置。
“ ”是存放照片的文件夹路径。实际操作中根据照片的实际路径修改。
“A2”是姓名单元格，也就是照片名称。
“width”和 “height”，图片的宽高属性，用于指定图片在EXCEL中显示的宽度和高度，具体操作大家可以根据实际情况进行调整。
（2）把G列的公式复制粘贴到记事本，然后清空G列的公式。
（3）复制记事本中的所有内容，然后在G2单元格选择性粘贴为Unicode文本。
（4）点击确定按钮，照片就批量导入到表格里了。
注意：如果姓名有变动的话，照片不能随之更新。
第二步：完成动态查询系统
我们根据上一步骤完成带有图片的数据源，做一个动态查询档案，输入姓名即可查询到照片、性别、出生日期等。做好了之后是这样的：
怎么操作呢？步骤如下：
（1）首先创建以下表格。
（2）在姓名对应的B2单元格输入“吴磊”。
（3）接下来“性别”“出生年月”等其他信息的获取，我们根据姓名“吴磊”采用一个公式来完成。在性别对应的B4单元格输入
=IFERROR(OFFSET(数据源!$A$1,MATCH($B$2,数据源!$A:$A,0)-1,MATCH(查询!A4,数据源!$1:$1,0)-1),"")
解析：
MATCH（查找内容，查找区域，0）：表示查找第一个参数在第二个参数的位置，第三个参数为0代表精确匹配。这里分别返回的是B2单元格“吴磊”在数据源A列（姓名列）对应的位置6和A4单元格“性别”在数据源第1行（标题行）对应的位置2。
OFFSET（参照位置，偏移的行位置，偏移的列位置）：表示以第一个参数为位置参照，偏移到第二参数定义的行数和第三参数定义的列数所在的单元格，返回其值。这里的含义是以“数据源”表里的A1单元格为准，向下偏移6-1行向右偏移2-1列，获取到B5单元格值“男”。
在上述OFFSET函数中，如果B2单元格为空，则返回错误信息“N/A”。我们利用IFERR0R函数，当单元格返回错误“N/A”则输出为空值。
因为后续还要查询“出生年月”“星座”等，所以公式中“查询!A4”这个是相对引用，其他都采用了绝对引用。
然后把这个公式复制应用到“出生年月”“星座”等对应的单元格里。注意修改相对引用项。
（4）接下来我们要把图片动态引用过来。
单击【公式】选项卡下的名称管理器旁边的“定义名称”。
在在弹出的对话菜单中，【名称】处输入“照片”，【引用位置】输入公式：
=INDEX(数据源!$G:$G,MATCH(查询!$B$2,数据源!$A:$A,0)
解析：
MATCH：表示查找第一个参数，也就是姓名“吴磊”单元格在第二个参数数据源姓名列的位置，返回6。
INDEX（数据区域，数据位置）：表示用第二个参数给出的位置在第一个参数中查找对应的值。
上述公式的意思就是利用INDEX函数返回数据源G列（图片列）中对应行号（由MATCH函数获取）位置的图片。
（5）复制数据源表任意一张照片，粘贴到“查询”表的D2单元格。单击该照片，在编辑栏中输入公式：=照片，点击Enter。
这样当B2单元格输入姓名后点击确定，对应的照片和其他信息就会一起动态更新了。
注意：使用这种方法时，当姓名为空的时候或者姓名错误的时候，仍然会显示上一次操作之后的照片。
相关文章
- 详细阅读
-
excel 合并上百个工作簿数据，你花了一天别人只用了10秒详细阅读
在工作中，可能许多朋友都会碰到一个情况，那就是工作簿和工作表数据的合并操作。如何将上百个工作簿快速合并到一个表格中，许多朋友可能会觉得不可思议。今天我们就来教大家学习一......
2023-01-08
-
Excel文本函数，excel拆分和组合函数，掌握这些就够了！详细阅读
今天在这里为你分享5个Excel文本函数，这些拆分和组合函数，你一定会用上的。①LEFT函数公式：=LEFT(A2,1)在Excel表格中，需要想要拆分汉字，想从哪里开始就从那哪里开始。首先选定单元格......
2023-01-08
-
Excel函数一键变翻译，学会了这个方法，再也用苦思冥想了！详细阅读
相信大家也和我一样，才开始看到Excel可以当做翻译软件的时候会很好奇，这究竟是怎样做到的？其实，这个方法并不是很难，它是由一个函数公式而制作出来的，好了，首先我们一起来看看成......
2023-01-08
-
Excel函数公式大全，办公室人员必备！详细阅读
函数可以说是所用快捷方法中最为简单的一种方法，为什么很多人认为函数用起来很难了？主要是因为它拥有很长的函数公式，记不住。其实不管是学Excel函数，还是学习其他的一些快捷方法......
2023-01-08