口算题卡.jpg
现在小学的数学口算题卡大约一本10几元，如果我们能自己打印，一张A4纸即使是去超市买只需要1毛，至少能省下一半的钱来购买别的课本来拓展眼界
今天我们就来做一个excel工作簿，使用各种函数与其他功能来实现自动出题的口算题卡
设计要求：
对于口算题卡，我们可能会有下面这些要求：
- 整数与小数：所有数字都是整数，还是有一定位数的小数
- 负数与否：如果不允许出现负数，也就是说我们的减法运算中不能出现被减数比减数小的情况
- 分数与否：真分数2/3，假分数3/2，带分数1’1/2
- 方程与否：而且我们有时候需要逆向运算，比如“1+( )=5”这样，但在低年级时我们一般没有这样类似方程的概念，只是已知过程求结果，这里需要将其分开。
- 运算方式：我们需要单一四则运算、随机加减运算、随机乘除运算、随机四则运算
- 两个、三个算数：三个数就涉及到先乘除后加减的问题
- 有无括号：有括号先算括号内的（适用于三个数字的运算）
- 形状周长与面积：正方形、长方形、三角形、平行四边形、梯形、圆型、圆锥体、圆柱体、正方体、长方体
- 单位换算：距离、质量、金额等
- 分数比较大小
由于篇幅及时间的关系，上面的功能我们不能在同一篇文章中实现，这篇文章我们主要来完成两个数（整数或小数）的各种运算（包括方程式）的试卷及答案自动生成的部分，之后如果有时间，我会再继续发布其他的部分的制作流程
注：数据验证是在office 2013以后的版本，之前的版本叫数据有效性，以下统称数据验证
如果您对单元格相对、绝对引用部分不是特别熟悉，请您先看看我的前一篇文章《身份证校验》，下面的这部分就不进行讲解了
制作工作簿
这里截图只是作为展示，并非最终效果，以下的操作步骤比较依赖源文件，请在微信公众号“未央暮城”回复“口算1”获取，进行对比学习
使用数据验证序列设置题型
首先我们先设置只能选择七种运算中的一种
这里使用数据验证，在『数据选项卡』-「数据工具组」~【数据验证下拉数据验证】，打开如下对话框：
数据验证序列.jpg
选择“序列”，然后选择运算方式的单元格范围
其他的要求同理。不再赘述。下面给出这部分的截图
试卷设计1.jpg
使用数据验证整数设置数值范围
这里我们可能需要自定义配置的数据范围，如果有比较高的要求，可以调整我们的数值范围，但小学阶段不可能使用太大的范围而且是口算，因此我们设置数值范围输入限定在[-999,999]
在数据有效性中我们设置允许整数，介于-999到999，如下图：
数据验证整数.jpg
完成这部分，我们给出该工作表的截图：
设计试卷页.jpg
这里是后期的截图，对表格进行了美化处理
两个数运算原理表
首先我们贴出结果图：
后台数据图.jpg
我们有第1、2个数和结果这三个数据，两个数值的运算中数值的取值范围取决于符号，如果是“+或×”，就决定前两个数值的随机范围，如果是“-或÷”就决定后两个数值的随机范围
确定运算符
那么符号如何确定呢？如果是单一的运算，我们可以直接使用vlookup函数来查找对应的符号，
VLOOKUP(lookup_value,table_array,col_index_number,[range_lookup])函数中第一个参数表示查找的值，第二个参数表示在哪个区域进行查找（所选区域的第一列包含查找值，所选区域的其他列与第一列数据个数相同），第三参数表示返回单元格（行是查找值所在行）列为所选区域的第几列，注意这里的列数字范围在所选区域内，不是在整个工作表中，第四参数是匹配的方式，0（FLASE）表示精确匹配，1（TRUE）表示模糊（近似）匹配，工作中一般使用精确匹配，模糊（近似）匹配只有在匹配某一范围时才使用
如果是混合运算，我们就要使用INDEX配合RANDBETWEEN来完成随机符号的输入
INDEX(array,row_num[,column_num])如果第一个参数选择的范围是单行或单列，只需指定前两个参数，返回第row_num[column_num]个数据，否则，后面的两个参数都需要指定，返回该范围的第R行，第C列数据
RANDBETWEEN(bottom,top)函数会返回包括两参数在内的范围中的任意一个整数，如公式=RANDBETWEEN(2,4)会随机显示2,3,4中的一个
同时考虑到公式的简洁性，我们将单一运算查找放在最后，使用IFS即可实现
IF(logical_text,[value_if_true],[value_if_false])比较好理解，如果条件成立，就执行前一条语句，否则执行另外的语句，这里的条件如果是一个恒等式，比如1=1，则一定会执行第一条语句（第二参数）
IFS(logical_text1,value_if_true1,logical_text2,value_if_true2,...)是IF的组合，如果条件1成立，就执行条件1对应的语句，如果条件2成立，就执行条件2的对应语句，依次类推，有点像C语言中的switch，如果找到一个条件成立，就执行该条件所对应要执行的语句，其他剩下的语句不管，比如公式
=IFS(1=2,"1",2=2,"2",3=3,"3")会显示2
因此，我们第一个符号位的公式为=IFS(设计考卷!$C$3=设计考卷!$L$9,INDEX(设计考卷!$M$3:$M$6,RANDBETWEEN(1,4)),设计考卷!$C$3=设计考卷!$L$8,INDEX(设计考卷!$M$3:$M$6,RANDBETWEEN(3,4)),设计考卷!$C$3=设计考卷!$L$7,INDEX(设计考卷!$M$3:$M$6,RANDBETWEEN(1,2)),1,VLOOKUP(设计考卷!$C$3,设计考卷!$L$3:$M$6,2,FALSE))
这里最后的1表示恒成立，如果前面的都不成立，就会使用vlookup来查找
前两个数（即非结果数）的取值范围
接下来我们的数值范围就比较好取了，比如我们的第一个数，如果符号为“+或×”，就应用对应的范围，如果为“-或÷”，就通过第二个数和结果来求，那么如何来取对应范围呢？这里如果是整数的话，使用上面介绍的RANDBETTEN就可以，但如果设置有小数，就要使用RAND函数，然后进行相应的变换，才能得到相应的范围，这里介绍一下RAND函数
RAND()无参函数，返回大于或等于0且小于1的小数，如果想对其进行变换，假设你想变换的范围是[min,max)，需要使用公式
=RAND()*(max-min)+min来实现
好了，这里我们依旧使用IFS函数配合IF函数，公式为=IF(设计考卷!$C$5=设计考卷!$O$3,IFS(两位数后台数据!B2=设计考卷!$M$3,RANDBETWEEN(设计考卷!$D$13,设计考卷!$E$13),两位数后台数据!B2=设计考卷!$M$5,RANDBETWEEN(设计考卷!$D$19,设计考卷!$E$19),两位数后台数据!B2=设计考卷!$M$4,C2+D2,两位数后台数据!B2=设计考卷!$M$6,C2*D2))
同理，第二个数和结果也类似，但这里出现一点问题，如果都使用这种方式，会出现下面循环引用的错误警告，
循环警告.jpg
由于当时没有截图，就在网络上找了这张照片
因此我们使用IF的循环嵌套来代替，于是公式就变为了=IF(设计考卷!$C$5=设计考卷!$O$3,IF(B2=设计考卷!$M$3,RANDBETWEEN(设计考卷!$D$13,设计考卷!$E$13),IF(B2=设计考卷!$M$4,C2+D2,IF(B2=设计考卷!$M$5,RANDBETWEEN(设计考卷!$D$19,设计考卷!$E$19),C2*D2))))
使用这种方法没有警告，也不知道是为什么，很困惑，可能是微软对IFS函数的同时处理不支持
整数与否增加判断
当然，我们还要匹配不是整数的格式，需要增加另一组的IF嵌套，使用RAND*(max-min)+min来替代RANDBETWEEN，于是，完整公式变为=IF(设计考卷!$C$5=设计考卷!$O$3,IF(B11=设计考卷!$M$3,RANDBETWEEN(设计考卷!$D$13,设计考卷!$E$13),IF(B11=设计考卷!$M$4,C11+D11,IF(B11=设计考卷!$M$5,RANDBETWEEN(设计考卷!$D$19,设计考卷!$E$19),C11*D11))),IF(B11=设计考卷!$M$3,RAND()*(设计考卷!$E$13-设计考卷!$D$13)+设计考卷!$D$13,IF(B11=设计考卷!$M$4,C11+D11,IF(B11=设计考卷!$M$5,RAND()*(设计考卷!$E$19-设计考卷!$D$19)+设计考卷!$D$19,C11*D11))))
公式看起来比较乱，但其中的条理比较清晰，这里需要注意括号所在的位置，否则会出现各种错误
小数位数设置
公式还有改进的地方，RAND会生成比较多的小数位数，像0.95687，而我们通常不需要这么多的小数位数，因此我们可以使用ROUND函数，限定小数位数
ROUND(number,number_digits)对第一个参数进行四舍五入的圆整，比如ROUND(1.234,2)=1.23，第二个参数是圆整到的小数位数，可以为正值、负值或零，负值表示取整整数部分的某一位置，比如ROUND(123.156,-2)=100
我们需要用户给出小数部分的位数，因此我们在“设计考卷”中增加一个选项，如果选择的不是整数，我们可以输入要保留的小数位数，设置该单元格的数据验证为公式 =C5<>O3
数据验证小数位数
这里我们还需要提示用户什么时候（条件下）输入，输入什么，于是我们可以在“输入信息”窗口，根据提示来输入对应的信息。
数据验证输入提示.jpg
而在“后台数据”工作表中，可以在所有的RAND所在语句的上一级增加ROUND(RAND()*(B-A)+A,n)，而现在我们的公式变为了=IF(设计考卷!$C$5=设计考卷!$O$3,IF(B2=设计考卷!$M$3,RANDBETWEEN(设计考卷!$D$13,设计考卷!$E$13),IF(B2=设计考卷!$M$4,C2+D2,IF(B2=设计考卷!$M$5,RANDBETWEEN(设计考卷!$D$19,设计考卷!$E$19),C2*D2))),IF(B2=设计考卷!$M$3,ROUND(RAND()*(设计考卷!$E$13-设计考卷!$D$13)+设计考卷!$D$13,设计考卷!$D$5),IF(B2=设计考卷!$M$4,C2+D2,IF(B2=设计考卷!$M$5,ROUND(RAND()*(设计考卷!$E$19-设计考卷!$D$19)+设计考卷!$D$19,设计考卷!$D$5),C2*D2))))
其他的公式类似，这里就不一一展示了
方程式位置设置
对于方程式的位置（就是要填数字的位置），我们使用IF函数配合RANDBETWEEN函数可以比较容易的实现，公式为=IF(设计考卷!$C$4=设计考卷!$N$3,RANDBETWEEN(1,3),0)。
这里要注意：为与数字1,2,3统一，最后这里不是字符“0”，而是数字0
这里使用&来连接两个字符串
答案与试题的显示
之后就可以表述答案与试题的显示问题了，但考虑到两位数的运算中，如果有负数出现在第二位数时，比如-5+-6=-11这种，我们通常在-6前后加上括号以示区分，于是我们使用IF函数加以判断，这里的公式比较简单且与上面的有重复，就不贴出来了。
=IF(C2>0,IFS(E2=0,A2&B2&C2&"= ",E2=1,"（ ）"&B2&C2&"="&D2,E2=2,A2&B2&"（ ）="&D2,E2=3,A2&B2&C2&"=（ ）"),IFS(E2=0,A2&B2&"("&C2&")= ",E2=1,"（ ）"&B2&"("&C2&")="&D2,E2=2,A2&B2&"（ ）="&D2,E2=3,A2&B2&"("&C2&")=（ ）"))，真香！
“试卷”或“答案”工作表设置
做好了上面的步骤，我们只要将相关的数据复制到“试卷”或“答案”工作表即可
接下来我们以“试卷”部分为例来讲解一下制作方式
这里首先给出制作好的完整截图：
试卷完整截图.jpg
标题设置
首先是我们的标题，我们需要根据之前的设置来进行对应的标题输出，比如选择一位小数加减随机运算，我们使用字符串连接，公式为=IF(设计考卷!$C$5=设计考卷!$O$3,"两个整数"&设计考卷!$C$3&"运算测试卷",设计考卷!$D$5&"位小数两个数"&设计考卷!C3&"运算测试卷")
接下来我们需要输入姓名评分等信息，然后我们需要空一行便于之后的调整空间
之后对上面的三行进行“合并后居中”处理，这里班级等信息之间使用空格进行分隔即可，最终效果如下图：
试卷表头.jpg
公式输出试题及答案
正式进入公式的输出阶段：
我们不可能通过一一的复制来实现，这里提供两种方法，首先说第一种：输入1-128的数字序号，然后通过vlookup函数查找序号然后进行对应的填充，但这种方法需要在“后台数据”中添加一列序号，但根据vlookup函数的要求，序号列需要在最左侧列，但我们可以配合使用MATCH和INDEX函数，我们在“后台数据”工作表的最后一列输入序号。如下图：
新增序号.jpg
返回“试卷”工作表，我们输入序号和“.”，留出公式和留白的位置，这里我们要出128道题，使用A4纸打印，类似下图：
试卷初步.jpg
然后我们选中“1”和“5”两个单元格，鼠标移动到“5”单元格的右下角，鼠标左键向下拖动填充，之后进行类似操作，直至完全填充。如下面的动图：
等差填充.gif
之后来对相应的序号进行算式的匹配
MATCH(lookup_value,lookup_array,[match_type])，同vlookup类似，在第二个参数范围内使用第三参数匹配方式查找第一参数，第三参数通常为1，表示精确匹配
如第一个算式单元格的公式为=INDEX(两位数后台数据!F:F,MATCH(A4,两位数后台数据!H:H,0))，这里使用了整列的数据，我们就不用绝对引用了
接下来我们来说一下第二种方法，使用行列函数
row([refrence])和column([refrence])函数分别返回参数单元格的行数和列数，如果不填写参数，返回当前单元格的行列数，比如B3单元格row(B3)返回3，column(B3)返回2
这里我们先将每个单元格的行和列还有需要引用的第几个元素写出来，如果每行有4个单元格的话，如下图：
行列1.jpg
我们在其中找规律，每行中，后一个单元格的引用都比前一个单元格的引用大一，恰好列函数满足这一条件；再来看每一列，后面的单元格引用都比前面的大4，这正好是每一行的单元格个数，上下单元格的行数相差1，如果乘上4，正好就是这个数字，因此我们总结的公式是=(ROW()-3)*4+COLUMN()
但如果我们的范围不是贴一边，即不包含A列和第1行，比如下面的位置那么我们如何使用公式表达呢？
行列2.jpg
通过分析，这里给出一般规律：
如果我们需要在Xm:Yn范围内自左向右，自上而下，“Z”字型无空值，增加值为1，依次填充1到(Y-X+1)(n-m+1)之间的整数的数字，我们每个单元格的公式都是=(ROW()-m)(Y-X+1)+COLUMN()-X+1，这里的Xm、Yn表示类似B2、D4单元格这样的命名方式，而Y-X表示首列到尾列共有多少列，也是每行有多少单元格
好了，回到正题上来，我们有了可以引用的位置，只要使用INDEX函数就可以了，于是我们的题目位置的公式都是=INDEX(两位数后台数据!$F:$F,(ROW()-4)*4+COLUMN()+1)
如果你还想再每个单元格前面加上标号和“.”，我们的公式就升级为=(ROW()-4)*4+COLUMN()&". "&INDEX(两位数后台数据!$F:$F,(ROW()-4)*4+COLUMN()+1)
展示一下最终效果：
试卷设计二.jpg
这里只是提供另一种方法，展示一下小技巧，这样设置的标号无法单独设置格式，需要VBA，但可以将我们的单行公式拆分到两个单元格，这样就只好先书写一行的公式，然后向下填充，我们的公式最后需要增减一定的数才能获得相应的引用位置
做到这里，我们的“答案”工作表也使用类似的方法就可以了，之后我们对各个工作表做一些美化处理，下面列举一些常用的设置
- 文字颜色、背景颜色
- 行高与列宽
- 文本对齐方式
- 使用【F4】重复上一步骤
打印设置
之后我们就可以设置打印的样式了，选择所有的数据（包括表头标题），在『页面布局』-「打印区域下拉设置打印区域」，之后我们使用快捷键【Ctrl+P】进行打印预览即可
如果要确保打印为一张纸，可以在『页面布局选项卡』-「调整为合适大小」~【宽度】与【高度】设置为“一页”
在『页面布局扩展』中切换到页边距，可以设置纸张的边距和是否垂直、水平对
页边距设置.jpg
我们这里默认的是一页的纸张范围，如果你想打印多页，选中上面的两个有数据的相邻单元格，然后向下进行填充即可，最后使用【F9】更新区域
这里需要注意：在打印试卷及答案过程中，如果进行其他操作，公式会自动进行计算，会使试卷与答案的试题不相符，但我们通过以下操作，避免这种情况的发生：
我们设置『公式选项卡』-「计算组」~【计算选项下拉手动】，如果重新计算需要按【F9】
手动计算.jpg
如果你想获得这份源文件，请在微信公众号“未央暮城”后台回复“口算1”即可获取
如果觉得我的文章还不错，欢迎点赞、转发、评论啊！
weyoungtrilight
网友评论