Автоматизация вычислений

В таблицах, содержащих числовую информацию, довольно часто приходится выполнять различные вычисления. Основное преимущество программы 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! Попытка деления на ноль

#Н/Д! Отсутствуют данные, необходимые для расчетов (возможно, ячейка пуста)

#ИМЯ! Ссылка на несуществующее имя

#ЧИСЛО! Использован недопустимый числовой аргумент

#ССЫЛКА! Неправильно указан адрес ячейки

#ЗНАЧ! Тип значения не совпадает с типом данных, допустимых для данного аргумента