gifts2017

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

Опубликовал Olvia в раздел Программирование - Практика программирования

Дабы не ломать голову над «мегараскрашиваниями» средствами 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

Скачать файлы

Наименование Файл Версия Размер
УсловноеФорматирование.xlsm 29
.xlsm 53,78Kb
20.01.12
29
.xlsm 53,78Kb Скачать

См. также

Подписаться Добавить вознаграждение
Комментарии
1. anry mc (AnryMc) 21.01.12 08:49
Я встречал здесь как это сделать на 8.х-ке...
Чисто на 1С. Без внешних компонент
2. Павел (Pavel777777) 21.01.12 11:08
Спасибо, интересный вариант... А правильно ли я понял общую схему работы в этом случае? -
Есть шаблон файла Excel с записанными макросами >>> средствами 1с в ячейках этого файла заполняются данные - запускаются макросы >>> сохраняется файл
3. Olvia 21.01.12 17:56
(2) Pavel777777, да, Вы все абсолютно правильно поняли, единственное... если надумаете использовать Excel-автофильтр - средствами VBA нельзя (на текущий момент) отловить событие "ПослеПримененияАвтофильтра" (например, сортировки строк), поэтому приходится вешать макрос пересчета форматирования на Worksheet_Calculate() соответствующего листа, а значит, чтобы не выгружать данные на следующую страницу целую вечность (на объеме от 10-ти строк - зациклится), надо работать с .ActiveSheet и перед выгрузкой сделать активной ее
4. Olvia 21.01.12 18:02
(1) AnryMc, какую внешнюю компоненту Вы имеете ввиду? Во вложении - Excel-файл, как пример работы с автофильтром, т. к. в этом случае возникает необходимость программного поиска исходных областей для применения условного форматирования
5. anry mc (AnryMc) 21.01.12 18:31
(4) Olvia,
Я имел ввиду, что в ячейках таблицы в 1С можно сделать "гистограмму" только средствами 1С без внешних компонент. Т.е. получить тот же результат нагладности без выгрузки в Excel.
6. Olvia 22.01.12 00:13
(5) AnryMc, да, действительно, есть такой вид универсального элемента управления ))) Я все-таки думаю, его нельзя применить к моей задаче: итоговый результат таки потеряет в визуализации... Если для "значков" еще можно организовать отдельные колонки, то что делать с количеством конечных объектов и размещением их в одной таблице со сторонними показателями? В любом случае, было бы интересно посмотреть на 1С-ную реализацию, пожалуйста, если вспомните, где видели, скиньте ссылку
7. anry mc (AnryMc) 23.01.12 12:07
(6) Olvia,
Нашёл - http://infostart.ru/public/80628/ "Микрографики в табличной части"
Если еще добавить итоги по списку с отбором (или динамическому) то можно все это делать в 1С
8. Olvia 24.01.12 11:59
(7) AnryMc, а... так вот почему мне самостоятельно не удалось найти - вертикальные рисочки ))))) правильно, зачем нам графические объекты, когда есть спец.кнопки клавиатуры )) Однако, признаю, что мне бы и в голову не пришло решать поставленную задачу подобным способом, автору - однозначно "плюс" за творчество!
Не поленившись скачать и затестить обработку, вывод следующий: используя 4 цвета, 50 строк данных, 2 вида показателей с двумя группировками и одной колонкой значков, вижу заметные тормоза при пересчете (использую ПриПолученииДанных вместо ПриВыводеСтроки, шаблон строки перед обходом таблицы, глобальные переменные цвета и значков), поэтому, к сожалению, в моем случае такое элегантное решение применить нельзя... Что же касается фильтра: реализовать отбор по цвету ячейки или части слова не составляет труда, но при прочих указанных условиях выполнение задачи таким способом кажется мне долговременным и даже в какой-то степени извращенским (( В любом случае, спасибо, AnryMc, за интересную ссылку
9. anry mc (AnryMc) 24.01.12 13:11
(8) Olvia,
Я сослался на идею. Алгоритм тормоз - согласен. Построение в цикле...
Я бы например формировал "Шаблонную строку" при установке Маштаба, а затем Лев(ШаблоннаяСтрока, Значение)
Бысродействие вырастет в разы...
Или еще как - не думал еще
10. igor sarafanov (ms200999) 25.01.12 08:38
Познавательно, спасибо.