Вопрос или проблема
У меня есть таблица Excel с примерно 55 листами. Большинство листов содержат более 100 строк. Я создаю список чисел, которые соответствуют определенному критерию. Я прошелся по таблице и выделил все нужные мне числа красным цветом. Теперь я хочу составить список уникальных значений.
Я хотел бы создать какую-то формулу/макрос/CV-код, который пройдет по каждому из 55 листов и перечислит все числа, выделенные красным. Есть ли способ сделать это, не просматривая каждый из листов, не копируя красные ячейки и не вставляя их в список?
(Следующим шагом будет устранение повторяющихся значений, но, вероятно, я справлюсь с этим шагом.)
Может быть, что-то вроде этого? Оно будет искать на всех листах все использованные ячейки и строки. Те, у которых фон красный, будут скопированы в массив и затем выведены на новый лист. Я не стал включать сортировку и удаление повторяющихся значений. Это не должно быть слишком сложно. Кстати, вывод будет в одной строке, возможно, вам будет удобнее выводить его в колонке. Вероятно, создав двумерный массив с одним значением на строку.
Думаю, если это можно сделать с помощью Application.FindFormat…
Sub FindReds()
Dim myArray() As Variant
Dim i As Integer
Dim outWS As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each Cell In ws.UsedRange.Cells
If Cell.Interior.ColorIndex = 3 Then
ReDim Preserve myArray(i)
myArray(i) = Cell.Value
i = i + 1
End If
Next
Next
Set outWS = ActiveWorkbook.Sheets.Add()
outWS.[A1].Resize(1, UBound(myArray)) = myArray
End Sub
Ответ или решение
Чтобы выполнить задачу по созданию списка уникальных значений из выделенных красным цветом ячеек в Excel, можно использовать макрос на языке VBA. Это эффективное решение позволит автоматически пройти по всем 55 листам вашей рабочей книги и извлечь необходимые данные без ручного копирования. Ниже приведен подробный план действий и код для реализации этой задачи.
Пошаговое руководство:
-
Откройте редактор VBA:
- Нажмите
ALT + F11, чтобы открыть редактор VBA в Excel.
- Нажмите
-
Создание модуля:
- В редакторе VBA выберите
Вставка>Модуль, чтобы создать новый модуль.
- В редакторе VBA выберите
-
Вставка кода VBA:
- Скопируйте и вставьте приведённый ниже код в созданный модуль.
-
Изменение и запуск:
- Убедитесь, что все данные правильно проиндексированы и нет ошибок. Запустите макрос, нажав
F5или через меню.
- Убедитесь, что все данные правильно проиндексированы и нет ошибок. Запустите макрос, нажав
-
Обработка результатов:
- Как только список будет сгенерирован на новом листе, вы сможете применить функции Excel для удаления дубликатов.
Код для макроса:
Sub НайтиКрасныеЯчейки()
Dim myArray() As Variant
Dim i As Integer
Dim outWS As Worksheet
Dim ws As Worksheet
Dim Cell As Range
' Инициализация счетчика
i = 0
' Проход по всем листам в книге
For Each ws In ActiveWorkbook.Worksheets
' Проход по всем ячейкам в используемом диапазоне текущего листа
For Each Cell In ws.UsedRange.Cells
' Проверка цвета ячейки
If Cell.Interior.ColorIndex = 3 Then
' Сохранение значения в массив
ReDim Preserve myArray(i)
myArray(i) = Cell.Value
i = i + 1
End If
Next Cell
Next ws
' Добавление нового листа для вывода результата
Set outWS = ActiveWorkbook.Sheets.Add()
' Вывод массива значений в одну строку (оставьте как есть или измените на вывод в колонку при необходимости)
outWS.[A1].Resize(1, UBound(myArray) + 1) = myArray
End SubОптимизация и улучшение:
- Удаление дубликатов: после создания списка воспользуйтесь функцией Excel "Удалить дубликаты", чтобы оставить только уникальные значения.
- Вывод в столбец: если необходимо, измените вывод вручную, чтобы значения отображались в одном столбце.
- Улучшение производительности: при обработке большого количества данных возможно стоит задуматься об оптимизации, например, за счет использования
ScreenUpdatingиCalculation(отключения обновления экрана и восстановления вычислений после завершения).
Этот подход не только автоматизирует задачу, но и экономит ваше время и усилия, избавляя от необходимости ручной обработки данных.