Рабочий лист является основным составным элементом рабочей книги. По умолчанию в состав новой книги включены три листа. Чтобы изменить количество листов в новой книге по умолчанию, необходимо войти в режим настройки Excel (для этого справа на панели быстрого доступа необходимо нажать кнопку с треугольником и в раскрывающемся списке выбрать пункт Другие команды), в разделе Основные, в поле Число листов, указать требуемое значение. Следует отметить, что максимально возможное количество листов в книге – 255.
Листы-синонимы
В программе Microsoft Excel запрещено создание разных рабочих листов с одинаковыми именами. Однако с помощью небольшой хитрости можно обойти этот запрет. Для этого нужно использовать разный регистр символов и выбирать те символы, которые в русском и английском языках одинаковые (например, буквы С, А, Р и т. д.). В частности, можно создать два листа с именем Таблица, но в имени одного из них будет использована английская буква А.
Автоматическая вставка URL-адреса
Наиболее распространенный способ вставки в документ URL-адреса – это копирование его в буфер обмена и последующая вставка в требуемое место. Чтобы произвести данную операцию, можно воспользоваться и другим способом. Для этого необходимо выполнить следующие действия.
1. Открыть требуемую страницу в Internet Explorer.
2. Перейти в Excel, выделить ячейку, в которую необходимо вставить адрес, и нажать сочетание клавиш Ctrl+K. В результате откроется окно Вставка гиперссылки.
3. Нажать Alt+Tab для перехода в Internet Explorer.
4. Нажать Alt+Tab для возврата обратно в Excel.
5. В окне Вставка гиперссылки нажать кнопку ОК.
В результате выполнения указанных действий адрес веб-страницы отобразится в выделенной ячейке рабочего листа.
Быстрый переход по рабочему листу
Если рабочий лист 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).
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.
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. В качестве аргумента данная функция принимает строку с именем искомого листа и возвращает значение ИСТИНА, если лист есть в книге, или ЛОЖЬ, если листа в книге нет.
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.
Sub IsSheetProtected()
' Проверка, установлена ли защита на содержимое листа
If Worksheets(1).ProtectContents Then
MsgBox «Защита листа включена»
Else
MsgBox «Защита листа не включена»
End If
End Sub
Если текущий рабочий лист защищен, то после выполнения данного макроса появится окно с сообщением Защита листа включена. При отсутствии защиты в этом окне отобразится сообщение Защита листа не включена.
Сколько страниц на всех листах?
Нередко перед тем как вывести данные на печать пользователь задумывается: а сколько страниц займет печатная форма документа? Для получения ответа на этот вопрос следует написать и применить макрос, код которого приведен в листинге 2.14 (данный код нужно набрать в стандартном модуле редактора VBA).
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 (этот код должен быть помещен в модуль рабочего листа).
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
Теперь все данные таблицы будут оформлены в соответствии с нашими требованиями. При внесении в таблицу изменений она будет автоматически переформирована.