ハンドメイドの材料・パーツの在庫を、仕入れた数と使った数から出す|買い足す時期を式で知らせる

公開日: 2026-11-11

作品の在庫は数えやすくても、金具・ビーズ・糸のような材料は、気づいたら足りないということが起こります。イベントの前の週に金具が切れて、急ぎで店頭で高く買う、ということもあります。

この記事では、材料ごとに「仕入れた数」と「使った数」を1行ずつ書き、残りを式で出して、決めた数を下回ったら「買い足す」と出す表を作ります。最後に、買い足す材料だけを並べた買い物リストも式で作ります。

表は3つに分ける

材料の表: 材料の名前・初めの在庫・買い足す目安(この数以下になったら買う)・1回に買う量
仕入の表: 買った日・材料・仕入先・数量・金額を1回ごとに1行
使用の表: 使った日・材料・使った数・作品を1回ごとに1行

材料の表の数は手で書き換えず、仕入と使用の記録から式で出します。数を直接書き換えると、いつ・何に使ったのかが残らず、合わなくなったときに原因を探せないためです。作品の在庫を販売記録から出す考え方は、作品の在庫を販売記録から自動で減らす記事と同じです。

残りと「買い足す」の式

J2(仕入れた数): =IF($A2="","",SUMIFS(仕入!$D$2:$D$2001,仕入!$B$2:$B$2001,$A2))
K2(使った数):   =IF($A2="","",SUMIFS(使用!$C$2:$C$3001,使用!$B$2:$B$3001,$A2))
L2(残り):       =IF($A2="","",N(D2)+J2-K2)          D = 初めの在庫
M2(状態):       =IF(OR($A2="",E2=""),"",IF(L2<=E2,"買い足す",""))   E = 買い足す目安

この例では、ピアス金具(残り6ペア、目安10)・UVレジン液(残り1本、目安1)・刺しゅう糸(残り2束、目安3)の3つに「買い足す」が出ました。

「初めの在庫」は、表を使い始めた日に数えた数です。途中から使い始めても、そこから先の仕入と使用を書けば合います。年末に数え直して記録と比べるときは、棚卸しで帳簿の数と数えた数の差を金額で見る記事の形で差を金額にします
目安は「使い切るまでに次の分が届くか」で決めます。ネットで買う材料は届くまでの日数分、店頭で買える材料は少なめで足ります
目安の欄を空にした材料は「買い足す」を出しません(梱包の袋のように、残りを気にしなくてよい材料に使います。梱包資材を発送ごとに数えたいときは、梱包資材の1個あたりの値段と残りの数の記事)

使った数の書き方

使った数は、作品を作った日にまとめて1行で書きます。「ビーズのピアスを30点作った → 金具30ペア・ビーズ240個(1点8個)」のように、作品の数 × 1点に使う量で書くと、あとで見ても数の根拠がわかります。1点に使う量は、原価の出し方の記事で作る「作品に使う量」の表と同じものを使えます。

残りがマイナスになったら、仕入の書き忘れ・使った数の書きまちがい・初めの在庫の数えまちがい・単位の取りちがえ(個とセットなど)のどれかです。月に1回くらい実際の数を数えて、ずれていたら使用の表に「数え直し」の行を足して合わせます(残りを手で書き換えないでください)。表の残りより実際の数が少ないときは差を正の数で、多いときは差をマイナスの数(例: -5)で書きます。使った数のマイナスは、残りを増やす向きに働きます。

「買い足す」の材料だけを並べる

材料が50種類を超えると、「買い足す」の行を目で探すのも手間です。買い足す材料だけを上から並べる表を、式で作ります。

材料の表に、並べるための列を1つ足します。

R2(並び): =IF(M2="買い足す",ROW(),"")

買い物リストのシートでは、この列の小さい順に材料の名前を取り出します。

A3: =IFERROR(INDEX(材料!$A:$A,SMALL(材料!$R$2:$R$201,1)),"")
A4: =IFERROR(INDEX(材料!$A:$A,SMALL(材料!$R$2:$R$201,2)),"")

SMALL の2つ目の数(1・2・3…)を行ごとに1つずつ増やします。並べる対象が無くなると SMALL がエラーになり、IFERROR で空になります。残り・1回に買う量・仕入先も、同じ SMALL(…,k) の行番号を INDEX に渡して取り出します。

金額の目安は「1回に買う量 × 最後に買ったときの単価」です。刺しゅう糸は、この表を使い始めてから一度も仕入れていないので単価がわからず、空になっています。最後に買ったときの単価と、いちばん安かった仕入先の出し方は、材料の単価を仕入先ごとに比べる記事で説明しています。買い足すついでに送料無料の線まで足すかは、送料無料まであと何円かの記事の比べ方で決められます。

Excel 365・Google スプレッドシートなら、=FILTER(材料!A2:A201,材料!M2:M201="買い足す") の1つの式でも同じ一覧になります。古い Excel でも動くように、テンプレートでは SMALL の形にしています。

LibreOffice で式の区切りが ; の設定になっている場合は、, を ; に置き換えて入力します。

作った表を、そのまま使いたい方へ

この記事の表は、設定(仕入先・送料)・材料・仕入・使用・買い物リスト・仕入先別をまとめたテンプレートで作りました。残りと買い足す材料のほか、仕入先ごとに「送料が無料になるまであと何円か」も式で出ます。表を一から作る時間を省きたい方は、ハンドメイド材料・パーツの在庫と買い足し管理シートもご覧ください。

カテゴリ: 在庫・材料・委託