Excelを使った文字列の比較
どうも。WithOne Advent Calender 2017の12月20日分担当、稲岡です。
今年の私は、Excelで資料を作ったり、サーバー上のパッケージ更新や既存ソースの更新をしたりと、あまり「プログラムを書く」ことはありませんでした。
ですが今回、パッケージ更新作業で必ずやることのひとつである「更新前後のバージョン比較」について、Excelのマクロを使ってプログラムを書いてみました。
これまではずっと、
- 現在導入されているパッケージを確認
- ↑の内容と、更新対象になるパッケージを目視で比較
という感じでやってきたのですが、これがまぁめんどくさい。
似たような文字列が並んでいるので、どれを見てるのか、どこが変わっているのか、たまに空目してしまいます。
そんなことを思いつつ作業してきたのですが、最近Excelで「文字列を比較し、違う箇所の色を変える」という処理を作ってみたのでご紹介します。
処理の流れ
今回の処理の流れは下記のとおりです。
1. インストールされているパッケージの情報を取得
2. その中からバージョンを抽出
3. 更新後のパッケージ情報を取得
4. 同様に、バージョンを抽出
5. 文字列を比較
このうち、
1.と3.についてはTera Termなどのターミナルにコマンドを打って取得、
2.と4.についてはExcelの数式で抽出、
最後の5.はマクロで比較、
という形です。
パッケージ情報の取得
まず、パッケージの情報を取得します。
情報を取得するコマンドで自分が使ったことがあるのは、下記の2つです。
- yum list installed | grep xxx
yumコマンドを使ったパッケージ情報の取得方法です。
この方法で取得したデータは「パッケージ名」「バージョン」「リポジトリ」の順で一覧表示されます。
なお、末尾の「xxx」は絞り込みの条件ですので、インストールされている、すべてのパッケージを確認したい場合は「|」以降の記載は不要です。
今回はこれで取得した情報からバージョンを抽出しました。 - rpm -qa | grep xxx
rpmコマンドを使ったパッケージ情報の取得方法です。
こちらはyumコマンドとは異なり、インストールされているrpmファイル名が表示されます。
末尾の「xxx」は、yumコマンドの時と同様、絞り込みの条件です。インストールされている、すべてのrpmファイルを確認したい場合は「|」以降の記載は不要です。
パッケージ更新前のバージョンを抽出
次に、取得したパッケージの情報を任意のセルに貼り付けます。
このとき「パッケージ名」「バージョン」「リポジトリ」の各情報を個別のセルに配置するようにします。
# 配置されないときは、メニュー「データ」の「区切り位置」で設定を変更できます。
貼り付けた後は「パッケージ名」から末尾にあるアーキテクチャ(「.x86_64」や「.noarch」など)を除外します。
この時に使った数式がコチラ
=IF(W8="","",MID(W8,1,IF(ISERROR(FIND(".",W8)),"",FIND(".",W8))-1))
パッと見、何をやっているかさっぱりだと思うので説明しますと、
- "."がないかどうかを検索する
- "."がなければ空白、あればその位置(何文字目にあるか)を出力する。
- "."が現れた位置までの文字列を抽出する。
という処理になります。
一番最初に出てくる「IF」については、表内の見栄えをよくする(空欄だったら何も表示させない)ためだけに記載しています。
これでアーキテクチャが除かれたパッケージ名が抽出できました。
あとはパッケージ名を基にバージョンを抽出するだけです。
この時に使った数式がコチラ
=IF(X8="","",IF(ISERROR(VLOOKUP(C8,$V$8:$Y$1000,3,FALSE)),"",VLOOKUP(C8,$V$8:$Y$1000,3,FALSE)))
相変わらず文字ばっかりで嫌になりそうですが説明しますと、
- 確認したい対象のパッケージ名が、アーキテクチャを除いたパッケージ名の中に存在しないか。
- 存在した場合、それに該当するバージョンを取得する。
以上です。意外とあっさりしています。
ここでも、一番最初に出てくる「IF」については、表内の見栄えをよくするためだけに記載しています。
これで更新前のバージョンが取れました。
パッケージ更新後のバージョンを抽出
続いて、パッケージ更新後のバージョンを抽出です。
パッケージ情報の取得、そしてアーキテクチャを除いたパッケージ名の抽出については前述のとおりなので割愛します。
パッケージ更新後のバージョン抽出だけ、更新前と数式が変わります。
その数式がコチラ
=IF(ISERROR(VLOOKUP(VLOOKUP(C8,$V$8:$Y$1000,2,FALSE),$AD$8:$AF$1000,2,FALSE)),"",VLOOKUP(VLOOKUP(C8,$V$8:$Y$1000,2,FALSE),$AD$8:$AF$1000,2,FALSE))
もう嫌になりそうですね。
この式では、
- パッケージ更新前に参照したパッケージ名を抽出する。
- それと同じパッケージ名のバージョンを抽出する。
となります。
なぜパッケージ更新前と同じ方法にしないかというと、パッケージ更新後は、パッケージ名は同じだが、アーキテクチャが異なるものがインストールされる場合があるからです。
ソート順次第で回避できるかもしれませんが、それでは確実ではありません。
なので、このような数式を用いています。
マクロを用いた文字列の比較
さて、これでパッケージ更新前後のバージョンを取得できました。
ここからは、VBAで作成したマクロを使ってバージョンを比較していきます。
今回作成したマクロの流れは下記のとおりです。
- パッケージ更新前後のバージョンの文字列とその文字数を取得
- 1文字目から文字数の少ない方までを、1文字ずつ比較
- 同じ文字の場合はそのまま、異なる文字の場合は色を赤に変更
# パッケージのバージョンは厳密にいうと「バージョン」「リリース番号」に分けることができますが、今回は一旦保留にしています。
この流れに沿って作成したマクロがコチラです。
Sub バージョン比較()
' データの定義
Dim old_version As String
Dim new_version As String
Dim i As Integer
Dim check As Integer
Dim old_ver_length As Integer
Dim new_ver_length As Integer
Dim check_length As Integer
Dim P As Integer
Dim Q As Integer
Dim R As Integer
Dim S As Integer
Dim st As Integer
Dim ed As Integer
' データ(定数)の設定
P = 16
Q = 17
R = 18
S = 19
st = 8
ed = 57
' 処理開始
For i = st To ed Step 1
' データの初期化
check = 0
old_version = ""
new_version = ""
Cells(i, Q).ClearContents
Cells(i, S).ClearContents
' 比較対象の設定
old_version = Cells(i, P).Value
new_version = Cells(i, R).Value
Cells(i, Q).Value = Cells(i, P)
Cells(i, S).Value = Cells(i, R)
Cells(i, Q).Font.ColorIndex = 0
Cells(i, S).Font.ColorIndex = 0
' 比較対象の文字数取得
old_ver_length = Len(old_version)
new_ver_length = Len(new_version)
' 比較文字数の設定(数が少ない方を優先)
If old_ver_length < new_ver_length Then
check_length = old_ver_length
ElseIf old_ver_length > new_ver_length Then
check_length = new_ver_length
Else
check_length = new_ver_length
End If
' 比較処理
For check = 1 To check_length Step 1
If Mid(old_version, check, 1) <> Mid(new_version, check, 1) Then
Cells(i, Q).Characters(check, 1).Font.ColorIndex = 3
Cells(i, S).Characters(check, 1).Font.ColorIndex = 3
End If
Next check
Next i
Range("A1").Select
End Sub
# 記載を省けるところ、改良すべき点は多々あると思いますがどうかご容赦ください。
見慣れない記述が多いですが、基本的には
『「どのセルの」「何々」=(は)「これ」』
という形になっています。
例えば、
Cells(i, Q).Value = Cells(i, P) ※"i"はループ変数
の場合、
セルQi [Cells(i, Q)] の値 [.Value] はセルPi [Cells(i, P)] ※"i"はループ変数
といった形です。
実際に動かしてみた
準備ができたので、実際に動かしてみます。
今回は、下の2つのパッケージの差分が取れるかを確認してみます。
その結果がコチラ
# 文字がつぶれてますね･･･、すみません･･･。
今回の場合は、確認するパッケージ名(kernel)をセルC8に、更新前後のパッケージの情報をW8、X8、AD8、AE8のセルにそれぞれ入力します。
画像では省略していますが、作成したマクロを設定したボタンをクリックすると、Q8、S8のセルにバージョンの比較結果が表示されます。
問題なく、差分の箇所が赤文字で表示されました。
最後に (作成してみての感想など)
普段、Excelを使う機会が多い自分ですが、マクロを使って何かすることは一切ありませんでした。
「効率よく」業務をする上では、こういった工夫も必要だな、と改めて思うことができました。
ただ、今回作成したこの機能もいろいろと不具合がありまして･･･。
例えば、
- バージョンとリリース番号をまとめているため、バージョンが変わる(例、1.9 → 1.10)と、それ以外に変更がない部分も赤文字で表示されてしまう
- パッケージ名 (画像でいうW列とAD列) が同名でバージョンが異なるものが存在する場合は、うまく検知できない可能性がある
(VLOOKUP関数は上から走査し、最初に見つけたデータを返してしまうため) - yumコマンドで取得したデータしか比較できない
(rpmコマンドの場合、枠や式を作り直す必要がある)
などです。
上記の不具合は今後、業務の合間に直していきたいと思います。
以上、長文にお付き合いいただき、ありがとうございました。
