こんにちは。キャプテンです。

社内向けの資料を作った際にマイナスを含む前年比についての処理をする必要がありましたので、備忘録を兼ねてまとめたいと思います。専門家ではございませんので、学術的に正しいかどうかはお約束できませんので、あらかじめご了承ください。

一般的に前年比の求め方は上記の通り、（今年÷昨年）×100で求めるかと思いますが、「今年」や「昨年」のどちらかにマイナスの値が含まれると前年比が正しく機能しなくなります。例えば、パターン3の場合は前年比は-200%となっています。昨年の-500に対して今年は1,000と良い結果になっているにも関わらず-200%と悪い結果のように見えてしまいます。

そこで、マイナスを含む場合は比較する「今年」と「去年」の値の差分を取り、それらを元に計算することにします。先ほどの表に習ってまとめると以下の通り。

例に上げたパターン3では前年比300%となっており、狙った結果が得られています。

上記の状態のスプレッドシート、もしくはExcelのC2に入れる数式はこちら。

=iferror(ifs(and(A2>0,B2>0),A2/B2,and(A2<0,B2<0),abs(B2)/abs(A2),and(A2>0,B2<0),(A2+abs(B2))/abs(B2),and(A2<0,B2>0),abs(A2)/(abs(A2)+B2)),0)

エラーが発生した場合は0となるようにエラー処理をしておりますが、基本的にはifs関数によりそれぞれのパターンに分岐して処理をしています。GoogleスプレッドシートとExcelで共通の関数を使っておりますので、どちらでもご活用いただけます。C2に入れた数式をコピーしてC5まで貼り付けていただければ、上記の図の通りの前年比が計算されます。

同じ悩みを持った方の参考になれば幸いです。