Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Добавление строк в таблицу с условным форматированием
 
#1
22.06.2017 13:14:20
Здравствуйте, уважаемые форумчане.
Имеется в работе таблица (Excel 2010) с данными по отгрузке продукции покупателям. Таблица ведется ежедневно нарастающим итогом, данные собираются за два года (текущий и предыдущий). В данный момент в ней 7000 строк и 32 покупателя. Для каждого покупателя должна быть выбрана своя заливка цветом (это обязательное требование). Я задала правила условного форматирования, все работает, но есть одна проблема: при добавлении в отчет новых строк правила условного форматирования начинают как-то "самопроизвольно размножаться" и при этом формулы почему-то меняются. Иногда это не влияет на конечный результат, а иногда влияет - чаще всего не закрашиваются ячейки с данными об оплате. Приходится каждый раз заходить в правила форматирования для нужного диапазона и вручную что-то исправлять.
Вопрос - можно ли каким-то образом сделать, чтобы правила условного форматирования распространялись бы сразу на весь динамический диапазон (который будет постоянно увеличиваться вниз)? В инете искала ответ на этот вопрос - в одном примере было предложено использовать формулу смещения, но там же был ответ, что это не работает. Еще нашла похожий вопрос - http://www.excelworld.ru/forum/2-17381-1 , но я с макросами совершенно не дружу. Пыталась вписать в УФ именованные диапазоны - при нажатии "Применить" все сбрасывается к обычным диапазонам. Пыталась сослаться целиком на столбцы, но при добавлении новых строк все равно появляются новые правила УФ. Можно ли как-то решить этот вопрос? Или хотя бы как добавлять новые строки, чтобы не происходило нарушения в формулах. Прописывать правила УФ сразу на 20 тыс. строк не хочется, тем более что под таблицей имеются ячейки со сводной информацией (в примере их нет).
Прикрепляю файл с таблицей и скрины правил УФ - на рис.1 правильные формулы, на рис.2 и 3 формулы, изменившиеся после добавления новых строк в таблицу.
 
 
 
#2
22.06.2017 13:33:55
Картинки удалены - превышение максимально допустимого размера. ElenaAC, пожалуйста, ознакомьтесь с нашими Правилами.
 
 
 
#3
22.06.2017 13:42:09
Извините, я подумала, что ограничение все же действует для каждого файла в отдельности... Получается я смогу только один файл здесь показать и все.... Жаль, хотелось большей наглядности. Или можно добавить рисунки каждый отдельным сообщением? Они же по отдельности меньше 100 Кб.
 
 
 
#4
22.06.2017 13:59:20
Цитата
ElenaAC написал:
Или можно добавить рисунки каждый отдельным сообщением?
Нет - так не пойдёт )) Если уж без рисунков никак, то выкладывайте их на какой-нибудь файлообменник, где не нужно регистрироваться.
 
 
 
#5
22.06.2017 14:00:30
Цитата
ElenaAC написал:
я подумала, что ограничение все же действует для каждого файла в отдельности.
Из Правил:
Цитата
2.3. Приложите файл(ы) с примером (общим весом не более 100 Кб)
 
 
 
#6
22.06.2017 14:05:34
Цитата
ElenaAC написал:
Жаль, хотелось большей наглядности.
Сделайте архивный файл из Ваших файлов.
 
 
 
#7
22.06.2017 14:11:46
без условного форматирования, макросом. Цвета задаёте закрашиванием перечня покупателей на листе products
 
 
 
#8
22.06.2017 14:38:34
Ссылка на рис. с правильными формулами УФ - http://my-files.ru/3nneux
Ссылки на рис. с формулами, изменившимися после добавления новых строк - http://my-files.ru/5w8xlv и http://my-files.ru/km0x8n
yozhik, спасибо, скачала Ваш файл, попробовала - работает. Макрос посмотрела (хотя ничего в нем и не поняла). Но еще момент - надо было конечно отдельно это указать, но я что-то не сообразила, думала будет на рисунках видно, что нужны такие формулы - ячейки N:O и P:Q должны заливаться соответствующим цветом только в том случае, если в них есть данные (об оплате). Если они пустые, то должны оставаться незакрашенными. Я это прописывала отдельно с помощью функции ЕПУСТО. Можно это как-то добавить в макрос?
 
 
 
#9
22.06.2017 14:55:57
в них данные появляются после выбора покупателя и вносятся вручную? или данные уже могут быть в ячейках и при смене покупателя в строке должны (или не должны) очищаться?
 
 
 
#10
22.06.2017 15:02:58
Данные вносятся после выбора покупателя и продукции, вручную. Смены покупателя в строке не происходит - вначале "фиксируются" данные об отгрузке продукции (кому, что, №, дата, кол-во и т.д.), а затем, по мере поступления оплаты заполняются столбцы N:O и P:Q (сделаны две пары столбцов на случай частичной оплаты, чтобы было видно, какого числа поступила первая часть платежа и какого оставшаяся). Т.е. по одной строке могут быть заполнены или ячейки N:O, или P:Q или и те и другие вместе.
 
 
 
#11
22.06.2017 15:17:25
можно так
Цитата
ElenaAC написал:
Смены покупателя в строке не происходит
в этом я бы не был уверен..)
 
 
 
#12
22.06.2017 15:32:38
Да, Вы конечно правы - "никогда не говори никогда"))).
Помучила Ваш файл, попробовала добавить нового покупателя с новой продукцией - все прекрасно работает. Осталось как-то запихнуть этот макрос в свою реальную таблицу и посмотреть что получится.))) Большое спасибо, как говорится, снимаю шляпу - мне эти макросы никогда не освоить наверное... Это какой-то другой уровень мышления. Пошла пробовать, хоть бы получилось....
 
 
 
#13
22.06.2017 15:54:11
Цитата
ElenaAC написал:
хоть бы получилось....
Вам надо будет сначала удалить правила условного форматирования (соответственно, пропадет вся предыдущая заливка  7-ми тысяч строк)
в помощь макрос удаления УФ и перекрашивания строк (предварительно закрасьте покупателей на листе products)
Код
Sub del_f()
With Range("C4").CurrentRegion
If .FormatConditions.Count > 0 Then .FormatConditions.Delete
End With
Dim rng As Range
For i = 4 To 30 '30 - номер строки с последней записью по покупателям, напишите свой номер строки
Set rng = Worksheets("products").Columns(1).Find(what:=Cells(i, 5).Value)
If Not rng Is Nothing Then
    Range("C" & i).Resize(, 15).Interior.ColorIndex = rng.Interior.ColorIndex
    For Each cl In Range("N" & i).Resize(, 4)
        If cl.Value = "" Then cl.Interior.ColorIndex = 0
    Next
End If
Next
End Sub
 
 
 
#14
22.06.2017 16:00:35
Это все сделала, удалила просто через меню "Условное форматирование - Удалить правила со всего листа", все естественно "обесцветилось". Покупатели у меня очень удачно закрашены именно на нужном листе (так сказать для образца заливки). Теперь читаю в инете про перенос макроса из одной книги в другую, но пока что-то не вижу, где этот модуль, про который там написано и который надо перенести....
 
 
 
#15
22.06.2017 16:07:30
копируете макрос целиком, в нужной книге на нужном листе правым мышом кликаете на ярлычке листа и выбираете "исходный текст" (вроде..View Code в анг яз версии)
вставляете скопированный макрос. Всё.
 
 
 
#16
22.06.2017 16:13:06
Так просто, а я в какие-то дебри полезла... Сделала. Работает при заполнении новой строки. А как его заставить сработать на тех строках, что есть? Файл закрывала/открывала, имеющиеся строки все равно без заливки...
 
 
 
#17
22.06.2017 16:24:06
посмотрите сообщение #13. В нем макрос. Скопируйте его в свою книгу
For i = 4 To 30   - (6 строка кода) - здесь поставьте номер последней заполненной строки с покупателем вместо 30 ( 7000 ?)
 
 
 
#18
22.06.2017 16:46:38
Виновата, сразу не вчиталась... Скопировала, добавила, № строки поставила - ничего не меняется. Завтра попробую сначала на примере сделать, может я все-таки что-то не так копирую или вставляю, без файла не понятно. Спасибо еще раз за помощь, надеюсь все-таки все в итоге получится.
 
 
 
#19
22.06.2017 16:53:41
подозрение у меня, что макрос Вы скопировали, но не запустили..) Предыдущий макрос срабатывал на изменения в ячейках. Этот надо запустить принудительно. Нажимаете Alt + F8, выбираете макрос с названием "del_f", нажимаете Enter
 
 
 
#20
22.06.2017 16:54:07
Сейчас "перебирала" в новой строке всех по очереди покупателей - часть моей используемой заливки видимо не входит в палитру макроса, цвета выдает совсем другие по некоторым покупателям, а на одного (заливка была выбрана красным цветом) вообще заругался, что ошибка. Поменяла для него красный цвет на другой, ошибка исчезла. Но это уже детали, это можно подкорректировать.
 
 
 
#21
22.06.2017 16:59:34
ДААААААААААА!!!!!!!!!!!!!! Заработало!!!!!!!!!!!! (Ну я же честно в начале писала, что в макросах ноль....) Спасибо!!!! Побежала домой, завтра буду играться дальше. Ура, ура, ура!!!
 
 
 
#22
23.06.2017 11:31:50
Здравствуйте, уважаемый yozhik! Еще раз хочу сказать большое спасибо за то, что Вы потратили столько времени за решение моей задачи. Таблицу доделала, все нормально, можно сегодня работать.
И еще маленький вопрос вдогонку - а макрос работает только с палитрой из 56 цветов? Никак нельзя добавить цвета хотя бы из основной палитры Excel? А то слишком темные и яркие цвета плохо "читаются". Я все, что смогла найти и понять - это перечень цветов этой самой ограниченной палитры и в соответствии с ним поменяла часть своей заливки. Но так не очень нравится.  
 
 
 
#23
23.06.2017 11:55:19
ElenaAC, используйте конструкцию:
Код
.Interior.Color = RGB(RED, GREEN, BLUE)
где RED, GREEN, BLUE могут принимать значения 0–255.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#24
23.06.2017 12:12:33
замените макросы
Вместо ColorIndex используется Color
 
 
 
#25
23.06.2017 13:14:15
Спасибо, получилось!!! Теперь вообще все прекрасно!
 
 
 
Страницы: 1
Читают тему
Наверх