在庫管理するなら知っておきたい便利な数値計算Excel関数まとめ

「在庫管理はExcelで」というEC通販企業は少なくありません。在庫管理はとても手間のかかる在庫管理は、手作業でやるよりExcelでやった方が効率的です。

ですが、在庫の量が増えれば増えるほど管理担当者の作業負担は増え、入力ミスや計算ミスの起こる可能性も高くなります。

入力ミスや計算ミスを防ぐための手段のひとつが、Excelの関数機能をしっかりマスターすること。

「関数」というワードを耳にすると「数学は苦手だし、自分には使いこなせそうにない」と思ってあまり関数機能をつかってこなかったという人も少なくないはず。

しかし、Excelの関数機能は数学の専門的な知識がなくても簡単に使いこなすことができます。今回は、そんなExcelの関数から、特に在庫管理に役立つ関数をご紹介します。

① SUMIF & SUMIFS関数

SUMIF関数とSUMIFS関数は、在庫管理でも欠かせない関数の一つ。以下の例のように、特定の商品の売れ行きを確認したい時に役立ちます。

今回は図1の通り、SUMIF関数を使って「オーガニックファンデーション」、「口紅（ワインレッド）」、「口紅（サーモンピンク）」という3商品の売れ行きを算出し、3商品のそれぞれの「合計売上」に、各商品の売上の合計を表示します。

図1

まずは図2.のように、下部にある3商品の「合計売上」にSUMIF関数を設定しましょう。
そして①範囲に「品名（A3:A27）」、②検索条件に「合計売上を算出したい品名（A:30）」、③合計範囲に「合計売上（E3:E27）」を選択します。

図2

すると、図3のようにオーガニックファンデーションの売上のみを合計した合計売上を計算できます。

図3

この作業を、残る2つの商品の「合計売上」でも行うと図4のようにそれぞれの合計売上を計算できます。

図4

≪ココに注意！≫
絶対参照をつけずにオートフィルをしてしまうと、数式がずれてしまいます。
3つの商品に共通した参照先である①範囲と③合計範囲は、絶対参照にして参照先を変えないようにしましょう。

② PRODUCT関数

PRODUCT関数は、複数の数値をまとめて掛け合わせることのできる関数。在庫管理では、定価×卸率×数量などの計算を求める時に使うことができます。

例として、図5のように単価、卸率、数量を掛け合わせた答えをPRODUCT関数で計算しましょう。

まずは、オーガニックファンデーションの「合計」セル（F3）でPRODUCT関数を選択。数値1で、積算をする値の入っている「C3:E3」を選択します。

図5

すると、図6のように「単価×卸率×数量」の合計が表示されます。

図6

あとは、計算したい範囲の終わりまでオートフィルで入力すれば計算完了。図7のように、他の商品についても「単価×卸率×数量」の合計が表示されます。

図7

③ CEILING & FLOOR関数

CEILING関数

CEILING関数は、基準値の倍数になるように数値を切り上げてくれる関数。箱単位でしか発注できない商品の在庫数を管理する時に便利です。

今回は、図8のように各商品の必要個数と1ケースあたりの個数にもとづいて、何ケース発注する必要があるのかを計算します。

まずは、オーガニックファンデーションの発注数を計算。はじめに、CEILING関数を使って必要個数と1ケース当たりの個数から基準値（1ケース当たりの個数）にもとづいた「換算数量」を求めます。オーガニックファンデーションの換算数量（D3）でCEILING関数を選択し、数値に「B3」、基準値に「C3」を入力します。

図8

オートフィルをつかって他の商品についても計算すると、図9の通り換算数量を求めることができます。

図9

次に、換算数量を使って発注する箱数を求めます。そこで図10のように、「発注（ケース）」の列に「換算数量/1ケース」という数式を入力。

図10

すると図11の通り、換算数量に応じた発注（ケース）数を求めることができます。

図11

FLOOR関数

FLOOR関数とは、基準値の倍数になるように数値を切り下げてくれる関数。ケース単位での購入をする際に、端数分を単品で購入するという場合の発注ケース数を求めるのに便利です。

CEILING関数と同じく、まずはオーガニックファンデーションの発注数を計算。オーガニックファンデーションの「換算数量」でFLOOR関数を選択し、数値に「B3」、基準値に「C3」を入力します。

図12

ばら売りの個数は、換算数量とともに後述するMOD関数を使って計算することができます。MOD関数は割り算での余りを計算する関数です。

図13

すると、図14の通り換算数量にもとづいた「発注（ケース）」と単品購入する「バラ個数」を求めることができます。

図14

④ ROUND関数

ROUNDDOWN関数とROUNDUP関数は、任意の桁で切捨てあるいは切上げできる関数。セール価格を算出する時に役立ちます。

ROUNDDOWN関数とROUNDUP関数は、切捨て・切上げする桁数を指定する必要する必要があります。
なお指定した値と切捨て・切上げする桁数との関係は、以下の表1の通りです。

表1

「ROUNDDOWN」の使い方

たとえば、図15のように定価に対して3割引の価格を計算した場合には「30%OFFの価格」の通りになります。当然ながら小数は価格に載せることができませんので、小数部分は切り上げるか切り捨てる必要があります。
仮に10の位、つまり10円単位で端数を切り捨てる場合には、図15のように桁数を「-1」に設定しましょう。

図15

すると、図16のように「30%OFFの価格」を十の位で切り捨てた価格を表示することができます。

図16

「ROUNDUP」の使い方

一方で、「30%OFFの価格」切り上げる場合にはROUNDUP関数を使いましょう。たとえば桁数を「0」に設定すると、図17のように一の位で切り上げられます。

図17

⑤ QUOTIENT関数&MOD関数

QUOTIENT関数

QUOTIENT関数を使えば、割り切れなかった小数点以下の余りを切り捨てて整数のみを求めることができます。日ごとの売り上げ単価を算出したいときに便利な関数です。

図18のように売上と売上個数から売上単価を求める場合には、QUOTIENT関数を使うと小数点以下を切り捨てて算出できます。QUOTIENT関数で指定するのは、分子と分母だけです。

図18

すると、図19のようにきれいに整数のみ求めることができます。

図19

MOD関数

MOD関数は、割り算の余りを計算する関数です。これを応用すると、複数のセール日やキャンペーンの開催日を楽に設定することができます。

たとえば、「3の倍数の日はキャンペーン」、「5の倍数の日は30％割引」をMOD関数で設定するケースを考えてみましょう。まずは図20のように、論理式に「MOD(A3,3)=0」、真の場合に「キャンペーン日」、偽の場合は「“”（空欄）」にします。

この数式は一見複雑に見えますが、仕組みが分かれば簡単です。
「=0」は、「余りがゼロ」を意味しています。つまり、余りがゼロの時にだけ、すなわち3の倍数の日だけ「キャンペーン日」というテキストを表示させるという式なのです。

図20

あとは図21のようにオートフィルで下までドラッグすれば、このように3の倍数の日だけ「キャンペーン日」の文字を表示させることができます。

図21

同様に「5の倍数の日は30％割引」については、5を割れば算出できます。
図22の通り、論理式は「MOD(A3,5)=0」、真の場合に「30%割引」、偽の場合は「“”（空欄）」となります。

図22

オートフィルで下までドラッグすれば、図23のように5日おきに「30%割引」の文字を表示させることができます。

図23

今回のまとめ

いかがでしたでしょうか。

関数を知るだけで、在庫管理の作業スピードが向上するのはもちろん、入力ミスを減らすこともできることが伝わったかと思います。Excelで在庫管理をしている方はぜひこれを機会に、今回紹介した関数を活用してみてください。