EXCELで先入先出の商品有高帳

質問

こんにちは。
EXCELで先入先出についてお聞きします。

ヘッダ部に品番を配置し
日付、受入欄(数量、単価、金額）、払出欄（数量、単価、金額）、残高欄（数量、単価、金額）の項目で
EXCELで先入先出の商品有高帳を作成したいのです。

移動平均でしたら払出の単価欄は１行前の残高の単価や残高欄の数量欄は受入数計-払出数計、
金額欄は受入額計-払出額計、単価欄は金額÷数量といった式を設定しましたが

先入先出の場合、式の設定といったら金額欄に数量×単価程度で、残高欄も
単価によって変わってきますので手入力と考えましたが

効率良く商品有高帳を入力する為に、どのような式を設定していますか？

最悪、ほとんどが手入力になってしまうのでしょうか？

分かる方おられましたら、教えて頂けないでしょうか。
宜しくお願いします。

回答No.12

bunjii · ベストアンサー

>最後に残数の式だけお聞きしたいのですが受入数-払出数だけではないのでしょうか？
最終入出の数量を入力した結果の受入毎の残高は以下の数式で算出しています。
H3=IF(B3="","",MIN(B3,MAX(SUM(B$3:B3)-SUM(E$3:E$15),0)))
H3セルを下へコピーします。
B列が空白の行は受入が無いので残高の計算をしません。
検証での範囲は3行目から15行目までにしてありますので範囲を広げて実データで試してください。
今回は別表を貼付しますので払出の管理に利用してみると良いでしょう。
この数表を利用すると払出の数量と金額を数式で導けます。
つまり、別表へ払出の引き当て数量を入力することで自動的に以前に提示した数表が出来上がります。

回答No.11

bunjii

>7/1の受入の50枚は7/3の払出があてられ
>0になるかと思いますが、0にするのは
>手入力で0にするのでしょううか？
「0になるかと思います」ではなく「0になるかことが分かりました」でしょう。
考え方としてそれで良いか否かはあなたが判断することなので考え方（管理の仕方）を否定されては論理を説明しても無駄になります。先にその点を述べてください。

>7/3の払出は50枚が単価400円、50枚が単価500円となりますがその平均を自分で計算して単価入力するのでしょうか？
現時点では払出の記入前の残高明細と払出数から数量の割り振りを手動で算出しています。
作業用の数表を別枠で作成すれば数式を組み込めると思います。（未検証）

>おそらく7/7の払出も30枚が単価500円,50枚が単価400円となると
思いますが。
計算し直してください。
7/6までの払出しの合計が150なので7/1の50と7/2の100が0になりますので7/7の80は7/4の100から引き当てしなければなりませんので単価は400です。

>どこまでが、運用者の手入力なのか、どこまでが式をセットして自動計算なのかがわからないのです。
考え方が逆ではないでしょうか？
極力自動計算するのがシステム化の課題なので本来の考え方をすれば自動倉庫のシステムを専門家に依頼して在庫管理の省力化に繋げるものではありませんか？
私の提示はあなたの質問内容からExcelの表計算で何処まで省力化が可能かを模索した未完成の数表です。
安易な質問と安易な回答で解決できるようなものとは思えません。
本来ならやりたいことの詳細を聞いてVBAのプログラムを組むべきものと思います。
当方の検証ではH列の処理は数式を設定してB列の受入数とE列の払出し数から算出できるようにしています。
数式を提示しても理解できないと思いますので計算の論理まで解説が必要になりそうでQ&Aが長引きそうな予感がします。
会社の在庫管理に関する合理化が目的と推測しますので社内の業務改善として取り上げて専門家によるシステム化をお薦めします。

補足

大変、詳しい回答ありがとうございます。
確かにおっしゃる通りだと思います。
最後に残数の式だけお聞きしたいのですが
受入数-払出数だけではないのでしょうか？

申し訳ございません、どうぞよろしくお願いします。

回答No.10

bunjii

>そもそも先入先出法では異なる単価ごとに記入するものではないのでしょうか？
「単価毎に」と言うより「受入順毎に」と言う考え方です。
同一単価でも受入順が前後するはずですから「単価毎に」管理されることではないと思います。

>また7/1の残高数が50枚でないのはどうしてでしょうか？
貼付画像のH3セルの値が50ではないことについての疑問でしょうか？
最終の入出が7/9なので「現在の残高」は「7/9現在の残高」と解釈してください。
7/1に受け入れられた50は7/3の払出し100の内50を充当すればその時点で残高が0になり、以降に増えることはありません。従って、7/9時点の残高は0です。
A列の日付に於ける残高を管理する列を併記したいのであれば一般的に使われている出納帳の残高の計算と同じにすれば良いでしょう。
一般的な出納帳には先入れ先出しの要素がありませんので在庫管理システムで払出し指示伝票を発行して同一品番の古い順に数量を記載しなければなりません。
私が提示した数表は最終入出後の残高を受入順に算出するためのものです。
あなたの目的に合わなければ無視してください。

補足

おはようございます。
何度もありがとうございます。

何度も聞いて申し訳ありません。
7/1の受入の50枚は7/3の払出があてられ
0になるかと思いますが、0にするのは
手入力で0にするのでしょううか？
また、式を入れるのでしょうか？

7/3の払出は50枚が単価400円、50枚が単価500円と
なりますがその平均を自分で計算して単価入力するのでしょうか？

おそらく7/7の払出も30枚が単価500円,50枚が単価400円となると
思いますが。

どこまでが、運用者の手入力なのか、どこまでが式をセットして
自動計算なのかがわからないのです。

大変申し訳ありません、今一度教えて頂けないでしょうか。
どうぞよろしくお願いします。

回答No.9

bunjii

>VLOOKUPを使ってという意見もあるので
VLOOKUP関数を使って何処のセルへどのような値を代入したいのですか？
回答No.1の補足に模擬データを提示していますが7/3の100個払出を7/1の50個受入と7/2の100個受入の内50個を充当する計算は簡単なよう見えても数式化が困難な条件です。
Excel 2013で添付画像のような表を作成してみましたので目的に合うか否かを確認してください。
現在の残高は最後に払い出した結果を受入日毎の残高を算出しています。
払出の数量は受入日の古い順に充当しますが単価の割り当ては手計算になっています。
使った数式は満足できる数表であれば提示することは可能です。
但し、数式を解読できないと応用面で行き詰まることになります。

補足

回答ありがとうございます。
VLOOKUPは別の方の教えがあったので、そのように書きました。
そもそも先入先出法では異なる単価ごとに
記入するものではないのでしょうか？
また7/1の残高数が50枚でないのはどうしてでしょうか？
どうぞよろしくお願いします。

回答No.8

aokii

テキストではEXCELを入れ込めません。（データが一部連続していて読めません。）
EXCELファイルそのものが無いとお望みの回答はできませんので、どうしても回答がほしいのでしたら、Googleドライブ等で、数値は違っていても本当のデータ形式で、EXCELファイルそのものを、公開（URL公開）して頂くしかありません。
なお、回答を受け取っても、使いこなすには、それなりのEXCEL知識が必要ですので、できれば、市販の図書を使って、1か月ほど、EXCELの初歩的な勉強をされるのがお勧めです。

補足

何度もありがとうございます。
他の方からの投稿でexcelの表がありますが
先入先出法では異なる単価ごとに
行を別けていないのでしょうか？

回答No.7

bunjii

>そうすると、金額欄に式を入力するくらいですか。
VBAでInputBoxを使えば払出の数量を入力して目的通りの先入れ先出しの処理が可能になるでしょう。（プログラマーに依頼してください）
数式のみ設定可能なことは次のとおりです。
受入と払出の金額欄に数量×単価の数式を入れることができます。
他には残高の数量と金額欄は受入と払出の差を求める数式で求められますが単価は平均単価として金額÷数量で算出することになるでしょう。

>異なる単価が発生した場合、残高欄を単価ごとに複数にするのも全て手作業かと。
単価毎の数量を算出するときは手作業になるでしょう。

補足

bunjiiさん、何度もありがとうございます。

おっしゃる通りだと思うのですが
なかなかあきらめつかないもので。

VLOOKUPを使ってという意見もあるので
何度も投稿させて頂いたのですが、
単価が異なりシフトさせたりしなければ
ならないので式だけでは無理ですよね。

回答No.6

aokii

受入数－払出数＝残数ですので、残数セルの式が、以下の式です。
=VLOOKUP(払出品番セル,受入の品番列:受入の数列,2,FALSE)-払出数セル

残高は残数セル×受入単価ですので、残高欄の式は、以下の式です。
=残数セル×受入単価セル

そのままの単価ごとの残数を自分で計算して手入力にする必要はありません。

実際のEXCELファイルが無いと、文字で説明するのは難しいです。

補足

すいません、何度もありがとうございます。
EXCELを入れ込んでみました。

日付 受入 払出 残高
数量 単価 金額 数量 単価 金額 数量 単価 金額
7/1 50 40 2,000 50 40 2,000
7/2 100 50 5,000 50 40 2,000
100 50 5,000
7/3 50 40 2,000 0 40 0
50 50 2,500 50 50 2,500

このようなEXCELで7/2は単価が違うため
２行になりますが、このようなEXCELで
VLOOKUPは設定できますでしょうか？

回答No.5

bunjii

>ヘッダ部に品番を配置し
ExcelのヘッダーはページレイアウトでBook単位で設定するものと解釈していますが質問文の「ヘッダ部」とはこのことでしょうか？

>EXCELで先入先出の商品有高帳を作成したいのです。
商品の品番とロット番号を組み合わせた在庫管理をしないと辻褄が合わなくなります。
同一品番であれば品質が同じで製造日が同一でも仕入時期によって単価が異なることも起こります。このようなとき実物と帳簿の残高が金額においで一致しなくなります。
払出し作業で払出し要求数量が仕入日を跨るとき払出し伝票を起こして実際の受入日に合致した商品の数を分割して記載しないと辻褄が合わなくなるでしょう。
従って、商品の移動と事務処理上の数合わせは自動倉庫のシステムを組まなければならないと思います。
Excelの数式では解決できません。

>最悪、ほとんどが手入力になってしまうのでしょうか？
最善も最悪もありません。
全て手入力（人の判断）になるでしょう。

補足

bunjiiさん、回答ありがとうございます。
そうすると、金額欄に式を入力するくらいですか。

VLOOKUPも使えないような気がしますし。
異なる単価が発生した場合、残高欄を単価ごとに複数に
するのも全て手作業かと。

今一度よろしくお願いします。

回答No.4

aokii

残高欄（列）は、単価ごとに2行、3行と別れて式を設定できます。
品番が異なると、単価ごとの残数量を求めることはできませんので、品番別に単価ごとの残数量を求めることになります。
ちなみに、残数や残高欄に式を入れたら、その列で下にドラッグコピーするだけで、全ての行にその式が全自動で適用されます。

お礼

aokiiさん、回答ありがとうございます。
残高欄の残数の式は無く、そのままの単価ごとの残数を
自分で計算して手入力になるかと思うのですが。

いろいろ何度も申し訳ありません。

回答No.3

aokii

先入先出法は単価毎に記入しますが、50個、50個と別けて入力する判断は運用者です。それからVLOOKUPの検索値が払出品番セルとは品番ごとに管理したい場合です。また残高欄には、残数量×単価の式を設定します。残数量が計算できれば残数量×単価で残高欄が自動計算できます。その品番の、その単価の、その残数量の、その残高欄が自動計算できます。
つまり、日付、受入欄(数量、単価、金額）、払出欄（数量、単価、金額）を手入力で記入すると、残高欄（数量、金額）の項目（数量、金額）が3つとも自動計算されます。

補足

aokiiさん回答ありがとうございます。
大変申し訳ありません、
品番別にシートを別けますが、VLOOKUPの設定箇所、意味が今一解らないのですが。
また残高欄ですが、単価ごとに2行、3行と別れて式を設定できるのでしょうか？

単価ごとの残数量をどのように求めるのでしょうか？
何度も、何度も申し訳ありません。
どうぞよろしくお願いします。

回答No.2

aokii

7/1 50個 単価 400円
7/2 100個 単価 500円
と受入があり
7/3に100個払い出す場合
7/3 50個 単価 400円 単価400円の残高0
7/3 50個 単価 500円 単価500円の残高50
払出欄にVLOOKUPの以下のような式を入れます。
=VLOOKUP(払出品番セル,受入の品番列:受入の数列,2,FALSE)

補足

aokiさん何度もありがとうございます。
今一分からないのですが、先入先出法は単価毎に
記入するかと思いますが、50個、50個と別けて入力する判断は
運用者ですよね。それからVLOOKUPの検索値が払出品番セルとは
どういうことになりますか。また残高欄には式の設定はしないのでしょうか。
大変申し訳ありません、よろしくお願いします。