新高考“3+1+2”模式已經(jīng)在部分省市實施，這種組合模式有12種之多。那么如何根據(jù)其中的組合對指定的科目分?jǐn)?shù)快速進(jìn)行統(tǒng)計？其實借助Excel老師們可以輕松進(jìn)行統(tǒng)計。這里就以福建高考“3+1+2”模式為例進(jìn)行講解。

比如圖1是某班級學(xué)生的考試成績統(tǒng)計，下面設(shè)置分科統(tǒng)計的成績。注意，這里每個科目的總分并不是和高考完全相同，比如語文、數(shù)學(xué)、英語都是滿分按100分統(tǒng)計，其他科目原始分也沒有轉(zhuǎn)換。這里只是按照每次成績進(jìn)行分類統(tǒng)計，方便同學(xué)們查看自己的組合科目的統(tǒng)計成績。

用好篩選，分科統(tǒng)計自己選擇

因為需要按照“3+1+2”模式進(jìn)行統(tǒng)計，所以為了方便篩選，這里就先對數(shù)據(jù)進(jìn)行轉(zhuǎn)置，將科目名稱放置在A列。選擇A2：J9數(shù)據(jù)復(fù)制，新建一個工作表，定位到A1選擇“粘貼→選擇性粘貼→轉(zhuǎn)置”，進(jìn)行行列轉(zhuǎn)換（圖2）。

全選數(shù)據(jù)，切換到“插入→表格”，插入一個新的表格，這樣就可以利用SUBTOTAL函數(shù)對篩選的科目進(jìn)行統(tǒng)計了。定位的B11輸入公式“=SUBTOTAL（109，B2：B10）”并右拉填充到H11，這里參數(shù)“109”表示排除手動隱藏的行，始終排除已篩選掉的單元格的數(shù)據(jù)進(jìn)行統(tǒng)計求和（圖3）。

好了，現(xiàn)在就可以根據(jù)科目組合進(jìn)行分?jǐn)?shù)統(tǒng)計了。比如福建高考“3+1+2”模式是指3（語文數(shù)學(xué)英語必選）+1（物理/歷史任選一科）+2（化學(xué)、政治、生物、地理任選兩科組合）。假設(shè)現(xiàn)在需要統(tǒng)計3（語文數(shù)學(xué)英語）+1（物理）+2（政治、地理）的成績，那么只要點擊A1篩選按鈕，在下拉列表中依次選擇上述的科目組合，返回表格后就可以看到這個分科的統(tǒng)計數(shù)據(jù)了，可以將每次分科統(tǒng)計結(jié)果復(fù)制到其他位置方便統(tǒng)計查看。

所有組合自定義顯示

因為整個組合科目總計有12種，為了方便查看所有組合的統(tǒng)計，這里還可以借助插入“復(fù)選框”控件來實現(xiàn)。點擊Excel菜單欄的“文件→選項→自定義功能區(qū)”，在右側(cè)主選項卡下勾選“開發(fā)工具”菜單。

為了方便看到選擇科目，這里可以先使用條件格式設(shè)置被選擇科目填充指定顏色。返回Excel窗口，將A2：I4填充為黃色底色顯示（表示必選的“3”，圖4），I2：I4填充為TRUE（表示被選中），I5：I10填充為“False”（表示未被選中，和TRUE值對應(yīng)）。

接著選中A5：I6，點擊“開始→條件格式→新建規(guī)則”，在打開的窗口中選擇“使用公式確定要設(shè)置格式的單元格”，在公式文本框中輸入“=$i5”（絕對引用），點擊“格式→填充”，在打開的窗口中將其填充顏色設(shè)置為“綠色”。表示當(dāng)I5、I6顯示為“TRUE”（表示選中），就填充為綠色顯示，對應(yīng)分科中的“1”。

操作同上，繼續(xù)選中A7：I10數(shù)據(jù)，同上在條件格式中添加一個公式“=$I7”，填充格式選擇藍(lán)色，表示當(dāng)I7：I10顯示為“TRUE”就填充為綠色顯示，對應(yīng)分科中的“2”。這樣只要我們在I列中將對應(yīng)科目的值設(shè)置為TRUE，被選中的科目就會顯示不同的顏色，分別對應(yīng)“3+1+2”的科目（圖5）。

接下來設(shè)置選中科目的求和公式，定位到B11輸入公式“=SUM（B2：B10*$I2：$I10）”，這是一個數(shù)組公式，表示對B2：B10數(shù)據(jù)求和，然后再和$I2：$I10對應(yīng)的求和相乘。當(dāng)I列顯示為False時數(shù)據(jù)是0（即沒有被選中科目是不參與統(tǒng)計）。按提示將公式向右填充到I11，以后按下Ctrl+Shift+Enter即可完成對選擇科目的統(tǒng)計（圖6）。

定位到A14，點擊“開發(fā)工具→插入→表單控件→復(fù)選框控件”，按提示插入一個復(fù)選框控件，并將它改名為“物化政組合”（表示“語數(shù)英+物理+化學(xué)政治”）。接著右擊選擇“設(shè)置控件格式”，將其單元格的鏈接設(shè)置為“$L$1”。

按Alt+F11打開VB設(shè)計窗口，點擊“插入→模塊”按提示輸入下列的代碼：

Sub 復(fù)選框0_Click（）

[i5] = [l1].Value

[i7] = [l1].Value

[i8] = [l1].Value

End Sub

代碼解釋：將單元格I5、I7、I8的值和L1的值關(guān)聯(lián)起來，也就是用戶點擊復(fù)選框變?yōu)楣催x時（即選擇“物化政組合”），L1的值顯示為TRUE，同時對應(yīng)的I5、I7、I8的值和L1一致，顯示為TRUE，從而參與科目分?jǐn)?shù)的統(tǒng)計，其他沒有選擇的科目顯示為False不參與統(tǒng)計。

返回Excel窗口，右擊上述插入的復(fù)選框，右擊選擇“指定宏”，然后選擇上述創(chuàng)建的名稱為“復(fù)選框0_Click”的宏（圖7）。

操作同上，繼續(xù)插入其他復(fù)選框控件，將所有科目的組合和插入的復(fù)選框關(guān)聯(lián)到對應(yīng)的宏，最后將不需要顯示的如I：M列隱藏。這樣以后我們只要點擊相應(yīng)的復(fù)選框，對應(yīng)科目的分?jǐn)?shù)統(tǒng)就會自動進(jìn)行統(tǒng)計，并且選擇的科目會自動添加不同的顏色（圖8）。

點評

當(dāng)然我們也可以將它作為以后的模板使用，后續(xù)只要將各個科目的成績填入，點擊相應(yīng)的組合復(fù)選框就可以很方便地查看各個科目的組合成績了。