データベースの内容をExcel上でまとめて昆虫標本のラベルを作る

昆虫標本にはラベルをつけますが、ラベルをパソコンで作る際の効率化について悩む方が多いのではないでしょうか。
全自動でできるならそれが一番いいけど、書式や形の制約があるのは困る。
かといって自力でマクロとか使ってプログラムを組めるほどの知識もない……
それでもExcelでデータベースを作るところまでできていれば、少しの工夫である程度の効率化が可能です。
本ブログでは過去にスマホアプリでを使って現地でデータを取得し、それを元にデータベースを作り上げていく手法をご紹介しています。

データベースを作っているなら、ラベル作りにも活用しましょう
本記事はこれらの記事に続く内容で、前記事までに示した通りに作業を終えたあとのファイル（記事中では「サンプル」と呼びます）を使って説明します。
別なやり方で、既に自作した標本データベースがあるのであればこの記事から読んでも問題はありません。
私はWindowsのパソコンで、MicrosoftのWordを用いてラベルを作成しています。
作業としては、

という流れです。
必要な作業を行うための関数を入れたサンプルファイルを公開しています。
（前の記事で紹介したものからさらに修正しています。）

標本データベースの例
https://www.dropbox.com/scl/fi/ofk1i2hx8g52ftyz7iyi6/.xlsx?rlkey=9skdsd23svvrr9r16owb9ru1v&dl=0

本記事では、この中で使っている関数などを紹介しながら、どういった作業を行っているのかを解説します。
Excelを使い慣れていないと理解が進みにくい内容になっているかもしれませんが、関数の使い方が分かれば、自分好みのスタイルにアレンジして使っていただけると思います。

データの整理と変換

まずはデータベースの内容を整理するところから始めます。
データベースに記録している内容は、あとからの利用や作業のしやすさなどを考えた結果、ラベルに書いてあるものとは内容は同じでも違う表示形式になるものがあります。
（あくまで私の場合は、です）
例えば、データベース上では緯度と経度はそれぞれ数値データの形ですが、ラベルに書くときは記号がつきます。

緯度経度のデータとラベルにおける「座標」の表記例
また、採集年月日もExcel上で使われる表記とラベルで使う表記は異なることがあります。

ローマ表記に変換してラベルに使う場合
こういったものについては、あらかじめExcel上でデータからラベル用に変換する列を作っておきます。
式を入れておくことで勝手に変換できるものもあれば、自分でひと手間かけなければいけないものもあります。
まずは自力でやらなければいけない作業を先に説明します。

緯度経度を文字列形式に変換

まずは座標のについての作業です。
緯度経度のデータは、前記事までの作業で小数点以下4桁までに切り捨てられたものになっています。
この数値データをそのまま使いたいところですが、ひとつ厄介な問題があります。

桁数が、少ない……！？
座標の数値データは、小数点以下の末尾数値にゼロが続くと省略されてしまうのです。

もちろん、「小数点以下の表示桁数を増やす」から消えたゼロを表示させることはできますが……これは見かけ上は表示されているだけです。
中身の数値データには末尾のゼロが含まれていないのです。
このままラベルにするとどうなるかというと……

表示と中身（ラベル案）が違う
数値データから作るラベルでは末尾のゼロが消えて、小数点以下3桁の座標値がラベル案に出てきます。
これは座標の有効数字が1つ減る＝示す誤差範囲が大きくなる、ということです。
位置として同じと言えば同じなのですが、考慮すべき誤差の範囲が全然違うもの（10 m程度→100 m程度）になってしまいます。

一方で下半分、ベージュ色のセル行は……
末尾のゼロもいれて座標を表示したい、そうなったときに使えるのが「文字列」の表記です。
下側が文字列の表記から作ったラベル案ですが、末尾のゼロがちゃんと表示されています（青枠）。
つまり、座標は数値から文字列への変換を行っておけば良いわけです。
これは式などで自動化できていないため、各自でやってもらう必要があります。

緯度経度の列とは別の列に、変換した文字列用の列を作ります。
サンプルでは「N」「E」の列がこれに当たります。
作った列を選んで、セルの書式設定で表示形式を「文字列」に変更しておきます。

元データの座標列を選んで、（見かけ上の）小数点以下の表示桁数は4桁にしておきます。
文字列に変換したい緯度経度をまとめて選択し、コピー（Ctrl+C）します。
準備できたら、ここでWordを新たに開きます。

コピーした座標データをそのまま表の形でWordに貼り付けます。
貼り付けた表を選択して再びコピーし、Excelに戻ります。

用意しておいた列にコピーしたものを貼りつけます。
このとき、「貼り付け先の書式に合わせる」を選択すると……値が文字列に変わります。
これで文字列への変換は完了です。
Wordを経由するという妙なやり方ですが、現状はこれより楽な方法が思いつきません。
（なにかしらかありそうだけど……）
続いて、文字列化した緯度経度をもとにラベル用の座標表記を作ります。
これについてはサンプルに式が入っているので、「N」「E」の列に緯度経度が入ると自動で出ます。

「座標」の列は必須でないが、あるとラベル作りがスムーズに進む（後述）
座標列では文字列化した緯度経度に記号と末尾のコンマをつけ足しています。
日本国内で採集する分にはこれで問題ありませんが、海外の採集品が含まれる場合は記号（NとかEとか）が変わることもあるため、要注意です。

英語地名を大地名から小地名の順に並び替える

GoogleのGeocoding APIで取得した英語の住所は、基本的に小地名→大地名の順番になっています。
ラベルの表記では大地名→小地名の表記が理想となるため、この順番も入れ替えてしまいましょう。
一度住所を分解して、逆順でつなげることで入れ替えができます。
まずは新しいシートを作って、英語地名だけを貼り付けましょう。
貼り付けた列を選択して、「区切り位置」を指定します。

これを使うと区切る前のデータが（分割されて）なくなるので、別シートに分けて作業する（推奨）

区切り文字はコンマ
区切り位置指定ウィザードを2/3までそのまま進め、ここで「区切り文字」として「コンマ」のみを指定します。
すると下に「詳細地名」「市町村」「都道府県」と区切られたようなプレビューが出ます。
このまま「完了」を選んで大丈夫です。

これで英語地名がコンマの位置ごとに別々のセルに分けられました。
分けられた住所は左の列から順番に並べられています。
住所の長さにより、右端にくるセルの位置（列）が違います。
ここで、一番多く分けられたセルがどの列まであるか……右端の列がどこにあるかを確認します。
確認方法は単純。1行目に「フィルタ」を設定するだけです。
（画像では既につけてあります）
フィルタのマークがついたところまでが、何らかのデータが存在する列ということです。
端の列が分かったら、そのひとつ右隣の列に式を入れます。

右から左へつなぐ
使うのはTEXTJOIN関数。
複数のセルの内容をつなげ、間に区切り文字をいれることができます。
区切り文字の指定は、コンマ。「”,”」
空のセルは無視するTRUEを続け、以降は画像の矢印のように右から左へと、セルをつなぎたい順番通りに一つずつ選択していきます。

大地名→小地名になりました
式を入力し、下までコピーするとこのようになります。
先頭にJapanが来ていることから分かるように、大地名→小地名の順に並び替えが完了しました。
これを元のデータベースにある英語地名「Address」の列に貼り付けて作業完了です。
自分でやっておくべき作業についてはここまで。
残りは関数を使って自動化できます。
どんな関数でやっているのか、参考までに説明します。

年月日の表記をローマ数字に変更

採集年月日の表記はラベルで使われる形に変更します。
私が使っている表示形式は

です。
この形にするには、既存の年月日列に対してTEXT関数などを用いて表示形式を変化させたものを作ります。
元のデータに時刻が入っていても、必要な年月日だけ出てくるので問題ありません。

使っているのは以下のような式です。

分解して説明していきます。
TEXT(DAY(テーブル1[@採集年月日]),”00″)
まずは、採集年月日からDAY関数で日付を指定しています。
さらにTEXT関数でその表示形式を”00″、すなわち2桁の数値(01～31)にしています。
こうすると例えば4月1日のような日付が1桁の日でも01.IVのように頭にゼロがついて表示されます。
この方が自分がラベルを読みとりやすいため私はこうしているのですが、一般的には頭のゼロはつけない形でのラベル表記が多いように思います。
この辺もなんかルールとかあるの……？
頭のゼロがいらなければ、式中の”00″を1桁減らして”0″にすればOKです。
&”.”&
続いて、これは年月日の間に入るピリオドです。ピリオドを打ちたい2カ所にこの式が入ります。
基本的な補足ですが、複数の関数や文字列を連続で使う時、間に＆を挟んで区切ります。
任意の文字列を入れたいときには、文字をダブルクォーテーションで囲って
“任意の文字列”
のようにします。
ピリオドをいれたければ
“.”
と、ピリオド＋半角スペースが良ければ
“. ”
というようにすれば良いわけです。
ROMAN(MONTH(テーブル1[@採集年月日]))
この部分は月を変換する式です。
MONTH関数で採集年月日から月を指定し、ROMAN関数がそれをローマ数字（I～XII）に変換しています。
月として使いたい形式が「Jan」などの場合、この部分をTEXT(MONTH(テーブル1[@採集年月日]),”mmm”)に入れ替えればいけそうです。

ローマ数字を使わない表示はセルの書式設定から出せるので、これを文字列に変換してしまう方が速いかも
RIGHT(YEAR(テーブル1[@採集年月日]),4)
最後は年です。
ここではYEAR関数で年を指定し、それをRIGHT関数で右から4文字分抽出しています。
最後の数字が桁数で、例えば2にすれば下2桁の年表示（15.IV.23）とかにもできます。する必要はありませんが……
採集年月日の変換はこのような形で行われています。

標高を10m単位で四捨五入する

アプリの「スーパー地形」で取得される標高の数値は、小数点以下2桁までです。
現地での測定誤差もあるため、私はこの数値をそのまま使用せずに10 m単位で四捨五入して調整しています。

四捨五入の関数である「ROUND関数」を使って、10 mの単位にそろえた列を作ります。
ROUND関数に入力する「桁数」は小数点以下の桁数を表しています。
今回は小数点＋1の位（＝小数点以下-1の位）で計算するため「-1」が入るわけです。

データラベルの作成式

データラベル作成の準備が整ったら、Excel上でデータを集約して仮のラベルを作ります。
Excel上でラベル作りを完結させることもできますが、行間の設定ができない、長音記号が出せないなど制約があります。
より自由にラベルを作りたい場合はExcelからWordへコピーする必要があります。
この作業を正確かつ円滑に行うため、先にExcelでラベルの形にまとめておくのです。
サンプル上では「A案」「B案」という2つの列でラベルの案を示しています。
私はデータの量や長さに応じて、案を比較し使い分けていますが、ぶっちゃけこんなところを細かく気にする必要はありません。
参考までに残しているだけです……

A案：標高は最後の行にくる

A案の構成は以下のようになっています。

この形になるように、以下のような式を組み立てています。

中身の順番や文字を変化させることで、お好みのスタイルに変えることが可能です

それぞれの意味について以下に示します。
“[JAPAN: ★Saitama-ken],”
最初に来る国名と県名は固定の文字にしています。これで先頭に必ず[JAPAN: ★Saitama-ken],が来ます。
すべて大文字の国名と、私のラベルの大半を占める埼玉県を固定で出るようにしつつ、★マークをつけることで県外のラベルにおける変更忘れを防ぎます。
ラベルの1行目を目立たせる目的でカッコ[]をつけます。
太字体にしようかとも思っていたのですが、関数でやるの難しそうだったのでやめました。
CHAR(10)
CHAR(10)は式中に何度も登場するもので、セル内での改行を行うための関数です。
ラベル内で確実に改行が発生する所にあらかじめ設置してあります。
テーブル1[@Address]&”,”
Addressの列には英語表記の住所が入っています。
これを2行目に呼び出し、末尾にコンマを打っています。
T2&”, “&テーブル1[@採集者]&”,”
列Tにはローマ表記に変換した採集年月日が入っています。これを3行目に呼び出し、コンマで区切って採集者名を続けます。
テーブル1[@座標]
座標の列には緯度経度をまとめて記号も付けた座標が入っています。
すでに記号がついているため、そのまま4行目に呼び出します。
“(alt. “&W2&” m), “
5行目ではまず、10 m単位に補正した標高を表示したいわけですが、前後に標高を示す記号を挿入するため、やや複雑な形になっています。
表示方法を変えたければ、前後の文字列をいじればOKです。
“(alt. “&W2&” m), “　→(alt. 10 m),
“H= “&W2&” m, “　　　→H= 10 m,
MID(テーブル1[@採集地],4,1000)
5行目の後半部分には日本語の採集地名が来ます。
ここに使われているMID関数は、文字列を途中から抜き出す関数です。
この式では4文字目から1000文字目まで（実質4文字目以降すべて）の採集地名を抽出します。
採集地名の先頭3文字分くらいは、たいていが都道府県名で必要のない部分になるため、先頭を4文字目にしているのです。
神奈川県みたいな4文字の都道府県でなければ、いい感じに抽出されます。

英語地名の市町村以下はたいてい2行になるため、ラベルとしては6行がデフォルトです。

このように同じ行の各セルから情報を集めている
各行で同様のラベル案が自動生成され、これを活用できる状態になっています。
地名の改行位置など細かい調整は、Wordに貼った後に行います。

B案：標高は採集者名のうしろ

B案の構成は以下のようになっています。

A案との違いは、採集年月日が採集地名の後に続くことで、標高が手前の行に入ることです。
結果的に、日本語の採集地名を入れるスペースが多く取れるようになり、A案のラベルと同じ幅でも盛り込める情報が増えることがあります。
また、飼育羽化などの羽化日を記す時もこの形の方がうまくいきやすいです。
英語地名が短めで、入れたい情報が多いときに使える型です。
この形になるようには、以下のような式を組み立てています。

順番を入れ替えているだけで、A案の時と使っている式は同じです。

Wordへデータラベルを貼り付け

A案またはB案のセルをコピーし、Wordのデータラベルテンプレートに貼っていきます。

昆虫標本データラベルのテンプレート（Word）
https://1drv.ms/w/s!AnrWaFOg-1B_iAupNVdm-TdYA8p1?e=D0erCR

A案のラベルを貼り付けた
貼り付け時、Wordで「書式と結合」で貼り付けられていれば画像のようにうまくいくと思います。
もしうまくいかなかったら、「既定の貼り付けの設定」を開いて設定を変えます。

少なくとも「他のプログラムからの貼り付け」は「書式を結合」にしておきましょう。
無事に貼り付けられたら地名をローマ字式に合わせて微修正します。
県名の変更や「-shi」「-ku」などの挿入、長音記号「ō」などの入力はこの段階で。
ジオコーディングで英語地名を取得した際についてくることもありますが、ないことが多いです。
「町」や「山」の読み方なども必要に応じて調べます。
住所をそのままコピーしてGoogle検索をかければふつうは読みが出てくるはずです

これで1つのラベルが完成です。
同じ地名のラベルが続く場合はこれを必要数コピーし、座標と標高を変えながら作業していくことができます。
ラベルの複製時に座標だけ貼り変えることが多いため、記号つきの「座標」の列をあらかじめ作っていたわけです。
ちなみに、今のラベルはB案で作ってもいいタイプです。

B案で作ったラベルが右
B案では、日本語地名を入れるスペースが増えたことで「中央区」を入れることができました。
ラベルの幅はA案とほとんど変わっていません。
じゃあここはB案のほうがいよね、となるわけです。
余力があれば、こうやって吟味しながらラベルの形を選んでいきます。
なお、これは私の好みですが、ラベルは基本的にWord表に対して縦（列）方向に増やして作ります。
ラベルの幅によって、入力する列を2つくらいに分けることで、無駄なスペースが減り印刷後のカット作業が楽になるためです。
この辺りは各自やりやすい方法でやりましょう。
今回紹介したやり方で2023年分の標本データ(950件)ほどのラベルを作りましたが、作業にかかったのは2日で、作業時間で言えば6時間くらいでした。

同定ラベルの準備

前項までで、データラベルの作成については説明しました。
ここからはデータベースを活用した同定ラベルの作り方を紹介します。
データラベルより項目が少ない上に有用なツールがあるため、作業ははるかにシンプルです。

和名と学名を1セルに集める

私は同定ラベルに記載する項目のうち、同定者と同定年はWordの方で先に作っています。
したがってデータベースからは、和名と学名のセットを持ってこれるように準備します。
サンプルExcelでは「同定」という列を作ってそこに以下の式を入れています。

=テーブル1[@和名]&CHAR(10)&テーブル1[@学名]
和名と学名を呼び出し、和名の後に改行をいれたものです。
これをWordに貼り付ければOKでは、と思ってしまいますが……学名はイタリック（斜体）にするルールがありますよね。
Wordに貼り付けたあと、部分的に斜体設定をかけることでもできます。
というか私はずっとそうやっていたのですが……
ありました。もっと良いやり方が。

「学名の自動イタリック化」を適用

BITTZUさんが作成し公開しているExcelマクロを使うと、学名部分のみを斜体にできます。
詳細の確認ととExcelのダウンロードはBITTZUさんのブログ↓から。

学名の自動イタリック化

エクセルで学名のリストを管理するときに、どうにかして学名部分のみをイタリックにしたり、学名部分と記載者&記載年の部分を分けたりする方法はないのかと思い探してみたのですが、結局見つからなかったので自分で作ってみることにしました。

smallneighbors.blogspot.com

ダウンロードしたExcelマクロに、データベースで作った和名学名のセットをコピーします。

和名が入ってても機能します
「実行」ボタンをクリックして、イタリック化をかけます。

あっという間にイタリック化が完了します
この時、見かけ上は学名だけが斜体化していますが、実は日本語のフォント部分にも斜体化がかかっています。
例で使っている「メイリオ」や「Meiryo UI」など斜体が存在しないフォントであれば大丈夫ですが、「游ゴシック」では斜体化してしまうので注意です。
データベースに戻って、「同定」列の隣にでも貼り付けます。

Excel上ではフォントを気にする必要はない
これで同定ラベルの素材は完成です。
データラベル同様、Wordに貼り付けて同定ラベルを作っていくことができます。
※イタリック化についてはここで紹介したもの以外に「学名字体変更」というのもあります。
こちらでも同じことができました（こっちだと和名は斜体かからないので、游ゴシック勢におすすめ）。
以前から存在は知っていたのですが、自分のやり方にうまく組み込めていませんでした。

Wordへ同定ラベルを貼り付け

Excelにて作成した素材を、同定ラベル用のテンプレートに貼り付けます。

昆虫標本同定ラベルのテンプレート
https://1drv.ms/w/s!AnrWaFOg-1B_iGqrwdZbCv_VErsq?e=80Yqqe

ここでも貼り付けの設定が「書式を結合」になっていないと失敗します。
あとは和名や学名の長さを見ながら適度に改行して、5行に収まるようにします。

こんなかんじです
ちなみに……これデータベースを種名別に並び替えてから作った方が楽ではあります。
（同種のラベルをまとめて作ることができるため）
ただその方式でやってしまうと、ラベルつける時に同定ラベルを探す時間がどうしても発生します。
私の場合は、データベースの順（＝採集した順）に合わせて作った方がスムーズにいけます。
2023年分の同定ラベルの作成（950件ほど）にかかった日数は2日で、時間で言えば7～8時間くらいでした。
データラベルと合わせると、ラベル作成にかかるのは合計3日（15時間ほど）でした。
以前からラベル作りに関してはそこまで負担に感じていませんでしたが、それでも効率化は出来てきていると思います。

ラベリング編につづきます

今回の記事では実際にデータベースからラベルを作成する流れをご紹介しました。
Excelの関数などの話が多く、不慣れな人にはしんどい記事になってしまったと思います。
しかしこの辺りは、実際にExcelを操作しながら慣れていくべきかなと思います。
昆虫標本を作る上で、データを整理してラベルを作り上げるまでに時間がかかってしまうことは昔からの悩みでした。
私の採集品の量を考えると、手書きで対応することは不可能で、高校生の頃からパソコンでのラベル作りを行ってきました。
最初はスマホに残したラベルメモ（日付、採集地×ラベル数）を見ながら1つずつ手打ちしていたと思います。
同定ラベルに関しては、このブログに書いた採集データを元に作っていた頃もありましたね(笑)
効率化に向けてさまざまな改善を重ね、今は紹介したようなデータベースを活用した作り方に落ち着いています。
ただ、採集データ取得から始まる一連の作業の中で最も時間がかかるのは……標本を台紙に貼ったり、ラベルを通したりする作業です。
そう、ここから先が最大の山です。

次回、その作業について参考までに紹介して、この解説シリーズを終えようと思います。
あの辺りの作業は手作業でやるほかないので、効率化とかそういうテクニックの紹介はたぶん、できないです……(笑)