利用Excel編制育種數據清單

郭 莉，馬現斌，李有明,2

（1襄陽市農業科學院，湖北襄陽441057；2主要糧食作物產業化湖北省協同創新中心，武漢434025）

利用Excel編制育種數據清單

郭 莉1，馬現斌1，李有明1,2

（1襄陽市農業科學院，湖北襄陽441057；2主要糧食作物產業化湖北省協同創新中心，武漢434025）

編制育種數據清單是育種的前提準備工作。目前，育種數清單的編制主要使用電子表格Microsoft Excel，且使用較為廣泛。文中分享了使用電子表格Excel編制育種數據清單的幾個小技巧。Excel中一些菜單命令、函數及符號如“if”、“vlookup”、“rand”、“=”、“&”、“$”等的巧用，可以快速、準確地得到信息詳細的育種數據清單。在使用Excel編制育種數據清單時，需要特別注意Excel中有助于解決育種清單數據之間連續性和關聯性的一些函數命令的使用，以解決一些育種數據清單編制問題。

育種數據清單；電子表格；命令；函數；符號；技巧

0 引言

系譜信息管理在育種工作中占有重要地位[1]。育種值的正確估計是建立在可靠數據測量、錄入、整理和分析基礎之上的[2]。就玉米育種來說，育種者每年在大田種植自交系、雜交組合之前，會清種，根據上年情況整理自己的育種清單，編行號，輸入信息等等，由于大部分播種材料都是由往年育種材料自交或雜交而來的，因而編制新的田間記載本可以充分利用以往的數據信息，減少數據輸入量，從而提高效率，為此，有學者編寫過清單查找的相關程序，例如劉紅艷編寫的CodeFinder，一個用于育種系譜信息查找的Excel宏[1]，但這需要一定的程序語言基礎，對育種者來說難度較大。就國內目前的情況來說，一些育種軟件還未普及，李雪等[3]認為，中國種業發展仍處于初級階段，性狀參數等信息的管理大多還停留在借助Excel等通用辦公軟件的半手工階段，時間成本高，數據缺失嚴重，人為誤差大；即使使用育種軟件，很多原始數據的導入仍可以通過Excel表的導入來完成。大多數育種單位或個人使用Microsoft Excel居多，但Excel使用的并不到位。Excel是微軟公司推出的一款功能強大，使用方便的電子表格式數據綜合管理與分析系統軟件，它提供了豐富的函數、強大的數據分析處理和統計分析[4-7]。以Excel中vlookup函數為例，vlookup函數數據之間的關聯性很強，該函數可以實現數據匹配和檢索的功能[8-9]。vlookup函數在財會金融、醫療、教育、人事管理等領域都有廣泛的應用[10-16]，其相關的文獻很多；育種是連續性的，育種數據清單之間的關聯性也很強，但vlookup函數與玉米育種甚至其他農作物直接相關的文獻很少，很可能是育種者在農作物實際育種操作中，關注計算機在農作物育種中的應用較少的緣故。筆者發現一些育種者，在使用Excel編制育種清單時，仍以使用“復制”、“粘貼”、“插入行”、“刪除行”等基礎性操作較多，而Excel中其他公式和菜單命令使用較少，育種清單數據的之間的連續性和關聯完全可以通過Excel中vlookup函數來實現，不需要太高的計算機能力要求?，F將在Excel中處理育種數據清單的一些簡易小技巧分享，用Excel中的公式函數命令等生成一份信息完整的育種數據清單，會較“復制”、“粘貼”等老方法準確、事半功倍，并且簡單易操作。

1 Excel中幾個常見函數及符號的使用

1.1 關于if函數

if函數的語法結構：if（條件，結果1，結果2），如圖1。對滿足條件的數據進行處理，條件滿足則輸出結果1，不滿足則輸出結果2。If函數和其他函數的套用，先用if函數判斷下條件，在嵌套使用其他函數，往往會使函數返回的結果更準確[17]。

1.2 關于vlookup函數

vlookup函數的語法結構：vlookup（查找值，查找的數據列表，返回的列序數，匹配條件），如圖2。vlookup函數是按列查找，最終返回該列所需查詢列序所對應的值。用該函數來實現數據匹配和檢索的功能。并且vlookup函數可對已有的基本數據加以整合，相互之間有連結性，修改基本數據，整合表會自動更新數據，避免重復輸入或修改數據[18]。vlookup函數在財會、醫療、教育等信息管理都有廣泛使用，不乏專門針對vlookup函數巧用的案例文獻[10-16]。

1.3 常用符號$、=、&

“$”是絕對引用符號，可以起“固定”的作用，固定某個變量，可固定列或固定行或固定某一個單元格。以A1單元格為例，$A1表示固定了A列，A$1表示固定了第一行，$A$1表示固定了單元格A1，可以根據需要靈活設置。在運用公式，橫拉或豎拉單元格自動填充時，固定變量后，不易出錯。“=”是賦值符，賦值符并不僅僅在輸入公式時才用得到，其根本的作用是“賦值”，可靈活運用給變量、對象賦值。“&”是連字符，用來合并文本。

2 過程

2.1 編制自交系清單表

2.1.1 輸入入選的上年編號 例如，已有“表1-2016年春玉米大田種植清單”（見圖3），大田編號16P1號至編號16P100號，現收獲、考種、清種后，選擇了其中50個材 料 16P3、16P4、16P8、16P15、16P18、16P20、16P25……，這50個2016年清單中編號的種子準備下一輪2017年春種植，現要“生成”2017年春種植清單。

圖1 Excel中if函數

圖2 Excel中vlookup函數

要“生成”2017年春種植清單，先輸入入選的16P編號，具體操作如下：“新建”一個Excel表格，或者將“表1-2016年春玉米大田種植清單”Excel工作薄的“sheet2”，“重命名”為“表2-2017年春玉米大田種植清單”。在Excel“表2-2017年春玉米大田種植清單”中輸入上年所入選的16P3、16P4、16P8、16P15、16P18、16P20、16P25等50個編號，如圖4。在輸入2016年的50個編號時，不用每個都輸入16P年代號，可先只輸入數字：3、4、8、15、18、20、25……，再利用“&”連接符號，可自動生成編號。例如表格中“B4”單元格為3，在“C4”單元格中輸入16P，“D4”欄單元格輸入公式“=$C $4&B4”可得到16P3，然后列下拉，自動生成16P3、16P4、16P8、16P15、16P18、16P20、16P25等50個編號，多余列可刪除。

圖3 表1-2016年春玉米大田種植清單表

圖4 “表2-2017年春玉米大田種植清單”中輸入上年入選的50個編號

2.1.2 根據上年編號利用if、vlookup函數將表格信息自動完善 已在“表2-2017年春玉米大田種植清單”表中輸入入選的50個上年16P編號（見圖4），現需將表中各個單元格信息一一對應填起，可用if、vlookup函數將信息完善。在“表2-2017年春玉米大田種植清單”表中，以17P1為例，其對應的入選編號是16P3，在“E4”單元格輸入=IF($D4="","",VLOOKUP($D4,'表1-2016年春玉米大田種植清單'!$A$2:$J$101,2,0))，然后按“Enter”鍵可以得到 17P1（16P3）對應的信息“15P215”，見圖5。同理，在“F4”單元格中輸入=IF ($D4="","",VLOOKUP($D4,'表1-2016年春玉米大田種植清單'!$A$2:$J$101,3,0))，然后按“Enter”鍵可以得到對應的信息系名“LP5”，以此類推。

Vlookup函數使用具體說明，要“查找”并“返回”表中17P1(16P3)一系列對應的信息，①vlookup函數（查找值，查找的數據列表，返回的列序數，匹配條件）中第一項，查找的源根為16P3，即查找單元格為D4，輸入vlookup函數中查找項$D4，使用了絕對引用符號$，方便橫拉；②函數第二項查找的數據列表，17P1(16P3)要在“表1-2016年春玉米大田種植清單”中查找及返回，vlookup函數“查找數據區域”為“表1-2016年春玉米大田種植清單”$A$2:$J$101，具體操作為，打開查找信息所在表格“表1-2016年春玉米大田種植清單”，并選定數據信息區域$A$2:$J$101；③函數第3項返回的列序數，17P1(16P3)要得到上季15P編號、系名、穗行數等信息，在vlookup函數中只是“返回的列”不同，上季15P編號、系名、穗行數、行粒數、粒色、粒型、穗型、軸色、結實性在“表1-2016年春玉米大田種植清單”中分別為第2列、第3列、第4列、第5列、第6列、第7列、第8列、第9列、第10列，輸入公式vlookup時查找返回列序號為2、3、4、5、6、7、8、9、10。④vlookup(查找值，查找的數據列表，返回的列序數，匹配條件)中，精確查詢，匹配條件為0(FALSE)。至此vlookup函數語法結構中4個內容填起，在表2“M4”單元格中輸入=IF($D4="","",VLOOKUP($D4,'表1-2016年春玉米大田種植清單'!$A$2:$J$101,10,0))，然后按“Enter”鍵可以得到對應的信息結實性“中”。

由上，表中17P1(16P3)的信息第4行全部填起了，然后下拉，就可以把表中其他行全部自動填起，“表2-2017年春玉米大田種植清單”表中入選的50個材料信息完善。

另外，使用vlookup函數時，要完善的信息excel表和要查找返回的根源信息excel表，如“表2-2017年春玉米大田種植清單”和“表1-2016年春玉米大田種植清單”，可在一個excel工作簿中，也可不在一個excel工作簿中。

2.1.3 if和vlookup函數的結合使用 查找返回的主要功能函數是vlookup函數，但其與if函數結合使用，先用if函數判斷下查找的條件，然后再使用vlookup函數，例如，在“E4”單元格輸入=IF($D4="","", VLOOKUP($D4,'表1-2016年春玉米大田種植清單'! $A$2:$J$101,2,0))，意為如果查的找根源數據單元格$D4如為空“”，if函數與vlookup結合使用返回的數據為空“”，即查找的條件為空格，則結果為空，而單獨使用vlookup函數，只要在原始表格中查不到根源數據，全部返回為“#N/A”[19]。對于返回的“#N/A”，可查找替換功能將其刪除。

2.2 編制雜交組合清單表

圖5 利用if、vlookup函數將表格信息自動完善

2.2.1 編制雜交組合名稱清單表 在大田做雜交組合組配之前，玉米育種者通常都會制定詳細的育種組配計劃清單；或者在大田收獲、考種后，育種者會登記雜交組合種子上編號整理組合鑒定清單?？上戎讳浫腚s交組合父母本編號，由此編號“生成”父母本具體名字，方法同2.1中自交系清單的生成。圖6中單元格D2=IF(B2="","",VLOOKUP(B2,'表2-2017年春玉米大田種植清單---利用公式'!$A$4:$M$53,6,0))，回車得到B119。同理，單元格E2輸入公式得到LP5，然后下拉，可得到所有雜交組合父母本具體名稱。如果想合并父母本名稱得到B119×LP5，見圖7，可在F列單元格輸入雜交符號×，在雜交組合名稱G列單元格G2輸入公式=D2& $F$2&E2，回車得到B119×LP5，然后下拉填完表格。

2.2.2 快速插入對照CK小技巧 已生成雜交組合清單，現需雜交組合鑒定比較清單中快速插入CK1、CK2、CK3、CK4……，例如，需將上圖6中每隔10個雜交組合插入一個對照CK，①可在H列A2“組合17C1”對應的H2單元格輸入數字1，下拉至10，在單元格H12復制H2～H11的數字1～10；②對應的I列單元格I2輸入數字1，單元格I12輸入數字2，對應的J列單元格J2輸入CK，單元格J12輸入CK；③選定I2:J21區域，然后下拉完表格。④選定I列，Excel菜單命令“編輯”-“定位”對話框，數據選項只勾選“常量”，點確定，“定位”I列數字。⑤在“定位”的I列數字上，如圖8，右鍵，“插入”選項，在其上方插入一整行。⑥在已插入行的表中，在K2輸入公式=J3&I3，下拉完，如圖9。⑦對A列，用Excel中“篩選”命令，篩選“空白”如圖10。⑧在A2中輸入公式=K2，然后下拉，如圖11。⑨取消“篩選”命令，得到圖12，所有組合已每隔10個插入了CK1、CK2、CK3、CK4……，刪除其他多余列。

圖6 由雜交組合父母本自交系編號生成其具體名稱

圖7 合并父母本生成雜交組合具體名稱

圖8 在已定位的I列數字，“右鍵”選擇在其上方插入一行

圖9 在已插入行的表格中，K2輸入公式并下拉

2.3 雜交組合隨機排列

用Excel中rand函數快速將雜交組合隨機排列，①在單元格B2中輸入=rand()，如圖13，回車得到一個隨機小數，然后下拉；②對B列隨機小數“排序”命令，就可將A列雜交組合隨機排列；③同理，可將A列、B列在Excel表中復制，每次下拉rand()函數，所得到的小數是不同的，可對rand函數隨機小數“列”進行“排序”，可得到多次不同的雜交組合隨機排列順序，如圖14，隨機小數列隨后可刪除。

圖10 篩選命令選擇A列“空白”

圖11 對A列.篩選命令，在單元格A2輸入=K2，并下拉

3 小結

育種中需要將各種有效信息快速、準確地管理好，以便最大化地使用。編制詳細的育種自交系材料及雜交組合種植清單，或編制雜交組合組配清單在育種中非常重要，是育種的基本前提，若清單不清，后續工作無法順利開展。有育種者在Excel中采用“復制”、“粘貼”操作，復制雜交組合“貼”錯父母本，進而搞錯組配，田間觀察出現問題后才察覺。特別是在父母本姊妹自交系比較多的情況下，系譜細微的差別，一個個“復制”、“粘貼”很容易出錯。

（1）Excel中if和vlookup中2個公式函數的嵌套使用可以達到這樣的效果：清種后，只需要知道種子袋子上一年的編號，使用vlookup函數，就可以將上一年Excel表中此編號對應的品種的信息全部一一對應快速、準確“拉”出來，年份清單相互關聯起來，將要完善的對應信息快速完善。不用Ctrl+F“查找對話框”一個個“查找”，然后“復制”、“粘貼”，這樣操作，不僅量大費時、費力、還易錯。使用vlookup函數時，需注意“查找列值”在根源數據表“查找列”中的唯一性，若“查找值”在根源數據表“查找列”中出現多次，則很可能只返回匹配的第1行的各數據[20]。但一般來說，育種數據清單中不會出現查找列值“同一個編號”對應“不同的材料”；但可以出現查找列值“多個不同編號”對應“一個材料”，vlookup函數查找列值“多個不同編號”，編號是不同的、唯一的，會準確返回相應數值。

圖12 A列取消篩選命令，將表格展開

圖13 在單元格B2中輸入=rand()并下拉

（2）玉米育種田間試驗設計，雜交組合鑒定比較清單需插入對照CK1、CK2、CK3、CK4……，文中的小技巧可統一、一次性插入空白行，再利用“篩選”命令、“=”等公式符號，快速準確、一次性插入所有CK，并編上CK1、CK2、CK3……至于每隔多少個插入一個對照，可靈活類似操作，不用逐個插入空白行并手工輸入CK。

（3）在數量較多，無重復的組合鑒定或品種展示試驗中需隨機排列時，不用進入DPS等統計分析軟件進行試驗設計，用Excel中rand函數可得到隨機小數，將隨機小數按大小“排列”，雜交組合隨即隨機排列，非常簡便。

作為整合在主流大型辦公軟件Microsoft Office中的組件，Excel眾所熟知，使用較廣，在系統兼容性、易用性和普及程度上遠優于其他應用軟件，便于學習和使用[21]。文中介紹了Excel中編制育種數據清單的一些小技巧，現階段一些育種單位或個人，在玉米育種中使用Excel編制或管理育種信息的頻率還是很高的，需注意Excel中函數及菜單命令的使用，特別要注意Excel中有助于育種清單數據之間的連續性和關聯性的一些函數命令的使用，不僅事半功倍，而且更準確，這些函數及菜單命令對育種者來說也易操作實現。現今一些育種軟件還沒普及，在Excel中運用專業計算機程序語言編制相關宏或程序對一些普通玉米育種者來說難度較大，而用Excel自帶的一些函數或命令不失為一種好的選擇。文中介紹的清單處理方法、小技巧，不光適用于玉米育種清單的編制，也可供其他作物的清單編制參考。但隨著信息化、數字化辦公的不斷深入，育種者如何快速、有效整合各方資源，系統數字化，會成為亟待育種者解決的問題，可能到時Excel就不能滿足應用需求，需要真正信息化、數字化的軟件或硬件。目前國內已開發的一些育種軟件會有這樣或那樣的問題[3]，因為只有育種人才最清楚育種的需求，相關育種軟件的開發并不只是編程人員的工作，非常需要育種人員關注相關育種軟件等育種信息化的研究，共同向信息化、數字化努力。

圖14 對隨機小數列“排序”

[1] 劉紅艷.CodeFinder,一個用于育種系譜信息查找的Excel宏[J].華中農業大學學報,2007(1):20-24.

[2] 梁永厚,張文廣,李金泉,等.Excel在絨山羊育種分析中的應用[J].河套大學學報,2008(2):99-103.

[3] 李雪,楊濤.玉米育種信息管理系統的研究[J].江蘇農業科學,2016 (1):418-421.

[4] 劉霞,路永貴,閆當萍.EXCEL在農藥毒力測定中的應用[J].中國農學通報,2009(19):206-208.

[5] 陳偉,王紅梅.在棉花育種田間調查數據處理中運用EXCEL里的公式函數[J].中國棉花,2012(3):35-37.

[6] 仝振祥.用Excel軟件進行柞蠶品種資源保育數據統計分析[J].北方蠶業,2012(2):16-21.

[7] 詹秋文.Excel和SAS在生物統計學的應用比較[J].生物學雜志, 2009(1):74-75,83.

[8] 張丹丹.利用EXCEL的VLOOKUP函數實現關聯數據的應用[J].科技創新導報,2008,34:205.

[9] 高源.巧用VLOOKUP函數批量標準化大數據[J].審計月刊,2015 (9):30-31.

[10]張志華.VLOOKUP在工作中的應用[J].中國現代教育裝備,2014, 23:104-105,115.

[11] 李蓉.巧用Vlookup函數完成信息核對——在招生工作中學生信息核對的應用[J].電腦開發與應用,2015(1):60-62.

[12] 劉彩虹,吳衛兵.利用Vlookup函數制訂藥品采購計劃[J].中國藥事,2015(3):333-338.

[13]楊煜紅.EXCEL查找函數VLOOKUP在財務數據管理中的應用[J].農業網絡信息,2007,12:164-166.

[14]韓桂林,牛興霞,王磊.VLOOKUP在人事管理中的應用實例[J].工業技術與職業教育,2015(4):10-12.

[15] 郭婷婷.用Vlookup函數快速填制數據[J].時代金融,2014,14:230.

[16]梁明.VLOOKUP函數在編制賬簿及報表中的應用[J].中國管理信息化,2013,6:22-23.

[17] 姜雪茸.教學中的IF函數應用進階[J].計算機光盤軟件與應用, 2012,13:249-250.

[18]莊振春.應用Excel的Vlookup和IF函數輕松核算黨費[J].電腦編程技巧與維護,2013,16:49-50.

[19]吳興林,羅鋒,呂偉.VLOOKUP函數在會計應用中的常見#N/A錯誤分析及對策[J].中小企業管理與科技:上旬刊,2011(8):106-107.

[20] 黃昊,張然,張陽,等.兩系統數據整合在臨床路徑數據統計的實踐[J].中國病案,2016,10:55-58.

[21]陳杰毅,楊育紅.EXCEL在實驗室數據管理中的應用[J].中國衛生檢驗雜志,2014,13:1967-1968.

Processing Breeding Data Listings in Excel

Guo Li1,Ma Xianbin1,Li Youming1,2
(1Xiangyang Academy of Agricultural Sciences,Xiangyang 441057,Hubei,China;2Hubei Collaborative Innovation Center for Grain Industry,Wuhan 434025,Hubei,China)

Processing breeding data listings was the precondition of breeding.Currently,Microsoft Excel was mainly and widely used to process breeding data listings.In this article,a few tips were shared by using Microsoft Excel to process breeding data listings.In excel,opportunely using some of the menu commands, functions and symbols,such as"if","vlookup","rand","=","&","$"and so on,the breeding data listing with detailed information could be quickly and accurately obtained.As processing breeding data listings in excel, the use of some functions and commands that could help to solve the continuity and correlation of breeding data listings should be especially noticed,in order to solve the problems of processing breeding data listings.

Breeding Data Listing;Microsoft Excel;Commands;Functions;Symbols;Tips

S513

A論文編號：cjas16090019

郭莉，女，1984年出生，湖北襄陽人，助理研究員，碩士，主要從事玉米育種。通信地址：441057湖北省襄陽市高新區鄧城大道81號，E-mail：842792335@qq.com。

李有明，男，1955年出生，湖北襄陽人，研究員，大專，主要從事玉米育種。通信地址：441057湖北省襄陽市高新區鄧城大道81號，E-mail：1150935603@qq.com。

2016-09-30，

2016-11-14。