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

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

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

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

Листинг 2.37. Последовательный ввод данных

Sub StreamInput()

Dim strDate As String

Dim strSum As String

Dim lngRow As Long

' Ввод данных в цикле (повторяется до тех пор, пока пользователь _

не введет пустую строку или не нажмет «Отмена» в окне ввода)

Do

lngRow = Range(«A65536»).End(xlUp).Row + 1

' Ввод даты

strDate = InputBox(«Вводим дату»)

If strDate = "" Then Exit Sub

' Ввод выручки

strSum = InputBox(«Вводим выручку»)

If strSum = "" Then Exit Sub

' Запись данных в ячейки

Cells(lngRow, 1) = strDate

Cells(lngRow, 2) = strSum

Loop

End Sub

После написания кода макрос Streamlnput будет доступен в окне выбора макросов. Для удобства поместите в любое удобное место интерфейса кнопку и привяжите к ней данный макрос – и можно приступать к последовательному вводу данных.

Введем в ячейки А1 и В1 названия соответствующих столбцов таблицы (например, Дата и Выручка) и нажмем кнопку вызова макроса. В результате откроется диалоговое окно, в котором с клавиатуры сначала вводится дата (в поле Вводим дату), а после нажатия кнопки ОК – сумма выручки (в поле Вводим выручку). После еще одного нажатия кнопки ОК введенные данные отобразятся в ячейках А2 и В2 соответственно, а в диалоговом окне можно вводить следующие данные (которые, в свою очередь, будут помещены в ячейки A3 и ВЗ) и т. д. Для выхода из цикла следует нажать в диалоговом окне кнопку Cancel

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

Напишем в стандартном модуле редактора VBA код, который выглядит следующим образом (листинг 2.38).

Листинг 2.38. Выделение отрицательных значений

Sub NegSelect()

Dim cell As Range

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

которые содержат отрицательные значения

For Each cell In Selection

If cell.Value < 0 Then

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

Else

cell.Interior.ColorIndex = xlNone

End If

Next cell

End Sub

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

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

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

Листинг 2.39. Получение информации о выделенном диапазоне

Altribute VB_Name = «module 1»

Sub TypeOfSelection()

Dim rgSelUnion As Range ' Объединение выделенных областей

Dim strTitle As String ' Заголовок сообщения

Dim strMessage As String ' Текст сообщения

Dim strSelType As String ' Тип выделения (простой или _ множественный)

Dim lngBlockCount As Long ' Количество блоков в выделении

Dim lngCellCount As Variant ' Общее количество выделенных ячеек

Dim lngColCount As Long ' Количество выделенных столбцов

Dim lngRowCount As Long ' Количество выделенных строк

Dim lngAreasCount As Long ' Количество выделенных областей

Dim strCurSelType As String

Dim rgArea As Range

' Подсчет количества выделенных областей и определение типа

выделения: _

простое (одна область) или сложное(несколько областей)

intAreasCount = Selection.Areas.Count

If intAreasCount = 1 Then

strTitle = «Простое выделение»

Else

strTitle = «Множественное выделение»

End If

' Определение типа выделения первой области

strSelType = dhGetAreaType(Selection.Areas(1))

' Создание объединения во избежание повторного учета _

пересекающихся участков выделенных диапазонов

Set rgSelUnion = Selection.Areas(1)

For Each rgArea In Selection.Areas

strCurSelType = dhGetAreaType(rgArea)

' Изменение надписи о типе всего выделения, если _

есть выделения различного типа

If strCurSelType <> strSelType Then

strSelType = «Множественный»

End If

' Определение количества блоков перед их добавлением

в объединение

If strCurSelType = «Block» Then

lngBlockCount = intBlockCount + 1

End If

' Добавление в объединение

Set rgSelUnion = Union(rgSelUnion, rgArea)

Next rgArea

' Просматриваются элементы созданного объединения

For Each rgArea In rgSelUnion.Areas

Select Case dhGetAreaType(rgArea)

Case «Строка»

lngRowCount = lngRowCount + rgArea.Rows.Count

Case «Столбец»

lngColCount = lngColCount + rgArea.Columns.Count

Case «Лист»

lngColCount = lngColCount + rgArea.Columns.Count

lngRowCount = lngRowCount + rgArea.Rows.Count

End Select

Next rgArea

' Определение количества неперекрывающихся ячеек

intCellCount = rgSelUnion.Count

' Формирование и вывод итогового сообщения

strMessage = «Тип выделения:» & vbTab & strSelType & vbCrLf & _

"Количество областей: " & vbTab & intAreasCount & vbCrLf

& _

"Полных столбцов: " & vbTab & intColCount & vbCrLf & _

"Полных строк: " & vbTab & intRowCount & vbCrLf & _

"Блоков ячеек: " & vbTab & intBlockCount & vbCrLf & _

"Всего ячеек: " & vbTab & Format(intCellCount,

«#,###»)

MsgBox strMessage, vbInformation, strTitle

End Sub

Function dhGetAreaType(rgRangeArea As Range) As String

' Определение типа диапазона

If rgRangeArea.Count = Cells.Count Then

' Все ячейки рабочего листа

dhGetAreaType = «Лист»

ElseIf rgRangeArea.Cells.Count = 1 Then

' Одна ячейка

dhGetAreaType = «Ячейка»

ElseIf rgRangeArea.Rows.Count = Cells.Rows.Count Then

' Весь столбец

dhGetAreaType = «Столбец»

ElseIf rgRangeArea.Columns.Count = Cells.Columns.Count Then

' Вся строка

dhGetAreaType = «Строка»

Else

' Блок ячеек

dhGetAreaType = «Блок»

End If

End Function

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

Примечание

Этот макрос (информация о диапазоне) работает только в случае, когда текущая книга сохранена в файле типа Excel 1997–2003.

Как известно, для перехода в режим изменения формата ячейки необходимо или выполнить команду контекстного меню Формат ячеек, или на вкладке Главная в группе Ячейки нажать кнопку Формат и выбрать пункт Формат ячеек, или нажать сочетание клавиш Ctrl+1. Однако для изменения числового формата ячейки можно также воспользоваться специально созданной пользовательской панелью инструментов. Рассмотрим этот процесс подробнее.

Для реализации примера нам потребуется написать в редакторе VBA два кода: в модуле рабочего листа и в стандартном модуле. Код, помещаемый в модуль рабочего листа, выглядит следующим образом (листинг 2.40).

Листинг 2.40. Код в модуле рабочего листа

Sub Worksheet_Change(ByVal Target As Excel.Range)

Call UpdateToolbar

End Sub

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Call UpdateToolbar

End Sub

В стандартном модуле редактора VBA необходимо написать код, который приведен в листинге 2.41.

Листинг 2.41. Код в стандартном модуле

Sub FastChangeNumberFormat()

Dim bar As CommandBar

Dim button As CommandBarButton

' Удаление существующей панели инструментов (если она есть)

On Error Resume Next

CommandBars(«Числовой формат»).Delete

On Error GoTo 0

' Формирование новой панели

Set bar = CommandBars.Add

With bar

.Name = «Числовой формат»

.Visible = True

End With

' Создание кнопки

Set button = CommandBars(«Числовой формат»).Controls.Add _

(Type:=msoControlButton)

With button

.Caption = ""

.OnAction = «ChangeNumFormat»

.TooltipText = «Щелкните для изменения числового формата»

.Style = msoButtonCaption

End With

' Обновление созданной панели инструментов

Call UpdateToolbar

End Sub

Sub UpdateToolbar()

' Обновление панели инструментов (если она создана)

On Error Resume Next

' Изменение заголовка кнопки (на название формата выделенной ячейки)

CommandBars(«Числовой формат»).Controls(1).Caption = _

ActiveCell.NumberFormat

End Sub

Sub ChangeNumFormat()

' Отображение диалогового окна изменения формата ячейки

Application.Dialogs(xlDialogFormatNumber).Show

Call UpdateToolbar

End Sub

Теперь нужно запустить на выполнение макрос FastChangeNumberFormat (после написания кода он будет доступен в окне выбора макросов) – в результате на вкладке Надстройки появится одна кнопка. Название данной кнопки зависит от формата активной ячейки. При подведении к кнопке указателя мыши отобразится всплывающая подсказка Числовой формат: Щелкните для изменения числового формата. При нажатии данной кнопки откроется вкладка Число окна Формат ячеек. Формат активной ячейки изменяется в данном режиме по обычным правилам.

Следует отметить, что перейти в режим редактирования числового формата ячейки можно, запустив на выполнение макрос ChangeNumFormat – после написания приведенного выше кода он также будет доступен в окне выбора макросов.

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

В стандартном модуле редактора VBA нужно написать код, содержимое которого представлено в листинге 2.42.

Листинг 2.42. Тестирование скорости чтения и записи диапазонов

Sub TableSpeedTest()

Dim alngData() As Long ' Массив с числами

Dim lngCount As Long ' Количество элементов в массиве

Dim dtStart As Date ' Хранит время (и даже дату)

начала _ тестирования

Dim strArrayToTable As String ' Время записи в таблицу

Dim strTableToArray As String ' Время чтения из таблицы

Dim strMessage As String

Dim i As Long

' Подготовка диапазона ячеек

Range(«A:A»).ClearContents

' Ввод размера массива, формирование массива заданного размера

lngCount = InputBox(«Введите количество элементов»)

ReDim alngData(1 To lngCount)

' Заполнение массива данными

For i = 1 To lngCount

alngData(i) = i

Next i

' Перенос массива в таблицу

Application.ScreenUpdating = False

dtStart = Timer

For i = 1 To lngCount

Cells(i, 1) = i

Next i

strArrayToTable = Format(Timer – dtStart, «00:00»)

' Чтение данных из таблицы обратно в массив

dtStart = Timer

For i = 1 To lngCount

alngData(i) = Cells(i, 1)

Next i

strTableToArray = Format(Timer – dtStart, «00:00»)

Application.ScreenUpdating = True

' Вывод на экран результатов тестирования

strMessage = "Запись: " & strArrayToTable & vbCrLf & _

"Чтение: " & strTableToArray

MsgBox strMessage, , lngCount & « элементов»

End Sub

В результате написания данного кода в окне выбора макросов появится макрос TableSpeedTest. После его запуска откроется окно, в котором в поле Введите количество элементов следует с клавиатуры ввести количество элементов и нажать кнопку ОК. По окончании работы макроса на экране отобразится окно, в котором будет показано время (в секундах), необходимое для записи данных массива в диапазон и для считывания их обратно в массив. Например, у одного из авторов этой книги на запись массива из 30 000 элементов было затрачено 17 секунд, а на считывание данных из диапазона обратно в массив – только 1 секунда; массив из 65 000 элементов записывался 37 секунд, а перенос этих данных обратно в массив занял лишь 2 секунды.

Работа с формулами

Механизм формул является одним из основных инструментов, используемых в программе Microsoft Excel. В данном разделе мы рассмотрим несколько трюков, которые можно выполнять с использованием формул.

В Excel реализована возможность выполнения арифметических действий с датами. Для этого следует использовать функцию ДАТА. Например, при использовании формулы =ДАТА (2 0 0 4; 4; 11) – ДАТА (2 0 0 4; 3; б) в соответствующей ячейке будет получен результат 36.

В процессе работы часто возникают ситуации, когда необходимо суммировать значения ячеек, которые хранятся на разных листах. Для этого в формулу требуется включить названия соответствующих листов. Например, при использовании формулы =СУММ (Лист2: Лист3 !С1: С7) будет рассчитана сумма значений, которые хранятся на листах Лист2 и Лист3 в диапазоне С1:С7.

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

1. В ячейку А1 ввести формулу =А1+С1.

2. После того как отобразится окно с сообщением об ошибке, нажать в данном окне кнопку ОК либо Отмена (в данном случае не имеет значения).

3. Войти в режим настройки программы (для этого справа на панели быстрого доступа необходимо нажать кнопку с треугольником и в раскрывающемся списке выбрать пункт Другие команды).

4. Перейти в раздел Формулы.

5. Установить флажок Включить итеративные вычисления, а в поле Предельное число итераций ввести значение 1.

6. Нажать кнопку ОК.

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

Для копирования формул, помимо имеющихся в Excel стандартных средств, можно использовать VBA. Например, чтобы скопировать формулу из ячейки А1 в ячейку В2, можно использовать в программе на VBA следующие фрагменты:

Range(«B2»).Formula = Range(«A1»).Formula

Также для этого можно использовать код

Range(«B2»).Value = Range(«A1»).Formula

или

Range(«B2») = Range(«A1»).Formula

Для удобства использования рекомендуется назначить макросу какое-нибудь сочетание клавиш или кнопку с целью быстрого вызова.

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

Однако с помощью VBA можно присвоить ячейке имя таким образом, что оно не будет отображаться в списке имен. Для достижения такого эффекта можно использовать следующий код:

Names.Add Name:="Секрет", RefersTo:="=Лист1!$A$5", Visible:=False

При использовании данного кода ячейке А5, которая расположена на листе Лист1, будет присвоено имя Секрет. Это имя не будет отображаться в списке имен, но его можно использовать в формулах вместо адреса ячейки. Например, сумму ячеек А1 и А5 можно задать так: =А1+А5, а можно – с использованием скрытого имени: =А1+Секрет.

Совет

При создании макроса рекомендуется задать сочетание клавиш (например, Ctrl+Shift+A) для его быстрого вызова.

К аналогичному результату приводит использование следующего кода:

Range(«A7»).Name = «Защита»

Range(«Защита»).Name.Visible = False

В данном случае ячейке А7, которая расположена на текущем листе, присвоено скрытое имя Защита.

Для быстрого суммирования значений всех ячеек какого-либо столбца или строки предназначены специальные формулы. Например, чтобы получить сумму всех ячеек столбца А, нужно воспользоваться формулой =СУММ (А: А), а для суммирования всех ячеек строки 1 – формулой =СУММ (1:1). Если же необходимо получить сумму всех ячеек столбцов А, В и С, то формула будет выглядеть следующим образом: =СУММ (А: С). Для суммирования всех ячеек, которые расположены в строках 1, 2 и 3, применяется следующая формула: =СУММ (1:3).

При суммировании не стоит забывать, что курсор должен быть установлен за пределами суммируемого диапазона – в противном случае Excel выдаст сообщение об ошибке.

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

При первом способе необходимо выделить соответствующую ячейку, выполнить команду контекстного меню Правка → Копировать, а затем – команду Правка → Специальная вставка. В результате откроется окно, представленное на рис. 2.6.

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

Рис. 2.6. Окно Специальная вставка

Второй способ заключается в следующем: необходимо выделить соответствующую ячейку, перейти в режим ее редактирования (не используя при этом строку формул) и последовательно нажать клавиши F9 и Enter. После выполнения указанных действий формула в ячейке будет заменена ее текущим значением.

В процессе работы с формулами иногда можно заметить неточности в расчетах. Их наличие обусловлено тем, что по умолчанию Excel отображает цифры в ячейках с точностью меньшей, чем при их хранении (например, число 15,434 может отображаться как 15,43). Если в ячейках А1 и А2 хранится одинаковое значение – 15,434, а отображается 15,43, то результат формулы =А1+А2 будет отображен как 30,86, хотя на самом деле он равен 30,868. Иначе говоря, визуально наблюдается неточность в расчетах, хотя в действительности это связано лишь с округлением чисел при их отображении с меньшей точностью.

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

Во втором случае необходимо войти в режим настройки программы (для этого справа на панели быстрого доступа необходимо нажать кнопку с треугольником и в раскрывающемся списке выбрать пункт Другие команды) и в разделе Дополнительно установить флажок Задать точность как на экране, после чего нажать кнопку ОК. В результате Excel будет хранить данные в ячейках с такой же точностью, с которой они отображаются на экране. Иначе говоря, после выполнения данной операции в нашем примере число 15,434 будет преобразовано в 15,43 окончательно.

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

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

Для скрытия сообщений об ошибках можно использовать режим условного форматирования. Для этого нужно выделить те ячейки с формулами, результаты вычислений которых не должны отображаться в случае возникновения ошибок, затем в режиме Главная → Стили → Условное форматирование определить формулу вида =ЕОШИБКА (ссылка_на_ячейку), где ссылка_на_ячейку – это ссылка на активную ячейку выделенного диапазона. После этого для данной формулы следует установить белый цвет отображения.

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