Excel做成绩表时自动实现同分同名次.docx
《Excel做成绩表时自动实现同分同名次.docx》由会员分享，可在线阅读，更多相关《Excel做成绩表时自动实现同分同名次.docx（26页珍藏版）》请在冰豆网上搜索。
Excel做成绩表时自动实现同分同名次
Excel应用技巧
Excel做成绩表时自动实现同分同名次1
一、使用排位函数RANK1
二、使用计数函数COUNTIF1
三、采用IF语句加COUNT函数1
多个Excel表中的数据合并计算1
按位置进行合并计算1
按分类进行合并计算2
通过公式进行合并计算2
更改多个Excel文档数据合并计算3
更改按位置或分类进行的合并计算3
更改按公式进行的合并计算3
把Excel里的一列数据快速转成多列数据3
在Excel中快速添加间隔空行5
Excel表中选择单元格及其内容5
在Excel工作表单元格中手动输入各种数据7
输入数字或文本7
输入日期或时间8
同时在多个单元格中输入相同数据8
在其他工作表中输入相同数据9
Excel2007轻松进行多条件求和9
一、反复筛选法9
二、条件求和法10
三、SUM结合IF10
四、SUMPRODUCT函数10
五、SUMIFS函数10
Excel做成绩表时自动实现同分同名次
在用Excel统计成绩时，我们一般会根据成绩高低进行排序，然后按序列自动填充出名次。
这种方法得出的名次与总分没有关联，即使成绩相同，也会填充出不同的名次。
如果数据较少，我们可以采用手动的方法将成绩相同的人员改成相同的名次，但数据较多时就很麻烦了。
经过实践，以下三种方法可以自动实现同分同名次的操作。
假设有1000名考生，字段位置如上图所示。
一、使用排位函数RANK
在H2单元格中输入函数“=RANK（G2,G$2:
G$1001,0）”，回车后H2单元格中会出现名次“1”，然后选定H2，拖动其填充柄向下填充即可自动实现。
该函数的功能是返回一个数字（上述函数中的G2，此处采用相对应用，填充时随行的变化而变化）在数字列表（上述函数中的G$2:
G$1001，此处采用绝对应用，填充时不发生变化）中的排位。
数字的排位是其大小与列表中其他值的比值。
该函数在使用时，即使总分没有排序，它也可以直接求出所对应总分的名次;如果总分已排过序，则数字的排位就是它当前的位置。
上述函数中最后的“0”指明排位的方式，当其为0或省略时，Excel对数字的排位是按照降序排列的。
如果不为零，Excel对数字的排位则是按照升序排列的。
该函数对重复数的排位相同，而下一名次则将前面的重复数计算在内。
二、使用计数函数COUNTIF
在H2单元格中输入函数“=COUNTIF（G$2:
G$1001,">"&G2）+1”，然后向下自动填充即可实现该操作。
该函数的功能是计算区域中满足给定条件的单元格个数。
上述函数中采用绝对引用指定计算区域G$2:
G$1001，名次所对应的单元格G2采用相对引用。
即求出在G2:
G1001区域中大于名次所对应总分的单元格的个数，然后加上1就可以得出该单元格中数值的名次。
该操作不管有几个重复的，大于它的个数却是固定的，所以重复数据的名次也就是相同的了。
同RANK函数一样，该函数不管是否已经按总分进行排序均可进行上述操作。
三、采用IF语句加COUNT函数
该操作与上述两种方法不同，必须先将总分按照降序排列。
然后将第一名的名次“1”输入，再在H3单元格中输入函数“=IF（G3=G2,H2,COUNT（$G$2:
G3））”，确定后向下自动填充也可实现同分同名次的操作。
COUNT函数的功能是返回指定区域中数字单元格的个数。
因为G列全是数字单元格，所以该总分处在第几位上名次就是几。
上述语句的意思就是如果当前总分和上一个总分相同，则输入上一个总分的名次，否则执行COUNT语句，统计个数。
以上三种方法均可自动实现同分同名次操作。
本文虽然有孔乙己先生关于“回”字有四种写法的嫌疑，但笔者的目的却只有一个，那就是抛砖引玉，让Excel函数更好的为我们服务。
多个Excel表中的数据合并计算
Excel2007中若要汇总和报告多个单独工作表的结果，可以将每个单独工作表中的数据合并计算到一个主工作表中。
这些工作表可以与主工作表在同一个工作簿中，也可以位于其他工作簿中。
对数据进行合并计算就是组合数据，以便能够更容易地对数据进行定期或不定期的更新和汇总。
例如，如果您有一个用于每个地区办事处开支数据的工作表，可使用合并计算将这些开支数据合并到公司的开支工作表中。
这个主工作表中可以包含整个企业的销售总额和平均值、当前的库存水平和销售额最高的产品。
要对数据进行合并计算，请使用“数据”选项卡上“数据工具”组中的“合并计算”命令。
按位置进行合并计算
——按同样的顺序排列所有工作表中的数据并将它们放在同一位置中
1、在每个单独的工作表上设置要合并计算的数据。
如何设置数据
确保每个数据区域都采用列表（列表：
包含相关数据的一系列行，或使用“创建列表”命令作为数据表指定给函数的一系列行。
）格式：
第一行中的每一列都具有标签，同一列中包含相似的数据，并且在列表中没有空行或空列。
将每个区域分别置于单独的工作表中。
不要将任何区域放在需要放置合并的工作表中。
确保每个区域都具有相同的布局。
命名每个区域：
选择整个区域，然后在“公式”选项卡的“命名单元格”组中，单击“命名单元格区域”旁边的箭头，然后在“名称”框中键入该区域的名称。
2、在包含要显示在主工作表中的合并数据的单元格区域中，单击左上方的单元格。
注释：
确保在该单元格右下侧为合并的数据留下足够的单元格。
“合并计算”命令根据需要填充该区域。
3、在“数据”选项卡上的“数据工具”组中，单击“合并”。
（如图）
4、在“函数”框中，单击MicrosoftOfficeExcel用来对数据进行合并计算的汇总函数（汇总函数：
是一种计算类型，用于在数据透视表或合并计算表中合并源数据，或在列表或数据库中插入自动分类汇总。
汇总函数的例子包括Sum、Count和Average。
）。
5、如果工作表在另一个工作簿中，请单击“浏览”找到文件，然后单击“确定”以关闭“浏览”对话框。
在“引用”框中输入后跟感叹号的文件路径。
6、键入为区域指定的名称，然后单击“添加”。
对每个区域重复这一步骤。
7、确定希望如何更新合并计算。
请执行下列操作之一：
·若要设置合并计算，以便它在源数据改变时自动更新，请选中“创建连至源数据的链接”复选框。
要点：
只有当该工作表位于其他工作簿中时，才能选中此复选框。
一旦选中此复选框，则不能对在合并计算中包括哪些单元格和区域进行更改。
·若要设置合并计算，以便您可以通过更改合并计算中包括的单元格和区域来手动更新合并计算，请清除“创建连至源数据的链接”复选框。
8、请将“标签位置”下的框留空。
Excel不将源区域中的行或列标签复制到合并计算中。
如果您需要为合并的数据加标签，请从某个源区域复制它们或手动输入它们。
按分类进行合并计算
——以不同的方式组织单独工作表中的数据，但是使用相同的行标签和列标签，以便能够与主工作表中的数据匹配
1、在每个单独的工作表上设置要合并计算的数据。
如何设置数据
·确保每个数据区域都采用列表（列表：
包含相关数据的一系列行，或使用“创建列表”命令作为数据表指定给函数的一系列行。
）格式：
第一行中的每一列都具有标签，同一列中包含相似的数据，并且在列表中没有空行或空列。
·将每个区域分别置于单独的工作表中。
不要将任何区域放在需要放置合并的工作表中。
·确保要合并的列或行的标签具有相同的拼写和大写;例如，标签AnnualAvg.和AnnualAverage是不同的，将不对它们进行合并计算。
·命名每个区域：
选择整个区域，然后在“公式”选项卡的“命名单元格”组中，单击“命名单元格区域”旁边的箭头，然后在“名称”框中键入该区域的名称。
2、在包含要显示在主工作表中的合并数据的单元格区域中，单击左上方的单元格。
注释：
确保在该单元格右下侧为合并的数据留下足够的单元格。
“合并计算”命令根据需要填充该区域。
3、在“数据”选项卡上的“数据工具”组中，单击“合并”。
（如图）
4、在“函数”框中，单击Excel用来对数据进行合并计算的汇总函数（汇总函数：
是一种计算类型，用于在数据透视表或合并计算表中合并源数据，或在列表或数据库中插入自动分类汇总。
汇总函数的例子包括Sum、Count和Average。
）。
5、如果工作表在另一个工作簿中，请单击“浏览”找到文件，然后单击“确定”以关闭“浏览”对话框。
在“引用”框中输入后跟感叹号的文件路径。
6、键入为区域指定的名称，然后单击“添加”。
对每个区域重复这一步骤。
7、确定希望如何更新合并计算。
请执行下列操作之一：
·若要设置合并计算，以便它在源数据改变时自动更新，请选中“创建连至源数据的链接”复选框。
要点：
只有当该工作表位于其他工作簿中时，才能选中此复选框。
一旦选中此复选框，则不能对在合并计算中包括哪些单元格和区域进行更改。
·若要设置合并计算，以便您可以通过更改合并计算中包括的单元格和区域来手动更新合并计算，请清除“创建连至源数据的链接”复选框。
8、在“标签位置”下，选中指示标签在源区域中位置的复选框：
“首行”或“最左列”或两者都选。
注释
·与其他源区域中的标签不匹配的任何标签都会导致合并计算中出现单独的行或列。
·确保不想进行合并计算的任何分类都有仅出现在一个源区域中的唯一标签
通过公式进行合并计算
——在公式中使用对要组合的其他工作表的单元格引用或三维引用（三维引用：
对跨越工作簿中两个或多个工作表的区域的引用。
），因为没有可依赖的一致位置或分类
1、在主工作表上，复制或输入要用于合并计算数据的列或行标签。
2、单击用来存放合并计算数据的单元格。
3、键入一个公式，其中包括对每个工作表上源单元格的单元格引用，或包含要合并计算的数据的三维引用。
如果要合并计算的数据位于不同工作表上的不同单元格中
·输入一个公式，其中包括对其他工作表的单元格引用，对于每个单独的工作表都有一个引用。
例如，要将Sales工作表中上单元格B4、HR工作表上单元格F5和Marketing工作表上单元格B9中的数据合并到主工作表的单元格A2中，将需要输入如下内容：
（如图）
提示：
如果想以非键入方式在公式中输入单元格引用（如Sales!
B4），请在需要使用引用的位置键入公式，单击工作表选项卡，然后单击该单元格。
如果要合并计算的数据位于不同工作表上的相同单元格中
·输入一个包含三维引用的公式，该公式使用指向一系列工作表名称的引用。
例如，要将工作表Sales到Marketing（包括Sales和Marketing工作表）上单元格A2中的数据合并到主工作表的单元格A2中，将需要输入如下内容：
（如图）
注释：
如果将工作簿设置为自动计算公式，则在单独的工作表中的数据改变时，总是会自动更新通过公式进行的合并计算。
更改多个Excel文档数据合并计算
对来自多个Excel工作表中的数据进行合并计算后，可能需要更改对数据进行合并计算的方式。
例如，可能要添加新的地区办事处的工作表，或删除不再存在的部门的工作表，或更改带有三维引用（三维引用：
对跨越工作簿中两个或多个工作表的区域的引用）的公式。
更改按位置或分类进行的合并计算
注释：
只有当您以前未在“合并计算”对话框内选中“创建连至源数据的链接”复选框的情况下，才能更改合并计算。
如果选中该复选框，请单击“关闭”，然后重新创建合并计算。
1、单击合并计算数据的左上角单元格。
2、在“数据”选项卡的“数据工具”组中，单击“合并计算”。
3、请执行下列一项或多项操作：
·为合并计算添加源区域
新的源区域必须在相同位置中有数据（如果以前按位置进行合并计算），或者有与合并计算中其他区域内的那些列标签匹配的列标签（如果以前按分类进行合并计算）。
A如果工作表在另一个工作簿中，请单击“浏览”找到文件，然后单击“
升级会员 