Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Помогите, excel: суммировать по условию - подсветить превышение суммы
 
#1
03.10.2011 15:32:22
Помогите:  
по каждой строчке должна быть проверка, что сумма по порядковому номеру с пустым значением в ячейки учет не превышает все суммы с тем же поряковым номером с "1" в поле учет. То есть: порядковый номер 2, сумма 2400, значит нужна проверка, что по всем коричневым двойкам (значение в столбце учет - 1) сумма не превышает 2400, в нашем случае по 16 строке должен быть какой то индикатор, что по поряковым номерам "2" сумма получается 2500, это больше чем 2400, нужен индикатор любого вида - цифра, цвет, что угодно.  
Во вложении подробное описание, задача на 10 минут. Автору решения 200 рублей на мобильный или любой электронный кошелек. Честно, гарантирую.
 
 
 
#2
03.10.2011 15:45:51
а подсвечивать нужно все ячейки с превышениями? или только последнюю по порядку (сверху вниз), там где это превышение возникло?
 
 
 
#3
03.10.2011 15:52:36
Вот так, если нужно посветить все превышения  
Экс 2007
 
 
 
#4
03.10.2011 15:53:49
Уупс, пардоньте. Отправил не то.  
вот то
 
 
 
#5
03.10.2011 16:07:25
Поправился (добавил проверку на наличие запланированных трат)
 
 
 
#6
03.10.2011 16:14:23
порядковый номер 2. если в столбце "учет" пусто, то это сумма ограничения.  
Далее порядковые номера 2 будут только со значением 1 в поле учет.  
нужна подсветка той строки, в которой сумма всех порядковых номеров 2 с "1" в поле "учет" будет больше суммы ограничения.  
   
№    учет     сумма  
2 - "пусто" 100 рублей -  это строка ограничение  
2 - "  1  " 50 рублей  
2 - "  1  " 40 рублей  
2 - "  1  " 20 рублей - тут должна быть подсветка, т.к. 50+40+20=110 - больше 100.
 
 
 
#7
03.10.2011 16:15:47
{quote}{login=ABZAC}{date=03.10.2011 03:45}{thema=}{post}а подсвечивать нужно все ячейки с превышениями? или только последнюю по порядку (сверху вниз), там где это превышение возникло?{/post}{/quote}
 
 
 
#8
03.10.2011 16:28:02
ОТЛИЧНО, все работает  
реквизиты для бонуса можно выслать на почту: trofimov80@mail.ru  
огромное спасибо
 
 
 
#9
03.10.2011 16:32:49
Ловите -- последнее превышение только подсвечивает
 
 
 
#10
03.10.2011 16:33:34
уупс, прикладываю
 
 
 
#11
03.10.2011 16:35:51
теле2 питерский  
+7-904-613-94-04  
ЗЫ: почтой не отправляю -- почтовик режет исходящую на этот домен
 
 
 
#12
03.10.2011 17:51:01
чек
 
 
 
#13
03.10.2011 17:59:46
есть случаи, когда по определенному порядковому номеру в столбце "учет" сразу идет цифра 1, и сверху нет этого порядкового номера без значения в столбце "учет". В этом случае ставится значение превышение.    
То есть, сразу идет оплата, без суммы ограничения, так бывает и это нормально.  
 
Можно сделать, чтобы в этом случае превышение не писалось
 
 
 
#14
03.10.2011 18:12:13
Пример?
 
 
 
#15
03.10.2011 18:38:53
Такого (Вами описанного) быть не должно. Это ошибка    
скорее всего "поплыли" диапазоны при протягивании/копировании  
попробуйте так:  
1. вставить формулу в певую (самую верхнюю ячейку)  
2. в формуле везде где $C$17, $C17 -- цифру (число строк) изменить на последнюю строку в таблице. Доллары не трогать -- где стоят не убирать, где нет -- не добавлять.  
3. протянуть до конца таблицы
 
 
 
#16
03.10.2011 18:40:19
...начала (началы) диапазонов в формуле не менять, а только конец (часть диапазонов -- весь столбец, часть -- начинается со след. ячейки)
 
 
 
#17
03.10.2011 18:50:04
все хорошо, спасибо
 
 
 
#18
04.10.2011 15:21:50
суть такая, лист excel имеет 3000 строк и каждый день от до 100 строк добавляется. Массив растет, а скорость работы падает. можно как-то сделать, чтобы формула по строкам, которые проверились удалялась или не считалась.  
то есть вопрос не обязательно к этой формуле, но к скорости работы точно.  
 
было предложено 2 варианта:  
1. протягивать формулу на 100 строк, потом удалять. (неподошел - требует памяти и аккуратности пользователя)  
2. при заполнении новой строки запускать макрос копирования формулы в новую строку и удаления со старой (неподошел - увеличивается нагрузка на пользователя)    
 
Может можно как-то привязаться к вставке 1 в поле учет, в этот момент вставляется формула из предидущей строки с 1 в поле учет. Или любой другой механизм  
Оплату, как обычно гарантирую
 
 
 
#19
04.10.2011 15:33:54
конечно можно.  
 
заполняете вручную?
Живи и дай жить..
 
 
 
#20
04.10.2011 15:39:42
да, значения вносятся ручками (файл 86)  
порядковый номер уже стоит  
цифра 1 в столбце "учет" и сумма вносятся вручную
 
 
 
#21
05.10.2011 09:49:40
300 рублей за решение  
готов датб любые комментарии по кейсу
 
 
 
#22
05.10.2011 10:38:37
...если, конечно я правильно понял задачу, проверяйте  
{quote}{login=etrofimovv}{date=04.10.2011 03:21}{thema=скорость}{post}суть  можно как-то сделать, чтобы формула по строкам, которые проверились удалялась или не считалась.  
{/post}{/quote}  
Работает так: перед закрытием книги меняет на "Лист1" формулы в столбце E на получившиеся значения (после этого, ессно нужно книгу сохранить).  
Нужно разрешить макросы.  
Для проверки скопируйте с соседнего листа столбец с формулами на Лист1 и закройте книгу, результат будет после повторного открытия  
Если нужно поменять имя листа для которого макрос вып-ся: жмем Alt-F11  
заходим на "Эта книга", меняем в строке Worksheets("Лист1") на нужное имя  
если нужно поменять столбец -- в строке Range(Cells(3, 5), Cells(end_strok, 5)) -- пятерку (столбец E) меняем на порядковый номер нужного.  
ЗЫ: в столбце B -- д.б. проставлены номера как минимум до последней строки с формулами.  
ЗЗЫ: массив 5000 строк обрабатывает 20 секунд ровно
 
 
 
#23
05.10.2011 14:36:30
да, схема рабочая  
Пару пожеланий:  
1. можно в формуле поставить ограничение, что поиск идет только вверху массива. Теперь, когда проверка 3000 строк сделана, нужна проверка вводимой строки и массива сверху. Порядковые номера проставлены до 20000 тысяч, но ниже 3000 пусто. Может это сможет ускорит расчет.  
 
2. можно в макросе изменить столбец, по которому определяется заполненная строка, ниже которой идет удаление строки. с 2 на 4 (по сумме)  
попробовал, что-то не помогло (end_strok = Cells(Rows.Count, 4).End(xlUp).Row)    
 
3. Было бы уж совсем хорошо, чтобы формула сама в макросе протягивалась вниз на допустим 100 строк вниз. ниже последней строки с суммой при закрытии (или открытии, неважно)  
 
Таким образом диапазон ячеек с формулами будут плавно спускаться по массиву в процессе заполнения и проверка будет быстро работать
 
 
 
#24
05.10.2011 15:24:00
пока по второму вопросу:  
вторая колонка выбрана не случайна (в столбце с суммой есть "пробелы" -- они воспринимаются как конец таблицы и макрос затыкается), работать будет если во всех ячейках столбца есть значения
 
 
 
#25
05.10.2011 15:44:54
первый и второй вопросы недопонял,  
 
сделал так:  
в списке макросов увидите соответсвующие названия (вставка/удаление формул)    
вставка работает так: поставить на первую, самую верхнюю ячейку, в которой и вниз до конца (конец определяется опять же по номеру в "В") столбца всвляется формула  
проверяйте формулу  
Если нужно чтобы все оптом делалось при зкрытии -- всталяеете код "вставка" в модуль книги после Calculate  
"само" "от какой ячейки формулы вставлять" -- не определится никак (во всяком случае я не могу придумать такого критерия)  
ЗЫ: привязка макроса убирания формул к закрытию файла осталась  
<EM><STRONG>Файл удален</STRONG> - велик размер - [<STRONG>МОДЕРАТОРЫ</STRONG>]</EM>
 
 
 
#26
05.10.2011 15:47:46
поспешил, людей насмешил размером файла, исправляюсь
 
 
 
#27
05.10.2011 16:52:01
Вариант.
 
 
 
#28
06.10.2011 12:17:47
Все заработало, вставил в реальный файл.  
последний штрих, можно адаптировать формулу для вставки/удаления в ячейку A7.  
соответственно, строки начинаются также  
№ - B7  
учет - C7  
сума - D7  
 
в excel я их легко переносил, а вот писать в кодировке R3C2 не получается)  
 
Соответственно нужны две формулы, для вставки в макрос с новым столбцом начала
 
 
 
#29
07.10.2011 16:50:22
Уважаемый ABZAC, перевод 300 рублей на мобильный будет совершен 11 октября, квитанцию выложу, как в прошлый раз. Приятно сотрудничать.
 
 
 
#30
15.10.2011 16:21:26
перевод в размере 300 рублей (со стоимостью услуг оператора) произведен в 17.55 14 октября
 
 
 
Страницы: 1
Читают тему
Loading...