Расчет количества остатков продукта на складах
DAUR
Дата: Суббота, 25.11.2023, 09:31 |
Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 53
Репутация:
0
±
Замечаний:
0% ±
Здраствуйте эксперты Помогите пожалуйста с кодом Имеются две таблицы ода по приходу продуктов а другая по расходу Необходимо чтобы в третьей таблице производился расчет по остаткам продуктов на складах Чтобы код сам циркулировал по строкам и рассчитывал остатки Заранее благодарю
Здраствуйте эксперты Помогите пожалуйста с кодом Имеются две таблицы ода по приходу продуктов а другая по расходу Необходимо чтобы в третьей таблице производился расчет по остаткам продуктов на складах Чтобы код сам циркулировал по строкам и рассчитывал остатки Заранее благодарю DAUR
Ответить
Сообщение Здраствуйте эксперты Помогите пожалуйста с кодом Имеются две таблицы ода по приходу продуктов а другая по расходу Необходимо чтобы в третьей таблице производился расчет по остаткам продуктов на складах Чтобы код сам циркулировал по строкам и рассчитывал остатки Заранее благодарю Автор - DAUR Дата добавления - 25.11.2023 в 09:31
i691198
Дата: Воскресенье, 26.11.2023, 18:09 |
Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 423
Репутация:
131
±
Замечаний:
0% ±
2016
Добрый вечер. Извините, но ваш вариант вывода остатков для реальной работы мало пригоден. Если имеется 5 позиций и 5 складов, ещё как то можно сориентироваться, а когда их будет намного больше, то работать с этим будет полная беда для оператора. Я набросал вам пример расчета остатков с распределением по продуктам и складам. Кликните на кнопочку, результат на листе Sheet2.
Добрый вечер. Извините, но ваш вариант вывода остатков для реальной работы мало пригоден. Если имеется 5 позиций и 5 складов, ещё как то можно сориентироваться, а когда их будет намного больше, то работать с этим будет полная беда для оператора. Я набросал вам пример расчета остатков с распределением по продуктам и складам. Кликните на кнопочку, результат на листе Sheet2. i691198
Ответить
Сообщение Добрый вечер. Извините, но ваш вариант вывода остатков для реальной работы мало пригоден. Если имеется 5 позиций и 5 складов, ещё как то можно сориентироваться, а когда их будет намного больше, то работать с этим будет полная беда для оператора. Я набросал вам пример расчета остатков с распределением по продуктам и складам. Кликните на кнопочку, результат на листе Sheet2. Автор - i691198 Дата добавления - 26.11.2023 в 18:09
Gustav
Дата: Воскресенье, 26.11.2023, 18:30 |
Сообщение № 3
Группа: Админы
Ранг: Участник клуба
Сообщений: 2843
Репутация:
1194
±
Замечаний:
±
начинал с Excel 4.0, видел 2.1
DAUR , использовал Вашу задачу в качестве разминки в Таблицах Google. Создал формулу для ячейки O4. После ввода ее в эту ячейку в Таблице Google в диапазоне O4:P8 возникнет ровно картина Вашего примера:
Prod1 2 x WH1
Prod2 2 x WH1, 59 x WH4
Prod3 54 x WH1
Prod6 20 x WH3, 8 x WH5
Prod8 21 x WH2
А формула для О4 такая:
=LET(
inTab; FILTER({C4:C\E4:E\F4:F}; C4:C<>"" );
outTab; FILTER({I4:I\-K4:K\L4:L}; I4:I<>"" );
tab; {inTab; outTab};
prodloc; SORT(UNIQUE({INDEX(tab;0 ;1 ) \ INDEX(tab;0 ;3 )}));
sumQty; MAP(INDEX(prodloc;0 ;1 ); INDEX(prodloc;0 ;2 ); LAMBDA(prod;loc; SUM(FILTER(INDEX(tab;0 ;2 ); INDEX(tab;0 ;1 )=prod; INDEX(tab;0 ;3 )=loc))));
group1; MAP(INDEX(prodloc;0 ;1 ); INDEX(prodloc;0 ;2 ); sumQty; LAMBDA(pr;lo;qt; {pr \ qt & " x " & lo}));
MAP(UNIQUE(INDEX(group1;0 ;1 )); LAMBDA(pr;{pr \ JOIN(", " ;FILTER(INDEX(group1;0 ;2 ); INDEX(group1;0 ;1 )=pr))}))
)
Перед ее вводом в O4 нужно очистить диапазон O4:P8, чтобы формула смогла "развернуться" в нём. Понимаю, что всё это не совсем VBA, точнее, совсем не VBA, но, может быть, формула в целом подскажет логические шаги при решении этой задачи процедурным способом (т.е. программой на VBA). А то вдруг Вам и Гугл Таблицы понравятся и Вы захотите задержаться в них для решения своей проблемы формульным путем.
DAUR , использовал Вашу задачу в качестве разминки в Таблицах Google. Создал формулу для ячейки O4. После ввода ее в эту ячейку в Таблице Google в диапазоне O4:P8 возникнет ровно картина Вашего примера:
Prod1 2 x WH1
Prod2 2 x WH1, 59 x WH4
Prod3 54 x WH1
Prod6 20 x WH3, 8 x WH5
Prod8 21 x WH2
А формула для О4 такая:
=LET(
inTab; FILTER({C4:C\E4:E\F4:F}; C4:C<>"" );
outTab; FILTER({I4:I\-K4:K\L4:L}; I4:I<>"" );
tab; {inTab; outTab};
prodloc; SORT(UNIQUE({INDEX(tab;0 ;1 ) \ INDEX(tab;0 ;3 )}));
sumQty; MAP(INDEX(prodloc;0 ;1 ); INDEX(prodloc;0 ;2 ); LAMBDA(prod;loc; SUM(FILTER(INDEX(tab;0 ;2 ); INDEX(tab;0 ;1 )=prod; INDEX(tab;0 ;3 )=loc))));
group1; MAP(INDEX(prodloc;0 ;1 ); INDEX(prodloc;0 ;2 ); sumQty; LAMBDA(pr;lo;qt; {pr \ qt & " x " & lo}));
MAP(UNIQUE(INDEX(group1;0 ;1 )); LAMBDA(pr;{pr \ JOIN(", " ;FILTER(INDEX(group1;0 ;2 ); INDEX(group1;0 ;1 )=pr))}))
)
Перед ее вводом в O4 нужно очистить диапазон O4:P8, чтобы формула смогла "развернуться" в нём. Понимаю, что всё это не совсем VBA, точнее, совсем не VBA, но, может быть, формула в целом подскажет логические шаги при решении этой задачи процедурным способом (т.е. программой на VBA). А то вдруг Вам и Гугл Таблицы понравятся и Вы захотите задержаться в них для решения своей проблемы формульным путем.Gustav
МОИ: Ник , Tip box: 41001663842605
Ответить
Сообщение DAUR , использовал Вашу задачу в качестве разминки в Таблицах Google. Создал формулу для ячейки O4. После ввода ее в эту ячейку в Таблице Google в диапазоне O4:P8 возникнет ровно картина Вашего примера: [vba]
Prod1 2 x WH1 Prod2 2 x WH1 , 59 x WH4 Prod3 54 x WH1 Prod6 20 x WH3 , 8 x WH5 Prod8 21 x WH2
[/vba] А формула для О4 такая: [vba]
=LET(inTab ; ФИЛЬТР({C4:C \E4 :E \F4 :F }; C4:C <>"");outTab ; ФИЛЬТР({I4 :I \-K4 :K \L4 :L }; I4 :I <>""); tab ; {inTab ; outTab };prodloc ; СОРТ(УНИК({ИНДЕКС(tab ;0;1) \ ИНДЕКС(tab ;0;3)}));sumQty ; MAP(ИНДЕКС(prodloc ;0;1); ИНДЕКС(prodloc ;0;2); LAMBDA(prod ;loc ; СУММ(ФИЛЬТР(ИНДЕКС(tab ;0;2); ИНДЕКС(tab ;0;1)=prod ; ИНДЕКС(tab ;0;3)=loc ))));group1 ; MAP(ИНДЕКС(prodloc ;0;1); ИНДЕКС(prodloc ;0;2); sumQty ; LAMBDA(pr ;lo ;qt ; {pr \ qt & " x " & lo }));MAP(УНИК(ИНДЕКС(group1 ;0;1)); LAMBDA(pr ;{pr \ JOIN(", ";ФИЛЬТР(ИНДЕКС(group1 ;0;2); ИНДЕКС(group1 ;0;1)=pr ))})))
[/vba] Перед ее вводом в O4 нужно очистить диапазон O4:P8, чтобы формула смогла "развернуться" в нём. Понимаю, что всё это не совсем VBA, точнее, совсем не VBA, но, может быть, формула в целом подскажет логические шаги при решении этой задачи процедурным способом (т.е. программой на VBA). А то вдруг Вам и Гугл Таблицы понравятся и Вы захотите задержаться в них для решения своей проблемы формульным путем.Автор - Gustav Дата добавления - 26.11.2023 в 18:30
msi2102
Дата: Понедельник, 27.11.2023, 09:56 |
Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 430
Репутация:
131
±
Замечаний:
0% ±
Excel 2019
Кнопочный PQ
let
Источник = Excel.CurrentWorkbook(){[Name="Table1"] }[Content] ,
#"Измененный тип" = Table.TransformColumnTypes(Источник,{{"QTY" , type number}}),
#"Сгруппированные строки1" = Table.Group(#"Измененный тип" , {"Prod" , "Location" }, {{"Сумма" , each List.Sum([QTY] ), type nullable number}}),
Источник1 = Excel.CurrentWorkbook(){[Name="Table2"] }[Content] ,
Измененный_тип1 = Table.TransformColumnTypes(Источник1,{{"QTY" , type number}}),
Сгруппированные_строки2 = Table.Group(Измененный_тип1, {"Prod" , "Location" }, {{"Сумма" , each List.Sum([QTY] ), type nullable number}}),
#"Объединенные запросы" = Table.NestedJoin(#"Сгруппированные строки1" , {"Prod" , "Location" }, Сгруппированные_строки2, {"Prod" , "Location" }, "Сгруппированные_строки2" , JoinKind.LeftOuter),
#"Развернутый элемент" = Table.ExpandTableColumn(#"Объединенные запросы" , "Сгруппированные_строки2" , {"Сумма" }, {"Сумма.1" }),
#"Замененное значение1" = Table.ReplaceValue(#"Развернутый элемент" ,null,0 ,Replacer.ReplaceValue,{"Сумма" , "Сумма.1" }),
#"Добавлен пользовательский объект" = Table.AddColumn(#"Замененное значение1" , "Остатки" , each [Сумма] -[Сумма.1] ),
#"Сгруппированные строки" = Table.Group(#"Добавлен пользовательский объект" , {"Prod" , "Location" }, {{"Остаток" , each List.Sum([Остатки] ), type number}}),
#"Сортированные строки" = Table.Sort(#"Сгруппированные строки" ,{{"Prod" , Order.Ascending}})
in
#"Сортированные строки"
Кнопочный PQ
let
Источник = Excel.CurrentWorkbook(){[Name="Table1"] }[Content] ,
#"Измененный тип" = Table.TransformColumnTypes(Источник,{{"QTY" , type number}}),
#"Сгруппированные строки1" = Table.Group(#"Измененный тип" , {"Prod" , "Location" }, {{"Сумма" , each List.Sum([QTY] ), type nullable number}}),
Источник1 = Excel.CurrentWorkbook(){[Name="Table2"] }[Content] ,
Измененный_тип1 = Table.TransformColumnTypes(Источник1,{{"QTY" , type number}}),
Сгруппированные_строки2 = Table.Group(Измененный_тип1, {"Prod" , "Location" }, {{"Сумма" , each List.Sum([QTY] ), type nullable number}}),
#"Объединенные запросы" = Table.NestedJoin(#"Сгруппированные строки1" , {"Prod" , "Location" }, Сгруппированные_строки2, {"Prod" , "Location" }, "Сгруппированные_строки2" , JoinKind.LeftOuter),
#"Развернутый элемент" = Table.ExpandTableColumn(#"Объединенные запросы" , "Сгруппированные_строки2" , {"Сумма" }, {"Сумма.1" }),
#"Замененное значение1" = Table.ReplaceValue(#"Развернутый элемент" ,null,0 ,Replacer.ReplaceValue,{"Сумма" , "Сумма.1" }),
#"Добавлен пользовательский объект" = Table.AddColumn(#"Замененное значение1" , "Остатки" , each [Сумма] -[Сумма.1] ),
#"Сгруппированные строки" = Table.Group(#"Добавлен пользовательский объект" , {"Prod" , "Location" }, {{"Остаток" , each List.Sum([Остатки] ), type number}}),
#"Сортированные строки" = Table.Sort(#"Сгруппированные строки" ,{{"Prod" , Order.Ascending}})
in
#"Сортированные строки"
msi2102
Сообщение отредактировал msi2102 - Понедельник, 27.11.2023, 10:33
Ответить
Сообщение Кнопочный PQ [vba]
let Источник = Excel.CurrentWorkbook(){[Name ="Тable1"]}[Content ]; Источник "Измененный тип" = Тable.TransformColumnТypes(type ;{{"QTY"; number each }}); QTY "Сгруппированные строки1" = Тable.Group(type "Измененный тип"; {"Prod"; "Location"}; {{"Сумма"; nullable List.Sum([number ]); Источник1 Name Content }}); Измененный_тип1 = Excel.CurrentWorkbook(){[Источник1 ="Тable2"]}[type ]; number = Тable.TransformColumnТypes(Сгруппированные_строки2 ;{{"QTY"; Измененный_тип1 each }}); QTY = Тable.Group(type ; {"Prod"; "Location"}; {{"Сумма"; nullable List.Sum([number ]); Сгруппированные_строки2 JoinKind.LeftOuter null }}); Replacer.ReplaceValue "Объединенные запросы" = Тable.NestedJoin(each "Сгруппированные строки1"; {"Prod"; "Location"}; Сумма ; {"Prod"; "Location"}; "Сгруппированные_строки2"; Сумма.1 ); each "Развернутый элемент" = Тable.ExpandТableColumn(Остатки "Объединенные запросы"; "Сгруппированные_строки2"; {"Сумма"}; {"Сумма.1"}); type "Замененное значение1" = Тable.ReplaceValue(number "Развернутый элемент";Order.Ascending ;0;in ;{"Сумма"; "Сумма.1"}); undefined"Добавлен пользовательский объект" = Тable.AddColumn(undefined"Замененное значение1"; "Остатки"; undefined [undefined]-[undefined]); undefined"Сгруппированные строки" = Тable.Group(undefined"Добавлен пользовательский объект"; {"Prod"; "Location"}; {{"Остаток"; undefined List.Sum([undefined]); undefined undefined}}); undefined"Сортированные строки" = Тable.Sort(undefined"Сгруппированные строки";{{"Prod"; undefined}})undefined undefined"Сортированные строки"
[/vba] Автор - msi2102 Дата добавления - 27.11.2023 в 09:56
msi2102
Дата: Понедельник, 27.11.2023, 10:25 |
Сообщение № 5
Группа: Проверенные
Ранг: Обитатель
Сообщений: 430
Репутация:
131
±
Замечаний:
0% ±
Excel 2019
Вариант VBA
Sub Spr()
Dim Dic As Object , arr1, arr2, arr3, y, n As Long , m As Long
Set Dic = CreateObject ("Scripting.Dictionary" )
m = 0
arr1 = ActiveSheet.ListObjects("Table1" ).DataBodyRange
For n = 1 To UBound (arr1)
If Not Dic.Exists(arr1(n, 1 ) & "|" & arr1(n, 4 )) Then
Dic.Add arr1(n, 1 ) & "|" & arr1(n, 4 ), arr1(n, 3 )
m = m + 1
Else
Dic(arr1(n, 1 ) & "|" & arr1(n, 4 )) = Dic(arr1(n, 1 ) & "|" & arr1(n, 4 )) + arr1(n, 3 )
End If
Next
arr2 = ActiveSheet.ListObjects("Table2" ).DataBodyRange
For n = 1 To UBound (arr2)
If Dic.Exists(arr2(n, 1 ) & "|" & arr2(n, 4 )) Then
Dic(arr2(n, 1 ) & "|" & arr2(n, 4 )) = Dic(arr2(n, 1 ) & "|" & arr2(n, 4 )) - arr2(n, 3 )
Else
Dic.Add arr2(n, 1 ) & "|" & arr2(n, 4 ), -arr2(n, 3 )
m = m + 1
End If
Next
ReDim arr3(1 To m, 1 To 3 )
n = 1
For Each y In Dic
arr3(n, 1 ) = Split (y, "|" )(0 )
arr3(n, 2 ) = Split (y, "|" )(1 )
arr3(n, 3 ) = Dic(y)
n = n + 1
Next
With Sheets("Sheet2" )
.Cells.Clear
.Range("B2" ).Resize(UBound (arr3), UBound (arr3, 2 )) = arr3
End With
End Sub
Вариант VBA
Sub Spr()
Dim Dic As Object , arr1, arr2, arr3, y, n As Long , m As Long
Set Dic = CreateObject ("Scripting.Dictionary" )
m = 0
arr1 = ActiveSheet.ListObjects("Table1" ).DataBodyRange
For n = 1 To UBound (arr1)
If Not Dic.Exists(arr1(n, 1 ) & "|" & arr1(n, 4 )) Then
Dic.Add arr1(n, 1 ) & "|" & arr1(n, 4 ), arr1(n, 3 )
m = m + 1
Else
Dic(arr1(n, 1 ) & "|" & arr1(n, 4 )) = Dic(arr1(n, 1 ) & "|" & arr1(n, 4 )) + arr1(n, 3 )
End If
Next
arr2 = ActiveSheet.ListObjects("Table2" ).DataBodyRange
For n = 1 To UBound (arr2)
If Dic.Exists(arr2(n, 1 ) & "|" & arr2(n, 4 )) Then
Dic(arr2(n, 1 ) & "|" & arr2(n, 4 )) = Dic(arr2(n, 1 ) & "|" & arr2(n, 4 )) - arr2(n, 3 )
Else
Dic.Add arr2(n, 1 ) & "|" & arr2(n, 4 ), -arr2(n, 3 )
m = m + 1
End If
Next
ReDim arr3(1 To m, 1 To 3 )
n = 1
For Each y In Dic
arr3(n, 1 ) = Split (y, "|" )(0 )
arr3(n, 2 ) = Split (y, "|" )(1 )
arr3(n, 3 ) = Dic(y)
n = n + 1
Next
With Sheets("Sheet2" )
.Cells.Clear
.Range("B2" ).Resize(UBound (arr3), UBound (arr3, 2 )) = arr3
End With
End Sub
msi2102
Сообщение отредактировал msi2102 - Понедельник, 27.11.2023, 10:33
Ответить
Сообщение Вариант VBA [vba]
Sub Spr() Dim Dic As Object ; arr1 ; arr2 ; arr3 ; y ; n As Long ; m As Long Set Dic = CreateObject("Scripting.Dictionary") m = 0 arr1 = ActiveSheet.ListObjects("Тable1").DataBodyRange For n = 1 To UBound(arr1 ) If Not Dic.Exists(arr1 (n ; 1) & "|" & arr1 (n ; 4)) Then Dic.Add arr1 (n ; 1) & "|" & arr1 (n ; 4); arr1 (n ; 3) m = m + 1 Else Dic(arr1 (n ; 1) & "|" & arr1 (n ; 4)) = Dic(arr1 (n ; 1) & "|" & arr1 (n ; 4)) + arr1 (n ; 3) End If Next arr2 = ActiveSheet.ListObjects("Тable2").DataBodyRange For n = 1 To UBound(arr2 ) If Dic.Exists(arr2 (n ; 1) & "|" & arr2 (n ; 4)) Then Dic(arr2 (n ; 1) & "|" & arr2 (n ; 4)) = Dic(arr2 (n ; 1) & "|" & arr2 (n ; 4)) - arr2 (n ; 3) Else Dic.Add arr2 (n ; 1) & "|" & arr2 (n ; 4); -arr2 (n ; 3) m = m + 1 End If Next ReDim arr3 (1 To m ; 1 To 3) n = 1 For Each y In Dic arr3 (n ; 1) = Split(y ; "|")(0) arr3 (n ; 2) = Split(y ; "|")(1) arr3 (n ; 3) = Dic(y ) n = n + 1 Next With Sheets("Sheet2") .Cells.Clear .Range("B2").Resize(UBound(arr3 ); UBound(arr3 ; 2)) = arr3 End WithEnd Sub
[/vba] Автор - msi2102 Дата добавления - 27.11.2023 в 10:25
DAUR
Дата: Понедельник, 11.12.2023, 20:45 |
Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 53
Репутация:
0
±
Замечаний:
0% ±
Всем огромнейшее спасибо Сработало
Всем огромнейшее спасибо Сработало DAUR
Ответить
Сообщение Всем огромнейшее спасибо Сработало Автор - DAUR Дата добавления - 11.12.2023 в 20:45