Excel программно задать формат ячейки
Библиотека JavaScript Excel предоставляет API для применения условного форматирования к диапазонам данных в книгах. Эта функция упрощает визуальный анализ больших наборов данных. Форматирование также динамически обновляется с учетом изменений в диапазоне.
В этой статье рассматривается условное форматирование в контексте надстроек JavaScript для Excel. В указанных ниже статьях представлены подробные сведения о всех возможностях условного форматирования в Excel.
Программное управление условным форматированием
Свойство Range.conditionalFormats — это коллекция объектов ConditionalFormat, применяемых к диапазону. Объект ConditionalFormat содержит несколько свойств, определяющих применяемый формат на основе ConditionalFormatType.
- cellValue
- colorScale
- custom
- dataBar
- iconSet
- preset
- textComparison
- topBottom
У каждого из этих свойств форматирования есть соответствующий вариант *OrNullObject . Дополнительные статьи об этом шаблоне в разделе * Методы OrNullObject.
Для объекта ConditionalFormat можно установить только один тип формата. Это определено свойством type , которое является значением перечисления объекта ConditionalFormatType. Параметр type устанавливается при добавлении условного форматирования к диапазону.
Создание правил условного форматирования
Условное форматирование добавляется к диапазону с помощью conditionalFormats.add . После добавления можно задать свойства, относящиеся к условному форматированию. В примерах ниже показано создание различных типов форматирования.
Значение ячейки
При условном форматировании значения ячейки применяется пользовательский формат на основе результатов одной или двух формул в ConditionalCellValueRule. Свойство operator является оператором ConditionalCellValueOperator, который определяет, как итоговое выражение связано с форматированием.
В приведенном ниже примере показано применение красного шрифта ко всем значениям диапазона, которые меньше нуля.
Цветовая шкала
При условном форматировании с использованием цветовой шкалы применяется цветовой градиент в диапазоне данных. Свойство criteria в ColorScaleConditionalFormat определяет три точки ConditionalColorScaleCriterion: minimum , maximum и (при желании) midpoint . У каждой точки условия есть три свойства:
- color — HTML-код цвета для конечной точки.
- formula — число или формула, представляющая значение конечной точки. Оно будет равным null , если type имеет значение lowestValue или highestValue .
- type — способ оценки формулы. highestValue и lowestValue относятся к значениям в форматируемом диапазоне.
В приведенном ниже примере показан диапазон, окрашенный с переходом от синего к желтому и красному цвету. Обратите внимание, что minimum и maximum являются минимальным и максимальным значением соответственно, и для них используются формулы null . Для значения midpoint используется тип percentage с формулой "=50" , чтобы самая желтая ячейка соответствовала среднему значению.
Пользовательское
При пользовательском условном форматировании применяется пользовательский формат к ячейкам на основе формулы произвольной сложности. Объект ConditionalFormatRule позволяет определять формулу в разных нотациях:
- formula — стандартная нотация.
- formulaLocal - Локализовано на основе языка пользователя.
- formulaR1C1 — нотация R1C1.
В приведенном ниже примере зеленым цветом окрашен шрифт ячеек с более высокими значениями, чем в ячейках слева.
Гистограмма
При условном форматировании с использованием гистограмм они добавляются к ячейкам. По умолчанию минимальное и максимальное значения в диапазоне создают границы и пропорциональные размеры гистограмм. Объект DataBarConditionalFormat обладает несколькими свойствами для управления внешним видом панели.
В приведенном ниже примере используется форматирование с помощью гистограмм с заполнением слева направо.
Набор значков
При условном форматировании с набором значков используются значки Excel для выделения ячеек. Свойство criteria — это массив объекта ConditionalIconCriterion, определяющий добавляемый символ и условия для добавления. Этот массив автоматически заполняется элементами условия со свойствами по умолчанию. Отдельные свойства не могут быть перезаписаны. Вместо этого необходимо заменить весь объект условия.
В приведенном ниже примере показано применение в диапазоне набора из трех значков с треугольниками.
Готовые условия
При условном форматировании с готовыми условиями применяется пользовательский формат к диапазону на основе выбранного стандартного правила. Эти правила определяются с помощью ConditionalFormatPresetCriterion в ConditionalPresetCriteriaRule.
В следующем примере цвет шрифта белый, где значение ячейки по крайней мере на одно стандартное отклонение выше среднего диапазона.
Сравнение текста
При условном форматировании со сравнением текста используется сравнение строк в качестве условия. Свойство rule является объектом ConditionalTextComparisonRule, определяющим строку для сравнения с ячейкой и оператор для указания типа сравнения.
В следующем примере форматы цвета шрифта красный, когда текст ячейки содержит "Задержки".
Верхнее или нижнее значение
При условном форматировании верхнего или нижнего значения применяется форматирование к наибольшему или наименьшему значению в диапазоне. Свойство rule , являющееся типом ConditionalTopBottomRule, указывает основание для условия (максимальное или минимальное значение), а также применение ранжированной или процентной оценки.
В приведенном ниже примере применяется зеленое выделение к ячейке с максимальным значением в диапазоне.
Разные форматирования и приоритет
К диапазону можно применять несколько типов условного форматирования. Если форматы содержат конфликтующие элементы, например разный цвет шрифта, только один формат применяет этот конкретный элемент. Приоритет определяется свойством ConditionalFormat.priority . Приоритет — это число (равное индексу в ConditionalFormatCollection ), которое можно установить при создании формата. Чем ниже значение priority , тем выше приоритет формата.
В приведенном ниже примере показан выбор цвета шрифта при конфликте между двумя форматами. Для отрицательных чисел применяется полужирный шрифт, но НЕ красный, так как приоритет получает формат, устанавливающий для них синий цвет шрифта.
Взаимоисключающие условные форматирования
Свойство stopIfTrue объекта ConditionalFormat не позволяет применять к диапазону условное форматирование с более низким приоритетом. Если при сопоставлении с диапазоном применяется условное форматирование со свойством stopIfTrue === true , последующие условные форматирования не применяются, даже если их элементы не вступают в противоречие.
В приведенном ниже примере показано добавление в диапазон двух условных форматов. Для отрицательных чисел будет использоваться синий шрифт со светло-зеленым фоном, независимо от того, выполняются ли условия другого формата.
Объект Range в VBA Excel представляет диапазон ячеек. Он (объект Range) может описывать любой диапазон, начиная от одной ячейки и заканчивая сразу всеми ячейками рабочего листа.
- Одна ячейка – Range("A1") .
- Девять ячеек – Range("A1:С3") .
- Весь рабочий лист в Excel 2016 – Range("1:1048576") .
В VBA Excel есть свойство Cells объекта Range, которое позволяет обратиться к одной ячейке в указанном диапазоне (возвращает объект Range в виде одной ячейки). Если в коде используется свойство Cells без указания диапазона, значит оно относится ко всему диапазону активного рабочего листа.
Примеры обращения к одной ячейке:
- Cells(1000) , где 1000 – порядковый номер ячейки на рабочем листе, возвращает ячейку «ALL1».
- Cells(50, 20) , где 50 – номер строки рабочего листа, а 20 – номер столбца, возвращает ячейку «T50».
- Range("A1:C3").Cells(6) , где «A1:C3» – заданный диапазон, а 6 – порядковый номер ячейки в этом диапазоне, возвращает ячейку «C2».
Подробнее о том, как обратиться к ячейке, смотрите в статье: Ячейки (обращение, запись, чтение, очистка).
В этой статье мы рассмотрим свойства объекта Range, применимые, в том числе, к диапазону, состоящему из одной ячейки.
Еще надо добавить, что свойства и методы объектов отделяются от объектов точкой, как в третьем примере обращения к одной ячейке: Range("A1:C3").Cells(6) .
Свойства ячейки (объекта Range)
| Свойство | Описание |
|---|---|
| Address | Возвращает адрес ячейки (диапазона). |
| Borders | Возвращает коллекцию Borders, представляющую границы ячейки (диапазона). Подробнее… |
| Cells | Возвращает объект Range, представляющий коллекцию всех ячеек заданного диапазона. Указав номер строки и номер столбца или порядковый номер ячейки в диапазоне, мы получаем конкретную ячейку. Подробнее… |
| Characters | Возвращает подстроку в размере указанного количества символов из текста, содержащегося в ячейке. Подробнее… |
| Column | Возвращает номер столбца ячейки (первого столбца диапазона). Подробнее… |
| ColumnWidth | Возвращает или задает ширину ячейки в пунктах (ширину всех столбцов в указанном диапазоне). |
| Comment | Возвращает комментарий, связанный с ячейкой (с левой верхней ячейкой диапазона). |
| CurrentRegion | Возвращает прямоугольный диапазон, ограниченный пустыми строками и столбцами. Очень полезное свойство для возвращения рабочей таблицы, а также определения номера последней заполненной строки. |
| EntireColumn | Возвращает весь столбец (столбцы), в котором содержится ячейка (диапазон). Диапазон может содержаться и в одном столбце, например, Range("A1:A20") . |
| EntireRow | Возвращает всю строку (строки), в которой содержится ячейка (диапазон). Диапазон может содержаться и в одной строке, например, Range("A2:H2") . |
| Font | Возвращает объект Font, представляющий шрифт указанного объекта. Подробнее о цвете шрифта… | HorizontalAlignment | Возвращает или задает значение горизонтального выравнивания содержимого ячейки (диапазона). Подробнее… |
| Interior | Возвращает объект Interior, представляющий внутреннюю область ячейки (диапазона). Применяется, главным образом, для возвращения или назначения цвета заливки (фона) ячейки (диапазона). Подробнее… |
| Name | Возвращает или задает имя ячейки (диапазона). |
| NumberFormat | Возвращает или задает код числового формата для ячейки (диапазона). Примеры кодов числовых форматов можно посмотреть, открыв для любой ячейки на рабочем листе Excel диалоговое окно «Формат ячеек», на вкладке «(все форматы)». Свойство NumberFormat диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковый числовой формат. Если нужно присвоить ячейке текстовый формат, записывается так: Range("A1").NumberFormat = "@" . |
| Offset | Возвращает объект Range, смещенный относительно первоначального диапазона на указанное количество строк и столбцов. Подробнее… |
| Resize | Изменяет размер первоначального диапазона до указанного количества строк и столбцов. Строки добавляются или удаляются снизу, столбцы – справа. Подробнее… |
| Row | Возвращает номер строки ячейки (первой строки диапазона). Подробнее… |
| RowHeight | Возвращает или задает высоту ячейки в пунктах (высоту всех строк в указанном диапазоне). |
| Text | Возвращает форматированный текст, содержащийся в ячейке. Свойство Text диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковое содержимое и один формат. Предназначено только для чтения. |
| Value | Возвращает или задает значение ячейки, в том числе с отображением значений в формате Currency и Date. Тип данных Variant. Value является свойством ячейки по умолчанию, поэтому в коде его можно не указывать. |
| Value2 | Возвращает или задает значение ячейки. Тип данных Variant. Значения в формате Currency и Date будут отображены в виде чисел с типом данных Double. | VerticalAlignment | Возвращает или задает значение вертикального выравнивания содержимого ячейки (диапазона). Подробнее… |
В таблице представлены не все свойства объекта Range. С полным списком вы можете ознакомиться не сайте разработчика.
Простые примеры для начинающих
Вы можете скопировать примеры кода VBA Excel в стандартный модуль и запустить их на выполнение. Как создать стандартный модуль и запустить процедуру на выполнение, смотрите в статье VBA Excel. Начинаем программировать с нуля.
Учтите, что в одном программном модуле у всех процедур должны быть разные имена. Если вы уже копировали в модуль подпрограммы с именами Primer1, Primer2 и т.д., удалите их или создайте еще один стандартный модуль.
Форматирование ячеек
Заливка ячейки фоном, изменение высоты строки, запись в ячейки текста, автоподбор ширины столбца, выравнивание текста в ячейке и выделение его цветом, добавление границ к ячейкам, очистка содержимого и форматирования ячеек.
Если вы запустите эту процедуру, информационное окно MsgBox будет прерывать выполнение программы и сообщать о том, что произойдет дальше, после его закрытия.
Всем доброго дня! Выгружаю из БД столбец с числовыми значениями. Попадаются как целые, так и дробные. Вопрос - как задать формат ячейки, чтобы запятая показывалась только при наличии дробной части. Интересует как задать NumberFormat?
__________________Помощь в написании контрольных, курсовых и дипломных работ здесь
Скопировать диапазон ячеек одной книги Excel в другую книгу Excel
Из темы все ясно. Не могу разобраться никак. Можно ли открыть одну книгу, скопировать диапазон.
Форматирование ячеек Excel
Добрый день! Подскажите, как программно форматировать данные в ячейках Excel? Интересует заливка.
Копирование ячеек Excel
Доброго дня, столкнулся с проблемой, не могу понять почему выпадает исключение, собственно есть.
Формат ячейки при записи в файл Excel
Код, записывает данные из таблиц в файл Excel. В таблице есть инн, и он не корректно.
просто установкой формата - никак. такое получить - нужно проходиться по всем ячейкам и к целым применять один формат, к дробным - другой
в вашем случае все равно NumberFormat ничего не даст, т.к. вы ячейке присваиваете текст, а не число и он форматироваться не будет. Вам нужно сначала конвертировать значение в число, а уж потом устанавливать формат ячейки
Добавлено через 8 минут
Чтение значений ячеек Excel
Добрый день всем. Прошу помочь в решении задачи. В Сети много информации по работе с Excel через.
Поиск объединенных ячеек в Excel
В Excel файле есть объединенные ячейки, наряду с ними есть обычные. Объединение вертикальное. Если.
Выделение диапозона ячеек в Excel
Здравствуйте. У меня такая проблема: мне нужно выделить 5 рядом стоящих ячеек в одной строке. Когда.
Удаление со сдвигом ячеек в Excel
Как можно удалить со сдвигом при этом до первой ячейки где есть символы, вот это сдвигает один раз.
На практике часто случается, что нужно решить какую-либо задачу, иногда довольно тривиальную с точки зрения пользователя Excel (к примеру изменить фон ячейки в Excel, добавить левую границу и т.д.), а решение в сети/документации искать не хочется или не получается, тогда можно попробовать начать запись макроса, выполнить нужную последовательность действий (изменить фон, добавить границу) , после чего записать макрос и открыть код VBA для анализа кода, названий свойств и методов. Такой способ довольно часто помогает быстро решить проблему.
1. Подключение к Excel
При подключении к Excel не забываем закрывать приложение после работы дабы не наплодить кучу ненужных процессов. Они также могут появиться если во время выполнения модуля выскочит ошибка до того как система дойдет до строк, где происходит закрытие Excel.
2. Запись значения в ячейку
Количество листов книги
3. Шрифт и размер шрифта
4. Ширина колонки
Чтобы скрыть колонку, нужно установить ширину в значение 0.
Получение последней ячейки в используемом диапазоне
Например, при открытии таблицы нам нужно узнать до какой строки и/или до какого столбца присутствуют заполненные ячейки.
4. Установка фильтра
5. Горизонтальное выравнивание ячейки
Режим выравнивания
Константа в Excel
Значение в ISBL
По центру выделения
По правому краю
6. Вертикальное выравнивание ячейки
Режим выравнивания
Константа в Excel
Значение в ISBL
По нижнему краю
По верхнему краю
7. Установка свойства ячейки "переносить по словам"
8. Отключение/включение режима показа предупреждений
9. Формулы
Для формул с русскими названиями нужно использовать не Formula, а FormulaLocal. Таким образом правильными вариантами написания формул будут:
10. Таблица цветов с индексами
11. Установка границы ячейки и тип линии границы
Значения константы XlBordersIndex
Значение в Excel
Значение в ISBL
В виде тире и точек
В виде тире и двойных точек
В виде двойной линии
В виде наклонной пунктирной
Свойство LineStyle (тип линии) может принимать следующие значения
Расположение линии
Значение в Excel
Значение в ISBL
Линия по диагонали сверху – вниз
Линия по диагонали снизу – вверх
Линия, обрамляющая диапазон слева
Линия, обрамляющая диапазон сверху
Линия, обрамляющая диапазон снизу
Линия, обрамляющая диапазон справа
Все вертикальные линии внутри диапазона
Все горизонтальные линии внутри диапазона
Значения константы XlBordersIndex
| Расположение линии | Значение в Excel | Константа в ISBL |
|---|---|---|
| Линия по диагонали сверху вниз | xlDiagonalDown | 5 |
| Линия по диагонали снизу – вверх | xlDiagonalUp | 6 |
| Линия, обрамляющая диапазон слева | xlEdgeLeft | 7 |
| Линия, обрамляющая диапазон сверху | xlEdgeTop | 8 |
| Линия, обрамляющая диапазон снизу | xlEdgeBottom | 9 |
| Линия, обрамляющая диапазон справа | xlEdgeRight | 10 |
| Все вертикальные линии внутри диапазона | xlInsideVertical | 11 |
| Все горизонтальные линии внутри диапазона | xlInsideHorizontal | 12 |
Свойство LineStyle (тип линии) может принимать следующие значения:
| Тип линии | Значение в Excel | Константа в ISBL |
|---|---|---|
| Нет линии | xlLineStyleNone | 0 |
| Непрерывная | xlContinuous | 1 |
| В виде тире и точек | xlDashDot | 4 |
| В виде тире и двойных точек | xlDashDotDot | 5 |
| В виде точек | xlDot | 8 |
| В виде двойной линии | xlDouble | 9 |
| В виде наклонной пунктирной | xlSlantDashDot | 13 |
Получение порядкового номера колонки Excel по буквенному обозначению имени
Однажды столкнулся с тем, что нужно было получить буквенное обозначение колонки Excel по её порядковому номеру. Например, для колонки номер 3 буквенное обозначение равно "C". Так вот ниже приведены две замечательные функции, которые вполне справляются с решением данной проблемы.
Наоборот, получение буквенного обозначения имени колонки по номеру колонки
Читайте также: