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

21.01.12

Разработка - Универсальные функции

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

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

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

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

  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

См. также

Вставляем картинку из буфера обмена (платформа 1С 8.3.24)

Универсальные функции Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Задача: вставить картинку из буфера обмена на форму средствами платформы 1С.

1 стартмани

18.03.2024    2683    1    John_d    8    

55

GUID в 1С 8.3 - как с ними быть

Универсальные функции Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Пришлось помучиться с GUID-ами немного, решил поделиться опытом, мало ли кому пригодится.

12.02.2024    4617    atdonya    22    

45

Переоткрытие внешних обработок

Универсальные функции Платформа 1С v8.3 Бесплатно (free)

На заключительных этапах, когда идет отладка или доработка интерфейса, необходимо много раз переоткрыть внешний объект. Вот один из способов автоматизации этого.

30.11.2023    3965    ke.92@mail.ru    16    

61

Валидация JSON через XDTO (включая массивы)

WEB-интеграция Универсальные функции Механизмы платформы 1С Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

При работе с интеграциями рано или поздно придется столкнуться с получением JSON файлов. И, конечно же, жизнь заставит проверять файлы перед тем, как записывать данные в БД.

28.08.2023    8848    YA_418728146    6    

141

Печать непроведенных документов для УТ, КА, ERP. Настройка печати по пользователям, документам и печатным формам

Пакетная печать Печатные формы Адаптация типовых решений Универсальные функции Платформа 1С v8.3 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Россия Абонемент ($m)

Расширение для программ 1С:Управление торговлей, 1С:Комплексная автоматизация, 1С:ERP, которое позволяет распечатывать печатные формы для непроведенных документов. Можно настроить, каким пользователям, какие конкретные формы документов разрешено печатать без проведения документа.

2 стартмани

22.08.2023    2078    21    progmaster    7    

3

Расширение: Быстрые отборы через буфер [Alt+C] Копировать список, [Alt+V] Вставить список, [Ctrl+C] Копировать из файлов

Инструментарий разработчика Универсальные функции Платформа 1С v8.3 Конфигурации 1cv8 1С:Розница 2 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Зарплата и Управление Персоналом 3.x Абонемент ($m)

Копирует в буфер значения из списков, из ячеек отчетов, таблиц, настроек списков, других отборов и вставляет в выбранную настройку отбора. Работает с Объект не найден. Работает как в одной так и между разными базами 1С. Использует комбинации [Alt+C] Копировать список, [Alt+V] Вставить список. Также для копирования данных используется стандартная [Ctrl+C] (например из открытого xls, mxl, doc и т.п. файла скопировать список наименований)

1 стартмани

13.10.2022    16157    133    sapervodichka    112    

129

Система контроля ведения учета [БСП]

Универсальные функции Механизмы типовых конфигураций БСП (Библиотека стандартных подсистем) Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

В данном материале рассмотрим типовой алгоритм подсистемы контроля учета БСП в конфигурациях на примерах.

18.07.2022    7243    quazare    8    

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