Нужно использую этот формат просчитать стоимость реализации, учитывая приходы по разным ценам и выбор запасов по ФИФО, никак не пойму как прописать формулу в моем случае?
Суть такая что при реализации выбираются сначала позиции ранних поступлений(остатков) и ценник там один, затем добираются из следующих поступлений(остатков) с другой стоимостью. То есть стоимость реализации получается составной из разных уровней остатков с разными ценами) Под зеленым диапазоном, я привела пример как составляется стоимость реализации. Необходимо формулы для расчета Стоимость Реал КС,руб (выделено зеленым в примере)
Прошу уделить мне ваше время. Заранее спасибо.
Пример приложила.
Есть определенный формат исходных данных.
Нужно использую этот формат просчитать стоимость реализации, учитывая приходы по разным ценам и выбор запасов по ФИФО, никак не пойму как прописать формулу в моем случае?
Суть такая что при реализации выбираются сначала позиции ранних поступлений(остатков) и ценник там один, затем добираются из следующих поступлений(остатков) с другой стоимостью. То есть стоимость реализации получается составной из разных уровней остатков с разными ценами) Под зеленым диапазоном, я привела пример как составляется стоимость реализации. Необходимо формулы для расчета Стоимость Реал КС,руб (выделено зеленым в примере)
Diana991, Думаю, что нужно более детально разжевать всю задачу. И лучше, разбить её на несколько подзадач. Я посмотрел на Ваши условия и на то что в файле, и честно говоря, ничего не понял)))
Diana991, Думаю, что нужно более детально разжевать всю задачу. И лучше, разбить её на несколько подзадач. Я посмотрел на Ваши условия и на то что в файле, и честно говоря, ничего не понял)))Roman777
Option Base 1 Function Raschet(Приход As Range, Расход As Range, Цены As Range, Остатки As Range, НомерРасчета As Integer) As Variant If Приход.Rows.Count <> Расход.Rows.Count Or Приход.Rows.Count <> Цены.Rows.Count Then Raschet = "Выбранные диапазоны не соответсвуют друг другу": Exit Function If Приход.Columns.Count <> Расход.Columns.Count Or Приход.Columns.Count <> Цены.Columns.Count Then Raschet = "Выбранные диапазоны не соответсвуют друг другу": Exit Function Dim k, dat, nPost, endItem, PrTmp, RsTmp, arrPr(), arrRs(), arrCash(), matrix(), arrOst(), Sum, Raz, Kor, REZ, SumS Raschet = 0 endItem = Приход.Columns.Count arrPr() = Приход.Value arrRs() = Расход.Value arrOst() = Остатки.Value arrCash() = Цены.Value ReDim matrix(2, НомерРасчета, НомерРасчета) For k = 1 To НомерРасчета PrTmp = PrTmp + arrPr(1, k) RsTmp = RsTmp + arrRs(1, k) Next k If RsTmp > PrTmp Then Raschet = "расход превышает приход": Exit Function If НомерРасчета = 1 Then Raschet = arrRs(1, 1) * arrCash(1, 1): Exit Function matrix(1, 1, 1) = arrOst(1, 1) matrix(2, 1, 1) = (arrPr(1, 1) - matrix(1, 1, 1)) * arrCash(1, 1) For dat = 2 To НомерРасчета Raz = 0 For k = dat To 1 Step -1: Raz = Raz + arrRs(1, k): Next k For nPost = 1 To НомерРасчета Sum = 0 For k = nPost To 1 Step -1: Sum = Sum + arrPr(1, k): Next k If nPost > 1 Then Kor = 0 For k = nPost - 1 To 1 Step -1: Kor = Kor + matrix(1, dat, k): Next k Else: Kor = 0 End If REZ = Sum - Kor - Raz If REZ > 0 Then matrix(1, dat, nPost) = REZ Else matrix(1, dat, nPost) = 0 If nPost >= dat Then Exit For Next nPost Next dat For dat = 2 To НомерРасчета For nPost = 1 To НомерРасчета matrix(2, dat, nPost) = (matrix(1, dat - 1, nPost) - matrix(1, dat, nPost)) * arrCash(1, nPost) If matrix(2, dat, nPost) < 0 Then matrix(2, dat, nPost) = 0 If nPost >= dat Then Exit For Next nPost Next dat For nPost = 1 To НомерРасчета Raschet = Raschet + matrix(2, НомерРасчета, nPost) Next nPost End Function
[/vba]
[offtop]но данные всеже лучше систематизировать в нормальный табличный вид [/offtop]
вот собрал UDF
[vba]
Код
Option Base 1 Function Raschet(Приход As Range, Расход As Range, Цены As Range, Остатки As Range, НомерРасчета As Integer) As Variant If Приход.Rows.Count <> Расход.Rows.Count Or Приход.Rows.Count <> Цены.Rows.Count Then Raschet = "Выбранные диапазоны не соответсвуют друг другу": Exit Function If Приход.Columns.Count <> Расход.Columns.Count Or Приход.Columns.Count <> Цены.Columns.Count Then Raschet = "Выбранные диапазоны не соответсвуют друг другу": Exit Function Dim k, dat, nPost, endItem, PrTmp, RsTmp, arrPr(), arrRs(), arrCash(), matrix(), arrOst(), Sum, Raz, Kor, REZ, SumS Raschet = 0 endItem = Приход.Columns.Count arrPr() = Приход.Value arrRs() = Расход.Value arrOst() = Остатки.Value arrCash() = Цены.Value ReDim matrix(2, НомерРасчета, НомерРасчета) For k = 1 To НомерРасчета PrTmp = PrTmp + arrPr(1, k) RsTmp = RsTmp + arrRs(1, k) Next k If RsTmp > PrTmp Then Raschet = "расход превышает приход": Exit Function If НомерРасчета = 1 Then Raschet = arrRs(1, 1) * arrCash(1, 1): Exit Function matrix(1, 1, 1) = arrOst(1, 1) matrix(2, 1, 1) = (arrPr(1, 1) - matrix(1, 1, 1)) * arrCash(1, 1) For dat = 2 To НомерРасчета Raz = 0 For k = dat To 1 Step -1: Raz = Raz + arrRs(1, k): Next k For nPost = 1 To НомерРасчета Sum = 0 For k = nPost To 1 Step -1: Sum = Sum + arrPr(1, k): Next k If nPost > 1 Then Kor = 0 For k = nPost - 1 To 1 Step -1: Kor = Kor + matrix(1, dat, k): Next k Else: Kor = 0 End If REZ = Sum - Kor - Raz If REZ > 0 Then matrix(1, dat, nPost) = REZ Else matrix(1, dat, nPost) = 0 If nPost >= dat Then Exit For Next nPost Next dat For dat = 2 To НомерРасчета For nPost = 1 To НомерРасчета matrix(2, dat, nPost) = (matrix(1, dat - 1, nPost) - matrix(1, dat, nPost)) * arrCash(1, nPost) If matrix(2, dat, nPost) < 0 Then matrix(2, dat, nPost) = 0 If nPost >= dat Then Exit For Next nPost Next dat For nPost = 1 To НомерРасчета Raschet = Raschet + matrix(2, НомерРасчета, nPost) Next nPost End Function
[/vba]
[offtop]но данные всеже лучше систематизировать в нормальный табличный вид [/offtop]K-SerJC