Как я могу извлечь все значения ячеек (а также их идентификаторы ячеек), которые имеют определенный цвет?

#r #excel #filter

#r #excel #Фильтр

Вопрос:

У меня есть набор данных, который выглядит следующим образом.

Я хочу создать другой набор данных, который имеет только два столбца:

(1) Значение / текст всех ячеек, которые являются красными (2) Исходный идентификатор ячейки, связанный с каждым значением / текстом

По сути, это должно выглядеть так.

Я предполагаю, что есть простой способ сделать это, но по какой-то причине я не смог заставить его работать в Excel (возможно, это потому, что я использую онлайн-версию Excel, но я не уверен). Я также был бы рад сделать это с помощью R, если это проще.

Заранее благодарю вас!

p.s. Извините за странно обрезанный набор данных (сделано для конфиденциальности).

1. Готовы ли вы использовать VBA? Потому что вы не можете проверить внутренний цвет ячейки, используя только формулы.

2. Я думаю , что пакет tidyxl может быть полезен для работы с файлами Excel с данными / информацией, введенными в качестве форматирования; однако потребуется некоторая обработка.

3. @Lucas да, я готов это сделать. У вас есть какие-либо дополнительные советы?

4. @joran Не могли бы вы рассказать о том, как я могу использовать tidyxl для достижения своей цели?

5. Я никогда не использовал его сам, я только знаю, что он существует.

Ответ №1:

Поскольку вы готовы использовать VBA и попросили дальнейших советов, я написал приведенный ниже код в качестве руководства, чтобы вы могли изучить, изменить его и достичь желаемого результата.

Предполагая, что у вас есть лист с именем «Цветные данные«, подобный этому:

И лист с именем «Анализ«, подобный этому:

Вы можете запустить этот код:

 Sub CompareAndHighlightDifferences()

Dim w1 As Worksheet, w2 As Worksheet
Dim cell_color As String
Dim cell_address As String
Dim new_row As Long

Set w1 = Sheets("Colored data")
Set w2 = Sheets("Analysis")

With w1
    For Each cel In .UsedRange
        cell_color = cel.Interior.Color
        cell_address = cel.Address
        new_row = w2.Cells(Rows.Count, 1).End(xlUp).Row   1
        w2.Cells(new_row, 1) = cell_address
        With w2.Cells(new_row, 2)
            .Value = cell_color
            .Interior.Color = cell_color
        End With
    Next cel
End With

End Sub

И получить следующие результаты на листе «Анализ«:

Надеюсь, это поможет.

1. Большое вам спасибо! Это чрезвычайно полезно. Я хочу, чтобы мой 2-й столбец включал текстовое значение исходных ячеек… так бы я просто сделал. Значение = cell_value ? Не уверен, какой правильный синтаксис для этого будет (я попытался его погуглить).

2. @Mint поскольку мы повторяем каждый «cel», вам следует заменить «.Value = cell_color» на «.Value = cel.value». В коде «cel» — это просто имя для вызова каждого объекта внутри нашего цикла. Я использовал его вместо «cell», чтобы избежать путаницы с объектом VBA с тем же именем.