在庫管理のSUMIFSが合わない。現在庫が22個多く出た原因を、15行の履歴で1つずつ特定した

入出庫の履歴から現在庫を出す式はSUMIFSで足りる。ただしSUMIFSは完全一致でしか拾わないので、コードの末尾に空白が1つ入っているだけで、その行を黙って落とす。エラーは出ない。合計が少し小さくなるだけだ。

手元で15行の履歴を作って試したら、現在庫が22個多く出た。棚卸しをすれば22個足りない、という形で表面化する。

式と数字は、2026年9月20日にMicrosoft 365のExcel（バージョン16）で計算させた。

入出庫の一覧から現在庫を出す式

履歴のシートは4列でいい。日付、商品コード、区分、数量。マスタ側に初期在庫を持たせて、こう書く。

入庫計: =SUMIFS(入出庫!$D:$D,入出庫!$B:$B,A2,入出庫!$C:$C,"入庫")
出庫計: =SUMIFS(入出庫!$D:$D,入出庫!$B:$B,A2,入出庫!$C:$C,"出庫")
現在庫: =C2+入庫計-出庫計

C2が初期在庫だ。SUMIFSは、1つ目の範囲が合計する列、そのあとが条件の範囲と条件の組になる。組はいくつ並べてもいい。

この式で3商品を計算させた結果がこれだ。

一見きれいに出ている。問題は、A-100の出庫が本当は60ではないことだ。

15行のうち4行が落ちていた

わざと現場でよくある崩れ方を混ぜてあった。A-100に関係する行のうち、SUMIFSが拾わなかったのは4行。

合計22個。現在庫は100と出たが、この4行を数えると78になる。差の22は、棚卸しの日まで誰も気づかない。

数量が文字列の行は、コードも区分も正しい。それでもSUMIFSの合計には入らない。見た目は右寄せでなく左寄せになるが、列幅が狭いと気づけない。

なぜ落ちるのか

SUMIFSの条件は、既定で完全一致だ。"A-100"と書いたら、セルの中身がその6文字と完全に同じ行だけを拾う。末尾に空白が1つあれば7文字なので、別のものになる。

全角も同じだ。ＡとAは、見た目が似ているだけで別の文字コードを持つ。ExcelにとってはXとYくらい違う。

数量が文字列なのは別の理由で、合計の対象が数値だけだからだ。SUMもSUMIFSも、文字列は0として扱う。他システムからCSVで受け取った数量が文字列になっていることは珍しくない。

区分の表記ゆれは、入力する人が違えば必ず起きる。「出庫」「出荷」「払出」。どれも同じ意味で使われるが、式にとっては別物だ。

落ちた行を見つける点検の式

合わないと分かってから探すより、毎月4本の式を回すほうが早い。履歴のシートに対して、こう数える。

数量が数値でない行:      =SUMPRODUCT((D2:D100<>"")*ISTEXT(D2:D100))
マスタにないコードの行:  =SUMPRODUCT((B2:B100<>"")*(COUNTIF(マスタ!$A$2:$A$50,B2:B100)=0))
末尾や先頭に空白がある行: =SUMPRODUCT((B2:B100<>"")*(B2:B100<>TRIM(B2:B100)))
全角が混じる行:          =SUMPRODUCT((B2:B100<>"")*(B2:B100<>ASC(B2:B100)))

手元の15行で回したら、数量が数値でない行が1、マスタにないコードの行が3、末尾に空白がある行が1、全角が混じる行が1と出た。マスタにないコードの3行には、空白つきのものと全角のものが含まれている。マスタと突き合わせるだけで、表記の崩れも一緒に見つかる。

区分の点検も足すなら、入庫でも出庫でもない行を数える。

=SUMPRODUCT((C2:C100<>"")*(C2:C100<>"入庫")*(C2:C100<>"出庫"))

手元では1行が引っかかった。「出荷」と書かれた行だ。

補助列で直すと78になる

直し方は2つある。式を複雑にするか、補助列を足すか。私は補助列を選ぶ。式が読めるうちは、式を短く保ったほうが後で直せる。

履歴のシートの右に3列足す。

コード（直した）: =TRIM(ASC(B2))
数量（直した）:   =IFERROR(VALUE(D2),D2)
区分（直した）:   =IF(OR(C2="出荷",C2="出庫"),"出庫",IF(OR(C2="入荷",C2="入庫"),"入庫",C2))

ASCは全角を半角に変える。TRIMは前後の空白を落とす。この2つを通すと、A-100 もＡ-100もA-100になる。全角の数字が混じる書類を照合する話はAIが書いた文書を照合する記事にも書いたが、同じ関数がここでも効く。

集計はこの補助列に向ける。

=SUMIFS(入出庫!$G:$G,入出庫!$F:$F,A2,入出庫!$H:$H,"出庫")

計算させたら、A-100の入庫は140のまま、出庫が82になった。現在庫は78。素直に書いた式との差は22で、最初に数えた4行ぶんと一致する。

補助列を足すのが嫌なら、SUMPRODUCTで1本にもできる。

=SUMPRODUCT((TRIM(ASC($B$2:$B$100))="A-100")*(($C$2:$C$100="出庫")+($C$2:$C$100="出荷")),$D$2:$D$100)

これで70と出た。補助列の82と合わないのは、文字列の数量7個をこの書き方では拾えないからだ。SUMPRODUCTの2つ目の引数に渡した範囲では、文字列は0として扱われる。数量の型だけは、補助列か元データで直すしかない。

期間を絞る、発注点を出す

SUMIFSの条件は日付にも使える。10日以降の出庫だけを見るならこう書く。

=SUMIFS(入出庫!$D:$D,入出庫!$B:$B,A2,入出庫!$C:$C,"出庫",入出庫!$A:$A,">="&DATE(2026,10,10))

不等号は文字列にして、日付と&でつなぐ。">=2026/10/10"と直接書く方法もあるが、セルの日付を参照したいときに書き換えが要るので、私はDATEか日付のセルを使う。

発注点の判定は現在庫との比較だけだ。

=IF(G2<=D2,"発注","")

手元のデータでは、どの商品も発注点を下回らなかった。ただしA-100は、本当の現在庫78に対して100と表示されていた。この状態で発注点が80だったら、発注しなければならないのに「発注」が出ない。表記の崩れは、数字が合わないだけでなく、判断そのものを狂わせる。

踏んだ失敗

他システムからCSVを取り込んだ月に、在庫が全商品で合わなくなったことがある。原因は数量の列が文字列になっていたことで、SUMIFSの結果が0だった。0なら気づけそうなものだが、初期在庫を足しているので「在庫が減らない」という見え方になり、しばらく気づかなかった。

手入力のシートでは、コードの末尾の空白が定期的に混ざる。コピー元の表の都合で付いてくる。点検の式を入れるまでは、毎月どこかで1件か2件ずれていた。

区分を自由入力にしていたのも失敗だった。入力規則のリストにして「入庫」「出庫」の2つから選ぶ形に変えてからは、表記ゆれが止まった。直すより、入らないようにするほうが早い。

自分の表に入れるとき

配布している在庫管理のテンプレートは、この形で作ってある。商品マスタと入出庫の履歴を分けて、現在庫はSUMIFSで出す。

新しく作るなら、順番はこうなる。履歴のシートを作る。コードは入力規則でマスタから選ばせる。区分もリストから選ばせる。数量の列は表示形式を数値にしておく。そのうえで点検の4本を右上に置く。

点検の数字が全部0なら、その月の在庫は信用していい。1つでも0でないなら、集計より先に履歴を直す。順番を逆にすると、直した先から集計がずれる。