日常工作中，文科学生极少使用编程语言解决现实定量问题，而经常使用微软Office系列办公软件中的Excel来进行简单的数据处理和分析。因此我们试图使用Excel中的众多函数来构建面向问题的算法。

一、问题描述：

   1、希望将高中专业背景为理科的学生与文科学生搭配分组，互助学习。

   2、考虑2人一组，男女搭配。

   3、如果文科学生多于理科学生，则1名理科学生搭配2名文科学生。

二、解决思路：

   1、将理科男同学（简称理男）、理女、文男和文女分别随机排序；

   2、先以文女搭配理男；之后以文男搭配理女；然后剩余文女搭配理女。

   3、如果文科同学>理科同学，将剩余文女搭配随机排序靠前的理男。

三、解决办法：

表一：建立“参数”表，存放各种参数。

1、主要建立2个变量。A=序号；B=随机数。

A列-“序号”是为了将“理男”等变量排序后，方便进行引用比较；

C列-“随机数”使用了随机函数RAND（）的随机数；

B列-“随机数（固定值）”直接将C列的随机数粘帖为数值，这样就避免了每当有数据变动时，C列的随机函数RAND（）就会发生变化的缺点，保证了搭配的稳定性。

函数RAND（）可以返回介于0和1之间的随机数。本例中使用RAND（），而没有使用RANDBETWEEN（），是因为后者在放回抽样中，可能会出现重复的随机数；而RAND（）产生的随机数小数位数较多，不太可能产生重复的随机数

2、参数中建立了分类表。

函数COUNTIFS（）可以对满足多个条件的数据进行计数。

使用COUNTIFS函数对“学生数据”通过“学科背景”和“性别”进行计数。

其中F2=“理男”数量；F3=“理女”数量；F4=“文男”数量；F5=“文女”数量

表二：建立“学生数据表”，存放原始数据以及各变量的随机数

1、主要数据列包括：I=姓名；J=学号；K=联系方式；L=寝室号；M=性别；N=专业背景。

其中关键列为J“学号”，M“性别”，N“专业背景”

2、A、B、C、D列为“理男”、“理女”、“文女”、“文男”引用的随机数列。

“理男”变量中，只对“学科背景”=“理”和“性别”=“男”的记录引用随机数，对不符合条件的记录引用数值“999”，目的是在随后的排序中，只计算理男的排序，忽略理女、文女、文男的影响。因为999是较大的数值，不会参与到随机数的排序中。

“理女”变量只对“学科背景”=“理”和“性别”=“女”的记录引用随机数，忽略其他不符合条件数据的影响。

这些随机数直接引用自“参数”表中的B“随机数（固定）”列，并将随机数×100。目的是只保留整数，与999差距不明显，不影响列宽。

函数IF（）为条件函数，被用来对“学科背景”=“理”和“性别”=“男”进行判断。其格式如下：

IF（条件判断，表达式1，表达式2），如果条件判断为TURE，显示表达式1；条件判断为FALSE，显示表达式2。

函数AND（）为逻辑函数，用来对“学科背景”=“理”和“性别”=“男”是否同时满足进行判断，其格式为：

AND（“学科背景”=“理”，“性别”=“男”），只有当二者同时满足时，返回TURE，否则返回FALSE。

公式为：IF（AND（“学科背景”=“理”，“性别”=“男”），随机数×100，999）

各个班级学生数量是不同的，我们设计了条件函数对超出学生数量的单元格不显示任何值，以便数据的美观。

函数COUNTA（）为计数函数，用来对I“姓名“计数，这个数值表示学生的数量。

函数IF（）为条件函数，用来对超出学生数量的单元格不显示任何值。

“”为不显示标志，表示该单元格内什么都不显示。

公式为：IF（序号> COUNTA（姓名）-1，“”，正常显示）

合并后的公式为：IF（序号> COUNTA（姓名）-1，“”，IF（AND（“学科背景”=“理”，“性别”=“男”），随机数×100，999））

连续出现的IF函数表示嵌套，最多可以嵌套7层。

3、E、F、G、H列为对“理男”、“理女”、“文女”、“文男”的排序号。

函数RANK.EQ（）为排序函数，它对数据进行排序，返回排序号。

函数IF（）用来取消非随机数999的影响。

公式为：IF（理男<999，“”，RANK.EQ（理男））

合并后的公式为：IF（序号> COUNTA（姓名）-1，“”，IF（理男<999，“”，RANK.EQ（理男）））

表三：建立应用表“分组名单”，设置4个变量

1、“序号”变量引用“参数”表中的序号。

条件函数IF（）用来判断序号的数量，

公式为：IF（序号>理男数+理女数，“”，序号）

2、“理科”变量引用了“专业背景”=“理”的学生姓名

函数VLOOKUP（）为引用函数，它依据“序号”对“序列(理男)”进行搜索，例如找出序号为1的理男，并返回其姓名。

公式为：

VLOOKUP（序号，搜索序列(理男)，返回姓名）

VLOOKUP（序号-理男数，搜索序列(理女)，返回姓名）

条件函数IF（）用来对先排列“理男”，后排列“理女”进行判断。

公式为：IF（序号<=理男数，显示理男姓名，显示理女姓名）

合并后的公式为：

IF（序号<=理男数，VLOOKUP（序号，搜索序列(理男)，返回姓名），VLOOKUP（序号-理男数，搜索序列(理女)，返回姓名））

函数IFERROR（）为容错函数，表示当理女姓名显示结束后，什么都不显示。

公式为：IFERROR（有数据就显示数据，“”）

3、“文科A”变量为主要变量，依据序号显示“专业背景”=“文”的学生姓名

函数VLOOKUP（）用来显示学生姓名，公式为：

VLOOKUP（序号，搜索序列(文女)，返回姓名），假设理男数<文女数。

VLOOKUP（序号-理男，搜索序列(文男)，返回姓名），假设文男数<文女数

VLOOKUP（序号-文男，搜索序列(文女)，返回姓名）

条件函数IF（）用来对先排列“文女”，后排列“文男”，再排列“文女”进行判断，进行了3次嵌套。公式为：

IF（序号<=理男数，VLOOKUP（序号，搜索序列(文女)，返回姓名），下一个IF嵌套）

IF（序号<=理男数+文男数，VLOOKUP（序号-理男，搜索序列(文男)，返回姓名），下一个IF嵌套）

IF（序号<=理男数+理女数，VLOOKUP（序号-文男，搜索序列(文女)，返回姓名），“”）

函数IFERROR（）表示当文女姓名显示结束后，什么都不显示。

4、“文科B”变量显示当（文女+文男）>（理男+理女）时，将多出来的文女再次分配给理男。

VLOOKUP（序号+理男数+理女数-文男数，搜索序列(文女)，返回姓名）

函数IFERROR（）表示当文女姓名显示结束后，什么都不显示。

   本算法中应用了大量函数，基本实现了随机分组的功能，同时便于应用。现在拿出来，供同学们学习Excel函数和分析现实问题使用。