背景
5年以上前に知人から頼まれて競り市用の伝票システムをExcel互換フリーソフトで作ったのですが、インボイス対応のために改修が必要になりました。
ただ元々のシステムが割とゴリ押しで作っていたこと、使用者側の環境が変わってGoogleスプレッドシートにも対応できるとのことだったのでロジックの部分を再実装することにしました。
システム概要
ざっくりと伝票システムについて、シート単位で解説します。
名簿シート
参加者に関する情報を記載するシートです。
名前やインボイス番号などの情報が記載されています。
取引リストシート
競り市での取引について記入していくシートです。
取引された品物とその値段、売主と買主の名前を競りと並行して担当者が記入していきます。
伝票シート
競りの参加者が行った取引をまとめ、売り買いの合計を出すシートです。
参加者の名前を選択すると取引した品物名、金額、買主または売主の名前が一覧として表示されるので、印刷して渡す必要があります。
今回インボイス対応として、売主ごとの小計を追加で出す必要があります。
システムシート
取引リストから必要な情報を抜き出し、名簿記載の情報と突き合わせながら計算して伝票シートに表示する内容を整理するためのシートです。
改修前後のシステム比較
名簿と取引リストについては手動で入力するだけのデータベース的なシートですので省略します。
システムシート
改修前
Filter関数もなにも無い環境で実装する必要があったので、重複するデータ全てを抜き出す方法としてよく紹介されているCOUNTIFとVLOOKUPを組み合わせた手法を用いました。
取引リストをセル参照して全データを転記し、売主と買主それぞれにCOUNTIFで通番をつけて下記表のような形でVLOOKUP用の検索キーを作ります。
| 売主キー | 買主キー | 品物 | …… |
|---|---|---|---|
| 売主A1 | 買主A1 | …… | …… |
| 売主B1 | 買主B1 | …… | …… |
| 売主C1 | 買主A2 | …… | …… |
| 売主A2 | 買主C1 | …… | …… |
| 売主D1 | 買主A3 | …… | …… |
| 売主C2 | 買主D1 | …… | …… |
伝票シートにある参加者名のセルを参照し、VLOOKUPで当該参加者に関する取引を「参加者名1」「参加者名2」という形で参照して伝票表示用に売り買いそれぞれリスト化していました。
改修後
Filter([取引リストの範囲], [売主列]=[参加者名のセル])で指定した参加者が売った品物のリストを取得し、同様にFilter([取引リストの範囲], [買主列]=[参加者名のセル])で買った品物のリストを取得します。
QUERY関数を使っても同様に実現できます。
またインボイス対応用に買った品物のリストから売主ごとの小計を出す必要があるので、UNIQUE関数で売主名をまとめてからSUMIF([買った品物リストの売主列],[UNIQUE関数で抜き出した売主名のセル],[買った品物リストの金額列])で売主名ごとの小計を計算します。
伝票シート
改修前
システムシートでリスト化した売り買いのデータをセル参照し、伝票フォーマットに合わせて表示します。
また名簿シートの名前列を元に印刷したい参加者をリストから選択できるよう、特定のセルに入力規則が設定されています。
伝票フォーマットは10ページ分用意されているため、データ件数が少なければ不要なページが出てきます。
そのため適当な行を判定用の行とし、データ件数に応じてそのページが必要なら1、不要なら0と判定させます。
その後判定行を1のみでフィルタして不要なページを非表示にします。
ただしそのままだと判定列も印刷されてしまうので、判定列を非表示にするか伝票部分を選択して選択範囲のみ印刷する必要があります。
基本的には問題なく運用できていましたが、人によってはフィルタを忘れたり判定行まで印刷してしまったりとミスが生じることがありました。
改修後
大枠は変わりませんが、インボイス用のページを追加して同様のロジックで必要に応じて印刷できるようにしました。
また手動操作まわりでミスが生じていたので、マクロ化して自動実行されるよう変更しました。
/** @OnlyCurrentDoc */
function onEdit(e) {
if(e.source.getSheetName() !== "[伝票シートのシート名]") return;
if(e.range.getA1Notation() !== "[参加者名を入力するセル]") return;
prePrint()
}
function onOpen() {
const customMenu = SpreadsheetApp.getUi()
customMenu.createMenu('印刷準備の手動実行')
.addItem('印刷範囲の調整を手動で実行する', 'manualExe')
.addToUi()
}
function manualExe() {
if(SpreadsheetApp.getActiveSpreadsheet().getSheetName() !== "[伝票シートのシート名]"){
SpreadsheetApp.getUi().alert('[伝票シートのシート名]で実行してください')
return;
}
prePrint()
}
function prePrint(){
const sheet = SpreadsheetApp.getActiveSheet();
const criteria = SpreadsheetApp.newFilterCriteria().setHiddenValues(['0']).build();
sheet.hideColumn(sheet.getRange('[判定列]'));
sheet.getFilter().setColumnFilterCriteria([判定列の位置], criteria);
}
onEdit(e)
編集イベントをトリガに実行される関数です。
イベントが伝票シートの参加者名を入力するセルで発生したときのみ、印刷準備のprePrintを実行します。
これにより、システム使用者は伝票を表示したいユーザを選択するだけで自動的に印刷準備まで行えるようになります。
onOpen()とmanualExe()
一応念のため手動でマクロを実行できるよう、カスタムメニューを作成しています。
シート上に図形を用意しても良いのですが、スプレッドシートで図形を印刷対象に含めない方法がぱっと見で見当たらなかったのでカスタムメニューにしています。
ただしカスタムメニューにするとUI的にどのシートでも実行できてしまうため、アクティブなシートのシート名が伝票シートのシート名と一致したときのみprePrintを実行できるようにしています。
prePrint()
印刷準備のための処理をまとめた関数です。
やってる事自体は改修前に手動でやっていたことと同様で、判定列を非表示にしてフィルタの絞り込みを行っています。
元々判定列は非表示にしているのですが、なにかの拍子に表示されてしまうと困るので非表示にするコマンドも記述しています。
おわりに
やったことは以上です。
どれも大して難しいことではないですが、よりシンプルな実装になったことで開発自体もスムーズに行えましたしシステムの挙動も随分と軽くなりました。
本記事が何かしらの参考になることがあれば幸いです。