|
Подчет значений при условии. Большая таблица
|
|
|
|
Helen |
Пятница, 20 Августа 2010, 22:21
|
Участник
Профиль
Группа: Участник
Сообщений: 375
Регистрация: 01.07.2008
Из: Another World
Пользователь №: 27505
Конфигурация
Корпус: Front T-80 350 W Процессор: Другой Материнская плата: Foxconn 945G7AD/Series чипсет Intel Lakeport i945P. DualCore Intel Pentium E2160 1800 Mhz (9x200) Оперативная память: DIMM1: Hyundai HYMP125U64CP8-S5 2 Гб DDR2-800 Видеокарта: NVidia 512 MB GF 9600 GT. Жесткий диск (винчестер): Seagate ST3160815AS (системный) (160 Гб, 7200.10 RPM, SATA-II). Seagate ST3320620AS (320 Гб, 7200.10 RPM, SATA-II). Seagate ST31000528AS (1 Tb 7200.12 SATA-II) Дисковод: 3.5" CD/DVD: Optiarc DVD RW Ad-7190A Модем: Внешний D-Link 2540U Сетевой адаптер: Realtek RTL8168/8111 PCI-E Gigabit Ethernet Adapter (PHY: Realtek RTL8211/8212) Звуковая плата: Realtek ALC861 @ Intel 82801GB ICH7 - High Definition Audio Controller [A-1] Монитор: LG L192WS (19-LCD) Операционная система: Windows XP-Professional SP2 Прочее: ИБП Ippon-400
|
Есть таблица: столбец А - Табельный номер специалиста; столбец В - некое число, сумма. Записей может быть от 0 до 1000. Табельный номер может повторяеся в таблице множество раз. На другом листе тоже таблица столбец А - табельный номер, но каждый написан только один раз, а в столбце В напротив каждого номера сумма значений ячеек из предыдущего листа для этого табельного номера. Например Табельный номер 100 повторяется в таблице 5 раз, в столбе В напротив него все пять раз стоит сумма 200. Нужно, чтобы на другом листе в таблице напротив табельного номера в столбце В отразилась сумма значений этих 5 ячеек, т.е. Табельный номер 100, сумма 1000. Формула =СУММЕСЛИ(Лист1!А1:А800; 100;Лист!1В1:В800) способна мне помочь. Но табельных номеров около 1000. Я решила записать макрос для выполнения этого подсчета. Стандартными средствами записала макрос, в каждой из сток написала формулу. Но при выполнении этого макроса он выдал сообщение на английском, что количество операций слишком велико, выполнение макромса прервано. Как-то можно код макроса оптимизировать?
--------------------
С уважением! Helen
|
|
|
|
|
pashulka |
Воскресенье, 29 Августа 2010, 16:00
|
Участник
Профиль
Группа: Участник
Сообщений: 216
Регистрация: 13.07.2003
Пользователь №: 5551
Конфигурация
Корпус: -- Процессор: -- Материнская плата: -- Оперативная память: -- Видеокарта: -- Жесткий диск (винчестер): -- Дисковод: -- CD/DVD: -- Модем: -- Сетевой адаптер: -- Звуковая плата: -- Монитор: -- Операционная система: -- Прочее: --
|
Код 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
|
Участник
Профиль
Группа: Участник
Сообщений: 375
Регистрация: 01.07.2008
Из: Another World
Пользователь №: 27505
Конфигурация
Корпус: Front T-80 350 W Процессор: Другой Материнская плата: Foxconn 945G7AD/Series чипсет Intel Lakeport i945P. DualCore Intel Pentium E2160 1800 Mhz (9x200) Оперативная память: DIMM1: Hyundai HYMP125U64CP8-S5 2 Гб DDR2-800 Видеокарта: NVidia 512 MB GF 9600 GT. Жесткий диск (винчестер): Seagate ST3160815AS (системный) (160 Гб, 7200.10 RPM, SATA-II). Seagate ST3320620AS (320 Гб, 7200.10 RPM, SATA-II). Seagate ST31000528AS (1 Tb 7200.12 SATA-II) Дисковод: 3.5" CD/DVD: Optiarc DVD RW Ad-7190A Модем: Внешний D-Link 2540U Сетевой адаптер: Realtek RTL8168/8111 PCI-E Gigabit Ethernet Adapter (PHY: Realtek RTL8211/8212) Звуковая плата: Realtek ALC861 @ Intel 82801GB ICH7 - High Definition Audio Controller [A-1] Монитор: LG L192WS (19-LCD) Операционная система: Windows XP-Professional SP2 Прочее: ИБП Ippon-400
|
Здорово! Спасибо . Чуток адаптирую и будет самое оно!
--------------------
С уважением! Helen
|
|
|
|
|
Helen |
Воскресенье, 24 Июля 2011, 19:44
|
Участник
Профиль
Группа: Участник
Сообщений: 375
Регистрация: 01.07.2008
Из: Another World
Пользователь №: 27505
Конфигурация
Корпус: Front T-80 350 W Процессор: Другой Материнская плата: Foxconn 945G7AD/Series чипсет Intel Lakeport i945P. DualCore Intel Pentium E2160 1800 Mhz (9x200) Оперативная память: DIMM1: Hyundai HYMP125U64CP8-S5 2 Гб DDR2-800 Видеокарта: NVidia 512 MB GF 9600 GT. Жесткий диск (винчестер): Seagate ST3160815AS (системный) (160 Гб, 7200.10 RPM, SATA-II). Seagate ST3320620AS (320 Гб, 7200.10 RPM, SATA-II). Seagate ST31000528AS (1 Tb 7200.12 SATA-II) Дисковод: 3.5" CD/DVD: Optiarc DVD RW Ad-7190A Модем: Внешний D-Link 2540U Сетевой адаптер: Realtek RTL8168/8111 PCI-E Gigabit Ethernet Adapter (PHY: Realtek RTL8211/8212) Звуковая плата: Realtek ALC861 @ Intel 82801GB ICH7 - High Definition Audio Controller [A-1] Монитор: LG L192WS (19-LCD) Операционная система: Windows XP-Professional SP2 Прочее: ИБП Ippon-400
|
Теперь задача чуток усложнилась, появился столбец С - код операции. Эти коды дополнительно в виде справочника вынесены полным списком на другой лист (лист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
--------------------
С уважением! Helen
|
|
|
|
|
pashulka |
Понедельник, 25 Июля 2011, 19:59
|
Участник
Профиль
Группа: Участник
Сообщений: 216
Регистрация: 13.07.2003
Пользователь №: 5551
Конфигурация
Корпус: -- Процессор: -- Материнская плата: -- Оперативная память: -- Видеокарта: -- Жесткий диск (винчестер): -- Дисковод: -- CD/DVD: -- Модем: -- Сетевой адаптер: -- Звуковая плата: -- Монитор: -- Операционная система: -- Прочее: --
|
Helen, А нельзя увидеть хотя бы часть исходной таблицы (разумеется, без конфиденциальных данных) и конечный результат ?
|
|
|
|
|
Helen |
Понедельник, 25 Июля 2011, 20:04
|
Участник
Профиль
Группа: Участник
Сообщений: 375
Регистрация: 01.07.2008
Из: Another World
Пользователь №: 27505
Конфигурация
Корпус: Front T-80 350 W Процессор: Другой Материнская плата: Foxconn 945G7AD/Series чипсет Intel Lakeport i945P. DualCore Intel Pentium E2160 1800 Mhz (9x200) Оперативная память: DIMM1: Hyundai HYMP125U64CP8-S5 2 Гб DDR2-800 Видеокарта: NVidia 512 MB GF 9600 GT. Жесткий диск (винчестер): Seagate ST3160815AS (системный) (160 Гб, 7200.10 RPM, SATA-II). Seagate ST3320620AS (320 Гб, 7200.10 RPM, SATA-II). Seagate ST31000528AS (1 Tb 7200.12 SATA-II) Дисковод: 3.5" CD/DVD: Optiarc DVD RW Ad-7190A Модем: Внешний D-Link 2540U Сетевой адаптер: Realtek RTL8168/8111 PCI-E Gigabit Ethernet Adapter (PHY: Realtek RTL8211/8212) Звуковая плата: Realtek ALC861 @ Intel 82801GB ICH7 - High Definition Audio Controller [A-1] Монитор: LG L192WS (19-LCD) Операционная система: Windows XP-Professional SP2 Прочее: ИБП Ippon-400
|
Поняла как сделать Код 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 завис намертво. Вопрос дальнейшей оптимизации кода остается открытым...
--------------------
С уважением! Helen
|
|
|
|
|
pashulka |
Понедельник, 25 Июля 2011, 20:57
|
Участник
Профиль
Группа: Участник
Сообщений: 216
Регистрация: 13.07.2003
Пользователь №: 5551
Конфигурация
Корпус: -- Процессор: -- Материнская плата: -- Оперативная память: -- Видеокарта: -- Жесткий диск (винчестер): -- Дисковод: -- CD/DVD: -- Модем: -- Сетевой адаптер: -- Звуковая плата: -- Монитор: -- Операционная система: -- Прочее: --
|
А если так ... Код 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 |
Понедельник, 25 Июля 2011, 21:07
|
Участник
Профиль
Группа: Участник
Сообщений: 375
Регистрация: 01.07.2008
Из: Another World
Пользователь №: 27505
Конфигурация
Корпус: Front T-80 350 W Процессор: Другой Материнская плата: Foxconn 945G7AD/Series чипсет Intel Lakeport i945P. DualCore Intel Pentium E2160 1800 Mhz (9x200) Оперативная память: DIMM1: Hyundai HYMP125U64CP8-S5 2 Гб DDR2-800 Видеокарта: NVidia 512 MB GF 9600 GT. Жесткий диск (винчестер): Seagate ST3160815AS (системный) (160 Гб, 7200.10 RPM, SATA-II). Seagate ST3320620AS (320 Гб, 7200.10 RPM, SATA-II). Seagate ST31000528AS (1 Tb 7200.12 SATA-II) Дисковод: 3.5" CD/DVD: Optiarc DVD RW Ad-7190A Модем: Внешний D-Link 2540U Сетевой адаптер: Realtek RTL8168/8111 PCI-E Gigabit Ethernet Adapter (PHY: Realtek RTL8211/8212) Звуковая плата: Realtek ALC861 @ Intel 82801GB ICH7 - High Definition Audio Controller [A-1] Монитор: LG L192WS (19-LCD) Операционная система: Windows XP-Professional SP2 Прочее: ИБП Ippon-400
|
Сейчас попробую.
Сообщение отредактировал Helen - Понедельник, 25 Июля 2011, 21:08
--------------------
С уважением! Helen
|
|
|
|
|
pashulka |
Понедельник, 25 Июля 2011, 21:10
|
Участник
Профиль
Группа: Участник
Сообщений: 216
Регистрация: 13.07.2003
Пользователь №: 5551
Конфигурация
Корпус: -- Процессор: -- Материнская плата: -- Оперативная память: -- Видеокарта: -- Жесткий диск (винчестер): -- Дисковод: -- CD/DVD: -- Модем: -- Сетевой адаптер: -- Звуковая плата: -- Монитор: -- Операционная система: -- Прочее: --
|
Некорректно считает только макрос MyCountIfs ? или Count тоже ?
|
|
|
|
|
Helen |
Понедельник, 25 Июля 2011, 21:44
|
Участник
Профиль
Группа: Участник
Сообщений: 375
Регистрация: 01.07.2008
Из: Another World
Пользователь №: 27505
Конфигурация
Корпус: Front T-80 350 W Процессор: Другой Материнская плата: Foxconn 945G7AD/Series чипсет Intel Lakeport i945P. DualCore Intel Pentium E2160 1800 Mhz (9x200) Оперативная память: DIMM1: Hyundai HYMP125U64CP8-S5 2 Гб DDR2-800 Видеокарта: NVidia 512 MB GF 9600 GT. Жесткий диск (винчестер): Seagate ST3160815AS (системный) (160 Гб, 7200.10 RPM, SATA-II). Seagate ST3320620AS (320 Гб, 7200.10 RPM, SATA-II). Seagate ST31000528AS (1 Tb 7200.12 SATA-II) Дисковод: 3.5" CD/DVD: Optiarc DVD RW Ad-7190A Модем: Внешний D-Link 2540U Сетевой адаптер: Realtek RTL8168/8111 PCI-E Gigabit Ethernet Adapter (PHY: Realtek RTL8211/8212) Звуковая плата: Realtek ALC861 @ Intel 82801GB ICH7 - High Definition Audio Controller [A-1] Монитор: LG L192WS (19-LCD) Операционная система: Windows XP-Professional SP2 Прочее: ИБП Ippon-400
|
А как выложить часть таблицы фотографиями или переслать файлом?
--------------------
С уважением! Helen
|
|
|
|
|
Helen |
Понедельник, 25 Июля 2011, 22:03
|
Участник
Профиль
Группа: Участник
Сообщений: 375
Регистрация: 01.07.2008
Из: Another World
Пользователь №: 27505
Конфигурация
Корпус: Front T-80 350 W Процессор: Другой Материнская плата: Foxconn 945G7AD/Series чипсет Intel Lakeport i945P. DualCore Intel Pentium E2160 1800 Mhz (9x200) Оперативная память: DIMM1: Hyundai HYMP125U64CP8-S5 2 Гб DDR2-800 Видеокарта: NVidia 512 MB GF 9600 GT. Жесткий диск (винчестер): Seagate ST3160815AS (системный) (160 Гб, 7200.10 RPM, SATA-II). Seagate ST3320620AS (320 Гб, 7200.10 RPM, SATA-II). Seagate ST31000528AS (1 Tb 7200.12 SATA-II) Дисковод: 3.5" CD/DVD: Optiarc DVD RW Ad-7190A Модем: Внешний D-Link 2540U Сетевой адаптер: Realtek RTL8168/8111 PCI-E Gigabit Ethernet Adapter (PHY: Realtek RTL8211/8212) Звуковая плата: Realtek ALC861 @ Intel 82801GB ICH7 - High Definition Audio Controller [A-1] Монитор: LG L192WS (19-LCD) Операционная система: Windows XP-Professional SP2 Прочее: ИБП Ippon-400
|
Лист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
--------------------
С уважением! Helen
|
|
|
|
|
pashulka |
Понедельник, 25 Июля 2011, 22:35
|
Участник
Профиль
Группа: Участник
Сообщений: 216
Регистрация: 13.07.2003
Пользователь №: 5551
Конфигурация
Корпус: -- Процессор: -- Материнская плата: -- Оперативная память: -- Видеокарта: -- Жесткий диск (винчестер): -- Дисковод: -- CD/DVD: -- Модем: -- Сетевой адаптер: -- Звуковая плата: -- Монитор: -- Операционная система: -- Прочее: --
|
На самом деле, мой вариант - это всего лишь попытка оптимизации Вашего кода, за счёт уменьшения используемых диапазонов, поэтому их результаты, должны быть идентичными. Что касается решения, то повторюсь, лучше видеть таблицы в виде xls файла (а свой e-mail я Вам выслал, так что ждём-с)
|
|
|
|
|
Helen |
Понедельник, 25 Июля 2011, 23:03
|
Участник
Профиль
Группа: Участник
Сообщений: 375
Регистрация: 01.07.2008
Из: Another World
Пользователь №: 27505
Конфигурация
Корпус: Front T-80 350 W Процессор: Другой Материнская плата: Foxconn 945G7AD/Series чипсет Intel Lakeport i945P. DualCore Intel Pentium E2160 1800 Mhz (9x200) Оперативная память: DIMM1: Hyundai HYMP125U64CP8-S5 2 Гб DDR2-800 Видеокарта: NVidia 512 MB GF 9600 GT. Жесткий диск (винчестер): Seagate ST3160815AS (системный) (160 Гб, 7200.10 RPM, SATA-II). Seagate ST3320620AS (320 Гб, 7200.10 RPM, SATA-II). Seagate ST31000528AS (1 Tb 7200.12 SATA-II) Дисковод: 3.5" CD/DVD: Optiarc DVD RW Ad-7190A Модем: Внешний D-Link 2540U Сетевой адаптер: Realtek RTL8168/8111 PCI-E Gigabit Ethernet Adapter (PHY: Realtek RTL8211/8212) Звуковая плата: Realtek ALC861 @ Intel 82801GB ICH7 - High Definition Audio Controller [A-1] Монитор: LG L192WS (19-LCD) Операционная система: Windows XP-Professional SP2 Прочее: ИБП Ippon-400
|
Отправила на яндекс. Функция счётеслимн не подходит, т.к. в качестве критерия нужно задать конкретное значение. Если я задаю диапозон как критерий, то Ecxel воспринимает целый диапозон как одно условие и получается ерунда. Суммы в файле посчитаны макросом1, он внутри.
Сообщение отредактировал Helen - Понедельник, 25 Июля 2011, 23:08
--------------------
С уважением! Helen
|
|
|
|
|
Helen |
Среда, 27 Июля 2011, 8:56
|
Участник
Профиль
Группа: Участник
Сообщений: 375
Регистрация: 01.07.2008
Из: Another World
Пользователь №: 27505
Конфигурация
Корпус: Front T-80 350 W Процессор: Другой Материнская плата: Foxconn 945G7AD/Series чипсет Intel Lakeport i945P. DualCore Intel Pentium E2160 1800 Mhz (9x200) Оперативная память: DIMM1: Hyundai HYMP125U64CP8-S5 2 Гб DDR2-800 Видеокарта: NVidia 512 MB GF 9600 GT. Жесткий диск (винчестер): Seagate ST3160815AS (системный) (160 Гб, 7200.10 RPM, SATA-II). Seagate ST3320620AS (320 Гб, 7200.10 RPM, SATA-II). Seagate ST31000528AS (1 Tb 7200.12 SATA-II) Дисковод: 3.5" CD/DVD: Optiarc DVD RW Ad-7190A Модем: Внешний D-Link 2540U Сетевой адаптер: Realtek RTL8168/8111 PCI-E Gigabit Ethernet Adapter (PHY: Realtek RTL8211/8212) Звуковая плата: Realtek ALC861 @ Intel 82801GB ICH7 - High Definition Audio Controller [A-1] Монитор: LG L192WS (19-LCD) Операционная система: Windows XP-Professional SP2 Прочее: ИБП Ippon-400
|
Благодарю, все работает!
--------------------
С уважением! Helen
|
|
|
|
|
Вормс |
Среда, 03 Января 2018, 21:31
|
Участник
Профиль
Группа: Участник
Сообщений: 20
Регистрация: 26.12.2017
Пользователь №: 34867
Конфигурация
Корпус: -- Процессор: -- Материнская плата: -- Оперативная память: -- Видеокарта: -- Жесткий диск (винчестер): -- Дисковод: -- CD/DVD: -- Модем: -- Сетевой адаптер: -- Звуковая плата: -- Монитор: -- Операционная система: -- Прочее: --
|
и у меня сработало
|
|
|
|
|
pita |
Пятница, 25 Мая 2018, 21:25
|
Участник
Профиль
Группа: Участник
Сообщений: 18
Регистрация: 25.05.2018
Пользователь №: 34889
Конфигурация
Корпус: -- Процессор: -- Материнская плата: -- Оперативная память: -- Видеокарта: -- Жесткий диск (винчестер): -- Дисковод: -- CD/DVD: -- Модем: -- Сетевой адаптер: -- Звуковая плата: -- Монитор: -- Операционная система: -- Прочее: --
|
Спасибо
|
|
|
|
|
|
1 чел. читают эту тему (1 Гостей и 0 Скрытых Пользователей)
|
0 Пользователей:
|
|
© Copyright by WinCity.Ru 2001 - 2008 | Обратная связьУслуги веб-хостинга предоставлены компанией MTW.RU
|
|