材料の単価を仕入先ごとに比べる|最後に買った単価と、いちばん安かった仕入先を式で出す

公開日: 2026-11-11

同じ金具でも、ネットでまとめて買うか、店頭で少しだけ買うかで1個あたりの値段が大きく変わります。急ぎで店頭で買ったら、ネットの1.5倍だった、ということは珍しくありません。

この記事では、仕入の記録から1回ごとの単価(送料込み)を出し、材料ごとに「最後に買ったときの単価」と「いちばん安かった単価・仕入先」を式で出します。どこで買うと安いかが、記録を書くだけで見えるようになります。

単価は送料込みで出す

仕入の表には、材料の金額と送料を分けて書きます。1回の注文で複数の材料を買ったときは、送料をどれか1行にだけ書きます(2行に書くと送料が2回数えられます)。

H2(単価・送料込み): =IF(OR($E2="",N($D2)<=0),"",(N($E2)+N($F2))/$D2)

この例のピアス金具は、ネットショップAで50ペアまとめて買うと送料込みで1ペア 55.4円、急ぎで駅前の手芸店で10ペア買うと 88円でした。送料を1行(ピアス金具)にまとめて書いたので、同じ注文の丸カンの単価には送料が入っていません。そのため、この書き方の送料込みの単価は、送料を書いた行だけ高めに出ます。材料どうしで単価を公平に比べたいときは、送料を材料の金額の割合で分けて、行ごとに書きます(例: 2,400円と650円の注文で送料370円なら、291円と79円)。

最後に買ったときの単価

材料ごとに、仕入の表のいちばん下(最後)の行の単価を取り出します。

N2: =IF(COUNTIF(仕入!$B$2:$B$2001,$A2)=0,"",LOOKUP(2,1/(仕入!$B$2:$B$2001=$A2),仕入!$H$2:$H$2001))

外側の IF は、まだ一度も仕入れていない材料を空にするためです(無いと #N/A になります)。仕入!B=A2 が当てはまる行は 1/TRUE = 1、当てはまらない行は 1/FALSE でエラーになります。LOOKUP は、探す値(2)より大きい値が無いとき、エラーを飛ばして最後の 1 の位置を返すので、いちばん下の当てはまる行の単価が出ます。古い Excel でも、配列数式として入力しなくても動く書き方です。

仕入の表は、日付の古い順に下へ書き足していく前提です。日付を後から挟むと「最後の行」と「最後の日付」がずれるので、並べ替えてから見ます
テンプレートでは、単価が出ない行(数量が空など)を飛ばすため、計算用の列を足して条件を1つ増やしています

いちばん安かった単価と、その仕入先

Excel 2019 以降・Microsoft 365・Google スプレッドシート・LibreOffice なら、MINIFS で書けます。

O2: =IF(N2="","",MINIFS(仕入!$H$2:$H$2001,仕入!$B$2:$B$2001,$A2))

Excel 2016 以前でも動くようにするには、当てはまらない行をとても大きな数に置き換えて SMALL で最小を取ります。

O2: =IF(N2="","",SUMPRODUCT(SMALL((仕入!$B$2:$B$2001=$A2)*仕入!$I$2:$I$2001+(仕入!$B$2:$B$2001<>$A2)*1E+99,1)))

(I 列は、単価が数のときは単価、空のときは 1E+99 にする計算用の列です。空の単価が 0 として最小に選ばれるのを防ぎます。どちらの式も、仕入の記録が無い材料は N2="" で空にします。MINIFS は当てはまる行が無いと 0 を返すためです)

安かった仕入先は、「材料が同じ」かつ「単価がいちばん安い単価と同じ」行を INDEX/MATCH で探します。

P2: =INDEX(仕入!$C$2:$C$2001,MATCH(1,INDEX((仕入!$B$2:$B$2001=$A2)*(仕入!$I$2:$I$2001=O2),0),0))

この例では、ピアス金具は最後に買った単価(88円)が最安(55.4円)の1.6倍でした。チェコビーズは、お試しでネットショップAから買った 13.5円より、ネットショップBの 12.7円の方が安くなっています(画面の単価は小数1けたで表示しています)。同じ単価の行が2つあるときは、上(先に買った方)の仕入先が出ます。

「在庫の額」は 残り × 最後に買った単価 の目安です(OPP袋 70枚 × 0.55円 = 38.5円 → 39円)。材料の在庫にいくら置いているかの目安で、帳簿の金額とは別のものです。

仕入先ごとの合計

仕入先ごとに、買った回数・材料の金額・送料を SUMIFS・COUNTIFS で集めると、送料をどこで多く払っているかが見えます。「買った回数」は仕入の表の行の数なので、1回の注文で材料を2行に分けて書くと2回と数えます(この例のネットショップAの3回は、2回の注文の3行です)。

B2(買った回数): =COUNTIFS(仕入!$C$2:$C$2001,A2,仕入!$A$2:$A$2001,"<>")
C2(金額の合計): =SUMIFS(仕入!$E$2:$E$2001,仕入!$C$2:$C$2001,A2)
D2(送料の合計): =SUMIFS(仕入!$F$2:$F$2001,仕入!$C$2:$C$2001,A2)

送料が無料になる金額が決まっているお店では、買い足す材料をまとめると送料が要らなくなることがあります。買い足す材料の一覧の作り方は、材料の在庫と買い足しの時期を式で出す記事で説明しています。単価がわかれば、作品1点の材料費もこの単価から出せます(原価の出し方の記事)。まだ買っていない店も含めて、買う前に候補の店を送料込みの1個あたりで比べる方法は、材料の仕入れ先を比べる記事で説明しています。

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

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

この記事の表は、材料の在庫・買い物リスト・仕入先別をまとめたテンプレートで作りました。最後の単価・いちばん安い単価・安かった仕入先のほか、仕入先ごとに「送料無料まであと何円か」も出ます。表を一から作る時間を省きたい方は、ハンドメイド材料・パーツの在庫と買い足し管理シートもご覧ください。

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