Текст книги "Excel. Трюки и эффекты"

Если рабочий лист Excel содержит большой объем информации, то целесообразно присвоить имена его произвольным областям для быстрого перехода по ним. Данный процесс называется созданием закладок.

Чтобы создать закладку, нужно выделить произвольный диапазон (несколько ячеек) и выполнить команду контекстного меню Имя диапазона. В результате откроется окно, изображенное на рис. 2.1.

Рис. 2.1. Окно Создание имени

В данном окне в поле Имя с клавиатуры следует ввести имя создаваемой закладки и нажать кнопку ОК.

При создании закладки необходимо соблюдать следующие правила.

• В имени закладки должно содержаться не более 255 символов.

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

• Если имя закладки состоит из нескольких слов, то они должны разделяться символом подчеркивания (не пробелом).

Чтобы проверить работоспособность созданной закладки, нужно установить курсор в любое место рабочего листа и нажать сочетание клавиш Ctrl+G. При этом откроется окно, показанное на рис. 2.2.

Рис. 2.2. Окно Переход

В данном окне нужно установить курсор на имя требуемой закладки (в нашем примере – Область!.) и нажать кнопку ОК. В результате Excel перейдет к соответствующей области рабочего листа.

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

1. Открыть рабочую книгу.

2. Перейти на Лист2 и в ячейке С5 ввести значение 100.

3. Перейти на Лист1 и в ячейке А2 ввести формулу =Лист2! С5.

4. Нажать Enter.

После выполнения указанных действий в ячейке А2, расположеной на листе Лист1, отобразится значение 100 (то есть то же значение, что и в ячейке С5 на листе Лист2).

При рисовании автофигур иногда возникает необходимость их привязки к сетке Excel. Чтобы использовать данный прием, нужно выбрать фигуру (Вставка → Иллюстрации → Фигуры) и рисовать ее при нажатой клавише Alt. Все стороны фигуры будут выравниваться только по границам ячеек.

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

На рабочем листе нужно найти последнюю строку, содержащую общедоступные данные, и выделить полностью строку, расположенную сразу под ней (для этого нужно нажать кнопку с номером строки). После этого, нажав сочетание клавиш Ctrl+Shift+I, выделить все остальные строки рабочего листа. Теперь после выполнения команды контекстного меню Скрыть выделенный диапазон будет скрыт с рабочего листа. При этом все имеющиеся в этом диапазоне данные сохраняются – чтобы они вновь отобразились, нужно выделить весь рабочий лист (Ctrl+A) и выполнить команду контекстного меню Отобразить.

При необходимости можно запретить использование контекстного меню текущего рабочего листа. Для этого можно воспользоваться таким макросом (листинг 2.10).

Листинг 2.10. Блокировка контекстного меню

Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As

Boolean)

Static intCount As Integer ' Счетчик нажатий кнопки мыши

Dim x As Integer, y As Integer

' Блокировать обработку щелчка правой кнопкой мыши

Cancel = True

' Отображение текстового поля с количеством щелчков правой _

кнопкой мыши

x = Target.Left

y = Target.Top

intCount = intCount + 1

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, _

x, y, 35, 20).TextFrame.Characters.Text = intCount

End Sub

Следует учитывать, что приведенный код должен быть помещен в модуль соответствующего рабочего листа.

Теперь при щелчке правой кнопкой мыши на любом месте текущего рабочего листа контекстное меню будет недоступно. Вместо него на экране будет появляться порядковый номер щелчка правой кнопкой мыши (например, при первой попытке вызова контекстного меню вместо него отобразится 1, при второй попытке – 2 и т. д.).

Однако при этом остается возможность вызова контекстного меню с помощью соответствующей клавиши.

Обычно колонтитулы вставляются с помощью комадны Вставка → Текст → Колонтитулы. Однако для этой цели можно также воспользоваться макросом, код которого приведен в листинге 2.11.

Листинг 2.11. Вставка колонтитула

Sub AddPageHeader()

Dim i As Integer

With ThisWorkbook

' Вставка колонтитулов на все листы рабочей книги

For i = 1 To .Worksheets.Count – 1

.Worksheets(i).PageSetup.LeftHeader = .FullName

.Worksheets(i).PageSetup.CenterHeader = Worksheets(i).Name

.Worksheets(i).PageSetup.RightHeader = Now()

Next

End With

End Sub

Следует учитывать, что приведенный код должен быть помещен в модуль ЭтаКнига.

В результате выполнения данного макроса в колонтитуле документа слева будет выведено имя рабочей книги (с указанием полного пути к файлу), в центре – имя текущего рабочего листа, а справа – текущая дата. Увидеть сформированный колонтитул можно в режиме Разметка страницы.

При большом количестве листов в рабочей книге иногда бывает необходимо быстро узнать, есть ли в ней тот или иной лист. Для этого целесообразно применить пользовательскую функцию dhSheetExist, код которой приводится в листинге 2.12. В качестве аргумента данная функция принимает строку с именем искомого листа и возвращает значение ИСТИНА, если лист есть в книге, или ЛОЖЬ, если листа в книге нет.

Листинг 2.12. Проверка существования листа

Function dhSheetExist(strSheetName As String) As Boolean

Dim objSheet As Object

On Error GoTo HandleError ' При ошибке перейти на HandleError

' Пытаемся получить ссылку на заданный лист

Set objSheet = ActiveWorkbook.Sheets(strSheetName)

' Ошибки не возникло – лист существует

dhSheetExist = True

Exit Function

HandleError:

' При попытке получить доступ к листу с заданным именем _

возникла ошибка, значит, такого листа не существует

dhSheetExist = False

End Function

Работа приведенной функции основана на том, что если листа в книге нет, то при попытке обращения к нему (инструкция ActiveWorkbook. Sheets (strSheetName)) будет сгенерирована ошибка времени исполнения, которую перехватит обработчик ошибок, идущий в тексте программы после метки HandleError. Таким образом, при возникновении ошибки функция возвращает значение ЛОЖЬ. Если ошибка отсутствует, то при обращении к листу инструкции функции исполняются до конца (до оператора End Function) и функция возвращает значение ИСТИНА.

После написания данной функции она появится в окне Мастер функций (Формулы → Вставить функцию) в категории Определенные пользователем. После выбора функции нужно будет указать имя листа (это единственный аргумент данной функции), наличие которого в текущей книге нужно проверить. Результат выполнения функции отобразится в ячейке, в которой находится курсор: если указанный лист присутствует в текущей книге, то будет выведено значение ИСТИНА, при отсутствии листа – значение ЛОЖЬ.

Перед началом работы с листом Excel иногда бывает полезно узнать, защищен он или нет (вспомним, что защита рабочего листа включается с помощью кнопки Защитить лист на вкладке Рецензирование в группе Изменения. Быстро получить ответ на этот вопрос можно с помощью макроса, код которого приведен в листинге 2.13.

Листинг 2.13. Проверка наличия защиты рабочего листа

Sub IsSheetProtected()

' Проверка, установлена ли защита на содержимое листа

If Worksheets(1).ProtectContents Then

MsgBox «Защита листа включена»

Else

MsgBox «Защита листа не включена»

End If

End Sub

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

Нередко перед тем как вывести данные на печать пользователь задумывается: а сколько страниц займет печатная форма документа? Для получения ответа на этот вопрос следует написать и применить макрос, код которого приведен в листинге 2.14 (данный код нужно набрать в стандартном модуле редактора VBA).

Листинг 2.14. Подсчет страниц

Sub GetPrintPagesCount()

Dim wshtSheet As Worksheet

Dim intPagesCount As Integer

' Суммирование количества страниц, необходимых для печати всех _

листов книги

For Each wshtSheet In Worksheets

intPagesCount = intPagesCount + (wshtSheet.HPageBreaks.Count

+ 1) * _

(wshtSheet.VPageBreaks.Count + 1)

Next

MsgBox "Всего страниц: " & intPagesCount

End Sub

Программа вычисляет количество необходимых для печати страниц на основе данных о горизонтальных и вертикальных разрывах страницы на листе (использование свойств HPageBreakers. Count и VPageBreakers. Count дает информацию о количестве горизонтальных и вертикальных разрывов страниц листа соответственно). Когда мы узнали, сколько разрывов страниц на листе, вычислить количество самих страниц не представляет большого труда (о чем свидетельствует простота используемого для этого расчета выражения: (wshtSheet. HPageBreaks. Count + 1) * (wshtSheet.VPageBreaks. Count + 1)).

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

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

Предположим, что в таблице в ячейках с 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

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

Ячейка и диапазон

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

Чтобы быстро заполнить ячейку содержимым ячейки, расположенной выше, следует нажать сочетание клавиш 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.

Листинг 2.16. Ввод данных в ячейки

Sub SetCellData()

' Заполнение значениями ячеек А3 и В4

Range(«A3») = «Данные для ячейки A3»

Range(«B4») = «Данные для ячейки B4»

End Sub

В результате выполнения данного макроса в ячейку A3 будет введено значение

Данные для ячейки A3, а в ячейку В4 – Данные для ячейки В4.

С помощью похожего макроса (листинг 2.17) можно выполнять вычисления с использованием формул.

Листинг 2.17. Ввод данных с использованием формул

Sub SetCellFormula()

' Запись в ячейку А6 формулы «=A5+B5»

Range(«A6») = «=A5+B5»

End Sub

В результате выполнения данного макроса в ячейке Аб отобразится сумма ячеек А5 и В5. Если же в приведенном макросе вместо строки Range («Аб») = «=А5+В5» ввести, например, Range («А2:А5») = 2, то все ячейки диапазона А2:А5 будут заполнены значением 2.

При вводе в ячейку дробных значений Excel может интерпретировать их в дату. Это обусловлено тем, что в некоторых случаях Excel не знает, что нужно ввести пользователю: дату или дробное значение. Например, при попытке ввести в ячейку дробь 2/5 после нажатия Enter в данной ячейке отобразится 02.май, а при вводе дроби 7/92 появится значение июл.92. Чтобы обойти эту особенность, достаточно при вводе дроби набрать перед ней символ 0, например 0 2/5. Теперь Excel поймет пользователя правильно и в ячейке после нажатия клавиши Enter отобразится значение 2/5.

Иногда возникает необходимость объединить содержимое разных ячеек рабочего листа. Для выполнения объединения необходимо воспользоваться оператором &. Чтобы лучше понять суть данной операции, рассмотрим ее на конкретном примере.

Допустим, имеются два списка: в одном содержатся должности сотрудников, в другом – их фамилии. Нам нужно объединить эти списки. В первом списке в ячейке А1 содержится должность Бухгалтер, во втором списке в ячейке В1 – фамилия Рублев. Объединим эти значения в ячейке С1 (все данные в примере условны).

Для этого в ячейке С1 необходимо ввести следующую формулу: =А1&" "&В1. После нажатия Enter в ней отобразится значение Бухгалтер Рублев.

При объединении ячеек можно ввести дополнительную информацию – соответствующий текст набирается в формуле между кавычками. Например, если в нашем примере для ячейки С1 создать следующую формулу: =А1&" по зарплате "&В1, то после ее ввода в ячейке отобразится значение Бухгалтер по зарплате Рублев.

Примечание

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

Если в ячейках А2 и В2 содержатся значения соответственно Экономист и Сидоров, то для объединения их в ячейке С2 можно нажать сочетание клавиш Ctrl+D. При этом созданная в ячейке С1 формула скопируется с учетом относительных адресов и в ячейке С2 отобразится значение Экономист по зарплате Сидоров.

Предположим, нам необходимо выделить диапазон с данными, расположенный непосредственно над текущей ячейкой. Можно, конечно, выполнить это стандартным способом (например, с помощью мыши). Но в некоторых случаях для этого целесообразнее использовать макрос, код которого приведен в листинге 2.18.

Листинг 2.18. Выделение диапазона над текущей ячейкой

Sub SelectCellRange()

Dim strSelTop As String, strSelBottom As String

' Получение адресов нижней и верхней ячеек диапазона для выделения

strSelBottom = ActiveCell.Address

strSelTop = Cells(1, ActiveCell.Column).Address

' Выделяем все ячейки выше текущей (вместе с текущей ячейкой)

Range(strSelTop & ":" & strSelBottom).Select

End Sub

После выполнения макроса будет выделен диапазон, расположенный над текущей ячейкой (вместе с текущей ячейкой).

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

Листинг 2.19. Поиск ближайшей пустой ячейки столбца

Sub FindEmptyCell()

' Поиск ближайшей пустой ячейки в текущем столбце

Do While Not IsEmpty(ActiveCell.Value)

ActiveCell.Offset(1, 0).Select

Loop

End Sub

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

В процессе работы с большими объемами данных часто возникает необходимость быстрого поиска максимального значения определенного диапазона (либо всего рабочего листа). Например, если в таблице хранится информация о торговой выручке за каждый день в течение года, то, чтобы найти максимальную дневную выручку вручную, потребуется слишком много времени и усилий. Для автоматизации данного процесса можно применить макрос, код которого приведен в листинге 2.20.

Листинг 2.20. Поиск максимального значения

Sub FindMaxValue()

On Error Goto NoCell

If Selection.Count > 1 Then

' Поиск максимального значения в выделенных ячейках

Selection.Find(Application.Max(Selection)).Select

Else

' Поиск максимального значения во всех ячейках листа

ActiveSheet.Cells.Find(Application.Max(ActiveSheet.Cells)).Select

End If

Exit Sub

NoCell:

MsgBox «Максимальное значение не найдено»

End Sub

После запуска данного макроса будет выделена ячейка, содержащая максимальное значение. Если выделен диапазон, поиск осуществляется только в нем; если же диапазон не выделен, то поиск ведется по всему рабочему листу. Если максимальное значение не обнаружено, то по окончании поиска на экране отобразится окно с сообщением Максимальное значение не найдено (текст этого сообщения можно корректировать по своему усмотрению путем внесения соответствующих изменений в код макроса).

Рассмотрим трюк, с помощью которого можно автоматически заменять значения ячеек диапазона на какие-либо фиксированные значения. В листинге 2.21 показан код одного из макросов, которые позволяют решить данную задачу.

Листинг 2.21. Автоматическая замена значений

Sub ReplaceValues()

Dim cell As Range

' Проверка каждой ячейки диапазона на возможность замены _

значения в ней (отрицательные значения заменяются на -1, _

положительные – на 1)

For Each cell In Range(«C1:C3»).Cells

If cell.Value < 0 Then

cell.Value = -1

ElseIf cell.Value > 0 Then

cell.Value = 1

End If

Next

End Sub

В данном примере обрабатывается диапазон С1:C3. После применения приведенного выше макроса все положительные значения данного диапазона будут заменены на 1, а отрицательные – на -1.

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