[Helen]

Есть таблица: столбец А - Табельный номер специалиста; столбец В - некое число, сумма. Записей может быть от 0 до 1000. Табельный номер может повторяеся в таблице множество раз. На другом листе тоже таблица столбец А - табельный номер, но каждый написан только один раз, а в столбце В напротив каждого номера сумма значений ячеек из предыдущего листа для этого табельного номера. Например Табельный номер 100 повторяется в таблице 5 раз, в столбе В напротив него все пять раз стоит сумма 200. Нужно, чтобы на другом листе в таблице напротив табельного номера в столбце В отразилась сумма значений этих 5 ячеек, т.е. Табельный номер 100, сумма 1000. Формула =СУММЕСЛИ(Лист1!А1:А800; 100;Лист!1В1:В800) способна мне помочь. Но табельных номеров около 1000.
Я решила записать макрос для выполнения этого подсчета. Стандартными средствами записала макрос, в каждой из сток написала формулу. Но при выполнении этого макроса он выдал сообщение на английском, что количество операций слишком велико, выполнение макромса прервано. Как-то можно код макроса оптимизировать?

[pashulka]

Код

Private Sub Test()
   With Range([A2], [A65536].End(xlUp)) '[A1]
        .Offset(, 1).Value = Application.SumIf([Лист1!A:A], .Cells, [Лист1!B:B])
   End With
End Sub

[Helen]

Здорово! Спасибо . Чуток адаптирую и будет самое оно!

[Helen]

Теперь задача чуток усложнилась, появился столбец С - код операции. Эти коды дополнительно в виде справочника вынесены полным списком на другой лист (лист3). Стоит задача: посчитать количество строк, в которых выполняются сразу два условия, т.е., например, табельный номер 100 повторяется в таблице 5 раз, в столбце С напротив этого номера коды операций, но только 4 из 5 соответствуют справочнику на Листе3. Нужно, чтобы на листе2 в таблице напротив табельного номера 100 в столбце С отразилась количество строк, коды в которых соответствуют справочнику, т.е. 4. Табельный номер 100, количество 4.
Раньше, когда нужно было подсчитать количество повторений каждого табельного номера в таблице я использовала макрос

Код

Sub count()
'
' count Макрос

With Range([B4], [B65536].End(xlUp))  '[B3]
       .Offset(, 4).Value = Application.CountIf([Лист1!A:A], .Cells)
 
  End With
End Sub

где в ячейках В4-В65536 находились табельный номера по порядку.
А как сюда приписать еще условие, чтобы соблюдались оба сразу, не могу сообразить.
Забыла сказать: пользуюсь офис 2007, функция счётеслимн для моего случая не подходит, записей слишком много.

Сообщение отредактировал Helen - Воскресенье, 24 Июля 2011, 19:49

[pashulka]

Helen, А нельзя увидеть хотя бы часть исходной таблицы (разумеется, без конфиденциальных данных) и конечный результат ?

[Helen]

Поняла как сделать

Код

Sub count()
'
' count Макрос

With Range([B4], [B65536].End(xlUp))  '[B3]
      .Offset(, 4).Value = Application.CountIfs([Лист1!A:A], .Cells, [Лист1!C:C], [Лист3!A1:A1000],)

 End With
End Sub

Хорошо что кодов операций меньше 1000, а то когда я поставила [Лист3!A:A], Ecxel завис намертво. Вопрос дальнейшей оптимизации кода остается открытым...

[pashulka]

А если так ...

Код

Private Sub MyCountIfs()
   Dim iTarget As Range, iColumnA As Range, iColumnC As Range, iColumnCode As Range, iRow&
   
   Set iTarget = Range([Лист2!B4], [Лист2!B65536].End(xlUp))
   Set iColumnCode = Range([Лист3!A1], [Лист3!A65536].End(xlUp))
   
   'Необходимо, чтобы количество ячеек в столбцах A и C было одинаково, поэтому :
   iRow = Application.Max([Лист1!A65536].End(xlUp).Row, [Лист1!C65536].End(xlUp).Row)
   
   Set iColumnA = [Лист1!A1].Resize(iRow)
   Set iColumnC = [Лист1!C1].Resize(iRow)

   iTarget.Offset(0, 4).Value = Application.CountIfs(iColumnA, iTarget, iColumnC, iColumnCode)
End Sub

Сообщение отредактировал pashulka - Понедельник, 25 Июля 2011, 20:58

[Helen]

Сейчас попробую.

Сообщение отредактировал Helen - Понедельник, 25 Июля 2011, 21:08

[pashulka]

Некорректно считает только макрос MyCountIfs ? или Count тоже ?

[Helen]

А как выложить часть таблицы фотографиями или переслать файлом?

[Helen]

Лист1

Лист2

Лист3

Например: на листе 1 у таб № 0013 услиги из справочника листа3 повторяются 3 раза, значит на листе 2 в колонке С напротив табельного номера 13 должна стоять цифра 3 и т.д.: напротив 7 должно быть 1, напротив 17 должно быть 1 и так далее.
P.S. думаю так достаточно безопасно выложить, никто не поймет что это и зачем

Вот этот макрос выдает мне нули на листе 2 в конке С.

Код

Sub Макрос2()
'
' Макрос2 Макрос
'

With Range([A2], [A65536].End(xlUp)) '[A1]
       .Offset(, 2).Value = Application.CountIfs([Лист1!A:A], .Cells, [Лист1!C:C], [Лист3!A1:A20])
  End With

End Sub

Ваш вариант тоже почему-то нули выдает, может дело в значениях справочника или формате ячеек?

Сообщение отредактировал Helen - Понедельник, 25 Июля 2011, 22:17

[pashulka]

На самом деле, мой вариант - это всего лишь попытка оптимизации Вашего кода, за счёт уменьшения используемых диапазонов, поэтому их результаты, должны быть идентичными. Что касается решения, то повторюсь, лучше видеть таблицы в виде xls файла (а свой e-mail я Вам выслал, так что ждём-с)

[Helen]

Отправила на яндекс.
Функция счётеслимн не подходит, т.к. в качестве критерия нужно задать конкретное значение. Если я задаю диапозон как критерий, то Ecxel воспринимает целый диапозон как одно условие и получается ерунда.
Суммы в файле посчитаны макросом1, он внутри.

Сообщение отредактировал Helen - Понедельник, 25 Июля 2011, 23:08

[Helen]

Благодарю, все работает!

[Вормс]

и у меня сработало

[pita]

Спасибо