Наглядный анализ данных посредством функции условного форматирования в MS Excel 2007\2010, запускаемой макросами из-под 1С 7.7 \ 8.х


Дабы не ломать голову над «мегараскрашиваниями» средствами 1С, предлагаю выводить данные в MS Excel и использовать простенькие макросы, запускаемые из 1С в процессе выгрузки, к тому же аналога функции "условное форматирование" в 1С нет

Прочитав в ТЗ о желании пользователя отбирать и сортировать данные по «для тестирования» исправленной части строки, цвету только что раскрашенной ячейки, автоизменении формата области после ручной корректировки сумм, сам собой напросился вариант:

  1. выгрузка итоговых значений в Excel
  2. автофильтр
  3. условное форматирование,

тем более что, начиная с 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

Осталось задать области Smile В моем случае - это множество подразделений, сотрудники которых по итогам заданного периода должны быть премированы \ уволены в зависимости от показателей. Во вложенном файле – процедура поиска строк для форматирования  после изменения их положения.

Оптимизация

  1. Для ускорения открытия книги сохраняю уже отформатированный вариант, что на языке 1С:

    ОбъектXLS.Run("ПрименитьУсловноеФорматирование");// ПрименитьУсловноеФорматирование – основная процедура общего модуля исходного Excel-файла

    После макроса буду заполнять еще страницы, а значит надо:

    ИсточникЗаписи.Worksheets(«СтраницаБезАвтофильтра»).Activate();

    и в основной процедуре исходного Excel-файла:

    If ActiveSheet.AutoFilter Is Nothing Then Exit Sub

  2. Для ускорения пересчета форматов до и после исполнения кода в основной процедуре вызываю:

    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/

  3. Коллекция цветов гистограмм и колонок форматирования в модуле объекта книги:

Sub Workbook_Open()

    Call ЗаполнитьПредопределенныеЗначения

End Sub

Файлы обработки:

-



Back to top