通过准备数据源以及查询表格两个步骤我们准备好了图片并批量导入了数据源表中。本篇我们就做出查询表。查询表的最终效果如下：

我们根据上一步骤完成的带有图片的数据源，做一个动态查询档案，输入姓名即可查询到照片、性别、出生日期等。做好了之后是这样的：怎么操作呢？步骤如下：（1）首先创建以下表格。

并且准备好数据源

（2）在姓名对应的B3单元格输入“张飞”。

（3）接下来“性别”“出生年月”等其他信息的获取，我们根据姓名“张飞”采用一个公式来完成。在性别对应的B5单元格输入

性别： =IFERROR(OFFSET(数据源!$A$3,MATCH($A$3,数据源!$A:$A,0)-1,MATCH(查询!A5,数据源!$1:$1,0)-1),"")

出生年月： =IFERROR(OFFSET(数据源!$A$3,MATCH($A$3,数据源!$A:$A,0)-1,MATCH(查询!A8,数据源!$1:$1,0)-1),"")

血型： =IFERROR(OFFSET(数据源!$A$3,MATCH($A$3,数据源!$A:$A,0)-1,MATCH(查询!A10,数据源!$1:$1,0)-1),"")

星座： =IFERROR(OFFSET(数据源!$A$3,MATCH($A$3,数据源!$A:$A,0)-1,MATCH(查询!D8,数据源!$1:$1,0)-1),"")

职业： =IFERROR(OFFSET(数据源!$A$3,MATCH($A$3,数据源!$A:$A,0)-1,MATCH(查询!D10,数据源!$1:$1,0)-1),"")

解析：

MATCH（查找内容，查找区域，0）：表示查找第一个参数在第二个参数的位置，第三个参数为0代表精确匹配。这里分别返回的是B2单元格“张飞”在数据源A列（姓名列）对应的位置2和A3单元格“性别”在数据源第1行（标题行）对应的位置2。

OFFSET（参照位置，偏移的行位置，偏移的列位置）：表示以第一个参数为位置参照，偏移到第二参数定义的行数和第三参数定义的列数所在的单元格，返回其值。这里的含义是以“数据源”表里的A1单元格为准，向下偏移2-1行向右偏移2-1列，获取到B3单元格值“男”。

在上述OFFSET函数中，如果B3单元格为空，则返回错误信息“N/A”。我们利用IFERR0R函数，当单元格返回错误“N/A”则输出为空值。因为后续还要查询“出生年月”“星座”等，所以公式中“查询!A10”这个是相对引用，其他都采用了绝对引用。 然后把这个公式复制应用到“出生年月”“星座”等对应的单元格里。注意修改相对引用项。

（4） 接下来我们要把图片动态引用过来。单击【公式】选项卡下的名称管理器旁边的“定义名称”。=INDEX(数据源!$G:$G,MATCH(查询!$B$3,数据源!$A:$A,0))在在弹出的对话菜单中，【名称】处输入“照片”，【引用位置】输入公式：=INDEX(数据源!$G:$G,MATCH(查询!$B$2,数据源!$A:$A,0))

解析：

MATCH：表示查找第一个参数，也就是姓名“张飞”单元格在第二个参数数据源姓名列的位置，返回3。INDEX（数据区域，数据位置）：表示用第二个参数给出的位置在第一个参数中查找对应的值。上述公式的意思就是利用INDEX函数返回数据源G列（图片列）中对应行号（由MATCH函数获取）位置的图片。

（5）复制数据源表任意一张照片，粘贴到“查询”表的D3单元格。单击该照片，在编辑栏中输入公式：=照片，点击Enter。这样当B3单元格输入姓名后点击确定，对应的照片和其他信息就会一起动态更新了。注意：使用这种方法时，当姓名为空的时候或者姓名错误的时候，仍然会显示上一次操作之后的照片。 Ok，整个查询系统就建立好了。简单回顾一下：利用PS的动作批处理实现图像不变形下统一大小；利用表格标签table代码实现图像批量插入；利用INDEX函数定义“照片”实现照片的动态查询。其他信息的动态查询则是利用OFFSET函数实现的。

假如你学习到了这个新技能不妨转发推荐给你的小伙伴。并动动小指头收藏，以免下次走丢。

想快速在公司通讯录查询某个员工的信息，你会怎么办？今天教大家用excel搭建一个自动查询系统，输入任一一个员工的工号或者姓名、就可以查看该员工的信息啦~

员工登记表，因为涉及到的内容较多，每次都很长一行，为了查看，总是需要来回拖动鼠标，创业型的小公司稍微还好一点，如果是那种几百上千号人，看着看着眼睛都要花了。但是我们可以发现，每个人的信息格式其实是一样的，那么只需用在新的一个sheet中设置一个模板样式，然后通过函数公式就可以实现数据自动查询显示了。

步骤演示：

1、新建模板样式表

在sheet2中，创建一个信息查询表，也就是把希望查询后展示出的内容项都列出来。

2、调整输入项工号格式

一般工号都是001开头，如果直接输入001将变成1，所以这时需要设置为“文本”格式

3、配置公式

在姓名栏D4输入公式：=VLOOKUP($D$3,员工登记表!$A$1:$N5,MATCH(C4,员工登记表!$A$1:$N$1,0),0)

那么当输入工号001则该员工的姓名信息就同步过来了。将公式复制到其他需要同步的内容项。

公式说明：

1.MATCH(C4,员工登记表!$A$1:$N$1,0)

指的是依据单元格C4，在A1-N1即整个区域进行查询，反馈对应的列。

2、VLOOKUP($D$3,员工登记表!$A$1:$N$5,MATCH(C4,员工登记表!$A$1:$N$1,0),0)

指的是根据输入的工号（D3）,在员工登记表内进行查询，得到对应的行，再与match函数得到的列，最终反馈交叉的值。

4、容错处理

当改变工号，员工相关信息会随着改变，但也有可能出现单元格信息显示为0，这表示该员工的某项信息在原始登记表中就是空的。可以菜单栏点击文件-选项-高级，去掉“在具有零值的单元格中显示零”选项的勾选，就可以了。同样，日期字段也会显示成数字，改一下格式就可以。

另外，当输入一个不存在的工号时，则会反馈错误。如下图：

这时我们可以在公式前加一个容错函数IFERROR，则D4的公式变为：

=IFERROR(VLOOKUP($D$3,员工登记表!$A$1:$N$5,MATCH(C4,员工登记表!$A$1:$N$5,0),0),"")

回车后，可以看到当输入不存在的工号时，表格为空。

5、照片的动态跟随

选中照片-公式-定义名称。

在弹出对话框输入公式：=INDEX(员工登记表!$D:$D,MATCH(员工信息查询表!$D$3,员工登记表!$A:$A,0))，命名为“照片”，点击确定。

然后在excel选项-快速访问工具栏，将照相机添加进来，这时excel页面左上方会出现照相机按钮。

点击照相机，在“照片”单元格内拖动鼠标，画出方框，编辑公式=照片；那么回车后，工号对应的照片也就显示出来了。

改变工号，对应的信息和照片都会一起随着改变了。

还有一种更简单的实现方式，只需2步！

1、用表单大师制作一个在线员工登记信息表，将需要登记的信息字段都添加到表单中；员工照片可以用文件上传字段来收集

2、表单制作好后，只需设置一个公开查询的功能，输入查询条件，这样就能实现上面那么复杂的操作了。还可以灵活勾选允许查看哪些信息，设置更简单，功能更强大；甚至还可以设置提醒，比如提交信息后，可以提醒hr进行查看呢

两步搞定，就是这么简单

不信，点击试试这套查询系统吧！

记录系统时间，大家第一时间想到的肯定是Now()函数，其实这个想法没有错，但如果要实现数据编辑的时间保持不变，用Now()函数时远远不够的，需要借助【迭代计算】和If函数共同来完成。

一、Now()函数记录系统时间。

方法：

在目标单元格中输入公式：=NOW()。

解读：

1、Now()函数虽然可以获取当前的系统时间，但和我们的目标相差甚远。如果停留一段时间在去编辑工作簿或打开工作簿，其原来记录的时间自动变成了最新的系统时间。

2、如何让记录的时间保持不变，这就需要用到【迭代计算】和If及Now函数了。

二、【迭代计算】及If和Now函数组合使用实现系统时间的记录。

1、启用【迭代计算】功能。

方法：

1、单击工作簿任意单元格。

2、【文件】-【选项】-【公式】-【计算选项】-【启用迭代计算】-【确定】。

2、判断并自动填充时间。

方法：

1、在目标第一单元格中输入公式：=IF(E3="","",IF(F3="",NOW(),F3))。

2、拖动填充柄，填充其他单元格。

3、在目标关键列（示例中为“地区”）输入信息。如果不为空，系统自动记录当前时间。

解读：

利用迭代计算和公式实现的自动记录，并不会随着工作簿的关闭打开或者时间的变化自动变化，更符合我们时间的工作需求。也正好避免了Now()函数的上述缺点。

结束语：

利用迭代计算机If函数来判断完成系统时间的记录，在日常生活中应用的非常广泛，例如打印票据等等，希望对大家的实际工作有所帮助。学习过程中的感想和看法可以在留言区留言讨论哦！

秦老师最近帝都培训出差，碰到天气变化，结果航班取消，住了酒店居然还没网络，正巧小伙伴问道想设计一个用于统计投票的系统，最好前三名能自动显示为红色，能显示唱票的总计，正好试试研究了一下。牛牛的搞定了这个问题。不敢独享，赶紧和小伙伴们分享一下。

具体操作如下：

首先需要一个如下的数据结构。

唱票数Ｇ列区域，不能手动输入候选人票数，这样很不方便，所以我们需要一个窗体控件，用点击鼠标的方法来实现唱票。在“开发工具－插入－数值调节钮”下图３处，然后拖拽得到一个如下图的控件。（下图４处）

接着利用控件来控制Ｇ３单元格，即第一个候选人。

选中控件，点击鼠标邮件，在“设置控件格式－链接单元格－Ｇ３单元格”，这是点击控件的上下箭头，可以来控制单元格的票数。如下动图操作。

依此类题，选中控件，Ｃｔｒｌ＋Ｃ／Ｃｔｒｌ＋Ｖ复制出剩下的五个控件，然后将其单元格链接分别对应后几个候选人对应的单元格。如下图动图所示。

接着插入一个图表，将其置于底层，按动控件的箭头，图表的箭头就会随之移动，那么我们这个唱票系统的雏形就有了。看下图动图：

但小伙伴们对唱票系统有要求，要求前三名的图表柱子能自动变成红色。剩余的名次的柱子保持原有颜色。而且还要对每个候选人的唱票有最终的汇总数字表达。所以我们要在插入图表之间，对数据结构进行改进。这也是本例的难点。

在数据结构后面，新增前三名列（H列）和剩余名次列（G列）。然后将下面的公式复制到数据结构中，注意按ctrl+shift+enter来执行这个函数公式。然后在进行拖拽填充，这是系统会知道抓住候选人的前三名数字，不是前三名的候选人会用数字0来代替。

=IFERROR(VLOOKUP(G3,LARGE($G$3:$G$8,ROW($G$1:$G$3)),1,0),0)

该公式是一个数组公式，注意ROW($G$1:$G$3)控制选择需要变红的名词的个数，本例为前三名，如果要前六名变颜色，就要改为ROW($G$1:$G$6).

Large函数来动态获取前三名的区域范围。

Vlookup来匹配候选人的数值是否能匹配前三名的数值区域，从而获得该数值。

IFERROR用让剩余没匹配的剩余（不是前三名的）数值为0。

G列这是候选人数据所在的列。

搞清楚这个公式，就可以对该数据模板进行随意按需的调整。

接着我们将不是前三名的数据显示在I列，所以利用如下公式即可搞定。

=IF(G3-H3>0,G3,0)

有了这两个辅助列，然后在插入堆积柱形图，然后对前三列的数据统一设置为红色，即可实现动态的前三名的柱形图显示。(看动图操作)

最难的地方搞定后，接下来开始对图表进行美化，调整图表的大小尺寸，固定坐标轴的最大高度，也就是唱票可能出现的最高值。

这里有些技巧：

1.拖拽图表大小的时候，按住alt键可以与单元格边缘进行吸附。

2.调整最后一个控件的位置，然后利用对齐按钮进行快速的对齐分距调整。

3.选中坐标轴，按ctrl+1可以快速打开坐标轴最大值设置窗口。

设置坐标轴最大值。

接着设置候选人对应的唱票数统计，在图表工具中插入对应的文本框。然后在编辑栏中输入=号，去引用G列对应的唱票数。再按住ctrl+shift进行快速的横向拖拽复制。

最后修改其他的文本框对应的单元格引用。然后美化图表，增加主次网格线即制作设计完毕。

最终效果如下：前三名会自动为红色。如果前三名票数一样，也会是红色。

总结：利用控件+函数+图表，就可以制作出一个非常专业的动态图表展现系统。本例的唱票系统就是一个典型，希望大家能够掌握。

EXCEL公式表格制定简易实用的【自动生成工资系统】管理工具

本文的自动生成工资系统结合了公式：VLOOKUP、IF、IFERROR、INDEX、SMALL、OR、AND、ROW( )等函数以及数组、绝对引用、相对引用以及超链接等功能。（本文适用于有一定的EXCEL应用基础的人）

关键点：

【工资表】、【全部工资条】、【查询表】、【查询工资条】、【系统介绍说明】等条框上面的矢量图标须与对应的表格进行超链接。

IFERROR函数的功能在于如果公式的计算结果为错误，则返回您指定的值；否则将返回公式的结果。作用与ISERROR与IF组合相类似。

数组“大扩号”的输入是：选择须要输入的单元格范围，按Ctrl+Shirt键，再敲Enter键即可。

第一步：建立一个EXCEL文档与表格，如下截图：

共分建立以下六个表格：【首页】、【工资表】、【全部工资条】、【查询表】、【查询工资条】、【系统介绍说明】等。

关键点：对应的表格顺序依上面截图排列。

第二步：设定每张表的格式或内容样式，先在【工资表】逐项填写年月及员工工资信息；如下截图：

注意关键点：

【工资表】的样式须与【工资条】的内容排列顺序完全一致。

【全部工资条】中的自动生成全体员工的工资条，如下截图：

注意关键点：

【全部工资条】的样式须与【查询工资条】的内容完全一样；

如上截图，全部工资条前面增加“2018年3月工资”字样的目的在于打印工资条后便于纸档的裁切。

【查询表】中，输入员工的姓名，可以查出该员工的工资条相关全部信息，如下截图：

并在【查询工资条】表格中自动生成该员工的工资条;输入部门名称，则可以查出该部门所有员工的工资，并在【查询工资条】表格中自动生成该部门所有员工的工资条。

注意关键点：个人所得税计算一列，可以依实际扣税标准并套用IF函数。

以上查询表正表显示的信息内容与工资料内容完全一样。

第三步：公式输入与链接

工资表公式链接，如下截图：

注意关键点：

上面A列平时在操作使用时可以隐藏；

这列公式的目的与【查询表】信息匹配。此表须注意函数AND（与）与OR（或）的结合使用以及绝对引用与返回行函数ROW( )的运用。

工资条公式链接，如下截图：

查询表公式链接，如下截图：

查询表第A列在平时操作时一般隐藏。

第五步：制作完成效果显示，如下截图：

只要依以上步骤操作，则可以实现简易的工资管理工具的制定，前提是须对相关公式函数有初步的了解与应用。

文/靳伟 来源：知乎 由微博：是琅嬛啊整理 版权归原作者所有 若发现侵权行为请与我联系我会立即删除谢谢！小编只是知识的搬运工 所以请大家多多关注原作者谢谢各位啦 当然如果喜欢小编的分享，也欢迎关注我的头条号，小编每天都会发掘更多有用的东西给大家！

怎样才算精通 Excel？

觉得有用就赶紧收藏转发点赞和关注我的头条号哟！

施老师：

现在公司里的销售业绩统计出来，数据也是非常庞大的。如果一个一个手动去统计销售员的业绩，未免太吃力。少则几百，多则上千的数据，一个一个手动统计要把我们累的够呛。今天宁双学好网施老师就给大家分享一招，只需用Excel函数就能解决这个问题，下面就看我演示一遍吧！

一、首先我们打开下方的工作表

二、然后在H3栏中输入公式：=IF(G3>10000,"优","差")，这里用到的是IF条件函数，后续教程我会更多的教大家使用IF函数，这里G3就是将要计算的那个单元格的位置。

三、在得出第一个总评的结果后，把鼠标移动右下角，出现“+”字的时候，拖住“+”往下拉，就可以得到下方的总评结果。

总结，这个IF函数是条件函数，是计算满足当前这个值是什么状态，不满足又是什么状态的。非常的实用。

欢迎在下方的评论区写下你的看法，有疑问请在下方的评论区问我。我会跟大家一起解答。喜欢此文就请转载，转载请注明出处.。

想学习更多的教程，请点击下方的"了解更多"

工作中，我们经常会用到评分，比如客户满意度、工作表现评分、产品使用调查等等。今天就给大家介绍如何在Excel中制作简单的星级评分表，让你的表格更生动形象~

1、REPT函数

如下图，在星级栏即C2输入公式：=REPT("★",ROUNDDOWN(B2,0))&REPT("☆",ROUNDUP(5-B2,0))

然后按住Enter键。则单元格c2显示如下：

公式中的"★","☆" 来自Excel中的“符号”，即选定一个单元格，单击菜单栏“插入-符号”，然后在对话框中选择“几何图形”选择实心星星和空心星星，点击插入，则实心星星和空心星星将分别插入到单元格内了。如下图：

这样，编辑公式时，复制符号到公式相应位置即可。

2、表单大师-评分字段

上诉评分效果要求对函数公式很熟悉，但是有一个更简单的方式，而且更美观：

只需在表单大师中创建一个表单，比如教学工作评估表，然后添加评分字段：

五星标志，常用来作为标识等级的符号。五星级饭店、五星级酒店等等。

实际上，Excel中也可以建立五星标志系统。

现在我们一起来看看吧。

第一步，我们先确定数据区域

原数据是姓名列和评分列（5分制）。

在右边新建了5列五星评分区，标上数字“1,2,3,4,5”，用来填充五星。

确定数据区域

是的，我们要用到田间格式的图标集了。

条件格式——图标集

第二步，我们要在五星区域输入公式。

下面要编写很重要的公式，从而确认这每隔单元格返回的是1、0还是小数。

我们以第一个人姓名为例，输入公式：

=IF(C$1<=$B3,1,IF(ROUNDUP($B3,0)=C$1,MOD($B3,1),0))

其中，如果B3单元格中的打分小于等于C1单元格所对应的数值，则显示为1；

否则， 如果B3向上取整后等于C1，则显示B3的小数位，否则显示为0。

在所有单元格中填充这个公式后，便显示为下图。

输入公式

第三步，我们用条件格式。

选中了区域C3:G12，点击“条件格式”——“新建规则”，弹出如下的对话框，进行设置，下图是设置好的。

设置条件格式

完成后我们看一下效果

我们再进行调整，就变成如下的效果了，嘿嘿。

效果图

文/作者：人资大叔

今天，大叔给大家分享一个由EXCEL制作的员工能力评价系统，请有兴趣的朋友公众号发送关键字索取！

请将您所负责评估的员工姓名依次写在“部门初评”工作表中的“员工姓名栏”内。

对每一位员工遵照以下原则进行评估：

1、全员核心胜任能力的评估：

全员核心胜任能力所包括的行为体现企业文化和价值观，要求员工能经常表现这些行为，其

评估方式为考察员工在这些行为上的表现频率：

1=很少表现， 2=有时表现，3=经常表现，4=总是表现

2、渠道销售序列通用能力的评估：

渠道销售序列通用能力所包括的行为体现该序列员工应该具备的基本技能，它对专业技术能力

的发展有支持作用，其评估方式为考察员工在这些行为上的表现频率：

1=很少表现， 2=有时表现，3=经常表现，4=总是表现

3、渠道销售序列专业技术能力的评估

1、根据渠道销售序列5项不同的专业技能维度《专业技术能力的层级描述》考察每位参评员工的水平；

2、五项专业能力都分为初、中、高三个层级，只有员工满足某一层级中“半数以上”的行

为技能描述时，方可将其评为该层级；同时根据员工在每一层级满足“行为描述”的数

目不同，在每一层级列出了几档分数供选择，用以更精细化地区分员工在“专业技术能

力”方面的细微差异。

初级（1，2，4）：1分--不具备“初级”列出的“半数以上”行为描述

2分--满足“初级”列出的“半数以上”行为描述，但不满足所有

4分--满足“初级”列出的所有行为描述

中级（5、6、8）：在满足初级列出的所有行为描述的基础上，同时：

5分--不具备“中级”列出的“半数以上”行为描述

6分--满足“中级”列出的“半数以上”行为描述，但不满足所有

8分--满足“中级”列出的所有行为描述

高级（9、10、12）：在满足初、中级列出的所有行为描述的基础上，同时：

9分--不具备“高级”列出的“半数以上”行为描述

10分--满足“高级”列出的“半数以上”行为描述，但不满足所有

12分--满足“高级”列出的所有行为描述

4、其它说明

1、因目前暂不可能开展360度评议，因此直线经理对全员核心胜任能力和序列通用能力某方面行为描述进行评价时，可能因观察不到而产生困难，建议请向其周围同事或其客户征询意见后再评价，在时间允许的情况下，建议可先采取员工自评的方式

2、在评估之前，直线经理需要与下属员工充分交流和沟通，并将《渠道销售序列专业技术能力的层级描述》与下属员工共享

3、请各业务经理严格按照该评估工具的内容和格式，逐条对员工进行客观、公正评价，如部门初评结果和“专家评议小组”面试答辩结果差异过大，需请相关业务经理说明原因

3、各部门完成初评后，请将此表作为附件，并以部门正式文件形式，于xxxx之前发送到人力资源部

4、如有考虑不周之处或您有更好建议，请告知我们改进

（版权声明：本文属“人资大叔”创作内容，如您想索要文中涉及的表格，请私信大叔发送“能力评价”即可免费获取下载连接！）