Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Расчет количества остатков продукта на складах - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Расчет количества остатков продукта на складах
DAUR Дата: Суббота, 25.11.2023, 09:31 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация: 0 ±
Замечаний: 0% ±

Здраствуйте эксперты
Помогите пожалуйста с кодом
Имеются две таблицы ода по приходу продуктов а другая по расходу
Необходимо чтобы в третьей таблице производился расчет по остаткам продуктов на складах
Чтобы код сам циркулировал по строкам и рассчитывал остатки

Заранее благодарю
К сообщению приложен файл: raschet_prod_po_mestam.xlsx (11.6 Kb)
 
Ответить
СообщениеЗдраствуйте эксперты
Помогите пожалуйста с кодом
Имеются две таблицы ода по приходу продуктов а другая по расходу
Необходимо чтобы в третьей таблице производился расчет по остаткам продуктов на складах
Чтобы код сам циркулировал по строкам и рассчитывал остатки

Заранее благодарю

Автор - DAUR
Дата добавления - 25.11.2023 в 09:31
i691198 Дата: Воскресенье, 26.11.2023, 18:09 | Сообщение № 2
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 234
Репутация: 88 ±
Замечаний: 0% ±

Добрый вечер. Извините, но ваш вариант вывода остатков для реальной работы мало пригоден. Если имеется 5 позиций и 5 складов, ещё как то можно сориентироваться, а когда их будет намного больше, то работать с этим будет полная беда для оператора. Я набросал вам пример расчета остатков с распределением по продуктам и складам. Кликните на кнопочку, результат на листе Sheet2.
К сообщению приложен файл: raschet_prod_po_mestam.xlsm (22.7 Kb)
 
Ответить
СообщениеДобрый вечер. Извините, но ваш вариант вывода остатков для реальной работы мало пригоден. Если имеется 5 позиций и 5 складов, ещё как то можно сориентироваться, а когда их будет намного больше, то работать с этим будет полная беда для оператора. Я набросал вам пример расчета остатков с распределением по продуктам и складам. Кликните на кнопочку, результат на листе Sheet2.

Автор - i691198
Дата добавления - 26.11.2023 в 18:09
Gustav Дата: Воскресенье, 26.11.2023, 18:30 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2763
Репутация: 1140 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
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; 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))}))
)
[/vba]
Перед ее вводом в O4 нужно очистить диапазон O4:P8, чтобы формула смогла "развернуться" в нём.

Понимаю, что всё это не совсем VBA, точнее, совсем не VBA, но, может быть, формула в целом подскажет логические шаги при решении этой задачи процедурным способом (т.е. программой на VBA). А то вдруг Вам и Гугл Таблицы понравятся и Вы захотите задержаться в них для решения своей проблемы формульным путем.


МОИ: Ник, 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; 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))}))
)
[/vba]
Перед ее вводом в O4 нужно очистить диапазон O4:P8, чтобы формула смогла "развернуться" в нём.

Понимаю, что всё это не совсем VBA, точнее, совсем не VBA, но, может быть, формула в целом подскажет логические шаги при решении этой задачи процедурным способом (т.е. программой на VBA). А то вдруг Вам и Гугл Таблицы понравятся и Вы захотите задержаться в них для решения своей проблемы формульным путем.

Автор - Gustav
Дата добавления - 26.11.2023 в 18:30
msi2102 Дата: Понедельник, 27.11.2023, 09:56 | Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 414
Репутация: 129 ±
Замечаний: 0% ±

Excel 2007
Кнопочный PQ
[vba]
Код
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
    #"Сортированные строки"
[/vba]
К сообщению приложен файл: 8243205.xlsm (28.7 Kb)


Сообщение отредактировал msi2102 - Понедельник, 27.11.2023, 10:33
 
Ответить
СообщениеКнопочный PQ
[vba]
Код
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
    #"Сортированные строки"
[/vba]

Автор - msi2102
Дата добавления - 27.11.2023 в 09:56
msi2102 Дата: Понедельник, 27.11.2023, 10:25 | Сообщение № 5
Группа: Проверенные
Ранг: Обитатель
Сообщений: 414
Репутация: 129 ±
Замечаний: 0% ±

Excel 2007
Вариант 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("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]
К сообщению приложен файл: 3171528.xlsm (28.7 Kb)


Сообщение отредактировал 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("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]

Автор - msi2102
Дата добавления - 27.11.2023 в 10:25
DAUR Дата: Понедельник, 11.12.2023, 20:45 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация: 0 ±
Замечаний: 0% ±

Всем огромнейшее спасибо
Сработало hands
 
Ответить
СообщениеВсем огромнейшее спасибо
Сработало hands

Автор - DAUR
Дата добавления - 11.12.2023 в 20:45
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!