Автоматический пересчет данных таблицы при изменении ее значений
Часто в процессе работы возникает необходимость пересортировки данных таблицы в зависимости от того, как они изменяются. Этот процесс целесообразно автоматизировать (особенно при работе с большими объемами информации). Рассмотрим решение этой проблемы на конкретном примере.
Предположим, что в таблице в ячейках с 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
Теперь все данные таблицы будут оформлены в соответствии с нашими требованиями. При внесении в таблицу изменений она будет автоматически переформирована.