金額ベスト10のデータもVBAで作れる -変更に対応できるようにしておこう

ベスト10を表示させたい

先日、私の職場で経理をやっているTさんが私に相談に来ました。

100社くらいの取引先との月ごとの取引額が書かれた表があります。この表に、月ごとに取引額ベストテンの社名を書き出して表に追加したいというのです。

取引額のベストテンは、表で並べ替えを行って金額を降順にソートすれば分かります。けれども毎月変わる結果を1つの表に書きたいので表全体を並び替えてしまっては具合が悪いのです。そこでTさんはLargeという関数を使おうとしていました。

Large関数は、指定した範囲の中からx番目に大きい値を取得するというものです。例えば＝Large(“A1:A100”,2) と書くと、A1からA100の範囲から、2番目に大きいものの値を返します。このLargeを使って、1番目に大きいものから10番目に大きい金額を選び出しました。

でもその金額に当てはまる会社名を並べることができません。

VLOOKUPでできそうな気がしますが、VLOOKUPを使うには範囲の一番左の列にキーになる値が入っていなくてはいけません。そのときの表はそうなっていないのでVLOOKUPではできません。

など、他にも制約があって、関数だけでは実現できそうになかったのです。

VBAで処理を作るぞ！

CREATOR: gd-jpeg v1.0 (using IJG JPEG v62), quality = 100

私はVBAでその処理を作ることにしました。その日は時間があったのですぐに取り掛かりました。

指定した月のデータを、作業用の表にコピーしてから金額が大きい順にソートして、上から順に10個を選んで書き移す。という処理にしました。

1時間程度でVBAのツールが完成しました。これでTさんの要望に応えることができました。短時間で解決できたので、Tさんには喜んでもらえました。

データが変わっても対応できるようにしておくのがコツ

経理で使うようなエクセル表は、月ごとにシートが追加されたり、〇〇一覧表_2020.xlsx のように年度ごとにファイル名が変わったりするものです。ですから、VBAで編集ツールを作るときにはファイル名やシート名が変わっても対応できるようにしておくべきです。

私は、上の絵のように、作ったツールの表に、処理対象のデータ表のファイル名やシート名を書いて、それを実行対象とするような作りにしました。（実物には「あべっかん」のイラストは入っていませんが…）

そして、取引先が増えて行が追加されても対応できるように工夫を入れておきました。こういうように、「もとのデータに多少変更があっても使えるようにしておく」というのがVBAでツールを作るときのコツです。えらて約1時間後に完成しました。表に会社が追加されたとかの変更があっても使えるような処理にしておきました。

金額ベストテンを表示したい。こんなときにも、エクセルのVBAが使えると解決できるのです。

エクセルVBAのプログラミングを覚えて、表の編集作業を自動化してみませんか？