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