前回の記事「実践Excelスキル共有：「列並べ替えツール」の実践操作をいくつか紹介します」では、いくつかの並べ替えツールの実践操作を学びました。今日は4つの関数を学習しますが、これらを上手に使うと、在庫を自動集計できる「仕入・販売・在庫・入出荷統計表」を作成することができます。この操作を覚えれば、面倒な作業から解放され、詩と距離を楽しむことができます。
リアルタイムの在庫と倉庫内外の数量を自動的にカウントできる仕入、販売、在庫テーブルを作成するにはどうすればよいでしょうか?実際、これには必要ありません。非常に難しい技術ですが、4 つの機能をマスターするだけで、Excel の基本的な編集と組版のスキルがあれば、自分で作成することができます。
これら 4 つの関数は、vlookup、iferror、sumif、および if です。次に、ベテランにこの入口と出口の表を作成する手順を説明してもらいます。
「購買、販売、在庫、入出荷統計テーブル」の機能説明:
リアルタイム統計機能: 出荷と受信を記録するだけで済みます。所定のフォーマットに従ってフローテーブルを作成し、最新の在庫と入出荷数量をリアルタイムに自動的に統計します。
スマートリマインダー機能: アイテムの在庫が安全在庫量を下回ると、自動的にマークが付けられ、警告効果が得られます。
「購買、販売、在庫、入出荷統計テーブル」の構成:
最も基本的な要件によると、通常、次の 3 つの部分で構成されます。データテーブル（基本情報テーブルとも呼ばれます）、入出力レコードテーブル（フロー詳細テーブルとも呼ばれます）、在庫統計テーブル（結果クエリテーブルとも呼ばれます）。以下に、これら 3 つの部分のそれぞれの方法について説明します。
1. 基本データ テーブル
企業の実際のニーズに応じて設計し、基本原則を把握し、テーブルは次のことができる必要があります。すべての項目属性が反映され、各属性は別個の列に格納されます。表は美しい必要はなく、結合されたセルが表示されてもなりません。
たとえば、次の図は、比較的標準化された基本的なデータ テーブルです:
注: シリアル番号は必要ありません。検索を容易にするためだけです。統計情報通常は製品コードのみを使用しますが、万が一自社製品にコードがない場合は、シリアル番号をコードとして使用することもできます。
統計データの正確性を確保するため、新製品があった場合はテーブルにレコードを追加する必要がありますが、廃止された製品がある場合は元のレコードを削除する必要はありません。
2. インバウンドとアウトバウンドの記録テーブル
通常、アウトバウンドとインバウンドは 2 つのシートに分けて保存されますが、結合して保存することもできます、便宜上、例としてそれらをまとめました。
テーブルのデータ列には、基本的な製品情報に加えて、出入りの日付と数量を含める必要があります。形式はおおよそ次のとおりです:
上のフロー テーブルでは、青色の列 A、E、F、G のみを時間内に記録する必要があります。分類、名前、単位列などの基本情報は数式によって自動的に生成されます。お察しのとおり、vlookup の出番です。
はい、ここで vlookup が登場します。下の図から、エンコード後の 3 つの列がすべて vlookup 関数を使用して取得されていることがわかります。セル B2 の数式は次のとおりです: =VLOOKUP($A2, 基本データ テーブル!$B:$E,COLUMN(B1),0)
# 式の解釈: vlookup には合計 4 つのパラメーターが必要です。基本的な形式は
=vlookup (検索値、検索範囲、列数、完全検索)
最初のパラメータ $A2 は、検索するコンテンツを表します。数式は右にプルダウンする必要があるため、先頭に $ が追加されていることに注意してくださいA を使用して列をロックし、列が右に引き下げられないようにします。エラーが発生しました;
2 番目のパラメータBasic data table!$B:$Eは検索対象の領域 (記事の前半で紹介した基本データ テーブル) を表します。注意してください。この領域はエンコード列から始まります。エンコードは基本データ テーブルの B 列にあるため、領域もエンコード列ではなく B 列から始まります。列 A. 多くの初心者がここで vlookup を使用するため、これを覚えておく必要があります。間違いが発生しました。
3 番目のパラメータは、返されたコンテンツが検索領域のどの列であるかを示します。右に引っ張られるようにするには、返される列の数として column(B1) を使用します。
column の機能は、パラメータの列番号を取得することです。 基本データテーブル$B:$Eの列2であるC列を返したいのですが、パラメータ基本データテーブル全体のセルB1の列番号は2なので、ここではを使用します。 column(B1 ) は、返される列の数を示します。数式を右に引くと、B1 が C1 になり、列番号が 2 から 3 に変わり、数式の右に引いて複数の列参照を完了するという目的が達成されます。
最後のパラメータ 0 は完全検索を示します。
テーブルの最後の 3 つの列、日付と倉庫への出入りの数量は、実際の状況に応じて記録できます。通常、このフローチャートは完成していますが、より賢く使用するために、vlookup 部分も最適化することができます。
製品コードを入力するときに、間違って入力すると (または、基本データ テーブルにない新しいコードを入力すると)、文字化けしたコードが表示されます。
この効果はあまり美しくないので、この問題を解決するには、別の関数 iferror を使用して vlookup と連携する必要があります。数式は次のように変更されます: =IFERROR(VLOOKUP($A2, Basicデータ テーブル!$ B:$E,COLUMN(B1),0),"エンコーディングが間違っているか確認してください!")
これはおそらくiferror を初めて見る友人もいます。 この関数を簡単に紹介しましょう:
=iferror(数式、数式の結果が間違っている場合に表示される内容)、数式には 2 つのパラメータのみが必要です。パラメータは数式で、2 番目のパラメータは数式の結果が間違っている場合に表示する必要があるパラメータです。この例では、最初のパラメータは vlookup です。vlookup の結果が正しい場合、iferror は効果がありません。しかし、vlookup の結果が間違っている場合は、必要なコンテンツが表示されます。この例では、テキスト文字列が表示されます。エンコードに誤りがありますのでご確認ください。注: 表示するコンテンツがテキストの場合は、引用符で囲む必要があります。
3. 在庫統計テーブル
この在庫統計テーブルの機能は、すべての商品の在庫状況をリアルタイムに表示することです。情報: 累計出荷数量、累計入荷数量、現在の在庫数量。不足プロンプトが必要な場合は、安全在庫数量と在庫切れかどうかも必要です。
この統計表は別途作成する必要はなく、基本データ表の後に記載した内容を追加するだけでよく、形式は以下のとおりです。
基本データ表の最後に6つの列が追加されていることがわかります。入力する必要があるのは、初期在庫量と安全在庫量だけです。累計出荷数量、累計入荷数量、在庫切れの有無を入力する必要があります。はすべて必須です。これは数式によって実現されます。これらのフィールドの簡単な説明は次のとおりです:
初期在庫: 在庫繰越とも呼ばれます。元の在庫は、この入退出統計テーブルが有効になっているときに記録されます。 。
累計出荷数量 (G 列): 式 =SUMIF(入出荷記録表!A:A,B2,入出荷記録表!F:F) を使用します統計結果:
数式分析: sumif 関数には 3 つのパラメータが必要です。基本構造は =SUMIF (条件領域、条件、合計領域)
受信および送信レコード テーブル!A:A は条件列を表します;
B2前の条件列が満たすべき条件を示します (行の品目コードに対応);
受信および送信レコード テーブル!F:F は、条件が満たされるか、この列で合計されることを示します。
受信および送信レコード テーブル!$F:$F を 受信および送信レコード テーブル!$G: に置き換えます。 $G累積倉庫数量を取得します (列 H):
=IF(I2>J2,"","在庫切れ")
; この例の条件は、
、つまり、現在在庫量が安全在庫量より大きい場合は空白と判断し、それ以外の場合は「在庫切れ」となります。在庫」を取得します。 同時に、この列には条件付き書式が設定されているため、不足している場合は色を使用して目を引く効果を得ることができます。
設定方法は、k列を選択し、[条件付き書式]→[セルの強調表示ルール]→[等号]をクリックします:
左側の Enterボックス内の「在庫切れ」という文字をクリックし、右側で希望する効果を選択して確認します。
この時点で、自動統計入口および出口テーブルを簡単に実装できます。このツールを使えば、数千点の倉庫在庫を計算間違える心配がなくなり、不足がわかったら調達先に指示して購入することができ、効率も上がりました！
最後に、同様のインバウンド統計テーブルとアウトバウンド統計テーブルの設計思想はほぼ同じであることを説明します。実際のアプリケーションに基づいていくつかの最適化を行うことができます。単価やその他の情報があれば、それを行うことができます。基本データ表で作成したものを加算し、数量×単価で金額を求めます。
データ入力を標準化するためにデータの有効性を使用することをお勧めします。たとえば、エンコードが一意である必要がある場合は、有効性を設定して重複入力を防ぐことができます (その方法がわからない場合)これは、メッセージを残すことができます)。
数式の保護を設定して、誤操作による数式の破壊やデータの精度への影響などを防ぎます...
関連する学習の推奨事項: Excel チュートリアル
以上が実践的な Excel スキルの共有: 関数を賢く使用して、仕入、販売、在庫の統計表を自動作成します。の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。
