鳥類バイオサイエンス研究センターにおける業務の紹介(2 年目)～データ入力の効率化と解析に ExcelVBA を活用してみた～○A)

生物・生体技術支援室高間瑠佳 A)
生物機能解析・実験実習技術グループ

概要
鳥類バイオサイエンス研究センターにおけるニワトリの生存記録データの管理業務において、ExcelVBA を活用してマクロを作成することで入力作業を効率化することに成功した。これによって入力時間を大幅に短縮し、入力データの確認を確実、かつ正確に行うことが可能になった。それによって鶏舎における作業時間を多く確保できるようになったため、昨年度には習得できなかった作業の技術を習得できるようになった。
また、データの解析にもマクロを導入し、系統による生育状況の差異を明確にすることができた。
今回は、データの管理方法の現状と改善点、および導入したマクロを紹介する。

1

鳥類バイオサイエンス研究センターとはニワトリとウズラは鳥類を代表するモデル動物であり、ライフサイエンス研究において不可欠な生物資源である。生命農学研究科の附属施設である鳥類バイオサイエンス研究センターは、研究用のニワトリ・ウズラを数多く保存し、また学内の研究室だけでなく外部の大学・研究機関にも生体・種卵・血液・臓器等のリソースを提供することを目的として運営されている。またナショナルバイオリソースプロジェクト（NBRP）「ニワトリ・ウズラ」の中核機関として、我が国のリソース事業における重要な役割を担っている。
なお本センターにおける業務は、本学動物実験委員会の承認を得て行っている。

2

センターにおけるデータ管理

2.1

センターにおける飼育の現状

現在センターで飼育管理しているニワトリは 39 系統でその個体数は約 1100 羽（雛 500、成鶏 600）である。種卵を雛に孵す孵卵と、孵化から約 6 ヶ月までの雛の飼育は育雛舎で行う。その後ニワトリの成体を系統維持鶏舎（成鶏舎）に移し、系統保存のための飼育と種卵を集める集卵を行う。
2.2

データの収集飼育における生存データは孵卵作業から始まる。
『入卵数』は孵卵器に入れた種卵の数である。孵化 4 日前に発生していない卵を孵卵器から除去し、発生している種卵を孵化棚「ハッチャー」に移す。この作業を検卵と言い、この時ハッチャーに移す卵の数は『下卵数』となる。そして、孵化予定日に孵化した雛の数は『孵化数』となる。これら 3 つの数値は入卵日・孵化日とともに「孵化簿」に系統毎に記録される。孵化した雛にはそれぞれ個体識別のため「翼帯番号」が 0001 番から 9999 番までの数字で順次付与される（9999 番まで到達したら 0001 番に戻る。年間孵化数と同年の個体数が 10,000 羽を超えることがないため、同じ個体番号が同じ年に生じることはない）。
孵化から 3 ヶ月齢までの雛は育雛室で飼育される。3 ヶ月を過ぎると中雛となり中大雛室に移動する。中大雛室に移す日を「中大雛室移動日」として翼帯番号とともに記録する。生後 6 ヶ月を過ぎると成体となるので系統維持鶏舎に移す。この日を「成鶏舎移動日」として翼帯番号とともに記録する。これを「移動記録票」にまとめる。
飼育途中において病気等で死亡したり、学内外に生体を譲渡したりする場合は「除籍」となり、その日を「除籍日」として翼帯番号とともに記録する。これを「除籍記録票」にまとめる。

孵卵幼除成雛籍

鶏中 大 雛図 1. 飼育手順とデータの収集（黄矢印は孵化簿・青矢印は移動票・赤矢印は除籍票にそれぞれ記載）
2.3

昨年度までのデータ管理

孵化簿のデータのうち入卵数と孵化数はエクセルデータ「孵化集計」に記録し、各系統の孵化率をまとめる。また孵化日・孵化数・翼帯番号をエクセルデータ「生存記録」に入力する。そして、「生存記録」データには移動記録票と除籍記録票の記録を随時追加する。また除籍記録票はエクセルデータ「除籍集計」に月報の形式でまとめる。
「生存記録」に孵化日を入力する作業は、同日に孵化した 80~100 羽分の翼帯番号に対し同じ日付をコピーするだけなので、エクセルでは単純作業である。しかし系統毎もしくは個体でも成長スピードには差がある
ため、孵化日が同じでも移動日が同じとは限らない。さらに病気で死亡する日は個体ごとに異なるので、死亡個体が発生した場合は、その都度、生存記録から翼帯番号を検索し、1 つずつ除籍日を入力する手間が発生する。1 年間に孵化するニワトリは 1400～1800 羽もあり、1 週間で除籍記録は約 20 件、移動記録は「中大雛室移動」「成鶏舎移動」でそれぞれ約 20 件ずつ発生する。そのため、これまではこれらの入力に 1 週間に2～3 時間の作業時間を費やしていた。
手入力

手入力

孵化簿
生存記録

孵化集計
移動記録票

手入力

除籍記録票

手入力

手入力

除籍集計
（月報）
図 2.昨年度までのデータ管理方法

3

データ入力に使ったマクロの紹介

3.1

マクロとは

Excel マクロとは、Excel ファイル内に組み込む一連の操作を自動化する機能のことであり、ユーザーが任意で作成できる自動処理プログラムである。ある操作をマクロとして登録しておくと、以降はマクロを実行するだけで自動的にその操作を済ませることができる。マクロを作成するには、「マクロの記録」機能で実際の操作を記録する方法と、「VBA（Visual Basic for Applications）」というプログラミング言語を使う方法の 2通りがある。プログラミングを行うには、Excel の「開発」タブの「Visual Basic」をクリックし「Visual BasicEditor」ウィンドウを開く必要がある（「開発」タブは初期設定では表示されない。Excel の「ファイル/オプション/リボンのユーザー設定/メインタブ」で「開発」をオンにする）。VBA でマクロを作成すると「条件に応じて処理を行う」「同じような処理を繰り返す」「フォームを作成する」の機能を加えることができる。
3.2

マクロの目標入力作業で一番時間がかかるのは、除籍集計から生存記録への検索とコピーを行う作業である。また除籍集計の入力内容（除籍理由や原因）が重複する場合も、翼帯番号はバラバラなので一つ一つ入力するのに手間がかかる。マクロを導入する前は、コピー＆ペーストか、エクセルの基本機能である「入力規制/リストからのみ入力」を使用していたが、これでも多量のデータを扱う場合は見落としが頻発していた。そこで「検索とコピー」「集計の簡易入力」2 つの問題を解決するマクロをそれぞれ考案した。
3.3「検索とコピー」マクロ

全ての個体データには翼帯番号がついているので、この番号でデータを紐づける。除籍集計（図 3-1）の表から翼帯番号を一つ読み込み、生存記録（図 3-2）内の同じ翼帯番号を検索し、除籍日・除籍理由などをコピーする。そして、入力が終わったら次の翼帯番号を読み込み、これを繰り返す。

マクロボタン

図 3-1.除籍集計

図 3-2.生存記録
育雛舎除籍入力のマクロのソースは以下のようになる。
Sub 育雛舎除籍入力()
Dim w, i, j, r1, r2'j = 情報元の行'w = alldata の行'i = 情報元の no.
Set r1 = Worksheets("alldata")
Set r2 = Worksheets("育雛舎除籍")
For j = 2 To r2.Range("A2").End(xlDown).Row
i = r2.Cells(j, 2).Value

For w = 2 To r1.Range("A2").End(xlDown).Row
If r1.Cells(w, 1).Value = i Thenr1.Cells(w, 6).Value = r2.Cells(j, 1).Valuer1.Cells(w, 7) = r2.Cells(j, 4)
r1.Cells(w, 8) = r2.Cells(j, 5)
r1.Cells(w, 9) = r2.Cells(j, 6)
End IfNextNextEnd Sub3.4「集計の簡易入力」マクロ

除籍集計の簡易入力を実現するために、入力フォームを作成した（図 4）。「サイズ」など翼帯番号以外の項目が前に入力した値と同じであれば、オプションボタンを操作しなくても入力ボタンを押すだけで、図 3-1の表にデータを入れることができる。入力する手数を減らすことができるので時間短縮につながる。

オプションボタン

図 4. 除籍入力フォーム
除籍入力フォームのソースは以下のようになる。
Option Explicit
Private size As String
Private riyuu As String
Private genin As String
Private syoribi As String
Private Sub 年入力_Change()syoribi 年.Text = 年入力.ValueEnd Sub
Private Sub 月入力_Change()syoribi 月.Text = 月入力.ValueEnd Sub
Private Sub 日付入力_Change()syoribi 日付.Text = 日付入力.ValueEnd Sub
Private Sub 育雛_Change()
size = "育"End Sub
Private Sub 中大雛_Change()
size = "中大"End Sub
Private Sub 死亡_Change()riyuu = "死亡"End Sub
Private Sub 淘汰_Change()riyuu = "淘汰"End Sub
Private Sub 譲渡_Change()riyuu = "譲渡"End Sub
Private Sub 病気_Change()
genin = "病気"End Sub
Private Sub 事故_Change()
genin = "事故"End Sub
Private Sub 余剰_Change()
genin = ”余剰”

End Sub
Private Sub 実験_Change()
genin = "実験用"End Sub
Private Sub コメント削除ボタン_Click()sonota.Text = ""End Sub
Private Sub 入力ボタン_Click()
Dim j As Integer
If No.Text = "" Then
MsgBox ("No を入力してください")
Exit SubEnd IfIf syoribi 年.Text = "" Then
MsgBox ("処理日の年を入力してください")
Exit SubEnd IfIf syoribi 月.Text = "" Then
MsgBox ("処理日の月を入力してください")
Exit SubEnd IfIf syoribi 日付.Text = "" Then
MsgBox ("処理日の日付を入力してください")
Exit SubEnd IfIf size = "" Then
MsgBox ("サイズを入力してください")
Exit SubEnd IfIf genin = "" Then
MsgBox ("除籍理由を入力してください")
Exit SubEnd Ifsyoribi = syoribi 年.Text + "/" + syoribi 月.Text + "/" + syoribi
日付.Text

With Worksheets("育雛舎除籍")
j = .Cells(Rows.Count, 1).End(xlUp).Row + 1.Cells(j, 2) = No.Text.Cells(j, 1) = syoribi.Cells(j, 3) = size
.Cells(j, 4) = riyuu.Cells(j, 5) = genin.Cells(j, 6) = sonota.Text
End WithEnd Sub

3.5「検索とコピー」マクロと「集計の簡易入力」マクロの導入による効果「検索とコピー」マクロと「集計の簡易入力」マクロは 1 つのマクロファイルにまとめ、作成した生存記録データと除籍集計リストはそれぞれのファイルに丸ごとコピーして保存することとした。この一連のマクロを使用することにより、データ入力の時間は記録簿 1 枚当たり 10 分ほどで処理することが可能になった。
データをため込まずに素早く処理することができ、ほかの作業者がデータ確認をしやすくなる。こまめに入力できるので一度に入力するデータ数が少ないため、入力確認作業をしやすくなった。その上鶏舎の作業時間を確保することができ、昨年度までに習得できなかった人工授精業務などを新たに習得することが可能になった。

入力フォーム孵化集計(マクロ含)

手入力

孵化簿

コピーフォーマット
入力

移動記録票

除籍記録票

マクロ

生存記録
コピー

入力フォーム

除籍集計（月報）

図 5.新しいデータ管理方法
図 5 における「孵化集計の入力フォーム」については、次の節で述べる。
3.6

入力フォームの改良

入卵数・孵化数をまとめるエクセルデータ「孵化集計」において、入力フォームを改良して入力しやすくした。センターで飼育するニワトリの系統数は 30 を超

マルチページ機能えるため、孵化は 2 週間に一度行うが、孵化を予定する系統は毎回異なる。これをエクセル表にただ入力するのでなく、チェックボックスを入れて必要な系統だ
け数字を入力する欄を表示させる機能を追加した（図6）。また一面に 30 以上の系統名を羅列すると該当の系統名を探すのが煩雑になるので、マルチページ機能でテキストボックスレイアウトした。また、図 6 の入力フォームでは『チェックボックスを ON すると個数を入力するテキストチェックボックスボックスを表示する、チェックボックスを OFF するとテキストボックスは表示しない』というプログラムをすべての系統で組みこんでいる。
図 6.孵化集計の入力フォーム

このソースは以下の通りである（系統「WLG」の場合）。
Private Sub c_wlg_Click()t_wlg.Visible = False

If c_wlg = True ThenEnd Ift_wlg.Visible = True

End SubElse
入力フォームを使用することで、図 7 の孵化集計ファイルに自動で入卵日毎の入卵数や孵化数を入力することが可能になった。

図 7.孵化集計（1 系統毎に入卵数を白地枠に、孵化数を青地枠に入力する）

4

データ解析に使ったマクロの紹介生存記録をもとに、データ解析を試みた。現場では、「○○系統は 1 ヶ月齢以内の死亡率が高い」「△△系統は中大雛になってからの死亡が多い」という経験談を聞いてはいたが、実際にそうなっているのかは実際の数値として表されていなかった。私がセンターの業務に就いて 2 年目になるので、この 2 年間に蓄積された生存記録データに基づき、生存率を算出してみることにした。
生後 20 日から 20 日ずつ 180 日間をそれぞれ超える生存個体がどれだけいるのかで算出した。まず、個体の生存日数と生後日数を以下に規定する。
計算基準日－孵化日＝生後日数…（1）

除籍日－孵化日＝生存日数…（2）ただし除籍記録がない場合は生存日数と生後日数は同値となる

この 2 式をマクロ第一段階において、全データで行う。
次に、各日数（20・40・60…~180 日）の生存数・母数・生存率を以下の式で規定する。
X 日生存数＝生存日数が X 日以上の個数…（3）

X 日母数＝生後日数が X 日以上の個数…（4）X 日生存率＝X 日生存数/X 日母数…（5）以上の 3 式を、系統を限定してマクロ第二段階で行う。
第一段階のマクロのソースは以下になる。生存記録から生後日数と生存日数を算出する。
Sub 生後日数と生存日数の計算()
If Worksheets(" 結 果 表 示 "). コ ピ ー .Value = True And
Worksheets(3).Name <> "データ加工" Then
Call Worksheets("alldata").Copy(After:=Workbooks(" 生 存率.xlsm").Sheets("alldata"))
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Name = "データ加工"
With Worksheets("データ加工").Columns(4).Hidden = True
.Columns(5).Hidden = True.Columns(7).Hidden = True.Columns(8).Hidden = True.Columns(9).Hidden = True
.Columns(10).Hidden = True.Range("K1").Value = "生後日数".Range("L1").Value = "生存日数"
End With

ElseIf Worksheets(" 結 果 表 示 "). コ ピ ー .Value = True And
Worksheets(3).Name = "データ加工" Then
MsgBox "現在の「データ加工」ワークシートを消してくだ
さい"Exit SubEnd IfDim WD As Worksheet 'データ加工のシート
Set WD = Worksheets("データ加工")
Dim SD As Integer '生後日数
Dim LD As Integer '生存日数
Dim CN As Integer '雛数
CN = WD.Range("A2").End(xlDown).Row
Dim cald As Date '計算基準日
cald = Worksheets("結果表示").Range("E2").Value
Dim FB As Date '孵化日
Dim JB As Date '除籍日'基準日数の計算
For SD = 2 To CNWD.Cells(SD, 11).Value = cald - WD.Cells(SD, 3).ValueNext'生存日数の計算
For LD = 2 To CN
If WD.Cells(LD, 6).Value = "" Then
WD.Cells(LD, 12).Value = WD.Cells(LD, 11).ValueElse

'生存日数の計算
For LD = 2 To CN
If WD.Cells(LD, 6).Value = "" Then
WD.Cells(LD, 12).Value = WD.Cells(LD, 11).ValueElse
JB = WD.Cells(LD, 6).Value
FB = WD.Cells(LD, 3).ValueWD.Cells(LD, 12).Value = JB - FB
End IfNextEnd Sub

第二段階のマクロのソースは以下になる。系統を指定し、生存数・母数・生存率を算出する（後半紙面都合により割愛）。第二段階のマクロだけで、系統ごとの生存率を求めることができる。
Option Explicit
Private Sub keisan_Click()
Dim cow1 As Worksheet
Set cow1 = Worksheets("データ加工")
Dim cow2 As Worksheet
Set cow2 = Worksheets("結果表示")
Dim j As String '系統リスト
j = 系統リスト.Value
Dim ty As Byte '結果表示の横列
Dim tg As Integer 'Worksheets("データ加工")個数検索
Dim td As Integer 'Worksheets("データ加工")検索対象
Dim rq As Integer 'Worksheets("データ加工")一番下検索
rq = cow1.Cells(Rows.Count, 1).End(xlUp).Rows
Dim rp As Integer 'Worksheets("データ加工")個数
Dim xt1 As Byte '生存率計算、生存数
Dim xt2 As Byte '生存率計算、母数
If 系統リスト.Value = "" Then
MsgBox ("系統を選択してください")
Exit Sub
Exit If
If cow2.Range("E2") = "" Then
MsgBox ("基準日を入力してください")
Exit SubEnd If'生存数計算
For ty = 2 To 10rp = 0td = (ty - 1) * 20
For tg = 2 To rq
If cow1.Cells(tg, 12).Value >= td And cow1.Cells(tg, 2) = jThen
rp = rp + 1
End IfNext
cow2.Cells(7, ty) = rp
Next'母数計算
For ty = 2 To 10rp = 0td = (ty - 1) * 20
For tg = 2 To rqIf cow1.Cells(tg, 11).Value >= td And cow1.Cells(tg, 2) = j
Then
rp = rp + 1
End IfNext
cow2.Cells(8, ty) = rp
Next'生存率計算
For ty = 2 To 10
xt1 = cow2.Cells(7, ty).Value
xt2 = cow2.Cells(8, ty).Value
cow2.Cells(9, ty).Value = Application.Round(xt1 / xt2, 2)
NextEnd Sub

この解析方法によって図 8 のような結果を得ることができた（まだ検討途中なので系統名はＡ－Ｄで表記した）。図 8 に示したように、孵化直後から生存率が下がる系統や、生後 2 ヶ月までは生存率が高いがその後急速に生存率が落ち込む系統もあるなど、生存率の推移が異なることが分かる。また成鶏まで生存する割合
について、7 割から 9 割近く生存する系統もあれば、3 割程度しか残らない系統もあり、大きく異なる。今後も、現在維持しているニワトリ系統について集計を進め、系統毎の特性を明らかにしたい。

図 8. 系統別生存率推移

5

今後のマクロ作成の目標

現在マクロに新たな機能を追加することを検討中である。それは「上書き防止機能」である。
エクセルの基本機能には「元に戻す（Undo）」があり、操作ミスでデータを消去しても復旧することが容易である。しかし、マクロで操作したデータ変更は、「元に戻す」ボタンでは元に戻せない。そのため記録票の読み間違いにより、除籍日の上書きをしてしまうこともありうる。現在はマクロで作成したデータは生存記録ファイルに新しいシートで追加することで、前のデータの消滅を防止している。しかし現在の方法では生存記録ファイルのシートの枚数を膨大に増やし、ファイルの巨大化につながる。
データの上書きを防ぐために、一つの項目について既にデータが入っている場合はエラー表示をしてマクロの機能を停止しその後のプロセスを中断できるというプログラムを試作している。上書き防止機能を全ての項目において行うには、手間と項目に合わせた工夫がいると思われる。今後じっくりと検討していきたい。

6

謝辞

前回に引き続き原稿の添削にご尽力いただきました、鳥類バイオサイエンス研究センター長の松田洋一教
授、動物実験支援センターの伊藤麻里子様および東郷フィールドの吉村文孝様、鶏舎の作業をご指導いただいた植村武夫様、今回のマクロ作成について多大なご協力をいただいた息子の高間一彰氏に心より御礼申し上げます。

参考文献
[1]

立山秀利,“図解！ExcelVBA のツボとコツがゼッタイにわかる本「“超”入門編」“,秀和システム[2]

立山秀利,“図解！ExcelVBA のツボとコツがゼッタイにわかる本「プログラミング実践編」“,秀和システム[3]

立山秀利,“今日から使える Excel マクロ＆VBA”,ソシム[4]名古屋大学大学院生命農学研究科附属鳥類バイオサイエンス研究センター (ABRC)ホームページ(https://www.agr.nagoya-u.ac.jp/~abrc/)