口算題卡.jpg

現(xiàn)在小學(xué)的數(shù)學(xué)口算題卡大約一本10幾元虚汛，如果我們能自己打印围来，一張A4紙即使是去超市買只需要1毛跺涤，至少能省下一半的錢來購買別的課本來拓展眼界

今天我們就來做一個excel工作簿，使用各種函數(shù)與其他功能來實現(xiàn)自動出題的口算題卡

設(shè)計要求：

對于口算題卡监透，我們可能會有下面這些要求：

• 整數(shù)與小數(shù)：所有數(shù)字都是整數(shù)桶错，還是有一定位數(shù)的小數(shù)
• 負數(shù)與否：如果不允許出現(xiàn)負數(shù)，也就是說我們的減法運算中不能出現(xiàn)被減數(shù)比減數(shù)小的情況
• 分數(shù)與否：真分數(shù)2/3胀蛮，假分數(shù)3/2院刁，帶分數(shù)1’1/2
• 方程與否：而且我們有時候需要逆向運算，比如“1+( )=5”這樣粪狼，但在低年級時我們一般沒有這樣類似方程的概念退腥，只是已知過程求結(jié)果任岸，這里需要將其分開。
• 運算方式：我們需要單一四則運算狡刘、隨機加減運算享潜、隨機乘除運算、隨機四則運算
• 兩個嗅蔬、三個算數(shù)：三個數(shù)就涉及到先乘除后加減的問題
• 有無括號：有括號先算括號內(nèi)的（適用于三個數(shù)字的運算）

• 形狀周長與面積：正方形剑按、長方形、三角形澜术、平行四邊形吕座、梯形、圓型瘪板、圓錐體吴趴、圓柱體、正方體侮攀、長方體
• 單位換算：距離锣枝、質(zhì)量、金額等
• 分數(shù)比較大小

由于篇幅及時間的關(guān)系兰英，上面的功能我們不能在同一篇文章中實現(xiàn)撇叁，這篇文章我們主要來完成兩個數(shù)（整數(shù)或小數(shù)）的各種運算（包括方程式）的試卷及答案自動生成的部分，之后如果有時間畦贸，我會再繼續(xù)發(fā)布其他的部分的制作流程

注：數(shù)據(jù)驗證是在office 2013以后的版本陨闹，之前的版本叫數(shù)據(jù)有效性，以下統(tǒng)稱數(shù)據(jù)驗證

如果您對單元格相對薄坏、絕對引用部分不是特別熟悉趋厉，請您先看看我的前一篇文章《身份證校驗》，下面的這部分就不進行講解了

制作工作簿

這里截圖只是作為展示胶坠，并非最終效果君账，以下的操作步驟比較依賴源文件，請在微信公眾號“未央暮城”回復(fù)“口算1”獲取沈善，進行對比學(xué)習(xí)

使用數(shù)據(jù)驗證序列設(shè)置題型

首先我們先設(shè)置只能選擇七種運算中的一種

這里使用數(shù)據(jù)驗證乡数，在『數(shù)據(jù)選項卡』-「數(shù)據(jù)工具組」~【數(shù)據(jù)驗證下拉數(shù)據(jù)驗證】，打開如下對話框：

數(shù)據(jù)驗證序列.jpg

選擇“序列”闻牡，然后選擇運算方式的單元格范圍

其他的要求同理净赴。不再贅述。下面給出這部分的截圖

試卷設(shè)計1.jpg

使用數(shù)據(jù)驗證整數(shù)設(shè)置數(shù)值范圍

這里我們可能需要自定義配置的數(shù)據(jù)范圍罩润，如果有比較高的要求玖翅，可以調(diào)整我們的數(shù)值范圍，但小學(xué)階段不可能使用太大的范圍而且是口算，因此我們設(shè)置數(shù)值范圍輸入限定在[-999,999]

在數(shù)據(jù)有效性中我們設(shè)置允許整數(shù)烧栋，介于-999到999，如下圖：

數(shù)據(jù)驗證整數(shù).jpg

完成這部分拳球，我們給出該工作表的截圖：

設(shè)計試卷頁.jpg

這里是后期的截圖审姓，對表格進行了美化處理

兩個數(shù)運算原理表

首先我們貼出結(jié)果圖：

后臺數(shù)據(jù)圖.jpg

我們有第1、2個數(shù)和結(jié)果這三個數(shù)據(jù)祝峻，兩個數(shù)值的運算中數(shù)值的取值范圍取決于符號魔吐，如果是“+或×”，就決定前兩個數(shù)值的隨機范圍莱找，如果是“-或÷”就決定后兩個數(shù)值的隨機范圍

確定運算符

那么符號如何確定呢酬姆？如果是單一的運算，我們可以直接使用vlookup函數(shù)來查找對應(yīng)的符號奥溺，

VLOOKUP(lookup_value,table_array,col_index_number,[range_lookup])函數(shù)中第一個參數(shù)表示查找的值辞色，第二個參數(shù)表示在哪個區(qū)域進行查找（所選區(qū)域的第一列包含查找值，所選區(qū)域的其他列與第一列數(shù)據(jù)個數(shù)相同）浮定，第三參數(shù)表示返回單元格（行是查找值所在行）列為所選區(qū)域的第幾列相满，注意這里的列數(shù)字范圍在所選區(qū)域內(nèi)，不是在整個工作表中桦卒，第四參數(shù)是匹配的方式立美，0（FLASE）表示精確匹配，1（TRUE）表示模糊（近似）匹配方灾，工作中一般使用精確匹配建蹄，模糊（近似）匹配只有在匹配某一范圍時才使用

如果是混合運算，我們就要使用INDEX配合RANDBETWEEN來完成隨機符號的輸入

INDEX(array,row_num[,column_num])如果第一個參數(shù)選擇的范圍是單行或單列裕偿，只需指定前兩個參數(shù)洞慎，返回第row_num[column_num]個數(shù)據(jù)，否則嘿棘，后面的兩個參數(shù)都需要指定拢蛋，返回該范圍的第R行，第C列數(shù)據(jù)

RANDBETWEEN(bottom,top)函數(shù)會返回包括兩參數(shù)在內(nèi)的范圍中的任意一個整數(shù)蔫巩，如公式=RANDBETWEEN(2,4)會隨機顯示2,3,4中的一個

同時考慮到公式的簡潔性谆棱，我們將單一運算查找放在最后，使用IFS即可實現(xiàn)

IF(logical_text,[value_if_true],[value_if_false])比較好理解圆仔，如果條件成立垃瞧，就執(zhí)行前一條語句，否則執(zhí)行另外的語句坪郭，這里的條件如果是一個恒等式个从，比如1=1泛鸟，則一定會執(zhí)行第一條語句（第二參數(shù)）

IFS(logical_text1,value_if_true1,logical_text2,value_if_true2,...)是IF的組合藕畔，如果條件1成立，就執(zhí)行條件1對應(yīng)的語句，如果條件2成立鉴逞，就執(zhí)行條件2的對應(yīng)語句，依次類推长窄，有點像C語言中的switch按厘，如果找到一個條件成立，就執(zhí)行該條件所對應(yīng)要執(zhí)行的語句碳默，其他剩下的語句不管贾陷，比如公式=IFS(1=2,"1",2=2,"2",3=3,"3")會顯示2

因此，我們第一個符號位的公式為=IFS(設(shè)計考卷!$C$3=設(shè)計考卷!$L$9,INDEX(設(shè)計考卷!$M$3:$M$6,RANDBETWEEN(1,4)),設(shè)計考卷!$C$3=設(shè)計考卷!$L$8,INDEX(設(shè)計考卷!$M$3:$M$6,RANDBETWEEN(3,4)),設(shè)計考卷!$C$3=設(shè)計考卷!$L$7,INDEX(設(shè)計考卷!$M$3:$M$6,RANDBETWEEN(1,2)),1,VLOOKUP(設(shè)計考卷!$C$3,設(shè)計考卷!$L$3:$M$6,2,FALSE))

這里最后的1表示恒成立嘱根，如果前面的都不成立髓废，就會使用vlookup來查找

前兩個數(shù)（即非結(jié)果數(shù)）的取值范圍

接下來我們的數(shù)值范圍就比較好取了，比如我們的第一個數(shù)该抒，如果符號為“+或×”慌洪，就應(yīng)用對應(yīng)的范圍，如果為“-或÷”凑保，就通過第二個數(shù)和結(jié)果來求蒋譬，那么如何來取對應(yīng)范圍呢？這里如果是整數(shù)的話愉适，使用上面介紹的RANDBETTEN就可以犯助，但如果設(shè)置有小數(shù)，就要使用RAND函數(shù)维咸，然后進行相應(yīng)的變換剂买，才能得到相應(yīng)的范圍，這里介紹一下RAND函數(shù)

RAND()無參函數(shù)癌蓖，返回大于或等于0且小于1的小數(shù)瞬哼，如果想對其進行變換，假設(shè)你想變換的范圍是[min,max)租副，需要使用公式=RAND()*(max-min)+min來實現(xiàn)

好了坐慰，這里我們依舊使用IFS函數(shù)配合IF函數(shù)，公式為=IF(設(shè)計考卷!$C$5=設(shè)計考卷!$O$3,IFS(兩位數(shù)后臺數(shù)據(jù)!B2=設(shè)計考卷!$M$3,RANDBETWEEN(設(shè)計考卷!$D$13,設(shè)計考卷!$E$13),兩位數(shù)后臺數(shù)據(jù)!B2=設(shè)計考卷!$M$5,RANDBETWEEN(設(shè)計考卷!$D$19,設(shè)計考卷!$E$19),兩位數(shù)后臺數(shù)據(jù)!B2=設(shè)計考卷!$M$4,C2+D2,兩位數(shù)后臺數(shù)據(jù)!B2=設(shè)計考卷!$M$6,C2*D2))

同理用僧，第二個數(shù)和結(jié)果也類似结胀，但這里出現(xiàn)一點問題，如果都使用這種方式责循，會出現(xiàn)下面循環(huán)引用的錯誤警告糟港，

循環(huán)警告.jpg

由于當時沒有截圖，就在網(wǎng)絡(luò)上找了這張照片

因此我們使用IF的循環(huán)嵌套來代替院仿，于是公式就變?yōu)榱?code>=IF(設(shè)計考卷!$C$5=設(shè)計考卷!$O$3,IF(B2=設(shè)計考卷!$M$3,RANDBETWEEN(設(shè)計考卷!$D$13,設(shè)計考卷!$E$13),IF(B2=設(shè)計考卷!$M$4,C2+D2,IF(B2=設(shè)計考卷!$M$5,RANDBETWEEN(設(shè)計考卷!$D$19,設(shè)計考卷!$E$19),C2*D2))))

使用這種方法沒有警告秸抚，也不知道是為什么速和，很困惑，可能是微軟對IFS函數(shù)的同時處理不支持

整數(shù)與否增加判斷

當然剥汤，我們還要匹配不是整數(shù)的格式颠放，需要增加另一組的IF嵌套，使用RAND*(max-min)+min來替代RANDBETWEEN吭敢，于是碰凶，完整公式變?yōu)?code>=IF(設(shè)計考卷!$C$5=設(shè)計考卷!$O$3,IF(B11=設(shè)計考卷!$M$3,RANDBETWEEN(設(shè)計考卷!$D$13,設(shè)計考卷!$E$13),IF(B11=設(shè)計考卷!$M$4,C11+D11,IF(B11=設(shè)計考卷!$M$5,RANDBETWEEN(設(shè)計考卷!$D$19,設(shè)計考卷!$E$19),C11*D11))),IF(B11=設(shè)計考卷!$M$3,RAND()*(設(shè)計考卷!$E$13-設(shè)計考卷!$D$13)+設(shè)計考卷!$D$13,IF(B11=設(shè)計考卷!$M$4,C11+D11,IF(B11=設(shè)計考卷!$M$5,RAND()*(設(shè)計考卷!$E$19-設(shè)計考卷!$D$19)+設(shè)計考卷!$D$19,C11*D11))))

公式看起來比較亂，但其中的條理比較清晰省有，這里需要注意括號所在的位置痒留，否則會出現(xiàn)各種錯誤

小數(shù)位數(shù)設(shè)置

公式還有改進的地方谴麦，RAND會生成比較多的小數(shù)位數(shù)蠢沿，像0.95687，而我們通常不需要這么多的小數(shù)位數(shù)匾效，因此我們可以使用ROUND函數(shù)舷蟀，限定小數(shù)位數(shù)

ROUND(number,number_digits)對第一個參數(shù)進行四舍五入的圓整，比如ROUND(1.234,2)=1.23面哼，第二個參數(shù)是圓整到的小數(shù)位數(shù)野宜，可以為正值、負值或零魔策，負值表示取整整數(shù)部分的某一位置匈子，比如ROUND(123.156,-2)=100

我們需要用戶給出小數(shù)部分的位數(shù)，因此我們在“設(shè)計考卷”中增加一個選項闯袒，如果選擇的不是整數(shù)虎敦，我們可以輸入要保留的小數(shù)位數(shù)，設(shè)置該單元格的數(shù)據(jù)驗證為公式 =C5<>O3

數(shù)據(jù)驗證小數(shù)位數(shù)

這里我們還需要提示用戶什么時候（條件下）輸入政敢，輸入什么其徙，于是我們可以在“輸入信息”窗口，根據(jù)提示來輸入對應(yīng)的信息喷户。

數(shù)據(jù)驗證輸入提示.jpg

而在“后臺數(shù)據(jù)”工作表中唾那，可以在所有的RAND所在語句的上一級增加ROUND(RAND()*(B-A)+A,n)，而現(xiàn)在我們的公式變?yōu)榱?code>=IF(設(shè)計考卷!$C$5=設(shè)計考卷!$O$3,IF(B2=設(shè)計考卷!$M$3,RANDBETWEEN(設(shè)計考卷!$D$13,設(shè)計考卷!$E$13),IF(B2=設(shè)計考卷!$M$4,C2+D2,IF(B2=設(shè)計考卷!$M$5,RANDBETWEEN(設(shè)計考卷!$D$19,設(shè)計考卷!$E$19),C2*D2))),IF(B2=設(shè)計考卷!$M$3,ROUND(RAND()*(設(shè)計考卷!$E$13-設(shè)計考卷!$D$13)+設(shè)計考卷!$D$13,設(shè)計考卷!$D$5),IF(B2=設(shè)計考卷!$M$4,C2+D2,IF(B2=設(shè)計考卷!$M$5,ROUND(RAND()*(設(shè)計考卷!$E$19-設(shè)計考卷!$D$19)+設(shè)計考卷!$D$19,設(shè)計考卷!$D$5),C2*D2))))

其他的公式類似褪尝，這里就不一一展示了

方程式位置設(shè)置

對于方程式的位置（就是要填數(shù)字的位置）闹获，我們使用IF函數(shù)配合RANDBETWEEN函數(shù)可以比較容易的實現(xiàn)，公式為=IF(設(shè)計考卷!$C$4=設(shè)計考卷!$N$3,RANDBETWEEN(1,3),0)河哑。

這里要注意：為與數(shù)字1,2,3統(tǒng)一昌罩，最后這里不是字符“0”，而是數(shù)字0

這里使用&來連接兩個字符串

答案與試題的顯示

之后就可以表述答案與試題的顯示問題了灾馒，但考慮到兩位數(shù)的運算中茎用，如果有負數(shù)出現(xiàn)在第二位數(shù)時，比如-5+-6=-11這種，我們通常在-6前后加上括號以示區(qū)分轨功，于是我們使用IF函數(shù)加以判斷旭斥，這里的公式比較簡單且與上面的有重復(fù)，就不貼出來了古涧。

=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&")=（ ）"))垂券，真香！

“試卷”或“答案”工作表設(shè)置

做好了上面的步驟羡滑，我們只要將相關(guān)的數(shù)據(jù)復(fù)制到“試卷”或“答案”工作表即可

接下來我們以“試卷”部分為例來講解一下制作方式

這里首先給出制作好的完整截圖：

試卷完整截圖.jpg

標題設(shè)置

首先是我們的標題菇爪，我們需要根據(jù)之前的設(shè)置來進行對應(yīng)的標題輸出，比如選擇一位小數(shù)加減隨機運算柒昏，我們使用字符串連接凳宙，公式為=IF(設(shè)計考卷!$C$5=設(shè)計考卷!$O$3,"兩個整數(shù)"&設(shè)計考卷!$C$3&"運算測試卷",設(shè)計考卷!$D$5&"位小數(shù)兩個數(shù)"&設(shè)計考卷!C3&"運算測試卷")

接下來我們需要輸入姓名評分等信息，然后我們需要空一行便于之后的調(diào)整空間

之后對上面的三行進行“合并后居中”處理职祷，這里班級等信息之間使用空格進行分隔即可氏涩，最終效果如下圖：

試卷表頭.jpg

公式輸出試題及答案

正式進入公式的輸出階段：

我們不可能通過一一的復(fù)制來實現(xiàn)，這里提供兩種方法有梆，首先說第一種：輸入1-128的數(shù)字序號是尖，然后通過vlookup函數(shù)查找序號然后進行對應(yīng)的填充，但這種方法需要在“后臺數(shù)據(jù)”中添加一列序號泥耀，但根據(jù)vlookup函數(shù)的要求饺汹，序號列需要在最左側(cè)列，但我們可以配合使用MATCH和INDEX函數(shù)痰催，我們在“后臺數(shù)據(jù)”工作表的最后一列輸入序號兜辞。如下圖：

新增序號.jpg

返回“試卷”工作表，我們輸入序號和“.”陨囊，留出公式和留白的位置弦疮，這里我們要出128道題，使用A4紙打印蜘醋，類似下圖：

試卷初步.jpg

然后我們選中“1”和“5”兩個單元格胁塞，鼠標移動到“5”單元格的右下角，鼠標左鍵向下拖動填充压语，之后進行類似操作啸罢，直至完全填充。如下面的動圖：

等差填充.gif

之后來對相應(yīng)的序號進行算式的匹配

MATCH(lookup_value,lookup_array,[match_type])胎食，同vlookup類似扰才，在第二個參數(shù)范圍內(nèi)使用第三參數(shù)匹配方式查找第一參數(shù)，第三參數(shù)通常為1厕怜，表示精確匹配

如第一個算式單元格的公式為=INDEX(兩位數(shù)后臺數(shù)據(jù)!F:F,MATCH(A4,兩位數(shù)后臺數(shù)據(jù)!H:H,0))衩匣，這里使用了整列的數(shù)據(jù)蕾总，我們就不用絕對引用了

接下來我們來說一下第二種方法，使用行列函數(shù)

row([refrence])和column([refrence])函數(shù)分別返回參數(shù)單元格的行數(shù)和列數(shù)琅捏，如果不填寫參數(shù)生百，返回當前單元格的行列數(shù)，比如B3單元格row(B3)返回3柄延，column(B3)返回2

這里我們先將每個單元格的行和列還有需要引用的第幾個元素寫出來蚀浆，如果每行有4個單元格的話，如下圖：

行列1.jpg

我們在其中找規(guī)律搜吧，每行中市俊，后一個單元格的引用都比前一個單元格的引用大一，恰好列函數(shù)滿足這一條件滤奈；再來看每一列摆昧，后面的單元格引用都比前面的大4，這正好是每一行的單元格個數(shù)僵刮，上下單元格的行數(shù)相差1据忘，如果乘上4鹦牛，正好就是這個數(shù)字搞糕，因此我們總結(jié)的公式是=(ROW()-3)*4+COLUMN()

但如果我們的范圍不是貼一邊，即不包含A列和第1行曼追，比如下面的位置那么我們?nèi)绾问褂霉奖磉_呢窍仰？

行列2.jpg

通過分析，這里給出一般規(guī)律：

如果我們需要在Xm:Yn范圍內(nèi)自左向右礼殊，自上而下驹吮，“Z”字型無空值，增加值為1晶伦，依次填充1到(Y-X+1)(n-m+1)之間的整數(shù)的數(shù)字碟狞，我們每個單元格的公式都是=(ROW()-m)(Y-X+1)+COLUMN()-X+1，這里的Xm婚陪、Yn表示類似B2族沃、D4單元格這樣的命名方式，而Y-X表示首列到尾列共有多少列泌参，也是每行有多少單元格

好了脆淹，回到正題上來，我們有了可以引用的位置沽一，只要使用INDEX函數(shù)就可以了盖溺，于是我們的題目位置的公式都是=INDEX(兩位數(shù)后臺數(shù)據(jù)!$F:$F,(ROW()-4)*4+COLUMN()+1)

如果你還想再每個單元格前面加上標號和“.”，我們的公式就升級為=(ROW()-4)*4+COLUMN()&". "&INDEX(兩位數(shù)后臺數(shù)據(jù)!$F:$F,(ROW()-4)*4+COLUMN()+1)

展示一下最終效果：

試卷設(shè)計二.jpg

這里只是提供另一種方法铣缠，展示一下小技巧烘嘱，這樣設(shè)置的標號無法單獨設(shè)置格式昆禽，需要VBA，但可以將我們的單行公式拆分到兩個單元格蝇庭，這樣就只好先書寫一行的公式为狸，然后向下填充，我們的公式最后需要增減一定的數(shù)才能獲得相應(yīng)的引用位置

做到這里遗契，我們的“答案”工作表也使用類似的方法就可以了辐棒，之后我們對各個工作表做一些美化處理，下面列舉一些常用的設(shè)置

• 文字顏色牍蜂、背景顏色
• 行高與列寬
• 文本對齊方式
• 使用【F4】重復(fù)上一步驟

打印設(shè)置

之后我們就可以設(shè)置打印的樣式了漾根，選擇所有的數(shù)據(jù)（包括表頭標題），在『頁面布局』-「打印區(qū)域下拉設(shè)置打印區(qū)域」鲫竞，之后我們使用快捷鍵【Ctrl+P】進行打印預(yù)覽即可

如果要確保打印為一張紙辐怕，可以在『頁面布局選項卡』-「調(diào)整為合適大小」~【寬度】與【高度】設(shè)置為“一頁”

在『頁面布局擴展』中切換到頁邊距，可以設(shè)置紙張的邊距和是否垂直从绘、水平對

頁邊距設(shè)置.jpg

我們這里默認的是一頁的紙張范圍寄疏，如果你想打印多頁，選中上面的兩個有數(shù)據(jù)的相鄰單元格僵井，然后向下進行填充即可陕截，最后使用【F9】更新區(qū)域

這里需要注意：在打印試卷及答案過程中，如果進行其他操作批什，公式會自動進行計算农曲，會使試卷與答案的試題不相符，但我們通過以下操作驻债，避免這種情況的發(fā)生：

我們設(shè)置『公式選項卡』-「計算組」~【計算選項下拉手動】乳规，如果重新計算需要按【F9】

手動計算.jpg

如果你想獲得這份源文件，請在微信公眾號“未央暮城”后臺回復(fù)“口算1”即可獲取

如果覺得我的文章還不錯合呐，歡迎點贊暮的、轉(zhuǎn)發(fā)、評論疤适怠冻辩！

weyoungtrilight