「マクロ禁止」の会社でも、関数だけでここまで自動化できる

記事
IT・テクノロジー
マクロが使えない会社は、実は珍しくありません。セキュリティの方針で、マクロ付きファイルは開けない。よくある話です。

でも「マクロがないと自動化は無理」は思い込みです。毎週の集計作業なら、関数だけでかなりのところまで減らせます。

■ 自動化のコツは「入力する場所」と「計算する場所」を分けること

毎回手作業になるファイルの多くは、データと集計が同じシートに混ざっています。たとえばよくあるのが、売上データの横に合計をその場で書き足していくパターンです。

これを3つのシートに分けます。

・入力シート:生データを貼るだけ。数式は入れない
・計算シート:関数だけ。人は触らない
・報告シート:見せる用の表

毎週やることは「入力シートに貼る」だけになります。貼り替えても、計算と報告は勝手に更新されます。

■ マクロなしで使える関数の組み合わせ

自動化の中心になるのは、次の4つです。

・SUMIFS:条件に合う行の合計(店舗別、月別の集計)
・XLOOKUP または VLOOKUP:別の表から名前や単価を引いてくる
・UNIQUE、FILTER:重複を除いた一覧や、条件で絞った一覧を自動で作る(Microsoft 365 などの新しい版のみ)
・IFERROR:エラーを空白や0に置き換えて、表を壊れにくくする

たとえば月別・店舗別の売上表なら、セルにこう書くだけです。

=SUMIFS(入力!D:D, 入力!A:A, $A2, 入力!B:B, B$1)

A列に店舗名、1行目に月を置けば、表全体にコピーして完成です。

■ 行が増えても崩れないようにする

貼るデータの行数が毎週変わると、数式の範囲がずれます。対策は2つです。

・データを「テーブル」に変換しておく(行が増えると範囲も自動で伸びる)
・範囲指定をD2:D500のように固定せず、列全体(D:D)で指定する

これだけで「範囲を直し忘れて合計が合わない」事故がかなり減ります。

■ 関数だけでは難しいこと

正直に言うと、苦手なこともあります。

・複数のファイルを開いて順番に処理する
・ボタン一つでPDFを作って保存する
・毎朝決まった時間に自動で動かす

ここまで来ると、マクロやGoogle Apps Scriptの出番です。ただ、会社の方針でマクロが使えないなら、「どこまでを関数でやり、残りを人が1クリックでやるか」を決めるのが現実的です。

■ 迷ったら、作業の流れを教えてください

出品中のExcel自動化、GAS自動化、Excel軽量化の3つのサービスは、プロフィールから見られます。「この作業は関数で足りるか」という相談からでも大丈夫です。

次回は、外注するときに依頼前に用意しておくと話が早いものを書きます。
サービス数100万件のスキルマーケット、あなたにぴったりのサービスを探す