Версия для печати темы

Нажмите сюда для просмотра этой темы в оригинальном формате

WinCity.Ru _ Microsoft/Office Excel _ Подчет значений при условии. Большая таблица

Автор: Helen Пятница, 20 Августа 2010, 22:21

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

Автор: pashulka Воскресенье, 29 Августа 2010, 16:00

Код

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 Понедельник, 30 Августа 2010, 19:08

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

Автор: Helen Воскресенье, 24 Июля 2011, 19:44

Теперь задача чуток усложнилась, появился столбец С - код операции. Эти коды дополнительно в виде справочника вынесены полным списком на другой лист (лист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, функция счётеслимн для моего случая не подходит, записей слишком много.

Автор: pashulka Понедельник, 25 Июля 2011, 19:59

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

Автор: Helen Понедельник, 25 Июля 2011, 20:04

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

Код

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 Понедельник, 25 Июля 2011, 20:57

А если так ...

Код

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

Автор: Helen Понедельник, 25 Июля 2011, 21:07

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

Автор: pashulka Понедельник, 25 Июля 2011, 21:10

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

Автор: Helen Понедельник, 25 Июля 2011, 21:44

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

Автор: Helen Понедельник, 25 Июля 2011, 22:03

Лист1
http://imageshost.ru/photo/112908/id764640.html
Лист2
http://imageshost.ru/photo/92788/id764638.html
Лист3
http://imageshost.ru/photo/91340/id764643.html
Например: на листе 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

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

Автор: pashulka Понедельник, 25 Июля 2011, 22:35

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

Автор: Helen Понедельник, 25 Июля 2011, 23:03

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

Автор: Helen Среда, 27 Июля 2011, 8:56

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

Автор: Вормс Среда, 03 Января 2018, 21:31

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

Автор: pita Пятница, 25 Мая 2018, 21:25

Спасибо