☰

Раскрашивание части текста в ячейке в Excel

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
Раскрашивание части текста в ячейке в Excel

В Excel существует несколько способов динамически раскрасить всю ячейку в зависимости от ее значения, но есть ли способ динамически раскрасить только часть ячейки в зависимости от ее значения?

Например, предположим, что я создаю отчет, который выглядит примерно так:

 _________________________
|       |    Dec    | Nov |
|_______|___________|_____|
|Gross R| $75 (-25%)| $100|
|_______|___________|_____|
|Net Inc| $55 (+10%)| $50 |
|_______|___________|_____|

В этом сценарии я хочу раскрасить только процентные значения (-25%) и (+10%), а не долларовые значения $75 и $55, которые также находятся в ячейке. Проблема усугубляется тем, что раскраска должна быть динамической (зеленый для положительных значений, красный для отрицательных), а эти ячейки являются ссылками (поэтому ручная раскраска невозможна).

Я пробовал использовать встроенную TEXT()функцию, но это тоже не сработало. В частности, я пробовал, =TEXT(A1,"$##")&" "&TEXT(A2,"[Green]0%;[Red](-0%)")где A1ссылка на ячейку для суммы в долларах, а A2где ссылка на ячейку для процентной дельты.

Раздражает то, что пользовательское форматирование [Green]0%;[Red](-0%)работает нормально, если применяется ко всей ячейке (через раздел пользовательского форматирования чисел), но когда применяется через TEXT()функцию, оно перестает работать. Итак, как мне сделать цвет части значения в ячейке индивидуальным?

решение1

Я обнаружил два подхода к решению этой проблемы, и ни один из них не оказался оптимальным.

Первый подход заключался в разбиении строк на два отдельных столбца, таким образом я мог использовать одно из ранее описанных пользовательских форматирований для установки его цвета. Это не идеальное решение, поскольку мне пришлось пойти на компромисс относительно «внешнего вида» отчета, чтобы разместить этот дополнительный столбец.

Второй подход заключается в использовании VBA/макросов, которые, хотя я и решил избегать в этом конкретном сценарии, были бы вполне подходящими для этой задачи. Хотя я не буду выводить весь рабочий код, он, по сути, сводится к следующему:

  1. Найдите ячейку, которую вы хотите настроить (сквозную ActiveCellили циклическую)
  2. Используйте Instrфункцию, чтобы найти место в строке, где вы хотите изменить цвет.
  3. Если длина текста переменная, используйте Instrеще раз, чтобы найти место в строке, где вы хотите остановить цвет.
  4. Используйте Characters(start, length)функцию, чтобы выделить именно те символы, которые вы хотите изменить, передавая им найденные ранее значения.
  5. Измените цвет с помощьюFont.Color = RGB(r,g,b)

решение2

Пример использования макроса можно найти здесь:

Макрос для раскрашивания части текста в ячейках Excel

Макросы Excel - Цикл For для окрашивания части ячеек

Используйте макрос Excel, содержащий цикл for, чтобы перебрать строки данных о погоде и окрасить часть текста ячейки в красный цвет, если она содержит слово Hot, и в синий цвет, если она содержит слово Cool:

Обратите внимание, что Hot и Cool чувствительны к регистру для Instr.

инструкции

  1. Нажмите на вкладку «Разработчик» в Excel.
  2. Нажмите на значок Visual Basic и скопируйте текст макроса ниже в окно кода.
  3. Щелкните значок Excel, чтобы вернуться к представлению Excel.
  4. Нажмите на значок «Макросы», выберите макрос с именем TextPartColourMacro и нажмите «Выполнить».

Готовый макрос:

Sub TextPartColourMacro()

' Declarations and Initialisation
Dim Row As Integer, Col As Integer
Dim CurrentCellText As String
Col = 1

' Loop Through Rows 2 to 5
For Row = 2 To 5

    ' Get Text in Current Cell
    CurrentCellText = ActiveSheet.Cells(Row, Col).Value
    
    ' Get the Position of the Text Hot and Cool
    HotStartPosition = InStr(1, CurrentCellText, "Hot")
    CoolStartPosition = InStr(1, CurrentCellText, "Cool")
    
    ' Colour the Word Hot Red
    If HotStartPosition > 0 Then
        ActiveSheet.Cells(Row, Col).Characters(HotStartPosition, 3).Font.Color = RGB(255, 0, 0)
    End If
    
    ' Colour the Word Cool Blue
    If CoolStartPosition > 0 Then
        ActiveSheet.Cells(Row, Col).Characters(CoolStartPosition, 4).Font.Color = RGB(0, 0, 255)
    End If

Next Row
End Sub

решение3

Этот макрос окрасит любой выделенный диапазон текста, содержащий скобки, в красный цвет, если текст внутри скобок содержит символ "-", в противном случае он окрасится в зеленый цвет. Весь текст за пределами скобок останется неизменным.

Dim rngCell As Range
Dim CharCount As Integer
Dim BracketBegin As Integer
Dim BracketEnd As Integer
Dim strToColour As String

For Each rngCell In Selection
    CharCount = Len(rngCell)
    BracketBegin = InStr(1, rngCell, "(")
    BracketEnd = InStr(1, rngCell, ")")
    With rngCell.Characters(BracketBegin, CharCount - BracketEnd)
        ' Pull out string to check for +/-
        strToColour = rngCell.Characters(BracketBegin, CharCount - BracketEnd).Text
        ' If negative, colour red, otherwise green
        If InStr(strToColour, "-") Then
            .Font.Color = vbRed
        Else
            .Font.Color = vbGreen
        End If
  End With
Next rngCell
End Sub

[Example][1]


  [1]: https://i.stack.imgur.com/4lyN4.png

Связанный контент