Доброго дня! Писать коды не мастак, нарыл подобного алгорима код хотел прикрутить к своему файлу но пока не срослось! Буду конечно дальше ковырять, но и так же не буду против если человек грамотный облегчит мои мучения. Примерный алгоритм работы (как я на данном этапе моих познаний excel его себе представляю) написан внутри файла. P.S.Загвоздка пока на этапе как обратится к данным на листе Данные. Копирование пока не исправлял.
Доброго дня! Писать коды не мастак, нарыл подобного алгорима код хотел прикрутить к своему файлу но пока не срослось! Буду конечно дальше ковырять, но и так же не буду против если человек грамотный облегчит мои мучения. Примерный алгоритм работы (как я на данном этапе моих познаний excel его себе представляю) написан внутри файла. P.S.Загвоздка пока на этапе как обратится к данным на листе Данные. Копирование пока не исправлял.ZamoK
ZamoK, не ясно как будет выполняться пункт "7. Затем заполняется лист Материал". отдельный макрос или ручками? И во всех листах есть поле "количество деталей" оно как должно считаться?
ZamoK, не ясно как будет выполняться пункт "7. Затем заполняется лист Материал". отдельный макрос или ручками? И во всех листах есть поле "количество деталей" оно как должно считаться?Roman777
Много чего не знаю!!!!
Сообщение отредактировал Roman777 - Пятница, 25.09.2015, 13:42
Option Explicit Option Compare Text Sub Razbienie() Dim i As Long, i_n As Long, i2 As Long, i2_n As Long, i3_n As Long, k As Long, k1 As Long Dim rng As Range Dim Detal() As String Dim nam As String Dim kolvo() As Long Dim material As Object Dim key As String Dim norm As String Set material = CreateObject("Scripting.Dictionary") i_n = Cells(Rows.Count, 2).End(xlUp).Row ReDim Detal(i_n) ReDim kolvo(Worksheets.Count) For i = 3 To i_n Detal(i) = Cells(i, 2) Next i For i = 3 To i_n Set rng = Worksheets("Данные").Cells.Find(Detal(i), , xlValues, xlPart) i2 = rng.Row Select Case Worksheets("Данные").Cells(i2, 3) Case "Сборка": nam = "Сборка" Case "Покупные": nam = "Покупные" Case "Кооперация": nam = "Покупные" Case "Крепеж": nam = "Крепёж" Case Else nam = "Механический" End Select i2_n = Worksheets(nam).Cells(Rows.Count, 1).End(xlUp).Row i3_n = Worksheets("Материал").Cells(Rows.Count, 1).End(xlUp).Row Worksheets("Итог").Cells(i, 2).Resize(, 3).Copy Worksheets(nam).Cells(i2_n + 1, 2) kolvo(Worksheets(nam).Index) = kolvo(Worksheets(nam).Index) + 1 Worksheets(nam).Cells(i2_n + 1, 1) = kolvo(Worksheets(nam).Index) key = Worksheets("Данные").Cells(i2, 3) norm = Worksheets("Данные").Cells(i2, 5) If Not material.Exists(key) Then material.Add key, norm k = k + 1 Worksheets("Материал").Cells(i3_n + 1, 1) = k Worksheets("Материал").Cells(i3_n + 1, 2) = key Worksheets("Материал").Cells(i3_n + 1, 3) = norm End If If nam = "Механический" Then Worksheets(nam).Cells(i2_n + 1, 5) = norm Worksheets(nam).Cells(i2_n + 1, 6) = norm * Worksheets(nam).Cells(i2_n + 1, 4).Value End If Next i End Sub
[/vba]
Вообще для заполнения необходимого кол-ва материала, нет нужды вносить лист материал и туда закидывать данные. В макросе это делается единым циклом всё...
ZamoK,
[vba]
Код
Option Explicit Option Compare Text Sub Razbienie() Dim i As Long, i_n As Long, i2 As Long, i2_n As Long, i3_n As Long, k As Long, k1 As Long Dim rng As Range Dim Detal() As String Dim nam As String Dim kolvo() As Long Dim material As Object Dim key As String Dim norm As String Set material = CreateObject("Scripting.Dictionary") i_n = Cells(Rows.Count, 2).End(xlUp).Row ReDim Detal(i_n) ReDim kolvo(Worksheets.Count) For i = 3 To i_n Detal(i) = Cells(i, 2) Next i For i = 3 To i_n Set rng = Worksheets("Данные").Cells.Find(Detal(i), , xlValues, xlPart) i2 = rng.Row Select Case Worksheets("Данные").Cells(i2, 3) Case "Сборка": nam = "Сборка" Case "Покупные": nam = "Покупные" Case "Кооперация": nam = "Покупные" Case "Крепеж": nam = "Крепёж" Case Else nam = "Механический" End Select i2_n = Worksheets(nam).Cells(Rows.Count, 1).End(xlUp).Row i3_n = Worksheets("Материал").Cells(Rows.Count, 1).End(xlUp).Row Worksheets("Итог").Cells(i, 2).Resize(, 3).Copy Worksheets(nam).Cells(i2_n + 1, 2) kolvo(Worksheets(nam).Index) = kolvo(Worksheets(nam).Index) + 1 Worksheets(nam).Cells(i2_n + 1, 1) = kolvo(Worksheets(nam).Index) key = Worksheets("Данные").Cells(i2, 3) norm = Worksheets("Данные").Cells(i2, 5) If Not material.Exists(key) Then material.Add key, norm k = k + 1 Worksheets("Материал").Cells(i3_n + 1, 1) = k Worksheets("Материал").Cells(i3_n + 1, 2) = key Worksheets("Материал").Cells(i3_n + 1, 3) = norm End If If nam = "Механический" Then Worksheets(nam).Cells(i2_n + 1, 5) = norm Worksheets(nam).Cells(i2_n + 1, 6) = norm * Worksheets(nam).Cells(i2_n + 1, 4).Value End If Next i End Sub
[/vba]
Вообще для заполнения необходимого кол-ва материала, нет нужды вносить лист материал и туда закидывать данные. В макросе это делается единым циклом всё...Roman777
И во всех листах есть поле "количество деталей" оно как должно считаться?
Кол-во деталей на всех листах кроме материала, просто копируется с листа Итог, на листе материал, как вы уже догадались нужно умножить на кол-во деталей, вот только вы это сделали не на том листе и добавить бы ссумирование строчек с однотипным материалом. И ещё что-то с листом материал не то!!! Лист материал заполняется с листа Механический - там 21 позиция (одну я добавил на листе Итог с одинаковым материалом Круг 8) на листе Материал 16 позиций т.е. не все и ссумированных там тоже нет., как бы тут немного поковырять нада.
туда закидывать данные. В макросе это делается единым циклом
не этого делать нет нужды т.к. там форма определена, только я ошибся когда форму делал столбец Детали и наименование нада местами поменять и отсортировать по Наименованию. А в остальном очень даже неплохо. Да можно ли исключить позиции на листе материал, которые =0, и при повторном нажатии на кнопку добавить бы очистку листов от заполненных данных, если не сложно
И во всех листах есть поле "количество деталей" оно как должно считаться?
Кол-во деталей на всех листах кроме материала, просто копируется с листа Итог, на листе материал, как вы уже догадались нужно умножить на кол-во деталей, вот только вы это сделали не на том листе и добавить бы ссумирование строчек с однотипным материалом. И ещё что-то с листом материал не то!!! Лист материал заполняется с листа Механический - там 21 позиция (одну я добавил на листе Итог с одинаковым материалом Круг 8) на листе Материал 16 позиций т.е. не все и ссумированных там тоже нет., как бы тут немного поковырять нада.
туда закидывать данные. В макросе это делается единым циклом
не этого делать нет нужды т.к. там форма определена, только я ошибся когда форму делал столбец Детали и наименование нада местами поменять и отсортировать по Наименованию. А в остальном очень даже неплохо. Да можно ли исключить позиции на листе материал, которые =0, и при повторном нажатии на кнопку добавить бы очистку листов от заполненных данных, если не сложноZamoK
ZamoK, Добрый день. Сразу отмечу. Макрос записывает материал из листа "данные" в лист "Материал" только тот, который используется для деталей с листа "Итог". Если у Вас на листе Итог не будут деталей, которые изготавливаются из какого-либо материала, указанного в листе Данные, то данный материал отображаться не будет (это такой получается так сказать "ком" при выполнении данного макроса за единый цикл). Не понял я что значит
Детали и наименование нада местами поменять и отсортировать по Наименованию
это на каком листе? Если будет время, поковыряюсь ещё...)
ZamoK, Добрый день. Сразу отмечу. Макрос записывает материал из листа "данные" в лист "Материал" только тот, который используется для деталей с листа "Итог". Если у Вас на листе Итог не будут деталей, которые изготавливаются из какого-либо материала, указанного в листе Данные, то данный материал отображаться не будет (это такой получается так сказать "ком" при выполнении данного макроса за единый цикл). Не понял я что значит
На листе Материал нужно взять норму расхода и умножить на потребность детали + норма расхода*потребность детали однотипного материала. Код сложный поэтому я и думаю надо вынести на отдельную кнопку.
Работа творческая время здесь не аргумент, да впрочем и основная работа уже сделана, осталось чуть чуть допилить, может и сам допилю если никто не обгонит, хотя я думаю не успею
На листе Материал нужно взять норму расхода и умножить на потребность детали + норма расхода*потребность детали однотипного материала. Код сложный поэтому я и думаю надо вынести на отдельную кнопку.
Работа творческая время здесь не аргумент, да впрочем и основная работа уже сделана, осталось чуть чуть допилить, может и сам допилю если никто не обгонит, хотя я думаю не успею ZamoK
Попробовал в рабочую книгу вставить - ответа ждал 3,5 минуты, это нормально или как-то можно ускорить процесс хотябы к минуте свести. P.S. Итог - 200 строк Данные - около 5000 строк
Попробовал в рабочую книгу вставить - ответа ждал 3,5 минуты, это нормально или как-то можно ускорить процесс хотябы к минуте свести. P.S. Итог - 200 строк Данные - около 5000 строкZamoK
ZamoK, поскольку в макросе идёт копирование областей (Range), то при таком количестве строк, думаю нормально, можно сначала пройтись макросом и записать все данные в массив, тогда, поидее, будет быстрее, но изменений в код надо будет больше вносить))). Может даже проще с самого начала будет переписать))).
ZamoK, поскольку в макросе идёт копирование областей (Range), то при таком количестве строк, думаю нормально, можно сначала пройтись макросом и записать все данные в массив, тогда, поидее, будет быстрее, но изменений в код надо будет больше вносить))). Может даже проще с самого начала будет переписать))).Roman777
ZamoK, ещё, я правильно понимаю, что на листе "Материал" должен отображаться материал только для деталей из листа "Механический" и этот материал должен отображаться в количестве "норма"*"кол-во деталей", при этом, для деталей из 1 материала, материалы должны сложиться и отобразиться в 1 строчке (результаты такого расчёта вы хотите увидеть на листе "Материал")?
ZamoK, ещё, я правильно понимаю, что на листе "Материал" должен отображаться материал только для деталей из листа "Механический" и этот материал должен отображаться в количестве "норма"*"кол-во деталей", при этом, для деталей из 1 материала, материалы должны сложиться и отобразиться в 1 строчке (результаты такого расчёта вы хотите увидеть на листе "Материал")?Roman777
Roman777, Код стал чище это +, но вот на листе материал колво материала суммируется, но не умножается на кол-во деталей из потребности, я тут в заначках нарыл код и немного струганул под свою ситуацию. Очень не плохо вышло положил на кнопочку чтоб было видно где в Вашем коде пробелы. работает он мгновенно - что радует. Ваш код попробовал в оригинальном файле, да чисто стало в результате его работы, но время 4 минуты я покурить успел сходить :D
Roman777, Код стал чище это +, но вот на листе материал колво материала суммируется, но не умножается на кол-во деталей из потребности, я тут в заначках нарыл код и немного струганул под свою ситуацию. Очень не плохо вышло положил на кнопочку чтоб было видно где в Вашем коде пробелы. работает он мгновенно - что радует. Ваш код попробовал в оригинальном файле, да чисто стало в результате его работы, но время 4 минуты я покурить успел сходить :D ZamoK
ZamoK, ааа, я просто посчитал зачем на листе Материал это делать, если там в 1 позиции может быть несколько деталей с листа "Механический". Ваше дополнение весьма приукрасило).
ZamoK, ааа, я просто посчитал зачем на листе Материал это делать, если там в 1 позиции может быть несколько деталей с листа "Механический". Ваше дополнение весьма приукрасило).Roman777
Как вариант быстрого алгоритма для сортировки по листам: 1. набираем словарь номеров с именами листов по данным из "Данные" 2. "Итог" берём в массив, проходим циклом, проверяем номер по словарю - получаем имя листа, который берём в другой словарь, где каждому имени листа собираем коллекцию номеров строк, которые в него будем копировать. 3. цикл уже по словарю имён листов - берём ключ, создаём массив нужного размера (всё уже известно), перекладываем в него данные из исходного масисва, выгружаем на лист. Вроде всё. Быстро.
Как вариант быстрого алгоритма для сортировки по листам: 1. набираем словарь номеров с именами листов по данным из "Данные" 2. "Итог" берём в массив, проходим циклом, проверяем номер по словарю - получаем имя листа, который берём в другой словарь, где каждому имени листа собираем коллекцию номеров строк, которые в него будем копировать. 3. цикл уже по словарю имён листов - берём ключ, создаём массив нужного размера (всё уже известно), перекладываем в него данные из исходного масисва, выгружаем на лист. Вроде всё. Быстро.Hugo
я не создавал ни 1 ни 2 модуль, я модуль 2 позаимствовал (автор: doober) и откорректировал, т.к. моих познаний VBA не хватит для такого рода творчества. Roman777,
я не создавал ни 1 ни 2 модуль, я модуль 2 позаимствовал (автор: doober) и откорректировал, т.к. моих познаний VBA не хватит для такого рода творчества. Roman777,
Как вариант быстрого алгоритма для сортировки по листам: 1. набираем словарь номеров с именами листов по данным из "Данные" 2. "Итог" берём в массив, проходим циклом, проверяем номер по словарю - получаем имя листа, который берём в другой словарь, где каждому имени листа собираем коллекцию номеров строк, которые в него будем копировать. 3. цикл уже по словарю имён листов - берём ключ, создаём массив нужного размера (всё уже известно), перекладываем в него данные из исходного масисва, выгружаем на лист.
может как вариант в виде кода? Если конечно не сложно.
Как вариант быстрого алгоритма для сортировки по листам: 1. набираем словарь номеров с именами листов по данным из "Данные" 2. "Итог" берём в массив, проходим циклом, проверяем номер по словарю - получаем имя листа, который берём в другой словарь, где каждому имени листа собираем коллекцию номеров строк, которые в него будем копировать. 3. цикл уже по словарю имён листов - берём ключ, создаём массив нужного размера (всё уже известно), перекладываем в него данные из исходного масисва, выгружаем на лист.
может как вариант в виде кода? Если конечно не сложно. ZamoK
Сейчас некогда, работы по горло. Может вечером, если интереснее занятие не найду. Вообще все части такого кода уже были на форуме - работа с словарём и массивами есть уже у Вас во втором модуле, а как в словарь подключить коллекцию я могу показать на таком примере из "загашника":
[vba]
Код
Sub PereborFailov() 'коллекция в словаре Dim a, i&, t$, Dic As Object Dim el, col
a = Range("C3", Cells(Rows.Count, "A").End(xlUp)).Value Set Dic = CreateObject("Scripting.Dictionary") With Dic .CompareMode = 1 For i = 1 To UBound(a) t = a(i, 1) If Not .exists(t) Then .Add t, New Collection .Item(t).Add a(i, 2) & "|" & a(i, 3) & "|" & i Next End With
For Each el In Dic.keys Debug.Print "Открываем файл " & el For Each col In Dic.Item(el) Debug.Print "Ищем данные " & col Next Debug.Print "Закрываем файл " & el Next
End Sub
[/vba]
Но может ещё кто подключится, может что-то на SQL можно придумать...
Сейчас некогда, работы по горло. Может вечером, если интереснее занятие не найду. Вообще все части такого кода уже были на форуме - работа с словарём и массивами есть уже у Вас во втором модуле, а как в словарь подключить коллекцию я могу показать на таком примере из "загашника":
[vba]
Код
Sub PereborFailov() 'коллекция в словаре Dim a, i&, t$, Dic As Object Dim el, col
a = Range("C3", Cells(Rows.Count, "A").End(xlUp)).Value Set Dic = CreateObject("Scripting.Dictionary") With Dic .CompareMode = 1 For i = 1 To UBound(a) t = a(i, 1) If Not .exists(t) Then .Add t, New Collection .Item(t).Add a(i, 2) & "|" & a(i, 3) & "|" & i Next End With
For Each el In Dic.keys Debug.Print "Открываем файл " & el For Each col In Dic.Item(el) Debug.Print "Ищем данные " & col Next Debug.Print "Закрываем файл " & el Next
End Sub
[/vba]
Но может ещё кто подключится, может что-то на SQL можно придумать...Hugo
Вот такой полуфабрикат, но рабочий. Полуфабрикат потому, что не понял что там с этими нормами расхода и куда их нужно умножать - код писал для первого примера, где нет примера... Но задел для норм в коде есть - это третий словарь, там каждому номеру прописан расход, бери и пользуйся (кроме тех, у кого 0) [vba]
Код
Option Explicit
Sub tt() Dim a(), i&, d1 As Object, d2 As Object, d3 As Object, t$, k, kol As Object, el, x&
Set d1 = CreateObject("scripting.dictionary"): d1.comparemode = 1 Set d2 = CreateObject("scripting.dictionary"): d2.comparemode = 1 Set d3 = CreateObject("scripting.dictionary"): d3.comparemode = 1 'норма расхода
'1. набираем словарь номеров с именами листов по данным из "Данные" a = Sheets("Данные").[a1].CurrentRegion.Columns(1).Resize(, 5).Value For i = 2 To UBound(a) t = Trim(a(i, 1)) If a(i, 5) > 0 Then d3.Item(t) = a(i, 5) 'норма расхода Select Case Trim(a(i, 3)) Case "Сборка": d1.Item(t) = "Сборка" Case "Покупные": d1.Item(t) = "Покупные" Case "Кооперация": d1.Item(t) = "Покупные" Case "Крепеж": d1.Item(t) = "Крепёж" Case Else: d1.Item(t) = "Механический" End Select Next
'2. "Итог" берём в массив, проходим циклом, проверяем номер по словарю - получаем имя листа, 'который берём в другой словарь, где каждому имени листа собираем коллекцию номеров строк, которые в него будем копировать.
a = Sheets("Итог").[a2].CurrentRegion.Columns(1).Resize(, 4).Value For i = 2 To UBound(a) t = Trim(a(i, 2)) If d1.exists(t) Then If Not d2.exists(d1.Item(t)) Then d2.Add d1.Item(t), New Collection d2.Item(d1.Item(t)).Add i End If Next
'3. цикл уже по словарю имён листов - берём ключ, создаём массив нужного размера (всё уже известно), 'перекладываем в него данные из исходного массива, выгружаем на лист.
For Each k In d2.keys Set kol = d2.Item(k) ReDim b(1 To kol.Count, 1 To 4) As String i = 0 For Each el In kol i = i + 1 For x = 1 To 4: b(i, x) = a(el, x): Next Next With Sheets(k) .UsedRange.Offset(2).Clear .[a3].Resize(UBound(B), 4) = b End With Next End Sub
[/vba]
Вот такой полуфабрикат, но рабочий. Полуфабрикат потому, что не понял что там с этими нормами расхода и куда их нужно умножать - код писал для первого примера, где нет примера... Но задел для норм в коде есть - это третий словарь, там каждому номеру прописан расход, бери и пользуйся (кроме тех, у кого 0) [vba]
Код
Option Explicit
Sub tt() Dim a(), i&, d1 As Object, d2 As Object, d3 As Object, t$, k, kol As Object, el, x&
Set d1 = CreateObject("scripting.dictionary"): d1.comparemode = 1 Set d2 = CreateObject("scripting.dictionary"): d2.comparemode = 1 Set d3 = CreateObject("scripting.dictionary"): d3.comparemode = 1 'норма расхода
'1. набираем словарь номеров с именами листов по данным из "Данные" a = Sheets("Данные").[a1].CurrentRegion.Columns(1).Resize(, 5).Value For i = 2 To UBound(a) t = Trim(a(i, 1)) If a(i, 5) > 0 Then d3.Item(t) = a(i, 5) 'норма расхода Select Case Trim(a(i, 3)) Case "Сборка": d1.Item(t) = "Сборка" Case "Покупные": d1.Item(t) = "Покупные" Case "Кооперация": d1.Item(t) = "Покупные" Case "Крепеж": d1.Item(t) = "Крепёж" Case Else: d1.Item(t) = "Механический" End Select Next
'2. "Итог" берём в массив, проходим циклом, проверяем номер по словарю - получаем имя листа, 'который берём в другой словарь, где каждому имени листа собираем коллекцию номеров строк, которые в него будем копировать.
a = Sheets("Итог").[a2].CurrentRegion.Columns(1).Resize(, 4).Value For i = 2 To UBound(a) t = Trim(a(i, 2)) If d1.exists(t) Then If Not d2.exists(d1.Item(t)) Then d2.Add d1.Item(t), New Collection d2.Item(d1.Item(t)).Add i End If Next
'3. цикл уже по словарю имён листов - берём ключ, создаём массив нужного размера (всё уже известно), 'перекладываем в него данные из исходного массива, выгружаем на лист.
For Each k In d2.keys Set kol = d2.Item(k) ReDim b(1 To kol.Count, 1 To 4) As String i = 0 For Each el In kol i = i + 1 For x = 1 To 4: b(i, x) = a(el, x): Next Next With Sheets(k) .UsedRange.Offset(2).Clear .[a3].Resize(UBound(B), 4) = b End With Next End Sub