Создание нестандартного формата и стиля
Если пользователя по какой-либо причине не устраивает набор предусмотренных в MS Excel форматов, то он может создать свой собственный нестандартный формат. Пользовательские форматы имеет смысл вводить, если достаточно часто приходится работать с информацией, для которой нет стандартного представления в Excel или же существующие форматы не удовлетворяют предъявляемым к оформлению таблиц требованиям. Чтобы создать собственный формат, в диалоговом окне Формат ячеек на странице Число в списке Числовые форматы: следует выбрать пункт (все форматы). Затем в списке Тип:, содержащем так называемую маску формата, нужно выбрать элемент, наиболее близкий к тому формату, в котором требуется представить значение в ячейке. Отобранный в списке элемент попадает в одноименное поле ввода, где его можно желательным образом изменить. После внесения в маску изменений нужно нажать кнопку ОК, чтобы сохранить новый формат.
Маска формата, по сути, представляет собой образец, шаблон, в соответствии с которым оформляется запись числового значения. Маска формата содержит ряд специальных символов, которые тем или иным образом влияют на оформление числа. Маски форматов состоят из одной секции для определения текстового формата и трех секций для определения числовых форматов: первая — для определения формата положительных значений, вторая — для отрицательных значений и третья — для нулевых. Некоторые специальные символы, которые чаще всего используются при задании пользовательских форматов, приведены в табл. 13.2.
Таблица 13.2.Специальные символы пользовательских форматов
Символ формата Соответствующие символы значения или влияние на формат
? Любая цифра. Незначащие нули заменяются пробелами
0 Любая цифра. Незначащие нули не удаляются.
# Любая цифра. Но если ничего не введено, ничего не отображается
Основной Формат по умолчанию, в котором представляются числа
/ Разделяет числитель и знаменатель дроби
Пробел Используется для разделения порядков
, Определяет позицию десятичной запятой
_(знак подчеркивания) Резервирует место для символа, который следует за ним
: р -+() Появляются в той позиции, в которой они введены в формате
Е, Е+е, е+ Формат научной записи чисел
% Умножает число на 100 и представляет его в виде процентного отношения
@ Указывает, где должен появляться текст, введенный в ячейку
*символ Заполняет оставшееся место в ячейки символами, указанными после *
"текст" Отображает текст, заключенный в кавычки
[цвет] Форматирует ячейку указанным цветом
\ Отображает следующий за косой чертой символ
Если достаточно часто приходится применять к различным ячейкам одни и те же параметры форматирования, то такой набор параметров удобно сохранить в собственном нестандартном стиле. Чтобы создать такой стиль, следует установить для какой-либо ячейки нужное форматирование, выполнить команду Формат > Стиль... и в появившемся окне в поле Имя стиля: ввести его название, а затем настроить желательным образом параметры форматирования и нажать кнопку Добавить.
СОВЕТ
Важно помнить, что если ячейки уже отформатированы определенным стилем, а затем какие-то параметры этого стиля изменились, то такие ячейки будут автоматически переформатированы.
Копирование форматирования
Во время стандартно выполняемого копирования ячейки осуществляется ее полное копирование, то есть копируются содержимое, примечание и формат ячейки. Если нужно перенести в другую ячейку какие-либо отдельные элементы, то обычное копирование через буфер обмена или буксировкой не приведет к желаемому результату. В этом случае вместо команды Правка > Вставить следует использовать команду Правка > Специальная вставка... В окне этой команды (рис. 13.9) в группе Вставить нужно включить переключатель, соответствующий тому элементу ячейки, который требуется скопировать. Например, для копирования только формата нужно включить переключатель форматы. К сожалению, за одно обращение к окну можно скопировать только один элемент. Поэтому для копирования нескольких элементов, например примечания и формата, команду Правка > Специальная вставка... потребуется выполнить несколько раз.
Рис. 13.9. Окно команды Специальная вставка
Для копирования параметров форматирования, которые уже имеют одна или несколько ячеек, можно использовать и прием, известный по текстовому редактору MS Word. Ячейку, которая имеет нужные параметры форматирования, следует выделить и затем щелкнуть на кнопке Формат по образцу в панели Стандартная. Рядом с курсором мыши появится кисточка. Теперь достаточно щелкнуть клавишей мыши на левой верхней ячейке выделенного диапазона ячеек, которому следует присвоить скопированный формат. При использовании форматирования по образцу копируются все параметры форматирования, в том числе параметры форматирования чисел, текстовых фрагментов, цвет, обрамление, узор заливки.
Автоматизация вычислений
В таблицах, содержащих числовую информацию, довольно часто приходится выполнять различные вычисления. Основное преимущество программы MS Excel состоит в богатом наборе средств автоматизации вычислений. Всевозможные вычисления в программе MS Excel основаны на формулах, которые, собственно, и задают правила выполнения этих вычислений.
Автосуммирование
Одной из самых распространенных вычислительных операций в таблицах является суммирование чисел, расположенных в некотором диапазоне ячеек. Суммирование, как и все остальные вычисления, основано на использовании формулы, но в связи с массовым характером применения этой операции в MS Excel предусмотрен ряд способов автоматизации суммирования без явного использования соответствующей формулы.
Самый простой способ автоматизации некоторых простых вычислений состоит в использовании строки состояния. При выделении любого фрагмента таблицы автоматически подсчитывается сумма всех чисел, находящихся в ячейках фрагмента. Результат суммирования отображается в строке состояния уже в процессе выделения ячеек (рис. 13.10). В контекстном меню этого участка строки состояния можно выбрать и другие варианты операций над содержимым выделенного фрагмента: найти среднее арифметическое, максимальное или минимальное значение и т. д. (рис. 13.10).
ВНИМАНИЕ
Используя этот способ автосуммирования, необходимо иметь в виду, что во время подсчета суммы используются значения из всех выделенных ячеек, игнорируются только ячейки, содержащие текст. Все остальные данные (типа дата, время, формула) участвуют в вычислении суммы после соответствующего автоматического преобразования в числовой формат. Так, например, значение 1/2/1, трактуемое как дата 01.02.2001 года, в числовом формате рассматривается как число 36 923. И если ячейка с такой датой попадет в выделенный фрагмент, то ее содержимое будет включено в общую сумму.
Второй способ состоит в использовании кнопки Автосуммирование панели Стандартная. Как правило, итоговые суммы в таблицах помещают справа от строки или под столбцом, которые содержат подлежащие суммированию числа. В программе MS Excel достаточно выделить ячейку, в которую следует поместить результат (то есть ячейку, расположенную справа или под суммируемыми ячейками), и нажать кнопку Автосуммирование. Программа MS Excel попытается самостоятельно определить, какой диапазон ячеек выбран для суммирования. Предполагаемый программой диапазон выделяется пунктирным контуром.
Рис. 13.10.Результаты автовычислений в строке состояния и контекстное меню
СОВЕТ
Необходимо внимательно проверить, совпадает ли предлагаемый программой диапазон ячеек с тем, который намечен для вычисления суммы.
Если программа правильно определила слагаемые, то для получения результата нужно нажать клавишу Enter. Программа вставит в выделенную ячейку соответствующую формулу и отобразит в ней вычисленную сумму. Если программа ошиблась и предложила диапазон ячеек, отличающийся от требуемого, то нужно выполнить буксировку пунктирного контура от ячейки с первым слагаемым к ячейке с последним слагаемым и после этого нажать клавишу Enter.
Формулы в MS Excel
Как уже неоднократно отмечалось, в ячейках таблицы могут содержаться не только значения, но и формулы, т. е. инструкции, указывающие, что и как следует вычислять. Формула представляет собой выражение, состоящее из чисел, знаков математических операций, адресов ячеек таблицы, названий функций, круглых скобок и т. д.
Отличительным признаком формул в программе MS Excel является наличие знака равенства (=), плюс (+) или минус (-) в начале формулы.
Обычно формулы в качестве аргументов используют значения из других ячеек рабочей таблицы. Рассмотрим, например, формулу =А1+А2.Она интерпретируется как указание MS Excel сложить числовые значения, хранящиеся в ячейках А1 и А2, после чего отобразить результат вычислений в ячейке, заменив находящуюся в ней формулу.
Операции и функции
В формулах допускается использование знаков математических и логических операций, а также операции конкатенации, объединяющей текстовые строки. Если в одном выражении используется несколько операций, то порядок их выполнения диктуется правилами старшинства, аналогичными правилам, действующим в арифметике. Например, действие умножения выполняется раньше, чем действие сложения или вычитания. Так, в формуле =А1+А2*АЗсначала выполняется умножение содержимого ячеек А2 и A3, а потом выполняется сложение полученного результата с содержимым ячейки А1.Если нужно изменить порядок, определяемый старшинством операций, то применяются круглые скобки. Как и в арифметике, действия в круглых скобках выполняются в первую очередь. Поэтому в формуле =( А1+А2)*АЗсначала будет выполнено сложение, а затем умножение. Ниже приведены обозначения операций, которые могут использоваться в формулах программы MS Excel (в порядке убывания их приоритета):
§ - (унарный минус или отрицание);
§ % (вычисление процента);
§ ^ (возведение в степень);
§ *, / (умножение и деление);
§ +, - (сложение и вычитание);
§ & (оператор конкатенации);
§ <, <=, >, >=, =, <> (операции сравнения: «меньше», «меньше или равно», «больше», «больше или равно», «равно» и «не равно» соответственно).
Рис. 13.11.Диалоговое окно Мастер функций
Кроме операций в формулах могут использоваться функции. В MS Excel определено более 200 различных функций — математических, статистических, финансовых, для работы с базами данных и ряд других. Командой Вставка > Функция..,, а также с помощью кнопки Вставка функций панели инструментов Стандартная или строки формул можно вызвать мастера вставки функций. Работа мастера состоит из двух шагов. Для каждого шага используется отдельное окно. Диалоговое окно первого шага Мастер функций — шаг 1 из 2 (рис. 13.11), в котором перечислены все функции, используется для выбора нужной для вычислений функции, а окно второго шага — для задания ее аргументов.
Чтобы включить функцию в формулу в списке Категория: окна первого шага, нужно выбрать строку, соответствующую характеру включаемой функции, а затем в обновившемся после выбора категории списке Функция: отобрать строку с ее названием. Во время выбора функции в нижнем секторе окна отображаются ее описание и требуемые аргументы. После нажатия кнопки ОК произойдет открытие окна второго шага мастера функций, в котором предоставляется возможность ввести все необходимые для вычисления значения функции аргументы. Аргументы функции записываются в круглых скобках после ее названия. Если аргументов у функции несколько, то они перечисляются через запятую. Например, функция вычисления суммы называется СУММ.Если требуется просуммировать числа, находящиеся в ячейках Al, A2 и A3, то это можно сделать с помощью функции СУММ(А1,А2,АЗ)с тремя аргументами.
Если аргументом функции или формулы является адрес ячейки, то вместо ввода этого адреса с клавиатуры можно выполнить щелчок клавишей мыши на нужной ячейке — ее адрес автоматически станет аргументом выбранной функции или записываемой в ячейку формулы. Кроме того, с помощью кнопки , которая расположена в правом конце поля ввода аргументов, можно временно перейти к исходной таблице и в ней выделить соответствующие ячейки или их диапазоны.
Некоторые функции, например функция вычисления суммы СУММ,среднего арифметического (она называется СРЗНАЧ),наименьшего (МИН)или наибольшего (МАХ) значений, могут иметь много аргументов, которые должны быть расположены в некоторых ячейках таблицы. В этом случае адреса всех аргументов должны быть указаны в обращении к такой функции. В программе MS Excel предусмотрена возможность упрощения обращения к функциям с помощью задания так называемого диапазона ячеек.Диапазон ячеек задается адресами первой и последней ячейки диапазона, которые отделены друг от друга двоеточием. Например, запись В2:В6аналогична перечислению совокупности ячеек В2, ВЗ, В4, В5, В6, а диапазон А1:ВЗзадает прямоугольный фрагмент, он эквивалентен следующему списку аргументов Al, A2, A3, Bl, B2, ВЗ.Обращаем внимание читателя на то, что обычный список может содержать произвольные, не смежные адреса ячеек, в то время как диапазон всегда включает в список аргументов все его ячейки.
Ошибки в формулах
При работе с формулами могут возникнуть ошибки, связанные с использованием адресов пустых или удаленных ячеек, некорректным вводом аргументов формул. В этом случае Excel выдает сообщение об ошибке, записанное в условном виде. В таблице 13.3 приведены наиболее часто встречающиеся сообщения об ошибках и описывается их смысл.
Таблица 13.3.Сообщения об ошибках в формулах
#ДЕЛ/0! Попытка деления на ноль
#Н/Д! Отсутствуют данные, необходимые для расчетов (возможно, ячейка пуста)
#ИМЯ! Ссылка на несуществующее имя
#ЧИСЛО! Использован недопустимый числовой аргумент
#ССЫЛКА! Неправильно указан адрес ячейки
#ЗНАЧ! Тип значения не совпадает с типом данных, допустимых для данного аргумента
Редактирование формул
По умолчанию Excel показывает непосредственно в ячейке результат вычислений по формуле, а саму формулу — в строке формул. Существует возможность редактировать формулы непосредственно в ячейках. Для этого следует выполнить команду Сервис > Параметры... и в окне команды на странице Вид в разделе Параметры окна нужно включить флажок формулы. MS Excel перейдет в режим, допускающий редактирование формул в ячейках.
Автопересчет
Каждый раз, когда в ячейки, адреса которых используются в формулах, вносятся изменения, программа MS Excel автоматически пересчитывает значения во всех ячейках таблицы. Если таблица достаточно велика, то такой пересчет может заметно увеличить время редактирования таблицы. Поэтому, например, во время отладки автоматический пересчет Excel можно отключить. Для этого нужно выполнить команду Сервис > Параметры... и на странице Вычисления включить переключатель вручную. Теперь пересчет значений будет производиться только при нажатии клавиши F9.