Алексей Гладкий - Excel. Трюки и эффекты

После запуска макроса на экране отобразится окно, в котором будет указано количество печатных страниц всех листов текущей рабочей книги.

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

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

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

Данный раздел посвящен описанию трюков, которые можно выполнять при работе с ячейкой либо с диапазоном выделенных ячеек.

Чтобы быстро заполнить ячейку содержимым ячейки, расположенной выше, следует нажать сочетание клавиш CtrL+D. Если же копируемая ячейка включает в себя формулу, то она будет скопирована с соблюдением относительных адресов. Рассмотрим это на конкретном примере.

Введем в ячейки с А1 по A3 значения 5, 10 и 15 соответственно, а в ячейки Bl, В2 и ВЗ – значения 3 0, 50 и 7 0. После этого в ячейку С1 введем формулу =СУММ (А1: В1) и нажмем Enter – в данной ячейке в соответствии с формулой отобразится значение 35. Если же теперь мы установим курсор в ячейки С2 и нажмем CtrL+D, то в ней отобразится значение 60 (то есть сумма ячеек А2 и В 2), а формула скопируется в следующем виде: =СУММ (А2: В2). При нажатии сочетания CtrL+D в ячейке C3 формула скопируется следующим образом: =СУММ (A3: ВЗ), и результат будет равен 85.

В Microsoft Excel с помощью несложного трюка можно вводить данные только в заранее определенные ячейки. Рассмотрим это на конкретном примере.

Допустим, нам необходимо ввести данные только в ячейки C3:С5 и А1:А4. Для этого следует выделить мышью диапазон C3:С5, а затем, нажав клавишу Ctrl, – диапазон А1:А4. Теперь, не снимая выделения, в ячейках с Al по А4 последовательно вводим необходимые значения. После того как заполнена ячейка А4 и нажата клавиша Enter, курсор автоматически перейдет в ячейку C3. После заполнения ячеек C3:С5 и нажатия Enter курсор опять перейдет в ячейку А1. После ввода всех необходимых данных можно снять выделение.

Для ввода данных в определенные ячейки можно использовать различные макросы. Пример одного из таких макросов приведен в листинге 2.16.