廊下のむし探検 第73弾
29日の昼過ぎにマンションの廊下を歩いてみました。やはり今頃は虫はほとんどいませんね。それでも、蛾を少しだけ。
最初はホソナミアツバ。これまで６月から９月にかけて５匹見ていました。この７年間に５匹だからかなり少ないですね。
それからシロズアツバ。こちらは５月と８－９月に５匹。
そして、この２匹の蛾はアオアツバ。アオアツバは全部で54匹。むちゃくちゃ多い蛾です。それにしても、なんだかアツバばかりですね。
そして、アカイラガ。アカイラガは６月と８月で７匹。
これはベニフキノメイガで、５月と９月に３匹。かなり少ない蛾です。
最後のヒメシャクは分かりそうにありませんね。蛾は全部でこの７匹でした。
雑談）ブログに出した画像１枚ずつに対応して、種名、学名、分類（界、門、綱、目、上科、科、亜科・・・）、日付、撮影場所、画像アドレス、ブログアドレスなどを入れたEXCELのデータベースを作っています。これまでに25,000枚分ほどを入力しました。このデータベースを使ってこの７年ほどの間にブログに出した虫や植物の種類数などを計算しています。まず「分類」で適当にソートして、必要な部分を選んで種名の数を数えるのですが、同じ名前がいくつも出てくるので、それらを除かないといけません。これまでネットで探して、理屈はあまり分からず利用していたのですが、これをEXCELのVBAでプログラムを作ってみたらその原理もよく分かりました。忘れないうちに書いておこうと思います。
例えば、1から9までこんな風に種名が並んでいたとします。アオイトトンボやキタキチョウは重複して載っています。種名がいくつあるかというのはワークシート関数のCountIfという関数を用います。この関数は範囲内に探しているのものがいくつあるかを答えてくれる関数です。例えば、９つの種名の中で「アオイトトンボ」はいくつあるか数えてみると３個になります。これを１～９まで順番に調べていきます。次の行ではまたアオイトトンボなのでやはり３個になります。次の行はキタキチョウなので２個という風になります。次にこの数の逆数を取ります。そうすると、３個だったものは1/3になります。種名としては重複を含め全部で９種になるのですが、こうして３個あるものは1/3にしてから足すと３個を合わせて１個にすることができます。それで全部足すと６種となり、重複を除いて範囲内の種類数を求めることができます。ネットで探すと、次のような関数を用いたらよいと書かれていたのでずっとそれを使っていました。
=SUMPRODUCT(1/COUNTIF(B2:B10,B2:B10))
大変便利なのですが、最近、若干不便を感じるようになりました。というのはEXCELを開いた時に毎回この計算を始めるのです。データベースの数が増えてくるとそれなりに時間を取ってしまいます。それで、何とか必要なときだけ計算をすることができないかと思ってプログラムを作ってみました。
1 Sub shuruisuu()
2
3 Dim istart As Long
4 Dim istop As Long
5 Dim isum As String
6 Dim sum As Single
7 Dim i As Long
8 Dim icountif As Long
9 Dim column1 As String
10
11 With Sheet2
12 column1 = .Cells(1, 8) '列の記号
13 istart = .Cells(1, 7) '最初の行
14 istop = .Cells(2, 7) '最後の行
15 isum = column1 & istart & ":" & column1 & istop
16
17 sum = 0
18 For i = istart To istop
19
20 If Range(column1 & i) <> "" Then
21 icountif = WorksheetFunction.CountIf(Range(isum), Range(column1 & i))
22 sum = sum + 1 / icountif
23 End If
24
25 Next
26
27 .Cells(1, 6) = Format(sum, "0") '種類数の計算結果
28
29 End With
30
31 End Sub
これがVBAプログラムのソースです。素人が作っているのではなはだ怪しいのですが、一応、動きます。22行目がCountIfという関数で数を数えているところで、23行目が逆数にして足しているところです。20行目は途中に空欄があるとエラーがでるので、それを避けています。12行目から14行目は種類数を数える範囲を指定するところで、実際のデータベースで使用している場所のデータを取るようになっています（次の図）。最後の27行目は、逆数にすると小数点以下の数字ができるので、それを足すと誤差のために小数点以下の数字が少し残ってしまいます。それを防ぐために最終結果を四捨五入をして整数にしています。
実際のデータベースではこんな感じになっています。この例だとE列の６行目から８行目までの種類数を重複を除いて数えると２種だったという答えが「種類数計算」というボタンを押すと計算して出てきます。