Excel手順書。
この単語を聞いただけで胃が痛くなる人もいるかと思います。

「Excel手順書はやめよう！」と心の中で叫びつつも、やめられない事情は人それぞれ。
この記事は、やめられない人の手順書管理を少しでも楽にできれば、という趣旨で書きました。

この記事の対象

対象者

Excel手順書を、諸事情からやめられない人。
Excel手順書に、数式を使った動的な表示を組み込んでしまってる人。
なんとなくレベルで python わかる人。

対象でない人

Excel手順書をやめられる人。
VBAまで使った動的手順書に踏み込んでしまってる人。

この記事で話すこと

数式盛り盛りなExcel手順書をメンテナンスする時、壊れるのを避けるためにテストを書こう。

※ ライブラリが対応してる範囲だけなので、VBA使っていなくてもテストできない手順書もあると思います。

Excel 手順書のテスト

記事内で紹介するコードは、以下にあります。
テスト処理はそのまま持ってきてもらって、テストケースだけ書き換えていただくと良いです。

使うライブラリ

今回、 xlcalculator を使います。
数式の再計算結果を反映してくれるのがありがたいライブラリです。

実際の利用イメージ

こんな感じで、テストを足していきます。
「このシートのこのセルを書き換えたら、別のシートのあのセルの値が期待通りに変わってる」という内容を確認します。

セルを書き換えたら、別シートの内容が変わってるテスト

def test_セルの数式が参照する値を更新することで計算結果が変わる_他のシートの文字編(self):
    workbook = Workbook.get_workbook(workbook="./test/samplebook.xlsx")
    workbook.set_cell_value(worksheet_name="Sheet1", cell_address="C2", cell_value="新しい本")

    actual = workbook.get_cell_value(worksheet_name="other_sheet", cell_address="B1")
    expected = "書籍 - 新しい本"
    assert actual == expected

普通にコード都合な形に書いてしまってるので読む人に優しい感じではないですが、以下が確認ポイントです。

ファイルの名前 (ここでは samplebook.xlsx)
手順書見る人が書き換えるシートとセルアドレス (ここでは Sheet1 の C2)
書き換えた結果、更新されてほしいシートとセルアドレス (ここでは、 other_sheet の B1)

テストの実行

git のリポジトリをローカルにダウンロードし、以下を実行します。
環境気になる方は、venv とか適宜設定してください。

ExcelTest直下にて実行

pip install -r requirements.txt
export PYTHONPATH=./src
python -m pytest

こんな感じで、結果が見れます。

成功ケース

(venv) ExcelTest $ python -m pytest
================= test session starts =================
platform darwin -- Python 3.7.9, pytest-6.2.5, py-1.11.0, pluggy-1.0.0
rootdir: /Users/hoge/ExcelTest
collected 6 items                                                                                               

test/test_workbook.py ......                     [100%]

================== 6 passed in 0.72s ==================

期待通りになっていないと、こんな感じに。先ほどのコードの期待する結果を、古い本に変えてみます。
(この辺の動きは、pytestの機能です)

失敗ケース

(venv) ExcelTest $ python -m pytest
================= test session starts =================
platform darwin -- Python 3.7.9, pytest-6.2.5, py-1.11.0, pluggy-1.0.0
rootdir: /Users/hoge/ExcelTest
collected 6 items                                                                                                                                                          

test/test_workbook.py ....F.                                                                                                                                         [100%]

================= test session starts =================
==================== FAILURES =========================
______________________________________________________ TestExcelEvaluator.test_セルの数式が参照する値を更新することで計算結果が変わる_他のシートの文字編 _______________________________________________________

self = <test.test_workbook.TestExcelEvaluator object at 0x7f9c3893e890>

    def test_セルの数式が参照する値を更新することで計算結果が変わる_他のシートの文字編(self):
        workbook = Workbook.get_workbook(workbook="./test/samplebook.xlsx")
        workbook.set_cell_value(worksheet_name="Sheet1", cell_address="C2", cell_value="新しい本")
    
        actual = workbook.get_cell_value(worksheet_name="other_sheet", cell_address="B1")
        expected = "書籍 - 古い本"
>       assert actual == expected
E       AssertionError: assert '書籍 - 新しい本' == '書籍 - 古い本'
E         - 書籍 - 古い本
E         ?      ^
E         + 書籍 - 新しい本
E         ?      ^^

test/test_workbook.py:41: AssertionError
================= test session starts =================
=============== short test summary info ===============
FAILED test/test_workbook.py::TestExcelEvaluator::test_セルの数式が参照する値を更新することで計算結果が変わる_他のシートの文字編 - AssertionError: assert '書籍 - 新しい...
============ 1 failed, 5 passed in 0.84s ==============

注意事項

若干、xlcalculator の動きなのか、テストファイルが悪いのか、Excel の仕様なのかではまったままのポイントがあるので書き残しておきます。

計算結果が怪しい。
- なぜか、 200 x 0.1 が 220 でなく、 220.00000000000003 に。この小数がどこから来ているか不明。
vlookup は特に、数字型、文字型に気をつけて
- vlookupの検索値は、数字か文字かも厳密です。""付き(文字型) かなし(数値型) かは意識しましょう。

まとめ

数式まみれの Excel 手順書をメンテナンスしやすくするために、テストを書いてみようというお話でした。
こんな数式でガチャガチャやるなら、Excel手順書から脱却できるのが一番ではあると思います。
しかし、諸事情から脱出できない事情は現場次第です。
(社内標準、運用部門の制約などなど)

この記事の話が、少しでも運用を楽にすることにつながればと思います。