excel表格_excel函数公式大全_execl从入门到精通
如何利用EXCEL制作自动化的员工考勤排行榜？2024-03-23 08:02:48
陈峰看了小琪手中的考勤表，羡慕不已，“对了，小琪，咱们公司有对各部门员工出勤情况进行排名的惯例，一般出勤情况最好的前三个部门是可以有全勤奖，而最差的后三名会被扣除百分之十的当月奖金。以前我都是手工算出来的，你能不能也做成模板？把各个部门的考勤表放到EXCEL表格中，自动可以出排名的那种？”
“嗯，我先想想！”小琪说。
随后，小琪将陈峰的建议告诉了顾城，顾城考虑了一下，说：“嗯，陈峰说的是排名分析，这种排名分析不仅仅用在考勤分析中，像财务、销售部门都经常会用到，这样我先用考勤数据教你可以做一个排名分析的模板吧！”
说完，顾城打开了之前小琪制作的考勤表。
“我先用这组数据教你做一下员工出勤情况的排名分析。”
“好！”
Step1：选择合适的控件，并进行格式设置。此处，由于需要进行排名分析，因此有升序和降序两种排列方式。所以，控件可以选择“选项按钮”。
1、 在开发工具菜单中，选择“选项按钮”，插入到EXCEL表格中。如图 462所示。此处需要插入两个选项按钮，一个用于升序排列，一个用于降序排列。如图 463所示。
图4-62
图4-63对添加的选项按钮重新命名，一个为升序，一个为降序（如图 464所示）。图4-642、 对两个选项按钮进行格式设置。选择“升序”选项按钮，右键单击，在快捷菜单中选择“设置控件格式”命令。在弹出的“设置控件格式”对话框中，单元格链接处，选择一个合适的单元格，做为存放返回值的位置。在本例中，选择L5单元格。为方便区分，可以为L5单元格设置背景色。（如图 465所示）图4-653、 为方便对选项按钮进行位置调整，可将其进行组合。同时选择两个选项按钮，右键单击后，选择组合命令，将两个按钮组合到一起。（如图 466所示）
图4-66此时，当选择升序按钮时，L5单元格返回值为1，而选择降序按钮时，L5单元格返回值则为2. （如图 467所示）图4-67Step2：制作辅助数据区域。
1、 对基础数据进行处理。由于基础数据中有存在相同数据的可能性，需要对重复数据进行强行排名，因此需要对基础数据进行排名前的数据处理工作，此处需要用到RAND函数。
RAND函数详解（如图 468所示）：
一、作用：RAND函数属于数学与三角函数。用于返回0-1之间的任意一个随机数。
二、参数：此函数无需参数。图4-68在本例中，可利用此函数，将所有的员工出勤率加上一个任意数，这样就避免了数值重复的情况。以员工AAA1的出勤率为例，处理后的数值可以表示为：=M8+RAND()/100000，即员工出勤率加上RAND函数返回的一个随机数，为避免数值过大，影响准确率，可用RAND函数除以100000，这样随机数变成了一个非常小的数值，既不会对员工出勤率的实际数值产生影响，又避免了出现重复的出勤率数值。（如图 469所示）图4-69将公式向下拖动，最终效果如图 470所示。图4-702、 接下来，需要对处理过的数据进行排序。而排序的方式需要根据控件的返回值来确定，当L5单元格返回值为1时升序排列；当L5单元格返回值为2时降序排列。实现此要求，需要用到两个排序函数LARGE和SMALL函数。
LARGE函数详解（如图 471所示）：
一、作用：LARGE函数属于统计函数。其主要用于返回一组数据中第K个最大值。
二、此函数用两个参数。
参数一Array：用来计算第K个最大值点的数值数组或数值区域。在本例中，录入N8:$N$16，即处理后的员工出勤率。
参数二K：表示第几大的数值。如果录入1，则表示将这组数据从大到小排列，取最大值，录入2则表示将这组数据从大到小排列，取排在第2位的数值，以此类推。当此数值由1逐步递增时，就实现了所选择数据区域的由大到小的排列。例如取本组数据中的最大数公式为：=ALRGE(N8:$N$16,1)
图4-71SMALL函数详解（如图 472所示）：
一、作用：SMALL函数属于统计函数。用来返回数据组中第K个最小值。
二、此函数共两个参数：
参数一Array：用来计算第K个最小值点的数值数组或数值区域。在本例中，录入N8:$N$16，即处理后的员工出勤率。
参数二K：表示要返回的最小值点在数组或数据区域中的位次。如果录入1，则表示将这组数据从小到大排列，取最小值，录入2则表示将这组数据从小到大排列，取排在第2位的数值，以此类推。当此数值由1逐步递增时，就实现了所选择数据区域的由小到大的排列。例如取本组数据中的最小数公式为：=SMALL($N$8:$N$16,1)图4-72在LARGE函数与SMALL函数公式的制作过程中，参数K是一个关键点。由于希望实现将公式向下拖动，即可完成整个数据排名的工作，因此这个参数K是否可以自动变化是解决这个问题的关键。如果手动录入1、2、3……等序列号，费时费力，此时可以考虑嵌套一个函数来实现序列号的自动变化。
在本例中，由于表格是纵向结构，公式需要向下拖动，即每当公式下移一行，K就要增加1，因此可以嵌套一个ROW函数。
ROW函数详解（如图 473所示）：
一、作用：ROW函数为查找引用函数。用于返回一个引用的行号。
二、此函数只有一个参数Reference：准备求取其行号的单元格或单元格区域；如果忽略，则返回包含ROW函数的单元格。图4-73在本例中可以选择任意一个位于第一行的单元格，此时返回值为1。
将此函数嵌入到LARGE函数和SMALL函数中的第二个参数中：
LARGE函数公式变为：=LARGE($N$8:$N$16,ROW(N1))
SMALL函数公式变为：=SMALL($N$8:$N$16,ROW(N1))
注意：N1单元格必须为相对引用。
将以上两个公式分别向下拖动时，N1会自动变为N2、N3……，如图 474所示。图4-74最终效果如图 475所示。
图4-75补充知识：当表格为横向结构时，如何实现数据由大到小或由小到大的自动排列？
当表格结构由纵向变为横向时，制作出来的LARGE函数或SMALL函数公式就需要横向拖动，每当公式横向移动一个单元格，第二个参数K就要增加1。根据规律，可以将原本嵌套在公式第二个参数中的ROW函数变为COLUMN函数。
COLUMN函数详解（如图 476所示）：
一、作用：COLUMN函数为查找引用函数。用于返回一个引用的列号。
二、此函数只有一个参数Reference：准备求取其列号的单元格或单元格区域；如果忽略，则返回包含COLUMN函数的单元格。图4-763、 下面就需要实现根据控件来确定表中员工出勤率显示顺序。完成此要求，可以通过利用IF函数，对控件的返回结果进行判断，如果L5单元格返回结果为1，则需要将数据由小到大，升序排列，采用=SMALL($N$8:$N$16,ROW(N1))公式，如果返回结果为2，则需要将数据由大到小，降序排列，采用=LARGE($N$8:$N$16,ROW(N1))公式。完整公式为：=IF($L$5=1,SMALL($N$8:$N$16,ROW(N1)),LARGE($N$8:$N$16,ROW(N1))) （如图 477所示）图4-77此时，为制图方便，可以重新制作一份辅助数据区域，并将上述公式录入到新的辅助数据区域中（如图 478所示）。图4-784、 按新辅助数据区域中出勤率的排列顺序取出对应的员工姓名。首先，插入INDEX函数，然后将参数一及参数三录入到函数对话框中，如图 479所示。
图4-79接下来录入参数二。参数二需要确定在所选择的姓名列中取出位于第几行的姓名。这个工作，可以通过确定“出勤率”的行位置在确定。
特别注意，新辅助数据区域中的“出勤率”为处理过的出勤率，即与原辅助数据区域中“处理后”所在的数据一致，如图 480所示，因此需要确定新辅助数据区域中的“出勤率”在“处理后”所在的数据列中的行位置，而此行位置就是姓名列中要取出的姓名所在的行位置。图4-80此时可以使用MATCH函数来进行行位置的定位。在INDEX函数的第二个参数中，插入MATCH函数。如图 481所示。图4-81此时，进入MATCH函数参数对话框中，参数一录入M20，即第一行的出勤率，参数二则选择“处理后”所在的那一列数据区域，参数三录入“0”。即确定M20单元格中的内容在“$N$8:$N$16”这组数据中的行位置，完整公式为：MATCH(M20,$N$8:$N$16,0) （如图 482所示）图4-82最后，点击“确定”完成整个公式的录入，姓名列的完整公式为：=INDEX($L$8:$L$16,MATCH(M20,$N$8:$N$16,0),1)，随后将公式向下拖动，完成新辅助数据区域的制作，如图 483所示。
图4-83Step3：将“出勤率”中的数值调整为百分比，然后插入柱形图，并进行美化。如图 484所示。图4-84“小琪，现在一份员工出勤情况的排名分析就制作好了！”
“顾总，我觉得你这份排名分析做得不好！”小琪想了想，说。
“为什么？”
“你看，这个表格中是模拟数据，人数比较少，你直接把所有人都进行了排名分析，如果想只显示前三名，或只显示最后三名，怎么办呢？还有，现在只是对总的出勤率进行了排名分析，如果想对具体的请假事由进行排名分析，怎么办呢？”
“嗯，小琪不错嘛！你提的问题很好，但要实现你说的功能，就需要用到多个控件进行配合，你目前还属于入门阶段，我就把难度降低了。没想到还让你找出毛病来了！”顾城笑着说。
“这样吧，今天时间不早了，这个排名分析我先讲到了，至于你提出的高难度的排名分析要求，咱们等以后有空再讲！”
“好，那顾总，你可别忘了！”
“忘不了，排行分析以后用的机会多着呢！”
小伙伴们，你们在工作中还有哪里会用到排行榜呢？
快按小编的方法练习一下吧！
标签: 如何在excel中添加强行