Доброе утро. Пытаюсь написать SumIf. Но вместо первого аргумента - Arg1 нужен не просто диапазон, а только вычлененные из диапазона артикулы деталей. Алгоритм такой: После знаков "КОД: (пробел)" выбираем все знаки до "-'" (тире), после "-" только 5 знаков. Там где нет тире, берётся артикул из cells(i,1). Для примера в файле есть формула, которая это делает. Количество строк 3000. Желательно языком для чайника, с комментами.
вынести за блок If...Then...Else...End If, как-то так
Код
Sub oborot()
Dim a As Byte, b As Byte, i As Long, lstr As Long, c As String
lstr = Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False
Columns(5).ClearContents
Columns(1).NumberFormat = "@"
For i = 7 To lstr
b = InStr(1, Cells(i, 2), "Êîä")
a = InStr(b, Cells(i, 2), "-")
If a = 0 Then
c = Cells(i, 1)
Else
c = Mid(Cells(i, 2), b + 5, a - b - 5) & Mid(Cells(i, 2), a + 1, 5)
End If
Cells(i, 5) = WorksheetFunction.SumIf(Range("a7:a" & lstr), c, Range("c7:c" & lstr))
Next i
Application.ScreenUpdating = True
End Sub
Согласие есть продукт при полном непротивлении сторон
Function myArray(rCod As Range, rTxt As Range)
Dim a$(), aTxt(), aCod()
Static r As Object, m As Object
aTxt = rTxt.Value: aCod = rCod.Value
If r Is Nothing Then
Set r = CreateObject("vbscript.regexp")
r.Pattern = "Код: ([^-]+-.{5})"
End If
ReDim a(1 To UBound(aTxt), 1 To UBound(aTxt, 2))
For i = 1 To UBound(a)
Set m = r.Execute(aCod(i, 1))
If m.Count Then a(i, 1) = Replace(m(0).submatches(0), "-", "") Else a(i, 1) = aTxt(i, 1)
Next
myArray = a
End Function
Саша, мне не надо ЮДФ_ку, к тому регэкспешную. Мне нужно в формулу WorksheetFunction.SumIf(Range("a7:a"& lstr), c, Range("c7:c"& lstr)) вместо Range("a7:a"& lstr) вставить виртуальный массив с уже вытащенными из столбца В кодами. Должно получиться что-то типа - WorksheetFunction.SumIf(arrKod, c, Range("c7:c"& lstr))
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
Суть тут такова. Есть оригинальные запчасти и неоригиналы. К примеру, два неоригинальных генератора имеют коды 37300-22650DSH и 37300-22650HAN. Оригинальный артикул запчасти - 37300-22650, а всё остальное это приставки. Поэтому получив этот номер можно посчитать статистику продаж всех неоригинальных запчастей. В нашем случае - это 8+6=14 генераторов в год.
сделать вспомогательный столбец с формулой, выделяющей "оригинальный" код. на основе этого столбца - обычной СУММЕСЛИ или сводной подсчитать требуемое.
вариант?
фрилансер Excel, VBA - контакты в профиле "Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
Насколько вижу, в ячейку ставим не саму формулу, а ее результат. Тогда почему не сделать свой вариант SUMIF, загнав сразу в массив столбцы 2-3, и пробежавшись по ним со сравнением, выгрузить сразу результат в столбец 5?
Как вариант, модно суммировать через словарь.
Если это только часть большого кода с более можно этот самописный SUMIF загнать в функцию, и всё.
Максим, с удовольствием рассмотрю Ваш вариант через словарь. Суммесли - это для понимания задачи. Мне бы понять, как не циклом, а сразу массивом обработать текст с артикулами запчастей, просуммировать совпадающие, а потом вывести на лист. Через Access сделал вчера, но интерес остался именно в реализации на VBA.
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
Хотя бы один цикл, да понадобится - пройтись по исходному массиву. в итоге, нужно вывести на лист количества к каждой строчке исходного массива (с повторами)? Или нужен список уникальных кодов с суммами?
Нужно вывести общее кол-во по столбцу С, если артикулы в столбце B совпадают. Артикулы в столбце B могут совпасть только после описанной выше обработки столбца B.
<<После знаков "КОД: (пробел)" выбираем все знаки до "-'" (тире), после "-" только 5 знаков. Там где нет тире, берётся артикул из cells(i,1).>>
Sub oborot()
Dim a As Byte, b As Byte, i As Long, lstr As Long, c As String
Dim oDic As Object, arrA(), arrO()
Set oDic = CreateObject("scripting.dictionary")
lstr = Cells(Rows.Count, 2).End(xlUp).Row
arrA = Range(Cells(7, 1), Cells(lstr, 3)).Value
Application.ScreenUpdating = False
Columns(5).ClearContents
Columns(1).NumberFormat = "@"
ReDim arrO(1 To UBound(arrA), 1 To 1) ' массив для выгрузки
For i = 1 To UBound(arrA, 1)
b = InStr(1, arrA(i, 2), "Код")
a = InStr(b, arrA(i, 2), "-")
If a = 0 Then
c = arrA(i, 1)
Else
c = Mid(arrA(i, 2), b + 5, a - b - 5) & Mid(arrA(i, 2), a + 1, 5)
End If
' суммируем словарем
If oDic.exists(c) Then
oDic(c) = oDic(c) + arrA(i, 3)
Else
oDic.Item(c) = arrA(i, 3)
End If
arrO(i, 1) = c ' пишем "c" в массив для соответствующего сопоставления при выгрузке
Next i
' заменяем в выходном массиве "с" на соотв. суммы
For i = 1 To UBound(arrA)
arrO(i, 1) = oDic(arrO(i, 1))
Next
Range(Cells(7, 5), Cells(lstr, 5)).Value = arrO
' Dim cArr, uArr
' cArr = oDic.Keys ' одномерный массив уникальных "с"
' uArr = oDic.Items ' одномерный массив сумм для уникальных "c"
Application.ScreenUpdating = True
End Sub