こんにちは。ヒトツメです。
前回、扱いやすいデータの話と、加工のためのOFFSET関数の話をしましたが、これを少し応用して、Excelで作られた表の列の並び替えについて、考えていきたいと思います。
意外と並び替えの作業は多い
並び替えが必要になるケース
なぜ今回Excelファイルの列の並び替えの話をしようと思ったかというと、思っている以上に、列の並び替えに苦しみながら業務をされている方が多いからです。
最近は、ヘッダー部分に記載されている項目名称をベースにして処理をするような形式が流行っているので、かなり減ってきたように思いますが、RPA（Robotics Process Automation）の流行に伴い、Excelを用いた「帳票」を自動的で処理するというケースは増えてきています。
この時、その「帳票」に関して、どの列にどの項目が含まれているかなど、かなり細かく事前に設定をしているケースが多く、例えば取引先から受け取った帳票の並びが少し変わってしまうだけで、処理ができなくなってしまうということは、比較的よく発生するようになってきています。
そこで、大抵は列を丸っとコピーして、別のシートに貼り付けて、といった作業を繰り返すことになります。ただ、これだとかなり手間ですし、数列を一回だけ処理するならともかく、かなりの列数のファイルに関して、毎日処理をしなければならないとなると、かなり手がかかります。
マクロの出番？
そこでよくあるのが、Excelマクロと呼ばれるものです。
実際の処理を記録し、再現するというもので、プログラミングも実施すれば、かなりいろいろなことができます。
ただ、Excelマクロは、ボタン一つで作業を再現したりすることができる一方で、作業がブラックボックス化しかねず、また、独自にプログラミングをして並び替え用のマクロを作成した場合、保守が難しいケースもあり、結果として余計に作業の手間が発生したり、ということが良くあります。
OFFSETで解決！
そこで、個人的には、並び替えはすべてOFFSETで解決するべきだと思っています。
例えば、こういった表があるとした場合、例えばF1セルに次のような関数を入れると、B列に入っている行をそのままコピーできます。
=OFFSET($A$1,0,1,6,1)
やっていることは非常に単純で、A1セルを基準に、縦に0、横に1動かした場所を起点として、高さ6、幅1の範囲をそのままF1セルに持ってきているというだけです。
例えばこの時の三つ目の引数を、別のセルから引っ張ってくるようにし、並び替えの順番を指定する部分と、並び替え後の配列が表示される部分（OFFSETおよびSPILLによって表示される部分）を分けて管理すれば、並び替えは意外と簡単にできてしまいます。
さらに言うと、上記でいうG1の部分を、ヘッダーの名前にし、OFFSET関数の三つ目の引数を、MATCH関数で指定してあげれば、元のデータの並びが変わったとしても、項目名が変わっていなければ、自動でデータを引っ張ってくることができます。
=OFFSET($A$1,0,MATCH(G1,$A$1:$E$1,0)-1,6,1)
G1にヘッダーで使われている項目名が記載されているとして、G2にこういった関数を記載するというやり方です。
MATCH関数により、A1からE1のなかのどの部分にG1と同じものがあるかを検索し、A1からその列数分移動した先にある列をそっくりそのままOFFSET関数で持ってくるという方法です。
注意点は一つで、A1を基準にしているので、A列を1、B列を2を数えるやり方だと、一つずれてしまうので、MATCH関数の後にマイナス1をしている点です。
なお、毎回、高さが違う表を引っ張ってくる必要があるのであれば、別途高さを指定するセルを設定し、OFFSET関数の4つ目の引数をそのセルから引っ張ってくるといったやり方も考えられます。
さいごに
このように、普段手作業でやっているものも、関数で意外と解決するケースは多いです。
Excelマクロは確かに便利ですが、本当に使える人が効率化のために使うものであって、保守のことなどを考えずにやみくもにやってしまうと、精査漏れなどで重大なミスを引き起こしてしまいます。
関数だと調べればある程度知識がある人なら簡単に理解できますので、可能な限りこういった関数で業務効率化の途を探っていく方が良いように思います。
コメント