Последние несколько часов я потратил на попытки найти что-то похожее на проблему, с которой столкнулся, и теперь у меня из ушей идет дым!
У меня есть два листа, один сзаказывторой спродажи.
Мне автоматически нужна помощьвычитаниеотзаказыосновано напродажи.
Я пытаюсь разработать сценарий, который будет работать при каждой продаже, и еслирасположениеиэлементабсолютно одинаковы, вычтите количество проданных товаров из заказанного количества, чтобы получить «новое подлежащее оплате» количество.
Будут продажи без заказа, что не является проблемой, но было бы неплохо, если бы эти результаты можно было перенести на другую вкладку. Ниже приведено резюме того, что требуется в терминах n00b.
Sheet1являетсяпродал, Sheet2являетсязаказ.
ЕслиSheet1 С2&Ф2такие же, как любая строка в Sheet2(А2,Д2коррелируют поля) вычестьSheet1 Г2, отSheet2 Ф2.
Я добавил пример, который вы можете посмотреть здесьВычесть, если выполнены несколько условий https://app.box.com/s/2m8nfjo8lieh5mfb9wgspy73f1bvzp0e
На вкладке результатов все, что выделено желтым, это то, что изменилось бы. Элементы, не выделенные желтым, не были проданы. Зеленое и красное форматирование было для справки и не требуется, если это сложно. (было бы идеально) столбец H, иллюстрирует, если это вообще возможно, как только количество заказа достигает 0, чтобы скрипт продолжал искать на листе следующий заказ с теми же критериями и вычитал оставшееся количество. Все, что поможет, я сейчас делаю это вручную на выходных и, как правило, 150+ продаж в неделю, и это очень много времени занимает.
Проще говоря, я пытаюсь заставить скрипт пройти по каждой строке продажи и вычесть количество из листа заказа, если завод и номер детали совпадают. Я отсортирую лист заказа на основе того, когда они сделаны, чтобы он удалил из правильного порядка.
Спасибо за любую помощь!
решение1
Я не думаю, что вам нужен VBA.
Посмотрите на SUMIFS()
Мне не разрешено просматривать ваши скриншоты, поскольку обратный прокси-сервер моей организации не разрешает доступ к app.box, но я предполагаю:
- Столбец «C» листа 1 содержит местоположения.
- Столбец «F» Листа 1 содержит элементы.
- Столбец «G» на Листе 1 содержит количество проданного товара.
- Столбец «А» листа 2 содержит местоположения.
- Столбец «D» листа 2 содержит элементы.
- Столбец «G» на Листе 2 содержит заказанное количество.
- Столбец «H» Листа 2 содержит сумму к оплате
На листе 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, а Ordered - Sold будет меньше нуля! Но этого легко избежать...
=MAX(0,G2-IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2)))
Но это не учитывает заказы выше текущей строки! У нас есть MIN(0, ThisOrderQuantity- LocationItemQtySold), когда должно быть MIN(0, ThisOrderQuantity+ PreviouslyOrderedQuantities- LocationItemQtySold) Сумма всех заказов выше текущей строки в Sheet2 составляет
=OFFSET(G2,0,0,ROW()-2, 1)
... ну... это сломается на строке 2. Мы хотим что-то, что будет работать на всех строках, включая первую. Так что давайте защитимся на строке 2.
=IF(ROW()<3,0,OFFSET(G2,0,0,ROW()-2, 1))
но это все предыдущие заказы, когда мы хотим только предыдущие заказы для того же местоположения и элемента. Нам нужна еще одна SUMIFS(). Для строки 10 Листа2 это будет
=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)))
За исключением того, что если у вас есть три заказа на местоположение/товар и только первый из них выполнен, то "должное" количество третьего будет равно этому заказу плюс количество второго заказа! Нам нужно убедиться, что если все предыдущие заказы еще не полностью выполнены, это не испортит наше "должное" количество. Поэтому нам следует добавить функцию 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 не требуется.
(Предупреждение: Вы упомянули «непроблему» продаж без заказов. Пожалуйста, учтите, что в таком случае, если выпозже(Если у вас есть заказ, он будет мгновенно выполнен!)
Обновление 3 февраля 2017 г.: Исправлена проблема с MIN и MAX; должно было быть MAX и MIN.