メインコンテンツへスキップ
見出し画像

前年が0の得意先で、前年比の列が壊れる人へ

    毎月、得意先ごとに前年同月比を出しています。
    当年の売上を前年の売上で割って、1を引く。
    式はそれだけです。

    困るのは、前年に取引が無かった得意先の行です。
    架空の例ですが、前年0円・当年12万円の得意先があると、前年比の欄は #DIV /0! になります。
    エラーが目立つのでIFERRORで0に置き換えると、今度は「前年と変わらない得意先」に見えます。

    いちばん伸びた相手が、いちばん動いていない相手と同じ表示になる。
    私はこれに気づくまで、しばらく0のまま表を配っていました。

    なぜ毎月起きるか

    原因は3つあります。

    ひとつめは、新しい得意先です。
    前年が0なので、割り算ができません。
    取引先が増えるたびに、エラーの行も増えます。

    ふたつめは、エラーの隠し方です。
    IFERRORで0や空欄にすると表はきれいになります。
    ただ、「新しく増えた」と「変わらない」と「計算できない」が同じ見た目になります。

    みっつめは、逆の場合です。
    前年だけ取引があって、今年は0の得意先。
    当年の明細から得意先の一覧を作って前年をVLOOKUPで引く作りだと、
    この得意先は行ごと出てきません。
    減った相手ほど、表から消えます。

    どれも式の間違いではありません。
    前年と当年で、得意先の顔ぶれが違うことから来ています。

    画像

    Excelだけでの対処

    Excelの中でも、かなりの部分は直せます。

    ひとつめは、式を分けます。
    前年が0なら「新規」と書き、そうでなければ率を出す。
    IF(前年=0,"新規",当年/前年-1) のような1行です。
    0に置き換えるより、読み違いがずっと減ります。

    ふたつめは、増減額の列を横に並べることです。
    率が出せない行でも、+12万円なら書けます。
    並べる順番も、率より増減額のほうが大きい動きから見られます。

    みっつめは、得意先の一覧を両方の年から作ることです。
    前年と当年の得意先を1列に縦に貼って、重複の削除を掛ける。
    その一覧に対して、両方の年をSUMIFSで引きます。
    これで今年0の得意先も行として残ります。

    最後に、得意先ごとの増減額を合計して全体の増減額と比べます。
    今年0の得意先が一覧から抜けていると、ここが合いません。
    合えば、減った相手を取りこぼしていないと言えます。

    ピボットテーブルで年を列に並べれば、両方の年の得意先が自動で出ます。
    ここまでできていて困っていない人は、この先は読まなくて大丈夫です。

    それでも残る手間

    残るのは、毎月の作り直しです。

    新しい得意先は毎月のように増えます。
    両方の年から作った一覧も、毎月作り直しになります。
    式の範囲を伸ばして、「新規」の行と今年0の行を目で拾う。
    ここまでやってから、ようやく「どこが伸びてどこが減ったか」を読み始められます。

    率の順に並べると、「新規」の行は並びから外れます。
    今年0の行は、−100%で一番下にまとまります。
    金額の大きい動きを上から見たいのに、並べ替えの前にこの整理が毎月入っていました。

    画像

    道具で解決したこと

    私はこの整理を毎月やりたくなくて、手元で動く集計の道具を作りました。

    前年と比べる設定にすると、前年が0の得意先は率の欄に「新規」と出ます。
    0や空欄にはしません。
    増減額は、率とは別にそのまま出します。

    もうひとつ、「増減要因」という欄を置きました。
    前年と比べて増えた相手と減った相手を、それぞれ上位5件まで出します。
    並びは率ではなく、増減額の大きい順です。
    前年だけあって今年0の得意先も、ここの「減少」の側に出ます。
    「前年の金額 → 0」の形で表示されます。
    行を押すと、その得意先だけに画面全体が絞り込まれます。
    減った理由を明細で追うのは、そこからです。

    得意先の一覧を作り直す作業はありません。
    データはPCの中だけで処理され、外には送りません。

    画像

    製品ページ: https://localworktools.com/?utm_source=note&utm_medium=referral&utm_campaign=note-20260929

    向いていない人も書いておきます。
    得意先が毎年ほとんど同じなら、IFの1行で足ります。
    新規も取引の止まった相手もめったに出ない表です。
    取引先の入れ替わりが多くて、前年比の表を毎月整えている人向けです。


    #Excel #業務効率化 #前年比 #売上管理 #データ集計

     
     
    Excelで毎月やっている面倒を、なくす。CSV・Excelから使える買い切り型の業務ツール「LocalTools」を作っています。クラウド送信なし・PC内で完結。ここでは、集計で困りがちなところの整理と、作りながら決めたことを書いています。

    あなたへのおすすめ