Excel. Трюки и эффекты

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

Повышение точности вычисления формул

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

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

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

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

Скрытие сообщений об ошибках при вычислениях

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

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

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

Разработка и применение полезных пользовательских функций

Наряду с формулами функции также являются одним из основных инструментов Microsoft Excel. Функции могут быть как системными (то есть изначально заложенными в программу), так и пользовательскими (пользовательская функция – это функция, созданная пользователем с применением языка VBA).

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

Объединение данных диапазона

Функция Couple предназначена для объединения всех данных, которые расположены в указанном диапазоне, с добавлением пробелов между ними. Код данной функции выглядит следующим образом (листинг 2.43).

Листинг 2.43. Функция Couple

Function Couple(Diapazon)

' Объединение данных, содержащихся в ячейках диапазона _

Diapazon (разделитель между значениями – пробел)

' iCell – текущая ячейка

For Each iCell In Diapazon

' Сцепляются данные только заполненных ячеек

If IsEmpty(iCell) <> True Then

' Добавление значения ячейки в выходную строку

If Couple = "" Then

Couple = iCell

Else

Couple = Couple & " " & iCell

End If

End If

Next

End Function