Прочитав в ТЗ о желании пользователя отбирать и сортировать данные по «для тестирования» исправленной части строки, цвету только что раскрашенной ячейки, автоизменении формата области после ручной корректировки сумм, сам собой напросился вариант:
- выгрузка итоговых значений в Excel
- автофильтр
- условное форматирование,
тем более что, начиная с 2010, форматы ячеек кэшируются (в отличие от более ранних версий, перепрорисовываются только при изменении данных), не говоря о существенном расширении функционала уже в 2007 (подробнее см. http://www.microsoft.com/rus/business/smb/blog/01/).
Если с первым и вторым – все просто, то третий пункт вызывает затруднения, так как условное форматирование устанавливается на конкретную область ячеек, а не на значения в ней, следовательно, после применения сортировки формат не перемещается вместе с данными. Для исправления этого досадного недоразумения, дабы не ломать голову над «раскрашиваниями» средствами 1С, предлагаю использовать простенькие макросы:
Sub Раскрасить(FormattingArea, xlColor) ''применит гистограммы цвета xlColor для FormattingArea
Set FormattingRange = Range(FormattingArea)
FormattingRange.FormatConditions.AddDatabar
With FormattingRange.FormatConditions(1)
.ShowValue = False
.MinPoint.Modify newtype:=xlConditionValueAutomaticMin
.MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
.NegativeBarFormat.ColorType = xlDataBarColor
.AxisPosition = xlDataBarAxisAutomatic
.NegativeBarFormat.Color.Color = 255
With .BarColor
.Color = xlColor
End With
End With
End Sub
Sub РасставитьЗначки(FormattingArea, minValue, maxValue, Optional xlType As Long = xlConditionValuePercent) ''проанализирует FormattingArea по параметру xlConditionValuePercent
Set FormattingRange = Range(FormattingArea)
Set FC = FormattingRange.FormatConditions
FC.AddIconSetCondition
FormattingRange.FormatConditions(FC.Count).SetFirstPriority
With FormattingRange.FormatConditions(1)
.ShowIconOnly = True
.IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
With .IconCriteria(2)
.Type = xlType
.Value = minValue
End With
With .IconCriteria(3)
.Type = xlType
.Value = maxValue
End With
End With
End Sub
Осталось задать области 
В моем случае - это множество подразделений, сотрудники которых по итогам заданного периода должны быть премированы \ уволены в зависимости от показателей. Во вложенном файле – процедура поиска строк для форматирования после изменения их положения.
Оптимизация
- Для ускорения открытия книги сохраняю уже отформатированный вариант, что на языке 1С:
ОбъектXLS.Run("ПрименитьУсловноеФорматирование");// ПрименитьУсловноеФорматирование – основная процедура общего модуля исходного Excel-файла
После макроса буду заполнять еще страницы, а значит надо:
ИсточникЗаписи.Worksheets(«СтраницаБезАвтофильтра»).Activate();
и в основной процедуре исходного Excel-файла:
If ActiveSheet.AutoFilter Is Nothing Then Exit Sub
- Для ускорения пересчета форматов до и после исполнения кода в основной процедуре вызываю:
Sub Before()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
Application.DisplayStatusBar = False
Application.DisplayAlerts = False
End Sub
Sub After()
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
'ActiveSheet.DisplayPageBreaks = True
Application.DisplayStatusBar = True
Application.DisplayAlerts = True
End Sub
Подробнее см. http://habrahabr.ru/blogs/microsoft/112458/
- Коллекция цветов гистограмм и колонок форматирования в модуле объекта книги:
Sub Workbook_Open()
Call ЗаполнитьПредопределенныеЗначения
End Sub
