☰

Excelで注文から売上を減算する

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 vba
Excelで注文から売上を減算する

私はここ数時間、自分が抱えている問題に似たものを見つけようとしてきましたが、現在、耳から煙が出ています。

シートが2枚あり、1枚には注文2番目は販売。

自動的に助けが必要減算するから注文に基づく販売。

私はすべての販売で実行されるスクリプトを開発しようとしており、位置そしてアイテムまったく同じ場合は、注文数量から販売数量を差し引いて「新しい支払予定」数量を算出します。

注文のない販売もありますが、これは問題ではありません。ただし、それらの結果を別のタブに移動できれば便利です。以下は、初心者向けに必要なものの概要です。

Sheet1は販売された、Sheet2は注文。

もしSheet1 C2＆F2Sheet2（のどの行も同じですA2、D2相関関係にあるフィールド）減算Sheet1 G2、 からSheet2 F2。

ここに例を追加しました。複数の条件が満たされた場合に減算する https://app.box.com/s/2m8nfjo8lieh5mfb9wgspy73f1bvzp0e

結果タブで黄色で強調表示されているものはすべて変更されたものです。黄色で強調表示されていないアイテムは販売されていません。緑と赤の書式設定は参照用であり、難しい場合は不要です。(理想的です) 列 H は、注文数量が 0 に達すると、スクリプトが同じ条件で次の注文のシートを検索し続け、残りの数量を減算することを示しています (これが可能であれば)。どんなことでも役立ちます。現在、週末に手動でこれを行っており、通常は週に 150 件以上の販売があり、非常に時間がかかります。

簡単に言うと、スクリプトで各販売行を調べ、工場と部品番号が同じ場合は注文書から数量を減算しようとしています。注文書は、いつ実行されるかに基づいて並べ替えて、正しい順序から削除します。

ご協力いただきありがとうございます!

答え1

VBA は必要ないと思います。

SUMIFS() を見てください

私の組織のリバース プロキシでは app.box へのアクセスが許可されていないため、スクリーンショットを見ることはできませんが、次のように推測しています。

  • Sheet1 の「C」列には場所が含まれています。
  • Sheet1 の「F」列には項目が含まれています。
  • Sheet1 の「G」列には販売数量が含まれています。
  • Sheet2 の「A」列には場所が含まれています。
  • Sheet2 の「D」列には項目が含まれています。
  • Sheet2 の「G」列には注文数量が含まれています。
  • シート2の「H」列には、支払予定数量が含まれています。

シート2では、行2に記載されている品目と場所の合計販売数量は次のとおりです。

=IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))

したがって、Sheet2!H2 に使用される数式は、注文数量からこれを単純に減算することになります。

=G2-IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))

ただし、注文が満たされ、同じ場所と品目に対する後続の注文が少なくとも部分的に満たされている場合は、販売合計が行 2 の注文よりも大きくなり、注文数 - 販売数がゼロ未満になる可能性があります。ただし、これは簡単に防ぐことができます...

=MAX(0,G2-IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2)))

しかし、これは現在の行より上の注文を考慮に入れていません。MIN(0, ThisOrderQuantity- )になっていますが、MIN(0, + - )LocationItemQtySoldであるべきです。Sheet2の現在の行より上のすべての注文の合計はThisOrderQuantityPreviouslyOrderedQuantitiesLocationItemQtySold

=OFFSET(G2,0,0,ROW()-2, 1)

... そうですね... 2 行目で壊れてしまいます。最初の行を含むすべての行で機能するものが必要です。それでは、2 行目で保護しましょう。

=IF(ROW()<3,0,OFFSET(G2,0,0,ROW()-2, 1))

しかし、同じ場所と商品に対する以前の注文だけが欲しいのに、それはすべて以前の注文です。別のSUMIFS()が必要です。Sheet2の行10の場合、これは次のようになります。

=IF(ROW()<3,0,SUMIFS(OFFSET(G2,0,0,ROW()-2, 1),OFFSET(A:A,0,0,ROW()-2,1),A10,OFFSET(D:D,0,0,ROW()-2,1),D10))

さて、これでこれらを組み合わせることができます。列全体にドラッグ可能な H2 の場合、式は次のようになります。

=MAX(0,G2+IF(OR(A2="",D2=""),0,IF(ROW()<3,0,SUMIFS(OFFSET(G2,0,0,ROW()-2, 1),OFFSET(A:A,0,0,ROW()-2,1),A2,OFFSET(D:D,0,0,ROW()-2,1),D2))-SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2)))

ただし、場所/アイテムの注文が 3 つあり、最初の注文のみが満たされている場合、3 番目の注文の「期限」数量はその注文と 2 番目の注文数量の合計になります。以前の注文がすべてまだ完全に満たされていない場合、「期限」数量が台無しにならないようにする必要があります。そのため、期限が注文された数量を超えないように、MAX 関数を追加する必要があります。

=MIN(G2,MIN(0,G2+IF(OR(A2="",D2=""),0,IF(ROW()<3,0,SUMIFS(OFFSET(G2,0,0,ROW()-2, 1),OFFSET(A:A,0,0,ROW()-2,1),A2,OFFSET(D:D,0,0,ROW()-2,1),D2))-SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))))

それを H2 に貼り付け、その数式を H の残りの部分にドラッグし、私が行ったことをデバッグすれば (ネタバレですが、私はデバッグしていません)、完了です。VBA は必要ありません。

（警告：注文のない販売は「問題ではない」とおっしゃっています。そのような場合には、後で注文があれば、その注文は即座に処理されます！

2017 年 2 月 3 日更新: MIN と MAX の問題を修正しました。正しくは MAX と MIN でした。

関連情報