FIFO с использованием формулы Excel или VBA

avatar
baimzz
8 апреля 2018 в 06:28
2620
2
-1

Вот мой список "IN" - 

PN  Qty Price
A   100 5
B   150 6
C   150 7
D   50  -9
E   100 5
F   5   9
G   20  6
I   5   7
J   15  7
J   30  10
K   100 10
K   50  10
A   20  8

Вот мой список "OUT" - 

PN  Qty
A   120
B   10
C   110
D   60
E   100
J   20
J   10

Ожидаемые результаты -

Ручная формула для расчета цены для PN = "A" = ((100*5)+(20*8))/120

PN  Qty Price   Total
A   120 5.5 660
B   10  6   60
C   110 7   770
D   60  -9  -540
E   100 5   500
J   20  7.75    155
J   10  10  100

Я хочу реализовать логику FIFO для расчета общей цены в списке "OUT" на основе "Количества" в списке "IN".

Источник
QHarr
8 апреля 2018 в 06:36
0

почему J выходит дважды и где F,G,I,K?

ashleedawg
8 апреля 2018 в 06:37
0

Добро пожаловать в Stack Overflow! У вас может быть неправильное представление об этом сайте; Stack Overflow — это место, где профессиональные (или энтузиасты) программисты могут поделиться советом, когда решение проблемы невозможно найти где-либо еще. Лучший С.О. Вопросы содержат часть исходного кода (см. «пример с минимальной воспроизводимостью».), но если ваш вопрос касается конкретной проблемы программирования, алгоритма или программного средства, и – это практичная, требующая ответа проблема, которая является уникальной для разработки программного обеспечения, тогда вы находитесь в правильном месте, чтобы задать свой вопрос! См. раздел «Справочный центр», а также приведенные выше ссылки.

ashleedawg
8 апреля 2018 в 06:37
1

@QHarr - ты слишком добрый :)

Ответы (2)

avatar
DisplayName
8 апреля 2018 в 07:02
0

ваши "Ожидаемые результаты" не соответствуют вашей "Ручной формуле"

после этого последнего

Option Explicit
Sub main()
    Dim cell As Range
    Dim dictSum As Object
    Dim dictRept As Object

    Set dictSum = CreateObject("Scripting.Dictionary")
    Set dictRept = CreateObject("Scripting.Dictionary")
    With Worksheets("IN")
        For Each cell In .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
            dictSum(cell.Value) = dictSum(cell.Value) + cell.Offset(, 1).Value * cell.Offset(, 2).Value
            dictRept(cell.Value) = dictRept(cell.Value) + cell.Offset(, 1).Value
        Next
    End With

    With Worksheets("OUT")
        For Each cell In .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
            cell.Offset(, 2) = dictSum(cell.Value) / dictRept(cell.Value)
            cell.Offset(, 3) = cell.Offset(, 1) * cell.Offset(, 2)
        Next
    End With
End Sub
baimzz
8 апреля 2018 в 09:21
0

Спасибо... за "J", это неправильное совпадение... Для первой J должно быть 7,75, у вас будет 9, у второй J должно быть 10, у вас будет 9

DisplayName
8 апреля 2018 в 09:24
0

и каким будет правило, охватывающее как A (которое суммирует все вхождения A, а затем делит их на количество таких вхождений), так и J?

baimzz
8 апреля 2018 в 09:30
0

Вот как должен работать FIFO, я возьму два примера - 1. Для "A" - количество на выходе равно 120, но если вы проверите на входе, есть несколько строк "A", одна со 100 кол-во по цене 5, и второе 20 штук по цене 8, поэтому моя цена за единицу на выходе будет =((100*5)+(20*8))/120 по цене за единицу....2. Для «J» есть две строки для «J» в OUT , одна с 20 кол-во и вторая с 10, но если вы видите IN, там также у вас есть «J» с двумя строками, одна с кол-во 15 и вторая с кол-во 30, поэтому цена за единицу для первого "J" на выходе будет =((15*7)+(5*10))/20, для второго "J" на выходе она должна быть =((10*10)) /10

DisplayName
8 апреля 2018 в 09:34
0

для вашего первого примера "J": откуда взялось это "5*10" в "((15*7)+(5*10))/20"?

baimzz
8 апреля 2018 в 09:39
0

ВХ 15 7 (15*7) Дж 30 10 (5*10) ВЫХ Дж 20 (15*7 )+ (5*10) Дж 10

DisplayName
8 апреля 2018 в 09:42
0

поскольку "J 15 7 (15*7)" откуда получается "J 30 10 (5*10)" вместо "J 30 10 (30*10) "?

baimzz
8 апреля 2018 в 09:46
0

Не беспокойтесь о «IN», я добавил его для вашего понимания ... Конечный результат должен быть «OUT», где у меня есть 20 первых величин, но в моем «IN» у меня есть только 15 первых по курсу 7, поэтому я получил 15 * 7, затем у меня осталось 5 количеств, которые должны быть основаны на втором «J» с количеством 30 по курсу 10, так что в конце концов у меня осталось 25 количеств для «J»....

DisplayName
8 апреля 2018 в 09:58
0

хорошо, если вы беспокоитесь о какой-либо помощи, то дополните свой вопрос любым примером, чтобы помочь полезным людям помочь вам

baimzz
8 апреля 2018 в 10:00
0

все в порядке, я найду способ... спасибо за код.

avatar
Igelaty
8 апреля 2018 в 06:42
1

enter image description here

Когда у вас есть такие таблицы, исходящая кнопка со следующим кодом:

    Private Sub Outgoing_Click()
Dim pn As String
Dim ammout As Long
Dim current As Long
pn = InputBox("Which Item do you want to take out?")
ammount = InputBox("How Item do you want to take out?")
Dim cells As Long
Dim fifo As Double

counter = 1 //line where your table starts
current = 0
fifo = 0
Do Until IsEmpty(cells(counter, 13).Value Or current = ammount)
    If cells(counter, 13).Value = pn Then
        If cells(counter, 14).Value > (ammount - current) Then
        fifo = fifo + (ammount - current) * cells(current, 15).value
        current = ammount
        Else
        fifo = fifo + cells(counter, 14).Value * cells(counter, 15).Value
        current = current + cells(counter, 14)
        cells(counter, 14).Value = 0
    End If
    counter = counter + 1
Loop
fifo = fifo / ammount
End Sub

Должно работать. Я не сопоставил ложь, если у вас достаточно в вашем текущем списке и других, поэтому часть проверки отсутствует.

baimzz
8 апреля 2018 в 09:32
0

в моей формуле было не 20*6, а 20*8....(100*5)+(20*8))/120 = 5,5

Igelaty
8 апреля 2018 в 09:41
0

Тогда я не понимаю вашу таблицу, FIFO - первый пришел первый вышел, поэтому у вас должен быть прямой порядок и брать "вещи" сверху. Я и другие просто хотим помочь, создание списков текущего порядка, которые вы динамически меняете, когда что-то берете или вынимаете, было бы тактикой. Без вашего кода или информации о том, как вы хотите реализовать свою систему, мы не сможем вам помочь. Сказать, что мы понимаем все ложно, когда у нас недостаточно информации, не поможет вам.

DisplayName
8 апреля 2018 в 10:09
0

приведенный ниже код не работает. это было построено из-за недостаточности базовых данных. кажется, он хорошо работает для всех букв, кроме J, но это просто удачный случай!

Igelaty
8 апреля 2018 в 10:26
0

Отредактировал и добавил код, как я увидел Буквы для разных видов предметов. Надеюсь, это поможет.