Автоматический пересчет данных таблицы при изменении ее значений

Часто в процессе работы возникает необходимость пересортировки данных таблицы в зависимости от того, как они изменяются. Этот процесс целесообразно автоматизировать (особенно при работе с большими объемами информации). Рассмотрим решение этой проблемы на конкретном примере.

Предположим, что в таблице в ячейках с Al по All содержится перечень торговых точек (магазинов), а в ячейках с В1 по В11 – дневная выручка каждой торговой точки. Нам нужно выделить красным жирным шрифтом максимальное значение, а синим – минимальное. Остальные данные отображаются шрифтом, используемым по умолчанию. Нам также нужно пометить желтым цветом ячейки, отображающие выручку выше средней всех торговых точек. При этом необходимо сделать так, чтобы таблица соответствующим образом автоматически переформировывалась при внесении в нее изменений. Решить эту задачу поможет код, приведенный в листинге 2.15 (этот код должен быть помещен в модуль рабочего листа).

Листинг 2.15. Переформирование таблицы

Sub Worksheet_Change(ByVal Target As Range)

Dim rgData As Range

Dim cell As Range

Dim dblMax As Double, dblMin As Double, dblAverage As Double

' Получение контролируемого диапазона ячеек

Set rgData = Range(«B2:B11»)

' Проверка, не входит ли измененная ячейка в контролируемый _

диапазон

If Not (Application.Intersect(Target, rgData) Is Nothing) Then

If Application.WorksheetFunction.CountA(rgData) > 0 Then

' Изменена ячейка из контролируемого диапазона

' Заново рассчитываем минимальное, максимальное и среднее _

значения в контролируемом диапазоне ячеек

dblMin = Application.WorksheetFunction.Min(rgData)

dblMax = Application.WorksheetFunction.Max(rgData)

dblAverage = Application.WorksheetFunction.

Average(rgData)

' Проверяем каждую ячейку из контролируемого диапазона _

и изменяем цвет шрифта ячеек с минимальным и максимальным _

значениями, а также помечаем желтым цветом ячейки _

со значениями больше среднего

For Each cell In rgData

If cell.Value = dblMax Then

' Ячейку с максимальным значением выделим красным цветом

cell.Font.Bold = True

cell.Font.Color = RGB(255, 0, 0)

ElseIf cell.Value = dblMin Then

' Ячейку с минимальным значением выделим синим

цветом

cell.Font.Bold = False

cell.Font.Color = RGB(0, 0, 255)

Else

cell.Font.Bold = False

cell.Font.Color = RGB(0, 0, 0)

End If

If cell.Value > dblAverage Then

' Значение в ячейке больше среднего – выделим ее _

желтым цветом

cell.Interior.Color = RGB(255, 255, 0)

Else

cell.Interior.ColorIndex = xlNone

End If

Next

Else

rgData.Interior.ColorIndex = xlNone

End If

End If

End Sub

Теперь все данные таблицы будут оформлены в соответствии с нашими требованиями. При внесении в таблицу изменений она будет автоматически переформирована.