基于VLOOKUP的數據調用模板在學生體檢分析中的應用

北京市東城區疾病預防控制中心(100009)

勉麗娜 陳 輝 楊 微

分析轄區中小學生生長發育、常見病檢出現狀及發展趨勢是學校衛生工作者每年必須要做的一項常規性工作[1-3]。而自啟用《北京市中小學健康信息管理系統》以來,雖然在數據保存及查詢方面提供了極大地方便,然而,由于系統下載的數據非常詳細,并且很多數據都在不同的工作表中,而我們在分析的過程中,往往只需要對不同學段、不同性別的檢出率情況進行分析。因此,就需要將這些零散的數據重新編輯在一個工作表中使用,此過程雖然沒有技術難度,但在操作過程中既浪費時間又很容易出錯。因此,建立一個只需將原始數據粘貼后就可以直接輸出結果的數據調用模板具有非常重要的現實意義。本文就以2015-2016學年度的不同學段不同性別的學生肥胖檢出率的比較為例,建立一個以VLOOKUP函數為基礎的數據調用模板。

VLOOKUP函數介紹

VLOOKUP函數是excel中的一個縱向查找函數,功能是按列查找,最終返回該列所需查詢列序所對應的值。

該函數的語法規則如下:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

參數說明見表1。

表1 VLOOKUP參數說明

建立數據調用模板

1.首先需要在《北京市中小學健康信息管理系統》中下載2015-2016學年度的“營養”報表,并將報表內的三個工作表BMI(年級男)、BMI(年級女)、BMI(年級男女合)分別復制粘貼到新工作簿中,分別重命名為“BMI男1516”、“BMI女1516”、“BMI合1516”,同時在該工作簿中建立一個新工作表“模板”,具體見圖1。

圖1 數據調用模板的總體結構

需要注意的是,在各個工作表的年級一列中,從小學到中職的各個年級的名稱均需不同才能運用VLOOKUP函數準確識別,因此,需要將各學段的“小計”分別改為“小學小計”、 “初中小計”、 “高中小計”、 “中職小計”,同時需要取消“總計”的合并單元格,并將“總計”粘貼至統計表B列。

2.點擊excel工具欄中的公式，定義名稱,分別將BMI男1516、BMI女1516、BMI合1516這3個工作表的B6:R26區域定義名稱為“BMI男15”、“BMI女15”、“BMI合15”。

3.以2015-2016學年度不同學段不同性別的肥胖檢出率以柱狀圖的形式比較為例,在“模板”工作表中的A3、A4、A5、A6單元格分別輸入“小學小計”、 “初中小計”、 “高中小計”、 “中職小計”,并在B3單元格內輸入公式:=VLOOKUP($A3,BMI男15,17,FALSE),該公式的涵義是:在“BMI男1516”工作表定義的“BMI男15”區域內,查找“小學合計”,查找到后,返回“小學合計”這一行的第17列,即“肥胖檢出率”一列的值。返回到B3的數值不是百分比形式,所以要在C3輸入公式:=B3*100,同理得到其他學段的不同性別的肥胖檢出率,插入柱狀圖,即可得到2015-2016學年度不同學段不同性別的肥胖檢出率比較的柱狀圖。

4.不同學段不同性別學生肥胖檢出率比較的模板制作完成,在下一年度撰寫分析報告時,只需將新下載的“營養”報表中的BMI(年級男)、BMI(年級女)、BMI(年級男女合)的“B6～R26”區域分別粘貼到模板中的“B6-R26”區域,此時“模板”工作表中就直接生成了新的數據源的柱狀圖,可以說是一步到位。

模板的使用范圍

以本模板為基礎,可以制作《北京市中小學健康信息管理系統》下載的各年級、各學段、不同性別間的視力不良、營養不良、超重、肥胖、貧血、恒牙患齲、沙眼、腸道蠕蟲檢出情況及身高、體重、肺活量等級評價的數據調用。

模板使用過程中的注意事項

1.《北京市中小學健康信息管理系統》下載的數據一般情況下兩年度的行和列結構不會發生太大變化,但是在使用之前還是需要先觀察兩年度的行數和列數是否一致。對于行的增減,只需要修改定義名稱的查找區域,但如果列有增減,則除了要修改定義名稱的查找區域外,還要調整VLOOKUP函數的第三個參數。

2.在定義VLOOKUP函數的第二個參數的查找區域時,第一個參數要查找的值必須在此區域的第1列,并且為了方便下拉填充其他調用結果,第一個參數需要絕對引用列。

3.在使用VLOOKUP函數時,因為需要精確查找,因此第四個參數必須是FALSE或0。

結 論

excel軟件作為一種功能強大的數據處理工具,公式與函數是它的核心,尤其是VLOOKUP函數在查找和引用數據時是一個非常實用的函數,被廣泛應用于財務、人事、學生體質健康管理等領域[4-7],然而它卻并不為很多醫務工作者所知,因此,本文以建立VLOOKUP函數為基礎的數據調用模板為例,目的是向廣大醫務工作者推廣本函數,因為此函數的使用,可以在保證數據準確的同時,極大的節省時間,提高工作效率。